From 971eac9a3922922edf819077c15efa1b07acb24d Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 31 May 2008 05:17:09 +0000 Subject: [PATCH] some new management functions for the materialized reporting view git-svn-id: svn://svn.open-ils.org/ILS/trunk@9741 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/sql/Pg/1.2.2.0-1.2.2.1-upgrade-db.sql | 44 +++++++++++++++++++ Open-ILS/src/sql/Pg/reporter-schema.sql | 23 ++++++++++ 2 files changed, 67 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/1.2.2.0-1.2.2.1-upgrade-db.sql diff --git a/Open-ILS/src/sql/Pg/1.2.2.0-1.2.2.1-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.2.2.0-1.2.2.1-upgrade-db.sql new file mode 100644 index 0000000000..1f9f4c171c --- /dev/null +++ b/Open-ILS/src/sql/Pg/1.2.2.0-1.2.2.1-upgrade-db.sql @@ -0,0 +1,44 @@ +/* + * Copyright (C) 2008 Equinox Software, Inc. + * Mike Rylander + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + */ + +BEGIN; + +-- Handy management functions for the new materialized reporting view + +CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ + DROP TRIGGER zzz_update_materialized_simple_record_tgr ON metabib.full_rec; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ + + TRUNCATE TABLE reporter.materialized_simple_record; + + 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.old_super_simple_record; + + 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(); + +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.refresh_materialized_simple_record () RETURNS VOID AS $$ + SELECT reporter.disable_materialized_simple_record_trigger(); + SELECT reporter.enable_materialized_simple_record_trigger(); +$$ LANGUAGE SQL; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 8cb513a3ce..2d18273cce 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -184,6 +184,29 @@ 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(); +CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$ + DROP TRIGGER zzz_update_materialized_simple_record_tgr ON metabib.full_rec; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$ + + TRUNCATE TABLE reporter.materialized_simple_record; + + 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.old_super_simple_record; + + 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(); + +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION reporter.refresh_materialized_simple_record () RETURNS VOID AS $$ + SELECT reporter.disable_materialized_simple_record_trigger(); + SELECT reporter.enable_materialized_simple_record_trigger(); +$$ LANGUAGE SQL; + CREATE OR REPLACE VIEW reporter.demographic AS SELECT u.id, u.dob, -- 2.43.2