package SQLDB;
use Common;
@ISA = qw(Common);

#公開したいサブルーチン
#@EXPORT = qw(DelSpace Reduce01 MakePath RegExpQuote);

use strict;
use Jcode;
use DBI;
#use CGI;

#use File::Path;
#use File::Basename;
#use File::Find;

use Symbol qw(gensym);

use Utils;
use Deps;
use CSV;

#===============================================
# 文字コード関係変数
#===============================================
# sjis, euc, jis, noconv, utf8
my $FileSystemCharCode = Deps::FileSystemCharCode();
my $PerlCharCode       = Deps::PerlCharCode();
my $MySQLCharCode      = Deps::MySQLCharCode();
my $WebCharCode        = Deps::WebCharCode();
my $WebCharSet         = Deps::WebCharSet();

#===============================================
# スクリプト大域変数
#===============================================
my $OS = Deps::OS();
my $LF = Deps::LF();
my $DirectorySeparator = Deps::DirectorySeparator();

my $SQLitePath  = "c:/sqlite3/sqlite3.exe";
$SQLitePath = "/usr/bin/sqlite3" if(!-e $SQLitePath);

#===============================================
# 静的メンバー関数
#===============================================
sub Quote
{
	my($this, $str, $sqlcharcode, $sourcecharcode) = (@_);
$sqlcharcode = 'no' if($sqlcharcode eq '' or $this->{CharCodeConversion});

	$sqlcharcode    = lc $sqlcharcode;
	$sourcecharcode = lc $sourcecharcode;
	my $strcharcode = Jcode::getcode(\$str);
	$sourcecharcode = $strcharcode if(!defined $sourcecharcode);
#print "str code: $strcharcode [$str]<br>\n";
#print "sql code: $sqlcharcode<br>\n";
#print "source code: $sourcecharcode<br>\n";

	if($this->DBMName() =~ /csv/i) {
		if($strcharcode eq 'ascii') {
			my $dbh = $this->dbh();
			$str = $dbh->quote($str);
			$str =~ s/^'//;
			$str =~ s/'$//;
			$str =~ s/\"/\\"/g;
#			$str =~ s/\"/\"\"/g; # \","" どちらのquoteでもOK。ただし、"=>'に変換される
		}
		else {
			$str =~ s/\\/\\\\/g;
			$str =~ s/\"/\\"/g;
			$str =~ s/'/''/g;
			if($sqlcharcode ne 'no' and $sqlcharcode ne $sourcecharcode) {
				Jcode::convert(\$str, $sqlcharcode, $sourcecharcode, "z");
			}
		}
#my $sc = Jcode::getcode(\$str);
#print "str [$str] ($sc)<br>";
		return $str;
	}
	elsif($this->DBMName() =~ /SQLite/i) {
#		my $dbh = $this->dbh();
#		$str = $dbh->quote($str);
#		$str =~ s/^'//;
#		$str =~ s/'$//;

		if($sqlcharcode ne 'no' and $strcharcode ne 'ascii') {
			Jcode::convert(\$str, 'jis', $sourcecharcode, "z");
		}
		$str =~ s/\\/\\\\/g; # \
		$str =~ s/\"/\"\"/g;
		Jcode::convert(\$str, $sqlcharcode) if($sqlcharcode ne 'no' and $strcharcode ne 'ascii');
		return $str;
	}
	elsif($this->DBMName() =~ /Pg/i) {
#print "char: $sqlcharcode, $sourcecharcode\n";
		$str = $this->dbh()->quote($str);
		$str =~ s/^'//;
		$str =~ s/'$//;
		Jcode::convert(\$str, $sqlcharcode, $sourcecharcode, "z") 
			if($sqlcharcode ne 'no' and $sqlcharcode ne $sourcecharcode);
		return $str;
	}
	else {
		my $s;
		if($this->{UseOriginalQuote}) {
Jcode::convert(\$str, $sqlcharcode, $sourcecharcode, "z") if($sqlcharcode ne 'no' and $sqlcharcode ne $sourcecharcode);
			$s = $this->dbh()->quote($str);
#			$s =~ s/^\'//;
#			$s =~ s/\'$//;
		}
		else {
#print "My\n";
#exit;
			$s = MySQLQuote($str, $sqlcharcode, $sourcecharcode);
		}
#print "s: $s<br>";
		return $s;
	}

#Perl DBIの関数quoteを使った場合。ただし、sjisの\を含む漢字もクオートとしてしまうため、だめ
	my $dbh = $this->dbh();
	$sourcecharcode = Jcode::getcode(\$str);
	Jcode::convert(\$str, $sqlcharcode, $sourcecharcode, "z")
			if($sqlcharcode ne 'no' and $strcharcode ne 'ascii');
	$str = $dbh->quote($str);
	$str =~ s/^'//;
	$str =~ s/'$//;

	return $str;
}

sub MySQLQuote
{
	my($str, $sqlcharcode, $sourcecharcode) = (@_);
	my $strcharcode = Jcode::getcode(\$str);
	$sourcecharcode = $strcharcode if(!defined $sourcecharcode);
$sqlcharcode = 'no' if($sqlcharcode eq '');

#print "str code: $strcharcode [$str]<br>\n";
#print "sql code: $sqlcharcode<br>\n";
#print "source code: $sourcecharcode<br>\n";
	if($sqlcharcode ne '') {
##		my $j = jcode($str);
##		$str = $j->h2z->jis;
##		Jcode::convert(\$str, 'jis');
		$str =~ s/\\/\\\\/g; # if($strcharcode =~ /jis/i or $strcharcode =~ /euc/i);
		if($sourcecharcode ne 'utf8') {
			Jcode::convert(\$str, 'euc', $sourcecharcode, "z") 
					if($sqlcharcode ne 'no' and $sqlcharcode ne $sourcecharcode);
		}
#"表"が最後に来ると、'\'でクォートされないので、スペースをつける
#		$str =~ s/$/ /;
		$str =~ s/"/\\"/g;
		$str =~ s/'/\\'/g;
#		$str =~ s/ $//;
		if($sourcecharcode ne 'utf8') {
			Jcode::convert(\$str, $sqlcharcode, 'euc') if($sqlcharcode ne 'no'  and $sqlcharcode ne $sourcecharcode);
		}
		else {
##			Jcode::convert(\$str, $sqlcharcode) if($sqlcharcode ne 'no' and $sqlcharcode ne $sourcecharcode);
			if($strcharcode eq $sqlcharcode) {
			}
			elsif($sqlcharcode ne 'no' and $sqlcharcode ne $sourcecharcode) {
# 2010/3/29 Schedule3.plで文字化けするので、この変換はやめる
#				Jcode::convert(\$str, $sqlcharcode, $sourcecharcode);
			}
		}
	}
	else {
		$str =~ s/\\/\\\\/g;
		$str =~ s/"/\\"/g;
		$str =~ s/'/\\'/g;
	}
##	$str =~ s/\c[/\\\c[/g; # ESC
##	$str =~ s/\//\\\//g; # \
##	$str =~ s/\:/\\\:/g;
##	$str =~ s/\(/\\\(/g;
##	$str =~ s/\)/\\\)/g;
##	$str =~ s/\[([^\c[])/\\\[\1/g;
##	$str =~ s/\[/\\\[/g;
##	$str =~ s/\]/\\\]/g;
#文字の最後が'\'だけは変
	$str =~ s/\\$//;# if($str !~ /\\\\$/);
#print " [$str]<br>";
	return $str;
}

sub GetDBDNameFromDBManagerName
{
	my ($ManagerName) = @_;
	if($ManagerName =~ /mysql/i) {
		return 'mysql';
	}
	elsif($ManagerName =~ /Postgre.*SQL/i or $ManagerName =~ /Pg/i) {
		return 'Pg';
	}
	elsif($ManagerName =~ /sqlite/i) {
		return 'SQLite';
	}
	elsif($ManagerName =~ /csv/i) {
		return 'CSV';
	}
	elsif($ManagerName =~ /Excel/i) {
		return 'Excel';
	}
	elsif($ManagerName =~ /Access/i or $ManagerName =~ /mdb/i) {
		return 'Access';
	}
	elsif($ManagerName =~ /SQL.*Server/i) {
		return 'SQLServer';
	}
	return $ManagerName;
}

#===============================================
# 変数取得関数
#===============================================
#sub print {
#	my ($this, @a) = @_;
##	return $this->App()->print(@a) if($this->App());
#	return $this->App()->print(@a);
##	return print(@a);
#}
#sub DebugPrint {
#	my ($this, @a) = @_;
#	return 1 unless($this->Debug());
#	return $this->print(@a);
#}
#sub Debug {
#	my ($this) = @_;
#	return $this->App()->Debug() if($this->App());
#	return $this->{'Debug'};
#}
#sub SetDebug {
#	my ($this,$f) = @_;
#	return $this->App()->SetDebug($f) if($this->App());
#	return $this->{'Debug'} = $f;
#}
sub SetSQLitePath { my ($this,$p)=@_; return $this->{SQLitePath} = $p; }
sub SQLitePath    { return shift->{SQLitePath}; }
sub DBMName {
	my ($this) = @_;
	$this->{DBMName} = 'mysql' if(!defined $this->{DBMName});
	return $this->{DBMName};
}
sub SetDBMName          { my($this,$d)=@_; return $this->{'DBMName'} = $d; }
sub db                  { return shift->{'dbh'};        }
sub dbh                 { return shift->{'dbh'};        }
sub sth                 { return shift->{'sth'};        }
sub DBServer            { return shift->{'DBServer'};   }
sub DBName              { return shift->{'DBName'};     }
sub DBUser              { return shift->{'DBUser'};     }
sub DBPassword          { return shift->{'DBPassword'}; }
sub AutoIncrementColumn { return shift->{AutoIncrementColumn}; }
sub SetAutoIncrementColumn {
	my ($this, $column) = @_;
	return $this->{AutoIncrementColumn} = $column;
}
sub DBCharCode            { return shift->{'DBCharCode'}; }
sub SetDBCharCode         { my ($this,$c)=@_; return shift->{DBCharCode}         = $c; }
sub SourceCharCode        { return shift->{'SourceCharCode'}; }
sub SetSourceCharCode     { my ($this,$c)=@_; return shift->{SourceCharCode}     = $c; }
sub SetCharCodeConversion { my ($this,$f)=@_; return shift->{CharCodeConversion} = $f; }
sub SetUseOriginalQuote   { my ($this,$f)=@_; return shift->{UseOriginalQuote}   = $f; }
sub ErrorNum              { return DBI::err;    };
sub ErrorStr              { return DBI::errstr; };
sub nFields {
	my ($this, $sth) = @_;
	$sth = $this->sth() unless($sth);
	return undef unless($sth);
	return $sth->{"NUM_OF_FIELDS"};
}
sub pFieldNames {
	my ($this, $sth) = @_;
	$sth = $this->sth() unless($sth);
	return undef unless($sth);
	return $sth->{"NAME"};
}
sub GetHitDataNumber
{
	my ($this, $TableName, $condition) = @_;
	my $nHitData = $this->IsExistData($TableName, $condition);
	return $nHitData;
}
#sub GetNextHit()

#===============================================
# コンストラクタ、デストラクタ
#===============================================
sub new
{
	my ($module, $DBMName, $CharCode, $DBServer, $DBUser, $DBPassword, $DBName,
		$PrintDBIError, $PrintError) = @_;
	my $this = {};
	bless $this;
	$this->Initialize();

	$this->SetSQLitePath($SQLitePath);

	if($DBMName =~ /csv/i) {
		$this->{'DBMName'} = 'CSV';
	}
	elsif($DBMName =~ /Excel/i) {
		$this->{'DBMName'} = 'Excel';
	}
	elsif($DBMName =~ /(pg|postgre)/i) {
		$this->{'DBMName'} = 'Pg';
	}
	elsif($DBMName =~ /mysql/i) {
		$this->{'DBMName'} = 'mysql';
	}
	elsif($DBMName =~ /sql/i) {
		$this->{'DBMName'} = $DBMName;
	}
	elsif($DBMName =~ /Access/i) {
		$this->{'DBMName'} = 'Access';
	}
	elsif($DBMName =~ /SQL.*Server/i) {
		$this->{'DBMName'} = 'SQLServer';
	}
	else {
		print "Warning in SQLDB::new: [$DBMName] is not implemented.\n" if($PrintError);
		($module, $CharCode, $DBServer, $DBUser, $DBPassword, $DBName, $PrintDBIError, $PrintError) = @_;
		$this->{'DBMName'} = 'mysql';
	}
	$this->{'DBCharCode'} = $CharCode if($CharCode);
	if($DBName) {
		$this->Open($DBServer, $DBUser, $DBPassword, $DBName,
				$CharCode, $PrintDBIError, $PrintError);
	}
	return $this;
}

sub DESTROY
{
	my $this = shift;
	$this->Close();
}

#===============================================
# 一般関数
#===============================================
sub Initialize
{
	my ($this) = @_;
	undef $this->{'DBCharCode'};
	undef $this->{'Debug'};
	undef $this->{'DBServer'};
	undef $this->{'DBName'};
	undef $this->{'DBUser'};
	undef $this->{'DBPassword'};
}

sub Open
{
	my ($this, $DBServer, $DBUser, $DBPassword, $DBName, $CharCode, $PrintDBIError, $PrintError) = @_;
	$PrintDBIError = 1 unless(defined $PrintDBIError);
	$PrintError = 1    unless(defined $PrintError);
	$this->{'DBCharCode'} = $CharCode if($CharCode);

	$this->{'DBServer'}   = $DBServer;
	$this->{'DBName'}     = $DBName;
	$this->{'DBUser'}     = $DBUser;
	$this->{'DBPassword'} = $DBPassword;

# DBIのエラー表示属性の設定
	my %Attributes = ();
	if(!$PrintDBIError) {
		$Attributes{"PrintError"} = 0;
		$Attributes{"RaiseError"} = 0;
	}
# サーバーへの接続
	my $DBMName = $this->DBMName();
	if($this->DBMName() =~ /SQLServer/i) {
		my $server = 'local';
		$server = $DBServer if($DBServer ne '' and lc $DBServer ne 'localhost');
		my $dsn = "dbi:ODBC:".
				  "driver={SQL Server};".
				  "Server=($server);database=$DBName;".
				  "Trusted_Connection=yes;". # yesだとWindows認証
				  "AutoTranslate=No;";
		$this->{dbh} = DBI->connect($dsn, $DBUser, $DBPassword, \%Attributes);
	}
	elsif($this->DBMName() =~ /Access/i) {
		my $dsn;
		if($DBName eq '') {
			print "Error in SQLDB::Open: Null database is not implemented for [Access].\n";
			return;
#			$dsn = "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=tfsp05.mdb";
		}
		else {
			$dsn = "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=$DBName";
		}
#print "dsn=$dsn\n";
		$this->{dbh} = DBI->connect($dsn, $DBUser, $DBPassword, \%Attributes);
	}
	elsif($this->DBMName() =~ /pg/i) {
		my $dsn;
		if($DBName eq '') {
			$dsn = "DBI:$DBMName:";
		}
		elsif($DBServer eq '' or $DBServer eq 'localhost') {
			$dsn = "DBI:$DBMName:dbname=$DBName" 
		}
		else {
			$dsn = "DBI:$DBMName:dbname=$DBName;host=$DBServer";
		}
		if($this->{DBPort}) {
			$dsn = "$dsn;Port=$this->{DBPort}";
		}
#print "dsn=$dsn [$DBUser][$DBPassword]\n";
		$this->{dbh} = DBI->connect($dsn, $DBUser, $DBPassword);
#print "dbh:$this->{dbh}\n";
	}
	elsif($this->DBMName() =~ /SQLite/i) {
		my $dsn = "DBI:$DBMName:dbname=$DBName";
		$this->{dbh} = DBI->connect($dsn);
	}
	elsif($this->DBMName() =~ /CSV/i) {
		$DBName =~ s/\\/\//g;
		my $dsn = "DBI:$DBMName:f_dir=$DBName";
#print "dsn: $dsn<br>\n";
		$this->{dbh} = DBI->connect($dsn) or print "connect error: $DBI::errstr\n";
#		chdir($DBName);
#print "out from connect<br>\n";
	}
	elsif($this->DBMName() =~ /Excel/i) {
		$DBName =~ s/\\/\//g;
		my $dsn = "DBI:$DBMName:file=$DBName";
#print "dsn: $dsn\n";
		$this->{dbh} = DBI->connect($dsn);
	}
	elsif($this->DBMName() =~ /mysql/i) {
		my $dsn;
		if($DBName eq '') {
			$DBName = 'mysql';
		}
		else {
		}
		$dsn = "DBI:$DBMName:database=$DBName;host=$DBServer";
		if($this->{DBPort}) {
			$dsn = "$dsn;port=$this->{DBPort}";
		}
#print "dsn:$dsn\n";
		$this->{'dbh'} = DBI->connect($dsn, $DBUser, $DBPassword, \%Attributes);
	}
	else {
		print "Warning in SQLDB::Open: [$DBMName] is not implemented.\n";
		my $dsn = "DBI:$DBMName:$DBName:$DBServer";
		$this->{'dbh'} = DBI->connect($dsn, $DBUser, $DBPassword, \%Attributes);
	}
	if(!$this->{dbh}){
		if($PrintError) {
			print "Error in SQLDB::Open: Unable to connect to $DBName\n";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}

	return ($this->{dbh}, undef) unless($DBName);

	if($DBMName =~ /SQLite/i or $DBMName =~ /csv/i or $DBMName =~ /Pg/i) {
#print "CSV return<br>\n";
		return ($this->{dbh}, undef);
	}

	return ($this->{dbh}) if($DBName eq '');

# データベースへの接続
#Utils::InitHTML();
#print "DBN[$DBName]<br>\n";
	return ($this->{dbh}, $this->Use($DBName, $PrintError) );
}

sub Use
{
	my ($this, $DBName, $PrintError) = @_;
	if($this->DBMName() =~ /SQLite/i or $this->DBMName() =~ /csv/i or
	   $this->DBMName() =~ /Access/i or $this->DBMName() =~ /SQLServer/i) {
		return undef;
	}

	my $command = "use $DBName;";
	if($this->DBMName() =~ /Pg/i) {
#		$command = "\\c $DBName;";
		return undef;
	}
#print "<br>sql: $command<br>\n";
#return if($command =~ /^use\s+[\.\d]+;$/);
	return undef if(!$this->{dbh});

	$this->{LastFunction} = "SQLDB::Use";
	$this->{LastCommand} = $command;
	$this->{DBsth} = $this->{dbh}->prepare("$command\n");
	if(!$this->{'DBsth'}) {
		if($PrintError) {
			print "Error in SQLDB::Use: Unable to prepare for opening $DBName$LF";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}
	my $result = $this->{'DBsth'}->execute;
	if(!$result){
		if($PrintError) {
			print "Error in SQLDB::Use: Unable to execute $command$LF";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}

	$this->{DBName} = $DBName;
	return $this->{DBsth};
}

sub CloseSTH
{
	my ($this, $sth) = @_;
	if($sth) {
		$sth->finish;
		return;
	}
	$this->{'sth'}->finish if($this->{'sth'});
	undef $this->{'sth'};
	$this->{'DBsth'}->finish if($this->{'DBsth'});
	undef $this->{'DBsth'};
}

sub CloseDBH
{
	my ($this) = @_;
	$this->{'dbh'}->disconnect if($this->{'dbh'});
	undef $this->{'dbh'};
}

sub Close
{
	my ($this) = @_;
	$this->CloseSTH();
	$this->CloseDBH();
	$this->Initialize();
}

sub Prepare
{
	my ($this, $command, $UseNonDefSTH, $PrintError) = @_;
#print "Prepare: $command\n";
#$this->DebugPrint("SQLDB::Prepare: dbh: " . $this->dbh() . "\n");
	return undef if(!$this->{'dbh'});
	$this->CloseSTH() unless($UseNonDefSTH);

	if($this->DBMName() =~ /csv/i) {
		$command =~ s/;$//;
		$command =~ s/\"/\'/g;
	}

	$this->{LastFunction} = "SQLDB::Prepare";
	$this->{LastCommand} = $command;
	my $sth = $this->{'dbh'}->prepare($command);
#print "Prepare: $command\n";
#$this->DebugPrint("SQLDB::Prepare: sth: $sth\n");
	unless($sth) {
		if($PrintError) {
			my $dbm = $this->DBMName();
			print "Error in SQLDB[$dbm]: Execute: Unable to prepare $command\n";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}
	$this->{'sth'} = $sth unless($UseNonDefSTH);
#print "sth: " . $this->{'sth'} . "\n";
	return $sth;
}

sub Execute
{
	my ($this, $command, $UseNonDefSTH, $PrintError) = @_;
	return undef if(!$this->{'dbh'});

	$this->CloseSTH();

	$this->{LastFunction} = "SQLDB::Execute";
	$this->{LastCommand} = $command;
	my $sth = $this->Prepare($command, $UseNonDefSTH, $PrintError);
	if(!$sth) {
		print "Error in SQLDB::Execute: Unable to prepare for [$command]\n";
		return undef;
	}
	my $ret = $sth->execute;
	unless($ret) {
		if($PrintError) {
			print "Error in SQLDB::Execute: Unable to execute [$command]\n";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}
	return ($this->{'sth'} = $sth, $ret);
}

our $DidInit = 0;
sub GetNextHit
{ 
	my ($this, $sth) = @_;
	return undef if(!defined $this);
#Utils::InitHTML() if $DidInit == 0;
#$DidInit = 1;
#print(" GetNextHit: ExecuteRet=$this->{ExecuteRet}   LastCommand=$this->{LastCommand}  LastSQL=$this->{LastSQL}<br>\n");
#	return undef if((!defined $this->{LastSQL} or $this->{LastSQL} eq '') and (!defined $this->{LastCommand} or $this->{LastCommand} eq ''));
#	return undef if($this->{ExecuteRet} eq '0E0');

#	Utils::InitHTML();
	$sth = $this->{sth} unless($sth);
	return undef if(!$sth);
	return undef if $this->rows() == 0;

#print("ExecuteRet: $this->{$this->{ExecuteRet}}<br>\n");
	my @a = $sth->fetchrow_array;
#print("  a=", @a, "<br>\n");
	my $nFields = $sth->{'NUM_OF_FIELDS'};
#Utils::InitHTML();
#print("  <H2>LastSQL=", $this->{LastSQL}, "</H2>\n");
#print("  sth=", $sth, "<br>\n");

	my $pFieldNames = $sth->{'NAME'};
#print("  sth NAME end\n");
	if(!$pFieldNames) {
#		Utils::InitHTML();
#		print("In SQLDB::GetNextHit()<br>\n");
#		print("LastFunction: $this->{LastFunction}<br>\n");
#		print("LastCommand : $this->{LastCommand}<br>\n");
	}
	my %ret;
	for(my $i = 0 ; $i < $nFields ; $i++) {
		my $name = $pFieldNames->[$i];
		if($this->DBMName() =~ /csv/i) {
			$a[$i] =~ s/''/'/g;
			$a[$i] =~ s/\\\\/\\/g;
		}
		$ret{$name} = $a[$i];
#		print("$name:$a[$i]<br>\n");
	}
	return %ret;
}

sub nHit { my ($this,$sth)=@_; return $this->nHits($sth); }
sub nHits
{
	my ($this, $sth) = @_;
	return $this->{nHit};
}

sub rows
{
	my ($this, $sth) = @_;
	$sth = $this->{'sth'} unless($sth);
	return undef unless($sth);
	return $sth->rows;
}

sub fetchrow_array
{
	my ($this, $sth) = @_;
	$sth = $this->{'sth'} unless($sth);
	return $sth->fetchrow_array;
}

sub DeleteDatabase
{
	my ($this, $DBName) = @_;
	my $sql = "drop database $DBName;";
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 0, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

sub DeleteTable
{
	my ($this, $DBName, $TableName, $IsPrint, $PrintError) = @_;
	$IsPrint = 1    if(!defined $IsPrint);
	$PrintError = 1 if(!defined $PrintError);

	if($this->DBMName() !~ /SQLite/i or $this->DBMName() !~ /Pg/i) {
		my $ret = $this->Use($DBName, 1);

#		my $sql = "use $DBName;";
#		my ($sth, $ret) = $this->Execute($sql, 0, 1);
#		return undef unless($sth);
#		$sth->finish;
	}

	my $sql = "drop table $TableName;";
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 0, $PrintError);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

sub GetDatabases
{
	my ($this) = @_;

	my @DBs;
	if($this->{DBMName} =~ /Pg/i) {
		open(FILE, "psql -U postgres -l |");
		while(1) {
			my $in = <FILE>;
			last if(!defined $in);
			last if($in =~ /^-----/);
		}
		my $c = 0;
		while(1) {
			my $in = <FILE>;
			last if(!defined $in);
			last if($in =~ /^\s*\(/);

			my ($db, $owner, $encode) = ($in =~ /^\s*(\S+)\s*\|\s*(\S+)\s*\|\s*(\S+)\s*$/);
#print "in: $in\n";
			$DBs[$c++] = "$db;$owner;$encode";
		}
		return @DBs;;
	}
	elsif($this->{DBMName} =~ /SQLite/i) {
		return ($this->{DBName});
	}
	elsif($this->{DBMName} =~ /csv/i) {
		return ($this->{DBName});
	}

	my $sql = "show databases;";
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 0, 1);
	unless($sth) {
#print("Query for 'show databases' failed.<br>\n");
		return ();
	}

	my $nDB = $this->rows();
$this->DebugPrint("nDatabases: $nDB\n");
	for(my $i = 0 ; $i < $nDB ; $i++) {
		my %hit = $this->GetNextHit();
		foreach my $key (keys %hit) {
			my $val = $hit{$key};
			next unless(defined $val);
$this->DebugPrint("  $key: $val\n");
			push(@DBs, $val);
		}
	}
	$sth->finish;
	return @DBs;
}

sub CreateDatabase
{
	my ($this, $dbname) = @_;
#print "DBM: ", $this->DBMName(), "  DB: $dbname\n";
	return Deps::CreateDirecotry($dbname) if($this->DBMName() =~ /^csv$/i);

	if($this->{DBMName} =~ /SQLite/i) {
		return 1;
	}

	my $sql = "create database $dbname;";
	my $ret;
	$this->CloseSTH();
	return $this->Execute($sql, 0, 1);
}

sub DescribeTable
{
	my ($this, $tablename) = @_;
#print "DBM: ", $this->DBMName(), "  DB: $dbname\n";

	if($this->{DBMName} !~ /mysql/i) {
		return 1;
	}

	my $sql = "DESC $tablename;";
	my $ret;
	$this->CloseSTH();
	my $ret = $this->Execute($sql, 0, 1);
	my $nHit = $this->rows();
#print "ret=$ret<br>\n";
#print "nHit=$nHit<br>\n";
	my @lines;
	for(my $i = 0 ; $i < $nHit ; $i++) {
		my %Hit = $this->GetNextHit();
		$lines[$i] = \%Hit;
	}
	my @Keys = keys %{$lines[0]} if($lines[0]);

	return ($nHit, \@Keys, @lines);
}

sub IsExistDatabase
{
	my ($this, $dbname) = @_;
	return -d $dbname if($this->DBMName() =~ /^csv$/i);

	if($this->{DBMName} =~ /SQLite/i) {
		return -e $dbname;
	}
	if($this->{DBMName} =~ /Pg/i) {
		my $DB = new SQLDB($this->{DBMName});
		my ($dbh, $sth) = $DB->Open($this->{DBServer}, $this->{DBUser}, $this->{DBPassword}, $this->{DBName});
		$DB->Close();
		return $dbh;
	}

	$dbname = lc $dbname;
	my @DBs = $this->GetDatabases();
	return -1 if(@DBs == 0);
	for(my $i = 0 ; $i < @DBs ; $i++) {
		return 1 if($dbname eq lc $DBs[$i]);
	}
	return 0;
}

sub GetTables
{
	my ($this, $DBName, $PrintError) = @_;
	$PrintError = 1 if(!defined $PrintError);
	$DBName = $this->{DBName} if(!defined $DBName);

	my ($sth, $ret);
	if($this->DBMName() =~ /csv/i) {
		my $fmask = Utils::MakePath($DBName, "*.csv", "/", 0);
		my @files = glob($fmask);
		my @a;
		for(my $i = 0 ; $i < @files ; $i++) {
			$a[$i] = Deps::ExtractFileName($files[$i]);
		}
		return @a;
	}
	elsif($this->DBMName() =~ /Pg/i) {
		my @tables = $this->dbh()->tables('', 'public', '', '');
		for(my $i = 0 ; $i < @tables ; $i++) {
			$tables[$i] =~ s/^public\.//;
		}
		return @tables;
	}
	elsif($this->DBMName() =~ /SQLite/i) {
		my $path = (defined $this->{SQLitePath})? $this->{SQLitePath} : 'sqlite3';
		my $ret = `$path $DBName .tables`;
		my @tables = Utils::Split("\\s+", $ret);
		return @tables;
	}
	else {
#Utils::InitHTML();
#print "DBN[$DBName]<br>\n";
		my $ret = $this->Use($DBName, $PrintError);
#		my $sql = "use $DBName;";
#		($sth, $ret) = $this->Execute($sql, 1, 1);
#		$sth->finish if($sth);
		my $sql = "show tables;";
		($sth, $ret) = $this->Execute($sql, 1, 1);
		return undef unless($sth);
	}

	my @Tables;
	my $nTables = $this->rows();
$this->DebugPrint("nTables: $nTables\n");
	for(my $i = 0 ; $i < $nTables ; $i++) {
		my %hit = $this->GetNextHit($sth);
		foreach my $key (keys %hit) {
			my $val = $hit{$key};
			$val = "NULL" unless(defined $val);
$this->DebugPrint("  $key: $val\n");
			push(@Tables, $val);
		}
	}
	$sth->finish;
	return @Tables;
}

sub IsExistTable
{
	my ($this, $DBName, $TableName) = @_;
	$DBName = $this->{DBName} if(!defined $DBName);

	if($this->DBMName() =~ /csv/i) {
		return -e "$DBName/$TableName";
	}

	$TableName = lc $TableName;
	my @TBLs = $this->GetTables($DBName);
	for(my $i = 0 ; $i < @TBLs ; $i++) {
		return 1 if(lc $TBLs[$i] eq $TableName);
	}
	return 0;
}

sub GetDrivers
{
	my ($this) = @_;
	return $this->GetDBManagers();
}

sub GetDBManagers
{
	my ($this) = @_;
	return DBI->available_drivers();
#print "drivers: ", scalar @drivers, "\n";
#for(my $i = 0 ; $i < @drivers ; $i++) {
#	print "$i: $drivers[$i]\n";
#}
}

sub GetFieldTypes
{
	my ($this, $DBName, $TableName, $FieldName, $PrintError) = @_;
	$PrintError = 1 if(!defined $PrintError);

	if($this->DBMName() !~ /SQLite/i) {
		$this->Use($DBName, $PrintError);
	}

	my $sql = "select * from $TableName;";
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);

	my $pFieldNames = $this->pFieldNames($sth);
	my $pFieldTypes = $this->sth()->{TYPE};
	$pFieldTypes = [] if(!defined $pFieldTypes);
	my @FieldTypes  = @$pFieldTypes;
	my $nFieldTypes = @FieldTypes;
$this->DebugPrint("    nFieldsType: $nFieldTypes\n");
	for(my $k = 0 ; $k < $nFieldTypes ; $k++) {
		my $name = $pFieldNames->[$k];
		my $type = $pFieldTypes->[$k];
		$type = -4 if(!defined $type);
		my $str = $this->GetStringType($type);
#$this->print("    $k: $name: $str [$type]\n");
		$FieldTypes[$k] = $str;
	}
	$sth->finish;

	return @FieldTypes;
}

sub GetStringType
{
	my ($this, $type) = @_;
	my $str;
	if($type == -1) {
		$str = 'text';
	}
	elsif($type == -4) {
		$str = 'text';
	}
	elsif($type == 4) {
		$str = 'int';
	}
	elsif($type == 12) {
		$str = 'varchar(255)';
	}
	else {
		$str = 'unrecognized';
	}
	return $str;

	if($type == DBI->SQL_CHAR) {
		return 'SQL_CHAR';
	}
	elsif($type == DBI->SQL_NUMERIC) {
		return 'SQL_NUMERIC';
	}
	elsif($type == DBI->SQL_DECIMAL) {
		return 'SQL_DECIMAL';
	}
	elsif($type == DBI->SQL_INTEGER) {
		return 'SQL_INTEGER';
	}
	elsif($type == DBI->SQL_SMALLINT) {
		return 'SQL_SMALLINT';
	}
	elsif($type == DBI->SQL_FLOAT) {
		return 'SQL_FLOAT';
	}
	elsif($type == DBI->SQL_DOUBLE) {
		return 'SQL_DOUBLE';
	}
	elsif($type == DBI->SQL_VARCHAR) {
		return 'SQL_VARCHAR';
	}
	return 'unrecognized';
}

sub ExtractDBHash
{
	my ($this, $DBName, $TableName, $pParams) = @_;
	my @Fields = $this->GetFields($DBName, $TableName);
	my %DBHash;
	for(my $i = 0 ; $i < @Fields ; $i++) {
		my $key = $Fields[$i];
		$DBHash{$key} = $pParams->{$key} if(defined $pParams->{$key});
#print "key: $key<br>\n";
	}
	return \%DBHash;
}

sub GetFields
{
	my ($this, $DBName, $TableName, $PrintError) = @_;
	$PrintError = 1 if(!defined $PrintError);

	if($this->DBMName() !~ /SQLite/i) {
		$this->Use($DBName, $PrintError) if(defined $DBName);
#		my $sql = "use $DBName;";
#		my ($sth, $ret) = $this->Execute($sql, 1, 1);
#		$sth->finish if($sth);
	}

	my $sql = "select * from $TableName;";
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);

	my @Fields;
	my $nFields = $this->nFields($sth);
$this->DebugPrint("    nFields: $nFields\n");
	my $pFieldNames = $this->pFieldNames($sth);
	for(my $k = 0 ; $k < $nFields ; $k++) {
		my $name = $pFieldNames->[$k];
$this->DebugPrint("    $k: $name\n");
		push(@Fields, $name);
	}

	$sth->finish;
	return @Fields;
}

sub BuildDBHash
{
	my ($this, $pLabels, $pHash, $DBCharCode, $AddBlankData, $SourceCharCode) = @_;
	$DBCharCode = $this->{DBCharCode} if(!defined $DBCharCode);
	$DBCharCode = lc $DBCharCode;
	$SourceCharCode = $this->SourceCharCode() if($SourceCharCode eq '');
$DBCharCode = 'no' if($this->{CharCodeConversion});

	my %d;
	my $n = @$pLabels;
	for(my $j = 0 ; $j < $n ; $j++) {
		my $label;
		if($this->{DBMName} =~ /Pg/i) {
			$label = lc $pLabels->[$j];
		}
		else {
			$label = $pLabels->[$j];
		}

		my $s = $pHash->{$label};
		next if(!$AddBlankData and lc $label ne 'sn' and $s eq '');
		$s = '' if(!defined $s);

		Jcode::convert(\$s, $DBCharCode) if(defined $DBCharCode and $DBCharCode ne 'no' and $DBCharCode ne $SourceCharCode);
		if(lc $label eq 'sn') {
			if($s eq '') {
				$s = $this->{CurrentSN} + 1;
			}
			if($s > $this->{CurrentSN}) {
				$this->{CurrentSN} = $s 
			}
		}
		$d{$label} = $s;
	}
	return %d;
}

sub BuildHash
{
	my ($this, $pLabels, $pData, $DBCharCode, $AddBlankData) = @_;
	$DBCharCode = $this->{DBCharCode} if(!defined $DBCharCode);
	$DBCharCode = lc $DBCharCode;
	my $SourceCharCode = $this->SourceCharCode();
$DBCharCode = 'no' if($this->{CharCodeConversion});

	my %d;
	my $n = @$pLabels;
	for(my $j = 0 ; $j < $n ; $j++) {
		my $s = $pData->[$j];
		my $label;
		if($this->{DBMName} =~ /Pg/i) {
			$label = lc $pLabels->[$j];
		}
		else {
			$label = $pLabels->[$j];
		}
		next if(!$AddBlankData and lc $label ne 'sn' and $s eq '');

		$s =~ s/\\\\/\\/g;
		Jcode::convert(\$s, $DBCharCode) if(defined $DBCharCode and $DBCharCode ne 'no' and $DBCharCode ne $SourceCharCode);
		if(lc $label eq 'sn') {
			if($s eq '') {
				$s = $this->{CurrentSN} + 1;
			}
			if($s > $this->{CurrentSN}) {
				$this->{CurrentSN} = $s 
			}
		}
		$d{$pLabels->[$j]} = $s;
	}
	return %d;
}

sub GuessDatabaseDefinition
{
	my ($this, $filename, $nGuessLines, $IgnoreBlank, $DBCharCode, $SourceCharCode, $IsPrint) = @_;
	$nGuessLines = 100 if(!defined $nGuessLines);
	$IgnoreBlank = 1   if(!defined $IgnoreBlank);
	$IsPrint = 1       if(!defined $IsPrint);
	$SourceCharCode = $this->SourceCharCode() if(!defined $SourceCharCode);
$DBCharCode = '' if($this->{CharCodeConversion});

	$this->{CurrentSN} = 0;
	my %type = (
			'I'		=> 'int',
			'F'		=> 'double precision',
			'S'		=> 'text'
		);
	if($this->DBMName() =~ /csv/i) {
		$type{F} = 'num';
	}
	my @DataBaseDefinition =
		(
			{"Field", "sn", "Type", "int",  "Key",  "primary"},
		);

	my $in = new CSV();
	$in->Open($filename, "r");
	if(!$in) {
		print "Error in SQLDB::GuessDatabaseDefinition: Can not read [$filename].\n";
		return undef;
	}

	my @labels = $in->ReadNextLine();
	my @Type;
	for(my $i = 0 ; $i < @labels ; $i++) {
		$Type[$i] = 'I';
	}
	for(my $i = 0 ; $i < $nGuessLines ; $i++) {
		my @data = $in->ReadNextLine();
		last if(!defined $data[0]);
		for(my $j = 0 ; $j < @labels ; $j++) {
			my $s = $data[$j];
#			next if($s eq '' or $Type[$j] eq 'S');
			next if($Type[$j] eq 'S');
			next if($IgnoreBlank and $s eq '');

			if($Type[$j] eq 'I' and $s =~ /^[+\-]?\d+$/) {
				$Type[$j] = 'I';
			}
			elsif(($Type[$j] eq 'I' or $Type[$j] eq 'F') and $s ne '' and
					$s =~ /^[+\-]?\d*(\.\d*)?([EeDd][+\-]?\d+)?$/) {
				$Type[$j] = 'F';
			}
			else {
				$Type[$j] = 'S';
			}
		}
	}

	for(my $i = 0 ; $i < @labels ; $i++) {
print "$i: $labels[$i]: $Type[$i]\n" if($IsPrint);
		next if($labels[$i] eq 'sn');

		if($DBCharCode) {
			my $s = $labels[$i];
#print "s=$s => ";
			Jcode::convert(\$s, $DBCharCode, $SourceCharCode) if($DBCharCode ne 'no' and $DBCharCode ne '');
			$s =~ s/[\.,]/_/g;
			$s =~ s/[\r\n]/ /g;
			$labels[$i] = $s;
#print "$s [$DBCharCode/$SourceCharCode]\n";
#$this->{c}++;
#exit if($this->{c} > 10);
		}
		my %a = (
			"Field" => $labels[$i],
			"Type"  => $type{$Type[$i]},
			"Null"  => 'Yes'
			);
		my $n = @DataBaseDefinition;
		$DataBaseDefinition[$n] = \%a;
		$n++;
	}
	$in->Close();

	return @DataBaseDefinition;
}

sub GetTableDefinition
{
	my ($this, $DBName, $TableName) = @_;
	my $sql = "desc $DBName.$TableName;";
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return (-1) if(!$sth or !$ret);
	my @pDefs;
	for(my $i = 0 ; $i < $this->rows() ; $i++) {
		my @a = $this->GetNextHit();
		my %b;
		for(my $j = 0 ; $j < @a ; $j += 2) {
			$b{$a[$j]} = $a[$j+1];
		}
		$pDefs[$i] = \%b;
	}
	$sth->finish if($sth);
	return @pDefs;
}

sub CreateTable
{
	my ($this, $TableName, $pDefsHash) = @_;
	my @pDefs = @$pDefsHash;
	my $nDefs = @pDefs;
	my @CSVTableDef;
#	my @CSVColumnNames;
	my $sql = "create table $TableName (";
	my $PrimKey = '';
	for(my $l = 0 ; $l < $nDefs ; $l++) {
		my $pA = $pDefs[$l];
		$sql .= ", " if($l > 0);
		$pA->{Type} =~ s/\(\d+\)// if($pA->{Type} =~ /int/i);
#print "Def[$pA->{Type}]: $pA->{Default}\n";
		if($this->DBMName() =~ /csv/i) {
#			push(@CSVColumnNames, $pA->{Field});
			my $type = $pA->{Type};
			if($pA->{Type} =~ /int/i) {
				push(@CSVTableDef, Text::CSV_XS::IV());
				$type = 'integer';
			}
			elsif($pA->{Type} =~ /num/i) {
				push(@CSVTableDef, Text::CSV_XS::NV());
			}
			else {
				push(@CSVTableDef, Text::CSV_XS::PV());
			}
			$sql .= " $pA->{Field} $type";
			next;
		}

#print "Extra: $pA->{Extra} / DBM: ", $this->DBMName(), " / Key: $pA->{Key}\n";
		if($pA->{Extra} =~ /auto.?increment/i and $this->DBMName() =~ /SQLite/i and $pA->{Key} =~ /^pri$/i) {
			$sql .= " $pA->{Field} integer primary key autoincrement";
			$PrimKey = '';
		}
		elsif($this->DBMName() =~ /Pg/i and $pA->{Key} =~ /^pri/i) {
			$sql .= " $pA->{Field} integer primary key";
			$PrimKey = '';
		}
		else {
			$sql .= "$pA->{Field} $pA->{Type}";
			if($pA->{Key} =~ /^pri/i) {
				$PrimKey = $pA->{Field};
			}
		}

#print("sql: $sql\n");
		if($pA->{Null} !~ /^yes$/i) {
			$sql .= " not null";
		}

#print "sql [Def: $pA->{Default}]: $sql => ";
		if(defined $pA->{Default}) {
			$sql .= " default $pA->{Default}";
		}
#print "$sql\n";

		if(lc $pA->{Extra} eq 'auto_increment') {
			if($this->DBMName =~ /SQLite/i) {
#				$sql .= " primary key autoincrement";
			}
			else {
				$sql .= " auto_increment";
			}
		}
	}
	if($PrimKey ne '' and $this->DBMName() !~ /csv/i and $this->DBMName() !~ /Pg/i) {
		$sql .= " , PRIMARY KEY ($PrimKey)";
	}
#	$sql .= ");";
	$sql .= ")";
#print("sql: $sql\n");

	if($this->DBMName() =~ /csv/i) {
#print "Def: ", @CSVTableDef, "\n";
#print "Col: ", @CSVColumnNames, "\n";
		$this->dbh()->{csv_tables}->{$TableName}->{types} = \@CSVTableDef;
#		$this->dbh()->{csv_tables}->{$TableName}->{col_names} = \@CSVColumnNames;
	}
#print "sql: $sql => ";
#Jcode::convert(\$sql, $this->DBCharCode(), "sjis");
#print "$sql\n";
#exit;
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	unless($sth) {
		$this->print("Error in CreateTable: $sql\n");
		return 0;
	}
	return ($sth, $ret);
}

sub IsExistData
{
	my ($this, $TableName, $condition) = @_;
	return -1 if(!defined $TableName);
	$condition = '' if(!defined $condition);

	my $sql = "select * from $TableName";
	if($condition ne '') {
		$condition = "where $condition" unless($condition =~ /^\s*where/i);
		$sql = "$sql $condition";
	}
	$sql = "$sql;";
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	unless($sth) {
		$this->print("<H2>Error: selelct in MakeSQLUpdateCommand: "
			."$sql</H2>\n");
		return 0;
	}
	my $nHit = $this->rows();
#	my $nHit = $this->nHit();
	$sth->finish;
	return $nHit;
}

sub JoinSearch
{
	my ($this, $TableName1, $TableName2, $JoinCondition, $condition, $fields) = @_;

	$this->{nHit} = 0;
	$fields = '*' if(!defined $fields or $fields eq '');
	my ($sql0, $sql);

	$sql0 = "select COUNT(*) from $TableName1 join $TableName2 on $JoinCondition;";
	$sql  = "select $fields from $TableName1 join $TableName2 on $JoinCondition;";

	if($condition) {
		if($condition !~ /^\s*where\s/i) {
			if($condition =~ /^\s*order\s/i) {
			}
			else {
				$condition = "where $condition";
			}
 		}
 		my $condition0 = $condition;
 		$condition0 =~ s/\sorder\s+by\s.*$//;
		$sql0 = "select COUNT(*) from $TableName1 join $TableName2 on $JoinCondition $condition0;";
		$sql  = "select $fields from $TableName1 join $TableName2 on $JoinCondition $condition;";
	}
#$this->DebugPrint("SQLDB: sql: $sql\n\n");
#	if($this->DBMName =~ /SQLite/i) {
		return undef if(!$this->Execute($sql0, 1, 1));
		my %Hit = $this->GetNextHit();
		my @k = keys %Hit;
		$this->{nHit} = $Hit{$k[0]};
#print "nHit(COUNT): $k[0] = $Hit{$k[0]}<br>\n";
#	}
	$this->{LastSQL} = $sql;

#print "SQLDB::Search: Table: $TableName<br>\n";
#print "SQLDB::Search: sql: $sql<br>\n";
	return $this->Execute($sql, 1, 1);
}

sub Search
{
	my ($this, $TableName, $condition, $fields) = @_;
#$this->DebugPrint("Search in: fields=[$fields] \n");
#print "SQLDB::Search: condition: $condition<br>\n";

	$this->{nHit} = 0;
	$fields = '*' if(!defined $fields or $fields eq '');
	my ($sql0, $sql);
#	if($TableName eq '' or $this->{DBMName} =~ /csv/i) {
	if($TableName eq '') {
		$sql0 = "select COUNT(*);";
		$sql  = "select $fields;";
	}
	else {
		$sql0 = "select COUNT(*) from $TableName;";
		$sql  = "select $fields from $TableName;";
	}
	if($condition) {
		if($condition !~ /^\s*where\s/i) {
			if($condition =~ /^\s*order\s/i) {
			}
			else {
				$condition = "where $condition";
			}
 		}
 		my $condition0 = $condition;
 		$condition0 =~ s/\sorder\s+by\s.*$//;
#		if($TableName eq '' or $this->{DBMName} =~ /csv/i) {
		if($TableName eq '') {
			$sql0 = "select COUNT(*) $condition0;";
			$sql  = "select $fields $condition;";
		}
		else {
			$sql0 = "select COUNT(*) from $TableName $condition0;";
			$sql  = "select $fields from $TableName $condition;";
		}
	}
#$this->DebugPrint("SQLDB: sql: $sql\n\n");
#	if($this->DBMName =~ /SQLite/i) {
		return undef if(!$this->Execute($sql0, 1, 1));
		my %Hit = $this->GetNextHit();
		my @k = keys %Hit;
		$this->{nHit} = $Hit{$k[0]};
#print "nHit(COUNT): $k[0] = $Hit{$k[0]}<br>\n";
#	}
	$this->{LastSQL} = $sql;

#print "SQLDB::Search: Table: $TableName<br>\n";
#print "SQLDB::Search: sql: $sql<br>\n";
	my $ret = $this->Execute($sql, 1, 1);

	$this->{ExecuteRet} = $ret;
#Utils::InitHTML();
#print "  Execute ret: $ret<br>\n";

	return $ret;
}

sub InsertData
{
	my ($this, $TableName, @a) = @_;
	my $CharCode = $this->{DBCharCode};
	if($this->DBMName() =~ /csv/i and $this->AutoIncrementColumn() ne '') {
		my $c = $this->AutoIncrementColumn();
		$this->Search($TableName, "order by $c desc", "");
		my $sn = 1;
		my %Hit = $this->GetNextHit();
		$sn = $Hit{sn}+1 if(defined $Hit{sn});
		@a = (
			@a,
			$c, $sn
			);
	}
	my $sql = $this->MakeUpdateCommand($TableName, $CharCode, "insert", '', @a);
#print "sql: $sql<br>\n";
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

sub UpdateData
{
	my ($this, $TableName, $condition, @a) = @_;
	my $CharCode = $this->{'DBCharCode'};
	my $sql = $this->MakeUpdateCommand($TableName, $CharCode, "update", $condition, @a);
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

sub DeleteData
{
	my ($this, $TableName, $condition) = @_;
	my $CharCode = $this->{'DBCharCode'};
	my $sql = "delete from $TableName where $condition;\n";
#$this->DebugPrint("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

sub UpdateDataByAutoJudge
{
	my ($this, $TableName, $condition, @a) = @_;
	my $CharCode = $this->{'DBCharCode'};
	my $sql = $this->MakeUpdateCommand($TableName, $CharCode, "auto", $condition, @a);
#$this->print("SQLDB: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

#'auto', 'insert', 'update'
#$DB->MakeUpdateCommand('paper_tbl', 'sjis', 'auto', 'where EMail = \'$EMail\',
#		'Title', $Title,
#		'Name', $Name
#		);
sub MakeUpdateCommand
{
	my $this = shift;
	my ($DBTableName, $charcode, $action, $condition, @a) = (@_);
	$charcode = $this->DBCharCode() if($charcode eq '');
$charcode = '';

	my $narg = @_;
	my $quote = '"';
	if($this->DBMName() =~ /csv/i or $this->DBMName() =~ /Pg/i) {
		$quote = '\'';
	}
	elsif($this->DBMName() =~ /mysql/i and $this->{UseOriginalQuote}) {
		$quote = '';
	}
	
#	my @Fields = $this->GetFields($this->{DBName}, $DBTableName);

#$this->DebugPrint("SQLDB: charcode : $charcode\n");
#$this->DebugPrint("SQLDB: action   : $action\n");
#$this->DebugPrint("SQLDB: condition: $condition\n");
#$this->print("SQLDB: narg: $narg\n");

	my $sql;
#$print "***action: $action\n";
	if($action =~ /^auto$/i) {
		if($condition eq '') {
			$this->print("<H2>Error in MakeSQLUpdateCommand: "
			     ."Condition should be specified for \'auto\'</H2>\n");
			return "";
		}
		else {
#print "***condition: $condition<br>\n";
#			my $nHit = $this->IsExistData($DBTableName, $condition);
			$this->Search($DBTableName, $condition, "");
			my $nHit = $this->nHit();
			if($nHit <= 0) {
				$action = 'insert';
			}
			else {
				$action = 'update';
			}
		}
	}
#print "***Action: $action ($condition)\n";

	if($action =~ /^insert$/i) {
		my $sql0 = "insert into $DBTableName(";
#$this->print("SQLDB: sql: $sql<br>\n");
		$sql = '';
#$this->DebugPrint("SQLDB: sql: $sql\n");
		for(my $i = 4 ; $i < $narg ; $i += 2) {
#print "m i=$i\n";
			next if(!defined $_[$i+1]);
			my $key;
			if($this->{DBMName} =~ /Pg/i) {
				$key = $_[$i];
#				$key = lc $_[$i];
			}
			else {
				$key = $_[$i];
			}
			if($sql eq '') {
				$sql = $key;
			}
			else {
				$sql .= ",$key";
			}
#			if($i < $narg-2) {
#				$sql = "$key,";
#			}
#			else {
#				$sql .= "$key) ";
#			}
#$this->DebugPrint("SQLDB: sql[$i]: $sql\n");
		}
		$sql .= ") ";
		$sql .= "values(";
		my $sql2 = '';
#$this->DebugPrint("SQLDB: sql: $sql\n");
		for(my $i = 4 ; $i < $narg ; $i += 2) {
#print "m i=$i\n";
#$this->print("SQLDB3: sql: $sql [$charcode]<br>\n");
#print("i=$i: key=$_[$i]<br>\n");
			next if(!defined $_[$i+1]);
#			$_[$i+1] = '' if(!defined $_[$i+1]);
#			my $s = SQLDB::MySQLQuote($_[$i+1], $charcode, $this->SourceCharCode());
#print "s=$_[$i+1]\n";
#print "m i=$i\n";
			my $s = $this->Quote($_[$i+1], $charcode, $this->SourceCharCode());
#			my $s = ($charcode ne '')? $this->Quote($_[$i+1], $charcode, $this->SourceCharCode()) : $_[$i+1];
#$s =~ s/\"/\'/mg;
#print "   => $s<br>\n";
			if($sql2 eq '') {
				$sql2 = "$quote$s$quote";
			}
			else {
				$sql2 .= ",$quote$s$quote";
			}
#			if($i < $narg-2) {
#				$sql .= "$quote$s$quote,";
#			}
#			else {
#				$sql .= "$quote$s$quote);";
#			}
#$this->DebugPrint("SQLDB: sql[$i]: $sql\n");
		}
		$sql = $sql0 . $sql . $sql2 . ");";
#$this->print("SQLDB2: sql: $sql\n");
	}
	elsif($action =~ /^update$/i) {
		$sql = "update $DBTableName set ";
#$this->DebugPrint("SQLDB: c: $sql\n");
		for(my $i = 4 ; $i < $narg ; $i += 2) {
#print("i=$i: key=$_[$i]<br>\n");
			next if(!defined $_[$i+1]);
			my $key = $_[$i];
#			my $s = SQLDB::MySQLQuote($_[$i+1], $charcode, $this->SourceCharCode());
			my $s = $this->Quote($_[$i+1], $charcode, $this->SourceCharCode());
#			my $s = ($charcode ne '')? $this->Quote($_[$i+1], $charcode, $this->SourceCharCode()) : $_[$i+1];
#$s =~ s/\"/\'/mg;
			if($i < $narg-2) {
				$sql .= "$key=$quote$s$quote,";
			}
			else {
				$sql .= "$key=$quote$s$quote ";
			}
#$this->DebugPrint("SQLDB: c: $sql\n");
		}
		$condition = "where $condition" if($condition !~ /^\s*where\s/i);
		$sql =~ s/,\s*$/ /;
		$sql .= "$condition;";
	}
	else {
		print "<H2>Error: Invalid action: $action</H2>\n";
		return "";
	}
#$this->DebugPrint("SQLDB: c: $sql\n");
#print("SQLDB: c: $sql\n");
	return $sql;
}

sub SaveToCSV
{
	my ($this, $TableName, $condition, $fields, $path, $charcode, $pDeleteKeys) = @_;
$charcode = 'no' if($charcode eq '' and $this->{CharCodeConversion});

	my $out = new CSV();
	return 0 if(!$out->Open($path, "w", 0));

	$this->Search($TableName, $condition, $fields);
	my $nFields = $this->nFields();
#print "nFields: $nFields<br>\n";
	my $pFieldNames = $this->pFieldNames();
	$out->WriteLabelLine(@$pFieldNames);

	my $nHit = $this->nHit();
	for(my $i = 0 ; $i < $nHit ; $i++) {
		my %Hit = $this->GetNextHit();
		if($pDeleteKeys) {
			for(my $i = 0 ; $i < @$pDeleteKeys ; $i++) {
				delete $Hit{$pDeleteKeys->[$i]};
			}
		}
		my @data;
my $line = join('', %Hit);#@data);
my $sourcecharcode = Jcode::getcode($line);
		for(my $j = 0 ; $j < $nFields ; $j++) {
			my $field = $pFieldNames->[$j];
			$data[$j] = $Hit{$field};
if($charcode ne 'no' and $sourcecharcode ne 'ascii' and $sourcecharcode ne $charcode) {
	Jcode::convert(\$data[$j], $charcode);
#	Jcode::convert(\$data[$j], $charcode, $sourcecharcode);
#print "c: $sourcecharcode => $charcode<br>\n";
}
		}
		$out->WriteDataLine(\@data);
	}

	return 1;
}

#================================================================================
# Utility関数
#================================================================================
sub MakeCSVFile
{
	my ($this, $OutFilePath, $CSVFileCharCode, $DBTableName, $sql, %args) = @_;
	my $IsPrint      = (defined $args{IsPrint})? $args{IsPrint} : 0;
	my $pLabel       = $args{pLabel};
	my $DBName       = (defined $args{DBName})?       $args{DBName}       : $this->{DBName};
	my $pDBFields    = (defined $args{pDBFields})?    $args{pDBFields}    : sort $this->GetFields($DBName, $DBTableName, 0);
#	my $pDBFields    = $args{pDBFields};
	my $pFirstDBList = (defined $args{pFirstDBList})? $args{pFirstDBList} : [qw(sn EMail Name JName)];
	my $pLastDBList  = (defined $args{pLastDBList})?  $args{pLastDBList}  : [qw(IPAddress LastUpdateTime LastLogonTime)];

	my $out = new CSV;
	if(!$out->Open($OutFilePath, "w", 0)) {
		return 0;
	}

	my @DBFields = ($pDBFields)? @$pDBFields : ();
	my @keys;
	$this->Search($DBTableName, $sql, "");
	my $nHit = $this->nHit();
	for(my $i = 0 ; $i < $nHit ; $i++) {
		my %Hit = $this->GetNextHit();
		if(@DBFields == 0) {
			@DBFields = sort keys %Hit;
			@DBFields = (@$pFirstDBList, Utils::DeleteItemFromList(\@DBFields, $pFirstDBList)) if($pFirstDBList);
			@DBFields = (Utils::DeleteItemFromList(\@DBFields, $pLastDBList), @$pLastDBList)   if($pLastDBList);
			@keys = @DBFields;
			if($pLabel) {
				for(my $i = 0 ; $i < @keys ; $i++) {
					if($pLabel->{$keys[$i]}) {
						$keys[$i] = $pLabel->{$keys[$i]};
Jcode::convert(\$keys[$i], $CSVFileCharCode) if(defined $CSVFileCharCode and $CSVFileCharCode ne 'ascii');
					}
				}
			}
			$out->WriteDataLine(\@keys);
		}

		my @a;
		for(my $i = 0 ; $i < @DBFields ; $i++) {
			my $key = $DBFields[$i];
			my $val = $Hit{$key};
Jcode::convert(\$val, $CSVFileCharCode) if(defined $CSVFileCharCode and $CSVFileCharCode ne 'ascii');
			$a[$i] = $val;
		}
		$out->WriteDataLine(\@a);
	}
	$out->Close();

	return $nHit;
}

#================================================================================
# コマンドラインインターフェース
#================================================================================
sub RestoreDBFromFile
{
	my ($this, $DBServer, $DBUser, $DBPassword, $File, $ShowMessage) = @_;
	$DBServer   = $this->{'DBServer'} unless($DBServer);
	$DBUser     = $this->{'DBUser'} unless($DBUser);
	$DBPassword = $this->{'DBPassword'} unless($DBPassword);

	my $HidePass = '***';
	$HidePass = $DBPassword if($ShowMessage >= 2);
	my $command = "mysql -h $DBServer -u $DBUser --password=$HidePass < $File";
	print "   $command\n" if($ShowMessage);
	$command    = "mysql -h $DBServer -u $DBUser --password=$DBPassword < $File";
	system($command);
}

# $ShowMessage: 0: メッセージ無し　1: パスワードを隠してメッセージを表示
#               2: パスワードとともにメッセージを表示
sub MakeBackupFile
{
	my ($this, $DBServer, $DBName, $DBUser, $DBPassword, $File, $ShowMessage) = @_;
	$DBServer   = $this->{'DBServer'} unless($DBServer);
	$DBName     = $this->{'DBName'} unless($DBName);
	$DBUser     = $this->{'DBUser'} unless($DBUser);
	$DBPassword = $this->{'DBPassword'} unless($DBPassword);
	
	my $HidePass = '***';
	$HidePass = $DBPassword if($ShowMessage >= 2);
	my $command = "mysqldump --opt --databases $DBName "
			."-h $DBServer -u $DBUser --password=$HidePass  > $File";
	if($DBName eq 'all') {
		$command = "mysqldump --opt --all-databases "
			."-h $DBServer -u $DBUser --password=$HidePass  > $File";
	}

	print "   $command\n" if($ShowMessage);

	$command = "mysqldump --opt --all-databases "
		."-h $DBServer -u $DBUser --password=$DBPassword  > $File";
	if($DBName eq 'all') {
		   $command = "mysqldump --opt --all-databases "
	   		."-h $DBServer -u $DBUser --password=$DBPassword  > $File";
	}
	system($command);
}

1;