Reuse SQL Queries between Server & Mobile App Databases

Como todos sabemos, a manutenção do código é cara - quanto mais complexo for o código, maior será o custo de sua manutenção. Portanto, desde o início dos tempos, os desenvolvedores de software têm se esforçado muito para atingir o objetivo de reutilização de código.

O Couchbase Mobile 3.0 introduziu o suporte para consultas SQL++. Como desenvolvedor, a primeira coisa que me veio à mente foi compartilhar as consultas usadas em projetos voltados para os aplicativos Couchbase Server e Couchbase Mobile.

Essa é uma boa ideia? Vamos explorar essa questão usando dados de amostra e um aplicativo móvel de prova de conceito.

Dados de amostra

Todo o código deste artigo está disponível em GitHub. Veja o LEIAME para obter informações sobre como configurar os projetos em seu computador.

Os dados de amostra são provenientes do OpenStreetMap e está licenciado sob a licença Open Data Commons Licença de banco de dados aberto (ODbL) pela OpenStreetMap Foundation.

O conjunto de dados contém todas as lojas que vendem sorvete nos Estados Unidos. Um exemplo de documento JSON está listado abaixo:

{
 "type": "Feature",
 "id": "node/472242349",
 "properties": {
 "addrCity": "Austin",
 "addrHousenumber": "4477",
 "addrPostcode": "78745",
 "addrState": "TX",
 "addrStreet": "South Lamar Boulevard",
 "addrUnit": "#790",
 "amenity": "ice_cream",
 "cuisine": "ice_cream",
 "name": "Amy's Ice Creams",
 "phone": "+1-512-891-0573",
 "id": "node/472242349"
},
  "geometry": {
   "type": "Point",
   "coordinates": [
    -97.7998856,
    30.230688
  ]
 }
}

{

"tipo": "Recurso",

"id": "node/472242349",

"propriedades": {

"addrCity": "Austin",

"addrHousenumber": "4477",

"addrPostcode": "78745",

"addrState": "TX",

"addrStreet": "South Lamar Boulevard",

"addrUnit": "#790",

"comodidade": "ice_cream",

"cozinha": "ice_cream",

"name" (nome): "Amy's Ice Creams" (Sorvetes da Amy),

"telefone": "+1-512-891-0573",

"id": "node/472242349"

"geometria": {

"tipo": "Ponto",

"coordenadas": [

-97.7998856,

30.230688

]

}

Como você pode ver, esse é um conjunto de dados versátil para testes porque tem propriedades incorporadas nos documentos.

Exemplo de aplicativo móvel

O Exemplo de aplicativo móvel foi escrito em SwiftUI com Combinar e é um aplicativo para iOS/macOS que exibe todas as várias lojas que vendem sorvete nos Estados Unidos. O aplicativo inclui um banco de dados pré-criado com os mesmos documentos JSON já importados para ele por conveniência. Esse aplicativo móvel usa a versão mais recente do SDK do Couchbase Lite. Você pode abrir o projeto no XCode a partir do arquivo de projeto na pasta src para acompanhar o processo.

Uma comparação de consulta simples

Para iniciar nossos testes, podemos escrever uma consulta simples para o Couchbase Server, para mostrar todos os endereços de sorveterias e ordená-los pelo nome da loja:

SELECT id, properties.addrCity, properties.addrHousenumber, properties.addrPostcode, 
properties.addrStreet, properties.addrState, properties.name 
FROM icecream 
WHERE properties.addrCity <> "" 
AND type = "Feature" 
ORDER BY properties.name

SELECIONAR id, propriedades.addrCity, propriedades.addrHousenumber, propriedades.addrPostcode,

propriedades.addrStreet, propriedades.addrState, propriedades.nome

DE sorvete

ONDE propriedades.addrCity <> ""

E tipo = "Recurso"

ORDEM BY propriedades.nome

Essa consulta tem um filtro que retorna apenas documentos em que o objeto da propriedade addrCity tem um valor e onde o documento tipo é igual a Recurso. Se você se pergunta por que temos o addrCity é devido a dados sujos ou documentos sem nomes ou informações ausentes nesse conjunto de dados. Usando um tipo O atributo é comum em aplicativos móveis para filtrar diferentes tipos de documentos.

Podemos usar o Editor de consultas do servidor Couchbase Console da Web para executar essa consulta. Quando a executei em meu Macbook Pro M1 Max pessoal, o resultado chegou em 38,4 ms. Essa consulta usa o adv_properties_addrCity_type que você pode ver clicando no ícone Consultor de índices botão.

Portando a consulta para o Couchbase Lite

Agora vamos ver essa mesma consulta em execução no nosso aplicativo móvel. Para visualizar o aplicativo móvel, você pode usar o XCode para abrir o arquivo IceCreamLocator.xcodeproj localizado no arquivo src pasta. Uma vez aberta no Xcode, localize a pasta IceCreamLocationRepository swift no arquivo Compartilhado\Data pasta.

O inicial ativa o registro detalhado do banco de dados para inspecionar as informações da consulta. Usando o registro detalhado, você pode analisar o que está acontecendo no Couchbase Lite, incluindo o desempenho da consulta.

Vamos analisar a consulta simples do Couchbase Server e ver como ela funciona no Swift com a nova API de consulta SQL++. A função para a consulta simples é denominada getListByTypeCityOrderNamee o código é exibido abaixo:

  func getListByTypeCityOrderName() -> Void {
      do {
        if let query = try _db?.createQuery("SELECT id, 
properties.addrCity, properties.addrHousenumber, properties.addrPostcode, properties.addrStreet, 
properties.addrState, properties.name FROM _ WHERE properties.addrCity <> \"\" AND 
type = \"Feature\" ORDER BY properties.name") {
            var results: [IceCreamLocation] = []
            let explain = try query.explain()
            print ("**EXPLAIN** \(explain)")
            for result in try query.execute() {
                if let data = result.toJSON().data(using: .utf8){
                   let location = try 
JSONDecoder().decode(IceCreamLocation.self, from: data)
                   results.append(location)
             }
           }
           self.iceCreamLocationList.send(results)
           self.iceCreamLocationList.send(completion: .finished)
         }
     } catch {
         print("**Error**: \(error)")
         self.iceCreamLocationList.send(completion: .failure(DbError.unknown))
     }
}

func getListByTypeCityOrderName() -> Vazio {

fazer {

se deixar consulta = tentar _db?.createQuery("SELECT id,

properties.addrCity, properties.addrHousenumber, properties.addrPostcode, properties.addrStreet,

properties.addrState, properties.name FROM _ WHERE properties.addrCity \"\" AND

type = \"Feature\" ORDER BY properties.name") {

var resultados: [IceCreamLocation] = []

deixar explicar = tentar consulta.explicar()

impressão ("**EXPLAIN** \(explicar)")

para resultado em tentar consulta.executar() {

se deixar dados = resultado.toJSON().dados(usando: .utf8){

deixar localização = tentar

JSONDecodificador().decodificar(IceCreamLocation.autônomo, de: dados)

resultados.anexar(localização)

}

autônomo.iceCreamLocationList.enviar(resultados)

autônomo.iceCreamLocationList.enviar(conclusão: .concluído)

}

} captura {

impressão("**Erro**: \(erro)")

autônomo.iceCreamLocationList.enviar(conclusão: .falha(DbError.desconhecido))

}

Há algumas maneiras de fazer uma consulta SQL++ no Swift. Neste exemplo, usamos o banco de dados createQuery para criar a consulta. No Couchbase Lite, estou usando o caractere de sublinhado (_) como o nome do banco de dados antes da função DE declaração. Alterar o nome do banco de dados é uma das alterações esperadas para reutilizar instruções SQL++. Além disso, para escapar das aspas no comando ONDE tive que adicionar o caractere de escape de string do Swift (\) antes de cada aspa.

Para testar essa consulta durante a execução, você pode abrir o arquivo IceCreamListViewModel encontrado no arquivo ViewModels pasta. Localize a pasta inicial e descomente a primeira linha de exemplo localizada na parte inferior da função que chama a função getListByTypeCityOrderName da função _repositório variável:

 //first example
_repository.getListByTypeCityOrderName()

1 2	//primeiro exemplo _repositório.getListByTypeCityOrderName()

Agora você pode usar o Xcode para executar o aplicativo em um simulador do iOS ou em um aplicativo do macOS. Em seguida, abra o aplicativo Console de depuração no Xcode, clicando no ícone Mostrar depuração no canto inferior direito do editor do Xcode, conforme destacado abaixo.

O registro detalhado exibirá uma quantidade enorme de informações de registro. Para ajudar a encontrar as informações corretas nos logs, use a caixa de filtro abaixo do console. Por exemplo, digite o texto "Informações de consulta" e, em seguida, pressione a tecla Enter em seu teclado para ver informações sobre a consulta. O console de depuração filtrará os dados que o Couchbase Lite retorna.

A inspeção dos registros mostrará a Informações de consulta e o número de documentos encontrados pela consulta, quantos bytes e quanto tempo a consulta levou. Lembre-se de que os números que você está vendo são de um simulador, não de um dispositivo real. Para ajustar o desempenho de aplicativos móveis, você deve usar ferramentas da Apple com um dispositivo real.

A velocidade de execução dessa consulta é impressionante. Ainda mais impressionante é o fato de que ainda não foram criados índices, portanto, essa consulta é executada sem um índice e faz uma varredura de tabela, o que significa que ela precisa examinar o banco de dados em cada documento para ver se ele corresponde aos requisitos da consulta. O Couchbase Lite não exige índices e fará varreduras de tabela se necessário, enquanto o Couchbase Server exige índices porque as varreduras de tabela podem prejudicar o desempenho de todo o cluster.

Em vários casos, a adição de índices pode melhorar significativamente o desempenho de suas consultas, portanto, vamos parar de executar o aplicativo, adicionar o índice adequado e verificar novamente o desempenho.

Abra o IceCreamLocationRepository e localize o createIceCreamIndexes função. Descomente o primeiro bloco de código para criar o primeiro índice de consulta:

   let simpleIndex = ValueIndexConfiguration(["properties.name", "properties.addrCity", "type"])
   try _db?.createIndex(simpleIndex, name: "idx_location_type_city_name")

1 2	deixar simpleIndex = ValueIndexConfiguration(["properties.name", "properties.addrCity", "tipo"]) tentar _db?.createIndex(simpleIndex, nome: "idx_location_type_city_name")

Como testamos para ver se a consulta usou o índice que criamos? Na consulta getListByTypeCityOrderName você verá duas linhas que imprimem os resultados da função explain no console:

let explain = try query.explain()
print ("**EXPLAIN** \(explain)")

1 2	deixar explicar = tentar consulta.explicar() impressão ("EXPLAIN \(explicar)")

A consulta explicar executará o equivalente à função SQLite EXPLICAR O PLANO DE CONSULTA e armazene os resultados em uma cadeia de caracteres que você envia ao console usando a função print. Você pode filtrar por TABELA DE VARREDURA no console de depuração para encontrar os resultados. Ao analisar os resultados, certifique-se de remover o filtro anterior da janela do console. Agora, execute novamente o aplicativo e examine os registros para verificar a alteração de desempenho.

Você deverá ver o USANDO O ÍNDICE com o nome do índice criado, o que informa que a consulta usa o índice adequado. Observe que, embora os índices possam ajudar as consultas, eles também podem tornar mais lentas as inserções e atualizações de documentos. Você pode encontrar mais informações sobre indexação na seção Couchbase Lite documentação.

Outra comparação de consulta

Em nossa segunda comparação, filtramos os resultados para mostrar apenas as sorveterias no estado da Geórgia, ordenadas por cidade e estado:

SELECT id, properties.addrCity, properties.addHousenumber, properties.addrPostcode, 
properties.addrStreet, properties.addrState, properties.name 
FROM icecream 
WHERE
properties.addrCity IS NOT NULL 
AND properties.addrState = 'GA'
AND type = "Feature" 
ORDER BY properties.name

SELECIONAR id, propriedades.addrCity, propriedades.addHousenumber, propriedades.addrPostcode,

propriedades.addrStreet, propriedades.addrState, propriedades.nome

DE sorvete

ONDE

propriedades.addrCity IS NÃO NULL

E propriedades.addrState = "GA

E tipo = "Recurso"

ORDEM BY propriedades.nome

Essa consulta retorna 15 documentos e usa uma consulta diferente, pois usamos o properties.addrState e properties.addrCity em nossos atributos ONDE cláusula.

Observe que mudamos a forma como verificamos o properties.addrCity e usar o atributo IS NOT NULL agora. Isso mostra a flexibilidade da linguagem SQL++ e como os desenvolvedores podem usar diferentes sintaxes para produzir os mesmos resultados.

Agora, vamos voltar ao nosso aplicativo móvel para observar as diferenças. A função para nossa segunda consulta é denominada getListByStateGeorgiae o código é exibido abaixo:

    func getListByStateGeorgia() -> Void {
        do {
           if let query = try _db?.createQuery("SELECT id, properties.addrCity, 
properties.addrHousenumber, properties.addrPostcode, properties.addrStreet, 
properties.addrState, properties.name FROM _ WHERE properties.addrCity NOT NULL AND 
properties.addrState = \"GA\" AND type=\"Feature\" ORDER BY properties.name") {
               var results: [IceCreamLocation] = []
               let explain = try query.explain()
               print ("**EXPLAIN** \(explain)")
               for result in try query.execute() {
                   if let data = result.toJSON().data(using: .utf8){
                      let location = try 
JSONDecoder().decode(IceCreamLocation.self, from: data)
                      results.append(location)
                 }
              }
              self.iceCreamLocationList.send(results)
              self.iceCreamLocationList.send(completion: .finished)
          }
        } catch {
            print("**Error**: \(error)")
            self.iceCreamLocationList.send(completion: .failure(DbError.unknown))
     }
   }

func getListByStateGeorgia() -> Vazio {

fazer {

se deixar consulta = tentar _db?.createQuery("SELECT id, properties.addrCity,

properties.addrHousenumber, properties.addrPostcode, properties.addrStreet,

properties.addrState, properties.name FROM _ WHERE properties.addrCity NOT NULL AND

properties.addrState = \"GA\" AND type=\"Feature\" ORDER BY properties.name") {

var resultados: [IceCreamLocation] = []

deixar explicar = tentar consulta.explicar()

impressão ("**EXPLAIN** \(explicar)")

para resultado em tentar consulta.executar() {

se deixar dados = resultado.toJSON().dados(usando: .utf8){

deixar localização = tentar

JSONDecodificador().decodificar(IceCreamLocation.autônomo, de: dados)

resultados.anexar(localização)

}

autônomo.iceCreamLocationList.enviar(resultados)

autônomo.iceCreamLocationList.enviar(conclusão: .concluído)

}

} captura {

impressão("**Erro**: \(erro)")

autônomo.iceCreamLocationList.enviar(conclusão: .falha(DbError.desconhecido))

}

Para testar essa nova consulta, você pode abrir o arquivo IceCreamListViewModel. Localize o inicial e comente a primeira linha de exemplo localizada na parte inferior da função que chama a função getListByTypeCityOrderName da função _repositório e descomente a linha que chama nossa nova função:

//first example
//_repository.getListByTypeCityOrderName()

//second example
_repository.getListByStateGeorgia()

//primeiro exemplo

//_repositório.getListByTypeCityOrderName()

/segundo exemplo

_repositório.getListByStateGeorgia()

Você pode depurar o aplicativo usando o Xcode e verificar a área de depuração. Ela mostra os mesmos 15 documentos que foram retornados pela consulta executada no Couchbase Server, mas voltamos a fazer uma varredura de tabela, o que não é o comportamento que queremos - alteramos a consulta, mas ainda não criamos nenhum índice novo.

Para corrigir isso, podemos voltar ao IceCreamLocationRepository e localize o createIceCreamIndexes função. Descomente o segundo bloco de código para criar o segundo índice de consulta:

        let secondIndex = 
ValueIndexConfiguration(["properties.name","properties.addrCity", "type", "properties.addrState"])
        try _db?.createIndex(secondIndex, name: "idx_location_type_city_state_name")

deixar secondIndex =

ValueIndexConfiguration(["properties.name","properties.addrCity", "tipo", "properties.addrState"])

tentar _db?.createIndex(secondIndex, nome: "idx_location_type_city_state_name")

Descobrimos que poderíamos reutilizar o código nessas duas consultas mais simples, mas tivemos que adicionar os índices para evitar varreduras de tabela.

Capturas com reutilização de consultas

O Couchbase Server e o Couchbase Mobile não usam o mesmo mecanismo de armazenamento ou de consulta. Portanto, você pode se deparar com consultas do Couchbase Server que precisam de pequenas modificações para serem otimizadas para o Couchbase Mobile.

Para obter mais informações sobre o Couchbase Server, consulte a documentação sobre o Otimizador baseado em custos que o mecanismo de consulta usa. O portal do desenvolvedor do Couchbase também tem um bom artigo sobre como melhorar desempenho da consulta no Couchbase Server que podem explicar por que algumas consultas são escritas de uma maneira específica.

Recomendo a leitura da documentação do Couchbase Mobile sobre solução de problemas consultas pois ele me ajudou a escrever consultas otimizadas e explica melhor a saída do plano de consulta do SQLite.

Resumo

Conforme mostrado em nossos exemplos de código com a versão 3.0 do Couchbase Mobile, é possível reutilizar o código SQL++ entre o Server e o Mobile. No entanto, talvez seja necessário modificar as consultas e os índices para obter o melhor desempenho. Por isso, recomendo fortemente a leitura da excelente documentação sobre SQL++ for Mobile - Diferenças em relação ao SQL++ for Server e Strings de consulta SQL++. Ele contém informações valiosas sobre as alterações que você pode precisar fazer em suas consultas para usá-las em seus aplicativos móveis.

Aqui estão alguns dos principais recursos mencionados ao longo da postagem:

Aaron LaBeau - Engenheiro de software principal

Platform

Self-Managed

Services

Capabilities

Why Couchbase?

Migrate to Capella

By Use Case

By Industry

By Application Need

Popular Docs

By Developer Role

Quickstart

Resource Center

About

Partnerships

Our Services

Partners: Register a Deal

Ready to register a deal with Couchbase?

Marriott

Todas as publicações

Reutilizar consultas SQL entre os bancos de dados do servidor e do aplicativo móvel