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") or $form->error("$filename : $!\n");
144 $_ = SL::Iconv::convert('UTF-8', 'UTF-8', $_);
146 # Remove DOS and Unix style line endings.
152 for (my $i = 0; $i < length($_); $i++) {
153 my $char = substr($_, $i, 1);
155 # Are we inside a string?
157 if ($char eq $quote_chars[-1]) {
159 } elsif (length $quote_chars[-1] > 1
160 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
161 $i += length($quote_chars[-1]) - 1;
162 $char = $quote_chars[-1];
169 if (($char eq "'") || ($char eq "\"")) {
170 push(@quote_chars, $char);
172 } elsif ($char eq '$' # start of dollar quoting
173 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
174 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
175 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
176 push @quote_chars, $char = '$' . $tag . '$';
178 } elsif ($char eq ";") {
180 # Query is complete. Send it.
182 $sth = $dbh->prepare($query);
183 if (!$sth->execute()) {
184 my $errstr = $dbh->errstr;
187 $form->dberror("The database update/creation did not succeed. " .
188 "The file ${filename} containing the following " .
189 "query failed:<br>${query}<br>" .
190 "The error message was: ${errstr}<br>" .
191 "All changes in that file have been reverted.");
203 # Insert a space at the end of each line so that queries split
204 # over multiple lines work properly.
206 $query .= @quote_chars ? "\n" : ' ';
210 if (ref($version_or_control) eq "HASH") {
211 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
212 } elsif ($version_or_control) {
213 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
219 $::lxdebug->leave_sub();
222 # Process a Perl script which updates the database.
223 # If the script returns 1 then the update was successful.
224 # Return code "2" means "needs more interaction; unlock
225 # the system and end current request".
226 # All other return codes are fatal errors.
227 sub process_perl_script {
228 $::lxdebug->enter_sub();
230 my ($self, $dbh, $filename, $version_or_control) = @_;
232 my %form_values = map { $_ => $::form->{$_} } qw(dbconnect dbdefault dbhost dbmbkiviunstable dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
236 # setup dbup_ export vars & run script
237 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
239 SL::DBUpgrade2::Base::execute_script(
240 file_name => $filename,
241 tag => $version_or_control->{tag},
243 myconfig => \%dbup_myconfig,
247 my $error = $EVAL_ERROR;
249 $dbh->rollback if 1 != ($result // -1);
251 if (!defined($result)) {
252 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
254 } elsif (1 != $result) {
255 SL::System::InstallationLock->unlock if 2 == $result;
259 if (ref($version_or_control) eq "HASH") {
260 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
261 } elsif ($version_or_control) {
262 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
266 # Clear $::form of values that may have been set so that following
267 # Perl upgrade scripts won't have to work with old data (think of
268 # the usual 'continued' mechanism that's used for determining
269 # whether or not the upgrade form must be displayed).
270 delete @{ $::form }{ keys %{ $::form } };
271 $::form->{$_} = $form_values{$_} for keys %form_values;
273 $::lxdebug->leave_sub();
277 my ($self, $dbh, $filename, $version_or_control) = @_;
279 if ($filename =~ m/sql$/) {
280 $self->process_query($dbh, $filename, $version_or_control);
282 $self->process_perl_script($dbh, $filename, $version_or_control);
286 sub update_available {
287 my ($self, $cur_version) = @_;
291 opendir SQLDIR, "sql/Pg-upgrade" || error("", "sql/Pg-upgrade: $!");
292 my @upgradescripts = grep /Pg-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
295 return ($#upgradescripts > -1);
298 sub update2_available {
299 $::lxdebug->enter_sub();
301 my ($self, $dbh) = @_;
303 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
305 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
307 while (my ($tag) = $sth->fetchrow_array) {
308 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
313 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
315 $::lxdebug->leave_sub();
317 return $needs_update;
320 sub unapplied_upgrade_scripts {
321 my ($self, $dbh) = @_;
323 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
325 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
326 my $sth = $dbh->prepare($query);
327 $sth->execute || $self->{form}->dberror($query);
328 while (my ($tag) = $sth->fetchrow_array()) {
329 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
333 return grep { !$_->{applied} } @all_scripts;
336 sub apply_admin_dbupgrade_scripts {
337 my ($self, $called_from_admin) = @_;
339 return 0 if !$self->{auth};
341 my $dbh = $::auth->dbconnect;
342 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
344 return 0 if !@unapplied_scripts;
346 $self->{form}->{login} ||= 'admin';
348 map { $_->{description} = SL::Iconv::convert('UTF-8', 'UTF-8', $_->{description}) } values %{ $self->{all_controls} };
350 if ($called_from_admin) {
351 $self->{form}->{title} = $::locale->text('Dataset upgrade');
352 $self->{form}->header;
355 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
357 foreach my $control (@unapplied_scripts) {
358 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
359 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
361 $self->process_file($dbh, "sql/Pg-upgrade2-auth/$control->{file}", $control);
364 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
369 sub _check_for_loops {
370 my ($form, $file_name, $controls, $tag, @path) = @_;
374 my $ctrl = $controls->{$tag};
376 if ($ctrl->{"loop"} == 1) {
378 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
380 } elsif ($ctrl->{"loop"} == 0) {
383 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
389 my ($form, $file_name, $message) = @_;
392 my $locale = $::locale;
394 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
397 sub _dbupdate2_calculate_depth {
398 $::lxdebug->enter_sub(2);
400 my ($tree, $tag) = @_;
402 my $node = $tree->{$tag};
404 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
408 foreach $tag (@{$node->{"depends"}}) {
409 _dbupdate2_calculate_depth($tree, $tag);
410 my $value = $tree->{$tag}->{"depth"};
411 $max_depth = $value if ($value > $max_depth);
414 $node->{"depth"} = $max_depth + 1;
416 $::lxdebug->leave_sub(2);
419 sub sort_dbupdate_controls {
422 $self->parse_dbupdate_controls unless $self->{all_controls};
424 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
436 SL::DBUpgrade2 - Parse database upgrade files stored in
437 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
445 # Apply outstanding updates to the authentication database
446 my $scripts = SL::DBUpgrade2->new(
450 $scripts->apply_admin_dbupgrade_scripts(1);
452 # Apply updates to a user database
453 my $scripts = SL::DBUpgrade2->new(
457 User->dbupdate2(form => $form,
458 updater => $scripts->parse_dbupdate_controls,
459 database => $dbname);
463 Database upgrade files are used to upgrade the database structure and
464 content of both the authentication database and the user
465 databases. They're applied when a user logs in. As long as the
466 authentication database is not up to date users cannot log in in
467 general, and the admin has to log in first in order to get his
470 Database scripts form a tree by specifying which upgrade file depends
471 on which other upgrade file. This means that such files are always
472 applied in a well-defined order.
474 Each script is run in a separate transaction. If a script fails the
475 current transaction is rolled back and the whole upgrade process is
476 stopped. The user/admin is required to fix the issue manually.
478 A list of applied upgrade scripts is maintained in a table called
479 C<schema_info> for the user database and C<auth.schema_info>) for the
480 authentication database. They contain the tags, the login name of the
481 user having applied the script and the timestamp when the script was
484 Database upgrade files come in two flavours: SQL files and Perl
485 files. For both there are control fields that determine the order in
486 which they're executed etc. The control fields are tag/value pairs
487 contained in comments.
489 =head1 OLD UPGRADE FILES
491 The files in C<sql/Pg-upgrade> are so old that I don't bother
492 documenting them. They're handled by this class, too, but new files
493 are only created as C<Pg-upgrade2> files.
495 =head1 CONTROL FIELDS
499 Control fields for Perl files:
502 # @tag2: some more values
507 Control fields for SQL files:
510 -- @tag2: some more values
513 =head2 TAGS AND THEIR MEANING
515 The following tags are recognized:
521 The name for this file. The C<tag> is also used for dependency
522 resolution (see C<depends>).
528 A description presented to the user when the update is applied.
534 A space-separated list of tags of scripts this particular script
535 depends on. All other upgrades listed in C<depends> will be applied
536 before the current one is applied.
540 Ordering the scripts by their dependencies alone produces a lot of
541 groups of scripts that could be applied at the same time (e.g. if both
542 B and C depend only on A then B could be applied before C or the other
543 way around). This field determines the order inside such a
544 group. Scripts with lower priority fields are executed before scripts
545 with higher priority fields.
547 If two scripts have equal priorities then their tag name decides.
549 The priority defaults to 1000.
557 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
559 Applies all unapplied upgrade files to the authentication/admin
560 database. The parameter C<$called_from_admin> should be truish if the
561 function is called from the web interface and falsish if it's called
562 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
564 =item C<init %params>
566 Initializes the object. Is called directly from L<new> and should not
571 Creates a new object. Possible parameters are:
577 Path to the upgrade files to parse. Required.
581 C<SL::Form> object to use. Required.
585 Optional parameter defaulting to 0. If trueish then the scripts read
586 are the ones applying to the authentication database.
590 =item C<parse_dbupdate_controls>
592 Parses all files located in C<path> (see L<new>), ananlyzes their
593 control fields, builds the tree, and signals errors if control fields
594 are missing/wrong (e.g. a tag name listed in C<depends> is not
595 found). Sets C<$Self->{all_controls}> to the list of database
598 =item C<process_file $dbh, $filename, $version_or_control>
600 Applies a single database upgrade file. Calls L<process_perl_script>
601 for Perl update files and C<process_query> for SQL update
602 files. Requires an open database handle(C<$dbh>), the file name
603 (C<$filename>) and a hash structure of the file's control fields as
604 produced by L<parse_dbupdate_controls> (C<$version_or_control>).
606 Returns the result of the actual function called.
608 =item C<process_perl_script $dbh, $filename, $version_or_control>
610 Applies a single Perl database upgrade file. Requires an open database
611 handle(C<$dbh>), the file name (C<$filename>) and a hash structure of
612 the file's control fields as produced by L<parse_dbupdate_controls>
613 (C<$version_or_control>).
615 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
616 an error is expected. There are two special return values: If the
617 script returns C<1> then the update was successful. Return code C<2>
618 means "needs more interaction from the user; unlock the system and
619 end current upgrade process". All other return codes are fatal errors.
621 Inside the Perl script several local variables exist that can be used:
627 A locale object for translating messages
631 The database handle (inside a transaction).
635 The global C<SL::Form> object.
639 A Perl script can actually implement queries that fail while
640 continueing the process by handling the transaction itself, e.g. with
641 the following function:
644 my ($query, $may_fail) = @_;
646 if (!$dbh->do($query)) {
647 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
653 =item C<process_query $dbh, $filename, $version_or_control>
655 Applies a single SQL database upgrade file. Requires an open database
656 handle(C<$dbh>), the file name (C<$filename>), and a hash structure of
657 the file's control fields as produced by L<parse_dbupdate_controls>
658 (C<$version_or_control>).
660 =item C<sort_dbupdate_controls>
662 Sorts the database upgrade scripts according to their C<tag> and
663 C<priority> control fields. Returns a list of their hash
664 representations that can be applied in order.
666 =item C<unapplied_upgrade_scripts $dbh>
668 Returns a list if upgrade scripts (their internal hash representation)
669 that haven't been applied to a database yet. C<$dbh> is an open handle
670 to the database that is checked.
672 Requires that the scripts have been parsed.
674 =item C<update2_available $dbh>
676 Returns trueish if at least one upgrade script hasn't been applied to
677 a database yet. C<$dbh> is an open handle to the database that is
680 Requires that the scripts have been parsed.
690 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>