/*************************************************************************************
 * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick
 *************************************************************************************/

/* Alte Views, Tabellen und Domains (in der richtigen Reihenfolge) löschen. */

DROP VIEW IF EXISTS haendler_liefert_ware CASCADE;
DROP VIEW IF EXISTS liefert               CASCADE;
DROP VIEW IF EXISTS ware                  CASCADE;
DROP VIEW IF EXISTS haendler              CASCADE;
DROP VIEW IF EXISTS typ                   CASCADE;

DROP TABLE IF EXISTS r_liefert  CASCADE;
DROP TABLE IF EXISTS e_ware     CASCADE;
DROP TABLE IF EXISTS e_haendler CASCADE;
DROP TABLE IF EXISTS enum_typ   CASCADE;

DROP DOMAIN IF EXISTS D_LIEFERZEIT CASCADE;
DROP DOMAIN IF EXISTS D_UNTAINTED  CASCADE;


/* Neue Domains erstellen. */

CREATE DOMAIN D_UNTAINTED
AS
VARCHAR
CHECK (value ~ '\A[^<>&"'']+\Z');

CREATE DOMAIN D_LIEFERZEIT
AS
SMALLINT
CHECK (value > 0);


/* Neue Tabellen erstellen. */

CREATE TABLE enum_typ
  (t_id          INTEGER     NOT NULL,
   t_name        D_UNTAINTED NOT NULL,
   t_supertyp_id INTEGER,

   CONSTRAINT pk_enum_typ
     PRIMARY KEY (t_id),

   CONSTRAINT fk_enum_typ_enum_typ
     FOREIGN KEY (t_supertyp_id) REFERENCES enum_typ (t_id),

   CONSTRAINT unique_enum_typ_tname
     UNIQUE (t_name)
  );

CREATE TABLE e_haendler
  (h_id        INTEGER     NOT NULL,
   h_name      D_UNTAINTED NOT NULL,
   h_ortschaft D_UNTAINTED,

   CONSTRAINT pk_haendler
     PRIMARY KEY (h_id),

   CONSTRAINT unique_haendler_name_ortschaft
     UNIQUE (h_name, h_ortschaft)
  );

CREATE TABLE e_ware
  (w_id          INTEGER     NOT NULL,
   t_id          INTEGER     NOT NULL DEFAULT 0,
   w_bezeichnung D_UNTAINTED NOT NULL,

   CONSTRAINT pk_ware
     PRIMARY KEY (w_id),

   CONSTRAINT fk_ware_typ
     FOREIGN KEY (t_id) REFERENCES enum_typ (t_id),

   CONSTRAINT unique_ware_t_id_bezeichnung
     UNIQUE (t_id, w_bezeichnung)
  );
                
CREATE TABLE r_liefert
  (h_id         INTEGER      NOT NULL,
   w_id         INTEGER      NOT NULL,
   l_preis      NUMERIC(8,2) NOT NULL,
   l_lieferzeit D_LIEFERZEIT,

   CONSTRAINT pk_liefert
     PRIMARY KEY (h_id,w_id,l_preis),

   CONSTRAINT fk_liefert_haendler
     FOREIGN KEY (h_id) REFERENCES e_haendler (h_id),

   CONSTRAINT fk_liefert_ware
     FOREIGN KEY (w_id) REFERENCES e_ware (w_id)
  );
  
  
/* Neue Updatable Views erstellen.*/

CREATE VIEW typ (t_id, t_name, t_supertyp_id)
AS
SELECT t_id, t_name, t_supertyp_id
FROM   enum_typ
;

CREATE VIEW ware (w_id, w_typ, w_bezeichnung)
AS
SELECT w_id, t_name, w_bezeichnung
FROM   e_ware JOIN enum_typ USING (t_id)
;

CREATE VIEW haendler (h_id, h_name, h_ortschaft)
AS
SELECT h_id, h_name, h_ortschaft
FROM   e_haendler
;

CREATE VIEW liefert (h_id, w_id, l_preis, l_lieferzeit)
AS
SELECT h_id, w_id, l_preis, l_lieferzeit
FROM   r_liefert
;

CREATE VIEW haendler_liefert_ware (h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit)
AS
SELECT h_id, h_name, h_ortschaft, w_id, w_typ, w_bezeichnung, l_preis, l_lieferzeit
FROM   haendler JOIN liefert USING (h_id)
                JOIN ware    USING (w_id)
;

DROP RULE IF EXISTS insert_into_ware ON ware CASCADE;
DROP RULE IF EXISTS delete_from_ware ON ware CASCADE;
DROP RULE IF EXISTS update_ware      ON ware CASCADE;

CREATE RULE insert_into_ware 
AS 
ON INSERT TO ware
DO INSTEAD
INSERT INTO e_ware (w_id, t_id, w_bezeichnung)
VALUES (NEW.w_id,
        (SELECT t_id FROM enum_typ WHERE t_name = NEW.w_typ),
        NEW.w_bezeichnung
       )
;

CREATE RULE delete_from_ware 
AS 
ON DELETE TO ware
DO INSTEAD
DELETE FROM e_ware
WHERE  w_id = OLD.w_id
;

CREATE RULE update_ware AS ON UPDATE TO ware
DO INSTEAD
UPDATE e_ware
SET    w_id          = NEW.w_id,
       w_id          = (SELECT t_id FROM enum_typ WHERE t_name = NEW.w_typ),
       w_bezeichnung = NEW.w_bezeichnung
WHERE  w_id = OLD.w_id
;


DROP RULE IF EXISTS insert_into_haendler ON haendler CASCADE;
DROP RULE IF EXISTS delete_from_haendler ON haendler CASCADE;
DROP RULE IF EXISTS update_haendler      ON haendler CASCADE;

CREATE RULE insert_into_haendler 
AS 
ON INSERT TO haendler
DO INSTEAD
INSERT INTO e_haendler (h_id, h_name, h_ortschaft)
VALUES (NEW.h_id, NEW.h_name, NEW.h_ortschaft)
;

CREATE RULE delete_from_haendler 
AS 
ON DELETE TO haendler
DO INSTEAD
DELETE FROM e_haendler
WHERE  h_id = OLD.h_id
;

CREATE RULE update_haendler 
AS 
ON UPDATE TO haendler
DO INSTEAD
UPDATE e_haendler
SET    h_id = NEW.h_id, h_name = NEW.h_name, h_ortschaft = NEW.h_ortschaft
WHERE  h_id = OLD.h_id
;


DROP RULE IF EXISTS insert_into_liefert ON liefert CASCADE;
DROP RULE IF EXISTS delete_from_liefert ON liefert CASCADE;
DROP RULE IF EXISTS update_liefert      ON liefert CASCADE;

CREATE RULE insert_into_liefert 
AS 
ON INSERT TO liefert
DO INSTEAD
INSERT INTO r_liefert (h_id, w_id, l_preis, l_lieferzeit)
VALUES (NEW.h_id, NEW.w_id, NEW.l_preis, NEW.l_lieferzeit)
;

CREATE RULE delete_from_liefert 
AS 
ON DELETE TO liefert
DO INSTEAD
DELETE FROM r_liefert
WHERE  h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis
;

CREATE RULE update_liefert 
AS 
ON UPDATE TO liefert
DO INSTEAD
UPDATE r_liefert
SET    h_id         = NEW.h_id,
       w_id         = NEW.w_id,
       l_preis      = NEW.l_preis,
       l_lieferzeit = NEW.l_lieferzeit
WHERE  h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis
;


DROP RULE IF EXISTS delete_from_haendler_liefert_ware ON haendler_liefert_ware CASCADE;

CREATE RULE delete_from_haendler_liefert_ware 
AS 
ON DELETE TO haendler_liefert_ware
DO INSTEAD
DELETE FROM r_liefert
WHERE  h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis
;


/* Daten einfügen */

INSERT INTO typ(t_id, t_name, t_supertyp_id)
VALUES
  (0, 'Sonstiges',       NULL),
  (1, 'Computerzubehör', NULL),
  (2, 'CPU',             1),
  (3, 'RAM',             1)
;

INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES 
  (1, 'Maier',   'Königsbrunn'),
  (2, 'Müller',  'Königsbrunn'),
  (3, 'Maier',   'Augsburg'),
  (4, 'Huber',   NULL),
  (5, 'Schmidt', 'Hamburg')
;

INSERT INTO ware(w_id, w_typ, w_bezeichnung)
VALUES
  (1, 'CPU',       'Pentium IV 3,8'),
  (2, 'CPU',       'Celeron 2,6'),
  (3, 'CPU',       'Athlon XP 3000+'),
  (4, 'RAM',       'SDRAM 1GB'),
  (5, 'Sonstiges', 'Eieruhr')
;

/* Fehler! 'CPUx' gibt es nicht als Typ!
INSERT INTO ware(w_id, w_typ, w_bezeichnung)
VALUES
  (10, 'CPUx', 'Pentium IV 3,8')
;
*/

INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES 
  (1, 1, 200.00, 1),
  (1, 1, 194.00, 6),
  (1, 2, 100.00, NULL),
  (1, 3, 150.00, 7),
  (1, 4,  10.00, 1),
  (1, 5,   5.00, 1),
  (2, 1, 190.00, NULL),
  (2, 3, 170.00, 4),
  (2, 1, 160.00, 1),
  (2, 2, 180.00, NULL),
  (3, 1, 195.00, 2),
  (3, 2, 190.00, 1),
  (4, 1, 150.00, 3),
  (4, 3, 180.00, 5),
  (4, 3, 199.99, 1)
;