X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=656015ef7009b12b018165729806c93efba9bf32;hp=06bebff2fd2b8194cacecc48ba3f5ce4943978fd;hb=0b30967a52114d24dbe09fe07e5df77090a2bf4d;hpb=bbc5f8afa530f385f185e089d87749a5ad0b9c3c

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 06bebff..656015e 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -200,6 +200,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
+        PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' );
@@ -650,24 +651,749 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
-CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
+CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
     DECLARE
         attempt_value ALIAS FOR $1;
         fail_value ALIAS FOR $2;
-        output NUMERIC(6,2);
+        output TIMESTAMPTZ;
     BEGIN
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(6,2) AS a;'
+            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
         LOOP
             RETURN output;
         END LOOP;
     EXCEPTION
         WHEN OTHERS THEN
             FOR output IN
-                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
             LOOP
                 RETURN output;
             END LOOP;
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(8,2);
+    BEGIN
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+-- add_codabar_checkdigit
+--   $barcode      source barcode
+--
+-- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
+-- character with a checkdigit computed according to the usual algorithm for library barcodes
+-- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
+-- input string does not meet those requirements, it is returned unchanged.
+--
+CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
+    my $barcode = shift;
+
+    return $barcode if $barcode !~ /^\d{13,14}$/;
+    $barcode = substr($barcode, 0, 13); # ignore 14th digit
+    my @digits = split //, $barcode;
+    my $total = 0;
+    $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
+    $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
+    my $remainder = $total % 10;
+    my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
+    return $barcode . $checkdigit; 
+$$ LANGUAGE PLPERL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
+  DECLARE
+    phone TEXT := $1;
+    areacode TEXT := $2;
+    temp TEXT := '';
+    output TEXT := '';
+    n_digits INTEGER := 0;
+  BEGIN
+    temp := phone;
+    temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
+    temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
+    n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
+    IF n_digits = 7 AND areacode <> '' THEN
+      temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
+      output := (areacode || '-' || temp);
+    ELSE
+      output := temp;
+    END IF;
+    RETURN output;
+  END;
+
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
+  my ($marcxml, $pos, $value) = @_;
+
+  use MARC::Record;
+  use MARC::File::XML;
+
+  my $xml = $marcxml;
+  eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+    my $leader = $marc->leader();
+    substr($leader, $pos, 1) = $value;
+    $marc->leader($leader);
+    $xml = $marc->as_xml_record;
+    $xml =~ s/^<\?.+?\?>$//mo;
+    $xml =~ s/\n//sgo;
+    $xml =~ s/>\s+</></sgo;
+  };
+  return $xml;
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
+  my ($marcxml, $pos, $value) = @_;
+
+  use MARC::Record;
+  use MARC::File::XML;
+
+  my $xml = $marcxml;
+  eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+    my $f008 = $marc->field('008');
+
+    if ($f008) {
+       my $field = $f008->data();
+       substr($field, $pos, 1) = $value;
+       $f008->update($field);
+       $xml = $marc->as_xml_record;
+       $xml =~ s/^<\?.+?\?>$//mo;
+       $xml =~ s/\n//sgo;
+       $xml =~ s/>\s+</></sgo;
+    }
+  };
+  return $xml;
+$$ LANGUAGE PLPERLU STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
+  DECLARE
+    profile ALIAS FOR $1;
+  BEGIN
+    RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
+  END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
+  BEGIN
+    RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
+  END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
+
+  my ($marcxml, $tags) = @_;
+
+  use MARC::Record;
+  use MARC::File::XML;
+
+  my $xml = $marcxml;
+
+  eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+    my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
+
+    my @incumbents = ();
+
+    foreach my $field ( $marc->fields() ) {
+      push @incumbents, $field->as_formatted();
+    }
+
+    foreach $field ( $to_insert->fields() ) {
+      if (!grep {$_ eq $field->as_formatted()} @incumbents) {
+        $marc->insert_fields_ordered( ($field) );
+      }
+    }
+
+    $xml = $marc->as_xml_record;
+    $xml =~ s/^<\?.+?\?>$//mo;
+    $xml =~ s/\n//sgo;
+    $xml =~ s/>\s+</></sgo;
+  };
+
+  return $xml;
+
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
+
+-- Usage:
+--
+--   First make sure the circ matrix is loaded and the circulations
+--   have been staged to the extent possible (but at the very least
+--   circ_lib, target_copy, usr, and *_renewal).  User profiles and
+--   circ modifiers must also be in place.
+--
+--   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
+--
+
+DECLARE
+  circ_lib             INT;
+  target_copy          INT;
+  usr                  INT;
+  is_renewal           BOOLEAN;
+  this_duration_rule   INT;
+  this_fine_rule       INT;
+  this_max_fine_rule   INT;
+  rcd                  config.rule_circ_duration%ROWTYPE;
+  rrf                  config.rule_recurring_fine%ROWTYPE;
+  rmf                  config.rule_max_fine%ROWTYPE;
+  circ                 INT;
+  n                    INT := 0;
+  n_circs              INT;
+  
+BEGIN
+
+  EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
+
+  FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
+
+    -- Fetch the correct rules for this circulation
+    EXECUTE ('
+      SELECT
+        circ_lib,
+        target_copy,
+        usr,
+        CASE
+          WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
+          ELSE FALSE
+        END
+      FROM ' || tablename || ' WHERE id = ' || circ || ';')
+      INTO circ_lib, target_copy, usr, is_renewal ;
+    SELECT
+      INTO this_duration_rule,
+           this_fine_rule,
+           this_max_fine_rule
+      duration_rule,
+      recurring_fine_rule,
+      max_fine_rule
+      FROM action.find_circ_matrix_matchpoint(
+        circ_lib,
+        target_copy,
+        usr,
+        is_renewal
+        );
+    SELECT INTO rcd * FROM config.rule_circ_duration
+      WHERE id = this_duration_rule;
+    SELECT INTO rrf * FROM config.rule_recurring_fine
+      WHERE id = this_fine_rule;
+    SELECT INTO rmf * FROM config.rule_max_fine
+      WHERE id = this_max_fine_rule;
+
+    -- Apply the rules to this circulation
+    EXECUTE ('UPDATE ' || tablename || ' c
+    SET
+      duration_rule = rcd.name,
+      recurring_fine_rule = rrf.name,
+      max_fine_rule = rmf.name,
+      duration = rcd.normal,
+      recurring_fine = rrf.normal,
+      max_fine =
+        CASE rmf.is_percent
+          WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
+          ELSE rmf.amount
+        END,
+      renewal_remaining = rcd.max_renewals
+    FROM
+      config.rule_circ_duration rcd,
+      config.rule_recurring_fine rrf,
+      config.rule_max_fine rmf,
+                        asset.copy ac
+    WHERE
+      rcd.id = ' || this_duration_rule || ' AND
+      rrf.id = ' || this_fine_rule || ' AND
+      rmf.id = ' || this_max_fine_rule || ' AND
+                        ac.id = c.target_copy AND
+      c.id = ' || circ || ';');
+
+    -- Keep track of where we are in the process
+    n := n + 1;
+    IF (n % 100 = 0) THEN
+      RAISE INFO '%', n || ' of ' || n_circs
+        || ' (' || (100*n/n_circs) || '%) circs updated.';
+    END IF;
+
+  END LOOP;
+
+  RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
+--        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
+
+-- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
+-- TODO: Add a similar tool for actor stat cats, which behave differently.
+
+DECLARE
+	c                    TEXT := schemaname || '.asset_copy_legacy';
+	sc									 TEXT := schemaname || '.asset_stat_cat';
+	sce									 TEXT := schemaname || '.asset_stat_cat_entry';
+	scecm								 TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
+	stat_cat						 INT;
+  stat_cat_entry       INT;
+  
+BEGIN
+
+  FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
+
+		EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
+
+		EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
+							SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
+							(SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
+
+  END LOOP;
+
+  RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
+
+-- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
+--        This will assign standing penalties as needed.
+
+DECLARE
+  org_unit  INT;
+  usr       INT;
+
+BEGIN
+
+  FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
+
+    FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
+  
+      EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
+
+    END LOOP;
+
+  END LOOP;
+
+  RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
+
+BEGIN
+  INSERT INTO metabib.metarecord (fingerprint, master_record)
+    SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+      FROM  biblio.record_entry b
+      WHERE NOT b.deleted
+        AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
+        AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+      ORDER BY b.fingerprint, b.quality DESC;
+  INSERT INTO metabib.metarecord_source_map (metarecord, source)
+    SELECT  m.id, r.id
+      FROM  biblio.record_entry r
+      JOIN  metabib.metarecord m USING (fingerprint)
+     WHERE  NOT r.deleted;
+END;
+  
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
+
+BEGIN
+  INSERT INTO metabib.metarecord (fingerprint, master_record)
+    SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+      FROM  biblio.record_entry b
+      WHERE NOT b.deleted
+        AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
+        AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+      ORDER BY b.fingerprint, b.quality DESC;
+  INSERT INTO metabib.metarecord_source_map (metarecord, source)
+    SELECT  m.id, r.id
+      FROM  biblio.record_entry r
+        JOIN metabib.metarecord m USING (fingerprint)
+      WHERE NOT r.deleted
+        AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
+END;
+    
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
+--        Then SELECT migration_tools.create_cards('m_foo');
+
+DECLARE
+	u                    TEXT := schemaname || '.actor_usr_legacy';
+	c                    TEXT := schemaname || '.actor_card';
+  
+BEGIN
+
+	EXECUTE ('DELETE FROM ' || c || ';');
+	EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
+	EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
+
+  RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
+
+  ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
+
+  my ($marcxml, $shortname) = @_;
+
+  use MARC::Record;
+  use MARC::File::XML;
+
+  my $xml = $marcxml;
+
+  eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+
+    foreach my $field ( $marc->field('856') ) {
+      if ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
+           ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
+        $field->add_subfields( '9' => $shortname );
+				$field->update( ind2 => '0');
+      }
+    }
+
+    $xml = $marc->as_xml_record;
+    $xml =~ s/^<\?.+?\?>$//mo;
+    $xml =~ s/\n//sgo;
+    $xml =~ s/>\s+</></sgo;
+  };
+
+  return $xml;
+
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT) RETURNS VOID AS $$
+
+DECLARE
+  old_volume   BIGINT;
+  new_volume   BIGINT;
+  bib          BIGINT;
+  owner        INTEGER;
+  old_label    TEXT;
+  remainder    BIGINT;
+
+BEGIN
+
+  -- Bail out if asked to change the label to ##URI##
+  IF new_label = '##URI##' THEN
+    RETURN;
+  END IF;
+
+  -- Gather information
+  SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
+  SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
+  SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
+
+  -- Bail out if the label already is ##URI##
+  IF old_label = '##URI##' THEN
+    RETURN;
+  END IF;
+
+  -- Bail out if the call number label is already correct
+  IF new_volume = old_volume THEN
+    RETURN;
+  END IF;
+
+  -- Check whether we already have a destination volume available
+  SELECT id INTO new_volume FROM asset.call_number 
+    WHERE 
+      record = bib AND
+      owning_lib = owner AND
+      label = new_label AND
+      NOT deleted;
+
+  -- Create destination volume if needed
+  IF NOT FOUND THEN
+    INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
+      VALUES (1, 1, bib, owner, new_label);
+    SELECT id INTO new_volume FROM asset.call_number
+      WHERE 
+        record = bib AND
+        owning_lib = owner AND
+        label = new_label AND
+        NOT deleted;
+  END IF;
+
+  -- Move copy to destination
+  UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
+
+  -- Delete source volume if it is now empty
+  SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
+  IF NOT FOUND THEN
+    DELETE FROM asset.call_number WHERE id = old_volume;
+  END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
+
+	my $input = $_[0];
+	my %zipdata;
+
+	open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
+
+	while (<FH>) {
+		chomp;
+		my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
+		$zipdata{$zip} = [$city, $state, $county];
+	}
+
+	if (defined $zipdata{$input}) {
+		my ($city, $state, $county) = @{$zipdata{$input}};
+		return [$city, $state, $county];
+	} elsif (defined $zipdata{substr $input, 0, 5}) {
+		my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
+		return [$city, $state, $county];
+	} else {
+		return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
+	}
+  
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
+
+DECLARE
+  ou  INT;
+	org_unit_depth INT;
+	ou_parent INT;
+	parent_depth INT;
+  errors_found BOOLEAN;
+	ou_shortname TEXT;
+	parent_shortname TEXT;
+	ou_type_name TEXT;
+	parent_type TEXT;
+	type_id INT;
+	type_depth INT;
+	type_parent INT;
+	type_parent_depth INT;
+	proper_parent TEXT;
+
+BEGIN
+
+	errors_found := FALSE;
+
+-- Checking actor.org_unit_type
+
+	FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
+
+		SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
+		SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
+
+		IF type_parent IS NOT NULL THEN
+
+			SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
+
+			IF type_depth - type_parent_depth <> 1 THEN
+				SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
+				SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
+				RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
+					ou_type_name, type_depth, parent_type, type_parent_depth;
+				errors_found := TRUE;
+
+			END IF;
+
+		END IF;
+
+	END LOOP;
+
+-- Checking actor.org_unit
+
+  FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
+
+		SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
+		SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
+		SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
+		SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
+		SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
+		SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
+		SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
+
+		IF ou_parent IS NOT NULL THEN
+
+			IF	(org_unit_depth - parent_depth <> 1) OR (
+				(SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
+			) THEN
+				RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', 
+					ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
+				errors_found := TRUE;
+			END IF;
+
+		END IF;
+
+  END LOOP;
+
+	IF NOT errors_found THEN
+		RAISE INFO 'No errors found.';
+	END IF;
+
+  RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
+
+BEGIN	
+
+	DELETE FROM asset.opac_visible_copies;
+
+	INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
+		SELECT DISTINCT
+			cp.id, cp.circ_lib, cn.record
+		FROM
+			asset.copy cp
+			JOIN asset.call_number cn ON (cn.id = cp.call_number)
+			JOIN actor.org_unit a ON (cp.circ_lib = a.id)
+			JOIN asset.copy_location cl ON (cp.location = cl.id)
+			JOIN config.copy_status cs ON (cp.status = cs.id)
+			JOIN biblio.record_entry b ON (cn.record = b.id)
+		WHERE 
+			NOT cp.deleted AND
+			NOT cn.deleted AND
+			NOT b.deleted AND
+			cs.opac_visible AND
+			cl.opac_visible AND
+			cp.opac_visible AND
+			a.opac_visible AND
+			cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
+
+DECLARE
+  old_volume     BIGINT;
+  new_volume     BIGINT;
+  bib            BIGINT;
+  old_owning_lib INTEGER;
+	old_label      TEXT;
+  remainder      BIGINT;
+
+BEGIN
+
+  -- Gather information
+  SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
+  SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
+  SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
+
+	-- Bail out if the new_owning_lib is not the ID of an org_unit
+	IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
+		RAISE WARNING 
+			'% is not a valid actor.org_unit ID; no change made.', 
+				new_owning_lib;
+		RETURN;
+	END IF;
+
+  -- Bail out discreetly if the owning_lib is already correct
+  IF new_owning_lib = old_owning_lib THEN
+    RETURN;
+  END IF;
+
+  -- Check whether we already have a destination volume available
+  SELECT id INTO new_volume FROM asset.call_number 
+    WHERE 
+      record = bib AND
+      owning_lib = new_owning_lib AND
+      label = old_label AND
+      NOT deleted;
+
+  -- Create destination volume if needed
+  IF NOT FOUND THEN
+    INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
+      VALUES (1, 1, bib, new_owning_lib, old_label);
+    SELECT id INTO new_volume FROM asset.call_number
+      WHERE 
+        record = bib AND
+        owning_lib = new_owning_lib AND
+        label = old_label AND
+        NOT deleted;
+  END IF;
+
+  -- Move copy to destination
+  UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
+
+  -- Delete source volume if it is now empty
+  SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
+  IF NOT FOUND THEN
+    DELETE FROM asset.call_number WHERE id = old_volume;
+  END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
+
+-- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
+
+DECLARE
+	new_owning_lib	INTEGER;
+
+BEGIN
+
+	-- Parse the new_owner as an org unit ID or shortname
+	IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
+		SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
+		PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
+	ELSIF new_owner ~ E'^[0-9]+$' THEN
+		IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
+			RAISE INFO 
+				'%',
+				E'You don\'t need to put the actor.org_unit ID in quotes; '
+					|| E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
+			new_owning_lib := new_owner::INTEGER;
+		PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
+		END IF;
+	ELSE
+		RAISE WARNING 
+			'% is not a valid actor.org_unit shortname or ID; no change made.', 
+			new_owning_lib;
+		RETURN;
+	END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+