SQLite, Resolvendo Problemas Simples com um Banco Simples

Porque levantar um pesado sistema de banco de dados, criar databases, criar tabelas, configurar permissões acessos, configurar usuários, setar portas, rodar daemons só para resolver um problema simples de armazenamento?

O SQLite é um banquinho leve e simples para resolver de maneira simples problemas que realmente são simples.

Estrutura do SQLite

Algumas das vantagens do SQLite são:

• Instalação e configuração muito simples.
• Implementa boa parte do SQL-92.
• Transações ACID – Atomicidade, Consistência, Isolamento e Durabilidade.
• Dados ficam guardados em um único arquivo. Fácil de transportar, fazer backup e manusear.
• Suporta base de dados acima de 2 terabytes.
• Não depende de outras bibliotecas, é escrito em C puro.
• É um software livre e esta disponível sobre domínio público.

A instalação dele no Ubuntu ou em outro sistema debian-like é só:

sudo apt-get install sqlite

Uma vez instalado o SQLite, você pode chamar o prompt do sistema pelo comando sqlite3 (ou somente sqlite, se você instalou uma versão anterior). Chamando o sqlite seguido de um nome de arquivo ele gravará o banco naquele arquivo.

$ sqlite teste.db
SQLite version 2.8.17
Enter “.help” for instructions
sqlite>

Dentro do console você pode usar comandos SQL para criar e manipular as entidades do banco ou usar os comandos que começam com um ponto para acessar comandos do SQLite.

Os comandos que uso mais são:

• .database – lista os bancos e arquivos abertos.
• .tables – lista as tabelas do banco.
• .read coisas.sql - lê comandos de SQL dentro da arquivo chamado coisas.sql.
• .mode column – formata a exibição padrão para colunas.
• .headers on – formata a exibição padrão para exibir os nomes dos campos.
• .help - Ajuda

Prática 1 – Agenda de Telefones

Vamos agora brincar um pouquinho com o SQLite fazendo alguns testes práticos dentro do próprio SQLite.

Vamos passar para o SQLite esse comando de SQL para criarmos uma simples agenda de números telefónicos.

CREATE TABLE agenda(
   id INTEGER PRIMARY KEY,
   nome VARCHAR(30),
   tel VARCHAR(20)
);

Podemos fazer isso de dentro do SQLite simplesmente digitando linha a linha do comando SQL:

$ sqlite telefones.db
SQLite version 2.8.17
Enter “.help” for instructions
sqlite> CREATE TABLE agenda(
…> id INTEGER PRIMARY KEY,
…> nome VARCHAR(30),
…> tel VARCHAR(20)
…> );
sqlite>

Um jeito prático de práticar SQL é guardar os comandos dentro de um arquivo, por exemplo telefones.sql. Depois você entra no SQLite e abre o arquivo com .read telefones.sql.

Vamos fazer isso com um comando de SQL maior, que vai destruir qualquer tabela que se chame agenda, criar uma tabela agenda e inserir alguns dados nela.

DROP TABLE agenda;
CREATE TABLE agenda(
   id INTEGER PRIMARY KEY,
   nome VARCHAR(30),
   tel VARCHAR(20)
);
INSERT INTO agenda(nome,tel) VALUES ('Central de Transplantes', '0800-8832323');
INSERT INTO agenda(nome,tel) VALUES ('Disque-Silêncio', '3452-6927');
INSERT INTO agenda(nome,tel) VALUES ('Bombeiros', '193');
INSERT INTO agenda(nome,tel) VALUES ('Ambulancia', '192');
INSERT INTO agenda(nome,tel) VALUES ('Policia Militar', '190');
INSERT INTO agenda(nome,tel) VALUES ('Defesa Civil', '199');
INSERT INTO agenda(nome,tel) VALUES ('Disque-Cidade Limpa','0800-851531');

obs1: Não precisamos inserir os dados de id porque eles são chaves primárias e vão se preencher sozinhos, sempre cuidando para que duas chaves nunca sejam iguais. obs2: Esse números são referentes ao Ceará. Para maiores informações consulte a Telelistas.

Depois abrimos o arquivo pelo SQLite e já podemos fazer algumas consultas:

$ sqlite telefones.db
SQLite version 2.8.17
Enter “.help” for instructions
sqlite> .read telefones.sql
sqlite> SELECT * FROM agenda;
1|Central de Transplantes|0800-8832323
2|Disque-Silêncio|3452-6927
3|Bombeiros|193
4|Ambulancia|192
5|Policia Militar|190
6|Defesa Civil|199
7|Disque-Cidade Limpa|0800-851531

Para selecionar somente alguns campos da tabela faça:

sqlite> SELECT nome, tel FROM agenda;
Central de Transplantes|0800-8832323
Disque-Silencio|3452-6927
Bombeiros|193
Ambulancia|192
Policia Militar|190
Defesa Civil|199
Disque-Cidade Limpa|0800-851531

Selecionar somente alguns campos da tabela e ordenar a saida pelo nome, faça:

sqlite> SELECT nome,tel FROM agenda ORDER BY nome;
Ambulancia|192
Bombeiros|193
Central de Transplantes|0800-8832323
Defesa Civil|199
Disque-Cidade Limpa|0800-851531
Disque-Silêncio|3452-6927
Policia Militar|190

Descobrir de quem eh o telefone 0800-8832323:

sqlite> SELECT nome FROM agenda WHERE tel == ’0800-8832323′;
Central de Transplantes

Descobrir que nomes sao da forma disque-alguma-coisa:

sqlite> SELECT nome FROM agenda WHERE nome LIKE ‘disque%’;
Disque-Silêncio
Disque-Cidade Limpa

Retirando da tabela agenda o nome e telefone do Disque-Silêncio. Depois vamos dar uma select para ver a agenda e verificar que essa entrada nao esta mais la.

sqlite> DELETE FROM agenda WHERE nome == ‘Disque-Silêncio’;
sqlite> select * from agenda;
1|Central de Transplantes|0800-8832323
3|Bombeiros|193
4|Ambulancia|192
5|Policia Militar|190
6|Defesa Civil|199
7|Disque-Cidade Limpa|0800-851531

Prática 2 – Fórum

Nesse exemplo vamos ver os TRIGGERS.

Eu disse Triggers e não tigers!

Trigger quer dizer gatilho. Com triggers você configura ações a serem tomadas quando certos eventos com ocorrem. Eventos que disparam uma trigger podem ser uma inserção, deleção ou atualização de alguma tabela.

Vamos criar uma tabela simples para guardar comentários no estilo de threads em fóruns.

DROP TABLE thread;
CREATE TABLE thread(
	id INTEGER PRIMARY KEY,
	pai INTEGER,
	apelido VARCHAR(20),
	texto TEXT,
	respostas INTEGER
);

O campo pai vai referenciar qual thread esta thread está referenciando. Vamos convencionar que um pai igual a zero corresponde a uma thread raiz. O campo respostas contara quantas outras threads apontam para esta thread como pai somado com o numero de respostas desses filhos. Vamos convencionar que toda thread começa com o campo respostas igual a zero.

Vamos criar dois triggers. O trigger comentario_novo vai ser disparado quando alguém adicionar uma novo comentario, ele vai procurar o pai daquela thread e adicionar o número de respostas. Mas só isso não basta. O trigger comentario_alterado vai ser disparado quando alguém mexer na campo respostas de uma thread, ele vai procurar o pai daquela thread e adicionar seu número de respostas.

CREATE TRIGGER comentario_novo AFTER INSERT ON thread
BEGIN
   UPDATE thread SET respostas = respostas + 1 WHERE id = new.pai;
END;

CREATE TRIGGER comentario_alterado AFTER UPDATE OF respostas ON thread
BEGIN
   UPDATE thread SET respostas = respostas + 1 WHERE id = new.pai;
END;

obs: Esse exemplo não cobre todas as possibilidades. Para manter o problema mais simples estou convencionando que não se pode deletar uma thread ou mudar o pai de uma thread.

Veja só o que acontece quando colocamos alguns comentários no banco:

INSERT INTO thread(pai, apelido, texto, respostas) VALUES (0, "Jose", "Oi. Alguem conheçe algum site legal?", 0);
INSERT INTO thread(pai, apelido, texto, respostas) VALUES (1, "Maria", "Tem o eupodiatamatando.com :D", 0);
INSERT INTO thread(pai, apelido, texto, respostas) VALUES (2, "Jose", "Valeu Maria!", 0);
INSERT INTO thread(pai, apelido, texto, respostas) VALUES (1, "Pedro", "Eu gosto do br-linux.org", 0);

sqlite> SELECT * FROM THREAD;
1|0|Jose|Oi. Alguem conheçe algum site legal?|3
2|1|Maria|Tem o eupodiatamatando.com :D|1
3|2|Jose|Valeu Maria!|0
4|1|Pedro|Eu gosto do br-linux.org|0

Voilá! Houve um efeito em cascata. Quando disparamos um trigger comentario_novo ele vai modificar uma thread e vai disparar um trigger comentario_alterado, que por sua vez vai disparar outro comentario_alterado até chegar na thread raíz.

Trigger são legais para se colocar alguma lógica dentro do banco e fora do código da aplicação principal. A divisão da lógica entre sua aplicação e o banco vai depender da facilidade de se implementar algo com SQL ou com seu código-fonte e de bom senso.

Prática 3 – Misture tudo e coloque Python!

Python não podia ficar de fora disso tudo Para instalar o que você precisa:

# apt-get install python-sqlite

Esse é um programa para abrir nossa agenda de telefones do primeiro exemplo:

#!/usr/bin/env python
import sqlite

conexao = sqlite.connect('telefones.db')
cursor = conexao.cursor()
cursor.execute('SELECT * FROM agenda;')
for i in cursor:
   print i[1], i[2]

Em cada iteração de i ele vai ser uma tripla com os campos da sua tabela. Mandamos imprimir os campos 1 e 2 que são o nome e telefone.

Isso vai imprimir isso na tela:

Central de Transplantes 0800-8832323
Disque-Silêncio 3452-6927
Bombeiros 193
Ambulancia 192
Policia Militar 190
Defesa Civil 199
Disque-Cidade Limpa 0800-851531

Vale a pena dar uma chance pro SQLite.

Dê uma olhada nos preços de livros sobre SQL ou livros sobre banco de dados. Buscapé.