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($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
167 $i += length $quote_chars[-1] - 1;
168 $char = $quote_chars[-1];
175 if (($char eq "'") || ($char eq "\"")) {
176 push(@quote_chars, $char);
178 } elsif ($char eq '$' # start of dollar quoting
179 && ($tag_end = index($_, '$', $i + 1)) > -1 # ends on same line
180 && (do { $tag = substr($_, $i + 1, $tag_end - $i - 1); 1 }) # extract tag
181 && $tag =~ /^ (?= [A-Za-z_] [A-Za-z0-9_]* | ) $/x) { # tag is identifier
182 push @quote_chars, $char = '$' . $tag . '$';
184 } elsif ($char eq ";") {
186 # Query is complete. Send it.
188 $sth = $dbh->prepare($query);
189 if (!$sth->execute()) {
190 my $errstr = $dbh->errstr;
193 $form->dberror("The database update/creation did not succeed. " .
194 "The file ${filename} containing the following " .
195 "query failed:<br>${query}<br>" .
196 "The error message was: ${errstr}<br>" .
197 "All changes in that file have been reverted.");
209 # Insert a space at the end of each line so that queries split
210 # over multiple lines work properly.
212 $query .= @quote_chars ? "\n" : ' ';
216 if (ref($version_or_control) eq "HASH") {
217 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
218 } elsif ($version_or_control) {
219 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
225 $::lxdebug->leave_sub();
228 # Process a Perl script which updates the database.
229 # If the script returns 1 then the update was successful.
230 # Return code "2" means "needs more interaction; remove
231 # users/nologin and end current request".
232 # All other return codes are fatal errors.
233 sub process_perl_script {
234 $::lxdebug->enter_sub();
236 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
238 my $form = $self->{form};
239 my $fh = IO::File->new($filename, "r") or $form->error("$filename : $!\n");
240 my $file_charset = Common::DEFAULT_CHARSET;
242 if (ref($version_or_control) eq "HASH") {
243 $file_charset = $version_or_control->{charset};
248 next if !/^--\s*\@(?:charset|encoding):\s*(.+)/;
252 $fh->seek(0, SEEK_SET);
255 my $contents = join "", <$fh>;
258 $db_charset ||= Common::DEFAULT_CHARSET;
260 my $iconv = SL::Iconv->new($file_charset, $db_charset);
264 # setup dbup_ export vars
265 my %dbup_myconfig = ();
266 map({ $dbup_myconfig{$_} = $form->{$_}; } qw(dbname dbuser dbpasswd dbhost dbport dbconnect));
268 my $dbup_locale = $::locale;
270 my $result = eval($contents);
277 if (!defined($result)) {
278 print $form->parse_html_template("dbupgrade/error",
279 { "file" => $filename,
282 } elsif (1 != $result) {
283 unlink("users/nologin") if (2 == $result);
287 if (ref($version_or_control) eq "HASH") {
288 $dbh->do("INSERT INTO " . $self->{schema} . "schema_info (tag, login) VALUES (" . $dbh->quote($version_or_control->{"tag"}) . ", " . $dbh->quote($form->{"login"}) . ")");
289 } elsif ($version_or_control) {
290 $dbh->do("UPDATE defaults SET version = " . $dbh->quote($version_or_control));
294 $::lxdebug->leave_sub();
298 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
300 if ($filename =~ m/sql$/) {
301 $self->process_query($dbh, $filename, $version_or_control, $db_charset);
303 $self->process_perl_script($dbh, $filename, $version_or_control, $db_charset);
307 sub update_available {
308 my ($self, $cur_version) = @_;
312 my $dbdriver = $self->{dbdriver};
313 opendir SQLDIR, "sql/${dbdriver}-upgrade" || error("", "sql/${dbdriver}-upgrade: $!");
314 my @upgradescripts = grep /${dbdriver}-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
317 return ($#upgradescripts > -1);
320 sub update2_available {
321 $::lxdebug->enter_sub();
323 my ($self, $dbh) = @_;
325 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
327 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
329 while (my ($tag) = $sth->fetchrow_array) {
330 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
335 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
337 $::lxdebug->leave_sub();
339 return $needs_update;
342 sub unapplied_upgrade_scripts {
343 my ($self, $dbh) = @_;
345 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
347 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
348 my $sth = $dbh->prepare($query);
349 $sth->execute || $self->{form}->dberror($query);
350 while (my ($tag) = $sth->fetchrow_array()) {
351 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
355 return grep { !$_->{applied} } @all_scripts;
358 sub apply_admin_dbupgrade_scripts {
359 my ($self, $called_from_admin) = @_;
361 return 0 if !$self->{auth};
363 my $dbh = $::auth->dbconnect;
364 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
366 return 0 if !@unapplied_scripts;
368 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
369 $self->{form}->{login} ||= 'admin';
371 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $self->{all_controls} };
373 if ($called_from_admin) {
374 $self->{form}->{title} = $::locale->text('Dataset upgrade');
375 $self->{form}->header;
378 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
380 foreach my $control (@unapplied_scripts) {
381 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
382 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
384 $self->process_file($dbh, "sql/$self->{dbdriver}-upgrade2-auth/$control->{file}", $control, $db_charset);
387 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
392 sub _check_for_loops {
393 my ($form, $file_name, $controls, $tag, @path) = @_;
397 my $ctrl = $controls->{$tag};
399 if ($ctrl->{"loop"} == 1) {
401 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
403 } elsif ($ctrl->{"loop"} == 0) {
406 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
412 my ($form, $file_name, $message) = @_;
415 my $locale = $::locale;
417 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
420 sub _dbupdate2_calculate_depth {
421 $::lxdebug->enter_sub(2);
423 my ($tree, $tag) = @_;
425 my $node = $tree->{$tag};
427 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
431 foreach $tag (@{$node->{"depends"}}) {
432 _dbupdate2_calculate_depth($tree, $tag);
433 my $value = $tree->{$tag}->{"depth"};
434 $max_depth = $value if ($value > $max_depth);
437 $node->{"depth"} = $max_depth + 1;
439 $::lxdebug->leave_sub(2);
442 sub sort_dbupdate_controls {
445 $self->parse_dbupdate_controls unless $self->{all_controls};
447 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
459 SL::DBUpgrade2 - Parse database upgrade files stored in
460 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
468 # Apply outstanding updates to the authentication database
469 my $scripts = SL::DBUpgrade2->new(
474 $scripts->apply_admin_dbupgrade_scripts(1);
476 # Apply updates to a user database
477 my $scripts = SL::DBUpgrade2->new(
479 dbdriver => $::form->{dbdriver},
482 User->dbupdate2($form, $scripts->parse_dbupdate_controls);
486 Database upgrade files are used to upgrade the database structure and
487 content of both the authentication database and the user
488 databases. They're applied when a user logs in. As long as the
489 authentication database is not up to date users cannot log in in
490 general, and the admin has to log in first in order to get his
493 Database scripts form a tree by specifying which upgrade file depends
494 on which other upgrade file. This means that such files are always
495 applied in a well-defined order.
497 Each script is run in a separate transaction. If a script fails the
498 current transaction is rolled back and the whole upgrade process is
499 stopped. The user/admin is required to fix the issue manually.
501 A list of applied upgrade scripts is maintained in a table called
502 C<schema_info> for the user database and C<auth.schema_info>) for the
503 authentication database. They contain the tags, the login name of the
504 user having applied the script and the timestamp when the script was
507 Database upgrade files come in two flavours: SQL files and Perl
508 files. For both there are control fields that determine the order in
509 which they're executed, what charset the scripts are written in
510 etc. The control fields are tag/value pairs contained in comments.
512 =head1 OLD UPGRADE FILES
514 The files in C<sql/Pg-upgrade> are so old that I don't bother
515 documenting them. They're handled by this class, too, but new files
516 are only created as C<Pg-upgrade2> files.
518 =head1 CONTROL FIELDS
522 Control fields for Perl files:
525 # @tag2: some more values
530 Control fields for SQL files:
533 -- @tag2: some more values
536 =head2 TAGS AND THEIR MEANING
538 The following tags are recognized:
544 The name for this file. The C<tag> is also used for dependency
545 resolution (see C<depends>).
551 A description presented to the user when the update is applied.
557 A space-separated list of tags of scripts this particular script
558 depends on. All other upgrades listed in C<depends> will be applied
559 before the current one is applied.
564 The charset this file uses. Defaults to C<ISO-8859-15> if
565 missing. Both terms are recognized.
569 Ordering the scripts by their dependencies alone produces a lot of
570 groups of scripts that could be applied at the same time (e.g. if both
571 B and C depend only on A then B could be applied before C or the other
572 way around). This field determines the order inside such a
573 group. Scripts with lower priority fields are executed before scripts
574 with higher priority fields.
576 If two scripts have equal priorities then their tag name decides.
578 The priority defaults to 1000.
586 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
588 Applies all unapplied upgrade files to the authentication/admin
589 database. The parameter C<$called_from_admin> should be truish if the
590 function is called from the web interface and falsish if it's called
591 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
593 =item C<init %params>
595 Initializes the object. Is called directly from L<new> and should not
600 Creates a new object. Possible parameters are:
606 Path to the upgrade files to parse. Required.
610 C<SL::Form> object to use. Required.
614 Name of the database driver. Currently only C<Pg> for PostgreSQL is
619 Optional parameter defaulting to 0. If trueish then the scripts read
620 are the ones applying to the authentication database.
624 =item C<parse_dbupdate_controls>
626 Parses all files located in C<path> (see L<new>), ananlyzes their
627 control fields, builds the tree, and signals errors if control fields
628 are missing/wrong (e.g. a tag name listed in C<depends> is not
629 found). Sets C<$Self->{all_controls}> to the list of database
632 =item C<process_file $dbh, $filename, $version_or_control, $db_charset>
634 Applies a single database upgrade file. Calls L<process_perl_script>
635 for Perl update files and C<process_query> for SQL update
636 files. Requires an open database handle(C<$dbh>), the file name
637 (C<$filename>), a hash structure of the file's control fields as
638 produced by L<parse_dbupdate_controls> (C<$version_or_control>) and
639 the database charset (for on-the-fly charset recoding of the script if
640 required, C<$db_charset>).
642 Returns the result of the actual function called.
644 =item C<process_perl_script $dbh, $filename, $version_or_control, $db_charset>
646 Applies a single Perl database upgrade file. Requires an open database
647 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
648 file's control fields as produced by L<parse_dbupdate_controls>
649 (C<$version_or_control>) and the database charset (for on-the-fly
650 charset recoding of the script if required, C<$db_charset>).
652 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
653 an error is expected. There are two special return values: If the
654 script returns C<1> then the update was successful. Return code C<2>
655 means "needs more interaction from the user; remove users/nologin and
656 end current upgrade process". All other return codes are fatal errors.
658 Inside the Perl script several local variables exist that can be used:
664 A locale object for translating messages
668 The database handle (inside a transaction).
672 The global C<SL::Form> object.
676 A Perl script can actually implement queries that fail while
677 continueing the process by handling the transaction itself, e.g. with
678 the following function:
681 my ($query, $may_fail) = @_;
683 if (!$dbh->do($query)) {
684 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
690 =item C<process_query $dbh, $filename, $version_or_control, $db_charset>
692 Applies a single SQL database upgrade file. Requires an open database
693 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
694 ofile's control fields as produced by L<parse_dbupdate_controls>
695 (C<$version_or_control>) and the database charset (for on-the-fly
696 charset recoding of the script if required, C<$db_charset>).
698 =item C<sort_dbupdate_controls>
700 Sorts the database upgrade scripts according to their C<tag> and
701 C<priority> control fields. Returns a list of their hash
702 representations that can be applied in order.
704 =item C<unapplied_upgrade_scripts $dbh>
706 Returns a list if upgrade scripts (their internal hash representation)
707 that haven't been applied to a database yet. C<$dbh> is an open handle
708 to the database that is checked.
710 Requires that the scripts have been parsed.
712 =item C<update2_available $dbh>
714 Returns trueish if at least one upgrade script hasn't been applied to
715 a database yet. C<$dbh> is an open handle to the database that is
718 Requires that the scripts have been parsed.
728 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>