@@ -, +, @@ - css/datatables.css - lib/jquery/plugins/jquery.dataTables.min.js - js/datatables.js patch embedded : YAML Config file add a section /path/to/your/install/dir in your koha-conf.xml --- C4/Update/Database.pm | 357 ++++++++++++++++++++ admin/ajax-updatedb-getinfos.pl | 62 ++++ admin/updatedatabase.pl | 101 ++++++ etc/update/database/config.yaml | 1 + installer/data/mysql/update.pl | 28 ++ installer/data/mysql/updatedatabase.pl | 15 + installer/data/mysql/versions/skeleton.pl | 17 + installer/data/mysql/versions/skeleton.sql | 3 + .../intranet-tmpl/prog/en/css/staff-global.css | 10 +- .../prog/en/modules/admin/admin-home.tt | 7 + .../prog/en/modules/admin/updatedatabase.tt | 171 ++++++++++ 11 files changed, 771 insertions(+), 1 deletions(-) create mode 100644 C4/Update/Database.pm create mode 100755 admin/ajax-updatedb-getinfos.pl create mode 100755 admin/updatedatabase.pl create mode 100644 etc/update/database/config.yaml create mode 100644 installer/data/mysql/update.pl create mode 100755 installer/data/mysql/versions/skeleton.pl create mode 100644 installer/data/mysql/versions/skeleton.sql create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt --- a/C4/Update/Database.pm +++ a/C4/Update/Database.pm @@ -0,0 +1,357 @@ +package C4::Update::Database; + +use Modern::Perl; + +use C4::Context; +use C4::Config::File::YAML; + +use File::Basename; +use Digest::MD5; +use List::MoreUtils qw/uniq/; + +my $config = C4::Config::File::YAML->new( C4::Context->config("installdir") . qq{/etc/update/database/config.yaml} ); +my $VERSIONS_PATH = C4::Context->config('intranetdir') . '/' . $config->{versions_dir}; + +my $version; +my $list; + +my $dbh = C4::Context->dbh; + +sub get_filepath { + my ( $version ) = @_; + my @files = <$VERSIONS_PATH/$version*>; + if ( scalar @files != 1 ) { + die "This version ($version) returned more than one file (or any) corresponding!"; + } + + return $files[0]; +} + +sub get_md5 { + my ( $filepath ) = @_; + open(FILE, $filepath); + + my $ctx = Digest::MD5->new; + $ctx->addfile(*FILE); + my $md5 = $ctx->hexdigest; + close(FILE); + return $md5; +} + +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} = "This file ( $filepath ) still already execute in version " . @$r[0]->{version} . " : same md5 (" . @$r[0]->{md5} . ")"; + return $report; + } + + my $queries; + given ( $extension ) { + when ( /.sql/ ) { + $queries = get_queries ( $filepath ); + } + when ( /.pl/ ) { + my $versions_dir = C4::Context->intranetdir . '/installer/data/mysql/versions/'; + my $version_file = $versions_dir . $filename; + if ( do $version_file ) { + $queries = _get_queries(); + } else { + $$report{$version} = "Load functions in $filename failed"; + } + } + default { + $$report{$version} = "This extension ($extension) is not take into account (only .pl or .sql)"; + } + } + + 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 ); + $$report{$version} = scalar( @$errors ) ? $errors : "OK"; + set_infos ( $version, $queries, $errors, $md5 ); + + return $report; +} + +sub list_versions_availables { + my @versions; + opendir DH, $VERSIONS_PATH or die "Cannot open directory ($!)"; + my @files = grep { !/^\.\.?$/ and /^.*\.(sql|pl)$/ and !/^skeleton/ } readdir DH; + for my $f ( @files ) { + my @file_infos = fileparse( $f, qr/\.[^.]*/ ); + push @versions, $file_infos[0]; + } + @versions = uniq @versions; + closedir DH; + return \@versions; +} + +sub list_versions_already_knows { + my $query = qq/ SELECT version, comment, status FROM updatedb_report /; + 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 = ? /; + $sth = $dbh->prepare( $query ); + $sth->execute( $$version{version} ); + $$version{queries} = $sth->fetchall_arrayref( {} ); + $sth->finish; + $query = qq/ SELECT error FROM updatedb_error WHERE version = ? /; + $sth = $dbh->prepare( $query ); + $sth->execute( $$version{version} ); + $$version{errors} = $sth->fetchall_arrayref( {} ); + $sth->finish; + } + return $versions; +} + +sub execute { + my ( $queries ) = @_; + my @errors; + for my $query ( @{$$queries{queries}} ) { + eval { + $dbh->do( $query ); + }; + push @errors, get_error(); + } + return \@errors; +} + +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; +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; +} + +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; +} + +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 }; +} + +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; +} + +sub set_infos { + my ( $version, $queries, $errors, $md5 ) = @_; + #SetVersion($DBversion) 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, + join ('\n', @{ $$queries{comments} }), + ( @$errors > 0 ) ? 0 : 1 + ); +} + +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; +} + +=item TransformToNum + + Transform the Koha version from a 4 parts string + to a number, with just 1 . + +=cut + +sub TransformToNum { + my $version = shift; + + # remove the 3 last . to have a Perl number + $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/; + return $version; +} + +sub SetVersion { + my $kohaversion = TransformToNum(shift); + if ( C4::Context->preference('Version') ) { + my $finish = $dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'"); + $finish->execute($kohaversion); + } else { + my $finish = $dbh->prepare( +"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($kohaversion); + } +} + +1; --- a/admin/ajax-updatedb-getinfos.pl +++ a/admin/ajax-updatedb-getinfos.pl @@ -0,0 +1,62 @@ +#!/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"; +} + --- a/admin/updatedatabase.pl +++ a/admin/updatedatabase.pl @@ -0,0 +1,101 @@ +#!/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 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 }, # FIXME Add a new flag + } +); + +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( + versions => \@sorted, + nb_availables => scalar @availables, + availables => [ map { {version => $$_{version}} } @availables ], + ); +} + +output_html_with_http_headers $query, $cookie, $template->output; --- a/etc/update/database/config.yaml +++ a/etc/update/database/config.yaml @@ -0,0 +1, @@ +versions_dir: installer/data/mysql/versions --- a/installer/data/mysql/update.pl +++ a/installer/data/mysql/update.pl @@ -0,0 +1,28 @@ +use Modern::Perl; + +use C4::Context; +use C4::Update::Database; +use Getopt::Long; + + +my $version; +my $list; + +GetOptions( + 'm:s' => \$version, + 'l' => \$list, +); + +if ( $version ) { + my $report = C4::Update::Database::execute_version($version); +} + +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; + +} --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -4550,6 +4550,21 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.06.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(qq{ + CREATE TABLE `updatedb_error` ( `version` varchar(32) DEFAULT NULL, `error` text ) ENGINE=InnoDB CHARSET=utf8; + }); + $dbh->do(qq{ + CREATE TABLE `updatedb_query` ( `version` varchar(32) DEFAULT NULL, `query` text ) ENGINE=InnoDB CHARSET=utf8; + }); + $dbh->do(qq{ + CREATE TABLE `updatedb_report` ( `version` text, `md5` varchar(50) DEFAULT NULL, `comment` text, `status` int(1) DEFAULT NULL ) ENGINE=InnoDB CHARSET=utf8; + }); + print "Upgrade to $DBversion done (Add tables for new updatedatabase version)\n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) --- a/installer/data/mysql/versions/skeleton.pl +++ a/installer/data/mysql/versions/skeleton.pl @@ -0,0 +1,17 @@ +#!/usr/bin/perl + +use Modern::Perl; +use C4::Update::Database; + +sub _get_queries { + my @queries = ( + qq{INSERT INTO foo VALUES ('bar1')}, + qq{INSERT INTO foo VALUES ('bar2')}, + ); + my @comments = ( + qq{This is a test}, + ); + return { queries => \@queries, comments => \@comments }; +} + +1; --- a/installer/data/mysql/versions/skeleton.sql +++ a/installer/data/mysql/versions/skeleton.sql @@ -0,0 +1,3 @@ +-- This is a comment +DELIMITER ; +INSERT INTO foo values("bar"); --- a/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css +++ a/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css @@ -2080,4 +2080,12 @@ div.pager input.pagedisplay { background-color : transparent; font-weight: bold; text-align : center; -} +} + +tr.dragClass td { + background-color: grey; + color: yellow; +} +.underline { + text-decoration : underline; +} --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt @@ -98,6 +98,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
+
+ --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/updatedatabase.tt @@ -0,0 +1,171 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Administration › Update Database + +[% INCLUDE 'doc-head-close.inc' %] + + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
+ +
+
+
+ +

Update Database

+ [% IF report_loop %] +
+ Report : +
    + [% FOREACH report_loo IN report_loop %] +
  • + [% report_loo.version %] -- + [% FOREACH r IN report_loo.report %] + [% r.error %]; + [% END %] + [% IF report_loo.coherency %] + [Mark as OK] + [% END %] +
  • + [% END %] +
+
+ [% END %] + + [% IF nb_availables %] + [% nb_availables %] versions are availables [ UPDATE ] + [% ELSE %] + Your database is up to date + [% END %] + + + + + + + + + + + + + + [% FOREACH v IN versions %] + + + + + + + + [% END %] + +
VersionCommentsStatusLaunchDetails
[% v.version %] +
    + [% FOREACH c IN 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 + [Execute] + [% ELSE %] + Already executed + [% END %] + + + Show details +
+ +
+
+
+
+ + --