#!/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 $user = "root";
my $pw = "###########99";
my $first;
my $synonyms;
my $uid;

# DATA SOURCE NAME
my $dsn = "dbi:mysql:onetopp:localhost:3306";
my $dsn2 = "dbi:mysql:openthesaurus: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
	});

# PERL DBI CONNECT
my $connect2 = DBI->connect($dsn2, $user, $pw, 
	{
		mysql_auto_reconnect => 1, AutoCommit => 1, RaiseError => 0, PrintError => 1, mysql_enable_utf8 => 1
	});	
	
my $count = 1;
while($count<4000000){
	# LOOP THROUGH RESULTS
	# PREPARE THE QUERY
	my $query = "SELECT word FROM `term` WHERE `synset_id` = \"$count\" AND `language_id` = \"2\" ORDER BY word DESC";
	my $query_handle2 = $connect2->prepare($query);

	# EXECUTE THE QUERY
	$query_handle2->execute();

	# BIND TABLE COLUMNS TO VARIABLES
	$query_handle2->bind_columns(\$first);
	my $uniq_string;
	my @ar = ();
	while (my @row = $query_handle2->fetchrow_array) {  # retrieve one row
		push @ar, @row;
	}
	$uniq_string = join(";", sort(@ar));
	print "\t\t$count -> $uniq_string\n";

	# select "$first" from synonym_unique
	my $query_unique = "SELECT word,synonyms FROM synonym_unique_simple 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 	= $uniq_string;
	my $synonym_first;
	while (my @results  = $sth->fetchrow()) {
		$word_first 	= $results[0];
		$synonym_first 	= $results[1];
		print "\t\t$count -> $synonym_first\n";
		
		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(";",$uniq_string);
			@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) };
	print "SQL: $query1\n\n\n";	
	sleep 1;
	$count++;
}