-- Evergreen DB patch 0546.schema.sip_statcats.sql BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0546', :eg_version); 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(); COMMIT;