From 85cc3577cc7289910b62e1ec17df6c407ffe6db3 Mon Sep 17 00:00:00 2001 From: scottmk Date: Wed, 21 Jul 2010 15:07:04 +0000 Subject: [PATCH] Two new tables in action schema: fieldset and fieldset_col_val. M Open-ILS/src/sql/Pg/090.schema.action.sql M Open-ILS/src/sql/Pg/999.functions.global.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0346.schema.action-fieldset.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@16994 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 37 ++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 41 ++ Open-ILS/src/sql/Pg/999.functions.global.sql | 1 + .../upgrade/0346.schema.action-fieldset.sql | 361 ++++++++++++++++++ 5 files changed, 441 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0346.schema.action-fieldset.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 2bbde3b113..03fa1dcb85 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6725,6 +6725,43 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 53b3e68166..50d3c9c08b 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0345'); -- gmc +INSERT INTO config.upgrade_log (version) VALUES ('0346'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index b14890ffe9..f13424b8c2 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -480,6 +480,47 @@ CREATE VIEW action.unfulfilled_hold_max_loop AS GROUP BY 1; +CREATE TABLE action.fieldset ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED, + owning_lib INT NOT NULL REFERENCES actor.org_unit (id) + DEFERRABLE INITIALLY DEFERRED, + status TEXT NOT NULL + CONSTRAINT valid_status CHECK ( status in + ( 'PENDING', 'APPLIED', 'ERROR' )), + creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), + scheduled_time TIMESTAMPTZ, + applied_time TIMESTAMPTZ, + classname TEXT NOT NULL, -- an IDL class name + name TEXT NOT NULL, + stored_query INT REFERENCES query.stored_query (id) + DEFERRABLE INITIALLY DEFERRED, + pkey_value TEXT, + CONSTRAINT lib_name_unique UNIQUE (owning_lib, name), + CONSTRAINT fieldset_one_or_the_other CHECK ( + (stored_query IS NOT NULL AND pkey_value IS NULL) OR + (pkey_value IS NOT NULL AND stored_query IS NULL) + ) + -- the CHECK constraint means we can update the fields for a single + -- row without all the extra overhead involved in a query +); + +CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time ); +CREATE INDEX action_owner_idx ON action.fieldset( owner ); + + +CREATE TABLE action.fieldset_col_val ( + id SERIAL PRIMARY KEY, + fieldset INT NOT NULL REFERENCES action.fieldset + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + col TEXT NOT NULL, -- "field" from the idl ... the column on the table + val TEXT, -- value for the column ... NULL means, well, NULL + CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col) +); + + -- represents a circ chain summary CREATE TYPE action.circ_chain_summary AS ( num_circs INTEGER, diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 4e25f7d9b6..4850d6c2c5 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -434,6 +434,7 @@ BEGIN DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr; UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr; DELETE FROM action.survey_response WHERE usr = src_usr; + UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr; -- actor.* DELETE FROM actor.card WHERE usr = src_usr; diff --git a/Open-ILS/src/sql/Pg/upgrade/0346.schema.action-fieldset.sql b/Open-ILS/src/sql/Pg/upgrade/0346.schema.action-fieldset.sql new file mode 100644 index 0000000000..fdd28f3a70 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0346.schema.action-fieldset.sql @@ -0,0 +1,361 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0346'); -- Scott McKellar + +CREATE TABLE action.fieldset ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED, + owning_lib INT NOT NULL REFERENCES actor.org_unit (id) + DEFERRABLE INITIALLY DEFERRED, + status TEXT NOT NULL + CONSTRAINT valid_status CHECK ( status in + ( 'PENDING', 'APPLIED', 'ERROR' )), + creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), + scheduled_time TIMESTAMPTZ, + applied_time TIMESTAMPTZ, + classname TEXT NOT NULL, -- an IDL class name + name TEXT NOT NULL, + stored_query INT REFERENCES query.stored_query (id) + DEFERRABLE INITIALLY DEFERRED, + pkey_value TEXT, + CONSTRAINT lib_name_unique UNIQUE (owning_lib, name), + CONSTRAINT fieldset_one_or_the_other CHECK ( + (stored_query IS NOT NULL AND pkey_value IS NULL) OR + (pkey_value IS NOT NULL AND stored_query IS NULL) + ) + -- the CHECK constraint means we can update the fields for a single + -- row without all the extra overhead involved in a query +); + +CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time ); +CREATE INDEX action_owner_idx ON action.fieldset( owner ); + + +CREATE TABLE action.fieldset_col_val ( + id SERIAL PRIMARY KEY, + fieldset INT NOT NULL REFERENCES action.fieldset + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + col TEXT NOT NULL, -- "field" from the idl ... the column on the table + val TEXT, -- value for the column ... NULL means, well, NULL + CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col) +); + +CREATE OR REPLACE FUNCTION actor.usr_purge_data( + src_usr IN INTEGER, + dest_usr IN INTEGER +) RETURNS VOID AS $$ +DECLARE + suffix TEXT; + renamable_row RECORD; +BEGIN + + UPDATE actor.usr SET + active = FALSE, + card = NULL, + mailing_address = NULL, + billing_address = NULL + WHERE id = src_usr; + + -- acq.* + UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr; + UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr; + UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr; + UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr; + UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr; + UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr; + DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr; + + -- Update with a rename to avoid collisions + FOR renamable_row in + SELECT id, name + FROM acq.picklist + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE acq.picklist + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + + UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr; + UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr; + UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr; + UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr; + UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr; + UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr; + UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr; + UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr; + + -- action.* + DELETE FROM action.circulation WHERE usr = src_usr; + UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr; + UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr; + UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr; + UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr; + UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr; + DELETE FROM action.hold_request WHERE usr = src_usr; + UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr; + UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr; + DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr; + UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr; + DELETE FROM action.survey_response WHERE usr = src_usr; + UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr; + + -- actor.* + DELETE FROM actor.card WHERE usr = src_usr; + DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr; + + -- The following update is intended to avoid transient violations of a foreign + -- key constraint, whereby actor.usr_address references itself. It may not be + -- necessary, but it does no harm. + UPDATE actor.usr_address SET replaces = NULL + WHERE usr = src_usr AND replaces IS NOT NULL; + DELETE FROM actor.usr_address WHERE usr = src_usr; + DELETE FROM actor.usr_note WHERE usr = src_usr; + UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr; + DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr; + UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr; + DELETE FROM actor.usr_setting WHERE usr = src_usr; + DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr; + UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr; + + -- asset.* + UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr; + UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr; + UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr; + UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr; + UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr; + UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr; + + -- auditor.* + DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr; + DELETE FROM auditor.actor_usr_history WHERE id = src_usr; + UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr; + UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr; + UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr; + UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr; + UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr; + UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr; + + -- biblio.* + UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr; + UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr; + UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr; + UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr; + + -- container.* + -- Update buckets with a rename to avoid collisions + FOR renamable_row in + SELECT id, name + FROM container.biblio_record_entry_bucket + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE container.biblio_record_entry_bucket + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + + FOR renamable_row in + SELECT id, name + FROM container.call_number_bucket + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE container.call_number_bucket + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + + FOR renamable_row in + SELECT id, name + FROM container.copy_bucket + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE container.copy_bucket + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + + FOR renamable_row in + SELECT id, name + FROM container.user_bucket + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE container.user_bucket + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + + DELETE FROM container.user_bucket_item WHERE target_user = src_usr; + + -- money.* + DELETE FROM money.billable_xact WHERE usr = src_usr; + DELETE FROM money.collections_tracker WHERE usr = src_usr; + UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr; + + -- permission.* + DELETE FROM permission.usr_grp_map WHERE usr = src_usr; + DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr; + DELETE FROM permission.usr_perm_map WHERE usr = src_usr; + DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr; + + -- reporter.* + -- Update with a rename to avoid collisions + BEGIN + FOR renamable_row in + SELECT id, name + FROM reporter.output_folder + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE reporter.output_folder + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + EXCEPTION WHEN undefined_table THEN + -- do nothing + END; + + BEGIN + UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr; + EXCEPTION WHEN undefined_table THEN + -- do nothing + END; + + -- Update with a rename to avoid collisions + BEGIN + FOR renamable_row in + SELECT id, name + FROM reporter.report_folder + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE reporter.report_folder + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + EXCEPTION WHEN undefined_table THEN + -- do nothing + END; + + BEGIN + UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr; + EXCEPTION WHEN undefined_table THEN + -- do nothing + END; + + BEGIN + UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr; + EXCEPTION WHEN undefined_table THEN + -- do nothing + END; + + -- Update with a rename to avoid collisions + BEGIN + FOR renamable_row in + SELECT id, name + FROM reporter.template_folder + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE reporter.template_folder + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + EXCEPTION WHEN undefined_table THEN + -- do nothing + END; + + -- vandelay.* + -- Update with a rename to avoid collisions + FOR renamable_row in + SELECT id, name + FROM vandelay.queue + WHERE owner = src_usr + LOOP + suffix := ' (' || src_usr || ')'; + LOOP + BEGIN + UPDATE vandelay.queue + SET owner = dest_usr, name = name || suffix + WHERE id = renamable_row.id; + EXCEPTION WHEN unique_violation THEN + suffix := suffix || ' '; + CONTINUE; + END; + EXIT; + END LOOP; + END LOOP; + +END; +$$ LANGUAGE plpgsql; +COMMIT; -- 2.43.2