From 0f73d47ad7faca68058619a0affc9e6575764e31 Mon Sep 17 00:00:00 2001 From: scottmk Date: Thu, 6 May 2010 16:00:37 +0000 Subject: [PATCH] 1. Add a boolean "negate" column to query.expression. 2. Remove the valid values 'xnbet' (for NOT BETWEEN), 'xnex' (for NOT EXIST) and 'xnin' (for NOT IN) for the column query.expression.type. We will represent those types with 'xbet', 'xnex', and 'xin', respectively, combined with negate = TRUE. 3. Eliminate the updatable views defined for the valid values removed above. 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/0252.schema.query-negation.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@16395 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 47 +----- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/008.schema.query.sql | 152 +----------------- .../Pg/upgrade/0252.schema.query-negation.sql | 18 +++ 4 files changed, 22 insertions(+), 197 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0252.schema.query-negation.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index cbeebea638..82d72b3e66 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6594,6 +6594,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6838,52 +6839,6 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 9f66c0a835..5f23e85d8f 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -65,7 +65,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0251'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0252'); -- 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 e8c8b0c2b1..9d60d8ac24 100644 --- a/Open-ILS/src/sql/Pg/008.schema.query.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -123,9 +123,6 @@ CREATE TABLE query.expression ( 'xfld', -- field 'xfunc', -- function 'xin', -- in - 'xnbet', -- not between - 'xnex', -- not exists - 'xnin', -- not in 'xnull', -- null 'xnum', -- number 'xop', -- operator @@ -150,7 +147,8 @@ CREATE TABLE query.expression ( subquery INT REFERENCES query.stored_query DEFERRABLE INITIALLY DEFERRED, cast_type INT REFERENCES query.datatype - DEFERRABLE INITIALLY DEFERRED + DEFERRABLE INITIALLY DEFERRED, + negate BOOL NOT NULL DEFAULT FALSE ); CREATE UNIQUE INDEX query_expr_parent_seq @@ -717,152 +715,6 @@ CREATE OR REPLACE RULE query_expr_xin_delete_rule AS DO INSTEAD DELETE FROM query.expression WHERE id = OLD.id; --- Create updatable view for NOT BETWEEN expressions - -CREATE OR REPLACE VIEW query.expr_xnbet AS - SELECT - id, - parenthesize, - parent_expr, - seq_no - FROM - query.expression - WHERE - type = 'xnbet'; - -CREATE OR REPLACE RULE query_expr_xnbet_insert_rule AS - ON INSERT TO query.expr_xnbet - DO INSTEAD - INSERT INTO query.expression ( - id, - type, - parenthesize, - parent_expr, - seq_no - ) VALUES ( - COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), - 'xnbet', - COALESCE(NEW.parenthesize, FALSE), - NEW.parent_expr, - COALESCE(NEW.seq_no, 1) - ); - -CREATE OR REPLACE RULE query_expr_xnbet_update_rule AS - ON UPDATE TO query.expr_xnbet - DO INSTEAD - UPDATE query.expression SET - id = NEW.id, - parenthesize = NEW.parenthesize, - parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no - WHERE - id = OLD.id; - -CREATE OR REPLACE RULE query_expr_xnbet_delete_rule AS - ON DELETE TO query.expr_xnbet - DO INSTEAD - DELETE FROM query.expression WHERE id = OLD.id; - --- Create updatable view for NOT EXISTS expressions - -CREATE OR REPLACE VIEW query.expr_xnex AS - SELECT - id, - parenthesize, - parent_expr, - seq_no, - subquery - FROM - query.expression - WHERE - type = 'xnex'; - -CREATE OR REPLACE RULE query_expr_xnex_insert_rule AS - ON INSERT TO query.expr_xnex - DO INSTEAD - INSERT INTO query.expression ( - id, - type, - parenthesize, - parent_expr, - seq_no, - subquery - ) VALUES ( - COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), - 'xnex', - COALESCE(NEW.parenthesize, FALSE), - NEW.parent_expr, - COALESCE(NEW.seq_no, 1), - NEW.subquery - ); - -CREATE OR REPLACE RULE query_expr_xnex_update_rule AS - ON UPDATE TO query.expr_xnex - DO INSTEAD - UPDATE query.expression SET - id = NEW.id, - parenthesize = NEW.parenthesize, - parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no, - subquery = NEW.subquery - WHERE - id = OLD.id; - -CREATE OR REPLACE RULE query_expr_xnex_delete_rule AS - ON DELETE TO query.expr_xnex - DO INSTEAD - DELETE FROM query.expression WHERE id = OLD.id; - --- Create updatable view for NOT IN expressions - -CREATE OR REPLACE VIEW query.expr_xnin AS - SELECT - id, - parenthesize, - parent_expr, - seq_no, - subquery - FROM - query.expression - WHERE - type = 'xnin'; - -CREATE OR REPLACE RULE query_expr_xnin_insert_rule AS - ON INSERT TO query.expr_xnin - DO INSTEAD - INSERT INTO query.expression ( - id, - type, - parenthesize, - parent_expr, - seq_no, - subquery - ) VALUES ( - COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), - 'xnin', - COALESCE(NEW.parenthesize, FALSE), - NEW.parent_expr, - COALESCE(NEW.seq_no, 1), - NEW.subquery - ); - -CREATE OR REPLACE RULE query_expr_xnin_update_rule AS - ON UPDATE TO query.expr_xnin - DO INSTEAD - UPDATE query.expression SET - id = NEW.id, - parenthesize = NEW.parenthesize, - parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no, - subquery = NEW.subquery - WHERE - id = OLD.id; - -CREATE OR REPLACE RULE query_expr_xnin_delete_rule AS - ON DELETE TO query.expr_xnin - DO INSTEAD - DELETE FROM query.expression WHERE id = OLD.id; - -- Create updatable view for NULL expressions CREATE OR REPLACE VIEW query.expr_xnull AS diff --git a/Open-ILS/src/sql/Pg/upgrade/0252.schema.query-negation.sql b/Open-ILS/src/sql/Pg/upgrade/0252.schema.query-negation.sql new file mode 100644 index 0000000000..b5252e3c77 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0252.schema.query-negation.sql @@ -0,0 +1,18 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0252'); -- Scott McKellar + +ALTER TABLE query.expression + ADD COLUMN negate BOOL NOT NULL DEFAULT FALSE; + +COMMIT; + +-- The following DROPs will fail if the views being dropped don't exist, +-- and that's okay. That's why they're outside of the BEGIN/COMMIT. + +DROP VIEW query.expr_xnbet; + +DROP VIEW query.expr_xnex; + +DROP VIEW query.expr_xnin; + -- 2.43.2