Lines 1-5
Link Here
|
1 |
package C4::Update::Database; |
1 |
package C4::Update::Database; |
2 |
|
2 |
|
|
|
3 |
# Copyright 2011 BibLibre |
4 |
# |
5 |
# This file is part of Koha. |
6 |
# |
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 |
10 |
# version. |
11 |
# |
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. |
15 |
# |
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. |
19 |
|
3 |
use Modern::Perl; |
20 |
use Modern::Perl; |
4 |
|
21 |
|
5 |
use C4::Context; |
22 |
use C4::Context; |
Lines 9-15
use File::Basename;
Link Here
|
9 |
use Digest::MD5; |
26 |
use Digest::MD5; |
10 |
use List::MoreUtils qw/uniq/; |
27 |
use List::MoreUtils qw/uniq/; |
11 |
|
28 |
|
12 |
my $config = C4::Config::File::YAML->new( C4::Context->config("installdir") . qq{/etc/update/database/config.yaml} ); |
29 |
=head1 NAME |
|
|
30 |
|
31 |
C4::Update::Database.pm |
32 |
|
33 |
=head1 SYNOPSIS |
34 |
|
35 |
use C4::Update::Database; |
36 |
|
37 |
This package is used by admin/updatedatabase.pl, to manage DB updates |
38 |
|
39 |
=head1 FUNCTIONS |
40 |
|
41 |
=cut |
42 |
my $config = C4::Config::File::YAML->new( C4::Context->config("intranetdir") . qq{/etc/update/database/config.yaml} ); |
13 |
my $VERSIONS_PATH = C4::Context->config('intranetdir') . '/' . $config->{versions_dir}; |
43 |
my $VERSIONS_PATH = C4::Context->config('intranetdir') . '/' . $config->{versions_dir}; |
14 |
|
44 |
|
15 |
my $version; |
45 |
my $version; |
Lines 17-32
my $list;
Link Here
|
17 |
|
47 |
|
18 |
my $dbh = C4::Context->dbh; |
48 |
my $dbh = C4::Context->dbh; |
19 |
|
49 |
|
|
|
50 |
=head2 |
51 |
|
52 |
my $file = get_filepath($version); |
53 |
this sub will return the full path of a given DB update number |
54 |
|
55 |
=cut |
56 |
|
20 |
sub get_filepath { |
57 |
sub get_filepath { |
21 |
my ( $version ) = @_; |
58 |
my ( $version ) = @_; |
22 |
my @files = <$VERSIONS_PATH/$version*>; |
59 |
my @files = <$VERSIONS_PATH/$version*>; |
23 |
if ( scalar @files != 1 ) { |
60 |
if ( scalar @files != 1 ) { |
24 |
die "This version ($version) returned more than one file (or any) corresponding!"; |
61 |
die "This version ($version) returned has ".scalar @files." corresponding, need only 1"; |
25 |
} |
62 |
} |
26 |
|
63 |
|
27 |
return $files[0]; |
64 |
return $files[0]; |
28 |
} |
65 |
} |
29 |
|
66 |
|
|
|
67 |
=head2 get_md5 |
68 |
|
69 |
my $md5 = get_md5($filepath) |
70 |
returns the md5sum of the selected file. |
71 |
This is used to check consistency of updates |
72 |
|
73 |
=cut |
30 |
sub get_md5 { |
74 |
sub get_md5 { |
31 |
my ( $filepath ) = @_; |
75 |
my ( $filepath ) = @_; |
32 |
open(FILE, $filepath); |
76 |
open(FILE, $filepath); |
Lines 38-43
sub get_md5 {
Link Here
|
38 |
return $md5; |
82 |
return $md5; |
39 |
} |
83 |
} |
40 |
|
84 |
|
|
|
85 |
=head2 execute_version |
86 |
|
87 |
$result = execute_version($version_number); |
88 |
Execute an update. |
89 |
This sub will detect if the number is made through a .pl or a .sql, and behave accordingly |
90 |
if there is more than 1 file with the same number, an error will be issued |
91 |
if you try to execute a version_number that has already be executed, then it will also issue an error |
92 |
the sub return an result hash, with the version number and the result |
93 |
|
94 |
=cut |
95 |
|
41 |
sub execute_version { |
96 |
sub execute_version { |
42 |
my ( $version ) = @_; |
97 |
my ( $version ) = @_; |
43 |
my $report; |
98 |
my $report; |
Lines 58-64
sub execute_version {
Link Here
|
58 |
my $r = md5_already_exists( $md5 ); |
113 |
my $r = md5_already_exists( $md5 ); |
59 |
if ( scalar @$r ) { |
114 |
if ( scalar @$r ) { |
60 |
my $p = @$r[0]; |
115 |
my $p = @$r[0]; |
61 |
$$report{$version} = "This file ( $filepath ) still already execute in version " . @$r[0]->{version} . " : same md5 (" . @$r[0]->{md5} . ")"; |
116 |
$report->{$version} = "This file ( $filepath ) still already execute in version " . @$r[0]->{version} . " : same md5 (" . @$r[0]->{md5} . ")"; |
62 |
return $report; |
117 |
return $report; |
63 |
} |
118 |
} |
64 |
|
119 |
|
Lines 73-91
sub execute_version {
Link Here
|
73 |
if ( do $version_file ) { |
128 |
if ( do $version_file ) { |
74 |
$queries = _get_queries(); |
129 |
$queries = _get_queries(); |
75 |
} else { |
130 |
} else { |
76 |
$$report{$version} = "Load functions in $filename failed"; |
131 |
$report->{$version} = "Load functions in $filename failed"; |
77 |
} |
132 |
} |
78 |
} |
133 |
} |
79 |
default { |
134 |
default { |
80 |
$$report{$version} = "This extension ($extension) is not take into account (only .pl or .sql)"; |
135 |
$report->{$version} = "This extension ($extension) is not take into account (only .pl or .sql)"; |
81 |
} |
136 |
} |
82 |
} |
137 |
} |
|
|
138 |
# check Koha version, and if this patch has a higher number, update it |
139 |
warn "VERSION : ".C4::Context->preference("Version")." against < ".TransformToNum($version); |
140 |
if (C4::Context->preference("Version") < TransformToNum($version)) { |
141 |
SetVersion(TransformToNum($version)); |
142 |
} |
83 |
|
143 |
|
84 |
return $report |
144 |
return $report |
85 |
if ( defined $$report{$version} ); |
145 |
if ( defined $report->{$version} ); |
86 |
|
146 |
|
87 |
my $errors; |
147 |
my $errors; |
88 |
for my $query ( @{$$queries{queries}} ) { |
148 |
for my $query ( @{$queries->{queries}} ) { |
89 |
eval { |
149 |
eval { |
90 |
check_coherency( $query ); |
150 |
check_coherency( $query ); |
91 |
}; |
151 |
}; |
Lines 96-112
sub execute_version {
Link Here
|
96 |
|
156 |
|
97 |
if ( $errors ) { |
157 |
if ( $errors ) { |
98 |
$_ =~ s/at [^ ]* line \d*\.$// for @$errors; |
158 |
$_ =~ s/at [^ ]* line \d*\.$// for @$errors; |
99 |
$$report{$version} = $errors; |
159 |
$report->{$version} = $errors; |
100 |
return $report; |
160 |
return $report; |
101 |
} |
161 |
} |
102 |
|
162 |
|
103 |
$errors = execute ( $queries ); |
163 |
$errors = execute ( $queries ); |
104 |
$$report{$version} = scalar( @$errors ) ? $errors : "OK"; |
164 |
$report->{$version} = scalar( @$errors ) ? $errors : "OK"; |
105 |
set_infos ( $version, $queries, $errors, $md5 ); |
165 |
set_infos ( $version, $queries, $errors, $md5 ); |
106 |
|
|
|
107 |
return $report; |
166 |
return $report; |
108 |
} |
167 |
} |
109 |
|
168 |
|
|
|
169 |
=head2 list_versions_availables |
170 |
|
171 |
my @versions = list_versions_availables; |
172 |
return an array with all version available |
173 |
This list is retrieved from the directory defined in the etc/update/database/config.yaml, versions_dir parameter |
174 |
|
175 |
=cut |
176 |
|
110 |
sub list_versions_availables { |
177 |
sub list_versions_availables { |
111 |
my @versions; |
178 |
my @versions; |
112 |
opendir DH, $VERSIONS_PATH or die "Cannot open directory ($!)"; |
179 |
opendir DH, $VERSIONS_PATH or die "Cannot open directory ($!)"; |
Lines 120-156
sub list_versions_availables {
Link Here
|
120 |
return \@versions; |
187 |
return \@versions; |
121 |
} |
188 |
} |
122 |
|
189 |
|
|
|
190 |
=head2 list_versions_already_knows |
191 |
|
192 |
my @versions = list_versions_availables; |
193 |
return an array with all version that have already been applied |
194 |
This sub check first that the updatedb tables exist and create them if needed |
195 |
|
196 |
=cut |
197 |
|
123 |
sub list_versions_already_knows { |
198 |
sub list_versions_already_knows { |
|
|
199 |
# 1st check if tables exist, otherwise create them |
200 |
$dbh->do(qq{ |
201 |
CREATE TABLE IF NOT EXISTS `updatedb_error` ( `version` varchar(32) DEFAULT NULL, `error` text ) ENGINE=InnoDB CHARSET=utf8; |
202 |
}); |
203 |
$dbh->do(qq{ |
204 |
CREATE TABLE IF NOT EXISTS `updatedb_query` ( `version` varchar(32) DEFAULT NULL, `query` text ) ENGINE=InnoDB CHARSET=utf8; |
205 |
}); |
206 |
$dbh->do(qq{ |
207 |
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; |
208 |
}); |
209 |
|
124 |
my $query = qq/ SELECT version, comment, status FROM updatedb_report /; |
210 |
my $query = qq/ SELECT version, comment, status FROM updatedb_report /; |
125 |
my $sth = $dbh->prepare( $query ); |
211 |
my $sth = $dbh->prepare( $query ); |
126 |
$sth->execute; |
212 |
$sth->execute; |
127 |
my $versions = $sth->fetchall_arrayref( {} ); |
213 |
my $versions = $sth->fetchall_arrayref( {} ); |
128 |
map { |
214 |
map { |
129 |
my $version = $_; |
215 |
my $version = $_; |
130 |
my @comments = defined $$_{comment} ? split '\\\n', $$_{comment} : ""; |
216 |
my @comments = defined $_->{comment} ? split '\\\n', $_->{comment} : ""; |
131 |
push @{ $$version{comments} }, { comment => $_ } for @comments; |
217 |
push @{ $version->{comments} }, { comment => $_ } for @comments; |
132 |
delete $$version{comment}; |
218 |
delete $version->{comment}; |
133 |
} @$versions; |
219 |
} @$versions; |
134 |
$sth->finish; |
220 |
$sth->finish; |
135 |
for my $version ( @$versions ) { |
221 |
for my $version ( @$versions ) { |
136 |
$query = qq/ SELECT query FROM updatedb_query WHERE version = ? /; |
222 |
$query = qq/ SELECT query FROM updatedb_query WHERE version = ? /; |
137 |
$sth = $dbh->prepare( $query ); |
223 |
$sth = $dbh->prepare( $query ); |
138 |
$sth->execute( $$version{version} ); |
224 |
$sth->execute( $version->{version} ); |
139 |
$$version{queries} = $sth->fetchall_arrayref( {} ); |
225 |
$version->{queries} = $sth->fetchall_arrayref( {} ); |
140 |
$sth->finish; |
226 |
$sth->finish; |
141 |
$query = qq/ SELECT error FROM updatedb_error WHERE version = ? /; |
227 |
$query = qq/ SELECT error FROM updatedb_error WHERE version = ? /; |
142 |
$sth = $dbh->prepare( $query ); |
228 |
$sth = $dbh->prepare( $query ); |
143 |
$sth->execute( $$version{version} ); |
229 |
$sth->execute( $version->{version} ); |
144 |
$$version{errors} = $sth->fetchall_arrayref( {} ); |
230 |
$version->{errors} = $sth->fetchall_arrayref( {} ); |
145 |
$sth->finish; |
231 |
$sth->finish; |
146 |
} |
232 |
} |
147 |
return $versions; |
233 |
return $versions; |
148 |
} |
234 |
} |
149 |
|
235 |
|
|
|
236 |
=head2 execute |
237 |
|
238 |
my @errors = $execute(\@queries); |
239 |
This sub will execute queries coming from an execute_version based on a .sql file |
240 |
|
241 |
=cut |
242 |
|
150 |
sub execute { |
243 |
sub execute { |
151 |
my ( $queries ) = @_; |
244 |
my ( $queries ) = @_; |
152 |
my @errors; |
245 |
my @errors; |
153 |
for my $query ( @{$$queries{queries}} ) { |
246 |
for my $query ( @{$queries->{queries}} ) { |
154 |
eval { |
247 |
eval { |
155 |
$dbh->do( $query ); |
248 |
$dbh->do( $query ); |
156 |
}; |
249 |
}; |
Lines 159-164
sub execute {
Link Here
|
159 |
return \@errors; |
252 |
return \@errors; |
160 |
} |
253 |
} |
161 |
|
254 |
|
|
|
255 |
=head2 get_tables_name |
256 |
|
257 |
my $tables = get_tables_name; |
258 |
return an array with all Koha mySQL table names |
259 |
|
260 |
=cut |
261 |
|
162 |
sub get_tables_name { |
262 |
sub get_tables_name { |
163 |
my $sth = $dbh->prepare("SHOW TABLES"); |
263 |
my $sth = $dbh->prepare("SHOW TABLES"); |
164 |
$sth->execute(); |
264 |
$sth->execute(); |
Lines 169-174
sub get_tables_name {
Link Here
|
169 |
return \@tables; |
269 |
return \@tables; |
170 |
} |
270 |
} |
171 |
my $tables; |
271 |
my $tables; |
|
|
272 |
|
273 |
=head2 check_coherency |
274 |
|
275 |
my $errors = check_coherency($query); |
276 |
This sub will try to check if a SQL query is useless or no. |
277 |
for queries that are CREATE TABLE, it will check if the table already exists |
278 |
for queries that are ALTER TABLE, it will search if the modification has already been made |
279 |
for queries that are INSERT, it will search if the insert has already been made if it's a syspref or a permission |
280 |
|
281 |
Those test cover 90% of the updatedatabases cases. That will help finding duplicate or inconsistencies |
282 |
|
283 |
=cut |
284 |
|
172 |
sub check_coherency { |
285 |
sub check_coherency { |
173 |
my ( $query ) = @_; |
286 |
my ( $query ) = @_; |
174 |
$tables = get_tables_name() if not $tables; |
287 |
$tables = get_tables_name() if not $tables; |
Lines 226-231
sub check_coherency {
Link Here
|
226 |
return 1; |
339 |
return 1; |
227 |
} |
340 |
} |
228 |
|
341 |
|
|
|
342 |
=head2 get_error |
343 |
|
344 |
my $errors = get_error() |
345 |
This sub will return any mySQL error that occured during an update |
346 |
|
347 |
=cut |
348 |
|
229 |
sub get_error { |
349 |
sub get_error { |
230 |
my @errors = $dbh->selectrow_array(qq{SHOW ERRORS}); # Get errors |
350 |
my @errors = $dbh->selectrow_array(qq{SHOW ERRORS}); # Get errors |
231 |
my @warnings = $dbh->selectrow_array(qq{SHOW WARNINGS}); # Get warnings |
351 |
my @warnings = $dbh->selectrow_array(qq{SHOW WARNINGS}); # Get warnings |
Lines 238-243
sub get_error {
Link Here
|
238 |
return; |
358 |
return; |
239 |
} |
359 |
} |
240 |
|
360 |
|
|
|
361 |
=head2 |
362 |
|
363 |
my $result = get_queries($filepath); |
364 |
this sub will return a hashref with 2 entries: |
365 |
$result->{queries} is an array with all queries to execute |
366 |
$result->{comments} is an array with all comments in the .sql file |
367 |
|
368 |
=cut |
369 |
|
241 |
sub get_queries { |
370 |
sub get_queries { |
242 |
my ( $filepath ) = @_; |
371 |
my ( $filepath ) = @_; |
243 |
open my $fh, "<", $filepath; |
372 |
open my $fh, "<", $filepath; |
Lines 265-270
sub get_queries {
Link Here
|
265 |
return { queries => \@queries, comments => \@comments }; |
394 |
return { queries => \@queries, comments => \@comments }; |
266 |
} |
395 |
} |
267 |
|
396 |
|
|
|
397 |
=head2 md5_already_exists |
398 |
|
399 |
my $result = md5_already_exists($md5); |
400 |
check if the md5 of an update has already been applied on the database. |
401 |
If yes, it will return a hash with the version related to this md5 |
402 |
|
403 |
=cut |
404 |
|
268 |
sub md5_already_exists { |
405 |
sub md5_already_exists { |
269 |
my ( $md5 ) = @_; |
406 |
my ( $md5 ) = @_; |
270 |
my $query = qq/SELECT version, md5 FROM updatedb_report WHERE md5 = ?/; |
407 |
my $query = qq/SELECT version, md5 FROM updatedb_report WHERE md5 = ?/; |
Lines 278-287
sub md5_already_exists {
Link Here
|
278 |
return \@r; |
415 |
return \@r; |
279 |
} |
416 |
} |
280 |
|
417 |
|
|
|
418 |
=head2 set_infos |
419 |
|
420 |
set_info($version,$queries, $error, $md5); |
421 |
this sub will insert into the updatedb tables what has been made on the database (queries, errors, result) |
422 |
|
423 |
=cut |
281 |
sub set_infos { |
424 |
sub set_infos { |
282 |
my ( $version, $queries, $errors, $md5 ) = @_; |
425 |
my ( $version, $queries, $errors, $md5 ) = @_; |
283 |
#SetVersion($DBversion) if not -s $errors; |
426 |
#SetVersion($DBversion) if not -s $errors; |
284 |
for my $query ( @{ $$queries{queries} } ) { |
427 |
for my $query ( @{ $queries->{queries} } ) { |
285 |
my $sth = $dbh->prepare("INSERT INTO updatedb_query(version, query) VALUES (?, ?)"); |
428 |
my $sth = $dbh->prepare("INSERT INTO updatedb_query(version, query) VALUES (?, ?)"); |
286 |
$sth->execute( $version, $query ); |
429 |
$sth->execute( $version, $query ); |
287 |
$sth->finish; |
430 |
$sth->finish; |
Lines 294-304
sub set_infos {
Link Here
|
294 |
$sth->execute( |
437 |
$sth->execute( |
295 |
$version, |
438 |
$version, |
296 |
$md5, |
439 |
$md5, |
297 |
join ('\n', @{ $$queries{comments} }), |
440 |
join ('\n', @{ $queries->{comments} }), |
298 |
( @$errors > 0 ) ? 0 : 1 |
441 |
( @$errors > 0 ) ? 0 : 1 |
299 |
); |
442 |
); |
300 |
} |
443 |
} |
301 |
|
444 |
|
|
|
445 |
=head2 mark_as_ok |
446 |
|
447 |
$mark_as_ok($version); |
448 |
this sub will force to mark as "OK" an update that has failed |
449 |
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 |
450 |
|
451 |
=cut |
302 |
sub mark_as_ok { |
452 |
sub mark_as_ok { |
303 |
my ( $version ) = @_; |
453 |
my ( $version ) = @_; |
304 |
my $sth = $dbh->prepare( "UPDATE updatedb_report SET status = 2 WHERE version=?" ); |
454 |
my $sth = $dbh->prepare( "UPDATE updatedb_report SET status = 2 WHERE version=?" ); |
Lines 308-314
sub mark_as_ok {
Link Here
|
308 |
my $filepath = get_filepath $version; |
458 |
my $filepath = get_filepath $version; |
309 |
my $queries = get_queries $filepath; |
459 |
my $queries = get_queries $filepath; |
310 |
my $errors; |
460 |
my $errors; |
311 |
for my $query ( @{$$queries{queries}} ) { |
461 |
for my $query ( @{$queries->{queries}} ) { |
312 |
eval { |
462 |
eval { |
313 |
check_coherency( $query ); |
463 |
check_coherency( $query ); |
314 |
}; |
464 |
}; |
Lines 326-335
sub mark_as_ok {
Link Here
|
326 |
$sth->finish; |
476 |
$sth->finish; |
327 |
} |
477 |
} |
328 |
|
478 |
|
329 |
=item TransformToNum |
479 |
=head2 TransformToNum |
330 |
|
480 |
|
331 |
Transform the Koha version from a 4 parts string |
481 |
Transform the Koha version from a 4 parts string |
332 |
to a number, with just 1 . |
482 |
to a number, with just 1 . (ie: it's a number) |
333 |
|
483 |
|
334 |
=cut |
484 |
=cut |
335 |
|
485 |
|