1 package SL::DBUpgrade2;
3 use English qw(-no_match_vars);
5 use List::MoreUtils qw(any);
8 use SL::DBUpgrade2::Base;
16 return bless({}, $package)->init(@_);
20 my ($self, %params) = @_;
23 $params{path_suffix} = "-auth";
24 $params{schema} = "auth.";
27 $params{path_suffix} ||= '';
28 $params{schema} ||= '';
29 $params{path} = "sql/Pg-upgrade2" . $params{path_suffix};
31 map { $self->{$_} = $params{$_} } keys %params;
40 sub parse_dbupdate_controls {
41 $::lxdebug->enter_sub();
45 my $form = $self->{form};
46 my $locale = $::locale;
51 my $path = $self->path;
53 foreach my $file_name (<$path/*.sql>, <$path/*.pl>) {
54 next unless (open(IN, "<:encoding(UTF-8)", $file_name));
56 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]));
77 } elsif ($fields[0] eq "locales") {
78 push @{$control->{locales}}, $fields[1];
80 $control->{$fields[0]} = $fields[1];
84 next if ($control->{ignore});
86 if (!$control->{"tag"}) {
87 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
90 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
91 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
94 if (defined($all_controls{$control->{"tag"}})) {
95 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
98 if (!$control->{"description"}) {
99 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
102 $control->{"priority"} *= 1;
103 $control->{"priority"} ||= 1000;
104 $control->{"file"} = $file;
106 delete @{$control}{qw(depth applied)};
108 $all_controls{$control->{"tag"}} = $control;
113 foreach my $control (values(%all_controls)) {
114 foreach my $dependency (@{$control->{"depends"}}) {
115 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
118 map({ $_->{"loop"} = 0; } values(%all_controls));
119 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
122 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
123 values(%all_controls));
125 $self->{all_controls} = \%all_controls;
127 $::lxdebug->leave_sub();
133 $::lxdebug->enter_sub();
135 my ($self, $dbh, $filename, $version_or_control) = @_;
137 my $form = $self->{form};
138 my $fh = IO::File->new($filename, "<:encoding(UTF-8)");
144 return "No such file: $filename" if $self->{return_on_error};
145 $form->error("$filename : $!\n");
151 # Remove DOS and Unix style line endings.
157 for (my $i = 0; $i < length($_); $i++) {
158 my $char = substr($_, $i, 1);
160 # Are we inside a string?
162 if ($char eq $quote_chars[-1]) {
164 } elsif (length $quote_chars[-1] > 1
165 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
166 $i += length($quote_chars[-1]) - 1;
167 $char = $quote_chars[-1];
174 if (($char eq "'") || ($char eq "\"")) {
175 push(@quote_chars, $char);
177 } elsif ($char eq '$' # start of dollar quoting
178 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
179 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
180 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
181 push @quote_chars, $char = '$' . $tag . '$';
183 } elsif ($char eq ";") {
185 # Query is complete. Send it.
187 $sth = $dbh->prepare($query);
188 if (!$sth->execute()) {
189 my $errstr = $dbh->errstr;
190 return $errstr // '<unknown database error>' if $self->{return_on_error};
193 $form->dberror("The database update/creation did not succeed. " .
194 "The file ${filename} containing the following " .
195 "query failed:<br>${query}<br>" .
196 "The error message was: ${errstr}<br>" .
197 "All changes in that file have been reverted.");
209 # Insert a space at the end of each line so that queries split
210 # over multiple lines work properly.
212 $query .= @quote_chars ? "\n" : ' ';
216 if (ref($version_or_control) eq "HASH") {
217 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
218 } elsif ($version_or_control) {
219 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
225 $::lxdebug->leave_sub();
231 # Process a Perl script which updates the database.
232 # If the script returns 1 then the update was successful.
233 # Return code "2" means "needs more interaction; unlock
234 # the system and end current request".
235 # All other return codes are fatal errors.
236 sub process_perl_script {
237 $::lxdebug->enter_sub();
239 my ($self, $dbh, $filename, $version_or_control) = @_;
241 my %form_values = map { $_ => $::form->{$_} } qw(dbconnect dbdefault dbhost dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
245 # setup dbup_ export vars & run script
246 my $old_dbh = $::form->set_standard_dbh($dbh);
247 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
249 SL::DBUpgrade2::Base::execute_script(
250 file_name => $filename,
251 tag => $version_or_control->{tag},
253 myconfig => \%dbup_myconfig,
257 my $error = $EVAL_ERROR;
259 $::form->set_standard_dbh($old_dbh);
261 $dbh->rollback if 1 != ($result // -1);
263 return $error if $self->{return_on_error} && (1 != ($result // -1));
265 if (!defined($result)) {
266 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
268 } elsif (1 != $result) {
269 SL::System::InstallationLock->unlock if 2 == $result;
273 if (ref($version_or_control) eq "HASH") {
274 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
275 } elsif ($version_or_control) {
276 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
279 $dbh->commit if !$dbh->{AutoCommit} || $dbh->{BegunWork};
281 # Clear $::form of values that may have been set so that following
282 # Perl upgrade scripts won't have to work with old data (think of
283 # the usual 'continued' mechanism that's used for determining
284 # whether or not the upgrade form must be displayed).
285 delete @{ $::form }{ keys %{ $::form } };
286 $::form->{$_} = $form_values{$_} for keys %form_values;
288 $::lxdebug->leave_sub();
294 my ($self, $dbh, $filename, $version_or_control) = @_;
296 return $filename =~ m/sql$/ ? $self->process_query( $dbh, $filename, $version_or_control)
297 : $self->process_perl_script($dbh, $filename, $version_or_control);
300 sub unapplied_upgrade_scripts {
301 my ($self, $dbh) = @_;
303 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
305 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
306 my $sth = $dbh->prepare($query);
307 $sth->execute || $self->{form}->dberror($query);
308 while (my ($tag) = $sth->fetchrow_array()) {
309 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
313 return grep { !$_->{applied} } @all_scripts;
316 sub update2_available {
317 my ($self, $dbh) = @_;
319 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
321 return !!@unapplied_scripts;
324 sub apply_admin_dbupgrade_scripts {
325 my ($self, $called_from_admin) = @_;
327 return 0 if !$self->{auth};
329 my $dbh = $::auth->dbconnect;
330 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
332 return 0 if !@unapplied_scripts;
334 $self->{form}->{login} ||= 'admin';
336 if ($called_from_admin) {
337 $self->{form}->{title} = $::locale->text('Dataset upgrade');
338 $self->{form}->header;
341 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
343 foreach my $control (@unapplied_scripts) {
344 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
345 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
347 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
350 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
355 sub _check_for_loops {
356 my ($form, $file_name, $controls, $tag, @path) = @_;
360 my $ctrl = $controls->{$tag};
362 if ($ctrl->{"loop"} == 1) {
364 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
366 } elsif ($ctrl->{"loop"} == 0) {
369 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
375 my ($form, $file_name, $message) = @_;
378 my $locale = $::locale;
380 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
383 sub _dbupdate2_calculate_depth {
384 $::lxdebug->enter_sub(2);
386 my ($tree, $tag) = @_;
388 my $node = $tree->{$tag};
390 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
394 foreach $tag (@{$node->{"depends"}}) {
395 _dbupdate2_calculate_depth($tree, $tag);
396 my $value = $tree->{$tag}->{"depth"};
397 $max_depth = $value if ($value > $max_depth);
400 $node->{"depth"} = $max_depth + 1;
402 $::lxdebug->leave_sub(2);
405 sub sort_dbupdate_controls {
408 $self->parse_dbupdate_controls unless $self->{all_controls};
410 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
422 SL::DBUpgrade2 - Parse database upgrade files stored in
423 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth>
430 # Apply outstanding updates to the authentication database
431 my $scripts = SL::DBUpgrade2->new(
435 $scripts->apply_admin_dbupgrade_scripts(1);
437 # Apply updates to a user database
438 my $scripts = SL::DBUpgrade2->new(
442 User->dbupdate2(form => $form,
443 updater => $scripts->parse_dbupdate_controls,
444 database => $dbname);
448 Database upgrade files are used to upgrade the database structure and
449 content of both the authentication database and the user
450 databases. They're applied when a user logs in. As long as the
451 authentication database is not up to date users cannot log in in
452 general, and the admin has to log in first in order to get his
455 Database scripts form a tree by specifying which upgrade file depends
456 on which other upgrade file. This means that such files are always
457 applied in a well-defined order.
459 Each script is run in a separate transaction. If a script fails the
460 current transaction is rolled back and the whole upgrade process is
461 stopped. The user/admin is required to fix the issue manually.
463 A list of applied upgrade scripts is maintained in a table called
464 C<schema_info> for the user database and C<auth.schema_info>) for the
465 authentication database. They contain the tags, the login name of the
466 user having applied the script and the timestamp when the script was
469 Database upgrade files come in two flavours: SQL files and Perl
470 files. For both there are control fields that determine the order in
471 which they're executed etc. The control fields are tag/value pairs
472 contained in comments.
474 =head1 CONTROL FIELDS
478 Control fields for Perl files:
481 # @tag2: some more values
486 Control fields for SQL files:
489 -- @tag2: some more values
492 =head2 TAGS AND THEIR MEANING
494 The following tags are recognized:
500 The name for this file. The C<tag> is also used for dependency
501 resolution (see C<depends>).
507 A description presented to the user when the update is applied.
513 A space-separated list of tags of scripts this particular script
514 depends on. All other upgrades listed in C<depends> will be applied
515 before the current one is applied.
519 Ordering the scripts by their dependencies alone produces a lot of
520 groups of scripts that could be applied at the same time (e.g. if both
521 B and C depend only on A then B could be applied before C or the other
522 way around). This field determines the order inside such a
523 group. Scripts with lower priority fields are executed before scripts
524 with higher priority fields.
526 If two scripts have equal priorities then their tag name decides.
528 The priority defaults to 1000.
536 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
538 Applies all unapplied upgrade files to the authentication/admin
539 database. The parameter C<$called_from_admin> should be truish if the
540 function is called from the web interface and falsish if it's called
541 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
543 =item C<init %params>
545 Initializes the object. Is called directly from L<new> and should not
550 Creates a new object. Possible parameters are:
556 Path to the upgrade files to parse. Required.
560 C<SL::Form> object to use. Required.
564 Optional parameter defaulting to 0. If trueish then the scripts read
565 are the ones applying to the authentication database.
569 =item C<parse_dbupdate_controls>
571 Parses all files located in C<path> (see L<new>), ananlyzes their
572 control fields, builds the tree, and signals errors if control fields
573 are missing/wrong (e.g. a tag name listed in C<depends> is not
574 found). Sets C<$Self->{all_controls}> to the list of database
577 =item C<process_file $dbh, $filename, $version_or_control>
579 Applies a single database upgrade file. Calls L<process_perl_script>
580 for Perl update files and C<process_query> for SQL update
581 files. Requires an open database handle(C<$dbh>), the file name
582 (C<$filename>) and a hash structure of the file's control fields as
583 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
585 Returns the result of the actual function called.
587 =item C<process_perl_script $dbh, $filename, $version_or_control>
589 Applies a single Perl database upgrade file. Requires an open database
590 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
591 the file's control fields as produced by L<parse_dbupdate_controls>
592 (C<$version_or_control>).
594 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
595 an error is expected. There are two special return values: If the
596 script returns C<1> then the update was successful. Return code C<2>
597 means "needs more interaction from the user; unlock the system and
598 end current upgrade process". All other return codes are fatal errors.
600 Inside the Perl script several local variables exist that can be used:
606 A locale object for translating messages
610 The database handle (inside a transaction).
614 The global C<SL::Form> object.
618 A Perl script can actually implement queries that fail while
619 continueing the process by handling the transaction itself, e.g. with
620 the following function:
623 my ($query, $may_fail) = @_;
625 if (!$dbh->do($query)) {
626 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
632 =item C<process_query $dbh, $filename, $version_or_control>
634 Applies a single SQL database upgrade file. Requires an open database
635 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
636 the file's control fields as produced by L<parse_dbupdate_controls>
637 (C<$version_or_control>).
639 =item C<sort_dbupdate_controls>
641 Sorts the database upgrade scripts according to their C<tag> and
642 C<priority> control fields. Returns a list of their hash
643 representations that can be applied in order.
645 =item C<unapplied_upgrade_scripts $dbh>
647 Returns a list if upgrade scripts (their internal hash representation)
648 that haven't been applied to a database yet. C<$dbh> is an open handle
649 to the database that is checked.
651 Requires that the scripts have been parsed.
653 =item C<update2_available $dbh>
655 Returns trueish if at least one upgrade script hasn't been applied to
656 a database yet. C<$dbh> is an open handle to the database that is
659 Requires that the scripts have been parsed.
669 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>