3 # 2008 Kyle Hall <kyle.m.hall@gmail.com>
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
23 create_koc_db.pl - create a database file for the offline circulation tool
30 -help brief help message
31 -man full documentation
39 Print a brief help message and exits.
43 Prints the manual page and exits.
47 the filename that we should use for the database file that we produce. Defaults to "borrowers.db"
51 Forcefully overwrite any existing db file. Defaults to false, so
52 program will terminate prematurely if the file already exists.
58 This script generates a sqlite database file full of patron and
59 holdings data that can be used by an offline circulation tool.
63 This program could be run from cron to occasionally refresh the
64 offline circulation database. For instance:
66 C<0 0 * * * create_koc_db.pl>
70 This program was originally created to interact with Kyle Hall's
71 offline circulation tool, which is available from
72 L<http://kylehall.info/index.php/projects/koha-tools/koha-offline-circulation/>,
73 but any similar tool could use the database that is produced.
86 use English qw(-no_match_vars);
91 my $filename = 'borrowers.db';
95 'verbose' => \$verbose,
98 'file=s' => \$filename,
101 pod2usage(1) if $help;
102 pod2usage( -verbose => 2 ) if $man;
104 my %wanted_borrowers_columns = map { $_ => 1 } qw/borrowernumber cardnumber surname firstname address city phone dateofbirth/;
105 my %wanted_issues_columns = map { $_ => 1 } qw/borrowernumber date_due itemcallnumber title itemtype/;
107 prepare_file_for_writing($filename)
108 or die "file: '$filename' already exists. Use --force to overwrite\n";
112 ## Create DB Connections
113 my $dbh_mysql = C4::Context->dbh;
114 my $dbh_sqlite = DBI->connect( "dbi:SQLite2:dbname=$filename", "", "" );
115 $dbh_sqlite->{AutoCommit} = 0;
117 create_borrowers_table();
118 populate_borrowers_table();
120 create_issues_table();
121 populate_issues_table();
123 =head1 INTERNAL METHODS
125 =head2 verify_dbd_sqlite
127 Since DBD::SQLite is a new prerequisite and an optional one, let's
128 make sure we have a new enough version of it.
132 sub verify_dbd_sqlite {
134 eval { require DBD::SQLite2; };
136 my $msg = <<'END_MESSAGE';
137 DBD::SQLite2 is required to generate offline circultion database files, but not found.
138 Please install the DBD::SQLite2 perl module. It is availalbe from
139 http://search.cpan.org/dist/DBD-SQLite2/ or through the CPAN module.
145 =head2 prepare_file_for_writing
147 pass in the filename that we're considering using for the SQLite db.
149 returns true if we can use it.
151 returns false if we can't. For example, if it alredy exists and we
152 don't have --force or don't have permissions to unlink it.
156 sub prepare_file_for_writing {
157 my $filename = shift;
158 if ( -e $filename ) {
160 # this file exists. remove it if --force.
162 return unlink $filename;
170 =head2 create_borrowers_table
172 Create sqlite borrowers table to mirror the koha borrowers table structure
176 sub create_borrowers_table {
178 my %borrowers_info = get_columns_and_types_of_table( 'borrowers' );
179 my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
181 $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} }
182 grep { exists($wanted_borrowers_columns{$_}) } keys %borrowers_info);
184 $sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
185 $sqlite_create_sql .= " ) ";
187 my $return = $dbh_sqlite->do($sqlite_create_sql);
189 warn 'unable to create borrowers table: ' . $dbh_sqlite->errstr();
195 =head2 populate_borrowers_table
197 Import the data from the koha.borrowers table into our sqlite table
201 sub populate_borrowers_table {
203 my @borrower_fields = grep { exists($wanted_borrowers_columns{$_}) } get_columns_of_table( 'borrowers' );
204 push @borrower_fields, 'total_fines';
206 my $sql = "INSERT INTO borrowers ( ";
207 $sql .= join( ',', @borrower_fields );
208 $sql .= " ) VALUES ( ";
209 $sql .= join( ',', map { '?' } @borrower_fields );
211 my $sth_sqlite = $dbh_sqlite->prepare($sql);
213 my $sth_mysql = $dbh_mysql->prepare(<<'END_SQL');
214 SELECT borrowernumber,
222 sum( accountlines.amountoutstanding ) as total_fines
224 LEFT JOIN accountlines USING (borrowernumber)
225 GROUP BY borrowernumber;
228 my $fields_count = $sth_mysql->execute();
229 warn "preparing to insert $fields_count borrowers\n" if $verbose;
232 while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
235 print '.' unless ( $count % 10 );
236 print "$count\n" unless ( $count % 1000 );
238 $sth_sqlite->execute( @$borrower{ @borrower_fields } );
239 $sth_sqlite->finish();
240 $dbh_sqlite->commit() if ( 0 == $count % 1000 );
242 $dbh_sqlite->commit();
243 print "inserted $count borrowers\n" if $verbose;
244 # add_fines_to_borrowers_table();
247 =head2 add_fines_to_borrowers_table
249 Import the fines from koha.accountlines into the sqlite db
253 sub add_fines_to_borrowers_table {
255 print "preparing to update borrowers\n" if $verbose;
256 my $sth_mysql = $dbh_mysql->prepare(
257 "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
259 GROUP BY borrowernumber"
261 $sth_mysql->execute();
263 while ( my $result = $sth_mysql->fetchrow_hashref() ) {
266 print '.' unless ( $count % 10 );
267 print "$count\n" unless ( $count % 1000 );
270 my $borrowernumber = $result->{'borrowernumber'};
271 my $total_fines = $result->{'total_fines'};
273 # warn "Fines for Borrower # $borrowernumber are \$ $total_fines \n" if $verbose;
274 my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
276 my $sth_sqlite = $dbh_sqlite->prepare($sql);
277 $sth_sqlite->execute( $total_fines, $borrowernumber );
278 $sth_sqlite->finish();
280 print "updated $count borrowers\n" if ( $verbose && $count );
283 =head2 create_issue_table
285 Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
289 sub create_issues_table {
291 my $fields = get_columns_for_issues_table();
293 my $sqlite_create_sql = "CREATE TABLE issues ( \n";
296 foreach my $key ( keys %$fields ) {
298 my $type = $fields->{$key};
301 $sqlite_create_sql .= " $field $type ";
304 $sqlite_create_sql .= ", \n $field $type ";
307 $sqlite_create_sql .= " ) ";
309 my $sth_sqlite = $dbh_sqlite->prepare($sqlite_create_sql);
310 $sth_sqlite->execute();
311 $sth_sqlite->finish();
315 =head2 populate_issues_table
317 Import open issues from the koha database
321 sub populate_issues_table {
323 print "preparing to populate ISSUES table\n" if $verbose;
324 my $sth_mysql = $dbh_mysql->prepare(
325 "SELECT issues.borrowernumber,
327 items.itemcallnumber,
330 FROM issues, items, biblioitems, biblio
331 WHERE issues.itemnumber = items.itemnumber
332 AND items.biblionumber = biblioitems.biblionumber
333 AND items.biblionumber = biblio.biblionumber
334 AND returndate IS NULL"
336 $sth_mysql->execute();
338 my $column_names = $sth_mysql->{'NAME'};
339 my $sql_sqlite = "INSERT INTO issues ( ";
340 $sql_sqlite .= join( ',', @$column_names );
341 $sql_sqlite .= " ) VALUES ( ";
342 $sql_sqlite .= join( ',', map { '?' } @$column_names );
343 $sql_sqlite .= " ) ";
345 warn "$sql_sqlite\n" if $verbose;
346 my $sth_sqlite = $dbh_sqlite->prepare($sql_sqlite);
349 while ( my $result = $sth_mysql->fetchrow_hashref ) {
353 print '.' unless ( $count % 10 );
354 print "$count\n" unless ( $count % 1000 );
357 $sth_sqlite->execute( @$result{ @$column_names } );
358 $sth_sqlite->finish();
359 $dbh_sqlite->commit() if ( 0 == $count % 1000 );
361 $dbh_sqlite->commit();
362 print "inserted $count issues\n" if ( $verbose && $count );
365 =head2 get_columns_of_table
367 pass in the name of a database table.
369 returns list of column names in that table.
373 sub get_columns_of_table {
374 my $table_name = shift;
376 my %column_info = get_columns_and_types_of_table( $table_name );
377 my @columns = keys %column_info;
382 =head2 get_columns_and_types_of_table
384 pass in the name of a database table
386 returns a hash of column names to their types.
390 sub get_columns_and_types_of_table {
391 my $table_name = shift;
393 my $column_info = $dbh_mysql->selectall_arrayref( "SHOW COLUMNS FROM $table_name" );
394 my %columns = map{ $_->[0] => $_->[1] } @$column_info;
399 =head2 get_columns_for_issues_table
401 This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
405 sub get_columns_for_issues_table {
407 my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
411 foreach my $table ( @tables ) {
412 my %table_info = get_columns_and_types_of_table( $table );
413 %fields = ( %fields, %table_info );
415 return { map { $_ => $fields{$_} } grep { exists($wanted_issues_columns{$_}) } keys %fields };