#!/usr/bin/perl
#### https://www.openthesaurus.de/about/download
#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_openthesaurus` (
 # `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 = "openthesaurus";
my $host = "localhost";
my $port = "3306";
my $tablename = "openthesaurus";	# 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:openthesaurus:localhost:3306";
my $dsn2 = "dbi:mysql:onetopp: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
	});
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_handle = $connect->prepare($query);

	# EXECUTE THE QUERY
	$query_handle->execute();

	# BIND TABLE COLUMNS TO VARIABLES
	$query_handle->bind_columns(\$first);
	my $unique_string;
	my @ar = ();
	while (my @row = $query_handle->fetchrow_array) {  # retrieve one row
		push @ar, @row;
	}
	$unique_string = join(";", sort(@ar));
	print "$count -> $unique_string\n";

	my $query1 = sprintf("%s (%s, %s, %s)",
		"INSERT INTO synonym_openthesaurus (word,synonyms,uid) VALUES",
		$connect2->quote($first),
		$connect2->quote($unique_string),
		$connect2->quote($count)
	);
	eval { $connect2->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 ;
