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 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 dbmbkiviunstable dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
245 # setup dbup_ export vars & run script
246 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
248 SL::DBUpgrade2::Base::execute_script(
249 file_name => $filename,
250 tag => $version_or_control->{tag},
252 myconfig => \%dbup_myconfig,
256 my $error = $EVAL_ERROR;
258 $dbh->rollback if 1 != ($result // -1);
260 return $error if $self->{return_on_error} && (1 != ($result // -1));
262 if (!defined($result)) {
263 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
265 } elsif (1 != $result) {
266 SL::System::InstallationLock->unlock if 2 == $result;
270 if (ref($version_or_control) eq "HASH") {
271 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
272 } elsif ($version_or_control) {
273 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
276 $dbh->commit if $dbh->{AutoCommit} && $dbh->{BegunWork};
278 # Clear $::form of values that may have been set so that following
279 # Perl upgrade scripts won't have to work with old data (think of
280 # the usual 'continued' mechanism that's used for determining
281 # whether or not the upgrade form must be displayed).
282 delete @{ $::form }{ keys %{ $::form } };
283 $::form->{$_} = $form_values{$_} for keys %form_values;
285 $::lxdebug->leave_sub();
291 my ($self, $dbh, $filename, $version_or_control) = @_;
293 return $filename =~ m/sql$/ ? $self->process_query( $dbh, $filename, $version_or_control)
294 : $self->process_perl_script($dbh, $filename, $version_or_control);
297 sub update2_available {
298 $::lxdebug->enter_sub();
300 my ($self, $dbh) = @_;
302 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
304 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
306 while (my ($tag) = $sth->fetchrow_array) {
307 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
312 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
314 $::lxdebug->leave_sub();
316 return $needs_update;
319 sub unapplied_upgrade_scripts {
320 my ($self, $dbh) = @_;
322 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
324 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
325 my $sth = $dbh->prepare($query);
326 $sth->execute || $self->{form}->dberror($query);
327 while (my ($tag) = $sth->fetchrow_array()) {
328 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
332 return grep { !$_->{applied} } @all_scripts;
335 sub apply_admin_dbupgrade_scripts {
336 my ($self, $called_from_admin) = @_;
338 return 0 if !$self->{auth};
340 my $dbh = $::auth->dbconnect;
341 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
343 return 0 if !@unapplied_scripts;
345 $self->{form}->{login} ||= 'admin';
347 map { $_->{description} = SL::Iconv::convert('UTF-8', 'UTF-8', $_->{description}) } values %{ $self->{all_controls} };
349 if ($called_from_admin) {
350 $self->{form}->{title} = $::locale->text('Dataset upgrade');
351 $self->{form}->header;
354 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
356 foreach my $control (@unapplied_scripts) {
357 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
358 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
360 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
363 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
368 sub _check_for_loops {
369 my ($form, $file_name, $controls, $tag, @path) = @_;
373 my $ctrl = $controls->{$tag};
375 if ($ctrl->{"loop"} == 1) {
377 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
379 } elsif ($ctrl->{"loop"} == 0) {
382 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
388 my ($form, $file_name, $message) = @_;
391 my $locale = $::locale;
393 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
396 sub _dbupdate2_calculate_depth {
397 $::lxdebug->enter_sub(2);
399 my ($tree, $tag) = @_;
401 my $node = $tree->{$tag};
403 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
407 foreach $tag (@{$node->{"depends"}}) {
408 _dbupdate2_calculate_depth($tree, $tag);
409 my $value = $tree->{$tag}->{"depth"};
410 $max_depth = $value if ($value > $max_depth);
413 $node->{"depth"} = $max_depth + 1;
415 $::lxdebug->leave_sub(2);
418 sub sort_dbupdate_controls {
421 $self->parse_dbupdate_controls unless $self->{all_controls};
423 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
435 SL::DBUpgrade2 - Parse database upgrade files stored in
436 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth>
443 # Apply outstanding updates to the authentication database
444 my $scripts = SL::DBUpgrade2->new(
448 $scripts->apply_admin_dbupgrade_scripts(1);
450 # Apply updates to a user database
451 my $scripts = SL::DBUpgrade2->new(
455 User->dbupdate2(form => $form,
456 updater => $scripts->parse_dbupdate_controls,
457 database => $dbname);
461 Database upgrade files are used to upgrade the database structure and
462 content of both the authentication database and the user
463 databases. They're applied when a user logs in. As long as the
464 authentication database is not up to date users cannot log in in
465 general, and the admin has to log in first in order to get his
468 Database scripts form a tree by specifying which upgrade file depends
469 on which other upgrade file. This means that such files are always
470 applied in a well-defined order.
472 Each script is run in a separate transaction. If a script fails the
473 current transaction is rolled back and the whole upgrade process is
474 stopped. The user/admin is required to fix the issue manually.
476 A list of applied upgrade scripts is maintained in a table called
477 C<schema_info> for the user database and C<auth.schema_info>) for the
478 authentication database. They contain the tags, the login name of the
479 user having applied the script and the timestamp when the script was
482 Database upgrade files come in two flavours: SQL files and Perl
483 files. For both there are control fields that determine the order in
484 which they're executed etc. The control fields are tag/value pairs
485 contained in comments.
487 =head1 CONTROL FIELDS
491 Control fields for Perl files:
494 # @tag2: some more values
499 Control fields for SQL files:
502 -- @tag2: some more values
505 =head2 TAGS AND THEIR MEANING
507 The following tags are recognized:
513 The name for this file. The C<tag> is also used for dependency
514 resolution (see C<depends>).
520 A description presented to the user when the update is applied.
526 A space-separated list of tags of scripts this particular script
527 depends on. All other upgrades listed in C<depends> will be applied
528 before the current one is applied.
532 Ordering the scripts by their dependencies alone produces a lot of
533 groups of scripts that could be applied at the same time (e.g. if both
534 B and C depend only on A then B could be applied before C or the other
535 way around). This field determines the order inside such a
536 group. Scripts with lower priority fields are executed before scripts
537 with higher priority fields.
539 If two scripts have equal priorities then their tag name decides.
541 The priority defaults to 1000.
549 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
551 Applies all unapplied upgrade files to the authentication/admin
552 database. The parameter C<$called_from_admin> should be truish if the
553 function is called from the web interface and falsish if it's called
554 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
556 =item C<init %params>
558 Initializes the object. Is called directly from L<new> and should not
563 Creates a new object. Possible parameters are:
569 Path to the upgrade files to parse. Required.
573 C<SL::Form> object to use. Required.
577 Optional parameter defaulting to 0. If trueish then the scripts read
578 are the ones applying to the authentication database.
582 =item C<parse_dbupdate_controls>
584 Parses all files located in C<path> (see L<new>), ananlyzes their
585 control fields, builds the tree, and signals errors if control fields
586 are missing/wrong (e.g. a tag name listed in C<depends> is not
587 found). Sets C<$Self->{all_controls}> to the list of database
590 =item C<process_file $dbh, $filename, $version_or_control>
592 Applies a single database upgrade file. Calls L<process_perl_script>
593 for Perl update files and C<process_query> for SQL update
594 files. Requires an open database handle(C<$dbh>), the file name
595 (C<$filename>) and a hash structure of the file's control fields as
596 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
598 Returns the result of the actual function called.
600 =item C<process_perl_script $dbh, $filename, $version_or_control>
602 Applies a single Perl database upgrade file. Requires an open database
603 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
604 the file's control fields as produced by L<parse_dbupdate_controls>
605 (C<$version_or_control>).
607 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
608 an error is expected. There are two special return values: If the
609 script returns C<1> then the update was successful. Return code C<2>
610 means "needs more interaction from the user; unlock the system and
611 end current upgrade process". All other return codes are fatal errors.
613 Inside the Perl script several local variables exist that can be used:
619 A locale object for translating messages
623 The database handle (inside a transaction).
627 The global C<SL::Form> object.
631 A Perl script can actually implement queries that fail while
632 continueing the process by handling the transaction itself, e.g. with
633 the following function:
636 my ($query, $may_fail) = @_;
638 if (!$dbh->do($query)) {
639 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
645 =item C<process_query $dbh, $filename, $version_or_control>
647 Applies a single SQL database upgrade file. Requires an open database
648 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
649 the file's control fields as produced by L<parse_dbupdate_controls>
650 (C<$version_or_control>).
652 =item C<sort_dbupdate_controls>
654 Sorts the database upgrade scripts according to their C<tag> and
655 C<priority> control fields. Returns a list of their hash
656 representations that can be applied in order.
658 =item C<unapplied_upgrade_scripts $dbh>
660 Returns a list if upgrade scripts (their internal hash representation)
661 that haven't been applied to a database yet. C<$dbh> is an open handle
662 to the database that is checked.
664 Requires that the scripts have been parsed.
666 =item C<update2_available $dbh>
668 Returns trueish if at least one upgrade script hasn't been applied to
669 a database yet. C<$dbh> is an open handle to the database that is
672 Requires that the scripts have been parsed.
682 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>