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/" . $params{dbdriver} . "-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 $control->{charset} = 'UTF-8' if $file =~ m/\.pl$/;
85 $control->{charset} = $control->{charset} || $control->{encoding} || Common::DEFAULT_CHARSET;
87 if (!$control->{"tag"}) {
88 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
91 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
92 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
95 if (defined($all_controls{$control->{"tag"}})) {
96 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
99 if (!$control->{"description"}) {
100 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
103 $control->{"priority"} *= 1;
104 $control->{"priority"} ||= 1000;
105 $control->{"file"} = $file;
107 delete @{$control}{qw(depth applied)};
109 $all_controls{$control->{"tag"}} = $control;
114 foreach my $control (values(%all_controls)) {
115 foreach my $dependency (@{$control->{"depends"}}) {
116 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
119 map({ $_->{"loop"} = 0; } values(%all_controls));
120 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
123 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
124 values(%all_controls));
126 $self->{all_controls} = \%all_controls;
128 $::lxdebug->leave_sub();
134 $::lxdebug->enter_sub();
136 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
138 my $form = $self->{form};
139 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
144 my $file_charset = Common::DEFAULT_CHARSET;
147 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
151 $fh->seek(0, SEEK_SET);
153 $db_charset ||= Common::DEFAULT_CHARSET;
158 $_ = SL::Iconv::convert($file_charset, $db_charset, $_);
160 # Remove DOS and Unix style line endings.
166 for (my $i = 0; $i < length($_); $i++) {
167 my $char = substr($_, $i, 1);
169 # Are we inside a string?
171 if ($char eq $quote_chars[-1]) {
173 } elsif (length $quote_chars[-1] > 1
174 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
175 $i += length($quote_chars[-1]) - 1;
176 $char = $quote_chars[-1];
183 if (($char eq "'") || ($char eq "\"")) {
184 push(@quote_chars, $char);
186 } elsif ($char eq '$' # start of dollar quoting
187 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
188 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
189 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
190 push @quote_chars, $char = '$' . $tag . '$';
192 } elsif ($char eq ";") {
194 # Query is complete. Send it.
196 $sth = $dbh->prepare($query);
197 if (!$sth->execute()) {
198 my $errstr = $dbh->errstr;
201 $form->dberror("The database update/creation did not succeed. " .
202 "The file ${filename} containing the following " .
203 "query failed:<br>${query}<br>" .
204 "The error message was: ${errstr}<br>" .
205 "All changes in that file have been reverted.");
217 # Insert a space at the end of each line so that queries split
218 # over multiple lines work properly.
220 $query .= @quote_chars ? "\n" : ' ';
224 if (ref($version_or_control) eq "HASH") {
225 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
226 } elsif ($version_or_control) {
227 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
233 $::lxdebug->leave_sub();
236 # Process a Perl script which updates the database.
237 # If the script returns 1 then the update was successful.
238 # Return code "2" means "needs more interaction; remove
239 # users/nologin and end current request".
240 # All other return codes are fatal errors.
241 sub process_perl_script {
242 $::lxdebug->enter_sub();
244 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
246 my %form_values = map { $_ => $::form->{$_} } qw(dbconnect dbdefault dbdriver dbhost dbmbkiviunstable dbname dboptions dbpasswd dbport dbupdate dbuser login template_object version);
250 # setup dbup_ export vars & run script
251 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
253 SL::DBUpgrade2::Base::execute_script(
254 file_name => $filename,
255 tag => $version_or_control->{tag},
257 myconfig => \%dbup_myconfig,
261 my $error = $EVAL_ERROR;
263 $dbh->rollback if 1 != ($result // -1);
265 if (!defined($result)) {
266 print $::form->parse_html_template("dbupgrade/error", { file => $filename, error => $error });
268 } elsif (1 != $result) {
269 unlink("users/nologin") if (2 == $result);
273 if (ref($version_or_control) eq "HASH") {
274 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{tag}) . ", " . $dbh->quote($::form->{login}) . ")");
275 } elsif ($version_or_control) {
276 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
280 # Clear $::form of values that may have been set so that following
281 # Perl upgrade scripts won't have to work with old data (think of
282 # the usual 'continued' mechanism that's used for determining
283 # whether or not the upgrade form must be displayed).
284 delete @{ $::form }{ keys %{ $::form } };
285 $::form->{$_} = $form_values{$_} for keys %form_values;
287 $::lxdebug->leave_sub();
291 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
293 if ($filename =~ m/sql$/) {
294 $self->process_query($dbh, $filename, $version_or_control, $db_charset);
296 $self->process_perl_script($dbh, $filename, $version_or_control, $db_charset);
300 sub update_available {
301 my ($self, $cur_version) = @_;
305 my $dbdriver = $self->{dbdriver};
306 opendir SQLDIR, "sql/${dbdriver}-upgrade" || error("", "sql/${dbdriver}-upgrade: $!");
307 my @upgradescripts = grep /${dbdriver}-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
310 return ($#upgradescripts > -1);
313 sub update2_available {
314 $::lxdebug->enter_sub();
316 my ($self, $dbh) = @_;
318 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
320 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
322 while (my ($tag) = $sth->fetchrow_array) {
323 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
328 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
330 $::lxdebug->leave_sub();
332 return $needs_update;
335 sub unapplied_upgrade_scripts {
336 my ($self, $dbh) = @_;
338 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
340 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
341 my $sth = $dbh->prepare($query);
342 $sth->execute || $self->{form}->dberror($query);
343 while (my ($tag) = $sth->fetchrow_array()) {
344 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
348 return grep { !$_->{applied} } @all_scripts;
351 sub apply_admin_dbupgrade_scripts {
352 my ($self, $called_from_admin) = @_;
354 return 0 if !$self->{auth};
356 my $dbh = $::auth->dbconnect;
357 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
359 return 0 if !@unapplied_scripts;
361 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
362 $self->{form}->{login} ||= 'admin';
364 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $self->{all_controls} };
366 if ($called_from_admin) {
367 $self->{form}->{title} = $::locale->text('Dataset upgrade');
368 $self->{form}->header;
371 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
373 foreach my $control (@unapplied_scripts) {
374 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
375 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
377 $self->process_file($dbh, "sql/$self->{dbdriver}-upgrade2-auth/$control->{file}", $control, $db_charset);
380 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
385 sub _check_for_loops {
386 my ($form, $file_name, $controls, $tag, @path) = @_;
390 my $ctrl = $controls->{$tag};
392 if ($ctrl->{"loop"} == 1) {
394 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
396 } elsif ($ctrl->{"loop"} == 0) {
399 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
405 my ($form, $file_name, $message) = @_;
408 my $locale = $::locale;
410 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
413 sub _dbupdate2_calculate_depth {
414 $::lxdebug->enter_sub(2);
416 my ($tree, $tag) = @_;
418 my $node = $tree->{$tag};
420 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
424 foreach $tag (@{$node->{"depends"}}) {
425 _dbupdate2_calculate_depth($tree, $tag);
426 my $value = $tree->{$tag}->{"depth"};
427 $max_depth = $value if ($value > $max_depth);
430 $node->{"depth"} = $max_depth + 1;
432 $::lxdebug->leave_sub(2);
435 sub sort_dbupdate_controls {
438 $self->parse_dbupdate_controls unless $self->{all_controls};
440 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
452 SL::DBUpgrade2 - Parse database upgrade files stored in
453 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
461 # Apply outstanding updates to the authentication database
462 my $scripts = SL::DBUpgrade2->new(
467 $scripts->apply_admin_dbupgrade_scripts(1);
469 # Apply updates to a user database
470 my $scripts = SL::DBUpgrade2->new(
472 dbdriver => $::form->{dbdriver},
475 User->dbupdate2($form, $scripts->parse_dbupdate_controls);
479 Database upgrade files are used to upgrade the database structure and
480 content of both the authentication database and the user
481 databases. They're applied when a user logs in. As long as the
482 authentication database is not up to date users cannot log in in
483 general, and the admin has to log in first in order to get his
486 Database scripts form a tree by specifying which upgrade file depends
487 on which other upgrade file. This means that such files are always
488 applied in a well-defined order.
490 Each script is run in a separate transaction. If a script fails the
491 current transaction is rolled back and the whole upgrade process is
492 stopped. The user/admin is required to fix the issue manually.
494 A list of applied upgrade scripts is maintained in a table called
495 C<schema_info> for the user database and C<auth.schema_info>) for the
496 authentication database. They contain the tags, the login name of the
497 user having applied the script and the timestamp when the script was
500 Database upgrade files come in two flavours: SQL files and Perl
501 files. For both there are control fields that determine the order in
502 which they're executed, what charset the scripts are written in
503 etc. The control fields are tag/value pairs contained in comments.
505 =head1 OLD UPGRADE FILES
507 The files in C<sql/Pg-upgrade> are so old that I don't bother
508 documenting them. They're handled by this class, too, but new files
509 are only created as C<Pg-upgrade2> files.
511 =head1 CONTROL FIELDS
515 Control fields for Perl files:
518 # @tag2: some more values
523 Control fields for SQL files:
526 -- @tag2: some more values
529 =head2 TAGS AND THEIR MEANING
531 The following tags are recognized:
537 The name for this file. The C<tag> is also used for dependency
538 resolution (see C<depends>).
544 A description presented to the user when the update is applied.
550 A space-separated list of tags of scripts this particular script
551 depends on. All other upgrades listed in C<depends> will be applied
552 before the current one is applied.
558 The charset this file uses. Defaults to C<ISO-8859-15> if
559 missing. Both terms are recognized.
563 Ordering the scripts by their dependencies alone produces a lot of
564 groups of scripts that could be applied at the same time (e.g. if both
565 B and C depend only on A then B could be applied before C or the other
566 way around). This field determines the order inside such a
567 group. Scripts with lower priority fields are executed before scripts
568 with higher priority fields.
570 If two scripts have equal priorities then their tag name decides.
572 The priority defaults to 1000.
580 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
582 Applies all unapplied upgrade files to the authentication/admin
583 database. The parameter C<$called_from_admin> should be truish if the
584 function is called from the web interface and falsish if it's called
585 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
587 =item C<init %params>
589 Initializes the object. Is called directly from L<new> and should not
594 Creates a new object. Possible parameters are:
600 Path to the upgrade files to parse. Required.
604 C<SL::Form> object to use. Required.
608 Name of the database driver. Currently only C<Pg> for PostgreSQL is
613 Optional parameter defaulting to 0. If trueish then the scripts read
614 are the ones applying to the authentication database.
618 =item C<parse_dbupdate_controls>
620 Parses all files located in C<path> (see L<new>), ananlyzes their
621 control fields, builds the tree, and signals errors if control fields
622 are missing/wrong (e.g. a tag name listed in C<depends> is not
623 found). Sets C<$Self->{all_controls}> to the list of database
626 =item C<process_file $dbh, $filename, $version_or_control, $db_charset>
628 Applies a single database upgrade file. Calls L<process_perl_script>
629 for Perl update files and C<process_query> for SQL update
630 files. Requires an open database handle(C<$dbh>), the file name
631 (C<$filename>), a hash structure of the file's control fields as
632 produced by L<parse_dbupdate_controls> (C<$version_or_control>) and
633 the database charset (for on-the-fly charset recoding of the script if
634 required, C<$db_charset>).
636 Returns the result of the actual function called.
638 =item C<process_perl_script $dbh, $filename, $version_or_control, $db_charset>
640 Applies a single Perl database upgrade file. Requires an open database
641 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
642 file's control fields as produced by L<parse_dbupdate_controls>
643 (C<$version_or_control>) and the database charset (for on-the-fly
644 charset recoding of the script if required, C<$db_charset>).
646 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
647 an error is expected. There are two special return values: If the
648 script returns C<1> then the update was successful. Return code C<2>
649 means "needs more interaction from the user; remove users/nologin and
650 end current upgrade process". All other return codes are fatal errors.
652 Inside the Perl script several local variables exist that can be used:
658 A locale object for translating messages
662 The database handle (inside a transaction).
666 The global C<SL::Form> object.
670 A Perl script can actually implement queries that fail while
671 continueing the process by handling the transaction itself, e.g. with
672 the following function:
675 my ($query, $may_fail) = @_;
677 if (!$dbh->do($query)) {
678 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
684 =item C<process_query $dbh, $filename, $version_or_control, $db_charset>
686 Applies a single SQL database upgrade file. Requires an open database
687 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
688 ofile's control fields as produced by L<parse_dbupdate_controls>
689 (C<$version_or_control>) and the database charset (for on-the-fly
690 charset recoding of the script if required, C<$db_charset>).
692 =item C<sort_dbupdate_controls>
694 Sorts the database upgrade scripts according to their C<tag> and
695 C<priority> control fields. Returns a list of their hash
696 representations that can be applied in order.
698 =item C<unapplied_upgrade_scripts $dbh>
700 Returns a list if upgrade scripts (their internal hash representation)
701 that haven't been applied to a database yet. C<$dbh> is an open handle
702 to the database that is checked.
704 Requires that the scripts have been parsed.
706 =item C<update2_available $dbh>
708 Returns trueish if at least one upgrade script hasn't been applied to
709 a database yet. C<$dbh> is an open handle to the database that is
712 Requires that the scripts have been parsed.
722 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>