use strict;
use warnings;

use utf8;

use DBI;
use IO::Compress::Gzip qw(gzip $GzipError) ;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError) ;

sub compressor;
sub uncompressor;

print "\nTesting 'compressor' and 'uncompressor' subs: " . uncompressor(compressor("test passed")) . "\n";

my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db",
							"",
							"", {
								# RaiseError => 1,		# we'll handle the errors ourselves
								sqlite_unicode => 1,
							}
						) or die "Can't connect to database: $DBI::errstr";;					# database connection var


$dbh->sqlite_create_function( 'compressme', 1, sub { compressor } );
$dbh->sqlite_create_function( 'uncompressme', 1, sub { uncompressor } );

$dbh->do("DROP TABLE IF EXISTS EnHu") or print "drop table failed";
# $dbh->do("CREATE VIRTUAL TABLE EnHu USING fts4 (English TEXT, Hungarian TEXT)") or print "failed to create fts4 table";		# uncompressed
$dbh->do("CREATE VIRTUAL TABLE EnHu USING fts4 (English TEXT, Hungarian TEXT, compress=compressme, uncompress=uncompressme)") or print "failed to create fts4 table"; # compressed


while (<DATA>) {
	chomp;
	/^([^\t]*)\t([^\t]*)/;
	my $lang1 = $1;
	my $lang2 = $2;
	# print "\n\ninserting:\n$lang1\n$lang2";
	
	my $insert = $dbh->prepare('INSERT INTO EnHu (English, Hungarian) VALUES(?, ?)');
	# my $insert = $dbh->prepare('INSERT INTO EnHu (English, Hungarian) VALUES(compressme(?), compressme(?))');	#no need to call the functions, SQLite seems to do it on its own
	$insert->execute("$lang1", "$lang2");
	
	
	
}

my $countentries = $dbh->prepare( "SELECT Count(*) FROM EnHu" ); # OK
$countentries->execute();
my $size = $countentries->fetch();

my $searchterm = "some";
print "\n\nImport done, the db contains @$size records.\nEnter search term to run query\n";
chomp($searchterm = <STDIN>);



my $q_1 = $dbh->prepare( "SELECT * FROM EnHu WHERE English MATCH ?" ) or print "prepare failed";	# works on uncompressed db, kinda works on compressed db
# my $q_1 = $dbh->prepare( "SELECT uncompressme(English) FROM EnHu WHERE English MATCH ?" ) or print "prepare failed";	# doesn't work
# my $q_1 = $dbh->prepare( "SELECT uncompressme(English),uncompressme(Hungarian) FROM EnHu WHERE English MATCH ?" ) or print "prepare failed";
# my $q_1 = $dbh->prepare( "SELECT uncompressme(English) FROM EnHu WHERE English MATCH ?" ) or print "prepare failed";
# my $q_1 = $dbh->prepare( "SELECT * FROM EnHu WHERE uncompressme(English) MATCH ?" );	# doesn't work
# my $q_1 = $dbh->prepare( "SELECT * FROM EnHu WHERE English MATCH compressme(?)" );	# nope


$q_1->execute($searchterm) or print "execute failed";

# my $q_2 = $dbh->prepare( "SELECT * FROM EnHu LIMIT 4" );
# $q_2->execute() or print "execute failed";
# my $all = $q_2->fetchall_arrayref() or print "fetch failed";

my $all = $q_1->fetchall_arrayref() or print "fetch failed";

my $i = @$all;
foreach my $row (@$all) {
		print "\n--------------------\n";
		foreach my $item (@$row) {
			print $item;
			print "\t";
			
		};
		
	}
	print "\n\n";
print "\n$i matches found.";


<STDIN>;

	sub compressor {
		my $in = shift;
		my $out;
		gzip \$in => \$out;
		return ($out);
	}
	sub uncompressor {
		my $in = shift;
		my $out;
		gunzip \$in => \$out;
		return ($out);
	}
	



__DATA__
this is some text	némi szöveg
some more text here	na még egy kicsi
just for testing you know	nalássuk
fourth row of text	még némi szöveg
and a little more text for good measure	dobjuk hozzá még ezt is