/*************************************************************************************
 * 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_lieferware 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;

/* Neue Domains erstellen. */

-- CREATE DOMAIN IF NOT EXISTS D_UNTAINTED
-- AS
-- VARCHAR CHECK (value !~ '[<>"'';]|--|/\*');
DO 
$$ 
  BEGIN
    CREATE DOMAIN D_UNTAINTED
    AS
    VARCHAR CHECK (value !~ '[<>"'';]|--|/\*');
  EXCEPTION
    WHEN duplicate_object THEN null;
  END
$$
;

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


/* Neue Tabellen erstellen. */

CREATE TABLE enum_typ
(id          INTEGER     NOT NULL,
 name        D_UNTAINTED NOT NULL,
 supertyp_id INTEGER,

 CONSTRAINT pk_enum_typ
   PRIMARY KEY (id),

 CONSTRAINT fk_enum_typ_enum_typ
   FOREIGN KEY (supertyp_id) REFERENCES enum_typ (id),

 CONSTRAINT unique_enum_typ_tname
   UNIQUE (name)
);

CREATE TABLE e_haendler
(id        INTEGER     NOT NULL,
 name      D_UNTAINTED NOT NULL,
 ortschaft D_UNTAINTED,

 CONSTRAINT pk_haendler
   PRIMARY KEY (id),

 CONSTRAINT unique_haendler_name_ortschaft
   UNIQUE (name, ortschaft)
);

CREATE TABLE e_ware
(id          INTEGER     NOT NULL,
 t_id        INTEGER     NOT NULL DEFAULT 0,
 bezeichnung D_UNTAINTED NOT NULL,

 CONSTRAINT pk_ware
   PRIMARY KEY (id),

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

 CONSTRAINT unique_ware_id_bezeichnung
   UNIQUE (id, bezeichnung)
);

CREATE TABLE r_liefert
(h_id       INTEGER      NOT NULL,
 w_id       INTEGER      NOT NULL,
 preis      NUMERIC(8,2) NOT NULL,
 lieferzeit D_LIEFERZEIT,

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

 CONSTRAINT fk_lieferhaendler
   FOREIGN KEY (h_id) REFERENCES e_haendler (id),

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

CREATE VIEW typ (id, name, supertyp_id)
AS
SELECT id, name, supertyp_id
FROM   enum_typ
;

CREATE VIEW ware (id, typ, bezeichnung)
AS
SELECT w.id, t.name, w.bezeichnung
FROM   e_ware w JOIN enum_typ t ON w.t_id = t.id
;

CREATE VIEW haendler (id, name, ortschaft)
AS
SELECT id, name, ortschaft
FROM   e_haendler
;

CREATE VIEW liefert (h_id, w_id, preis, lieferzeit)
AS
SELECT h_id, w_id, preis, lieferzeit
FROM   r_liefert
;

CREATE VIEW haendler_lieferware (h_id, name, ortschaft, w_id, typ, bezeichnung, preis, lieferzeit)
AS
SELECT h.id, h.name, h.ortschaft, w.id, w.typ, w.bezeichnung, l.preis, l.lieferzeit
FROM   haendler h JOIN liefert l ON h.id = l.h_id
                  JOIN ware    w ON l.w_id = w.id 
;

DROP RULE IF EXISTS inserinto_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 inserinto_ware 
AS 
ON INSERT TO ware
DO INSTEAD
INSERT INTO e_ware (id, t_id, bezeichnung)
VALUES (NEW.id,
        (SELECT id FROM enum_typ WHERE name = NEW.typ),
        NEW.bezeichnung
       )
;

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

CREATE RULE update_ware AS ON UPDATE TO ware
DO INSTEAD
UPDATE e_ware
SET    id          = NEW.id,
       t_id        = (SELECT id FROM enum_typ WHERE name = NEW.typ),
       bezeichnung = NEW.bezeichnung
WHERE  id = OLD.id
;


DROP RULE IF EXISTS inserinto_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 inserinto_haendler 
AS 
ON INSERT TO haendler
DO INSTEAD
INSERT INTO e_haendler (id, name, ortschaft)
VALUES (NEW.id, NEW.name, NEW.ortschaft)
;

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

CREATE RULE update_haendler 
AS 
ON UPDATE TO haendler
DO INSTEAD
UPDATE e_haendler
SET    id = NEW.id, name = NEW.name, ortschaft = NEW.ortschaft
WHERE  id = OLD.id
;


DROP RULE IF EXISTS inserinto_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 inserinto_liefert 
AS 
ON INSERT TO liefert
DO INSTEAD
INSERT INTO r_liefert (h_id, w_id, preis, lieferzeit)
VALUES (NEW.h_id, NEW.w_id, NEW.preis, NEW.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 preis = OLD.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,
       preis      = NEW.preis,
       lieferzeit = NEW.lieferzeit
WHERE  h_id = OLD.h_id AND w_id = OLD.w_id AND preis = OLD.preis
;


DROP RULE IF EXISTS delete_from_haendler_lieferware ON haendler_lieferware CASCADE;

CREATE RULE delete_from_haendler_lieferware 
AS 
ON DELETE TO haendler_lieferware
DO INSTEAD
DELETE FROM r_liefert
WHERE  h_id = OLD.h_id AND w_id = OLD.w_id AND preis = OLD.preis
;


/* Daten einfügen */

INSERT INTO typ(id, name, supertyp_id)
VALUES
(0, 'Sonstiges',       NULL),
(1, 'Computerzubehör', NULL),
(2, 'CPU',             1),
(3, 'RAM',             1)
;

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

INSERT INTO ware(id, typ, 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(id, typ, bezeichnung)
VALUES
  (10, 'CPUx', 'Pentium IV 3,8')
;
*/

INSERT INTO liefert(h_id, h_id, preis, 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, 160.00, NULL),
(2, 1, 190.00, 1),
(2, 2, 180.00, NULL),
(2, 3, 170.00, 4),
(2, 5,   5.00, 1),
(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)
;