Migrating your call numbers, copies, and parts

Holdings, comprised of call numbers, copies, and parts, are the set of
objects that enable users to locate and potentially acquire materials from your
library system.

Call numbers connect libraries to bibliographic records. Each call number has a
label associated with a classification scheme such as a the Library of Congress
or Dewey Decimal systems, and can optionally have either or both a label prefix
and a label suffix. Label prefixes and suffixes do not affect the sort order of
the label.

Copies connect call numbers to particular instances of that resource at a
particular library. Each copy has a barcode and must exist in a particular copy
location. Other optional attributes of copies include circulation modifier,
which may affect whether that copy can circulate or for how long it can
circulate, and OPAC visibility, which controls whether that particular copy
should be visible in the public catalog.

Parts provide more granularity for copies, primarily to enable patrons to
place holds on individual parts of a set of items. For example, an encyclopedia
might be represented by a single bibliographic record, with a single call
number representing the label for that encyclopedia at a given library, with 26
copies representing each letter of the alphabet, with each copy mapped to a
different part such as A, B, C, … Z.

To migrate this data into your Evergreen system, you will create another
staging table in the database to hold the raw data for your materials from
which the actual call numbers, copies, and parts will be generated.

Begin by connecting to the PostgreSQL database using the psql command. For
example:

psql -U <user-name> -h <hostname> -d <database>

Create the staging materials table by issuing the following SQL statement:

For the purposes of this example migration of call numbers, copies, and parts,
we assume that you are able to create a tab-delimited file containing values
that map to the staging table properties, with one copy per line. For example,
the following 5 lines demonstrate how the file could look for 5 different
copies, with non-applicable attribute values represented by \N, and 3 of the
copies connected to a single call number and bibliographic record via parts:

Once your holdings are in a tab-delimited format—which, for the purposes of
this example, we will name holdings.tsv--you can import the holdings file
into your staging table. Copy the contents of the holdings file into the
staging table using the COPY SQL statement:

Generate the parts for your holdings. First, create the set of parts that are
required for each record based on your staging materials table:

INSERT INTO biblio.monograph_part (record, label)
SELECT DISTINCT bibkey, part
FROM staging_materials sm
WHERE part IS NOT NULL AND NOT EXISTS (
SELECT 1 FROM biblio.monograph_part bmp
WHERE sm.part = bmp.label
AND sm.bibkey = bmp.record
);

Now map the parts for each record to the specific copies that you added:

INSERT INTO asset.copy_part_map (target_copy, part)
SELECT DISTINCT acp.id, bmp.id
FROM staging_materials sm
INNER JOIN asset.copy acp
ON acp.barcode = sm.barcode
INNER JOIN biblio.monograph_part bmp
ON bmp.record = sm.bibkey
WHERE part IS NOT NULL
AND part = bmp.label
AND acp.deleted IS FALSE
AND NOT EXISTS (
SELECT 1 FROM asset.copy_part_map
WHERE target_copy = acp.id
AND part = bmp.id
);

At this point, you have loaded your bibliographic records, call numbers, call
number prefixes and suffixes, copies, and parts, and your records should be
visible to searches in the public catalog within the appropriate organization
unit scope.