Ajuste de PRAGMA do SQLite

Introdução

Neste laboratório, você explorará o ajuste de PRAGMA do SQLite para otimizar o desempenho e a confiabilidade do banco de dados. Você aprenderá como configurar aspectos-chave do comportamento do SQLite usando as instruções PRAGMA. Abordaremos a configuração do modo de journal, a ativação de verificações de chave estrangeira (foreign key), a execução de verificações de integridade e o ajuste do tamanho do cache.

Criar um Banco de Dados e Configurar o Modo de Journal

Nesta etapa, você criará um banco de dados SQLite e configurará seu modo de journal. O modo de journal controla como o SQLite lida com as transações e garante a integridade dos dados.

Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.

Crie um banco de dados SQLite chamado test.db e entre no shell do SQLite usando o seguinte comando:

sqlite3 test.db

Este comando cria o arquivo de banco de dados test.db (se ele não existir) e abre a ferramenta de linha de comando do SQLite. Você verá um prompt como este:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Agora, vamos configurar o modo de journal. O SQLite oferece vários modos de journal, incluindo DELETE, TRUNCATE, PERSIST, MEMORY, WAL e OFF. WAL (Write-Ahead Logging) oferece um bom equilíbrio entre desempenho e confiabilidade.

Execute o seguinte comando SQL para definir o modo de journal como WAL:

PRAGMA journal_mode=WAL;

Este comando configura o banco de dados para usar Write-Ahead Logging. WAL melhora a concorrência e o desempenho, escrevendo as alterações em um arquivo WAL separado antes de aplicá-las ao banco de dados.

Para verificar o modo de journal, execute:

PRAGMA journal_mode;

Saída Esperada:

wal

Isso confirma que o modo de journal está definido como WAL.

Habilitar Suporte a Chaves Estrangeiras

Nesta etapa, você habilitará o suporte a chaves estrangeiras em seu banco de dados SQLite. As chaves estrangeiras impõem relacionamentos entre tabelas e ajudam a manter a integridade dos dados.

Dentro do shell do SQLite (se você saiu na etapa anterior, reconecte-se com sqlite3 test.db), execute o seguinte comando:

PRAGMA foreign_keys = ON;

Este comando habilita a aplicação de chaves estrangeiras para a conexão atual do banco de dados. Observe que você deve habilitar as chaves estrangeiras para cada nova conexão ao banco de dados.

Para verificar se o suporte a chaves estrangeiras está habilitado, execute:

PRAGMA foreign_keys;

Saída Esperada:

1

Esta saída confirma que o suporte a chaves estrangeiras está habilitado.

Criar Tabelas com Relacionamento de Chave Estrangeira

Agora que o suporte a chaves estrangeiras está habilitado, vamos criar duas tabelas com um relacionamento de chave estrangeira para demonstrar sua funcionalidade.

Execute os seguintes comandos SQL para criar uma tabela users e uma tabela orders:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Esses comandos criam duas tabelas:

• users: Armazena informações do usuário com as colunas id (chave primária) e name.
• orders: Armazena informações do pedido com as colunas id (chave primária), user_id (chave estrangeira referenciando users.id) e amount.

A cláusula FOREIGN KEY (user_id) REFERENCES users(id) estabelece um relacionamento entre a tabela orders e a tabela users. Ela garante que o user_id na tabela orders deve existir na coluna id da tabela users.

Testar Restrição de Chave Estrangeira

Nesta etapa, você testará a restrição de chave estrangeira para ver como ela impede que dados inválidos sejam inseridos no banco de dados.

Primeiro, insira um usuário na tabela users:

INSERT INTO users (id, name) VALUES (1, 'Alice');

Este comando insere um novo usuário com id 1 e name 'Alice' na tabela users.

Agora, tente inserir um pedido na tabela orders com um user_id que não existe na tabela users:

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

Como as verificações de chave estrangeira estão habilitadas, este comando falhará com uma mensagem de erro:

Error: FOREIGN KEY constraint failed

Isso demonstra que a restrição de chave estrangeira está funcionando corretamente, impedindo que você crie um pedido para um usuário inexistente.

Em seguida, insira um pedido válido com o user_id existente:

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

Este comando terá sucesso porque o user_id 1 existe na tabela users.

Realizar uma Verificação de Integridade

Nesta etapa, você realizará uma verificação de integridade em seu banco de dados SQLite para garantir que não haja inconsistências ou erros.

Execute o seguinte comando dentro do shell SQLite:

PRAGMA integrity_check;

Este comando verifica todo o banco de dados em busca de quaisquer inconsistências ou erros. Se o banco de dados estiver íntegro, a saída será ok.

Saída Esperada:

ok

Se o banco de dados estiver corrompido, a saída conterá mensagens de erro indicando a natureza e a localização da corrupção.

Ajustar o Tamanho do Cache e Sair

Nesta etapa final, você ajustará o tamanho do cache usado pelo SQLite e, em seguida, sairá do shell SQLite.

O tamanho do cache determina quanta memória o SQLite usa para armazenar dados acessados recentemente. Aumentar o tamanho do cache pode melhorar o desempenho, especialmente para cargas de trabalho com muitas leituras.

Execute o seguinte comando para definir o tamanho do cache para 4MB (4096 KB):

PRAGMA cache_size = 4096;

Este comando define o tamanho do cache para 4MB para a conexão atual do banco de dados.

Para verificar o tamanho do cache, execute:

PRAGMA cache_size;

Saída Esperada:

4096

Finalmente, saia do shell SQLite:

.exit

Este comando fecha a conexão com o banco de dados e retorna você para a linha de comando.

Resumo

Neste laboratório, você explorou o ajuste de PRAGMA do SQLite. Você aprendeu como configurar o modo de journal para melhorar o desempenho e a confiabilidade, habilitar o suporte a chaves estrangeiras (foreign key) para impor a integridade dos dados, criar tabelas com relacionamentos de chaves estrangeiras, testar restrições de chaves estrangeiras, realizar verificações de integridade para identificar a corrupção do banco de dados e ajustar o tamanho do cache para otimizar o uso da memória. Essas habilidades fornecem uma base sólida para trabalhar com bancos de dados SQLite de forma eficaz.