From 8d11e9235042f376e832771a8192c266cbc9472d Mon Sep 17 00:00:00 2001 From: scottmk Date: Mon, 30 Nov 2009 20:35:11 +0000 Subject: [PATCH] Create a schema named "query" instead of "sql" (which is a reserved word). Note that two previous upgrade scripts are being changed. If you have already run them, you can run them again after manually deleting two entries from config.upgrade.log: DELETE FROM config.upgrade.log WHERE version in ( '0097', '0098' ); The 0097.schema.sql-datatype.sql will drop the misbegotten sql schema if it exists. A Pg/008.schema.query.sql M Pg/upgrade/0098.schema.sql-stored-query.sql M Pg/upgrade/0097.schema.sql-datatype.sql D Pg/008.schema.sql.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@15047 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- ...08.schema.sql.sql => 008.schema.query.sql} | 172 +++++++++++------- .../Pg/upgrade/0097.schema.sql-datatype.sql | 27 +-- .../upgrade/0098.schema.sql-stored-query.sql | 16 +- 3 files changed, 133 insertions(+), 82 deletions(-) rename Open-ILS/src/sql/Pg/{008.schema.sql.sql => 008.schema.query.sql} (64%) diff --git a/Open-ILS/src/sql/Pg/008.schema.sql.sql b/Open-ILS/src/sql/Pg/008.schema.query.sql similarity index 64% rename from Open-ILS/src/sql/Pg/008.schema.sql.sql rename to Open-ILS/src/sql/Pg/008.schema.query.sql index b6a0dda0c8..f94db2bb7a 100644 --- a/Open-ILS/src/sql/Pg/008.schema.sql.sql +++ b/Open-ILS/src/sql/Pg/008.schema.query.sql @@ -1,17 +1,17 @@ --- Script to create the sql schema and the tables therein +-- Script to create the query schema and the tables therein BEGIN; -DROP SCHEMA IF EXISTS sql CASCADE; -CREATE SCHEMA sql; -COMMENT ON SCHEMA sql is $$ +DROP SCHEMA IF EXISTS query CASCADE; +CREATE SCHEMA query; +COMMENT ON SCHEMA actor IS $$ /* * Copyright (C) 2009 Equinox Software, Inc. / Georgia Public Library Service * Scott McKellar * - * Schema: sql + * Schema: query * - * Contains tables designed to represent SQL queries for use in + * Contains tables designed to represent user-defined queries for * reports and the like. * * **** @@ -26,38 +26,38 @@ COMMENT ON SCHEMA sql is $$ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */ -$$; -CREATE TABLE sql.stored_query ( + +CREATE TABLE query.stored_query ( id SERIAL PRIMARY KEY, type TEXT NOT NULL CONSTRAINT query_type CHECK ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ), use_all BOOLEAN NOT NULL DEFAULT FALSE, use_distinct BOOLEAN NOT NULL DEFAULT FALSE, - from_clause INT NOT NULL , --REFERENCES sql.from_clause - where_clause INT , --REFERENCES sql.expression + from_clause INT NOT NULL , --REFERENCES query.from_clause + where_clause INT , --REFERENCES query.expression --DEFERRABLE INITIALLY DEFERRED, - having_clause INT --REFERENCES sql.expression + having_clause INT --REFERENCES query.expression --DEFERRABLE INITIALLY DEFERRED ); -- (Foreign keys to be defined later after other tables are created) -CREATE TABLE sql.query_sequence ( +CREATE TABLE query.query_sequence ( id SERIAL PRIMARY KEY, parent_query INT NOT NULL - REFERENCES sql.stored_query + REFERENCES query.stored_query ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL, child_query INT NOT NULL - REFERENCES sql.stored_query + REFERENCES query.stored_query ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT sql_query_seq UNIQUE( parent_query, seq_no ) + CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no ) ); -CREATE TABLE sql.datatype ( +CREATE TABLE query.datatype ( id SERIAL PRIMARY KEY, datatype_name TEXT NOT NULL UNIQUE, is_numeric BOOL NOT NULL DEFAULT FALSE, @@ -66,50 +66,50 @@ CREATE TABLE sql.datatype ( ( is_numeric IS FALSE OR is_composite IS FALSE ) ); -CREATE TABLE sql.subfield ( +CREATE TABLE query.subfield ( id SERIAL PRIMARY KEY, composite_type INT NOT NULL - REFERENCES sql.datatype(id) + REFERENCES query.datatype(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL CONSTRAINT qsf_pos_seq_no CHECK( seq_no > 0 ), subfield_type INT NOT NULL - REFERENCES sql.datatype(id) + REFERENCES query.datatype(id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no) ); -CREATE TABLE sql.function_sig ( +CREATE TABLE query.function_sig ( id SERIAL PRIMARY KEY, function_name TEXT NOT NULL, - return_type INT REFERENCES sql.datatype(id) + return_type INT REFERENCES query.datatype(id) DEFERRABLE INITIALLY DEFERRED, is_aggregate BOOL NOT NULL DEFAULT FALSE, CONSTRAINT qfd_rtn_or_aggr CHECK ( return_type IS NULL OR is_aggregate = FALSE ) ); -CREATE INDEX sql_function_sig_name_idx - ON sql.function_sig (function_name); +CREATE INDEX query_function_sig_name_idx + ON query.function_sig (function_name); -CREATE TABLE sql.function_param_def ( +CREATE TABLE query.function_param_def ( id SERIAL PRIMARY KEY, function_id INT NOT NULL - REFERENCES sql.function_sig( id ) + REFERENCES query.function_sig( id ) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL CONSTRAINT qfpd_pos_seq_no CHECK ( seq_no > 0 ), datatype INT NOT NULL - REFERENCES sql.datatype( id ) + REFERENCES query.datatype( id ) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no) ); -CREATE TABLE sql.expression ( +CREATE TABLE query.expression ( id SERIAL PRIMARY KEY, type TEXT NOT NULL CONSTRAINT predicate_type CHECK ( type IN ( @@ -132,66 +132,66 @@ CREATE TABLE sql.expression ( 'xsubq' -- subquery ) ), parenthesize BOOL NOT NULL DEFAULT FALSE, - parent_expr INT REFERENCES sql.expression + parent_expr INT REFERENCES query.expression ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL DEFAULT 1, literal TEXT, table_alias TEXT, column_name TEXT, - left_operand INT REFERENCES sql.expression + left_operand INT REFERENCES query.expression DEFERRABLE INITIALLY DEFERRED, operator TEXT, - right_operand INT REFERENCES sql.expression + right_operand INT REFERENCES query.expression DEFERRABLE INITIALLY DEFERRED, - function_id INT REFERENCES sql.function_sig + function_id INT REFERENCES query.function_sig DEFERRABLE INITIALLY DEFERRED, - subquery INT REFERENCES sql.stored_query + subquery INT REFERENCES query.stored_query DEFERRABLE INITIALLY DEFERRED, - cast_type INT REFERENCES sql.datatype + cast_type INT REFERENCES query.datatype DEFERRABLE INITIALLY DEFERRED ); -CREATE UNIQUE INDEX sql_expr_parent_seq - ON sql.expression( parent_expr, seq_no ) +CREATE UNIQUE INDEX query_expr_parent_seq + ON query.expression( parent_expr, seq_no ) WHERE parent_expr IS NOT NULL; -- Due to some circular references, the following foreign key definitions --- had to be deferred until sql.expression existed: +-- had to be deferred until query.expression existed: -ALTER TABLE sql.stored_query +ALTER TABLE query.stored_query ADD FOREIGN KEY ( where_clause ) - REFERENCES sql.expression( id ) + REFERENCES query.expression( id ) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE sql.stored_query +ALTER TABLE query.stored_query ADD FOREIGN KEY ( having_clause ) - REFERENCES sql.expression( id ) + REFERENCES query.expression( id ) DEFERRABLE INITIALLY DEFERRED; -CREATE TABLE sql.case_branch ( +CREATE TABLE query.case_branch ( id SERIAL PRIMARY KEY, - parent_expr INT NOT NULL REFERENCES sql.expression + parent_expr INT NOT NULL REFERENCES query.expression ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL, - condition INT REFERENCES sql.expression + condition INT REFERENCES query.expression DEFERRABLE INITIALLY DEFERRED, - result INT NOT NULL REFERENCES sql.expression + result INT NOT NULL REFERENCES query.expression DEFERRABLE INITIALLY DEFERRED, CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no) ); -CREATE TABLE sql.from_relation ( +CREATE TABLE query.from_relation ( id SERIAL PRIMARY KEY, type TEXT NOT NULL CONSTRAINT relation_type CHECK ( type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ), table_name TEXT, class_name TEXT, - subquery INT REFERENCES sql.stored_query, - function_call INT REFERENCES sql.expression, + subquery INT REFERENCES query.stored_query, + function_call INT REFERENCES query.expression, table_alias TEXT NOT NULL, - parent_relation INT REFERENCES sql.from_relation + parent_relation INT REFERENCES query.from_relation ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL DEFAULT 1, @@ -199,7 +199,7 @@ CREATE TABLE sql.from_relation ( join_type IS NULL OR join_type IN ( 'INNER', 'LEFT', 'RIGHT', 'FULL' ) ), - on_clause INT REFERENCES sql.expression + on_clause INT REFERENCES query.expression DEFERRABLE INITIALLY DEFERRED, CONSTRAINT join_or_core CHECK ( ( parent_relation IS NULL AND join_type IS NULL @@ -211,20 +211,20 @@ CREATE TABLE sql.from_relation ( ); CREATE UNIQUE INDEX from_parent_seq - ON sql.from_relation( parent_relation, seq_no ) + ON query.from_relation( parent_relation, seq_no ) WHERE parent_relation IS NOT NULL; -- The following foreign key had to be deferred until --- sql.from_relation existed +-- query.from_relation existed -ALTER TABLE sql.stored_query +ALTER TABLE query.stored_query ADD FOREIGN KEY (from_clause) - REFERENCES sql.from_relation + REFERENCES query.from_relation DEFERRABLE INITIALLY DEFERRED; -CREATE TABLE sql.record_column ( +CREATE TABLE query.record_column ( id SERIAL PRIMARY KEY, - from_relation INT NOT NULL REFERENCES sql.from_relation + from_relation INT NOT NULL REFERENCES query.from_relation ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL, @@ -233,29 +233,79 @@ CREATE TABLE sql.record_column ( CONSTRAINT column_sequence UNIQUE (from_relation, seq_no) ); -CREATE TABLE sql.select_item ( +CREATE TABLE query.select_item ( id SERIAL PRIMARY KEY, - stored_query INT NOT NULL REFERENCES sql.stored_query + stored_query INT NOT NULL REFERENCES query.stored_query ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL, - expression INT NOT NULL REFERENCES sql.expression + expression INT NOT NULL REFERENCES query.expression DEFERRABLE INITIALLY DEFERRED, column_alias TEXT, grouped_by BOOL NOT NULL DEFAULT FALSE, CONSTRAINT select_sequence UNIQUE( stored_query, seq_no ) ); -CREATE TABLE sql.order_by_item ( +CREATE TABLE query.order_by_item ( id SERIAL PRIMARY KEY, - stored_query INT NOT NULL REFERENCES sql.stored_query + stored_query INT NOT NULL REFERENCES query.stored_query ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL, - expression INT NOT NULL REFERENCES sql.expression + expression INT NOT NULL REFERENCES query.expression ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no ) ); +-- Create updatable views + +CREATE OR REPLACE VIEW query.expr_string 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_string + 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_string + 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_string + DO INSTEAD + DELETE FROM query.expression WHERE id = OLD.id; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0097.schema.sql-datatype.sql b/Open-ILS/src/sql/Pg/upgrade/0097.schema.sql-datatype.sql index cf82e1a470..03b30e0536 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0097.schema.sql-datatype.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0097.schema.sql-datatype.sql @@ -1,14 +1,15 @@ --- Script to create the sql schema and the tables therein +-- Script to create the query schema and the tables therein BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0097'); -- Scott McKellar DROP SCHEMA IF EXISTS sql CASCADE; +DROP SCHEMA IF EXISTS query CASCADE; -CREATE SCHEMA sql; +CREATE SCHEMA query; -CREATE TABLE sql.datatype ( +CREATE TABLE query.datatype ( id SERIAL PRIMARY KEY, datatype_name TEXT NOT NULL UNIQUE, is_numeric BOOL NOT NULL DEFAULT FALSE, @@ -17,45 +18,45 @@ CREATE TABLE sql.datatype ( ( is_numeric IS FALSE OR is_composite IS FALSE ) ); -CREATE TABLE sql.subfield ( +CREATE TABLE query.subfield ( id SERIAL PRIMARY KEY, composite_type INT NOT NULL - REFERENCES sql.datatype(id) + REFERENCES query.datatype(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL CONSTRAINT qsf_pos_seq_no CHECK( seq_no > 0 ), subfield_type INT NOT NULL - REFERENCES sql.datatype(id) + REFERENCES query.datatype(id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no) ); -CREATE TABLE sql.function_sig ( +CREATE TABLE query.function_sig ( id SERIAL PRIMARY KEY, function_name TEXT NOT NULL, - return_type INT REFERENCES sql.datatype(id) + return_type INT REFERENCES query.datatype(id) DEFERRABLE INITIALLY DEFERRED, is_aggregate BOOL NOT NULL DEFAULT FALSE, CONSTRAINT qfd_rtn_or_aggr CHECK ( return_type IS NULL OR is_aggregate = FALSE ) ); -CREATE INDEX sql_function_sig_name_idx - ON sql.function_sig (function_name); +CREATE INDEX query_function_sig_name_idx + ON query.function_sig (function_name); -CREATE TABLE sql.function_param_def ( +CREATE TABLE query.function_param_def ( id SERIAL PRIMARY KEY, function_id INT NOT NULL - REFERENCES sql.function_sig( id ) + REFERENCES query.function_sig( id ) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL CONSTRAINT qfpd_pos_seq_no CHECK ( seq_no > 0 ), datatype INT NOT NULL - REFERENCES sql.datatype( id ) + REFERENCES query.datatype( id ) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no) ); diff --git a/Open-ILS/src/sql/Pg/upgrade/0098.schema.sql-stored-query.sql b/Open-ILS/src/sql/Pg/upgrade/0098.schema.sql-stored-query.sql index 1134f31982..577a3ff87d 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0098.schema.sql-stored-query.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0098.schema.sql-stored-query.sql @@ -2,33 +2,33 @@ BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0098'); -- Scott McKellar -CREATE TABLE sql.stored_query ( +CREATE TABLE query.stored_query ( id SERIAL PRIMARY KEY, type TEXT NOT NULL CONSTRAINT query_type CHECK ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ), use_all BOOLEAN NOT NULL DEFAULT FALSE, use_distinct BOOLEAN NOT NULL DEFAULT FALSE, - from_clause INT NOT NULL , --REFERENCES sql.from_clause - where_clause INT , --REFERENCES sql.expression + from_clause INT NOT NULL , --REFERENCES query.from_clause + where_clause INT , --REFERENCES query.expression --DEFERRABLE INITIALLY DEFERRED, - having_clause INT --REFERENCES sql.expression + having_clause INT --REFERENCES query.expression --DEFERRABLE INITIALLY DEFERRED ); -- (Foreign keys to be defined later after other tables are created) -CREATE TABLE sql.query_sequence ( +CREATE TABLE query.query_sequence ( id SERIAL PRIMARY KEY, parent_query INT NOT NULL - REFERENCES sql.stored_query + REFERENCES query.stored_query ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, seq_no INT NOT NULL, child_query INT NOT NULL - REFERENCES sql.stored_query + REFERENCES query.stored_query ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT sql_query_seq UNIQUE( parent_query, seq_no ) + CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no ) ); COMMIT; -- 2.43.2