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