1 package SL::DBUpgrade2;
4 use List::MoreUtils qw(any);
7 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/" . $params{dbdriver} . "-upgrade2" . $params{path_suffix};
31 map { $self->{$_} = $params{$_} } keys %params;
40 sub parse_dbupdate_controls {
41 $::lxdebug->enter_sub();
45 my $form = $self->{form};
46 my $locale = $::locale;
51 my $path = $self->path;
53 foreach my $file_name (<$path/*.sql>, <$path/*.pl>) {
54 next unless (open(IN, $file_name));
56 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]));
77 $control->{$fields[0]} = $fields[1];
81 next if ($control->{ignore});
83 $control->{charset} = 'UTF-8' if $file =~ m/\.pl$/;
84 $control->{charset} = $control->{charset} || $control->{encoding} || Common::DEFAULT_CHARSET;
86 if (!$control->{"tag"}) {
87 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
90 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
91 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
94 if (defined($all_controls{$control->{"tag"}})) {
95 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
98 if (!$control->{"description"}) {
99 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
102 $control->{"priority"} *= 1;
103 $control->{"priority"} ||= 1000;
104 $control->{"file"} = $file;
106 delete @{$control}{qw(depth applied)};
108 $all_controls{$control->{"tag"}} = $control;
113 foreach my $control (values(%all_controls)) {
114 foreach my $dependency (@{$control->{"depends"}}) {
115 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
118 map({ $_->{"loop"} = 0; } values(%all_controls));
119 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
122 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
123 values(%all_controls));
125 $self->{all_controls} = \%all_controls;
127 $::lxdebug->leave_sub();
133 $::lxdebug->enter_sub();
135 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
137 my $form = $self->{form};
138 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
143 my $file_charset = Common::DEFAULT_CHARSET;
146 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
150 $fh->seek(0, SEEK_SET);
152 $db_charset ||= Common::DEFAULT_CHARSET;
157 $_ = SL::Iconv::convert($file_charset, $db_charset, $_);
159 # Remove DOS and Unix style line endings.
165 for (my $i = 0; $i < length($_); $i++) {
166 my $char = substr($_, $i, 1);
168 # Are we inside a string?
170 if ($char eq $quote_chars[-1]) {
172 } elsif (length $quote_chars[-1] > 1
173 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
174 $i += length($quote_chars[-1]) - 1;
175 $char = $quote_chars[-1];
182 if (($char eq "'") || ($char eq "\"")) {
183 push(@quote_chars, $char);
185 } elsif ($char eq '$' # start of dollar quoting
186 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
187 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
188 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
189 push @quote_chars, $char = '$' . $tag . '$';
191 } elsif ($char eq ";") {
193 # Query is complete. Send it.
195 $sth = $dbh->prepare($query);
196 if (!$sth->execute()) {
197 my $errstr = $dbh->errstr;
200 $form->dberror("The database update/creation did not succeed. " .
201 "The file ${filename} containing the following " .
202 "query failed:<br>${query}<br>" .
203 "The error message was: ${errstr}<br>" .
204 "All changes in that file have been reverted.");
216 # Insert a space at the end of each line so that queries split
217 # over multiple lines work properly.
219 $query .= @quote_chars ? "\n" : ' ';
223 if (ref($version_or_control) eq "HASH") {
224 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
225 } elsif ($version_or_control) {
226 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
232 $::lxdebug->leave_sub();
235 # Process a Perl script which updates the database.
236 # If the script returns 1 then the update was successful.
237 # Return code "2" means "needs more interaction; remove
238 # users/nologin and end current request".
239 # All other return codes are fatal errors.
240 sub process_perl_script {
241 $::lxdebug->enter_sub();
243 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
245 my %form_values = map { $_ => $::form->{$_} } qw(dbconnect dbdefault dbdriver dbhost dbmbkiviunstable dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
249 # setup dbup_ export vars & run script
250 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
251 my $result = SL::DBUpgrade2::Base::execute_script(
252 file_name => $filename,
253 tag => $version_or_control->{tag},
255 myconfig => \%dbup_myconfig,
258 if (1 != ($result // 1)) {
262 if (!defined($result)) {
263 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $@ });
265 } elsif (1 != $result) {
266 unlink("users/nologin") 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));
277 # Clear $::form of values that may have been set so that following
278 # Perl upgrade scripts won't have to work with old data (think of
279 # the usual 'continued' mechanism that's used for determining
280 # whether or not the upgrade form must be displayed).
281 delete @{ $::form }{ keys %{ $::form } };
282 $::form->{$_} = $form_values{$_} for keys %form_values;
284 $::lxdebug->leave_sub();
288 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
290 if ($filename =~ m/sql$/) {
291 $self->process_query($dbh, $filename, $version_or_control, $db_charset);
293 $self->process_perl_script($dbh, $filename, $version_or_control, $db_charset);
297 sub update_available {
298 my ($self, $cur_version) = @_;
302 my $dbdriver = $self->{dbdriver};
303 opendir SQLDIR, "sql/${dbdriver}-upgrade" || error("", "sql/${dbdriver}-upgrade: $!");
304 my @upgradescripts = grep /${dbdriver}-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
307 return ($#upgradescripts > -1);
310 sub update2_available {
311 $::lxdebug->enter_sub();
313 my ($self, $dbh) = @_;
315 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
317 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
319 while (my ($tag) = $sth->fetchrow_array) {
320 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
325 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
327 $::lxdebug->leave_sub();
329 return $needs_update;
332 sub unapplied_upgrade_scripts {
333 my ($self, $dbh) = @_;
335 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
337 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
338 my $sth = $dbh->prepare($query);
339 $sth->execute || $self->{form}->dberror($query);
340 while (my ($tag) = $sth->fetchrow_array()) {
341 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
345 return grep { !$_->{applied} } @all_scripts;
348 sub apply_admin_dbupgrade_scripts {
349 my ($self, $called_from_admin) = @_;
351 return 0 if !$self->{auth};
353 my $dbh = $::auth->dbconnect;
354 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
356 return 0 if !@unapplied_scripts;
358 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
359 $self->{form}->{login} ||= 'admin';
361 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $self->{all_controls} };
363 if ($called_from_admin) {
364 $self->{form}->{title} = $::locale->text('Dataset upgrade');
365 $self->{form}->header;
368 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
370 foreach my $control (@unapplied_scripts) {
371 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
372 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
374 $self->process_file($dbh, "sql/$self->{dbdriver}-upgrade2-auth/$control->{file}", $control, $db_charset);
377 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
382 sub _check_for_loops {
383 my ($form, $file_name, $controls, $tag, @path) = @_;
387 my $ctrl = $controls->{$tag};
389 if ($ctrl->{"loop"} == 1) {
391 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
393 } elsif ($ctrl->{"loop"} == 0) {
396 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
402 my ($form, $file_name, $message) = @_;
405 my $locale = $::locale;
407 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
410 sub _dbupdate2_calculate_depth {
411 $::lxdebug->enter_sub(2);
413 my ($tree, $tag) = @_;
415 my $node = $tree->{$tag};
417 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
421 foreach $tag (@{$node->{"depends"}}) {
422 _dbupdate2_calculate_depth($tree, $tag);
423 my $value = $tree->{$tag}->{"depth"};
424 $max_depth = $value if ($value > $max_depth);
427 $node->{"depth"} = $max_depth + 1;
429 $::lxdebug->leave_sub(2);
432 sub sort_dbupdate_controls {
435 $self->parse_dbupdate_controls unless $self->{all_controls};
437 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
449 SL::DBUpgrade2 - Parse database upgrade files stored in
450 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
458 # Apply outstanding updates to the authentication database
459 my $scripts = SL::DBUpgrade2->new(
464 $scripts->apply_admin_dbupgrade_scripts(1);
466 # Apply updates to a user database
467 my $scripts = SL::DBUpgrade2->new(
469 dbdriver => $::form->{dbdriver},
472 User->dbupdate2($form, $scripts->parse_dbupdate_controls);
476 Database upgrade files are used to upgrade the database structure and
477 content of both the authentication database and the user
478 databases. They're applied when a user logs in. As long as the
479 authentication database is not up to date users cannot log in in
480 general, and the admin has to log in first in order to get his
483 Database scripts form a tree by specifying which upgrade file depends
484 on which other upgrade file. This means that such files are always
485 applied in a well-defined order.
487 Each script is run in a separate transaction. If a script fails the
488 current transaction is rolled back and the whole upgrade process is
489 stopped. The user/admin is required to fix the issue manually.
491 A list of applied upgrade scripts is maintained in a table called
492 C<schema_info> for the user database and C<auth.schema_info>) for the
493 authentication database. They contain the tags, the login name of the
494 user having applied the script and the timestamp when the script was
497 Database upgrade files come in two flavours: SQL files and Perl
498 files. For both there are control fields that determine the order in
499 which they're executed, what charset the scripts are written in
500 etc. The control fields are tag/value pairs contained in comments.
502 =head1 OLD UPGRADE FILES
504 The files in C<sql/Pg-upgrade> are so old that I don't bother
505 documenting them. They're handled by this class, too, but new files
506 are only created as C<Pg-upgrade2> files.
508 =head1 CONTROL FIELDS
512 Control fields for Perl files:
515 # @tag2: some more values
520 Control fields for SQL files:
523 -- @tag2: some more values
526 =head2 TAGS AND THEIR MEANING
528 The following tags are recognized:
534 The name for this file. The C<tag> is also used for dependency
535 resolution (see C<depends>).
541 A description presented to the user when the update is applied.
547 A space-separated list of tags of scripts this particular script
548 depends on. All other upgrades listed in C<depends> will be applied
549 before the current one is applied.
555 The charset this file uses. Defaults to C<ISO-8859-15> if
556 missing. Both terms are recognized.
560 Ordering the scripts by their dependencies alone produces a lot of
561 groups of scripts that could be applied at the same time (e.g. if both
562 B and C depend only on A then B could be applied before C or the other
563 way around). This field determines the order inside such a
564 group. Scripts with lower priority fields are executed before scripts
565 with higher priority fields.
567 If two scripts have equal priorities then their tag name decides.
569 The priority defaults to 1000.
577 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
579 Applies all unapplied upgrade files to the authentication/admin
580 database. The parameter C<$called_from_admin> should be truish if the
581 function is called from the web interface and falsish if it's called
582 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
584 =item C<init %params>
586 Initializes the object. Is called directly from L<new> and should not
591 Creates a new object. Possible parameters are:
597 Path to the upgrade files to parse. Required.
601 C<SL::Form> object to use. Required.
605 Name of the database driver. Currently only C<Pg> for PostgreSQL is
610 Optional parameter defaulting to 0. If trueish then the scripts read
611 are the ones applying to the authentication database.
615 =item C<parse_dbupdate_controls>
617 Parses all files located in C<path> (see L<new>), ananlyzes their
618 control fields, builds the tree, and signals errors if control fields
619 are missing/wrong (e.g. a tag name listed in C<depends> is not
620 found). Sets C<$Self->{all_controls}> to the list of database
623 =item C<process_file $dbh, $filename, $version_or_control, $db_charset>
625 Applies a single database upgrade file. Calls L<process_perl_script>
626 for Perl update files and C<process_query> for SQL update
627 files. Requires an open database handle(C<$dbh>), the file name
628 (C<$filename>), a hash structure of the file's control fields as
629 produced by L<parse_dbupdate_controls> (C<$version_or_control>) and
630 the database charset (for on-the-fly charset recoding of the script if
631 required, C<$db_charset>).
633 Returns the result of the actual function called.
635 =item C<process_perl_script $dbh, $filename, $version_or_control, $db_charset>
637 Applies a single Perl database upgrade file. Requires an open database
638 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
639 file's control fields as produced by L<parse_dbupdate_controls>
640 (C<$version_or_control>) and the database charset (for on-the-fly
641 charset recoding of the script if required, C<$db_charset>).
643 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
644 an error is expected. There are two special return values: If the
645 script returns C<1> then the update was successful. Return code C<2>
646 means "needs more interaction from the user; remove users/nologin and
647 end current upgrade process". All other return codes are fatal errors.
649 Inside the Perl script several local variables exist that can be used:
655 A locale object for translating messages
659 The database handle (inside a transaction).
663 The global C<SL::Form> object.
667 A Perl script can actually implement queries that fail while
668 continueing the process by handling the transaction itself, e.g. with
669 the following function:
672 my ($query, $may_fail) = @_;
674 if (!$dbh->do($query)) {
675 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
681 =item C<process_query $dbh, $filename, $version_or_control, $db_charset>
683 Applies a single SQL database upgrade file. Requires an open database
684 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
685 ofile's control fields as produced by L<parse_dbupdate_controls>
686 (C<$version_or_control>) and the database charset (for on-the-fly
687 charset recoding of the script if required, C<$db_charset>).
689 =item C<sort_dbupdate_controls>
691 Sorts the database upgrade scripts according to their C<tag> and
692 C<priority> control fields. Returns a list of their hash
693 representations that can be applied in order.
695 =item C<unapplied_upgrade_scripts $dbh>
697 Returns a list if upgrade scripts (their internal hash representation)
698 that haven't been applied to a database yet. C<$dbh> is an open handle
699 to the database that is checked.
701 Requires that the scripts have been parsed.
703 =item C<update2_available $dbh>
705 Returns trueish if at least one upgrade script hasn't been applied to
706 a database yet. C<$dbh> is an open handle to the database that is
709 Requires that the scripts have been parsed.
719 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>