##
#
#    Copyright 2001-2007, AllAfrica Global Media
#
#    This file is part of XML::Comma
#
#    XML::Comma 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
#    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.
#
#    For more information about XML::Comma, point a web browser at
#    http://xml-comma.org, or read the tutorial included
#    with the XML::Comma distribution at docs/guide.html
#
##

# PORTING NOTES: started working on making the pg stuff work with
# textsearches, and stopped when it became clear that the "seqs"
# fields need to store arrays of numbers, rather than to be packed, as in mysql.

package XML::Comma::SQL::Pg;
use strict;

use XML::Comma::Util qw( dbg );
use MIME::Base64;

sub sql_create_hold_table {
  my ($self, $dbh) = @_;
  dbg 'creating hold table';
  $dbh->commit();
  my $sth = $dbh->prepare
    ( "CREATE TABLE comma_hold ( key VARCHAR(255) UNIQUE)" );
  $sth->execute();
  $sth->finish();
  $dbh->commit();
}

sub sql_get_hold {
  my ( $lock_singlet, $key ) = @_;
  my $dbh = $lock_singlet->get_dbh();
  my $q_lock_name = $dbh->quote ( $key );
  # dbg 'dbh', $dbh;
  $dbh->{AutoCommit}=0;
  $dbh->commit();
  my $sth = $dbh->prepare
    ( "INSERT INTO comma_hold (key) VALUES ($q_lock_name)" );
  $sth->execute();
  $sth->finish();
}

sub sql_release_hold {
  my ( $lock_singlet, $key ) = @_;
  my $dbh = $lock_singlet->get_dbh();
  my $q_lock_name = $dbh->quote ( $key );
  my $sth = $dbh->prepare ( "DELETE FROM comma_hold WHERE key = $q_lock_name" );
  $sth->execute();
  $sth->finish();
  $dbh->commit();
  $dbh->{AutoCommit}=1;
}

sub sql_create_index_tables_table {
my $index = shift();
my $sth = $index->get_dbh()->prepare (
"CREATE TABLE index_tables
  ( _comma_flag    INT2,
    _sq            SERIAL,
    doctype        VARCHAR(255),
    index_name     VARCHAR(255),
    table_name     VARCHAR(255),
    table_type     INT2,
    last_modified  INT,
    sort_spec      VARCHAR(255),
    textsearch     VARCHAR(255),
    collection     VARCHAR(255),
    index_def      TEXT )"
);
$sth->execute();
$sth->finish();
}


sub sql_sort_table_definition {
  return
"CREATE TABLE $_[1] (
  _comma_flag  INT2,
  doc_id ${ \( $_[0]->element('doc_id_sql_type')->get() ) } PRIMARY KEY )";
}


sub sql_data_table_definition {
  return
"CREATE TABLE $_[1] (
  _comma_flag             INT2,
  record_last_modified    INT4,
  _sq                     SERIAL,
  doc_id ${ \( $_[0]->element('doc_id_sql_type')->get() ) } PRIMARY KEY )";
}

sub sql_bcollection_table_definition {
  my ( $index, $name, %arg ) = @_;
  my $extra_column = '';
  if ( @{$arg{bcoll_el}->elements('field')} ) {
    $extra_column = ", extra " .
      $arg{bcoll_el}->element('field')->element('sql_type')->get();
  }

  return
"CREATE TABLE $name (
  _comma_flag  INT2,
  doc_id ${ \( $index->element('doc_id_sql_type')->get() ) },
  value   ${ \( $arg{bcoll_el}->element('sql_type')->get() ) }
  $extra_column
 );
 CREATE INDEX bci_$name ON $name (value)";
}

sub sql_textsearch_index_table_definition {
  my ($index, $name) = @_;
  use XML::Comma::Pkg::Textsearch::Preprocessor;
  my $max_length = $XML::Comma::Pkg::Textsearch::Preprocessor::max_word_length;
  return
  qq[
      CREATE TABLE $name (
        word  CHAR($max_length)  PRIMARY KEY,
        seqs  bytea )
  ];
}

sub sql_textsearch_defers_table_definition {
  my ($index, $name) = @_;
  my $doc_id_type = $index->element('doc_id_sql_type')->get();

  return
  qq[
      CREATE TABLE $name (
        doc_id        $doc_id_type,
        action        smallint,
        text          bytea,
        _sq           serial )
  ];
}

sub sql_textsearch_word_lock {
  my ( $index, $i_table_name, $word ) = @_;
  my $dbh = $index->get_dbh_writer();
  $dbh->{AutoCommit}=0;
  $dbh->commit();
  my $sth = $dbh->prepare
    ( "LOCK TABLE $i_table_name IN SHARE ROW EXCLUSIVE MODE" );
  $sth->execute();
  $sth->finish();
}

sub sql_textsearch_word_unlock {
  my ( $index, $i_table_name, $word ) = @_;
  my $dbh = $index->get_dbh_writer();
  #my $q_lock_name = $dbh->quote ( $i_table_name . $word );
  $dbh->commit();
  $dbh->{AutoCommit}=1;
  #$dbh->do ( "COMMIT WORK" );
}


sub sql_index_only_doc_id_type {
  return 'VARCHAR( 255 )';
}


# yech. should we be trying to use the non-standard array *= operators
# in postgres to do this textsearch stuff?
sub sql_textsearch_pack_seq_list {
  shift();
  return '' unless @_;
  return '-' . join ( '-', @_ ) . '-';
}

sub sql_textsearch_unpack_seq_list {
  my @ret = split ( '-', $_[1] );
  shift @ret;
  return @ret;
}

sub sql_textsearch_cat_seq_list {
  my ($self, $packed1, $packed2) = @_;
  chop $packed1;
  return $packed1 . $packed2;
}

sub sql_limit_clause {
  my ( $index, $limit_number, $limit_offset ) = @_;
  if ( $limit_number ) {
    if ( $limit_offset ) {
      return " LIMIT $limit_number OFFSET $limit_offset";
    } else {
      return " LIMIT $limit_number";
    }
  } else {
    return '';
  }
}

# sub sql_load_data {
#   my ( $index, $dbh, $temp_table_name, $temp_filename ) = @_;
#   $dbh->do ( "COPY $temp_table_name FROM '$temp_filename'" );
# }

sub sql_create_textsearch_temp_table_stmt {
  my $index = shift;
  my $dbh   = $index->get_dbh_writer();
  my $temp_table_name = '_temp_' . $$ . '_' . int(rand(0xffffffff));
  my $sth = $dbh->prepare_cached ( 
    qq[
        CREATE TEMPORARY TABLE $temp_table_name 
          ( id VARCHAR(255) PRIMARY KEY ) 
      ]
  );
  $sth->execute();
  $sth->finish();
  return $temp_table_name;
}

sub sql_load_data {
  my ( $index, $temp_table_name, $temp_filename ) = @_;
  my $dbh = $index->get_dbh_writer();
  my $sth = $dbh->prepare_cached ( 
    qq[
        COPY $temp_table_name FROM '$temp_filename'
      ]
  );
  $sth->execute();
  $sth->finish();
}

1;