From aea415bc3f2ee8c7021e4c39dc944f36270a9482 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 26 Jul 2005 15:53:18 +0000 Subject: [PATCH 1/1] schema tweaks git-svn-id: svn://svn.open-ils.org/ILS/trunk@1479 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 49 +++++++++++++++-- .../src/sql/Pg/006.schema.permissions.sql | 25 ++++++--- Open-ILS/src/sql/Pg/020.schema.functions.sql | 30 ++++++++--- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 37 +++++++++---- Open-ILS/src/sql/Pg/040.schema.asset.sql | 12 ++--- Open-ILS/src/sql/Pg/080.schema.money.sql | 12 ++--- Open-ILS/src/sql/Pg/090.schema.action.sql | 52 +++++++++++-------- 7 files changed, 152 insertions(+), 65 deletions(-) diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 34a0f84b05..0d5d1496d4 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -130,7 +130,7 @@ CREATE TRIGGER actor_crypt_pw_insert_trigger -- Just so that there is a user... INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou ) - VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', '', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 ); + VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 ); CREATE TABLE actor.usr_setting ( @@ -145,7 +145,7 @@ COMMENT ON TABLE actor.usr_setting IS $$ * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander * - * User objects + * User settings * * This table contains any arbitrary settings that a client * program would like to save for a user. @@ -164,6 +164,9 @@ COMMENT ON TABLE actor.usr_setting IS $$ */ $$; +CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr); + + CREATE TABLE actor.stat_cat ( id SERIAL PRIMARY KEY, owner INT NOT NULL, @@ -344,8 +347,42 @@ INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, ' INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2'); INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3'); INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4'); -INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL4', 'Example Sub-lib 1'); -INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM4', 'Example Bookmobile 1'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1'); + +CREATE TABLE actor.org_unit_setting ( + id BIGSERIAL PRIMARY KEY, + org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE, + name TEXT NOT NULL, + value TEXT NOT NULL, + CONSTRAINT name_once_per_value UNIQUE (org_unit,name) +); +COMMENT ON TABLE actor.org_unit_setting IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Org Unit settings + * + * This table contains any arbitrary settings that a client + * program would like to save for an org unit. + * + * **** + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + */ +$$; + +CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit); + CREATE TABLE actor.usr_address ( id SERIAL PRIMARY KEY, @@ -361,6 +398,8 @@ CREATE TABLE actor.usr_address ( post_code TEXT NOT NULL ); +CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr); + CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1)); CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2)); @@ -383,6 +422,8 @@ CREATE TABLE actor.org_address ( post_code TEXT NOT NULL ); +CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit); + INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303'); UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1; diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index d2012be73c..c07238bf3c 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -4,8 +4,9 @@ BEGIN; CREATE SCHEMA permission; CREATE TABLE permission.perm_list ( - id SERIAL PRIMARY KEY, - code TEXT NOT NULL UNIQUE + id SERIAL PRIMARY KEY, + code TEXT NOT NULL UNIQUE, + description TEXT ); CREATE INDEX perm_list_code_idx ON permission.perm_list (code); @@ -47,9 +48,10 @@ INSERT INTO permission.perm_list VALUES (36, 'CREATE_PAYMENT'); SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 37); CREATE TABLE permission.grp_tree ( - id SERIAL PRIMARY KEY, - name TEXT NOT NULL UNIQUE, - parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE, + parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT, + description TEXT ); CREATE INDEX grp_tree_parent ON permission.grp_tree (parent); @@ -182,7 +184,7 @@ BEGIN END; $$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target INT ) RETURNS BOOL AS $$ +CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$ DECLARE r_usr actor.usr%ROWTYPE; r_perm permission.usr_perm_map%ROWTYPE; @@ -190,6 +192,15 @@ BEGIN SELECT * INTO r_usr FROM actor.usr WHERE id = iuser; + IF r_usr.active = FALSE THEN + RETURN FALSE; + END IF; + + IF r_usr.super_user = TRUE THEN + RETURN TRUE; + END IF; + + FOR r_perm IN SELECT * FROM permission.usr_perms(iuser) p JOIN permission.perm_list l @@ -198,7 +209,7 @@ BEGIN OR p.perm = -1 LOOP PERFORM * - FROM actor.org_unit_descendants(target,r_perm.depth) + FROM actor.org_unit_descendants(target_ou,r_perm.depth) WHERE id = r_usr.home_ou; IF FOUND THEN diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index 059aa79c22..8d1fe38642 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -53,8 +53,6 @@ CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor. ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; $$ LANGUAGE SQL STABLE; - - CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$ SELECT a.* FROM connectby('actor.org_unit','id','parent_ou','name', @@ -68,20 +66,36 @@ CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; $$ LANGUAGE SQL STABLE; -CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS ' +CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * FROM actor.org_unit_ancestors($1) UNION SELECT * FROM actor.org_unit_descendants($1); -' LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT * + FROM actor.org_unit_ancestors($1) + UNION + SELECT * + FROM actor.org_unit_ancestors($2); +$$ LANGUAGE SQL STABLE; +CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT * + FROM actor.org_unit_ancestors($1) + INTERSECT + SELECT * + FROM actor.org_unit_ancestors($2); +$$ LANGUAGE SQL STABLE; -CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS ' +CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$ SELECT COUNT(id)::INT FROM ( - select * from (SELECT id FROM actor.org_unit_ancestors($1) UNION SELECT id FROM actor.org_unit_ancestors($2)) x + SELECT id FROM actor.org_unit_combined_ancestors($1, $2) EXCEPT - select * from (SELECT id FROM actor.org_unit_ancestors($1) INTERSECT SELECT id FROM actor.org_unit_ancestors($2)) y) z; -' LANGUAGE SQL STABLE; + SELECT id FROM actor.org_unit_common_ancestors($1, $2) + ) z; +$$ LANGUAGE SQL STABLE; diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 32d61802f3..d9436cc347 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -23,6 +23,8 @@ CREATE TRIGGER metabib_title_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.title_field_entry FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); +CREATE INDEX metabib_title_field_entry_index_vector_idx ON (metabib.title_field_entry) USING GIST (index_vector); + CREATE TABLE metabib.author_field_entry ( id BIGSERIAL PRIMARY KEY, @@ -35,6 +37,8 @@ CREATE TRIGGER metabib_author_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.author_field_entry FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); +CREATE INDEX metabib_author_field_entry_index_vector_idx ON (metabib.author_field_entry) USING GIST (index_vector); + CREATE TABLE metabib.subject_field_entry ( id BIGSERIAL PRIMARY KEY, @@ -47,6 +51,8 @@ CREATE TRIGGER metabib_subject_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.subject_field_entry FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); +CREATE INDEX metabib_subject_field_entry_index_vector_idx ON (metabib.subject_field_entry) USING GIST (index_vector); + CREATE TABLE metabib.keyword_field_entry ( id BIGSERIAL PRIMARY KEY, @@ -59,6 +65,9 @@ CREATE TRIGGER metabib_keyword_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); +CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON (metabib.keyword_field_entry) USING GIST (index_vector); + + CREATE TABLE metabib.series_field_entry ( id BIGSERIAL PRIMARY KEY, source BIGINT NOT NULL, @@ -70,19 +79,22 @@ CREATE TRIGGER metabib_series_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.series_field_entry FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); +CREATE INDEX metabib_series_field_entry_index_vector_idx ON (metabib.series_field_entry) USING GIST (index_vector); + + CREATE TABLE metabib.rec_descriptor ( id BIGSERIAL PRIMARY KEY, record BIGINT, - item_type TEXT, - item_form TEXT, - bib_level TEXT, - control_type TEXT, - char_encoding TEXT, - enc_level TEXT, + item_type "char", + item_form "char", + bib_level "char", + control_type "char", + char_encoding "char", + enc_level "char", + audience "char", cat_form TEXT, pub_status TEXT, - item_lang TEXT, - audience TEXT + item_lang TEXT ); CREATE INDEX metabib_rec_descriptor_record_idx ON metabib.rec_descriptor (record); /* We may not need these... @@ -105,9 +117,9 @@ CREATE TABLE metabib.full_rec ( id BIGSERIAL PRIMARY KEY, record BIGINT NOT NULL, tag CHAR(3) NOT NULL, - ind1 CHAR(1), - ind2 CHAR(1), - subfield CHAR(1), + ind1 "char", + ind2 "char", + subfield "char", value TEXT NOT NULL, index_vector tsvector NOT NULL ); @@ -116,6 +128,9 @@ CREATE TRIGGER metabib_full_rec_fti_trigger BEFORE UPDATE OR INSERT ON metabib.full_rec FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); +CREATE INDEX metabib_full_rec_index_vector_idx ON (metabib.full_rec) USING GIST (index_vector); + + CREATE TABLE metabib.metarecord_source_map ( id BIGSERIAL PRIMARY KEY, metarecord BIGINT NOT NULL, diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 7e38df8be8..84b40050b0 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -43,19 +43,19 @@ CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number) WHERE status = 0; CREATE TABLE asset.copy_transparency ( id SERIAL PRIMARY KEY, - name TEXT NOT NULL, + deposit_amount NUMERIC(6,2), owner INT NOT NULL REFERENCES actor.org_unit (id), circ_lib INT REFERENCES actor.org_unit (id), - holdable BOOL, loan_duration INT CHECK ( loan_duration IN (1,2,3) ), fine_level INT CHECK ( fine_level IN (1,2,3) ), + holdable BOOL, circulate BOOL, deposit BOOL, - deposit_amount NUMERIC(6,2), ref BOOL, + opac_visible BOOL, circ_modifier TEXT, circ_as_type TEXT, - opac_visible BOOL, + name TEXT NOT NULL, CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name) ); @@ -77,8 +77,8 @@ CREATE TABLE asset.stat_cat_entry_transparency_map ( CREATE TABLE asset.stat_cat ( id SERIAL PRIMARY KEY, owner INT NOT NULL, + opac_visible BOOL NOT NULL DEFAULT FALSE, name TEXT NOT NULL, - opac_visible BOOL NOT NULL DEFAULT FALSE, CONSTRAINT sc_once_per_owner UNIQUE (owner,name) ); @@ -114,8 +114,8 @@ CREATE TABLE asset.call_number ( editor BIGINT NOT NULL, edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), record bigint NOT NULL, - label TEXT NOT NULL, owning_lib INT NOT NULL, + label TEXT NOT NULL, CONSTRAINT asset_call_number_label_once_per_lib UNIQUE (record, owning_lib, label) ); CREATE INDEX asset_call_number_record_idx ON asset.call_number (record); diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index dfdd702e09..5812490273 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -15,20 +15,20 @@ CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr) WHERE xact_finish CREATE TABLE money.billing ( id BIGSERIAL PRIMARY KEY, xact BIGINT NOT NULL, -- money.billable_xact.id - amount NUMERIC(6,2) NOT NULL, billing_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - note TEXT, - voided BOOL NOT NULL DEFAULT FALSE + voided BOOL NOT NULL DEFAULT FALSE, + amount NUMERIC(6,2) NOT NULL, + note TEXT ); CREATE INDEX m_b_xact_idx ON money.billing (xact); CREATE TABLE money.payment ( id BIGSERIAL PRIMARY KEY, xact BIGINT NOT NULL, -- money.billable_xact.id - amount NUMERIC(6,2) NOT NULL, payment_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - note TEXT, - voided BOOL NOT NULL DEFAULT FALSE + voided BOOL NOT NULL DEFAULT FALSE, + amount NUMERIC(6,2) NOT NULL, + note TEXT ); CREATE INDEX m_p_xact_idx ON money.payment (xact); diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index cf8c62400c..457a8ffe05 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -7,14 +7,14 @@ CREATE SCHEMA action; CREATE TABLE action.survey ( id SERIAL PRIMARY KEY, owner INT NOT NULL REFERENCES actor.org_unit (id), - name TEXT NOT NULL, - description TEXT NOT NULL, start_date DATE NOT NULL DEFAULT NOW(), end_date DATE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL, usr_summary BOOL NOT NULL DEFAULT FALSE, opac BOOL NOT NULL DEFAULT FALSE, poll BOOL NOT NULL DEFAULT FALSE, - required BOOL NOT NULL DEFAULT FALSE + required BOOL NOT NULL DEFAULT FALSE, + name TEXT NOT NULL, + description TEXT NOT NULL ); CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name); @@ -56,17 +56,17 @@ CREATE TRIGGER action_survey_response_answer_date_fixup_tgr CREATE TABLE action.circulation ( target_copy BIGINT NOT NULL, -- asset.copy.id - renewal BOOL NOT NULL DEFAULT FALSE, circ_lib INT NOT NULL, -- actor.org_unit.id - duration_rule TEXT NOT NULL, -- name of "circ duration" rule - duration INTERVAL NOT NULL, -- derived from "circ duration" rule renewal_remaining INT NOT NULL, -- derived from "circ duration" rule - recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule + renewal BOOL NOT NULL DEFAULT FALSE, + due_date TIMESTAMP WITH TIME ZONE NOT NULL, + duration INTERVAL NOT NULL, -- derived from "circ duration" rule + fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule - max_fine_rule TEXT NOT NULL, -- name of "max fine" rule max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule - fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule - due_date TIMESTAMP WITH TIME ZONE NOT NULL, + duration_rule TEXT NOT NULL, -- name of "circ duration" rule + recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule + max_fine_rule TEXT NOT NULL, -- name of "max fine" rule stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE')) ) INHERITS (money.billable_xact); CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL; @@ -91,6 +91,12 @@ BEGIN IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'CLAIMSRETURNED' THEN UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr; END IF; + IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'LOST' THEN + UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy; + END IF; + IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'MISSING' THEN + UPDATE asset.copy SET status = 4 WHERE id = NEW.target_copy; + END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; @@ -109,16 +115,16 @@ CREATE TABLE action.hold_request ( return_time TIMESTAMP WITH TIME ZONE, prev_check_time TIMESTAMP WITH TIME ZONE, expire_time TIMESTAMP WITH TIME ZONE, + hold_type "char" NOT NULL CHECK (hold_type IN ('M','T','V','C')), + target BIGINT NOT NULL, -- see hold_type + current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL, requestor INT NOT NULL REFERENCES actor.usr (id), usr INT NOT NULL REFERENCES actor.usr (id), - hold_type CHAR NOT NULL CHECK (hold_type IN ('M','T','V','C')), - holdable_formats TEXT, - phone_notify TEXT, - email_notify TEXT, - target BIGINT NOT NULL, -- see hold_type selection_depth INT NOT NULL DEFAULT 0, pickup_lib INT NOT NULL REFERENCES actor.org_unit, - current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL + holdable_formats TEXT, + phone_notify TEXT, + email_notify TEXT ); @@ -139,23 +145,23 @@ CREATE TABLE action.hold_copy_map ( 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), - persistant_transfer BOOL NOT NULL DEFAULT FALSE, - source_send_time TIMESTAMP WITH TIME ZONE, - dest_recv_time TIMESTAMP WITH TIME ZONE, - prev_hop INT REFERENCES action.transit_copy (id) + prev_hop INT REFERENCES action.transit_copy (id), + persistant_transfer BOOL NOT NULL DEFAULT FALSE ); CREATE TABLE action.hold_transit_copy ( - hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED + hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ) INHERITS (action.transit_copy); CREATE TABLE action.unfulfilled_hold_list ( - id BIGSERIAL PRIMARY KEY, - hold INT NOT NULL, + id BIGSERIAL PRIMARY KEY, current_copy BIGINT NOT NULL, + hold INT NOT NULL, circ_lib INT NOT NULL, fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -- 2.43.2