Stamping populariy parameter upgrade scripts

Also, the org-count parameter function is adjusted to consistently find
and count copies by circ lib.  Because this parameter is about counting
copies on the shelves of libraries, this makes more sense than owing
library.  Changed in consultation with Kathy Lussier.

Signed-off-by: Mike Rylander <mrylander@gmail.com>

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 96e1170..f42d9e9 100644 (file)
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -90,7 +90,7 @@ CREATE TRIGGER no_overlapping_deps
     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
 
     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
 

-INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1051', :eg_version); -- berick/miker
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1053', :eg_version); -- kmlussier/miker

 
 CREATE TABLE config.bib_source (
        id              SERIAL  PRIMARY KEY,
 
 CREATE TABLE config.bib_source (
        id              SERIAL  PRIMARY KEY,

diff --git a/Open-ILS/src/sql/Pg/220.schema.rating.sql b/Open-ILS/src/sql/Pg/220.schema.rating.sql
index 242ecb0..b5e4fb3 100644 (file)
--- a/Open-ILS/src/sql/Pg/220.schema.rating.sql
+++ b/Open-ILS/src/sql/Pg/220.schema.rating.sql
@@ -950,13 +950,13 @@ BEGIN
     );
     ANALYZE precalc_copy_filter_bib_list;
 
     );
     ANALYZE precalc_copy_filter_bib_list;
 

+    -- Use circ rather than owning lib here as that means "on the shelf at..."

     RETURN QUERY
      SELECT f.id::INT AS bib,
             COUNT(DISTINCT cp.circ_lib)::NUMERIC
      FROM asset.copy cp
           JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
     RETURN QUERY
      SELECT f.id::INT AS bib,
             COUNT(DISTINCT cp.circ_lib)::NUMERIC
      FROM asset.copy cp
           JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)

-          JOIN asset.call_number cn ON (cn.id = cp.call_number)
-     WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
+     WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;

 
 END;
 $f$ LANGUAGE PLPGSQL STRICT;
 
 END;
 $f$ LANGUAGE PLPGSQL STRICT;

diff --git a/Open-ILS/src/sql/Pg/upgrade/1052.data-inhouse-use-badge.sql b/Open-ILS/src/sql/Pg/upgrade/1052.data-inhouse-use-badge.sql
new file mode 100644 (file)
index 0000000..b47d390
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/1052.data-inhouse-use-badge.sql
@@ -0,0 +1,63 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('1052', :eg_version);
+
+CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
+    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
+DECLARE
+    badge   rating.badge_with_orgs%ROWTYPE;
+    iage    INT     := 1;
+    iint    INT     := NULL;
+    iscale  NUMERIC := NULL;
+BEGIN
+
+    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
+
+    IF badge.horizon_age IS NULL THEN
+        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
+            badge.name,
+            badge.id;
+    END IF;
+
+    PERFORM rating.precalc_bibs_by_copy(badge_id);
+
+    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
+        SELECT id FROM precalc_filter_bib_list
+            INTERSECT
+        SELECT id FROM precalc_bibs_by_copy_list
+    );
+
+    ANALYZE precalc_copy_filter_bib_list;
+
+    iint := EXTRACT(EPOCH FROM badge.importance_interval);
+    IF badge.importance_age IS NOT NULL THEN
+        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
+    END IF;
+
+    -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
+    iscale := COALESCE(badge.importance_scale, 1.0);
+
+    RETURN QUERY
+     SELECT bib,
+            SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
+      FROM (
+         SELECT cn.record AS bib,
+                (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
+                COUNT(u.id)::INT AS uses
+          FROM  action.in_house_use u
+                JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
+                JOIN asset.copy cp ON (cp.id = u.item)
+                JOIN asset.call_number cn ON (cn.id = cp.call_number)
+          WHERE u.use_time >= NOW() - badge.horizon_age
+                AND cn.owning_lib = ANY (badge.orgs)
+          GROUP BY 1, 2
+      ) x
+      GROUP BY 1;
+END;
+$f$ LANGUAGE PLPGSQL STRICT;
+
+INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES
+    (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE);
+
+COMMIT;
+

diff --git a/Open-ILS/src/sql/Pg/upgrade/1053.data.org-unit-count-badge.sql b/Open-ILS/src/sql/Pg/upgrade/1053.data.org-unit-count-badge.sql
new file mode 100644 (file)
index 0000000..adb457f
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/1053.data.org-unit-count-badge.sql
@@ -0,0 +1,37 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('1053', :eg_version);
+
+CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT)
+    RETURNS TABLE (record INT, value NUMERIC) AS $f$
+DECLARE
+    badge   rating.badge_with_orgs%ROWTYPE;
+BEGIN
+
+    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
+
+    PERFORM rating.precalc_bibs_by_copy(badge_id);
+
+    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
+        SELECT id FROM precalc_filter_bib_list
+            INTERSECT
+        SELECT id FROM precalc_bibs_by_copy_list
+    );
+    ANALYZE precalc_copy_filter_bib_list;
+
+    -- Use circ rather than owning lib here as that means "on the shelf at..."
+    RETURN QUERY
+     SELECT f.id::INT AS bib,
+            COUNT(DISTINCT cp.circ_lib)::NUMERIC
+     FROM asset.copy cp
+          JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
+     WHERE cp.circ_lib = ANY (badge.orgs) GROUP BY 1;
+
+END;
+$f$ LANGUAGE PLPGSQL STRICT;
+
+INSERT INTO rating.popularity_parameter (id, name, func, require_percentile) VALUES
+    (17,'Circulation Library Count', 'rating.org_unit_count', TRUE);
+
+COMMIT;
+

diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql
deleted file mode 100644 (file)
index 16d3f16..0000000
--- a/Open-ILS/src/sql/Pg/upgrade/xxxx.data-inhouse-use-badge.sql
+++ /dev/null
@@ -1,63 +0,0 @@
-BEGIN;
-
--- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
-
-CREATE OR REPLACE FUNCTION rating.inhouse_over_time(badge_id INT)
-    RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
-DECLARE
-    badge   rating.badge_with_orgs%ROWTYPE;
-    iage    INT     := 1;
-    iint    INT     := NULL;
-    iscale  NUMERIC := NULL;
-BEGIN
-
-    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
-
-    IF badge.horizon_age IS NULL THEN
-        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
-            badge.name,
-            badge.id;
-    END IF;
-
-    PERFORM rating.precalc_bibs_by_copy(badge_id);
-
-    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
-        SELECT id FROM precalc_filter_bib_list
-            INTERSECT
-        SELECT id FROM precalc_bibs_by_copy_list
-    );
-
-    ANALYZE precalc_copy_filter_bib_list;
-
-    iint := EXTRACT(EPOCH FROM badge.importance_interval);
-    IF badge.importance_age IS NOT NULL THEN
-        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
-    END IF;
-
-    -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
-    iscale := COALESCE(badge.importance_scale, 1.0);
-
-    RETURN QUERY
-     SELECT bib,
-            SUM( uses * GREATEST( iscale * (iage - cage), 1.0 ))
-      FROM (
-         SELECT cn.record AS bib,
-                (1 + EXTRACT(EPOCH FROM AGE(u.use_time)) / iint)::INT AS cage,
-                COUNT(u.id)::INT AS uses
-          FROM  action.in_house_use u
-                JOIN precalc_copy_filter_bib_list cf ON (u.item = cf.copy)
-                JOIN asset.copy cp ON (cp.id = u.item)
-                JOIN asset.call_number cn ON (cn.id = cp.call_number)
-          WHERE u.use_time >= NOW() - badge.horizon_age
-                AND cn.owning_lib = ANY (badge.orgs)
-          GROUP BY 1, 2
-      ) x
-      GROUP BY 1;
-END;
-$f$ LANGUAGE PLPGSQL STRICT;
-
-INSERT INTO rating.popularity_parameter (id, name, func, require_horizon,require_percentile) VALUES
-    (18,'In-House Use Over Time', 'rating.inhouse_over_time', TRUE, TRUE);
-
-COMMIT;
-

diff --git a/Open-ILS/src/sql/Pg/upgrade/yyyy.data.org-unit-count-badge.sql b/Open-ILS/src/sql/Pg/upgrade/yyyy.data.org-unit-count-badge.sql
deleted file mode 100644 (file)
index 8eae7a2..0000000
--- a/Open-ILS/src/sql/Pg/upgrade/yyyy.data.org-unit-count-badge.sql
+++ /dev/null
@@ -1,37 +0,0 @@
-BEGIN;
-
--- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
-
-CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT)
-    RETURNS TABLE (record INT, value NUMERIC) AS $f$
-DECLARE
-    badge   rating.badge_with_orgs%ROWTYPE;
-BEGIN
-
-    SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
-
-    PERFORM rating.precalc_bibs_by_copy(badge_id);
-
-    DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
-        SELECT id FROM precalc_filter_bib_list
-            INTERSECT
-        SELECT id FROM precalc_bibs_by_copy_list
-    );
-    ANALYZE precalc_copy_filter_bib_list;
-
-    RETURN QUERY
-     SELECT f.id::INT AS bib,
-            COUNT(DISTINCT cp.circ_lib)::NUMERIC
-     FROM asset.copy cp
-          JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
-          JOIN asset.call_number cn ON (cn.id = cp.call_number)
-     WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
-
-END;
-$f$ LANGUAGE PLPGSQL STRICT;
-
-INSERT INTO rating.popularity_parameter (id, name, func, require_percentile) VALUES
-    (17,'Circulation Library Count', 'rating.org_unit_count', TRUE);
-
-COMMIT;
-