From f6b7bca95c34eb60c5f3c0d16f7569fb9b71f9b6 Mon Sep 17 00:00:00 2001 From: dbs Date: Fri, 9 May 2008 16:31:09 +0000 Subject: [PATCH] Make all foreign keys deferrable, so we can DELETE and INSERT inside a transaction without constraints hobbling us git-svn-id: svn://svn.open-ils.org/ILS/trunk@9542 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 48 +++++++------- .../src/sql/Pg/006.schema.permissions.sql | 22 +++---- Open-ILS/src/sql/Pg/011.schema.authority.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 32 ++++----- Open-ILS/src/sql/Pg/080.schema.money.sql | 8 +-- Open-ILS/src/sql/Pg/090.schema.action.sql | 66 +++++++++---------- .../src/sql/Pg/1.2.1-1.2.2-upgrade-db.sql | 2 +- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 26 ++++---- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 28 ++++---- .../src/sql/Pg/300.schema.staged_search.sql | 2 +- Open-ILS/src/sql/Pg/reporter-schema.sql | 6 +- 11 files changed, 121 insertions(+), 121 deletions(-) diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 596135c3f2..13149e2830 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, @@ -134,8 +134,8 @@ CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE acto 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, @@ -145,7 +145,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 $$ @@ -173,7 +173,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) @@ -306,7 +306,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 ); @@ -344,7 +344,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 ); @@ -352,8 +352,8 @@ CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent); 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, @@ -378,8 +378,8 @@ CREATE TABLE actor.org_lasso ( CREATE TABLE actor.org_lasso_map ( id SERIAL PRIMARY KEY, - lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE, - org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE + lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ); CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit); CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit); @@ -393,7 +393,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', @@ -412,7 +412,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 @@ -422,22 +422,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) @@ -474,7 +474,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, @@ -498,7 +498,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 eb6d448df5..a873d47ef1 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_object_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, object_type TEXT NOT NULL, object_id TEXT NOT NULL, grantable BOOL NOT NULL DEFAULT FALSE, @@ -53,8 +53,8 @@ CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr); 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) ); @@ -93,8 +93,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/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index e32b1bb517..cd0599393a 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -6,7 +6,7 @@ CREATE SCHEMA vandelay; CREATE TABLE vandelay.queue ( id BIGSERIAL PRIMARY KEY, - owner INT NOT NULL REFERENCES actor.usr (id), + owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, name TEXT NOT NULL, complete BOOL NOT NULL DEFAULT FALSE, queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')), @@ -57,25 +57,25 @@ CREATE TABLE vandelay.bib_queue ( ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id); CREATE TABLE vandelay.queued_bib_record ( - queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE, - bib_source INT REFERENCES config.bib_source (id), - imported_as INT REFERENCES biblio.record_entry (id) + queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED, + imported_as INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED ) INHERITS (vandelay.queued_record); ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id); CREATE TABLE vandelay.queued_bib_record_attr ( id BIGSERIAL PRIMARY KEY, - record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id), - field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id), + record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED, + field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED, attr_value TEXT NOT NULL ); CREATE TABLE vandelay.bib_match ( id BIGSERIAL PRIMARY KEY, field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')), - matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE, - queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE, - eg_record BIGINT REFERENCES biblio.record_entry (id) + matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + eg_record BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED ); CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$ @@ -186,23 +186,23 @@ CREATE TABLE vandelay.authority_queue ( ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id); CREATE TABLE vandelay.queued_authority_record ( - queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE, - imported_as INT REFERENCES authority.record_entry (id) + queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED ) INHERITS (vandelay.queued_record); ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id); CREATE TABLE vandelay.queued_authority_record_attr ( id BIGSERIAL PRIMARY KEY, - record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id), - field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id), + record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED, + field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED, attr_value TEXT NOT NULL ); CREATE TABLE vandelay.authority_match ( id BIGSERIAL PRIMARY KEY, - matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE, - queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE, - eg_record BIGINT REFERENCES authority.record_entry (id) + matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED ); CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$ diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index a6c966ab82..24d6b581d4 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); @@ -324,7 +324,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 e54287782d..76e7c49a63 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() ); @@ -167,15 +167,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, @@ -193,8 +193,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 @@ -203,8 +203,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); @@ -214,11 +214,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); @@ -230,7 +230,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/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 321e697f27..bb21ae420d 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -93,12 +93,12 @@ INSERT INTO config.videorecording_format_map VALUES ('z','Other'); CREATE TABLE config.circ_matrix_matchpoint ( id SERIAL PRIMARY KEY, active BOOL NOT NULL DEFAULT TRUE, - org_unit INT NOT NULL REFERENCES actor.org_unit (id), -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - grp INT NOT NULL REFERENCES permission.grp_tree (id), -- Set to the top applicable group from the group tree; will need decendents and prox functions for filtering - circ_modifier TEXT REFERENCES config.circ_modifier (code), - marc_type TEXT REFERENCES config.item_type_map (code), - marc_form TEXT REFERENCES config.item_form_map (code), - marc_vr_format TEXT REFERENCES config.videorecording_format_map (code), + org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering + circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, + marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, ref_flag BOOL, usr_age_lower_bound INTERVAL, usr_age_upper_bound INTERVAL, @@ -108,7 +108,7 @@ CREATE TABLE config.circ_matrix_matchpoint ( -- Tests to determine if circ can occur for this item at this location for this patron CREATE TABLE config.circ_matrix_test ( - matchpoint INT PRIMARY KEY NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE, + matchpoint INT PRIMARY KEY NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, circulate BOOL NOT NULL DEFAULT TRUE, -- Hard "can't circ" flag requiring an override max_items_out INT, -- Total current active circulations must be less than this, NULL means skip (always pass) max_overdue INT, -- Total overdue active circulations must be less than this, NULL means skip (always pass) @@ -119,18 +119,18 @@ CREATE TABLE config.circ_matrix_test ( -- Tests for max items out by circ_modifier CREATE TABLE config.circ_matrix_circ_mod_test ( id SERIAL PRIMARY KEY, - matchpoint INT NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE, + matchpoint INT NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, items_out INT NOT NULL, -- Total current active circulations must be less than this, NULL means skip (always pass) - circ_mod TEXT NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE -- circ_modifier type that the max out applies to + circ_mod TEXT NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED-- circ_modifier type that the max out applies to ); -- How to circ, assuming tests pass CREATE TABLE config.circ_matrix_ruleset ( - matchpoint INT PRIMARY KEY REFERENCES config.circ_matrix_matchpoint (id), - duration_rule INT NOT NULL REFERENCES config.rule_circ_duration (id), - recurring_fine_rule INT NOT NULL REFERENCES config.rule_recuring_fine (id), - max_fine_rule INT NOT NULL REFERENCES config.rule_max_fine (id) + matchpoint INT PRIMARY KEY REFERENCES config.circ_matrix_matchpoint (id) DEFERRABLE INITIALLY DEFERRED, + duration_rule INT NOT NULL REFERENCES config.rule_circ_duration (id) DEFERRABLE INITIALLY DEFERRED, + recurring_fine_rule INT NOT NULL REFERENCES config.rule_recuring_fine (id) DEFERRABLE INITIALLY DEFERRED, + max_fine_rule INT NOT NULL REFERENCES config.rule_max_fine (id) DEFERRABLE INITIALLY DEFERRED ); CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT ) RETURNS INT AS $func$ diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index 801440a238..b80dcdd60b 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -19,30 +19,30 @@ BEGIN; CREATE TABLE config.hold_matrix_matchpoint ( id SERIAL PRIMARY KEY, active BOOL NOT NULL DEFAULT TRUE, - user_home_ou INT REFERENCES actor.org_unit (id), -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - request_ou INT REFERENCES actor.org_unit (id), -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - pickup_ou INT REFERENCES actor.org_unit (id), -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - item_owning_ou INT REFERENCES actor.org_unit (id), -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - item_circ_ou INT REFERENCES actor.org_unit (id), -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" - usr_grp INT REFERENCES permission.grp_tree (id), -- Set to the top applicable group from the group tree; will need decendents and prox functions for filtering - requestor_grp INT NOT NULL REFERENCES permission.grp_tree (id), -- Set to the top applicable group from the group tree; will need decendents and prox functions for filtering - circ_modifier TEXT REFERENCES config.circ_modifier (code), - marc_type TEXT REFERENCES config.item_type_map (code), - marc_form TEXT REFERENCES config.item_form_map (code), - marc_vr_format TEXT REFERENCES config.videorecording_format_map (code), + user_home_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + request_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + pickup_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + item_owning_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + item_circ_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" + usr_grp INT REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering + requestor_grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering + circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, + marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, ref_flag BOOL, CONSTRAINT hous_once_per_grp_loc_mod_marc UNIQUE (user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, requestor_grp, usr_grp, circ_modifier, marc_type, marc_form, marc_vr_format) ); -- Tests to determine if hold against a specific copy is possible for a user at (and from) a location CREATE TABLE config.hold_matrix_test ( - matchpoint INT PRIMARY KEY REFERENCES config.hold_matrix_matchpoint (id), + matchpoint INT PRIMARY KEY REFERENCES config.hold_matrix_matchpoint (id) DEFERRABLE INITIALLY DEFERRED, holdable BOOL NOT NULL DEFAULT TRUE, -- Hard "can't hold" flag requiring an override distance_is_from_owner BOOL NOT NULL DEFAULT FALSE, -- How to calculate transit_range. True means owning lib, false means copy circ lib - transit_range INT REFERENCES actor.org_unit_type (id), -- Can circ inside range of cn.owner/cp.circ_lib at depth of the org_unit_type specified here + transit_range INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED, -- Can circ inside range of cn.owner/cp.circ_lib at depth of the org_unit_type specified here max_holds INT, -- Total hold requests must be less than this, NULL means skip (always pass) include_frozen_holds BOOL NOT NULL DEFAULT TRUE, -- Include frozen hold requests in the count for max_holds test - age_hold_protect_rule INT REFERENCES config.rule_age_hold_protect (id) -- still not sure we want to move this off the copy + age_hold_protect_rule INT REFERENCES config.rule_age_hold_protect (id) DEFERRABLE INITIALLY DEFERRED -- still not sure we want to move this off the copy ); CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$ 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 bbfb291a93..50f3a05842 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 9fd9a9e77a..8cb513a3ce 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.43.2