1 -- Script to create the query schema and the tables therein
5 DROP SCHEMA IF EXISTS query CASCADE;
7 COMMENT ON SCHEMA actor IS $$
9 * Copyright (C) 2009 Equinox Software, Inc. / Georgia Public Library Service
10 * Scott McKellar <scott@esilibrary.com>
14 * Contains tables designed to represent user-defined queries for
15 * reports and the like.
19 * This program is free software; you can redistribute it and/or
20 * modify it under the terms of the GNU General Public License
21 * as published by the Free Software Foundation; either version 2
22 * of the License, or (at your option) any later version.
24 * This program is distributed in the hope that it will be useful,
25 * but WITHOUT ANY WARRANTY; without even the implied warranty of
26 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
27 * GNU General Public License for more details.
31 CREATE TABLE query.stored_query (
32 id SERIAL PRIMARY KEY,
33 type TEXT NOT NULL CONSTRAINT query_type CHECK
34 ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
35 use_all BOOLEAN NOT NULL DEFAULT FALSE,
36 use_distinct BOOLEAN NOT NULL DEFAULT FALSE,
37 from_clause INT, --REFERENCES query.from_clause
38 --DEFERRABLE INITIALLY DEFERRED,
39 where_clause INT, --REFERENCES query.expression
40 --DEFERRABLE INITIALLY DEFERRED,
41 having_clause INT --REFERENCES query.expression
42 --DEFERRABLE INITIALLY DEFERRED
45 -- (Foreign keys to be defined later after other tables are created)
47 CREATE TABLE query.query_sequence (
48 id SERIAL PRIMARY KEY,
49 parent_query INT NOT NULL
50 REFERENCES query.stored_query
52 DEFERRABLE INITIALLY DEFERRED,
54 child_query INT NOT NULL
55 REFERENCES query.stored_query
57 DEFERRABLE INITIALLY DEFERRED,
58 CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
61 CREATE TABLE query.datatype (
62 id SERIAL PRIMARY KEY,
63 datatype_name TEXT NOT NULL UNIQUE,
64 is_numeric BOOL NOT NULL DEFAULT FALSE,
65 is_composite BOOL NOT NULL DEFAULT FALSE,
66 CONSTRAINT qdt_comp_not_num CHECK
67 ( is_numeric IS FALSE OR is_composite IS FALSE )
70 CREATE TABLE query.subfield (
71 id SERIAL PRIMARY KEY,
72 composite_type INT NOT NULL
73 REFERENCES query.datatype(id)
75 DEFERRABLE INITIALLY DEFERRED,
77 CONSTRAINT qsf_pos_seq_no
79 subfield_type INT NOT NULL
80 REFERENCES query.datatype(id)
81 DEFERRABLE INITIALLY DEFERRED,
82 CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
85 CREATE TABLE query.function_sig (
86 id SERIAL PRIMARY KEY,
87 function_name TEXT NOT NULL,
88 return_type INT REFERENCES query.datatype(id)
89 DEFERRABLE INITIALLY DEFERRED,
90 is_aggregate BOOL NOT NULL DEFAULT FALSE,
91 CONSTRAINT qfd_rtn_or_aggr CHECK
92 ( return_type IS NULL OR is_aggregate = FALSE )
95 CREATE INDEX query_function_sig_name_idx
96 ON query.function_sig (function_name);
98 CREATE TABLE query.function_param_def (
99 id SERIAL PRIMARY KEY,
100 function_id INT NOT NULL
101 REFERENCES query.function_sig( id )
103 DEFERRABLE INITIALLY DEFERRED,
105 CONSTRAINT qfpd_pos_seq_no CHECK
107 datatype INT NOT NULL
108 REFERENCES query.datatype( id )
109 DEFERRABLE INITIALLY DEFERRED,
110 CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
113 CREATE TABLE query.bind_variable (
114 name TEXT PRIMARY KEY,
116 CONSTRAINT bind_variable_type CHECK
117 ( type in ( 'string', 'number', 'string_list', 'number_list' )),
118 description TEXT NOT NULL,
119 default_value TEXT -- to be encoded in JSON
122 CREATE TABLE query.expression (
123 id SERIAL PRIMARY KEY,
124 type TEXT NOT NULL CONSTRAINT expression_type CHECK
127 'xbind', -- bind variable
142 parenthesize BOOL NOT NULL DEFAULT FALSE,
143 parent_expr INT REFERENCES query.expression
145 DEFERRABLE INITIALLY DEFERRED,
146 seq_no INT NOT NULL DEFAULT 1,
150 left_operand INT REFERENCES query.expression
151 DEFERRABLE INITIALLY DEFERRED,
153 right_operand INT REFERENCES query.expression
154 DEFERRABLE INITIALLY DEFERRED,
155 function_id INT REFERENCES query.function_sig
156 DEFERRABLE INITIALLY DEFERRED,
157 subquery INT REFERENCES query.stored_query
158 DEFERRABLE INITIALLY DEFERRED,
159 cast_type INT REFERENCES query.datatype
160 DEFERRABLE INITIALLY DEFERRED,
161 negate BOOL NOT NULL DEFAULT FALSE,
162 bind_variable TEXT REFERENCES query.bind_variable
163 DEFERRABLE INITIALLY DEFERRED
166 CREATE UNIQUE INDEX query_expr_parent_seq
167 ON query.expression( parent_expr, seq_no )
168 WHERE parent_expr IS NOT NULL;
170 -- Due to some circular references, the following foreign key definitions
171 -- had to be deferred until query.expression existed:
173 ALTER TABLE query.stored_query
174 ADD FOREIGN KEY ( where_clause )
175 REFERENCES query.expression( id )
176 DEFERRABLE INITIALLY DEFERRED;
178 ALTER TABLE query.stored_query
179 ADD FOREIGN KEY ( having_clause )
180 REFERENCES query.expression( id )
181 DEFERRABLE INITIALLY DEFERRED;
183 CREATE TABLE query.case_branch (
184 id SERIAL PRIMARY KEY,
185 parent_expr INT NOT NULL REFERENCES query.expression
187 DEFERRABLE INITIALLY DEFERRED,
189 condition INT REFERENCES query.expression
190 DEFERRABLE INITIALLY DEFERRED,
191 result INT NOT NULL REFERENCES query.expression
192 DEFERRABLE INITIALLY DEFERRED,
193 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
196 CREATE TABLE query.from_relation (
197 id SERIAL PRIMARY KEY,
198 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
199 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
202 subquery INT REFERENCES query.stored_query,
203 function_call INT REFERENCES query.expression,
205 parent_relation INT REFERENCES query.from_relation
207 DEFERRABLE INITIALLY DEFERRED,
208 seq_no INT NOT NULL DEFAULT 1,
209 join_type TEXT CONSTRAINT good_join_type CHECK (
210 join_type IS NULL OR join_type IN
211 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
213 on_clause INT REFERENCES query.expression
214 DEFERRABLE INITIALLY DEFERRED,
215 CONSTRAINT join_or_core CHECK (
216 ( parent_relation IS NULL AND join_type IS NULL
217 AND on_clause IS NULL )
219 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
220 AND on_clause IS NOT NULL )
224 CREATE UNIQUE INDEX from_parent_seq
225 ON query.from_relation( parent_relation, seq_no )
226 WHERE parent_relation IS NOT NULL;
228 -- The following foreign key had to be deferred until
229 -- query.from_relation existed
231 ALTER TABLE query.stored_query
232 ADD FOREIGN KEY (from_clause)
233 REFERENCES query.from_relation
234 DEFERRABLE INITIALLY DEFERRED;
236 CREATE TABLE query.record_column (
237 id SERIAL PRIMARY KEY,
238 from_relation INT NOT NULL REFERENCES query.from_relation
240 DEFERRABLE INITIALLY DEFERRED,
242 column_name TEXT NOT NULL,
243 column_type INT NOT NULL REFERENCES query.datatype
245 DEFERRABLE INITIALLY DEFERRED,
246 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
249 CREATE TABLE query.select_item (
250 id SERIAL PRIMARY KEY,
251 stored_query INT NOT NULL REFERENCES query.stored_query
253 DEFERRABLE INITIALLY DEFERRED,
255 expression INT NOT NULL REFERENCES query.expression
256 DEFERRABLE INITIALLY DEFERRED,
258 grouped_by BOOL NOT NULL DEFAULT FALSE,
259 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
262 CREATE TABLE query.order_by_item (
263 id SERIAL PRIMARY KEY,
264 stored_query INT NOT NULL REFERENCES query.stored_query
266 DEFERRABLE INITIALLY DEFERRED,
268 expression INT NOT NULL REFERENCES query.expression
270 DEFERRABLE INITIALLY DEFERRED,
271 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
274 -- Create updatable views -------------------------------------------
276 -- Create updatable view for BETWEEN expressions
278 CREATE OR REPLACE VIEW query.expr_xbet AS
290 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
291 ON INSERT TO query.expr_xbet
293 INSERT INTO query.expression (
301 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
303 COALESCE(NEW.parenthesize, FALSE),
305 COALESCE(NEW.seq_no, 1),
309 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
310 ON UPDATE TO query.expr_xbet
312 UPDATE query.expression SET
314 parenthesize = NEW.parenthesize,
315 parent_expr = NEW.parent_expr,
321 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
322 ON DELETE TO query.expr_xbet
324 DELETE FROM query.expression WHERE id = OLD.id;
326 -- Create updatable view for boolean expressions
328 CREATE OR REPLACE VIEW query.expr_xbool AS
341 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
342 ON INSERT TO query.expr_xbool
344 INSERT INTO query.expression (
353 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
355 COALESCE(NEW.parenthesize, FALSE),
357 COALESCE(NEW.seq_no, 1),
362 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
363 ON UPDATE TO query.expr_xbool
365 UPDATE query.expression SET
367 parenthesize = NEW.parenthesize,
368 parent_expr = NEW.parent_expr,
370 literal = NEW.literal,
375 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
376 ON DELETE TO query.expr_xbool
378 DELETE FROM query.expression WHERE id = OLD.id;
380 -- Create updatable view for CASE expressions
382 CREATE OR REPLACE VIEW query.expr_xcase AS
394 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
395 ON INSERT TO query.expr_xcase
397 INSERT INTO query.expression (
405 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
407 COALESCE(NEW.parenthesize, FALSE),
409 COALESCE(NEW.seq_no, 1),
413 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
414 ON UPDATE TO query.expr_xcase
416 UPDATE query.expression SET
418 parenthesize = NEW.parenthesize,
419 parent_expr = NEW.parent_expr,
425 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
426 ON DELETE TO query.expr_xcase
428 DELETE FROM query.expression WHERE id = OLD.id;
430 -- Create updatable view for cast expressions
432 CREATE OR REPLACE VIEW query.expr_xcast AS
446 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
447 ON INSERT TO query.expr_xcast
449 INSERT INTO query.expression (
459 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
461 COALESCE(NEW.parenthesize, FALSE),
463 COALESCE(NEW.seq_no, 1),
469 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
470 ON UPDATE TO query.expr_xcast
472 UPDATE query.expression SET
474 parenthesize = NEW.parenthesize,
475 parent_expr = NEW.parent_expr,
477 left_operand = NEW.left_operand,
478 cast_type = NEW.cast_type,
483 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
484 ON DELETE TO query.expr_xcast
486 DELETE FROM query.expression WHERE id = OLD.id;
488 -- Create updatable view for column expressions
490 CREATE OR REPLACE VIEW query.expr_xcol AS
504 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
505 ON INSERT TO query.expr_xcol
507 INSERT INTO query.expression (
517 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
519 COALESCE(NEW.parenthesize, FALSE),
521 COALESCE(NEW.seq_no, 1),
527 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
528 ON UPDATE TO query.expr_xcol
530 UPDATE query.expression SET
532 parenthesize = NEW.parenthesize,
533 parent_expr = NEW.parent_expr,
535 table_alias = NEW.table_alias,
536 column_name = NEW.column_name,
541 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
542 ON DELETE TO query.expr_xcol
544 DELETE FROM query.expression WHERE id = OLD.id;
546 -- Create updatable view for EXISTS expressions
548 CREATE OR REPLACE VIEW query.expr_xex AS
561 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
562 ON INSERT TO query.expr_xex
564 INSERT INTO query.expression (
573 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
575 COALESCE(NEW.parenthesize, FALSE),
577 COALESCE(NEW.seq_no, 1),
582 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
583 ON UPDATE TO query.expr_xex
585 UPDATE query.expression SET
587 parenthesize = NEW.parenthesize,
588 parent_expr = NEW.parent_expr,
590 subquery = NEW.subquery,
595 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
596 ON DELETE TO query.expr_xex
598 DELETE FROM query.expression WHERE id = OLD.id;
600 -- Create updatable view for field expressions
602 CREATE OR REPLACE VIEW query.expr_xfld AS
616 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
617 ON INSERT TO query.expr_xfld
619 INSERT INTO query.expression (
629 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
631 COALESCE(NEW.parenthesize, FALSE),
633 COALESCE(NEW.seq_no, 1),
639 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
640 ON UPDATE TO query.expr_xfld
642 UPDATE query.expression SET
644 parenthesize = NEW.parenthesize,
645 parent_expr = NEW.parent_expr,
647 column_name = NEW.column_name,
648 left_operand = NEW.left_operand,
653 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
654 ON DELETE TO query.expr_xfld
656 DELETE FROM query.expression WHERE id = OLD.id;
658 -- Create updatable view for function call expressions
660 CREATE OR REPLACE VIEW query.expr_xfunc AS
673 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
674 ON INSERT TO query.expr_xfunc
676 INSERT INTO query.expression (
685 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
687 COALESCE(NEW.parenthesize, FALSE),
689 COALESCE(NEW.seq_no, 1),
694 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
695 ON UPDATE TO query.expr_xfunc
697 UPDATE query.expression SET
699 parenthesize = NEW.parenthesize,
700 parent_expr = NEW.parent_expr,
702 function_id = NEW.function_id,
707 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
708 ON DELETE TO query.expr_xfunc
710 DELETE FROM query.expression WHERE id = OLD.id;
712 -- Create updatable view for IN expressions
714 CREATE OR REPLACE VIEW query.expr_xin AS
728 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
729 ON INSERT TO query.expr_xin
731 INSERT INTO query.expression (
741 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
743 COALESCE(NEW.parenthesize, FALSE),
745 COALESCE(NEW.seq_no, 1),
751 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
752 ON UPDATE TO query.expr_xin
754 UPDATE query.expression SET
756 parenthesize = NEW.parenthesize,
757 parent_expr = NEW.parent_expr,
759 left_operand = NEW.left_operand,
760 subquery = NEW.subquery,
765 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
766 ON DELETE TO query.expr_xin
768 DELETE FROM query.expression WHERE id = OLD.id;
770 -- Create updatable view for NULL expressions
772 CREATE OR REPLACE VIEW query.expr_xnull AS
784 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
785 ON INSERT TO query.expr_xnull
787 INSERT INTO query.expression (
795 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
797 COALESCE(NEW.parenthesize, FALSE),
799 COALESCE(NEW.seq_no, 1),
803 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
804 ON UPDATE TO query.expr_xnull
806 UPDATE query.expression SET
808 parenthesize = NEW.parenthesize,
809 parent_expr = NEW.parent_expr,
815 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
816 ON DELETE TO query.expr_xnull
818 DELETE FROM query.expression WHERE id = OLD.id;
820 -- Create updatable view for numeric literal expressions
822 CREATE OR REPLACE VIEW query.expr_xnum AS
834 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
835 ON INSERT TO query.expr_xnum
837 INSERT INTO query.expression (
845 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
847 COALESCE(NEW.parenthesize, FALSE),
849 COALESCE(NEW.seq_no, 1),
853 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
854 ON UPDATE TO query.expr_xnum
856 UPDATE query.expression SET
858 parenthesize = NEW.parenthesize,
859 parent_expr = NEW.parent_expr,
861 literal = NEW.literal
865 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
866 ON DELETE TO query.expr_xnum
868 DELETE FROM query.expression WHERE id = OLD.id;
870 -- Create updatable view for operator expressions
872 CREATE OR REPLACE VIEW query.expr_xop AS
887 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
888 ON INSERT TO query.expr_xop
890 INSERT INTO query.expression (
901 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
903 COALESCE(NEW.parenthesize, FALSE),
905 COALESCE(NEW.seq_no, 1),
912 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
913 ON UPDATE TO query.expr_xop
915 UPDATE query.expression SET
917 parenthesize = NEW.parenthesize,
918 parent_expr = NEW.parent_expr,
920 left_operand = NEW.left_operand,
921 operator = NEW.operator,
922 right_operand = NEW.right_operand,
927 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
928 ON DELETE TO query.expr_xop
930 DELETE FROM query.expression WHERE id = OLD.id;
932 -- Create updatable view for string literal expressions
934 CREATE OR REPLACE VIEW query.expr_xstr AS
946 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
947 ON INSERT TO query.expr_xstr
949 INSERT INTO query.expression (
957 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
959 COALESCE(NEW.parenthesize, FALSE),
961 COALESCE(NEW.seq_no, 1),
965 CREATE OR REPLACE RULE query_expr_string_update_rule AS
966 ON UPDATE TO query.expr_xstr
968 UPDATE query.expression SET
970 parenthesize = NEW.parenthesize,
971 parent_expr = NEW.parent_expr,
973 literal = NEW.literal
977 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
978 ON DELETE TO query.expr_xstr
980 DELETE FROM query.expression WHERE id = OLD.id;
982 -- Create updatable view for subquery expressions
984 CREATE OR REPLACE VIEW query.expr_xsubq AS
997 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
998 ON INSERT TO query.expr_xsubq
1000 INSERT INTO query.expression (
1009 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1011 COALESCE(NEW.parenthesize, FALSE),
1013 COALESCE(NEW.seq_no, 1),
1018 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1019 ON UPDATE TO query.expr_xsubq
1021 UPDATE query.expression SET
1023 parenthesize = NEW.parenthesize,
1024 parent_expr = NEW.parent_expr,
1025 seq_no = NEW.seq_no,
1026 subquery = NEW.subquery,
1031 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1032 ON DELETE TO query.expr_xsubq
1034 DELETE FROM query.expression WHERE id = OLD.id;