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

DROP FUNCTION IF EXISTS singleton()                         CASCADE;
DROP FUNCTION IF EXISTS basispreis_berechne_gesamtpreis()   CASCADE;
DROP FUNCTION IF EXISTS komponente_berechne_gesamtpreis()   CASCADE;
DROP FUNCTION IF EXISTS komponente_on_delete_cascade()      CASCADE;
DROP FUNCTION IF EXISTS bestandteil_berechne_gesamtpreis()  CASCADE;
DROP FUNCTION IF EXISTS rechner_berechne_gesamtpreis()      CASCADE;


/*************************************************************************************
 * Tabellen
 *************************************************************************************/

CREATE TABLE basispreis
  (grundpreis NUMERIC(8,2) NOT NULL
  );

CREATE TABLE rechner
  (rnr         INTEGER      NOT NULL,
   name        VARCHAR(30)  NOT NULL,
   gesamtpreis NUMERIC(8,2) NOT NULL DEFAULT 0.00,

   CONSTRAINT pk_rechner
     PRIMARY KEY (rnr),

   CONSTRAINT unique_name
     UNIQUE (name)
  );

CREATE TABLE komponente
  (knr          INTEGER      NOT NULL,
   bezeichnung  VARCHAR(50)  NOT NULL,
   preis        NUMERIC(8,2) NOT NULL,

   CONSTRAINT pk_komponente
     PRIMARY KEY (knr),

   CONSTRAINT unique_bezeichnung
     UNIQUE (bezeichnung)
  );
                
CREATE TABLE bestandteil
  (rnr    INTEGER NOT NULL,
   knr    INTEGER NOT NULL,
   anzahl INTEGER NOT NULL CHECK (anzahl > 0),

   CONSTRAINT pk_bestandteil
     PRIMARY KEY (rnr,knr),

   CONSTRAINT fk_bestandteil_rechner
     FOREIGN KEY (rnr) REFERENCES rechner (rnr),

   CONSTRAINT fk_bestandteil_komponente
     FOREIGN KEY (knr) REFERENCES komponente (knr)
     -- ON DELETE CASCADE -- um Komponenten direkt löschen zu können
     -- Funktioniert nicht korrekt. Siehe Anmerkung zum Trigger
     -- komponente_on_delete_cascade.
  );

/*************************************************************************************
 * Trigger
 *
 * http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
 * http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html
 *************************************************************************************/




CREATE FUNCTION singleton() RETURNS TRIGGER AS
$_SQL_$
  DECLARE
    one_element_only BOOLEAN;
  BEGIN
    -- Erstelle die SQL-Anfrage dynamisch zur Laufzeit. TG_TABLE_NAME enthält
    -- den Namen der Singleton-Tabelle. 
    IF TG_WHEN = 'BEFORE'
    THEN
      EXECUTE 'SELECT COUNT(*)<>1 FROM ' || TG_TABLE_NAME INTO one_element_only;
    ELSE
      EXECUTE 'SELECT COUNT(*)=1 FROM ' || TG_TABLE_NAME INTO one_element_only;
    END IF;
    IF NOT one_element_only
    THEN
      RAISE EXCEPTION '"%" ist eine Singleton-Tabelle', TG_TABLE_NAME;
    END IF;
    RETURN NULL; 
  END
$_SQL_$
LANGUAGE plpgsql;

-- Damit andere Trigger, die auf die Singleton-Eigenschaft vertrauen,
-- nicht vorher anschlagen, muss der Singleton-Test so früh wie möglich stattfinden.
CREATE TRIGGER basispreis_singleton_before BEFORE INSERT OR DELETE ON basispreis 
FOR STATEMENT EXECUTE PROCEDURE singleton();
-- Auf den AFTER-Test kann dennoch nicht verzichtet werden, wenn auch beim
-- allerersten Insert sichergestellt werden soll, dass genau ein Element
-- eingefügt wird.
CREATE TRIGGER basispreis_singleton_after AFTER INSERT ON basispreis 
FOR STATEMENT EXECUTE PROCEDURE singleton();




CREATE FUNCTION basispreis_berechne_gesamtpreis() RETURNS TRIGGER AS
$_SQL_$
  BEGIN
    IF TG_OP = 'UPDATE'
    THEN
      UPDATE rechner 
      SET    gesamtpreis = gesamtpreis - OLD.grundpreis
      ;
    END IF;
    UPDATE rechner
    SET    gesamtpreis = gesamtpreis + NEW.grundpreis
    ;
    RETURN NULL;
  END
$_SQL_$
LANGUAGE plpgsql;

CREATE TRIGGER basispreis_berechne_gesamtpreis AFTER INSERT OR UPDATE ON basispreis
FOR ROW EXECUTE PROCEDURE basispreis_berechne_gesamtpreis();  




-- PostgreSQL-Fehler: Das Löschen einer Komponente hat wg.
-- ON DELETE CASCADE das Löschen der zugehörigen Bestandteilbeziehungen
-- zur Folge. Allerdings wird in diesem Fall der zugehörige 
-- DELETE-Trigger nicht korrekt ausgelöst. :-(
-- Der genaue Grund ist noch unklar, das in trigger1.sql
-- ON DELETE CASCADE koorekt funktioniert.
-- Lösung: ON DELETE CASCADE per Tigger nachbilden.
CREATE FUNCTION komponente_on_delete_cascade() RETURNS TRIGGER AS
$_SQL_$
  BEGIN
    DELETE FROM bestandteil
    WHERE  knr = OLD.knr;
    RETURN NULL;
  END
$_SQL_$
LANGUAGE plpgsql;

CREATE TRIGGER komponente_on_delete_cascade BEFORE DELETE ON komponente
FOR EACH ROW EXECUTE PROCEDURE komponente_on_delete_cascade();  


-- Bei INSERT oder DELETE ändert sich der Gesamtpreis keines einzigen
-- Computers, da eine Beziehung "bestandteil" bei INSERT erst danach 
-- erstellt werden kann und DELETE zuvor aufgelöst worden sein muss
-- (wg. des zugehörigen Foreign Keys).
CREATE FUNCTION komponente_berechne_gesamtpreis() RETURNS TRIGGER AS
$_SQL_$
  BEGIN
    UPDATE rechner AS c
    SET    gesamtpreis = gesamtpreis-OLD.preis*b.anzahl
    FROM   bestandteil b
    WHERE  c.rnr = b.rnr AND b.knr=OLD.knr
    ;
    UPDATE rechner AS c
    SET    gesamtpreis = gesamtpreis+NEW.preis*b.anzahl
    FROM   bestandteil b
    WHERE  c.rnr = b.rnr AND b.knr=NEW.knr
    ;
    RETURN NULL;
  END
$_SQL_$
LANGUAGE plpgsql;

CREATE TRIGGER komponente_berechne_gesamtpreis AFTER UPDATE ON komponente
FOR EACH ROW EXECUTE PROCEDURE komponente_berechne_gesamtpreis();  





CREATE FUNCTION bestandteil_berechne_gesamtpreis() RETURNS TRIGGER AS
$_SQL_$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      UPDATE rechner AS c
      SET    gesamtpreis = gesamtpreis+k.preis*NEW.anzahl
      FROM   komponente k 
      WHERE  c.rnr = NEW.rnr AND k.knr = NEW.knr
      ;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE rechner AS c
      SET    gesamtpreis = gesamtpreis-k.preis*OLD.anzahl
      FROM   komponente k 
      WHERE  c.rnr = OLD.rnr AND k.knr = OLD.knr
      ;
      UPDATE rechner AS c
      SET    gesamtpreis = gesamtpreis+k.preis*NEW.anzahl
      FROM   komponente k 
      WHERE  c.rnr = NEW.rnr AND k.knr = NEW.knr
      ;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE rechner AS c
      SET    gesamtpreis = gesamtpreis-k.preis*OLD.anzahl
      FROM   komponente k 
      WHERE  c.rnr = OLD.rnr AND k.knr = OLD.knr
      ;
    END IF;
    RETURN NULL;
  END
$_SQL_$
LANGUAGE plpgsql;

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




-- Initialisiere einen neunen Computer ohne Komponenten. Dieser kostet den Grundpreis.
CREATE FUNCTION rechner_berechne_gesamtpreis() RETURNS TRIGGER AS
$_SQL_$
  BEGIN
    UPDATE rechner AS c
    SET    gesamtpreis = (SELECT grundpreis FROM basispreis)
    WHERE  c.rnr = NEW.rnr;
    RETURN NULL;
  END
$_SQL_$
LANGUAGE plpgsql;

CREATE TRIGGER rechner_berechne_gesamtpreis AFTER INSERT ON rechner
FOR EACH ROW EXECUTE PROCEDURE rechner_berechne_gesamtpreis();




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

INSERT INTO basispreis(grundpreis)
VALUES
  (100)
;

INSERT INTO rechner(rnr, name)
VALUES 
  (1, 'Computer Low-End')
, (2, 'Computer High-End')
;

INSERT INTO komponente(knr, bezeichnung, preis)
VALUES
  (1, 'Gehäuse',     100.00)
, (2, 'Mainboard',   150.00)
, (3, 'CPU',          50.00)
, (4, 'RAM',          20.00)
, (5, 'SSD-Platte',  200.00)
;

INSERT INTO bestandteil(rnr, knr, anzahl)
VALUES 
  (1, 1, 1)
, (1, 2, 1)
, (1, 3, 1)
, (1, 4, 1)
, (1, 5, 1)
, (2, 1, 1)
, (2, 2, 1)
, (2, 3, 2)
, (2, 4, 4)
, (2, 5, 3)
;

/*************************************************************************************
 * Views, nur zum Ausgeben von Test-Ergebnissen
 *************************************************************************************/

DROP VIEW IF EXISTS rechner2;
DROP AGGREGATE IF EXISTS LIST(anyelement) CASCADE;
DROP FUNCTION IF EXISTS list_function(VARCHAR, anyelement) CASCADE;

CREATE OR REPLACE FUNCTION list_function(VARCHAR, anyelement) RETURNS VARCHAR AS
$_SQL_$
  SELECT COALESCE($1||', '||$2, $1, $2, '');
$_SQL_$
LANGUAGE SQL IMMUTABLE;

CREATE AGGREGATE LIST(anyelement)
(
  SFUNC = list_function,
  STYPE = VARCHAR
);

CREATE VIEW rechner2(rnr, name, gesamtpreis, gesamtpreis_berechnet, delta, komponenten)
AS
SELECT   rnr, name, gesamtpreis, grundpreis+COALESCE(SUM(preis*anzahl),0.0),
         gesamtpreis-(grundpreis+COALESCE(SUM(preis*anzahl),0.0)), LIST(bezeichnung)
FROM     rechner LEFT JOIN bestandteil USING (rnr)
                 LEFT JOIN komponente  USING (knr)
         CROSS JOIN
         basispreis
GROUP BY rnr, grundpreis
;

