Защитите правила регистрации на мастер-классы

Введение

REPAIR CIRCLE проводит практические мастер-классы по ремонту. Система регистрации уже позволяет записывать участников и показывать фактический список записей. Однако база данных пока не отклоняет повторные регистрации и некорректные ссылки. Цель — сохранить работоспособность этой системы и обеспечить соблюдение связей регистрации средствами самой базы данных.

Вам понадобятся базовые навыки навигации в терминале и базовые знания MySQL SELECT, INSERT и UPDATE. Ограничения, транзакции для обратимых пробных операций и ALTER TABLE объясняются здесь. MySQL и синтетические данные мастер-классов уже подготовлены. На протяжении всей работы используйте Terminal; предоставленные клиенты подключаются к базе данных MySQL этого курса.

Вы можете добавить требуемую регистрацию и изменить необходимую структуру registrations, сохранив SQL в rules.sql. Сохраните исходные ID, участников, названия занятий, размеры групп, заметки и предоставленные клиенты. Один человек может посещать разные занятия; у разных людей могут совпадать имена. Этот проект не ограничивает вместимость и не решает задачу одновременного распределения мест.

Используйте систему регистрации и выявите дубликат

На этом шаге вы сохраните обычную запись на занятие, изучите её фактические связи и продемонстрируете недостающее правило, не оставив некорректную запись. Начните во вкладке Terminal. cd меняет каталог; если вы перейдёте не в тот каталог, снова используйте этот абсолютный путь:

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

./desk — предоставленный клиент регистрации в этом каталоге. ./ означает текущий каталог. roster читает настоящие таблицы MySQL, а не показывает заранее подготовленный ответ:

./desk roster

Ожидаются две исходные регистрации. Регистрация 501 связывает участника 101, Mara Lin, с занятием 11, Lamp Repair, одним местом и заметкой Window seat. Регистрация 502 связывает участника 102, Alex Kim, с занятием 12, Bike Patch, двумя местами и заметкой Bring partner. Сохраните эти исходные связи и значения.

Поступил новый запрос: записать участника 104, Sam Reed, на занятие 11 с одним местом. book принимает ID человека, ID мероприятия и размер группы именно в таком порядке. Сохраните эту допустимую запись:

./desk book 104 11 1

./desk roster

Третья регистрация относится к Sam, а не к Mara, хотя оба посещают Lamp Repair. Клиент возвращает сохранённый базой данных ID. Выбирать или запоминать этот новый ID не требуется.

./db открывает клиентское соединение с MySQL этого курса. Используйте его вместо не связанной с курсом команды mysql из образа. --table запрашивает удобочитаемый табличный вывод; -e выполняет SQL в кавычках и закрывает соединение. Кавычки удерживают пробелы и точки с запятой в одном аргументе. Прочитайте две родительские таблицы:

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

Участников 102 и 103 обоих зовут Alex Kim. Это разные люди, поскольку их ID различаются. Имя не должно становиться уникальным идентификатором регистрации. Три таблицы связаны следующим образом:

Правила работы допускают только одну регистрацию для каждой пары (participant_id,session_id), с положительным числом мест и существующими родительскими ID. Существующая запись должна препятствовать удалению или изменению ID человека или занятия, на который она ссылается. Неиспользуемые занятия по-прежнему можно удалять при необходимости.

Прежде чем защитить схему, проверьте пока отсутствующее правило запрета дубликатов. Транзакция объединяет изменения данных в одном соединении. START TRANSACTION начинает пробную операцию; ROLLBACK отменяет её INSERT вместо сохранения. Следующая единая команда намеренно пробует второй раз записать Mara на Lamp Repair, подсчитывает записи этой пары, а затем откатывает изменение. Многострочный текст в кавычках по-прежнему образует один аргумент оболочки:

./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;"

Количество равно 2: база данных приняла дубликат. Это реальный недостаток, а не ошибка соединения. Пробная операция использует INSERT, а не ALTER. Её откат сохраняет исходную запись Mara и новый запрос Sam:

./desk roster

Должны по-прежнему оставаться три регистрации. Если в обычной команде указан неверный путь, вернитесь с помощью абсолютной команды cd. Используйте ls, чтобы вывести файлы этого каталога. Не удаляйте человека, не очищайте таблицу регистраций и не сбрасывайте базу данных ради исправления опечатки.

Обеспечьте соблюдение связей и сохраните допустимые записи

На этом шаге вы сохраните одно изменение схемы, примените его к существующей таблице и докажете, что допустимые записи по-прежнему работают, а некорректные запросы отклоняются. Начните в том же каталоге во вкладке Terminal.

Ограничение — это правило базы данных, применяемое к операциям записи, в том числе выполняемым через клиент, отличный от ./desk. Составное ограничение UNIQUE отклоняет повторную пару, не запрещая тому же человеку посещать другое занятие. FOREIGN KEY связывает ссылочный столбец с первичным ключом родительской таблицы. RESTRICT запрещает удаление или изменение ID, пока регистрация ссылается на эту родительскую запись; автоматически регистрацию оно не удаляет.

CHECK (seats > 0) отклоняет нулевые и отрицательные значения. SQL NULL означает отсутствие значения; CHECK, результат которого неизвестен, сам по себе не отклоняет NULL. Поэтому для числа мест и обеих ссылок также требуется NOT NULL. MODIFY повторно задаёт тип столбца и добавляет это требование. Все три предоставленные таблицы уже используют InnoDB — механизм хранения, обеспечивающий соблюдение этих связей.

ALTER TABLE меняет существующую таблицу. MySQL неявно фиксирует изменения схемы: не помещайте этот ALTER в пробную транзакцию в ожидании, что ROLLBACK его отменит. Мы используем одну инструкцию ALTER для уже допустимых записей, сохраняя их ID и заметки.

Для registrations сохраните в rules.sql только обычные инструкции ALTER TABLE без квалификации имени таблицы именем базы данных; USE, команды оболочки и изменения других объектов выходят за рамки этого файла. Не добавляйте триггеры и не меняйте определения родительских таблиц. В оболочке Linux > перезаписывает файл назначения, а <<'SQL' начинает буквальный here-document. Вставьте полный блок, включая завершающий SQL на отдельной строке. Не вводите эту команду cat в приглашении 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

Если оболочка продолжает ждать ввод, введите недостающую завершающую строку SQL. Если вы допустили опечатку в файле, повторите весь блок, чтобы перезаписать его. Имена ограничений позволяют распознавать эти правила в сообщениях об ошибках MySQL; их точные имена не являются требуемым рабочим результатом.

< передаёт сохранённый файл клиенту в качестве входного SQL. Примените его один раз:

./db < rules.sql

При успехе вывода не ожидается. Если имя файла указано неверно, используйте ls, исправьте путь и попробуйте снова. Если в этой единственной инструкции ALTER есть синтаксическая ошибка, MySQL отклонит её; перед повторной попыткой исправьте полный сохранённый блок. После успешного ALTER не применяйте его повторно: ограничения уже существуют. Ошибка дублирования ограничения при втором запуске не означает потерю данных.

Прочитайте фактическое определение. SHOW CREATE TABLE сообщает определение текущей таблицы; \G выводит результат вертикально, чтобы полное определение было удобнее читать:

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

Найдите три столбца ссылок и количества с NOT NULL, двухстолбцовый ключ UNIQUE, обе связи FOREIGN KEY и CHECK положительного числа мест. Имена или форматирование могут различаться; связи должны соответствовать требованиям.

Теперь повторите запрос-дубликат через обычный клиент. Ожидается, что эта команда завершится отказом базы данных, содержащим ERROR 1062. Она не должна создать ещё одну строку:

./desk book 101 11 1

Это отказ из-за дублирования ключа, а не потеря соединения с сервером. Список регистраций остаётся доступным:

./desk roster

Докажите допустимость обоих случаев идентификации в пробной транзакции данных в реальной системе регистрации. Mara может посетить Bike Patch, а другой Alex Kim (103) — то же занятие, что и Alex 102. Положительный размер группы больше единицы также допустим:

./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;"

Пробная операция показывает пять строк с отдельными идентификаторами. Откат удаляет только две её новые пробные строки. Он не отменяет ни предыдущее изменение схемы, ни уже зафиксированный запрос Sam.

Напрямую проверьте три характерных случая отклоняемых записей. Каждая команда ниже должна завершиться ошибкой; в этих командах нет транзакции, которую нужно восстанавливать, или успешного изменения, которое нужно отменять. MySQL отклоняет отдельную недопустимую инструкцию. 9999 отсутствует в предоставленной родительской таблице:

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

Ожидается ERROR 1452: ссылка на неизвестную родительскую запись.

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

Ожидается ERROR 1048: отсутствует число мест. Замена NULL на 0 нарушила бы CHECK, а не сделала запись допустимой.

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

Ожидается ERROR 1451: у Lamp Repair по-прежнему есть регистрации, поэтому занятие сохраняется. Это показывает, почему удаление родительской записи или каскадное удаление регистраций было бы неправильным обходным решением.

Наконец, докажите в обратимой пробной операции, что занятие без регистраций по-прежнему можно удалить:

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

Внутри этой транзакции отображаются только занятия 11 и 12. Откат восстанавливает неиспользуемое занятие. Проверьте итоговое состояние для передачи результата:

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

./desk roster

Все три исходных занятия сохранены, и остаются ровно три настоящие регистрации: две исходные и новый запрос Sam. Сохраните rules.sql для такого же структурного изменения в другой совместимой системе регистрации. Это однократная миграция схемы, а не идемпотентный импорт. Соединения по умолчанию обеспечивают соблюдение внешних ключей; отключение их проверок выходит за рамки задания. Ограничения защищают обычные операции записи приложения, но не от администратора, намеренно отключающего их применение.

Резюме

Вы сохранили работоспособность системы регистрации и перенесли правила её связей в настоящие ограничения MySQL. Вы сохранили исходные записи и заметки, разрешили допустимые сочетания для людей с одинаковыми именами и участия в разных занятиях, отклонили дубликаты и некорректные ссылки, а также защитили записи, на которые ссылаются регистрации, от удаления.