From c8b172c0ebaa2434b70b996edfdf7b2271d19622 Mon Sep 17 00:00:00 2001
From: Bill Erickson <berick@esilibrary.com>
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 <berick@esilibrary.com>
Signed-off-by: Ben Shum <bshum@biblio.org>
---
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,
<field reporter:label="Claim Policy" name="claim_policy" reporter:datatype="link" />
<field reporter:label="Cancel Reason" name="cancel_reason" reporter:datatype="link" />
<field reporter:label="Estimated Unit Price" name="estimated_unit_price" reporter:datatype="money" />
+ <field reporter:label="Queued Vandelay Record" name="queued_record" reporter:datatype="link" />
<field reporter:label="Item Count" name="item_count" oils_persist:virtual="true" reporter:datatype="int" />
<field reporter:label="Descriptive Attributes" name="attributes" oils_persist:virtual="true" reporter:datatype="link" />
<field reporter:label="Line Item Details" name="lineitem_details" oils_persist:virtual="true" reporter:datatype="link" />
@@ -7180,6 +7181,7 @@ SELECT usr,
<link field="claim_policy" reltype="has_a" key="id" map="" class="acqclp"/>
<link field="invoice_entries" reltype="has_many" key="lineitem" map="" class="acqie"/>
<link field="order_summary" reltype="might_have" key="lineitem" map="" class="acqlisum"/>
+ <link field="queued_record" reltype="has_a" key="id" map="" class="vqbr"/>
</links>
<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<actions>
@@ -7213,6 +7215,7 @@ SELECT usr,
<field reporter:label="Claim Policy" name="claim_policy" reporter:datatype="link"/>
<field reporter:label="Cancel Reason" name="cancel_reason" reporter:datatype="link"/>
<field reporter:label="Estimated Unit Price" name="estimated_unit_price" reporter:datatype="money" />
+ <field reporter:label="Queued Vandelay Record" name="queued_record" reporter:datatype="link" />
</fields>
<links>
<link field="id" reltype="has_a" key="id" map="" class="jub"/>
@@ -7225,6 +7228,7 @@ SELECT usr,
<link field="eg_bib_id" reltype="has_a" key="id" map="" class="bre"/>
<link field="cancel_reason" reltype="has_a" key="id" map="" class="acqcr"/>
<link field="claim_policy" reltype="has_a" key="id" map="" class="acqclp"/>
+ <link field="queued_record" reltype="has_a" key="id" map="" class="vqbr"/>
</links>
<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<actions>
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