Koha VS Home-grown software/database
Good day. I have what I think is a somewhat unusual question about Koha. A couple of years ago my daughter and I created a Library Database for our grade school library (Kindergarten through 8th grade). Since I'm fairly well-versed in the land of Linux, here's the infrastructure that we came up with: Debian (Woody) MySQL Perl The librarian just wanted something to catalog the books. She didn't want to do check-in/check-out. Nor did she want anything more than Title, Author, Keywords, Section (of the library). She also just wanted to do the Fiction part of the library so CallNumber was pretty basic (something like "TWA 3"). During this library-system creation, I stumbled across Koha. I thought that Koha would be a much better choice than rolling my own. Consequently, I spent a fair amount of time trying to figure out how to convert my MySQL DB into Koha's. Unfortunately, I couldn't figure out how to map from my table layout to Koha's table layout. Now, time has passed, the Library DB is all created and in use. (And the librarian really likes it!) Now, of course, the librarian wants a check-in/checkout system. I may be up to the task of modifying my DB and scripts to do this but it seems kind of silly to roll my own when a great system like Koha already exists. So, here's my question, finally..... Is it possible for me to take my data and map it to the Koha layout? That is, if I start with a mysqldump of my data, can I use Perl to mangle it into a mysqldump of data suitable for importing into Koha? Yeah, yeah. I know. *ANYTHING'S* possible. But could someone possibly provide me with enough guidance (or documentation) so that I could do the job? Here's my table layout: CREATE TABLE catalog ( bookidnum smallint(6) NOT NULL auto_increment, title varchar(75) NOT NULL default '', author varchar(30) NOT NULL default '', keywords varchar(50) NOT NULL default '', numcopies tinyint(4) NOT NULL default '1', callnumber varchar(10) NOT NULL default '', hard_paperback char(1) NOT NULL default 'H', section varchar(30) NOT NULL default '', PRIMARY KEY (bookidnum), UNIQUE KEY bookidnum (bookidnum) ) TYPE=MyISAM; Notes: o The bookidnum has nothing to do with the physical book. It's just basically the record number in the DB. o keywords consists of a string that looks something like "cats christmas" or "mississippi" or "berenstain bears" or "dinosaurs holidays". We would really like to keep these keywords because that's one of the ways that the librarian/user can search for books. o numcopies is misnamed and is actually the copy number. The first book is copy #1. The second is copy #2. And so on. o hard_paperback is just "H" or "P". o section looks like one of these: Fiction Easy Fiction Intermediate Fiction Non-Fiction Easy Non-Fiction Intermediate Non-Fiction Biography Easy Holidays YA Caldecott Newbery I realize that I'm asking a lot here. What I'm really looking for is some kind of one-to-one mapping that says something like this: "Your 'bookidnum' field is really field 'bookno' in table 'booktable'" so that I might be able to mangle the data from my output to a new input for Koha (rather than have to re-enter all of this data). FWIW, we have around 6000 books in this DB. I appreciate any guidance that I can get! Thanks! - Bill Benedetto
On Thu, Aug 05, 2004 at 04:45:16PM -0400, Bill Benedetto said:
Good day.
G'day
So, here's my question, finally.....
Is it possible for me to take my data and map it to the Koha layout? That is, if I start with a mysqldump of my data, can I use Perl to mangle it into a mysqldump of data suitable for importing into Koha?
Certainly is, Ive spent a lot of time doing things just like this. I think ive done about 6 or 7 conversions now, from other library systems to Koha. And im about to start another one next week.
Yeah, yeah. I know. *ANYTHING'S* possible. But could someone possibly provide me with enough guidance (or documentation) so that I could do the job?
I'll certainly try. Luckily Koha has a set of functions we can use. So basically, what I would do, is not do a dump, but set up a koha install running, leave your database running. And write a script to select the data from your db, and then munge it a bit, and call some of the Koha functions to put it into its database.
Here's my table layout:
CREATE TABLE catalog ( bookidnum smallint(6) NOT NULL auto_increment, title varchar(75) NOT NULL default '', author varchar(30) NOT NULL default '', keywords varchar(50) NOT NULL default '', numcopies tinyint(4) NOT NULL default '1', callnumber varchar(10) NOT NULL default '', hard_paperback char(1) NOT NULL default 'H', section varchar(30) NOT NULL default '', PRIMARY KEY (bookidnum), UNIQUE KEY bookidnum (bookidnum) ) TYPE=MyISAM;
Notes:
o The bookidnum has nothing to do with the physical book. It's just basically the record number in the DB.
So its just the unique identifier?
o keywords consists of a string that looks something like "cats christmas" or "mississippi" or "berenstain bears" or "dinosaurs holidays". We would really like to keep these keywords because that's one of the ways that the librarian/user can search for books.
No worries, we can put this in the notes field, thats search by a keyword search on Koha.
o numcopies is misnamed and is actually the copy number. The first book is copy #1. The second is copy #2. And so on.
Ahh righto, so if you have 3 copies of a book, ull have 3 rows in your database?
o hard_paperback is just "H" or "P".
Sweet, that can map to Itemtype
o section looks like one of these: Fiction Easy Fiction Intermediate Fiction Non-Fiction Easy Non-Fiction Intermediate Non-Fiction Biography Easy Holidays YA Caldecott Newbery
We can stick this in class
I realize that I'm asking a lot here. What I'm really looking for is some kind of one-to-one mapping that says something like this:
"Your 'bookidnum' field is really field 'bookno' in table 'booktable'"
so that I might be able to mangle the data from my output to a new input for Koha (rather than have to re-enter all of this data). FWIW, we have around 6000 books in this DB.
Yep makes sense. Bookidnum would map to itemnumber in the item table it sounds like. But what we dont want to do is just run some inserts. If we use the Koha functions, it builds up the MARC data for us at the same time (neat eh, .. thanks to Paul and the other programmers who worked on the MARC stuff) Heres a snippet of perl, showing how id perhaps do it. Feel free to email me back offlist, or on the koha-devel list and we can get into the nitty gritty. But here it is #!/usr/bin/perl use C4::Context; use C4::Biblio; use DBI; use strict; # get us a connection to the old db. my $dbh=DBI->connect("DBI:$db_driver:$db_name:$db_host",$db_user, $db_passwd); my $query="SELECT * FROM catalog ORDER BY title"; my $sth=$dbh->prepare($query); $sth->execute(); my $title=''; my $biblionumber; while (my $data=$sth->fetchrow_hashref()){ if ($title ne $data->{'title'}){ #its a new book $title=$data->{'title'}; my $biblio= { title => $title, author => $data->{'author'}, notes => $data->{'keywords'}, }; $biblionumber=newbiblio($biblio); } my $biblioitem ={ biblionumber => $biblionumber, itemtype => $data->{'hard_paperback'}, classification => $data->{'section'}, dewey => $data->{'callnumber'}, } $biblioitemnumber = newbiblioitem($biblioitem); my $item = { biblioitemnumber => $biblioitemnumber, biblionumber => $biblionumber, } my $barcode = $data->{'bookidnum'}; newitems($item,($barcode)); } In theory, that should make a biblio for each title, and a biblioitem and item for each copy. I havent tried it, but barring typo's, it oughta work. Hope this is some help Chris -- Chris Cormack Programmer 027 4500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz
Others may correct me, but I found the easiest thing to is to convert my data to MARC format. I'm currently running Debian/Sarge. My experience was not with a database but with, of all things, someone who stored their data in and Excel spreadsheet. First thing I did was to save this as a CSV file. Based on your email, I'm guessing you probably can figure out how to do this with your database. Then I installed the libxml-csv-perl package. I simply used the 3-line "Example #1" from "man XML::CSV" against my CVS file to convert it to XML. From there, I installed xalan and created an XSL file to map my new XML file to XML that conforms to MARC XML (http://www.loc.gov/MARC21/slim). Finally, I took the MARC XML file used libmarc-xml-perl_0.65-1_all.deb to turn it into a traditional MARC record. This was not in Debian/Sarge, but it was easily made into a package using dh-make-perl. I know this sounds like many steps, but it was fairly easy, since I didn't have to do any programming. The hardest thing to was the logical mapping into MARC records. On Thu, Aug 05, 2004 at 04:45:16PM -0400, Bill Benedetto wrote:
Good day.
I have what I think is a somewhat unusual question about Koha.
A couple of years ago my daughter and I created a Library Database for our grade school library (Kindergarten through 8th grade).
Since I'm fairly well-versed in the land of Linux, here's the infrastructure that we came up with:
Debian (Woody) MySQL Perl
The librarian just wanted something to catalog the books. She didn't want to do check-in/check-out. Nor did she want anything more than Title, Author, Keywords, Section (of the library). She also just wanted to do the Fiction part of the library so CallNumber was pretty basic (something like "TWA 3").
During this library-system creation, I stumbled across Koha. I thought that Koha would be a much better choice than rolling my own. Consequently, I spent a fair amount of time trying to figure out how to convert my MySQL DB into Koha's.
Unfortunately, I couldn't figure out how to map from my table layout to Koha's table layout.
Now, time has passed, the Library DB is all created and in use. (And the librarian really likes it!) Now, of course, the librarian wants a check-in/checkout system. I may be up to the task of modifying my DB and scripts to do this but it seems kind of silly to roll my own when a great system like Koha already exists.
So, here's my question, finally.....
Is it possible for me to take my data and map it to the Koha layout? That is, if I start with a mysqldump of my data, can I use Perl to mangle it into a mysqldump of data suitable for importing into Koha?
Yeah, yeah. I know. *ANYTHING'S* possible. But could someone possibly provide me with enough guidance (or documentation) so that I could do the job?
Here's my table layout:
CREATE TABLE catalog ( bookidnum smallint(6) NOT NULL auto_increment, title varchar(75) NOT NULL default '', author varchar(30) NOT NULL default '', keywords varchar(50) NOT NULL default '', numcopies tinyint(4) NOT NULL default '1', callnumber varchar(10) NOT NULL default '', hard_paperback char(1) NOT NULL default 'H', section varchar(30) NOT NULL default '', PRIMARY KEY (bookidnum), UNIQUE KEY bookidnum (bookidnum) ) TYPE=MyISAM;
Notes:
o The bookidnum has nothing to do with the physical book. It's just basically the record number in the DB.
o keywords consists of a string that looks something like "cats christmas" or "mississippi" or "berenstain bears" or "dinosaurs holidays". We would really like to keep these keywords because that's one of the ways that the librarian/user can search for books.
o numcopies is misnamed and is actually the copy number. The first book is copy #1. The second is copy #2. And so on.
o hard_paperback is just "H" or "P".
o section looks like one of these: Fiction Easy Fiction Intermediate Fiction Non-Fiction Easy Non-Fiction Intermediate Non-Fiction Biography Easy Holidays YA Caldecott Newbery
I realize that I'm asking a lot here. What I'm really looking for is some kind of one-to-one mapping that says something like this:
"Your 'bookidnum' field is really field 'bookno' in table 'booktable'"
so that I might be able to mangle the data from my output to a new input for Koha (rather than have to re-enter all of this data). FWIW, we have around 6000 books in this DB.
I appreciate any guidance that I can get!
Thanks!
- Bill Benedetto _______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- Encrypted Mail Preferred: Key ID: 8527B9AF Key Fingerprint: E1B6 40B6 B73F 695E 0D3B 644E 6427 DD74 8527 B9AF Information: http://www.gnupg.org/ ASCII ribbon campaign: () against HTML email /\ against Microsoft attachments Information: http://www.expita.com/nomime.html
participants (3)
-
Bill Benedetto -
Chris Cormack -
David Everly