From 44d0eb13c72273d7eb6385d4348da9a9008e818a Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 10 Mar 2010 22:30:04 +0000
Subject: [PATCH] Add "ON DELETE CASCADE" clauses to foreign keys pointing to
acq.lineitem, from the following tables:
acq.distribution_formula_application
acq.lineitem_attr
acq.lineitem_detail
acq.lineitem_note
M Open-ILS/src/sql/Pg/200.schema.acq.sql
M Open-ILS/src/sql/Pg/002.schema.config.sql
A Open-ILS/src/sql/Pg/upgrade/0186.schema.acq-on-delete-li.sql
git-svn-id: svn://svn.open-ils.org/ILS/trunk@15780 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +-
Open-ILS/src/sql/Pg/200.schema.acq.sql | 8 ++---
.../Pg/upgrade/0186.schema.acq-on-delete-li.sql | 37 ++++++++++++++++++++++
3 files changed, 42 insertions(+), 5 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0186.schema.acq-on-delete-li.sql
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 3c7b5a3a30..16118eaaea 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log (
install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
-INSERT INTO config.upgrade_log (version) VALUES ('0185'); -- Scott McKellar
+INSERT INTO config.upgrade_log (version) VALUES ('0186'); -- Scott McKellar
CREATE TABLE config.bib_source (
id SERIAL PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index 047a0a4252..873763f77d 100644
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -475,7 +475,7 @@ CREATE TABLE acq.lineitem_alert_text (
CREATE TABLE acq.lineitem_note (
id SERIAL PRIMARY KEY,
- lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
+ lineitem INT NOT NULL REFERENCES acq.lineitem (id) ON CASCADE DELETE DEFERRABLE INITIALLY DEFERRED,
creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
@@ -490,7 +490,7 @@ CREATE INDEX li_note_editor_idx ON acq.lineitem_note ( editor );
CREATE TABLE acq.lineitem_detail (
id BIGSERIAL PRIMARY KEY,
- lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
+ lineitem INT NOT NULL REFERENCES acq.lineitem (id) ON CASCADE DELETE DEFERRABLE INITIALLY DEFERRED,
fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
@@ -544,7 +544,7 @@ CREATE TABLE acq.lineitem_local_attr_definition (
CREATE TABLE acq.lineitem_attr (
id BIGSERIAL PRIMARY KEY,
definition BIGINT NOT NULL,
- lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
+ lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) ON CASCADE DELETE DEFERRABLE INITIALLY DEFERRED,
attr_type TEXT NOT NULL,
attr_name TEXT NOT NULL,
attr_value TEXT NOT NULL
@@ -604,7 +604,7 @@ CREATE TABLE acq.distribution_formula_application (
formula INT NOT NULL
REFERENCES acq.distribution_formula(id) DEFERRABLE INITIALLY DEFERRED,
lineitem INT NOT NULL
- REFERENCES acq.lineitem(id) DEFERRABLE INITIALLY DEFERRED
+ REFERENCES acq.lineitem(id) ON CASCADE DELETE DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX acqdfa_df_idx
diff --git a/Open-ILS/src/sql/Pg/upgrade/0186.schema.acq-on-delete-li.sql b/Open-ILS/src/sql/Pg/upgrade/0186.schema.acq-on-delete-li.sql
new file mode 100644
index 0000000000..32151704b8
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0186.schema.acq-on-delete-li.sql
@@ -0,0 +1,37 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0186'); -- Scott McKellar
+
+ALTER TABLE acq.distribution_formula_application
+ DROP CONSTRAINT distribution_formula_application_lineitem_fkey;
+
+ALTER TABLE acq.distribution_formula_application
+ ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
+ ON DELETE CASCADE
+ DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE acq.lineitem_attr
+ DROP CONSTRAINT lineitem_attr_lineitem_fkey;
+
+ALTER TABLE acq.lineitem_attr
+ ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
+ ON DELETE CASCADE
+ DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE acq.lineitem_detail
+ DROP CONSTRAINT lineitem_detail_lineitem_fkey;
+
+ALTER TABLE acq.lineitem_detail
+ ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
+ ON DELETE CASCADE
+ DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE acq.lineitem_note
+ DROP CONSTRAINT lineitem_note_lineitem_fkey;
+
+ALTER TABLE acq.lineitem_note
+ ADD FOREIGN KEY (lineitem) REFERENCES acq.lineitem( id )
+ ON DELETE CASCADE
+ DEFERRABLE INITIALLY DEFERRED;
+
+COMMIT;
--
2.11.0