/***********************************************************************
 * Falls die Datenbank neu aufgebaut werden soll, muessen die Tabellen
 * zunaechst geloescht werden. Dies ist natuerlich nur dann moeglich, 
 * wenn die Tabellen vorher schon mal erzeugt wurden.
 * http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html 
 * http://www.faqs.org/docs/ppbook/x13546.htm
 * http://www.postgresql.at/de/docs/sql-createtable.html
 ***********************************************************************/

START TRANSACTION;
DROP TABLE IF EXISTS a CASCADE;
DROP TABLE IF EXISTS b CASCADE;
COMMIT WORK; 

/***********************************************************************
 * Datenbankschema
 ***********************************************************************/

START TRANSACTION;

CREATE TABLE a
(
  id      INTEGER NOT NULL,
  b       INTEGER NOT NULL,
  CONSTRAINT p_a
    PRIMARY KEY (id)
);

CREATE TABLE b
(
  id      INTEGER NOT NULL,
  a       INTEGER NOT NULL,
  CONSTRAINT p_b
    PRIMARY KEY (id),
  CONSTRAINT f_b_a
    FOREIGN KEY (a) REFERENCES a(id)
    DEFERRABLE INITIALLY DEFERRED
);

/* Kann erst hier definiert werden, aber nicht direkt in TABLE a. */
ALTER TABLE a ADD
  CONSTRAINT f_a_b
    FOREIGN KEY (b) REFERENCES b(id)
    DEFERRABLE INITIALLY DEFERRED;

COMMIT WORK;

/***********************************************************************
 * Daten
 ***********************************************************************/

/* Funktioniert nicht, wenn Auto-Commit je Befehl aktiv! */
/*
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
*/

/* Funktioniert nicht! */
/*
START TRANSACTION;
INSERT INTO a VALUES (1,2);
COMMIT WORK;
START TRANSACTION;
INSERT INTO b VALUES (2,1);
COMMIT WORK;
*/

/* Funktioniert! */
START TRANSACTION;
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
COMMIT WORK;

/* Funktioniert! */
START TRANSACTION;
DELETE FROM a;
DELETE FROM b;
COMMIT WORK;

/* Funktioniert nicht! */
/*
SET CONSTRAINTS f_a_b IMMEDIATE;
START TRANSACTION;
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
COMMIT WORK;
*/

/* Analog mit:
SET CONSTRAINTS f_a_b, f_b_a IMMEDIATE;
SET CONSTRAINTS ALL IMMEDIATE;
*/

/* Funktioniert! */
SET CONSTRAINTS ALL DEFERRED;
START TRANSACTION;
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
COMMIT WORK;


/***********************************************************************
 * Händler-Datenbank
 ***********************************************************************/

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Hier wäre ein Phantom-Read von MAX(h_id) fatal.

INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES ((SELECT MAX(h_id)+1 FROM haendler), 'Maier 1',   'Königsbrunn'),
       ((SELECT MAX(h_id)+2 FROM haendler), 'Maier zwo', 'Bobingen')
;

INSERT INTO liefert(h_id, w_id, l_preis)
VALUES ((SELECT MAX(h_id)-1 FROM haendler),
        (SELECT w_id 
         FROM   ware 
         WHERE  w_typ = 'Sonstiges' AND w_bezeichnung = 'Eieruhr'
        ),
        6.00 
       ),
       ((SELECT MAX(h_id) FROM haendler),
        (SELECT w_id 
         FROM   ware 
         WHERE  w_typ = 'Sonstiges' AND w_bezeichnung = 'Eieruhr'
        ),
        5.50 
       )
;

COMMIT WORK;

SELECT *
FROM   haendler JOIN liefert USING (h_id)
                JOIN ware    USING (w_id)
;

/***********************************************************************
 * Beispiele für ISOLATION LEVEL
 ***********************************************************************/

SERIALIZABLE:
  obiges Beispiel

REPEATABLE READS:
  Bank-Transaktionen (Kontenbewegungen)
  
READ COMMITED (Postgres-Stadard)
  So gut wie REPEATABLE READS, wenn keine 
  multiplen Reads vorkommen.
  Locks können vor Transaktionsende freigegeben werden.  
  
READ UNCOMMITTED
  Statistische Auswertungen (z.B. aktuelle Zahl der Corona-Inifizierten)