From 32e4998b3a92a82ff9c1b400262a7b67fa3240d5 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 2 Apr 2013 17:22:37 -0400 Subject: [PATCH] Copy location sample data additions * added some new locations and better groups (thanks gdunbar) * applying locations to copies in a slightly more sane fashion * added todo comment about possible future improvement Signed-off-by: Bill Erickson Signed-off-by: Kathy Lussier --- Open-ILS/tests/datasets/sql/assets_extras.sql | 98 +++++++++++++++---- 1 file changed, 81 insertions(+), 17 deletions(-) diff --git a/Open-ILS/tests/datasets/sql/assets_extras.sql b/Open-ILS/tests/datasets/sql/assets_extras.sql index b57c0163b6..24609deb13 100644 --- a/Open-ILS/tests/datasets/sql/assets_extras.sql +++ b/Open-ILS/tests/datasets/sql/assets_extras.sql @@ -3,11 +3,27 @@ -- copy stat cats -- ... +-- TODO: +-- This applies locations to copies without regard for the type +-- of record of each copy. This leads to nonsensical copy / location +-- pairings. (E.g. a violin concerto copy with a "Newspapers" location). +-- An improvement would be to create locations first, then select +-- locations for copies as they are inserted. Time will tell if there will +-- ever be a large enough variety of data to do this in a meaningful way. +-- Also, it's kind of a pain, so, maybe later.. + INSERT INTO asset.copy_location (owning_lib, name) VALUES +(1, 'Reference'), +(4, 'Reference'), +(4, 'Easy Reader'), +(5, 'Easy Reader'), +(6, 'Easy Reader'), (2, 'Fiction'), (3, 'Fiction'), (2, 'Non-Fiction'), (3, 'Non-Fiction'), +(2, 'Juvenile Non-Fiction'), +(3, 'Juvenile Non-Fiction'), (2, 'Young Adult Fiction'), (4, 'Young Adult Fiction'), (5, 'Young Adult Fiction'), @@ -26,7 +42,29 @@ INSERT INTO asset.copy_location (owning_lib, name) VALUES (4, 'Biography'), (5, 'Biography'), (6, 'Local History'), -(7, 'Local History'); +(7, 'Local History'), +(6, 'Federal Documents'); + + +-- non-holdable, non-circulating +INSERT INTO asset.copy_location + (owning_lib, name, holdable, circulate) VALUES +(4, 'Periodicals', FALSE, FALSE), +(6, 'Periodicals', FALSE, FALSE), +(5, 'Magazines', FALSE, FALSE), +(7, 'Magazines', FALSE, FALSE), +(4, 'Newspapers', FALSE, FALSE); + +-- non-holdable +INSERT INTO asset.copy_location (owning_lib, name, holdable) VALUES +(4, 'Reserves', FALSE), +(5, 'Reserves', FALSE), +(3, 'Reserves', FALSE), +(5, 'Theses', FALSE), +(7, 'Theses', FALSE), +(2, 'Special Collections', FALSE), +(6, 'Special Collections', FALSE), +(7, 'Special Collections', FALSE); -- different settings per org level @@ -44,19 +82,34 @@ INSERT INTO asset.copy_location -- copy location groups -INSERT INTO asset.copy_location_group (name, owner) VALUES ('Sys1 Fiction', 2); +INSERT INTO asset.copy_location_group (name, owner) + VALUES ('Juvenile Collection', 2); INSERT INTO asset.copy_location_group_map (lgroup, location) SELECT CURRVAL('asset.copy_location_group_id_seq'), id FROM asset.copy_location - WHERE owning_lib in (2, 4, 5) AND opac_visible; + WHERE owning_lib IN (2, 4, 5) AND + opac_visible AND + name IN ( + 'Young Adult Fiction', + 'Children''s Fiction', + 'Easy Reader', + 'Juvenile Non-Fiction' + ); -INSERT INTO asset.copy_location_group (name, owner) VALUES ('Sys2 Fiction', 2); +INSERT INTO asset.copy_location_group (name, owner) + VALUES ('Local Interest Collection', 3); INSERT INTO asset.copy_location_group_map (lgroup, location) SELECT CURRVAL('asset.copy_location_group_id_seq'), id FROM asset.copy_location - WHERE owning_lib in (3, 6, 7) AND opac_visible; + WHERE owning_lib IN (3, 6, 7) AND + opac_visible AND + name IN ( + 'Geneology', + 'Special Collections', + 'Local History' + ); -- Distribute copies evenly across copy locations whose owning_lib -- matches the copy circ lib. To provide some level of repeatable @@ -65,28 +118,39 @@ INSERT INTO asset.copy_location_group_map (lgroup, location) DO $$ DECLARE cur_loc INTEGER; DECLARE cur_copy asset.copy%ROWTYPE; + DECLARE cur_cn INTEGER; BEGIN cur_loc := 0; + cur_cn := 0; FOR cur_copy IN SELECT * FROM asset.copy - WHERE location = 1 ORDER BY circ_lib, id LOOP + WHERE location = 1 ORDER BY circ_lib, call_number, id LOOP - -- find the next location for the current copy's circ lib - SELECT INTO cur_loc id FROM asset.copy_location - WHERE owning_lib = cur_copy.circ_lib AND id > cur_loc - ORDER BY id LIMIT 1; + -- Move to the next copy location if we are changing call numbers. + -- This provides some visual consistency between call numbers and + -- copy locations and helps avoid having practically every copy in + -- view residing in a different location. + IF cur_cn <> cur_copy.call_number THEN - IF NOT FOUND THEN - -- start back over at the front of the list - cur_loc := 0; + -- find the next location for the current copy's circ lib SELECT INTO cur_loc id FROM asset.copy_location WHERE owning_lib = cur_copy.circ_lib AND id > cur_loc ORDER BY id LIMIT 1; - END IF; - IF NOT FOUND THEN - -- no copy location at this circ lib, leave the default (1) - CONTINUE; + IF NOT FOUND THEN + -- start back over at the front of the list + cur_loc := 0; + SELECT INTO cur_loc id FROM asset.copy_location + WHERE owning_lib = cur_copy.circ_lib AND id > cur_loc + ORDER BY id LIMIT 1; + END IF; + + IF NOT FOUND THEN + -- no copy location at this circ lib, leave the default (1) + CONTINUE; + END IF; + + cur_cn := cur_copy.call_number; END IF; UPDATE asset.copy SET location = cur_loc WHERE id = cur_copy.id; -- 2.43.2