#!/usr/bin/perl
#
# Add Tier info to Database 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.
#
#
#######################################################
#
$HomeDir = '../../..';
require "$HomeDir/Links.pl";

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

$DatabaseTables = "$HomeDir/home/Shadowing/DBMStables";
my $Target = "shadow";

@ARGV = ('phonemes', 'syllparts', 'syllables', 'lexsyll', 'words', 'cgn', 'lexcgn'
, 'translit') unless @ARGV;

my $Tier;
foreach $Tier (@ARGV)
{

      my $DatabaseCreated = 0;

      $Tier = uc($Tier);

      my $TableName = "${Tier}$Target";

      my @Columns = ();
      my @ValueTypes = ();
      # Limit the glob to 4 character speaker ID codes to prevent name-in-name errors
      my @FileList = glob("$DatabaseTables/[FM][0-9][0-9][A-Z]$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");

                   print STDERR $conn->errstr,
                                   ":\nDROP TABLE $TableName;\n" if $conn->err;
                        
		   # Create new table	
		   # Column names
		   @Columns = split(/[\t]+/, $Header);
		   @ValueTypes = map {/\[([^\]]+)\]/; $1;} @Columns;
# Kludge to correct header error
@ValueTypes = map {s/INT1/INT2/g; $_;} @ValueTypes;
		   @Columns = map {/\s+\[([^\]]+)\]/i; $`;} @Columns;

		   # 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, 
				   ":\nCREATE TABLE $TableName ($ColumnDef);\n" if $conn->err;

		   $DatabaseCreated = scalar(@Columns);
	   };

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

		   my @Values = split(/\t/, $Line);

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

		   # Read next line
		   $Line = <INPUT>;

		   if(scalar(@Values) != $DatabaseCreated)
		   {
	     	  print STDERR "ERROR: ", scalar(@Values), " found where $DatabaseCreated expected\n";
		      print STDERR join("\t", @Columns), "\n";
		      print STDERR join("\t", @Values), "\n";

		      next;
		   };

		   # 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, 
				   ":\nINSERT INTO $TableName VALUES ($InsertList);\n";

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

			   die "$ColumnDef\n";
		   }; 
	   };

      };

      # 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 anonymous;");
      print "GRANT SELECT on $TableName TO anonymous: ", $conn->errstr, "\n";

      my @ary = $conn->selectrow_array("SELECT count(id) FROM $TableName;");

      print "Query $TableName: ", $conn->status, " ", $ary[0], "\n";

};