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 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;
125 $::lxdebug->leave_sub();
131 $::lxdebug->enter_sub();
133 my ($self, $dbh, $filename, $version_or_control) = @_;
135 my $form = $self->{form};
136 my $fh = IO::File->new($filename, "r");
142 return "No such file: $filename" if $self->{return_on_error};
143 $form->error("$filename : $!\n");
149 $_ = SL::Iconv::convert('UTF-8', 'UTF-8', $_);
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 $::lxdebug->dump(0, "bb: query: ", $query);
189 if (!$sth->execute()) {
190 my $errstr = $dbh->errstr;
191 return $errstr // '<unknown database error>' if $self->{return_on_error};
194 $form->dberror("The database update/creation did not succeed. " .
195 "The file ${filename} containing the following " .
196 "query failed:<br>${query}<br>" .
197 "The error message was: ${errstr}<br>" .
198 "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 = map { $_ => $::form->{$_} } qw(dbconnect dbdefault dbhost dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
246 # setup dbup_ export vars & run script
247 my $old_dbh = $::form->set_standard_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 $::form->set_standard_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 });
269 } elsif (1 != $result) {
270 SL::System::InstallationLock->unlock if 2 == $result;
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 update2_available {
302 $::lxdebug->enter_sub();
304 my ($self, $dbh) = @_;
306 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
308 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
310 while (my ($tag) = $sth->fetchrow_array) {
311 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
316 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
318 $::lxdebug->leave_sub();
320 return $needs_update;
323 sub unapplied_upgrade_scripts {
324 my ($self, $dbh) = @_;
326 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
328 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
329 my $sth = $dbh->prepare($query);
330 $sth->execute || $self->{form}->dberror($query);
331 while (my ($tag) = $sth->fetchrow_array()) {
332 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
336 return grep { !$_->{applied} } @all_scripts;
339 sub apply_admin_dbupgrade_scripts {
340 my ($self, $called_from_admin) = @_;
342 return 0 if !$self->{auth};
344 my $dbh = $::auth->dbconnect;
345 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
347 return 0 if !@unapplied_scripts;
349 $self->{form}->{login} ||= 'admin';
351 map { $_->{description} = SL::Iconv::convert('UTF-8', 'UTF-8', $_->{description}) } values %{ $self->{all_controls} };
353 if ($called_from_admin) {
354 $self->{form}->{title} = $::locale->text('Dataset upgrade');
355 $self->{form}->header;
358 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
360 foreach my $control (@unapplied_scripts) {
361 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
362 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
364 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
367 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
372 sub _check_for_loops {
373 my ($form, $file_name, $controls, $tag, @path) = @_;
377 my $ctrl = $controls->{$tag};
379 if ($ctrl->{"loop"} == 1) {
381 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
383 } elsif ($ctrl->{"loop"} == 0) {
386 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
392 my ($form, $file_name, $message) = @_;
395 my $locale = $::locale;
397 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
400 sub _dbupdate2_calculate_depth {
401 $::lxdebug->enter_sub(2);
403 my ($tree, $tag) = @_;
405 my $node = $tree->{$tag};
407 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
411 foreach $tag (@{$node->{"depends"}}) {
412 _dbupdate2_calculate_depth($tree, $tag);
413 my $value = $tree->{$tag}->{"depth"};
414 $max_depth = $value if ($value > $max_depth);
417 $node->{"depth"} = $max_depth + 1;
419 $::lxdebug->leave_sub(2);
422 sub sort_dbupdate_controls {
425 $self->parse_dbupdate_controls unless $self->{all_controls};
427 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
439 SL::DBUpgrade2 - Parse database upgrade files stored in
440 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth>
447 # Apply outstanding updates to the authentication database
448 my $scripts = SL::DBUpgrade2->new(
452 $scripts->apply_admin_dbupgrade_scripts(1);
454 # Apply updates to a user database
455 my $scripts = SL::DBUpgrade2->new(
459 User->dbupdate2(form => $form,
460 updater => $scripts->parse_dbupdate_controls,
461 database => $dbname);
465 Database upgrade files are used to upgrade the database structure and
466 content of both the authentication database and the user
467 databases. They're applied when a user logs in. As long as the
468 authentication database is not up to date users cannot log in in
469 general, and the admin has to log in first in order to get his
472 Database scripts form a tree by specifying which upgrade file depends
473 on which other upgrade file. This means that such files are always
474 applied in a well-defined order.
476 Each script is run in a separate transaction. If a script fails the
477 current transaction is rolled back and the whole upgrade process is
478 stopped. The user/admin is required to fix the issue manually.
480 A list of applied upgrade scripts is maintained in a table called
481 C<schema_info> for the user database and C<auth.schema_info>) for the
482 authentication database. They contain the tags, the login name of the
483 user having applied the script and the timestamp when the script was
486 Database upgrade files come in two flavours: SQL files and Perl
487 files. For both there are control fields that determine the order in
488 which they're executed etc. The control fields are tag/value pairs
489 contained in comments.
491 =head1 CONTROL FIELDS
495 Control fields for Perl files:
498 # @tag2: some more values
503 Control fields for SQL files:
506 -- @tag2: some more values
509 =head2 TAGS AND THEIR MEANING
511 The following tags are recognized:
517 The name for this file. The C<tag> is also used for dependency
518 resolution (see C<depends>).
524 A description presented to the user when the update is applied.
530 A space-separated list of tags of scripts this particular script
531 depends on. All other upgrades listed in C<depends> will be applied
532 before the current one is applied.
536 Ordering the scripts by their dependencies alone produces a lot of
537 groups of scripts that could be applied at the same time (e.g. if both
538 B and C depend only on A then B could be applied before C or the other
539 way around). This field determines the order inside such a
540 group. Scripts with lower priority fields are executed before scripts
541 with higher priority fields.
543 If two scripts have equal priorities then their tag name decides.
545 The priority defaults to 1000.
553 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
555 Applies all unapplied upgrade files to the authentication/admin
556 database. The parameter C<$called_from_admin> should be truish if the
557 function is called from the web interface and falsish if it's called
558 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
560 =item C<init %params>
562 Initializes the object. Is called directly from L<new> and should not
567 Creates a new object. Possible parameters are:
573 Path to the upgrade files to parse. Required.
577 C<SL::Form> object to use. Required.
581 Optional parameter defaulting to 0. If trueish then the scripts read
582 are the ones applying to the authentication database.
586 =item C<parse_dbupdate_controls>
588 Parses all files located in C<path> (see L<new>), ananlyzes their
589 control fields, builds the tree, and signals errors if control fields
590 are missing/wrong (e.g. a tag name listed in C<depends> is not
591 found). Sets C<$Self->{all_controls}> to the list of database
594 =item C<process_file $dbh, $filename, $version_or_control>
596 Applies a single database upgrade file. Calls L<process_perl_script>
597 for Perl update files and C<process_query> for SQL update
598 files. Requires an open database handle(C<$dbh>), the file name
599 (C<$filename>) and a hash structure of the file's control fields as
600 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
602 Returns the result of the actual function called.
604 =item C<process_perl_script $dbh, $filename, $version_or_control>
606 Applies a single Perl database upgrade file. Requires an open database
607 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
608 the file's control fields as produced by L<parse_dbupdate_controls>
609 (C<$version_or_control>).
611 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
612 an error is expected. There are two special return values: If the
613 script returns C<1> then the update was successful. Return code C<2>
614 means "needs more interaction from the user; unlock the system and
615 end current upgrade process". All other return codes are fatal errors.
617 Inside the Perl script several local variables exist that can be used:
623 A locale object for translating messages
627 The database handle (inside a transaction).
631 The global C<SL::Form> object.
635 A Perl script can actually implement queries that fail while
636 continueing the process by handling the transaction itself, e.g. with
637 the following function:
640 my ($query, $may_fail) = @_;
642 if (!$dbh->do($query)) {
643 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
649 =item C<process_query $dbh, $filename, $version_or_control>
651 Applies a single SQL database upgrade file. Requires an open database
652 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
653 the file's control fields as produced by L<parse_dbupdate_controls>
654 (C<$version_or_control>).
656 =item C<sort_dbupdate_controls>
658 Sorts the database upgrade scripts according to their C<tag> and
659 C<priority> control fields. Returns a list of their hash
660 representations that can be applied in order.
662 =item C<unapplied_upgrade_scripts $dbh>
664 Returns a list if upgrade scripts (their internal hash representation)
665 that haven't been applied to a database yet. C<$dbh> is an open handle
666 to the database that is checked.
668 Requires that the scripts have been parsed.
670 =item C<update2_available $dbh>
672 Returns trueish if at least one upgrade script hasn't been applied to
673 a database yet. C<$dbh> is an open handle to the database that is
676 Requires that the scripts have been parsed.
686 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>