1 package SL::DBUpgrade2;
3 use English qw(-no_match_vars);
5 use List::MoreUtils qw(any);
8 use SL::DBUpgrade2::Base;
10 use SL::System::Process;
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} ||= SL::System::Process->exe_dir . "/sql/Pg-upgrade2" . $params{path_suffix};
32 map { $self->{$_} = $params{$_} } keys %params;
41 sub parse_dbupdate_controls {
44 my $form = $self->{form};
45 my $locale = $::locale;
50 my $path = $self->path;
52 foreach my $file_name (<$path/*.sql>, <$path/*.pl>) {
53 next unless (open(IN, "<:encoding(UTF-8)", $file_name));
55 my $file = $file_name;
66 next unless (/^(--|\#)\s*\@/);
71 my @fields = split(/\s*:\s*/, $_, 2);
72 next unless (scalar(@fields) == 2);
74 if ($fields[0] eq "depends") {
75 push(@{$control->{"depends"}}, split(/\s+/, $fields[1]));
76 } elsif ($fields[0] eq "locales") {
77 push @{$control->{locales}}, $fields[1];
79 $control->{$fields[0]} = $fields[1];
83 next if ($control->{ignore});
85 if (!$control->{"tag"}) {
86 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
89 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
90 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
93 if (defined($all_controls{$control->{"tag"}})) {
94 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
97 if (!$control->{"description"}) {
98 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
101 delete @{$control}{qw(depth applied)};
103 my @unknown_keys = grep { !m{^ (?: depends | description | file | ignore | locales | may_fail | priority | tag ) $}x } keys %{ $control };
105 _control_error($form, $file_name, sprintf($locale->text("Unknown control fields: #1", join(' ', sort({ lc $a cmp lc $b } @unknown_keys)))));
108 $control->{"priority"} *= 1;
109 $control->{"priority"} ||= 1000;
110 $control->{"file"} = $file;
112 $all_controls{$control->{"tag"}} = $control;
117 foreach my $control (values(%all_controls)) {
118 foreach my $dependency (@{$control->{"depends"}}) {
119 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
122 map({ $_->{"loop"} = 0; } values(%all_controls));
123 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
126 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
127 values(%all_controls));
129 $self->{all_controls} = \%all_controls;
135 $::lxdebug->enter_sub();
137 my ($self, $dbh, $filename, $version_or_control) = @_;
139 my $form = $self->{form};
140 my $fh = IO::File->new($filename, "<:encoding(UTF-8)");
146 return "No such file: $filename" if $self->{return_on_error};
147 $form->error("$filename : $!\n");
153 # Remove DOS and Unix style line endings.
156 for (my $i = 0; $i < length($_); $i++) {
157 my $char = substr($_, $i, 1);
159 # Are we inside a string?
161 if ($char eq $quote_chars[-1]) {
163 } elsif (length $quote_chars[-1] > 1
164 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
165 $i += length($quote_chars[-1]) - 1;
166 $char = $quote_chars[-1];
173 if (($char eq "'") || ($char eq "\"")) {
174 push(@quote_chars, $char);
176 } elsif ($char eq '$' # start of dollar quoting
177 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
178 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
179 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
180 push @quote_chars, $char = '$' . $tag . '$';
182 } elsif ($char eq "-") {
183 if ( substr($_, $i+1, 1) eq "-") {
184 # found a comment outside quote
187 } elsif ($char eq ";") {
189 # Query is complete. Send it.
191 $sth = $dbh->prepare($query);
192 if (!$sth->execute()) {
193 my $errstr = $dbh->errstr;
194 return $errstr // '<unknown database error>' if $self->{return_on_error};
197 if (!ref $version_or_control || ref $version_or_control ne 'HASH' || !$version_or_control->{may_fail}) {
198 $form->dberror("The database update/creation did not succeed. " .
199 "The file ${filename} containing the following " .
200 "query failed:<br>${query}<br>" .
201 "The error message was: ${errstr}<br>" .
202 "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();
237 # Process a Perl script which updates the database.
238 # If the script returns 1 then the update was successful.
239 # Return code "2" means "needs more interaction; unlock
240 # the system and end current request".
241 # All other return codes are fatal errors.
242 sub process_perl_script {
243 $::lxdebug->enter_sub();
245 my ($self, $dbh, $filename, $version_or_control) = @_;
247 my %form_values = %$::form;
251 # setup dbup_ export vars & run script
252 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
254 SL::DBUpgrade2::Base::execute_script(
255 file_name => $filename,
256 tag => $version_or_control->{tag},
258 myconfig => \%dbup_myconfig,
262 my $error = $EVAL_ERROR;
264 $dbh->rollback if 1 != ($result // -1);
266 return $error if $self->{return_on_error} && (1 != ($result // -1));
268 if (!defined($result)) {
269 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
270 $::dispatcher->end_request;
271 } elsif (1 != $result) {
272 SL::System::InstallationLock->unlock if 2 == $result;
273 $::dispatcher->end_request;
276 if (ref($version_or_control) eq "HASH") {
277 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
278 } elsif ($version_or_control) {
279 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
282 $dbh->commit if !$dbh->{AutoCommit} || $dbh->{BegunWork};
284 # Clear $::form of values that may have been set so that following
285 # Perl upgrade scripts won't have to work with old data (think of
286 # the usual 'continued' mechanism that's used for determining
287 # whether or not the upgrade form must be displayed).
288 delete @{ $::form }{ keys %{ $::form } };
289 $::form->{$_} = $form_values{$_} for keys %form_values;
291 $::lxdebug->leave_sub();
297 my ($self, $dbh, $filename, $version_or_control) = @_;
299 my $result = $filename =~ m/sql$/ ? $self->process_query( $dbh, $filename, $version_or_control)
300 : $self->process_perl_script($dbh, $filename, $version_or_control);
302 $::lxdebug->log_time("DB upgrade script '${filename}' finished");
307 sub unapplied_upgrade_scripts {
308 my ($self, $dbh) = @_;
310 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
312 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
313 my $sth = $dbh->prepare($query);
314 $sth->execute || $self->{form}->dberror($query);
315 while (my ($tag) = $sth->fetchrow_array()) {
316 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
320 return grep { !$_->{applied} } @all_scripts;
323 sub update2_available {
324 my ($self, $dbh) = @_;
326 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
328 return !!@unapplied_scripts;
331 sub apply_admin_dbupgrade_scripts {
332 my ($self, $called_from_admin) = @_;
334 return 0 if !$self->{auth};
336 my $dbh = $::auth->dbconnect;
337 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
339 return 0 if !@unapplied_scripts;
341 $self->{form}->{login} ||= 'admin';
343 if ($called_from_admin) {
344 $self->{form}->{title} = $::locale->text('Dataset upgrade');
345 $self->{form}->header;
348 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
350 $::lxdebug->log_time("DB upgrades commencing");
352 foreach my $control (@unapplied_scripts) {
353 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
354 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
356 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
359 $::lxdebug->log_time("DB upgrades finished");
361 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
366 sub _check_for_loops {
367 my ($form, $file_name, $controls, $tag, @path) = @_;
371 my $ctrl = $controls->{$tag};
373 if ($ctrl->{"loop"} == 1) {
375 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
377 } elsif ($ctrl->{"loop"} == 0) {
380 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
386 my ($form, $file_name, $message) = @_;
389 my $locale = $::locale;
391 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
394 sub _dbupdate2_calculate_depth {
395 my ($tree, $tag) = @_;
397 my $node = $tree->{$tag};
399 return if (defined($node->{"depth"}));
403 foreach $tag (@{$node->{"depends"}}) {
404 _dbupdate2_calculate_depth($tree, $tag);
405 my $value = $tree->{$tag}->{"depth"};
406 $max_depth = $value if ($value > $max_depth);
409 $node->{"depth"} = $max_depth + 1;
412 sub sort_dbupdate_controls {
415 $self->parse_dbupdate_controls unless $self->{all_controls};
417 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
429 SL::DBUpgrade2 - Parse database upgrade files stored in
430 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth>
437 # Apply outstanding updates to the authentication database
438 my $scripts = SL::DBUpgrade2->new(
442 $scripts->apply_admin_dbupgrade_scripts(1);
444 # Apply updates to a user database
445 my $scripts = SL::DBUpgrade2->new(
449 User->dbupdate2(form => $form,
450 updater => $scripts->parse_dbupdate_controls,
451 database => $dbname);
455 Database upgrade files are used to upgrade the database structure and
456 content of both the authentication database and the user
457 databases. They're applied when a user logs in. As long as the
458 authentication database is not up to date users cannot log in in
459 general, and the admin has to log in first in order to get his
462 Database scripts form a tree by specifying which upgrade file depends
463 on which other upgrade file. This means that such files are always
464 applied in a well-defined order.
466 Each script is run in a separate transaction. If a script fails the
467 current transaction is rolled back and the whole upgrade process is
468 stopped. The user/admin is required to fix the issue manually.
470 A list of applied upgrade scripts is maintained in a table called
471 C<schema_info> for the user database and C<auth.schema_info>) for the
472 authentication database. They contain the tags, the login name of the
473 user having applied the script and the timestamp when the script was
476 Database upgrade files come in two flavours: SQL files and Perl
477 files. For both there are control fields that determine the order in
478 which they're executed etc. The control fields are tag/value pairs
479 contained in comments.
481 =head1 CONTROL FIELDS
485 Control fields for Perl files:
488 # @tag2: some more values
493 Control fields for SQL files:
496 -- @tag2: some more values
499 =head2 TAGS AND THEIR MEANING
501 The following tags are recognized:
507 The name for this file. The C<tag> is also used for dependency
508 resolution (see C<depends>).
514 A description presented to the user when the update is applied.
520 A space-separated list of tags of scripts this particular script
521 depends on. All other upgrades listed in C<depends> will be applied
522 before the current one is applied.
526 Ordering the scripts by their dependencies alone produces a lot of
527 groups of scripts that could be applied at the same time (e.g. if both
528 B and C depend only on A then B could be applied before C or the other
529 way around). This field determines the order inside such a
530 group. Scripts with lower priority fields are executed before scripts
531 with higher priority fields.
533 If two scripts have equal priorities then their tag name decides.
535 The priority defaults to 1000.
543 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
545 Applies all unapplied upgrade files to the authentication/admin
546 database. The parameter C<$called_from_admin> should be truish if the
547 function is called from the web interface and falsish if it's called
548 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
550 =item C<init %params>
552 Initializes the object. Is called directly from L<new> and should not
557 Creates a new object. Possible parameters are:
563 Path to the upgrade files to parse. Required.
567 C<SL::Form> object to use. Required.
571 Optional parameter defaulting to 0. If trueish then the scripts read
572 are the ones applying to the authentication database.
576 =item C<parse_dbupdate_controls>
578 Parses all files located in C<path> (see L<new>), ananlyzes their
579 control fields, builds the tree, and signals errors if control fields
580 are missing/wrong (e.g. a tag name listed in C<depends> is not
581 found). Sets C<$Self->{all_controls}> to the list of database
584 =item C<process_file $dbh, $filename, $version_or_control>
586 Applies a single database upgrade file. Calls L<process_perl_script>
587 for Perl update files and C<process_query> for SQL update
588 files. Requires an open database handle(C<$dbh>), the file name
589 (C<$filename>) and a hash structure of the file's control fields as
590 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
592 Returns the result of the actual function called.
594 =item C<process_perl_script $dbh, $filename, $version_or_control>
596 Applies a single Perl database upgrade file. Requires an open database
597 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
598 the file's control fields as produced by L<parse_dbupdate_controls>
599 (C<$version_or_control>).
601 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
602 an error is expected. There are two special return values: If the
603 script returns C<1> then the update was successful. Return code C<2>
604 means "needs more interaction from the user; unlock the system and
605 end current upgrade process". All other return codes are fatal errors.
607 Inside the Perl script several local variables exist that can be used:
613 A locale object for translating messages
617 The database handle (inside a transaction).
621 The global C<SL::Form> object.
625 A Perl script can actually implement queries that fail while
626 continuing the process by handling the transaction itself, e.g. with
627 the following function:
630 my ($query, $may_fail) = @_;
632 if (!$dbh->do($query)) {
633 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
639 =item C<process_query $dbh, $filename, $version_or_control>
641 Applies a single SQL database upgrade file. Requires an open database
642 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
643 the file's control fields as produced by L<parse_dbupdate_controls>
644 (C<$version_or_control>).
646 =item C<sort_dbupdate_controls>
648 Sorts the database upgrade scripts according to their C<tag> and
649 C<priority> control fields. Returns a list of their hash
650 representations that can be applied in order.
652 =item C<unapplied_upgrade_scripts $dbh>
654 Returns a list if upgrade scripts (their internal hash representation)
655 that haven't been applied to a database yet. C<$dbh> is an open handle
656 to the database that is checked.
658 Requires that the scripts have been parsed.
660 =item C<update2_available $dbh>
662 Returns trueish if at least one upgrade script hasn't been applied to
663 a database yet. C<$dbh> is an open handle to the database that is
666 Requires that the scripts have been parsed.
676 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>