From 87e91b6a2bbe3706451c7a4c065d4d21c66444d0 Mon Sep 17 00:00:00 2001 From: scottmk Date: Mon, 27 Sep 2010 21:31:17 +0000 Subject: [PATCH] 1. Turn some ints into bigints. 2. Rename the uniqueness constraint for booking.resource_type. M Open-ILS/src/sql/Pg/090.schema.action.sql M Open-ILS/src/sql/Pg/200.schema.acq.sql M Open-ILS/src/sql/Pg/012.schema.vandelay.sql M Open-ILS/src/sql/Pg/095.schema.booking.sql M Open-ILS/src/sql/Pg/002.schema.config.sql M Open-ILS/src/sql/Pg/070.schema.container.sql A Open-ILS/src/sql/Pg/upgrade/0421.schema.embiggen-ints.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@18054 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 2 +- Open-ILS/src/sql/Pg/070.schema.container.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 +- Open-ILS/src/sql/Pg/095.schema.booking.sql | 4 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 2 +- .../sql/Pg/upgrade/0421.schema.embiggen-ints.sql | 44 ++++++++++++++++++++++ 7 files changed, 51 insertions(+), 7 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0421.schema.embiggen-ints.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index d0f53c9ce..1dbd195d6 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0420'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0421'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 41372e237..fbfba89a6 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -77,7 +77,7 @@ 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 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 + imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED ) INHERITS (vandelay.queued_record); ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id); CREATE INDEX queued_bib_record_queue_idx ON vandelay.queued_bib_record (queue); diff --git a/Open-ILS/src/sql/Pg/070.schema.container.sql b/Open-ILS/src/sql/Pg/070.schema.container.sql index beceec94a..6bbd17c76 100644 --- a/Open-ILS/src/sql/Pg/070.schema.container.sql +++ b/Open-ILS/src/sql/Pg/070.schema.container.sql @@ -161,7 +161,7 @@ CREATE TABLE container.biblio_record_entry_bucket_item ( ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, - target_biblio_record_entry INT NOT NULL + target_biblio_record_entry BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE ON UPDATE CASCADE diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 76f7c0ba5..170f4626a 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -411,7 +411,7 @@ CREATE INDEX ahn_hold_idx ON action.hold_notification (hold); CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff ); CREATE TABLE action.hold_copy_map ( - id SERIAL PRIMARY KEY, + id BIGSERIAL PRIMARY KEY, 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, -- XXX could be an serial.issuance CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy) diff --git a/Open-ILS/src/sql/Pg/095.schema.booking.sql b/Open-ILS/src/sql/Pg/095.schema.booking.sql index f79d968cf..6d755824d 100644 --- a/Open-ILS/src/sql/Pg/095.schema.booking.sql +++ b/Open-ILS/src/sql/Pg/095.schema.booking.sql @@ -32,9 +32,9 @@ CREATE TABLE booking.resource_type ( DEFERRABLE INITIALLY DEFERRED, catalog_item BOOLEAN NOT NULL DEFAULT FALSE, transferable BOOLEAN NOT NULL DEFAULT FALSE, - record INT REFERENCES biblio.record_entry (id) + record BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name, record) + CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record) ); CREATE TABLE booking.resource ( diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 13db4da10..0db32d72b 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -482,7 +482,7 @@ CREATE TABLE acq.lineitem ( create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), marc TEXT NOT NULL, - eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED, + eg_bib_id BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED, source_label TEXT, state TEXT NOT NULL DEFAULT 'new', cancel_reason INT REFERENCES acq.cancel_reason( id ) diff --git a/Open-ILS/src/sql/Pg/upgrade/0421.schema.embiggen-ints.sql b/Open-ILS/src/sql/Pg/upgrade/0421.schema.embiggen-ints.sql new file mode 100644 index 000000000..092ccc527 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0421.schema.embiggen-ints.sql @@ -0,0 +1,44 @@ +-- 1. Turn some ints into bigints. + +-- 2. Rename a constraint for consistency and accuracy (currently it may +-- have either of two different names). + +\qecho One of the following DROPs will fail, so we do them +\qecho both outside of a transaction. Ignore the failure. + +ALTER TABLE booking.resource_type + DROP CONSTRAINT brt_name_or_record_once_per_owner; + +ALTER TABLE booking.resource_type + DROP CONSTRAINT brt_name_once_per_owner; + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0421'); -- Scott McKellar + +ALTER TABLE booking.resource_type + ALTER COLUMN record SET DATA TYPE bigint, + ADD CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record); + +ALTER TABLE container.biblio_record_entry_bucket_item + ALTER COLUMN target_biblio_record_entry SET DATA TYPE bigint; + +-- Before we can embiggen the next one, we must drop a view +-- that depends on it (and recreate it later) + +DROP VIEW IF EXISTS acq.acq_lineitem_lifecycle; + +ALTER TABLE acq.lineitem + ALTER COLUMN eg_bib_id SET DATA TYPE bigint; + +-- Recreate the view + +SELECT acq.create_acq_lifecycle( 'acq', 'lineitem' ); + +ALTER TABLE vandelay.queued_bib_record + ALTER COLUMN imported_as SET DATA TYPE bigint; + +ALTER TABLE action.hold_copy_map + ALTER COLUMN id SET DATA TYPE bigint; + +COMMIT; -- 2.11.0