-#!/usr/bin/perl -w
+#!/usr/bin/perl
# 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 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 3 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.
+# 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
+# You should have received a copy of the GNU General Public License
+# along with Koha; if not, see <http://www.gnu.org/licenses>.
#
=head1 NAME
=item B<--file>
-the filename that we should use for the database file that we produce. Defaults to "borrowers.db"
+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
=cut
-$|++;
-
use strict;
use warnings;
+$|++;
+
use DBI;
-use Getopt::Long;
-use Pod::Usage;
+use Getopt::Long qw( GetOptions );
+use Pod::Usage qw( pod2usage );
+
+use Koha::Script -cron;
use C4::Context;
use English qw(-no_match_vars);
my $man = 0;
my $filename = 'borrowers.db';
my $force = 0;
+my $sqlite2 = 0;
+my $sqlite3 = 0;
GetOptions(
'verbose' => \$verbose,
'man' => \$man,
'file=s' => \$filename,
'force' => \$force,
+ 'sqlite2' => \$sqlite2,
+ 'sqlite3' => \$sqlite3,
) or pod2usage(2);
+
pod2usage(1) if $help;
pod2usage( -verbose => 2 ) if $man;
+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/;
+
prepare_file_for_writing($filename)
or die "file: '$filename' already exists. Use --force to overwrite\n";
## Create DB Connections
my $dbh_mysql = C4::Context->dbh;
-my $dbh_sqlite = DBI->connect( "dbi:SQLite:dbname=$filename", "", "" );
+my $dbh_sqlite;
+if ( $sqlite2 ) {
+ $dbh_sqlite = DBI->connect( "dbi:SQLite2:dbname=$filename", "", "" );
+} elsif ( $sqlite3 ) {
+ $dbh_sqlite = DBI->connect( "dbi:SQLite:dbname=$filename", "", "" );
+}
+$dbh_sqlite->{AutoCommit} = 0;
create_borrowers_table();
populate_borrowers_table();
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;
+ }
+ } elsif ( $sqlite3 ) {
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.
+ 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;
+ die $msg;
}
-}
+ } else {
+ die( "Error: execution requires either the option --sqlite2 or --sqlite3. Run with --help for details." );
+ }
+}
=head2 prepare_file_for_writing
pass in the filename that we're considering using for the SQLite db.
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);
+
+ $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 .= " ) ";
sub populate_borrowers_table {
- my @borrower_fields = get_columns_of_table( 'borrowers' );
+ my @borrower_fields = grep { exists($wanted_borrowers_columns{$_}) } get_columns_of_table( 'borrowers' );
push @borrower_fields, 'total_fines';
my $sql = "INSERT INTO borrowers ( ";
my $sth_sqlite = $dbh_sqlite->prepare($sql);
my $sth_mysql = $dbh_mysql->prepare(<<'END_SQL');
-SELECT borrowers.borrowernumber, sum( accountlines.amountoutstanding ) as total_fines
+SELECT borrowernumber,
+ cardnumber,
+ surname,
+ firstname,
+ address,
+ city,
+ phone,
+ dateofbirth,
+ sum( accountlines.amountoutstanding ) as total_fines
FROM borrowers
-LEFT JOIN accountlines
- ON borrowers.borrowernumber = accountlines.borrowernumber
+LEFT JOIN accountlines USING (borrowernumber)
GROUP BY borrowernumber;
END_SQL
}
$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();
}
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"
+ "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();
$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 );
}
my %table_info = get_columns_and_types_of_table( $table );
%fields = ( %fields, %table_info );
}
- return \%fields;
+ return { map { $_ => $fields{$_} } grep { exists($wanted_issues_columns{$_}) } keys %fields };
}
1;
projects / srvgit / blobdiff