Regeln für Workshopanmeldungen absichern

Einführung

REPAIR CIRCLE veranstaltet praktische Reparaturworkshops. Die Anmeldung kann bereits Buchungen erfassen und die tatsächliche Teilnehmerliste anzeigen. Die Datenbank lehnt jedoch doppelte Anmeldungen oder ungültige Verweise noch nicht ab. Ziel ist es, diese funktionierende Anmeldung nutzbar zu halten und zugleich die Datenbank die Beziehungen der Anmeldungen durchsetzen zu lassen.

Du benötigst grundlegende Kenntnisse der Navigation im Terminal sowie von SELECT, INSERT und UPDATE in MySQL. Constraints, Transaktionen für rückgängig zu machende Tests und ALTER TABLE werden hier erklärt. MySQL und synthetische Workshopdaten sind vorbereitet. Verwende durchgehend Terminal; die bereitgestellten Clients verbinden sich mit der MySQL-Datenbank dieses Kurses.

Du darfst die erforderliche Anmeldung hinzufügen und die notwendige Struktur von registrations ändern. Speichere dein SQL in rules.sql. Bewahre die ursprünglichen IDs, Personen, Veranstaltungstitel, Gruppengrößen, Notizen und bereitgestellten Clients. Eine Person darf verschiedene Veranstaltungen besuchen; verschiedene Personen dürfen denselben Namen haben. Dieses Projekt begrenzt weder die Kapazität noch behandelt es die gleichzeitige Vergabe von Plätzen.

Die Anmeldung verwenden und ein Duplikat aufdecken

In diesem Schritt erfasst du eine normale Buchung, liest ihre tatsächlichen Beziehungen aus und zeigst die fehlende Regel auf, ohne einen fehlerhaften Datensatz zu hinterlassen. Beginne in Terminal. cd wechselt das Verzeichnis; verwende diesen absoluten Pfad erneut, falls du in ein falsches Verzeichnis wechselst:

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

./desk ist der bereitgestellte Anmeldeclient in diesem Verzeichnis. ./ bedeutet das aktuelle Verzeichnis. roster liest die tatsächlichen MySQL-Tabellen; es zeigt keine vorberechnete Antwort an:

./desk roster

Erwartet werden zwei ursprüngliche Anmeldungen. Anmeldung 501 verbindet Person 101, Mara Lin, mit Veranstaltung 11, Lamp Repair, mit einem Platz und der Notiz Window seat. Anmeldung 502 verbindet Person 102, Alex Kim, mit Veranstaltung 12, Bike Patch, mit zwei Plätzen und Bring partner. Bewahre diese ursprünglichen Beziehungen und Werte.

Eine neue Anfrage bittet um einen Platz für Person 104, Sam Reed, in Veranstaltung 11. book nimmt die Personen-ID, die Veranstaltungs-ID und die Gruppengröße in dieser Reihenfolge entgegen. Speichere diese zulässige Buchung:

./desk book 104 11 1

./desk roster

Die dritte Anmeldung gehört Sam, nicht Mara, obwohl beide Lamp Repair besuchen. Der Client gibt die von der Datenbank gespeicherte ID zurück. Du musst diese neue ID weder auswählen noch auswendig lernen.

./db öffnet die MySQL-Clientverbindung dieses Kurses. Verwende es statt des davon unabhängigen Befehls mysql im Image. --table fordert eine lesbare Tabellenausgabe an; -e führt das SQL in Anführungszeichen aus und schließt die Verbindung. Die Anführungszeichen halten Leerzeichen und Semikolons in einem Argument zusammen. Lies die beiden übergeordneten Tabellen:

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

Die Personen 102 und 103 heißen beide Alex Kim. Sie sind unterschiedliche Personen, weil sich ihre IDs unterscheiden. Ein Name darf nicht zu einer eindeutigen Anmeldeidentität werden. Die drei Tabellen haben folgende Beziehungen:

Das Geschäft erlaubt nur eine Anmeldung pro Paar (participant_id,session_id), mit einer positiven Anzahl an Plätzen und vorhandenen übergeordneten IDs. Eine bestehende Buchung muss das Löschen oder Umbenennen der Personen-/Veranstaltungs-ID verhindern, auf die sie verweist. Ungenutzte Veranstaltungen dürfen bei Bedarf weiterhin entfernt werden.

Teste vor dem Absichern des Schemas die derzeit fehlende Duplikatregel. Eine Transaktion fasst Datenänderungen auf einer Verbindung zusammen. START TRANSACTION beginnt den Test; ROLLBACK macht dessen INSERT rückgängig, statt es zu speichern. Der folgende einzelne Befehl versucht absichtlich eine zweite Buchung für Mara in Lamp Repair, zählt dieses Paar und rollt sie anschließend zurück. Die mehrzeiligen Anführungszeichen bilden weiterhin ein einziges Shell-Argument:

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

Die Anzahl ist 2: Die Datenbank hat ein Duplikat akzeptiert. Dies ist eine tatsächliche Schwachstelle, kein Verbindungsfehler. Der Test verwendet INSERT, kein ALTER. Sein Rollback lässt Maras ursprüngliche Buchung und Sams neue Anfrage unverändert bestehen:

./desk roster

Es sollten weiterhin drei Anmeldungen vorhanden sein. Hat ein gewöhnlicher Befehl einen falschen Pfad, kehre mit dem absoluten cd zurück. Verwende ls, um die Dateien dieses Verzeichnisses aufzulisten. Entferne keine Person, leere nicht die Anmeldetabelle und setze die Datenbank nicht zurück, um einen Tippfehler zu beheben.

Beziehungen durchsetzen und zulässige Buchungen bewahren

In diesem Schritt speicherst du eine Schemaänderung, wendest sie auf die vorhandene Tabelle an und weist nach, dass sinnvolle Buchungen weiterhin funktionieren, während ungültige Anfragen abgelehnt werden. Beginne im selben Verzeichnis in Terminal.

Ein Constraint ist eine Datenbankregel, die auf Schreibzugriffe angewendet wird, auch auf solche über einen anderen Client als ./desk. Ein zusammengesetzter UNIQUE-Constraint lehnt ein wiederholtes Paar ab, ohne dieselbe Person in einer anderen Veranstaltung zu verbieten. Ein FOREIGN KEY verbindet eine Verweisspalte mit dem Primärschlüssel eines übergeordneten Datensatzes. RESTRICT verweigert das Löschen oder eine ID-Änderung, solange eine Buchung auf diesen Datensatz verweist; es löscht die Buchung nicht automatisch.

CHECK (seats > 0) lehnt den Wert 0 und negative Werte ab. SQL NULL bedeutet einen fehlenden Wert; ein CHECK, dessen Ergebnis unbekannt ist, lehnt NULL allein nicht ab. NOT NULL ist deshalb zusätzlich für die Plätze und beide Verweise notwendig. MODIFY gibt den Spaltentyp erneut an und ergänzt diese Anforderung. Alle drei bereitgestellten Tabellen verwenden bereits InnoDB, die Speicher-Engine, die diese Beziehungen durchsetzt.

ALTER TABLE ändert eine vorhandene Tabelle. MySQL bestätigt Schemaänderungen implizit per Commit: Umschließe dieses ALTER nicht mit einer Testtransaktion in der Erwartung, ROLLBACK würde es rückgängig machen. Wir verwenden eine einzelne ALTER-Anweisung für bereits gültige Datensätze und bewahren dabei deren IDs und Notizen.

Speichere nur gewöhnliche ALTER TABLE-Anweisungen für registrations ohne Datenbankqualifizierung in rules.sql; USE, Shell-Befehle und Änderungen an anderen Objekten liegen außerhalb des erlaubten Umfangs dieser Datei. Füge keine Trigger hinzu und ändere nicht die Definitionen der übergeordneten Tabellen. In der Linux-Shell ersetzt > die Zieldatei, und <<'SQL' beginnt ein wörtliches Here-Dokument. Füge den vollständigen Block ein, einschließlich seines abschließenden SQL allein in einer Zeile. Gib diesen cat-Befehl nicht an einer MySQL-Eingabeaufforderung ein:

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

Wenn die Shell weiter wartet, gib die fehlende abschließende Zeile SQL ein. Hast du dich in der Datei vertippt, wiederhole den ganzen Block, um sie zu ersetzen. Constraint-Namen kennzeichnen diese Regeln in MySQL-Fehlermeldungen; ihre genauen Namen sind nicht das fachliche Ergebnis.

< liefert die gespeicherte Datei als SQL-Eingabe des Clients. Wende sie einmal an:

./db < rules.sql

Bei Erfolg wird keine Ausgabe erwartet. War der Dateiname falsch, verwende ls, korrigiere den Pfad und versuche es erneut. Hat dieses einzelne ALTER einen Syntaxfehler, lehnt MySQL es ab; korrigiere vor dem erneuten Versuch den gespeicherten vollständigen Block. Sobald das ALTER erfolgreich ist, wende es nicht noch einmal an: Seine Constraints existieren bereits. Ein Fehler wegen eines doppelten Constraints bei einem zweiten Lauf bedeutet nicht, dass deine Daten verloren gegangen sind.

Lies die tatsächliche Definition. SHOW CREATE TABLE zeigt die aktuelle Tabelle an; \G stellt das Ergebnis vertikal dar, sodass die vollständige Definition leichter zu lesen ist:

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

Achte auf die drei NOT NULL-Spalten für Verweise/Anzahl, den zweispaltigen UNIQUE-Schlüssel, beide FOREIGN KEY-Beziehungen und den CHECK für eine positive Platzanzahl. Namen oder Formatierung dürfen abweichen; die Beziehungen müssen übereinstimmen.

Wiederhole nun die Duplikatanfrage mit dem normalen Client. Dieser Befehl soll mit einer Datenbankablehnung fehlschlagen, die ERROR 1062 enthält. Er darf keine weitere Zeile erzeugen:

./desk book 101 11 1

Der Fehler ist eine Ablehnung wegen eines doppelten Schlüssels, kein Verbindungsabbruch zum Server. Die Teilnehmerliste bleibt nutzbar:

./desk roster

Weise beide erlaubten Identitätsfälle in einer anschließend verworfenen Datentransaktion an der tatsächlichen Anmeldung nach. Mara darf Bike Patch besuchen, und der andere Alex Kim (103) darf dieselbe Veranstaltung wie Alex 102 besuchen. Eine positive Gruppengröße größer als eins ist ebenfalls zulässig:

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

Der Test zeigt fünf Zeilen mit getrennten Identitäten. Rollback entfernt nur seine beiden neuen Testzeilen. Es macht weder die frühere Schemaänderung noch Sams bereits per Commit gespeicherte Anfrage rückgängig.

Prüfe drei repräsentative abgelehnte Schreibzugriffe direkt. Jeder folgende Befehl soll fehlschlagen; diese Befehle enthalten weder eine zu reparierende Transaktion noch eine erfolgreiche Änderung, die rückgängig gemacht werden müsste. MySQL lehnt die einzelne ungültige Anweisung ab. 9999 ist in der bereitgestellten übergeordneten Tabelle nicht vorhanden:

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

Erwartet wird ERROR 1452, ein unbekannter Verweis auf einen übergeordneten Datensatz.

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

Erwartet wird ERROR 1048, eine fehlende Platzanzahl. NULL durch 0 zu ersetzen, würde den CHECK verletzen, statt eine gültige Buchung zu ergeben.

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

Erwartet wird ERROR 1451: Lamp Repair hat weiterhin Buchungen, deshalb bleibt die Veranstaltung erhalten. Dies zeigt, weshalb das Löschen des übergeordneten Datensatzes oder das kaskadierende Entfernen von Anmeldungen eine falsche Abkürzung wäre.

Weise zum Schluss in einem rückgängig zu machenden Test nach, dass eine Veranstaltung ohne Buchungen weiterhin entfernt werden kann:

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

Innerhalb dieser Transaktion erscheinen nur die Veranstaltungen 11 und 12. Rollback stellt die ungenutzte Veranstaltung wieder her. Prüfe die abschließende Übergabe:

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

./desk roster

Alle drei ursprünglichen Veranstaltungen bleiben erhalten, und es bleiben genau drei echte Anmeldungen: die beiden ursprünglichen und Sams neue Anfrage. Bewahre rules.sql für dieselbe Strukturänderung an einer weiteren kompatiblen Anmeldung auf. Es ist eine einmalige Schemamigration, kein idempotenter Import. Standardverbindungen erzwingen Fremdschlüssel; deren Prüfungen auszuschalten liegt außerhalb dieser Aufgabe. Die Constraints schützen gewöhnliche Anwendungsschreibzugriffe, nicht einen Administrator, der die Durchsetzung absichtlich deaktiviert.

Zusammenfassung

Du hast die Anmeldung nutzbar gehalten und ihre Beziehungsregeln in tatsächliche MySQL-Constraints überführt. Du hast ursprüngliche Buchungen und Notizen bewahrt, zulässige Kombinationen mit gleichnamigen Personen und über verschiedene Veranstaltungen hinweg akzeptiert, Duplikate und ungültige Verweise abgelehnt und das Entfernen referenzierter Datensätze verhindert.