From f162d51940be2b37e13a0ab740b5c587c8712483 Mon Sep 17 00:00:00 2001 From: scottmk Date: Mon, 29 Mar 2010 19:22:38 +0000 Subject: [PATCH] In query.stored query: make from_clause nullable, so that we can represent UNION, INTERSECT, and EXCEPT queries. M Open-ILS/src/sql/Pg/002.schema.config.sql M Open-ILS/src/sql/Pg/008.schema.query.sql A Open-ILS/src/sql/Pg/upgrade/0220.schema.query.nullable-from.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@16038 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/008.schema.query.sql | 5 +++-- .../sql/Pg/upgrade/0220.schema.query.nullable-from.sql | 9 +++++++++ 3 files changed, 13 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0220.schema.query.nullable-from.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index cb38751ddf..e2f7eb5e13 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -60,7 +60,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0219'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0220); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/008.schema.query.sql b/Open-ILS/src/sql/Pg/008.schema.query.sql index a95dd0ec80..e8c8b0c2b1 100644 --- a/Open-ILS/src/sql/Pg/008.schema.query.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -34,8 +34,9 @@ CREATE TABLE query.stored_query ( ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ), use_all BOOLEAN NOT NULL DEFAULT FALSE, use_distinct BOOLEAN NOT NULL DEFAULT FALSE, - from_clause INT NOT NULL , --REFERENCES query.from_clause - where_clause INT , --REFERENCES query.expression + from_clause INT, --REFERENCES query.from_clause + --DEFERRABLE INITIALLY DEFERRED, + where_clause INT, --REFERENCES query.expression --DEFERRABLE INITIALLY DEFERRED, having_clause INT --REFERENCES query.expression --DEFERRABLE INITIALLY DEFERRED diff --git a/Open-ILS/src/sql/Pg/upgrade/0220.schema.query.nullable-from.sql b/Open-ILS/src/sql/Pg/upgrade/0220.schema.query.nullable-from.sql new file mode 100644 index 0000000000..c12e43f6c8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0220.schema.query.nullable-from.sql @@ -0,0 +1,9 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0220'); -- Scott McKellar + +ALTER TABLE query.stored_query + ALTER COLUMN from_clause DROP NOT NULL; + +COMMIT; + -- 2.43.2