munge schema

Start a new utility schema called munge:

  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.

First function is a utility to change a copy call number:

  munge.change_copy_call_number(copy_id, new_label);

  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.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>

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;