From 8afb24be89d4001de8b01c6f4c29617fb58e39c8 Mon Sep 17 00:00:00 2001 From: scottmk Date: Mon, 28 Jun 2010 19:45:43 +0000 Subject: [PATCH] Represent a subfield expression as a variant of a function call expression, rather than a function call in its own right. 1. Eliminate 'xfld' as a valid value for query.expression.type. 2. Eliminate the query.expr_xfld view. 3. Expand the query.expr_xfunc view to include the column_name column. 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/0322.schema.query.no-xfld-expr.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@16824 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 19 +--- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/008.schema.query.sql | 63 +----------- .../Pg/upgrade/0322.schema.query.no-xfld-expr.sql | 112 +++++++++++++++++++++ 4 files changed, 118 insertions(+), 78 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0322.schema.query.no-xfld-expr.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index fcc6ffb9e1..0545152363 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -7144,30 +7144,13 @@ 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 5be1d9a781..ca3b0eb976 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0321'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0322'); -- 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 be97141d28..6fef65ed8d 100644 --- a/Open-ILS/src/sql/Pg/008.schema.query.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -135,7 +135,6 @@ CREATE TABLE query.expression ( 'xcast', -- cast 'xcol', -- column 'xex', -- exists - 'xfld', -- field 'xfunc', -- function 'xin', -- in 'xisnull', -- is null @@ -662,64 +661,6 @@ CREATE OR REPLACE RULE query_expr_xex_delete_rule AS DO INSTEAD DELETE FROM query.expression WHERE id = OLD.id; --- Create updatable view for field expressions - -CREATE OR REPLACE VIEW query.expr_xfld AS - SELECT - id, - parenthesize, - parent_expr, - seq_no, - column_name, - left_operand, - negate - FROM - query.expression - WHERE - type = 'xfld'; - -CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS - ON INSERT TO query.expr_xfld - DO INSTEAD - INSERT INTO query.expression ( - id, - type, - parenthesize, - parent_expr, - seq_no, - column_name, - left_operand, - negate - ) VALUES ( - COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), - 'xfld', - COALESCE(NEW.parenthesize, FALSE), - NEW.parent_expr, - COALESCE(NEW.seq_no, 1), - NEW.column_name, - NEW.left_operand, - COALESCE(NEW.negate, false) - ); - -CREATE OR REPLACE RULE query_expr_xfld_update_rule AS - ON UPDATE TO query.expr_xfld - DO INSTEAD - UPDATE query.expression SET - id = NEW.id, - parenthesize = NEW.parenthesize, - parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no, - column_name = NEW.column_name, - left_operand = NEW.left_operand, - negate = NEW.negate - WHERE - id = OLD.id; - -CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS - ON DELETE TO query.expr_xfld - DO INSTEAD - DELETE FROM query.expression WHERE id = OLD.id; - -- Create updatable view for function call expressions CREATE OR REPLACE VIEW query.expr_xfunc AS @@ -728,6 +669,7 @@ CREATE OR REPLACE VIEW query.expr_xfunc AS parenthesize, parent_expr, seq_no, + column_name, function_id, negate FROM @@ -744,6 +686,7 @@ CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS parenthesize, parent_expr, seq_no, + column_name, function_id, negate ) VALUES ( @@ -752,6 +695,7 @@ CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, COALESCE(NEW.seq_no, 1), + NEW.column_name, NEW.function_id, COALESCE(NEW.negate, false) ); @@ -764,6 +708,7 @@ CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, + column_name = NEW.column_name, function_id = NEW.function_id, negate = NEW.negate WHERE diff --git a/Open-ILS/src/sql/Pg/upgrade/0322.schema.query.no-xfld-expr.sql b/Open-ILS/src/sql/Pg/upgrade/0322.schema.query.no-xfld-expr.sql new file mode 100644 index 0000000000..f66c68c413 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0322.schema.query.no-xfld-expr.sql @@ -0,0 +1,112 @@ +-- Instead of representing a subfield as a distinct expression type in its +-- own right, express it as a variant of the xfunc type -- i.e. a function +-- call with a column name. In consequence: + +-- 1. Eliminate the query.expr_xfld view; + +-- 2. Expand the query.expr_xfunc view to include column_name; + +-- 3. Eliminate 'xfld' as a valid value for expression.type. + +-- Theoretically, the latter change could create a problem if you already +-- have xfld rows in your expression table. You would have to delete them, +-- and represent the corresponding expressions by other means. In practice +-- this is exceedingly unlikely, since subfields were never even +-- supported until earlier today. + +-- We start by dropping two views; the first for good, and the second so that +-- we can replace it. We drop them outside the transaction so that the +-- script won't fail if the views don't exist yet. + +DROP VIEW query.expr_xfld; + +DROP VIEW query.expr_xfunc; + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0322'); -- Scott McKellar + +-- Eliminate 'xfld' as an expression type + +ALTER TABLE query.expression + DROP CONSTRAINT expression_type; + +ALTER TABLE query.expression + ADD CONSTRAINT expression_type CHECK ( type in ( + 'xbet', -- between + 'xbind', -- bind variable + 'xbool', -- boolean + 'xcase', -- case + 'xcast', -- cast + 'xcol', -- column + 'xex', -- exists + 'xfunc', -- function + 'xin', -- in + 'xisnull', -- is null + 'xnull', -- null + 'xnum', -- number + 'xop', -- operator + 'xser', -- series + 'xstr', -- string + 'xsubq' -- subquery + ) ); + +-- Create updatable view for function call expressions + +CREATE OR REPLACE VIEW query.expr_xfunc AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + column_name, + function_id, + negate + FROM + query.expression + WHERE + type = 'xfunc'; + +CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS + ON INSERT TO query.expr_xfunc + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + column_name, + function_id, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xfunc', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.column_name, + NEW.function_id, + COALESCE(NEW.negate, false) + ); + +CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS + ON UPDATE TO query.expr_xfunc + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + column_name = NEW.column_name, + function_id = NEW.function_id, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS + ON DELETE TO query.expr_xfunc + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +COMMIT; -- 2.11.0