3 # Copyright (C) 2008 LibLime
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
22 use Encode qw( encode is_utf8 );
24 use YAML::Syck qw( LoadFile );
29 use vars qw(@ISA @EXPORT);
32 @ISA = qw( Exporter );
33 push @EXPORT, qw( foreign_key_exists index_exists column_exists TableExists);
43 my $installer = C4::Installer->new();
44 my $all_languages = getAllLanguages();
45 my $error = $installer->load_db_schema();
47 #fill $list with list of sql files
48 my ($fwk_language, $error_list) = $installer->load_sql_in_order($all_languages, @$list);
49 $installer->set_version_syspref();
50 $installer->set_marcflavour_syspref('MARC21');
60 my $installer = C4::Installer->new();
62 Creates a new installer.
71 # get basic information from context
72 $self->{'dbname'} = C4::Context->config("database");
73 $self->{'dbms'} = C4::Context->config("db_scheme") ? C4::Context->config("db_scheme") : "mysql";
74 $self->{'hostname'} = C4::Context->config("hostname");
75 $self->{'port'} = C4::Context->config("port");
76 $self->{'user'} = C4::Context->config("user");
77 $self->{'password'} = C4::Context->config("pass");
78 $self->{'tls'} = C4::Context->config("tls");
79 if( $self->{'tls'} && $self->{'tls'} eq 'yes' ) {
80 $self->{'ca'} = C4::Context->config('ca');
81 $self->{'cert'} = C4::Context->config('cert');
82 $self->{'key'} = C4::Context->config('key');
83 $self->{'tlsoptions'} = ";mysql_ssl=1;mysql_ssl_client_key=".$self->{key}.";mysql_ssl_client_cert=".$self->{cert}.";mysql_ssl_ca_file=".$self->{ca};
84 $self->{'tlscmdline'} = " --ssl-cert ". $self->{cert} . " --ssl-key " . $self->{key} . " --ssl-ca ".$self->{ca}." "
86 $self->{'dbh'} = DBI->connect("DBI:$self->{dbms}:dbname=$self->{dbname};host=$self->{hostname}" .
87 ( $self->{port} ? ";port=$self->{port}" : "" ).
88 ( $self->{tlsoptions} ? $self->{tlsoptions} : ""),
89 $self->{'user'}, $self->{'password'});
90 $self->{'language'} = undef;
91 $self->{'marcflavour'} = undef;
92 $self->{'dbh'}->do('set NAMES "utf8"');
93 $self->{'dbh'}->{'mysql_enable_utf8'}=1;
99 =head2 marc_framework_sql_list
101 my ($defaulted_to_en, $list) =
102 $installer->marc_framework_sql_list($lang, $marcflavour);
104 Returns in C<$list> a structure listing the filename, description, section,
105 and mandatory/optional status of MARC framework scripts available for C<$lang>
108 If the C<$defaulted_to_en> return value is true, no scripts are available
109 for language C<$lang> and the 'en' ones are returned.
113 sub marc_framework_sql_list {
116 my $marcflavour = shift;
118 my $defaulted_to_en = 0;
121 my $dir = C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}/$lang/marcflavour/".lc($marcflavour);
122 unless (opendir( MYDIR, $dir )) {
124 warn "cannot open MARC frameworks directory $dir";
126 # if no translated MARC framework is available,
128 $dir = C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}/en/marcflavour/".lc($marcflavour);
129 opendir(MYDIR, $dir) or warn "cannot open English MARC frameworks directory $dir";
130 $defaulted_to_en = 1;
133 my @listdir = sort grep { !/^\.|marcflavour/ && -d "$dir/$_" } readdir(MYDIR);
137 my $request = $self->{'dbh'}->prepare("SELECT value FROM systempreferences WHERE variable='FrameworksLoaded'");
139 my ($frameworksloaded) = $request->fetchrow;
140 $frameworksloaded = '' unless defined $frameworksloaded; # avoid warning
141 my %frameworksloaded;
142 foreach ( split( /\|/, $frameworksloaded ) ) {
143 $frameworksloaded{$_} = 1;
146 foreach my $requirelevel (@listdir) {
147 opendir( MYDIR, "$dir/$requirelevel" );
148 my @listname = grep { !/^\./ && -f "$dir/$requirelevel/$_" && $_ =~ m/\.(sql|yml)$/ } readdir(MYDIR);
153 my $name = substr( $_, 0, -4 ); # FIXME: restricted to 3 letter extension
154 open my $fh, "<:encoding(UTF-8)", "$dir/$requirelevel/$name.txt";
156 $line = Encode::encode('UTF-8', $line) unless ( Encode::is_utf8($line) );
157 my @lines = split /\n/, $line;
158 my $mandatory = ($requirelevel =~ /(mandatory|requi|oblig|necess)/i);
162 'fwkfile' => "$dir/$requirelevel/$_",
163 'fwkdescription' => \@lines,
164 'checked' => ( ( $frameworksloaded{$_} || $mandatory ) ? 1 : 0 ),
165 'mandatory' => $mandatory,
169 sort { $a->{'fwkname'} cmp $b->{'fwkname'} } @frameworklist;
171 $cell{"frameworks"} = \@fwks;
172 $cell{"label"} = ucfirst($requirelevel);
173 $cell{"code"} = lc($requirelevel);
174 push @fwklist, \%cell;
177 return ($defaulted_to_en, \@fwklist);
180 =head2 sample_data_sql_list
182 my ($defaulted_to_en, $list) = $installer->sample_data_sql_list($lang);
184 Returns in C<$list> a structure listing the filename, description, section,
185 and mandatory/optional status of sample data scripts available for C<$lang>.
186 If the C<$defaulted_to_en> return value is true, no scripts are available
187 for language C<$lang> and the 'en' ones are returned.
191 sub sample_data_sql_list {
195 my $defaulted_to_en = 0;
198 my $dir = C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}/$lang";
199 unless (opendir( MYDIR, $dir )) {
201 warn "cannot open sample data directory $dir";
203 # if no sample data is available,
205 $dir = C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}/en";
206 opendir(MYDIR, $dir) or warn "cannot open English sample data directory $dir";
207 $defaulted_to_en = 1;
210 my @listdir = sort grep { !/^\.|marcflavour/ && -d "$dir/$_" } readdir(MYDIR);
214 my $request = $self->{'dbh'}->prepare("SELECT value FROM systempreferences WHERE variable='FrameworksLoaded'");
216 my ($frameworksloaded) = $request->fetchrow;
217 $frameworksloaded = '' unless defined $frameworksloaded; # avoid warning
218 my %frameworksloaded;
219 foreach ( split( /\|/, $frameworksloaded ) ) {
220 $frameworksloaded{$_} = 1;
223 foreach my $requirelevel (@listdir) {
224 opendir( MYDIR, "$dir/$requirelevel" );
225 my @listname = grep { !/^\./ && -f "$dir/$requirelevel/$_" && $_ =~ m/\.(sql|yml)$/ } readdir(MYDIR);
230 my ( $name, $ext ) = split /\./, $_;
232 if ( $ext =~ /yml/ ) {
233 my $yaml = LoadFile("$dir/$requirelevel/$name\.$ext");
234 @lines = @{ $yaml->{'description'} };
236 open my $fh, "<:encoding(UTF-8)", "$dir/$requirelevel/$name.txt";
238 $line = Encode::encode('UTF-8', $line) unless ( Encode::is_utf8($line) );
239 @lines = split /\n/, $line;
241 my $mandatory = ($requirelevel =~ /(mandatory|requi|oblig|necess)/i);
245 'fwkfile' => "$dir/$requirelevel/$_",
246 'fwkdescription' => \@lines,
247 'checked' => ( ( $frameworksloaded{$_} || $mandatory ) ? 1 : 0 ),
248 'mandatory' => $mandatory,
251 my @fwks = sort { $a->{'fwkname'} cmp $b->{'fwkname'} } @frameworklist;
253 $cell{"frameworks"} = \@fwks;
254 $cell{"label"} = ucfirst($requirelevel);
255 $cell{"code"} = lc($requirelevel);
256 push @levellist, \%cell;
259 return ($defaulted_to_en, \@levellist);
262 =head2 load_db_schema
264 my $error = $installer->load_db_schema();
266 Loads the SQL script that creates Koha's tables and indexes. The
267 return value is a string containing error messages reported by the
275 my $datadir = C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}";
276 my $error = $self->load_sql("$datadir/kohastructure.sql");
281 =head2 load_sql_in_order
283 my ($fwk_language, $list) = $installer->load_sql_in_order($all_languages, @sql_list);
285 Given a list of SQL scripts supplied in C<@sql_list>, loads each of them
286 into the database and sets the FrameworksLoaded system preference to names
287 of the scripts that were loaded.
289 The SQL files are loaded in alphabetical order by filename (not including
290 directory path). This means that dependencies among the scripts are to
291 be resolved by carefully naming them, keeping in mind that the directory name
292 does *not* currently count.
294 B<FIXME:> this is a rather delicate way of dealing with dependencies between
297 The return value C<$list> is an arrayref containing a hashref for each
298 "level" or directory containing SQL scripts; the hashref in turns contains
299 a list of hashrefs containing a list of each script load and any error
300 messages associated with the loading of each script.
302 B<FIXME:> The C<$fwk_language> code probably doesn't belong and needs to be
303 moved to a different method.
307 sub load_sql_in_order {
309 my $all_languages = shift;
315 my @aa = split /\/|\\/, ($a);
316 my @bb = split /\/|\\/, ($b);
319 my $request = $self->{'dbh'}->prepare( "SELECT value FROM systempreferences WHERE variable='FrameworksLoaded'" );
321 my ($systempreference) = $request->fetchrow;
322 $systempreference = '' unless defined $systempreference; # avoid warning
324 my $global_mandatory_dir = C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}/mandatory";
326 # Make sure some stuffs are loaded first
327 unshift(@fnames, C4::Context->config('intranetdir') . "/installer/data/$self->{dbms}/sysprefs.sql");
329 "$global_mandatory_dir/subtag_registry.sql",
330 "$global_mandatory_dir/auth_val_cat.sql",
331 "$global_mandatory_dir/message_transport_types.sql",
332 "$global_mandatory_dir/sample_notices_message_attributes.sql",
333 "$global_mandatory_dir/sample_notices_message_transports.sql",
334 "$global_mandatory_dir/keyboard_shortcuts.sql",
337 push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/userflags.sql";
338 push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/userpermissions.sql";
339 push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/audio_alerts.sql";
340 push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_offset_types.sql";
341 push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_credit_types.sql";
342 push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_debit_types.sql";
343 foreach my $file (@fnames) {
346 my $error = $self->load_sql($file);
347 my @file = split qr(\/|\\), $file;
348 $lang = $file[ scalar(@file) - 3 ] unless ($lang);
349 my $level = $file[ scalar(@file) - 2 ];
351 $systempreference .= "$file[scalar(@file)-1]|"
352 unless ( index( $systempreference, $file[ scalar(@file) - 1 ] ) >= 0 );
355 #Bulding here a hierarchy to display files by level.
356 push @{ $hashlevel{$level} },
357 { "fwkname" => $file[ scalar(@file) - 1 ], "error" => $error };
360 #systempreference contains an ending |
361 chop $systempreference;
363 map { push @list, { "level" => $_, "fwklist" => $hashlevel{$_} } } keys %hashlevel;
365 for my $each_language (@$all_languages) {
367 # warn "CODE".$each_language->{'language_code'};
368 # warn "LANG:".$lang;
369 if ( $lang eq $each_language->{'language_code'} ) {
370 $fwk_language = $each_language->{language_locale_name};
375 "UPDATE systempreferences set value=\"$systempreference\" where variable='FrameworksLoaded'"
378 unless ( $updateflag == 1 ) {
380 "INSERT INTO systempreferences (value, variable, explanation, type) VALUES (\"$systempreference\",'FrameworksLoaded','Frameworks loaded through webinstaller','choice')";
381 my $rq = $self->{'dbh'}->prepare($string);
384 return ($fwk_language, \@list);
387 =head2 set_marcflavour_syspref
389 $installer->set_marcflavour_syspref($marcflavour);
391 Set the 'marcflavour' system preference. The incoming
392 C<$marcflavour> references to a subdirectory of
393 installer/data/$dbms/$lang/marcflavour, and is
394 normalized to MARC21, UNIMARC or NORMARC.
396 FIXME: this method assumes that the MARC flavour will be either
397 MARC21, UNIMARC or NORMARC.
401 sub set_marcflavour_syspref {
403 my $marcflavour = shift;
405 # we can have some variants of marc flavour, by having different directories, like : unimarc_small and unimarc_full, for small and complete unimarc frameworks.
406 # marc_cleaned finds the marcflavour, without the variant.
407 my $marc_cleaned = 'MARC21';
408 $marc_cleaned = 'UNIMARC' if $marcflavour =~ /unimarc/i;
409 $marc_cleaned = 'NORMARC' if $marcflavour =~ /normarc/i;
411 $self->{'dbh'}->prepare(
412 "INSERT IGNORE INTO `systempreferences` (variable,value,explanation,options,type) VALUES('marcflavour','$marc_cleaned','Define global MARC flavor (MARC21, UNIMARC or NORMARC) used for character encoding','MARC21|UNIMARC|NORMARC','Choice');"
417 =head2 set_version_syspref
419 $installer->set_version_syspref();
421 Set or update the 'Version' system preference to the current
422 Koha software version.
426 sub set_version_syspref {
429 my $kohaversion = Koha::version();
430 # remove the 3 last . to have a Perl number
431 $kohaversion =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
432 if (C4::Context->preference('Version')) {
433 warn "UPDATE Version";
434 my $finish=$self->{'dbh'}->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
435 $finish->execute($kohaversion);
437 warn "INSERT Version";
438 my $finish=$self->{'dbh'}->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller')");
439 $finish->execute($kohaversion);
441 C4::Context->clear_syspref_cache();
446 my $error = $installer->load_sql($filename);
448 Runs the specified input file using a sql loader DBIx::RunSQL, or a yaml loader
449 Returns any strings sent to STDERR
451 # FIXME This should be improved: sometimes the caller and load_sql warn the same
458 my $filename = shift;
461 my $dbh = $self->{ dbh };
466 open STDERR, ">>", \$dup_stderr;
468 if ( $filename =~ /sql$/ ) { # SQL files
470 DBIx::RunSQL->run_sql_file(
478 my $yaml = LoadFile( $filename ); # Load YAML
479 for my $table ( @{ $yaml->{'tables'} } ) {
480 my $table_name = ( keys %$table )[0]; # table name
481 my @rows = @{ $table->{$table_name}->{rows} }; #
482 my @columns = ( sort keys %{$rows[0]} ); # column names
483 my $fields = join ",", @columns; # idem, joined
484 my $placeholders = join ",", map { "?" } @columns; # '?,..,?' string
485 my $query = "INSERT INTO $table_name ( $fields ) VALUES ( $placeholders )";
486 my $sth = $dbh->prepare($query);
487 foreach my $row ( @rows ) {
488 my @values = map { $row->{$_} } @columns;
489 $sth->execute( @values );
495 warn "Something went wrong loading file $filename ($@)";
498 # errors thrown while loading installer data should be logged
500 warn "C4::Installer::load_sql returned the following errors while attempting to load $filename:\n";
501 $error = $dup_stderr;
507 =head2 get_file_path_from_name
509 my $filename = $installer->get_file_path_from_name('script_name');
511 searches through the set of known SQL scripts and finds the fully
512 qualified path name for the script that mathches the input.
514 returns undef if no match was found.
519 sub get_file_path_from_name {
521 my $partialname = shift;
523 my $lang = 'en'; # FIXME: how do I know what language I want?
525 my ($defaulted_to_en, $list) = $self->sample_data_sql_list($lang);
526 # warn( Data::Dumper->Dump( [ $list ], [ 'list' ] ) );
529 foreach my $frameworklist ( @$list ) {
530 push @found, grep { $_->{'fwkfile'} =~ /$partialname$/ } @{$frameworklist->{'frameworks'}};
533 # warn( Data::Dumper->Dump( [ \@found ], [ 'found' ] ) );
534 if ( 0 == scalar @found ) {
536 } elsif ( 1 < scalar @found ) {
537 warn "multiple results found for $partialname";
540 return $found[0]->{'fwkfile'};
545 sub foreign_key_exists {
546 my ( $table_name, $constraint_name ) = @_;
547 my $dbh = C4::Context->dbh;
548 my (undef, $infos) = $dbh->selectrow_array(qq|SHOW CREATE TABLE $table_name|);
549 return $infos =~ m|CONSTRAINT `$constraint_name` FOREIGN KEY|;
553 my ( $table_name, $key_name ) = @_;
554 my $dbh = C4::Context->dbh;
555 my ($exists) = $dbh->selectrow_array(
557 SHOW INDEX FROM $table_name
565 my ( $table_name, $column_name ) = @_;
566 return unless TableExists($table_name);
567 my $dbh = C4::Context->dbh;
568 my ($exists) = $dbh->selectrow_array(
570 SHOW COLUMNS FROM $table_name
572 |, undef, $column_name
577 sub TableExists { # Could be renamed table_exists for consistency
580 my $dbh = C4::Context->dbh;
581 local $dbh->{PrintError} = 0;
582 local $dbh->{RaiseError} = 1;
583 $dbh->do(qq{SELECT * FROM $table WHERE 1 = 0 });
592 C4::Installer is a refactoring of logic originally from installer/installer.pl, which was
593 originally written by Henri-Damien Laurant.
595 Koha Development Team <http://koha-community.org/>
597 Galen Charlton <galen.charlton@liblime.com>