1#!/usr/local/bin/perl
2# ATSlog version 2.1.1 build 664 www.atslog.com
3# Copyright (C) 2003 Denis CyxoB www.yamiyam.dp.ua
4
5BEGIN {
6print "\nATSlog SQL database installer/updater\n\n";
7}
8use DBI;
9use File::Copy; # copy/move functions
10
11$config=$ARGV[0];
12if ( ! -f $config) {
13    die ("USAGE: install-sql <atslog_config>\n\nCant open \"$config\" file\n");
14}
15
16my $dbtype =input('Database type: (mysql or postgresql)',    'mysql');
17
18my @drivers = DBI->available_drivers;
19die "No drivers found!\n" unless @drivers; # should never happen
20
21if ($dbtype !~ /^(mysql|postgresql)$/){
22	die("Wrong database type '$dbtype'\n");
23}
24
25if ($dbtype eq "postgresql"){
26	$sqltype="Pg";
27	$dbname="template1";
28}
29else{ # mysql
30	$sqltype="mysql";
31	$dbname="mysql";
32}
33
34if (!grep { /^(${sqltype})$/ } @drivers) {
35	die("Please install DBI:$sqltype driver\n");
36}
37
38my $root =input('Database manager',    'root');
39my $rpsw =input('Manager\'s password', '');
40my $dbhost =input('Database host',    'localhost');
41my $atslogdb =input('Database name',       'atslog');
42my $atslogdu =input('Database user',       'atslog');
43my $atslogdp =input('User\'s password',    randomPassword(8));
44
45my $dsn="DBI:$sqltype:database=$dbname;";
46if($dbhost ne "localhost") { $dsn .= "host=$dbhost;";}
47
48print "Connecting to '$dsn' as '$root'...\n";
49my $db=DBI->connect($dsn,$root,$rpsw,{PrintError => 0})
50        ||die("Could not connect to $sqltype as '$root'. ".$DBI::errstr);
51
52print "Creating database...";
53$db->do("CREATE DATABASE ${atslogdb};");
54if($db->err){
55	if($db->err==7 || $db->err==1007){ # mysql and Pg
56		print "FAILED\n";
57		print("WARNING: Database \"${atslogdb}\" already exists.\nInstaller will drop it and create a new one.\n");
58		if(input('Continue (yes|no)?','no')!~/^yes$/i) {
59			die("Please backup your existing database and try again.\n");
60		}
61		print "Drop database ${atslogdb}...";
62		$db->do("DROP DATABASE  ${atslogdb};"); print $db->err ? $db->errstr : '';
63		print "OK\n";
64		$db->do("CREATE DATABASE ${atslogdb};");
65	}
66	elsif($db->err) {
67		die($db->err.":".$db->errstr."\n");
68	}
69}
70else {	print "OK\n";}
71print "Creating user...";
72if ($sqltype eq "mysql") {
73	$db->do("delete from mysql.user where user=\'${atslogdu}\';"); print $db->err ? $db->errstr : '';
74	$db->do("GRANT USAGE ON *.* TO \'${atslogdu}\'@\'${dbhost}' IDENTIFIED BY \'${atslogdp}\' WITH GRANT OPTION;"); print $db->err ? $db->errstr : '';
75	$db->do("GRANT ALL PRIVILEGES ON ${atslogdb}.* TO \'${atslogdu}\'@\'localhost\'"); print $db->err ? $db->errstr : '';
76	$db->do("FLUSH PRIVILEGES;"); print $db->err ? $db->errstr : '';
77}
78elsif($sqltype eq "Pg"){
79	$db->do("SET client_min_messages = 'ERROR';"); print $db->err ? $db->errstr : '';
80	$db->do("DROP USER ${atslogdu}");
81	$db->do("CREATE USER ${atslogdu} PASSWORD '${atslogdp}' CREATEDB CREATEUSER;"); print $db->err ? $db->errstr : '';
82	$db->do("SET SESSION AUTHORIZATION '${atslogdu}';"); print $db->err ? $db->errstr : '';
83	$db->do("GRANT ALL ON DATABASE ${atslogdb} TO ${atslogdu};"); print $db->err ? $db->errstr : '';
84}
85print "OK\n";
86
87if ($sqltype eq "mysql") {
88	$db->do("USE ${atslogdb};"); print $db->err ? $db->errstr : '';
89}
90elsif ($sqltype eq "Pg") {
91	$db->disconnect;
92	$dsn="DBI:$sqltype:database=${atslogdb};";
93	if($dbhost ne "localhost") { $dsn .= "host=$dbhost;";}
94	print "Connecting to '$dsn' as '$root'...\n";
95	$db=DBI->connect($dsn,$root,$rpsw,{PrintError => 0})
96        ||die("Could not connect to $sqltype as '$root'. ".$DBI::errstr);
97}
98
99my $row;
100my $cmd ='';
101my $cmt ='';
102print "Creating tables...";
103open(DATA,"/usr/local/share/atslog/sql/createsqltables.${sqltype}.sql") || die print("Can open SQL dump /usr/local/share/atslog/sql/createsqltables.${sqltype}.sql");
104readsql();
105close(DATA);
106print "OK\n";
107
108print "Inserting data...";
109open(DATA,"/usr/local/share/atslog/sql/data.sql") || die print("Cant open SQL dump");
110readsql();
111close(DATA);
112print "OK\n";
113
114print("Patching configuration file...\n");
115move($config,$config.".bak");
116open IN,  $config.".bak" or die $!;
117open OUT,  ">$config" or die $!;
118while ($row =<IN>) {
119    $row =~ s/^sqlhost=.*$/sqlhost=$dbhost/g;
120    $row =~ s/^sqldatabase=.*$/sqldatabase=$atslogdb/g;
121    $row =~ s/^sqlmasteruser=.*$/sqlmasteruser=$atslogdu/g;
122    $row =~ s/^sqlmaspasswd=.*$/sqlmaspasswd=$atslogdp/g;
123    $row =~ s/^sqltype=.*$/sqltype=$dbtype/g;
124    print OUT $row;
125}
126close IN;close OUT;
127
128print("Done :)\n");
129
130sub input {
131 my ($pr, $dv) =@_;
132 print $pr, @_ >1 ? ' [' .(defined($dv) ? $dv : 'null') .']' :'', ': ';
133 my $r =<STDIN>;
134 chomp($r);
135 $r eq '' ? $dv : $r
136}
137
138sub readsql {
139while ($row =<DATA>) {
140  chomp($row);
141  if ($cmd && ($row =~/^#/ || ($cmd !~/^\s*\{/ && $cmd =~/;\s*$/) )) {
142     my $v;
143     chomp($cmd);
144     #print $cmt ||$cmd, " -> ";
145     if   ($cmd =~/^\s*\{/) {$v =eval($cmd);   print $@ ? $@ : ''}
146     else {$v =$db->do($cmd); print $db->err ? $db->errstr : ''}
147     #print ': ', defined($v) ? $v : 'null', "\n\n";
148     $cmd ='';
149     $cmt ='';
150  }
151  next if $row =~/^\s*#*\s*$/;
152  if    ($row =~/^#/ && $cmd !~/^\s*\{/) {
153        $cmt =$row;
154  }
155  elsif ($row =~/^\s*#/ || $row eq '') {
156  }
157  else {
158        $cmd .=($cmd ? "\n" : '') .$row;
159  }
160}
161}
162
163
164sub randomPassword {
165 my $password;
166 my $_rand;
167
168 my $password_length = $_[0];
169 if (!$password_length) {
170  $password_length = 10;
171 }
172
173 my @chars = split(" ",
174 "a b c d e f g h i j k l m n o p q r s t u v w x y z
175  - _ % #
176  0 1 2 3 4 5 6 7 8 9");
177
178 srand;
179
180 for (my $i=0; $i <= $password_length ;$i++) {
181  $_rand = int(rand 41);
182  $password .= $chars[$_rand];
183 }
184 return $password;
185}
186