From 328af17b7914fa75a961218f72b3af0a8303ac31 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 14 Jun 2012 11:46:57 +0200 Subject: [PATCH] Bug 7167: Update database improvements Content-Type: text/plain; charset="utf-8" This is a major patch, changing the way we handle database revisions when installing or upgrading. It includes a normal and developer mode, dictated by the DEBUG environment variable (see koha-httpd.conf). Please see Bugzilla report 7167 for more details. This patch has been amended a couple of times and has been squashed by Marcel. Original signoffs include: Signed-off-by: Chris Nighswonger Signed-off-by: Paul Poulain Still needs signoff, but made some (small) amendments: * Removed the empty 043.sql file from versions dir. * While noting the omission of dbrev 011, I removed the other files too. When we start the new mechanism, we start adding files there. * Ascending order for table on admin/updatedatabase * Important typo $koha39=~ should be assignment. Updated version number in install and Auth due to removing older sql files. * Typo _get_queries: removed the call after all; it is easier to leave SQL in perl files outside the update tables. Added some checks for emptiness. * Corrected execution of perl dbrevs (filepath). * Few additional changes to update.pl (see BZ). --- C4/Auth.pm | 107 ++--- C4/Installer.pm | 11 +- C4/Update/Database.pm | 545 ++++++++++++++++++++ about.pl | 42 ++- admin/ajax-updatedb-getinfos.pl | 61 +++ admin/updatedatabase.pl | 103 ++++ installer/data/mysql/kohastructure.sql | 21 + installer/data/mysql/update.pl | 86 +++ installer/data/mysql/versions/3.09/newupdate.sql | 1 + installer/install.pl | 40 +- .../intranet-tmpl/prog/en/css/staff-global.css | 7 + koha-tmpl/intranet-tmpl/prog/en/modules/about.tt | 2 +- .../prog/en/modules/admin/admin-home.tt | 7 + .../prog/en/modules/admin/updatedatabase.tt | 191 +++++++ mainpage.pl | 3 + 15 files changed, 1145 insertions(+), 82 deletions(-) create mode 100644 C4/Update/Database.pm create mode 100755 admin/ajax-updatedb-getinfos.pl create mode 100755 admin/updatedatabase.pl create mode 100755 installer/data/mysql/update.pl create mode 100644 installer/data/mysql/versions/3.09/newupdate.sql create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt diff --git a/C4/Auth.pm b/C4/Auth.pm index b6dc2f5..afdf7cb 100644 --- a/C4/Auth.pm +++ b/C4/Auth.pm @@ -28,6 +28,7 @@ require Exporter; use C4::Context; use C4::Templates; # to get the template use C4::Branch; # GetBranches +use C4::Update::Database; use C4::VirtualShelves; use POSIX qw/strftime/; use List::MoreUtils qw/ any /; @@ -133,9 +134,9 @@ sub get_template_and_user { my $in = shift; my $template = C4::Templates::gettemplate( $in->{'template_name'}, $in->{'type'}, $in->{'query'} ); - my ( $user, $cookie, $sessionID, $flags ); + my ( $user, $cookie, $sessionID, $flags, $new_session ); if ( $in->{'template_name'} !~m/maintenance/ ) { - ( $user, $cookie, $sessionID, $flags ) = checkauth( + ( $user, $cookie, $sessionID, $flags, $new_session ) = checkauth( $in->{'query'}, $in->{'authnotrequired'}, $in->{'flagsrequired'}, @@ -477,6 +478,12 @@ sub get_template_and_user { $template->param(OpacPublic => '1') if ($user || C4::Context->preference("OpacPublic")); } + + if ( $new_session ) { + # Check the version and redirect if DB is not up-to-date + version_check($in->{query}, $in->{'type'}, $cookie); + } + return ( $template, $borrowernumber, $cookie, $flags); } @@ -558,54 +565,43 @@ has authenticated. =cut -sub _version_check ($$) { - my $type = shift; - my $query = shift; - my $version; - # If Version syspref is unavailable, it means Koha is beeing installed, - # and so we must redirect to OPAC maintenance page or to the WebInstaller - # also, if OpacMaintenance is ON, OPAC should redirect to maintenance - if (C4::Context->preference('OpacMaintenance') && $type eq 'opac') { - warn "OPAC Install required, redirecting to maintenance"; - print $query->redirect("/cgi-bin/koha/maintenance.pl"); - } - unless ( $version = C4::Context->preference('Version') ) { # assignment, not comparison +sub _session_log { + (@_) or return 0; + open L, ">>/tmp/sessionlog" or warn "ERROR: Cannot append to /tmp/sessionlog"; + printf L join("\n",@_); + close L; +} + +sub version_check { + my ( $query, $type, $cookie ) = @_; + # check we have a Version. Otherwise => go to installer + unless ( C4::Context->preference('Version') ) { if ( $type ne 'opac' ) { - warn "Install required, redirecting to Installer"; + $debug && warn "Install required, redirecting to Installer"; print $query->redirect("/cgi-bin/koha/installer/install.pl"); } else { - warn "OPAC Install required, redirecting to maintenance"; + $debug && warn "OPAC Install required, redirecting to maintenance"; print $query->redirect("/cgi-bin/koha/maintenance.pl"); } safe_exit; } - # check that database and koha version are the same - # there is no DB version, it's a fresh install, - # go to web installer - # there is a DB version, compare it to the code version - my $kohaversion=C4::Context::KOHAVERSION; - # remove the 3 last . to have a Perl number - $kohaversion =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/; - $debug and print STDERR "kohaversion : $kohaversion\n"; - if ($version < $kohaversion){ - my $warning = "Database update needed, redirecting to %s. Database is $version and Koha is $kohaversion"; - if ($type ne 'opac'){ - warn sprintf($warning, 'Installer'); - print $query->redirect("/cgi-bin/koha/installer/install.pl?step=3"); - } else { - warn sprintf("OPAC: " . $warning, 'maintenance'); - print $query->redirect("/cgi-bin/koha/maintenance.pl"); - } + # check if you're uptodate, and if you're not, head to updater + my $koha39 = "3.0900026"; + + # Old updatedatabase method + if (C4::Context->preference('Version') < $koha39) { + print $query->redirect("/cgi-bin/koha/installer/install.pl?step=3"); safe_exit; } -} -sub _session_log { - (@_) or return 0; - open L, ">>/tmp/sessionlog" or warn "ERROR: Cannot append to /tmp/sessionlog"; - printf L join("\n",@_); - close L; + # New updatedatabase method + unless ( C4::Update::Database::is_uptodate() ) { + # not up-to-date, redirect to updatedatabase page + warn "redirect to updatedatabase"; + print $query->redirect(-location => "/cgi-bin/koha/admin/updatedatabase.pl", -cookie => $cookie); + safe_exit; + } } sub checkauth { @@ -616,6 +612,7 @@ sub checkauth { my $flagsrequired = shift; my $type = shift; $type = 'opac' unless $type; + my $new_session = 0; my $dbh = C4::Context->dbh; my $timeout = C4::Context->preference('timeout'); @@ -625,7 +622,6 @@ sub checkauth { }; $timeout = 600 unless $timeout; - _version_check($type,$query); # state variables my $loggedin = 0; my %info; @@ -729,6 +725,7 @@ sub checkauth { my $sessionID = $session->id; C4::Context->_new_userenv($sessionID); $cookie = $query->cookie( CGISESSID => $sessionID ); + $userid = $query->param('userid'); if ( ( $cas && $query->param('ticket') ) || $userid @@ -743,6 +740,7 @@ sub checkauth { checkpw( $dbh, $userid, $password, $query ); $userid = $retuserid; $info{'invalidCasLogin'} = 1 unless ($return); + $new_session = 1; } elsif ( ( $pki_field eq 'Common Name' && $ENV{'SSL_CLIENT_S_DN_CN'} ) @@ -781,6 +779,7 @@ sub checkauth { ( $return, $cardnumber, $retuserid ) = checkpw( $dbh, $userid, $password, $query ); $userid = $retuserid if ( $retuserid ne '' ); + $new_session = 1; } if ($return) { #_session_log(sprintf "%20s from %16s logged in at %30s.\n", $userid,$ENV{'REMOTE_ADDR'},(strftime '%c', localtime)); @@ -922,7 +921,7 @@ sub checkauth { unless ($cookie) { $cookie = $query->cookie( CGISESSID => '' ); } - return ( $userid, $cookie, $sessionID, $flags ); + return ( $userid, $cookie, $sessionID, $flags, $new_session ); } # @@ -1081,19 +1080,6 @@ sub check_api_auth { my $timeout = C4::Context->preference('timeout'); $timeout = 600 unless $timeout; - unless (C4::Context->preference('Version')) { - # database has not been installed yet - return ("maintenance", undef, undef); - } - my $kohaversion=C4::Context::KOHAVERSION; - $kohaversion =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/; - if (C4::Context->preference('Version') < $kohaversion) { - # database in need of version update; assume that - # no API should be called while databsae is in - # this condition. - return ("maintenance", undef, undef); - } - # FIXME -- most of what follows is a copy-and-paste # of code from checkauth. There is an obvious need # for refactoring to separate the various parts of @@ -1314,19 +1300,6 @@ sub check_cookie_auth { my $timeout = C4::Context->preference('timeout'); $timeout = 600 unless $timeout; - unless (C4::Context->preference('Version')) { - # database has not been installed yet - return ("maintenance", undef); - } - my $kohaversion=C4::Context::KOHAVERSION; - $kohaversion =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/; - if (C4::Context->preference('Version') < $kohaversion) { - # database in need of version update; assume that - # no API should be called while databsae is in - # this condition. - return ("maintenance", undef); - } - # FIXME -- most of what follows is a copy-and-paste # of code from checkauth. There is an obvious need # for refactoring to separate the various parts of diff --git a/C4/Installer.pm b/C4/Installer.pm index c1f356f..95985c7 100644 --- a/C4/Installer.pm +++ b/C4/Installer.pm @@ -23,6 +23,7 @@ use strict; our $VERSION = 3.07.00.049; use C4::Context; use C4::Installer::PerlModules; +use C4::Update::Database; =head1 NAME @@ -466,7 +467,15 @@ Koha software version. sub set_version_syspref { my $self = shift; - + # get all updatedatabase, and mark them as passed, as it's a fresh install + my $versions = C4::Update::Database::list_versions_availables(); + for my $v ( @$versions ) { + my $queries; + $queries->{queries} = ["initial setup"]; + $queries->{comments} = ["initial setup"]; + C4::Update::Database::set_infos($v,$queries,undef,undef); + } + # mark the "old" 3.6 version number my $kohaversion=C4::Context::KOHAVERSION; # remove the 3 last . to have a Perl number $kohaversion =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/; diff --git a/C4/Update/Database.pm b/C4/Update/Database.pm new file mode 100644 index 0000000..9d6047c --- /dev/null +++ b/C4/Update/Database.pm @@ -0,0 +1,545 @@ +package C4::Update::Database; + +# Copyright 2011 BibLibre +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use C4::Context; + +use File::Basename; +use File::Find::Rule; +use Digest::MD5; +use List::MoreUtils qw/uniq/; +use YAML; + +=head1 NAME + +C4::Update::Database.pm + +=head1 SYNOPSIS + + use C4::Update::Database; + + This package is used by admin/updatedatabase.pl, to manage DB updates + +=head1 FUNCTIONS + +=cut + +my $VERSIONS_PATH = C4::Context->config('intranetdir') . '/installer/data/mysql/versions'; + +my $version; +my $list; + +my $dbh = C4::Context->dbh; + +=head2 + + my $file = get_filepath($version); + this sub will return the full path of a given DB update number + +=cut + +sub get_filepath { + my ( $version ) = @_; + my @files = File::Find::Rule->file->name( "$version.sql", "$version.pl" ) ->in( ( $VERSIONS_PATH ) ); + + if ( scalar @files != 1 ) { + die "This version ($version) returned has ".scalar @files." corresponding, need only 1"; + } + + return $files[0]; +} + +=head2 get_md5 + + my $md5 = get_md5($filepath) + returns the md5sum of the selected file. + This is used to check consistency of updates + +=cut +sub get_md5 { + my ( $filepath ) = @_; + open(FILE, $filepath); + + my $ctx = Digest::MD5->new; + $ctx->addfile(*FILE); + my $md5 = $ctx->hexdigest; + close(FILE); + return $md5; +} + +=head2 execute_version + + $result = execute_version($version_number); + Execute an update. + This sub will detect if the number is made through a .pl or a .sql, and behave accordingly + if there is more than 1 file with the same number, an error will be issued + if you try to execute a version_number that has already be executed, then it will also issue an error + the sub return an result hash, with the version number and the result + +=cut + +sub execute_version { + my ( $version ) = @_; + my $report; + + my $filepath; + eval { + $filepath = get_filepath $version; + }; + if ( $@ ) { + return { $version => $@ }; + } + + my @file_infos = fileparse( $filepath, qr/\.[^.]*/ ); + my $extension = $file_infos[2]; + my $filename = $version . $extension; + + my $md5 = get_md5 $filepath; + my $r = md5_already_exists( $md5 ); + if ( scalar @$r ) { + my $p = @$r[0]; + $report->{$version} = { + error => "ALREADY_EXISTS", + filepath => $filepath, + old_version => @$r[0]->{version}, + md5 => @$r[0]->{md5}, + }; + return $report; + } + + my $queries; + given ( $extension ) { + when ( /.sql/ ) { + $queries = get_queries ( $filepath ); + } + when ( /.pl/ ) { + unless(do $filepath) { + $report->{$version} = { + error => "PERL_DBREV_FAILED", + filename => $filename, + }; + } + } + default { + $report->{$version} = { + error => "BAD_EXTENSION", + extension => $extension, + }; + } + } + + return $report + if ( defined $report->{$version} ); + + my $errors; + for my $query ( @{$queries->{queries}} ) { + eval { + check_coherency( $query ); + }; + if ( $@ ) { + push @$errors, $@ + } + } + + if ( $errors ) { + $_ =~ s/at [^ ]* line \d*\.$// for @$errors; + $report->{$version} = $errors; + return $report; + } + + $errors = execute ( $queries ) if $queries; + $report->{$version} = scalar( @$errors ) ? $errors : "OK"; + set_infos ( $version, $queries, $errors, $md5 ); + return $report; +} + +=head2 list_versions_availables + + my @versions = list_versions_availables; + return an array with all version available + This list is retrieved from the directory defined in the etc/update/database/config.yaml, versions_dir parameter + +=cut + +sub list_versions_availables { + my @versions; + + my @files = File::Find::Rule->file->name( "*.sql", "*.pl" ) ->in( ( $VERSIONS_PATH ) ); + + for my $f ( @files ) { + my @file_infos = fileparse( $f, qr/\.[^.]*/ ); + push @versions, $file_infos[0]; + } + @versions = uniq @versions; + return \@versions; +} + +=head2 list_versions_already_knows + + my @versions = list_versions_availables; + return an array with all version that have already been applied + This sub check first that the updatedb tables exist and create them if needed + +=cut + +sub list_versions_already_knows { + # 1st check if tables exist, otherwise create them + $dbh->do(qq{ + CREATE TABLE IF NOT EXISTS `updatedb_error` ( `version` varchar(32) DEFAULT NULL, `error` text ) ENGINE=InnoDB CHARSET=utf8; + }); + $dbh->do(qq{ + CREATE TABLE IF NOT EXISTS `updatedb_query` ( `version` varchar(32) DEFAULT NULL, `query` text ) ENGINE=InnoDB CHARSET=utf8; + }); + $dbh->do(qq{ + 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; + }); + + my $query = qq/ SELECT version, comment, status FROM updatedb_report ORDER BY version/; + my $sth = $dbh->prepare( $query ); + $sth->execute; + my $versions = $sth->fetchall_arrayref( {} ); + map { + my $version = $_; + my @comments = defined $_->{comment} ? split '\\\n', $_->{comment} : ""; + push @{ $version->{comments} }, { comment => $_ } for @comments; + delete $version->{comment}; + } @$versions; + $sth->finish; + for my $version ( @$versions ) { + $query = qq/ SELECT query FROM updatedb_query WHERE version = ? ORDER BY version/; + $sth = $dbh->prepare( $query ); + $sth->execute( $version->{version} ); + $version->{queries} = $sth->fetchall_arrayref( {} ); + $sth->finish; + $query = qq/ SELECT error FROM updatedb_error WHERE version = ? ORDER BY version/; + $sth = $dbh->prepare( $query ); + $sth->execute( $version->{version} ); + $version->{errors} = $sth->fetchall_arrayref( {} ); + $sth->finish; + } + return $versions; +} + +=head2 execute + + my @errors = $execute(\@queries); + This sub will execute queries coming from an execute_version based on a .sql file + +=cut + +sub execute { + my ( $queries ) = @_; + my @errors; + for my $query ( @{$queries->{queries}} ) { + eval { + $dbh->do( $query ); + }; + push @errors, get_error(); + } + return \@errors; +} + +=head2 get_tables_name + + my $tables = get_tables_name; + return an array with all Koha mySQL table names + +=cut + +sub get_tables_name { + my $sth = $dbh->prepare("SHOW TABLES"); + $sth->execute(); + my @tables; + while ( my ( $table ) = $sth->fetchrow_array ) { + push @tables, $table; + } + return \@tables; +} +my $tables; + +=head2 check_coherency + + my $errors = check_coherency($query); + This sub will try to check if a SQL query is useless or no. + for queries that are CREATE TABLE, it will check if the table already exists + for queries that are ALTER TABLE, it will search if the modification has already been made + for queries that are INSERT, it will search if the insert has already been made if it's a syspref or a permission + + Those test cover 90% of the updatedatabases cases. That will help finding duplicate or inconsistencies + +=cut + +sub check_coherency { + my ( $query ) = @_; + $tables = get_tables_name() if not $tables; + + given ( $query ) { + when ( /CREATE TABLE(?:.*?)? `?(\w+)`?/ ) { + my $table_name = $1; + if ( grep { /$table_name/ } @$tables ) { + die "COHERENCY: Table $table_name already exists"; + } + } + + when ( /ALTER TABLE *`?(\w+)`? *ADD *(?:COLUMN)? `?(\w+)`?/ ) { + my $table_name = $1; + my $column_name = $2; + next if $column_name =~ /(UNIQUE|CONSTRAINT|INDEX|KEY|FOREIGN)/; + if ( not grep { /$table_name/ } @$tables ) { + return "COHERENCY: Table $table_name does not exist"; + } else { + my $sth = $dbh->prepare( "DESC $table_name $column_name" ); + my $rv = $sth->execute; + if ( $rv > 0 ) { + die "COHERENCY: Field $table_name.$column_name already exists"; + } + } + } + + when ( /INSERT INTO `?(\w+)`?.*?VALUES *\((.*?)\)/ ) { + my $table_name = $1; + my @values = split /,/, $2; + s/^ *'// foreach @values; + s/' *$// foreach @values; + given ( $table_name ) { + when ( /systempreferences/ ) { + my $syspref = $values[0]; + my $sth = $dbh->prepare( "SELECT COUNT(*) FROM systempreferences WHERE variable = ?" ); + $sth->execute( $syspref ); + if ( ( my $count = $sth->fetchrow_array ) > 0 ) { + die "COHERENCY: Syspref $syspref already exists"; + } + } + + when ( /permissions/){ + my $module_bit = $values[0]; + my $code = $values[1]; + my $sth = $dbh->prepare( "SELECT COUNT(*) FROM permissions WHERE module_bit = ? AND code = ?" ); + $sth->execute($module_bit, $code); + if ( ( my $count = $sth->fetchrow_array ) > 0 ) { + die "COHERENCY: Permission $code already exists"; + } + } + } + } + } + return 1; +} + +=head2 get_error + + my $errors = get_error() + This sub will return any mySQL error that occured during an update + +=cut + +sub get_error { + my @errors = $dbh->selectrow_array(qq{SHOW ERRORS}); # Get errors + my @warnings = $dbh->selectrow_array(qq{SHOW WARNINGS}); # Get warnings + if ( @errors ) { # Catch specifics errors + return qq{$errors[0] : $errors[1] => $errors[2]}; + } elsif ( @warnings ) { + return qq{$warnings[0] : $warnings[1] => $warnings[2]} + if $warnings[0] ne 'Note'; + } + return; +} + +=head2 + + my $result = get_queries($filepath); + this sub will return a hashref with 2 entries: + $result->{queries} is an array with all queries to execute + $result->{comments} is an array with all comments in the .sql file + +=cut + +sub get_queries { + my ( $filepath ) = @_; + open my $fh, "<", $filepath; + my @queries; + my @comments; + my $old_delimiter = $/; + while ( <$fh> ) { + my $line = $_; + chomp $line; + $line =~ s/^\s*//; + if ( $line =~ s/^--(.*)$// ) { + push @comments, $1; + next; + } + if ( $line =~ /^delimiter (.*)$/i ) { + $/ = $1; + next; + } + $line =~ s#$/##; + push @queries, $line if not $line =~ /^\s*$/; # Push if query is not empty + } + $/ = $old_delimiter; + close $fh; + + return { queries => \@queries, comments => \@comments }; +} + +=head2 md5_already_exists + + my $result = md5_already_exists($md5); + check if the md5 of an update has already been applied on the database. + If yes, it will return a hash with the version related to this md5 + +=cut + +sub md5_already_exists { + my ( $md5 ) = @_; + my $query = qq/SELECT version, md5 FROM updatedb_report WHERE md5 = ?/; + my $sth = $dbh->prepare( $query ); + $sth->execute( $md5 ); + my @r; + while ( my ( $version, $md5 ) = $sth->fetchrow ) { + push @r, { version => $version, md5 => $md5 }; + } + $sth->finish; + return \@r; +} + +=head2 set_infos + + set_info($version,$queries, $error, $md5); + this sub will insert into the updatedb tables what has been made on the database (queries, errors, result) + +=cut +sub set_infos { + my ( $version, $queries, $errors, $md5 ) = @_; + SetVersion($version) if not -s $errors; + for my $query ( @{ $queries->{queries} } ) { + my $sth = $dbh->prepare("INSERT INTO updatedb_query(version, query) VALUES (?, ?)"); + $sth->execute( $version, $query ); + $sth->finish; + } + for my $error ( @$errors ) { + my $sth = $dbh->prepare("INSERT INTO updatedb_error(version, error) VALUES (?, ?)"); + $sth->execute( $version, $error ); + } + my $sth = $dbh->prepare("INSERT INTO updatedb_report(version, md5, comment, status) VALUES (?, ?, ?, ?)"); + $sth->execute( + $version, + $md5, + $queries&&exists $queries->{comments}? join ('\n', @{ $queries->{comments} }):'', + ( @$errors > 0 ) ? 0 : 1 + ); +} + +=head2 mark_as_ok + + mark_as_ok($version); + this sub will force to mark as "OK" an update that has failed + 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 + +=cut +sub mark_as_ok { + my ( $version ) = @_; + my $sth = $dbh->prepare( "UPDATE updatedb_report SET status = 2 WHERE version=?" ); + my $affected = $sth->execute( $version ); + if ( $affected < 1 ) { + # For "Coherency" + my $filepath = get_filepath $version; + my $queries = get_queries $filepath; + my $errors; + for my $query ( @{$queries->{queries}} ) { + eval { + check_coherency( $query ); + }; + if ( $@ ) { + push @$errors, $@ + } + } + + $_ =~ s/at [^ ]* line \d*\.$// for @$errors; + my $md5 = get_md5 $filepath; + set_infos $version, $queries, $errors, $md5; + + $sth->execute( $version ); + } + $sth->finish; +} + +=head2 is_uptodate + is_uptodate(); + return 1 if the database is up to date else 0. + The database is up to date if all versions are excecuted. +=cut +sub is_uptodate { + my $versions_availables = C4::Update::Database::list_versions_availables; + my $versions = C4::Update::Database::list_versions_already_knows; + for my $v ( @$versions_availables ) { + if ( not grep { $v eq $$_{version} } @$versions ) { + return 0; + } + } + return 1; +} + +=head2 TransformToNum + + Transform the Koha version from a 4 parts string + to a number, with just 1 . (ie: it's a number) + +=cut +sub TransformToNum { + my $version = shift; + + # remove the 3 last . to have a Perl number + $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/; + $version =~ s/Bug(\d+)/$1/; + return $version; +} + +sub SetVersion { + my $new_version = TransformToNum(shift); + my $current_version = TransformToNum( C4::Context->preference('Version') ); + unless ( C4::Context->preference('Version') ) { + my $finish = $dbh->prepare(qq{ + INSERT IGNORE INTO systempreferences (variable,value,explanation) + VALUES ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller') + }); + $finish->execute($new_version); + return; + } + if ( $new_version > $current_version ) { + my $finish = $dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'"); + $finish->execute($new_version); + } +} + +=head2 TableExists($table) + +=cut +sub TableExists { + my $table = shift; + eval { + local $dbh->{PrintError} = 0; + local $dbh->{RaiseError} = 1; + $dbh->do(qq{SELECT * FROM $table WHERE 1 = 0 }); + }; + return 1 unless $@; + return 0; +} + +1; diff --git a/about.pl b/about.pl index daf6829..18729a0 100755 --- a/about.pl +++ b/about.pl @@ -32,6 +32,7 @@ use C4::Output; use C4::Auth; use C4::Context; use C4::Installer; +use C4::Update::Database; #use Smart::Comments '####'; @@ -47,7 +48,45 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); -my $kohaVersion = C4::Context::KOHAVERSION; +my $kohaVersion = C4::Context->preference("Version"); +# restore ., for display consistency +$kohaVersion =~ /(.)\.(..)(..)(...)/; +# transform digits to Perl number, to display 3.6.1.2 instead of 3.06.01.002 +$kohaVersion = ($1+0).".".($2+0).".".($3+0).".".($4+0); + +my $dbrev_applied=""; # the list of database revisions + +# the $kohaVersion is duplicated since 3.7: the 3.6 (that uses the old mechanism) and the 3.7 (new mechanism). +# Both versions reflects how the database has been upgraded +my $already_knows = C4::Update::Database::list_versions_already_knows(); +# $last_known contains the previous DBrev applied number (all . removed). It's used to have a . instead of a number in case of continuous updates +my $last_known=0; +# $last_known_sep contains the previous DBrev applied with the separator (used for display) +my $last_known_sep=""; +for my $v ( @$already_knows ) { + my $current = $v->{version}; + $current =~s/\.//g; + # if the current number is the previous one +1, then just add a ., for a better display N.........N+10, for example + # (instead of N / N+1 / N+2 / ...) + if ($current==$last_known+1) { + $dbrev_applied.="."; + } else { # we're not N+1, start a new range + # if version don't end by a ., no need to add the current loop number + # this avoid having N...N (in case of an isolated BDrev number) + if ($last_known & $dbrev_applied =~ /\.$/) { + $dbrev_applied .= "...".$last_known_sep; + } + # start a new range + $dbrev_applied .= " ".$v->{version}; + } + $last_known= $current; + $last_known_sep=$v->{version}; +} +# add the last DB rev number, we don't want to end with "..." +if ($dbrev_applied =~ /\.$/) { + $dbrev_applied .= "...".$last_known_sep; +} + my $osVersion = `uname -a`; my $perl_path = $^X; if ($^O ne 'VMS') { @@ -74,6 +113,7 @@ my $errZebraConnection = C4::Context->Zconn("biblioserver",0)->errcode(); $template->param( kohaVersion => $kohaVersion, + dbrev_applied => $dbrev_applied, osVersion => $osVersion, perlPath => $perl_path, perlVersion => $perlVersion, diff --git a/admin/ajax-updatedb-getinfos.pl b/admin/ajax-updatedb-getinfos.pl new file mode 100755 index 0000000..25ceda5 --- /dev/null +++ b/admin/ajax-updatedb-getinfos.pl @@ -0,0 +1,61 @@ +#!/usr/bin/perl + +# Copyright 2011 BibLibre +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +=head1 NAME + +ajax-updatedb-getinfos.pl + +=head1 DESCRIPTION +this script returns comments for a updatedatabase version + +=back + +=cut + +use Modern::Perl; +use CGI; +use C4::Update::Database; + +my $input = new CGI; +my $version = $input->param('version'); + +binmode STDOUT, ":utf8"; +print $input->header(-type => 'text/plain', -charset => 'UTF-8'); +my $filepath; +my $queries; +eval { + $filepath = C4::Update::Database::get_filepath( $version ); + $queries = C4::Update::Database::get_queries( $filepath ); +}; +if ( $@ ){ + print $@; + exit; +} + +if ( @{ $$queries{comments} } ) { + print "Comments :
" . join ( "
", @{ $$queries{comments} } ); +} else { + print "No comment
"; +} + +if ( @{ $$queries{queries} } ) { + print "

Queries :
" . join ( "
", @{ $$queries{queries} } ); +} else { + print "
No queries"; +} diff --git a/admin/updatedatabase.pl b/admin/updatedatabase.pl new file mode 100755 index 0000000..5735451 --- /dev/null +++ b/admin/updatedatabase.pl @@ -0,0 +1,103 @@ +#!/usr/bin/perl + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; +use strict; +use CGI; +use C4::Auth; +use C4::Output; +use C4::Update::Database; + +my $query = new CGI; +my $op = $query->param('op') || 'list'; + +my ( $template, $borrowernumber, $cookie ) = get_template_and_user( + { template_name => "admin/updatedatabase.tmpl", + query => $query, + type => "intranet", + authnotrequired => 0, + flagsrequired => { parameters => 1 }, + } +); + +if ( $op eq 'update' ) { + my @versions = $query->param('version'); + @versions = sort { + C4::Update::Database::TransformToNum( $a ) <=> C4::Update::Database::TransformToNum( $b ) + } @versions; + + my @reports; + for my $version ( @versions ) { + push @reports, C4::Update::Database::execute_version $version; + } + + my @report_loop = map { + my ( $v, $r ) = each %$_; + my @errors = ref ( $r ) eq 'ARRAY' + ? + map { + { error => $_ } + } @$r + : + { error => $r }; + { + version => $v, + report => \@errors, + coherency => ( ref ( $r ) eq 'ARRAY' + ? @$r[0] =~ /COHERENCY/ ? 1 : 0 + : $r =~ /COHERENCY/ ? 1 : 0 ) + } + } @reports; + $template->param( report_loop => \@report_loop ); + + $op = 'list'; +} + +if ( $op eq 'mark_as_ok' ) { + my @versions = $query->param('version'); + C4::Update::Database::mark_as_ok $_ for @versions; + $op = 'list'; +} + +if ( $op eq 'list' ) { + my $versions_availables = C4::Update::Database::list_versions_availables; + my $versions = C4::Update::Database::list_versions_already_knows; + + for my $v ( @$versions_availables ) { + if ( not grep { $v eq $$_{version} } @$versions ) { + push @$versions, { + version => $v, + available => 1 + }; + } + } + my @sorted = sort { + C4::Update::Database::TransformToNum( $$a{version} ) <=> C4::Update::Database::TransformToNum( $$b{version} ) + } @$versions; + + my @availables = grep { defined $$_{available} and $$_{available} == 1 } @sorted; + my @v_availables = map { {version => $$_{version}} } @availables; + + $template->param( + dev_mode => $ENV{DEBUG}, + versions => \@sorted, + nb_availables => scalar @availables, + availables => [ map { {version => $$_{version}} } @availables ], + ); +} + +output_html_with_http_headers $query, $cookie, $template->output; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index fc9c5f0..d9eaed4 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2054,6 +2054,27 @@ CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are us REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for database updates +-- +CREATE TABLE`updatedb_error` ( + `version` varchar(32) DEFAULT NULL, + `error` text +) ENGINE=InnoDB CHARSET=utf8; + +CREATE TABLE `updatedb_query` ( + `version` varchar(32) DEFAULT NULL, + `query` text +) ENGINE=InnoDB CHARSET=utf8; + +CREATE TABLE `updatedb_report` ( + `version` text, + `md5` varchar(50) DEFAULT NULL, + `comment` text, + `status` int(1) DEFAULT NULL +) ENGINE=InnoDB CHARSET=utf8; + -- -- Table structure for table `userflags` -- diff --git a/installer/data/mysql/update.pl b/installer/data/mysql/update.pl new file mode 100755 index 0000000..a327913 --- /dev/null +++ b/installer/data/mysql/update.pl @@ -0,0 +1,86 @@ +#!/usr/bin/perl + +use Modern::Perl; + +use C4::Context; +use C4::Update::Database; +use Getopt::Long; + +my $help; +my $version; +my $list; +my $all; +my $min; + +GetOptions( + 'h|help|?' => \$help, + 'm:s' => \$version, + 'l|list' => \$list, + 'a|all' => \$all, + 'min:s' => \$min, +); + +if ( $help or not ($version or $list or $all) ) { + usage(); + exit; +} + +my @reports; +if ( $version ) { + my $report = C4::Update::Database::execute_version($version); + push @reports, $report; +} + +if ( $list ) { + my $versions = C4::Update::Database::list_versions_availables(); + say "Versions availables:"; + say "\t- $_" for @$versions; + $versions = C4::Update::Database::list_versions_already_knows(); + say "Versions already knows:"; + say "\t- $$_{version}" for @$versions; + +} + +if ( $all ) { + my $versions_availables = C4::Update::Database::list_versions_availables(); + my $versions = C4::Update::Database::list_versions_already_knows; + my $min_version = $min + ? $min =~ m/\d\.\d{2}\.\d{2}\.\d{3}/ + ? C4::Update::Database::TransformToNum( $min ) + : $min + : 0; + + for my $v ( @$versions_availables ) { + if ( not grep { $v eq $$_{version} } @$versions + and $v=~/^\d+\.\d+/ + and C4::Update::Database::TransformToNum( $v ) >= $min_version + ) { + my $report = C4::Update::Database::execute_version $v; + push @reports, $report; + } + } +} + +if ( $version or $all ) { + say @reports? "Report:": "Nothing to report"; + for my $report ( @reports ) { + my ( $v, $r ) = each %$report; + if ( ref( $r ) eq 'HASH' ) { + say "\t$v => $r->{error}"; + } else { + say "\t$v => $r"; + } + } +} + +sub usage { + say "update.pl"; + say "This script updates your database for you"; + say "Usage:"; + say "\t-h\tShow this help message"; + say "\t-m\tExecute a given version"; + say "\t-l\tList all the versions"; + say "\t-all\tExecute all available versions"; + say "\t-min\tWith -all, Execute all available versions since a given version"; + say "\t\tCan be X.XX.XX.XXX or X.XXXXXXX"; +} diff --git a/installer/data/mysql/versions/3.09/newupdate.sql b/installer/data/mysql/versions/3.09/newupdate.sql new file mode 100644 index 0000000..ae0ef71 --- /dev/null +++ b/installer/data/mysql/versions/3.09/newupdate.sql @@ -0,0 +1 @@ +# dbrev new update mechanism diff --git a/installer/install.pl b/installer/install.pl index d9feac4..21ebb2c 100755 --- a/installer/install.pl +++ b/installer/install.pl @@ -321,19 +321,19 @@ elsif ( $step && $step == 3 ) { warn "# plack? inserted PERL5LIB $ENV{PERL5LIB}\n"; } - my $cmd = C4::Context->config("intranetdir") . "/installer/data/$info{dbms}/updatedatabase.pl"; - my ($success, $error_code, $full_buf, $stdout_buf, $stderr_buf) = IPC::Cmd::run(command => $cmd, verbose => 0); - - if (@$stdout_buf) { - $template->param(update_report => [ map { { line => $_ } } split(/\n/, join('', @$stdout_buf)) ] ); - $template->param(has_update_succeeds => 1); - } - if (@$stderr_buf) { - $template->param(update_errors => [ map { { line => $_ } } split(/\n/, join('', @$stderr_buf)) ] ); - $template->param(has_update_errors => 1); - warn "The following errors were returned while attempting to run the updatedatabase.pl script:\n"; - foreach my $line (@$stderr_buf) {warn "$line\n";} + my $koha39 = "3.0900026"; + my $cmd; + # Old updatedatabase method + my $current_version = C4::Context->preference('Version'); + if ( $current_version < $koha39 ) { + $cmd = C4::Context->config("intranetdir") . "/installer/data/$info{dbms}/updatedatabase.pl"; + my ($success, $error_code, $full_buf, $stdout_buf, $stderr_buf) = IPC::Cmd::run(command => $cmd, verbose => 0); + print_std( "updatedatabase.pl", $stdout_buf, $stderr_buf ); + $current_version= $koha39; } + $cmd = C4::Context->config("intranetdir") . "/installer/data/$info{dbms}/update.pl -all -min=$current_version"; + my ($success, $error_code, $full_buf, $stdout_buf, $stderr_buf) = IPC::Cmd::run(command => $cmd, verbose => 0); + print_std( "update.pl", $stdout_buf, $stderr_buf ); $template->param( $op => 1 ); } @@ -406,4 +406,20 @@ else { } } } + +sub print_std { + my ( $script, $stdout_buf, $stderr_buf ) = @_; + if (@$stdout_buf) { + $template->param(update_report => [ map { { line => $_ } } split(/\n/, join('', @$stdout_buf)) ] ); + $template->param(has_update_succeeds => 1); + } + if (@$stderr_buf) { + $template->param(update_errors => [ map { { line => $_ } } split(/\n/, join('', @$stderr_buf)) ] ); + $template->param(has_update_errors => 1); + warn "The following errors were returned while attempting to run the $script script:\n"; + foreach my $line (@$stderr_buf) {warn "$line\n";} + } +} + + output_html_with_http_headers $query, $cookie, $template->output; diff --git a/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css b/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css index a8e9d0a..8355bb1 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css +++ b/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css @@ -2133,6 +2133,13 @@ a.localimage img { } div.pager p { margin: 0; + +tr.dragClass td { + background-color: grey; + color: yellow; +} +.underline { + text-decoration : underline; } div#acqui_order_supplierlist > div.supplier { diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt index 34f6578..e37c894 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/about.tt @@ -37,7 +37,7 @@ - + diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt index a85d490..159b354 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt @@ -106,6 +106,13 @@
Z39.50 client targets
Define which servers to query for MARC data in the integrated Z39.50 client.
+ +

Update Database

+
+
Check your updates
+
Verify your database versions and execute new updates
+
+ diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt new file mode 100644 index 0000000..30a268e --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt @@ -0,0 +1,191 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Administration › Update Database +[% INCLUDE 'doc-head-close.inc' %] + + +[% INCLUDE 'datatables-strings.inc' %] + + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
+ +
+
+
+ +

Database update

+ [% IF report_loop %] +
+ Report : +
    + [% FOREACH report_loo IN report_loop %] +
  • + [% report_loo.version %] -- + [% FOREACH r IN report_loo.report %] + [% IF r.error.error == "ALREADY_EXISTS" %] + This file ( [% r.error.filepath %] ) still already execute in version [% r.error.old_version %] : same md5 ([% r.error.md5 %]) + [Mark as OK] + [% ELSIF r.error.error == "LOAD_FUNCTIONS_FAILED" %] + Load functions in [% r.error.filename %] failed + [% ELSIF r.error.error == "BAD_EXTENSION" %] + This extension ([% r.error.extension %]) is not take into account (only .pl or .sql)"; + [% ELSE %] + [% r.error %]; + [% END %] + [% END %] + [% IF report_loo.coherency %] + [Mark as OK] + [% END %] +
  • + [% END %] +
+
+ [% END %] + + [% IF nb_availables %] + Your datebase is not up to date.
+ [% IF nb_availables == 1 %] + 1 update available [UPDATE [% availables.first.version %]] + [% ELSE %] + [% nb_availables %] updates available [UPDATE ALL]: + [% IF ( dev_mode ) %] +
    + [% FOREACH av IN availables %] +
  • [% av.version %] [UPDATE]
  • + [% END %] +
+ [% END %] + [% END %] + [% ELSE %] + Your database is up to date + [% END %] +
+ +
Server information
Koha version: [% kohaVersion |html %]
Koha version: [% kohaVersion |html %] with the following database revisionapplied: [% dbrev_applied|html %]
OS version ('uname -a'): [% osVersion |html %]
Perl interpreter: [% perlPath |html %]
Perl version: [% perlVersion |html %]
+ + + + + + + + + + + [% FOREACH v IN versions %] + + + + + + + + [% END %] + +
DB revisionCommentsStatusAvailabilityDetails
[% v.version %] + [% FOREACH c IN v.comments %] + [% c.comment %] + [% END %] + + [% IF v.available %] + Unknown + [% ELSE %] + [% IF v.status %] + OK + [% IF v.status == 2 %] + [FORCED] + [% END %] + [% ELSE %] + Failed + [% END %] + [% END %] + + [% IF v.available %] + Available + [% IF (dev_mode) %] + [Execute] + [% END %] + [% ELSE %] + Already executed + [% END %] + + + Show details +
+ + + + + + diff --git a/mainpage.pl b/mainpage.pl index f834985..505c166 100755 --- a/mainpage.pl +++ b/mainpage.pl @@ -28,6 +28,7 @@ use C4::NewsChannels; use C4::Review qw/numberofreviews/; use C4::Suggestions qw/CountSuggestion/; use C4::Tags qw/get_count_by_tag_status/; + my $query = new CGI; my ( $template, $loggedinuser, $cookie ) = get_template_and_user( @@ -42,6 +43,8 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); +C4::Auth::version_check($query, 'intranet', $cookie); + my $all_koha_news = &GetNewsToDisplay("koha"); my $koha_news_count = scalar @$all_koha_news; -- 1.7.7.6