#!/usr/bin/perl
#
# Create a table and fill it from a generic list.
#
# Use:
# ./InsertGenericList.pl DirectoryPath [TableName TableGlob IndexAttribute]
#
# DirectoryPath: The path to the directory that contains the list-files
# TableName: The name of the new table (defaults to the directory name)
# TableGlob: A glob for the list-files (defaults to '*.(txt|gz)')
# IndexAttribute: The name of the column which should be indexed (defaults to 'id')
#
# If you want to enter an optional value, you must specify all preceding optional
# values.
#
# The list-file format:
# Start with a series of lines of the following form (one for each column):
# "#> ColumnName\t[TYPE]\tDescription"
#
# Type should be one of the official PostgreSQL types (e.g., INT4, CHAR(2), TEXT)
# The description can be any kind of text, as long as it doesn't contain tabs.
#
# Then you can add comments. Every (other) line that starts with a plain '#' 
# will be ignored, empty lines are ignored too.
#
# Data lines are tab delimited relational table-records of the following form:
# "ValueColumn1\tValueColumn2\tValueColumn3\tValueColumn4\tValueColumn5\t.....\n";
#
# All values of a single record should fit on the same line.
#
# It is advisable to have a (unique) indexing column which can be used to
# identify individual records.
#
###############################################################################
#
# Copyright R.J.J.H. van Son © 2000, 2001, 2002
#
# 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";

# First argument
my $DatabaseDir = shift(@ARGV);
$DatabaseDir =~ s@/+\s*$@@g;  # remove final /
# The default table name is the directory name
$DatabaseDir =~ m!([^/]+)$!;

# More arguments or Default Values
my $TableName = shift(@ARGV) || $1;		# The default table name is the directory name
my $TableGlob = shift(@ARGV) || '*.gz';	# Just all text or zipped files
my $IndexAttribute = shift(@ARGV) || 'id';	# The column on which the index should be created

# The delimiter of the table lines
my $Delimiter = "\t";

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

my $Target = $MasterTableName;
my @FileList = ();

# Use arguments if given
@FileList = glob("$DatabaseDir/$TableGlob");

# Values to be calculated once per database!
my $DatabaseCreated = 0;
my @Columns = ();
my @ValueTypes = ();

my $FilePath;
foreach $FilePath (@FileList)
{
  print STDERR "File: $FilePath\n";

	if($FilePath =~ /\.gz/)
	{
	      open(INPUT, "gunzip -qc $FilePath|") || die "gunzip -qc $FilePath|: $!\n";
	}
	else
	{
	      open(INPUT, "<$FilePath") || die "<$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 .= "$Delimiter$'";
	   	     chomp($Header);
	     };
	};

	# Create database, unless this has already been done before
	unless($DatabaseCreated)
	{
		# Remove old table
		$result = $conn->do("DROP TABLE $TableName");

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

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

		$DatabaseCreated = scalar(@Columns);
	};

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

		# Split the lines
		my @Values = split(/$Delimiter/, $Line);
        
		# Add '-quotes to char values
		my $InsertList = "";
		my $i;
		for($i=0; $i < scalar(@Values); ++$i)
		{
	        $Values[$i] =~ s/\'/\\\'/g;
			# Text values should be enclosed in ''-quotes, unless they are NULL
			$Values[$i] = "E'$Values[$i]'" if($ValueTypes[$i] =~ /char|text/i && $Values[$i] ne 'NULL');
            # Strange values
            if($ValueTypes[$i] =~ /char\(1\)/i && length($Values[$i]) > 3) # Includes ''
            {
                print STDERR localtime().": Value too long: $Values[$i] in $ValueTypes[$i]\n";
                print STDERR $Line, "\n";
		        # Read next line
		        $Line = <INPUT>;
                # Go to next round
                next LINE;
            };
			$Values[$i] = 'NULL' if($Values[$i] =~ /^[\s\+\-]+$/ && $ValueTypes[$i] !~ /char|text/i) ;
			$Values[$i] = 'NULL' unless $Values[$i] =~ /\S/;	# Ensure that there is some value
		};

		# Read next line
		$Line = <INPUT>;

		# Make sure NO values are entered that do NOT have a the first two entries
		next unless $Values[0] && $Values[1];

		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;
		};

		# Ignore empty or broken records 
		# (i.e., with an undefined value like '', or no value at all, or the first value starts with -)
		if(grep(/^(\'\'|\s*)$/, @Values[0..2]) || $Values[0] =~ /^\s*\-/)
		{
	        print STDERR "ERROR: broken record\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,
				": INSERT INTO $TableName VALUES ($InsertList);\n";

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

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

	close(INPUT);
};

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

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

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

# Test data
my @ary = $conn->selectrow_array("select count($IndexAttribute) from $TableName");

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

