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]) {
169 if (($char eq "'") || ($char eq "\"")) {
170 push(@quote_chars, $char);
172 } elsif ($char eq ";") {
174 # Query is complete. Send it.
176 $sth = $dbh->prepare($query);
177 if (!$sth->execute()) {
178 my $errstr = $dbh->errstr;
181 $form->dberror("The database update/creation did not succeed. " .
182 "The file ${filename} containing the following " .
183 "query failed:<br>${query}<br>" .
184 "The error message was: ${errstr}<br>" .
185 "All changes in that file have been reverted.");
197 # Insert a space at the end of each line so that queries split
198 # over multiple lines work properly.
200 $query .= @quote_chars ? "\n" : ' ';
204 if (ref($version_or_control) eq "HASH") {
205 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
206 } elsif ($version_or_control) {
207 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
213 $::lxdebug->leave_sub();
216 # Process a Perl script which updates the database.
217 # If the script returns 1 then the update was successful.
218 # Return code "2" means "needs more interaction; remove
219 # users/nologin and end current request".
220 # All other return codes are fatal errors.
221 sub process_perl_script {
222 $::lxdebug->enter_sub();
224 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
226 my $form = $self->{form};
227 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
228 my $file_charset = Common::DEFAULT_CHARSET;
230 if (ref($version_or_control) eq "HASH") {
231 $file_charset = $version_or_control->{charset};
236 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
240 $fh->seek(0, SEEK_SET);
243 my $contents = join "", <$fh>;
246 $db_charset ||= Common::DEFAULT_CHARSET;
248 my $iconv = SL::Iconv->new($file_charset, $db_charset);
252 # setup dbup_ export vars
253 my %dbup_myconfig = ();
254 map({ $dbup_myconfig{$_} = $form->{$_}; } qw(dbname dbuser dbpasswd dbhost dbport dbconnect));
256 my $dbup_locale = $::locale;
258 my $result = eval($contents);
265 if (!defined($result)) {
266 print $form->parse_html_template("dbupgrade/error",
267 { "file" => $filename,
270 } elsif (1 != $result) {
271 unlink("users/nologin") if (2 == $result);
275 if (ref($version_or_control) eq "HASH") {
276 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
277 } elsif ($version_or_control) {
278 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
282 $::lxdebug->leave_sub();
286 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
288 if ($filename =~ m/sql$/) {
289 $self->process_query($dbh, $filename, $version_or_control, $db_charset);
291 $self->process_perl_script($dbh, $filename, $version_or_control, $db_charset);
295 sub update_available {
296 my ($self, $cur_version) = @_;
300 my $dbdriver = $self->{dbdriver};
301 opendir SQLDIR, "sql/${dbdriver}-upgrade" || error("", "sql/${dbdriver}-upgrade: $!");
302 my @upgradescripts = grep /${dbdriver}-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
305 return ($#upgradescripts > -1);
308 sub update2_available {
309 $::lxdebug->enter_sub();
311 my ($self, $dbh) = @_;
313 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
315 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
317 while (my ($tag) = $sth->fetchrow_array) {
318 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
323 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
325 $::lxdebug->leave_sub();
327 return $needs_update;
330 sub unapplied_upgrade_scripts {
331 my ($self, $dbh) = @_;
333 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
335 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
336 my $sth = $dbh->prepare($query);
337 $sth->execute || $self->{form}->dberror($query);
338 while (my ($tag) = $sth->fetchrow_array()) {
339 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
343 return grep { !$_->{applied} } @all_scripts;
346 sub apply_admin_dbupgrade_scripts {
347 my ($self, $called_from_admin) = @_;
349 return 0 if !$self->{auth};
351 my $dbh = $::auth->dbconnect;
352 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
354 return 0 if !@unapplied_scripts;
356 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
357 $self->{form}->{login} ||= 'admin';
359 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $self->{all_controls} };
361 if ($called_from_admin) {
362 $self->{form}->{title} = $::locale->text('Dataset upgrade');
363 $self->{form}->header;
366 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
368 foreach my $control (@unapplied_scripts) {
369 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
370 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
372 $self->process_file($dbh, "sql/$self->{dbdriver}-upgrade2-auth/$control->{file}", $control, $db_charset);
375 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
380 sub _check_for_loops {
381 my ($form, $file_name, $controls, $tag, @path) = @_;
385 my $ctrl = $controls->{$tag};
387 if ($ctrl->{"loop"} == 1) {
389 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
391 } elsif ($ctrl->{"loop"} == 0) {
394 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
400 my ($form, $file_name, $message) = @_;
403 my $locale = $::locale;
405 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
408 sub _dbupdate2_calculate_depth {
409 $::lxdebug->enter_sub(2);
411 my ($tree, $tag) = @_;
413 my $node = $tree->{$tag};
415 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
419 foreach $tag (@{$node->{"depends"}}) {
420 _dbupdate2_calculate_depth($tree, $tag);
421 my $value = $tree->{$tag}->{"depth"};
422 $max_depth = $value if ($value > $max_depth);
425 $node->{"depth"} = $max_depth + 1;
427 $::lxdebug->leave_sub(2);
430 sub sort_dbupdate_controls {
433 $self->parse_dbupdate_controls unless $self->{all_controls};
435 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
447 SL::DBUpgrade2 - Parse database upgrade files stored in
448 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
456 # Apply outstanding updates to the authentication database
457 my $scripts = SL::DBUpgrade2->new(
462 $scripts->apply_admin_dbupgrade_scripts(1);
464 # Apply updates to a user database
465 my $scripts = SL::DBUpgrade2->new(
467 dbdriver => $::form->{dbdriver},
470 User->dbupdate2($form, $scripts->parse_dbupdate_controls);
474 Database upgrade files are used to upgrade the database structure and
475 content of both the authentication database and the user
476 databases. They're applied when a user logs in. As long as the
477 authentication database is not up to date users cannot log in in
478 general, and the admin has to log in first in order to get his
481 Database scripts form a tree by specifying which upgrade file depends
482 on which other upgrade file. This means that such files are always
483 applied in a well-defined order.
485 Each script is run in a separate transaction. If a script fails the
486 current transaction is rolled back and the whole upgrade process is
487 stopped. The user/admin is required to fix the issue manually.
489 A list of applied upgrade scripts is maintained in a table called
490 C<schema_info> for the user database and C<auth.schema_info>) for the
491 authentication database. They contain the tags, the login name of the
492 user having applied the script and the timestamp when the script was
495 Database upgrade files come in two flavours: SQL files and Perl
496 files. For both there are control fields that determine the order in
497 which they're executed, what charset the scripts are written in
498 etc. The control fields are tag/value pairs contained in comments.
500 =head1 OLD UPGRADE FILES
502 The files in C<sql/Pg-upgrade> are so old that I don't bother
503 documenting them. They're handled by this class, too, but new files
504 are only created as C<Pg-upgrade2> files.
506 =head1 CONTROL FIELDS
510 Control fields for Perl files:
513 # @tag2: some more values
518 Control fields for SQL files:
521 -- @tag2: some more values
524 =head2 TAGS AND THEIR MEANING
526 The following tags are recognized:
532 The name for this file. The C<tag> is also used for dependency
533 resolution (see C<depends>).
539 A description presented to the user when the update is applied.
545 A space-separated list of tags of scripts this particular script
546 depends on. All other upgrades listed in C<depends> will be applied
547 before the current one is applied.
552 The charset this file uses. Defaults to C<ISO-8859-15> if
553 missing. Both terms are recognized.
557 Ordering the scripts by their dependencies alone produces a lot of
558 groups of scripts that could be applied at the same time (e.g. if both
559 B and C depend only on A then B could be applied before C or the other
560 way around). This field determines the order inside such a
561 group. Scripts with lower priority fields are executed before scripts
562 with higher priority fields.
564 If two scripts have equal priorities then their tag name decides.
566 The priority defaults to 1000.
574 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
576 Applies all unapplied upgrade files to the authentication/admin
577 database. The parameter C<$called_from_admin> should be truish if the
578 function is called from the web interface and falsish if it's called
579 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
581 =item C<init %params>
583 Initializes the object. Is called directly from L<new> and should not
588 Creates a new object. Possible parameters are:
594 Path to the upgrade files to parse. Required.
598 C<SL::Form> object to use. Required.
602 Name of the database driver. Currently only C<Pg> for PostgreSQL is
607 Optional parameter defaulting to 0. If trueish then the scripts read
608 are the ones applying to the authentication database.
612 =item C<parse_dbupdate_controls>
614 Parses all files located in C<path> (see L<new>), ananlyzes their
615 control fields, builds the tree, and signals errors if control fields
616 are missing/wrong (e.g. a tag name listed in C<depends> is not
617 found). Sets C<$Self->{all_controls}> to the list of database
620 =item C<process_file $dbh, $filename, $version_or_control, $db_charset>
622 Applies a single database upgrade file. Calls L<process_perl_script>
623 for Perl update files and C<process_query> for SQL update
624 files. Requires an open database handle(C<$dbh>), the file name
625 (C<$filename>), a hash structure of the file's control fields as
626 produced by L<parse_dbupdate_controls> (C<$version_or_control>) and
627 the database charset (for on-the-fly charset recoding of the script if
628 required, C<$db_charset>).
630 Returns the result of the actual function called.
632 =item C<process_perl_script $dbh, $filename, $version_or_control, $db_charset>
634 Applies a single Perl database upgrade file. Requires an open database
635 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
636 file's control fields as produced by L<parse_dbupdate_controls>
637 (C<$version_or_control>) and the database charset (for on-the-fly
638 charset recoding of the script if required, C<$db_charset>).
640 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
641 an error is expected. There are two special return values: If the
642 script returns C<1> then the update was successful. Return code C<2>
643 means "needs more interaction from the user; remove users/nologin and
644 end current upgrade process". All other return codes are fatal errors.
646 Inside the Perl script several local variables exist that can be used:
652 A locale object for translating messages
656 The database handle (inside a transaction).
660 The global C<SL::Form> object.
664 A Perl script can actually implement queries that fail while
665 continueing the process by handling the transaction itself, e.g. with
666 the following function:
669 my ($query, $may_fail) = @_;
671 if (!$dbh->do($query)) {
672 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
678 =item C<process_query $dbh, $filename, $version_or_control, $db_charset>
680 Applies a single SQL database upgrade file. Requires an open database
681 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
682 ofile's control fields as produced by L<parse_dbupdate_controls>
683 (C<$version_or_control>) and the database charset (for on-the-fly
684 charset recoding of the script if required, C<$db_charset>).
686 =item C<sort_dbupdate_controls>
688 Sorts the database upgrade scripts according to their C<tag> and
689 C<priority> control fields. Returns a list of their hash
690 representations that can be applied in order.
692 =item C<unapplied_upgrade_scripts $dbh>
694 Returns a list if upgrade scripts (their internal hash representation)
695 that haven't been applied to a database yet. C<$dbh> is an open handle
696 to the database that is checked.
698 Requires that the scripts have been parsed.
700 =item C<update2_available $dbh>
702 Returns trueish if at least one upgrade script hasn't been applied to
703 a database yet. C<$dbh> is an open handle to the database that is
706 Requires that the scripts have been parsed.
716 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>