From 497430ca0a24713c6885fbd26324707baf616d5e Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 23 Feb 2012 14:28:50 -0500 Subject: [PATCH] Search Filter Groups and Generic Queries : DB / IDL Adds 3 new tables. The first is a general purpose container for query-parser queries. These can be used by other entities for storing saved searches, saved search filters, etc. Each contains a query string and a label. The two other tables are for creating org unit saved search filter groups. A filter group is a named set of search queries, treated as filters, collected to create a high-level search filter. Filter group entries use search_query's under the covers, so the content of the filter can be any valid query string, including other filters or even other filter groups. For example, you could create an "Audience" filter group with entries like, "Children" => "audience(a, b) locations(3,4,5)" to filter on copy location in addition to the MARC audience data. Signed-off-by: Bill Erickson Signed-off-by: Mike Rylander --- Open-ILS/examples/fm_IDL.xml | 62 +++++++++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 27 +++++++ .../XXXX.schema.search_query_groups.sql | 78 +++++++++++++++++++ 3 files changed, 167 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search_query_groups.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 2ff29042de..79b8738736 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4771,6 +4771,68 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 21363b966b..86958525ce 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -681,4 +681,31 @@ CREATE TABLE actor.org_unit_custom_tree_node ( CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit) ); +CREATE TABLE actor.search_query ( + id SERIAL PRIMARY KEY, + label TEXT NOT NULL, -- i18n + query_text TEXT NOT NULL -- QP text +); + +CREATE TABLE actor.search_filter_group ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.org_unit (id) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + code TEXT NOT NULL, -- for CGI, etc. + label TEXT NOT NULL, -- i18n + create_date TIMESTAMPTZ NOT NULL DEFAULT now(), + CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label), + CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code) +); + +CREATE TABLE actor.search_filter_group_entry ( + id SERIAL PRIMARY KEY, + grp INT NOT NULL REFERENCES actor.search_filter_group(id) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + pos INT NOT NULL DEFAULT 0, + query INT NOT NULL REFERENCES actor.search_query(id) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query) +); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search_query_groups.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search_query_groups.sql new file mode 100644 index 0000000000..1ce760d3c8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search_query_groups.sql @@ -0,0 +1,78 @@ + +BEGIN; + +-- General purpose query container. Any table the needs to store +-- a QueryParser query should store it here. This will be the +-- source for top-level and QP sub-search inclusion queries. +CREATE TABLE actor.search_query ( + id SERIAL PRIMARY KEY, + label TEXT NOT NULL, -- i18n + query_text TEXT NOT NULL -- QP text +); + +-- e.g. "Reading Level" +CREATE TABLE actor.search_filter_group ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.org_unit (id) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + code TEXT NOT NULL, -- for CGI, etc. + label TEXT NOT NULL, -- i18n + create_date TIMESTAMPTZ NOT NULL DEFAULT now(), + CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label), + CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code) +); + +-- e.g. "Adult", "Teen", etc. +CREATE TABLE actor.search_filter_group_entry ( + id SERIAL PRIMARY KEY, + grp INT NOT NULL REFERENCES actor.search_filter_group(id) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + pos INT NOT NULL DEFAULT 0, + query INT NOT NULL REFERENCES actor.search_query(id) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query) +); + +COMMIT; + +/* +-- Fictional Example +BEGIN; + +INSERT INTO actor.search_filter_group (owner, code, label) + VALUES (4, 'reading_level', 'Reading Level'); + +INSERT INTO actor.search_query (label, query_text) + VALUES ('Children', 'audience(a,b,c) locations(3,4,5,6)'); +INSERT INTO actor.search_query (label, query_text) + VALUES ('Juvenile', 'audience(j,d) locations(1,2,7,8)'); +INSERT INTO actor.search_query (label, query_text) + VALUES ('General', 'audience(e,f,g)'); + +INSERT INTO actor.search_filter_group_entry (grp, query) + VALUES ( + (SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'), + (SELECT id FROM actor.search_query WHERE label = 'Children') + ); +INSERT INTO actor.search_filter_group_entry (grp, query) + VALUES ( + (SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'), + (SELECT id FROM actor.search_query WHERE label = 'Juvenile') + ); +INSERT INTO actor.search_filter_group_entry (grp, query) + VALUES ( + (SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'), + (SELECT id FROM actor.search_query WHERE label = 'General') + ); + +COMMIT; +*/ + +/* +-- UNDO +BEGIN; +DROP TABLE actor.search_filter_group_entry; +DROP TABLE actor.search_filter_group; +DROP TABLE actor.search_query; +COMMIT; +*/ -- 2.43.2