From f7fea567240164c04c669b4b87ee5dcabb0e41ef Mon Sep 17 00:00:00 2001 From: scottmk Date: Sat, 8 May 2010 20:17:42 +0000 Subject: [PATCH] Revise the updatable views for the various types of query.expression: 1. Rename query.expr_string to query.expr_xstr, for consistency. 2. Add a "negate" column to each view, except for query.expr_xnum and query.expr_xstr. 3. Add a "left_operand" column to query.expr_xin, correcting an earlier oversignt. 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/0253.schema.query_expr_view.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@16408 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 15 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/008.schema.query.sql | 156 +++-- .../sql/Pg/upgrade/0253.schema.query_expr_view.sql | 749 +++++++++++++++++++++ 4 files changed, 868 insertions(+), 54 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0253.schema.query_expr_view.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index d6fbf28142..18a9991301 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6703,6 +6703,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6718,6 +6719,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6732,6 +6734,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6748,6 +6751,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6766,6 +6770,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6781,6 +6786,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6798,6 +6804,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6814,6 +6821,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6829,10 +6837,13 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + @@ -6845,6 +6856,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6877,6 +6889,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -6887,7 +6900,7 @@ 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 5f23e85d8f..ac7e79884b 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 ('0252'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0253'); -- 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 9d60d8ac24..aa22e0a539 100644 --- a/Open-ILS/src/sql/Pg/008.schema.query.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -268,7 +268,8 @@ CREATE OR REPLACE VIEW query.expr_xbet AS id, parenthesize, parent_expr, - seq_no + seq_no, + negate FROM query.expression WHERE @@ -282,13 +283,15 @@ CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS type, parenthesize, parent_expr, - seq_no + seq_no, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xbet', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, - COALESCE(NEW.seq_no, 1) + COALESCE(NEW.seq_no, 1), + NEW.negate ); CREATE OR REPLACE RULE query_expr_xbet_update_rule AS @@ -298,7 +301,8 @@ CREATE OR REPLACE RULE query_expr_xbet_update_rule AS id = NEW.id, parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no + seq_no = NEW.seq_no, + negate = NEW.negate WHERE id = OLD.id; @@ -315,7 +319,8 @@ CREATE OR REPLACE VIEW query.expr_xbool AS parenthesize, parent_expr, seq_no, - literal + literal, + negate FROM query.expression WHERE @@ -330,14 +335,16 @@ CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS parenthesize, parent_expr, seq_no, - literal + literal, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xbool', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, COALESCE(NEW.seq_no, 1), - NEW.literal + NEW.literal, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xbool_update_rule AS @@ -348,7 +355,8 @@ CREATE OR REPLACE RULE query_expr_xbool_update_rule AS parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, - literal = NEW.literal + literal = NEW.literal, + negate = NEW.negate WHERE id = OLD.id; @@ -364,7 +372,8 @@ CREATE OR REPLACE VIEW query.expr_xcase AS id, parenthesize, parent_expr, - seq_no + seq_no, + negate FROM query.expression WHERE @@ -378,13 +387,15 @@ CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS type, parenthesize, parent_expr, - seq_no + seq_no, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xcase', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, - COALESCE(NEW.seq_no, 1) + COALESCE(NEW.seq_no, 1), + NEW.negate ); CREATE OR REPLACE RULE query_expr_xcase_update_rule AS @@ -394,7 +405,8 @@ CREATE OR REPLACE RULE query_expr_xcase_update_rule AS id = NEW.id, parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no + seq_no = NEW.seq_no, + negate = NEW.negate WHERE id = OLD.id; @@ -412,7 +424,8 @@ CREATE OR REPLACE VIEW query.expr_xcast AS parent_expr, seq_no, left_operand, - cast_type + cast_type, + negate FROM query.expression WHERE @@ -428,7 +441,8 @@ CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS parent_expr, seq_no, left_operand, - cast_type + cast_type, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xcast', @@ -436,7 +450,8 @@ CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS NEW.parent_expr, COALESCE(NEW.seq_no, 1), NEW.left_operand, - NEW.cast_type + NEW.cast_type, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xcast_update_rule AS @@ -448,7 +463,8 @@ CREATE OR REPLACE RULE query_expr_xcast_update_rule AS parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, left_operand = NEW.left_operand, - cast_type = NEW.cast_type + cast_type = NEW.cast_type, + negate = NEW.negate WHERE id = OLD.id; @@ -466,7 +482,8 @@ CREATE OR REPLACE VIEW query.expr_xcol AS parent_expr, seq_no, table_alias, - column_name + column_name, + negate FROM query.expression WHERE @@ -482,7 +499,8 @@ CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS parent_expr, seq_no, table_alias, - column_name + column_name, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xcol', @@ -490,7 +508,8 @@ CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS NEW.parent_expr, COALESCE(NEW.seq_no, 1), NEW.table_alias, - NEW.column_name + NEW.column_name, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xcol_update_rule AS @@ -502,7 +521,8 @@ CREATE OR REPLACE RULE query_expr_xcol_update_rule AS parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, table_alias = NEW.table_alias, - column_name = NEW.column_name + column_name = NEW.column_name, + negate = NEW.negate WHERE id = OLD.id; @@ -519,7 +539,8 @@ CREATE OR REPLACE VIEW query.expr_xex AS parenthesize, parent_expr, seq_no, - subquery + subquery, + negate FROM query.expression WHERE @@ -534,14 +555,16 @@ CREATE OR REPLACE RULE query_expr_xex_insert_rule AS parenthesize, parent_expr, seq_no, - subquery + subquery, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xex', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, COALESCE(NEW.seq_no, 1), - NEW.subquery + NEW.subquery, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xex_update_rule AS @@ -552,7 +575,8 @@ CREATE OR REPLACE RULE query_expr_xex_update_rule AS parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, - subquery = NEW.subquery + subquery = NEW.subquery, + negate = NEW.negate WHERE id = OLD.id; @@ -570,7 +594,8 @@ CREATE OR REPLACE VIEW query.expr_xfld AS parent_expr, seq_no, column_name, - left_operand + left_operand, + negate FROM query.expression WHERE @@ -586,7 +611,8 @@ CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS parent_expr, seq_no, column_name, - left_operand + left_operand, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xfld', @@ -594,7 +620,8 @@ CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS NEW.parent_expr, COALESCE(NEW.seq_no, 1), NEW.column_name, - NEW.left_operand + NEW.left_operand, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xfld_update_rule AS @@ -606,7 +633,8 @@ CREATE OR REPLACE RULE query_expr_xfld_update_rule AS parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, column_name = NEW.column_name, - left_operand = NEW.left_operand + left_operand = NEW.left_operand, + negate = NEW.negate WHERE id = OLD.id; @@ -623,7 +651,8 @@ CREATE OR REPLACE VIEW query.expr_xfunc AS parenthesize, parent_expr, seq_no, - function_id + function_id, + negate FROM query.expression WHERE @@ -638,14 +667,16 @@ CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS parenthesize, parent_expr, seq_no, - function_id + 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.function_id + NEW.function_id, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS @@ -656,7 +687,8 @@ CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, - function_id = NEW.function_id + function_id = NEW.function_id, + negate = NEW.negate WHERE id = OLD.id; @@ -673,7 +705,9 @@ CREATE OR REPLACE VIEW query.expr_xin AS parenthesize, parent_expr, seq_no, - subquery + left_operand, + subquery, + negate FROM query.expression WHERE @@ -688,14 +722,18 @@ CREATE OR REPLACE RULE query_expr_xin_insert_rule AS parenthesize, parent_expr, seq_no, - subquery + left_operand, + subquery, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xin', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, COALESCE(NEW.seq_no, 1), - NEW.subquery + NEW.left_operand, + NEW.subquery, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xin_update_rule AS @@ -706,7 +744,9 @@ CREATE OR REPLACE RULE query_expr_xin_update_rule AS parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, - subquery = NEW.subquery + left_operand = NEW.left_operand, + subquery = NEW.subquery, + negate = NEW.negate WHERE id = OLD.id; @@ -722,7 +762,8 @@ CREATE OR REPLACE VIEW query.expr_xnull AS id, parenthesize, parent_expr, - seq_no + seq_no, + negate FROM query.expression WHERE @@ -736,13 +777,15 @@ CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS type, parenthesize, parent_expr, - seq_no + seq_no, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xnull', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, - COALESCE(NEW.seq_no, 1) + COALESCE(NEW.seq_no, 1), + NEW.negate ); CREATE OR REPLACE RULE query_expr_xnull_update_rule AS @@ -752,7 +795,8 @@ CREATE OR REPLACE RULE query_expr_xnull_update_rule AS id = NEW.id, parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, - seq_no = NEW.seq_no + seq_no = NEW.seq_no, + negate = NEW.negate WHERE id = OLD.id; @@ -821,7 +865,8 @@ CREATE OR REPLACE VIEW query.expr_xop AS seq_no, left_operand, operator, - right_operand + right_operand, + negate FROM query.expression WHERE @@ -838,7 +883,8 @@ CREATE OR REPLACE RULE query_expr_xop_insert_rule AS seq_no, left_operand, operator, - right_operand + right_operand, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xop', @@ -847,7 +893,8 @@ CREATE OR REPLACE RULE query_expr_xop_insert_rule AS COALESCE(NEW.seq_no, 1), NEW.left_operand, NEW.operator, - NEW.right_operand + NEW.right_operand, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xop_update_rule AS @@ -860,7 +907,8 @@ CREATE OR REPLACE RULE query_expr_xop_update_rule AS seq_no = NEW.seq_no, left_operand = NEW.left_operand, operator = NEW.operator, - right_operand = NEW.right_operand + right_operand = NEW.right_operand, + negate = NEW.negate WHERE id = OLD.id; @@ -871,7 +919,7 @@ CREATE OR REPLACE RULE query_expr_xop_delete_rule AS -- Create updatable view for string literal expressions -CREATE OR REPLACE VIEW query.expr_string AS +CREATE OR REPLACE VIEW query.expr_xstr AS SELECT id, parenthesize, @@ -884,7 +932,7 @@ CREATE OR REPLACE VIEW query.expr_string AS type = 'xstr'; CREATE OR REPLACE RULE query_expr_string_insert_rule AS - ON INSERT TO query.expr_string + ON INSERT TO query.expr_xstr DO INSTEAD INSERT INTO query.expression ( id, @@ -903,7 +951,7 @@ CREATE OR REPLACE RULE query_expr_string_insert_rule AS ); CREATE OR REPLACE RULE query_expr_string_update_rule AS - ON UPDATE TO query.expr_string + ON UPDATE TO query.expr_xstr DO INSTEAD UPDATE query.expression SET id = NEW.id, @@ -915,7 +963,7 @@ CREATE OR REPLACE RULE query_expr_string_update_rule AS id = OLD.id; CREATE OR REPLACE RULE query_expr_string_delete_rule AS - ON DELETE TO query.expr_string + ON DELETE TO query.expr_xstr DO INSTEAD DELETE FROM query.expression WHERE id = OLD.id; @@ -927,7 +975,8 @@ CREATE OR REPLACE VIEW query.expr_xsubq AS parenthesize, parent_expr, seq_no, - subquery + subquery, + negate FROM query.expression WHERE @@ -942,14 +991,16 @@ CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS parenthesize, parent_expr, seq_no, - subquery + subquery, + negate ) VALUES ( COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), 'xsubq', COALESCE(NEW.parenthesize, FALSE), NEW.parent_expr, COALESCE(NEW.seq_no, 1), - NEW.subquery + NEW.subquery, + NEW.negate ); CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS @@ -960,7 +1011,8 @@ CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS parenthesize = NEW.parenthesize, parent_expr = NEW.parent_expr, seq_no = NEW.seq_no, - subquery = NEW.subquery + subquery = NEW.subquery, + negate = NEW.negate WHERE id = OLD.id; diff --git a/Open-ILS/src/sql/Pg/upgrade/0253.schema.query_expr_view.sql b/Open-ILS/src/sql/Pg/upgrade/0253.schema.query_expr_view.sql new file mode 100644 index 0000000000..d281117b57 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0253.schema.query_expr_view.sql @@ -0,0 +1,749 @@ +-- Add a negate column to most of the query expression views, +-- plus a left_operand column in the case of query.expr_xin. + +-- The DROP VIEW statements will fail harmlessly if the views +-- don't exist, and are therefore outside of the transaction. + +DROP VIEW query.expr_xbet CASCADE; + +DROP VIEW query.expr_xbool CASCADE; + +DROP VIEW query.expr_xcase CASCADE; + +DROP VIEW query.expr_xcast CASCADE; + +DROP VIEW query.expr_xcol CASCADE; + +DROP VIEW query.expr_xex CASCADE; + +DROP VIEW query.expr_xfld CASCADE; + +DROP VIEW query.expr_xfunc CASCADE; + +DROP VIEW query.expr_xin CASCADE; + +DROP VIEW query.expr_xnull CASCADE; + +DROP VIEW query.expr_xop CASCADE; + +DROP VIEW query.expr_string CASCADE; + +DROP VIEW query.expr_xsubq CASCADE; + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0253'); -- Scott McKellar + +-- Create updatable views ------------------------------------------- + +-- Create updatable view for BETWEEN expressions + +CREATE OR REPLACE VIEW query.expr_xbet AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + negate + FROM + query.expression + WHERE + type = 'xbet'; + +CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS + ON INSERT TO query.expr_xbet + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xbet', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xbet_update_rule AS + ON UPDATE TO query.expr_xbet + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS + ON DELETE TO query.expr_xbet + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for boolean expressions + +CREATE OR REPLACE VIEW query.expr_xbool AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + literal, + negate + FROM + query.expression + WHERE + type = 'xbool'; + +CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS + ON INSERT TO query.expr_xbool + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + literal, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xbool', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.literal, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xbool_update_rule AS + ON UPDATE TO query.expr_xbool + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + literal = NEW.literal, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS + ON DELETE TO query.expr_xbool + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for CASE expressions + +CREATE OR REPLACE VIEW query.expr_xcase AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + negate + FROM + query.expression + WHERE + type = 'xcase'; + +CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS + ON INSERT TO query.expr_xcase + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xcase', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xcase_update_rule AS + ON UPDATE TO query.expr_xcase + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS + ON DELETE TO query.expr_xcase + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for cast expressions + +CREATE OR REPLACE VIEW query.expr_xcast AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + left_operand, + cast_type, + negate + FROM + query.expression + WHERE + type = 'xcast'; + +CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS + ON INSERT TO query.expr_xcast + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + left_operand, + cast_type, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xcast', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.left_operand, + NEW.cast_type, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xcast_update_rule AS + ON UPDATE TO query.expr_xcast + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + left_operand = NEW.left_operand, + cast_type = NEW.cast_type, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS + ON DELETE TO query.expr_xcast + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for column expressions + +CREATE OR REPLACE VIEW query.expr_xcol AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + table_alias, + column_name, + negate + FROM + query.expression + WHERE + type = 'xcol'; + +CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS + ON INSERT TO query.expr_xcol + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + table_alias, + column_name, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xcol', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.table_alias, + NEW.column_name, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xcol_update_rule AS + ON UPDATE TO query.expr_xcol + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + table_alias = NEW.table_alias, + column_name = NEW.column_name, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS + ON DELETE TO query.expr_xcol + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for EXISTS expressions + +CREATE OR REPLACE VIEW query.expr_xex AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + subquery, + negate + FROM + query.expression + WHERE + type = 'xex'; + +CREATE OR REPLACE RULE query_expr_xex_insert_rule AS + ON INSERT TO query.expr_xex + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + subquery, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xex', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.subquery, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xex_update_rule AS + ON UPDATE TO query.expr_xex + 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, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xex_delete_rule AS + ON DELETE TO query.expr_xex + 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, + NEW.negate + ); + +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 + SELECT + id, + parenthesize, + parent_expr, + seq_no, + 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, + 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.function_id, + NEW.negate + ); + +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, + 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; + +-- Create updatable view for IN expressions + +CREATE OR REPLACE VIEW query.expr_xin AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + left_operand, + subquery, + negate + FROM + query.expression + WHERE + type = 'xin'; + +CREATE OR REPLACE RULE query_expr_xin_insert_rule AS + ON INSERT TO query.expr_xin + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + left_operand, + subquery, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xin', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.left_operand, + NEW.subquery, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xin_update_rule AS + ON UPDATE TO query.expr_xin + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + left_operand = NEW.left_operand, + subquery = NEW.subquery, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xin_delete_rule AS + ON DELETE TO query.expr_xin + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for NULL expressions + +CREATE OR REPLACE VIEW query.expr_xnull AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + negate + FROM + query.expression + WHERE + type = 'xnull'; + +CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS + ON INSERT TO query.expr_xnull + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xnull', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xnull_update_rule AS + ON UPDATE TO query.expr_xnull + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS + ON DELETE TO query.expr_xnull + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for operator expressions + +CREATE OR REPLACE VIEW query.expr_xop AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + left_operand, + operator, + right_operand, + negate + FROM + query.expression + WHERE + type = 'xop'; + +CREATE OR REPLACE RULE query_expr_xop_insert_rule AS + ON INSERT TO query.expr_xop + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + left_operand, + operator, + right_operand, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xop', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.left_operand, + NEW.operator, + NEW.right_operand, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xop_update_rule AS + ON UPDATE TO query.expr_xop + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + left_operand = NEW.left_operand, + operator = NEW.operator, + right_operand = NEW.right_operand, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xop_delete_rule AS + ON DELETE TO query.expr_xop + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for string literal expressions + +CREATE OR REPLACE VIEW query.expr_xstr AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + literal + FROM + query.expression + WHERE + type = 'xstr'; + +CREATE OR REPLACE RULE query_expr_string_insert_rule AS + ON INSERT TO query.expr_xstr + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + literal + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xstr', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.literal + ); + +CREATE OR REPLACE RULE query_expr_string_update_rule AS + ON UPDATE TO query.expr_xstr + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + literal = NEW.literal + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_string_delete_rule AS + ON DELETE TO query.expr_xstr + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +-- Create updatable view for subquery expressions + +CREATE OR REPLACE VIEW query.expr_xsubq AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + subquery, + negate + FROM + query.expression + WHERE + type = 'xsubq'; + +CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS + ON INSERT TO query.expr_xsubq + DO INSTEAD + INSERT INTO query.expression ( + id, + type, + parenthesize, + parent_expr, + seq_no, + subquery, + negate + ) VALUES ( + COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)), + 'xsubq', + COALESCE(NEW.parenthesize, FALSE), + NEW.parent_expr, + COALESCE(NEW.seq_no, 1), + NEW.subquery, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS + ON UPDATE TO query.expr_xsubq + 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, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS + ON DELETE TO query.expr_xsubq + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +COMMIT; -- 2.11.0