Create a schema named "query" instead of "sql" (which is a
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 008.schema.query.sql
1 -- Script to create the query schema and the tables therein
2
3 BEGIN;
4
5 DROP SCHEMA IF EXISTS query CASCADE;
6 CREATE SCHEMA query;
7 COMMENT ON SCHEMA actor IS $$
8 /*
9  * Copyright (C) 2009  Equinox Software, Inc. / Georgia Public Library Service
10  * Scott McKellar <scott@esilibrary.com>
11  *
12  * Schema: query
13  *
14  * Contains tables designed to represent user-defined queries for
15  * reports and the like.
16  *
17  * ****
18  *
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.
23  *
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.
28  */
29
30
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            NOT NULL , --REFERENCES query.from_clause
38         where_clause  INT            , --REFERENCES query.expression
39                                      --DEFERRABLE INITIALLY DEFERRED,
40         having_clause INT            --REFERENCES query.expression
41                                      --DEFERRABLE INITIALLY DEFERRED
42 );
43
44 -- (Foreign keys to be defined later after other tables are created)
45
46 CREATE TABLE query.query_sequence (
47         id              SERIAL            PRIMARY KEY,
48         parent_query    INT               NOT NULL
49                                           REFERENCES query.stored_query
50                                                                           ON DELETE CASCADE
51                                                                           DEFERRABLE INITIALLY DEFERRED,
52         seq_no          INT               NOT NULL,
53         child_query     INT               NOT NULL
54                                           REFERENCES query.stored_query
55                                                                           ON DELETE CASCADE
56                                                                           DEFERRABLE INITIALLY DEFERRED,
57         CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
58 );
59
60 CREATE TABLE query.datatype (
61         id              SERIAL            PRIMARY KEY,
62         datatype_name   TEXT              NOT NULL UNIQUE,
63         is_numeric      BOOL              NOT NULL DEFAULT FALSE,
64         is_composite    BOOL              NOT NULL DEFAULT FALSE,
65         CONSTRAINT qdt_comp_not_num CHECK
66         ( is_numeric IS FALSE OR is_composite IS FALSE )
67 );
68
69 CREATE TABLE query.subfield (
70         id              SERIAL            PRIMARY KEY,
71         composite_type  INT               NOT NULL
72                                           REFERENCES query.datatype(id)
73                                           ON DELETE CASCADE
74                                           DEFERRABLE INITIALLY DEFERRED,
75         seq_no          INT               NOT NULL
76                                           CONSTRAINT qsf_pos_seq_no
77                                           CHECK( seq_no > 0 ),
78         subfield_type   INT               NOT NULL
79                                           REFERENCES query.datatype(id)
80                                           DEFERRABLE INITIALLY DEFERRED,
81         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
82 );
83
84 CREATE TABLE query.function_sig (
85         id              SERIAL            PRIMARY KEY,
86         function_name   TEXT              NOT NULL,
87         return_type     INT               REFERENCES query.datatype(id)
88                                           DEFERRABLE INITIALLY DEFERRED,
89         is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
90         CONSTRAINT qfd_rtn_or_aggr CHECK
91         ( return_type IS NULL OR is_aggregate = FALSE )
92 );
93
94 CREATE INDEX query_function_sig_name_idx 
95         ON query.function_sig (function_name);
96
97 CREATE TABLE query.function_param_def (
98         id              SERIAL            PRIMARY KEY,
99         function_id     INT               NOT NULL
100                                           REFERENCES query.function_sig( id )
101                                           ON DELETE CASCADE
102                                           DEFERRABLE INITIALLY DEFERRED,
103         seq_no          INT               NOT NULL
104                                           CONSTRAINT qfpd_pos_seq_no CHECK
105                                           ( seq_no > 0 ),
106         datatype        INT               NOT NULL
107                                           REFERENCES query.datatype( id )
108                                           DEFERRABLE INITIALLY DEFERRED,
109         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
110 );
111
112 CREATE TABLE query.expression (
113         id            SERIAL        PRIMARY KEY,
114         type          TEXT          NOT NULL CONSTRAINT predicate_type CHECK
115                                     ( type IN (
116                                         'xbet',    -- between
117                                                                         'xbool',   -- boolean
118                                         'xcase',   -- case
119                                                                         'xcast',   -- cast
120                                                                         'xcol',    -- column
121                                                                         'xex',     -- exists
122                                                                         'xfld',    -- field
123                                                                         'xfunc',   -- function
124                                                                         'xin',     -- in
125                                                                         'xnbet',   -- not between
126                                         'xnex',    -- not exists
127                                                                         'xnin',    -- not in
128                                         'xnull',   -- null
129                                                                         'xnum',    -- number
130                                                                         'xop',     -- operator
131                                                                         'xstr',    -- string
132                                                 'xsubq'    -- subquery
133                                                                 ) ),
134         parenthesize  BOOL          NOT NULL DEFAULT FALSE,
135         parent_expr   INT           REFERENCES query.expression
136                                     ON DELETE CASCADE
137                                     DEFERRABLE INITIALLY DEFERRED,
138         seq_no        INT           NOT NULL DEFAULT 1,
139         literal       TEXT,
140         table_alias   TEXT,
141         column_name   TEXT,
142         left_operand  INT           REFERENCES query.expression
143                                     DEFERRABLE INITIALLY DEFERRED,
144         operator      TEXT,
145         right_operand INT           REFERENCES query.expression
146                                     DEFERRABLE INITIALLY DEFERRED,
147         function_id   INT           REFERENCES query.function_sig
148                                     DEFERRABLE INITIALLY DEFERRED,
149         subquery      INT           REFERENCES query.stored_query
150                                     DEFERRABLE INITIALLY DEFERRED,
151         cast_type     INT           REFERENCES query.datatype
152                                     DEFERRABLE INITIALLY DEFERRED
153 );
154
155 CREATE UNIQUE INDEX query_expr_parent_seq
156         ON query.expression( parent_expr, seq_no )
157         WHERE parent_expr IS NOT NULL;
158
159 -- Due to some circular references, the following foreign key definitions
160 -- had to be deferred until query.expression existed:
161
162 ALTER TABLE query.stored_query
163         ADD FOREIGN KEY ( where_clause )
164         REFERENCES query.expression( id )
165         DEFERRABLE INITIALLY DEFERRED;
166
167 ALTER TABLE query.stored_query
168         ADD FOREIGN KEY ( having_clause )
169         REFERENCES query.expression( id )
170         DEFERRABLE INITIALLY DEFERRED;
171
172 CREATE TABLE query.case_branch (
173         id            SERIAL        PRIMARY KEY,
174         parent_expr   INT           NOT NULL REFERENCES query.expression
175                                     ON DELETE CASCADE
176                                     DEFERRABLE INITIALLY DEFERRED,
177         seq_no        INT           NOT NULL,
178         condition     INT           REFERENCES query.expression
179                                     DEFERRABLE INITIALLY DEFERRED,
180         result        INT           NOT NULL REFERENCES query.expression
181                                     DEFERRABLE INITIALLY DEFERRED,
182         CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
183 );
184
185 CREATE TABLE query.from_relation (
186         id               SERIAL        PRIMARY KEY,
187         type             TEXT          NOT NULL CONSTRAINT relation_type CHECK (
188                                            type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
189         table_name       TEXT,
190         class_name       TEXT,
191         subquery         INT           REFERENCES query.stored_query,
192         function_call    INT           REFERENCES query.expression,
193         table_alias      TEXT          NOT NULL,
194         parent_relation  INT           REFERENCES query.from_relation
195                                        ON DELETE CASCADE
196                                        DEFERRABLE INITIALLY DEFERRED,
197         seq_no           INT           NOT NULL DEFAULT 1,
198         join_type        TEXT          CONSTRAINT good_join_type CHECK (
199                                            join_type IS NULL OR join_type IN
200                                            ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
201                                        ),
202         on_clause        INT           REFERENCES query.expression
203                                        DEFERRABLE INITIALLY DEFERRED,
204         CONSTRAINT join_or_core CHECK (
205             ( parent_relation IS NULL AND join_type IS NULL 
206               AND on_clause IS NULL and table_alias IS NULL )
207             OR
208             ( parent_relation IS NOT NULL AND join_type IS NOT NULL
209               AND on_clause IS NOT NULL )
210         )
211 );
212
213 CREATE UNIQUE INDEX from_parent_seq
214         ON query.from_relation( parent_relation, seq_no )
215         WHERE parent_relation IS NOT NULL;
216
217 -- The following foreign key had to be deferred until
218 -- query.from_relation existed
219
220 ALTER TABLE query.stored_query
221         ADD FOREIGN KEY (from_clause)
222         REFERENCES query.from_relation
223         DEFERRABLE INITIALLY DEFERRED;
224
225 CREATE TABLE query.record_column (
226         id            SERIAL            PRIMARY KEY,
227         from_relation INT               NOT NULL REFERENCES query.from_relation
228                                         ON DELETE CASCADE
229                                         DEFERRABLE INITIALLY DEFERRED,
230         seq_no        INT               NOT NULL,
231         column_name   TEXT              NOT NULL,
232         column_type   TEXT              NOT NULL,
233         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
234 );
235
236 CREATE TABLE query.select_item (
237         id               SERIAL         PRIMARY KEY,
238         stored_query     INT            NOT NULL REFERENCES query.stored_query
239                                         ON DELETE CASCADE
240                                         DEFERRABLE INITIALLY DEFERRED,
241         seq_no           INT            NOT NULL,
242         expression       INT            NOT NULL REFERENCES query.expression
243                                         DEFERRABLE INITIALLY DEFERRED,
244         column_alias     TEXT,
245         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
246         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
247 );
248
249 CREATE TABLE query.order_by_item (
250         id               SERIAL         PRIMARY KEY,
251         stored_query     INT            NOT NULL REFERENCES query.stored_query
252                                         ON DELETE CASCADE
253                                         DEFERRABLE INITIALLY DEFERRED,
254         seq_no           INT            NOT NULL,
255         expression       INT            NOT NULL REFERENCES query.expression
256                                         ON DELETE CASCADE
257                                         DEFERRABLE INITIALLY DEFERRED,
258         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
259 );
260
261 -- Create updatable views
262
263 CREATE OR REPLACE VIEW query.expr_string AS
264     SELECT
265         id,
266         parenthesize,
267         parent_expr,
268         seq_no,
269         literal
270     FROM
271         query.expression
272     WHERE
273         type = 'xstr';
274
275 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
276     ON INSERT TO query.expr_string
277     DO INSTEAD
278     INSERT INTO query.expression (
279         id,
280         type,
281         parenthesize,
282         parent_expr,
283         seq_no,
284         literal
285     ) VALUES (
286         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
287         'xstr',
288         COALESCE(NEW.parenthesize, FALSE),
289         NEW.parent_expr,
290         COALESCE(NEW.seq_no, 1),
291         NEW.literal
292     );
293
294 CREATE OR REPLACE RULE query_expr_string_update_rule AS
295     ON UPDATE TO query.expr_string
296     DO INSTEAD
297     UPDATE query.expression SET
298         id = NEW.id,
299         parenthesize = NEW.parenthesize,
300         parent_expr = NEW.parent_expr,
301         seq_no = NEW.seq_no,
302         literal = NEW.literal
303     WHERE
304         id = OLD.id;
305
306 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
307     ON DELETE TO query.expr_string
308     DO INSTEAD
309     DELETE FROM query.expression WHERE id = OLD.id;
310
311 COMMIT;