SQL PRAGMAs Úteis

Todo carro tem o painel, com o velocímetro e o marcador de combustível, e tem os botões, que ligam o farol e o limpador. No SQLite, o painel e os botões do banco são os PRAGMAs: comandos especiais que mostram informações sobre o banco (quais colunas uma tabela tem, se o arquivo está saudável) ou mudam o jeito como ele trabalha (ligar as chaves estrangeiras, ligar o modo WAL). Você já esbarrou em alguns nas aulas anteriores. Nesta aula, a Ana conhece os mais úteis, um por um.

Os PRAGMAs são comandos SQL, então rodam no editor do guia: os exemplos com quadro "Resultado" rodam no SQLite do seu navegador, com a Loja Aurora. Mas lembre que o banco do editor vive na memória e é recriado a cada Rodar. Os PRAGMAs que mexem com o arquivo (o modo WAL, uma versão que precisa sobreviver até amanhã) foram testados no terminal da Ana, com o ~/loja.db.

A ideia: o painel e os botões

Pragma vem do grego e quer dizer "coisa prática". Um PRAGMA não lê nem muda os dados das tabelas: ele lê ou muda o banco. E ele é uma extensão do SQLite; não faz parte do SQL padrão (veja Nos outros bancos). Todo PRAGMA tem uma destas formas:

1. A estrutura do banco: table_list e table_info

No terminal, o .tables e o .schema (aula Comandos de Ponto) mostram as tabelas. Mas eles são do programa sqlite3; um programa em Python ou PHP, ou o editor do guia, não os tem. Os PRAGMAs fazem o mesmo em SQL. O table_list lista as tabelas:

PRAGMA table_list;

Aparecem as sete tabelas da Loja Aurora e duas que você não criou: sqlite_schema e sqlite_temp_schema, as tabelas internas onde o SQLite guarda a definição de tudo (do banco principal, main, e do banco temporário, temp). A coluna ncol é o número de colunas de cada tabela.

O table_info descreve as colunas de uma tabela:

PRAGMA table_info(produtos);

cid

A posição da coluna, contando do 0.

name

O nome da coluna.

type

O tipo declarado no CREATE TABLE (veja Os Tipos no SQLite).

notnull

1 se a coluna é NOT NULL (veja NOT NULL).

dflt_value

O valor padrão (DEFAULT), se houver: o estoque e o descontinuado começam em 0. NULL = sem padrão.

pk

1 se a coluna faz parte da chave primária (numa chave de duas colunas, 1 e 2).

Repare que o id aparece com notnull 0, mesmo sendo a chave primária: no SQLite, um INTEGER PRIMARY KEY nunca fica NULL (ele ganha um número sozinho), então não precisa da regra escrita. No terminal, com .mode box, a mesma pergunta sobre os clientes fica assim:

sqlite> .mode box
sqlite> PRAGMA table_info(clientes);
┌─────┬────────────┬─────────┬─────────┬────────────┬────┐
│ cid │    name    │  type   │ notnull │ dflt_value │ pk │
├─────┼────────────┼─────────┼─────────┼────────────┼────┤
│ 0   │ id         │ INTEGER │ 0       │            │ 1  │
│ 1   │ nome       │ TEXT    │ 1       │            │ 0  │
│ 2   │ email      │ TEXT    │ 0       │            │ 0  │
│ 3   │ cidade     │ TEXT    │ 0       │            │ 0  │
│ 4   │ estado     │ TEXT    │ 0       │            │ 0  │
│ 5   │ nascimento │ TEXT    │ 0       │            │ 0  │
│ 6   │ vip        │ INTEGER │ 1       │ 0          │ 0  │
└─────┴────────────┴─────────┴─────────┴────────────┴────┘

O PRAGMA dentro de um SELECT

Os PRAGMAs que devolvem uma tabela também existem como funções de tabela: o mesmo nome com pragma_ na frente, usado no FROM. Assim você pode filtrar, ordenar e juntar o resultado como o de qualquer consulta. Quais colunas de clientes aceitam ficar vazias?

SELECT name, type
FROM pragma_table_info('clientes')
WHERE "notnull" = 0 AND pk = 0;

pragma_table_info('clientes')

A função de tabela. O nome da tabela vai entre aspas simples, como um texto.

"notnull"

A coluna se chama notnull, que também é uma palavra do SQL (NOT NULL). As aspas duplas dizem ao SQLite "isto é o nome de uma coluna". É um dos poucos lugares onde aspas duplas são o certo.

pk = 0

Deixa de fora a chave primária.

SELECT name, "notnull", pk
FROM pragma_table_info('pedidos');

SELECT name, "notnull", pk
FROM pragma_table_info('pedidos')
WHERE "notnull" = 0 AND pk = 0;

É o entregue_em: um pedido que ainda não foi entregue não tem data de entrega (veja IS NULL).

2. Índices: index_list e index_info

O index_list mostra os índices de uma tabela (aula Índices). A Loja Aurora não criou nenhum índice à mão, mas a tabela itens_pedido tem um que o SQLite criou sozinho:

PRAGMA index_list(itens_pedido);
PRAGMA index_info(sqlite_autoindex_itens_pedido_1);

sqlite_autoindex_itens_pedido_1

Um índice automático (autoindex). A tabela tem a chave primária de duas colunas, PRIMARY KEY (pedido_id, produto_id), e o SQLite precisa de um índice para conferir rápido se um par já existe.

unique = 1

O índice não aceita valores repetidos.

origin = pk

De onde ele veio: pk (chave primária), u (um UNIQUE) ou c (um CREATE INDEX seu).

index_info(…)

As colunas do índice, na ordem: pedido_id e depois produto_id.

Um índice criado com CREATE INDEX aparece com origin igual a c:

CREATE INDEX idx_produtos_categoria ON produtos (categoria_id);
PRAGMA index_list(produtos);

3. Chaves estrangeiras: foreign_keys e foreign_key_check

Você viu na aula As Garantias ACID que a conferência de chaves estrangeiras vem desligada no SQLite. O foreign_keys lê e muda isso. No editor, o guia a liga ao carregar a Loja Aurora:

PRAGMA foreign_keys;
PRAGMA foreign_key_list(produtos);

O 1 quer dizer "ligado". O foreign_key_list mostra as chaves estrangeiras de uma tabela: o categoria_id de produtos aponta (to) para o id de categorias, e o fornecedor_id, para o id de fornecedores. Agora o terminal, onde ela vem desligada:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys;
0
sqlite> INSERT INTO pedidos (id, cliente_id, funcionario_id, data, frete) VALUES (91, 99, 2, '2026-10-18', 0);
sqlite> PRAGMA foreign_key_check;
pedidos|91|clientes|1
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_key_check;
pedidos|91|clientes|1
sqlite> DELETE FROM pedidos WHERE id = 91;
sqlite> PRAGMA foreign_key_check;
sqlite> .quit
$ sqlite3 loja.db "PRAGMA foreign_keys;"
0                                « desligada de novo: vale só para a conexão

PRAGMA foreign_keys; → 0

Desligada. Por isso o pedido do cliente 99, que não existe, entrou sem erro.

PRAGMA foreign_key_check;

Procura no banco inteiro linhas que quebram alguma chave estrangeira. Respondeu: na tabela pedidos, a linha 91 aponta para um registro de clientes que não existe (o 1 é o número da chave estrangeira dentro de pedidos). Funciona mesmo com a conferência desligada.

PRAGMA foreign_keys = ON;

Liga a conferência. Repare que ligar não conserta o que já entrou: o foreign_key_check continua achando o pedido 91. A conferência vale só para os comandos daqui em diante.

(nada)

Depois do DELETE, o foreign_key_check não acha mais nada: o banco está consistente.

0, de novo

O foreign_keys não fica gravado no arquivo: vale para a conexão, do momento em que você liga até fechar o sqlite3. Todo programa precisa ligá-lo de novo, logo depois de abrir o banco.

4. O diário: journal_mode e o modo WAL

Na aula de ACID, você viu o loja.db-journal, o diário que guarda como as páginas eram antes de a transação mexer nelas. O journal_mode mostra e muda o jeito de manter esse diário:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode;
delete
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> UPDATE produtos SET estoque = estoque - 1 WHERE id = 3;
sqlite> .shell ls -l loja.db*
-rw-r--r-- 1 ana ana 45056 out 18 11:20 loja.db
-rw-r--r-- 1 ana ana 32768 out 18 11:20 loja.db-shm
-rw-r--r-- 1 ana ana  4152 out 18 11:20 loja.db-wal
sqlite> .quit
$ ls -l loja.db*
-rw-r--r-- 1 ana ana 45056 out 18 11:20 loja.db
$ sqlite3 loja.db "PRAGMA journal_mode;"
wal

delete

O modo padrão: o diário (-journal) é criado a cada transação e apagado no fim dela.

PRAGMA journal_mode = WAL;

Liga o modo WAL (write-ahead log, "registro antes da escrita"). O PRAGMA responde com o modo que ficou valendo: wal.

.shell ls -l loja.db*

O .shell roda um comando do terminal sem sair do sqlite3. Com o banco aberto em WAL, aparecem dois arquivos novos: o -wal, onde as mudanças novas são anotadas antes de irem para o banco, e o -shm, um índice que os programas usam para se organizar.

(depois do .quit)

Quando o último programa fecha o banco, o SQLite passa tudo do -wal para o loja.db e apaga os dois arquivos.

wal, de novo

Diferente do foreign_keys, o modo WAL fica gravado no arquivo: quem abrir o banco depois já o encontra em WAL.

No modo WAL, quem lê não atrapalha quem escreve, e vice-versa (a "foto" da contadora na aula ACID), e as gravações costumam ser mais rápidas. É o modo recomendado para quase todo programa que tem mais de um usuário ao mesmo tempo. Para voltar ao modo padrão:

$ sqlite3 loja.db "PRAGMA journal_mode = DELETE;"
delete

$ sqlite3
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA journal_mode;
memory
sqlite> PRAGMA journal_mode = WAL;
memory

5. A versão do esquema: user_version

Bancos mudam com o tempo: hoje a Ana acrescenta a coluna telefone aos clientes, no mês que vem uma tabela de cupons. Um programa que abre o banco precisa saber em que "versão" ele está, para saber o que ainda falta mudar. O user_version é um número guardado no cabeçalho do arquivo só para isso. O SQLite não usa esse número para nada: ele é seu.

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA user_version;
0
sqlite> PRAGMA user_version = 1;
sqlite> .quit
$ sqlite3 loja.db "PRAGMA user_version;"
1

Começa em 0. A Ana pôs 1, saiu, e o número continuou lá: fica gravado no arquivo, como o modo WAL. A ideia é que cada mudança no esquema venha junto com o aumento da versão; o programa lê o user_version ao abrir e, se for menor que o esperado, roda as mudanças que faltam.

No editor, o número também funciona, mas só dura até o fim do Rodar, porque o banco é recriado a cada vez:

PRAGMA user_version;
PRAGMA user_version = 3;
PRAGMA user_version;

6. A saúde e o tamanho: integrity_check, page_size e page_count

O integrity_check ("conferência de integridade") lê o banco inteiro e confere se a estrutura do arquivo está correta: se cada índice bate com a sua tabela, se nenhuma página está estragada. Quando está tudo certo, a resposta é uma palavra só:

PRAGMA integrity_check;
PRAGMA quick_check;

O quick_check ("conferência rápida") faz quase tudo o que o integrity_check faz, mas pula a comparação dos índices com as tabelas: num banco grande, termina bem mais rápido. Se algo estiver errado, os dois listam os problemas em vez do ok. No editor, o resultado sempre será ok, porque o banco acabou de ser criado na memória. Onde esses PRAGMAs servem de verdade é num arquivo: depois de um travamento, depois de copiar o banco de um pendrive, ou para conferir um backup (aula Backup).

O arquivo do banco é dividido em blocos de tamanho fixo, as páginas. O page_size diz o tamanho de cada uma, em bytes, e o page_count quantas o banco tem. Multiplicando os dois, dá o tamanho do arquivo:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA page_size;
4096
sqlite> PRAGMA page_count;
11
sqlite> .quit
$ ls -l loja.db
-rw-r--r-- 1 ana ana 45056 out 18 11:20 loja.db       « 4096 × 11 = 45056

No editor, o banco em memória foi criado com outro tamanho de página:

PRAGMA page_size;
PRAGMA page_count;

Para saber quantas dessas páginas estão vazias, esperando dados novos, existe o freelist_count. Ele e o VACUUM, que devolve esse espaço, são o assunto da próxima aula, VACUUM e ANALYZE.

7. Quais PRAGMAs fazem sentido onde

8. Formas diferentes de fazer

A forma pragma_…() é a mais flexível: dá para juntar o resultado com outras tabelas e filtrar com WHERE. A forma PRAGMA …; é a mais curta para uma olhada rápida.

Quando dá errado

Os PRAGMAs têm uma pegadinha séria: na maior parte dos erros, não aparece mensagem nenhuma.

PRAGMA foreign_key = ON;
PRAGMA tabela_que_nao_existe;

PRAGMA table_info(cliente);

BEGIN;
PRAGMA foreign_keys = OFF;
PRAGMA foreign_keys;
COMMIT;

Nos outros bancos

Referência rápida

A lista completa está na referência PRAGMAs.

Exercícios

Missão final: a versão 2 do esquema

-- 1. acrescente a coluna telefone em clientes
-- 2. mude o user_version para 2

SELECT (SELECT count(*) FROM pragma_table_info('clientes')
        WHERE name = 'telefone') AS tem_telefone,
       user_version AS versao
FROM pragma_user_version;

ALTER TABLE clientes ADD COLUMN telefone TEXT;
PRAGMA user_version = 2;

SELECT (SELECT count(*) FROM pragma_table_info('clientes')
        WHERE name = 'telefone') AS tem_telefone,
       user_version AS versao
FROM pragma_user_version;

A consulta do fim usa duas funções de PRAGMA: pragma_table_info conta se existe a coluna telefone, e pragma_user_version lê a versão. No terminal, a Ana faria o mesmo no loja.db, e as duas mudanças ficariam gravadas. Melhor ainda: dentro de um BEGIN … COMMIT, para que a coluna e a versão mudem juntas, ou nenhuma das duas.

Resumo

• Um PRAGMA lê ou muda o banco, não os dados: PRAGMA nome; lê, PRAGMA nome = valor; muda, PRAGMA nome(tabela); pergunta sobre uma tabela. É só do SQLite.
• table_list, table_info, index_list e foreign_key_list mostram a estrutura, em SQL; com pragma_…() no FROM, dá para filtrar com WHERE.
• foreign_keys vem desligado no terminal e vale só para a conexão: ligue a cada vez, fora de transação. foreign_key_check acha o que já entrou quebrado.
• journal_mode = WAL e user_version ficam gravados no arquivo; no banco em memória do editor, o WAL é recusado e a versão some no próximo Rodar.
• integrity_check e quick_check respondem ok quando o arquivo está saudável; page_size × page_count = tamanho do arquivo.
• PRAGMA com nome errado não dá erro: depois de mudar um, leia de volta.