From 293de6d0ef9a94010a989e31d04e6645a7329e5c Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 15 Sep 2009 20:24:27 +0000 Subject: [PATCH] completing previous commit (hold loop calcuation upgrade scripts) git-svn-id: svn://svn.open-ils.org/ILS/trunk@14029 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- ...chema.hold-index-on-unfilled_hold_list.sql | 4 +++ .../0003.schema.hold-loop-counting.sql | 26 +++++++++++++++++++ 2 files changed, 30 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0002.schema.hold-index-on-unfilled_hold_list.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0003.schema.hold-loop-counting.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/0002.schema.hold-index-on-unfilled_hold_list.sql b/Open-ILS/src/sql/Pg/upgrade/0002.schema.hold-index-on-unfilled_hold_list.sql new file mode 100644 index 0000000000..4e28cdad2c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0002.schema.hold-index-on-unfilled_hold_list.sql @@ -0,0 +1,4 @@ +BEGIN; +INSERT INTO config.upgrade_log (version) VALUES ('0002.schema.hold-index-on-unfilled_hold_list.sql'); +CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold); +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0003.schema.hold-loop-counting.sql b/Open-ILS/src/sql/Pg/upgrade/0003.schema.hold-loop-counting.sql new file mode 100644 index 0000000000..f222d1e25a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0003.schema.hold-loop-counting.sql @@ -0,0 +1,26 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0003.schema.hold-loop-counting.sql'); + +CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS + SELECT u.hold, + c.circ_lib, + count(*) + FROM action.unfulfilled_hold_list u + JOIN asset.copy c ON (c.id = u.current_copy) + GROUP BY 1,2; + +CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS + SELECT hold, + min(count) + FROM action.unfulfilled_hold_loops + GROUP BY 1; + +CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS + SELECT DISTINCT l.* + FROM action.unfulfilled_hold_loops l + JOIN action.unfulfilled_hold_min_loop m USING (hold) + WHERE l.count = m.min; + + +COMMIT; -- 2.43.2