# mysql-lib.pl
# Common MySQL functions

do '../web-lib.pl';
&init_config();
%access = ( 'create', 0,
	    'delete', 0,
	    'bpath', '/',
	    'buser', 'root',
	    'edonly', 0 );
&switch_to_remote_user();
&create_user_config_dirs();
if ($config{'mysql_libs'}) {
	$ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}});
	$ENV{$gconfig{'ld_env'}} .= $config{'mysql_libs'};
	}
if ($config{'mysql'} =~ /^(\S+)\/bin\/mysql$/ && $1 ne '' && $1 ne '/usr') {
	$ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}});
	$ENV{$gconfig{'ld_env'}} .= "$1/lib";
	}
$authstr = &make_authstr();
$master_db = 'mysql';

@type_list = ('tinyint', 'smallint', 'mediumint', 'int', 'bigint',
	    'float', 'double', 'decimal', 'date', 'datetime', 'timestamp',
	    'time', 'year', 'char', 'varchar', 'tinyblob', 'tinytext',
	    'blob', 'text', 'mediumblob', 'mediumtext', 'longblob', 'longtext',
	    'enum', 'set');

if (!$config{'nodbi'}) {
	# Check if we have DBI::mysql
	eval <<EOF;
use DBI;
\$driver_handle = DBI->install_driver("mysql");
EOF
}

sub make_authstr
{
local ($l, $p);
($l = $userconfig{'login'}) =~ s/['\\]/\\$1/g;
($p = $userconfig{'pass'}) =~ s/(['\\])/\\$1/g;
return ($config{'sock'} ? " -S $config{'sock'}" : "").
       ($config{'host'} ? " -h $config{'host'}" : "").
       ($userconfig{'login'} ? " -u '$l'" : "").
       ($userconfig{'pass'} ? " '-p$p'" : "");
}

# is_mysql_running()
# Returns 1 if mysql is running, 0 if not, or -1 if running but
# inaccessible without a password
sub is_mysql_running
{
local $out = `$config{'mysqladmin'} $authstr status 2>&1`;
return $out =~ /uptime/i ? 1 :
       $out =~ /denied|password/i ? -1 : 0;
}

# list_databases()
# Returns a list of all databases
sub list_databases
{
open(DBS, "$config{'mysqlshow'} $authstr |");
local $t = &parse_mysql_table(DBS);
close(DBS);
return map { $_->[0] } @{$t->{'data'}};
}

# list_tables(database)
# Returns a list of tables in some database
sub list_tables
{
if ($_[0] =~ /_/) {
	open(DBS, "$config{'mysqlshow'} $authstr $_[0] % 2>&1 |");
	}
else {
	open(DBS, "$config{'mysqlshow'} $authstr $_[0] 2>&1 |");
	}
local $t = &parse_mysql_table(DBS);
close(DBS);
if ($t =~ /access denied/i) {
	&error($text{'edenied'});
	}
elsif (!ref($t)) {
	&error("<tt>$t</tt>");
	}
return map { $_->[0] } @{$t->{'data'}};
}

# table_structure(database, table)
# Returns a list of hashes detailing the structure of a table
sub table_structure
{
local $s = &execute_sql($_[0], "desc ".&quotestr($_[1]));
local (@rv, $r);
foreach $r (@{$s->{'data'}}) {
	push(@rv, { 'field' => $r->[0],
		    'type' => $r->[1],
		    'null' => $r->[2],
		    'key' => $r->[3],
		    'default' => $r->[4],
		    'extra' => $r->[5] });
	}
return @rv;
}

# execute_sql(database, command)
# Executes some SQL and returns the results
sub execute_sql
{
if ($driver_handle) {
	# Use the DBI interface
	local $cstr = "database=$_[0]";
	$cstr .= ";host=$config{'host'}" if ($config{'host'});
	$cstr .= ";mysql_socket=$config{'sock'}" if ($config{'sock'});
	local ($user, $pass);
	$user = $userconfig{'login'};
	$pass = $userconfig{'pass'};
	local $dbh = $driver_handle->connect($cstr, $user, $pass);
	$dbh || &error("DBI connect failed : ",$driver_handle->errstr);
	local $cmd = $dbh->prepare($_[1]);
	if (!$cmd->execute()) {
		&error(&text('esql', "<tt>".&html_escape($_[1])."</tt>",
			     "<tt>".&html_escape($dbh->errstr)."</tt>"));
		}
	local (@data, @row);
	local @titles = @{$cmd->{'NAME'}};
	while(@row = $cmd->fetchrow()) {
		push(@data, [ @row ]);
		}
	$cmd->finish();
	$dbh->disconnect();
	return { 'titles' => \@titles,
		 'data' => \@data };
	}
else {
	# Use the mysql command program
	local $temp = &tempname();
	open(TEMP, ">$temp");
	print TEMP $_[1],"\n";
	close(TEMP);
	open(DBS, "$config{'mysql'} $authstr -E -t $_[0] <$temp 2>&1 |");
	local $t = &parse_mysql_vertical(DBS);
	close(DBS);
	unlink($temp);
	if (!ref($t)) {
		$t =~ s/^ERROR[^:]*://;
		&error(&text('esql', "<tt>".&html_escape($_[1])."</tt>",
			    "<tt>".&html_escape($1)."</tt>"));
		}
	return $t;
	}
}

# execute_sql_logged(database, command)
sub execute_sql_logged
{
&additional_log('sql', $_[0], $_[1]);
return &execute_sql(@_);
}

# parse_mysql_table(handle)
# Given a filehandle, parses a text table in the format mysql uses
sub parse_mysql_table
{
local $fh = $_[0];
local ($line, $i, @edge);
do {
	# skip to table top
	$line = <$fh>;
	return $line if ($line =~ /^(ERROR|\S*mysqlshow:)/);
	} while($line && $line !~ /^\+/);
for($i=0; $i<length($line); $i++) {
	push(@edge, $i) if (substr($line, $i, 1) eq '+');
	}
$line = <$fh>;		# skip first row of -'s
local @titles = &parse_mysql_line($line, \@edge);
$line = <$fh>;		# skip next row of -'s
local @data;
while(1) {
	$line = <$fh>;
	last if (!$line || $line !~ /^\|/);
	while($line !~ /\|\s+$/) {
		# Line has a return in it!
		$line .= <$fh>;
		}
	push(@data, [ &parse_mysql_line($line, \@edge) ]);
	}
return { 'titles' => \@titles,
	 'data' => \@data };
}

# parse_mysql_line(line, &edges)
sub parse_mysql_line
{
local @rv;
for($i=0; $i<@{$_[1]}-1; $i++) {
	local $w = substr($_[0], $_[1]->[$i]+1,
			  $_[1]->[$i+1] - $_[1]->[$i] - 2);
	$w =~ s/^\s//;
	$w =~ s/\s+$//;
	$w =~ s/\\/\\\\/g;
	$w =~ s/\n/\\n/g;
	push(@rv, $w);
	}
return @rv;
}

# parse_mysql_vertical(handle)
# Parses mysql output in the -E format
sub parse_mysql_vertical
{
local (@data, @titles, $row = -1, $col, %hascol);
local $fh = $_[0];
local $line = <$fh>;
if (!$line) {
	# No output at all - must be a non-select
	return { };
	}
return $line if ($line =~ /^ERROR/);
local $errtxt = &text('eparse', "<tt>mysql</tt>", "<tt>DBI</tt>",
		      "<tt>DBD::mysql</tt>");
while($line) {
	$line =~ s/\r|\n//g;
	if ($line =~ /^\*\*\*/) {
		# A row header
		$row++;
		$col = -1;
		$data[$row] = [ ];
		}
	elsif ($line =~ /^\s*([^:\s]+): (.*)/ && ($row == 0 || $hascol{$1})) {
		# A new column
		$col++;
		$titles[$col] = $1;
		$row >= 0 || return $errtxt;
		$data[$row]->[$col] = $2;
		$hascol{$1}++;
		}
	else {
		# Continuing the last column
		$row >= 0 || return $errtxt;
		$data[$row]->[$col] .= "\n".$line;
		}
	$line = <$fh>;
	}
return { 'titles' => \@titles,
	 'data' => \@data };
}

sub can_edit_db
{
foreach $l (split(/\t/, $config{'access'})) {
	if ($l =~ /^(\S+):\s*(.*)$/ && ($1 eq $remote_user || $1 eq '*')) {
		local @dbs = split(/\s+/, $2);
		foreach $d (@dbs) {
			$d =~ s/\$REMOTE_USER/$remote_user/g;
			return 1 if ($d eq '*' || $_[0] =~ /^$d$/);
			}
		return 0;
		}
	}
return 0;
}

# split_table(&titles, &links, &col1, &col2, ...)
sub split_table
{
local $mid = int((@{$_[1]}+1) / 2);
local ($i, $j);
print "<table width=100%><tr>\n";
foreach $s ([0, $mid-1], [$mid, @{$_[1]}-1]) {
	print "<td width=50% valign=top><table border width=100%>\n";
	if ($s->[0] <= $s->[1]) {
		print "<tr $tb> ";
		foreach $t (@{$_[0]}) {
			print "<td><b>$t</b></td> ";
			}
		print "</tr>\n";
		}
	for($i=$s->[0]; $i<=$s->[1]; $i++) {
		print "<tr $cb>\n";
		print "<td><a href='$_[1]->[$i]'>$_[2]->[$i]</a></td>\n";
		for($j=3; $j<@_; $j++) {
			print "<td>$_[$j]->[$i]</td>\n";
			}
		print "</tr>\n";
		}
	print "</table></td>\n";
	}
print "</tr></table>\n";
}

# select_db(db)
sub select_db
{
local $rv;
local @dbs = &list_databases();
local $ind = &indexof($_[0],@dbs) >= 0;
$rv .= sprintf "<input type=radio name=db_def value=1 %s> %s\n",
	$_[0] eq '%' || $_[0] eq '' ? 'checked' : '', $text{'host_any'};
$rv .= sprintf "<input type=radio name=db_def value=2 %s> %s\n",
	$ind ? 'checked' : '', $text{'host_sel'};
$rv .= "<select name=dbs>\n";
foreach $d (@dbs) {
	$rv .= sprintf "<option %s>%s\n", $_[0] eq $d ? 'selected' : '', $d;
	}
$rv .= "</select>\n";
$rv .= sprintf "<input type=radio name=db_def value=0 %s>\n",
	$_[0] eq '%' || $_[0] eq '' || $ind ? '' : 'checked';
$rv .= sprintf "<input name=db size=10 value='%s'>\n",
	$_[0] eq '%' || $_[0] eq '' || $ind ? '' : $_[0];
return $rv;
}

# quotestr(string)
sub quotestr
{
if (&supports_quoting()) {
	return "`$_[0]`";
	}
else {
	return $_[0];
	}
}

sub supports_quoting
{
if ($mysql_version =~ /^(\d+)\.(\d+)\.(\d+)$/ &&
    ($1 > 3 || ($1 == 3 && $2 > 23) || ($1 == 3 && $2 == 23 && $3 >= 6))) {
	return 1;
	}
elsif ($mysql_version > 4) {
	return 1;
	}
else {
	return 0;
	}
}

1;

