From a88fc6f57f41a28bd38556d37cbac4f78dcb52ab Mon Sep 17 00:00:00 2001 From: scottmk Date: Thu, 20 May 2010 20:35:12 +0000 Subject: [PATCH] Create database support for IS [NOT] NULL expressions in stored 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/0269.schema.query-xisnull.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@16466 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 17 ++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/008.schema.query.sql | 55 ++++++++++++ .../Pg/upgrade/0269.schema.query-xisnull.sql | 85 +++++++++++++++++++ 4 files changed, 158 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0269.schema.query-xisnull.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index bfe2bafd7f..0f3d7c60d7 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6905,6 +6905,23 @@ 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 2ffb67a68f..2e1cdd6501 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 ('0268'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0269'); -- 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 ac6d80b066..5c627148cd 100644 --- a/Open-ILS/src/sql/Pg/008.schema.query.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -134,6 +134,7 @@ CREATE TABLE query.expression ( 'xfld', -- field 'xfunc', -- function 'xin', -- in + 'xisnull' -- is null 'xnull', -- null 'xnum', -- number 'xop', -- operator @@ -868,6 +869,60 @@ CREATE OR REPLACE RULE query_expr_xin_delete_rule AS DO INSTEAD DELETE FROM query.expression WHERE id = OLD.id; +-- Create updatable view for IS NULL expressions + +CREATE OR REPLACE VIEW query.expr_xisnull AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + left_operand, + negate + FROM + query.expression + WHERE + type = 'xisnull'; + +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, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS + ON UPDATE TO query.expr_xisnull + 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, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS + ON DELETE TO query.expr_xisnull + 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/0269.schema.query-xisnull.sql b/Open-ILS/src/sql/Pg/upgrade/0269.schema.query-xisnull.sql new file mode 100644 index 0000000000..22ff26ff58 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0269.schema.query-xisnull.sql @@ -0,0 +1,85 @@ +BEGIN; + +-- Create new expression type for IS [NOT] NULL + +INSERT INTO config.upgrade_log (version) VALUES ('0269'); -- Scott McKellar + +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 + 'xfld', -- field + 'xfunc', -- function + 'xin', -- in + 'xisnull' -- is null + 'xnull', -- null + 'xnum', -- number + 'xop', -- operator + 'xstr', -- string + 'xsubq' -- subquery + ) ); + +-- Create updatable view for IS NULL expressions + +CREATE OR REPLACE VIEW query.expr_xisnull AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + left_operand, + negate + FROM + query.expression + WHERE + type = 'xisnull'; + +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, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS + ON UPDATE TO query.expr_xisnull + 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, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS + ON DELETE TO query.expr_xisnull + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +COMMIT; -- 2.43.2