4 -- General purpose query container. Any table the needs to store
5 -- a QueryParser query should store it here. This will be the
6 -- source for top-level and QP sub-search inclusion queries.
7 CREATE TABLE actor.search_query (
9 label TEXT NOT NULL, -- i18n
10 query_text TEXT NOT NULL -- QP text
13 -- e.g. "Reading Level"
14 CREATE TABLE actor.search_filter_group (
15 id SERIAL PRIMARY KEY,
16 owner INT NOT NULL REFERENCES actor.org_unit (id)
17 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
18 code TEXT NOT NULL, -- for CGI, etc.
19 label TEXT NOT NULL, -- i18n
20 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
21 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
22 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
25 -- e.g. "Adult", "Teen", etc.
26 CREATE TABLE actor.search_filter_group_entry (
27 id SERIAL PRIMARY KEY,
28 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
29 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
30 pos INT NOT NULL DEFAULT 0,
31 query INT NOT NULL REFERENCES actor.search_query(id)
32 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
33 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
42 INSERT INTO actor.search_filter_group (owner, code, label)
43 VALUES (4, 'reading_level', 'Reading Level');
45 INSERT INTO actor.search_query (label, query_text)
46 VALUES ('Children', 'audience(a,b,c) locations(3,4,5,6)');
47 INSERT INTO actor.search_query (label, query_text)
48 VALUES ('Juvenile', 'audience(j,d) locations(1,2,7,8)');
49 INSERT INTO actor.search_query (label, query_text)
50 VALUES ('General', 'audience(e,f,g)');
52 INSERT INTO actor.search_filter_group_entry (grp, query)
54 (SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'),
55 (SELECT id FROM actor.search_query WHERE label = 'Children')
57 INSERT INTO actor.search_filter_group_entry (grp, query)
59 (SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'),
60 (SELECT id FROM actor.search_query WHERE label = 'Juvenile')
62 INSERT INTO actor.search_filter_group_entry (grp, query)
64 (SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'),
65 (SELECT id FROM actor.search_query WHERE label = 'General')
74 DROP TABLE actor.search_filter_group_entry;
75 DROP TABLE actor.search_filter_group;
76 DROP TABLE actor.search_query;