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 x 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 |
+------------+-------------+------+-----+---------+----------------+
...
+------------+--------------+------+-----+---------+----------------+
| 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)
These titles should be added to the folio:marcRecords MySQL table (see Parse) from whence a revised version of 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.
So, all that Louisa2folio needs to do is map three fields from Louisa:titles to folio:marcRecords:
Louisa:titles | folio:marcRecords |
---|---|
mrc | marc |
ckey | catalogKey |
createDate (yyyy-mm-dd) | catalogedDate (yyyymmdd) |
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 and/or GovDocs. It wouldn't hurt to investigate that further, later.
Next...
- update the Louisa MARCs with the relevant MARCs from Walter's marc:marcs MySQL table since those MARC records have Sirsi-based 999s : /opt/folio/create/updateMarcRecordsPerLouisa.pl
- cp bibLoad.pl bibLoad4Louisa.pl and have it look in Louisa:items if there is anything there, perhaps cnA, that it needs.
When that's all done the next step will be adding MARCs to FOLIO.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