#!/usr/bin/perl
#
# Add Text info to table
#
###############################################################################
#
# Copyright R.J.J.H. van Son © 2000, 2001
#
# Author Rob van Son
# Institute of Phonetic Sciences & ACLC
# University of Amsterdam
# Herengracht 338
# NL-1016CG Amsterdam, The Netherlands
# Email: Rob.van.Son@hum.uva.nl
#        rob.van.son@workmail.com
# WWW  : http://www.fon.hum.uva.nl/rob/
# mail:  Institute of Phonetic Sciences
#        University of Amsterdam
#        Herengracht 338
#        NL-1016CG Amsterdam
#        The Netherlands
#        tel +31 205252183
#        fax +31 205252197
#
# License for use and disclaimers
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#
#######################################################
#
my $DatabaseCreated = 0;
$TableName = 'texts';

my $Columndef = "";
my @Columns = ();
my @ValueTypes = ();

$HomeDir = '../../..';
require "$HomeDir/Links.pl";
my $DatabaseDir = "$DatabaseTables/Texts";
my $TranscriptionsDir = "$HomeDir/Transcriptions";

# Remove password for writing access
$DBMSpassword = '';
require "$HomeDir/connectDBD.pl";

my @FileList = glob("$DatabaseDir/*$TableName.txt.gz");
foreach $FilePath (@FileList)
{

    open(INPUT, "gunzip -qc $FilePath|") || die "gunzip -qc $FilePath|: $!\n";

    # Get the first line
    my $Header = <INPUT>;
    chomp($Header);
    # Remove leading #
    $Header =~ s/^\s*\#[\>]?\s*//g;

    # Skip comments
    while(($Line = <INPUT>) =~ /^\s*(\#[\>]?\s*|$)/)
    { 
     	    if($Line =~ /^\s*\#[\>]\s*/)
     	    {
	     	    $Header .= "\t$'";
	     	    chomp($Header);
     	    };
    };

    unless($DatabaseCreated)
    {
	    # Remove old table
	    $result = $conn->do("DROP TABLE $TableName");

	    # Create new table	
	    # Column names
	    @Columns = split(/[\t]+/, $Header);
	    @ValueTypes = map {/\[([^\]]+)\]/; $1;} @Columns;
	    @Columns = map {/\s+\[([^\]]+)\]/i; $`;} @Columns;

	    # Add a column
	    push(@Columns, 'Text');
	    push(@ValueTypes, 'TEXT');


	    # Construct table definition for CREATE
	    my $i;
	    $ColumnDef = "";
	    for($i=0; $i < scalar(@Columns); ++$i)
	    {
		    $ColumnDef .= "$Columns[$i] $ValueTypes[$i], ";
	    };
	    $ColumnDef =~ s/\,\s*$//g;
	    # Create table
	    $result = $conn->do("CREATE TABLE $TableName ($ColumnDef);");

	    print STDERR $conn->errstr, 
			    ": CREATE TABLE $TableName ($ColumnDef);\n" if $conn->err;

	    $DatabaseCreated = 1;
    };

    # Fill Table (NOTE: the first line has already been read!)
    while($Line)
    {
	    # Skip comments and empty lines
	    ($Line = <INPUT>, next) if $Line =~ /^\s*(\#|$)/;

	    my @Values = split(' ', $Line);

	    # Get the real text from the files
	    my $ID = $Values[0];
	    my $Speaker = $Values[1];
	    my $FileName = "$TranscriptionsDir/$Speaker/$ID.txt";
	    my $CurrentText = "";
	    if(-e "$FileName")
	    {
		    open(TEXTFILE, "<$FileName") || die "<$FileName: $!\n";
		    $CurrentText = join("", <TEXTFILE>);
		    $CurrentText =~ s/\s/ /g;
		    $CurrentText =~ s/[\'\"]/\`/g;
		    $CurrentText =~ s/[ийкл]/e/ig;
		    $CurrentText =~ s/[абвгде]/a/ig;
		    $CurrentText =~ s/[мноп]/i/ig;
		    $CurrentText =~ s/[туфхц]/o/ig;
		    $CurrentText =~ s/[щъыь]/u/ig;
#		    $CurrentText =~ tr [\200-\377] [\000-\177];
		    $CurrentText =~ s/[\x00-\x1F\x7F-\xFF]/_/g;
	    };
	    push(@Values, $CurrentText);

	    # Add '-quotes to char values
	    my $InsertList = "";
	    my $i;
	    for($i=0; $i < scalar(@Values); ++$i)
	    {
		    $Values[$i] = "E'$Values[$i]'" if($ValueTypes[$i] =~ /char|text/i && $Values[$i] ne 'NULL');
	    };

	    # Construct the insert list
	    my $InsertList = join(", ", @Values);

	    # Insert the record
	    $result = $conn->do("INSERT INTO $TableName VALUES ($InsertList);");
	    if($conn->err)
	    {
		    print STDERR $conn->errstr, 
			    ": INSERT INTO $TableName VALUES ($InsertList);\n";

		    while(@Values || @ValueTypes)
		    {
			    print shift(@Values), " ", shift(@ValueTypes), "\n";
		    };

		    die "$ColumnDef\n";
	    }; 

	    # Read next line
	    $Line = <INPUT>;
    };
    
    close(INPUT);

};

# Create indices on ID
$result = $conn->do("CREATE INDEX ${TableName}_index ON $TableName (id);");

print STDERR $conn->errstr, 
	    ": create index ${TableName}_index on $TableName (id);\n" if $conn->err;

# Grant SELECT to user ANONYMOUS
$conn->do("GRANT SELECT on $TableName TO PUBLIC");
print "GRANT SELECT on $TableName TO PUBLIC: ",$conn->errstr, "\n";

my $ary = $conn->selectall_arrayref("select * from $TableName");

print "Query $TableName: ",$conn->errstr, " ", scalar(@$ary), "\n";

