9. Reisebüro

Das Reisebüro ermittelt für einen Fluggast die möglichen Flüge und fügt bei einer Buchung Einträge in die Tabellen persons und passangers hinzu. Damit ein Flug nicht überbucht wird, darf die Anzahl der Sitzplätze in einer Maschine nicht überschritten werden. Um dies zu gewährleisten wird ein Trigger eingesetzt, der Überbuchungen als Fehler ausweist. Ein Trigger ruft vor oder nach einer Datenbankoperation, z.B. INSERT, UPDATE, DELETE oder sogar TRUNCATE, eine sogenannte Triggerfunktion auf. Die Triggerfunktion check_booking soll im Folgenden die Überbuchung eines Fluges verhindern. Dazu wird die Anzahl der gebuchten Plätze vor dem Einfügen eines neuen Datensatzes in die Tabelle passangers überprüft.

CREATE FUNCTION check_booking()
RETURNS trigger
AS
$$
DECLARE
  v_seats integer;
  v_booked integer;
BEGIN
  -- places available read into vseats
  SELECT INTO v_seats seats FROM flights WHERE flight_number=NEW.flight_number;
  IF NOT FOUND
  THEN
    RAISE EXCEPTION 'flight_number "%" NOT existing', NEW.flight_number;
  END IF;
  -- places booked read into v_booked
  SELECT INTO v_booked count(*) FROM passangers WHERE flight_number=NEW.flight_number;
  IF v_seats <= v_booked
  THEN
    RAISE EXCEPTION 'Flight "%" just booked out', NEW.flight_number;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Die Triggerfunktion wird ebenfalls über den DDL-Befehl CREATE FUNCTION angelegt. Der Rückgabewert ist vom Datentyp trigger. Die Parameterliste der Funktion ist leer. Der Funktionsrumpf ist in PL/pgSQL implementiert. Nach DECLARE werden die lokalen Variablen v_seats und v_booked vom Typ integer definiert. Diese Trigger Funktion soll für jeden neu einzufügenden Datensatz in die Tabelle passangers aufgerufen werden. Die neu einzufügenden Werte sind in diesem Fall in der Variable NEW abgelegt. Die einzelnen Felder werden mittels der "."-Notation adressiert. Die Werte aus der zurückgegebenen Variablen NEW sollen in die Tabelle passanger eingefügt werden. Falls die Flugnummer nicht existiert oder die Platznummer die Anzahl der Sitze übersteigt, wird mittels der RAISE-Anweisungen ein Fehler ausgelöst. Die Variablen v_seats und v_booked werden durch Verwendung der INTO-Bedingung innerhalb einer SELECT-Anweisung mit Werten belegt. Dazu wird der folgende Ausdruck aus der Auswahlliste der SELECT-Anweisung genutzt.

Nun definieren wir den Trigger mittels des DDL-Befehl CREATE TRIGGER.

CREATE TRIGGER check_place_number
  BEFORE INSERT ON passangers FOR EACH ROW EXECUTE PROCEDURE check_booking();

Bevor ein Datensatz in die Tabelle passangers eingefügt wird, wird nun für diesen Datensatz die Funktion check_place_number ausgeführt. Die Werte des Datensatzes finden sich entsprechend in der Variable NEW. Trigger sind wichtige Ergänzungen der Integritätsbedingungen und können im praktischen Einsatz viel Arbeit ersparen. Um das Kundenverhalten für Umbuchungen oder Stornierungen von Flügen aufzuzeichnen verwendet das Reisebüro eine Historie. Diese wird in der Tabelle booking_history geführt.

CREATE TABLE booking_history (
 booking_id serial,
 booking_parent integer,
 flight_number varchar NOT NULL,
 person_id integer NOT NULL,
 createdate timestamp(0) without time zone DEFAULT now(),
 action varchar CHECK ( action IN ('booked', 'rebooked', 'canceld') ),
 CONSTRAINT booking_history_pk PRIMARY KEY (booking_id),
 CONSTRAINT booking_parent_fk FOREIGN KEY (booking_parent) REFERENCES booking_history (booking_id)
);

Die Historie wird über einen Trigger belegt. Zunächst definieren wir die dazugehörige Trigger Funktion booking_history.

CREATE FUNCTION booking_history()
RETURNS TRIGGER
AS
$$
DECLARE
  v_booking_parent integer;
BEGIN
  CASE TG_OP
    WHEN 'INSERT' THEN
      INSERT INTO booking_history (flight_number, person_id, action) VALUES (NEW.flight_number, NEW.person_id, 'booked');
    WHEN 'UPDATE' THEN
      SELECT max(booking_id) INTO v_booking_parent FROM booking_history WHERE flight_number = OLD.flight_number AND person_id = OLD.person_id;
      INSERT INTO booking_history (booking_parent, flight_number, person_id, action) VALUES (v_booking_parent, NEW.flight_number, NEW.person_id, 'rebooked');
    WHEN 'DELETE' THEN
      SELECT max(booking_id) INTO v_booking_parent FROM booking_history WHERE flight_number = OLD.flight_number AND person_id = OLD.person_id;
      INSERT INTO booking_history (booking_parent, flight_number, person_id, action) VALUES (v_booking_parent, OLD.flight_number, OLD.person_id, 'canceld');
    ELSE
      RAISE EXCEPTION 'operation not defined: %', TG_OP;
  END CASE;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Diese Funktion soll für die Operationen INSERT, UPDATE und DELETE auf der Tabelle flights einen entsprechenden Eintrag in die Historie einfügen. Die drei Operationen werden durch die CASE-Anweisung unterschieden. Der Wert der Variable TG_OP enthält die die Trigger Funktion auslösende Datenbankoperation. Vor dem Hinzufügen eines Datensatzes in die Historie wird für UPDATE und DELETE der letzte Schritt in der Historie ermittelt. Dieser wird aus den aktuellen Werten des schon existierenden Datensatzes in der Variable OLD rekonstruiert. Dazu wird der letzte Eintrag zur aktuellen Flugnummer und Personenkennung in der Tabelle booking_history gesucht. Die Triggerfunktion soll nach der Operation auf der Tabelle passangers aufgerufen werden.

CREATE TRIGGER booking_history
   AFTER INSERT OR UPDATE OR DELETE ON passangers FOR EACH ROW EXECUTE PROCEDURE booking_history();

Wir untersuchen die Historie durch folgende Zusatzbuchung, die einmal umgebucht und storniert wird.

INSERT INTO passangers (flight_number, person_id) VALUES ('LH 457', 2);
UPDATE passangers SET flight_number='AB 8510' WHERE flight_number='LH 457' AND person_id = 2;
DELETE FROM passangers WHERE flight_number='AB 8510' AND person_id=2;

SELECT * FROM booking_history;

 booking_id | booking | flight_number | person_id |     createdate      |  action
------------+---------+---------------+-----------+---------------------+----------
          1 |         | LH 457        |         2 | 2011-04-25 16:31:37 | booked
          2 |       1 | AB 8510       |         2 | 2011-04-25 16:32:08 | rebooked
          3 |       2 | AB 8510       |         2 | 2011-04-25 16:32:35 | canceld
(3 rows)

In diesem Workshop haben wir uns Grundlagen in der Datenbanksprache SQL erworben. Wir haben Tabellen mit Datentypen erstellt, die wir für die entsprechende Aufgabenstellung zuvor definiert haben. Wir haben implizite und explizite Konvertierungsmechanismen kennengelernt, genau wie wir die Bedeutung der wichtigsten Integritätsbedingungen studiert haben. Zudem haben wir uns mit der Speicherung von Daten innerhalb von PostgreSQL beschäftigt. Das Einfügen, INSERT, Verändern, UPDATE, sowie das Löschen, DELETE, von Daten wurde von uns behandelt, genau so wie wir die Bedeutung von Schemen kennen gelernt haben.

Wir haben immer wieder den DML-Befehl SELECT genutzt und eine Vielzahl von Operatoren, Aggregatfunktionen und Bedingungen angewendet. Dieses Wissen haben wir für die Erstellung von Sichten und Funktionen genutzt. Mit dem Wissen über Funktionen konnten wir mittels Triggerfunktionen Trigger definieren. Weitere Themen wie Indexe, Transaktionen, die Verwendung von Cursor-Objekten, die Rechteverwaltung und das Zugriffsmanagement waren jedoch nicht Gegenstand dieses Workshops. Diese Themen, genau wie die Optimierung von Datenbankabfragen und des Datenbanksystems sind zu komplex, um sie hier zu behandeln. In diesen Zusammenhängen wird dann auch über die Auflösung der Prinzipien gesprochen werden, die wir hier kennen gelernt haben.