X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=misc%2Fcronjobs%2Fcreate_koc_db.pl;h=ccc4a5bae23c98a4f6ef6cdef6af71878dfc54fb;hb=6e71b80ca39e6f1d2c31659a9a656de807ef3f32;hp=c1baa50a4861cc3e37e15a4025a97cc12c9b976d;hpb=556d9cc43d4c1d060f8a6bffefd5c734b3c1e971;p=koha_fer diff --git a/misc/cronjobs/create_koc_db.pl b/misc/cronjobs/create_koc_db.pl index c1baa50a48..ccc4a5bae2 100755 --- a/misc/cronjobs/create_koc_db.pl +++ b/misc/cronjobs/create_koc_db.pl @@ -1,4 +1,4 @@ -#!/usr/bin/perl -w +#!/usr/bin/perl # 2008 Kyle Hall @@ -44,13 +44,25 @@ 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" +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 @@ -74,11 +86,11 @@ but any similar tool could use the database that is produced. =cut -$|++; - use strict; use warnings; +$|++; + use DBI; use Getopt::Long; use Pod::Usage; @@ -90,6 +102,8 @@ my $help = 0; my $man = 0; my $filename = 'borrowers.db'; my $force = 0; +my $sqlite2 = 0; +my $sqlite3 = 0; GetOptions( 'verbose' => \$verbose, @@ -97,10 +111,17 @@ GetOptions( '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"; @@ -108,7 +129,13 @@ verify_dbd_sqlite(); ## 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(); @@ -127,17 +154,31 @@ make sure we have a new enough version of it. 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. @@ -173,8 +214,9 @@ 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); + + $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 .= " ) "; @@ -195,7 +237,7 @@ Import the data from the koha.borrowers table into our sqlite table 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 ( "; @@ -206,10 +248,17 @@ sub populate_borrowers_table { 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 @@ -225,7 +274,9 @@ 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(); } @@ -308,11 +359,16 @@ 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" + "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(); @@ -337,7 +393,9 @@ sub populate_issues_table { $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 ); } @@ -391,7 +449,7 @@ sub get_columns_for_issues_table { 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;