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 $control->{"priority"} *= 1;
102 $control->{"priority"} ||= 1000;
103 $control->{"file"} = $file;
105 delete @{$control}{qw(depth applied)};
107 $all_controls{$control->{"tag"}} = $control;
112 foreach my $control (values(%all_controls)) {
113 foreach my $dependency (@{$control->{"depends"}}) {
114 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
117 map({ $_->{"loop"} = 0; } values(%all_controls));
118 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
121 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
122 values(%all_controls));
124 $self->{all_controls} = \%all_controls;
130 $::lxdebug->enter_sub();
132 my ($self, $dbh, $filename, $version_or_control) = @_;
134 my $form = $self->{form};
135 my $fh = IO::File->new($filename, "<:encoding(UTF-8)");
141 return "No such file: $filename" if $self->{return_on_error};
142 $form->error("$filename : $!\n");
148 # Remove DOS and Unix style line endings.
151 for (my $i = 0; $i < length($_); $i++) {
152 my $char = substr($_, $i, 1);
154 # Are we inside a string?
156 if ($char eq $quote_chars[-1]) {
158 } elsif (length $quote_chars[-1] > 1
159 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
160 $i += length($quote_chars[-1]) - 1;
161 $char = $quote_chars[-1];
168 if (($char eq "'") || ($char eq "\"")) {
169 push(@quote_chars, $char);
171 } elsif ($char eq '$' # start of dollar quoting
172 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
173 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
174 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
175 push @quote_chars, $char = '$' . $tag . '$';
177 } elsif ($char eq "-") {
178 if ( substr($_, $i+1, 1) eq "-") {
179 # found a comment outside quote
182 } elsif ($char eq ";") {
184 # Query is complete. Send it.
186 $sth = $dbh->prepare($query);
187 if (!$sth->execute()) {
188 my $errstr = $dbh->errstr;
189 return $errstr // '<unknown database error>' if $self->{return_on_error};
192 if (!ref $version_or_control || ref $version_or_control ne 'HASH' || !$version_or_control->{may_fail}) {
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.")
210 # Insert a space at the end of each line so that queries split
211 # over multiple lines work properly.
213 $query .= @quote_chars ? "\n" : ' ';
217 if (ref($version_or_control) eq "HASH") {
218 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
219 } elsif ($version_or_control) {
220 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
226 $::lxdebug->leave_sub();
232 # Process a Perl script which updates the database.
233 # If the script returns 1 then the update was successful.
234 # Return code "2" means "needs more interaction; unlock
235 # the system and end current request".
236 # All other return codes are fatal errors.
237 sub process_perl_script {
238 $::lxdebug->enter_sub();
240 my ($self, $dbh, $filename, $version_or_control) = @_;
242 my %form_values = %$::form;
246 # setup dbup_ export vars & run script
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 $dbh->rollback if 1 != ($result // -1);
261 return $error if $self->{return_on_error} && (1 != ($result // -1));
263 if (!defined($result)) {
264 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
265 $::dispatcher->end_request;
266 } elsif (1 != $result) {
267 SL::System::InstallationLock->unlock if 2 == $result;
268 $::dispatcher->end_request;
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));
277 $dbh->commit if !$dbh->{AutoCommit} || $dbh->{BegunWork};
279 # Clear $::form of values that may have been set so that following
280 # Perl upgrade scripts won't have to work with old data (think of
281 # the usual 'continued' mechanism that's used for determining
282 # whether or not the upgrade form must be displayed).
283 delete @{ $::form }{ keys %{ $::form } };
284 $::form->{$_} = $form_values{$_} for keys %form_values;
286 $::lxdebug->leave_sub();
292 my ($self, $dbh, $filename, $version_or_control) = @_;
294 return $filename =~ m/sql$/ ? $self->process_query( $dbh, $filename, $version_or_control)
295 : $self->process_perl_script($dbh, $filename, $version_or_control);
298 sub unapplied_upgrade_scripts {
299 my ($self, $dbh) = @_;
301 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
303 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
304 my $sth = $dbh->prepare($query);
305 $sth->execute || $self->{form}->dberror($query);
306 while (my ($tag) = $sth->fetchrow_array()) {
307 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
311 return grep { !$_->{applied} } @all_scripts;
314 sub update2_available {
315 my ($self, $dbh) = @_;
317 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
319 return !!@unapplied_scripts;
322 sub apply_admin_dbupgrade_scripts {
323 my ($self, $called_from_admin) = @_;
325 return 0 if !$self->{auth};
327 my $dbh = $::auth->dbconnect;
328 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
330 return 0 if !@unapplied_scripts;
332 $self->{form}->{login} ||= 'admin';
334 if ($called_from_admin) {
335 $self->{form}->{title} = $::locale->text('Dataset upgrade');
336 $self->{form}->header;
339 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
341 foreach my $control (@unapplied_scripts) {
342 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
343 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
345 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
348 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
353 sub _check_for_loops {
354 my ($form, $file_name, $controls, $tag, @path) = @_;
358 my $ctrl = $controls->{$tag};
360 if ($ctrl->{"loop"} == 1) {
362 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
364 } elsif ($ctrl->{"loop"} == 0) {
367 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
373 my ($form, $file_name, $message) = @_;
376 my $locale = $::locale;
378 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
381 sub _dbupdate2_calculate_depth {
382 my ($tree, $tag) = @_;
384 my $node = $tree->{$tag};
386 return if (defined($node->{"depth"}));
390 foreach $tag (@{$node->{"depends"}}) {
391 _dbupdate2_calculate_depth($tree, $tag);
392 my $value = $tree->{$tag}->{"depth"};
393 $max_depth = $value if ($value > $max_depth);
396 $node->{"depth"} = $max_depth + 1;
399 sub sort_dbupdate_controls {
402 $self->parse_dbupdate_controls unless $self->{all_controls};
404 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
416 SL::DBUpgrade2 - Parse database upgrade files stored in
417 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth>
424 # Apply outstanding updates to the authentication database
425 my $scripts = SL::DBUpgrade2->new(
429 $scripts->apply_admin_dbupgrade_scripts(1);
431 # Apply updates to a user database
432 my $scripts = SL::DBUpgrade2->new(
436 User->dbupdate2(form => $form,
437 updater => $scripts->parse_dbupdate_controls,
438 database => $dbname);
442 Database upgrade files are used to upgrade the database structure and
443 content of both the authentication database and the user
444 databases. They're applied when a user logs in. As long as the
445 authentication database is not up to date users cannot log in in
446 general, and the admin has to log in first in order to get his
449 Database scripts form a tree by specifying which upgrade file depends
450 on which other upgrade file. This means that such files are always
451 applied in a well-defined order.
453 Each script is run in a separate transaction. If a script fails the
454 current transaction is rolled back and the whole upgrade process is
455 stopped. The user/admin is required to fix the issue manually.
457 A list of applied upgrade scripts is maintained in a table called
458 C<schema_info> for the user database and C<auth.schema_info>) for the
459 authentication database. They contain the tags, the login name of the
460 user having applied the script and the timestamp when the script was
463 Database upgrade files come in two flavours: SQL files and Perl
464 files. For both there are control fields that determine the order in
465 which they're executed etc. The control fields are tag/value pairs
466 contained in comments.
468 =head1 CONTROL FIELDS
472 Control fields for Perl files:
475 # @tag2: some more values
480 Control fields for SQL files:
483 -- @tag2: some more values
486 =head2 TAGS AND THEIR MEANING
488 The following tags are recognized:
494 The name for this file. The C<tag> is also used for dependency
495 resolution (see C<depends>).
501 A description presented to the user when the update is applied.
507 A space-separated list of tags of scripts this particular script
508 depends on. All other upgrades listed in C<depends> will be applied
509 before the current one is applied.
513 Ordering the scripts by their dependencies alone produces a lot of
514 groups of scripts that could be applied at the same time (e.g. if both
515 B and C depend only on A then B could be applied before C or the other
516 way around). This field determines the order inside such a
517 group. Scripts with lower priority fields are executed before scripts
518 with higher priority fields.
520 If two scripts have equal priorities then their tag name decides.
522 The priority defaults to 1000.
530 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
532 Applies all unapplied upgrade files to the authentication/admin
533 database. The parameter C<$called_from_admin> should be truish if the
534 function is called from the web interface and falsish if it's called
535 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
537 =item C<init %params>
539 Initializes the object. Is called directly from L<new> and should not
544 Creates a new object. Possible parameters are:
550 Path to the upgrade files to parse. Required.
554 C<SL::Form> object to use. Required.
558 Optional parameter defaulting to 0. If trueish then the scripts read
559 are the ones applying to the authentication database.
563 =item C<parse_dbupdate_controls>
565 Parses all files located in C<path> (see L<new>), ananlyzes their
566 control fields, builds the tree, and signals errors if control fields
567 are missing/wrong (e.g. a tag name listed in C<depends> is not
568 found). Sets C<$Self->{all_controls}> to the list of database
571 =item C<process_file $dbh, $filename, $version_or_control>
573 Applies a single database upgrade file. Calls L<process_perl_script>
574 for Perl update files and C<process_query> for SQL update
575 files. Requires an open database handle(C<$dbh>), the file name
576 (C<$filename>) and a hash structure of the file's control fields as
577 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
579 Returns the result of the actual function called.
581 =item C<process_perl_script $dbh, $filename, $version_or_control>
583 Applies a single Perl database upgrade file. Requires an open database
584 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
585 the file's control fields as produced by L<parse_dbupdate_controls>
586 (C<$version_or_control>).
588 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
589 an error is expected. There are two special return values: If the
590 script returns C<1> then the update was successful. Return code C<2>
591 means "needs more interaction from the user; unlock the system and
592 end current upgrade process". All other return codes are fatal errors.
594 Inside the Perl script several local variables exist that can be used:
600 A locale object for translating messages
604 The database handle (inside a transaction).
608 The global C<SL::Form> object.
612 A Perl script can actually implement queries that fail while
613 continuing the process by handling the transaction itself, e.g. with
614 the following function:
617 my ($query, $may_fail) = @_;
619 if (!$dbh->do($query)) {
620 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
626 =item C<process_query $dbh, $filename, $version_or_control>
628 Applies a single SQL database upgrade file. Requires an open database
629 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
630 the file's control fields as produced by L<parse_dbupdate_controls>
631 (C<$version_or_control>).
633 =item C<sort_dbupdate_controls>
635 Sorts the database upgrade scripts according to their C<tag> and
636 C<priority> control fields. Returns a list of their hash
637 representations that can be applied in order.
639 =item C<unapplied_upgrade_scripts $dbh>
641 Returns a list if upgrade scripts (their internal hash representation)
642 that haven't been applied to a database yet. C<$dbh> is an open handle
643 to the database that is checked.
645 Requires that the scripts have been parsed.
647 =item C<update2_available $dbh>
649 Returns trueish if at least one upgrade script hasn't been applied to
650 a database yet. C<$dbh> is an open handle to the database that is
653 Requires that the scripts have been parsed.
663 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>