From a88fc6f57f41a28bd38556d37cbac4f78dcb52ab Mon Sep 17 00:00:00 2001 From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4> 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 ++++++++++++++ .../sql/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 </permacrud> </class> + <class id="xisnull" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="query::expr_xisnull" oils_persist:tablename="query.expr_xisnull" reporter:label="IS NULL Expression"> + <fields oils_persist:primary="id" oils_persist:sequence="expression_id_seq"> + <field reporter:label="Expression ID" name="id" reporter:datatype="id"/> + <field reporter:label="Is Parenthesized" name="parenthesize" reporter:datatype="bool"/> + <field reporter:label="Parent Expression" name="parent_expr" reporter:datatype="link"/> + <field reporter:label="Sequence Number" name="seq_no" reporter:datatype="int"/> + <field reporter:label="Left Operand" name="left_operand" reporter:datatype="link"/> + <field reporter:label="Negate?" name="negate" reporter:datatype="bool"/> + </fields> + <links> + <link field="parent_expr" reltype="might_have" key="id" map="" class="qxp"/> + <link field="left_operand" reltype="might_have" key="id" map="" class="qxp"/> + </links> + <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1"> + </permacrud> + </class> + <class id="xnull" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="query::expr_xnull" oils_persist:tablename="query.expr_xnull" reporter:label="Null Expression"> <fields oils_persist:primary="id" oils_persist:sequence="expression_id_seq"> <field reporter:label="Expression ID" name="id" reporter:datatype="id"/> 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.11.0