From 406d79e765a2ad318af1005f36d4dd983761c9b7 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 18 Mar 2013 11:01:36 -0400 Subject: [PATCH] LP#1155771 Copy location sample data additions * Add/remove some locations * Add some locations whose flags (circulate, etc.) vary along the org pathway. Thanks to Thomas Berezansky for this suggestion. * Ensure that copy location owner matches copy circ lib when applying locations to copies. Signed-off-by: Bill Erickson Signed-off-by: Kathy Lussier --- Open-ILS/tests/datasets/sql/assets_extras.sql | 102 +++++++++++------- 1 file changed, 64 insertions(+), 38 deletions(-) diff --git a/Open-ILS/tests/datasets/sql/assets_extras.sql b/Open-ILS/tests/datasets/sql/assets_extras.sql index 2efefb982d..b57c0163b6 100644 --- a/Open-ILS/tests/datasets/sql/assets_extras.sql +++ b/Open-ILS/tests/datasets/sql/assets_extras.sql @@ -4,35 +4,43 @@ -- ... INSERT INTO asset.copy_location (owning_lib, name) VALUES -(4, 'Adult'), -(4, 'Young Adult'), -(4, 'Juvenile'), -(4, 'AV'), -(4, 'Children''s AV'), -(5, 'Western'), -(5, 'Young Adult'), +(2, 'Fiction'), +(3, 'Fiction'), +(2, 'Non-Fiction'), +(3, 'Non-Fiction'), +(2, 'Young Adult Fiction'), +(4, 'Young Adult Fiction'), +(5, 'Young Adult Fiction'), +(6, 'Young Adult Fiction'), +(2, 'Children''s Fiction'), +(3, 'Children''s Fiction'), +(4, 'Audio/Video'), +(5, 'Audio/Video'), +(5, 'Music'), +(6, 'Music'), +(6, 'Audio/Video'), +(4, 'Science Fiction'), +(7, 'Science Fiction'), (5, 'Genealogy'), -(5, 'Local History'), -(6, 'Sci-Fi'), -(6, 'Biography'), -(6, 'Ninjas'), -(6, 'Young Adult'), -(7, 'Vampires'), -(7, 'Western'), -(7, 'Young Adult'), -(7, 'Sports'); +(6, 'Genealogy'), +(4, 'Biography'), +(5, 'Biography'), +(6, 'Local History'), +(7, 'Local History'); --- non-holable -INSERT INTO asset.copy_location (owning_lib, name, holdable) VALUES -(4, 'New Arrivals', FALSE); --- non-holable, non-cirulcateable, non-visible +-- different settings per org level INSERT INTO asset.copy_location (owning_lib, name, holdable, opac_visible, circulate) VALUES -(5, 'Display', FALSE, FALSE, FALSE), -(6, 'Display', FALSE, FALSE, FALSE), -(7, 'Display', FALSE, FALSE, FALSE); - +(2, 'Display', FALSE, FALSE, TRUE), +(4, 'Display', FALSE, FALSE, FALSE), +(5, 'Display', TRUE, FALSE, FALSE), +(6, 'Display', TRUE, FALSE, FALSE), +(7, 'Display', FALSE, FALSE, FALSE), +(1, 'New Arrivals', TRUE, TRUE, TRUE), +(2, 'New Arrivals', FALSE, TRUE, TRUE), +(4, 'New Arrivals', TRUE, TRUE, FALSE), +(5, 'New Arrivals', TRUE, TRUE, TRUE); -- copy location groups @@ -40,30 +48,48 @@ INSERT INTO asset.copy_location_group (name, owner) VALUES ('Sys1 Fiction', 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 (4, 5) - AND opac_visible; + FROM asset.copy_location + WHERE owning_lib in (2, 4, 5) AND opac_visible; INSERT INTO asset.copy_location_group (name, owner) VALUES ('Sys2 Fiction', 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 (6, 7) - AND opac_visible; - --- evenly distribute the copies across all of the copy locations. --- there's probably a more effecient way, but this gets the job done + FROM asset.copy_location + WHERE owning_lib in (3, 6, 7) AND opac_visible; +-- Distribute copies evenly across copy locations whose owning_lib +-- matches the copy circ lib. To provide some level of repeatable +-- outcome, we loop instead of applying locations at randon within +-- a given owning_lib. DO $$ DECLARE cur_loc INTEGER; DECLARE cur_copy asset.copy%ROWTYPE; BEGIN - cur_copy := evergreen.next_copy(0); + cur_loc := 0; + + FOR cur_copy IN SELECT * FROM asset.copy + WHERE location = 1 ORDER BY circ_lib, 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; - WHILE cur_copy.id IS NOT NULL LOOP - FOR cur_loc IN SELECT id FROM asset.copy_location ORDER BY id LOOP - UPDATE asset.copy SET location = cur_loc WHERE id = cur_copy.id; - cur_copy := evergreen.next_copy(cur_copy.id); - EXIT WHEN cur_copy.id IS NULL; - END LOOP; + 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; + + UPDATE asset.copy SET location = cur_loc WHERE id = cur_copy.id; END LOOP; END $$; + -- 2.43.2