From ff0ad6f9a10c2100e3b5edb0d5fed8a9caee23bf Mon Sep 17 00:00:00 2001 From: dbs Date: Fri, 9 May 2008 16:34:30 +0000 Subject: [PATCH] Merge rev. 9542 from trunk: add deferrable to all FKs git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_2@9543 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 44 ++++++++-------- Open-ILS/src/sql/Pg/006.schema.permissions.sql | 18 +++---- Open-ILS/src/sql/Pg/011.schema.authority.sql | 2 +- Open-ILS/src/sql/Pg/080.schema.money.sql | 8 +-- Open-ILS/src/sql/Pg/090.schema.action.sql | 66 ++++++++++++------------ Open-ILS/src/sql/Pg/1.2.1-1.2.2-upgrade-db.sql | 2 +- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 2 +- Open-ILS/src/sql/Pg/reporter-schema.sql | 6 +-- 8 files changed, 74 insertions(+), 74 deletions(-) diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 2f0a62d0b1..8965157ce2 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -32,12 +32,12 @@ CREATE TABLE actor.usr ( usrname TEXT NOT NULL UNIQUE, email TEXT, passwd TEXT NOT NULL, - standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id), - ident_type INT NOT NULL REFERENCES config.identification_type (id), + standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED, + ident_type INT NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED, ident_value TEXT, - ident_type2 INT REFERENCES config.identification_type (id), + ident_type2 INT REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED, ident_value2 TEXT, - net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id), + net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED, photo_url TEXT, prefix TEXT, first_given_name TEXT NOT NULL, @@ -138,8 +138,8 @@ INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, fa CREATE TABLE actor.usr_note ( id BIGSERIAL PRIMARY KEY, - usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE, - creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE, + usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), pub BOOL NOT NULL DEFAULT FALSE, title TEXT NOT NULL, @@ -149,7 +149,7 @@ CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr); CREATE TABLE actor.usr_standing_penalty ( id SERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE, + usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, penalty_type TEXT NOT NULL ); COMMENT ON TABLE actor.usr_standing_penalty IS $$ @@ -177,7 +177,7 @@ CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (u CREATE TABLE actor.usr_setting ( id BIGSERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE, + usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, name TEXT NOT NULL, value TEXT NOT NULL, CONSTRAINT usr_once_per_key UNIQUE (usr,name) @@ -310,7 +310,7 @@ CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (targe CREATE TABLE actor.card ( id SERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr (id), + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, barcode TEXT NOT NULL UNIQUE, active BOOL NOT NULL DEFAULT TRUE ); @@ -351,7 +351,7 @@ CREATE TABLE actor.org_unit_type ( name TEXT NOT NULL, opac_label TEXT NOT NULL, depth INT NOT NULL, - parent INT REFERENCES actor.org_unit_type (id), + parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED, can_have_vols BOOL NOT NULL DEFAULT TRUE, can_have_users BOOL NOT NULL DEFAULT TRUE ); @@ -366,8 +366,8 @@ INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Book CREATE TABLE actor.org_unit ( id SERIAL PRIMARY KEY, - parent_ou INT REFERENCES actor.org_unit (id), - ou_type INT NOT NULL REFERENCES actor.org_unit_type (id), + parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED, ill_address INT, holds_address INT, mailing_address INT, @@ -404,7 +404,7 @@ CREATE TABLE actor.org_unit_proximity ( CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org); CREATE TABLE actor.hours_of_operation ( - id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE, + id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, dow_0_open TIME NOT NULL DEFAULT '09:00', dow_0_close TIME NOT NULL DEFAULT '17:00', dow_1_open TIME NOT NULL DEFAULT '09:00', @@ -423,7 +423,7 @@ CREATE TABLE actor.hours_of_operation ( CREATE TABLE actor.org_unit_closed ( id SERIAL PRIMARY KEY, - org_unit INT NOT NULL REFERENCES actor.org_unit (id), + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, close_start TIMESTAMP WITH TIME ZONE NOT NULL, close_end TIMESTAMP WITH TIME ZONE NOT NULL, reason TEXT @@ -433,22 +433,22 @@ CREATE TABLE actor.org_unit_closed ( CREATE TABLE actor.workstation ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, - owning_lib INT NOT NULL REFERENCES actor.org_unit (id) + owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE actor.usr_org_unit_opt_in ( id SERIAL PRIMARY KEY, - org_unit INT NOT NULL REFERENCES actor.org_unit (id), - usr INT NOT NULL REFERENCES actor.usr (id), - staff INT NOT NULL REFERENCES actor.usr (id), + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - opt_in_ws INT NOT NULL REFERENCES actor.workstation (id), + opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit) ); CREATE TABLE actor.org_unit_setting ( id BIGSERIAL PRIMARY KEY, - org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE, + org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, name TEXT NOT NULL, value TEXT NOT NULL, CONSTRAINT ou_once_per_key UNIQUE (org_unit,name) @@ -485,7 +485,7 @@ CREATE TABLE actor.usr_address ( valid BOOL NOT NULL DEFAULT TRUE, within_city_limits BOOL NOT NULL DEFAULT TRUE, address_type TEXT NOT NULL DEFAULT 'MAILING', - usr INT NOT NULL REFERENCES actor.usr (id), + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, street1 TEXT NOT NULL, street2 TEXT, city TEXT NOT NULL, @@ -509,7 +509,7 @@ CREATE TABLE actor.org_address ( id SERIAL PRIMARY KEY, valid BOOL NOT NULL DEFAULT TRUE, address_type TEXT NOT NULL DEFAULT 'MAILING', - org_unit INT NOT NULL REFERENCES actor.org_unit (id), + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, street1 TEXT NOT NULL, street2 TEXT, city TEXT NOT NULL, diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index 37c5d2a3f2..2796d82865 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -13,7 +13,7 @@ CREATE INDEX perm_list_code_idx ON permission.perm_list (code); CREATE TABLE permission.grp_tree ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, - parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT, + parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED, usergroup BOOL NOT NULL DEFAULT TRUE, perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL, description TEXT, @@ -23,8 +23,8 @@ CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent); CREATE TABLE permission.grp_perm_map ( id SERIAL PRIMARY KEY, - grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE, - perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE, + grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, depth INT NOT NULL, grantable BOOL NOT NULL DEFAULT FALSE, CONSTRAINT perm_grp_once UNIQUE (grp,perm) @@ -32,8 +32,8 @@ CREATE TABLE permission.grp_perm_map ( CREATE TABLE permission.usr_perm_map ( id SERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE, - perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE, + usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, depth INT NOT NULL, grantable BOOL NOT NULL DEFAULT FALSE, CONSTRAINT perm_usr_once UNIQUE (usr,perm) @@ -41,8 +41,8 @@ CREATE TABLE permission.usr_perm_map ( CREATE TABLE permission.usr_grp_map ( id SERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE, - grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE, + usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT usr_grp_once UNIQUE (usr,grp) ); @@ -81,8 +81,8 @@ $$ LANGUAGE SQL STABLE; CREATE TABLE permission.usr_work_ou_map ( id SERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE, - work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE, + usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou) ); diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 89e87e1c4f..2b2013d2e5 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -23,7 +23,7 @@ CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_s CREATE TABLE authority.record_note ( id BIGSERIAL PRIMARY KEY, - record BIGINT NOT NULL REFERENCES authority.record_entry (id), + record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED, value TEXT NOT NULL, creator INT NOT NULL DEFAULT 1, editor INT NOT NULL DEFAULT 1, diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index e3b0235951..f119d01075 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -6,9 +6,9 @@ CREATE SCHEMA money; CREATE TABLE money.collections_tracker ( id BIGSERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr (id), -- actor.usr.id - collector INT NOT NULL REFERENCES actor.usr (id), - location INT NOT NULL REFERENCES actor.org_unit (id), + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, -- actor.usr.id + collector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + location INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, enter_time TIMESTAMP WITH TIME ZONE ); CREATE UNIQUE INDEX m_c_t_usr_collector_location_once_idx ON money.collections_tracker (usr, collector, location); @@ -285,7 +285,7 @@ CREATE INDEX money_goods_payment_payment_ts_idx ON money.goods_payment (payment_ CREATE INDEX money_goods_payment_accepting_usr_idx ON money.goods_payment (accepting_usr); CREATE TABLE money.bnm_desk_payment ( - cash_drawer INT REFERENCES actor.workstation (id) + cash_drawer INT REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED ) INHERITS (money.bnm_payment); ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id); diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 0181700dbe..be9934a007 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -6,32 +6,32 @@ CREATE SCHEMA action; CREATE TABLE action.in_house_use ( id SERIAL PRIMARY KEY, - item BIGINT NOT NULL REFERENCES asset.copy (id), - staff INT NOT NULL REFERENCES actor.usr (id), - org_unit INT NOT NULL REFERENCES actor.org_unit (id), + item BIGINT NOT NULL REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED, + staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE TABLE action.non_cataloged_circulation ( id SERIAL PRIMARY KEY, - patron INT NOT NULL REFERENCES actor.usr (id), - staff INT NOT NULL REFERENCES actor.usr (id), - circ_lib INT NOT NULL REFERENCES actor.org_unit (id), - item_type INT NOT NULL REFERENCES config.non_cataloged_type (id), + patron INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + item_type INT NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED, circ_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE TABLE action.non_cat_in_house_use ( id SERIAL PRIMARY KEY, - item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id), - staff INT NOT NULL REFERENCES actor.usr (id), - org_unit INT NOT NULL REFERENCES actor.org_unit (id), + item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED, + staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE TABLE action.survey ( id SERIAL PRIMARY KEY, - owner INT NOT NULL REFERENCES actor.org_unit (id), + owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL, usr_summary BOOL NOT NULL DEFAULT FALSE, @@ -45,13 +45,13 @@ CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name); CREATE TABLE action.survey_question ( id SERIAL PRIMARY KEY, - survey INT NOT NULL REFERENCES action.survey, + survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED, question TEXT NOT NULL ); CREATE TABLE action.survey_answer ( id SERIAL PRIMARY KEY, - question INT NOT NULL REFERENCES action.survey_question, + question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED, answer TEXT NOT NULL ); @@ -61,9 +61,9 @@ CREATE TABLE action.survey_response ( id BIGSERIAL PRIMARY KEY, response_group_id INT, usr INT, -- REFERENCES actor.usr - survey INT NOT NULL REFERENCES action.survey, - question INT NOT NULL REFERENCES action.survey_question, - answer INT NOT NULL REFERENCES action.survey_answer, + survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED, + question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED, + answer INT NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED, answer_date TIMESTAMP WITH TIME ZONE, effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); @@ -175,15 +175,15 @@ CREATE TABLE action.hold_request ( expire_time TIMESTAMP WITH TIME ZONE, cancel_time TIMESTAMP WITH TIME ZONE, target BIGINT NOT NULL, -- see hold_type - current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL, - fulfillment_staff INT REFERENCES actor.usr (id), - fulfillment_lib INT REFERENCES actor.org_unit (id), - request_lib INT NOT NULL REFERENCES actor.org_unit (id), - requestor INT NOT NULL REFERENCES actor.usr (id), - usr INT NOT NULL REFERENCES actor.usr (id), + current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, + usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, selection_ou INT NOT NULL, selection_depth INT NOT NULL DEFAULT 0, - pickup_lib INT NOT NULL REFERENCES actor.org_unit, + pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED, hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')), holdable_formats TEXT, phone_notify TEXT, @@ -201,8 +201,8 @@ CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check CREATE TABLE action.hold_notification ( id SERIAL PRIMARY KEY, - hold INT NOT NULL REFERENCES action.hold_request (id), - notify_staff INT REFERENCES actor.usr (id), + hold INT NOT NULL REFERENCES action.hold_request (id) DEFERRABLE INITIALLY DEFERRED, + notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), method TEXT NOT NULL, -- email address or phone number note TEXT @@ -211,8 +211,8 @@ CREATE INDEX ahn_hold_idx ON action.hold_notification (hold); CREATE TABLE action.hold_copy_map ( id SERIAL PRIMARY KEY, - hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE, - target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE, + hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy) ); -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold); @@ -222,11 +222,11 @@ CREATE TABLE action.transit_copy ( id SERIAL PRIMARY KEY, source_send_time TIMESTAMP WITH TIME ZONE, dest_recv_time TIMESTAMP WITH TIME ZONE, - target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE, - source INT NOT NULL REFERENCES actor.org_unit (id), - dest INT NOT NULL REFERENCES actor.org_unit (id), - prev_hop INT REFERENCES action.transit_copy (id), - copy_status INT NOT NULL REFERENCES config.copy_status (id), + target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED, + copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED, persistant_transfer BOOL NOT NULL DEFAULT FALSE ); CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest); @@ -238,7 +238,7 @@ CREATE TABLE action.hold_transit_copy ( hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ) INHERITS (action.transit_copy); ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id); -ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE; +ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest); CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source); CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy); diff --git a/Open-ILS/src/sql/Pg/1.2.1-1.2.2-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.2.1-1.2.2-upgrade-db.sql index 3b98d9942a..6d06d2880b 100644 --- a/Open-ILS/src/sql/Pg/1.2.1-1.2.2-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.2.1-1.2.2-upgrade-db.sql @@ -170,7 +170,7 @@ CREATE SCHEMA search; CREATE TABLE search.relevance_adjustment ( id SERIAL PRIMARY KEY, active BOOL NOT NULL DEFAULT TRUE, - field INT NOT NULL REFERENCES config.metabib_field (id), + field INT NOT NULL REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED, bump_type TEXT NOT NULL CHECK (bump_type IN ('word_order','first_word','full_match')), multiplier NUMERIC NOT NULL DEFAULT 1.0 ); diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index 69d1e75ba3..21f41d7e35 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -8,7 +8,7 @@ CREATE SCHEMA search; CREATE TABLE search.relevance_adjustment ( id SERIAL PRIMARY KEY, active BOOL NOT NULL DEFAULT TRUE, - field INT NOT NULL REFERENCES config.metabib_field (id), + field INT NOT NULL REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED, bump_type TEXT NOT NULL CHECK (bump_type IN ('word_order','first_word','full_match')), multiplier NUMERIC NOT NULL DEFAULT 1.0 ); diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index d8302b7c8a..52a81d60da 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -51,7 +51,7 @@ CREATE TABLE reporter.template ( name TEXT NOT NULL, description TEXT NOT NULL, data TEXT NOT NULL, - folder INT NOT NULL REFERENCES reporter.template_folder (id) + folder INT NOT NULL REFERENCES reporter.template_folder (id) DEFERRABLE INITIALLY DEFERRED ); CREATE INDEX rpt_tmpl_owner_idx ON reporter.template (owner); CREATE INDEX rpt_tmpl_fldr_idx ON reporter.template (folder); @@ -65,7 +65,7 @@ CREATE TABLE reporter.report ( description TEXT NOT NULL DEFAULT '', template INT NOT NULL REFERENCES reporter.template (id) DEFERRABLE INITIALLY DEFERRED, data TEXT NOT NULL, - folder INT NOT NULL REFERENCES reporter.report_folder (id), + folder INT NOT NULL REFERENCES reporter.report_folder (id) DEFERRABLE INITIALLY DEFERRED, recur BOOL NOT NULL DEFAULT FALSE, recurance INTERVAL ); @@ -76,7 +76,7 @@ CREATE UNIQUE INDEX rtp_report_folder_once_idx ON reporter.report (name,folder); CREATE TABLE reporter.schedule ( id SERIAL PRIMARY KEY, report INT NOT NULL REFERENCES reporter.report (id) DEFERRABLE INITIALLY DEFERRED, - folder INT NOT NULL REFERENCES reporter.output_folder (id), + folder INT NOT NULL REFERENCES reporter.output_folder (id) DEFERRABLE INITIALLY DEFERRED, runner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, run_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), start_time TIMESTAMP WITH TIME ZONE, -- 2.11.0