Habilidade de IA: Engenharia e Otimização de SQLite (db-sqlite)

Esta skill orienta a inteligência artificial a atuar como especialista no banco de dados SQLite, fundamentada rigorosamente na documentação oficial (sqlite.org/docs.html). Cobre padrões de engenharia de software para bancos embarcados (embedded), tunagem de alta concorrência via WAL mode, pragmas de performance, índices parciais e tabelas WITHOUT ROWID.

---

🧭 Arquitetura Embarcada e Modo WAL (Write-Ahead Logging)

Ao contrário dos bancos cliente-servidor, o SQLite opera como um motor embarcado no mesmo processo da aplicação.

1. Configuração Recomendada de Concorrência e PRAGMAs

Por padrão, o SQLite opera em modo Rollback Journal (que bloqueia leituras durante gravações). Para habilitar leituras concorrentes com gravações, ative obrigatoriamente o modo WAL:

-- Ativar modo Write-Ahead Logging (persistido no arquivo da base)
PRAGMA journal_mode = WAL;

-- Sincronização segura para WAL (desempenho 10x superior sem perda de consistência)
PRAGMA synchronous = NORMAL;

-- Manter tabela temporária em memória RAM
PRAGMA temp_store = MEMORY;

-- Aumentar tamanho do cache de memória (ex: 64MB = 16000 páginas de 4KB)
PRAGMA cache_size = -64000;

-- Definir tempo limite de espera para evitar SQLITE_BUSY em escritas concorrentes
PRAGMA busy_timeout = 5000;

-- Habilitar verificação de chaves estrangeiras
PRAGMA foreign_keys = ON;

---

🛠️ Estratégias de Indexação e Modelagem de Alto Desempenho

1. Tabelas WITHOUT ROWID

• Para tabelas associativas (N:M) ou tabelas com Chave Primária natural composta ou alfanumérica (ex: UUID/TEXT), utilize a cláusula WITHOUT ROWID para economizar espaço de armazenamento e eliminar uma busca B-Tree dupla:

CREATE TABLE user_roles (
    user_id TEXT NOT NULL,
    role_id TEXT NOT NULL,
    assigned_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    PRIMARY KEY (user_id, role_id)
) WITHOUT ROWID;

2. Índices Parciais e Índices em Expressões

• Índice Parcial: Indexe apenas os registros relevantes da tabela:

CREATE INDEX idx_active_subscriptions 
ON subscriptions (user_id) 
WHERE status = 'ACTIVE';

• Índice em Expressão: Indexe o resultado de funções deterministicas ou dados extraídos de JSON:

CREATE INDEX idx_user_email_domain 
ON users (substr(email, instr(email, '@') + 1));

---

🔍 Busca Textual (FTS5) e Manipulação de JSON

1. Suporte Nativo a JSON

O SQLite possui suporte nativo a JSON (JSON1 extension habilitada por padrão):

CREATE TABLE user_settings (
    user_id INTEGER PRIMARY KEY,
    data TEXT CHECK (json_valid(data))
);

-- Extraindo valores formatados
SELECT json_extract(data, '$.theme') AS theme FROM user_settings;

2. Busca de Texto Completo (Full-Text Search - FTS5)

CREATE VIRTUAL TABLE documents_fts USING fts5(
    title,
    body,
    tokenize = 'porter ascii'
);

-- Consulta por frase ou prefixo com ordenação por relevância (bm25)
SELECT title, bm25(documents_fts) AS rank 
FROM documents_fts 
WHERE documents_fts MATCH 'sqlite AND performance*' 
ORDER BY rank;

---

⚙️ Diretrizes para Aplicação e Deploy

1. Tratamento do erro SQLITE_BUSY: Garanta que o driver SQLite da aplicação implemente o busy_timeout ou trate a exceção com retentativas e backoff exponencial.
2. Backups Online Não-Bloqueantes: Utilize a API nativa de backup do SQLite (sqlite3_backup_init ou o comando CLI .backup) em vez de copiar diretamente o arquivo .db enquanto a aplicação está em execução.

---

🔒 Hardening e Criptografia em Bancos Embarcados (OWASP MASVS & ASVS)

• Criptografia em Repouso: Em sistemas operacionais móveis ou desktop, adote SEE (SQLite Encryption Extension) ou SQLCipher (AES-256) para proteger arquivos .db contra exfiltração física ou engenharia reversa.
• Proteção de Permissões no SO: Restrinja as permissões do arquivo de banco de dados (chmod 600) para acesso exclusivo do processo proprietário da aplicação.
• Mitigação de SQL Injection: Obrigatoriamente utilize parâmetros vinculados (prepared statements) em vez de concatenação de strings em sqlite3_exec.

---

🔗 Integração com Outras Skills

• Para integrar SQLite em aplicações desktop, mobile ou embarcadas em Python/C/Rust, consulte lang-python, lang-c e lang-rust.
• Para diretrizes gerais de administração de bancos de dados relacionais, consulte dba-database-administrator.
• Para requisitos de segurança em armazenamento móvel e embarcado, consulte appsec-owasp-masvs e appsec-owasp-asvs.