Proteger as regras de inscrição em oficinas

Introdução

A REPAIR CIRCLE organiza oficinas práticas de conserto. Seu posto de inscrições já consegue registrar uma reserva e exibir a lista real de inscritos. No entanto, o banco de dados ainda não rejeita inscrições duplicadas nem referências inválidas. O objetivo é manter esse posto funcional enquanto o banco de dados passa a garantir os relacionamentos das inscrições.

Você precisa saber navegar pelo terminal e ter conhecimentos básicos de SELECT, INSERT e UPDATE no MySQL. Restrições, transações usadas para testes reversíveis e ALTER TABLE são explicados aqui. O MySQL e os dados sintéticos das oficinas estão preparados. Use Terminal durante todo o lab; os clientes fornecidos se conectam ao banco de dados MySQL deste curso.

Você pode adicionar a inscrição solicitada e alterar a estrutura necessária de registrations, salvando seu SQL em rules.sql. Preserve os IDs originais, as pessoas, os títulos das sessões, os tamanhos dos grupos, as notas e os clientes fornecidos. Uma pessoa pode participar de sessões diferentes; pessoas diferentes podem ter o mesmo nome. Este projeto não limita a capacidade nem trata da alocação concorrente de vagas.

Usar o posto e demonstrar uma duplicata

Nesta etapa, você registrará uma reserva normal, consultará seus relacionamentos reais e demonstrará a regra ausente sem deixar um registro incorreto para trás. Comece em Terminal. cd muda o diretório; use novamente este caminho absoluto se entrar no diretório errado:

cd /home/labex/project/sign-up-desk

./desk é o cliente de inscrições fornecido neste diretório. ./ significa o diretório atual. roster consulta as tabelas reais do MySQL; ele não exibe uma resposta calculada previamente:

./desk roster

Espere duas inscrições originais. A inscrição 501 relaciona a pessoa 101, Mara Lin, à sessão 11, Lamp Repair, com uma vaga e a nota Window seat. A inscrição 502 relaciona a pessoa 102, Alex Kim, à sessão 12, Bike Patch, com duas vagas e Bring partner. Preserve esses relacionamentos e valores originais.

Uma nova solicitação pede a inscrição da pessoa 104, Sam Reed, na sessão 11 com uma vaga. book recebe o ID da pessoa, o ID do evento e o tamanho do grupo, nessa ordem. Salve esta reserva legítima:

./desk book 104 11 1

./desk roster

A terceira inscrição pertence a Sam, não a Mara, embora ambos participem de Lamp Repair. O cliente retorna o ID salvo pelo banco de dados. Você não precisa escolher nem memorizar esse novo ID.

./db abre a conexão do cliente MySQL deste curso. Use-o em vez do comando mysql da imagem, que não está relacionado ao curso. --table solicita uma saída tabular legível; -e executa o SQL entre aspas e fecha a conexão. As aspas mantêm espaços e pontos e vírgulas juntos em um único argumento. Consulte as duas tabelas pai:

./db --table -e "SELECT id,name FROM participants ORDER BY id; SELECT id,title FROM sessions ORDER BY id;"

As pessoas 102 e 103 se chamam Alex Kim. São pessoas diferentes porque seus IDs são diferentes. Um nome não deve se tornar uma identidade única de inscrição. As três tabelas têm estes relacionamentos:

O negócio permite apenas uma inscrição por par (participant_id,session_id), com uma quantidade positiva de vagas e IDs existentes nas tabelas pai. Uma reserva existente deve impedir a exclusão ou a alteração do ID da pessoa/sessão que ela referencia. Sessões sem inscrições ainda podem ser removidas quando necessário.

Antes de proteger o esquema, teste a regra de duplicidade que ainda falta. Uma transação agrupa alterações de dados em uma conexão. START TRANSACTION inicia o teste; ROLLBACK desfaz seu INSERT em vez de salvá-lo. O comando único a seguir tenta deliberadamente uma segunda reserva para Mara em Lamp Repair, conta esse par e depois desfaz a alteração. As aspas em várias linhas continuam formando um único argumento do shell:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,11,1);
SELECT COUNT(*) AS matching_registrations FROM registrations WHERE participant_id=101 AND session_id=11;
ROLLBACK;"

A contagem é 2: o banco de dados aceitou uma duplicata. Essa é uma falha real, não um erro de conexão. O teste usa INSERT, não ALTER. Seu rollback mantém intactas a reserva original de Mara e a nova solicitação de Sam:

./desk roster

Ainda deve haver três inscrições. Se um comando comum tiver um caminho incorreto, retorne com o cd absoluto. Use ls para listar os arquivos deste diretório. Não remova uma pessoa, não esvazie a tabela de inscrições nem reinicialize o banco de dados para se recuperar de um erro de digitação.

Garantir os relacionamentos e manter reservas legítimas

Nesta etapa, você salvará uma alteração de esquema, aplicará essa alteração à tabela existente e comprovará que reservas úteis continuam funcionando enquanto solicitações inválidas são rejeitadas. Comece no mesmo diretório em Terminal.

Uma restrição é uma regra do banco de dados aplicada às operações de escrita, incluindo aquelas feitas por um cliente diferente de ./desk. Uma restrição UNIQUE composta rejeita um par repetido sem proibir a mesma pessoa em outra sessão. Uma FOREIGN KEY relaciona uma coluna de referência à chave primária de uma tabela pai. RESTRICT recusa a exclusão ou uma alteração de ID enquanto uma reserva referencia esse registro pai; ele não exclui a reserva automaticamente.

CHECK (seats > 0) rejeita zero e valores negativos. NULL em SQL significa um valor ausente; um CHECK cujo resultado é desconhecido não rejeita NULL por si só. Portanto, NOT NULL também é necessário para a quantidade de vagas e ambas as referências. MODIFY declara novamente o tipo da coluna e acrescenta essa exigência. As três tabelas fornecidas já usam InnoDB, o mecanismo de armazenamento que garante esses relacionamentos.

ALTER TABLE altera uma tabela existente. O MySQL confirma implicitamente alterações de esquema: não envolva este ALTER em uma transação de teste esperando que ROLLBACK o desfaça. Usamos uma única instrução ALTER sobre registros já válidos, preservando seus IDs e suas notas.

Salve em rules.sql apenas instruções ALTER TABLE comuns para registrations, sem qualificar o nome da tabela com o nome do banco de dados; USE, comandos do shell e alterações em outros objetos estão fora do escopo desse arquivo. Não adicione triggers nem altere as definições das tabelas pai. No shell Linux, > substitui o arquivo de destino, e <<'SQL' inicia um here-document literal. Cole o bloco completo, incluindo seu SQL final sozinho em uma linha. Não digite este comando cat em um prompt do MySQL:

cat > rules.sql <<'SQL'
ALTER TABLE registrations
  MODIFY participant_id INT NOT NULL,
  MODIFY session_id INT NOT NULL,
  MODIFY seats INT NOT NULL,
  ADD CONSTRAINT one_person_event UNIQUE (participant_id, session_id),
  ADD CONSTRAINT known_person FOREIGN KEY (participant_id)
    REFERENCES participants(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT known_event FOREIGN KEY (session_id)
    REFERENCES sessions(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT positive_group CHECK (seats > 0);
SQL

Se o shell continuar esperando, digite a linha final SQL que está faltando. Se você digitou o arquivo incorretamente, repita todo o bloco para substituí-lo. Os nomes das restrições identificam essas regras nas mensagens de erro do MySQL; seus nomes exatos não são o resultado de negócio.

< fornece o arquivo salvo como entrada SQL do cliente. Aplique-o uma vez:

./db < rules.sql

Nenhuma saída é esperada em caso de sucesso. Se o nome do arquivo estiver incorreto, use ls, corrija o caminho e tente novamente. Se este ALTER único tiver um erro de sintaxe, o MySQL o rejeitará; corrija o bloco completo salvo antes de tentar novamente. Depois que o ALTER for bem-sucedido, não o aplique novamente: suas restrições já existem. Um erro de restrição duplicada em uma segunda execução não significa que seus dados foram perdidos.

Consulte a definição real. SHOW CREATE TABLE informa a tabela atual; \G exibe seu resultado verticalmente para facilitar a leitura da definição completa:

./db -e "SHOW CREATE TABLE registrations\G"

Procure as três colunas de referência/quantidade com NOT NULL, a chave UNIQUE de duas colunas, os dois relacionamentos FOREIGN KEY e o CHECK de quantidade positiva de vagas. Os nomes ou a formatação podem ser diferentes; os relacionamentos devem corresponder.

Agora repita a solicitação duplicada usando o cliente normal. Espera-se que este comando falhe com uma recusa do banco de dados contendo ERROR 1062. Ele não deve criar outra linha:

./desk book 101 11 1

A falha é uma recusa por chave duplicada, não um servidor desconectado. A lista de inscritos continua utilizável:

./desk roster

Comprove os dois casos permitidos de identidade em uma transação descartável de dados no posto real. Mara pode participar de Bike Patch, e o outro Alex Kim (103) pode participar da mesma sessão que Alex 102. Um tamanho positivo de grupo maior que um também é legítimo:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,12,3),(103,12,5);
SELECT participant_id,session_id,seats FROM registrations ORDER BY participant_id,session_id;
ROLLBACK;"

O teste mostra cinco linhas com identidades separadas. O rollback remove apenas as duas novas linhas de teste. Ele não desfaz a alteração anterior de esquema nem a solicitação de Sam já confirmada.

Verifique diretamente três exemplos representativos de escritas rejeitadas. Espera-se que cada comando abaixo falhe; esses comandos não contêm uma transação para corrigir nem uma alteração bem-sucedida para desfazer. O MySQL rejeita a instrução inválida individual. 9999 não existe na tabela pai fornecida:

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(9999,11,1);"

Espere ERROR 1452, uma referência a um registro pai desconhecido.

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(103,11,NULL);"

Espere ERROR 1048, uma quantidade de vagas ausente. Substituir NULL por 0 violaria o CHECK, em vez de produzir uma reserva válida.

./db -e "DELETE FROM sessions WHERE id=11;"

Espere ERROR 1451: Lamp Repair ainda tem reservas, portanto a sessão permanece. Isso demonstra por que excluir o registro pai ou eliminar inscrições em cascata seria um atalho incorreto.

Por fim, comprove que uma sessão sem reservas ainda pode ser removida em um teste reversível:

./db --table -e "
START TRANSACTION;
DELETE FROM sessions WHERE id=13;
SELECT id,title FROM sessions ORDER BY id;
ROLLBACK;"

Apenas as sessões 11 e 12 aparecem dentro dessa transação. O rollback restaura a sessão sem inscrições. Inspecione a entrega final:

./db --table -e "SELECT id,title FROM sessions ORDER BY id;"

./desk roster

As três sessões originais permanecem, e restam exatamente três inscrições reais: as duas originais e a nova solicitação de Sam. Mantenha rules.sql para realizar a mesma alteração estrutural em outro posto compatível. Trata-se de uma migração de esquema executada uma única vez, não de uma importação idempotente. As conexões padrão verificam as chaves estrangeiras; desativar suas verificações está fora desta tarefa. As restrições protegem as escritas comuns da aplicação; elas não impedem um administrador de desativar deliberadamente a verificação das regras.

Resumo

Você manteve o posto de inscrições funcional enquanto transferia suas regras de relacionamento para restrições reais do MySQL. Preservou reservas e notas originais, aceitou combinações legítimas de pessoas com o mesmo nome e de participação em sessões diferentes, rejeitou duplicatas e referências inválidas e impediu a remoção de registros referenciados.