1 package C4::Update::Database;
3 # Copyright Biblibre 2012
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
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
27 use List::MoreUtils qw/uniq/;
32 C4::Update::Database.pm
36 use C4::Update::Database;
38 This package is used by admin/updatedatabase.pl, to manage DB updates
44 my $VERSIONS_PATH = C4::Context->config('intranetdir') . '/installer/data/mysql/versions';
49 my $dbh = C4::Context->dbh;
53 my $file = get_filepath($version);
54 this sub will return the full path of a given DB update number
60 my @files = File::Find::Rule->file->name( "$version.sql", "$version.pl" ) ->in( ( $VERSIONS_PATH ) );
62 if ( scalar @files != 1 ) {
63 die "This version ($version) returned has ".scalar @files." corresponding, need only 1";
71 my $md5 = get_md5($filepath)
72 returns the md5sum of the selected file.
73 This is used to check consistency of updates
78 my ( $filepath ) = @_;
79 open(FILE, $filepath);
81 my $ctx = Digest::MD5->new;
83 my $md5 = $ctx->hexdigest;
88 =head2 execute_version
90 $result = execute_version($version_number);
92 This sub will detect if the number is made through a .pl or a .sql, and behave accordingly
93 if there is more than 1 file with the same number, an error will be issued
94 if you try to execute a version_number that has already be executed, then it will also issue an error
95 the sub return an result hash, with the version number and the result
100 my ( $version ) = @_;
105 $filepath = get_filepath $version;
108 return { $version => $@ };
111 my @file_infos = fileparse( $filepath, qr/\.[^.]*/ );
112 my $extension = $file_infos[2];
113 my $filename = $version . $extension;
115 my $md5 = get_md5 $filepath;
116 my $r = md5_already_exists( $md5 );
119 $report->{$version} = {
120 error => "ALREADY_EXISTS",
121 filepath => $filepath,
122 old_version => @$r[0]->{version},
123 md5 => @$r[0]->{md5},
129 given ( $extension ) {
131 $queries = get_queries ( $filepath );
135 $queries = get_queries ( $filepath );
138 $report->{$version} = {
139 error => "LOAD_FUNCTIONS_FAILED",
140 filename => $filename,
146 $report->{$version} = {
147 error => "BAD_EXTENSION",
148 extension => $extension,
154 if ( defined $report->{$version} );
156 my $errors = execute ( $queries );
157 $report->{$version} = scalar( @$errors ) ? $errors : "OK";
158 set_infos ( $version, $queries, $errors, $md5 );
162 =head2 list_versions_available
164 my @versions = list_versions_available;
165 return an array with all version available
169 sub list_versions_available {
172 my @files = File::Find::Rule->file->name( "*.sql", "*.pl" ) ->in( ( $VERSIONS_PATH ) );
174 for my $f ( @files ) {
175 my @file_infos = fileparse( $f, qr/\.[^.]*/ );
176 push @versions, $file_infos[0];
178 @versions = uniq @versions;
182 =head2 list_versions_already_applied
184 my @versions = list_versions_available;
185 return an array with all version that have already been applied
186 This sub check first that the updatedb tables exist and create them if needed
190 sub list_versions_already_applied {
191 # 1st check if tables exist, otherwise create them
193 CREATE TABLE IF NOT EXISTS `updatedb_error` ( `version` varchar(32) DEFAULT NULL, `error` text ) ENGINE=InnoDB CHARSET=utf8;
196 CREATE TABLE IF NOT EXISTS `updatedb_query` ( `version` varchar(32) DEFAULT NULL, `query` text ) ENGINE=InnoDB CHARSET=utf8;
199 CREATE TABLE IF NOT EXISTS `updatedb_report` ( `version` text, `md5` varchar(50) DEFAULT NULL, `comment` text, `status` int(1) DEFAULT NULL ) ENGINE=InnoDB CHARSET=utf8;
202 my $query = qq/ SELECT version, comment, status FROM updatedb_report ORDER BY version/;
203 my $sth = $dbh->prepare( $query );
205 my $versions = $sth->fetchall_arrayref( {} );
208 my @comments = defined $_->{comment} ? split '\\\n', $_->{comment} : "";
209 push @{ $version->{comments} }, { comment => $_ } for @comments;
210 delete $version->{comment};
213 for my $version ( @$versions ) {
214 $query = qq/ SELECT query FROM updatedb_query WHERE version = ? ORDER BY version/;
215 $sth = $dbh->prepare( $query );
216 $sth->execute( $version->{version} );
217 $version->{queries} = $sth->fetchall_arrayref( {} );
219 $query = qq/ SELECT error FROM updatedb_error WHERE version = ? ORDER BY version/;
220 $sth = $dbh->prepare( $query );
221 $sth->execute( $version->{version} );
222 $version->{errors} = $sth->fetchall_arrayref( {} );
230 my @errors = $execute(\@queries);
231 This sub will execute queries coming from an execute_version based on a .sql file
236 my ( $queries ) = @_;
238 for my $query ( @{$queries->{queries}} ) {
242 push @errors, get_error();
247 =head2 get_tables_name
249 my $tables = get_tables_name;
250 return an array with all Koha mySQL table names
254 sub get_tables_name {
255 my $sth = $dbh->prepare("SHOW TABLES");
258 while ( my ( $table ) = $sth->fetchrow_array ) {
259 push @tables, $table;
265 =head2 check_coherency
267 my $errors = check_coherency($query); UNUSED
268 This sub will try to check if a SQL query is useless or no.
269 for queries that are CREATE TABLE, it will check if the table already exists
270 for queries that are ALTER TABLE, it will search if the modification has already been made
271 for queries that are INSERT, it will search if the insert has already been made if it's a syspref or a permission
273 Those test cover 90% of the updatedatabases cases. That will help finding duplicate or inconsistencies
277 #sub check_coherency {
278 # my ( $query ) = @_;
279 # $tables = get_tables_name() if not $tables;
282 # when ( /CREATE TABLE(?:.*?)? `?(\w+)`?/ ) {
283 # my $table_name = $1;
284 # if ( grep { /$table_name/ } @$tables ) {
285 # die "COHERENCY: Table $table_name already exists";
289 # when ( /ALTER TABLE *`?(\w+)`? *ADD *(?:COLUMN)? `?(\w+)`?/ ) {
290 # my $table_name = $1;
291 # my $column_name = $2;
292 # next if $column_name =~ /(UNIQUE|CONSTRAINT|INDEX|KEY|FOREIGN)/;
293 # if ( not grep { /$table_name/ } @$tables ) {
294 # return "COHERENCY: Table $table_name does not exist";
296 # my $sth = $dbh->prepare( "DESC $table_name $column_name" );
297 # my $rv = $sth->execute;
299 # die "COHERENCY: Field $table_name.$column_name already exists";
304 # when ( /INSERT INTO `?(\w+)`?.*?VALUES *\((.*?)\)/ ) {
305 # my $table_name = $1;
306 # my @values = split /,/, $2;
307 # s/^ *'// foreach @values;
308 # s/' *$// foreach @values;
309 # given ( $table_name ) {
310 # when ( /systempreferences/ ) {
311 # my $syspref = $values[0];
312 # my $sth = $dbh->prepare( "SELECT COUNT(*) FROM systempreferences WHERE variable = ?" );
313 # $sth->execute( $syspref );
314 # if ( ( my $count = $sth->fetchrow_array ) > 0 ) {
315 # die "COHERENCY: Syspref $syspref already exists";
319 # when ( /permissions/){
320 # my $module_bit = $values[0];
321 # my $code = $values[1];
322 # my $sth = $dbh->prepare( "SELECT COUNT(*) FROM permissions WHERE module_bit = ? AND code = ?" );
323 # $sth->execute($module_bit, $code);
324 # if ( ( my $count = $sth->fetchrow_array ) > 0 ) {
325 # die "COHERENCY: Permission $code already exists";
336 my $errors = get_error()
337 This sub will return any mySQL error that occured during an update
342 my @errors = $dbh->selectrow_array(qq{SHOW ERRORS}); # Get errors
343 my @warnings = $dbh->selectrow_array(qq{SHOW WARNINGS}); # Get warnings
344 if ( @errors ) { # Catch specifics errors
345 return qq{$errors[0] : $errors[1] => $errors[2]};
346 } elsif ( @warnings ) {
347 return qq{$warnings[0] : $warnings[1] => $warnings[2]}
348 if $warnings[0] ne 'Note';
355 my $result = get_queries($filepath);
356 this sub will return a hashref with 2 entries:
357 $result->{queries} is an array with all queries to execute
358 $result->{comments} is an array with all comments in the .sql file
363 my ( $filepath ) = @_;
364 open my $fh, "<", $filepath;
367 if ( $filepath =~ /\.pl$/ ) {
368 if ( do $filepath ) {
369 my $infos = _get_queries();
370 @queries = @{ $infos->{queries} } if exists $infos->{queries};
371 @comments = @{ $infos->{comments} } if exists $infos->{comments};
374 die "I can't load $filepath. Please check the execute flag and if this file is a valid perl script ($@)";
377 my $old_delimiter = $/;
382 if ( $line =~ /^--/ ) {
383 my @l = split $old_delimiter, $line;
392 $tmp_query .= $l . $old_delimiter;
394 push @queries, $tmp_query if $tmp_query;
399 push @comments, $line;
402 if ( $line =~ /^delimiter (.*)$/i ) {
407 push @queries, $line if not $line =~ /^\s*$/; # Push if query is not empty
413 return { queries => \@queries, comments => \@comments };
416 =head2 md5_already_exists
418 my $result = md5_already_exists($md5);
419 check if the md5 of an update has already been applied on the database.
420 If yes, it will return a hash with the version related to this md5
424 sub md5_already_exists {
426 my $query = qq/SELECT version, md5 FROM updatedb_report WHERE md5 = ?/;
427 my $sth = $dbh->prepare( $query );
428 $sth->execute( $md5 );
430 while ( my ( $version, $md5 ) = $sth->fetchrow ) {
431 push @r, { version => $version, md5 => $md5 };
439 set_info($version,$queries, $error, $md5);
440 this sub will insert into the updatedb tables what has been made on the database (queries, errors, result)
445 my ( $version, $queries, $errors, $md5 ) = @_;
446 SetVersion($version) if not -s $errors;
447 for my $query ( @{ $queries->{queries} } ) {
448 my $sth = $dbh->prepare("INSERT INTO updatedb_query(version, query) VALUES (?, ?)");
449 $sth->execute( $version, $query );
452 for my $error ( @$errors ) {
453 my $sth = $dbh->prepare("INSERT INTO updatedb_error(version, error) VALUES (?, ?)");
454 $sth->execute( $version, $error );
456 my $sth = $dbh->prepare("INSERT INTO updatedb_report(version, md5, comment, status) VALUES (?, ?, ?, ?)");
460 join ('\n', @{ $queries->{comments} }),
461 ( @$errors > 0 ) ? 0 : 1
467 mark_as_ok($version);
468 this sub will force to mark as "OK" an update that has failed
469 once this has been made, the status will look as "forced OK", and appear in green like versions that have been applied without any problem
474 my ( $version ) = @_;
475 my $sth = $dbh->prepare( "UPDATE updatedb_report SET status = 2 WHERE version=?" );
476 my $affected = $sth->execute( $version );
477 if ( $affected < 1 ) {
478 my $filepath = get_filepath $version;
479 my $queries = get_queries $filepath;
480 my $md5 = get_md5 $filepath;
481 set_infos $version, $queries, undef, $md5;
483 $sth->execute( $version );
490 return 1 if the database is up to date else 0.
491 The database is up to date if all versions are excecuted.
496 my $versions_available = C4::Update::Database::list_versions_available;
497 my $versions = C4::Update::Database::list_versions_already_applied;
498 for my $v ( @$versions_available ) {
499 if ( not grep { $v eq $$_{version} } @$versions ) {
506 =head2 TransformToNum
508 Transform the Koha version from a 4 parts string
509 to a number, with just 1 . (ie: it's a number)
516 # remove the 3 last . to have a Perl number
517 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
518 $version =~ s/Bug(\d+)/$1/;
523 my $new_version = TransformToNum(shift);
524 return unless $new_version =~ /\d\.\d+/;
525 my $current_version = TransformToNum( C4::Context->preference('Version') );
526 unless ( C4::Context->preference('Version') ) {
527 my $finish = $dbh->prepare(qq{
528 INSERT IGNORE INTO systempreferences (variable,value,explanation)
529 VALUES ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller')
531 $finish->execute($new_version);
534 if ( $new_version > $current_version ) {
535 my $finish = $dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
536 $finish->execute($new_version);
540 =head2 TableExists($table)
547 local $dbh->{PrintError} = 0;
548 local $dbh->{RaiseError} = 0;
549 $dbh->do(qq{SELECT * FROM $table WHERE 1 = 0 });