#!/usr/bin/perl -w

my $VERSION;
$VERSION="2.1";


my $me=$0;

use strict;
use Getopt::Long;
use fontlinge::Config; 
use fontlinge::Filebasics; 
#use fontlinge::Fontling; 
use DBI;
use Term::ReadKey;

no utf8;
use bytes;

my $file_uncompressed	="2003_02_02.txt";

my $dbadmin_user;
my $dbadmin_password;
my $root; # obsolete
my $create_user;
my $create_database;
my $update_database;
my $import_database;
my $import_text;
my $reject_log;
my $trusted_sorters;
my $export_text;
my $export_my_text;
my $export_database;
my $export_my_database;
my $check;
my $overwrite_duplicates;
my $help;
my $download;
my $mysqlclient;
my $phoenix;

GetOptions (
		'dbadmin-user=s'	=> \$dbadmin_user,
		'dbadmin-password=s'	=> \$dbadmin_password,
		'root:s'		=> \$root, # obsolete
 		'create-user' 		=> \$create_user,
 		'mysqlclient=s'		=> \$mysqlclient,
 		'create-database'	=> \$create_database,
 		'update-database'	=> \$update_database,
 		'import-database'	=> \$import_database,
 		'import-text'		=> \$import_text,
 		'help'			=> \$help,
 		'phoenix'		=> \$phoenix,
		'reject-log=s'		=> \$reject_log,
		'trusted-sorters:s'	=> \$trusted_sorters,
 		'export-text'		=> \$export_text,
 		'export-my-text' 	=> \$export_my_text,
 		'export-database' 	=> \$export_database,
 		'export-my-database'	=> \$export_my_database,
		'overwrite-duplicates'	=> \$overwrite_duplicates,
 		'download' 	=> \$download,
 		'check' 		=> \$check,
 ) || exit -1 ;

my $cmd_count = 0;

if ($create_user) {$cmd_count += 1};
if ($create_database) { $cmd_count += 1};
if ($update_database) { $cmd_count += 1};
if ($import_database) { $cmd_count += 1};
if ($import_text) { $cmd_count += 1};
if ($export_database) { $cmd_count += 1};
if ($export_my_database) { $cmd_count += 1};
if ($phoenix) { $cmd_count += 1};
if ($export_text) { $cmd_count += 1};
if ($export_my_text) { $cmd_count += 1};
if ($check) { $cmd_count += 1};
if ($download) { $cmd_count += 1};

if ($cmd_count > 1) {
	print STDERR "ERROR: You can only use one command at a time!\n\n";
	exit 1;
}

# if ( defined($help)) {
if ( $help || ( $cmd_count != 1 ) ) {
	&usage;
	exit;
}


my %config;
%config=fontlinge_Get_Config();
my $username	= $config{'username'};
my $password	= $config{'password'};
my $sortername	= $config{'sortername'};
my $database	= $config{'database'};
my $mysqlserver= $config{'mysqlserver'};


# do some basic security checks
if ($mysqlserver =~ /[\\'";]/) {
	print STDERR "ERROR: The mysqlserver in your ~/.fontlinge config file contains the invalid\n";
	print STDERR "       character $&. Please change your configuration and try again.\n";
	exit 1;
}
if ($database =~ /[\\\/.*%'"; \t]/) {
	print STDERR "ERROR: The database name your in ~/.fontlinge config file contains the invalid\n";
	print STDERR "       character $&. Please change your configuration and try again.\n";
	exit 1;
}
if ($username =~ /[\\'";]/) {
	print STDERR "ERROR: The MySQL username in your ~/.fontlinge config file contains the invalid\n";
	print STDERR "       character $&. Please change your configuration and try again.\n";
	exit 1;
}
if ($password =~ /[\\'";]/) {
	print STDERR "ERROR: The MySQL password in your ~/.fontlinge config file contains the invalid\n";
	print STDERR "       character $&. Please change your configuration and try again.\n";
	exit 1;
}

#' bug in gedit. This "'" closes an already closed string.

if ( ($mysqlserver eq "localhost") && (! defined $mysqlclient) ) {
	$mysqlclient= "localhost"; 
}
if (! defined $mysqlclient) {
	print "\
Your MySQL server is set to '$mysqlserver', which
is NOT recognized as localhost.
You have to use something like

	--mysqlclient=\"192.168.0.10\"   or
	--mysqlclient=\"%\"   for unlimited access.

Use that one of your IPs your MySQL server is 'seeing' you as.
	
";
	exit 2;
}



my $file=$ARGV[0];

if ($create_user) {
	if(defined $root) { # obsolete
		print STDERR "Warning: param --root is obsolete and will be removed in future versions.\n";
		print STDERR "         Please use --dbadmin-password=\"pass\" instead.\n\n";
		if (! defined $dbadmin_password) {
			$dbadmin_password=$root;
		} else {
			print STDERR "Warning: --root and --dbadmin-password were given.\n";
			print STDERR "         I will use password given with --dbadmin-password.\n\n";
		}
	}
#	if(! defined $dbadmin_password) {$dbadmin_password="";}
	if(! defined $dbadmin_user) {$dbadmin_user="root";}
	if ( $create_user ) { &create_user; }
	exit;
}

if ($create_database) {
	&create_database;
	exit;
}

if ($update_database) {
	&update_database(1);
	exit;
}


if ($import_database) {
	&import_database;
	exit;
}

if ($import_text) {
	&import_text;
	exit;
}

if ($export_database) {
	&export_database("");
	exit;
}

if ($export_my_database) {
	&export_database('look_creator="'.$sortername.'"');
	exit;
}


if ($export_text) {
	&export_text("");
	exit;
}

if ($export_my_text) {
	&export_text('look_creator="'.$sortername.'"');
	exit;
}

if($check) {
	&db_check;
	exit;
}

if($phoenix) {
	&phoenix;
	exit;
}

if($download) {
	&download;
	exit;
}




sub db_check{
	my $dbh;
	my $sth;
	my $e;
	my %empty;
	
	print "\n\nConnecting database \"$database\" on host \"$mysqlserver\" using username \"$username\", password from configfile.\n\n";
	$dbh = DBI->connect("DBI:mysql:database=$database;host=$mysqlserver", "$username", "$password",{RaiseError => 0, PrintError => 0} );
	if (! defined $dbh) {
		$e=$DBI::err;
		if ($e==1045) { 
			print "I could not even connect to the database with this account.\n"; 
			print "Make sure MySql is prepared for user \"$username\"\n"; 
			print "or change ~/.fontlinge to a valid account.\n"; 
			print "\n";
			print "You can try:\n";
			print "fontlinge_database_assistant --create-user\n";
			print "    [--dbadmin-password=\"MySqlRootPassword]\" [--dbadmin-user=\"SqlAdmin\"]\n";
			print "- or -\n";
			print "you have created the user but used the wrong host. Check --mysqlhost=\$HOST_IP\n";
			exit 3;
		} elsif ($e==2002) { 
			print "I did not find the MySql-server.\n"; 
			print "Make sure it is installed and running.\n";
			exit 8;
			
		} elsif ($e==1049) { 
			print "There is no database with the name \"$database\".\n"; 
			print "Please create it.\n"; 
			print "\n";
			print "You can try:\n";
			print "fontlinge_database_assistant --create-database\n";
			exit 7
		} elsif ($e==1044) { 
			print "The user may not access the database.\n"; 
			print "Please grant access.\n"; 
			print "\n";
			print "You can try:\n";
			print "fontlinge_database_assistant --create-user\n";
			exit 10;
		}
		print "\n\nError: ($e) $DBI::errstr\n\n";
		exit 66;
	}
	print "The database exists, I can login.\n";

	foreach my $db ('fonts','kategorien','look') {
		$sth = $dbh->prepare("SELECT * FROM $db LIMIT 1" , {RaiseError => 0, PrintError => 0} );
		$sth->execute();
		if(defined $dbh->err) {
			print $dbh->err;
			print "\n";
			print $dbh->errstr;
			print "\n";
			$dbh->disconnect();
			print "\n";
			print "A required table does not exist. You have to create it.\n";
			print "\n";
			print "You can try:\n";
			print "fontlinge_database_assistant --import-database empty.SQL\n";
			print "\n";
			exit 4;		#if ($dbh->err == 1062) {
		} else {
			$sth->finish();
			print "table '$db' exists.\n";
			$empty{$db}=1-$sth->rows;
		}
	}
	
	if( $empty{'look'} ) {
		print "\n";
		print "A required table was empty.\n";
		print "\n";
		print "You can continue manually - _N_O_T_  _G_O_O_D_!_!_!_!_\n";
		print "- or - you can try\n";
		print "fontlinge_database_assistant -import-text DATABASE_FILE.txt\n";
		print "\n";
		print "You can find the database file where you downloaded this program.\n";
		print "\n";
		exit 5;
	}

	# old version did not grant the permission to alter. Find the Problem.
	my $que2 = $dbh->prepare("SHOW GRANTS for '" . quotemeta($username) . "'\@'" . quotemeta($mysqlserver) . "'");
	my $result2 = $que2->execute or die "error on database statement ", $que2->errstr, __LINE__;
	my $answer;
	my $ok = 0;
	while ( my $tmp2 = $que2->fetchrow_hashref ) {
		$answer=$tmp2->{ "Grants for $username\@$mysqlserver" };
		if( $answer=~/GRANT (.*?) ON/s ) {
			my $tmp=$1;
			$tmp=~s/ //gis;
			my @tmp=split("," , $tmp);
			if( grep (/^ALTER$/ , @tmp) && ($answer =~ /ON.*[^a-zA-Z0-9]$database[^a-zA-Z0-9].*TO/ ) ) { $ok=1; }
		}
	}
	if( !$ok ) {
		print "\n\n\nError: It seems you're not permitted to ALTER your database.\n\n";
		print "try fontlinge_database_assistant --create-user\n";
		exit 12;
	} else {
		print "Permissions OK.\n";
	}


	# print "Closing database\n"; 
	$dbh->disconnect();

	&update_database(0);

	print "Finished.\n"; 

} 

# returns a hash of hashes, containing the indicies of table "fonts"
sub get_index_fonts {
	return (
		# name of index		=>	  typ		column(s) (coma-separated, no spaces!)
		"font_path"		=>	{ typ => "u",	cols => "font_path" },
		"font_id"		=>	{ typ => "i", 	cols => "font_id"},
		"font_kategorie_name"	=>	{ typ => "i",	cols => "font_kategorie,font_name"},
	);
}

# returns a hash of hashes, containing the indicies of table "look"
sub get_index_look {
	return (
		# name of index		=>	  typ		column(s) (coma-separated, no spaces!)
		"look_name"		=>	{ typ => "u",	cols => "look_name" },
		"look_creator"		=>	{ typ => "i", 	cols => "look_creator"},
	);
}


sub check_indicies {
	my $fix_index = shift;
	my $table = shift;
	my $checkfunc = shift;

	# check (and optionally fix) table "fonts"

	my %index  = &$checkfunc;
	my %index2 = &$checkfunc(); # a hash of hashes cannot be copied that easy, so use this way

	my @checkresult = &check_index($table, %index);

	if ($#checkresult >= 0 ) {
		if ($fix_index == 0) {
			print "\n\nThe following indicies need to be (re-)created:\n";
			print "    table $table:\n";
			foreach my $key (@checkresult) {
				print "        $key\t$index2{$key}{cols}\n";
			}
			print "\nTry running fontlinge_database_assistant --update-database\n";
			exit 14;
		} else { # create/fix indicies
			foreach my $key (@checkresult) {
				create_index($table, $key, $index2{$key}{cols}, $index2{$key}{typ});
			}
		}
	} else {
		if ($fix_index == 0) { print "Table $table: Indicies OK.\n"; }
	}

}


# checks indicies of a table in database
# returns an array of broken indicies
sub check_index {
	# Params:
	my $table = shift; # name of the table to check
	my %index = @_; # hash of hashes with the expected indicies

	my $dbh;
	my $indexname; 
	my $indextyp;
	my $indexcols;
	my $sth;
	my $searchfor;
	my $row_hash;
	my $maydel;
#	my %addindex; # additional index created by user

	$dbh = DBI->connect("DBI:mysql:database=$database;host=$mysqlserver", "$username", "$password" ) || die "Cannot connect to database";
	$sth = $dbh->prepare("SHOW indexes FROM $table");
	$sth->execute();
	while ( $row_hash = $sth->fetchrow_hashref) {
		$maydel = 1;
		$indexname = $row_hash->{"Key_name"};
#		print "checking index: $indexname... ";
		if (!defined ($index{$indexname})) {
			if (exists($index{$indexname}{cols})) { # initializes $index{$indexname} :-(
				print "\nTable $table: Additional column in index $indexname found. Column: $row_hash->{Column_name}\n";
#				$addindex{$indexname} .= $row_hash->{Column_name} . ",";
			} else {
				print "\nTable $table: Additional index $indexname found. Column: $row_hash->{Column_name}\n";
#				$addindex{$indexname} .= $row_hash->{Column_name} . ",";
			}
			print "If you think this is an enhancement, please inform the author ;-)\n";
			delete $index{$indexname}; # needed, see "exists" call above
			next; # skip rest of the loop.
		}
		$indextyp = $index{$indexname}{"typ"};
		$indexcols = $index{$indexname}{"cols"};

		# (non-)unique correct?
		if ($indextyp eq "u") { 
			if ($row_hash->{"Non_unique"} != 0) { $maydel = 0; } 
		} elsif ($indextyp eq "i") {
			if ($row_hash->{"Non_unique"} != 1) { $maydel = 0; }
		}

		# check if the index contains the correct columns
		if ($row_hash->{"Column_name"} eq $indexcols) {
		} else { 
			$searchfor = $row_hash->{"Column_name"};
			if ($indexcols =~ /,/) { # multiple (remaining) columns
				$indexcols =~ s/,*$/,/; # append "," (if not already there) - makes searching easier ;-)
				if ("$indexcols" =~ /^$searchfor,/) {
					$indexcols =~ s/^$searchfor,//;
				} else { # no match - index broken
					$indexcols = "-,$indexcols";  # lock column list - can't become correct anymore
				}
				$indexcols =~ s/^,|,$//;
				$index{$indexname}{cols} = "$indexcols";
				if ( $indexcols !~ /^[,-]*$/ ) { $maydel = 0; }
			} else {
				$maydel = 0;
			}
		}

		if ($maydel == 1) { # alles OK
			delete $index{$indexname}; 
		}
	}

	return keys(%index); # return key names of broken indicies
}


sub create_user{
			print "\n";
			print "Creating user with login and password from ~/.fontlinge for host $mysqlclient,\n";
			print "logging in as \"$dbadmin_user\".\n";
			print "\n";
			execute_mysql_root(
				 "GRANT ALTER,CREATE,DELETE,DROP,INDEX,INSERT,SELECT,UPDATE,USAGE ON $database.* TO ?\@? IDENTIFIED BY ?",
				 $username, $mysqlserver, $password
			);
			print "\nCreated user '$username' and granted access to database '$database' successfully.\n";
}

# execute_mysql( database , mysqlserver , username , password , query , value1 , value2 , value3 , ... )

sub mysql_open {
	my ( $database , $mysqlserver , $username , $password ) = ( @_ );
	my $tmp = ($database ne "" ? "DBI:mysql:database=$database;"    :   "DBI:mysql:;" )."host=$mysqlserver";
	my $dbh = DBI->connect( $tmp , $username , $password ) || die "\nERROR: Cannot connect to database.\n";
	return $dbh;
}

sub execute_mysql {
	my ( $database , $mysqlserver , $username , $password , $query , @args ) = ( @_ );
	my $dbh = mysql_open( $database , $mysqlserver , $username , $password );
	my $sth = $dbh->prepare( $query , {RaiseError => 0, PrintError => 0} );
	$sth->execute( @args );
	my $ret = $sth->rows;
	if(defined $dbh->err) { print STDERR "Error: $DBI::errstr\n"; exit 1; }
	$sth->finish();
	$dbh->disconnect();
	return $ret;
}


sub update_database{
	my $fix_index = shift; # possible values:
		# 0 - check only (--check); 
		# 1 - fix database (--update-database)
	&check_indicies($fix_index, "fonts", \&get_index_fonts);
	&check_indicies($fix_index, "look", \&get_index_look);
	if ($fix_index != 0) {
		execute_mysql_user( "ALTER TABLE $database.fonts CHANGE font_path font_path varbinary(255) not null" , () );
		# workaround for http://bugs.mysql.com/bug.php?id=19371
		execute_mysql_user( 'UPDATE fonts SET font_path=REPLACE(font_path, "\\0", "")' , () );
	}
}

sub create_index{
	my $table  = shift; # name of table
	my $name   = shift; # name of index
	my $fields = shift; # field(s) in index
	my $typ    = shift; # index type (index / unique)

	if ($typ eq "i") {
		$typ = "INDEX"
	} elsif ($typ eq "u") {
		$typ = "UNIQUE"
	} else {
		die "unknown index type";
	}

	my $dbh = DBI->connect( "DBI:mysql:database=$database;host=$mysqlserver" , $username , $password ,{'RaiseError' => 0 , 'PrintError' => 0} ) || die "\nERROR: Cannot connect to database.\n";
	my $sth;

	# drop existing (broken) indexes first
	$sth = $dbh->do( "ALTER TABLE $table DROP INDEX $name" ) ; 
	if (! $dbh->err) {
		print "Dropped broken index '$name'.\n";
	} elsif ($dbh->err == 1091) {
		# print "There was no index '$name' dropped.\n";
	} elsif ($dbh->err == 1044) {
		print "\nERROR: Cannot drop index '$name' because of missing ALTER rights.\n";
		die "Try running fontlinge_database_assistant --create-user\n"
	} else {
		print "\nERROR when dropping index '$name':\n";
		die $dbh->errstr . "\n";
	}

	# (re-)create indexes
	$sth = $dbh->do( "ALTER TABLE $table ADD $typ $name ($fields)" ) 
	    || die "\n" . $dbh->err . $dbh->errstr
		. "\n\nERROR: Cannot create index '$name'.\n"
		. "Try running fontlinge_database_assistant --create-user\n";
	print "Created index '$name'.\n";
	$dbh->disconnect();
	return;
}

sub execute_mysql_user {
	my ( $query , @args ) = ( @_ );
	return execute_mysql( $database , $mysqlserver , $username , $password , $query , @args );
}

# execute_mysql_root( query , value1 , value2 , value3 , ... )
sub execute_mysql_root {
	my ( $query , @args ) = ( @_ );
	my $password=$dbadmin_password;
	if( ! defined $password ) { $password=ask_password(); }
	return execute_mysql( "" , $mysqlserver , $dbadmin_user , $password , $query , @args );
}

sub ask_password{
	print "Please enter MySQL password for \"$dbadmin_user\"\n";
	ReadMode('noecho');
	my $password=ReadLine(0);
	ReadMode('restore');
	chomp $password;
	return $password;
}

sub create_database{
	return execute_mysql( "" , $mysqlserver , $username , $password , "create database $database" );
}

sub import_database{
	my $command = qq[ mysql -u "$username" -p"$password" -h "$mysqlserver" "$database" ];
	open(MYSQL, "| $command") or die $!;
	if( ! -e $file) { print "Import file does not exist.\n"; exit 11; }
	if(defined $file) {
		open(SQLCMDS, "<$file") or die $!;
		while(<SQLCMDS>) {
			print MYSQL $_;
		}
		close(SQLCMDS);
	} else {
		while(<>) {
			print MYSQL $_;
		}
	}
	close(MYSQL);
}


sub import_text{
	if (!defined $file) {
		$file="-";
	}
	my $id;
	my $kat;
	my $sorter;
	my $query;
	my $dbh;
	my $sth;
	my $dups_count=0;
	my $row_hash;

	if (defined $trusted_sorters) {
		# overridden by command line, do nothing
	} elsif (defined $config{'trusted_sorters'}) {
		$trusted_sorters = $config{'trusted_sorters'};
	} else {
		$trusted_sorters="";
		print STDERR "Warning: Config option trusted_sorters not set, no trusted sorter.\n";
		print STDERR "         If you want to change this, run fontlinge_config again or\n";
		print STDERR "         use the --trusted-sorters= option.\n";
	}
	$trusted_sorters =~  s/[^a-zA-Z0-9,]//g; # restrict allowed characters
	$trusted_sorters =~  s/,/|/g;
	
	$trusted_sorters="^($trusted_sorters)\$";

	open LESEN,"<$file" or die "file $file not readable.\n";
		my @lines=<LESEN>;
	close LESEN;
	if(defined $reject_log) {open SCHREIBEN, ">$reject_log"; print "Logging to $reject_log\n";}
	$dbh = DBI->connect("DBI:mysql:database=$database;host=$mysqlserver", "$username", "$password" ) || die "Cannot connect to database";;
	foreach my $i (@lines) {
		chomp $i; 
		($id,$kat,$sorter)=split("\t","$i\t\t");
		if ( ($id eq '') || ($kat eq '') || ($sorter eq '') ) {
			print "skipped empty ressource $id,$kat,$sorter\n";
		} else {
			local $dbh->{RaiseError};
			local $dbh->{PrintError};
			$sth = $dbh->prepare('INSERT INTO look (look_name,look_kategorie,look_creator) values(?,?,?)' , {RaiseError => 0, PrintError => 0} );
			$sth->execute($id,$kat,$sorter);
			if(defined $dbh->err) {
				if ($dbh->err == 1062) {
					# duplicate entry
					if ($overwrite_duplicates) {
						$sth = $dbh->prepare('UPDATE look SET look_name=?,look_kategorie=?,look_creator=? WHERE look_name=?');
						$sth->execute($id,$kat,$sorter,$id);
						# "old" reject_log format when --overwrite-duplicates is given
						if(defined $reject_log) {
							print SCHREIBEN "$id\t$kat\t$sorter\n";
						}
					} else {
						# check if is a real duplicate or if it differs somewhere
						$sth = $dbh->prepare("SELECT look_name,look_kategorie,look_creator FROM look WHERE look_name=? LIMIT 1");
						$sth->execute($id);
					
						$row_hash = $sth->fetchrow_hashref;
						$sth->finish(); # fetch "EOF" to avoid a warning
						if ($kat ne $row_hash->{"look_kategorie"}) {
							# different category
							$row_hash->{"look_kategorie_new"} = $kat;
						}
						if ($sorter ne $row_hash->{"look_creator"}) {
							# different sortername
							$row_hash->{"look_creator_new"} = $sorter;
						}
						if (defined $row_hash->{"look_kategorie_new"} || defined $row_hash->{"look_creator_new"}) {
							# sortlist entry differs from the existing entry in database
							if (!defined $row_hash->{"look_kategorie_new"}) {
								 $row_hash->{"look_kategorie_new"} = $row_hash->{"look_kategorie"};
								 $row_hash->{"look_kategorie"} = "";
							}
							if (!defined $row_hash->{"look_creator_new"}) {
								 $row_hash->{"look_creator_new"} = $row_hash->{"look_creator"};
								 $row_hash->{"look_creator"} = "";
							}
							if (
								$row_hash->{"look_creator_new"} =~ /$trusted_sorters/ &&
								$row_hash->{"look_creator"} =~ /^$|$trusted_sorters/
							) {
								# trusted sorter in database AND sortlist, replace entry
								$sth = $dbh->prepare('UPDATE look SET look_name=?,look_kategorie=?,look_creator=? WHERE look_name=?');
								$sth->execute($id,$kat,$sorter,$id);
							} else {
								# not a trusted sorter
								$dups_count++;
								if(defined $reject_log) {
									# print really different lines
									# new log format: (spaces are \t each)
									# Note: with --overwrite-duplicates, the old format is still used
			
									# look_name look_kategorie_new look_creator_new || look_kategorie look_creator
									#           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
									#           NEW values - just use them with     |  for information: OLD values
									#           --overwrite-duplicates              |
									#                                               `----- just a nice divider ;-)
	
									print SCHREIBEN "$row_hash->{look_name}\t$row_hash->{look_kategorie_new}\t$row_hash->{look_creator_new}";
									print SCHREIBEN "\t||\t$row_hash->{look_kategorie}\t$row_hash->{look_creator}\n";
								}
							}
						}
					}
				} else {
					print $dbh->err;
					print "\t --- \t";
					print $dbh->errstr;
					print "\n";
				}
			}
		}
	}
	if(defined $reject_log) {close SCHREIBEN;}

	$dbh->disconnect();
	if ($dups_count > 0) {
		print "Skipped $dups_count duplicate entries.\n";
	}
}


sub export_database{
	my $what=shift(@_);
	my $create;
	
	if ($what ne '') {
		$what='-w "'.quotemeta($what).'"';
		$create="mysqldump $what --add-locks --add-drop-table --all --allow-keywords -u \"".quotemeta($username)."\" -p\"".quotemeta($password)."\" -h \"".quotemeta($mysqlserver)."\" -O net_buffer_length=65536 \"".quotemeta($database)."\" look";
	} else {
		$create="mysqldump       --add-locks --add-drop-table --all --allow-keywords -u \"".quotemeta($username)."\" -p\"".quotemeta($password)."\" -h \"".quotemeta($mysqlserver)."\" -O net_buffer_length=65536 \"".quotemeta($database)."\"";
	}
	
	print `$create`;
}




sub export_text{
	my $row_hash;
	my $dbh;
	my $sth;
	my $what=shift(@_);

	if ($what ne '') {
		$what="WHERE $what";
	}

	$dbh = DBI->connect("DBI:mysql:database=$database;host=$mysqlserver", "$username", "$password" ) || die "Cannot connect to database";;

	$sth = $dbh->prepare("SELECT look_name,look_kategorie,look_creator FROM look $what");
	$sth->execute();

	while($row_hash = $sth->fetchrow_hashref) {
		print "$row_hash->{look_name}\t$row_hash->{look_kategorie}\t$row_hash->{look_creator}\n";
	}
	$dbh->disconnect();
}

sub phoenix {
	my $ret;
	my $lastret;
	my $sqldump = $fontlinge::Config{'docdir'} . "/empty.SQL";

	$ret=2006;
	while ($ret != 0) {
		print "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n";
		$lastret=$ret;
		print "Executing $me --check :\n\n";
		system(($me, "--check")); $ret=$?>>8;
		print "Got $ret...\n\n\n";
		if	($ret == 5)	{	print "\n\n\n\nLooking for a file '$file_uncompressed' in this folder to import it...\nThis will fail if it does not exist.\n\n";	}
		if($lastret == $ret) {
			print "Autocreating database failed.\n";
			print "Use --check manually.\n";
			exit 9;
		}
		if	($ret == 0)	{	print "Success!\n"; exit;}
		if	($ret ==66)	{	print "\n\nUncatched error. Try manually and please bugreport\n"; exit 66;}
		if	($ret == 1)	{	exit 1;} # duplikate command? Ha? Error already printed, fail silently
		if	($ret == 2)	{	exit 2;} # Host not localhost and not known, error already printed, fail silently
		if	($ret == 3)	{	system(($me, "--create-user"));}
		if	($ret == 4)	{	system(($me, "--import-database", $sqldump)); }
		if	($ret == 5)	{	if( download() !=1 ) {exit 5;} else {system(($me, "--import-text", $file_uncompressed))};}
		if	($ret == 6)	{	print "\n\nUncatched MySQL error. Try manually and please bugreport\n"; exit 6;}
		if	($ret == 7)	{	system(($me, "--create-database"));}
		if	($ret == 8)	{	print "There is no MySQL server."; exit 8;}
		if	($ret ==10)	{	system(($me, "--create-user"));}
		if	($ret ==12)	{	system(($me, "--create-user"));}
		if	($ret ==14)	{	system(($me, "--update-database"));}

	}
}

sub download {
	# changes from here...

	#my THIS IS DEFINED GLOBALLY: $file_uncompressed	="2003_02_02.txt"; !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
	my $file_compressed		="$file_uncompressed.bz2";
	my $file_size_uncompressed=1248409;
	my $file_size_compressed=259456;
	my @server=split(',','heanet,easynews,keihanna,unc,cesnet,umn');
	my $path='http://##SERVER##.dl.sourceforge.net/sourceforge/fontlinge/##FILE##';
	my @tool=split("," , "lwp-download ##PATH##,lynx -source ##PATH## > ##FILE##,wget ##PATH##,curl -L -o '##FILE##' ##PATH##");
	# ...to here




	my $command_nr;
	my $command_count;
	my @command;
	my $command;
	my $tmp;

	# predeclaration of "local" subroutine: Handle bz2-file
	my $download_bunzip = sub{
		if( -f $file_compressed ) {
			if( -s $file_compressed == $file_size_compressed) {
				print "bunzip2 $file_compressed\n";
				print `bunzip2 $file_compressed`;
				return 1; 
			print "*** The compressed file was broken and has been deleted! ***\n";
			unlink $file_compressed; # This file is broken!
			}
		}
	};

	# predeclaration of "local" subroutine: Handle txt-file
	my $download_checkfortextfile = sub{
		if( -f $file_uncompressed ) { 
			if( -s $file_uncompressed == $file_size_uncompressed) {
				return 1; 
			}
			print "*** The uncompressed file was broken and has been deleted! ***\n";
			unlink $file_uncompressed; # This file is broken!
		}
		return 0;
	};

	if( ! `which which` ) {print "\n\n\nYou must have 'which' installed!\n"; exit; }
	if( ! `which bunzip2` ) {print "\n\n\nYou must have 'bunzip2' installed!\n"; exit; }
	if( ! `which lwp-download` && ! `which lynx`&& ! `which wget` && ! `which curl`) {print "\n\n\nYou must have 'lwp-download' or 'lynx' or 'wget' or 'curl' installed!\n"; exit; }

	# create a list of all possible command/server combinations for download
	foreach my $s (@server) {
		foreach my $t (@tool) {
			$tmp="$t";
			$tmp=~s/##PATH##/$path/gs;
			$tmp=~s/##SERVER##/$s/gs;
			$tmp=~s/##FILE##/$file_compressed/gs;
			push @command , $tmp;
		}
	}

	# maybe the textfile is already here?
	if( &$download_checkfortextfile() ) {return 1;}

	# maybe the compressed file is already here?
	&$download_bunzip();
	if( &$download_checkfortextfile() ) {return 1;}

	# try different servers and commands in randomized order. Leave after success.
	$command_count=scalar(@command);
	for ( my $i=0 ; $i<$command_count ; $i++ ) {
		$command_nr = int(rand( scalar(@command) ));
		$command = $command[$command_nr];
		splice @command, $command_nr,1;
		print "$command\n";
		`$command`;
		&$download_bunzip();
		if( &$download_checkfortextfile() ) {return 1;}
	}
	return 0;
}



sub usage {
	print	<< 'END_OF_HELP'
USAGE

fontlinge_database_assistant --check
Can we connect to the database?

or

fontlinge_database_assistant --update-database
Update database from older Fontlinge versions.

or

fontlinge_database_assistant --create-user [--mysqlclient=clienthost]
   [--dbadmin-user=SqlAdmin] [--dbadmin-password=MySqlRootPassword]
Create the user with login and password from ~/.fontlinge
Note that the MySql-rootpassword may differ from 'normal' root-password.
mysqlclient is the ip or name of the client computer. This option must be
given if mysqlserver (in ~/.fontlinge) is not localhost. See INSTALL for
details.
SqlAdmin is the username of your MySql administrator (defaults to "root").
You also need --create-user if you change the database name after installation
or if --update-database doesn't work.

or
fontlinge_database_assistant --export-text
fontlinge_database_assistant --export-my-text
Export the look-database to STDOUT.
You can export the complete look-database (--export-text) or just the entries
with your sortername (--export-my-text).

or
fontlinge_database_assistant --import-text [database.txt] [--reject-log=FILE]
    [--trusted-sorters=[name1[,name2[,...]]]]
fontlinge_database_assistant --import-text --overwrite-duplicates [database.txt]
Generate look-database from textfile.
You may or may not overwrite existing entries.
Use --reject-log=FILE to get a list of rejected entries. Not useful with
'--overwrite-duplicates'
If no filename is given STDIN is used.
--trusted-sorters will override the trusted_sorters option in your configfile.
Database entries will be overwritten if the old and the new sortername are
trusted (even without --overwrite-duplicates!)

or
fontlinge_database_assistant --create-database
Create the empty database.

or
fontlinge_database_assistant --import-database file.SQL
Import an SQL-File to the database. Depending on the file DATA MAY BE DELETED.

or
fontlinge_database_assistant --export-database
Writes the complete database to STDOUT.

or
fontlinge_database_assistant --export-my-database
Writes your look-like-additions of the database to STDOUT.

or
fontlinge_database_assistant --download
Try to download a category database from internet.
Depends on one of some tools (lynx or lwp-download or ...)

or
fontlinge_database_assistant --phoenix
Create a working database with standard settings

END_OF_HELP
;
}

# vim:tabstop=8 shiftwidth=8
