/************************************************************************************* ************************************************************************************* * 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) 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 ); /************************************************************************************* * Views *************************************************************************************/ -- Achtung: Der Gesamtpreis eines Rechners darf nicht NULL sein. -- Da der Gesamtpreis für alle Rechner mit Hilfe von Triggern -- und folgender View ermittelt wird, muss diese für *ALLE* -- Rechner ein Ergebnis liefern. Dies wird mit LEFT JOINs und -- COALESCE erreicht. CREATE VIEW rechner_mit_gesamtpreis(rnr, name, gesamtpreis) AS SELECT r.rnr, name, grundpreis+COALESCE(SUM(preis*anzahl),0.0)*(1.0+gewinn) FROM rechner r LEFT JOIN bestandteil b ON r.rnr = b.rnr LEFT JOIN komponente k ON b.knr = k.knr CROSS JOIN basispreis GROUP BY r.rnr, grundpreis, gewinn ; /************************************************************************************* * Trigger * * http://www.postgresql.org/docs/current/static/sql-createtrigger.html * http://www.postgresql.org/docs/current/static/plpgsql-trigger.html *************************************************************************************/ CREATE FUNCTION singleton() RETURNS TRIGGER LANGUAGE plpgsql AS $$ 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 $$ ; -- 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(); -- Initialisiere einen neuen Computer ohne Komponenten. CREATE FUNCTION rechner_berechne_gesamtpreis() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN UPDATE rechner AS r SET gesamtpreis = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis rg WHERE rg.rnr = r.rnr ) WHERE r.rnr = NEW.rnr; RETURN NULL; END $$ ; CREATE TRIGGER rechner_berechne_gesamtpreis AFTER INSERT ON rechner FOR EACH ROW EXECUTE PROCEDURE rechner_berechne_gesamtpreis() ; CREATE FUNCTION bestandteil_berechne_gesamtpreis() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF (TG_OP = 'INSERT') THEN UPDATE rechner AS r SET gesamtpreis = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis rg WHERE rg.rnr = r.rnr ) WHERE r.rnr = NEW.rnr; ELSIF (TG_OP = 'UPDATE') THEN UPDATE rechner AS r SET gesamtpreis = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis rg WHERE rg.rnr = r.rnr ) WHERE r.rnr IN (OLD.rnr, NEW.rnr); ELSIF (TG_OP = 'DELETE') THEN UPDATE rechner AS r SET gesamtpreis = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis rg WHERE rg.rnr = r.rnr ) WHERE r.rnr = OLD.rnr; END IF; RETURN NULL; END $$ ; CREATE TRIGGER bestandteil_berechne_gesamtpreis AFTER INSERT OR UPDATE OR DELETE ON bestandteil FOR EACH ROW EXECUTE PROCEDURE bestandteil_berechne_gesamtpreis(); CREATE FUNCTION basispreis_berechne_gesamtpreis() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN UPDATE rechner AS r SET gesamtpreis = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis rg WHERE rg.rnr = r.rnr ) ; RETURN NULL; END $$ ; CREATE TRIGGER basispreis_berechne_gesamtpreis AFTER INSERT OR UPDATE ON basispreis FOR STATEMENT EXECUTE PROCEDURE basispreis_berechne_gesamtpreis(); -- Bei INSERT oder DELETE einer Komponente ändert sich der Gesamtpreis -- keines einzigen Rechners, da eine Beziehung "bestandteil" -- bei INSERT erst danach erstellt werden kann und bei DELETE -- zuvor aufgelöst worden sein muss(wg. des zugehörigen Foreign Keys). CREATE FUNCTION komponente_berechne_gesamtpreis() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN UPDATE rechner AS r SET gesamtpreis = (SELECT gesamtpreis FROM rechner_mit_gesamtpreis rg WHERE rg.rnr = r.rnr) WHERE r.rnr IN (SELECT r.rnr FROM rechner r JOIN bestandteil b ON r.rnr = b.rnr WHERE knr IN (OLD.knr, NEW.knr) ) ; RETURN NULL; END $$ ; CREATE TRIGGER komponente_berechne_gesamtpreis AFTER UPDATE ON komponente FOR EACH ROW EXECUTE PROCEDURE komponente_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; CREATE VIEW rechner2(rnr, name, gesamtpreis, selbstkosten, gewinn, komponenten) AS SELECT r.rnr, name, gesamtpreis, grundpreis+COALESCE(SUM(preis*anzahl),0.0), gesamtpreis-(grundpreis+COALESCE(SUM(preis*anzahl),0.0)), JSON_OBJECT_AGG(COALESCE(bezeichnung,''), COALESCE(anzahl,0)) FROM rechner r LEFT JOIN bestandteil b ON r.rnr = b.rnr LEFT JOIN komponente k ON b.knr = k.knr CROSS JOIN basispreis GROUP BY r.rnr, grundpreis ; SELECT * FROM rechner2;