ワークショップの登録ルールを保護する

はじめに

REPAIR CIRCLE は、実践的な修理ワークショップを開催しています。受付では、すでに予約を記録し、実際の参加者一覧を表示できます。しかし、データベースはまだ重複登録や不正な参照を拒否しません。この実習の目標は、動作している受付を引き続き使える状態に保ち、登録の関係をデータベースで強制することです。

基本的なターミナル操作と、MySQL の SELECT、INSERT、UPDATE の基礎知識が必要です。制約、取り消せる試行に使うトランザクション、ALTER TABLE はここで説明します。MySQL と架空のワークショップデータは準備済みです。全編を通して Terminal を使います。提供されたクライアントは、このコースの MySQL データベースに接続します。

指定された登録の追加と、registrations の必要な構造変更を行い、SQL を rules.sql に保存できます。元の ID、人物、セッション名、グループ人数、メモ、提供されたクライアントを保全してください。1 人が異なるセッションに参加してもよく、別人が同じ名前でもかまいません。このプロジェクトでは定員制限や同時実行時の座席割り当ては扱いません。

受付を使って重複の問題を確かめる

このステップでは、通常の予約を記録して実際の関係を読み取り、不正なレコードを残さずに不足しているルールを確かめます。Terminal から始めます。cd はディレクトリを移動します。誤ったディレクトリに入った場合も、この絶対パスで戻ってください。

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

./desk は、このディレクトリにある提供済みの登録クライアントです。./ は現在のディレクトリを意味します。roster は実際の MySQL テーブルを読み取り、あらかじめ計算した答えを表示するものではありません。

./desk roster

元の登録が 2 件あるはずです。登録 501 は、人物 101 の Mara Lin をセッション 11 の Lamp Repair に結び付け、座席数は 1、メモは Window seat です。登録 502 は、人物 102 の Alex Kim をセッション 12 の Bike Patch に結び付け、座席数は 2、メモは Bring partner です。この元の関係と値を保ってください。

新しい依頼は、人物 104 の Sam Reed をセッション 11 に 1 席で登録するものです。book は、人物 ID、イベント ID、グループ人数をこの順番で受け取ります。この正当な予約を保存します。

./desk book 104 11 1

./desk roster

3 件目の登録は Sam のもので、Mara のものではありません。両者とも Lamp Repair に参加していても別の登録です。クライアントは、データベースに保存された ID を返します。この新しい ID を自分で選んだり、覚えたりする必要はありません。

./db は、このコースの MySQL クライアント接続を開きます。イメージ内の無関係な mysql コマンドではなく、こちらを使ってください。--table は読みやすい表形式の出力を指定し、-e は引用符内の SQL を実行して接続を閉じます。引用符は、空白とセミコロンをまとめて 1 つの引数にします。2 つの親テーブルを読み取ります。

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

人物 102 と 103 はどちらも Alex Kim という名前です。ID が異なるため、別人です。名前を登録の一意な識別情報にしてはいけません。3 つのテーブルには次の関係があります。

業務上、(participant_id,session_id) の組ごとに登録は 1 件だけで、座席数は正、親の ID は存在する必要があります。既存の予約が参照している人物・セッションの ID は、削除や変更を防ぐ必要があります。使われていないセッションは、必要に応じて引き続き削除できます。

スキーマを保護する前に、現在不足している重複防止ルールを試します。トランザクションは、1 つの接続でのデータ変更をひとまとめにします。START TRANSACTION は試行を開始し、ROLLBACK は INSERT を保存せず取り消します。次の 1 つのコマンドは、Mara の Lamp Repair への 2 件目の予約を意図的に試し、その組の件数を数えてから取り消します。複数行にまたがる引用符も、1 つのシェル引数を構成します。

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

登録は引き続き 3 件のはずです。通常のコマンドでパスを間違えた場合は、絶対パスの cd で戻ります。ls でこのディレクトリのファイルを一覧表示できます。入力ミスから復旧するために、人物を削除したり、登録テーブルを空にしたり、データベースをリセットしたりしないでください。

関係を強制し、正当な予約を保つ

このステップでは、1 つのスキーマ変更を保存して既存のテーブルに適用し、正当な予約は引き続き利用でき、不正な依頼は拒否されることを確かめます。同じ Terminal のディレクトリから始めます。

制約とは、書き込みに適用するデータベースのルールです。./desk 以外のクライアントからの書き込みにも適用されます。複合 UNIQUE 制約は、同じ人物の別セッションへの参加を禁止せず、組の重複を拒否します。FOREIGN KEY は、参照列を親の主キーに結び付けます。RESTRICT は、その親を参照する予約がある間、削除や ID の変更を拒否します。予約を自動削除するものではありません。

CHECK (seats > 0) はゼロと負の値を拒否します。SQL の NULL は値の欠落を表します。結果が不明となる CHECK は、それだけでは NULL を拒否しません。そのため、座席数と両方の参照には NOT NULL も必要です。MODIFY は列の型を再指定し、この要件を加えます。提供された 3 つのテーブルは、すでにこれらの関係を強制するストレージエンジン InnoDB を使っています。

ALTER TABLE は既存のテーブルを変更します。MySQL はスキーマ変更を暗黙的にコミットします。この ALTER を試行用トランザクションで囲んでも、ROLLBACK で元に戻るとは考えないでください。ここでは、有効な既存のレコードに対して 1 つの ALTER 文を使い、ID とメモを保全します。

rules.sql には、registrations に対する通常の ALTER TABLE 文だけを、データベース名による修飾を付けずに保存してください。USE、シェルコマンド、他のオブジェクトの変更は、このファイルの範囲外です。トリガーの追加や親テーブルの定義変更は行いません。Linux シェルでは、> が保存先ファイルを置き換え、<<'SQL' が内容をそのまま扱うヒアドキュメントを開始します。最後の単独行の 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 入力として渡します。1 回だけ適用します。

./db < rules.sql

成功時には何も出力されません。ファイル名を間違えた場合は、ls で確認してパスを修正し、再試行します。この 1 つの ALTER に構文エラーがあれば、MySQL は拒否します。保存したブロック全体を修正してから再試行してください。ALTER が成功した後は、制約がすでに存在するので再適用しません。2 回目の実行で制約の重複エラーが出ても、データが失われたわけではありません。

実際の定義を読みます。SHOW CREATE TABLE は現在のテーブルを示し、\G は定義全体を読みやすくするために結果を縦方向に表示します。

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

3 つの参照・数量列の NOT NULL、2 列の UNIQUE キー、両方の FOREIGN KEY の関係、正の座席数を求める CHECK を確認します。名前や書式が異なっても、関係は一致する必要があります。

次に、通常のクライアントで重複する依頼を繰り返します。このコマンドは、ERROR 1062 を含むデータベースの拒否によって失敗する想定です。新しい行を作成してはいけません。

./desk book 101 11 1

これは重複キーによる拒否であり、サーバーの切断ではありません。参加者一覧は引き続き利用できます。

./desk roster

実際の受付に対する、後で取り消すデータトランザクションで、許される 2 種類の識別情報の組み合わせを確かめます。Mara は Bike Patch にも参加でき、もう 1 人の Alex Kim（103）は Alex 102 と同じセッションに参加できます。1 を超える正のグループ人数も正当です。

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

試行では、別々の識別情報を持つ 5 行が表示されます。ロールバックで削除されるのは、新しく追加した試行用の 2 行だけです。先に行ったスキーマ変更や、コミット済みの Sam の依頼は取り消されません。

代表的な 3 種類の拒否される書き込みを、直接確かめます。以下の各コマンドは失敗する想定です。これらには修復すべきトランザクションも、取り消すべき成功した変更もありません。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

元の 3 つのセッションがすべて残り、実際の登録はちょうど 3 件、元の 2 件と Sam の新しい依頼です。互換性のある別の受付にも同じ構造変更を行えるよう、rules.sql を保管してください。これは 1 回限りのスキーマ移行で、何度適用しても同じ結果になるインポートではありません。通常の接続では外部キーが強制されます。その検査を無効にすることは、このタスクの範囲外です。この制約は通常のアプリケーションの書き込みを保護するものであり、意図的に強制を無効にする管理者を防ぐものではありません。

まとめ

受付を引き続き使える状態に保ちながら、登録の関係に関するルールを実際の MySQL 制約に移しました。元の予約とメモを保全し、同名の別人や複数セッションへの参加という正当な組み合わせを受け入れ、重複と不正な参照を拒否し、参照されているレコードが削除されないようにしました。