2 # eg_db_config.pl -- configure Evergreen database settings and create schema
5 # Copyright (C) 2008 Equinox Software, Inc.
6 # Copyright (C) 2008-2009 Laurentian University
7 # Author: Kevin Beswick <kevinbeswick00@gmail.com>
8 # Author: Dan Scott <dscott@laurentian.ca>
10 # This program is free software; you can redistribute it and/or
11 # modify it under the terms of the GNU General Public License
12 # as published by the Free Software Foundation; either version 2
13 # of the License, or (at your option) any later version.
15 # This program is distributed in the hope that it will be useful,
16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 # GNU General Public License for more details.
20 use strict; use warnings;
27 use Cwd qw/abs_path getcwd/;
29 my ($dbhost, $dbport, $dbname, $dbuser, $dbpw, $help, $admin_user, $admin_pw, $load_all, $load_concerto, $load_concerto_enhanced);
32 my $offline_file = '';
35 my $create_db_sql_extensions = '';
38 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
42 # Get the directory for this script
43 my $script_dir = abs_path(dirname($0));
45 # Set the location and base file for sample data
46 my $_sample_dir = abs_path(File::Spec->catdir($script_dir, '../../tests/datasets/sql/concerto/'));
47 my $_sample_all = 'load_all.sql';
48 my $_sample_concerto = 'load_concerto.sql';
49 my $_enhanced_dir = abs_path(File::Spec->catdir($script_dir, '../../tests/datasets/sql/concerto_enhanced/'));
50 my $_enhanced_loader = 'load_all.sql';
55 =item update_config() - Puts command line specified settings into xml file
58 my ($services, $settings) = @_;
60 my $parser = XML::LibXML->new();
61 my $opensrf_config = $parser->parse_file($config_file);
64 foreach my $service (@$services) {
65 foreach my $key (keys %$settings) {
66 next unless $settings->{$key};
69 if ($service eq 'state_store') {
70 (@node) = $opensrf_config->findnodes("//state_store/$key/text()");
72 (@node) = $opensrf_config->findnodes("//$service//database/$key/text()");
76 $_->setData($settings->{$key});
83 my $timestamp = sprintf("%d.%d.%d.%d.%d.%d",
84 $year + 1900, $mon +1, $mday, $hour, $min, $sec);
85 if (copy($config_file, "$config_file.$timestamp")) {
86 print "Backed up original configuration file to '$config_file.$timestamp'\n";
88 print STDERR "Unable to write to '$config_file.$timestamp'; bailed out.\n";
91 $opensrf_config->toFile($config_file) or
92 die "ERROR: Failed to update the configuration file '$config_file'\n";
95 =item create_offline_config() - Write out the offline config
97 sub create_offline_config {
98 my ($setup, $settings) = @_;
100 open(FH, '>', $setup) or die "Could not write offline database setup to $setup\n";
102 print "Writing offline database configuration to $setup\n";
104 printf FH "\$main::config{base_dir} = '%s/var/data/offline/';\n", $prefix;
105 printf FH "\$main::config{bootstrap} = '%s/opensrf_core.xml';\n", $sysconfdir;
107 printf FH "\$main::config{dsn} = 'dbi:Pg:host=%s;dbname=%s;port=%d';\n",
108 $settings->{host}, $settings->{db}, $settings->{port};
110 printf FH "\$main::config{usr} = '%s';\n", $settings->{user};
111 printf FH "\$main::config{pw} = '%s';\n", $settings->{pw};
116 =item get_settings() - Extracts database settings from opensrf.xml
119 my $settings = shift;
121 my $host = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/host/text()";
122 my $port = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/port/text()";
123 my $dbname = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/db/text()";
124 my $user = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/user/text()";
125 my $pw = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/pw/text()";
127 my $parser = XML::LibXML->new();
128 my $opensrf_config = $parser->parse_file($config_file);
130 # If the user passed in settings at the command line,
131 # we don't want to override them
132 $settings->{host} = $settings->{host} || $opensrf_config->findnodes($host);
133 $settings->{port} = $settings->{port} || $opensrf_config->findnodes($port);
134 $settings->{db} = $settings->{db} || $opensrf_config->findnodes($dbname);
135 $settings->{user} = $settings->{user} || $opensrf_config->findnodes($user);
136 $settings->{pw} = $settings->{pw} || $opensrf_config->findnodes($pw);
139 =item create_database() - Creates the database using create_database_extensions.sql
141 sub create_database {
142 my $settings = shift;
144 $ENV{'PGUSER'} = $settings->{user};
145 $ENV{'PGPASSWORD'} = $settings->{pw};
146 $ENV{'PGPORT'} = $settings->{port};
147 $ENV{'PGHOST'} = $settings->{host};
148 my @temp = split(/ /, `psql -d postgres -qXAtc 'show server_version;' | cut -d. -f 1,2`);
150 my $pgversion = $temp[0];
152 unless ($pgversion >= '9.6') {
155 "\n------------------------------------------------------------------------------\n",
156 "Cannot create database; version of PostgreSQL appears to be less than the\n",
157 "minimum required of 9.6\n",
158 "------------------------------------------------------------------------------\n",
162 $cmd = 'psql -vdb_name=' . $settings->{db} . ' -d postgres -f ' . $create_db_sql_extensions;
163 my @output = `$cmd 2>&1`;
164 if(grep(/(ERROR|No such file or directory)/,@output)) {
165 push(@output, "\n------------------------------------------------------------------------------\n",
166 "There was a problem creating the database.\n",
167 "See above for more information.\n");
168 if(grep/unsupported language/, @output) {
169 push(@output, "\nYou may need to install the postgresql plperl package on the database server.\n");
171 if(grep/No such file or directory/, @output) {
172 if($pgversion >= '9.1') {
173 push(@output, "\nYou may need to install the postgresql contrib package on the database server.\n");
175 push(@output, "\nYou may need to install the postgresql contrib package on this server.\n");
178 push(@output, "------------------------------------------------------------------------------\n");
183 =item create_schema() - Creates the database schema by calling build-db.sh
186 my $settings = shift;
188 chdir(dirname($build_db_sh));
189 my $cmd = File::Spec->catfile('.', basename($build_db_sh)) . " " .
190 $settings->{host} ." ". $settings->{port} ." ".
191 $settings->{db} ." ". $settings->{user} ." ".
197 =item load_sample_data() - Loads sample bib records, copies, users, and transactions
199 sub load_sample_data {
200 my $settings = shift;
202 my $load_script = $_sample_all;
204 if ($load_concerto) {
205 $load_script = $_sample_concerto;
207 elsif ($load_concerto_enhanced) {
208 $load_script = $_enhanced_loader;
209 chdir($_enhanced_dir);
211 $ENV{'PGUSER'} = $settings->{user};
212 $ENV{'PGPASSWORD'} = $settings->{pw};
213 $ENV{'PGPORT'} = $settings->{port};
214 $ENV{'PGHOST'} = $settings->{host};
215 $ENV{'PGDATABASE'} = $settings->{db};
216 my @output = `psql -f $load_script 2>&1`;
221 =item set_admin_account() - Sets the administrative user's username and password
223 sub set_admin_account {
224 my $admin_user = shift;
225 my $admin_pw = shift;
226 my $settings = shift;
228 my $dbh = DBI->connect('dbi:Pg:dbname=' . $settings->{db} .
229 ';host=' . $settings->{host} . ';port=' . $settings->{port} . ';',
230 $settings->{user} . "", $settings->{pw} . "", {AutoCommit => 1}
233 print STDERR "Could not connect to database to set admin account. ";
234 print STDERR "Error was " . $dbh->errstr . "\n";
237 my $stmt = $dbh->prepare("UPDATE actor.usr SET usrname = ? WHERE id = 1");
238 $stmt->execute(($admin_user));
240 print STDERR "Failed to set admin username. ";
241 print STDERR "Error was " . $dbh->errstr . "\n";
245 # Legacy actor.usr.passwd-style passwords must go through
246 # in intermediate round of hashing before final crypt'ing.
247 # The hashing step requires access to the password salt.
248 # Create a new salt, perform MD5 hashing, set the new password.
249 $stmt = $dbh->prepare("SELECT actor.create_salt('main') AS new_salt");
251 my $new_salt = $dbh->selectrow_hashref($stmt)->{new_salt};
253 $stmt = $dbh->prepare(
254 "SELECT actor.set_passwd(1, 'main', MD5(? || MD5(?)), ?)");
255 $stmt->execute(($new_salt, $admin_pw, $new_salt));
257 print STDERR "Failed to set admin password. ";
258 print STDERR "Error was " . $dbh->errstr . "\n";
270 GetOptions("create-schema" => \$cschema,
271 "create-database" => \$cdatabase,
272 "load-all-sample" => \$load_all,
273 "load-concerto-sample" => \$load_concerto,
274 "load-concerto-enhanced" => \$load_concerto_enhanced,
275 "create-offline" => \$offline,
276 "update-config" => \$uconfig,
277 "config-file=s" => \$config_file,
278 "build-db-file=s" => \$build_db_sh,
279 "create-db-sql-extensions=s" => \$create_db_sql_extensions,
280 "pg-config=s" => \$pgconfig,
281 "admin-user=s" => \$admin_user,
282 "admin-password=s" => \$admin_pw,
283 "service=s" => \@services,
284 "user=s" => \$settings{'user'},
285 "password=s" => \$settings{'pw'},
286 "database=s" => \$settings{'db'},
287 "hostname=s" => \$settings{'host'},
288 "port=i" => \$settings{'port'},
292 if (grep(/^all$/, @services)) {
293 @services = qw/reporter open-ils.cstore open-ils.pcrud open-ils.storage open-ils.reporter-store state_store open-ils.qstore/;
296 my $eg_config = File::Spec->catfile($script_dir, '../extras/eg_config');
299 my @temp = `$eg_config --sysconfdir`;
301 $sysconfdir = $temp[0];
302 $config_file = File::Spec->catfile($sysconfdir, "opensrf.xml");
306 my @temp = `$eg_config --prefix`;
312 $build_db_sh = File::Spec->catfile($script_dir, '../sql/Pg/build-db.sh');
315 if (!$create_db_sql_extensions) {
316 $create_db_sql_extensions = File::Spec->catfile($script_dir, '../sql/Pg/create_database_extensions.sql');
319 if (!$offline_file) {
320 $offline_file = File::Spec->catfile($sysconfdir, 'offline-config.pl');
323 unless (-e $build_db_sh) { die "Error: $build_db_sh does not exist. \n"; }
324 unless (-e $config_file) { die "Error: $config_file does not exist. \n"; }
326 if ($uconfig) { update_config(\@services, \%settings); }
328 # Get our settings from the config file
329 get_settings(\%settings);
331 if ($cdatabase) { create_database(\%settings); }
332 if ($cschema) { create_schema(\%settings); }
333 if ($admin_user && $admin_pw) {
334 set_admin_account($admin_user, $admin_pw, \%settings);
336 if ($load_all || $load_concerto || $load_concerto_enhanced) {
337 load_sample_data(\%settings);
339 if ($offline) { create_offline_config($offline_file, \%settings); }
341 if ((!$cdatabase && !$cschema && !$load_all && !$load_concerto && !$uconfig && !$offline && !$admin_pw) || $help) {
345 eg_db_config.pl [OPTION] ... [COMMAND] ... [CONFIG OPTIONS]
348 Creates or recreates the Evergreen database schema based on the settings
349 in the opensrf.xml configuration file.
351 Manipulates the configuration file
355 specifies the opensrf.xml file. Defaults to @sysconfdir@/opensrf.xml
358 specifies the script that creates the database schema. Defaults to
359 Open-ILS/src/sql/pg/build-db.sh
362 specifies the offline database settings file required by the offline
363 data uploader. Defaults to @sysconfdir@/offline-config.pl
367 Configures Evergreen database settings in the file specified by
371 Creates the database setting file required by the offline data uploader
374 Creates the Evergreen database schema according to the settings in
375 the file specified by --build-db-file.
378 Creates the database itself, provided the user and password options
379 represent a superuser.
382 Loads all sample data, including bibliographic records, call numbers,
383 copies, users, and transactions.
385 --load-concerto-sample
386 Loads a subset of sample data that includes just 100 bibliographic
387 records, and associated call numbers and copies.
389 --load-concerto-enhanced
390 Loads the community enhanced test dataset. Details here:
391 https://bugs.launchpad.net/evergreen/+bug/1901932
392 A new dataset can be generated using the support script "make_concerto_from_evergreen_db.pl"
397 Specify "all" or one or more of the following services to update:
402 * open-ils.reporter-store
405 DATABASE CONFIGURATION OPTIONS
406 --user username for the database
408 --password password for the user
410 --database name of the database
412 --hostname name or address of the database host
414 --port port number for database access
416 --admin-user administration user's username
418 --admin-pass administration user's password
421 This script is normally used during the initial installation and
422 configuration process. This creates the database schema, sets
423 the administration user's username and password, and modifies your
424 configuration files to include the correct database connection
427 For a single server install, or an install with one web/application
428 server and one database server, you will typically want to invoke this
429 script with a complete set of commands:
431 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
432 --service all --create-schema --create-offline \
433 --user <db-user> --password <db-pass> --hostname localhost --port 5432 \
434 --database evergreen --admin-user <admin-user> --admin-pass <admin-pass>
436 To update the configuration for a single service - for example, if you
437 replicated a database for reporting purposes - just issue the
438 --update-config command with the service identified and the changed
439 database parameters specified:
441 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
442 --service reporter --hostname foobar --password newpass