From 87e91b6a2bbe3706451c7a4c065d4d21c66444d0 Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
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 d0f53c9ce4..1dbd195d66 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 41372e2374..fbfba89a6d 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 beceec94a0..6bbd17c76c 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 76f7c0ba51..170f4626a4 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 f79d968cf9..6d755824d9 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 13db4da102..0db32d72b7 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 0000000000..092ccc5278
--- /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