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
277 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
278 ON INSERT TO query.expr_xbet
280 INSERT INTO query.expression (
287 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
289 COALESCE(NEW.parenthesize, FALSE),
291 COALESCE(NEW.seq_no, 1)
294 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
295 ON UPDATE TO query.expr_xbet
297 UPDATE query.expression SET
299 parenthesize = NEW.parenthesize,
300 parent_expr = NEW.parent_expr,
305 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
306 ON DELETE TO query.expr_xbet
308 DELETE FROM query.expression WHERE id = OLD.id;
310 -- Create updatable view for boolean expressions
312 CREATE OR REPLACE VIEW query.expr_xbool AS
324 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
325 ON INSERT TO query.expr_xbool
327 INSERT INTO query.expression (
335 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
337 COALESCE(NEW.parenthesize, FALSE),
339 COALESCE(NEW.seq_no, 1),
343 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
344 ON UPDATE TO query.expr_xbool
346 UPDATE query.expression SET
348 parenthesize = NEW.parenthesize,
349 parent_expr = NEW.parent_expr,
351 literal = NEW.literal
355 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
356 ON DELETE TO query.expr_xbool
358 DELETE FROM query.expression WHERE id = OLD.id;
360 -- Create updatable view for CASE expressions
362 CREATE OR REPLACE VIEW query.expr_xcase AS
373 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
374 ON INSERT TO query.expr_xcase
376 INSERT INTO query.expression (
383 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
385 COALESCE(NEW.parenthesize, FALSE),
387 COALESCE(NEW.seq_no, 1)
390 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
391 ON UPDATE TO query.expr_xcase
393 UPDATE query.expression SET
395 parenthesize = NEW.parenthesize,
396 parent_expr = NEW.parent_expr,
401 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
402 ON DELETE TO query.expr_xcase
404 DELETE FROM query.expression WHERE id = OLD.id;
406 -- Create updatable view for cast expressions
408 CREATE OR REPLACE VIEW query.expr_xcast AS
421 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
422 ON INSERT TO query.expr_xcast
424 INSERT INTO query.expression (
433 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
435 COALESCE(NEW.parenthesize, FALSE),
437 COALESCE(NEW.seq_no, 1),
442 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
443 ON UPDATE TO query.expr_xcast
445 UPDATE query.expression SET
447 parenthesize = NEW.parenthesize,
448 parent_expr = NEW.parent_expr,
450 left_operand = NEW.left_operand,
451 cast_type = NEW.cast_type
455 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
456 ON DELETE TO query.expr_xcast
458 DELETE FROM query.expression WHERE id = OLD.id;
460 -- Create updatable view for column expressions
462 CREATE OR REPLACE VIEW query.expr_xcol AS
475 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
476 ON INSERT TO query.expr_xcol
478 INSERT INTO query.expression (
487 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
489 COALESCE(NEW.parenthesize, FALSE),
491 COALESCE(NEW.seq_no, 1),
496 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
497 ON UPDATE TO query.expr_xcol
499 UPDATE query.expression SET
501 parenthesize = NEW.parenthesize,
502 parent_expr = NEW.parent_expr,
504 table_alias = NEW.table_alias,
505 column_name = NEW.column_name
509 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
510 ON DELETE TO query.expr_xcol
512 DELETE FROM query.expression WHERE id = OLD.id;
514 -- Create updatable view for EXISTS expressions
516 CREATE OR REPLACE VIEW query.expr_xex AS
528 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
529 ON INSERT TO query.expr_xex
531 INSERT INTO query.expression (
539 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
541 COALESCE(NEW.parenthesize, FALSE),
543 COALESCE(NEW.seq_no, 1),
547 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
548 ON UPDATE TO query.expr_xex
550 UPDATE query.expression SET
552 parenthesize = NEW.parenthesize,
553 parent_expr = NEW.parent_expr,
555 subquery = NEW.subquery
559 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
560 ON DELETE TO query.expr_xex
562 DELETE FROM query.expression WHERE id = OLD.id;
564 -- Create updatable view for field expressions
566 CREATE OR REPLACE VIEW query.expr_xfld AS
579 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
580 ON INSERT TO query.expr_xfld
582 INSERT INTO query.expression (
591 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
593 COALESCE(NEW.parenthesize, FALSE),
595 COALESCE(NEW.seq_no, 1),
600 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
601 ON UPDATE TO query.expr_xfld
603 UPDATE query.expression SET
605 parenthesize = NEW.parenthesize,
606 parent_expr = NEW.parent_expr,
608 column_name = NEW.column_name,
609 left_operand = NEW.left_operand
613 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
614 ON DELETE TO query.expr_xfld
616 DELETE FROM query.expression WHERE id = OLD.id;
618 -- Create updatable view for function call expressions
620 CREATE OR REPLACE VIEW query.expr_xfunc AS
632 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
633 ON INSERT TO query.expr_xfunc
635 INSERT INTO query.expression (
643 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
645 COALESCE(NEW.parenthesize, FALSE),
647 COALESCE(NEW.seq_no, 1),
651 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
652 ON UPDATE TO query.expr_xfunc
654 UPDATE query.expression SET
656 parenthesize = NEW.parenthesize,
657 parent_expr = NEW.parent_expr,
659 function_id = NEW.function_id
663 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
664 ON DELETE TO query.expr_xfunc
666 DELETE FROM query.expression WHERE id = OLD.id;
668 -- Create updatable view for IN expressions
670 CREATE OR REPLACE VIEW query.expr_xin AS
682 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
683 ON INSERT TO query.expr_xin
685 INSERT INTO query.expression (
693 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
695 COALESCE(NEW.parenthesize, FALSE),
697 COALESCE(NEW.seq_no, 1),
701 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
702 ON UPDATE TO query.expr_xin
704 UPDATE query.expression SET
706 parenthesize = NEW.parenthesize,
707 parent_expr = NEW.parent_expr,
709 subquery = NEW.subquery
713 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
714 ON DELETE TO query.expr_xin
716 DELETE FROM query.expression WHERE id = OLD.id;
718 -- Create updatable view for NULL expressions
720 CREATE OR REPLACE VIEW query.expr_xnull AS
731 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
732 ON INSERT TO query.expr_xnull
734 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)
748 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
749 ON UPDATE TO query.expr_xnull
751 UPDATE query.expression SET
753 parenthesize = NEW.parenthesize,
754 parent_expr = NEW.parent_expr,
759 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
760 ON DELETE TO query.expr_xnull
762 DELETE FROM query.expression WHERE id = OLD.id;
764 -- Create updatable view for numeric literal expressions
766 CREATE OR REPLACE VIEW query.expr_xnum AS
778 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
779 ON INSERT TO query.expr_xnum
781 INSERT INTO query.expression (
789 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
791 COALESCE(NEW.parenthesize, FALSE),
793 COALESCE(NEW.seq_no, 1),
797 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
798 ON UPDATE TO query.expr_xnum
800 UPDATE query.expression SET
802 parenthesize = NEW.parenthesize,
803 parent_expr = NEW.parent_expr,
805 literal = NEW.literal
809 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
810 ON DELETE TO query.expr_xnum
812 DELETE FROM query.expression WHERE id = OLD.id;
814 -- Create updatable view for operator expressions
816 CREATE OR REPLACE VIEW query.expr_xop AS
830 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
831 ON INSERT TO query.expr_xop
833 INSERT INTO query.expression (
843 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
845 COALESCE(NEW.parenthesize, FALSE),
847 COALESCE(NEW.seq_no, 1),
853 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
854 ON UPDATE TO query.expr_xop
856 UPDATE query.expression SET
858 parenthesize = NEW.parenthesize,
859 parent_expr = NEW.parent_expr,
861 left_operand = NEW.left_operand,
862 operator = NEW.operator,
863 right_operand = NEW.right_operand
867 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
868 ON DELETE TO query.expr_xop
870 DELETE FROM query.expression WHERE id = OLD.id;
872 -- Create updatable view for string literal expressions
874 CREATE OR REPLACE VIEW query.expr_string AS
886 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
887 ON INSERT TO query.expr_string
889 INSERT INTO query.expression (
897 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
899 COALESCE(NEW.parenthesize, FALSE),
901 COALESCE(NEW.seq_no, 1),
905 CREATE OR REPLACE RULE query_expr_string_update_rule AS
906 ON UPDATE TO query.expr_string
908 UPDATE query.expression SET
910 parenthesize = NEW.parenthesize,
911 parent_expr = NEW.parent_expr,
913 literal = NEW.literal
917 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
918 ON DELETE TO query.expr_string
920 DELETE FROM query.expression WHERE id = OLD.id;
922 -- Create updatable view for subquery expressions
924 CREATE OR REPLACE VIEW query.expr_xsubq AS
936 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
937 ON INSERT TO query.expr_xsubq
939 INSERT INTO query.expression (
947 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
949 COALESCE(NEW.parenthesize, FALSE),
951 COALESCE(NEW.seq_no, 1),
955 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
956 ON UPDATE TO query.expr_xsubq
958 UPDATE query.expression SET
960 parenthesize = NEW.parenthesize,
961 parent_expr = NEW.parent_expr,
963 subquery = NEW.subquery
967 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
968 ON DELETE TO query.expr_xsubq
970 DELETE FROM query.expression WHERE id = OLD.id;