Dados relacionais, SQL e transações
Você vai executar relacional.py com Python 3 e sqlite3 da biblioteca padrão. O script cria um banco numa pasta temporária e abre duas conexões reais. As lojas A e B usam ids iguais de propósito. Não conecte esse exercício a dados de produção: a força do caso está em controlar cada linha e conseguir prever as relações e falhas.
Prepare um desenho com clientes, pedidos, produtos, itens e recibos. Escreva a chave de cada tabela e o significado de cada relação antes de executar. Ao final, entregue o desenho, a consulta correta e os resultados das falhas provocadas. A solução inclui testes de rollback e concorrência; executar uma inserção nominal não é suficiente para demonstrar esses contratos.
SQLAo terminar esta aula
- Prove restrições com violações controladas.
- Parâmetros separam dados de sintaxe, não substituem autorização.
- Interprete isolamento pela ordem das transações.
Antes de continuar: Leitura: Dados relacionais, SQL e transações
Construa identidades e relações sem atalhos
FundamentosObserve que produtos não guarda o nome do cliente e clientes não guarda o saldo do produto. Cada tabela tem uma responsabilidade. A chave composta garante que A/C1 e B/C1 são entidades distintas, embora a parte textual do id seja igual. Itens precisa conservar tenant para referenciar tanto o pedido quanto o produto. No modelo deste laboratório, um mesmo SKU aparece no máximo uma vez por pedido; a quantidade agrega unidades desse produto. Se o domínio permitisse linhas separadas para o mesmo SKU com preços diferentes, essa chave exigiria revisão.
Cada conexão executa PRAGMA foreign_keys=ON. Não basta escrever FOREIGN KEY no texto e presumir que a configuração de execução está correta. O teste com C99 tenta inserir um pedido que aponta para cliente ausente e exige IntegrityError. O check saldo>=0 protege uma propriedade do estoque, enquanto quantidade>0 protege uma propriedade do item. Esses mecanismos não decidem quem pode alterar a loja; a autorização pertence a uma fronteira adicional.
Compare uma ligação correta com a ambígua
FundamentosLeia a consulta antes de rodar: pedidos se liga a clientes por tenant e cliente; itens se liga a pedidos por tenant e pedido. O parâmetro seleciona somente A e a tupla esperada contém Ana, I2 e quantidade dois. Copie a consulta numa versão de investigação e remova temporariamente c.tenant=p.tenant. Conte as linhas e observe nomes. Restaure a condição e explique por que DISTINCT não seria uma correção: as duas pessoas são valores diferentes e a relação continua incorreta.
O placeholder ? transmite tenant como dado. O teste "A' OR 1=1 --" deve retornar vazio, porque não existe uma loja com esse nome; o texto não vira parte da sintaxe SQL. Não construa a consulta concatenando entrada do usuário. Parâmetros resolvem esse problema de representação, mas não autenticam a pessoa nem validam sua permissão. Na aplicação, tenant deve vir de identidade confiável; aqui ele é um argumento sintético para estudar o contrato da consulta.
Falhe entre saldo e recibo, depois repita
FundamentosA função reservar valida quantidade, inicia BEGIN IMMEDIATE e procura recibo pela chave composta. Se já existe, compara o conteúdo normalizado: mesma operação retorna repetido; quantidade diferente gera erro. Se não existe, o UPDATE inclui saldo>=quantidade e o código verifica rowcount. Essa condição liga leitura e mudança numa instrução, evitando separar "verifiquei saldo" de "reduzi saldo" com uma janela desnecessária de concorrência.
Use falhar=True na primeira tentativa. O update acontece, uma exceção é lançada e o bloco de tratamento executa rollback antes de propagar a falha. As asserções confirmam saldo três e ausência do recibo FALHA. Em seguida OP1 reserva duas unidades e deixa saldo um. Repetir OP1 com o mesmo conteúdo conserva saldo um; reutilizar OP1 com outra quantidade é recusado. A unicidade do recibo e o vínculo ao conteúdo protegem problemas diferentes: uma chave única sozinha ainda pode aceitar a operação errada.
Observe duas conexões e interprete a evidência
FundamentosA conexão b inicia uma leitura e vê saldo três. A conexão a reserva e confirma saldo um. Enquanto b mantém a transação, continua vendo três; depois do COMMIT de b, uma nova consulta vê um. Esse é o experimento de snapshot WAL. Não descreva o resultado como cache da aplicação: as conexões consultaram o banco e a diferença veio do contrato de isolamento. A solução não ativa leitura de alterações não confirmadas.
Depois a inicia BEGIN IMMEDIATE sem concluir; b tenta fazer o mesmo e recebe OperationalError porque existe um escritor ativo. O timeout curto mantém o experimento local rápido, mas não define uma política de retry de produção. Entregue os eventos na ordem e declare SQLite/Python observados. A rubrica exige ausência de mistura entre lojas, rollback completo, replay vinculado e explicação do snapshot. Não extrapole esses testes para vários servidores, pagamento externo, backup ou controle de acesso que não foram implementados.
Exercício aplicado
Duas lojas usam cliente C1 e pedido P1. Uma consulta junta apenas id e mistura pessoas; uma reserva reduz estoque, falha antes de gravar recibo e deixa saldo incorreto. Modele identidade composta, corrija a consulta e torne saldo/recibo uma operação atômica.
- Antes da solução, desenhe entidades e dependências; diga quais identificadores são locais ao tenant.
- Escreva joins que incluem tenant e teste um valor malicioso como dado parametrizado.
- Provoque falha entre update e recibo; confirme rollback dos dois efeitos.
- Abra duas conexões, observe snapshot WAL e escritor concorrente; explique o alcance do isolamento.
Abrir resolução comentada
A identidade é (tenant,id), portanto cada referência e join precisa conservar os dois componentes. Clientes, pedidos, produtos e itens armazenam fatos diferentes; nome não é repetido em cada item. Foreign keys e checks tornam parte do contrato verificável no banco, mas não substituem a autenticação da aplicação.
BEGIN IMMEDIATE delimita a reserva: update condicional, recibo vinculado ao conteúdo e commit. A falha provoca rollback, enquanto replay com a mesma chave e conteúdo devolve o registro sem reduzir saldo novamente. A segunda conexão lê um snapshot até encerrar sua transação. WAL permite esse leitor durante o commit do escritor, mas SQLite continua admitindo um escritor por vez.
import sqlite3, tempfile, os, json
with tempfile.TemporaryDirectory() as pasta:
arquivo=os.path.join(pasta, 'loja.sqlite')
a=sqlite3.connect(arquivo, isolation_level=None, timeout=0.1)
b=sqlite3.connect(arquivo, isolation_level=None, timeout=0.1)
try:
for c in (a,b):
c.execute('PRAGMA foreign_keys=ON')
a.execute('PRAGMA journal_mode=WAL')
a.executescript('''
CREATE TABLE clientes(tenant TEXT NOT NULL,id TEXT NOT NULL,nome TEXT NOT NULL,PRIMARY KEY(tenant,id));
CREATE TABLE pedidos(tenant TEXT NOT NULL,id TEXT NOT NULL,cliente TEXT NOT NULL,PRIMARY KEY(tenant,id),FOREIGN KEY(tenant,cliente) REFERENCES clientes(tenant,id));
CREATE TABLE produtos(tenant TEXT NOT NULL,sku TEXT NOT NULL,saldo INTEGER NOT NULL CHECK(saldo>=0),PRIMARY KEY(tenant,sku));
CREATE TABLE itens(tenant TEXT NOT NULL,pedido TEXT NOT NULL,sku TEXT NOT NULL,quantidade INTEGER NOT NULL CHECK(quantidade>0),PRIMARY KEY(tenant,pedido,sku),FOREIGN KEY(tenant,pedido) REFERENCES pedidos(tenant,id),FOREIGN KEY(tenant,sku) REFERENCES produtos(tenant,sku));
CREATE TABLE recibos(tenant TEXT NOT NULL,chave TEXT NOT NULL,conteudo TEXT NOT NULL,PRIMARY KEY(tenant,chave));
INSERT INTO clientes VALUES('A','C1','Ana'),('B','C1','Bia');
INSERT INTO pedidos VALUES('A','P1','C1'),('B','P1','C1');
INSERT INTO produtos VALUES('A','I2',3),('B','I2',7);
INSERT INTO itens VALUES('A','P1','I2',2),('B','P1','I2',1);
''')
consulta='''SELECT p.tenant,p.id,c.nome,i.sku,i.quantidade FROM pedidos p
JOIN clientes c ON c.tenant=p.tenant AND c.id=p.cliente
JOIN itens i ON i.tenant=p.tenant AND i.pedido=p.id WHERE p.tenant=?'''
assert a.execute(consulta,('A',)).fetchall()==[('A','P1','Ana','I2',2)]
# O parametro e dado, mesmo se parecer uma instrucao SQL.
assert a.execute(consulta,("A' OR 1=1 --",)).fetchall()==[]
def saldo(c):return c.execute("SELECT saldo FROM produtos WHERE tenant='A' AND sku='I2'").fetchone()[0]
def reservar(tenant,sku,q,chave,falhar=False):
if type(q) is not int or q<=0: raise ValueError('quantidade invalida')
conteudo=json.dumps([tenant,sku,q],separators=(',',':'))
a.execute('BEGIN IMMEDIATE')
try:
r=a.execute('SELECT conteudo FROM recibos WHERE tenant=? AND chave=?',(tenant,chave)).fetchone()
if r:
if r[0]!=conteudo:raise ValueError('chave com outro conteudo')
a.execute('COMMIT');return 'repetido'
cur=a.execute('UPDATE produtos SET saldo=saldo-? WHERE tenant=? AND sku=? AND saldo>=?',(q,tenant,sku,q))
if cur.rowcount!=1:raise ValueError('saldo insuficiente ou produto ausente')
if falhar:raise RuntimeError('falha antes do recibo')
a.execute('INSERT INTO recibos VALUES(?,?,?)',(tenant,chave,conteudo))
a.execute('COMMIT');return 'reservado'
except Exception:
a.execute('ROLLBACK');raise
try:reservar('A','I2',1,'FALHA',True)
except RuntimeError:pass
else:raise AssertionError('falha nao aconteceu')
assert saldo(a)==3
assert a.execute("SELECT count(*) FROM recibos WHERE chave='FALHA'").fetchone()[0]==0
# A leitura em WAL permanece no snapshot ate encerrar sua transacao.
b.execute('BEGIN');assert saldo(b)==3
assert reservar('A','I2',2,'OP1')=='reservado'
assert saldo(a)==1 and saldo(b)==3
b.execute('COMMIT');assert saldo(b)==1
assert reservar('A','I2',2,'OP1')=='repetido';assert saldo(a)==1
try:reservar('A','I2',1,'OP1')
except ValueError:pass
else:raise AssertionError('conteudo alterado aceito')
try:reservar('A','I2',2,'OP2')
except ValueError:pass
else:raise AssertionError('saldo insuficiente aceito')
a.execute('BEGIN IMMEDIATE')
try:b.execute('BEGIN IMMEDIATE')
except sqlite3.OperationalError:pass
else:raise AssertionError('dois escritores ativos')
finally:a.execute('ROLLBACK')
try:a.execute("INSERT INTO pedidos VALUES('A','P2','C99')")
except sqlite3.IntegrityError:pass
else:raise AssertionError('referencia inexistente aceita')
assert saldo(a)==1
print('joins, parametros, rollback, snapshot, replay e writer lock verificados')
print('Python sqlite:',sqlite3.sqlite_version)
finally:
b.close();a.close()
Como conferir seu resultado
- Toda identidade e referência mantém tenant.
- Join devolve apenas Ana para A, sem linhas de B.
- Falha antes do recibo conserva saldo e não deixa recibo parcial.
- Replay vincula chave ao conteúdo; conteúdo diferente e saldo insuficiente não alteram dados.
- Snapshot e bloqueio de escritor são observados por conexões reais.
Teste sua compreensão
Responda com suas palavras antes de abrir o comentário. Saber explicar uma decisão é parte do domínio.
1. Por que ativar foreign_keys em duas conexões?
Porque cada conexão precisa da configuração esperada.
O teste de referência ausente confirma a proteção real do ambiente.
2. Por que comparar o conteúdo de um recibo repetido?
Para impedir reutilizar a autorização de uma operação em outra.
Unicidade da chave não descreve sozinha qual operação foi aprovada.
3. Qual evidência demonstra rollback?
Saldo original e ausência de recibo após a falha.
Um erro impresso sozinho não comprova que a mudança parcial foi desfeita.
Seu progresso fica salvo neste navegador. Concluir a leitura não substitui demonstrar o domínio nos exercícios.
Referências e aprofundamento
Documentação oficial e trabalhos originais. As referências registram o escopo e as limitações para você conferir o que sustentam.
- Database Design2nd Edition: Normalization
Adrienne Watt / BCcampus • consulta: 2026-10-06
FundamentosDependências funcionais e anomalias de dados.
Limites: Livro aberto; o exemplo local ilustra dependências sem esgotar formas normais.
- SQLite: SELECT
SQLite • consulta: 2026-10-06
FundamentosJoins, filtros, ordenação e processamento de SELECT.
Limites: Dialeto SQLite; relações corretas dependem das chaves e predicados escolhidos.
- SQLite: Foreign Key Support
SQLite • consulta: 2026-10-06
FundamentosReferências e ativação de foreign_keys por conexão.
Limites: Restrições não substituem autorização do chamador.
- SQLite: Transaction
SQLite • consulta: 2026-10-06
FundamentosBEGIN, COMMIT, ROLLBACK e modos de transação.
Limites: Transação cobre banco local, não efeitos em serviços externos.
- SQLite: Isolation
SQLite • consulta: 2026-10-06
FundamentosSnapshot em WAL e serialização de escritores.
Limites: Contrato depende do modo e configuração; não extrapolar para outros bancos.
- Python: sqlite3
Python Software Foundation • consulta: 2026-10-06
PythonConexoes, placeholders e API sqlite3 padrão.
Limites: Documentação consultada3.14; runtime localPython3.14.4 e versaoSQLite registrada no teste.