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);
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/'));
47 my $_sample_all = 'load_all.sql';
48 my $_sample_concerto = 'load_concerto.sql';
52 =item update_config() - Puts command line specified settings into xml file
55 my ($services, $settings) = @_;
57 my $parser = XML::LibXML->new();
58 my $opensrf_config = $parser->parse_file($config_file);
61 foreach my $service (@$services) {
62 foreach my $key (keys %$settings) {
63 next unless $settings->{$key};
66 if ($service eq 'state_store') {
67 (@node) = $opensrf_config->findnodes("//state_store/$key/text()");
69 (@node) = $opensrf_config->findnodes("//$service//database/$key/text()");
73 $_->setData($settings->{$key});
80 my $timestamp = sprintf("%d.%d.%d.%d.%d.%d",
81 $year + 1900, $mon +1, $mday, $hour, $min, $sec);
82 if (copy($config_file, "$config_file.$timestamp")) {
83 print "Backed up original configuration file to '$config_file.$timestamp'\n";
85 print STDERR "Unable to write to '$config_file.$timestamp'; bailed out.\n";
88 $opensrf_config->toFile($config_file) or
89 die "ERROR: Failed to update the configuration file '$config_file'\n";
92 =item create_offline_config() - Write out the offline config
94 sub create_offline_config {
95 my ($setup, $settings) = @_;
97 open(FH, '>', $setup) or die "Could not write offline database setup to $setup\n";
99 print "Writing offline database configuration to $setup\n";
101 printf FH "\$main::config{base_dir} = '%s/var/data/offline/';\n", $prefix;
102 printf FH "\$main::config{bootstrap} = '%s/opensrf_core.xml';\n", $sysconfdir;
104 printf FH "\$main::config{dsn} = 'dbi:Pg:host=%s;dbname=%s;port=%d';\n",
105 $settings->{host}, $settings->{db}, $settings->{port};
107 printf FH "\$main::config{usr} = '%s';\n", $settings->{user};
108 printf FH "\$main::config{pw} = '%s';\n", $settings->{pw};
113 =item get_settings() - Extracts database settings from opensrf.xml
116 my $settings = shift;
118 my $host = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/host/text()";
119 my $port = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/port/text()";
120 my $dbname = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/db/text()";
121 my $user = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/user/text()";
122 my $pw = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/pw/text()";
124 my $parser = XML::LibXML->new();
125 my $opensrf_config = $parser->parse_file($config_file);
127 # If the user passed in settings at the command line,
128 # we don't want to override them
129 $settings->{host} = $settings->{host} || $opensrf_config->findnodes($host);
130 $settings->{port} = $settings->{port} || $opensrf_config->findnodes($port);
131 $settings->{db} = $settings->{db} || $opensrf_config->findnodes($dbname);
132 $settings->{user} = $settings->{user} || $opensrf_config->findnodes($user);
133 $settings->{pw} = $settings->{pw} || $opensrf_config->findnodes($pw);
136 =item create_database() - Creates the database using create_database_extensions.sql
138 sub create_database {
139 my $settings = shift;
141 $ENV{'PGUSER'} = $settings->{user};
142 $ENV{'PGPASSWORD'} = $settings->{pw};
143 $ENV{'PGPORT'} = $settings->{port};
144 $ENV{'PGHOST'} = $settings->{host};
145 my @temp = split(/ /, `psql -d postgres -qtc 'show server_version;' | xargs | cut -d. -f 1,2`);
147 my $pgversion = $temp[0];
149 unless ($pgversion >= '9.4') {
152 "\n------------------------------------------------------------------------------\n",
153 "Cannot create database; version of PostgreSQL appears to be less than the\n",
154 "minimum required of 9.4\n",
155 "------------------------------------------------------------------------------\n",
159 $cmd = 'psql -vdb_name=' . $settings->{db} . ' -d postgres -f ' . $create_db_sql_extensions;
160 my @output = `$cmd 2>&1`;
161 if(grep(/(ERROR|No such file or directory)/,@output)) {
162 push(@output, "\n------------------------------------------------------------------------------\n",
163 "There was a problem creating the database.\n",
164 "See above for more information.\n");
165 if(grep/unsupported language/, @output) {
166 push(@output, "\nYou may need to install the postgresql plperl package on the database server.\n");
168 if(grep/No such file or directory/, @output) {
169 if($pgversion >= '9.1') {
170 push(@output, "\nYou may need to install the postgresql contrib package on the database server.\n");
172 push(@output, "\nYou may need to install the postgresql contrib package on this server.\n");
175 push(@output, "------------------------------------------------------------------------------\n");
180 =item create_schema() - Creates the database schema by calling build-db.sh
183 my $settings = shift;
185 chdir(dirname($build_db_sh));
186 my $cmd = File::Spec->catfile('.', basename($build_db_sh)) . " " .
187 $settings->{host} ." ". $settings->{port} ." ".
188 $settings->{db} ." ". $settings->{user} ." ".
194 =item load_sample_data() - Loads sample bib records, copies, users, and transactions
196 sub load_sample_data {
197 my $settings = shift;
199 my $load_script = $_sample_all;
201 if ($load_concerto) {
202 $load_script = $_sample_concerto;
204 $ENV{'PGUSER'} = $settings->{user};
205 $ENV{'PGPASSWORD'} = $settings->{pw};
206 $ENV{'PGPORT'} = $settings->{port};
207 $ENV{'PGHOST'} = $settings->{host};
208 $ENV{'PGDATABASE'} = $settings->{db};
209 my @output = `psql -f $load_script 2>&1`;
214 =item set_admin_account() - Sets the administrative user's user name and password
216 sub set_admin_account {
217 my $admin_user = shift;
218 my $admin_pw = shift;
219 my $settings = shift;
221 my $dbh = DBI->connect('dbi:Pg:dbname=' . $settings->{db} .
222 ';host=' . $settings->{host} . ';port=' . $settings->{port} . ';',
223 $settings->{user} . "", $settings->{pw} . "", {AutoCommit => 1}
226 print STDERR "Could not connect to database to set admin account. ";
227 print STDERR "Error was " . $dbh->errstr . "\n";
230 my $stmt = $dbh->prepare("UPDATE actor.usr SET usrname = ? WHERE id = 1");
231 $stmt->execute(($admin_user));
233 print STDERR "Failed to set admin username. ";
234 print STDERR "Error was " . $dbh->errstr . "\n";
238 # Legacy actor.usr.passwd-style passwords must go through
239 # in intermediate round of hashing before final crypt'ing.
240 # The hashing step requires access to the password salt.
241 # Create a new salt, perform MD5 hashing, set the new password.
242 $stmt = $dbh->prepare("SELECT actor.create_salt('main') AS new_salt");
244 my $new_salt = $dbh->selectrow_hashref($stmt)->{new_salt};
246 $stmt = $dbh->prepare(
247 "SELECT actor.set_passwd(1, 'main', MD5(? || MD5(?)), ?)");
248 $stmt->execute(($new_salt, $admin_pw, $new_salt));
250 print STDERR "Failed to set admin password. ";
251 print STDERR "Error was " . $dbh->errstr . "\n";
263 GetOptions("create-schema" => \$cschema,
264 "create-database" => \$cdatabase,
265 "load-all-sample" => \$load_all,
266 "load-concerto-sample" => \$load_concerto,
267 "create-offline" => \$offline,
268 "update-config" => \$uconfig,
269 "config-file=s" => \$config_file,
270 "build-db-file=s" => \$build_db_sh,
271 "create-db-sql-extensions=s" => \$create_db_sql_extensions,
272 "pg-config=s" => \$pgconfig,
273 "admin-user=s" => \$admin_user,
274 "admin-password=s" => \$admin_pw,
275 "service=s" => \@services,
276 "user=s" => \$settings{'user'},
277 "password=s" => \$settings{'pw'},
278 "database=s" => \$settings{'db'},
279 "hostname=s" => \$settings{'host'},
280 "port=i" => \$settings{'port'},
284 if (grep(/^all$/, @services)) {
285 @services = qw/reporter open-ils.cstore open-ils.pcrud open-ils.storage open-ils.reporter-store state_store open-ils.qstore/;
288 my $eg_config = File::Spec->catfile($script_dir, '../extras/eg_config');
291 my @temp = `$eg_config --sysconfdir`;
293 $sysconfdir = $temp[0];
294 $config_file = File::Spec->catfile($sysconfdir, "opensrf.xml");
298 my @temp = `$eg_config --prefix`;
304 $build_db_sh = File::Spec->catfile($script_dir, '../sql/Pg/build-db.sh');
307 if (!$create_db_sql_extensions) {
308 $create_db_sql_extensions = File::Spec->catfile($script_dir, '../sql/Pg/create_database_extensions.sql');
311 if (!$offline_file) {
312 $offline_file = File::Spec->catfile($sysconfdir, 'offline-config.pl');
315 unless (-e $build_db_sh) { die "Error: $build_db_sh does not exist. \n"; }
316 unless (-e $config_file) { die "Error: $config_file does not exist. \n"; }
318 if ($uconfig) { update_config(\@services, \%settings); }
320 # Get our settings from the config file
321 get_settings(\%settings);
323 if ($cdatabase) { create_database(\%settings); }
324 if ($cschema) { create_schema(\%settings); }
325 if ($admin_user && $admin_pw) {
326 set_admin_account($admin_user, $admin_pw, \%settings);
328 if ($load_all || $load_concerto) {
329 load_sample_data(\%settings);
331 if ($offline) { create_offline_config($offline_file, \%settings); }
333 if ((!$cdatabase && !$cschema && !$load_all && !$load_concerto && !$uconfig && !$offline && !$admin_pw) || $help) {
337 eg_db_config.pl [OPTION] ... [COMMAND] ... [CONFIG OPTIONS]
340 Creates or recreates the Evergreen database schema based on the settings
341 in the opensrf.xml configuration file.
343 Manipulates the configuration file
347 specifies the opensrf.xml file. Defaults to @sysconfdir@/opensrf.xml
350 specifies the script that creates the database schema. Defaults to
351 Open-ILS/src/sql/pg/build-db.sh
354 specifies the offline database settings file required by the offline
355 data uploader. Defaults to @sysconfdir@/offline-config.pl
359 Configures Evergreen database settings in the file specified by
363 Creates the database setting file required by the offline data uploader
366 Creates the Evergreen database schema according to the settings in
367 the file specified by --build-db-file.
370 Creates the database itself, provided the user and password options
371 represent a superuser.
374 Loads all sample data, including bibliographic records, call numbers,
375 copies, users, and transactions.
377 --load-concerto-sample
378 Loads a subset of sample data that includes just 100 bibliographic
379 records, and associated call numbers and copies.
383 Specify "all" or one or more of the following services to update:
388 * open-ils.reporter-store
391 DATABASE CONFIGURATION OPTIONS
392 --user username for the database
394 --password password for the user
396 --database name of the database
398 --hostname name or address of the database host
400 --port port number for database access
402 --admin-user administration user's user name
404 --admin-pass administration user's password
407 This script is normally used during the initial installation and
408 configuration process. This creates the database schema, sets
409 the administration user's user name and password, and modifies your
410 configuration files to include the correct database connection
413 For a single server install, or an install with one web/application
414 server and one database server, you will typically want to invoke this
415 script with a complete set of commands:
417 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
418 --service all --create-schema --create-offline \
419 --user <db-user> --password <db-pass> --hostname localhost --port 5432 \
420 --database evergreen --admin-user <admin-user> --admin-pass <admin-pass>
422 To update the configuration for a single service - for example, if you
423 replicated a database for reporting purposes - just issue the
424 --update-config command with the service identified and the changed
425 database parameters specified:
427 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
428 --service reporter --hostname foobar --password newpass