/****************************************************************************** * Alten DB-Inhalt löschen ******************************************************************************/ DROP TRIGGER IF EXISTS liefert_trigger ON liefert; DROP FUNCTION IF EXISTS liefert_function(); DROP TRIGGER IF EXISTS ware_trigger ON ware; DROP FUNCTION IF EXISTS ware_function(); DROP TRIGGER IF EXISTS typ_trigger ON typ; DROP FUNCTION IF EXISTS typ_function(); DROP TRIGGER IF EXISTS haendler_trigger ON haendler; DROP FUNCTION IF EXISTS haendler_function(); DROP TRIGGER IF EXISTS volltext_update_trigger ON volltext; DROP FUNCTION IF EXISTS volltext_update_function(); DROP INDEX IF EXISTS volltext_index; DROP VIEW IF EXISTS volltext_info; DROP VIEW IF EXISTS haendler_liefert_ware_german; DROP VIEW IF EXISTS haendler_liefert_ware_phonet1; DROP VIEW IF EXISTS haendler_liefert_ware_volltext; DROP VIEW IF EXISTS haendler_liefert_ware; DROP TABLE IF EXISTS volltext; DROP TABLE IF EXISTS liefert; DROP TABLE IF EXISTS ware; DROP TABLE IF EXISTS haendler; DROP TABLE IF EXISTS typ; DROP DOMAIN IF EXISTS D_LIEFERZEIT; /****************************************************************************** * Domains und Tabellen erstellen ******************************************************************************/ CREATE DOMAIN D_LIEFERZEIT AS SMALLINT CHECK (value > 0); CREATE TABLE typ (t_id INTEGER NOT NULL, t_name VARCHAR(20) NOT NULL, t_supertyp_id INTEGER, CONSTRAINT pk_typ PRIMARY KEY (t_id), CONSTRAINT fk_typ_typ FOREIGN KEY (t_supertyp_id) REFERENCES typ (t_id), CONSTRAINT unique_typ_t_name UNIQUE (t_name) ); CREATE TABLE haendler (h_id INTEGER NOT NULL, h_name VARCHAR(30) NOT NULL, h_ortschaft VARCHAR(50), CONSTRAINT pk_haendler PRIMARY KEY (h_id), CONSTRAINT unique_haendler_name_ortschaft UNIQUE (h_name, h_ortschaft) ); CREATE TABLE ware (w_id INTEGER NOT NULL, t_id INTEGER NOT NULL DEFAULT 0, w_bezeichnung VARCHAR(50) NOT NULL, CONSTRAINT pk_ware PRIMARY KEY (w_id), CONSTRAINT fk_ware_typ FOREIGN KEY (t_id) REFERENCES typ (t_id), CONSTRAINT unique_ware_t_id_bezeichnung UNIQUE (t_id, w_bezeichnung) ); CREATE TABLE 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 haendler (h_id), CONSTRAINT fk_liefert_ware FOREIGN KEY (w_id) REFERENCES ware (w_id) ); CREATE TABLE volltext (v_id INTEGER NOT NULL, v_volltext TSVECTOR NOT NULL, h_id INTEGER, w_id INTEGER, t_id INTEGER, l_preis NUMERIC(8,2), CONSTRAINT pk_volltext PRIMARY KEY (v_id), CONSTRAINT fk_volltext_haendler FOREIGN KEY (h_id) REFERENCES haendler(h_id), CONSTRAINT fk_volltext_ware FOREIGN KEY (w_id) REFERENCES ware(w_id), CONSTRAINT fk_volltext_typ FOREIGN KEY (t_id) REFERENCES typ(t_id) ); /****************************************************************************** * Indexe ******************************************************************************/ /* Volltextindex */ CREATE INDEX volltext_index ON volltext USING gin(v_volltext); /* Sekundärindexe */ CREATE INDEX heandler_volltext ON volltext USING btree (h_id, w_id, l_preis); CREATE INDEX ware_volltext ON volltext USING btree (w_id, h_id, l_preis); CREATE INDEX liefert_volltext ON volltext USING btree (l_preis,w_id, h_id); CREATE INDEX typ_volltext ON volltext USING btree (t_id, w_id); /****************************************************************************** * Views ******************************************************************************/ DROP VIEW IF EXISTS haendler_liefert_ware; CREATE VIEW haendler_liefert_ware (h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name ) AS SELECT h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name FROM haendler FULL JOIN liefert USING (h_id) FULL JOIN ware USING (w_id) FULL JOIN typ USING (t_id) UNION DISTINCT SELECT h_id, h_name, h_ortschaft, NULL, NULL, NULL, NULL, NULL, NULL FROM haendler UNION DISTINCT SELECT NULL, NULL, NULL, NULL, NULL, w_id, w_bezeichnung, t_id, t_name FROM ware JOIN typ USING (t_id) UNION DISTINCT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, t_id, t_name FROM typ ; DROP VIEW IF EXISTS haendler_liefert_ware_volltext; CREATE VIEW haendler_liefert_ware_volltext (volltext, h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name ) AS SELECT COALESCE(h_name, '') || ' ' || COALESCE(h_ortschaft, '') || ' ' || COALESCE(CAST(l_preis AS VARCHAR), '') || ' ' || COALESCE(CAST(l_lieferzeit AS VARCHAR), '') || ' ' || COALESCE(w_bezeichnung, '') || ' ' || COALESCE(t_name, ''), h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name FROM haendler_liefert_ware ; DROP VIEW IF EXISTS haendler_liefert_ware_german; CREATE VIEW haendler_liefert_ware_german (v_volltext, h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name ) AS SELECT to_tsvector('german', volltext), h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name FROM haendler_liefert_ware_volltext ; -- Wenn die Extension dict_phone existiert, können noch -- weitere Volltext-Indexe definiert werden: DO --TBD: Make it DRY! $_plpgsql_$ BEGIN IF (SELECT EXISTS(SELECT * FROM pg_extension WHERE extname = 'dict_phonet')) THEN DROP TEXT SEARCH DICTIONARY IF EXISTS phonet1 CASCADE; CREATE TEXT SEARCH DICTIONARY phonet1 ( TEMPLATE = phonet_template, ENCODING = 'utf8', -- 'utf8', 'latin1' LANGUAGE = 'german', -- 'german', 'no language' RULES = '1', -- '1', '2' MAXLEN = '100' -- '100', '' ); CREATE TEXT SEARCH CONFIGURATION phonet1 ( PARSER = phonet_parser ); ALTER TEXT SEARCH CONFIGURATION phonet1 ALTER MAPPING FOR word WITH phonet1; DROP VIEW IF EXISTS haendler_liefert_ware_phonet1; CREATE VIEW haendler_liefert_ware_phonet1 (v_volltext, h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name ) AS SELECT to_tsvector('phonet1', volltext), h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name FROM haendler_liefert_ware_volltext ; END IF; END $_plpgsql_$; DROP VIEW IF EXISTS volltext_info; CREATE VIEW volltext_info (v_volltext, h_id, h_name, h_ortschaft, l_preis, l_lieferzeit, w_id, w_bezeichnung, t_id, t_name ) AS SELECT v_volltext, h.h_id, h_name, h_ortschaft, l.l_preis, l_lieferzeit, w.w_id, w_bezeichnung, t.t_id, t_name FROM volltext v LEFT JOIN haendler h ON v.h_id = h.h_id LEFT JOIN ware w ON v.w_id = w.w_id LEFT JOIN typ t ON v.t_id = t.t_id LEFT JOIN liefert l ON v.h_id = l.h_id AND v.w_id = l.w_id AND v.l_preis = l.l_preis ; /****************************************************************************** * Trigger und Rules (= PostgreSQL-spezifische Trigger) ******************************************************************************/ DROP TRIGGER IF EXISTS volltext_update_trigger ON volltext; DROP FUNCTION IF EXISTS volltext_update_function(); CREATE FUNCTION volltext_update_function() RETURNS TRIGGER AS $_plpgsql_$ BEGIN NEW.v_id = COALESCE((SELECT MAX(v_id)+1 FROM volltext), 1); RETURN NEW; END $_plpgsql_$ LANGUAGE plpgsql; CREATE TRIGGER volltext_update_trigger BEFORE INSERT ON volltext FOR EACH ROW EXECUTE PROCEDURE volltext_update_function(); ; DROP FUNCTION IF EXISTS haendler_function(); CREATE FUNCTION haendler_function() RETURNS TRIGGER AS $_plpgsql_$ BEGIN IF (TG_OP IN ('UPDATE', 'DELETE')) THEN DELETE FROM volltext WHERE h_id = OLD.h_id ; END IF; IF (TG_OP IN ('INSERT', 'UPDATE')) THEN DELETE FROM volltext WHERE h_id = NEW.h_id ; INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis) SELECT v_volltext, h_id, w_id, t_id, l_preis FROM haendler_liefert_ware_german WHERE h_id = NEW.h_id ; END IF; RETURN NULL; END $_plpgsql_$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS haendler_trigger ON haendler; CREATE TRIGGER haendler_trigger AFTER INSERT OR UPDATE OR DELETE ON haendler FOR EACH ROW EXECUTE PROCEDURE haendler_function(); ; DROP FUNCTION IF EXISTS typ_function(); CREATE FUNCTION typ_function() RETURNS TRIGGER AS $_plpgsql_$ BEGIN IF (TG_OP IN ('UPDATE', 'DELETE')) THEN DELETE FROM volltext WHERE t_id = OLD.t_id ; END IF; IF (TG_OP IN ('INSERT', 'UPDATE')) THEN DELETE FROM volltext WHERE t_id = NEW.t_id ; INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis) SELECT v_volltext, h_id, w_id, t_id, l_preis FROM haendler_liefert_ware_german WHERE t_id = NEW.t_id ; END IF; RETURN NULL; END $_plpgsql_$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS typ_trigger ON typ; CREATE TRIGGER typ_trigger AFTER INSERT OR UPDATE OR DELETE ON typ FOR EACH ROW EXECUTE PROCEDURE typ_function(); ; DROP FUNCTION IF EXISTS ware_function(); CREATE FUNCTION ware_function() RETURNS TRIGGER AS $_plpgsql_$ BEGIN IF (TG_OP IN ('UPDATE', 'DELETE')) THEN DELETE FROM volltext WHERE w_id = OLD.w_id ; END IF; IF (TG_OP IN ('INSERT', 'UPDATE')) THEN DELETE FROM volltext WHERE w_id = NEW.w_id ; INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis) SELECT v_volltext, h_id, w_id, t_id, l_preis FROM haendler_liefert_ware_german WHERE w_id = NEW.w_id ; END IF; RETURN NULL; END $_plpgsql_$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS ware_trigger ON ware; CREATE TRIGGER ware_trigger AFTER INSERT OR UPDATE OR DELETE ON ware FOR EACH ROW EXECUTE PROCEDURE ware_function(); ; DROP FUNCTION IF EXISTS liefert_function(); CREATE FUNCTION liefert_function() RETURNS TRIGGER AS $_plpgsql_$ BEGIN IF (TG_OP IN ('UPDATE', 'DELETE')) THEN DELETE FROM volltext WHERE h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis ; END IF; IF (TG_OP IN ('INSERT', 'UPDATE')) THEN DELETE FROM volltext WHERE h_id = NEW.h_id AND w_id = NEW.w_id AND l_preis = NEW.l_preis ; INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis) SELECT v_volltext, h_id, w_id, t_id, l_preis FROM haendler_liefert_ware_german WHERE h_id = NEW.h_id AND w_id = NEW.w_id AND l_preis = NEW.l_preis ; END IF; RETURN NULL; END $_plpgsql_$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS liefert_trigger ON liefert; CREATE TRIGGER liefert_trigger AFTER INSERT OR UPDATE OR DELETE ON liefert FOR EACH ROW EXECUTE PROCEDURE liefert_function(); ; /****************************************************************************** * Daten ******************************************************************************/ 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, t_id, w_bezeichnung) VALUES (1, 2, 'Pentium IV 3,8'), (2, 2, 'Celeron 2,6'), (3, 2, 'Athlon XP 3000+'), (4, 3, 'SDRAM 1GB'), (5, 0, 'Eieruhr') ; 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) ; /****************************************************************************** * Test ******************************************************************************/ SELECT * FROM volltext_info ; SELECT 'volltext' AS query , * FROM volltext_info UNION SELECT 'view', * FROM haendler_liefert_ware_german ORDER BY query, h_id, w_id ; SELECT 'volltext' AS query, vi.*, 'view' AS query, hlwg.* FROM volltext_info vi FULL JOIN haendler_liefert_ware_german hlwg ON vi.v_volltext = hlwg.v_volltext ORDER BY vi.h_id, vi.w_id ; /* UPDATE haendler SET h_ortschaft = 'Rosenheim' WHERE h_id = 1 ; SELECT * FROM volltext_info ORDER BY h_id, w_id ; UPDATE typ SET t_name = 'Prozessor' WHERE t_id = 2 ; SELECT * FROM volltext_info ORDER BY h_id, w_id ; */ SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', 'CPU') ; SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', 'Königsbrunn') AND l_preis IS NULL ; SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', 'Königsbrunn') AND l_preis IS NOT NULL ; SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', 'Königsbrunn & Pentium') ; SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', 'Königsbrunn & IV & 3,8') ; SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', '190.00') ; SELECT * FROM volltext_info WHERE v_volltext @@ to_tsquery('german', 'Muller') ;