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