From 9d3deeccb7b6b6dcb4cfdf8d16f6069ddc109a2d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 9 Apr 2012 11:48:10 -0400 Subject: [PATCH] search filter group : db/idl consolidation stuff Signed-off-by: Bill Erickson --- Open-ILS/examples/fm_IDL.xml | 22 +++++++--- .../Pg/upgrade/XXXX.schema.search_query_groups.sql | 47 ++++++++++++---------- 2 files changed, 42 insertions(+), 27 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 96a80f13b7..1564108f6f 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4766,11 +4766,23 @@ SELECT usr, + + + + + + + + + + + + + - @@ -4792,12 +4804,12 @@ SELECT usr, - - + + @@ -4805,10 +4817,10 @@ SELECT usr, - + - + 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 index d2af899a3d..b9404f5da9 100644 --- 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 @@ -1,11 +1,13 @@ BEGIN; --- QP sub-search inclusion will be based on actor.search_query.id +-- 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 + query_text TEXT NOT NULL -- QP text ); -- e.g. "Reading Level" @@ -15,7 +17,6 @@ CREATE TABLE actor.search_filter_group ( ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, label TEXT NOT NULL, -- i18n create_date TIMESTAMPTZ NOT NULL DEFAULT now(), - CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code), CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label) ); @@ -30,35 +31,37 @@ CREATE TABLE actor.search_filter_group_entry ( CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query) ); - --- also use query for user saved searches. --- this allows us to support user-default-search-filters for KCLS (opac36) --- https://docs.google.com/document/d/1EvdytPqFjLwuA3R7U0tSuk3floBossoBdRfLR_1YH7Y/edit --- note:would need a tmp stored proc to migrate properly -ALTER TABLE actor.usr_saved_search - ADD COLUMN query NOT NULL REFERENCES actor.search_query(id) - ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - DROP COLUMN query_text, -- gets from query - DROP COLUMN name, -- gets from query - DROP COLUMN query_type, -- may be presumptuous. QP text is the only used type. - --- - COMMIT; /* -- Fictional Example BEGIN; -INSERT INTO actor.search_filter_group (owner, code, label) VALUES (4, 'reading_level', 'Reading Level'); -INSERT INTO actor.search_filter_group_entry (grp, label, query_text) VALUES (1, 'Children', 'audience(a,b,c) locations(3,4,5,6)'); -INSERT INTO actor.search_filter_group_entry (grp, label, query_text) VALUES (1, 'Juvenile', 'audience(j,d) locations(1,2,7,8)'); -INSERT INTO actor.search_filter_group_entry (grp, label, query_text) VALUES (1, 'General', 'audience(e,f,g)'); + +INSERT INTO actor.search_filter_group (owner, label) + VALUES (4, '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 (1, (SELECT id FROM actor.search_query WHERE label = 'Children')); +INSERT INTO actor.search_filter_group_entry (grp, query) + VALUES (1, (SELECT id FROM actor.search_query WHERE label = 'Juvenile')); +INSERT INTO actor.search_filter_group_entry (grp, query) + VALUES (1, (SELECT id FROM actor.search_query WHERE label = 'General')); + COMMIT; */ -/* UNDO +/* +-- UNDO BEGIN; DROP TABLE actor.search_filter_group_entry; DROP TABLE actor.search_filter_group; +DROP TABLE actor.search_query; COMMIT; */ -- 2.11.0