[Koha-patches] [PATCH] bug 2503 [9/9]: refactoring and bugfixing on create_koc_db.pl
Andrew Moore
andrew.moore at liblime.com
Tue Aug 19 16:00:30 CEST 2008
This is the script that generates the patron and holdings database for the offline circ module. I
touched up a few parts of it:
* fixed the database syntax to squash a bug or two
* extracted some methods for readability and reuse
* added some documentation
* added a --file and --force command line argument
* made it die if DBD::SQLite is not present. That module is required for this feature.
Here's an example crontab line that will generate the borrowers.db file each midnight.
0 0 * * * create_koc_db.pl --file /tmp/borrowers.db
---
Makefile.PL | 1 +
offline_circ/create_koc_db.pl | 461 ++++++++++++++++++++++++++++-------------
2 files changed, 317 insertions(+), 145 deletions(-)
diff --git a/Makefile.PL b/Makefile.PL
index 69a12cf..bc915f2 100644
--- a/Makefile.PL
+++ b/Makefile.PL
@@ -540,6 +540,7 @@ WriteMakefile(
'Class::Factory::Util' => 1.6,
'Class::Accessor' => 0.30,
'DBD::mysql' => 4.004,
+ 'DBD::SQLite' => 1.13, # optional, used for offline circulation
'DBI' => 1.53,
'Data::ICal' => 0.13,
'Data::Dumper' => 2.121,
diff --git a/offline_circ/create_koc_db.pl b/offline_circ/create_koc_db.pl
index 12db544..5a974b7 100755
--- a/offline_circ/create_koc_db.pl
+++ b/offline_circ/create_koc_db.pl
@@ -1,202 +1,373 @@
#!/usr/bin/perl -w
-## 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 at 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.
+
+=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;
+
+GetOptions(
+ 'verbose' => \$verbose,
+ 'help' => \$help,
+ 'man' => \$man,
+ 'file=s' => \$filename,
+ 'force' => \$force,
+) or pod2usage(2);
+pod2usage(1) if $help;
+pod2usage( -verbose => 2 ) if $man;
+
+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 = DBI->connect("dbi:SQLite:dbname=borrowers.db","","");
+my $dbh_sqlite = DBI->connect( "dbi:SQLite:dbname=$filename", "", "" );
+
+create_borrowers_table();
+populate_borrowers_table();
+
+create_issues_table();
+populate_issues_table();
+
+=head1 INTERNAL METHODS
+
+=head2 verify_dbd_sqlite
-## Create sqlite borrowers table to mirror the koha borrowers table structure
-my $sth_mysql = $dbh_mysql->prepare("DESCRIBE borrowers");
-$sth_mysql->execute();
+Since DBD::SQLite is a new prerequisite and an optional one, let's
+make sure we have a new enough version of it.
-my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
+=cut
-my $result = $sth_mysql->fetchrow_hashref();
-my $field = $result->{'Field'};
-my $type = $result->{'Type'};
-$sqlite_create_sql .= " $field $type ";
+sub verify_dbd_sqlite {
-while ( my $result = $sth_mysql->fetchrow_hashref() ) {
- $field = $result->{'Field'};
- $type = $result->{'Type'};
- $sqlite_create_sql .= " , \n $field $type ";
+ eval { require DBD::SQLite; };
+ if ( $EVAL_ERROR ) {
+ my $msg = <<'END_MESSAGE';
+DBD::SQLite is required to generate offline circultion database files, but not found.
+Please install the DBD::SQLite perl module. It is availalbe from
+http://search.cpan.org/dist/DBD-SQLite/ or through the CPAN module.
+END_MESSAGE
+ die $msg;
+ }
}
-$sth_mysql->finish();
-$sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
-$sqlite_create_sql .= " ) ";
+=head2 prepare_file_for_writing
-my $sth_sqlite = $dbh_sqlite->prepare( $sqlite_create_sql );
-$sth_sqlite->execute();
-$sth_sqlite->finish();
+pass in the filename that we're considering using for the SQLite db.
-## 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();
+returns true if we can use it.
-print "Number of Borrowers: $fields_count\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.
-while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
- my @keys;
- my @values;
+=cut
- print "Working on Borrower # $borrower->{'borrowernumber'} \n";
+sub prepare_file_for_writing {
+ my $filename = shift;
+ if ( -e $filename ) {
- my $sql = "INSERT INTO borrowers ( ";
-
- my $firstLine = 1;
- foreach my $key (keys %$borrower) {
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+ # this file exists. remove it if --force.
+ if ($force) {
+ return unlink $filename;
+ } else {
+ return;
+ }
}
- push( @keys, $key );
- }
-
- $sql .= " ) VALUES ( ";
-
- $firstLine = 1;
- foreach my $key (keys %$borrower) {
- my $data = $borrower->{$key};
+ return $filename;
+}
+
+=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";
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+ $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} } 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();
}
- push( @values, $data );
- }
+ return $return;
- $sql .= " ) ";
+}
+
+=head2 populate_borrowers_table
+
+Import the data from the koha.borrowers table into our sqlite table
-print "\n$sql\n";
+=cut
- $sth_sqlite = $dbh_sqlite->prepare( $sql );
- $sth_sqlite->execute( @keys, @values );
- $sth_sqlite->finish();
+sub populate_borrowers_table {
+
+ my @borrower_fields = get_columns_of_table( 'borrowers' );
+
+ my $sql = "INSERT INTO borrowers ( ";
+ $sql .= join( ',', @borrower_fields );
+ $sql .= " ) VALUES ( ";
+ $sql .= join( ',', map { '?' } @borrower_fields );
+ $sql .= " ) ";
+ warn "$sql\n" if $verbose;
+ my $sth_sqlite = $dbh_sqlite->prepare($sql);
+
+ my $sth_mysql = $dbh_mysql->prepare("SELECT * FROM borrowers ORDER BY borrowernumber DESC");
+ my $fields_count = $sth_mysql->execute();
+ warn "Number of Borrowers: $fields_count\n" if $verbose;
+
+ while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
+ warn "Working on Borrower # $borrower->{'borrowernumber'} \n" if $verbose;
+ $sth_sqlite->execute( @$borrower{ @borrower_fields } );
+ $sth_sqlite->finish();
+ }
+ add_fines_to_borrowers_table();
}
-## Import the fines from koha.accountlines into the sqlite db
-$sth_mysql = $dbh_mysql->prepare( "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
+=head2 add_fines_to_borrowers_table
+
+Import the fines from koha.accountlines into the sqlite db
+
+=cut
+
+sub add_fines_to_borrowers_table {
+
+ my $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 = ?";
-
- $sth_sqlite = $dbh_sqlite->prepare( $sql );
- $sth_sqlite->execute( $total_fines, $borrowernumber );
- $sth_sqlite->finish();
+ GROUP BY borrowernumber"
+ );
+ $sth_mysql->execute();
+ while ( my $result = $sth_mysql->fetchrow_hashref() ) {
+ 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();
+ }
}
-## Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
-my $fields = GetIssuesFields();
+=head2 create_issue_table
-$sqlite_create_sql = "CREATE TABLE issues ( \n";
+Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
-my $firstField = 1;
-foreach my $key (keys %$fields) {
- my $field = $key;
- my $type = $fields->{$key};
+=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 ";
+ }
+ }
+ $sqlite_create_sql .= " ) ";
+ warn $sqlite_create_sql if $verbose;
+
+ my $sth_sqlite = $dbh_sqlite->prepare($sqlite_create_sql);
+ $sth_sqlite->execute();
+ $sth_sqlite->finish();
- if ( $firstField ) {
- $sqlite_create_sql .= " $field, $type ";
- $firstField = 0;
- } else {
- $sqlite_create_sql .= ", \n $field, $type ";
- }
}
-$sqlite_create_sql .= " ) ";
-$sth_sqlite = $dbh_sqlite->preapre( $sqlite_create_sql );
-$sth_sqlite->execute();
-$sth_sqlite->finish();
+=head2 populate_issues_table
+
+Import open issues from the koha database
+
+=cut
-## Import open issues from the koha database
-$sth_mysql = $dbh_mysql->prepare( "SELECT * FROM issues, items, biblioitems, biblio
+sub populate_issues_table {
+
+ my $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" );
+ ORDER By borrowernumber DESC"
+ );
+ $sth_mysql->execute();
-while ( my $result = $sth_mysql->fetchrow_hashref ) {
- my @keys;
- my @values;
+ while ( my $result = $sth_mysql->fetchrow_hashref ) {
- print "Adding issue for Borrower # $result->{'borrowernumber'} \n";
+ # my @keys;
+ # my @values;
- my $sql = "INSERT INTO issues ( ";
-
- my $firstLine = 1;
- foreach my $key (keys %$result) {
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
- }
- push( @keys, $key );
- }
-
- $sql .= " ) VALUES ( ";
-
- $firstLine = 1;
- foreach my $key (keys %$result) {
- my $data = $result->{$key};
-
- if ( $firstLine ) {
- $sql .= '?';
- $firstLine = 0;
- } else {
- $sql .= ', ?';
+ warn "Adding issue for Borrower # $result->{'borrowernumber'} \n" if $verbose;
+
+ my $sql = "INSERT INTO issues ( ";
+ $sql .= join( ',', keys %$result );
+ $sql .= " ) VALUES ( ";
+ $sql .= join( ',', map { '?' } keys %$result );
+ $sql .= " ) ";
+
+ warn "$sql\n" if $verbose;
+
+ my $sth_sqlite = $dbh_sqlite->prepare($sql);
+ $sth_sqlite->execute( values %$result );
+ $sth_sqlite->finish();
}
- push( @values, $data );
- }
+}
+
+=head2 get_columns_of_table
+
+pass in the name of a database table.
- $sql .= " ) ";
+returns list of column names in that table.
-print "\n$sql\n";
+=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;
- $sth_sqlite = $dbh_sqlite->prepare( $sql );
- $sth_sqlite->execute( @keys, @values );
- $sth_sqlite->finish();
}
+=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;
-## 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;
+}
+
+=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
+
+=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 \%fields;
+}
+
+1;
+__END__
+
--
1.5.6
More information about the Koha-patches
mailing list