From 4b297f221ae89775cab0aedb39e01cbd3619bc64 Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 26 Apr 2008 16:05:14 +0000 Subject: [PATCH] sql script for upgrading to a materialized version of the simple record view (reporting speed) git-svn-id: svn://svn.open-ils.org/ILS/trunk@9453 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/upgrade_simple_record.sql | 30 +++++++++++++++++++ 1 file changed, 30 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade_simple_record.sql diff --git a/Open-ILS/src/sql/Pg/upgrade_simple_record.sql b/Open-ILS/src/sql/Pg/upgrade_simple_record.sql new file mode 100644 index 0000000000..a328f0023e --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade_simple_record.sql @@ -0,0 +1,30 @@ + +BEGIN; + +CREATE TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.simple_record WHERE 1=0; + +INSERT INTO reporter.materialized_simple_record + (id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn) + SELECT DISTINCT ON (id) * FROM reporter.super_simple_record; + +ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id); + +CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP IN ('UPDATE','DELETE') THEN + DELETE FROM reporter.materialized_simple_record WHERE id = OLD.record; + END IF; + + IF TG_OP IN ('INSERT','UPDATE') AND NOT NEW.deleted THEN + INSERT INTO reporter.materialized_simple_record SELECT * FROM reporter.simple_record WHERE id = NEW.record; + END IF; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER zzz_update_materialized_simple_record_tgr + AFTER INSERT OR UPDATE OR DELETE ON metabib.full_rec + FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync(); + +COMMIT; + -- 2.43.2