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
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
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.
56 This option cause the output file to be an SQLite2 database.
57 This is the format that the original offline circ client
58 ( the one written in PHP/Gtk, version < 1.0 ) requires.
62 This option cause the output file to be an SQLite3 database.
63 This is the format that the new offline circ client
64 ( the one written in C++/Qt4, version >= 1.0 ) requires.
70 This script generates a sqlite database file full of patron and
71 holdings data that can be used by an offline circulation tool.
75 This program could be run from cron to occasionally refresh the
76 offline circulation database. For instance:
78 C<0 0 * * * create_koc_db.pl>
82 This program was originally created to interact with Kyle Hall's
83 offline circulation tool, which is available from
84 L<http://kylehall.info/index.php/projects/koha-tools/koha-offline-circulation/>,
85 but any similar tool could use the database that is produced.
98 use Koha
::Script
-cron
;
100 use English
qw(-no_match_vars);
105 my $filename = 'borrowers.db';
111 'verbose' => \
$verbose,
114 'file=s' => \
$filename,
116 'sqlite2' => \
$sqlite2,
117 'sqlite3' => \
$sqlite3,
120 pod2usage
(1) if $help;
121 pod2usage
( -verbose
=> 2 ) if $man;
123 my %wanted_borrowers_columns = map { $_ => 1 } qw
/borrowernumber cardnumber surname firstname address city phone dateofbirth/;
124 my %wanted_issues_columns = map { $_ => 1 } qw
/borrowernumber date_due itemcallnumber title itemtype/;
126 prepare_file_for_writing
($filename)
127 or die "file: '$filename' already exists. Use --force to overwrite\n";
131 ## Create DB Connections
132 my $dbh_mysql = C4
::Context
->dbh;
135 $dbh_sqlite = DBI
->connect( "dbi:SQLite2:dbname=$filename", "", "" );
136 } elsif ( $sqlite3 ) {
137 $dbh_sqlite = DBI
->connect( "dbi:SQLite:dbname=$filename", "", "" );
139 $dbh_sqlite->{AutoCommit
} = 0;
141 create_borrowers_table
();
142 populate_borrowers_table
();
144 create_issues_table
();
145 populate_issues_table
();
147 =head1 INTERNAL METHODS
149 =head2 verify_dbd_sqlite
151 Since DBD::SQLite is a new prerequisite and an optional one, let's
152 make sure we have a new enough version of it.
156 sub verify_dbd_sqlite
{
159 eval { require DBD
::SQLite2
; };
161 my $msg = <<'END_MESSAGE';
162 DBD::SQLite2 is required to generate offline circultion database files, but not found.
163 Please install the DBD::SQLite2 perl module. It is available from
164 http://search.cpan.org/dist/DBD-SQLite2/ or through the CPAN module.
168 } elsif ( $sqlite3 ) {
169 eval { require DBD::SQLite; };
171 my $msg = <<'END_MESSAGE
';
172 DBD::SQLite3 is required to generate offline circultion database files, but not found.
173 Please install the DBD::SQLite3 perl module. It is available from
174 http://search.cpan.org/dist/DBD-SQLite3/ or through the CPAN module.
179 die( "Error: execution requires either the option --sqlite2 or --sqlite3. Run with --help for details." );
183 =head2 prepare_file_for_writing
185 pass in the filename that we're considering using
for the SQLite db
.
187 returns true
if we can
use it
.
189 returns false
if we can
't. For example, if it alredy exists and we
190 don't have
--force
or don
't have permissions to unlink it.
194 sub prepare_file_for_writing {
195 my $filename = shift;
196 if ( -e $filename ) {
198 # this file exists. remove it if --force.
200 return unlink $filename;
208 =head2 create_borrowers_table
210 Create sqlite borrowers table to mirror the koha borrowers table structure
214 sub create_borrowers_table {
216 my %borrowers_info = get_columns_and_types_of_table( 'borrowers
' );
217 my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
219 $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} }
220 grep { exists($wanted_borrowers_columns{$_}) } keys %borrowers_info);
222 $sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
223 $sqlite_create_sql .= " ) ";
225 my $return = $dbh_sqlite->do($sqlite_create_sql);
227 warn 'unable to create borrowers table
: ' . $dbh_sqlite->errstr();
233 =head2 populate_borrowers_table
235 Import the data from the koha.borrowers table into our sqlite table
239 sub populate_borrowers_table {
241 my @borrower_fields = grep { exists($wanted_borrowers_columns{$_}) } get_columns_of_table( 'borrowers
' );
242 push @borrower_fields, 'total_fines
';
244 my $sql = "INSERT INTO borrowers ( ";
245 $sql .= join( ',', @borrower_fields );
246 $sql .= " ) VALUES ( ";
247 $sql .= join( ',', map { '?
' } @borrower_fields );
249 my $sth_sqlite = $dbh_sqlite->prepare($sql);
251 my $sth_mysql = $dbh_mysql->prepare(<<'END_SQL');
252 SELECT borrowernumber
,
260 sum
( accountlines
.amountoutstanding
) as total_fines
262 LEFT JOIN accountlines USING
(borrowernumber
)
263 GROUP BY borrowernumber
;
266 my $fields_count = $sth_mysql->execute();
267 warn "preparing to insert $fields_count borrowers\n" if $verbose;
270 while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
273 print '.' unless ( $count % 10 );
274 print "$count\n" unless ( $count % 1000 );
276 $sth_sqlite->execute( @
$borrower{ @borrower_fields } );
277 $sth_sqlite->finish();
278 $dbh_sqlite->commit() if ( 0 == $count % 1000 );
280 $dbh_sqlite->commit();
281 print "inserted $count borrowers\n" if $verbose;
282 # add_fines_to_borrowers_table();
285 =head2 add_fines_to_borrowers_table
287 Import the fines from koha.accountlines into the sqlite db
291 sub add_fines_to_borrowers_table
{
293 print "preparing to update borrowers\n" if $verbose;
294 my $sth_mysql = $dbh_mysql->prepare(
295 "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
297 GROUP BY borrowernumber"
299 $sth_mysql->execute();
301 while ( my $result = $sth_mysql->fetchrow_hashref() ) {
304 print '.' unless ( $count % 10 );
305 print "$count\n" unless ( $count % 1000 );
308 my $borrowernumber = $result->{'borrowernumber'};
309 my $total_fines = $result->{'total_fines'};
311 # warn "Fines for Borrower # $borrowernumber are \$ $total_fines \n" if $verbose;
312 my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
314 my $sth_sqlite = $dbh_sqlite->prepare($sql);
315 $sth_sqlite->execute( $total_fines, $borrowernumber );
316 $sth_sqlite->finish();
318 print "updated $count borrowers\n" if ( $verbose && $count );
321 =head2 create_issue_table
323 Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
327 sub create_issues_table
{
329 my $fields = get_columns_for_issues_table
();
331 my $sqlite_create_sql = "CREATE TABLE issues ( \n";
334 foreach my $key ( keys %$fields ) {
336 my $type = $fields->{$key};
339 $sqlite_create_sql .= " $field $type ";
342 $sqlite_create_sql .= ", \n $field $type ";
345 $sqlite_create_sql .= " ) ";
347 my $sth_sqlite = $dbh_sqlite->prepare($sqlite_create_sql);
348 $sth_sqlite->execute();
349 $sth_sqlite->finish();
353 =head2 populate_issues_table
355 Import open issues from the koha database
359 sub populate_issues_table
{
361 print "preparing to populate ISSUES table\n" if $verbose;
362 my $sth_mysql = $dbh_mysql->prepare(
363 "SELECT issues.borrowernumber,
365 items.itemcallnumber,
368 FROM issues, items, biblioitems, biblio
369 WHERE issues.itemnumber = items.itemnumber
370 AND items.biblionumber = biblioitems.biblionumber
371 AND items.biblionumber = biblio.biblionumber
372 AND returndate IS NULL"
374 $sth_mysql->execute();
376 my $column_names = $sth_mysql->{'NAME'};
377 my $sql_sqlite = "INSERT INTO issues ( ";
378 $sql_sqlite .= join( ',', @
$column_names );
379 $sql_sqlite .= " ) VALUES ( ";
380 $sql_sqlite .= join( ',', map { '?' } @
$column_names );
381 $sql_sqlite .= " ) ";
383 warn "$sql_sqlite\n" if $verbose;
384 my $sth_sqlite = $dbh_sqlite->prepare($sql_sqlite);
387 while ( my $result = $sth_mysql->fetchrow_hashref ) {
391 print '.' unless ( $count % 10 );
392 print "$count\n" unless ( $count % 1000 );
395 $sth_sqlite->execute( @
$result{ @
$column_names } );
396 $sth_sqlite->finish();
397 $dbh_sqlite->commit() if ( 0 == $count % 1000 );
399 $dbh_sqlite->commit();
400 print "inserted $count issues\n" if ( $verbose && $count );
403 =head2 get_columns_of_table
405 pass in the name of a database table.
407 returns list of column names in that table.
411 sub get_columns_of_table
{
412 my $table_name = shift;
414 my %column_info = get_columns_and_types_of_table
( $table_name );
415 my @columns = keys %column_info;
420 =head2 get_columns_and_types_of_table
422 pass in the name of a database table
424 returns a hash of column names to their types.
428 sub get_columns_and_types_of_table
{
429 my $table_name = shift;
431 my $column_info = $dbh_mysql->selectall_arrayref( "SHOW COLUMNS FROM $table_name" );
432 my %columns = map{ $_->[0] => $_->[1] } @
$column_info;
437 =head2 get_columns_for_issues_table
439 This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
443 sub get_columns_for_issues_table
{
445 my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
449 foreach my $table ( @tables ) {
450 my %table_info = get_columns_and_types_of_table
( $table );
451 %fields = ( %fields, %table_info );
453 return { map { $_ => $fields{$_} } grep { exists($wanted_issues_columns{$_}) } keys %fields };