Stamping upgrade for "exclude_invisible_acn" unapi holdings filter
authorBen Shum <bshum@biblio.org>
Mon, 11 Mar 2013 21:45:23 +0000 (17:45 -0400)
committerBen Shum <bshum@biblio.org>
Mon, 11 Mar 2013 21:45:23 +0000 (17:45 -0400)
Signed-off-by: Ben Shum <bshum@biblio.org>
Open-ILS/src/sql/Pg/002.schema.config.sql
Open-ILS/src/sql/Pg/upgrade/0769.schema.unapi_exclude_invisible_acn.sql [new file with mode: 0644]
Open-ILS/src/sql/Pg/upgrade/XXX.schema.unapi_exclude_invisible_acn.sql [deleted file]

index 9ad2ba5..9acacb1 100644 (file)
@@ -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');
 
-INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0768', :eg_version); -- berick/bshum
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0769', :eg_version); -- berick/bshum
 
 CREATE TABLE config.bib_source (
        id              SERIAL  PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/upgrade/0769.schema.unapi_exclude_invisible_acn.sql b/Open-ILS/src/sql/Pg/upgrade/0769.schema.unapi_exclude_invisible_acn.sql
new file mode 100644 (file)
index 0000000..7bef743
--- /dev/null
@@ -0,0 +1,145 @@
+
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('0769', :eg_version);
+
+DROP FUNCTION IF EXISTS 
+    evergreen.ranked_volumes(BIGINT, INT, INT, HSTORE, HSTORE, INT);
+
+CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
+    bibid BIGINT, 
+    ouid INT,
+    depth INT DEFAULT NULL,
+    slimit HSTORE DEFAULT NULL,
+    soffset HSTORE DEFAULT NULL,
+    pref_lib INT DEFAULT NULL,
+    includes TEXT[] DEFAULT NULL::TEXT[]
+) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
+    SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
+        SELECT acn.id, aou.name, acn.label_sortkey,
+            evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
+            RANK() OVER w
+        FROM asset.call_number acn
+            JOIN asset.copy acp ON (acn.id = acp.call_number)
+            JOIN actor.org_unit_descendants( $2, COALESCE(
+                $3, (
+                    SELECT depth
+                    FROM actor.org_unit_type aout
+                        INNER JOIN actor.org_unit ou ON ou_type = aout.id
+                    WHERE ou.id = $2
+                ), $6)
+            ) AS aou ON (acp.circ_lib = aou.id)
+        WHERE acn.record = $1
+            AND acn.deleted IS FALSE
+            AND acp.deleted IS FALSE
+            AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN 
+                EXISTS (
+                    SELECT 1 
+                    FROM asset.opac_visible_copies 
+                    WHERE copy_id = acp.id AND record = acn.record
+                ) ELSE TRUE END
+        GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
+        WINDOW w AS (
+            ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
+        )
+    ) AS ua
+    GROUP BY ua.id, ua.name, ua.label_sortkey
+    ORDER BY rank, ua.name, ua.label_sortkey
+    LIMIT ($4 -> 'acn')::INT
+    OFFSET ($5 -> 'acn')::INT;
+$$
+LANGUAGE SQL STABLE;
+
+CREATE OR REPLACE FUNCTION unapi.holdings_xml (
+    bid BIGINT,
+    ouid INT,
+    org TEXT,
+    depth INT DEFAULT NULL,
+    includes TEXT[] DEFAULT NULL::TEXT[],
+    slimit HSTORE DEFAULT NULL,
+    soffset HSTORE DEFAULT NULL,
+    include_xmlns BOOL DEFAULT TRUE,
+    pref_lib INT DEFAULT NULL
+)
+RETURNS XML AS $F$
+     SELECT  XMLELEMENT(
+                 name holdings,
+                 XMLATTRIBUTES(
+                    CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
+                    CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
+                    (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
+                 ),
+                 XMLELEMENT(
+                     name counts,
+                     (SELECT  XMLAGG(XMLELEMENT::XML) FROM (
+                         SELECT  XMLELEMENT(
+                                     name count,
+                                     XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
+                                 )::text
+                           FROM  asset.opac_ou_record_copy_count($2,  $1)
+                                     UNION
+                         SELECT  XMLELEMENT(
+                                     name count,
+                                     XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
+                                 )::text
+                           FROM  asset.staff_ou_record_copy_count($2, $1)
+                                     UNION
+                         SELECT  XMLELEMENT(
+                                     name count,
+                                     XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
+                                 )::text
+                           FROM  asset.opac_ou_record_copy_count($9,  $1)
+                                     ORDER BY 1
+                     )x)
+                 ),
+                 CASE 
+                     WHEN ('bmp' = ANY ($5)) THEN
+                        XMLELEMENT(
+                            name monograph_parts,
+                            (SELECT XMLAGG(bmp) FROM (
+                                SELECT  unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
+                                  FROM  biblio.monograph_part
+                                  WHERE record = $1
+                            )x)
+                        )
+                     ELSE NULL
+                 END,
+                 XMLELEMENT(
+                     name volumes,
+                     (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
+                        -- Physical copies
+                        SELECT  unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
+                        FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
+                        UNION ALL
+                        -- Located URIs
+                        SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
+                        FROM evergreen.located_uris($1, $2, $9) AS uris
+                     )x)
+                 ),
+                 CASE WHEN ('ssub' = ANY ($5)) THEN 
+                     XMLELEMENT(
+                         name subscriptions,
+                         (SELECT XMLAGG(ssub) FROM (
+                            SELECT  unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
+                              FROM  serial.subscription
+                              WHERE record_entry = $1
+                        )x)
+                     )
+                 ELSE NULL END,
+                 CASE WHEN ('acp' = ANY ($5)) THEN 
+                     XMLELEMENT(
+                         name foreign_copies,
+                         (SELECT XMLAGG(acp) FROM (
+                            SELECT  unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
+                              FROM  biblio.peer_bib_copy_map p
+                                    JOIN asset.copy c ON (p.target_copy = c.id)
+                              WHERE NOT c.deleted AND p.peer_record = $1
+                            LIMIT ($6 -> 'acp')::INT
+                            OFFSET ($7 -> 'acp')::INT
+                        )x)
+                     )
+                 ELSE NULL END
+             );
+$F$ LANGUAGE SQL STABLE;
+
+COMMIT;
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXX.schema.unapi_exclude_invisible_acn.sql b/Open-ILS/src/sql/Pg/upgrade/XXX.schema.unapi_exclude_invisible_acn.sql
deleted file mode 100644 (file)
index cae5216..0000000
+++ /dev/null
@@ -1,143 +0,0 @@
-
-BEGIN;
-
-DROP FUNCTION IF EXISTS 
-    evergreen.ranked_volumes(BIGINT, INT, INT, HSTORE, HSTORE, INT);
-
-CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
-    bibid BIGINT, 
-    ouid INT,
-    depth INT DEFAULT NULL,
-    slimit HSTORE DEFAULT NULL,
-    soffset HSTORE DEFAULT NULL,
-    pref_lib INT DEFAULT NULL,
-    includes TEXT[] DEFAULT NULL::TEXT[]
-) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
-    SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
-        SELECT acn.id, aou.name, acn.label_sortkey,
-            evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
-            RANK() OVER w
-        FROM asset.call_number acn
-            JOIN asset.copy acp ON (acn.id = acp.call_number)
-            JOIN actor.org_unit_descendants( $2, COALESCE(
-                $3, (
-                    SELECT depth
-                    FROM actor.org_unit_type aout
-                        INNER JOIN actor.org_unit ou ON ou_type = aout.id
-                    WHERE ou.id = $2
-                ), $6)
-            ) AS aou ON (acp.circ_lib = aou.id)
-        WHERE acn.record = $1
-            AND acn.deleted IS FALSE
-            AND acp.deleted IS FALSE
-            AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN 
-                EXISTS (
-                    SELECT 1 
-                    FROM asset.opac_visible_copies 
-                    WHERE copy_id = acp.id AND record = acn.record
-                ) ELSE TRUE END
-        GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
-        WINDOW w AS (
-            ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
-        )
-    ) AS ua
-    GROUP BY ua.id, ua.name, ua.label_sortkey
-    ORDER BY rank, ua.name, ua.label_sortkey
-    LIMIT ($4 -> 'acn')::INT
-    OFFSET ($5 -> 'acn')::INT;
-$$
-LANGUAGE SQL STABLE;
-
-CREATE OR REPLACE FUNCTION unapi.holdings_xml (
-    bid BIGINT,
-    ouid INT,
-    org TEXT,
-    depth INT DEFAULT NULL,
-    includes TEXT[] DEFAULT NULL::TEXT[],
-    slimit HSTORE DEFAULT NULL,
-    soffset HSTORE DEFAULT NULL,
-    include_xmlns BOOL DEFAULT TRUE,
-    pref_lib INT DEFAULT NULL
-)
-RETURNS XML AS $F$
-     SELECT  XMLELEMENT(
-                 name holdings,
-                 XMLATTRIBUTES(
-                    CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
-                    CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
-                    (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
-                 ),
-                 XMLELEMENT(
-                     name counts,
-                     (SELECT  XMLAGG(XMLELEMENT::XML) FROM (
-                         SELECT  XMLELEMENT(
-                                     name count,
-                                     XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
-                                 )::text
-                           FROM  asset.opac_ou_record_copy_count($2,  $1)
-                                     UNION
-                         SELECT  XMLELEMENT(
-                                     name count,
-                                     XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
-                                 )::text
-                           FROM  asset.staff_ou_record_copy_count($2, $1)
-                                     UNION
-                         SELECT  XMLELEMENT(
-                                     name count,
-                                     XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
-                                 )::text
-                           FROM  asset.opac_ou_record_copy_count($9,  $1)
-                                     ORDER BY 1
-                     )x)
-                 ),
-                 CASE 
-                     WHEN ('bmp' = ANY ($5)) THEN
-                        XMLELEMENT(
-                            name monograph_parts,
-                            (SELECT XMLAGG(bmp) FROM (
-                                SELECT  unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
-                                  FROM  biblio.monograph_part
-                                  WHERE record = $1
-                            )x)
-                        )
-                     ELSE NULL
-                 END,
-                 XMLELEMENT(
-                     name volumes,
-                     (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
-                        -- Physical copies
-                        SELECT  unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
-                        FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
-                        UNION ALL
-                        -- Located URIs
-                        SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
-                        FROM evergreen.located_uris($1, $2, $9) AS uris
-                     )x)
-                 ),
-                 CASE WHEN ('ssub' = ANY ($5)) THEN 
-                     XMLELEMENT(
-                         name subscriptions,
-                         (SELECT XMLAGG(ssub) FROM (
-                            SELECT  unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
-                              FROM  serial.subscription
-                              WHERE record_entry = $1
-                        )x)
-                     )
-                 ELSE NULL END,
-                 CASE WHEN ('acp' = ANY ($5)) THEN 
-                     XMLELEMENT(
-                         name foreign_copies,
-                         (SELECT XMLAGG(acp) FROM (
-                            SELECT  unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
-                              FROM  biblio.peer_bib_copy_map p
-                                    JOIN asset.copy c ON (p.target_copy = c.id)
-                              WHERE NOT c.deleted AND p.peer_record = $1
-                            LIMIT ($6 -> 'acp')::INT
-                            OFFSET ($7 -> 'acp')::INT
-                        )x)
-                     )
-                 ELSE NULL END
-             );
-$F$ LANGUAGE SQL STABLE;
-
-COMMIT;