#!/usr/bin/perl

#sleep 25000;
system("clear");
###########################
### Autor: Sebastian Enger / B.Sc. 
### Copyright: Sebastian Enger
### Licence: BSD
### Version: 1.1.b  - 20080716@19.Uhr
### Contact: sebastian.enger@gmail.com | icq: 135846444
### Latest Version: PENDRIVE:\Programming\Perl\IncrementalBackup
###########################

#CREATE TABLE `synonym_unique` (
 # `word` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 # `synonyms` mediumtext COLLATE utf8_unicode_ci NOT NULL,
 # `uid` bigint(50) NOT NULL
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

use strict;
use DBI;
use File::Find;	# perl -MCPAN -e 'force install "File::Find"'
use File::Copy;	# perl -MCPAN -e 'force install "File::Copy"'
use File::Path;	# perl -MCPAN -e 'force install "File::Path"'
#use XML::Code; # perl -MCPAN -e 'force install "Curses"'
use File::Basename;
use Data::Dumper;
use String::Random; # perl -MCPAN -e 'force install "String::Random"'
use Digest::SHA qw(sha512_hex);
use Encode qw(encode decode);
use Regexp::Common qw/whitespace/;

# CONFIG VARIABLES
my $platform = "mysql";
my $database = "onetopp";
my $host = "localhost";
my $port = "3306";
my $tablename = "synonym_non_unique";	# dort wo alle - auch doppelte syn einträge drin stehen
my $user = "root";
my $pw = "###########99";
my $first;
my $synonyms;
my $uid;

# DATA SOURCE NAME
my $dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT
my $connect = DBI->connect($dsn, $user, $pw, 
	{
		mysql_auto_reconnect => 1, AutoCommit => 1, RaiseError => 0, PrintError => 1, mysql_enable_utf8 => 1
	});
# PREPARE THE QUERY
my $query = "SELECT word,synonyms,uid FROM $tablename;";
my $query_handle = $connect->prepare($query);

# EXECUTE THE QUERY
$query_handle->execute();

# BIND TABLE COLUMNS TO VARIABLES
$query_handle->bind_columns(\$first, \$synonyms, \$uid);

my $count = 0;
# LOOP THROUGH RESULTS
while($query_handle->fetch()) {
	next if (length($first) <= 2);
	next if ($first =~ /(\+|\-|\,|\;|\|\?|\!)/);
	#print "$first, $synonyms, $uid \n";
	#$first =~ s/(\+|\-|\,|\;|\|\?|\!)/ /ig;
	#$first =~ s/[^\w\n]//g;
	#$first =~ s/^\s+|\s+$//g;
	#$first =~ s/(.)([A-Z][^A-Z])/$1 $2/g;
	chomp($first);
	print "($count) $uid -> $first\n";
	# http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
	
	#open(W,"+>>/home/100biere/sql.log");
	#print W "$uid -> $first\n";
	#close W;
	#next;

	# slect "$first" from synonym_unique
	my $query_unique 		= "SELECT word,synonyms FROM $tablename WHERE word=\"$first\"";	# final live
	my $sth = $connect->prepare($query_unique);     # prepare the query
	$sth->execute();                   				# execute the query with parameter
	
	my $word_first		= $first;
	my $unique_string 	= $synonyms;
	my $synonym_first;
	while (my @results  = $sth->fetchrow()) {
		$word_first 	= $results[0];
		$synonym_first 	= $results[1];
				
		if (($word_first =~ /(\w{2,})/ig) && ( length($word_first)>2) && ($synonym_first =~ /(\w{2,})/ig) && ( length($synonym_first)>3) ){
			my %unique;
			my $unique;
			
			my @syn_split_dubletten1 	= ();
			my @syn_split_dubletten2 	= ();
			my $unique_string 			= "";
			
			@syn_split_dubletten1 = split(";",$synonyms);
			@syn_split_dubletten2 = split(";",$synonym_first);
					
			foreach my $entry1 (@syn_split_dubletten1){
				$unique{lc($entry1)} = $entry1;
			}
			foreach my $entry2 (@syn_split_dubletten2){
				$unique{lc($entry2)} = $entry2;
			}
			
			while ( my ($key,$value) = each %unique) {
			  $unique_string .= $value.";";
			}
			#print "Final Output: $unique_string\n";
		}
	} # while (@results = $sth->fetchrow()) {
	
	my $query1 = sprintf("%s (%s, %s, %s)",
		"INSERT INTO synonym_unique_simple (word,synonyms,uid) VALUES",
		$connect->quote($first),
		$connect->quote($unique_string),
		$connect->quote($count)
	);
	eval { $connect->do($query1) };
		
	$count++;
} 

#CREATE TABLE IF NOT EXISTS `synonym_unique` (
#  `word` text COLLATE utf8_unicode_ci NOT NULL,
# `synonyms` mediumtext COLLATE utf8_unicode_ci NOT NULL,
#  `uid` bigint(50) NOT NULL AUTO_INCREMENT,
# PRIMARY KEY (`uid`)
#) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=359018 ;
