From 28d68f0a9a51be8683ea2793cf54128e2dcdba88 Mon Sep 17 00:00:00 2001 From: scottmk Date: Thu, 27 May 2010 16:10:50 +0000 Subject: [PATCH 1/1] Add new expression type 'xser' to represent series expressions, i.e. a series of expressions separated by operators, such as a chain of ANDed conditions. 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/0280.schema.query-xser.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@16521 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 48 ++++++---- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/008.schema.query.sql | 56 ++++++++++++ .../sql/Pg/upgrade/0280.schema.query-xser.sql | 87 +++++++++++++++++++ 4 files changed, 176 insertions(+), 17 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0280.schema.query-xser.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 2992e75f0a..db798065dc 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6864,7 +6864,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -6899,8 +6899,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - + + @@ -6917,9 +6917,9 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - - + + + @@ -6935,8 +6935,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - + + @@ -6951,7 +6951,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -6966,7 +6966,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -6984,9 +6984,25 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - - + + + + + + + + + + + + + + + + + + + @@ -7001,7 +7017,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -7016,8 +7032,8 @@ 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 132ddaf3b2..93f2a6df6c 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 ('0279'); -- atz / senator +INSERT INTO config.upgrade_log (version) VALUES ('0280'); -- 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 dccb1a66ef..1a91d6659e 100644 --- a/Open-ILS/src/sql/Pg/008.schema.query.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -138,6 +138,7 @@ CREATE TABLE query.expression ( 'xnull', -- null 'xnum', -- number 'xop', -- operator + 'xser', -- series 'xstr', -- string 'xsubq' -- subquery ) ), @@ -1085,6 +1086,61 @@ CREATE OR REPLACE RULE query_expr_xop_delete_rule AS DO INSTEAD DELETE FROM query.expression WHERE id = OLD.id; +-- Create updatable view for series expressions, +-- i.e. series of expressions separated by operators + +CREATE OR REPLACE VIEW query.expr_xser AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + operator, + negate + FROM + query.expression + WHERE + type = 'xser'; + +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, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xser_update_rule AS + ON UPDATE TO query.expr_xser + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + operator = NEW.operator, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xser_delete_rule AS + ON DELETE TO query.expr_xser + 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 diff --git a/Open-ILS/src/sql/Pg/upgrade/0280.schema.query-xser.sql b/Open-ILS/src/sql/Pg/upgrade/0280.schema.query-xser.sql new file mode 100644 index 0000000000..be85fb6b9c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0280.schema.query-xser.sql @@ -0,0 +1,87 @@ +BEGIN; + +-- Create new expression type 'xser' for serial expressions, +-- i.e. series of expressions separated by operators + +INSERT INTO config.upgrade_log (version) VALUES ('0280'); -- 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 + 'xser', -- series + 'xstr', -- string + 'xsubq' -- subquery + ) ); + +-- Create updatable view for series expressions + +CREATE OR REPLACE VIEW query.expr_xser AS + SELECT + id, + parenthesize, + parent_expr, + seq_no, + operator, + negate + FROM + query.expression + WHERE + type = 'xser'; + +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, + NEW.negate + ); + +CREATE OR REPLACE RULE query_expr_xser_update_rule AS + ON UPDATE TO query.expr_xser + DO INSTEAD + UPDATE query.expression SET + id = NEW.id, + parenthesize = NEW.parenthesize, + parent_expr = NEW.parent_expr, + seq_no = NEW.seq_no, + operator = NEW.operator, + negate = NEW.negate + WHERE + id = OLD.id; + +CREATE OR REPLACE RULE query_expr_xser_delete_rule AS + ON DELETE TO query.expr_xser + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + +COMMIT; -- 2.43.2