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 {
43 my $form = $self->{form};
44 my $locale = $::locale;
49 my $path = $self->path;
51 foreach my $file_name (<$path/*.sql>, <$path/*.pl>) {
52 next unless (open(IN, "<:encoding(UTF-8)", $file_name));
54 my $file = $file_name;
65 next unless (/^(--|\#)\s*\@/);
70 my @fields = split(/\s*:\s*/, $_, 2);
71 next unless (scalar(@fields) == 2);
73 if ($fields[0] eq "depends") {
74 push(@{$control->{"depends"}}, split(/\s+/, $fields[1]));
75 } elsif ($fields[0] eq "locales") {
76 push @{$control->{locales}}, $fields[1];
78 $control->{$fields[0]} = $fields[1];
82 next if ($control->{ignore});
84 if (!$control->{"tag"}) {
85 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
88 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
89 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
92 if (defined($all_controls{$control->{"tag"}})) {
93 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
96 if (!$control->{"description"}) {
97 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
100 $control->{"priority"} *= 1;
101 $control->{"priority"} ||= 1000;
102 $control->{"file"} = $file;
104 delete @{$control}{qw(depth applied)};
106 $all_controls{$control->{"tag"}} = $control;
111 foreach my $control (values(%all_controls)) {
112 foreach my $dependency (@{$control->{"depends"}}) {
113 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
116 map({ $_->{"loop"} = 0; } values(%all_controls));
117 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
120 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
121 values(%all_controls));
123 $self->{all_controls} = \%all_controls;
129 $::lxdebug->enter_sub();
131 my ($self, $dbh, $filename, $version_or_control) = @_;
133 my $form = $self->{form};
134 my $fh = IO::File->new($filename, "<:encoding(UTF-8)");
140 return "No such file: $filename" if $self->{return_on_error};
141 $form->error("$filename : $!\n");
147 # Remove DOS and Unix style line endings.
150 for (my $i = 0; $i < length($_); $i++) {
151 my $char = substr($_, $i, 1);
153 # Are we inside a string?
155 if ($char eq $quote_chars[-1]) {
157 } elsif (length $quote_chars[-1] > 1
158 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
159 $i += length($quote_chars[-1]) - 1;
160 $char = $quote_chars[-1];
167 if (($char eq "'") || ($char eq "\"")) {
168 push(@quote_chars, $char);
170 } elsif ($char eq '$' # start of dollar quoting
171 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
172 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
173 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
174 push @quote_chars, $char = '$' . $tag . '$';
176 } elsif ($char eq "-") {
177 if ( substr($_, $i+1, 1) eq "-") {
178 # found a comment outside quote
181 } elsif ($char eq ";") {
183 # Query is complete. Send it.
185 $sth = $dbh->prepare($query);
186 if (!$sth->execute()) {
187 my $errstr = $dbh->errstr;
188 return $errstr // '<unknown database error>' if $self->{return_on_error};
191 if (!ref $version_or_control || ref $version_or_control ne 'HASH' || !$version_or_control->{may_fail}) {
192 $form->dberror("The database update/creation did not succeed. " .
193 "The file ${filename} containing the following " .
194 "query failed:<br>${query}<br>" .
195 "The error message was: ${errstr}<br>" .
196 "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 = %$::form;
245 # setup dbup_ export vars & run script
246 my $old_dbh = SL::DB->client->dbh;
247 SL::DB->client->dbh($dbh);
248 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
250 SL::DBUpgrade2::Base::execute_script(
251 file_name => $filename,
252 tag => $version_or_control->{tag},
254 myconfig => \%dbup_myconfig,
258 my $error = $EVAL_ERROR;
260 SL::DB->client->dbh($old_dbh);
262 $dbh->rollback if 1 != ($result // -1);
264 return $error if $self->{return_on_error} && (1 != ($result // -1));
266 if (!defined($result)) {
267 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
268 $::dispatcher->end_request;
269 } elsif (1 != $result) {
270 SL::System::InstallationLock->unlock if 2 == $result;
271 $::dispatcher->end_request;
274 if (ref($version_or_control) eq "HASH") {
275 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
276 } elsif ($version_or_control) {
277 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
280 $dbh->commit if !$dbh->{AutoCommit} || $dbh->{BegunWork};
282 # Clear $::form of values that may have been set so that following
283 # Perl upgrade scripts won't have to work with old data (think of
284 # the usual 'continued' mechanism that's used for determining
285 # whether or not the upgrade form must be displayed).
286 delete @{ $::form }{ keys %{ $::form } };
287 $::form->{$_} = $form_values{$_} for keys %form_values;
289 $::lxdebug->leave_sub();
295 my ($self, $dbh, $filename, $version_or_control) = @_;
297 return $filename =~ m/sql$/ ? $self->process_query( $dbh, $filename, $version_or_control)
298 : $self->process_perl_script($dbh, $filename, $version_or_control);
301 sub unapplied_upgrade_scripts {
302 my ($self, $dbh) = @_;
304 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
306 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
307 my $sth = $dbh->prepare($query);
308 $sth->execute || $self->{form}->dberror($query);
309 while (my ($tag) = $sth->fetchrow_array()) {
310 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
314 return grep { !$_->{applied} } @all_scripts;
317 sub update2_available {
318 my ($self, $dbh) = @_;
320 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
322 return !!@unapplied_scripts;
325 sub apply_admin_dbupgrade_scripts {
326 my ($self, $called_from_admin) = @_;
328 return 0 if !$self->{auth};
330 my $dbh = $::auth->dbconnect;
331 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
333 return 0 if !@unapplied_scripts;
335 $self->{form}->{login} ||= 'admin';
337 if ($called_from_admin) {
338 $self->{form}->{title} = $::locale->text('Dataset upgrade');
339 $self->{form}->header;
342 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
344 foreach my $control (@unapplied_scripts) {
345 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
346 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
348 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
351 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
356 sub _check_for_loops {
357 my ($form, $file_name, $controls, $tag, @path) = @_;
361 my $ctrl = $controls->{$tag};
363 if ($ctrl->{"loop"} == 1) {
365 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
367 } elsif ($ctrl->{"loop"} == 0) {
370 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
376 my ($form, $file_name, $message) = @_;
379 my $locale = $::locale;
381 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
384 sub _dbupdate2_calculate_depth {
385 my ($tree, $tag) = @_;
387 my $node = $tree->{$tag};
389 return if (defined($node->{"depth"}));
393 foreach $tag (@{$node->{"depends"}}) {
394 _dbupdate2_calculate_depth($tree, $tag);
395 my $value = $tree->{$tag}->{"depth"};
396 $max_depth = $value if ($value > $max_depth);
399 $node->{"depth"} = $max_depth + 1;
402 sub sort_dbupdate_controls {
405 $self->parse_dbupdate_controls unless $self->{all_controls};
407 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
419 SL::DBUpgrade2 - Parse database upgrade files stored in
420 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth>
427 # Apply outstanding updates to the authentication database
428 my $scripts = SL::DBUpgrade2->new(
432 $scripts->apply_admin_dbupgrade_scripts(1);
434 # Apply updates to a user database
435 my $scripts = SL::DBUpgrade2->new(
439 User->dbupdate2(form => $form,
440 updater => $scripts->parse_dbupdate_controls,
441 database => $dbname);
445 Database upgrade files are used to upgrade the database structure and
446 content of both the authentication database and the user
447 databases. They're applied when a user logs in. As long as the
448 authentication database is not up to date users cannot log in in
449 general, and the admin has to log in first in order to get his
452 Database scripts form a tree by specifying which upgrade file depends
453 on which other upgrade file. This means that such files are always
454 applied in a well-defined order.
456 Each script is run in a separate transaction. If a script fails the
457 current transaction is rolled back and the whole upgrade process is
458 stopped. The user/admin is required to fix the issue manually.
460 A list of applied upgrade scripts is maintained in a table called
461 C<schema_info> for the user database and C<auth.schema_info>) for the
462 authentication database. They contain the tags, the login name of the
463 user having applied the script and the timestamp when the script was
466 Database upgrade files come in two flavours: SQL files and Perl
467 files. For both there are control fields that determine the order in
468 which they're executed etc. The control fields are tag/value pairs
469 contained in comments.
471 =head1 CONTROL FIELDS
475 Control fields for Perl files:
478 # @tag2: some more values
483 Control fields for SQL files:
486 -- @tag2: some more values
489 =head2 TAGS AND THEIR MEANING
491 The following tags are recognized:
497 The name for this file. The C<tag> is also used for dependency
498 resolution (see C<depends>).
504 A description presented to the user when the update is applied.
510 A space-separated list of tags of scripts this particular script
511 depends on. All other upgrades listed in C<depends> will be applied
512 before the current one is applied.
516 Ordering the scripts by their dependencies alone produces a lot of
517 groups of scripts that could be applied at the same time (e.g. if both
518 B and C depend only on A then B could be applied before C or the other
519 way around). This field determines the order inside such a
520 group. Scripts with lower priority fields are executed before scripts
521 with higher priority fields.
523 If two scripts have equal priorities then their tag name decides.
525 The priority defaults to 1000.
533 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
535 Applies all unapplied upgrade files to the authentication/admin
536 database. The parameter C<$called_from_admin> should be truish if the
537 function is called from the web interface and falsish if it's called
538 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
540 =item C<init %params>
542 Initializes the object. Is called directly from L<new> and should not
547 Creates a new object. Possible parameters are:
553 Path to the upgrade files to parse. Required.
557 C<SL::Form> object to use. Required.
561 Optional parameter defaulting to 0. If trueish then the scripts read
562 are the ones applying to the authentication database.
566 =item C<parse_dbupdate_controls>
568 Parses all files located in C<path> (see L<new>), ananlyzes their
569 control fields, builds the tree, and signals errors if control fields
570 are missing/wrong (e.g. a tag name listed in C<depends> is not
571 found). Sets C<$Self->{all_controls}> to the list of database
574 =item C<process_file $dbh, $filename, $version_or_control>
576 Applies a single database upgrade file. Calls L<process_perl_script>
577 for Perl update files and C<process_query> for SQL update
578 files. Requires an open database handle(C<$dbh>), the file name
579 (C<$filename>) and a hash structure of the file's control fields as
580 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
582 Returns the result of the actual function called.
584 =item C<process_perl_script $dbh, $filename, $version_or_control>
586 Applies a single Perl database upgrade file. Requires an open database
587 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
588 the file's control fields as produced by L<parse_dbupdate_controls>
589 (C<$version_or_control>).
591 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
592 an error is expected. There are two special return values: If the
593 script returns C<1> then the update was successful. Return code C<2>
594 means "needs more interaction from the user; unlock the system and
595 end current upgrade process". All other return codes are fatal errors.
597 Inside the Perl script several local variables exist that can be used:
603 A locale object for translating messages
607 The database handle (inside a transaction).
611 The global C<SL::Form> object.
615 A Perl script can actually implement queries that fail while
616 continueing the process by handling the transaction itself, e.g. with
617 the following function:
620 my ($query, $may_fail) = @_;
622 if (!$dbh->do($query)) {
623 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
629 =item C<process_query $dbh, $filename, $version_or_control>
631 Applies a single SQL database upgrade file. Requires an open database
632 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
633 the file's control fields as produced by L<parse_dbupdate_controls>
634 (C<$version_or_control>).
636 =item C<sort_dbupdate_controls>
638 Sorts the database upgrade scripts according to their C<tag> and
639 C<priority> control fields. Returns a list of their hash
640 representations that can be applied in order.
642 =item C<unapplied_upgrade_scripts $dbh>
644 Returns a list if upgrade scripts (their internal hash representation)
645 that haven't been applied to a database yet. C<$dbh> is an open handle
646 to the database that is checked.
648 Requires that the scripts have been parsed.
650 =item C<update2_available $dbh>
652 Returns trueish if at least one upgrade script hasn't been applied to
653 a database yet. C<$dbh> is an open handle to the database that is
656 Requires that the scripts have been parsed.
666 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>