SQLITE 3 – Guia rápido

há 2 meses 49

Saudações.

Vou ensinar como usar o banco de dados em arquivo chamado SQLITE3.

Pré-requisitos (constam em outros artigos aqui do blog):

Instalação do Linux (Debian);
Internet no servidor para instalar os programas;

Links úteis:

Syntax diagrams: https://www.sqlite.org/syntaxdiagrams.html
Site oficial: https://www.sqlite.org
Documentação: https://www.sqlite.org/docs.html
Download: https://www.sqlite.org/download.html

1 – Sobre o SQLITE

O SQLITE é, em uma definição simples, um banco de dados SQL de arquivo único embutido (embedded), auto-contido, sem servidor (serverless) e de configuração zero.

Diferente do PostgreSQL ou MySQL, o SQLite não requer um processo de servidor separado – ele lê e escreve diretamente em arquivos de disco.

Crie o arquivo “.db” ou “.sqlite” com pelo menos 1 tabela e você ja pode fazer uso imediato e armazenar dados.

Toda a política de banco de dados, tabelas, relações e execução é implementado pela biblioteca SQLITE executada pelo software cliente. Esse desafio impõe na libsqlite um peso grande, exigindo dela perfeição e algoritmos de última geração.

Ao alcançar estabilidade máxima ele se tornou o banco de dados preferido por milhões de aplicativos para smartphone (Whatsapp por exemplo) e por softwares modernos (padrão do N8N).

Principais características:

Serverless: Não requer instalação ou configuração de servidor;
Self-contained: Uma única biblioteca C sem dependências externas;
Zero-configuration: Não precisa de setup ou administração;
Cross-platform: Funciona em qualquer sistema operacional;
Transacional: Suporte completo a ACID (Atomicidade, Consistência, Isolamento, Durabilidade);
Compacto: Biblioteca com menos de 1MB.
Segurança: ele NÃO possui sistema de usuários ou permissões interna, a segurança é gerenciada pelo sistema de arquivos do sistema operacional.

Instalando no Linux:

Bash

apt-get -y install sqlite3;

2 – Primeiro contato

Vamos criar um banco (arquivo):

Bash

# "Conectar" ao banco via terminal, se o arquivo não existir ele será criado: sqlite3 /tmp/meubanco.db;

Ao executar o comando sqlite3, você estará no shell do SQLITE (linha de comando).

Use o comando “.quit” ou “.exit” para sair:

SQLITE3 Shell

# Sair do shell .quit

O arquivo será criado vazio (zero bytes). Outra forma é entrar no comando sqlite3 sem especificar o arquivo do banco e carregá-lo pelo shell SQLITE:

Bash

# Entrar no shell sem informar o arquivo do banco: sqlite3

SQLITE3 Shell

-- Carregar arquivo, abrir/criar banco de dados .open /tmp/meubanco.db -- Exibir banco atual .databases -- main: /tmp/meubanco.db r/w -- Verificar integridade do banco PRAGMA integrity_check; -- ok -- Verificação rápida PRAGMA quick_check; -- ok -- Salvar banco atual no mesmo arquivo .save /tmp/meubanco.db -- Salvar banco atual em arquivo versionado (backup manual) .save /tmp/meubanco-v01.db .exit

3 – Comandos rápidos do SQLITE3

Quando estiver no shell do SQLITE, use o “.help” para pedir ajuda, guia completo de referência dos comandos de ponto:

SQLITE3 Shell

-- COMANDOS DE PONTO (dot commands) - específicos do shell sqlite3 .help -- Ajuda geral dos comandos .help COMMAND -- Ajuda para comando específico .quit -- Sair do SQLite (ou .exit) .exit -- Sair do SQLite (ou .quit) .databases -- Listar bancos de dados conectados .tables -- Listar tabelas do banco atual .tables pattern -- Listar tabelas que correspondem ao padrão .schema -- Mostrar estrutura de todas as tabelas .schema tabela -- Mostrar estrutura de uma tabela específica .fullschema -- Schema completo incluindo estatísticas .indices -- Listar todos os índices .indices tabela -- Listar índices de uma tabela específica .headers on -- Mostrar cabeçalhos nas consultas .headers off -- Ocultar cabeçalhos nas consultas .mode column -- Modo de exibição em colunas alinhadas .mode table -- Modo de exibição em tabela formatada .mode box -- Modo de exibição em caixa (bordas) .mode list -- Modo de exibição em lista (separado por |) .mode csv -- Modo de exibição CSV .mode json -- Modo de exibição JSON .mode line -- Modo de exibição uma coluna por linha .mode markdown -- Modo de exibição markdown .mode html -- Modo de exibição HTML .width N1 N2 ... -- Definir largura das colunas .nullvalue 'NULL' -- Como exibir valores NULL .separator ',' -- Definir separador para modo list/csv .timer on -- Mostrar tempo de execução das queries .timer off -- Ocultar tempo de execução .open arquivo.db -- Abrir/criar banco de dados .save arquivo.db -- Salvar banco atual em arquivo .backup arquivo.db -- Fazer backup do banco atual .restore arquivo.db -- Restaurar banco de backup .read script.sql -- Executar comandos de um arquivo SQL .output arquivo.txt -- Redirecionar saída para arquivo .output stdout -- Voltar saída para tela .dump -- Exportar banco inteiro como SQL .dump tabela -- Exportar tabela específica como SQL .import arquivo.csv tabela -- Importar CSV para tabela .excel -- Abrir resultado no Excel (se disponível) .show -- Mostrar configurações atuais .stats on -- Mostrar estatísticas de execução .stats off -- Ocultar estatísticas .shell COMMAND -- Executar comando do sistema .system COMMAND -- Executar comando do sistema (alias) ! COMMAND -- Executar comando do sistema (atalho) .changes on -- Mostrar número de linhas alteradas .changes off -- Ocultar número de linhas alteradas .eqp on -- Mostrar plano de execução das queries .eqp off -- Ocultar plano de execução .explain on -- Modo de exibição para EXPLAIN .explain off -- Voltar ao modo normal .print texto -- Imprimir texto na saída .once arquivo -- Próximo resultado vai para arquivo .log arquivo -- Registrar todas as operações em arquivo .log off -- Parar de registrar -- PRAGMA - Comandos de configuração e informação PRAGMA database_list; -- Listar bancos de dados anexados PRAGMA table_info(tabela); -- Informações das colunas de uma tabela PRAGMA table_xinfo(tabela); -- Informações estendidas das colunas PRAGMA index_list(tabela); -- Lista de índices de uma tabela PRAGMA index_info(indice); -- Informações de um índice PRAGMA foreign_key_list(t); -- Lista de chaves estrangeiras PRAGMA foreign_keys; -- Verificar se FK está habilitada PRAGMA foreign_keys = ON; -- Habilitar verificação de FK PRAGMA foreign_keys = OFF; -- Desabilitar verificação de FK PRAGMA journal_mode; -- Ver modo de journal atual PRAGMA journal_mode=WAL; -- Usar Write-Ahead Logging (recomendado) PRAGMA journal_mode=DELETE; -- Modo padrão (delete journal após commit) PRAGMA synchronous; -- Ver modo de sincronização PRAGMA synchronous=NORMAL; -- Modo normal (equilíbrio) PRAGMA synchronous=FULL; -- Modo seguro (mais lento) PRAGMA synchronous=OFF; -- Modo rápido (risco de corrupção) PRAGMA cache_size; -- Ver tamanho do cache PRAGMA cache_size=10000; -- Definir cache (páginas, negativo=KB) PRAGMA page_size; -- Ver tamanho da página PRAGMA page_count; -- Número de páginas no banco PRAGMA freelist_count; -- Páginas livres (não usadas) PRAGMA integrity_check; -- Verificar integridade do banco PRAGMA quick_check; -- Verificação rápida de integridade PRAGMA optimize; -- Otimizar banco de dados VACUUM; -- Reconstruir banco (compactar) PRAGMA busy_timeout=5000; -- Timeout para banco bloqueado (ms) PRAGMA encoding; -- Ver encoding do banco (UTF-8, etc.) -- TRANSAÇÕES BEGIN; -- Iniciar transação BEGIN TRANSACTION; -- Iniciar transação (explícito) BEGIN IMMEDIATE; -- Iniciar com lock imediato BEGIN EXCLUSIVE; -- Iniciar com lock exclusivo COMMIT; -- Confirmar transação ROLLBACK; -- Desfazer transação SAVEPOINT nome; -- Criar ponto de salvamento RELEASE nome; -- Liberar savepoint ROLLBACK TO nome; -- Voltar ao savepoint -- ATTACH/DETACH - Múltiplos bancos ATTACH 'outro.db' AS outro; -- Anexar outro banco de dados DETACH outro; -- Desanexar banco de dados

4 – Usando SQLITE em scripts shell/bash

Essa é a maneira mais usada para inicializar bancos SQLITE, observe:

Bash

# Criar diretório para bancos mkdir -p /var/lib/sqlite; # Criar banco de dados do sistema sqlite3 /var/lib/sqlite/sistema.db << 'EOF' .headers on .mode column CREATE TABLE IF NOT EXISTS config ( chave TEXT PRIMARY KEY, valor TEXT ); INSERT INTO config VALUES ('versao', '1.0.0'); INSERT INTO config VALUES ('nome', 'MeuSistema'); SELECT * FROM config; EOF # Ou no meu formato: # - arquivo com esquema de tabelas ( echo '.headers on'; echo '.mode column'; echo 'CREATE TABLE IF NOT EXISTS config ('; echo ' chave TEXT PRIMARY KEY,'; echo ' valor TEXT'; echo ');'; ) > /var/lib/sqlite/001-schema.sql; # - arquivo com dados iniciais ( echo "INSERT INTO config VALUES ('versao', '1.0.0');"; echo "INSERT INTO config VALUES ('nome', 'MeuSistema');"; ) > /var/lib/sqlite/010-startup-config.sql; # Aplicar: sqlite3 /var/lib/sqlite/sistema.db < /var/lib/sqlite/001-schema.sql; sqlite3 /var/lib/sqlite/sistema.db < /var/lib/sqlite/010-startup-config.sql; # Conferindo: sqlite3 /var/lib/sqlite/sistema.db "SELECT * FROM config;"; # Verificar arquivo criado ls -lah /var/lib/sqlite/sistema.db; # -rw-r--r-- 1 root root 12K Dec 20 11:04 /var/lib/sqlite/sistema.db # Conferir o esquema aplicado e as tabelas sqlite3 /var/lib/sqlite/sistema.db ".schema"; # CREATE TABLE config ( # chave TEXT PRIMARY KEY, # valor TEXT # ); sqlite3 /var/lib/sqlite/sistema.db ".tables"; # config

Exemplos de execução em linha de comando:

Bash

# Criar banco e executar comando único sqlite3 teste.db "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);"; # Inserir dados via comando sqlite3 teste.db "INSERT INTO users (name) VALUES ('João');"; sqlite3 teste.db "INSERT INTO users (name) VALUES ('Maria');"; # Consultar dados sqlite3 teste.db "SELECT * FROM users;"; # Executar com formatação sqlite3 -header -column teste.db "SELECT * FROM users;"; # Saída em CSV sqlite3 -header -csv teste.db "SELECT * FROM users;" > users.csv; # Saída em JSON sqlite3 -json teste.db "SELECT * FROM users;"; # Executar arquivo SQL cat > script.sql << 'EOF' .headers on .mode box SELECT * FROM users; SELECT COUNT(*) AS total FROM users; EOF sqlite3 teste.db < script.sql; # Executar múltiplos comandos sqlite3 teste.db << 'EOF' .headers on .mode column SELECT * FROM users; INSERT INTO users (name) VALUES ('Pedro'); SELECT * FROM users; EOF # Dump do banco sqlite3 teste.db .dump > backup.sql; # Restore do dump sqlite3 novo_banco.db < backup.sql; # Verificar integridade sqlite3 teste.db "PRAGMA integrity_check;"; # Ver estrutura sqlite3 teste.db ".schema"; # Modo interativo com configurações sqlite3 -header -column -nullvalue NULL teste.db; # One-liner para estatísticas sqlite3 teste.db "SELECT COUNT(*) as total, MAX(id) as ultimo_id FROM users;"; # Limpar arquivos de teste rm -f teste.db novo_banco.db script.sql backup.sql users.csv;

5 – Configuração de comportamento

O SQLITE possui vários parâmetros para flexibilizar o uso, permitir acesso simultâneo de escrita e leitura (WAL):

Bash

# Pasta para salvar arquivos mkdir -p /var/lib/sqlite; # Entrar no sqlite3 sqlite3;

Dentro do sqlite3:

SQLITE3 Shell

-- Configurar modo de exibição .headers on .mode box -- Criar banco de aplicação .open /var/lib/sqlite/certificados.db; -- Verificar informações do banco PRAGMA database_list; PRAGMA page_size; PRAGMA page_count; PRAGMA encoding; -- Otimizar para performance PRAGMA journal_mode = WAL; -- Write-Ahead Logging PRAGMA synchronous = NORMAL; -- Equilíbrio segurança/velocidade PRAGMA cache_size = -64000; -- 64MB de cache PRAGMA temp_store = MEMORY; -- Temporários em RAM PRAGMA mmap_size = 268435456; -- 256MB memory-mapped I/O -- Verificar configurações PRAGMA journal_mode; PRAGMA synchronous; PRAGMA cache_size; -- Salvar explicitamente .save /var/lib/sqlite/certificados.db; .quit

5 – Tipos de colunas

Os tipos de colunas no SQLITE são:

AfinidadeTipos aceitosDescrição

INTEGER	INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, INT2, INT8	Números inteiros
REAL	REAL, DOUBLE, FLOAT, DOUBLE PRECISION	Ponto flutuante
TEXT	TEXT, VARCHAR, CHAR, CLOB, CHARACTER	Strings de texto
BLOB	BLOB, nenhum tipo especificado	Dados binários
NUMERIC	NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME	Numérico genérico
DATE/TIME	O SQLite NÃO possui tipo DATE/TIME nativo, datas são armazenadas como TEXT, INTEGER ou REAL	Não

Praticando tipos:

SQLITE3 Shell

-- Criar banco .open /var/lib/sqlite/tipos.db; -- Tabela para testes de tipos -- ============================================ -- O SQLite aceita QUALQUER dado em QUALQUER coluna (exceto INTEGER PRIMARY KEY) CREATE TABLE IF NOT EXISTS teste_de_tipos ( id INTEGER PRIMARY KEY, coluna_int INTEGER, coluna_text TEXT, coluna_real REAL ); -- Inserir dados de tipos errados - SQLite aceita: INSERT INTO teste_de_tipos VALUES (1, 'texto', 123, 'abc'); INSERT INTO teste_de_tipos VALUES (2, 3.14, NULL, 999); -- Ver dados SELECT * FROM teste_de_tipos; -- Ver tipos reais armazenados SELECT id, typeof(coluna_int) AS tipo_int, typeof(coluna_text) AS tipo_text, typeof(coluna_real) AS tipo_real FROM teste_de_tipos; -- Tabela de teste de numeros -- ============================================ CREATE TABLE teste_integer ( id INTEGER PRIMARY KEY, pequeno INTEGER, -- Qualquer inteiro contador INTEGER DEFAULT 0, booleano INTEGER CHECK (booleano IN (0, 1)) ); -- Inserir valores INSERT INTO teste_integer (pequeno, contador, booleano) VALUES (42, 100, 1); INSERT INTO teste_integer (pequeno, contador, booleano) VALUES (-999, 0, 0); INSERT INTO teste_integer (pequeno, contador, booleano) VALUES (9223372036854775807, 1, 1); -- Visualizar SELECT * FROM teste_integer; -- Operações matemáticas SELECT id, pequeno, pequeno * 2 AS dobro, pequeno + 100 AS mais_cem, ABS(pequeno) AS absoluto FROM teste_integer; -- Tabela de teste de numeros reais -- ============================================ CREATE TABLE teste_real ( id INTEGER PRIMARY KEY, preco REAL, taxa REAL DEFAULT 0.0, coordenada_lat REAL, coordenada_lng REAL ); -- Inserir valores decimais INSERT INTO teste_real (preco, taxa, coordenada_lat, coordenada_lng) VALUES (99.99, 0.15, -23.5505199, -46.6333094); INSERT INTO teste_real (preco, taxa, coordenada_lat, coordenada_lng) VALUES (1234.56789, 0.0825, -22.9068467, -43.1728965); -- Consultar com formatação SELECT id, printf('%.2f', preco) AS preco, printf('%.4f%%', taxa * 100) AS taxa_percentual, printf('%.8f', coordenada_lat) AS latitude, printf('%.8f', coordenada_lng) AS longitude FROM teste_real; -- Cálculos com REAL SELECT preco, taxa, printf('%.2f', preco * (1 + taxa)) AS preco_com_taxa, ROUND(preco, 1) AS preco_arredondado FROM teste_real; -- Tabela de teste de texto -- ============================================ CREATE TABLE teste_text ( id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT, email TEXT UNIQUE, json_data TEXT, -- Armazenar JSON como texto codigo TEXT CHECK (LENGTH(codigo) <= 10) ); -- Inserir textos INSERT INTO teste_text (nome, descricao, email, codigo) VALUES ('Produto A', 'Descrição longa do produto...', 'a@email.com', 'ABC123'); INSERT INTO teste_text (nome, email, json_data) VALUES ('Produto B', 'b@email.com', '{"cor": "azul", "tamanho": "M"}'); -- Consultas com funções de texto SELECT nome, LENGTH(nome) AS tamanho_nome, UPPER(nome) AS nome_maiusculo, LOWER(nome) AS nome_minusculo, SUBSTR(nome, 1, 5) AS primeiros_5 FROM teste_text; -- Busca com LIKE SELECT * FROM teste_text WHERE nome LIKE 'Produto%'; SELECT * FROM teste_text WHERE email LIKE '%@email.com'; -- Busca case-insensitive SELECT * FROM teste_text WHERE nome LIKE '%produto%' COLLATE NOCASE; -- Concatenação SELECT nome || ' - ' || COALESCE(descricao, 'Sem descrição') AS nome_completo FROM teste_text; -- Extrair JSON (SQLite 3.38+) SELECT nome, json_data, json_extract(json_data, '$.cor') AS cor, json_extract(json_data, '$.tamanho') AS tamanho FROM teste_text WHERE json_data IS NOT NULL; -- Tabela de teste de blob/binario -- ============================================ CREATE TABLE teste_blob ( id INTEGER PRIMARY KEY, nome TEXT NOT NULL, arquivo BLOB, hash_md5 BLOB ); -- Inserir dados binários (hexadecimal) INSERT INTO teste_blob (nome, arquivo, hash_md5) VALUES ('imagem.png', X'89504E470D0A1A0A', X'D41D8CD98F00B204E9800998ECF8427E'); -- Inserir via zeroblob (blob de zeros) INSERT INTO teste_blob (nome, arquivo) VALUES ('placeholder', zeroblob(1024)); -- 1KB de zeros -- Consultar SELECT id, nome, LENGTH(arquivo) AS tamanho_bytes, HEX(arquivo) AS arquivo_hex, HEX(hash_md5) AS hash_hex FROM teste_blob; -- Verificar tipo SELECT nome, typeof(arquivo) AS tipo FROM teste_blob; DROP TABLE teste_blob; -- Tabela basica de controle de data/hora -- ============================================ CREATE TABLE teste_datetime ( id INTEGER PRIMARY KEY, evento TEXT NOT NULL, -- Diferentes formas de armazenar data/hora data_texto TEXT, -- ISO8601: '2024-01-15 14:30:00' data_unix INTEGER, -- Unix timestamp: 1705329000 data_julian REAL -- Dia Juliano: 2460325.10417 ); -- Inserir datas INSERT INTO teste_datetime (evento, data_texto, data_unix, data_julian) VALUES ('Evento 1', datetime('now'), strftime('%s', 'now'), julianday('now')); INSERT INTO teste_datetime (evento, data_texto, data_unix, data_julian) VALUES ('Evento 2', '2024-06-15 10:00:00', 1718445600, 2460476.91667); INSERT INTO teste_datetime (evento, data_texto) VALUES ('Evento 3', datetime('now', '+7 days')); -- Consultar e formatar datas SELECT evento, data_texto, date(data_texto) AS apenas_data, time(data_texto) AS apenas_hora, strftime('%d/%m/%Y', data_texto) AS data_br, strftime('%H:%M', data_texto) AS hora_curta FROM teste_datetime; -- Converter Unix timestamp SELECT evento, data_unix, datetime(data_unix, 'unixepoch') AS data_legivel, datetime(data_unix, 'unixepoch', 'localtime') AS data_local FROM teste_datetime; -- Funções de data úteis SELECT datetime('now') AS agora, date('now') AS hoje, time('now') AS hora_atual, datetime('now', 'localtime') AS agora_local, datetime('now', '+1 day') AS amanha, datetime('now', '-1 month') AS mes_passado, datetime('now', 'start of month') AS inicio_mes, datetime('now', 'start of year') AS inicio_ano, strftime('%W', 'now') AS semana_ano, strftime('%j', 'now') AS dia_ano; -- Calcular diferença entre datas SELECT evento, data_texto, CAST((julianday('now') - julianday(data_texto)) AS INTEGER) AS dias_passados, CAST((julianday('now') - julianday(data_texto)) * 24 AS INTEGER) AS horas_passadas FROM teste_datetime; DROP TABLE teste_datetime; -- Tabela basica de controle de usuarios -- ============================================ CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, -- Auto incremento name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, status INTEGER DEFAULT 1, -- Boolean como INTEGER created_at TEXT DEFAULT CURRENT_TIMESTAMP ); -- Ver estrutura .schema users PRAGMA table_info(users); -- Inserir dados INSERT INTO users (name, email) VALUES ('João', 'joao@email.com'); INSERT INTO users (name, email) VALUES ('Maria', 'maria@email.com'); INSERT INTO users (name, email, status) VALUES ('Pedro', 'pedro@email.com', 0); -- Consultar SELECT * FROM users; -- Salvar explicitamente .save /var/lib/sqlite/tipos.db; .quit

6 – Índices

Índices ajudam a localizar registros mais rápidos.

SQLITE3 Shell

.open /var/lib/sqlite/indices.db .headers on .mode box -- Mostrar tempo de execução .timer on -- Criar tabela de exemplo com muitos dados -- ============================================ CREATE TABLE IF NOT EXISTS clientes ( id INTEGER PRIMARY KEY, cpf TEXT UNIQUE NOT NULL, nome TEXT NOT NULL, email TEXT, cidade TEXT, estado TEXT, ativo INTEGER DEFAULT 1, criado_em TEXT DEFAULT CURRENT_TIMESTAMP ); -- Inserir dados de teste (10.000 registros simulados) WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt WHERE x < 10000 ) INSERT INTO clientes (cpf, nome, email, cidade, estado) SELECT printf('%011d', x) AS cpf, 'Cliente ' || x AS nome, 'cliente' || x || '@email.com' AS email, CASE (x % 5) WHEN 0 THEN 'São Paulo' WHEN 1 THEN 'Rio de Janeiro' WHEN 2 THEN 'Belo Horizonte' WHEN 3 THEN 'Curitiba' ELSE 'Porto Alegre' END AS cidade, CASE (x % 5) WHEN 0 THEN 'SP' WHEN 1 THEN 'RJ' WHEN 2 THEN 'MG' WHEN 3 THEN 'PR' ELSE 'RS' END AS estado FROM cnt; -- Verificar quantidade SELECT COUNT(*) AS total FROM clientes; -- ============================================ -- CONSULTA SEM ÍNDICE -- ============================================ -- Ver plano de execução (sem índice em cidade) EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE cidade = 'São Paulo'; -- SCAN clientes (busca completa na tabela) -- Executar consulta (note o tempo) SELECT COUNT(*) FROM clientes WHERE cidade = 'São Paulo'; -- ============================================ -- CRIAR ÍNDICES -- ============================================ -- Índice simples em uma coluna CREATE INDEX idx_clientes_cidade ON clientes(cidade); -- Ver plano de execução (com índice) EXPLAIN QUERY PLAN SELECT * FROM clientes WHERE cidade = 'São Paulo'; -- SEARCH clientes USING INDEX idx_clientes_cidade (usa índice) -- Executar mesma consulta (compare o tempo) SELECT COUNT(*) FROM clientes WHERE cidade = 'São Paulo'; -- Índice composto (múltiplas colunas) CREATE INDEX idx_clientes_estado_cidade ON clientes(estado, cidade); -- Índice com ordenação CREATE INDEX idx_clientes_nome ON clientes(nome COLLATE NOCASE); -- Índice parcial (apenas registros ativos) CREATE INDEX idx_clientes_ativos ON clientes(email) WHERE ativo = 1; -- Índice único (já existe no cpf por UNIQUE) -- CREATE UNIQUE INDEX idx_clientes_cpf ON clientes(cpf); -- ============================================ -- GERENCIAR ÍNDICES -- ============================================ -- Listar todos os índices .indices clientes -- Ver estrutura de índice específico PRAGMA index_info(idx_clientes_cidade); -- Ver lista de índices com mais detalhes PRAGMA index_list(clientes); -- Ver SQL de criação do índice SELECT sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 'clientes'; -- Analisar estatísticas (otimiza planos de execução) ANALYZE clientes; -- Ver estatísticas SELECT * FROM sqlite_stat1 WHERE tbl = 'clientes'; -- ============================================ -- REMOVER ÍNDICES -- ============================================ -- Remover índice DROP INDEX IF EXISTS idx_clientes_cidade; DROP INDEX IF EXISTS idx_clientes_estado_cidade; DROP INDEX IF EXISTS idx_clientes_nome; DROP INDEX IF EXISTS idx_clientes_ativos; -- Reindexar tabela REINDEX clientes; -- Destruir tabela DROP TABLE clientes; -- Forcar salvamento .save /var/lib/sqlite/indices.db

7 – Views

Views são tabelas virtuais construídas em tempo de execução para produzir registros em um formato especifico, extraindo dados de outras tabelas:

SQLITE3 Shell

.open /var/lib/sqlite/views.db .headers on .mode box -- Criar tabelas base CREATE TABLE funcionarios ( id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cargo TEXT, departamento TEXT, salario REAL, data_admissao TEXT, ativo INTEGER DEFAULT 1 ); CREATE TABLE departamentos ( id INTEGER PRIMARY KEY, nome TEXT UNIQUE NOT NULL, orcamento REAL ); -- Inserir dados INSERT INTO departamentos VALUES (1, 'TI', 500000); INSERT INTO departamentos VALUES (2, 'RH', 200000); INSERT INTO departamentos VALUES (3, 'Vendas', 300000); INSERT INTO funcionarios VALUES (1, 'João Silva', 'Desenvolvedor', 'TI', 8000, '2020-01-15', 1); INSERT INTO funcionarios VALUES (2, 'Maria Santos', 'Gerente', 'RH', 12000, '2018-06-01', 1); INSERT INTO funcionarios VALUES (3, 'Pedro Costa', 'Vendedor', 'Vendas', 5000, '2021-03-10', 1); INSERT INTO funcionarios VALUES (4, 'Ana Oliveira', 'Analista', 'TI', 6500, '2022-08-20', 1); INSERT INTO funcionarios VALUES (5, 'Carlos Lima', 'Estagiário', 'TI', 2000, '2023-01-05', 0); -- ============================================ -- CRIAR VIEWS -- ============================================ -- View simples - funcionários ativos CREATE VIEW vw_funcionarios_ativos AS SELECT id, nome, cargo, departamento, salario FROM funcionarios WHERE ativo = 1; -- Usar a view como tabela SELECT * FROM vw_funcionarios_ativos; -- View com cálculos CREATE VIEW vw_folha_pagamento AS SELECT f.nome, f.cargo, f.departamento, f.salario, f.salario * 0.11 AS inss, f.salario * 0.275 AS irrf_estimado, f.salario - (f.salario * 0.11) AS salario_liquido_est FROM funcionarios f WHERE f.ativo = 1; SELECT * FROM vw_folha_pagamento; -- View com agregação - resumo por departamento CREATE VIEW vw_resumo_departamento AS SELECT departamento, COUNT(*) AS total_funcionarios, printf('R$ %.2f', SUM(salario)) AS total_salarios, printf('R$ %.2f', AVG(salario)) AS media_salarios, printf('R$ %.2f', MIN(salario)) AS menor_salario, printf('R$ %.2f', MAX(salario)) AS maior_salario FROM funcionarios WHERE ativo = 1 GROUP BY departamento; SELECT * FROM vw_resumo_departamento; -- View com JOIN CREATE VIEW vw_funcionarios_completo AS SELECT f.id, f.nome, f.cargo, f.departamento, d.orcamento AS orcamento_depto, printf('R$ %.2f', f.salario) AS salario, date(f.data_admissao) AS admissao, CAST((julianday('now') - julianday(f.data_admissao)) / 365 AS INTEGER) AS anos_empresa, CASE f.ativo WHEN 1 THEN 'Ativo' ELSE 'Inativo' END AS status FROM funcionarios f LEFT JOIN departamentos d ON f.departamento = d.nome; SELECT * FROM vw_funcionarios_completo; -- ============================================ -- GERENCIAR VIEWS -- ============================================ -- Listar views SELECT name, sql FROM sqlite_master WHERE type = 'view'; -- Ver definição de view específica SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'vw_funcionarios_ativos'; -- Recriar view (alterar) DROP VIEW IF EXISTS vw_funcionarios_ativos; CREATE VIEW vw_funcionarios_ativos AS SELECT id, nome, cargo, departamento, printf('R$ %.2f', salario) AS salario FROM funcionarios WHERE ativo = 1 ORDER BY nome; -- Remover view DROP VIEW IF EXISTS vw_folha_pagamento; -- ============================================ -- VIEWS TEMPORÁRIAS -- ============================================ -- View temporária (existe apenas na sessão) CREATE TEMP VIEW vw_temp_ti AS SELECT * FROM funcionarios WHERE departamento = 'TI'; SELECT * FROM vw_temp_ti; -- Limpar DROP VIEW IF EXISTS vw_funcionarios_ativos; DROP VIEW IF EXISTS vw_resumo_departamento; DROP VIEW IF EXISTS vw_funcionarios_completo; DROP TABLE funcionarios; DROP TABLE departamentos;

7 – Triggers

Triggers executam código automaticamente em resposta a eventos (INSERT, UPDATE, DELETE):

SQLITE3 Shell

.open /var/lib/sqlite/triggers.db .headers on .mode box -- Criar tabelas CREATE TABLE produtos ( id INTEGER PRIMARY KEY, nome TEXT NOT NULL, preco REAL NOT NULL, estoque INTEGER DEFAULT 0, atualizado_em TEXT ); CREATE TABLE produtos_log ( id INTEGER PRIMARY KEY, produto_id INTEGER, acao TEXT, dados_antigos TEXT, dados_novos TEXT, usuario TEXT DEFAULT 'sistema', data_hora TEXT DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE estoque_alertas ( id INTEGER PRIMARY KEY, produto_id INTEGER, produto_nome TEXT, estoque_atual INTEGER, mensagem TEXT, data_hora TEXT DEFAULT CURRENT_TIMESTAMP ); -- ============================================ -- TRIGGERS DE INSERT -- ============================================ -- Trigger AFTER INSERT - registrar log CREATE TRIGGER trg_produtos_insert_log AFTER INSERT ON produtos BEGIN INSERT INTO produtos_log (produto_id, acao, dados_novos) VALUES ( NEW.id, 'INSERT', json_object('nome', NEW.nome, 'preco', NEW.preco, 'estoque', NEW.estoque) ); END; -- Trigger AFTER INSERT - alerta de estoque baixo CREATE TRIGGER trg_produtos_estoque_baixo AFTER INSERT ON produtos WHEN NEW.estoque < 10 BEGIN INSERT INTO estoque_alertas (produto_id, produto_nome, estoque_atual, mensagem) VALUES (NEW.id, NEW.nome, NEW.estoque, 'Estoque baixo ao cadastrar produto!'); END; -- Testar INSERT INSERT INTO produtos (nome, preco, estoque) VALUES ('Notebook', 4500.00, 5); INSERT INTO produtos (nome, preco, estoque) VALUES ('Mouse', 89.90, 100); SELECT * FROM produtos; SELECT * FROM produtos_log; SELECT * FROM estoque_alertas; -- ============================================ -- TRIGGERS DE UPDATE -- ============================================ -- Trigger BEFORE UPDATE - atualizar timestamp CREATE TRIGGER trg_produtos_update_timestamp BEFORE UPDATE ON produtos BEGIN UPDATE produtos SET atualizado_em = datetime('now') WHERE id = NEW.id; END; -- Trigger AFTER UPDATE - registrar log CREATE TRIGGER trg_produtos_update_log AFTER UPDATE ON produtos BEGIN INSERT INTO produtos_log (produto_id, acao, dados_antigos, dados_novos) VALUES ( NEW.id, 'UPDATE', json_object('nome', OLD.nome, 'preco', OLD.preco, 'estoque', OLD.estoque), json_object('nome', NEW.nome, 'preco', NEW.preco, 'estoque', NEW.estoque) ); END; -- Trigger AFTER UPDATE - alerta quando estoque diminui CREATE TRIGGER trg_produtos_estoque_update AFTER UPDATE OF estoque ON produtos WHEN NEW.estoque < 10 AND OLD.estoque >= 10 BEGIN INSERT INTO estoque_alertas (produto_id, produto_nome, estoque_atual, mensagem) VALUES (NEW.id, NEW.nome, NEW.estoque, 'Estoque ficou abaixo do mínimo!'); END; -- Testar UPDATE UPDATE produtos SET preco = 4299.00 WHERE id = 1; UPDATE produtos SET estoque = 8 WHERE id = 2; SELECT * FROM produtos; SELECT * FROM produtos_log; SELECT * FROM estoque_alertas; -- ============================================ -- TRIGGERS DE DELETE -- ============================================ -- Trigger BEFORE DELETE - registrar log CREATE TRIGGER trg_produtos_delete_log BEFORE DELETE ON produtos BEGIN INSERT INTO produtos_log (produto_id, acao, dados_antigos) VALUES ( OLD.id, 'DELETE', json_object('nome', OLD.nome, 'preco', OLD.preco, 'estoque', OLD.estoque) ); END; -- Testar DELETE DELETE FROM produtos WHERE id = 1; SELECT * FROM produtos; SELECT * FROM produtos_log; -- ============================================ -- TRIGGER INSTEAD OF (para VIEWS) -- ============================================ -- Criar view CREATE VIEW vw_produtos_ativos AS SELECT id, nome, preco, estoque FROM produtos WHERE estoque > 0; -- Trigger para permitir INSERT via VIEW CREATE TRIGGER trg_vw_produtos_insert INSTEAD OF INSERT ON vw_produtos_ativos BEGIN INSERT INTO produtos (nome, preco, estoque) VALUES (NEW.nome, NEW.preco, NEW.estoque); END; -- Inserir via view INSERT INTO vw_produtos_ativos (nome, preco, estoque) VALUES ('Teclado', 199.90, 50); SELECT * FROM produtos; SELECT * FROM vw_produtos_ativos; -- ============================================ -- GERENCIAR TRIGGERS -- ============================================ -- Listar triggers SELECT name, sql FROM sqlite_master WHERE type = 'trigger'; -- Ver trigger específico SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = 'trg_produtos_insert_log'; -- Remover trigger DROP TRIGGER IF EXISTS trg_produtos_insert_log; DROP TRIGGER IF EXISTS trg_produtos_estoque_baixo; -- Limpar tudo DROP VIEW IF EXISTS vw_produtos_ativos; DROP TABLE IF EXISTS produtos; DROP TABLE IF EXISTS produtos_log; DROP TABLE IF EXISTS estoque_alertas;

8 – Transações

Transações garantem integridade dos dados (ACID):

SQLITE3 Shell

.open /var/lib/sqlite/triggers.db .headers on .mode box -- Criar tabelas CREATE TABLE contas ( id INTEGER PRIMARY KEY, titular TEXT NOT NULL, saldo REAL DEFAULT 0 CHECK (saldo >= 0) ); CREATE TABLE transferencias ( id INTEGER PRIMARY KEY, conta_origem INTEGER, conta_destino INTEGER, valor REAL, data_hora TEXT DEFAULT CURRENT_TIMESTAMP, status TEXT ); -- Dados iniciais INSERT INTO contas (titular, saldo) VALUES ('João', 1000.00); INSERT INTO contas (titular, saldo) VALUES ('Maria', 500.00); INSERT INTO contas (titular, saldo) VALUES ('Pedro', 200.00); SELECT * FROM contas; -- ============================================ -- TRANSAÇÃO BÁSICA - COMMIT -- ============================================ -- Iniciar transação BEGIN TRANSACTION; -- Transferir R$ 100 de João para Maria UPDATE contas SET saldo = saldo - 100 WHERE id = 1; UPDATE contas SET saldo = saldo + 100 WHERE id = 2; -- Registrar transferência INSERT INTO transferencias (conta_origem, conta_destino, valor, status) VALUES (1, 2, 100.00, 'concluida'); -- Verificar antes de confirmar SELECT * FROM contas; -- Confirmar transação COMMIT; SELECT * FROM contas; SELECT * FROM transferencias; -- ============================================ -- TRANSAÇÃO COM ROLLBACK -- ============================================ -- Verificar saldo atual SELECT * FROM contas; -- Iniciar transação BEGIN TRANSACTION; -- Tentar transferir R$ 1000 de Pedro (só tem R$ 200) UPDATE contas SET saldo = saldo - 1000 WHERE id = 3; -- ERRO: CHECK constraint failed (saldo >= 0) -- Se houve erro, desfazer ROLLBACK; -- Saldos permanecem inalterados SELECT * FROM contas; -- ============================================ -- TRANSAÇÃO COM SAVEPOINT -- ============================================ BEGIN TRANSACTION; -- Primeira operação UPDATE contas SET saldo = saldo + 50 WHERE id = 1; SAVEPOINT ponto1; -- Segunda operação UPDATE contas SET saldo = saldo + 100 WHERE id = 2; SAVEPOINT ponto2; -- Terceira operação (queremos desfazer) UPDATE contas SET saldo = saldo + 200 WHERE id = 3; -- Ver estado atual SELECT * FROM contas; -- Voltar ao ponto2 (desfaz terceira operação) ROLLBACK TO ponto2; -- Ver estado após rollback parcial SELECT * FROM contas; -- Confirmar transação COMMIT; SELECT * FROM contas; -- ============================================ -- MODOS DE TRANSAÇÃO -- ============================================ -- BEGIN DEFERRED (padrão) - lock obtido quando necessário BEGIN DEFERRED TRANSACTION; SELECT * FROM contas; COMMIT; -- BEGIN IMMEDIATE - lock de escrita imediato BEGIN IMMEDIATE TRANSACTION; UPDATE contas SET saldo = saldo + 1 WHERE id = 1; COMMIT; -- BEGIN EXCLUSIVE - lock exclusivo (bloqueia outras conexões) BEGIN EXCLUSIVE TRANSACTION; UPDATE contas SET saldo = saldo + 1 WHERE id = 1; COMMIT; -- ============================================ -- TRANSAÇÃO AUTOMÁTICA (Autocommit) -- ============================================ -- Por padrão, cada comando é uma transação automática -- Estes comandos são equivalentes: UPDATE contas SET saldo = 1000 WHERE id = 1; -- É igual a: BEGIN; UPDATE contas SET saldo = 1000 WHERE id = 1; COMMIT; -- Limpar DROP TABLE contas; DROP TABLE transferencias;

9 – Funções SQL Úteis

SQLITE3 Shell

.open /var/lib/sqlite/samples01.db .headers on .mode box -- ============================================ -- FUNÇÕES DE AGREGAÇÃO -- ============================================ CREATE TEMP TABLE vendas ( id INTEGER PRIMARY KEY, produto TEXT, quantidade INTEGER, valor REAL, data TEXT ); INSERT INTO vendas VALUES (1, 'A', 10, 100.00, '2024-01-15'), (2, 'B', 5, 50.00, '2024-01-16'), (3, 'A', 8, 80.00, '2024-01-17'), (4, 'C', 15, 150.00, '2024-01-18'), (5, 'B', 3, 30.00, '2024-01-19'); SELECT COUNT(*) AS total_vendas, COUNT(DISTINCT produto) AS produtos_unicos, SUM(quantidade) AS qtd_total, SUM(valor) AS valor_total, AVG(valor) AS media_valor, MIN(valor) AS menor_venda, MAX(valor) AS maior_venda, GROUP_CONCAT(produto, ', ') AS produtos FROM vendas; -- Agregação por grupo SELECT produto, COUNT(*) AS vendas, SUM(quantidade) AS qtd, printf('R$ %.2f', SUM(valor)) AS total FROM vendas GROUP BY produto HAVING SUM(valor) > 50; -- ============================================ -- FUNÇÕES DE TEXTO -- ============================================ SELECT 'texto' AS original, LENGTH('texto') AS tamanho, UPPER('texto') AS maiusculo, LOWER('TEXTO') AS minusculo, SUBSTR('texto', 2, 3) AS substring, REPLACE('texto', 'e', 'a') AS substituido, TRIM(' texto ') AS sem_espacos, LTRIM(' texto') AS sem_espacos_esq, RTRIM('texto ') AS sem_espacos_dir, INSTR('texto', 'x') AS posicao_x, 'pre' || 'fixo' AS concatenado, PRINTF('%05d', 42) AS formatado, QUOTE('texto com ''aspas''') AS com_escape; -- ============================================ -- FUNÇÕES NUMÉRICAS -- ============================================ SELECT ABS(-10) AS absoluto, ROUND(3.14159, 2) AS arredondado, ROUND(3.5) AS arred_inteiro, MAX(1, 2, 3) AS maximo, MIN(1, 2, 3) AS minimo, RANDOM() AS aleatorio, ABS(RANDOM() % 100) AS aleatorio_0_99, CAST(3.7 AS INTEGER) AS para_inteiro, CAST(42 AS REAL) AS para_real, TYPEOF(42) AS tipo_int, TYPEOF(3.14) AS tipo_real, TYPEOF('abc') AS tipo_text; -- ============================================ -- FUNÇÕES DE DATA/HORA -- ============================================ SELECT DATE('now') AS hoje, TIME('now') AS hora, DATETIME('now') AS data_hora, DATETIME('now', 'localtime') AS data_hora_local, DATE('now', '+7 days') AS daqui_7_dias, DATE('now', '-1 month') AS mes_passado, DATE('now', 'start of month') AS inicio_mes, DATE('now', 'start of year') AS inicio_ano, DATE('now', '+1 month', 'start of month', '-1 day') AS fim_mes, STRFTIME('%d/%m/%Y', 'now') AS formato_br, STRFTIME('%H:%M:%S', 'now') AS formato_hora, STRFTIME('%Y', 'now') AS ano, STRFTIME('%m', 'now') AS mes, STRFTIME('%d', 'now') AS dia, STRFTIME('%W', 'now') AS semana_ano, STRFTIME('%w', 'now') AS dia_semana, STRFTIME('%s', 'now') AS unix_timestamp, JULIANDAY('now') AS dia_juliano; -- Diferença entre datas SELECT CAST(JULIANDAY('2024-12-31') - JULIANDAY('2024-01-01') AS INTEGER) AS dias_no_ano, CAST((JULIANDAY('now') - JULIANDAY('2024-01-01')) AS INTEGER) AS dias_desde_jan; -- ============================================ -- FUNÇÕES CONDICIONAIS -- ============================================ SELECT COALESCE(NULL, 'valor_padrao') AS coalesce_ex, IFNULL(NULL, 'se_null') AS ifnull_ex, NULLIF(1, 1) AS nullif_igual, NULLIF(1, 2) AS nullif_diferente, IIF(1 > 0, 'verdadeiro', 'falso') AS iif_ex, CASE WHEN 10 > 5 THEN 'maior' WHEN 10 < 5 THEN 'menor' ELSE 'igual' END AS case_ex; -- CASE com valores SELECT produto, valor, CASE WHEN valor >= 100 THEN 'Alto' WHEN valor >= 50 THEN 'Médio' ELSE 'Baixo' END AS categoria FROM vendas; -- ============================================ -- FUNÇÕES JSON (SQLite 3.38+) -- ============================================ SELECT JSON('{"nome":"João","idade":30}') AS json_valido, JSON_EXTRACT('{"nome":"João","idade":30}', '$.nome') AS nome, JSON_EXTRACT('{"nome":"João","idade":30}', '$.idade') AS idade, JSON_TYPE('{"nome":"João"}', '$.nome') AS tipo_nome, JSON_ARRAY(1, 2, 3) AS array_json, JSON_OBJECT('a', 1, 'b', 2) AS objeto_json, JSON_INSERT('{"a":1}', '$.b', 2) AS json_inserido, JSON_REPLACE('{"a":1}', '$.a', 99) AS json_substituido, JSON_SET('{"a":1}', '$.b', 2) AS json_setado, JSON_REMOVE('{"a":1,"b":2}', '$.b') AS json_removido; -- JSON com arrays SELECT JSON_ARRAY_LENGTH('[1,2,3,4,5]') AS tamanho, JSON_EXTRACT('[1,2,3,4,5]', '$[0]') AS primeiro, JSON_EXTRACT('[1,2,3,4,5]', '$[#-1]') AS ultimo; DROP TABLE vendas;

10 – Consultas avançadas

Mais exemplos:

SQLITE3 Shell

.open /var/lib/sqlite/consultas.db .headers on .mode box -- Criar tabelas de exemplo CREATE TABLE clientes ( id INTEGER PRIMARY KEY, nome TEXT, cidade TEXT ); CREATE TABLE pedidos ( id INTEGER PRIMARY KEY, cliente_id INTEGER, valor REAL, status TEXT, data TEXT ); -- Inserir dados INSERT INTO clientes VALUES (1, 'João', 'São Paulo'), (2, 'Maria', 'Rio de Janeiro'), (3, 'Pedro', 'São Paulo'), (4, 'Ana', 'Curitiba'); INSERT INTO pedidos VALUES (1, 1, 100.00, 'entregue', '2024-01-10'), (2, 1, 200.00, 'entregue', '2024-01-15'), (3, 2, 150.00, 'pendente', '2024-01-20'), (4, 2, 300.00, 'entregue', '2024-02-01'), (5, 3, 50.00, 'cancelado', '2024-02-10'), (6, 1, 175.00, 'pendente', '2024-02-15'); -- ============================================ -- JOINS -- ============================================ -- INNER JOIN SELECT c.nome, p.valor, p.status FROM clientes c INNER JOIN pedidos p ON c.id = p.cliente_id; -- LEFT JOIN (todos os clientes, mesmo sem pedidos) SELECT c.nome, COALESCE(SUM(p.valor), 0) AS total_compras FROM clientes c LEFT JOIN pedidos p ON c.id = p.cliente_id GROUP BY c.id; -- ============================================ -- SUBQUERIES -- ============================================ -- Subquery no WHERE SELECT * FROM clientes WHERE id IN (SELECT DISTINCT cliente_id FROM pedidos WHERE status = 'entregue'); -- Subquery escalar SELECT nome, (SELECT COUNT(*) FROM pedidos WHERE cliente_id = clientes.id) AS qtd_pedidos FROM clientes; -- Subquery no FROM SELECT cidade, total_clientes FROM ( SELECT cidade, COUNT(*) AS total_clientes FROM clientes GROUP BY cidade ) AS resumo WHERE total_clientes >= 1; -- ============================================ -- CTE (Common Table Expressions) -- ============================================ -- CTE simples WITH pedidos_entregues AS ( SELECT * FROM pedidos WHERE status = 'entregue' ) SELECT c.nome, SUM(pe.valor) AS total_entregue FROM clientes c JOIN pedidos_entregues pe ON c.id = pe.cliente_id GROUP BY c.id; -- CTE múltiplas WITH resumo_cliente AS ( SELECT cliente_id, COUNT(*) AS qtd_pedidos, SUM(valor) AS total FROM pedidos GROUP BY cliente_id ), media_geral AS ( SELECT AVG(total) AS media FROM resumo_cliente ) SELECT c.nome, rc.qtd_pedidos, printf('R$ %.2f', rc.total) AS total, CASE WHEN rc.total > mg.media THEN 'Acima' ELSE 'Abaixo' END AS comparativo FROM clientes c JOIN resumo_cliente rc ON c.id = rc.cliente_id CROSS JOIN media_geral mg; -- CTE recursiva (hierarquia) CREATE TABLE categorias ( id INTEGER PRIMARY KEY, nome TEXT, pai_id INTEGER ); INSERT INTO categorias VALUES (1, 'Eletrônicos', NULL), (2, 'Computadores', 1), (3, 'Notebooks', 2), (4, 'Desktops', 2), (5, 'Celulares', 1), (6, 'Smartphones', 5); WITH RECURSIVE arvore AS ( -- Base: categorias raiz SELECT id, nome, pai_id, 0 AS nivel, nome AS caminho FROM categorias WHERE pai_id IS NULL UNION ALL -- Recursivo: filhos SELECT c.id, c.nome, c.pai_id, a.nivel + 1, a.caminho || ' > ' || c.nome FROM categorias c JOIN arvore a ON c.pai_id = a.id ) SELECT SUBSTR(' ', 1, nivel * 2) || nome AS categoria, caminho FROM arvore ORDER BY caminho; -- ============================================ -- WINDOW FUNCTIONS (SQLite 3.25+) -- ============================================ -- ROW_NUMBER, RANK, DENSE_RANK SELECT cliente_id, valor, ROW_NUMBER() OVER (ORDER BY valor DESC) AS posicao, RANK() OVER (ORDER BY valor DESC) AS ranking, DENSE_RANK() OVER (ORDER BY valor DESC) AS ranking_denso FROM pedidos; -- Particionado por cliente SELECT cliente_id, valor, ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY valor DESC) AS pos_cliente, SUM(valor) OVER (PARTITION BY cliente_id) AS total_cliente FROM pedidos; -- Running total (soma acumulada) SELECT data, valor, SUM(valor) OVER (ORDER BY data) AS acumulado, AVG(valor) OVER (ORDER BY data ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_movel_3 FROM pedidos ORDER BY data; -- LEAD e LAG (próximo e anterior) SELECT data, valor, LAG(valor, 1) OVER (ORDER BY data) AS valor_anterior, LEAD(valor, 1) OVER (ORDER BY data) AS valor_proximo, valor - LAG(valor, 1) OVER (ORDER BY data) AS diferenca FROM pedidos ORDER BY data; -- FIRST_VALUE e LAST_VALUE SELECT cliente_id, data, valor, FIRST_VALUE(valor) OVER ( PARTITION BY cliente_id ORDER BY data ) AS primeiro_valor, LAST_VALUE(valor) OVER ( PARTITION BY cliente_id ORDER BY data RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS ultimo_valor FROM pedidos; -- ============================================ -- UPSERT (INSERT OR REPLACE) -- ============================================ CREATE TABLE config ( chave TEXT PRIMARY KEY, valor TEXT, atualizado TEXT ); -- Insert or Replace (substitui se existir) INSERT OR REPLACE INTO config VALUES ('tema', 'escuro', datetime('now')); INSERT OR REPLACE INTO config VALUES ('idioma', 'pt-BR', datetime('now')); INSERT OR REPLACE INTO config VALUES ('tema', 'claro', datetime('now')); SELECT * FROM config; -- Insert or Ignore (ignora se existir) INSERT OR IGNORE INTO config VALUES ('tema', 'azul', datetime('now')); SELECT * FROM config; -- UPSERT com ON CONFLICT (SQLite 3.24+) INSERT INTO config (chave, valor, atualizado) VALUES ('versao', '1.0', datetime('now')) ON CONFLICT(chave) DO UPDATE SET valor = excluded.valor, atualizado = excluded.atualizado; SELECT * FROM config; -- Limpar DROP TABLE clientes; DROP TABLE pedidos; DROP TABLE categorias; DROP TABLE config;

11 – Importação e Exportação de Dados

Manipulando dados para dentro e para fora do SQLITE:

SQLITE3 Shell

.open /var/lib/sqlite/importacao.db -- ============================================ -- EXPORTAR DADOS -- ============================================ -- Criar tabela de exemplo CREATE TABLE usuarios ( id INTEGER PRIMARY KEY, nome TEXT, email TEXT, cidade TEXT ); INSERT INTO usuarios VALUES (1, 'João Silva', 'joao@email.com', 'São Paulo'), (2, 'Maria Santos', 'maria@email.com', 'Rio de Janeiro'), (3, 'Pedro Costa', 'pedro@email.com', 'Belo Horizonte'); -- Exportar para CSV .headers on .mode csv .output usuarios.csv SELECT * FROM usuarios; .output stdout -- Exportar para JSON (linha por linha) .mode json .output usuarios.json SELECT * FROM usuarios; .output stdout -- Exportar como SQL (dump) .output usuarios.sql .dump usuarios .output stdout -- Exportar apenas dados (INSERT) .mode insert usuarios .output usuarios_insert.sql SELECT * FROM usuarios; .output stdout -- Ver arquivos criados .shell ls -la *.csv *.json *.sql -- ============================================ -- IMPORTAR DADOS -- ============================================ -- Criar tabela para importação CREATE TABLE usuarios_import ( id INTEGER PRIMARY KEY, nome TEXT, email TEXT, cidade TEXT ); -- Importar CSV .mode csv .import usuarios.csv usuarios_import -- Verificar importação .mode box .headers on SELECT * FROM usuarios_import; -- Se CSV tiver header, pular primeira linha DELETE FROM usuarios_import; .import --skip 1 usuarios.csv usuarios_import SELECT * FROM usuarios_import; -- ============================================ -- BACKUP E RESTORE -- ============================================ -- Backup do banco inteiro .backup backup_completo.db -- Dump SQL completo .output dump_completo.sql .dump .output stdout -- Restore de dump .read dump_completo.sql -- ============================================ -- ATTACH E COPIAR ENTRE BANCOS -- ============================================ -- Criar segundo banco ATTACH DATABASE 'outro_banco.db' AS outro; -- Copiar tabela para outro banco CREATE TABLE outro.usuarios AS SELECT * FROM usuarios; -- Verificar SELECT * FROM outro.usuarios; -- Desanexar DETACH DATABASE outro; -- Limpar arquivos .shell rm -f usuarios.csv usuarios.json usuarios.sql usuarios_insert.sql .shell rm -f backup_completo.db dump_completo.sql outro_banco.db DROP TABLE usuarios; DROP TABLE usuarios_import;

12 – Performance e Otimização

SQLITE3 Shell

.open /var/lib/sqlite/performance.db .headers on .mode box -- ============================================ -- CONFIGURAÇÕES DE PERFORMANCE -- ============================================ -- Modo WAL (Write-Ahead Logging) - recomendado para concorrência PRAGMA journal_mode = WAL; -- Cache em memória (páginas, negativo = KB) PRAGMA cache_size = -64000; -- 64MB -- Armazenar temporários em memória PRAGMA temp_store = MEMORY; -- Memory-mapped I/O PRAGMA mmap_size = 268435456; -- 256MB -- Modo de sincronização PRAGMA synchronous = NORMAL; -- Equilíbrio entre segurança e velocidade -- Verificar configurações PRAGMA journal_mode; PRAGMA cache_size; PRAGMA temp_store; PRAGMA mmap_size; PRAGMA synchronous; -- ============================================ -- ANALISAR QUERIES -- ============================================ -- Criar tabela grande para testes CREATE TABLE IF NOT EXISTS logs ( id INTEGER PRIMARY KEY, nivel TEXT, mensagem TEXT, data_hora TEXT, origem TEXT ); -- Inserir 100.000 registros WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt WHERE x < 100000 ) INSERT INTO logs (nivel, mensagem, data_hora, origem) SELECT CASE (x % 4) WHEN 0 THEN 'INFO' WHEN 1 THEN 'WARN' WHEN 2 THEN 'ERROR' ELSE 'DEBUG' END, 'Mensagem de log número ' || x, datetime('2024-01-01', '+' || (x % 365) || ' days', '+' || (x % 24) || ' hours'), CASE (x % 3) WHEN 0 THEN 'api' WHEN 1 THEN 'web' ELSE 'batch' END FROM cnt; SELECT COUNT(*) AS total FROM logs; -- Ver plano de execução .timer on EXPLAIN QUERY PLAN SELECT * FROM logs WHERE nivel = 'ERROR'; -- Resultado: SCAN logs (varredura completa) -- Criar índice CREATE INDEX idx_logs_nivel ON logs(nivel); -- Ver plano novamente EXPLAIN QUERY PLAN SELECT * FROM logs WHERE nivel = 'ERROR'; -- Resultado: SEARCH logs USING INDEX (usa índice) -- Comparar tempo de execução SELECT COUNT(*) FROM logs WHERE nivel = 'ERROR'; -- Analisar estatísticas ANALYZE logs; -- Ver estatísticas SELECT * FROM sqlite_stat1 WHERE tbl = 'logs'; -- ============================================ -- EXPLAIN DETALHADO -- ============================================ -- EXPLAIN mostra bytecode VM EXPLAIN SELECT * FROM logs WHERE nivel = 'ERROR' LIMIT 5; -- EXPLAIN QUERY PLAN é mais legível EXPLAIN QUERY PLAN SELECT l1.nivel, COUNT(*) FROM logs l1 JOIN logs l2 ON l1.data_hora = l2.data_hora GROUP BY l1.nivel; -- ============================================ -- VACUUM - OTIMIZAÇÃO DE ESPAÇO -- ============================================ -- Ver tamanho do banco SELECT page_count * page_size AS tamanho_bytes FROM pragma_page_count(), pragma_page_size(); -- Deletar metade dos registros DELETE FROM logs WHERE id % 2 = 0; -- Ver páginas livres PRAGMA freelist_count; -- Compactar banco (recupera espaço) VACUUM; -- Verificar novamente PRAGMA freelist_count; SELECT page_count * page_size AS tamanho_bytes FROM pragma_page_count(), pragma_page_size(); -- ============================================ -- OTIMIZAÇÃO AUTOMÁTICA -- ============================================ -- Rodar otimização (analisa e reorganiza) PRAGMA optimize; -- Verificar integridade PRAGMA integrity_check; -- Limpar DROP TABLE logs; .timer off

13 – Extensões e Funcionalidades Especiais

SQLITE3 Shell

.open /var/lib/sqlite/extensoes.db .headers on .mode box -- ============================================ -- FULL-TEXT SEARCH (FTS5) -- ============================================ -- Criar tabela FTS5 CREATE VIRTUAL TABLE artigos USING fts5( titulo, conteudo, autor, content='artigos_base', content_rowid='id' ); -- Tabela base CREATE TABLE artigos_base ( id INTEGER PRIMARY KEY, titulo TEXT, conteudo TEXT, autor TEXT ); -- Triggers para sincronizar CREATE TRIGGER artigos_ai AFTER INSERT ON artigos_base BEGIN INSERT INTO artigos(rowid, titulo, conteudo, autor) VALUES (new.id, new.titulo, new.conteudo, new.autor); END; CREATE TRIGGER artigos_ad AFTER DELETE ON artigos_base BEGIN INSERT INTO artigos(artigos, rowid, titulo, conteudo, autor) VALUES ('delete', old.id, old.titulo, old.conteudo, old.autor); END; CREATE TRIGGER artigos_au AFTER UPDATE ON artigos_base BEGIN INSERT INTO artigos(artigos, rowid, titulo, conteudo, autor) VALUES ('delete', old.id, old.titulo, old.conteudo, old.autor); INSERT INTO artigos(rowid, titulo, conteudo, autor) VALUES (new.id, new.titulo, new.conteudo, new.autor); END; -- Inserir dados INSERT INTO artigos_base (titulo, conteudo, autor) VALUES ('Introdução ao SQLite', 'SQLite é um banco de dados leve e eficiente para aplicações embarcadas.', 'João'), ('Python e Banco de Dados', 'Python possui excelente suporte para SQLite através do módulo sqlite3.', 'Maria'), ('Performance em SQLite', 'Dicas para otimizar consultas e índices no SQLite.', 'Pedro'), ('SQLite vs PostgreSQL', 'Comparação entre SQLite e PostgreSQL para diferentes casos de uso.', 'Ana'); -- Busca full-text SELECT * FROM artigos WHERE artigos MATCH 'SQLite'; -- Busca com ranking SELECT *, rank FROM artigos WHERE artigos MATCH 'SQLite' ORDER BY rank; -- Busca em coluna específica SELECT * FROM artigos WHERE artigos MATCH 'titulo:SQLite'; -- Busca com operadores SELECT * FROM artigos WHERE artigos MATCH 'SQLite AND Python'; SELECT * FROM artigos WHERE artigos MATCH 'SQLite OR PostgreSQL'; SELECT * FROM artigos WHERE artigos MATCH 'SQLite NOT PostgreSQL'; -- Busca por prefixo SELECT * FROM artigos WHERE artigos MATCH 'SQL*'; -- Highlight dos termos encontrados SELECT highlight(artigos, 0, '<b>', '</b>') AS titulo_destacado FROM artigos WHERE artigos MATCH 'SQLite'; -- Snippet do conteúdo SELECT snippet(artigos, 1, '<b>', '</b>', '...', 10) AS trecho FROM artigos WHERE artigos MATCH 'banco dados'; -- ============================================ -- R-TREE (Índice Espacial) -- ============================================ -- Criar índice R-Tree para dados geoespaciais CREATE VIRTUAL TABLE locais USING rtree( id, min_lat, max_lat, min_lng, max_lng ); -- Inserir pontos (como retângulos de tamanho zero) INSERT INTO locais VALUES (1, -23.5505, -23.5505, -46.6333, -46.6333); -- São Paulo INSERT INTO locais VALUES (2, -22.9068, -22.9068, -43.1729, -43.1729); -- Rio de Janeiro INSERT INTO locais VALUES (3, -19.9191, -19.9191, -43.9386, -43.9386); -- Belo Horizonte INSERT INTO locais VALUES (4, -25.4284, -25.4284, -49.2733, -49.2733); -- Curitiba -- Buscar pontos em uma região (bounding box) SELECT id FROM locais WHERE min_lat >= -25 AND max_lat <= -20 AND min_lng >= -50 AND max_lng <= -40; -- ============================================ -- GENERATED COLUMNS (SQLite 3.31+) -- ============================================ CREATE TABLE produtos ( id INTEGER PRIMARY KEY, nome TEXT NOT NULL, preco REAL NOT NULL, quantidade INTEGER NOT NULL, -- Coluna gerada (calculada automaticamente) valor_total REAL GENERATED ALWAYS AS (preco * quantidade) STORED, -- Coluna virtual (calculada sob demanda) em_estoque TEXT GENERATED ALWAYS AS ( CASE WHEN quantidade > 0 THEN 'Sim' ELSE 'Não' END ) VIRTUAL ); INSERT INTO produtos (nome, preco, quantidade) VALUES ('Notebook', 3500.00, 10); INSERT INTO produtos (nome, preco, quantidade) VALUES ('Mouse', 89.90, 50); INSERT INTO produtos (nome, preco, quantidade) VALUES ('Monitor', 1200.00, 0); SELECT * FROM produtos; -- ============================================ -- STRICT TABLES (SQLite 3.37+) -- ============================================ -- Tabela com tipos estritos (como outros bancos) CREATE TABLE usuarios_strict ( id INTEGER PRIMARY KEY, nome TEXT NOT NULL, idade INTEGER, saldo REAL ) STRICT; -- Isso funciona INSERT INTO usuarios_strict VALUES (1, 'João', 30, 1000.50); -- Isso FALHA (texto em coluna INTEGER) -- INSERT INTO usuarios_strict VALUES (2, 'Maria', 'trinta', 500.00); -- Error: cannot store TEXT value in INTEGER column usuarios_strict.idade -- ============================================ -- JSON (Funções nativas) -- ============================================ CREATE TABLE configuracoes ( id INTEGER PRIMARY KEY, nome TEXT, dados TEXT -- JSON armazenado como TEXT ); INSERT INTO configuracoes VALUES (1, 'app', '{"tema": "escuro", "idioma": "pt-BR", "notificacoes": true}'), (2, 'usuario', '{"nome": "João", "preferencias": {"fonte": 14, "cores": ["azul", "verde"]}}'); -- Extrair valores JSON SELECT nome, json_extract(dados, '$.tema') AS tema, json_extract(dados, '$.idioma') AS idioma FROM configuracoes WHERE nome = 'app'; -- Extrair de estruturas aninhadas SELECT json_extract(dados, '$.nome') AS nome_usuario, json_extract(dados, '$.preferencias.fonte') AS tamanho_fonte, json_extract(dados, '$.preferencias.cores[0]') AS cor_primaria FROM configuracoes WHERE nome = 'usuario'; -- Modificar JSON UPDATE configuracoes SET dados = json_set(dados, '$.tema', 'claro') WHERE nome = 'app'; SELECT dados FROM configuracoes WHERE nome = 'app'; -- Limpar DROP TABLE artigos; DROP TABLE artigos_base; DROP TABLE locais; DROP TABLE produtos; DROP TABLE usuarios_strict; DROP TABLE configuracoes;

14 – Dicas e Boas Práticas

SQLITE3 Shell

-- ============================================ -- BOAS PRÁTICAS -- ============================================ -- 1. SEMPRE usar transações para múltiplas operações BEGIN; INSERT INTO tabela VALUES (1, 'a'); INSERT INTO tabela VALUES (2, 'b'); INSERT INTO tabela VALUES (3, 'c'); COMMIT; -- 2. Habilitar chaves estrangeiras (desabilitadas por padrão!) PRAGMA foreign_keys = ON; -- 3. Usar WAL mode para melhor concorrência PRAGMA journal_mode = WAL; -- 4. Criar índices para colunas usadas em WHERE, JOIN, ORDER BY CREATE INDEX idx_tabela_coluna ON tabela(coluna); -- 5. Usar EXPLAIN QUERY PLAN para otimizar queries EXPLAIN QUERY PLAN SELECT * FROM tabela WHERE coluna = 'valor'; -- 6. Executar ANALYZE após grandes alterações ANALYZE; -- 7. Usar VACUUM periodicamente para compactar VACUUM; -- 8. Usar prepared statements (via código) para evitar SQL injection -- 9. Fazer backup regular -- .backup arquivo_backup.db -- 10. Usar tipos apropriados (mesmo com type affinity flexível) CREATE TABLE exemplo ( id INTEGER PRIMARY KEY, -- Sempre INTEGER para PK nome TEXT NOT NULL, -- TEXT para strings valor REAL, -- REAL para decimais ativo INTEGER DEFAULT 1, -- INTEGER para boolean (0/1) dados BLOB, -- BLOB para binários criado TEXT DEFAULT CURRENT_TIMESTAMP -- TEXT para datas ); -- ============================================ -- ERROS COMUNS A EVITAR -- ============================================ -- ❌ NÃO: Esquecer de habilitar FK -- ✅ SIM: PRAGMA foreign_keys = ON; -- ❌ NÃO: Múltiplos INSERTs sem transação (muito lento) -- ✅ SIM: BEGIN; ... múltiplos INSERTs ... COMMIT; -- ❌ NÃO: SELECT * em produção -- ✅ SIM: SELECT coluna1, coluna2 FROM tabela; -- ❌ NÃO: Confiar no autocommit para operações críticas -- ✅ SIM: Usar transações explícitas -- ❌ NÃO: Guardar arquivos grandes no banco -- ✅ SIM: Guardar path do arquivo e manter arquivo no filesystem -- ❌ NÃO: Usar SQLite para alta concorrência de escrita -- ✅ SIM: Usar PostgreSQL/MySQL para esses casos -- ============================================ -- QUANDO USAR SQLITE -- ============================================ -- ✅ Aplicações desktop/mobile -- ✅ Sites com baixo/médio tráfego -- ✅ Dispositivos embarcados/IoT -- ✅ Testes e desenvolvimento -- ✅ Cache local -- ✅ Armazenamento de configurações -- ✅ Prototipagem rápida -- ✅ Análise de dados (datasets pequenos/médios) -- ❌ Alta concorrência de escrita -- ❌ Grandes volumes de dados (>100GB) -- ❌ Replicação necessária -- ❌ Múltiplos servidores acessando mesmo banco

“A simplicidade é o mais
alto grau de sofisticação“
Autor: Leonardo da Vinci

Terminamos por hoje!

Patrick Brandão, patrickbrandao@gmail.com

Ler artigo completo