munge schema

[evergreen-equinox.git] / Open-ILS / src / sql / Pg / 991.schema.munge.sql

diff --git a/Open-ILS/src/sql/Pg/991.schema.munge.sql b/Open-ILS/src/sql/Pg/991.schema.munge.sql
new file mode 100644 (file)
index 0000000..ee6992f
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/991.schema.munge.sql
@@ -0,0 +1,130 @@
+/*
+ * Copyright (C) 2011  Equinox Software, Inc.
+ * Galen Charlton <gmc@esilibrary.com>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ *
+ */
+
+DROP SCHEMA IF EXISTS munge CASCADE;
+
+BEGIN;
+
+CREATE SCHEMA munge;
+
+COMMENT ON SCHEMA munge IS $$
+munge - Evergreen database utilities
+
+The schema munge contains stored procedures used for maintaining
+an Evergreen database, including migration and data update
+routines.  Routines in munge will not necessarily have a
+user interface exposed in the Evergreen staff client or OPAC,
+and will typically be invoked from the command line.  However,
+other Evergreen application code is free to make use of munge.
+$$;
+
+-- original version of routine proposed by Ben Ostrowsky on 2011-04-13
+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 $$
+DECLARE
+    current_acn     asset.call_number%ROWTYPE;
+    new_acn_id      asset.call_number.id%TYPE;
+BEGIN
+ 
+    -- bail out if new label is invalid; we will not touch
+    -- located URIs in this routine
+    IF new_label = '##URI##' OR new_label IS NULL THEN
+        RETURN false;
+    END IF;
+ 
+    -- get current volume
+    SELECT acn.* INTO current_acn
+        FROM asset.copy ac
+        JOIN asset.call_number acn ON (acn.id = ac.call_number)
+        WHERE ac.id = copy_id;
+    IF NOT FOUND THEN
+        RETURN false;
+    END IF;
+
+    -- bail out if the label already is ##URI##
+    IF current_acn.label = '##URI##' THEN
+        RETURN false;
+    END IF;
+
+    -- bail out if the call number label is already correct
+    IF current_acn.label = new_label THEN
+        RETURN false;
+    END IF;
+
+    -- bail out if we're a precat
+    IF current_acn.id = -1 THEN
+        RETURN false;
+    END IF;
+ 
+    -- Check whether we already have a destination volume available
+    SELECT id INTO new_acn_id FROM asset.call_number 
+    WHERE 
+        record     = current_acn.record AND
+        owning_lib = current_acn.owning_lib AND
+        label      = new_label AND
+        NOT deleted;
+
+    -- Create destination volume if needed
+    IF NOT FOUND THEN
+        -- if at least on other item is attached to the current volume, create a
+        -- new volume so as to not change the the other item's call number label, otherwise
+        -- change the label in place
+        PERFORM 1 FROM asset.copy WHERE NOT deleted AND id <> copy_id AND call_number = current_acn.id;
+        IF FOUND THEN
+            INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
+                VALUES (1, 1, current_acn.record, current_acn.owning_lib, new_label);
+            SELECT CURRVAL('asset.call_number_id_seq') INTO new_acn_id;
+        ELSE
+            UPDATE asset.call_number
+                SET editor = 1,
+                    label = new_label,
+                    edit_date = NOW()
+                WHERE id = current_acn.id;
+        END IF;
+    END IF;
+
+    IF new_acn_id IS NOT NULL THEN
+        -- Move copy to destination
+        UPDATE asset.copy SET call_number = new_acn_id WHERE id = copy_id;
+
+        -- Delete source volume if it is now empty
+        PERFORM 1 FROM asset.copy WHERE call_number = current_acn.id AND NOT deleted;
+        IF NOT FOUND THEN
+            DELETE FROM asset.call_number WHERE id = current_acn.id;
+        END IF;
+    END IF;
+
+    RETURN true;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION munge.change_copy_call_number(asset.copy.id%TYPE, asset.call_number.label%TYPE) IS $$
+Change the call number label associated with the specified item,
+creating, relinking, and deleting volume records as needed.  The
+call number associated with any other items attached to the target
+item's original volume will remain unchanged.
+
+Accepts the following parameters:
+
+copy_id   - asset.copy.id of the copy to change
+new_label - new call number string
+
+The return value is a boolean indicating whether the copy call
+number was changed or not.
+
+This routine will refuse to touch or create located URIs.
+$$;
+
+COMMIT;