From c8b172c0ebaa2434b70b996edfdf7b2271d19622 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 30 Nov 2011 14:54:41 -0500 Subject: [PATCH] ACQ+Vandelay schema and IDL changes w/ upgrade script Adds a queued_record link from acq lineitems to vandelay queued record. Ditto acq_lineitem_history. This will be used to link vandelay-mediated acq lineitems to the vandelay record used for their import. For ease of future changes, port the queue_type column from bib and authority queues to ENUMs and drop the constraint, which is implicit in the ENUM. Signed-off-by: Bill Erickson Signed-off-by: Ben Shum --- Open-ILS/examples/fm_IDL.xml | 4 ++ Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 11 ++-- Open-ILS/src/sql/Pg/200.schema.acq.sql | 2 + .../XXXX.schema.acq-vandelay-integration.sql | 72 ++++++++++++++++++++++ 4 files changed, 84 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-vandelay-integration.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 94e1aa2524..b0ab83745b 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -7156,6 +7156,7 @@ SELECT usr, + @@ -7180,6 +7181,7 @@ SELECT usr, + @@ -7213,6 +7215,7 @@ SELECT usr, + @@ -7225,6 +7228,7 @@ SELECT usr, + diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 4b6c52577b..65e2ca70cb 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -50,9 +50,7 @@ CREATE TABLE vandelay.queue ( 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')), - match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT vand_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type) + match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE vandelay.queued_record ( @@ -114,8 +112,10 @@ CREATE TABLE vandelay.import_error ( description TEXT NOT NULL -- i18n ); +CREATE TYPE vandelay.bib_queue_queue_type AS ENUM ('bib', 'acq'); + CREATE TABLE vandelay.bib_queue ( - queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'), + queue_type vandelay.bib_queue_queue_type NOT NULL DEFAULT 'bib', item_attr_def BIGINT REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type) ) INHERITS (vandelay.queue); @@ -1518,8 +1518,9 @@ CREATE TABLE vandelay.authority_attr_definition ( remove TEXT NOT NULL DEFAULT '' ); +CREATE TYPE vandelay.authority_queue_queue_type AS ENUM ('authority'); CREATE TABLE vandelay.authority_queue ( - queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'), + queue_type vandelay.authority_queue_queue_type NOT NULL DEFAULT 'authority', CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type) ) INHERITS (vandelay.queue); ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id); diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 8765c4a659..2263898dfd 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -486,6 +486,8 @@ CREATE TABLE acq.lineitem ( estimated_unit_price NUMERIC, claim_policy INT REFERENCES acq.claim_policy DEFERRABLE INITIALLY DEFERRED, + queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) + ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL) ); CREATE INDEX li_po_idx ON acq.lineitem (purchase_order); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-vandelay-integration.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-vandelay-integration.sql new file mode 100644 index 0000000000..213c7ac8f8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.acq-vandelay-integration.sql @@ -0,0 +1,72 @@ +-- Evergreen DB patch XXXX.schema.acq-vandelay-integration.sql +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- schema -- + +-- Replace the constraints with more flexible ENUM's +ALTER TABLE vandelay.queue DROP CONSTRAINT queue_queue_type_check; +ALTER TABLE vandelay.bib_queue DROP CONSTRAINT bib_queue_queue_type_check; +ALTER TABLE vandelay.authority_queue DROP CONSTRAINT authority_queue_queue_type_check; + +CREATE TYPE vandelay.bib_queue_queue_type AS ENUM ('bib', 'acq'); +CREATE TYPE vandelay.authority_queue_queue_type AS ENUM ('authority'); + +-- dropped column is also implemented by the child tables +ALTER TABLE vandelay.queue DROP COLUMN queue_type; + +-- to recover after using the undo sql from below +-- alter table vandelay.bib_queue add column queue_type text default 'bib' not null; +-- alter table vandelay.authority_queue add column queue_type text default 'authority' not null; + +-- modify the child tables to use the ENUMs +ALTER TABLE vandelay.bib_queue + ALTER COLUMN queue_type DROP DEFAULT, + ALTER COLUMN queue_type TYPE vandelay.bib_queue_queue_type + USING (queue_type::vandelay.bib_queue_queue_type), + ALTER COLUMN queue_type SET DEFAULT 'bib'; + +ALTER TABLE vandelay.authority_queue + ALTER COLUMN queue_type DROP DEFAULT, + ALTER COLUMN queue_type TYPE vandelay.authority_queue_queue_type + USING (queue_type::vandelay.authority_queue_queue_type), + ALTER COLUMN queue_type SET DEFAULT 'authority'; + +-- give lineitems a pointer to their vandelay queued_record + +ALTER TABLE acq.lineitem ADD COLUMN queued_record BIGINT + REFERENCES vandelay.queued_bib_record (id) + ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE acq.acq_lineitem_history ADD COLUMN queued_record BIGINT + REFERENCES vandelay.queued_bib_record (id) + ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; + +COMMIT; + +/* UNDO SQL +-- XXX this does not exactly recover the state. The bib/auth queue_type colum is +-- directly inherited instead of overridden, which will fail with some of the sql above. +ALTER TABLE acq.lineitem DROP COLUMN queued_record; +ALTER TABLE acq.acq_lineitem_history DROP COLUMN queued_record; +ALTER TABLE vandelay.authority_queue DROP COLUMN queue_type; +ALTER TABLE vandelay.bib_queue DROP COLUMN queue_type; + +DROP TYPE vandelay.bib_queue_queue_type; +DROP TYPE vandelay.authority_queue_queue_type; + +ALTER TABLE vandelay.bib_queue DROP CONSTRAINT vand_bib_queue_name_once_per_owner_const; +ALTER TABLE vandelay.authority_queue DROP CONSTRAINT vand_authority_queue_name_once_per_owner_const; + +ALTER TABLE vandelay.queue ADD COLUMN queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')); +UPDATE vandelay.authority_queue SET queue_type = 'authority'; +ALTER TABLE vandelay.bib_queue ADD CONSTRAINT bib_queue_queue_type_check CHECK (queue_type IN ('bib')); +ALTER TABLE vandelay.authority_queue ADD CONSTRAINT authority_queue_queue_type_check CHECK (queue_type IN ('authority')); + +DELETE FROM permission.perm_list WHERE code = 'IMPORT_ACQ_LINEITEM_BIB_RECORD_UPLOAD'; +DELETE FROM vandelay.import_error WHERE code = 'import.record.perm_failure'; +*/ + + -- 2.11.0