From 3bb5566d96843eacf453683367580d100328c445 Mon Sep 17 00:00:00 2001 From: Thomas Berezansky Date: Sat, 28 May 2011 21:44:45 -0400 Subject: [PATCH] Upgrade script, needs wrapping. Signed-off-by: Thomas Berezansky Signed-off-by: Bill Erickson --- .../src/sql/Pg/upgrade/XXXX.sip_statcats.sql | 74 +++++++++++++++++++ 1 file changed, 74 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.sip_statcats.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.sip_statcats.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.sip_statcats.sql new file mode 100644 index 0000000000..aa9c5cd956 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.sip_statcats.sql @@ -0,0 +1,74 @@ +CREATE TABLE actor.stat_cat_sip_fields ( + field CHAR(2) PRIMARY KEY, + name TEXT NOT NULL, + one_only BOOL NOT NULL DEFAULT FALSE +); +COMMENT ON TABLE actor.stat_cat_sip_fields IS $$ +Actor Statistical Category SIP Fields + +Contains the list of valid SIP Field identifiers for +Statistical Categories. +$$; +ALTER TABLE actor.stat_cat + ADD COLUMN sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + ADD COLUMN sip_format TEXT; + +CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$ +DECLARE + sipfield actor.stat_cat_sip_fields%ROWTYPE; + use_count INT; +BEGIN + IF NEW.sip_field IS NOT NULL THEN + SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field; + IF sipfield.one_only THEN + SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id; + IF use_count > 0 THEN + RAISE EXCEPTION 'Sip field cannot be used twice'; + END IF; + END IF; + END IF; + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE TRIGGER actor_stat_cat_sip_update_trigger + BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW + EXECUTE PROCEDURE actor.stat_cat_check(); + +CREATE TABLE asset.stat_cat_sip_fields ( + field CHAR(2) PRIMARY KEY, + name TEXT NOT NULL, + one_only BOOL NOT NULL DEFAULT FALSE +); +COMMENT ON TABLE asset.stat_cat_sip_fields IS $$ +Asset Statistical Category SIP Fields + +Contains the list of valid SIP Field identifiers for +Statistical Categories. +$$; + +ALTER TABLE asset.stat_cat + ADD COLUMN sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + ADD COLUMN sip_format TEXT; + +CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$ +DECLARE + sipfield asset.stat_cat_sip_fields%ROWTYPE; + use_count INT; +BEGIN + IF NEW.sip_field IS NOT NULL THEN + SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field; + IF sipfield.one_only THEN + SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id; + IF use_count > 0 THEN + RAISE EXCEPTION 'Sip field cannot be used twice'; + END IF; + END IF; + END IF; + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE TRIGGER asset_stat_cat_sip_update_trigger + BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW + EXECUTE PROCEDURE asset.stat_cat_check(); -- 2.43.2