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 $pg_contribdir = '';
36 my $create_db_sql_contribs = '';
37 my $create_db_sql_extensions = '';
40 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
44 # Get the directory for this script
45 my $script_dir = abs_path(dirname($0));
47 # Set the location and base file for sample data
48 my $_sample_dir = abs_path(File::Spec->catdir($script_dir, '../../tests/datasets/sql/'));
49 my $_sample_all = 'load_all.sql';
50 my $_sample_concerto = 'load_concerto.sql';
54 =item update_config() - Puts command line specified settings into xml file
57 my ($services, $settings) = @_;
59 my $parser = XML::LibXML->new();
60 my $opensrf_config = $parser->parse_file($config_file);
63 foreach my $service (@$services) {
64 foreach my $key (keys %$settings) {
65 next unless $settings->{$key};
68 if ($service eq 'state_store') {
69 (@node) = $opensrf_config->findnodes("//state_store/$key/text()");
71 (@node) = $opensrf_config->findnodes("//$service//database/$key/text()");
75 $_->setData($settings->{$key});
82 my $timestamp = sprintf("%d.%d.%d.%d.%d.%d",
83 $year + 1900, $mon +1, $mday, $hour, $min, $sec);
84 if (copy($config_file, "$config_file.$timestamp")) {
85 print "Backed up original configuration file to '$config_file.$timestamp'\n";
87 print STDERR "Unable to write to '$config_file.$timestamp'; bailed out.\n";
90 $opensrf_config->toFile($config_file) or
91 die "ERROR: Failed to update the configuration file '$config_file'\n";
94 =item create_offline_config() - Write out the offline config
96 sub create_offline_config {
97 my ($setup, $settings) = @_;
99 open(FH, '>', $setup) or die "Could not write offline database setup to $setup\n";
101 print "Writing offline database configuration to $setup\n";
103 printf FH "\$main::config{base_dir} = '%s/var/data/offline/';\n", $prefix;
104 printf FH "\$main::config{bootstrap} = '%s/opensrf_core.xml';\n", $sysconfdir;
106 printf FH "\$main::config{dsn} = 'dbi:Pg:host=%s;dbname=%s;port=%d';\n",
107 $settings->{host}, $settings->{db}, $settings->{port};
109 printf FH "\$main::config{usr} = '%s';\n", $settings->{user};
110 printf FH "\$main::config{pw} = '%s';\n", $settings->{pw};
115 =item get_settings() - Extracts database settings from opensrf.xml
118 my $settings = shift;
120 my $host = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/host/text()";
121 my $port = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/port/text()";
122 my $dbname = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/db/text()";
123 my $user = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/user/text()";
124 my $pw = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/pw/text()";
126 my $parser = XML::LibXML->new();
127 my $opensrf_config = $parser->parse_file($config_file);
129 # If the user passed in settings at the command line,
130 # we don't want to override them
131 $settings->{host} = $settings->{host} || $opensrf_config->findnodes($host);
132 $settings->{port} = $settings->{port} || $opensrf_config->findnodes($port);
133 $settings->{db} = $settings->{db} || $opensrf_config->findnodes($dbname);
134 $settings->{user} = $settings->{user} || $opensrf_config->findnodes($user);
135 $settings->{pw} = $settings->{pw} || $opensrf_config->findnodes($pw);
138 =item create_database() - Creates the database using create_database_contribs.sql
140 sub create_database {
141 my $settings = shift;
143 $ENV{'PGUSER'} = $settings->{user};
144 $ENV{'PGPASSWORD'} = $settings->{pw};
145 $ENV{'PGPORT'} = $settings->{port};
146 $ENV{'PGHOST'} = $settings->{host};
147 my @temp = `psql -d postgres -qtc 'show server_version;' | xargs | cut -c1,3`;
149 my $pgversion = $temp[0];
151 # If it looks like it is 9.1 or greater, use create_database_extensions.sql
152 # Otherwise use create_database_contribs.sql
153 if($pgversion >= '91') {
154 $cmd = 'psql -vdb_name=' . $settings->{db} . ' -d postgres -f ' . $create_db_sql_extensions;
156 $cmd = 'psql -vdb_name=' . $settings->{db} . ' -vcontrib_dir=' . $pg_contribdir .
157 ' -d postgres -f ' . $create_db_sql_contribs;
159 my @output = `$cmd 2>&1`;
160 if(grep(/(ERROR|No such file or directory)/,@output)) {
161 push(@output, "\n------------------------------------------------------------------------------\n",
162 "There was a problem creating the database.\n",
163 "See above for more information.\n");
164 if(grep/unsupported language/, @output) {
165 push(@output, "\nYou may need to install the postgresql plperl package on the database server.\n");
167 if(grep/No such file or directory/, @output) {
168 if($pgversion >= '91') {
169 push(@output, "\nYou may need to install the postgresql contrib package on the database server.\n");
171 push(@output, "\nYou may need to install the postgresql contrib package on this server.\n");
174 push(@output, "------------------------------------------------------------------------------\n");
179 =item create_schema() - Creates the database schema by calling build-db.sh
182 my $settings = shift;
184 chdir(dirname($build_db_sh));
185 my $cmd = File::Spec->catfile('.', basename($build_db_sh)) . " " .
186 $settings->{host} ." ". $settings->{port} ." ".
187 $settings->{db} ." ". $settings->{user} ." ".
193 =item load_sample_data() - Loads sample bib records, copies, users, and transactions
195 sub load_sample_data {
196 my $settings = shift;
198 my $load_script = $_sample_all;
200 if ($load_concerto) {
201 $load_script = $_sample_concerto;
203 $ENV{'PGUSER'} = $settings->{user};
204 $ENV{'PGPASSWORD'} = $settings->{pw};
205 $ENV{'PGPORT'} = $settings->{port};
206 $ENV{'PGHOST'} = $settings->{host};
207 $ENV{'PGDATABASE'} = $settings->{db};
208 my @output = `psql -f $load_script 2>&1`;
213 =item set_admin_account() - Sets the administrative user's user name and password
215 sub set_admin_account {
216 my $admin_user = shift;
217 my $admin_pw = shift;
218 my $settings = shift;
220 my $dbh = DBI->connect('dbi:Pg:dbname=' . $settings->{db} .
221 ';host=' . $settings->{host} . ';port=' . $settings->{port} . ';',
222 $settings->{user} . "", $settings->{pw} . "", {AutoCommit => 1}
225 print STDERR "Could not connect to database to set admin account. ";
226 print STDERR "Error was " . $dbh->errstr . "\n";
229 my $stmt = $dbh->prepare("UPDATE actor.usr SET usrname = ?, passwd = ? WHERE id = 1");
230 $stmt->execute(($admin_user, $admin_pw));
232 print STDERR "Failed to set admin account. ";
233 print STDERR "Error was " . $dbh->errstr . "\n";
245 GetOptions("create-schema" => \$cschema,
246 "create-database" => \$cdatabase,
247 "load-all-sample" => \$load_all,
248 "load-concerto-sample" => \$load_concerto,
249 "create-offline" => \$offline,
250 "update-config" => \$uconfig,
251 "config-file=s" => \$config_file,
252 "build-db-file=s" => \$build_db_sh,
253 "pg-contrib-dir=s" => \$pg_contribdir,
254 "create-db-sql-contribs=s" => \$create_db_sql_contribs,
255 "create-db-sql-extensions=s" => \$create_db_sql_extensions,
256 "pg-config=s" => \$pgconfig,
257 "admin-user=s" => \$admin_user,
258 "admin-password=s" => \$admin_pw,
259 "service=s" => \@services,
260 "user=s" => \$settings{'user'},
261 "password=s" => \$settings{'pw'},
262 "database=s" => \$settings{'db'},
263 "hostname=s" => \$settings{'host'},
264 "port=i" => \$settings{'port'},
268 if (grep(/^all$/, @services)) {
269 @services = qw/reporter open-ils.cstore open-ils.pcrud open-ils.storage open-ils.reporter-store state_store/;
272 my $eg_config = File::Spec->catfile($script_dir, '../extras/eg_config');
275 my @temp = `$eg_config --sysconfdir`;
277 $sysconfdir = $temp[0];
278 $config_file = File::Spec->catfile($sysconfdir, "opensrf.xml");
282 my @temp = `$eg_config --prefix`;
288 $build_db_sh = File::Spec->catfile($script_dir, '../sql/Pg/build-db.sh');
291 if (!$pg_contribdir) {
292 $pgconfig = 'pg_config' if(!$pgconfig);
293 my @temp = `$pgconfig --sharedir`;
295 $pg_contribdir = File::Spec->catdir($temp[0], 'contrib');
298 if (!$create_db_sql_contribs) {
299 $create_db_sql_contribs = File::Spec->catfile($script_dir, '../sql/Pg/create_database_contribs.sql');
302 if (!$create_db_sql_extensions) {
303 $create_db_sql_extensions = File::Spec->catfile($script_dir, '../sql/Pg/create_database_extensions.sql');
306 if (!$offline_file) {
307 $offline_file = File::Spec->catfile($sysconfdir, 'offline-config.pl');
310 unless (-e $build_db_sh) { die "Error: $build_db_sh does not exist. \n"; }
311 unless (-e $config_file) { die "Error: $config_file does not exist. \n"; }
313 if ($uconfig) { update_config(\@services, \%settings); }
315 # Get our settings from the config file
316 get_settings(\%settings);
318 if ($cdatabase) { create_database(\%settings); }
319 if ($cschema) { create_schema(\%settings); }
320 if ($admin_user && $admin_pw) {
321 set_admin_account($admin_user, $admin_pw, \%settings);
323 if ($load_all || $load_concerto) {
324 load_sample_data(\%settings);
326 if ($offline) { create_offline_config($offline_file, \%settings); }
328 if ((!$cdatabase && !$cschema && !$load_all && !$load_concerto && !$uconfig && !$offline && !$admin_pw) || $help) {
332 eg_db_config.pl [OPTION] ... [COMMAND] ... [CONFIG OPTIONS]
335 Creates or recreates the Evergreen database schema based on the settings
336 in the opensrf.xml configuration file.
338 Manipulates the configuration file
342 specifies the opensrf.xml file. Defaults to @sysconfdir@/opensrf.xml
345 specifies the script that creates the database schema. Defaults to
346 Open-ILS/src/sql/pg/build-db.sh
349 specifies the offline database settings file required by the offline
350 data uploader. Defaults to @sysconfdir@/offline-config.pl
354 Configures Evergreen database settings in the file specified by
358 Creates the database setting file required by the offline data uploader
361 Creates the Evergreen database schema according to the settings in
362 the file specified by --config-file.
365 Creates the database itself, provided the user and password options
366 represent a superuser.
369 Loads all sample data, including bibliographic records, call numbers,
370 copies, users, and transactions.
372 --load-concerto-sample
373 Loads a subset of sample data that includes just 100 bibliographic
374 records, and associated call numbers and copies.
378 Specify "all" or one or more of the following services to update:
383 * open-ils.reporter-store
386 DATABASE CONFIGURATION OPTIONS
387 --user username for the database
389 --password password for the user
391 --database name of the database
393 --hostname name or address of the database host
395 --port port number for database access
397 --admin-user administration user's user name
399 --admin-pass administration user's password
402 This script is normally used during the initial installation and
403 configuration process. This creates the database schema, sets
404 the administration user's user name and password, and modifies your
405 configuration files to include the correct database connection
408 For a single server install, or an install with one web/application
409 server and one database server, you will typically want to invoke this
410 script with a complete set of commands:
412 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
413 --service all --create-schema --create-offline \
414 --user <db-user> --password <db-pass> --hostname localhost --port 5432 \
415 --database evergreen --admin-user <admin-user> --admin-pass <admin-pass>
417 To update the configuration for a single service - for example, if you
418 replicated a database for reporting purposes - just issue the
419 --update-config command with the service identified and the changed
420 database parameters specified:
422 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
423 --service reporter --hostname foobar --password newpass