#!/usr/bin/perl
#
# Add speaker 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 $TableName = 'subjects';
$HomeDir = '../../..';
require "$HomeDir/Links.pl";

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

my $SpeakerInfoFile = "$DatabaseTables/Recordings/ALLsubjects_file.txt";
open(INPUT, "<$SpeakerInfoFile") || die "$SpeakerInfoFile: $!\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);
     };
};

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

        print STDERR $conn->errstr, 
                        ": DROP TABLE $TableName ; $!\n" if $conn->err;
        

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

	# Add a column
	unshift(@Columns, 'Speaker');
	unshift(@ValueTypes, 'TEXT');

	# Construct table definition for CREATE
	my $i;
	$ColumnDef = "";

	for($i=0; $i < scalar(@Columns); ++$i)
	{
	        $Columns[$i] = "EndTime" if $Columns[$i] eq 'End';
		$ColumnDef .= "$Columns[$i] $ValueTypes[$i], \n";
	};
	$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 = scalar(@Columns);
};

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

	my @Values = split(' ', $Line);
	my $SpeakerID = $Values[0].$Values[1].$Values[2];
	unshift(@Values, $SpeakerID);
	
	# 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);");
	print STDERR $conn->errstr, 
		": INSERT INTO $TableName VALUES ($InsertList);\n" if $conn->err;
 

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

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

print STDERR $conn->errstr, 
	    ": create index ${TableName}_index on $TableName (speaker);\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: ",$conn->errstr, " ", scalar(@$ary), "\n";
