###########################################################################
#
# dbutil.pm -- utility functions for writing to different databases
# Copyright (C) 2008  DL Consulting Ltd
#
# A component of the Greenstone digital library software
# from the New Zealand Digital Library Project at the 
# University of Waikato, New Zealand.
#
# 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., 675 Mass Ave, Cambridge, MA 02139, USA.
#
###########################################################################

package dbutil;

use strict;


sub close_infodb_write_handle
{
    my $infodb_type = shift(@_);
    my $infodb_handle = shift(@_);

    if ($infodb_type eq "sqlite")
    {
	return &close_infodb_write_handle_sqlite($infodb_handle);
    }

    # Use GDBM if the infodb type is empty or not one of the values above
    return &close_infodb_write_handle_gdbm($infodb_handle);
}


sub get_default_infodb_type
{
    return "gdbm";
}


sub get_infodb_file_path
{
    my $infodb_type = shift(@_);
    my $collection_name = shift(@_);
    my $infodb_directory_path = shift(@_);

    if ($infodb_type eq "sqlite")
    {
	return &get_infodb_file_path_sqlite($collection_name, $infodb_directory_path);
    }

    # Use GDBM if the infodb type is empty or not one of the values above
    return &get_infodb_file_path_gdbm($collection_name, $infodb_directory_path);
}


sub open_infodb_write_handle
{
    my $infodb_type = shift(@_);
    my $infodb_file_path = shift(@_);

    if ($infodb_type eq "sqlite")
    {
	return &open_infodb_write_handle_sqlite($infodb_file_path);
    }

    # Use GDBM if the infodb type is empty or not one of the values above
    return &open_infodb_write_handle_gdbm($infodb_file_path);
}


sub read_infodb_file
{
    my $infodb_type = shift(@_);
    my $infodb_file_path = shift(@_);
    my $infodb_map = shift(@_);

    if ($infodb_type eq "sqlite")
    {
	return &read_infodb_file_sqlite($infodb_file_path, $infodb_map);
    }

    # Use GDBM if the infodb type is empty or not one of the values above
    return &read_infodb_file_gdbm($infodb_file_path, $infodb_map);
}


sub write_infodb_entry
{
    my $infodb_type = shift(@_);
    my $infodb_handle = shift(@_);
    my $infodb_key = shift(@_);
    my $infodb_map = shift(@_);

    if ($infodb_type eq "sqlite")
    {
	return &write_infodb_entry_sqlite($infodb_handle, $infodb_key, $infodb_map);
    }

    # Use GDBM if the infodb type is empty or not one of the values above
    return &write_infodb_entry_gdbm($infodb_handle, $infodb_key, $infodb_map);
}



# ----------------------------------------------------------------------------------------
#   GDBM IMPLEMENTATION
# ----------------------------------------------------------------------------------------

sub close_infodb_write_handle_gdbm
{
    my $infodb_handle = shift(@_);

    close($infodb_handle);
}


sub get_infodb_file_path_gdbm
{
    my $collection_name = shift(@_);
    my $infodb_directory_path = shift(@_);

    my $infodb_file_extension = (&util::is_little_endian() ? ".ldb" : ".bdb");
    my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
    return &util::filename_cat($infodb_directory_path, $infodb_file_name);
}


sub open_infodb_write_handle_gdbm
{
    my $infodb_file_path = shift(@_);

    my $txt2db_exe = &util::filename_cat("$ENV{'GSDLHOME'}/bin/$ENV{'GSDLOS'}", "txt2db" . &util::get_os_exe());
    my $infodb_file_handle = undef;
    if (!-e "$txt2db_exe" || !open($infodb_file_handle, "| \"$txt2db_exe\" \"$infodb_file_path\""))
    {
	return undef;
    }

    return $infodb_file_handle;
}


sub read_infodb_file_gdbm
{
    my $infodb_file_path = shift(@_);
    my $infodb_map = shift(@_);

    open (PIPEIN, "db2txt \"$infodb_file_path\" |") || die "couldn't open pipe from db2txt\n";
    my $infodb_line = "";
    my $infodb_key = "";
    my $infodb_value = "";
    while (defined ($infodb_line = <PIPEIN>))
    {
	if ($infodb_line =~ /^\[([^\]]+)\]$/)
	{
	    $infodb_key = $1;
	}
	elsif ($infodb_line =~ /^-{70}$/)
	{
	    $infodb_map->{$infodb_key} = $infodb_value;
	    $infodb_key = "";
	    $infodb_value = "";
	}
	else
	{
	    $infodb_value .= $infodb_line;
	}
    }

    close (PIPEIN);
}

    
sub write_infodb_entry_gdbm
{
    my $infodb_handle = shift(@_);
    my $infodb_key = shift(@_);
    my $infodb_map = shift(@_);

    print $infodb_handle "[$infodb_key]\n";
    foreach my $infodb_value_key (keys(%$infodb_map))
    {
	foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
	{
	    if ($infodb_value =~ /-{70,}/)
	    {
		# if value contains 70 or more hyphens in a row we need to escape them
		# to prevent txt2db from treating them as a separator
		$infodb_value =~ s/-/&\#045;/gi;
	    }
	    print $infodb_handle "<$infodb_value_key>" . $infodb_value . "\n";
	}
    }
    print $infodb_handle '-' x 70, "\n";
}



# ----------------------------------------------------------------------------------------
#   SQLITE IMPLEMENTATION
# ----------------------------------------------------------------------------------------

sub close_infodb_write_handle_sqlite
{
    my $infodb_handle = shift(@_);

    # Close the transaction we began after opening the file
    print $infodb_handle "END TRANSACTION;\n";

    # This is crucial for efficient queries on the database!
    print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n";

    close($infodb_handle);
}


sub get_infodb_file_path_sqlite
{
    my $collection_name = shift(@_);
    my $infodb_directory_path = shift(@_);

    my $infodb_file_extension = ".db";
    my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
    return &util::filename_cat($infodb_directory_path, $infodb_file_name);
}


sub open_infodb_write_handle_sqlite
{
    my $infodb_file_path = shift(@_);

    my $sqlite3_exe = &util::filename_cat("$ENV{'GSDLHOME'}/bin/$ENV{'GSDLOS'}", "sqlite3" . &util::get_os_exe());
    my $infodb_handle = undef;
    if (!-e "$sqlite3_exe" || !open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\""))
    {
	return undef;
    }

    print $infodb_handle "CREATE TABLE IF NOT EXISTS data (key TEXT PRIMARY KEY, value TEXT);\n";
    print $infodb_handle "CREATE TABLE IF NOT EXISTS document_metadata (id INTEGER PRIMARY KEY, docOID TEXT, element TEXT, value TEXT);\n";

    # This is crucial for efficiency when importing large amounts of data
    print $infodb_handle "CREATE INDEX IF NOT EXISTS dmd ON document_metadata(docOID);\n";

    # This is very important for efficiency, otherwise each command will be actioned one at a time
    print $infodb_handle "BEGIN TRANSACTION;\n";

    return $infodb_handle;
}


sub read_infodb_file_sqlite
{
    my $infodb_file_path = shift(@_);
    my $infodb_map = shift(@_);

    # !! TO IMPLEMENT
}


sub write_infodb_entry_sqlite
{
    my $infodb_handle = shift(@_);
    my $infodb_key = shift(@_);
    my $infodb_map = shift(@_);

    # Add the key -> value mapping into the "data" table
    my $infodb_entry_value = "";
    foreach my $infodb_value_key (keys(%$infodb_map))
    {
	foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
	{
	    $infodb_entry_value .= "<$infodb_value_key>" . $infodb_value . "\n";
	}
    }

    my $safe_infodb_key = &sqlite_safe($infodb_key);
    print $infodb_handle "INSERT OR REPLACE INTO data (key, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_entry_value) . "');\n";

    # If this infodb entry is for a document, add all the interesting document metadata to the
    #   "document_metadata" table (for use by the dynamic classifiers)
    if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
    {
	print $infodb_handle "DELETE FROM document_metadata WHERE docOID='" . $safe_infodb_key . "';\n";

	foreach my $infodb_value_key (keys(%$infodb_map))
	{
	    # We're not interested in most of the automatically added document metadata
	    next if ($infodb_value_key eq "archivedir" ||
		     $infodb_value_key eq "assocfilepath" ||
		     $infodb_value_key eq "childtype" ||
		     $infodb_value_key eq "contains" ||
		     $infodb_value_key eq "docnum" ||
		     $infodb_value_key eq "doctype" ||
		     $infodb_value_key eq "Encoding" ||
		     $infodb_value_key eq "FileSize" ||
		     $infodb_value_key eq "hascover" ||
		     $infodb_value_key eq "hastxt" ||
		     $infodb_value_key eq "lastmodified" ||
		     $infodb_value_key eq "metadataset" ||
		     $infodb_value_key eq "thistype" ||
		     $infodb_value_key =~ /^metadatafreq\-/ ||
		     $infodb_value_key =~ /^metadatalist\-/);

	    foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
	    {
		print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n";
	    }
	}
    }
}


sub sqlite_safe
{
    my $value = shift(@_);

    # Escape any single quotes in the value
    $value =~ s/\'/\'\'/g;

    return $value;
}


1;