# mysql-lib.pl
# Common MySQL functions

do '../web-lib.pl';
&init_config();
require '../ui-lib.pl';
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";
	}
if ($module_info{'usermin'}) {
	# Usermin always runs this module as the logged-in Unix user.
	# %access is faked up to prevent attempts to create and delete DBs
	&switch_to_remote_user();
	&create_user_config_dirs();
	%access = ( 'create', 0,
	            'delete', 0,
	            'bpath', '/',
	            'buser', 'root',
	            'edonly', 0 );
	$mysql_login = $userconfig{'login'};
	$mysql_pass = $userconfig{'pass'};
	if (open(VERSION, "$user_module_config_directory/version")) {
		chop($mysql_version = <VERSION>);
		close(VERSION);
		}
	}
else {
	# Webmin connects to the database as the user specified in the per-user
	# or global config
	%access = &get_module_acl();
	if ($access{'user'} && !$use_global_login) {
		$mysql_login = $access{'user'};
		$mysql_pass = $access{'pass'};
		}
	else {
		$mysql_login = $config{'login'};
		$mysql_pass = $config{'pass'};
		}
	if (open(VERSION, "$module_config_directory/version")) {
		chop($mysql_version = <VERSION>);
		close(VERSION);
		}
	$cron_cmd = "$module_config_directory/backup.pl";
	}
$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');

@priv_cols = ('Host', 'User', 'Password', 'Select_priv', 'Insert_priv', 'Update_priv', 'Delete_priv', 'Create_priv', 'Drop_priv', 'Reload_priv', 'Shutdown_priv', 'Process_priv', 'File_priv', 'Grant_priv', 'References_priv', 'Index_priv', 'Alter_priv', 'Show_db_priv', 'Super_priv', 'Create_tmp_table_priv', 'Lock_tables_priv', 'Execute_priv', 'Repl_slave_priv', 'Repl_client_priv');

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


$old_user_priv_cols = $mysql_version >= 4 ? 21 : 14;
$old_db_priv_cols = $mysql_version >= 4 ? 12 : 10;

sub make_authstr
{
return ($config{'sock'} ? " -S $config{'sock'}" : "").
       ($config{'host'} ? " -h $config{'host'}" : "").
       ($config{'port'} ? " -P $config{'port'}" : "").
       ($mysql_login ? " -u ".quotemeta($mysql_login) : "").
       ($mysql_pass ? " -p".quotemeta($mysql_pass) : "");
}

# 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, [empty-if-denied])
# Returns a list of tables in some database
sub list_tables
{
if ($_[0] =~ /_/) {
	open(DBS, "$config{'mysqlshow'} $authstr ".quotemeta($_[0])." % 2>&1 |");
	}
else {
	open(DBS, "$config{'mysqlshow'} $authstr ".quotemeta($_[0])." 2>&1 |");
	}
local $t = &parse_mysql_table(DBS);
close(DBS);
if ($t =~ /access denied/i) {
	if ($_[1]) {
		return ( );
		}
	else {
		&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);
local (%tp, $i);
for($i=0; $i<@{$s->{'titles'}}; $i++) {
	$tp{lc($s->{'titles'}->[$i])} = $i;
	}
foreach $r (@{$s->{'data'}}) {
	push(@rv, { 'field' => $r->[$tp{'field'}],
		    'type' => $r->[$tp{'type'}],
		    'null' => $r->[$tp{'null'}],
		    'key' => $r->[$tp{'key'}],
		    'default' => $r->[$tp{'default'}],
		    'extra' => $r->[$tp{'extra'}] });
	}
return @rv;
}

# execute_sql(database, command, [param, ...])
# Executes some SQL and returns the results
sub execute_sql
{
local $sql = $_[1];
$sql =~ s/\\/\\\\/g;
@params = @_[2..$#_];
if ($driver_handle) {
	# Use the DBI interface
	local $cstr = "database=$_[0]";
	$cstr .= ";host=$config{'host'}" if ($config{'host'});
	$cstr .= ";port=$config{'port'}" if ($config{'port'});
	$cstr .= ";mysql_socket=$config{'sock'}" if ($config{'sock'});
	local $dbh = $driver_handle->connect($cstr, $mysql_login, $mysql_pass,
					     { });
	$dbh || &error("DBI connect failed : ",$driver_handle->errstr);
	local $cmd = $dbh->prepare($sql);
	if (!$cmd->execute(@params)) {
		&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();
	if (@params) {
		# Sub in ? parameters
		local $p;
		local $pos = -1;
		foreach $p (@params) {
			$pos = index($sql, '?', $pos+1);
			&error("Incorrect number of parameters") if ($pos < 0);
			local $qp = $p;
			$qp =~ s/'/''/g;
			$qp = $qp eq '' ? 'NULL' : "'$qp'";
			$sql = substr($sql, 0, $pos).$qp.substr($sql, $pos+1);
			$pos += length($qp)-1;
			}
		}
	open(TEMP, ">$temp");
	print TEMP $sql,"\n";
	close(TEMP);
	open(DBS, "$config{'mysql'} $authstr -E -t ".quotemeta($_[0])." <$temp 2>&1 |");
	local $t = &parse_mysql_vertical(DBS);
	close(DBS);
	unlink($temp);
	if (!ref($t)) {
		$t =~ s/^ERROR[^:]*://;
		&error($sql);
		&error(&text('esql', "<tt>".&html_escape($_[1])."</tt>",
			    "<tt>".&html_escape($t)."</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
{
if ($module_info{'usermin'}) {
	foreach $l (split(/\t/, $config{'access'})) {
		if ($l =~ /^(\S+):\s*(.*)$/ &&
		    ($1 eq $remote_user || $1 eq '*')) {
			local @dbs = split(/\s+/, $2);
			local $d;
			foreach $d (@dbs) {
				$d =~ s/\$REMOTE_USER/$remote_user/g;
				return 1 if ($d eq '*' || $_[0] =~ /^$d$/);
				}
			return 0;
			}
		}
	return 0;
	}
else {
	local $d;
	return 1 if ($access{'dbs'} eq '*');
	foreach $d (split(/\s+/, $access{'dbs'})) {
		return 1 if ($d && $d eq $_[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 $d;
if ($access{'perms'} == 2 && $access{'dbs'} ne '*') {
	# Can only select his own databases
	$rv .= "<select name=dbs>\n";
	local $found = !$_[0];
	foreach $d (@dbs) {
		if (&can_edit_db($d)) {
			$rv .= sprintf "<option %s>%s\n", $_[0] eq $d ? 'selected' : '', $d;
			$found++ if ($_[0] eq $d);
			}
		}
	$rv .= "<option selected>$_[0]\n" if (!$found);
	$rv .= "</select>\n";
	}
else {
	# Can select any 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];
	}
}

# supports_quoting()
# Returns 1 if running mysql version 3.23.6 or later
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;
	}
}

# user_priv_cols()
# Returns the number of columns used for privileges in the user table
sub user_priv_cols
{
if (!$user_priv_cols) {
	local @str = &table_structure("mysql", "user");
	local $s;
	foreach $s (@str) {
		$user_priv_cols++ if ($s->{'field'} =~ /_priv/i);
		}
	}
return $user_priv_cols;
}

# db_priv_cols()
# Returns the number of columns used for privileges in the db table
sub db_priv_cols
{
if (!$db_priv_cols) {
	local @str = &table_structure("mysql", "db");
	local $s;
	foreach $s (@str) {
		$db_priv_cols++ if ($s->{'field'} =~ /_priv/i);
		}
	}
return $db_priv_cols;
}

sub is_blob
{
return $_[0]->{'type'} =~ /(text|blob)$/i;
}

# get_mysql_version(&out)
# Returns a version number, undef if one cannot be found, or -1 for a .so
# problem
sub get_mysql_version
{
local $out = `$config{'mysql'} -V 2>&1`;
${$_[0]} = $out;
if ($out =~ /lib\S+\.so/) {
	return -1;
	}
elsif ($out =~ /distrib\s+((3|4|5)\.[0-9\.]*)/i) {
	return $1;
	}
else {
	return undef;
	}
}

# date_subs(filename)
# Does strftime-style date substitutions on a filename, if enabled
sub date_subs
{
if ($config{'date_subs'}) {
        eval "use POSIX";
	eval "use posix" if ($@);
        local @tm = localtime(time());
        return strftime($_[0], @tm);
        }
else {
        return $_[0];
        }
}

# execute_before(db, handle, escape, path)
sub execute_before
{
local $cmd = $config{'backup_before_'.$_[0]};
if ($cmd) {
	$ENV{'BACKUP_FILE'} = $_[3];
	local $h = $_[1];
	local $out = `($cmd) 2>&1 </dev/null`;
	if ($h && $out) {
		print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out;
		}
	}
}

# execute_after(db, handle, escape, path)
sub execute_after
{
local $cmd = $config{'backup_after_'.$_[0]};
if ($cmd) {
	$ENV{'BACKUP_FILE'} = $_[3];
	local $h = $_[1];
	local $out = `($cmd) 2>&1 </dev/null`;
	if ($h && $out) {
		print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out;
		}
	}
}

# show_table_form(count)
sub show_table_form
{
print "<table border>\n";
print "<tr $tb> <td><b>$text{'field_name'}</b></td> ",
      "<td><b>$text{'field_type'}</b></td> ",
      "<td><b>$text{'field_size'}</b></td> ",
      "<td><b>$text{'table_nkey'}</b></td> ",
      "<td><b>$text{'field_auto'}</b></td> ",
      "<td><b>$text{'field_null'}</b></td> ",
      "<td><b>$text{'field_default'}</b></td> </tr>\n";
local $i;
for($i=0; $i<$_[0]; $i++) {
	print "<tr $cb>\n";
	print "<td><input name=field_$i size=20></td>\n";
	print "<td><select name=type_$i>\n";
	print "<option selected>\n";
	local $t;
	foreach $t (@type_list) {
		print "<option>$t\n";
		}
	print "</select></td>\n";
	print "<td><input name=size_$i size=10></td>\n";
	print "<td><input type=checkbox name=key_$i value=1> ",
	      "$text{'yes'}</td>\n";
	print "<td><input type=checkbox name=auto_$i value=1> ",
	      "$text{'yes'}</td>\n";
	print "<td><input type=checkbox name=null_$i value=1 checked> ",
	      "$text{'yes'}</td>\n";
	print "<td><input name=default_$i size=15></td>\n";
	print "</tr>\n";
	}
print "</table>\n";
}

# parse_table_form(&extrafields, tablename)
sub parse_table_form
{
local @fields = @{$_[0]};
local $i;
local (@auto, @pri);
for($i=0; defined($in{"field_$i"}); $i++) {
	next if (!$in{"field_$i"});
	$in{"field_$i"} =~ /^\S+$/ ||
		&error(&text('table_efield', $in{"field_$i"}));
	$in{"type_$i"} || &error(&text('table_etype', $in{"field_$i"}));
	if ($in{"type_$i"} eq 'enum' || $in{"type_$i"} eq 'set') {
		local @ev = split(/\s+/, $in{"size_$i"});
		@ev || &error(&text('table_eenum', $in{"type_$i"},
						   $in{"field_$i"}));
		$in{"size_$i"} = join(",", map { "'$_'" } @ev);
		}
	if ($in{"size_$i"}) {
		push(@fields, sprintf "%s %s(%s)",
		     &quotestr($in{"field_$i"}), $in{"type_$i"},$in{"size_$i"});
		}
	else {
		push(@fields, sprintf "%s %s",
			&quotestr($in{"field_$i"}), $in{"type_$i"});
		}
	if (!$in{"null_$i"}) {
		$fields[@fields-1] .= " not null";
		}
	if ($in{"key_$i"}) {
		$in{"null_$i"} && &error(&text('table_epnull',$in{"field_$i"}));
		push(@pri, $in{"field_$i"});
		}
	if ($in{"auto_$i"}) {
		$in{"key_$i"} || &error($text{'table_eautokey'});
		push(@auto, $fields[@fields-1]);
		}
	if ($in{"default_$i"}) {
		$fields[@fields-1] .= " default '".$in{"default_$i"}."'";
		}
	}
@auto < 2 || &error($text{'table_eauto'});
@fields || &error($text{'table_enone'});
local @sql;
local $sql = "create table ".&quotestr($_[1])." (".join(",", @fields).")";
$sql .= " type = $in{'type'}" if ($in{'type'});
push(@sql, $sql);
if (@pri) {
	# Setup primary fields too
	push(@sql, "alter table ".&quotestr($_[1])." add primary key (".
		    join(",", map { &quotestr($_) } @pri).")");
	}
if (@auto) {
	# Make field auto-increment
	push(@sql, "alter table ".&quotestr($_[1]).
		   " modify $auto[0] auto_increment");
	}
return @sql;
}

1;

