Sqlite

MANUAL SQLite¶

Versão 1.0

01. O que é¶

O SQLite, ou também chamado de banco de dados incorporado, é uma biblioteca feita na linguagem C e de código aberto que armazena seus registros em um único arquivo, ao contrário dos Sistemas de Gerenciamento de Banco de Dados (SGBDs) tradicionais, que precisam obrigatoriamente de uma arquitetura cliente-servidor.

Por sua praticidade e simplicidade, o SQLite está presente em aparelhos de televisão, aplicações mobile, desktop e web.

02. Formato de arquivo do aplicativo¶

SQLite é frequentemente usado como formato de arquivo em disco para aplicativos de desktop, como sistemas de controle de versão, ferramentas de análise financeira, catalogação de mídia e suítes de edição, pacotes CAD, programas de manutenção de registros e assim por diante. A operação tradicional Arquivo/Abrir chama sqlite3_open() para anexar ao arquivo de banco de dados. As atualizações acontecem automaticamente à medida que o conteúdo do aplicativo é revisado, de modo que a opção de menu Arquivo/Salvar se torna supérflua. A opção de menu Arquivo/Salvar_Como pode ser implementada usando a API de backup .

Há muitos benefícios nessa abordagem, incluindo melhor desempenho, redução de custos e complexidade e maior confiabilidade. Consulte as notas técnicas "aff_short.html" e "appfileformat.html" e "fasterthanfs.html" para obter mais informações. Este caso de uso está intimamente relacionado ao formato de transferência de dados e aos casos de uso do contêiner de dados abaixo.

03. Diferenciais¶

A diferença fundamental entre o SQLite e outros bancos de dados está no seu design e uso. Algumas das distinções mais significativas incluem:

Arquitetura: SQLite é um banco de dados baseado em arquivo, o que significa que toda a base de dados é armazenada em um único arquivo, enquanto outros bancos de dados como MySQL, PostgreSQL e SQL Server são bancos de dados de servidor, o que implica em um processo de servidor separado que gerencia as operações de banco de dados.
Escalabilidade: Devido à sua natureza baseada em arquivo, o SQLite é mais adequado para uso em aplicativos embarcados, aplicativos de desktop, e em dispositivos móveis, enquanto outros bancos de dados são mais escaláveis e projetados para atender a um grande número de usuários e operações simultâneas.
Recursos avançados: Em comparação com muitos outros bancos de dados, o SQLite tem um conjunto mais limitado de recursos avançados, como suporte limitado a procedimentos armazenados, funções definidas pelo usuário e algumas capacidades de segurança em comparação com sistemas de gerenciamento de banco de dados mais robustos.
Configuração e Gerenciamento: SQLite é projetado para ser leve e de fácil configuração, enquanto outros bancos de dados geralmente requerem configuração de servidor e gerenciamento mais complexo.

Embora o SQLite seja excelente para certos casos de uso, é importante considerar suas limitações em termos de escalabilidade e recursos avançados ao escolher um banco de dados para um aplicativo específico.

04. Decisão por usá-lo¶

O SQLite é frequentemente utilizado em cenários que envolvem:

Aplicativos Móveis: É amplamente utilizado em aplicativos móveis devido à sua natureza leve, não necessitando de um servidor separado e sendo facilmente integrado em dispositivos móveis.
Desenvolvimento de Protótipos: Por ser simples de configurar e usar, o SQLite é muitas vezes empregado em fases iniciais de desenvolvimento de software para prototipagem e testes.
Armazenamento Local em Aplicativos Web: Pode ser utilizado para armazenar dados localmente em aplicativos web que operam no navegador do usuário, oferecendo uma solução de banco de dados incorporada.
Aplicativos de Desktop: Em aplicativos de desktop, o SQLite é útil para armazenar dados localmente sem a necessidade de configurar um servidor de banco de dados separado.
Pequenos Projetos e Ferramentas: Em projetos menores, ferramentas e aplicativos de única utilização, o SQLite oferece uma solução simples e eficaz para armazenamento de dados.
Estes são apenas alguns exemplos de onde o SQLite é comumente empregado, destacando sua versatilidade e aplicabilidade em uma variedade de cenários de desenvolvimento de software.

05. Instalações¶

05.1. Instalação WIN¶

Acessar a página de downloads em https://sqlite.org/download.html ⧉

Copie e extraia o arquivo sqlite-tools-win32-x86-3390200.zip para a raiz da máquina

Renomeie a pasta para sqlite3, um nome mais curto e legível

Na pasta constarão três arquivos : sqldiff.exe, sqlite3.exe, sqlite3_analyzer.exe

Adicione o sqlite3 nas variáveis de ambiente

Rode o SQLITE via terminal >sqlite3

sqlite> Pronto, o SQLITE está iniciado e sua linha de comando disponibilizada

05.2. INSTALAÇÃO LINUX¶

No terminal digite a instrução a seguir para a instalação - sudo apt-get install sqlite

Rode o SQLITE via terminal >sqlite3

sqlite3> Pronto, o SQLITE está iniciado e sua linha de comando disponibilizada

06. Criação de uma base para testes (em ambos os SOs)¶

sqlite3 magna.db

06.1. Criação da primeira tabela para teste¶

CREATE TABLE mith(

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

projeto VARCHAR(70) NOT NULL,

responsável VARCHAR(100) NOT NULL,

esforço INTEGER,

gerente VARCHAR(50)

);

Após rodar, digite na linha de comando .table e seu retorno imediato deverá ser mith (nome da nossa tabela)

06.2. Inserir dados na tabela¶

INSERT INTO mith (projeto, responsavel, esforco, gerente) VALUES (“VOLT MX”, “Ary Almeida Júnior”, 40, “Marcelo Nappi”);

06.3. Consultar a tabela mith¶

sqlite> SELECT * FROM mith;

1|VOLT MX|Ary Almeida Júnior|40|Marcelo Nappi

sqlite>

Ou seja, via terminal podemos criar, inserir e consultar nossa primeira tabela no SQLite, e utilizar alguns dos principais comandos de SQL: CREATE TABLE, INSERT INTO e SELECT FROM. Dessa forma, damos início ao catálogo do MITH da Magna. Ainda temos a opção de utilizar um software de apoio visual ao SQLite chamado DB Browser ( https://sqlitebrowser.org/ ⧉) ou o de uso mais comum, DBEAVER ( https://dbeaver.io/ ⧉ ) que também oferece suporte ao banco.

07. Vamos aprofundar um pouco mais no proveito que podemos tirar do SQLITE¶

07.1. Dispositivos embarcados e a internet das coisas¶

Como um banco de dados SQLite não requer administração, ele funciona bem em dispositivos que precisam operar sem suporte humano especializado. SQLite é uma boa opção para uso em celulares, decodificadores, televisores, consoles de jogos, câmeras, relógios, eletrodomésticos de cozinha, termostatos, automóveis, máquinas-ferramentas, aviões, sensores remotos, drones, dispositivos médicos e robôs: a "internet das coisas".

Os mecanismos de banco de dados cliente/servidor são projetados para funcionar dentro de um datacenter cuidadosamente atendido no centro da rede. O SQLite também funciona lá, mas o SQLite também prospera na borda da rede, defendendo-se sozinho e ao mesmo tempo fornecendo serviços de dados rápidos e confiáveis para aplicativos que, de outra forma, teriam conectividade duvidosa.

07.2. Formato de arquivo do aplicativo¶

SQLite é frequentemente usado como formato de arquivo em disco para aplicativos de desktop, como sistemas de controle de versão, ferramentas de análise financeira, catalogação de mídia e suítes de edição, pacotes CAD, programas de manutenção de registros e assim por diante. A operação tradicional Arquivo/Abrir chama sqlite3_open() para anexar ao arquivo de banco de dados. As atualizações acontecem automaticamente à medida que o conteúdo do aplicativo é revisado, de modo que a opção de menu Arquivo/Salvar se torna supérflua. A opção de menu Arquivo/Salvar_Como pode ser implementada usando a API de backup .

Há muitos benefícios nessa abordagem, incluindo melhor desempenho, redução de custos e complexidade e maior confiabilidade. Consulte as notas técnicas "aff_short.html" e "appfileformat.html" e "fasterthanfs.html" para obter mais informações. Este caso de uso está intimamente relacionado ao formato de transferência de dados e aos casos de uso do contêiner de dados abaixo.

07.3. Sites¶

SQLite funciona muito bem como mecanismo de banco de dados para a maioria dos sites de tráfego baixo a médio (ou seja, a maioria dos sites). A quantidade de tráfego da web que o SQLite pode suportar depende da intensidade com que o site usa seu banco de dados. De modo geral, qualquer site que receba menos de 100 mil acessos/dia deve funcionar bem com SQLite. O número de 100 mil acessos/dia é uma estimativa conservadora, não um limite superior rígido. Foi demonstrado que o SQLite funciona com 10 vezes mais tráfego.

07.4. Análise de dados¶

Pessoas que entendem de SQL podem empregar o shell de linha de comando sqlite3 (ou vários programas de acesso SQLite de terceiros) para analisar grandes conjuntos de dados. Os dados brutos podem ser importados de arquivos CSV e, em seguida, esses dados podem ser divididos e divididos em cubos para gerar uma infinidade de relatórios resumidos. Análises mais complexas podem ser feitas usando scripts simples escritos em Tcl ou Python (ambos vêm com SQLite integrado) ou em R ou outras linguagens usando adaptadores prontamente disponíveis. Os usos possíveis incluem análise de log de sites, análise de estatísticas esportivas, compilação de métricas de programação e análise de resultados experimentais. Muitos pesquisadores de bioinformática usam o SQLite dessa forma.

A mesma coisa pode ser feita com um banco de dados cliente/servidor corporativo, é claro. A vantagem do SQLite é que ele é mais fácil de instalar e usar e o banco de dados resultante é um arquivo único que pode ser gravado em um cartão de memória USB ou enviado por e-mail para um colega.

07.5. Cache para dados corporativos¶

Muitos aplicativos usam SQLite como cache de conteúdo relevante de um RDBMS corporativo. Isso reduz a latência, já que a maioria das consultas agora ocorre no cache local e evita uma viagem de ida e volta na rede. Também reduz a carga na rede e no servidor de banco de dados central. E, em muitos casos, significa que o aplicativo do lado do cliente pode continuar operando durante interrupções na rede.

07.6. Banco de dados do lado do servidor¶

Os projetistas de sistemas relatam sucesso ao usar o SQLite como armazenamento de dados em aplicativos de servidor executados no datacenter ou, em outras palavras, ao usar o SQLite como mecanismo de armazenamento subjacente para um servidor de banco de dados específico de aplicativo.

Com esse padrão, o sistema geral ainda é cliente/servidor: os clientes enviam solicitações ao servidor e recebem respostas pela rede. Mas, em vez de enviar SQL genérico e recuperar o conteúdo bruto da tabela, as solicitações do cliente e as respostas do servidor são de alto nível e específicas do aplicativo. O servidor traduz as solicitações em múltiplas consultas SQL, reúne os resultados, faz pós-processamento, filtragem e análise e, em seguida, constrói uma resposta de alto nível contendo apenas as informações essenciais.

Os desenvolvedores relatam que o SQLite costuma ser mais rápido que um mecanismo de banco de dados SQL cliente/servidor neste cenário. As solicitações de banco de dados são serializadas pelo servidor, portanto a simultaneidade não é um problema. A simultaneidade também é melhorada pela "fragmentação de banco de dados": usando arquivos de banco de dados separados para diferentes subdomínios. Por exemplo, o servidor pode ter um banco de dados SQLite separado para cada usuário, de modo que o servidor possa lidar com centenas ou milhares de conexões simultâneas, mas cada banco de dados SQLite é usado apenas por uma conexão.

07.7. Formato de transferência de dados¶

Como um banco de dados SQLite é um único arquivo compacto em um formato multiplataforma bem definido , ele é frequentemente usado como um contêiner para transferência de conteúdo de um sistema para outro. O remetente reúne o conteúdo em um arquivo de banco de dados SQLite, transfere esse arquivo para o destinatário e, em seguida, o destinatário usa SQL para extrair o conteúdo conforme necessário.

Um banco de dados SQLite facilita a transferência de dados entre sistemas mesmo quando os endpoints têm diferentes tamanhos de palavras e/ou ordens de bytes. Os dados podem ser uma mistura complexa de grandes blobs binários, texto e pequenos valores numéricos ou booleanos. O formato dos dados pode ser facilmente estendido adicionando novas tabelas e/ou colunas, sem quebrar os receptores legados. A linguagem de consulta SQL significa que os receptores não são obrigados a analisar toda a transferência de uma só vez, mas podem consultar o conteúdo recebido conforme necessário. O formato dos dados é "transparente" no sentido de que é facilmente decodificado para visualização humana usando uma variedade de ferramentas de código aberto universalmente disponíveis, de vários fornecedores.

07.8. Arquivo de arquivos e/ou contêiner de dados¶

A ideia do SQLite Archive mostra como o SQLite pode ser usado como substituto de arquivos ZIP ou Tarballs. Um arquivo de arquivos armazenados no SQLite é apenas um pouco maior e, em alguns casos, até menor, do que o arquivo ZIP equivalente. E um arquivo SQLite apresenta atualização incremental e atômica e a capacidade de armazenar metadados muito mais ricos.

Fossil versão 2.5 e posterior oferece arquivos SQLite Archive como formato de download, além do tarball tradicional e arquivo ZIP. O shell de linha de comando sqlite3.exe versão 3.22.0 e posterior criará, listará ou descompactará um arquivamento SQL usando o comando .archive .

SQLite é uma boa solução para qualquer situação que exija o agrupamento de diversos conteúdos em um pacote independente e autodescritivo para envio através de uma rede. O conteúdo é codificado em um formato de arquivo estável, multiplataforma e bem definido . A codificação é eficiente e os receptores podem extrair pequenos subconjuntos do conteúdo sem precisar ler e analisar o arquivo inteiro.

Os arquivos SQL são úteis como formato de distribuição para atualizações de software ou conteúdo que são transmitidas para muitos clientes. Variações dessa ideia são usadas, por exemplo, para transmitir guias de programação de TV para decodificadores e para enviar atualizações pelo ar para sistemas de navegação de veículos.

07.9. Substituição de arquivos de disco ad hoc¶

Muitos programas usam fopen() , fread() e fwrite() para criar e gerenciar arquivos de dados em formatos caseiros. SQLite funciona particularmente bem como um substituto para esses arquivos de dados ad hoc . Ao contrário da intuição, o SQLite pode ser mais rápido que o sistema de arquivos para leitura e gravação de conteúdo em disco.

07.10. Bancos de dados internos ou temporários¶

Para programas que possuem muitos dados que devem ser peneirados e classificados de diversas maneiras, geralmente é mais fácil e rápido carregar os dados em um banco de dados SQLite na memória e usar consultas com junções e cláusulas ORDER BY para extrair os dados no forma e ordem necessárias, em vez de tentar codificar as mesmas operações manualmente. Usar um banco de dados SQL internamente dessa forma também dá ao programa maior flexibilidade, já que novas colunas e índices podem ser adicionados sem a necessidade de recodificar cada consulta.

07.11. Substituição de um banco de dados corporativo durante demonstrações ou testes¶

Os aplicativos cliente normalmente usam uma interface de banco de dados genérica que permite conexões com vários mecanismos de banco de dados SQL. Faz sentido incluir o SQLite na combinação de bancos de dados suportados e vincular estaticamente o mecanismo SQLite ao cliente. Dessa forma, o programa cliente pode ser usado de forma independente com um arquivo de dados SQLite para testes ou demonstrações.

08. SQLite e Json¶

Por padrão, SQLite suporta dezesseis funções e dois operadores para lidar com valores JSON. Existem também duas funções com valor de tabela que podem ser usadas para decompor uma string JSON.

Existem 15 funções e operadores escalares:

json ( json )

json_array ( valor1 , valor2 ,...)

json_array_length ( json )

json_array_length ( json , caminho )

json_error_position ( json )

json_extract ( json , caminho ,...)

json -> caminho

json ->> caminho

json_insert ( json , caminho , valor ,...)

json_object ( rótulo1 , valor1 ,...)

json_patch ( json 1, json2)

json_remove ( json , caminho ,...)

json_replace ( json , caminho , valor ,...)

json_set ( json , caminho , valor ,...)

json_type ( json )

json_type ( json , caminho )

json_válido ( json )

json_quote ( valor )

Existem duas funções SQL agregadas :

json_group_array ( valor )

json_group_object ( rótulo , valor )

As duas funções com valor de tabela são:

json_each ( json ) json_each ( json , caminho ) json_tree ( json ) json_tree ( json , caminho )

09. Exemplos Práticos¶

Seguem exemplos simplificados de algumas das funções e operadores escalares do SQLite relacionadas ao JSON:

json_array: Cria um array JSON. Exemplo: json_array(1, 2, 3)

json_array_length: Retorna o comprimento de um array JSON. Exemplo: json_array_length('[1, 2, 3]')

json_extract: Extrai um valor de um caminho específico em um JSON. Exemplo: json_extract('{"nome": "Ary", "idade": 51}', '$.nome')

json_insert: Insere um valor em um caminho específico em um JSON. Exemplo: json_insert('{"nome": "Ary"}', '$.idade', 51)

json_remove: Remove um valor de um caminho específico em um JSON. Exemplo: json_remove('{"nome": "Ary", "idade": 51}', '$.idade')

json_quote: Coloca aspas duplas em volta de um valor. Exemplo: json_quote('exemplo')

json_type: Retorna o tipo de um valor JSON. Exemplo: json_type('{"nome": "Ary"}', '$.nome')

json_group_array: Agrega valores em um array. Exemplo: json_group_array(nome)

json_group_object: Agrega valores em um objeto JSON. Exemplo: json_group_object(id, nome)

Esses exemplos demonstram como essas funções podem ser aplicadas em um contexto simplificado.

Para maiores informações consulte a documentação do Produto em https://www.sqlite.org/docs.html ⧉