mitmario.dev

Die Datenbasis

PHP Sandbox 4 Min Lesezeit 3 BeispieleLektion 2 von 8

Ohne Angabe, wem ein Lesezeichen gehört, sieht jeder alle, auch die der anderen. Sobald sich jemand anmeldet, braucht jedes Lesezeichen einen Besitzer, und der steht in einer zweiten Tabelle. Zwei Tabellen mit einem Verweis kennst du aus dem Abschnitt über Datenbanken. Dort waren es Notizen und Verfasser, hier sind es Nutzer und Lesezeichen.

Zwei Tabellen, weil es zwei Dinge sind

Zwei Tabellen und ein Verweis
<?php

declare(strict_types=1);

// Zwei Tabellen, und die zweite zeigt auf die erste. So gehoeren die
// Lesezeichen im Projekt jemandem.

$db = new PDO("sqlite::memory:", null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$db->exec("CREATE TABLE nutzer (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
)");

$db->exec("CREATE TABLE lesezeichen (
    id INTEGER PRIMARY KEY,
    nutzer_id INTEGER NOT NULL REFERENCES nutzer(id),
    titel TEXT NOT NULL
)");

$db->exec("INSERT INTO nutzer (name) VALUES ('mia'), ('tom')");
$db->exec("INSERT INTO lesezeichen (nutzer_id, titel) VALUES
    (1, 'Astro'), (1, 'PHP-Handbuch'), (2, 'Toms Werkstatt')");

// Die Spalten stehen ausgeschrieben da, nicht als *: Bei einem JOIN heissen
// zwei von ihnen id, und mit * ueberlebt nur die letzte.
$alle = $db->query("SELECT lesezeichen.titel AS titel, nutzer.name AS besitzer
    FROM lesezeichen
    JOIN nutzer ON nutzer.id = lesezeichen.nutzer_id
    ORDER BY lesezeichen.id")->fetchAll();

foreach ($alle as $zeile) {
    echo str_pad($zeile["titel"], 16), "gehört ", $zeile["besitzer"], "\n";
}

// Und die Frage, die die Anwendung jedes Mal stellt: was gehoert mia?
$mias = $db->prepare("SELECT titel FROM lesezeichen WHERE nutzer_id = :b");
$mias->execute([":b" => 1]);

echo "\nMia hat ", count($mias->fetchAll()), " Lesezeichen.\n";

Nutzer und Lesezeichen sind zwei Arten von Dingen, und jede bekommt ihre eigene Tabelle. In eine einzige Tabelle gequetscht, bräuchtest du Spalten, die meistens leer bleiben, also beim Nutzer die Spalten des Lesezeichens und beim Lesezeichen die des Nutzers.

Zusammengehalten werden beide von einer einzigen Spalte, nutzer_id. Sie steht beim Lesezeichen und enthält die id aus der Tabelle nutzer. Das ist ein Fremdschlüssel, so wie bei den Verfassern im Abschnitt über Datenbanken.

Im ersten Beispiel stehen zwei Abfragen. Die erste holt mit JOIN beide Seiten zusammen, denn sie soll die Namen ausgeben. Die zweite holt alles, was zu einer nutzer_id gehört, und diese Frage stellt deine Anwendung ständig. Dafür braucht es kein JOIN, nur ein WHERE.

Die Spalten sind dabei ausgeschrieben statt als Stern. Bei einem JOIN heißen zwei von ihnen id, und mit SELECT * überlebt im Array davon nur die letzte.

Was REFERENCES wirklich tut

Ohne Fremdschlüssel geht alles durch
<?php

declare(strict_types=1);

// REFERENCES steht in der Tabelle, aber SQLite haelt sich nur daran, wenn
// man es darum bittet. Und die Bitte gilt je Verbindung.

function baueDatenbank(bool $mitPruefung): PDO
{
    $db = new PDO("sqlite::memory:", null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    if ($mitPruefung) {
        $db->exec("PRAGMA foreign_keys = ON");
    }

    $db->exec("CREATE TABLE nutzer (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
    $db->exec("CREATE TABLE lesezeichen (
        id INTEGER PRIMARY KEY,
        nutzer_id INTEGER NOT NULL REFERENCES nutzer(id),
        titel TEXT NOT NULL
    )");
    $db->exec("INSERT INTO nutzer (id, name) VALUES (1, 'mia')");

    return $db;
}

foreach ([false, true] as $mitPruefung) {
    $db = baueDatenbank($mitPruefung);

    echo $mitPruefung ? "Mit PRAGMA foreign_keys = ON:  " : "So wie SQLite startet:         ";
    echo "PRAGMA sagt ", $db->query("PRAGMA foreign_keys")->fetchColumn(), ", ";

    try {
        // Nutzer 99 gibt es nicht. Trotzdem?
        $db->exec("INSERT INTO lesezeichen (nutzer_id, titel) VALUES (99, 'Geisterzeichen')");
        echo "das Geisterzeichen ist drin.\n";
    } catch (PDOException $fehler) {
        echo $fehler->getMessage(), "\n";
    }
}

REFERENCES nutzer(id) in der Spaltendefinition sieht aus wie ein Versprechen, dass hier nur eine Zahl steht, die es drüben wirklich gibt. Im zweiten Beispiel hält SQLite dieses Versprechen erst einmal gar nicht. Ohne weiteres Zutun schreibt es ein Lesezeichen für Nutzer 99 anstandslos in die Tabelle, obwohl es Nutzer 99 nicht gibt.

Der Schalter dafür heißt PRAGMA foreign_keys = ON, und er gilt je Verbindung, nicht je Datenbank. Willst du ihn haben, setzt du ihn also gleich hinter dem Verbindungsaufbau, und zwar jedes Mal. Mit ihm meldet sich derselbe Einfügeversuch als FOREIGN KEY constraint failed.

Auch ohne den Schalter steht REFERENCES nicht umsonst da. Wer den Code liest, sieht daran die Beziehung, und andere Datenbanken halten sich von sich aus daran. In diesem Projekt bleibt es bei der Deklaration ohne Schalter. Der Code gibt nie eine erfundene Nummer weiter, denn sie kommt immer aus der Sitzung.

Die Tabellen legt die Klasse an

CREATE TABLE IF NOT EXISTS, dreimal gestartet
<?php

declare(strict_types=1);

// Die Speicherklasse legt ihre Tabellen bei jedem Start an. Das geht nur
// wegen der drei Woerter IF NOT EXISTS, und es geht nur so weit.

$datei = __DIR__ . "/probe.db";
@unlink($datei);

$anlegen = "CREATE TABLE IF NOT EXISTS lesezeichen (
    id INTEGER PRIMARY KEY,
    titel TEXT NOT NULL
)";

foreach ([1, 2, 3] as $start) {
    $db = new PDO("sqlite:" . $datei, null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $db->exec($anlegen);
    $db->exec("INSERT INTO lesezeichen (titel) VALUES ('Nummer $start')");

    echo "Start ", $start, ": Tabelle da, Zeilen: ",
        $db->query("SELECT count(*) FROM lesezeichen")->fetchColumn(), "\n";
}

// Und jetzt der Tag, an dem eine Spalte dazukommen soll. IF NOT EXISTS
// hilft dabei nicht: Die Tabelle gibt es ja, also passiert gar nichts.
$db = new PDO("sqlite:" . $datei, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("CREATE TABLE IF NOT EXISTS lesezeichen (
    id INTEGER PRIMARY KEY,
    titel TEXT NOT NULL,
    url TEXT NOT NULL
)");

$spalten = array_column($db->query("PRAGMA table_info(lesezeichen)")->fetchAll(PDO::FETCH_ASSOC), "name");
echo "\nNach dem erweiterten CREATE: ", implode(", ", $spalten), "\n";

$db->exec("ALTER TABLE lesezeichen ADD COLUMN url TEXT NOT NULL DEFAULT ''");

$spalten = array_column($db->query("PRAGMA table_info(lesezeichen)")->fetchAll(PDO::FETCH_ASSOC), "name");
echo "Nach ALTER TABLE:            ", implode(", ", $spalten), "\n";

@unlink($datei);

In Lektion 14.2 durfte CREATE TABLE IF NOT EXISTS beliebig oft laufen. Eine Tabelle, die es schon gibt, lässt es in Ruhe. Starten muss es trotzdem jemand, auf einem frisch aufgesetzten Server also ein Einrichtungsskript, das von Hand läuft, bevor die erste Anfrage kommt. In einem kleinen Projekt kann die Speicherklasse ihre beiden Tabellen auch einfach bei jedem Start selbst anlegen, ohne dass vorher jemand von Hand ein Skript ausführen muss.

Im dritten Beispiel läuft dieselbe Datei dreimal hintereinander. Die Zahl der Zeilen steigt von 1 auf 3, weil keiner der Starts die Tabelle neu anlegt und die Zeilen aus den Läufen davor einfach liegen bleiben.

Am Tag, an dem eine Spalte dazukommen soll, hilft IF NOT EXISTS nicht mehr. Die Tabelle gibt es ja, also passiert gar nichts. Nach dem erweiterten CREATE stehen im Beispiel weiter nur id, titel da, und beschweren tut sich niemand. Für so etwas gibt es ALTER TABLE und ab einer gewissen Größe eine eigene, durchnummerierte Datei je Änderung. Die nennt man Migrationen.

Was in die Speicherklasse gehört und was nicht

Die Klasse, die du gleich schreibst, ist die einzige Stelle des Projekts, die SQL enthält, und das gilt in zwei Richtungen.

Nach innen heißt das, jede Abfrage steht hier. Willst du wissen, was die Anwendung mit ihrer Datenbank tut, liest du eine Datei und nicht zwölf.

Nach außen heißt es, hier steht nichts anderes, kein echo, kein header() und keine Entscheidung darüber, wer etwas sehen darf. Die Klasse beantwortet Fragen wie „gib mir alle Lesezeichen von Nutzer 3“ und stellt keine. Mit den Mitteln aus Abschnitt 17 lässt sie sich deshalb in wenigen Zeilen testen. Den Pfad zur Datenbankdatei nimmt sie schon im Startcode entgegen, statt ihn selbst zu kennen, so wie in der Lektion „Testbaren Code schreiben“.

In der Aufgabe stehen zwei Methoden für die Nutzer schon fertig in dieser Klasse. Die beiden Tabellen und vier Methoden für die Lesezeichen schreibst du selbst.

Zum Mitnehmen

Nutzer und Lesezeichen liegen in zwei Tabellen, und die Nummer des Besitzers verbindet sie. In SQLite bleibt REFERENCES ohne PRAGMA foreign_keys eine Beschreibung.

Jetzt du

Basis Konto, kostenlos

Zu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.

Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.

In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.

Was in dieser Lektion steckt

  • Artikel mit 3 Beispielen zum Ausprobieren

    Steht hier, ohne Konto lesbar.

  • Aufgabe, dein Code läuft auf einem Server

    Öffnet sich mit dem Basis Konto.