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 prepare_file_for_writing($filename)
105 or die "file: '$filename' already exists. Use --force to overwrite\n";
109 ## Create DB Connections
110 my $dbh_mysql = C4::Context->dbh;
111 my $dbh_sqlite = DBI->connect( "dbi:SQLite:dbname=$filename", "", "" );
113 create_borrowers_table();
114 populate_borrowers_table();
116 create_issues_table();
117 populate_issues_table();
119 =head1 INTERNAL METHODS
121 =head2 verify_dbd_sqlite
123 Since DBD::SQLite is a new prerequisite and an optional one, let's
124 make sure we have a new enough version of it.
128 sub verify_dbd_sqlite {
130 eval { require DBD::SQLite; };
132 my $msg = <<'END_MESSAGE';
133 DBD::SQLite is required to generate offline circultion database files, but not found.
134 Please install the DBD::SQLite perl module. It is availalbe from
135 http://search.cpan.org/dist/DBD-SQLite/ or through the CPAN module.
141 =head2 prepare_file_for_writing
143 pass in the filename that we're considering using for the SQLite db.
145 returns true if we can use it.
147 returns false if we can't. For example, if it alredy exists and we
148 don't have --force or don't have permissions to unlink it.
152 sub prepare_file_for_writing {
153 my $filename = shift;
154 if ( -e $filename ) {
156 # this file exists. remove it if --force.
158 return unlink $filename;
166 =head2 create_borrowers_table
168 Create sqlite borrowers table to mirror the koha borrowers table structure
172 sub create_borrowers_table {
174 my %borrowers_info = get_columns_and_types_of_table( 'borrowers' );
175 my $sqlite_create_sql = "CREATE TABLE borrowers ( \n";
177 $sqlite_create_sql .= join(',', map{ $_ . ' ' . $borrowers_info{$_} } keys %borrowers_info);
179 $sqlite_create_sql .= " , \n total_fines decimal(28,6) "; ## Extra field to store the total fines for a borrower in.
180 $sqlite_create_sql .= " ) ";
182 my $return = $dbh_sqlite->do($sqlite_create_sql);
184 warn 'unable to create borrowers table: ' . $dbh_sqlite->errstr();
190 =head2 populate_borrowers_table
192 Import the data from the koha.borrowers table into our sqlite table
196 sub populate_borrowers_table {
198 my @borrower_fields = get_columns_of_table( 'borrowers' );
199 push @borrower_fields, 'total_fines';
201 my $sql = "INSERT INTO borrowers ( ";
202 $sql .= join( ',', @borrower_fields );
203 $sql .= " ) VALUES ( ";
204 $sql .= join( ',', map { '?' } @borrower_fields );
206 my $sth_sqlite = $dbh_sqlite->prepare($sql);
208 my $sth_mysql = $dbh_mysql->prepare(<<'END_SQL');
209 SELECT borrowers.borrowernumber, sum( accountlines.amountoutstanding ) as total_fines
211 LEFT JOIN accountlines
212 ON borrowers.borrowernumber = accountlines.borrowernumber
213 GROUP BY borrowernumber;
216 my $fields_count = $sth_mysql->execute();
217 warn "preparing to insert $fields_count borrowers\n" if $verbose;
220 while ( my $borrower = $sth_mysql->fetchrow_hashref ) {
223 print '.' unless ( $count % 10 );
224 print "$count\n" unless ( $count % 1000 );
226 $sth_sqlite->execute( @$borrower{ @borrower_fields } );
227 $sth_sqlite->finish();
229 print "inserted $count borrowers\n" if $verbose;
230 # add_fines_to_borrowers_table();
233 =head2 add_fines_to_borrowers_table
235 Import the fines from koha.accountlines into the sqlite db
239 sub add_fines_to_borrowers_table {
241 print "preparing to update borrowers\n" if $verbose;
242 my $sth_mysql = $dbh_mysql->prepare(
243 "SELECT DISTINCT borrowernumber, SUM( amountoutstanding ) AS total_fines
245 GROUP BY borrowernumber"
247 $sth_mysql->execute();
249 while ( my $result = $sth_mysql->fetchrow_hashref() ) {
252 print '.' unless ( $count % 10 );
253 print "$count\n" unless ( $count % 1000 );
256 my $borrowernumber = $result->{'borrowernumber'};
257 my $total_fines = $result->{'total_fines'};
259 # warn "Fines for Borrower # $borrowernumber are \$ $total_fines \n" if $verbose;
260 my $sql = "UPDATE borrowers SET total_fines = ? WHERE borrowernumber = ?";
262 my $sth_sqlite = $dbh_sqlite->prepare($sql);
263 $sth_sqlite->execute( $total_fines, $borrowernumber );
264 $sth_sqlite->finish();
266 print "updated $count borrowers\n" if ( $verbose && $count );
269 =head2 create_issue_table
271 Create sqlite issues table with minimal information needed from koha tables issues, items, biblio, biblioitems
275 sub create_issues_table {
277 my $fields = get_columns_for_issues_table();
279 my $sqlite_create_sql = "CREATE TABLE issues ( \n";
282 foreach my $key ( keys %$fields ) {
284 my $type = $fields->{$key};
287 $sqlite_create_sql .= " $field $type ";
290 $sqlite_create_sql .= ", \n $field $type ";
293 $sqlite_create_sql .= " ) ";
295 my $sth_sqlite = $dbh_sqlite->prepare($sqlite_create_sql);
296 $sth_sqlite->execute();
297 $sth_sqlite->finish();
301 =head2 populate_issues_table
303 Import open issues from the koha database
307 sub populate_issues_table {
309 print "preparing to populate ISSUES table\n" if $verbose;
310 my $sth_mysql = $dbh_mysql->prepare(
311 "SELECT * FROM issues, items, biblioitems, biblio
312 WHERE issues.itemnumber = items.itemnumber
313 AND items.biblionumber = biblioitems.biblionumber
314 AND items.biblionumber = biblio.biblionumber
315 AND returndate IS NULL"
317 $sth_mysql->execute();
319 my $column_names = $sth_mysql->{'NAME'};
320 my $sql_sqlite = "INSERT INTO issues ( ";
321 $sql_sqlite .= join( ',', @$column_names );
322 $sql_sqlite .= " ) VALUES ( ";
323 $sql_sqlite .= join( ',', map { '?' } @$column_names );
324 $sql_sqlite .= " ) ";
326 warn "$sql_sqlite\n" if $verbose;
327 my $sth_sqlite = $dbh_sqlite->prepare($sql_sqlite);
330 while ( my $result = $sth_mysql->fetchrow_hashref ) {
334 print '.' unless ( $count % 10 );
335 print "$count\n" unless ( $count % 1000 );
338 $sth_sqlite->execute( @$result{ @$column_names } );
339 $sth_sqlite->finish();
341 print "inserted $count issues\n" if ( $verbose && $count );
344 =head2 get_columns_of_table
346 pass in the name of a database table.
348 returns list of column names in that table.
352 sub get_columns_of_table {
353 my $table_name = shift;
355 my %column_info = get_columns_and_types_of_table( $table_name );
356 my @columns = keys %column_info;
361 =head2 get_columns_and_types_of_table
363 pass in the name of a database table
365 returns a hash of column names to their types.
369 sub get_columns_and_types_of_table {
370 my $table_name = shift;
372 my $column_info = $dbh_mysql->selectall_arrayref( "SHOW COLUMNS FROM $table_name" );
373 my %columns = map{ $_->[0] => $_->[1] } @$column_info;
378 =head2 get_columns_for_issues_table
380 This sub returns a hashref where the keys are all the fields in the given tables, and the data is the field's type
384 sub get_columns_for_issues_table {
386 my @tables = ( 'issues', 'items', 'biblioitems', 'biblio' );
390 foreach my $table ( @tables ) {
391 my %table_info = get_columns_and_types_of_table( $table );
392 %fields = ( %fields, %table_info );