1 package SL::DBUpgrade2;
4 use List::MoreUtils qw(any);
15 return bless({}, $package)->init(@_);
19 my ($self, %params) = @_;
22 $params{path_suffix} = "-auth";
23 $params{schema} = "auth.";
26 $params{path_suffix} ||= '';
27 $params{schema} ||= '';
29 map { $self->{$_} = $params{$_} } keys %params;
34 sub parse_dbupdate_controls {
35 $::lxdebug->enter_sub();
39 my $form = $self->{form};
40 my $locale = $::locale;
45 my $path = "sql/" . $self->{dbdriver} . "-upgrade2" . $self->{path_suffix};
47 foreach my $file_name (<$path/*.sql>, <$path/*.pl>) {
48 next unless (open(IN, $file_name));
50 my $file = $file_name;
60 next unless (/^(--|\#)\s*\@/);
65 my @fields = split(/\s*:\s*/, $_, 2);
66 next unless (scalar(@fields) == 2);
68 if ($fields[0] eq "depends") {
69 push(@{$control->{"depends"}}, split(/\s+/, $fields[1]));
71 $control->{$fields[0]} = $fields[1];
75 next if ($control->{ignore});
77 $control->{charset} = $control->{charset} || $control->{encoding} || Common::DEFAULT_CHARSET;
79 if (!$control->{"tag"}) {
80 _control_error($form, $file_name, $locale->text("Missing 'tag' field.")) ;
83 if ($control->{"tag"} =~ /[^a-zA-Z0-9_\(\)\-]/) {
84 _control_error($form, $file_name, $locale->text("The 'tag' field must only consist of alphanumeric characters or the carachters - _ ( )"))
87 if (defined($all_controls{$control->{"tag"}})) {
88 _control_error($form, $file_name, sprintf($locale->text("More than one control file with the tag '%s' exist."), $control->{"tag"}))
91 if (!$control->{"description"}) {
92 _control_error($form, $file_name, sprintf($locale->text("Missing 'description' field."))) ;
95 $control->{"priority"} *= 1;
96 $control->{"priority"} ||= 1000;
97 $control->{"file"} = $file;
99 delete @{$control}{qw(depth applied)};
101 $all_controls{$control->{"tag"}} = $control;
106 foreach my $control (values(%all_controls)) {
107 foreach my $dependency (@{$control->{"depends"}}) {
108 _control_error($form, $control->{"file"}, sprintf($locale->text("Unknown dependency '%s'."), $dependency)) if (!defined($all_controls{$dependency}));
111 map({ $_->{"loop"} = 0; } values(%all_controls));
112 _check_for_loops($form, $control->{"file"}, \%all_controls, $control->{"tag"});
115 map({ _dbupdate2_calculate_depth(\%all_controls, $_->{"tag"}) }
116 values(%all_controls));
118 $self->{all_controls} = \%all_controls;
120 $::lxdebug->leave_sub();
126 $::lxdebug->enter_sub();
128 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
130 my $form = $self->{form};
131 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
136 my $file_charset = Common::DEFAULT_CHARSET;
139 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
143 $fh->seek(0, SEEK_SET);
145 $db_charset ||= Common::DEFAULT_CHARSET;
150 $_ = SL::Iconv::convert($file_charset, $db_charset, $_);
152 # Remove DOS and Unix style line endings.
158 for (my $i = 0; $i < length($_); $i++) {
159 my $char = substr($_, $i, 1);
161 # Are we inside a string?
163 if ($char eq $quote_chars[-1]) {
165 } elsif (length $quote_chars[-1] > 1
166 && substr($quote_chars[-1], 0, 1) eq $char
167 && substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
168 $i += length $quote_chars[-1] - 1;
169 $char = $quote_chars[-1];
176 if (($char eq "'") || ($char eq "\"")) {
177 push(@quote_chars, $char);
179 } elsif ($char eq '$' # start of dollar quoting
180 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
181 && (do { substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
182 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
183 push @quote_chars, $char = '$' . $tag . '$';
185 } elsif ($char eq ";") {
187 # Query is complete. Send it.
189 $sth = $dbh->prepare($query);
190 if (!$sth->execute()) {
191 my $errstr = $dbh->errstr;
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();
229 # Process a Perl script which updates the database.
230 # If the script returns 1 then the update was successful.
231 # Return code "2" means "needs more interaction; remove
232 # users/nologin and end current request".
233 # All other return codes are fatal errors.
234 sub process_perl_script {
235 $::lxdebug->enter_sub();
237 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
239 my $form = $self->{form};
240 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
241 my $file_charset = Common::DEFAULT_CHARSET;
243 if (ref($version_or_control) eq "HASH") {
244 $file_charset = $version_or_control->{charset};
249 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
253 $fh->seek(0, SEEK_SET);
256 my $contents = join "", <$fh>;
259 $db_charset ||= Common::DEFAULT_CHARSET;
261 my $iconv = SL::Iconv->new($file_charset, $db_charset);
265 # setup dbup_ export vars
266 my %dbup_myconfig = ();
267 map({ $dbup_myconfig{$_} = $form->{$_}; } qw(dbname dbuser dbpasswd dbhost dbport dbconnect));
269 my $dbup_locale = $::locale;
271 my $result = eval($contents);
278 if (!defined($result)) {
279 print $form->parse_html_template("dbupgrade/error",
280 { "file" => $filename,
283 } elsif (1 != $result) {
284 unlink("users/nologin") if (2 == $result);
288 if (ref($version_or_control) eq "HASH") {
289 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
290 } elsif ($version_or_control) {
291 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
295 $::lxdebug->leave_sub();
299 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
301 if ($filename =~ m/sql$/) {
302 $self->process_query($dbh, $filename, $version_or_control, $db_charset);
304 $self->process_perl_script($dbh, $filename, $version_or_control, $db_charset);
308 sub update_available {
309 my ($self, $cur_version) = @_;
313 my $dbdriver = $self->{dbdriver};
314 opendir SQLDIR, "sql/${dbdriver}-upgrade" || error("", "sql/${dbdriver}-upgrade: $!");
315 my @upgradescripts = grep /${dbdriver}-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
318 return ($#upgradescripts > -1);
321 sub update2_available {
322 $::lxdebug->enter_sub();
324 my ($self, $dbh) = @_;
326 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
328 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
330 while (my ($tag) = $sth->fetchrow_array) {
331 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
336 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
338 $::lxdebug->leave_sub();
340 return $needs_update;
343 sub unapplied_upgrade_scripts {
344 my ($self, $dbh) = @_;
346 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
348 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
349 my $sth = $dbh->prepare($query);
350 $sth->execute || $self->{form}->dberror($query);
351 while (my ($tag) = $sth->fetchrow_array()) {
352 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
356 return grep { !$_->{applied} } @all_scripts;
359 sub apply_admin_dbupgrade_scripts {
360 my ($self, $called_from_admin) = @_;
362 return 0 if !$self->{auth};
364 my $dbh = $::auth->dbconnect;
365 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
367 return 0 if !@unapplied_scripts;
369 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
370 $self->{form}->{login} ||= 'admin';
372 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $self->{all_controls} };
374 if ($called_from_admin) {
375 $self->{form}->{title} = $::locale->text('Dataset upgrade');
376 $self->{form}->header;
379 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
381 foreach my $control (@unapplied_scripts) {
382 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
383 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
385 $self->process_file($dbh, "sql/$self->{dbdriver}-upgrade2-auth/$control->{file}", $control, $db_charset);
388 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
393 sub _check_for_loops {
394 my ($form, $file_name, $controls, $tag, @path) = @_;
398 my $ctrl = $controls->{$tag};
400 if ($ctrl->{"loop"} == 1) {
402 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
404 } elsif ($ctrl->{"loop"} == 0) {
407 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
413 my ($form, $file_name, $message) = @_;
416 my $locale = $::locale;
418 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
421 sub _dbupdate2_calculate_depth {
422 $::lxdebug->enter_sub(2);
424 my ($tree, $tag) = @_;
426 my $node = $tree->{$tag};
428 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
432 foreach $tag (@{$node->{"depends"}}) {
433 _dbupdate2_calculate_depth($tree, $tag);
434 my $value = $tree->{$tag}->{"depth"};
435 $max_depth = $value if ($value > $max_depth);
438 $node->{"depth"} = $max_depth + 1;
440 $::lxdebug->leave_sub(2);
443 sub sort_dbupdate_controls {
446 $self->parse_dbupdate_controls unless $self->{all_controls};
448 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
460 SL::DBUpgrade2 - Parse database upgrade files stored in
461 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
469 # Apply outstanding updates to the authentication database
470 my $scripts = SL::DBUpgrade2->new(
475 $scripts->apply_admin_dbupgrade_scripts(1);
477 # Apply updates to a user database
478 my $scripts = SL::DBUpgrade2->new(
480 dbdriver => $::form->{dbdriver},
483 User->dbupdate2($form, $scripts->parse_dbupdate_controls);
487 Database upgrade files are used to upgrade the database structure and
488 content of both the authentication database and the user
489 databases. They're applied when a user logs in. As long as the
490 authentication database is not up to date users cannot log in in
491 general, and the admin has to log in first in order to get his
494 Database scripts form a tree by specifying which upgrade file depends
495 on which other upgrade file. This means that such files are always
496 applied in a well-defined order.
498 Each script is run in a separate transaction. If a script fails the
499 current transaction is rolled back and the whole upgrade process is
500 stopped. The user/admin is required to fix the issue manually.
502 A list of applied upgrade scripts is maintained in a table called
503 C<schema_info> for the user database and C<auth.schema_info>) for the
504 authentication database. They contain the tags, the login name of the
505 user having applied the script and the timestamp when the script was
508 Database upgrade files come in two flavours: SQL files and Perl
509 files. For both there are control fields that determine the order in
510 which they're executed, what charset the scripts are written in
511 etc. The control fields are tag/value pairs contained in comments.
513 =head1 OLD UPGRADE FILES
515 The files in C<sql/Pg-upgrade> are so old that I don't bother
516 documenting them. They're handled by this class, too, but new files
517 are only created as C<Pg-upgrade2> files.
519 =head1 CONTROL FIELDS
523 Control fields for Perl files:
526 # @tag2: some more values
531 Control fields for SQL files:
534 -- @tag2: some more values
537 =head2 TAGS AND THEIR MEANING
539 The following tags are recognized:
545 The name for this file. The C<tag> is also used for dependency
546 resolution (see C<depends>).
552 A description presented to the user when the update is applied.
558 A space-separated list of tags of scripts this particular script
559 depends on. All other upgrades listed in C<depends> will be applied
560 before the current one is applied.
565 The charset this file uses. Defaults to C<ISO-8859-15> if
566 missing. Both terms are recognized.
570 Ordering the scripts by their dependencies alone produces a lot of
571 groups of scripts that could be applied at the same time (e.g. if both
572 B and C depend only on A then B could be applied before C or the other
573 way around). This field determines the order inside such a
574 group. Scripts with lower priority fields are executed before scripts
575 with higher priority fields.
577 If two scripts have equal priorities then their tag name decides.
579 The priority defaults to 1000.
587 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
589 Applies all unapplied upgrade files to the authentication/admin
590 database. The parameter C<$called_from_admin> should be truish if the
591 function is called from the web interface and falsish if it's called
592 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
594 =item C<init %params>
596 Initializes the object. Is called directly from L<new> and should not
601 Creates a new object. Possible parameters are:
607 Path to the upgrade files to parse. Required.
611 C<SL::Form> object to use. Required.
615 Name of the database driver. Currently only C<Pg> for PostgreSQL is
620 Optional parameter defaulting to 0. If trueish then the scripts read
621 are the ones applying to the authentication database.
625 =item C<parse_dbupdate_controls>
627 Parses all files located in C<path> (see L<new>), ananlyzes their
628 control fields, builds the tree, and signals errors if control fields
629 are missing/wrong (e.g. a tag name listed in C<depends> is not
630 found). Sets C<$Self->{all_controls}> to the list of database
633 =item C<process_file $dbh, $filename, $version_or_control, $db_charset>
635 Applies a single database upgrade file. Calls L<process_perl_script>
636 for Perl update files and C<process_query> for SQL update
637 files. Requires an open database handle(C<$dbh>), the file name
638 (C<$filename>), a hash structure of the file's control fields as
639 produced by L<parse_dbupdate_controls> (C<$version_or_control>) and
640 the database charset (for on-the-fly charset recoding of the script if
641 required, C<$db_charset>).
643 Returns the result of the actual function called.
645 =item C<process_perl_script $dbh, $filename, $version_or_control, $db_charset>
647 Applies a single Perl database upgrade file. Requires an open database
648 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
649 file's control fields as produced by L<parse_dbupdate_controls>
650 (C<$version_or_control>) and the database charset (for on-the-fly
651 charset recoding of the script if required, C<$db_charset>).
653 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
654 an error is expected. There are two special return values: If the
655 script returns C<1> then the update was successful. Return code C<2>
656 means "needs more interaction from the user; remove users/nologin and
657 end current upgrade process". All other return codes are fatal errors.
659 Inside the Perl script several local variables exist that can be used:
665 A locale object for translating messages
669 The database handle (inside a transaction).
673 The global C<SL::Form> object.
677 A Perl script can actually implement queries that fail while
678 continueing the process by handling the transaction itself, e.g. with
679 the following function:
682 my ($query, $may_fail) = @_;
684 if (!$dbh->do($query)) {
685 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
691 =item C<process_query $dbh, $filename, $version_or_control, $db_charset>
693 Applies a single SQL database upgrade file. Requires an open database
694 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
695 ofile's control fields as produced by L<parse_dbupdate_controls>
696 (C<$version_or_control>) and the database charset (for on-the-fly
697 charset recoding of the script if required, C<$db_charset>).
699 =item C<sort_dbupdate_controls>
701 Sorts the database upgrade scripts according to their C<tag> and
702 C<priority> control fields. Returns a list of their hash
703 representations that can be applied in order.
705 =item C<unapplied_upgrade_scripts $dbh>
707 Returns a list if upgrade scripts (their internal hash representation)
708 that haven't been applied to a database yet. C<$dbh> is an open handle
709 to the database that is checked.
711 Requires that the scripts have been parsed.
713 =item C<update2_available $dbh>
715 Returns trueish if at least one upgrade script hasn't been applied to
716 a database yet. C<$dbh> is an open handle to the database that is
719 Requires that the scripts have been parsed.
729 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>