Die Bäckerei, Etappe 5: die Datenbank arbeitet von selbst
Kurz gesagt: Etappe 5 fügt einen Index, eine View mit den Bestellsummen, einen Trigger, der den Lagerbestand senkt, und eine neue Spalte mit ALTER TABLE hinzu.
Problem
Die Dinge, an die man denken muss
In Etappe 4 wurde der Lagerbestand mit einer von Hand geschriebenen Abfrage gesenkt, und die Summe einer Bestellung wurde jedes Mal mit drei Tabellen berechnet. Die Bäckerei wächst: Aus den Bestellungen werden Tausende, und mehr Leute schreiben Abfragen. Dinge, die davon abhängen, dass jemand daran denkt, werden früher oder später vergessen.
Diese Etappe gibt die Arbeit an die Datenbank ab: ein Index für die schnelle Suche, eine View für die Summen, ein Trigger fürs Lager und eine neue Spalte für das Telefon.
Die Idee
Wie die Datenbank sucht
Die Seite „Meine Bestellungen“ fragt nach den Bestellungen eines Kunden. EXPLAIN QUERY PLAN zeigt, wie die Datenbank das erledigt:
SCAN orders: Die Datenbank liest jede Bestellung und schaut auf den Kunden. Bei sechs Bestellungen spielt das keine Rolle, bei einer Million ist es langsam.
Probier es aus
Der Index
Lege einen Index orders_customer auf die Spalte customer_id von orders an.
Lösung
CREATE INDEX orders_customer ON orders (customer_id);CREATE INDEX orders_customer ON orders (customer_id);. Die Prüfung schaut, welche Spalte im Index steht. Ein Index auf status hilft bei der Suche nach dem Kunden nicht, und ein Index auf customers (id) ist überflüssig: Der Primärschlüssel ist schon ein Index.
Dieselbe Abfrage mit dem Index:
SEARCH … USING INDEX: Die Datenbank geht direkt zu den Bestellungen von Kunde 2.
Genauer, für Neugierige
SQLite legt für einen Fremdschlüssel nicht von selbst einen Index an, PostgreSQL auch nicht. Eine Spalte, die auf eine andere Tabelle zeigt, will fast immer einen Index: Nach ihr werden Tabellen verbunden und durchsucht. Der Index ist aber nicht gratis: Jedes INSERT und UPDATE aktualisiert ihn.
Probier es aus
Die View
Die Summe einer Bestellung sind jedes Mal drei Tabellen und ein GROUP BY. Eine View ist eine Abfrage mit einem Namen: Du liest aus ihr wie aus einer Tabelle.
Lege eine View order_totals an, mit der Nummer der Bestellung (id), dem Kunden (customer_id), dem Status und der Summe (total, auf 2 Stellen gerundet) jeder Bestellung.
Lösung
CREATE VIEW order_totals AS
SELECT o.id, o.customer_id, o.status,
ROUND(SUM(i.quantity * i.unit_price), 2) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;CREATE VIEW order_totals AS SELECT o.id, o.customer_id, o.status, ROUND(SUM(i.quantity * i.unit_price), 2) AS total FROM orders o JOIN order_items i ON i.order_id = o.id GROUP BY o.id;. Ohne GROUP BY kommt eine Zeile mit der Summe von allem heraus. Mit SUM(i.unit_price) werden die Stücke nicht gezählt.
Sieh es dir an
Die View als Tabelle
Wie viel jeder Kunde ausgegeben hat, ohne die stornierten Bestellungen. Die Summe über die drei Tabellen steckt in der View:
Die View speichert keine Daten, sie speichert ihre Abfrage. Eine neue Bestellung erscheint sofort darin:
Probier es aus
Der Trigger
Ein Trigger ist Code, den die Datenbank bei einem INSERT, UPDATE oder DELETE in einer Tabelle von selbst ausführt. In seinem Rumpf ist new die neue Zeile.
Lege einen Trigger take_stock an: Nach jeder neuen Zeile in order_items sinkt der Lagerbestand des Produkts in der Zeile um deren Stückzahl.
Lösung
CREATE TRIGGER take_stock
AFTER INSERT ON order_items
BEGIN
UPDATE products SET stock = stock - new.quantity
WHERE id = new.product_id;
END;CREATE TRIGGER take_stock AFTER INSERT ON order_items BEGIN UPDATE products SET stock = stock - new.quantity WHERE id = new.product_id; END;. Die Prüfung schreibt eine Bestellung mit zwei Baguettes und schaut aufs Lager: Die Baguettes müssen 23 sein, und die Croissants müssen 40 bleiben. Ohne WHERE sinken alle Produkte. Mit stock - 1 nehmen die zwei Baguettes eines weg.
Sieh es dir an
Der Trigger und die Regeln
Mit dem Trigger bestellt Kalin 20 Kozunaks, und es gibt 10:
Das UPDATE im Trigger verletzt CHECK (stock >= 0). Der Trigger ist Teil des INSERT, also kommt auch die Zeile der Bestellung nicht hinein: Die Datenbank hat keine Zeile, die „20 Kozunaks verkauft“ sagt, wenn es sie nicht gibt.
Genauer, für Neugierige
Trigger halten eine Regel an einer einzigen Stelle, aber sie sind versteckt: Wer den Code des Programms liest, sieht sie nicht. Darum setzt man sie sparsam ein, für Dinge, die immer passieren müssen, egal, was in die Datenbank schreibt. Der Lagerbestand hier ist so etwas, das Senden einer E-Mail nicht.
Probier es aus
Eine neue Spalte
Die Bäckerei will die Kunden anrufen, wenn die Bestellung fertig ist. Die Tabelle hat Daten, also wird sie nicht neu angelegt: ALTER TABLE fügt ihr eine Spalte hinzu.
Füge customers eine Spalte phone vom Typ TEXT hinzu, die fehlen darf.
Lösung
ALTER TABLE customers ADD COLUMN phone TEXT;ALTER TABLE customers ADD COLUMN phone TEXT;. Die fünf Kunden bekommen NULL: Ihre Telefonnummern sind noch nicht bekannt. Das Telefon ist Text, keine Zahl: +359 88… hat ein Vorzeichen und Leerzeichen, und die führende Null von 0888… verschwindet in INTEGER.
Mit NOT NULL geht es nicht:
Genauer, für Neugierige
ALTER TABLE kann in SQLite Spalten hinzufügen, umbenennen und (seit 3.35) löschen. Eine Regel wie CHECK oder einen Fremdschlüssel zu einer vorhandenen Spalte hinzufügen kann es nicht: Dann wird die Tabelle neu angelegt (eine neue Tabelle, INSERT … SELECT, DROP der alten und eine Umbenennung). In PostgreSQL macht ALTER TABLE … ADD CONSTRAINT das direkt.
Die Idee
Was als Nächstes kommt
Die Datenbank ist voll, schnell und hält ihre Regeln von selbst ein. Die letzte Etappe ist das Ende: ein Bericht, eine Kopie der Datenbank und DROP von allem in der richtigen Reihenfolge.
Test