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.expression (
114 id SERIAL PRIMARY KEY,
115 type TEXT NOT NULL CONSTRAINT predicate_type CHECK
132 parenthesize BOOL NOT NULL DEFAULT FALSE,
133 parent_expr INT REFERENCES query.expression
135 DEFERRABLE INITIALLY DEFERRED,
136 seq_no INT NOT NULL DEFAULT 1,
140 left_operand INT REFERENCES query.expression
141 DEFERRABLE INITIALLY DEFERRED,
143 right_operand INT REFERENCES query.expression
144 DEFERRABLE INITIALLY DEFERRED,
145 function_id INT REFERENCES query.function_sig
146 DEFERRABLE INITIALLY DEFERRED,
147 subquery INT REFERENCES query.stored_query
148 DEFERRABLE INITIALLY DEFERRED,
149 cast_type INT REFERENCES query.datatype
150 DEFERRABLE INITIALLY DEFERRED,
151 negate BOOL NOT NULL DEFAULT FALSE
154 CREATE UNIQUE INDEX query_expr_parent_seq
155 ON query.expression( parent_expr, seq_no )
156 WHERE parent_expr IS NOT NULL;
158 -- Due to some circular references, the following foreign key definitions
159 -- had to be deferred until query.expression existed:
161 ALTER TABLE query.stored_query
162 ADD FOREIGN KEY ( where_clause )
163 REFERENCES query.expression( id )
164 DEFERRABLE INITIALLY DEFERRED;
166 ALTER TABLE query.stored_query
167 ADD FOREIGN KEY ( having_clause )
168 REFERENCES query.expression( id )
169 DEFERRABLE INITIALLY DEFERRED;
171 CREATE TABLE query.case_branch (
172 id SERIAL PRIMARY KEY,
173 parent_expr INT NOT NULL REFERENCES query.expression
175 DEFERRABLE INITIALLY DEFERRED,
177 condition INT REFERENCES query.expression
178 DEFERRABLE INITIALLY DEFERRED,
179 result INT NOT NULL REFERENCES query.expression
180 DEFERRABLE INITIALLY DEFERRED,
181 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
184 CREATE TABLE query.from_relation (
185 id SERIAL PRIMARY KEY,
186 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
187 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
190 subquery INT REFERENCES query.stored_query,
191 function_call INT REFERENCES query.expression,
193 parent_relation INT REFERENCES query.from_relation
195 DEFERRABLE INITIALLY DEFERRED,
196 seq_no INT NOT NULL DEFAULT 1,
197 join_type TEXT CONSTRAINT good_join_type CHECK (
198 join_type IS NULL OR join_type IN
199 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
201 on_clause INT REFERENCES query.expression
202 DEFERRABLE INITIALLY DEFERRED,
203 CONSTRAINT join_or_core CHECK (
204 ( parent_relation IS NULL AND join_type IS NULL
205 AND on_clause IS NULL )
207 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
208 AND on_clause IS NOT NULL )
212 CREATE UNIQUE INDEX from_parent_seq
213 ON query.from_relation( parent_relation, seq_no )
214 WHERE parent_relation IS NOT NULL;
216 -- The following foreign key had to be deferred until
217 -- query.from_relation existed
219 ALTER TABLE query.stored_query
220 ADD FOREIGN KEY (from_clause)
221 REFERENCES query.from_relation
222 DEFERRABLE INITIALLY DEFERRED;
224 CREATE TABLE query.record_column (
225 id SERIAL PRIMARY KEY,
226 from_relation INT NOT NULL REFERENCES query.from_relation
228 DEFERRABLE INITIALLY DEFERRED,
230 column_name TEXT NOT NULL,
231 column_type INT NOT NULL REFERENCES query.datatype
233 DEFERRABLE INITIALLY DEFERRED,
234 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
237 CREATE TABLE query.select_item (
238 id SERIAL PRIMARY KEY,
239 stored_query INT NOT NULL REFERENCES query.stored_query
241 DEFERRABLE INITIALLY DEFERRED,
243 expression INT NOT NULL REFERENCES query.expression
244 DEFERRABLE INITIALLY DEFERRED,
246 grouped_by BOOL NOT NULL DEFAULT FALSE,
247 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
250 CREATE TABLE query.order_by_item (
251 id SERIAL PRIMARY KEY,
252 stored_query INT NOT NULL REFERENCES query.stored_query
254 DEFERRABLE INITIALLY DEFERRED,
256 expression INT NOT NULL REFERENCES query.expression
258 DEFERRABLE INITIALLY DEFERRED,
259 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
262 -- Create updatable views -------------------------------------------
264 -- Create updatable view for BETWEEN expressions
266 CREATE OR REPLACE VIEW query.expr_xbet AS
278 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
279 ON INSERT TO query.expr_xbet
281 INSERT INTO query.expression (
289 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
291 COALESCE(NEW.parenthesize, FALSE),
293 COALESCE(NEW.seq_no, 1),
297 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
298 ON UPDATE TO query.expr_xbet
300 UPDATE query.expression SET
302 parenthesize = NEW.parenthesize,
303 parent_expr = NEW.parent_expr,
309 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
310 ON DELETE TO query.expr_xbet
312 DELETE FROM query.expression WHERE id = OLD.id;
314 -- Create updatable view for boolean expressions
316 CREATE OR REPLACE VIEW query.expr_xbool AS
329 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
330 ON INSERT TO query.expr_xbool
332 INSERT INTO query.expression (
341 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
343 COALESCE(NEW.parenthesize, FALSE),
345 COALESCE(NEW.seq_no, 1),
350 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
351 ON UPDATE TO query.expr_xbool
353 UPDATE query.expression SET
355 parenthesize = NEW.parenthesize,
356 parent_expr = NEW.parent_expr,
358 literal = NEW.literal,
363 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
364 ON DELETE TO query.expr_xbool
366 DELETE FROM query.expression WHERE id = OLD.id;
368 -- Create updatable view for CASE expressions
370 CREATE OR REPLACE VIEW query.expr_xcase AS
382 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
383 ON INSERT TO query.expr_xcase
385 INSERT INTO query.expression (
393 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
395 COALESCE(NEW.parenthesize, FALSE),
397 COALESCE(NEW.seq_no, 1),
401 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
402 ON UPDATE TO query.expr_xcase
404 UPDATE query.expression SET
406 parenthesize = NEW.parenthesize,
407 parent_expr = NEW.parent_expr,
413 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
414 ON DELETE TO query.expr_xcase
416 DELETE FROM query.expression WHERE id = OLD.id;
418 -- Create updatable view for cast expressions
420 CREATE OR REPLACE VIEW query.expr_xcast AS
434 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
435 ON INSERT TO query.expr_xcast
437 INSERT INTO query.expression (
447 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
449 COALESCE(NEW.parenthesize, FALSE),
451 COALESCE(NEW.seq_no, 1),
457 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
458 ON UPDATE TO query.expr_xcast
460 UPDATE query.expression SET
462 parenthesize = NEW.parenthesize,
463 parent_expr = NEW.parent_expr,
465 left_operand = NEW.left_operand,
466 cast_type = NEW.cast_type,
471 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
472 ON DELETE TO query.expr_xcast
474 DELETE FROM query.expression WHERE id = OLD.id;
476 -- Create updatable view for column expressions
478 CREATE OR REPLACE VIEW query.expr_xcol AS
492 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
493 ON INSERT TO query.expr_xcol
495 INSERT INTO query.expression (
505 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
507 COALESCE(NEW.parenthesize, FALSE),
509 COALESCE(NEW.seq_no, 1),
515 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
516 ON UPDATE TO query.expr_xcol
518 UPDATE query.expression SET
520 parenthesize = NEW.parenthesize,
521 parent_expr = NEW.parent_expr,
523 table_alias = NEW.table_alias,
524 column_name = NEW.column_name,
529 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
530 ON DELETE TO query.expr_xcol
532 DELETE FROM query.expression WHERE id = OLD.id;
534 -- Create updatable view for EXISTS expressions
536 CREATE OR REPLACE VIEW query.expr_xex AS
549 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
550 ON INSERT TO query.expr_xex
552 INSERT INTO query.expression (
561 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
563 COALESCE(NEW.parenthesize, FALSE),
565 COALESCE(NEW.seq_no, 1),
570 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
571 ON UPDATE TO query.expr_xex
573 UPDATE query.expression SET
575 parenthesize = NEW.parenthesize,
576 parent_expr = NEW.parent_expr,
578 subquery = NEW.subquery,
583 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
584 ON DELETE TO query.expr_xex
586 DELETE FROM query.expression WHERE id = OLD.id;
588 -- Create updatable view for field expressions
590 CREATE OR REPLACE VIEW query.expr_xfld AS
604 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
605 ON INSERT TO query.expr_xfld
607 INSERT INTO query.expression (
617 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
619 COALESCE(NEW.parenthesize, FALSE),
621 COALESCE(NEW.seq_no, 1),
627 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
628 ON UPDATE TO query.expr_xfld
630 UPDATE query.expression SET
632 parenthesize = NEW.parenthesize,
633 parent_expr = NEW.parent_expr,
635 column_name = NEW.column_name,
636 left_operand = NEW.left_operand,
641 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
642 ON DELETE TO query.expr_xfld
644 DELETE FROM query.expression WHERE id = OLD.id;
646 -- Create updatable view for function call expressions
648 CREATE OR REPLACE VIEW query.expr_xfunc AS
661 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
662 ON INSERT TO query.expr_xfunc
664 INSERT INTO query.expression (
673 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
675 COALESCE(NEW.parenthesize, FALSE),
677 COALESCE(NEW.seq_no, 1),
682 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
683 ON UPDATE TO query.expr_xfunc
685 UPDATE query.expression SET
687 parenthesize = NEW.parenthesize,
688 parent_expr = NEW.parent_expr,
690 function_id = NEW.function_id,
695 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
696 ON DELETE TO query.expr_xfunc
698 DELETE FROM query.expression WHERE id = OLD.id;
700 -- Create updatable view for IN expressions
702 CREATE OR REPLACE VIEW query.expr_xin AS
716 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
717 ON INSERT TO query.expr_xin
719 INSERT INTO query.expression (
729 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
731 COALESCE(NEW.parenthesize, FALSE),
733 COALESCE(NEW.seq_no, 1),
739 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
740 ON UPDATE TO query.expr_xin
742 UPDATE query.expression SET
744 parenthesize = NEW.parenthesize,
745 parent_expr = NEW.parent_expr,
747 left_operand = NEW.left_operand,
748 subquery = NEW.subquery,
753 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
754 ON DELETE TO query.expr_xin
756 DELETE FROM query.expression WHERE id = OLD.id;
758 -- Create updatable view for NULL expressions
760 CREATE OR REPLACE VIEW query.expr_xnull AS
772 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
773 ON INSERT TO query.expr_xnull
775 INSERT INTO query.expression (
783 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
785 COALESCE(NEW.parenthesize, FALSE),
787 COALESCE(NEW.seq_no, 1),
791 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
792 ON UPDATE TO query.expr_xnull
794 UPDATE query.expression SET
796 parenthesize = NEW.parenthesize,
797 parent_expr = NEW.parent_expr,
803 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
804 ON DELETE TO query.expr_xnull
806 DELETE FROM query.expression WHERE id = OLD.id;
808 -- Create updatable view for numeric literal expressions
810 CREATE OR REPLACE VIEW query.expr_xnum AS
822 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
823 ON INSERT TO query.expr_xnum
825 INSERT INTO query.expression (
833 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
835 COALESCE(NEW.parenthesize, FALSE),
837 COALESCE(NEW.seq_no, 1),
841 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
842 ON UPDATE TO query.expr_xnum
844 UPDATE query.expression SET
846 parenthesize = NEW.parenthesize,
847 parent_expr = NEW.parent_expr,
849 literal = NEW.literal
853 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
854 ON DELETE TO query.expr_xnum
856 DELETE FROM query.expression WHERE id = OLD.id;
858 -- Create updatable view for operator expressions
860 CREATE OR REPLACE VIEW query.expr_xop AS
875 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
876 ON INSERT TO query.expr_xop
878 INSERT INTO query.expression (
889 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
891 COALESCE(NEW.parenthesize, FALSE),
893 COALESCE(NEW.seq_no, 1),
900 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
901 ON UPDATE TO query.expr_xop
903 UPDATE query.expression SET
905 parenthesize = NEW.parenthesize,
906 parent_expr = NEW.parent_expr,
908 left_operand = NEW.left_operand,
909 operator = NEW.operator,
910 right_operand = NEW.right_operand,
915 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
916 ON DELETE TO query.expr_xop
918 DELETE FROM query.expression WHERE id = OLD.id;
920 -- Create updatable view for string literal expressions
922 CREATE OR REPLACE VIEW query.expr_xstr AS
934 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
935 ON INSERT TO query.expr_xstr
937 INSERT INTO query.expression (
945 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
947 COALESCE(NEW.parenthesize, FALSE),
949 COALESCE(NEW.seq_no, 1),
953 CREATE OR REPLACE RULE query_expr_string_update_rule AS
954 ON UPDATE TO query.expr_xstr
956 UPDATE query.expression SET
958 parenthesize = NEW.parenthesize,
959 parent_expr = NEW.parent_expr,
961 literal = NEW.literal
965 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
966 ON DELETE TO query.expr_xstr
968 DELETE FROM query.expression WHERE id = OLD.id;
970 -- Create updatable view for subquery expressions
972 CREATE OR REPLACE VIEW query.expr_xsubq AS
985 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
986 ON INSERT TO query.expr_xsubq
988 INSERT INTO query.expression (
997 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
999 COALESCE(NEW.parenthesize, FALSE),
1001 COALESCE(NEW.seq_no, 1),
1006 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1007 ON UPDATE TO query.expr_xsubq
1009 UPDATE query.expression SET
1011 parenthesize = NEW.parenthesize,
1012 parent_expr = NEW.parent_expr,
1013 seq_no = NEW.seq_no,
1014 subquery = NEW.subquery,
1019 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1020 ON DELETE TO query.expr_xsubq
1022 DELETE FROM query.expression WHERE id = OLD.id;