/*************************************************************************************
 *************************************************************************************
 * 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 bestandteil_berechne_gesamtpreis()  CASCADE;
DROP FUNCTION IF EXISTS rechner_berechne_gesamtpreis()      CASCADE;

DROP VIEW  IF EXISTS rechner_mit_gesamtpreis CASCADE;

DROP TABLE IF EXISTS bestandteil CASCADE;
DROP TABLE IF EXISTS komponente  CASCADE;
DROP TABLE IF EXISTS rechner     CASCADE;
DROP TABLE IF EXISTS basispreis  CASCADE;

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

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

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

   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
  );

/*************************************************************************************
 * Views
 *************************************************************************************/

-- Achtung: Der Gesamtpreis eines Computers darf nicht NULL sein.
--          Da der Gesamtpreis für alle Computer mit Hilfe von Triggern
--          und folgender View ermittelt wird, muss diese für *ALLE* 
--          Computer ein Ergebnis liefern. Dies wird mit LEFT JOINs und
--          COALESCE erreicht.  

CREATE VIEW rechner_mit_gesamtpreis(rnr, name, gesamtpreis)
AS
SELECT   rnr, name, grundpreis+COALESCE(SUM(preis*anzahl),0.0)*(1.0+gewinn)
FROM     rechner LEFT JOIN bestandteil USING (rnr)
                 LEFT JOIN komponente  USING (knr)
         CROSS JOIN
         basispreis
GROUP BY rnr, grundpreis, gewinn
;


/*************************************************************************************
 * 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
$_plpgsql_$
  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
$_plpgsql_$
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
$_plpgsql_$
  BEGIN
    UPDATE rechner AS c
    SET    gesamtpreis 
             = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis WHERE rnr = c.rnr)
    ;
    RETURN NULL;
  END
$_plpgsql_$
LANGUAGE plpgsql;

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


-- 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
$_plpgsql_$
  BEGIN
    UPDATE rechner AS c
    SET    gesamtpreis
             = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis WHERE rnr = c.rnr)
    WHERE  c.rnr IN
           (SELECT rnr 
            FROM   rechner JOIN bestandteil USING (rnr) 
            WHERE  knr = NEW.knr OR knr = OLD.knr
           )
    ;
    RETURN NULL;
  END
$_plpgsql_$
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
$_plpgsql_$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      UPDATE rechner AS c
      SET    gesamtpreis
               = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis WHERE rnr = c.rnr)
      WHERE  c.rnr = NEW.rnr;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE rechner AS c
      SET    gesamtpreis
               = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis WHERE rnr = c.rnr)
      WHERE  c.rnr = NEW.rnr OR c.rnr = OLD.rnr;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE rechner AS c
      SET    gesamtpreis
               = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis WHERE rnr = c.rnr)
      WHERE  c.rnr = OLD.rnr;
    END IF;
    RETURN NULL;
  END
$_plpgsql_$
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 neuen Computer ohne Komponenten. 
CREATE FUNCTION rechner_berechne_gesamtpreis() RETURNS TRIGGER AS
$_plpgsql_$
  BEGIN
    UPDATE rechner AS c
    SET    gesamtpreis
             = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis WHERE rnr = c.rnr)
    WHERE  c.rnr = NEW.rnr;
    RETURN NULL;
  END
$_plpgsql_$
LANGUAGE plpgsql;

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


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

INSERT INTO basispreis(grundpreis, gewinn)
VALUES
  (100, 0.1)
;

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)
;

/*************************************************************************************
 * Komplexere View 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
$_plpgsql_$
  SELECT COALESCE($1||', '||$2, $1, $2, '');
$_plpgsql_$
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)*(1.0+gewinn),
         gesamtpreis-(grundpreis+COALESCE(SUM(preis*anzahl),0.0)*(1.0+gewinn)), LIST(bezeichnung)
FROM     rechner LEFT JOIN bestandteil USING (rnr)
                 LEFT JOIN komponente  USING (knr)
         CROSS JOIN
         basispreis
GROUP BY rnr, grundpreis, gewinn
;