1) Modelagem de dados e esquemas eficientes em SQLite

• SQLite possui tipagem dinâmica; priorize esquemas claros com chaves primárias explícitas (INTEGER PRIMARY KEY) para acesso rápido ao rowid.
• ConsidereWITHOUT ROWID quando apropriado para tabelas longas com chaves compostas simples, reduzindo o custo de armazenamento e acelerando alguns acessos por linha.
• Equilíbrio entre normalização e desnormalização: normalize para integridade, desnormalize com cuidado para leituras pesadas se necessário.
• Uso estratégico de índices: crie índices compostos na ordem de predicados (WHERE, JOIN, ORDER BY) e prefira índices que cubram as colunas utilizadas pela consulta (covering index).
• Habilite e mantenha a integridade referencial: PRAGMA foreign_keys = ON; constraints ajudam, mas planeje migrations com cuidado para evitar migrações caras.

Dica prática: quando uma consulta frequente usa filtros em várias colunas, avalie um índice composto e verifique o plano de consulta com EXPLAIN QUERY PLAN.

2) Consultas avançadas e otimização de desempenho

• Use Common Table Expressions (CTEs) recursivas para hierarquias ou encadeamento de relações; ajudam a manter consultas legíveis e escaláveis.
• Examine planos de consulta com EXPLAIN QUERY PLAN para entender variações de custo entre variações de índices e estratégias de acesso.
• Prefira consultas que possam ser cobertas por índices (covering) para evitar leituras de dados reais da tabela.
• Minimize a quantidade de colunas retornadas (SELECT col1, col2) e evite SELECT * em cenários de alto volume.

-- Exemplo: árvore de categorias com CTE recursiva
WITH RECURSIVE ancestors(id, name, depth, path) AS (
  -- raiz(es) da hierarquia
  SELECT id, name, 0 AS depth, printf('%s', name) AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- junção recursiva para filhos
  SELECT c.id, c.name, a.depth + 1,
         printf('%s / %s', a.path, c.name)
  FROM categories c
  JOIN ancestors a ON c.parent_id = a.id
)
SELECT id, path, depth
FROM ancestors
ORDER BY path;

Dica adicional: quando filtrar por data ou intervalo numérico, verifique se a cláusula WHERE utiliza colunas com índices apropriados e considere criar índices parciais se apropriado (por exemplo, apenas para dias recentes).

3) Transações, concorrência e integridade

• Utilize o modo WAL (write-ahead log) para reduzir contenção entre múltiplos leitores e escritores: PRAGMA journal_mode = WAL;
• Transações curtas são preferíveis; envolva apenas as operações necessárias entre BEGIN e COMMIT. Use SAVEPOINTs para reversões parciais em operações complexas.
• Ajuste de sincronismo (PRAGMA synchronous) pode equilibrar durabilidade e performance conforme o ambiente (NORMAL ou FULL).
• Planeje migrations com estratégias “in-place” quando possível, e, para alterações estruturais grandes, crie novas tabelas com o esquema alvo, copie os dados, renomeie e remova a antiga (minimiza downtime).

Exemplo conceitual: para uma migração de tabela complexa, o padrão recomendado envolve criar uma nova tabela com o schema desejado, popular com dados existentes e, em seguida, substituir as tabelas, reduzindo o tempo de bloqueio durante a migração.

4) Funcionalidades avançadas e cenários desafiadores

• Full-Text Search com FTS5: crie tabelas virtuais para pesquisa textual eficiente, útil em logs, notas, artigos, etc. Exemplo:
• Trigers para manter consistência além das constraints nativas quando necessário; eles permitem manter colunas derivadas ou sincronizar dados entre tabelas.
• Views úteis, mas cuidado: algumas views não são atualizáveis; prefira views simples para leitura e mantenha a lógica de escrita em triggers quando necessário.
• Extensões e tabelas virtuais como JSON1 (json_extract, json_group_array) para manipular dados JSON sem sair do ambiente SQLite.
• Migrações seguras: quando alterações estruturais são grandes, crie uma nova tabela com o schema desejado, aplique as transformações de dados e faça a substituição encadeada para manter integridade e disponibilidade.

Exemplos inline:

• Criar tabela virtual FTS5:
  CREATE VIRTUAL TABLE articles USING fts5(title, body, content="articles_content");
• Usar JSON1 para extrair dados embutidos:
  SELECT json_extract(data, '$.author') AS author FROM posts WHERE json_type(data, '$.tags') = 'array';

Observação: SQLite é altamente flexível, mas certos cenários exigem planejamento cuidadoso de índices, esquemas de dados e migrações para manter performance estável.