From 556d9cc43d4c1d060f8a6bffefd5c734b3c1e971 Mon Sep 17 00:00:00 2001 From: Andrew Moore Date: Wed, 20 Aug 2008 11:21:52 -0500 Subject: [PATCH] bug 2503: refactoring and bugfixing on create_koc_db.pl 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 Signed-off-by: Galen Charlton --- Makefile.PL | 1 + misc/cronjobs/create_koc_db.pl | 491 +++++++++++++++++++++++++++++------------ 2 files changed, 345 insertions(+), 147 deletions(-) diff --git a/Makefile.PL b/Makefile.PL index 69a12cfabd..bc915f26ee 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/misc/cronjobs/create_koc_db.pl b/misc/cronjobs/create_koc_db.pl index 12db544a33..c1baa50a48 100755 --- a/misc/cronjobs/create_koc_db.pl +++ b/misc/cronjobs/create_koc_db.pl @@ -1,202 +1,399 @@ #!/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 + +# 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, +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 sqlite borrowers table to mirror the koha borrowers table structure -my $sth_mysql = $dbh_mysql->prepare("DESCRIBE borrowers"); -$sth_mysql->execute(); +create_issues_table(); +populate_issues_table(); -my $sqlite_create_sql = "CREATE TABLE borrowers ( \n"; +=head1 INTERNAL METHODS -my $result = $sth_mysql->fetchrow_hashref(); -my $field = $result->{'Field'}; -my $type = $result->{'Type'}; -$sqlite_create_sql .= " $field $type "; +=head2 verify_dbd_sqlite -while ( my $result = $sth_mysql->fetchrow_hashref() ) { - $field = $result->{'Field'}; - $type = $result->{'Type'}; - $sqlite_create_sql .= " , \n $field $type "; +Since DBD::SQLite is a new prerequisite and an optional one, let's +make sure we have a new enough version of it. + +=cut + +sub verify_dbd_sqlite { + + 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"; + + $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} } keys %borrowers_info); - if ( $firstLine ) { - $sql .= '?'; - $firstLine = 0; - } else { - $sql .= ', ?'; + $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 -print "\n$sql\n"; +Import the data from the koha.borrowers table into our sqlite table - $sth_sqlite = $dbh_sqlite->prepare( $sql ); - $sth_sqlite->execute( @keys, @values ); - $sth_sqlite->finish(); +=cut + +sub populate_borrowers_table { + + my @borrower_fields = 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 borrowers.borrowernumber, sum( accountlines.amountoutstanding ) as total_fines +FROM borrowers +LEFT JOIN accountlines + ON borrowers.borrowernumber = accountlines.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(); + } + print "inserted $count borrowers\n" if $verbose; + # 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 { + + 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" ); -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(); + 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(); + } + print "updated $count borrowers\n" if ( $verbose && $count ); } -## 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 .= " ) "; + + 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 -$sth_mysql = $dbh_mysql->prepare( "SELECT * FROM issues, items, biblioitems, biblio +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 * 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" ); + 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(); + } + print "inserted $count issues\n" if ( $verbose && $count ); +} -while ( my $result = $sth_mysql->fetchrow_hashref ) { - my @keys; - my @values; +=head2 get_columns_of_table - print "Adding issue for Borrower # $result->{'borrowernumber'} \n"; +pass in the name of a database table. - 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 .= ', ?'; - } - push( @values, $data ); - } +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 - $sql .= " ) "; +pass in the name of a database table -print "\n$sql\n"; +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 -## 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; +=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__ + -- 2.11.0