1 -- Evergreen DB patch 0684.schema.acq-vandelay-integration.sql
4 -- check whether patch can be applied
5 SELECT evergreen.upgrade_deps_block_check('0684', :eg_version);
9 -- Replace the constraints with more flexible ENUM's
10 ALTER TABLE vandelay.queue DROP CONSTRAINT queue_queue_type_check;
11 ALTER TABLE vandelay.bib_queue DROP CONSTRAINT bib_queue_queue_type_check;
12 ALTER TABLE vandelay.authority_queue DROP CONSTRAINT authority_queue_queue_type_check;
14 CREATE TYPE vandelay.bib_queue_queue_type AS ENUM ('bib', 'acq');
15 CREATE TYPE vandelay.authority_queue_queue_type AS ENUM ('authority');
17 -- dropped column is also implemented by the child tables
18 ALTER TABLE vandelay.queue DROP COLUMN queue_type;
20 -- to recover after using the undo sql from below
21 -- alter table vandelay.bib_queue add column queue_type text default 'bib' not null;
22 -- alter table vandelay.authority_queue add column queue_type text default 'authority' not null;
24 -- modify the child tables to use the ENUMs
25 ALTER TABLE vandelay.bib_queue
26 ALTER COLUMN queue_type DROP DEFAULT,
27 ALTER COLUMN queue_type TYPE vandelay.bib_queue_queue_type
28 USING (queue_type::vandelay.bib_queue_queue_type),
29 ALTER COLUMN queue_type SET DEFAULT 'bib';
31 ALTER TABLE vandelay.authority_queue
32 ALTER COLUMN queue_type DROP DEFAULT,
33 ALTER COLUMN queue_type TYPE vandelay.authority_queue_queue_type
34 USING (queue_type::vandelay.authority_queue_queue_type),
35 ALTER COLUMN queue_type SET DEFAULT 'authority';
37 -- give lineitems a pointer to their vandelay queued_record
39 ALTER TABLE acq.lineitem ADD COLUMN queued_record BIGINT
40 REFERENCES vandelay.queued_bib_record (id)
41 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
43 ALTER TABLE acq.acq_lineitem_history ADD COLUMN queued_record BIGINT
44 REFERENCES vandelay.queued_bib_record (id)
45 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
49 INSERT INTO permission.perm_list ( id, code, description )
52 'IMPORT_ACQ_LINEITEM_BIB_RECORD_UPLOAD',
55 'Allows a user to create new bibs directly from an ACQ MARC file upload',
62 INSERT INTO vandelay.import_error ( code, description )
64 'import.record.perm_failure',
66 'import.record.perm_failure',
67 'Perm failure creating a record', 'vie', 'description')
74 -- XXX this does not exactly recover the state. The bib/auth queue_type colum is
75 -- directly inherited instead of overridden, which will fail with some of the sql above.
76 ALTER TABLE acq.lineitem DROP COLUMN queued_record;
77 ALTER TABLE acq.acq_lineitem_history DROP COLUMN queued_record;
78 ALTER TABLE vandelay.authority_queue DROP COLUMN queue_type;
79 ALTER TABLE vandelay.bib_queue DROP COLUMN queue_type;
81 DROP TYPE vandelay.bib_queue_queue_type;
82 DROP TYPE vandelay.authority_queue_queue_type;
84 ALTER TABLE vandelay.bib_queue DROP CONSTRAINT vand_bib_queue_name_once_per_owner_const;
85 ALTER TABLE vandelay.authority_queue DROP CONSTRAINT vand_authority_queue_name_once_per_owner_const;
87 ALTER TABLE vandelay.queue ADD COLUMN queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority'));
88 UPDATE vandelay.authority_queue SET queue_type = 'authority';
89 ALTER TABLE vandelay.bib_queue ADD CONSTRAINT bib_queue_queue_type_check CHECK (queue_type IN ('bib'));
90 ALTER TABLE vandelay.authority_queue ADD CONSTRAINT authority_queue_queue_type_check CHECK (queue_type IN ('authority'));
92 DELETE FROM permission.perm_list WHERE code = 'IMPORT_ACQ_LINEITEM_BIB_RECORD_UPLOAD';
93 DELETE FROM vandelay.import_error WHERE code = 'import.record.perm_failure';