Anwendungsentwicklung mit PostgreSQL

von

Wer spricht?

  • Dipl.-Phys. Peter Andreas Möller

  • seit 2001 Entwicklung internetbasierter Software

  • Datenbank- und Webanwendungen sowie Single Source Publishing

Ziele

  • Beispiel: Erstellung einer Datenbankanwendung

  • Datenbankobjekte: Tabelle, Datentyp, Sicht, Funktion und Trigger

  • Datenbankoperationen: CREATE, ALTER, DROP, INSERT, UPDATE, SELECT, DELETE, ...

  • Zielgruppe: Programmierer und Interessierte (keine oder wenige SQL-Kenntnisse)

Verwaltung des Flugverkehrs

  • Flughäfen: TXL - Berlin Tegel Airport (52.55°N,13.28°O) bis 2012

  • Fluggesellschaften: AB - Air Berlin

  • Flüge: AB 9172 von TXL nach PMI (Palma)

  • Fluggäste: MR Andreas Müller

Was macht Datenbankentwicklung?

  • Persistente und Konsistente Speicherung von Daten

  • Speicherung der Anwendungslogik

  • Damit Schnittstelle für externe Anwender

  • Optimiert Datenbankoperationen und Design

Was ist PostgreSQL?

  • Objekt-Relationales Datenbank Managementsystem in C

  • OpenSource Lizenz

  • erweiterbar und flexibel

  • Transaktionen gemäß ACID

Transaktionen nach ACID

  • Atomarität - Alle Operationen oder keine!

  • Konsistenz - Nur gültige Änderungen

  • Isolation - Transaktionen überlagern sich nicht

  • Dauerhaftigkeit - Daten bleiben dauerhaft gespeichert

Relationale Datenbanken

  • Datensätze erfasst durch Relationen (Tabelle)

  • Mathematische Theorie der Relationalen Algebra

  • Eigene Sprache: SQL

SQL, oft auch Sturctured Querying Language

  • streng typisierte, deklarative Sprache

  • ANSI-SQL:2008 (ISO) gut erfüllt in PostgreSQL

  • Dialekte, beipsielsweise Oracle, DB2 oder, PostgreSQL

  • Dreigliedrig: DDL, DML und DCL

Wie gehen wir vor?

  • Erfahrung keine Verfahren zur Modellierung

  • Erde als Kugel mit Landkarte aus Ländern, Städten und Flughäfen

  • Flug befördert Passagiere zwischen zwei Flughäfen

  • Flugroute kürzeste Strecke zwischen zwei Punkten (Orthodrom)

Längen- und Breitengrade
Abbildung 1: Längen- und Breitengrade

Längen- und Breitengrade zur Positionsbestimmung auf der Erde

CREATE DOMAIN latitude float
  CHECK (VALUE BETWEEN -90.0 AND 90.0);
CREATE DOMAIN longitude float
  CHECK (VALUE BETWEEN 0.0 AND 360.0);
CREATE TYPE location AS (
  latitude latitude,
  longitude longitude
);
SELECT '(90.1,0.0)'::location;

FEHLER:  Wert für Domäne latitude verletzt Check-Constraint »latitude_check«

Data Definition Language (DDL)

  • Beschreibung eines Relationalen Schemas

  • CREATE Object - Erzeugen

  • ALTER Object - Ändern

  • DROP Object - Entfernen

Länder

CREATE TABLE countries (
 country_id char(2),
 name varchar(256) NOT NULL,
 population integer
);
INSERT INTO countries (country_id, name) VALUES ('AU', 'Australia');
ALTER TABLE countries ADD CONSTRAINT pk_country_id PRIMARY KEY (country_id);
INSERT INTO countries (country_id, name) VALUES ('AU', 'United States');

FEHLER:  doppelter Schlüsselwert verletzt Unique-Constkraint »pk_country_id«
DETAIL:  Schlüssel »(country_id)=(AU)« existiert bereits.
UPDATE countries SET name=NULL WHERE country_id = 'AU';

FEHLER:  NULL-Wert in Spalte »name« verletzt Not-Null-Constraint

Constraints

  • Sorgen für die Konsistenz

  • PRIMARY KEY - Wert nur einmal erlaubt (Primärschlüssel, obligatorisch)

  • NOT NULL - Wert darf nicht Datenbankwert NULL annehmen

  • FOREIGN KEY - Wert ist Primärschlüssel aus anderer Tabelle

Flughäfen

CREATE TABLE airports (
 airport_id char(3) NOT NULL, 
 name varchar(256) NOT NULL,
 location location NOT NULL,
 country char(2) NOT NULL,
 open boolean DEFAULT TRUE,
 CONSTRAINT pk_airport_id PRIMARY KEY (airport_id),
 CONSTRAINT fk_country FOREIGN KEY (country) REFERENCES countries (country_id)
);
INSERT INTO airports (airport_id, name, location, country) VALUES ('GVA', 'Geneva International Airport', '( 46.23, 6.10)', 'CH');

FEHLER:  Einfügen oder Aktualisieren in Tabelle »airports« verletzt Fremdschlüssel-Constraint »fk_country«
DETAIL:  Schlüssel (country)=(CH) ist nicht in Tabelle »countries« vorhanden.

Relationales Schema des Flugbetriebs als Diagramm

Data Manupulation Language (DML)

  • Verwalten von Datensätzen

  • INSERT INTO table ... - Einfügen eines Datensatzes

  • UPDATE table ... - Ändern von Datensätzen

  • DELETE table ... - Löschen von Datensätzen

  • SELECT ... FROM table ... - Abfragen einer Ergebnismenge

Abfrage einer Ergebnismenge mit Bedingung

SELECT *
FROM countries
WHERE 
  (country_id IN ('DE', 'ES') AND population > 40000000)
  OR 
  (name LIKE 'United%');

 country_id |     name      | population
------------+---------------+------------
 DE         | Germany       |   80000000
 US         | United States |  300000000
(2 rows)

Über alle Zeilen mitteln

SELECT avg((location).latitude) AS latitude,
       avg((location).longitude) AS longitude
FROM airports;

   latitude   | longitude
--------------+-----------
 31.54        |     72.12
(1 row)

Daten aus mehreren Tabelle abfragen mittels Verbund

SELECT name,
 flight_number
FROM airlines
LEFT JOIN flights USING (airline_id)
ORDER BY name ASC;

      name       | flight_number
-----------------+--------------
 Air Berlin      | AB 9143
 Air Berlin      | AB 8510
 Air Berlin      | AB 8550
 Air Berlin      | AB 9172
 Iberia Airlines |    NULL -- kein Eintrag in flights mit NULL aufgefüllt
 Lufthansa       |  LH 457
 Lufthansa       |  LH 456
(7 rows)

Verbünde behandeln fehlende Einträge unterschiedlich

  • INNER JOIN: Wert in beiden Tabellen

  • LEFT JOIN: alle aus linker Tabelle, rechts mit NULL auffüllen

  • RIGHT JOIN: vice versa

  • FULL JOIN: links und rechts mit NULL auffüllen

Ergebnismenge gruppieren und mit count() auszählen

SELECT name,
 count(flight_number)
FROM airlines
 LEFT JOIN flights USING (airline_id)
GROUP BY name
ORDER BY count(*) DESC;

      name       | count
-----------------+-------
 Air Berlin      |     4
 Lufthansa       |     2
 Iberia Airlines |     0
(3 rows)

Abfragen als Sicht speichern

CREATE VIEW timetable AS
  (
   SELECT 'departure' AS dir, "from" AS airport, airline_id AS airline, 
     "from", departure, "to", arrival 
   FROM flights WHERE departure BETWEEN now() AND now()+ interval '1 hour'
  )
  UNION -- Mengenoperator
  (
   SELECT 'arrival' AS dir, "to" AS ariport, airline_id AS airline, 
     "from", departure, "to", arrival 
   FROM flights WHERE arrival BETWEEN now() AND now() + interval '1 hour';
  )

Sicht wie Tabelle abfragen

SELECT * FROM timetable WHERE airport = 'TXL';

    dir    | airport | airline | from |      departure      | to  |       arrival
-----------+---------+---------+------+---------------------+-----+---------------------
 departure | TXL     | AB      | TXL  | 2011-04-27 18:09:19 | GRZ | 2011-04-27 19:29:19
 departure | TXL     | AB      | TXL  | 2011-04-27 18:54:19 | PMI | 2011-04-27 21:29:19
(2 rows)

Funktionen

  • Einmalig gespeichert im RDBMS Schnittstelle für Benutzer, Dienste

  • Kapseln Anweisungen nach Anwendungslogik unter Funktionennamen

  • Liefern Ergebnismenge oder verändern Datensätze zu Eingabewerten

  • Built-In oder Benutzer definiert: SQL, C, PL/pgSQL, PL/Perl, PL/Python, PL/Tcl

PL/pgSQL

CREATE FUNCTION orthodromic_distance(v_from location, v_to location)
RETURNS float
AS
$$
DECLARE
  v_result float;
BEGIN
  IF ($1=$2) THEN
    v_result := 0.0;
  ELSE  
     v_result := acos(
             sin(radians(v_from.latitude))*sin(radians(v_to.latitude))
            +cos(radians(v_from.latitude))*cos(radians(v_to.latitude))
              *cos(radians(v_to.longitude)-radians(v_from.longitude))
            )*6370.0;
  END IF;

  RETURN v_result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Funktion in Abfrage verwenden

SELECT a.airport_id AS "from", b.airport_id AS "to", 
 orthodromic_distance(a.location, b.location) AS distance
FROM airports a, airports b LIMIT 10;  -- Kartesisches Produkt (NxN)

 from | to  |     distance
------+-----+------------------
 TXL  | TXL |                0
 TXL  | LAX | 9319.42892823799
 TXL  | PMI | 1655.85872296913
 TXL  | GRZ | 619.751073066838
 TXL  | FRA | 434.422849092028
 TXL  | SYD | 16100.8622060376
 LAX  | TXL | 9319.42892823799
 LAX  | LAX |                0
 LAX  | PMI | 9850.19095355263
 LAX  | GRZ | 9888.90382700642
(10 rows)

Trigger

  • Vor oder nach Datenbankoperation werden Anweisungen ausgeführt

  • BEFORE oder AFTER INSERT oder UPDATE oder DELETE oder TRUNCATE

  • Daten können verändert werden, oder es können andere Daten verändert werden

  • In PostgreSQL werden Triggerfunktionen aufgerufen

Triggerfunktionen

CREATE TRIGGER booking_history
   AFTER INSERT OR UPDATE OR DELETE ON passangers FOR EACH ROW EXECUTE PROCEDURE booking_history();
CREATE FUNCTION booking_history()
RETURNS TRIGGER
AS
$$
DECLARE
  v_bpar integer; -- vorhergehende Buchung
BEGIN
  CASE TG_OP 
    -- nur NEW
    WHEN 'INSERT' THEN
      INSERT INTO booking_history (...) VALUES (NEW.flight_number, ..., 'booked'); 
    -- NEW, OLD 
    WHEN 'UPDATE' THEN
      SELECT max(booking_id) INTO v_bpar FROM ... WHERE ... = OLD.flight_number AND ...;
      INSERT INTO booking_history (...) VALUES (v_bpar, NEW. ..., 'rebooked'); 
    -- nur OLD
    WHEN 'DELETE' THEN
      ...
  END CASE;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Ausblick