Loading Louisa's titles and items into FOLIO

Back in the day when our contract with our former ILS vendor limited us to < 500K titles it became necessary to put several non-circulating titles into a separate MySQL db named Louisa.

Louisa has 2 db tables:

mysql> describe titles;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| mrc        | mediumtext  | YES  |     | NULL    |                |
| tcn        | varchar(20) | YES  |     | NULL    |                |
| ckey       | int(11)     | YES  |     | NULL    |                |
| createDate | date        | YES  |     | NULL    |                |
| titleID    | int(11)     | NO   | PRI | NULL    | auto_increment |
+------------+-------------+------+-----+---------+----------------+

mysql> describe items;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| cnA        | varchar(100) | YES  |     | NULL    |                |
| cnD        | varchar(50)  | YES  |     | NULL    |                |
| cnType     | varchar(10)  | YES  |     | NULL    |                |
| collection | varchar(15)  | YES  |     | NULL    |                |
| barcode    | varchar(15)  | YES  |     | NULL    |                |
| hLocation  | varchar(20)  | YES  |     | NULL    |                |
| cLocation  | varchar(10)  | YES  |     | NULL    |                |
| itemType   | varchar(10)  | YES  |     | NULL    |                |
| titleID    | int(11)      | YES  |     | NULL    |                |
| ckey       | int(11)      | YES  |     | NULL    |                |
| createDate | date         | YES  |     | NULL    |                |
| itemID     | int(11)      | NO   | PRI | NULL    | auto_increment |
+------------+--------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)

After these titles and items are in FOLIO their MARCs will need to be re-written with FOLIO 999s and added to the records db table so before loading them into FOLIO they should be added to the folio:marcRecords MySQL table (see Parse) from whence bibLoad.pl should be able to do what needs to be done. So, the trick is going to be to write an /opt/folio/parse/Louisa2folio.pl script to get that job done. Note that Louisa's MARCs do not have 999s - since that is the case it would probably be easier to use the MARC with 999s that can be found in the marcs db table (where the ckey will be the 001) - question: do I need any of the information that is in Louisa:items?

So, all that Louisa2folio needs to do is map three fields from Louisa:titles to folio:marcRecords:

I'm adding a field to Louisa:titles to indicate that the data has been copied to folio:marcRecords : Louisa:titles.copied (Y or NULL)

Louisa2folio.pl is pretty simple:

#! /usr/bin/perl

use DBI;
$dbf = DBI->connect("DBI:mysql:database=folio;host=localhost","perl","t0k3n") or error_exit($DBI::errstr);
$dbl = DBI->connect("DBI:mysql:database=Louisa;host=localhost","perl","t0k3n") or error_exit($DBI::errstr);

for ($x = 0; $x < 240174; $x++) {

$select = $dbl->prepare("select mrc,ckey,createDate,titleID from titles where copied is null limit 1"); $select->execute();
 while (@result = $select->fetchrow()) {
 $mrc = "$result[0]"; $ckey = "$result[1]"; $createDate = "$result[2]"; $titleID = "$result[3]";
 $createDate =~ s/-//g;
 print "$x $titleID $ckey $createDate \n";
 }
 $select->finish();

$insert = $dbf->prepare("insert into marcRecords (marc,catalogedDate,catalogKey) values (?,?,?)");
 $insert->execute($mrc,$createDate,$ckey);
 $insert->finish();

$update = $dbl->prepare("update titles set copied=? where titleID=?");
 $update->execute('Y',$titleID);
 $update->finish();

}

$dbl->disconnect();
$dbf->disconnect();

Only 193,522 of Louisa's 240,175 titles had catalogKeys which suggests that the other 46,653 are eBooks. It wouldn't hurt to investigate that further, later.

Next up: make a copy of bibLoad.pl and tweak it to look to Louisa's items table for all of that 999 info...