#!/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
###########################

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);

# CONFIG VARIABLES
my $platform = "mysql";
my $database = "onetipp";
my $host = "localhost";
my $port = "3306";
my $tablename = "synonym_table";	# 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, 
	{
		RaiseError => 0, PrintError => 1, mysql_enable_utf8 => 1
	});

# PREPARE THE QUERY
my $query = "SELECT word,synonyms 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);

my $count = 0;
# LOOP THROUGH RESULTS
while($query_handle->fetch()) {
	#print "$first, $synonyms, $uid \n";
	$first =~ s/(\+|\-|\,|\;)/ /ig;
	# http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
	
	# slect "$first" from synonym_unique
	my $query_unique 		= "SELECT word,synonyms FROM synonym_unique WHERE word=\"$first\" LIMIT 1;";	# final live
	#my $query_unique 		= "SELECT word,synonyms FROM $tablename WHERE word=\"$first\" LIMIT 1;";		# debug test
	#print "SQL: $query_unique\n";
	
	my $sth = $connect->prepare($query_unique);     # prepare the query
	$sth->execute();                   				# execute the query with parameter
	my @row = $sth->fetchrow_array;
	my ($word_first,$synonym_first ) = @row;

	if ($word_first	=~ /(\w{2,})/ig && $synonym_first =~ /(\w{2,})/ig ){
	
		print "$count) Double -> word_first: $word_first\n";
		
		my %unique;
		my $unique;
		
		my @syn_split_dubletten1 = split(";",$synonyms);
		my @syn_split_dubletten2 = split(";",$synonym_first);
		
		foreach my $entry1 (@syn_split_dubletten1){
			$unique{$entry1} = $entry1;
		}
		foreach my $entry2 (@syn_split_dubletten2){
			$unique{$entry2} = $entry2;
		}
		
		@syn_split_dubletten1 	= ();
		@syn_split_dubletten2 	= ();
		my $unique_string 		= "";
		
		while ( my ($key,$value) = each %unique) {
		  $unique_string .= $value.";";
		}
		#print "Final Output: $unique_string\n";
		
		my $query1 = sprintf("%s (%s, %s, %s)",
			"INSERT INTO synonym_unique (word,synonyms,uid) VALUES",
			$connect->quote($first),
			$connect->quote($unique_string),		
			$connect->quote(""),											
		);
		eval { $connect->do($query1) };
		
	} else { # no double
		# if ($word_first	=~ /(\w{2,})/ig && $synonym_first	!~ /(\w{2,})/ig ){
		print "$count) Normal insert -> word_first: $first\n";
		my $query2 = sprintf("%s (%s, %s, %s)",
			"INSERT INTO synonym_unique (word,synonyms,uid) VALUES",
			$connect->quote($first),
			$connect->quote($synonyms),		
			$connect->quote(""),											
		);
		eval { $connect->do($query2) };
	}
	$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 ;
