From fc3d6ea96e26d5d00684604d443e3402b60b4807 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 4 May 2011 05:01:04 +0000 Subject: [PATCH] Addressing LP#732681 at upgrade time -- make authority records useful for controlling bibs git-svn-id: svn://svn.open-ils.org/ILS/trunk@20405 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 232 ++++++------------- 1 file changed, 75 insertions(+), 157 deletions(-) diff --git a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index 6d6451edbd..28c45bad45 100644 --- a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -109,6 +109,9 @@ INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('temp -- Recreate one of the constraints that we just dropped, -- under a different name: +ALTER TABLE booking.resource_type + ALTER COLUMN record TYPE BIGINT; + ALTER TABLE booking.resource_type ADD CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record); @@ -3167,37 +3170,77 @@ INSERT INTO action_trigger.validator (module,description) VALUES ( ) ; --- What was event_definition #15 in v1.6.1 will be recreated as #20. This + +-- The password reset event_definition in v1.6.1 will be moved to #20. This -- renumbering requires some juggling: -- -- 1. Update any child rows to point to #20. These updates will temporarily --- violate foreign key constraints, but that's okay as long as we create +-- violate foreign key constraints, but that's okay as long as we have a -- #20 before committing. -- --- 2. Delete the old #15. +-- 2. Update the id of the password reset event_definition to 20 -- --- 3. Insert the new #15. --- --- 4. Insert #20. --- --- We could combine steps 2 and 3 into a single update, but that would create --- additional opportunities for typos, since we already have the insert from --- an upgrade script. +-- This code might fail in some cases, but should work with all stock 1.6.1 +-- instances, whether fresh or via upgrade UPDATE action_trigger.environment SET event_def = 20 -WHERE event_def = 15; +WHERE event_def = (SELECT id FROM action_trigger.event_definition WHERE hook = 'password.reset_request' ORDER BY id ASC LIMIT 1); UPDATE action_trigger.event SET event_def = 20 -WHERE event_def = 15; +WHERE event_def = (SELECT id FROM action_trigger.event_definition WHERE hook = 'password.reset_request' ORDER BY id ASC LIMIT 1); UPDATE action_trigger.event_params SET event_def = 20 -WHERE event_def = 15; +WHERE event_def = (SELECT id FROM action_trigger.event_definition WHERE hook = 'password.reset_request' ORDER BY id ASC LIMIT 1); + +UPDATE action_trigger.event_definition +SET id = 20 +WHERE id = (SELECT id FROM action_trigger.event_definition WHERE hook = 'password.reset_request' ORDER BY id ASC LIMIT 1); + + +-- Let's also take the opportunity to rebuild the trigger +-- if it got mangled somehow +INSERT INTO action_trigger.hook (key,core_type,description) + SELECT 'password.reset_request','aupr','Patron has requested a self-serve password reset' + WHERE (SELECT COUNT(*) FROM action_trigger.hook WHERE key = 'password.reset_request') = 0; + +INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, template) + SELECT 20, 'f', 1, 'Password reset request notification', 'password.reset_request', 'NOOP_True', 'SendEmail', '00:00:01', +$$ +[%- USE date -%] +[%- user = target.usr -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || user.home_ou.email || default_sender %] +Subject: [% user.home_ou.name %]: library account password reset request + +You have received this message because you, or somebody else, requested a reset +of your library system password. If you did not request a reset of your library +system password, just ignore this message and your current password will +continue to work. + +If you did request a reset of your library system password, please perform +the following steps to continue the process of resetting your password: + +1. Open the following link in a web browser: https://[% params.hostname %]/opac/password/[% params.locale || 'en-US' %]/[% target.uuid %] +The browser displays a password reset form. + +2. Enter your new password in the password reset form in the browser. You must +enter the password twice to ensure that you do not make a mistake. If the +passwords match, you will then be able to log in to your library system account +with the new password. + +$$ + WHERE (SELECT COUNT(*) FROM action_trigger.event_definition WHERE id = 20) = 0; + +INSERT INTO action_trigger.environment ( event_def, path) + SELECT 20, 'usr' + WHERE (SELECT COUNT(*) FROM action_trigger.environment WHERE event_def = 20 AND path = 'usr') = 0; -DELETE FROM action_trigger.event_definition -WHERE id = 15; +INSERT INTO action_trigger.environment ( event_def, path) + SELECT 20, 'usr.home_ou' + WHERE (SELECT COUNT(*) FROM action_trigger.environment WHERE event_def = 20 AND path = 'usr.home_ou') = 0; INSERT INTO action_trigger.event_definition ( id, @@ -8355,138 +8398,6 @@ ALTER TABLE action.transit_copy ADD COLUMN prev_dest INTEGER REFERENCES actor.org_unit( id ) DEFERRABLE INITIALLY DEFERRED; -DROP SCHEMA IF EXISTS booking CASCADE; - -CREATE SCHEMA booking; - -CREATE TABLE booking.resource_type ( - id SERIAL PRIMARY KEY, - name TEXT NOT NULL, - fine_interval INTERVAL, - fine_amount DECIMAL(8,2) NOT NULL DEFAULT 0, - owner INT NOT NULL - REFERENCES actor.org_unit( id ) - DEFERRABLE INITIALLY DEFERRED, - catalog_item BOOLEAN NOT NULL DEFAULT FALSE, - transferable BOOLEAN NOT NULL DEFAULT FALSE, - record BIGINT REFERENCES biblio.record_entry (id) - DEFERRABLE INITIALLY DEFERRED, - max_fine NUMERIC(8,2), - elbow_room INTERVAL, - CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record) -); - -CREATE TABLE booking.resource ( - id SERIAL PRIMARY KEY, - owner INT NOT NULL - REFERENCES actor.org_unit(id) - DEFERRABLE INITIALLY DEFERRED, - type INT NOT NULL - REFERENCES booking.resource_type(id) - DEFERRABLE INITIALLY DEFERRED, - overbook BOOLEAN NOT NULL DEFAULT FALSE, - barcode TEXT NOT NULL, - deposit BOOLEAN NOT NULL DEFAULT FALSE, - deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00, - user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00, - CONSTRAINT br_unique UNIQUE (owner, barcode) -); - --- For non-catalog items: hijack barcode for name/description - -CREATE TABLE booking.resource_attr ( - id SERIAL PRIMARY KEY, - owner INT NOT NULL - REFERENCES actor.org_unit(id) - DEFERRABLE INITIALLY DEFERRED, - name TEXT NOT NULL, - resource_type INT NOT NULL - REFERENCES booking.resource_type(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - required BOOLEAN NOT NULL DEFAULT FALSE, - CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name) -); - -CREATE TABLE booking.resource_attr_value ( - id SERIAL PRIMARY KEY, - owner INT NOT NULL - REFERENCES actor.org_unit(id) - DEFERRABLE INITIALLY DEFERRED, - attr INT NOT NULL - REFERENCES booking.resource_attr(id) - DEFERRABLE INITIALLY DEFERRED, - valid_value TEXT NOT NULL, - CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value) -); - -CREATE TABLE booking.resource_attr_map ( - id SERIAL PRIMARY KEY, - resource INT NOT NULL - REFERENCES booking.resource(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - resource_attr INT NOT NULL - REFERENCES booking.resource_attr(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - value INT NOT NULL - REFERENCES booking.resource_attr_value(id) - DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr) -); - -CREATE TABLE booking.reservation ( - request_time TIMESTAMPTZ NOT NULL DEFAULT now(), - start_time TIMESTAMPTZ, - end_time TIMESTAMPTZ, - capture_time TIMESTAMPTZ, - cancel_time TIMESTAMPTZ, - pickup_time TIMESTAMPTZ, - return_time TIMESTAMPTZ, - booking_interval INTERVAL, - fine_interval INTERVAL, - fine_amount DECIMAL(8,2), - target_resource_type INT NOT NULL - REFERENCES booking.resource_type(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - target_resource INT REFERENCES booking.resource(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - current_resource INT REFERENCES booking.resource(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - request_lib INT NOT NULL - REFERENCES actor.org_unit(id) - DEFERRABLE INITIALLY DEFERRED, - pickup_lib INT REFERENCES actor.org_unit(id) - DEFERRABLE INITIALLY DEFERRED, - capture_staff INT REFERENCES actor.usr(id) - DEFERRABLE INITIALLY DEFERRED, - max_fine NUMERIC(8,2) -) INHERITS (money.billable_xact); - -ALTER TABLE booking.reservation ADD PRIMARY KEY (id); - -ALTER TABLE booking.reservation - ADD CONSTRAINT booking_reservation_usr_fkey - FOREIGN KEY (usr) REFERENCES actor.usr (id) - DEFERRABLE INITIALLY DEFERRED; - -CREATE TABLE booking.reservation_attr_value_map ( - id SERIAL PRIMARY KEY, - reservation INT NOT NULL - REFERENCES booking.reservation(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - attr_value INT NOT NULL - REFERENCES booking.resource_attr_value(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value) -); - -- represents a circ chain summary CREATE TYPE action.circ_chain_summary AS ( num_circs INTEGER, @@ -8581,8 +8492,11 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; +DROP TRIGGER IF EXISTS mat_summary_create_tgr ON booking.reservation; CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('reservation'); +DROP TRIGGER IF EXISTS mat_summary_change_tgr ON booking.reservation; CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update (); +DROP TRIGGER IF EXISTS mat_summary_remove_tgr ON booking.reservation; CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete (); ALTER TABLE config.standing_penalty @@ -18810,16 +18724,6 @@ UPDATE action.reservation_transit_copy -- Recreate some foreign keys that were somehow dropped, probably -- by some kind of cascade from an inherited table: -ALTER TABLE action.reservation_transit_copy - ADD CONSTRAINT artc_tc_fkey FOREIGN KEY (target_copy) - REFERENCES booking.resource(id) - ON DELETE CASCADE - DEFERRABLE INITIALLY DEFERRED, - ADD CONSTRAINT reservation_transit_copy_reservation_fkey FOREIGN KEY (reservation) - REFERENCES booking.reservation(id) - ON DELETE SET NULL - DEFERRABLE INITIALLY DEFERRED; - CREATE INDEX user_bucket_item_target_user_idx ON container.user_bucket_item ( target_user ); @@ -19070,6 +18974,17 @@ COMMIT; -- Some operations go outside of the transaction, because they may -- legitimately fail. +ALTER TABLE action.reservation_transit_copy + ADD CONSTRAINT artc_tc_fkey FOREIGN KEY (target_copy) + REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + ADD CONSTRAINT reservation_transit_copy_reservation_fkey FOREIGN KEY (reservation) + REFERENCES booking.reservation(id) + ON DELETE SET NULL + DEFERRABLE INITIALLY DEFERRED; + + \qecho ALTERs of auditor.action_hold_request_history will fail if the table \qecho doesn't exist; ignore those errors if they occur. @@ -19604,7 +19519,10 @@ return undef; $func$ LANGUAGE PLPERLU; +\qecho Rewriting authority records to include a 901$c, so that +\qecho they can be used to control bibs. This may take a while... +UPDATE authority.record_entry SET active = active; \qecho Upgrade script completed. \qecho But wait, there's more: please run reingest-1.6-2.0.pl -- 2.43.2