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;
28 my ($dbhost, $dbport, $dbname, $dbuser, $dbpw, $help, $admin_user, $admin_pw);
31 my $offline_file = '';
34 my $pg_contribdir = '';
35 my $create_db_sql = '';
36 my $create_db_sql_9_1 = '';
39 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
41 # Get the directory for this script
42 my $script_dir = dirname($0);
46 =item update_config() - Puts command line specified settings into xml file
49 my ($services, $settings) = @_;
51 my $parser = XML::LibXML->new();
52 my $opensrf_config = $parser->parse_file($config_file);
55 foreach my $service (@$services) {
56 foreach my $key (keys %$settings) {
57 next unless $settings->{$key};
60 if ($service eq 'state_store') {
61 (@node) = $opensrf_config->findnodes("//state_store/$key/text()");
63 (@node) = $opensrf_config->findnodes("//$service//database/$key/text()");
67 $_->setData($settings->{$key});
74 my $timestamp = sprintf("%d.%d.%d.%d.%d.%d",
75 $year + 1900, $mon +1, $mday, $hour, $min, $sec);
76 if (copy($config_file, "$config_file.$timestamp")) {
77 print "Backed up original configuration file to '$config_file.$timestamp'\n";
79 print STDERR "Unable to write to '$config_file.$timestamp'; bailed out.\n";
82 $opensrf_config->toFile($config_file) or
83 die "ERROR: Failed to update the configuration file '$config_file'\n";
86 =item create_offline_config() - Write out the offline config
88 sub create_offline_config {
89 my ($setup, $settings) = @_;
91 open(FH, '>', $setup) or die "Could not write offline database setup to $setup\n";
93 print "Writing offline database configuration to $setup\n";
95 printf FH "\$main::config{base_dir} = '%s/var/data/offline/';\n", $prefix;
96 printf FH "\$main::config{bootstrap} = '%s/opensrf_core.xml';\n", $sysconfdir;
98 printf FH "\$main::config{dsn} = 'dbi:Pg:host=%s;dbname=%s;port=%d';\n",
99 $settings->{host}, $settings->{db}, $settings->{port};
101 printf FH "\$main::config{usr} = '%s';\n", $settings->{user};
102 printf FH "\$main::config{pw} = '%s';\n", $settings->{pw};
107 =item get_settings() - Extracts database settings from opensrf.xml
110 my $settings = shift;
112 my $host = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/host/text()";
113 my $port = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/port/text()";
114 my $dbname = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/db/text()";
115 my $user = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/user/text()";
116 my $pw = "/opensrf/default/apps/open-ils.storage/app_settings/databases/database/pw/text()";
118 my $parser = XML::LibXML->new();
119 my $opensrf_config = $parser->parse_file($config_file);
121 # If the user passed in settings at the command line,
122 # we don't want to override them
123 $settings->{host} = $settings->{host} || $opensrf_config->findnodes($host);
124 $settings->{port} = $settings->{port} || $opensrf_config->findnodes($port);
125 $settings->{db} = $settings->{db} || $opensrf_config->findnodes($dbname);
126 $settings->{user} = $settings->{user} || $opensrf_config->findnodes($user);
127 $settings->{pw} = $settings->{pw} || $opensrf_config->findnodes($pw);
130 =item create_database() - Creates the database using create_database.sql
132 sub create_database {
133 my $settings = shift;
135 $ENV{'PGUSER'} = $settings->{user};
136 $ENV{'PGPASSWORD'} = $settings->{pw};
137 $ENV{'PGPORT'} = $settings->{port};
138 $ENV{'PGHOST'} = $settings->{host};
139 my @temp = `psql -d postgres -qtc 'show server_version;' | xargs | cut -c1,3`;
141 my $pgversion = $temp[0];
143 # If it looks like it is 9.1 or greater, use create_database_9_1.sql
144 # Otherwise use create_database.sql
145 if($pgversion >= '91') {
146 $cmd = 'psql -vdb_name=' . $settings->{db} . ' -d postgres -f ' . $create_db_sql_9_1;
148 $cmd = 'psql -vdb_name=' . $settings->{db} . ' -vcontrib_dir=' . $pg_contribdir .
149 ' -d postgres -f ' . $create_db_sql;
151 my @output = `$cmd 2>&1`;
152 if(grep(/(ERROR|No such file or directory)/,@output)) {
153 push(@output, "\n------------------------------------------------------------------------------\n",
154 "There was a problem creating the database.\n",
155 "See above for more information.\n");
156 if(grep/unsupported language/, @output) {
157 push(@output, "\nYou may need to install the postgresql plperl package on the database server.\n");
159 if(grep/No such file or directory/, @output) {
160 if($pgversion >= '91') {
161 push(@output, "\nYou may need to install the postgresql contrib package on the database server.\n");
163 push(@output, "\nYou may need to install the postgresql contrib package on this server.\n");
166 push(@output, "------------------------------------------------------------------------------\n");
171 =item create_schema() - Creates the database schema by calling build-db.sh
174 my $settings = shift;
176 chdir(dirname($build_db_sh));
177 my $cmd = File::Spec->catfile('.', basename($build_db_sh)) . " " .
178 $settings->{host} ." ". $settings->{port} ." ".
179 $settings->{db} ." ". $settings->{user} ." ".
185 =item set_admin_account() - Sets the administrative user's user name and password
187 sub set_admin_account {
188 my $admin_user = shift;
189 my $admin_pw = shift;
190 my $settings = shift;
192 my $dbh = DBI->connect('dbi:Pg:dbname=' . $settings->{db} .
193 ';host=' . $settings->{host} . ';port=' . $settings->{port} . ';',
194 $settings->{user} . "", $settings->{pw} . "", {AutoCommit => 1}
197 print STDERR "Could not connect to database to set admin account. ";
198 print STDERR "Error was " . $dbh->errstr . "\n";
201 my $stmt = $dbh->prepare("UPDATE actor.usr SET usrname = ?, passwd = ? WHERE id = 1");
202 $stmt->execute(($admin_user, $admin_pw));
204 print STDERR "Failed to set admin account. ";
205 print STDERR "Error was " . $dbh->errstr . "\n";
217 GetOptions("create-schema" => \$cschema,
218 "create-database" => \$cdatabase,
219 "create-offline" => \$offline,
220 "update-config" => \$uconfig,
221 "config-file=s" => \$config_file,
222 "build-db-file=s" => \$build_db_sh,
223 "pg-contrib-dir=s" => \$pg_contribdir,
224 "create-db-sql=s" => \$create_db_sql,
225 "create-db-sql-9-1=s" => \$create_db_sql_9_1,
226 "pg-config=s" => \$pgconfig,
227 "admin-user=s" => \$admin_user,
228 "admin-password=s" => \$admin_pw,
229 "service=s" => \@services,
230 "user=s" => \$settings{'user'},
231 "password=s" => \$settings{'pw'},
232 "database=s" => \$settings{'db'},
233 "hostname=s" => \$settings{'host'},
234 "port=i" => \$settings{'port'},
238 if (grep(/^all$/, @services)) {
239 @services = qw/reporter open-ils.cstore open-ils.pcrud open-ils.storage open-ils.reporter-store state_store/;
242 my $eg_config = File::Spec->catfile($script_dir, '../extras/eg_config');
245 my @temp = `$eg_config --sysconfdir`;
247 $sysconfdir = $temp[0];
248 $config_file = File::Spec->catfile($sysconfdir, "opensrf.xml");
252 my @temp = `$eg_config --prefix`;
258 $build_db_sh = File::Spec->catfile($script_dir, '../sql/Pg/build-db.sh');
261 if (!$pg_contribdir) {
262 $pgconfig = 'pg_config' if(!$pgconfig);
263 my @temp = `$pgconfig --sharedir`;
265 $pg_contribdir = File::Spec->catdir($temp[0], 'contrib');
268 if (!$create_db_sql) {
269 $create_db_sql = File::Spec->catfile($script_dir, '../sql/Pg/create_database.sql');
272 if (!$create_db_sql_9_1) {
273 $create_db_sql_9_1 = File::Spec->catfile($script_dir, '../sql/Pg/create_database_9_1.sql');
276 if (!$offline_file) {
277 $offline_file = File::Spec->catfile($sysconfdir, 'offline-config.pl');
280 unless (-e $build_db_sh) { die "Error: $build_db_sh does not exist. \n"; }
281 unless (-e $config_file) { die "Error: $config_file does not exist. \n"; }
283 if ($uconfig) { update_config(\@services, \%settings); }
285 # Get our settings from the config file
286 get_settings(\%settings);
288 if ($cdatabase) { create_database(\%settings); }
289 if ($cschema) { create_schema(\%settings); }
290 if ($admin_user && $admin_pw) {
291 set_admin_account($admin_user, $admin_pw, \%settings);
293 if ($offline) { create_offline_config($offline_file, \%settings); }
295 if ((!$cdatabase && !$cschema && !$uconfig && !$offline && !$admin_pw) || $help) {
299 eg_db_config.pl [OPTION] ... [COMMAND] ... [CONFIG OPTIONS]
302 Creates or recreates the Evergreen database schema based on the settings
303 in the opensrf.xml configuration file.
305 Manipulates the configuration file
309 specifies the opensrf.xml file. Defaults to /openils/conf/opensrf.xml
312 specifies the script that creates the database schema. Defaults to
313 Open-ILS/src/sql/pg/build-db.sh
316 specifies the offline database settings file required by the offline
317 data uploader. Defaults to /openils/conf/offline-config.pl
321 Configures Evergreen database settings in the file specified by
325 Creates the database setting file required by the offline data uploader
328 Creates the Evergreen database schema according to the settings in
329 the file specified by --config-file.
332 Creates the database itself, provided the user and password options
333 represent a superuser.
337 Specify "all" or one or more of the following services to update:
342 * open-ils.reporter-store
345 DATABASE CONFIGURATION OPTIONS
346 --user username for the database
348 --password password for the user
350 --database name of the database
352 --hostname name or address of the database host
354 --port port number for database access
356 --admin-user administration user's user name
358 --admin-pass administration user's password
361 This script is normally used during the initial installation and
362 configuration process. This creates the database schema, sets
363 the administration user's user name and password, and modifies your
364 configuration files to include the correct database connection
367 For a single server install, or an install with one web/application
368 server and one database server, you will typically want to invoke this
369 script with a complete set of commands:
371 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
372 --service all --create-schema --create-offline \
373 --user <db-user> --password <db-pass> --hostname localhost --port 5432 \
374 --database evergreen --admin-user <admin-user> --admin-pass <admin-pass>
376 To update the configuration for a single service - for example, if you
377 replicated a database for reporting purposes - just issue the
378 --update-config command with the service identified and the changed
379 database parameters specified:
381 perl Open-ILS/src/support-scripts/eg_db_config.pl --update-config \
382 --service reporter --hostname foobar --password newpass