From c2c30505e8e3e05d0dc64b5a3f8e5a88cd5a61c2 Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Sun, 30 May 2010 03:50:47 +0000
Subject: [PATCH] For various updatable views based on query.expression: add
 COALESCE to the insert rule so that we don't always have to specify a value
 for the "negate" column.

Also: eliminate a harmless but annoying duplication of
the create statements for the query.expr_xbind view.

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/0286.schema.query-coalesce-negate.sql


git-svn-id: svn://svn.open-ils.org/ILS/trunk@16536 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/002.schema.config.sql     |   2 +-
 Open-ILS/src/sql/Pg/008.schema.query.sql      |  78 +----
 .../0286.schema.query-coalesce-negate.sql     | 309 ++++++++++++++++++
 3 files changed, 324 insertions(+), 65 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0286.schema.query-coalesce-negate.sql

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 38be33104d..6e6ceaf211 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 ('0285'); -- phasefx
+INSERT INTO config.upgrade_log (version) VALUES ('0286'); -- 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 1a91d6659e..420b59e127 100644
--- a/Open-ILS/src/sql/Pg/008.schema.query.sql
+++ b/Open-ILS/src/sql/Pg/008.schema.query.sql
@@ -306,7 +306,7 @@ CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
         COALESCE(NEW.parenthesize, FALSE),
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
@@ -328,56 +328,6 @@ CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
 
 -- Create updatable view for bind variable expressions
 
-CREATE OR REPLACE VIEW query.expr_xbind AS
-    SELECT
-		id,
-		parenthesize,
-		parent_expr,
-		seq_no,
-		bind_variable
-    FROM
-        query.expression
-    WHERE
-        type = 'xbind';
-
-CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
-    ON INSERT TO query.expr_xbind
-    DO INSTEAD
-    INSERT INTO query.expression (
-		id,
-		type,
-		parenthesize,
-		parent_expr,
-		seq_no,
-		bind_variable
-    ) VALUES (
-        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
-        'xbind',
-        COALESCE(NEW.parenthesize, FALSE),
-        NEW.parent_expr,
-        COALESCE(NEW.seq_no, 1),
-		NEW.bind_variable
-    );
-
-CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
-    ON UPDATE TO query.expr_xbind
-    DO INSTEAD
-    UPDATE query.expression SET
-        id = NEW.id,
-        parenthesize = NEW.parenthesize,
-        parent_expr = NEW.parent_expr,
-        seq_no = NEW.seq_no,
-		bind_variable = NEW.bind_variable
-    WHERE
-        id = OLD.id;
-
-CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
-    ON DELETE TO query.expr_xbind
-    DO INSTEAD
-    DELETE FROM query.expression WHERE id = OLD.id;
-
--- Create updatable view for bind variable expressions
-
 CREATE OR REPLACE VIEW query.expr_xbind AS
     SELECT
 		id,
@@ -459,7 +409,7 @@ CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
         NEW.literal,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
@@ -510,7 +460,7 @@ CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
         COALESCE(NEW.parenthesize, FALSE),
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
@@ -566,7 +516,7 @@ CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
         COALESCE(NEW.seq_no, 1),
 		NEW.left_operand,
 		NEW.cast_type,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
@@ -624,7 +574,7 @@ CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
         COALESCE(NEW.seq_no, 1),
 		NEW.table_alias,
 		NEW.column_name,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
@@ -679,7 +629,7 @@ CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
 		NEW.subquery,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
@@ -736,7 +686,7 @@ CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
         COALESCE(NEW.seq_no, 1),
 		NEW.column_name,
 		NEW.left_operand,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
@@ -791,7 +741,7 @@ CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
 		NEW.function_id,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
@@ -848,7 +798,7 @@ CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
         COALESCE(NEW.seq_no, 1),
 		NEW.left_operand,
 		NEW.subquery,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
@@ -903,7 +853,7 @@ CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
 		NEW.left_operand,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
@@ -954,7 +904,7 @@ CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
         COALESCE(NEW.parenthesize, FALSE),
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
@@ -1063,7 +1013,7 @@ CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
 		NEW.left_operand,
 		NEW.operator,
 		NEW.right_operand,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
@@ -1120,7 +1070,7 @@ CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
 		NEW.operator,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
@@ -1224,7 +1174,7 @@ CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
         NEW.parent_expr,
         COALESCE(NEW.seq_no, 1),
 		NEW.subquery,
-		NEW.negate
+		COALESCE(NEW.negate, false)
     );
 
 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
diff --git a/Open-ILS/src/sql/Pg/upgrade/0286.schema.query-coalesce-negate.sql b/Open-ILS/src/sql/Pg/upgrade/0286.schema.query-coalesce-negate.sql
new file mode 100644
index 0000000000..f94cfcb801
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0286.schema.query-coalesce-negate.sql
@@ -0,0 +1,309 @@
+BEGIN;
+
+-- For various updatable views based on query.expression:
+-- add COALESCE to the insert rule so that we don't always
+-- have to specify a value for the "negate" column.
+
+INSERT INTO config.upgrade_log (version) VALUES ('0286'); -- Scott McKellar
+
+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),
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+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),
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+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_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,
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
+    ON INSERT TO query.expr_xisnull
+    DO INSTEAD
+    INSERT INTO query.expression (
+		id,
+		type,
+		parenthesize,
+		parent_expr,
+		seq_no,
+		left_operand,
+		negate
+    ) VALUES (
+        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
+        'xisnull',
+        COALESCE(NEW.parenthesize, FALSE),
+        NEW.parent_expr,
+        COALESCE(NEW.seq_no, 1),
+		NEW.left_operand,
+		COALESCE(NEW.negate, false)
+    );
+
+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),
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
+    ON INSERT TO query.expr_xser
+    DO INSTEAD
+    INSERT INTO query.expression (
+		id,
+		type,
+		parenthesize,
+		parent_expr,
+		seq_no,
+		operator,
+		negate
+    ) VALUES (
+        COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
+        'xser',
+        COALESCE(NEW.parenthesize, FALSE),
+        NEW.parent_expr,
+        COALESCE(NEW.seq_no, 1),
+		NEW.operator,
+		COALESCE(NEW.negate, false)
+    );
+
+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,
+		COALESCE(NEW.negate, false)
+    );
+
+COMMIT;
-- 
2.43.2