#============================================================
# SQLDB2
#============================================================
package SQLDB2;
use lib '../lib';
use lib '../lib2.0';
use Common2;
@ISA = qw(Common2);
#公開したいサブルーチン
#@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;

#===============================================
# 文字コード関係変数
#===============================================
# 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();

#===============================================
# 静的メンバー関数
#===============================================
sub GetDBConfig
{
	my ($idx, $pDBConfigArray) = @_;
	my $pDBKey    = $pDBConfigArray->[0];
	my $pDBConfig = $pDBConfigArray->[$idx];
	my %DBConfig;
	for(my $i = 0 ; $i < @$pDBConfig ; $i++) {
		$DBConfig{$pDBKey->[$i]} = $pDBConfig->[$i];
#print "DBConfig{$pDBKey->[$i]} = $pDBConfig->[$i]<br>";
	}
	return %DBConfig;
}

sub FindDBConfig
{
	my ($conf, $pDBConfigArray) = @_;
	for(my $i = 1 ; $i < @$pDBConfigArray ; $i++) {
		my %DBConfig = GetDBConfig($i, $pDBConfigArray);
#print "Config: $DBConfig{DBConfigName} ($conf)<br>\n";
		return %DBConfig if($DBConfig{DBConfigName} eq $conf);
	}
	return undef;
}

sub OpenDBByConfig
{
	my ($App, $DBCharCode, $SourceCharCode, $PrintDBIError, $PrintError, $Debug, $pDBConfig) = @_;
#print "DBM: ",  $pDBConfig->{DBMName}, "\n";
	return new SQLDB2(
		-Application         => $App,
		-DBConfigName        => $pDBConfig->{DBConfigName},
		-DBMName             => $pDBConfig->{DBMName},
		-DBServer            => $pDBConfig->{DBServer},
		-DBPort              => $pDBConfig->{DBPort},
		-DBUser              => $pDBConfig->{DBUser},
		-DBPassword          => $pDBConfig->{DBPassword},
		-DBName              => $pDBConfig->{DBName},
		-TableName           => $pDBConfig->{TableName},
		-DBCharCode          => $DBCharCode,
		-SourceCharCode      => $SourceCharCode,
		-AutoIncrementColumn => 'sn',
		-PrintDBIError       => $PrintDBIError,
		-PrintError          => $PrintError,
		-Debug               => $Debug,
		);
}

sub OpenDBByDBConfigArray
{
	my ($App, $config, $pDBConfigArray, $ShowErrorMessage) = @_;
	my $DB;
	my %DBConfig;
	if($config and $config !~ /^auto$/i) {
		%DBConfig = SQLDB2::FindDBConfig($config, $pDBConfigArray);
		$DB = SQLDB2::OpenDBByConfig($App, $App->SQLCharCode(), $App->WebCharCode(),
				0, 0, $App->Debug(), \%DBConfig);
	}
	elsif($config =~ /^auto$/i) {
		for(my $i = 1 ; $i < @$pDBConfigArray ; $i += 7) {
			%DBConfig = SQLDB2::GetDBConfig($i, $pDBConfigArray);
#print "DBM: ",  $DBConfig{DBMName}, "\n";
			$DB = SQLDB2::OpenDBByConfig($App, $App->SQLCharCode(), $App->WebCharCode(),
					$ShowErrorMessage, $ShowErrorMessage, $App->Debug(), \%DBConfig);
			last if($DB->dbh());
		}
	}
	if(!$DB->dbh()) {
#		$App->print("Error in SQLDB::OpenDB: DBI:" . $DB->DBMName() . ":" . $DB->DBName() 
#			. ":" . $DB->DBServer() . "\n") if($ShowErrorMessage);
		return undef;
	}
	return $App->{DB} = $DB;
}

sub Quote
{
	my($this, $str, $sqlcharcode, $sourcecharcode) = (@_);
	my $strcharcode = Jcode::getcode(\$str);
	$sourcecharcode = $strcharcode if(!defined $sourcecharcode);
	$sqlcharcode = $this->{SQLCharCode} if($sqlcharcode eq '');
#print "strchr: $strcharcode [$str]<br>\n";
#print "char: [$sqlcharcode] : [$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;
			Jcode::convert(\$str, $sqlcharcode, $sourcecharcode, "z")
				if($sqlcharcode ne $sourcecharcode);
		}
#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($strcharcode ne 'ascii') {
			Jcode::convert(\$str, 'jis', $sourcecharcode, "z");
		}
		$str =~ s/\\/\\\\/g; # \
		$str =~ s/\"/\"\"/g;
		Jcode::convert(\$str, $sqlcharcode) if($strcharcode ne 'ascii');
		return $str;
	}
	else {
		my $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($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);

	my $IsLastCharBackSlash = 0;
#print "s: [$str]";
#print "sqlchar: $sqlcharcode<br>";
	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);
		Jcode::convert(\$str, 'euc', $sourcecharcode, "z")
				if($sourcecharcode ne $sourcecharcode);
#"表"が最後に来ると、'\'でクォートされないので、スペースをつける
#		$str =~ s/$/ /;
		$str =~ s/"/\\"/g;
		$str =~ s/'/\\'/g;
#		$str =~ s/ $//;
#print "<b>str:</b> $str<br>\n";
		$IsLastCharBackSlash = 1 if($str =~ /\\$/);
		Jcode::convert(\$str, $sqlcharcode)
				if($sourcecharcode ne $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 !~ /\\\\$/);
	$str = "$str\\" if($IsLastCharBackSlash);
#print "<br><b>Quoted:</b> [$str] [$IsLastCharBackSlash]<br>";
	return $str;
}

#===============================================
# 変数取得関数
#===============================================
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 DBPort              { return shift->{'DBPort'};     }
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 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, @args) = @_;
	my $this = $module;
	if($this !~ /^HASH=/) {
		$this = {};
		bless $this;
	}
	$this->Initialize();
	Common2::new($this, @args);
	if($this->{DBName}) {
		$this->Open(
			-DBMName       => $this->{DBMName},
			-DBServer      => $this->{DBServer},
			-DBPort        => $this->{DBPort},
			-DBUser        => $this->{DBUser},
			-DBPassword    => $this->{DBPassword},
			-DBName        => $this->{DBName},
			-DBCharCode    => $this->{DBCharCode},
			-PrintDBIError => $this->{PrintDBIError},
			-PrintError    => $this->{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, %Args) = @_;
	foreach my $key (keys %Args) {
		my $key1 = $key;
		$key1 =~ s/^\-//;
		$this->{$key1} = $Args{$key};
	}
	$this->{PrintDBIError} = 1 unless(defined $this->{PrintDBIError});
	$this->{PrintError}    = 1 unless(defined $this->{PrintError});

# DBIのエラー表示属性の設定
	my %Attributes = ();
	if(!$this->{PrintDBIError}) {
		$Attributes{PrintError} = 0;
		$Attributes{RaiseError} = 0;
	}

# サーバーへの接続
	my $DBMName = $this->DBMName();
	if($this->DBMName() =~ /pg/i) {
		my $dsn;
		if($this->{DBServer} eq '') {
			$dsn = "DBI:$DBMName:dbname=$this->{DBName}" 
		}
		else {
			$dsn = "DBI:$DBMName:dbname=$this->{DBName};host=$this->{DBServer}";
		}
		$dsn = "DBI:$DBMName:" if($this->{DBName} eq '');
		if($this->{DBPort}) {
			$dsn = "$dsn;Port=$this->{DBPort}";
		}
		$this->{'dbh'} = DBI->connect($dsn, $this->{DBUser}, $this->{DBPassword});
	}
	elsif($this->DBMName() =~ /sqlite/i) {
		my $dsn = "DBI:DBMName:dbname=$this->{DBName}";
		$this->{dbh} = DBI->connect($dsn);
	}
	elsif($this->DBMName() =~ /csv/i) {
		my $dsn = "DBI:$DBMName:f_dir=$this->{DBName}";
		$this->{dbh} = DBI->connect($dsn);
	}
	else {
		my $dsn = "DBI:$DBMName:$this->{DBName}:$this->{DBServer}";
		$this->{dbh} = DBI->connect($dsn, $this->{DBUser}, $this->{DBPassword}, \%Attributes);
	}
	if(!$this->{dbh}){
		if($this->{PrintError}) {
			print "Error in SQLDB2::OpenDB: "
			      ."Unable to connect to $this->{DBName}\n";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}

	return ($this->{dbh}, undef) unless($this->{DBName});
	if($DBMName =~ /sqlite/i or $DBMName =~ /csv/i) {
		return ($this->{dbh}, undef);
	}

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

# データベースへの接続
	return ($this->{dbh}, $this->Use($this->{DBName}, $this->{PrintError}) );
}

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

	my $command = "use $DBName;";
	if($this->DBMName() =~ /Pg/i) {
#		$command = "\\c $DBName";
		return undef;
	}

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

	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) = @_;
#$this->DebugPrint("SQLDB2::Prepare: dbh: " . $this->dbh() . "\n");
	return undef if(!$this->{'dbh'});
	$this->CloseSTH() unless($UseNonDefSTH);

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

	my $sth = $this->{'dbh'}->prepare($command);
#$this->DebugPrint("SQLDB2::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();

	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;
#print "ret: $ret  PrintError: $PrintError\n";
	unless($ret) {
		if($PrintError) {
			print "Error in SQLDB2::Execute: Unable to execute [$command]\n";
			print "    DBI::err: " . DBI::errstr . "(" . DBI::err . ")\n";
		}
		return undef;
	}
	return ($this->{'sth'} = $sth, $ret);
}

sub GetNextHit
{ 
	my ($this, $sth) = @_;
	return undef if(!defined $this);
	$sth = $this->{'sth'} unless($sth);
	my @a = $sth->fetchrow_array;
	return () if(@a == 0);
#	return () if(!defined @a or @a == 0);
	my $nFields = $sth->{'NUM_OF_FIELDS'};
#print "nFields: $nFields  a:@a<br>\n";
	my $pFieldNames = $sth->{'NAME'};
	my %ret;
	for(my $i = 0 ; $i < $nFields ; $i++) {
		my $name = $pFieldNames->[$i];
#print "name: $name<br>\n";
		if($this->DBMName() =~ /csv/i) {
			$a[$i] =~ s/''/'/g;
			$a[$i] =~ s/\\\\/\\/g;
		}
		$ret{$name} = $a[$i];
	}
#print "ret: $ret{FileGroup}<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) = @_;
	if(!defined $DBName) {
		$DBName = $this->{DBName};
	}
	my $sql = "drop database $DBName;";
#$this->DebugPrint("SQLDB2: 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) = @_;
	if(!defiend $DBName) {
		$DBName = $this->{DBName};
	}
	if(!defiend $TableName) {
		$TableName = $this->{TableName};
	}
	if($this->DBMName() !~ /sqlite/i) {
		$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("SQLDB2: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 0, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

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

	my @DBs;
	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, $UseNonDefSTH, $PrintError) = @_;
	$UseNonDefSTH = 0 if(!defined $UseNonDefSTH);
	$PrintError = 1   if(!defined $PrintError);
#print "this: $this  DBM: ", $this->DBMName(), "  DB: $dbname\n";
	return Deps::CreateDirecotry($dbname) if($this->DBMName() =~ /^csv$/i);

	my $sql = "create database $dbname;";
	my $sth = $this->dbh()->prepare($sql);
	my $ret = $sth->execute;
	return $ret;
}

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

	$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) = @_;
	if(!defiend $DBName) {
		$DBName = $this->{DBName};
	}
	my ($sth, $ret);
	if($this->DBMName() =~ /sqlite/i) {
#		($sth, $ret) = $this->Execute(".table", 1, 1);
		return undef unless($sth);
	}
	else {
		$this->Use($DBName, 1);
#		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) = @_;
	if(!defiend $DBName) {
		$DBName = $this->{DBName};
	}
	if(!defiend $TableName) {
		$TableName = $this->{TableName};
	}
	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 GetFields
{
	my ($this, $DBName, $TableName) = @_;
	if($this->DBMName() !~ /sqlite/i) {
		$this->Use($DBName, 1);
#		my $sql = "use $DBName;";
#		my ($sth, $ret) = $this->Execute($sql, 1, 1);
#		$sth->finish if($sth);
	}

	my $sql = "select * from $TableName;";
#$this->DebugPrint("SQLDB2: 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 BuildHash
{
	my ($this, $pLabels, $pData, $charcode) = @_;
	$charcode = $this->{DBCharCode} if(!defined $charcode);

	my %d;
	my $n = @$pLabels;
	for(my $j = 0 ; $j < $n ; $j++) {
		my $s = $pData->[$j];
		$s =~ s/\\\\/\\/g;
		Jcode::convert(\$s, $charcode);
		$d{$pLabels->[$j]} = $s;
	}
	return %d;
}

sub GuessDatabaseDefinition
{
	my ($this, $filename, $nGuessLines, $IgnoreBlank) = @_;
	$nGuessLines = 100 if(!defined $nGuessLines);
	$IgnoreBlank = 1   if(!defined $IgnoreBlank);

	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(@data == 0);
#		last if(!defined @data);
		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";
		next if($labels[$i] eq 'sn');
	
		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) = @_;
	if(!defiend $TableName) {
		$TableName = $this->{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);

		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};
			}
		}

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

		if(!defined $pA->{Default}) {
			$sql .= " default $pA->{Default}";
		}

		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;
	}

	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) = @_;
	if(!defined $condition) {
		$condition = $TableName;
		$TableName = $this->{TableName};
	}

	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 Search
{
	my ($this, $TableName, $condition, $fields) = @_;
	if(!defined $fields) {
		($condition, $fields) = ($TableName, $condition);
		$TableName = $this->{TableName};
	}
#$this->DebugPrint("Search in: fields=[$fields] \n");
	$this->{nHit} = 0;
	$fields = '*' if(!defined $fields or $fields eq '');
	my $sql0 = "select COUNT(*) from $TableName;";
	my $sql  = "select $fields from $TableName;";
	if($condition) {
		if($condition !~ /^\s*where\s/i) {
			if($condition =~ /^\s*order/i) {
			}
			else {
				$condition = "where $condition";
			}
 		}
		$sql0 = "select COUNT(*) from $TableName $condition;";
		$sql  = "select $fields from $TableName $condition;";
	}
#print("SQLDB2: sql: $sql<br>\n");
$this->DebugPrint("SQLDB2: 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";
#	}
	return $this->Execute($sql, 1, 1);
}

sub InsertData
{
	my ($this, $TableName, $pHash) = @_;
	if(!defined $pHash) {
		$pHash = $TableName;
		$TableName = $this->{TableName};
	}
	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});
		$pHash->{$c} = $sn;
	}
	my $sql = $this->MakeUpdateCommand($TableName, $CharCode, "insert", '', $pHash);
$this->DebugPrint("SQLDB2: sql: $sql\n");
#print("\nSQLDB2: sql: $sql\n\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

sub UpdateData
{
	my ($this, $TableName, $condition, $pHash) = @_;
	if(!defined $pHash) {
		($condition, $pHash) = ($TableName, $condition);
		$TableName = $this->{TableName};
	}
	my $CharCode = $this->{'DBCharCode'};
	my $sql = $this->MakeUpdateCommand($TableName, $CharCode, "update", $condition, $pHash);
#$this->DebugPrint("SQLDB2: 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) = @_;
	if(!defined $condition) {
		$condition = $TableName;
		$TableName = $this->{TableName};
	}
	my $CharCode = $this->{'DBCharCode'};
	my $sql = "delete from $TableName where $condition;\n";
#$this->DebugPrint("SQLDB2: 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, $pHash) = @_;
#print "Make: <b>cond:</b> $condition<br>\n";
	if(!defined $pHash) {
		($condition, $pHash) = ($TableName, $condition);
		$TableName = $this->{TableName};
	}
#print "Make: <b>cond:</b> $condition<br>\n";
	my $CharCode = $this->{'DBCharCode'};
	my $sql = $this->MakeUpdateCommand($TableName, $CharCode, "auto", $condition, $pHash);
#print "<b>sql:</b> $sql<br>\n";
#$this->DebugPrint("SQLDB2: sql: $sql\n");
	my ($sth, $ret) = $this->Execute($sql, 1, 1);
	return undef unless($sth);
	$sth->finish;
	return 1;
}

#'auto', 'insert', 'update'
sub MakeUpdateCommand
{
	my ($this, $TableName, $charcode, $action, $condition, $pHash) = @_;
#print "Make: <b>cond:</b> $condition<br>\n";
	if(!defined $pHash) {
		($charcode, $action, $condition, $pHash)
			= ($TableName, $charcode, $action, $condition);
		$TableName = $this->{TableName};
	}
#print "Make: <b>cond:</b> $condition<br>\n";
#print "<b>tbl:</b> $TableName   <b>cond:</b> $condition<br>\n";
	$charcode = $this->DBCharCode() if($charcode eq '');
	my $quote = '"';
	if($this->DBMName() =~ /csv/i or $this->DBMName() =~ /Pg/i) {
		$quote = '\'';
	}

	my @key = keys %$pHash;
	my $narg = @key;
	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\n";
#			my $nHit = $this->IsExistData($TableName, $condition);
			$this->Search($TableName, $condition, "");
			my $nHit = $this->nHit();
			if($nHit <= 0) {
				$action = 'insert';
			}
			else {
				$action = 'update';
			}
		}
	}
#print "***Action: $action ($condition)\n";
	if($action =~ /^insert$/i) {
		$sql = "insert into $TableName(";
#$this->DebugPrint("SQLDB2: sql: $sql\n");
		for(my $i = 0 ; $i < $narg ; $i++) {
			next if(!defined $pHash->{$key[$i]});
			if($i < $narg-1) {
				$sql .= "$key[$i],";
			}
			else {
				$sql .= "$key[$i]) ";
			}
#$this->DebugPrint("SQLDB2: sql[$i]: $sql\n");
		}
		$sql .= "values(";
#$this->DebugPrint("SQLDB2: sql: $sql\n");
		for(my $i = 0 ; $i < $narg ; $i++) {
			next if(!defined $pHash->{$key[$i]});
			my $s = $this->Quote($pHash->{$key[$i]}, $charcode, $this->SourceCharCode());
			if($i < $narg-1) {
				$sql .= "$quote$s$quote,";
			}
			else {
				$sql .= "$quote$s$quote);";
			}
#$this->DebugPrint("SQLDB2: sql[$i]: $sql\n");
		}
	}
	elsif($action =~ /^update$/i) {
		$sql = "update $TableName set ";
#$this->DebugPrint("SQLDB2: c: $sql\n");
		for(my $i = 0 ; $i < $narg ; $i++) {
#print "<b>$key[$i]:</b> $pHash->{$key[$i]}<br>\n";
			next if(!defined $pHash->{$key[$i]});
			my $s = $this->Quote($pHash->{$key[$i]}, $charcode, $this->SourceCharCode());
			if($i < $narg-1) {
				$sql .= "$key[$i]=$quote$s$quote,";
			}
			else {
				$sql .= "$key[$i]=$quote$s$quote ";
			}
#$this->DebugPrint("SQLDB2: c: $sql\n");
		}
		$condition = "where $condition" if($condition !~ /^\s*where\s/i);
		$sql .= "$condition;";
	}
	else {
		print "<H2>Error: Invalid action: $action</H2>\n";
		return "";
	}
$this->DebugPrint("SQLDB2: c: $sql\n");
	return $sql;
}

#================================================================================
# コマンドラインインターフェース
#================================================================================
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;