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) = @_;
247 # setup dbup_ export vars & run script
248 my %dbup_myconfig = map { ($_ => $::form->{$_}) } qw(dbname dbuser dbpasswd dbhost dbport dbconnect);
249 my $result = SL::DBUpgrade2::Base::execute_script(
250 file_name => $filename,
251 tag => $version_or_control->{tag},
254 myconfig => \%dbup_myconfig,
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 $::lxdebug->leave_sub();
281 my ($self, $dbh, $filename, $version_or_control, $db_charset) = @_;
283 if ($filename =~ m/sql$/) {
284 $self->process_query($dbh, $filename, $version_or_control, $db_charset);
286 $self->process_perl_script($dbh, $filename, $version_or_control, $db_charset);
290 sub update_available {
291 my ($self, $cur_version) = @_;
295 my $dbdriver = $self->{dbdriver};
296 opendir SQLDIR, "sql/${dbdriver}-upgrade" || error("", "sql/${dbdriver}-upgrade: $!");
297 my @upgradescripts = grep /${dbdriver}-upgrade-\Q$cur_version\E.*\.(sql|pl)$/, readdir SQLDIR;
300 return ($#upgradescripts > -1);
303 sub update2_available {
304 $::lxdebug->enter_sub();
306 my ($self, $dbh) = @_;
308 map { $_->{applied} = 0; } values %{ $self->{all_controls} };
310 my $sth = $dbh->prepare(qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|);
312 while (my ($tag) = $sth->fetchrow_array) {
313 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
318 my $needs_update = any { !$_->{applied} } values %{ $self->{all_controls} };
320 $::lxdebug->leave_sub();
322 return $needs_update;
325 sub unapplied_upgrade_scripts {
326 my ($self, $dbh) = @_;
328 my @all_scripts = map { $_->{applied} = 0; $_ } $self->sort_dbupdate_controls;
330 my $query = qq|SELECT tag FROM | . $self->{schema} . qq|schema_info|;
331 my $sth = $dbh->prepare($query);
332 $sth->execute || $self->{form}->dberror($query);
333 while (my ($tag) = $sth->fetchrow_array()) {
334 $self->{all_controls}->{$tag}->{applied} = 1 if defined $self->{all_controls}->{$tag};
338 return grep { !$_->{applied} } @all_scripts;
341 sub apply_admin_dbupgrade_scripts {
342 my ($self, $called_from_admin) = @_;
344 return 0 if !$self->{auth};
346 my $dbh = $::auth->dbconnect;
347 my @unapplied_scripts = $self->unapplied_upgrade_scripts($dbh);
349 return 0 if !@unapplied_scripts;
351 my $db_charset = $::lx_office_conf{system}->{dbcharset} || Common::DEFAULT_CHARSET;
352 $self->{form}->{login} ||= 'admin';
354 map { $_->{description} = SL::Iconv::convert($_->{charset}, $db_charset, $_->{description}) } values %{ $self->{all_controls} };
356 if ($called_from_admin) {
357 $self->{form}->{title} = $::locale->text('Dataset upgrade');
358 $self->{form}->header;
361 print $self->{form}->parse_html_template("dbupgrade/header", { dbname => $::auth->{DB_config}->{db} });
363 foreach my $control (@unapplied_scripts) {
364 $::lxdebug->message(LXDebug->DEBUG2(), "Applying Update $control->{file}");
365 print $self->{form}->parse_html_template("dbupgrade/upgrade_message2", $control);
367 $self->process_file($dbh, "sql/$self->{dbdriver}-upgrade2-auth/$control->{file}", $control, $db_charset);
370 print $self->{form}->parse_html_template("dbupgrade/footer", { is_admin => 1 }) if $called_from_admin;
375 sub _check_for_loops {
376 my ($form, $file_name, $controls, $tag, @path) = @_;
380 my $ctrl = $controls->{$tag};
382 if ($ctrl->{"loop"} == 1) {
384 _control_error($form, $file_name, $::locale->text("Dependency loop detected:") . " " . join(" -> ", @path))
386 } elsif ($ctrl->{"loop"} == 0) {
389 map({ _check_for_loops($form, $file_name, $controls, $_, @path); } @{ $ctrl->{"depends"} });
395 my ($form, $file_name, $message) = @_;
398 my $locale = $::locale;
400 $form->error(sprintf($locale->text("Error in database control file '%s': %s"), $file_name, $message));
403 sub _dbupdate2_calculate_depth {
404 $::lxdebug->enter_sub(2);
406 my ($tree, $tag) = @_;
408 my $node = $tree->{$tag};
410 return $::lxdebug->leave_sub(2) if (defined($node->{"depth"}));
414 foreach $tag (@{$node->{"depends"}}) {
415 _dbupdate2_calculate_depth($tree, $tag);
416 my $value = $tree->{$tag}->{"depth"};
417 $max_depth = $value if ($value > $max_depth);
420 $node->{"depth"} = $max_depth + 1;
422 $::lxdebug->leave_sub(2);
425 sub sort_dbupdate_controls {
428 $self->parse_dbupdate_controls unless $self->{all_controls};
430 return sort { ($a->{depth} <=> $b->{depth}) || ($a->{priority} <=> $b->{priority}) || ($a->{tag} cmp $b->{tag}) } values %{ $self->{all_controls} };
442 SL::DBUpgrade2 - Parse database upgrade files stored in
443 C<sql/Pg-upgrade2> and C<sql/Pg-upgrade2-auth> (and also in
451 # Apply outstanding updates to the authentication database
452 my $scripts = SL::DBUpgrade2->new(
457 $scripts->apply_admin_dbupgrade_scripts(1);
459 # Apply updates to a user database
460 my $scripts = SL::DBUpgrade2->new(
462 dbdriver => $::form->{dbdriver},
465 User->dbupdate2($form, $scripts->parse_dbupdate_controls);
469 Database upgrade files are used to upgrade the database structure and
470 content of both the authentication database and the user
471 databases. They're applied when a user logs in. As long as the
472 authentication database is not up to date users cannot log in in
473 general, and the admin has to log in first in order to get his
476 Database scripts form a tree by specifying which upgrade file depends
477 on which other upgrade file. This means that such files are always
478 applied in a well-defined order.
480 Each script is run in a separate transaction. If a script fails the
481 current transaction is rolled back and the whole upgrade process is
482 stopped. The user/admin is required to fix the issue manually.
484 A list of applied upgrade scripts is maintained in a table called
485 C<schema_info> for the user database and C<auth.schema_info>) for the
486 authentication database. They contain the tags, the login name of the
487 user having applied the script and the timestamp when the script was
490 Database upgrade files come in two flavours: SQL files and Perl
491 files. For both there are control fields that determine the order in
492 which they're executed, what charset the scripts are written in
493 etc. The control fields are tag/value pairs contained in comments.
495 =head1 OLD UPGRADE FILES
497 The files in C<sql/Pg-upgrade> are so old that I don't bother
498 documenting them. They're handled by this class, too, but new files
499 are only created as C<Pg-upgrade2> files.
501 =head1 CONTROL FIELDS
505 Control fields for Perl files:
508 # @tag2: some more values
513 Control fields for SQL files:
516 -- @tag2: some more values
519 =head2 TAGS AND THEIR MEANING
521 The following tags are recognized:
527 The name for this file. The C<tag> is also used for dependency
528 resolution (see C<depends>).
534 A description presented to the user when the update is applied.
540 A space-separated list of tags of scripts this particular script
541 depends on. All other upgrades listed in C<depends> will be applied
542 before the current one is applied.
547 The charset this file uses. Defaults to C<ISO-8859-15> if
548 missing. Both terms are recognized.
552 Ordering the scripts by their dependencies alone produces a lot of
553 groups of scripts that could be applied at the same time (e.g. if both
554 B and C depend only on A then B could be applied before C or the other
555 way around). This field determines the order inside such a
556 group. Scripts with lower priority fields are executed before scripts
557 with higher priority fields.
559 If two scripts have equal priorities then their tag name decides.
561 The priority defaults to 1000.
569 =item C<apply_admin_dbupgrade_scripts $called_from_admin>
571 Applies all unapplied upgrade files to the authentication/admin
572 database. The parameter C<$called_from_admin> should be truish if the
573 function is called from the web interface and falsish if it's called
574 from e.g. a command line script like C<scripts/dbupgrade2_tool.pl>.
576 =item C<init %params>
578 Initializes the object. Is called directly from L<new> and should not
583 Creates a new object. Possible parameters are:
589 Path to the upgrade files to parse. Required.
593 C<SL::Form> object to use. Required.
597 Name of the database driver. Currently only C<Pg> for PostgreSQL is
602 Optional parameter defaulting to 0. If trueish then the scripts read
603 are the ones applying to the authentication database.
607 =item C<parse_dbupdate_controls>
609 Parses all files located in C<path> (see L<new>), ananlyzes their
610 control fields, builds the tree, and signals errors if control fields
611 are missing/wrong (e.g. a tag name listed in C<depends> is not
612 found). Sets C<$Self->{all_controls}> to the list of database
615 =item C<process_file $dbh, $filename, $version_or_control, $db_charset>
617 Applies a single database upgrade file. Calls L<process_perl_script>
618 for Perl update files and C<process_query> for SQL update
619 files. Requires an open database handle(C<$dbh>), the file name
620 (C<$filename>), a hash structure of the file's control fields as
621 produced by L<parse_dbupdate_controls> (C<$version_or_control>) and
622 the database charset (for on-the-fly charset recoding of the script if
623 required, C<$db_charset>).
625 Returns the result of the actual function called.
627 =item C<process_perl_script $dbh, $filename, $version_or_control, $db_charset>
629 Applies a single Perl database upgrade file. Requires an open database
630 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
631 file's control fields as produced by L<parse_dbupdate_controls>
632 (C<$version_or_control>) and the database charset (for on-the-fly
633 charset recoding of the script if required, C<$db_charset>).
635 Perl scripts are executed via L<eval>. If L<eval> returns falsish then
636 an error is expected. There are two special return values: If the
637 script returns C<1> then the update was successful. Return code C<2>
638 means "needs more interaction from the user; remove users/nologin and
639 end current upgrade process". All other return codes are fatal errors.
641 Inside the Perl script several local variables exist that can be used:
647 A locale object for translating messages
651 The database handle (inside a transaction).
655 The global C<SL::Form> object.
659 A Perl script can actually implement queries that fail while
660 continueing the process by handling the transaction itself, e.g. with
661 the following function:
664 my ($query, $may_fail) = @_;
666 if (!$dbh->do($query)) {
667 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr) unless $may_fail;
673 =item C<process_query $dbh, $filename, $version_or_control, $db_charset>
675 Applies a single SQL database upgrade file. Requires an open database
676 handle(C<$dbh>), the file name (C<$filename>), a hash structure of the
677 ofile's control fields as produced by L<parse_dbupdate_controls>
678 (C<$version_or_control>) and the database charset (for on-the-fly
679 charset recoding of the script if required, C<$db_charset>).
681 =item C<sort_dbupdate_controls>
683 Sorts the database upgrade scripts according to their C<tag> and
684 C<priority> control fields. Returns a list of their hash
685 representations that can be applied in order.
687 =item C<unapplied_upgrade_scripts $dbh>
689 Returns a list if upgrade scripts (their internal hash representation)
690 that haven't been applied to a database yet. C<$dbh> is an open handle
691 to the database that is checked.
693 Requires that the scripts have been parsed.
695 =item C<update2_available $dbh>
697 Returns trueish if at least one upgrade script hasn't been applied to
698 a database yet. C<$dbh> is an open handle to the database that is
701 Requires that the scripts have been parsed.
711 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>