2 * Copyright (C) 2011 Equinox Software, Inc.
3 * Galen Charlton <gmc@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
17 DROP SCHEMA IF EXISTS munge CASCADE;
23 COMMENT ON SCHEMA munge IS $$
24 munge - Evergreen database utilities
26 The schema munge contains stored procedures used for maintaining
27 an Evergreen database, including migration and data update
28 routines. Routines in munge will not necessarily have a
29 user interface exposed in the Evergreen staff client or OPAC,
30 and will typically be invoked from the command line. However,
31 other Evergreen application code is free to make use of munge.
34 -- original version of routine proposed by Ben Ostrowsky on 2011-04-13
35 CREATE OR REPLACE FUNCTION munge.change_copy_call_number(copy_id asset.copy.id%TYPE, new_label asset.call_number.label%TYPE) RETURNS BOOLEAN AS $$
37 current_acn asset.call_number%ROWTYPE;
38 new_acn_id asset.call_number.id%TYPE;
41 -- bail out if new label is invalid; we will not touch
42 -- located URIs in this routine
43 IF new_label = '##URI##' OR new_label IS NULL THEN
48 SELECT acn.* INTO current_acn
50 JOIN asset.call_number acn ON (acn.id = ac.call_number)
51 WHERE ac.id = copy_id;
56 -- bail out if the label already is ##URI##
57 IF current_acn.label = '##URI##' THEN
61 -- bail out if the call number label is already correct
62 IF current_acn.label = new_label THEN
66 -- bail out if we're a precat
67 IF current_acn.id = -1 THEN
71 -- Check whether we already have a destination volume available
72 SELECT id INTO new_acn_id FROM asset.call_number
74 record = current_acn.record AND
75 owning_lib = current_acn.owning_lib AND
79 -- Create destination volume if needed
81 -- if at least on other item is attached to the current volume, create a
82 -- new volume so as to not change the the other item's call number label, otherwise
83 -- change the label in place
84 PERFORM 1 FROM asset.copy WHERE NOT deleted AND id <> copy_id AND call_number = current_acn.id;
86 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
87 VALUES (1, 1, current_acn.record, current_acn.owning_lib, new_label);
88 SELECT CURRVAL('asset.call_number_id_seq') INTO new_acn_id;
90 UPDATE asset.call_number
94 WHERE id = current_acn.id;
98 IF new_acn_id IS NOT NULL THEN
99 -- Move copy to destination
100 UPDATE asset.copy SET call_number = new_acn_id WHERE id = copy_id;
102 -- Delete source volume if it is now empty
103 PERFORM 1 FROM asset.copy WHERE call_number = current_acn.id AND NOT deleted;
105 DELETE FROM asset.call_number WHERE id = current_acn.id;
113 COMMENT ON FUNCTION munge.change_copy_call_number(asset.copy.id%TYPE, asset.call_number.label%TYPE) IS $$
114 Change the call number label associated with the specified item,
115 creating, relinking, and deleting volume records as needed. The
116 call number associated with any other items attached to the target
117 item's original volume will remain unchanged.
119 Accepts the following parameters:
121 copy_id - asset.copy.id of the copy to change
122 new_label - new call number string
124 The return value is a boolean indicating whether the copy call
125 number was changed or not.
127 This routine will refuse to touch or create located URIs.