1 package SL::DBUpgrade2;
3 use English qw(-no_match_vars);
5 use List::MoreUtils qw(any);
8 use SL::DBUpgrade2::Base;
17 return bless({}, $package)->init(@_);
21 my ($self, %params) = @_;
24 $params{path_suffix} = "-auth";
25 $params{schema} = "auth.";
28 $params{path_suffix} ||= '';
29 $params{schema} ||= '';
30 $params{path} = "sql/Pg-upgrade2" . $params{path_suffix};
32 map { $self->{$_} = $params{$_} } keys %params;
41 sub parse_dbupdate_controls {
42 $::lxdebug->enter_sub();
46 my $form = $self->{form};
47 my $locale = $::locale;
52 my $path = $self->path;
54 foreach my $file_name (<$path/*.sql>, <$path/*.pl>) {
55 next unless (open(IN, $file_name));
57 my $file = $file_name;
67 next unless (/^(--|\#)\s*\@/);
72 my @fields = split(/\s*:\s*/, $_, 2);
73 next unless (scalar(@fields) == 2);
75 if ($fields[0] eq "depends") {
76 push(@{$control->{"depends"}}, split(/\s+/, $fields[1]));
78 $control->{$fields[0]} = $fields[1];
82 next if ($control->{ignore});
84 $control->{charset} = 'UTF-8' if $file =~ m/\.pl$/;
85 $control->{charset} = $control->{charset} || $control->{encoding} || 'UTF-8';
87 if (!$control->{"tag"}) {
88 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
91 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
92 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
95 if (defined($all_controls{$control->{"tag"}})) {
96 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
99 if (!$control->{"description"}) {
100 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
103 $control->{"priority"} *= 1;
104 $control->{"priority"} ||= 1000;
105 $control->{"file"} = $file;
107 delete @{$control}{qw(depth applied)};
109 $all_controls{$control->{"tag"}} = $control;
114 foreach my $control (values(%all_controls)) {
115 foreach my $dependency (@{$control->{"depends"}}) {
116 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
119 map({ $_->{"loop"} = 0; } values(%all_controls));
120 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
123 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
124 values(%all_controls));
126 $self->{all_controls} = \%all_controls;
128 $::lxdebug->leave_sub();
134 $::lxdebug->enter_sub();
136 my ($self, $dbh, $filename, $version_or_control) = @_;
138 my $form = $self->{form};
139 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
144 my $file_charset = 'UTF-8';
147 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
151 $fh->seek(0, SEEK_SET);
156 $_ = SL::Iconv::convert($file_charset, 'UTF-8', $_);
158 # Remove DOS and Unix style line endings.
164 for (my $i = 0; $i < length($_); $i++) {
165 my $char = substr($_, $i, 1);
167 # Are we inside a string?
169 if ($char eq $quote_chars[-1]) {
171 } elsif (length $quote_chars[-1] > 1
172 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
173 $i += length($quote_chars[-1]) - 1;
174 $char = $quote_chars[-1];
181 if (($char eq "'") || ($char eq "\"")) {
182 push(@quote_chars, $char);
184 } elsif ($char eq '$' # start of dollar quoting
185 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
186 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
187 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
188 push @quote_chars, $char = '$' . $tag . '$';
190 } elsif ($char eq ";") {
192 # Query is complete. Send it.
194 $sth = $dbh->prepare($query);
195 if (!$sth->execute()) {
196 my $errstr = $dbh->errstr;
199 $form->dberror("The database update/creation did not succeed. " .
200 "The file ${filename} containing the following " .
201 "query failed:<br>${query}<br>" .
202 "The error message was: ${errstr}<br>" .
203 "All changes in that file have been reverted.");
215 # Insert a space at the end of each line so that queries split
216 # over multiple lines work properly.
218 $query .= @quote_chars ? "\n" : ' ';
222 if (ref($version_or_control) eq "HASH") {
223 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
224 } elsif ($version_or_control) {
225 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
231 $::lxdebug->leave_sub();
234 # Process a Perl script which updates the database.
235 # If the script returns 1 then the update was successful.
236 # Return code "2" means "needs more interaction; unlock
237 # the system and end current request".
238 # All other return codes are fatal errors.
239 sub process_perl_script {
240 $::lxdebug->enter_sub();
242 my ($self, $dbh, $filename, $version_or_control) = @_;
244 my %form_values = map { $_ => $::form->{$_} } qw(dbconnect dbdefault dbhost dbmbkiviunstable dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
248 # setup dbup_ export vars & run script
249 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
251 SL::DBUpgrade2::Base::execute_script(
252 file_name => $filename,
253 tag => $version_or_control->{tag},
255 myconfig => \%dbup_myconfig,
259 my $error = $EVAL_ERROR;
261 $dbh->rollback if 1 != ($result // -1);
263 if (!defined($result)) {
264 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
266 } elsif (1 != $result) {
267 SL::System::InstallationLock->unlock if 2 == $result;
271 if (ref($version_or_control) eq "HASH") {
272 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
273 } elsif ($version_or_control) {
274 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
278 # Clear $::form of values that may have been set so that following
279 # Perl upgrade scripts won't have to work with old data (think of
280 # the usual 'continued' mechanism that's used for determining
281 # whether or not the upgrade form must be displayed).
282 delete @{ $::form }{ keys %{ $::form } };
283 $::form->{$_} = $form_values{$_} for keys %form_values;
285 $::lxdebug->leave_sub();
289 my ($self, $dbh, $filename, $version_or_control) = @_;
291 if ($filename =~ m/sql$/) {
292 $self->process_query($dbh, $filename, $version_or_control);
294 $self->process_perl_script($dbh, $filename, $version_or_control);
298 sub update_available {
299 my ($self, $cur_version) = @_;
303 opendir SQLDIR, "sql/Pg-upgrade" || error("", "sql/Pg-upgrade: $!");
304 my @upgradescripts = grep /Pg-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
307 return ($#upgradescripts > -1);
310 sub update2_available {
311 $::lxdebug->enter_sub();
313 my ($self, $dbh) = @_;
315 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
317 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
319 while (my ($tag) = $sth->fetchrow_array) {
320 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
325 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
327 $::lxdebug->leave_sub();
329 return $needs_update;
332 sub unapplied_upgrade_scripts {
333 my ($self, $dbh) = @_;
335 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
337 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
338 my $sth = $dbh->prepare($query);
339 $sth->execute || $self->{form}->dberror($query);
340 while (my ($tag) = $sth->fetchrow_array()) {
341 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
345 return grep { !$_->{applied} } @all_scripts;
348 sub apply_admin_dbupgrade_scripts {
349 my ($self, $called_from_admin) = @_;
351 return 0 if !$self->{auth};
353 my $dbh = $::auth->dbconnect;
354 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
356 return 0 if !@unapplied_scripts;
358 $self->{form}->{login} ||= 'admin';
360 map { $_->{description} = SL::Iconv::convert($_->{charset}, 'UTF-8', $_->{description}) } values %{ $self->{all_controls} };
362 if ($called_from_admin) {
363 $self->{form}->{title} = $::locale->text('Dataset upgrade');
364 $self->{form}->header;
367 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
369 foreach my $control (@unapplied_scripts) {
370 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
371 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
373 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
376 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
381 sub _check_for_loops {
382 my ($form, $file_name, $controls, $tag, @path) = @_;
386 my $ctrl = $controls->{$tag};
388 if ($ctrl->{"loop"} == 1) {
390 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
392 } elsif ($ctrl->{"loop"} == 0) {
395 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
401 my ($form, $file_name, $message) = @_;
404 my $locale = $::locale;
406 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
409 sub _dbupdate2_calculate_depth {
410 $::lxdebug->enter_sub(2);
412 my ($tree, $tag) = @_;
414 my $node = $tree->{$tag};
416 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
420 foreach $tag (@{$node->{"depends"}}) {
421 _dbupdate2_calculate_depth($tree, $tag);
422 my $value = $tree->{$tag}->{"depth"};
423 $max_depth = $value if ($value > $max_depth);
426 $node->{"depth"} = $max_depth + 1;
428 $::lxdebug->leave_sub(2);
431 sub sort_dbupdate_controls {
434 $self->parse_dbupdate_controls unless $self->{all_controls};
436 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
448 SL::DBUpgrade2 - Parse database upgrade files stored in
449 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
457 # Apply outstanding updates to the authentication database
458 my $scripts = SL::DBUpgrade2->new(
462 $scripts->apply_admin_dbupgrade_scripts(1);
464 # Apply updates to a user database
465 my $scripts = SL::DBUpgrade2->new(
469 User->dbupdate2(form => $form,
470 updater => $scripts->parse_dbupdate_controls,
471 database => $dbname);
475 Database upgrade files are used to upgrade the database structure and
476 content of both the authentication database and the user
477 databases. They're applied when a user logs in. As long as the
478 authentication database is not up to date users cannot log in in
479 general, and the admin has to log in first in order to get his
482 Database scripts form a tree by specifying which upgrade file depends
483 on which other upgrade file. This means that such files are always
484 applied in a well-defined order.
486 Each script is run in a separate transaction. If a script fails the
487 current transaction is rolled back and the whole upgrade process is
488 stopped. The user/admin is required to fix the issue manually.
490 A list of applied upgrade scripts is maintained in a table called
491 C<schema_info> for the user database and C<auth.schema_info>) for the
492 authentication database. They contain the tags, the login name of the
493 user having applied the script and the timestamp when the script was
496 Database upgrade files come in two flavours: SQL files and Perl
497 files. For both there are control fields that determine the order in
498 which they're executed, what charset the scripts are written in
499 etc. The control fields are tag/value pairs contained in comments.
501 =head1 OLD UPGRADE FILES
503 The files in C<sql/Pg-upgrade> are so old that I don't bother
504 documenting them. They're handled by this class, too, but new files
505 are only created as C<Pg-upgrade2> files.
507 =head1 CONTROL FIELDS
511 Control fields for Perl files:
514 # @tag2: some more values
519 Control fields for SQL files:
522 -- @tag2: some more values
525 =head2 TAGS AND THEIR MEANING
527 The following tags are recognized:
533 The name for this file. The C<tag> is also used for dependency
534 resolution (see C<depends>).
540 A description presented to the user when the update is applied.
546 A space-separated list of tags of scripts this particular script
547 depends on. All other upgrades listed in C<depends> will be applied
548 before the current one is applied.
554 The charset this file uses. Defaults to C<ISO-8859-15> if
555 missing. Both terms are recognized.
559 Ordering the scripts by their dependencies alone produces a lot of
560 groups of scripts that could be applied at the same time (e.g. if both
561 B and C depend only on A then B could be applied before C or the other
562 way around). This field determines the order inside such a
563 group. Scripts with lower priority fields are executed before scripts
564 with higher priority fields.
566 If two scripts have equal priorities then their tag name decides.
568 The priority defaults to 1000.
576 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
578 Applies all unapplied upgrade files to the authentication/admin
579 database. The parameter C<$called_from_admin> should be truish if the
580 function is called from the web interface and falsish if it's called
581 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
583 =item C<init %params>
585 Initializes the object. Is called directly from L<new> and should not
590 Creates a new object. Possible parameters are:
596 Path to the upgrade files to parse. Required.
600 C<SL::Form> object to use. Required.
604 Optional parameter defaulting to 0. If trueish then the scripts read
605 are the ones applying to the authentication database.
609 =item C<parse_dbupdate_controls>
611 Parses all files located in C<path> (see L<new>), ananlyzes their
612 control fields, builds the tree, and signals errors if control fields
613 are missing/wrong (e.g. a tag name listed in C<depends> is not
614 found). Sets C<$Self->{all_controls}> to the list of database
617 =item C<process_file $dbh, $filename, $version_or_control>
619 Applies a single database upgrade file. Calls L<process_perl_script>
620 for Perl update files and C<process_query> for SQL update
621 files. Requires an open database handle(C<$dbh>), the file name
622 (C<$filename>) and a hash structure of the file's control fields as
623 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
625 Returns the result of the actual function called.
627 =item C<process_perl_script $dbh, $filename, $version_or_control>
629 Applies a single Perl database upgrade file. Requires an open database
630 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
631 the file's control fields as produced by L<parse_dbupdate_controls>
632 (C<$version_or_control>).
634 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
635 an error is expected. There are two special return values: If the
636 script returns C<1> then the update was successful. Return code C<2>
637 means "needs more interaction from the user; unlock the system and
638 end current upgrade process". All other return codes are fatal errors.
640 Inside the Perl script several local variables exist that can be used:
646 A locale object for translating messages
650 The database handle (inside a transaction).
654 The global C<SL::Form> object.
658 A Perl script can actually implement queries that fail while
659 continueing the process by handling the transaction itself, e.g. with
660 the following function:
663 my ($query, $may_fail) = @_;
665 if (!$dbh->do($query)) {
666 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
672 =item C<process_query $dbh, $filename, $version_or_control>
674 Applies a single SQL database upgrade file. Requires an open database
675 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
676 the file's control fields as produced by L<parse_dbupdate_controls>
677 (C<$version_or_control>).
679 =item C<sort_dbupdate_controls>
681 Sorts the database upgrade scripts according to their C<tag> and
682 C<priority> control fields. Returns a list of their hash
683 representations that can be applied in order.
685 =item C<unapplied_upgrade_scripts $dbh>
687 Returns a list if upgrade scripts (their internal hash representation)
688 that haven't been applied to a database yet. C<$dbh> is an open handle
689 to the database that is checked.
691 Requires that the scripts have been parsed.
693 =item C<update2_available $dbh>
695 Returns trueish if at least one upgrade script hasn't been applied to
696 a database yet. C<$dbh> is an open handle to the database that is
699 Requires that the scripts have been parsed.
709 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>