-#!/usr/bin/perl -w
+#!/usr/bin/perl
-## FIXME: if there is an existing borrowers.db file it needs to be deleted before this script is ran.
+# 2008 Kyle Hall <kyle.m.hall@gmail.com>
+
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+#
+
+=head1 NAME
+
+create_koc_db.pl - create a database file for the offline circulation tool
+
+=head1 SYNOPSIS
+
+create_koc_db.pl
+
+ Options:
+ -help brief help message
+ -man full documentation
+
+=head1 OPTIONS
+
+=over 8
+
+=item B<--help>
+
+Print a brief help message and exits.
+
+=item B<--man>
+
+Prints the manual page and exits.
+
+=item B<--file>
+
+The filename that we should use for the database file that we produce. Defaults to "borrowers.db"
+
+=item B<--force>
+
+Forcefully overwrite any existing db file. Defaults to false, so
+program will terminate prematurely if the file already exists.
+
+=item B<--sqlite2>
+
+This option cause the output file to be an SQLite2 database.
+This is the format that the original offline circ client
+( the one written in PHP/Gtk, version < 1.0 ) requires.
+
+=item B<--sqlite3>
+
+This option cause the output file to be an SQLite3 database.
+This is the format that the new offline circ client
+( the one written in C++/Qt4, version >= 1.0 ) requires.
+
+=back
+
+=head1 DESCRIPTION
+
+This script generates a sqlite database file full of patron and
+holdings data that can be used by an offline circulation tool.
+
+=head1 USAGE EXAMPLES
+
+This program could be run from cron to occasionally refresh the
+offline circulation database. For instance:
+
+C<0 0 * * * create_koc_db.pl>
+
+=head1 SEE ALSO
+
+This program was originally created to interact with Kyle Hall's
+offline circulation tool, which is available from
+L<http://kylehall.info/index.php/projects/koha-tools/koha-offline-circulation/>,
+but any similar tool could use the database that is produced.
+
+=cut
+
+use strict;
+use warnings;
+
+$|++;
use DBI;
+use Getopt::Long;
+use Pod::Usage;
use C4::Context;
+use English qw(-no_match_vars);
-use strict;
+my $verbose = 0;
+my $help = 0;
+my $man = 0;
+my $filename = 'borrowers.db';
+my $force = 0;
+my $sqlite2 = 0;
+my $sqlite3 = 0;
-## Create DB Connections
-my $dbh_mysql = C4::Context->dbh;
-my $dbh_sqlite = DBI->connect("dbi:SQLite:dbname=borrowers.db","","");
+GetOptions(
+ 'verbose' => \$verbose,
+ 'help' => \$help,
+ 'man' => \$man,
+ 'file=s' => \$filename,
+ 'force' => \$force,
+ 'sqlite2' => \$sqlite2,
+ 'sqlite3' => \$sqlite3,
+) or pod2usage(2);
-## Create sqlite borrowers table to mirror the koha borrowers table structure
-my $sth_mysql = $dbh_mysql->prepare("DESCRIBE borrowers");
-$sth_mysql->execute();
+pod2usage(1) if $help;
+pod2usage( -verbose => 2 ) if $man;
-my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
-my $result = $sth_mysql->fetchrow_hashref();
-my $field = $result->{'Field'};
-my $type = $result->{'Type'};
-$sqlite_create_sql .= " $field $type ";
+my %wanted_borrowers_columns = map { $_ => 1 } qw/borrowernumber cardnumber surname firstname address city phone dateofbirth/;
+my %wanted_issues_columns = map { $_ => 1 } qw/borrowernumber date_due itemcallnumber title itemtype/;
-while ( my $result = $sth_mysql->fetchrow_hashref() ) {
- $field = $result->{'Field'};
- $type = $result->{'Type'};
- $sqlite_create_sql .= " , \n $field $type ";
+prepare_file_for_writing($filename)
+ or die "file: '$filename' already exists. Use --force to overwrite\n";
+
+verify_dbd_sqlite();
+
+## Create DB Connections
+my $dbh_mysql = C4::Context->dbh;
+my $dbh_sqlite;
+if ( $sqlite2 ) {
+ $dbh_sqlite = DBI->connect( "dbi:SQLite2:dbname=$filename", "", "" );
+} elsif ( $sqlite3 ) {
+ $dbh_sqlite = DBI->connect( "dbi:SQLite:dbname=$filename", "", "" );
}
-$sth_mysql->finish();
+$dbh_sqlite->{AutoCommit} = 0;
-$sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
-$sqlite_create_sql .= " ) ";
+create_borrowers_table();
+populate_borrowers_table();
-my $sth_sqlite = $dbh_sqlite->prepare( $sqlite_create_sql );
-$sth_sqlite->execute();
-$sth_sqlite->finish();
+create_issues_table();
+populate_issues_table();
-## Import the data from the koha.borrowers table into our sqlite table
-$sth_mysql = $dbh_mysql->prepare("SELECT * FROM borrowers ORDER BY borrowernumber DESC");
-my $fields_count = $sth_mysql->execute();
+=head1 INTERNAL METHODS
-print "Number of Borrowers: $fields_count\n";
+=head2 verify_dbd_sqlite
-while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
- my @keys;
- my @values;
+Since DBD::SQLite is a new prerequisite and an optional one, let's
+make sure we have a new enough version of it.
- print "Working on Borrower # $borrower->{'borrowernumber'} \n";
+=cut
- my $sql = "INSERT INTO borrowers ( ";
-
- my $firstLine = 1;
- foreach my $key (keys %$borrower) {
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+sub verify_dbd_sqlite {
+
+ if ( $sqlite2 ) {
+ eval { require DBD::SQLite2; };
+ if ( $EVAL_ERROR ) {
+ my $msg = <<'END_MESSAGE';
+DBD::SQLite2 is required to generate offline circultion database files, but not found.
+Please install the DBD::SQLite2 perl module. It is available from
+http://search.cpan.org/dist/DBD-SQLite2/ or through the CPAN module.
+END_MESSAGE
+ die $msg;
}
- push( @keys, $key );
- }
-
- $sql .= " ) VALUES ( ";
-
- $firstLine = 1;
- foreach my $key (keys %$borrower) {
- my $data = $borrower->{$key};
-
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+ } elsif ( $sqlite3 ) {
+ eval { require DBD::SQLite; };
+ if ( $EVAL_ERROR ) {
+ my $msg = <<'END_MESSAGE';
+DBD::SQLite3 is required to generate offline circultion database files, but not found.
+Please install the DBD::SQLite3 perl module. It is available from
+http://search.cpan.org/dist/DBD-SQLite3/ or through the CPAN module.
+END_MESSAGE
+ die $msg;
}
- push( @values, $data );
+ } else {
+ die( "Error: execution requires either the option --sqlite2 or --sqlite3. Run with --help for details." );
}
- $sql .= " ) ";
+}
+=head2 prepare_file_for_writing
+
+pass in the filename that we're considering using for the SQLite db.
+
+returns true if we can use it.
-print "\n$sql\n";
+returns false if we can't. For example, if it alredy exists and we
+don't have --force or don't have permissions to unlink it.
- $sth_sqlite = $dbh_sqlite->prepare( $sql );
- $sth_sqlite->execute( @keys, @values );
- $sth_sqlite->finish();
+=cut
+
+sub prepare_file_for_writing {
+ my $filename = shift;
+ if ( -e $filename ) {
+
+ # this file exists. remove it if --force.
+ if ($force) {
+ return unlink $filename;
+ } else {
+ return;
+ }
+ }
+ return $filename;
}
-## Import the fines from koha.accountlines into the sqlite db
-$sth_mysql = $dbh_mysql->prepare( "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
- FROM accountlines
- GROUP BY borrowernumber" );
-while ( my $result = $sth_mysql->fetchrow_hashref() ) {
- my $borrowernumber = $result->{'borrowernumber'};
- my $total_fines = $result->{'total_fines'};
-
- print "Fines for Borrower # $borrowernumber are \$ $total_fines \n";
-
- my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
+=head2 create_borrowers_table
+
+Create sqlite borrowers table to mirror the koha borrowers table structure
+
+=cut
+
+sub create_borrowers_table {
+
+ my %borrowers_info = get_columns_and_types_of_table( 'borrowers' );
+ my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
- $sth_sqlite = $dbh_sqlite->prepare( $sql );
- $sth_sqlite->execute( $total_fines, $borrowernumber );
- $sth_sqlite->finish();
+ $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} }
+ grep { exists($wanted_borrowers_columns{$_}) } keys %borrowers_info);
+
+ $sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
+ $sqlite_create_sql .= " ) ";
+
+ my $return = $dbh_sqlite->do($sqlite_create_sql);
+ unless ( $return ) {
+ warn 'unable to create borrowers table: ' . $dbh_sqlite->errstr();
+ }
+ return $return;
+
}
-## Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
-my $fields = GetIssuesFields();
+=head2 populate_borrowers_table
-$sqlite_create_sql = "CREATE TABLE issues ( \n";
+Import the data from the koha.borrowers table into our sqlite table
-my $firstField = 1;
-foreach my $key (keys %$fields) {
- my $field = $key;
- my $type = $fields->{$key};
+=cut
- if ( $firstField ) {
- $sqlite_create_sql .= " $field, $type ";
- $firstField = 0;
- } else {
- $sqlite_create_sql .= ", \n $field, $type ";
- }
+sub populate_borrowers_table {
+
+ my @borrower_fields = grep { exists($wanted_borrowers_columns{$_}) } get_columns_of_table( 'borrowers' );
+ push @borrower_fields, 'total_fines';
+
+ my $sql = "INSERT INTO borrowers ( ";
+ $sql .= join( ',', @borrower_fields );
+ $sql .= " ) VALUES ( ";
+ $sql .= join( ',', map { '?' } @borrower_fields );
+ $sql .= " ) ";
+ my $sth_sqlite = $dbh_sqlite->prepare($sql);
+
+ my $sth_mysql = $dbh_mysql->prepare(<<'END_SQL');
+SELECT borrowernumber,
+ cardnumber,
+ surname,
+ firstname,
+ address,
+ city,
+ phone,
+ dateofbirth,
+ sum( accountlines.amountoutstanding ) as total_fines
+FROM borrowers
+LEFT JOIN accountlines USING (borrowernumber)
+GROUP BY borrowernumber;
+END_SQL
+
+ my $fields_count = $sth_mysql->execute();
+ warn "preparing to insert $fields_count borrowers\n" if $verbose;
+
+ my $count;
+ while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
+ $count++;
+ if ( $verbose ) {
+ print '.' unless ( $count % 10 );
+ print "$count\n" unless ( $count % 1000 );
+ }
+ $sth_sqlite->execute( @$borrower{ @borrower_fields } );
+ $sth_sqlite->finish();
+ $dbh_sqlite->commit() if ( 0 == $count % 1000 );
+ }
+ $dbh_sqlite->commit();
+ print "inserted $count borrowers\n" if $verbose;
+ # add_fines_to_borrowers_table();
}
-$sqlite_create_sql .= " ) ";
-$sth_sqlite = $dbh_sqlite->preapre( $sqlite_create_sql );
-$sth_sqlite->execute();
-$sth_sqlite->finish();
+=head2 add_fines_to_borrowers_table
-## Import open issues from the koha database
-$sth_mysql = $dbh_mysql->prepare( "SELECT * FROM issues, items, biblioitems, biblio
- WHERE issues.itemnumber = items.itemnumber
- AND items.biblionumber = biblioitems.biblionumber
- AND items.biblionumber = biblio.biblionumber
- AND returndate IS NULL
- ORDER By borrowernumber DESC" );
+Import the fines from koha.accountlines into the sqlite db
-while ( my $result = $sth_mysql->fetchrow_hashref ) {
- my @keys;
- my @values;
+=cut
- print "Adding issue for Borrower # $result->{'borrowernumber'} \n";
+sub add_fines_to_borrowers_table {
- my $sql = "INSERT INTO issues ( ";
-
- my $firstLine = 1;
- foreach my $key (keys %$result) {
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+ print "preparing to update borrowers\n" if $verbose;
+ my $sth_mysql = $dbh_mysql->prepare(
+ "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
+ FROM accountlines
+ GROUP BY borrowernumber"
+ );
+ $sth_mysql->execute();
+ my $count;
+ while ( my $result = $sth_mysql->fetchrow_hashref() ) {
+ $count++;
+ if ( $verbose ) {
+ print '.' unless ( $count % 10 );
+ print "$count\n" unless ( $count % 1000 );
+ }
+
+ my $borrowernumber = $result->{'borrowernumber'};
+ my $total_fines = $result->{'total_fines'};
+
+ # warn "Fines for Borrower # $borrowernumber are \$ $total_fines \n" if $verbose;
+ my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
+
+ my $sth_sqlite = $dbh_sqlite->prepare($sql);
+ $sth_sqlite->execute( $total_fines, $borrowernumber );
+ $sth_sqlite->finish();
}
- push( @keys, $key );
- }
-
- $sql .= " ) VALUES ( ";
-
- $firstLine = 1;
- foreach my $key (keys %$result) {
- my $data = $result->{$key};
-
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+ print "updated $count borrowers\n" if ( $verbose && $count );
+}
+
+=head2 create_issue_table
+
+Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
+
+=cut
+
+sub create_issues_table {
+
+ my $fields = get_columns_for_issues_table();
+
+ my $sqlite_create_sql = "CREATE TABLE issues ( \n";
+
+ my $firstField = 1;
+ foreach my $key ( keys %$fields ) {
+ my $field = $key;
+ my $type = $fields->{$key};
+
+ if ($firstField) {
+ $sqlite_create_sql .= " $field $type ";
+ $firstField = 0;
+ } else {
+ $sqlite_create_sql .= ", \n $field $type ";
+ }
}
- push( @values, $data );
- }
+ $sqlite_create_sql .= " ) ";
- $sql .= " ) ";
+ my $sth_sqlite = $dbh_sqlite->prepare($sqlite_create_sql);
+ $sth_sqlite->execute();
+ $sth_sqlite->finish();
-print "\n$sql\n";
+}
+
+=head2 populate_issues_table
+
+Import open issues from the koha database
+
+=cut
+
+sub populate_issues_table {
+
+ print "preparing to populate ISSUES table\n" if $verbose;
+ my $sth_mysql = $dbh_mysql->prepare(
+ "SELECT issues.borrowernumber,
+ issues.date_due,
+ items.itemcallnumber,
+ biblio.title,
+ biblioitems.itemtype
+ FROM issues, items, biblioitems, biblio
+ WHERE issues.itemnumber = items.itemnumber
+ AND items.biblionumber = biblioitems.biblionumber
+ AND items.biblionumber = biblio.biblionumber
+ AND returndate IS NULL"
+ );
+ $sth_mysql->execute();
+
+ my $column_names = $sth_mysql->{'NAME'};
+ my $sql_sqlite = "INSERT INTO issues ( ";
+ $sql_sqlite .= join( ',', @$column_names );
+ $sql_sqlite .= " ) VALUES ( ";
+ $sql_sqlite .= join( ',', map { '?' } @$column_names );
+ $sql_sqlite .= " ) ";
+
+ warn "$sql_sqlite\n" if $verbose;
+ my $sth_sqlite = $dbh_sqlite->prepare($sql_sqlite);
+
+ my $count;
+ while ( my $result = $sth_mysql->fetchrow_hashref ) {
+
+ $count++;
+ if ( $verbose ) {
+ print '.' unless ( $count % 10 );
+ print "$count\n" unless ( $count % 1000 );
+ }
+
+ $sth_sqlite->execute( @$result{ @$column_names } );
+ $sth_sqlite->finish();
+ $dbh_sqlite->commit() if ( 0 == $count % 1000 );
+ }
+ $dbh_sqlite->commit();
+ print "inserted $count issues\n" if ( $verbose && $count );
+}
+
+=head2 get_columns_of_table
+
+pass in the name of a database table.
+
+returns list of column names in that table.
+
+=cut
+
+sub get_columns_of_table {
+ my $table_name = shift;
+
+ my %column_info = get_columns_and_types_of_table( $table_name );
+ my @columns = keys %column_info;
+ return @columns;
+
+}
+
+=head2 get_columns_and_types_of_table
+
+pass in the name of a database table
+
+returns a hash of column names to their types.
+
+=cut
+
+sub get_columns_and_types_of_table {
+ my $table_name = shift;
+
+ my $column_info = $dbh_mysql->selectall_arrayref( "SHOW COLUMNS FROM $table_name" );
+ my %columns = map{ $_->[0] => $_->[1] } @$column_info;
+ return %columns;
- $sth_sqlite = $dbh_sqlite->prepare( $sql );
- $sth_sqlite->execute( @keys, @values );
- $sth_sqlite->finish();
}
+=head2 get_columns_for_issues_table
-## This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
-sub GetIssuesFields {
- my $dbh = C4::Context->dbh;
+This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
+
+=cut
+
+sub get_columns_for_issues_table {
my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
- my $fields;
+
+ my %fields;
foreach my $table ( @tables ) {
- my $sth = $dbh->prepare( 'DESCRIBE ?' );
- $sth->execute( $table );
-
- while ( my $result = $sth->fetchrow_hashref ) {
- my $field = $result->{'Field'};
- my $type = $result->{'Type'};
-
- $fields->{$field} = $type;
- }
-
- $sth->finish()
+ my %table_info = get_columns_and_types_of_table( $table );
+ %fields = ( %fields, %table_info );
}
-
- return $fields;
-}
\ No newline at end of file
+ return { map { $_ => $fields{$_} } grep { exists($wanted_issues_columns{$_}) } keys %fields };
+}
+
+1;
+__END__
+