package Apache2::Translation::DB;

use 5.8.8;
use strict;
use warnings;
no warnings qw(uninitialized);

use DBI;
use Class::Member::HASH -CLASS_MEMBERS=>qw/database user password table
					   key uri block order action notes
					   cachesize cachetbl cachecol
					   singleton id is_initialized
					   seqtbl seqnamecol seqvalcol
					   idseqname dbinit
					   _existing_keys
					   _cache _cache_version _dbh/;
our @CLASS_MEMBERS;

our $VERSION = '0.06';

sub new {
  my $parent=shift;
  my $class=ref($parent) || $parent;
  my $I=bless {}=>$class;
  my $x=0;
  my %o=map {($x=!$x) ? lc($_) : $_} @_;

  if( ref($parent) ) {         # inherit first
    foreach my $m (@CLASS_MEMBERS) {
      $I->$m=$parent->$m;
    }
  }

  $I->cachesize=1000;
  $I->singleton=0;

  # then override with named parameters
  foreach my $m (@CLASS_MEMBERS) {
    $I->$m=$o{$m} if( exists $o{$m} );
  }

  $I->_existing_keys={};
  $I->_cache={};
  if( $I->cachesize=~/^\d/ ) {
    eval "use Tie::Cache::LRU";
    die "$@" if $@;
    tie %{$I->_cache}, 'Tie::Cache::LRU', $I->cachesize;
  }

  return $I;
}

sub connect {
  my $I=shift;

  my $dbh=$I->_dbh=DBI->connect( $I->database, $I->user, $I->password,
				{
				 AutoCommit=>1,
				 PrintError=>0,
				 RaiseError=>1,
				} );
  $dbh->do($I->dbinit) if( length $I->dbinit );
  return $dbh;
}

sub start {
  my $I=shift;
  unless( $I->_dbh and eval {$I->start_common} ) {
    $I->_dbh->disconnect if( $I->_dbh );
    $I->connect;
    $I->start_common;
  }
}

sub stop {
  my $I=shift;
  undef $I->_dbh if( !$I->singleton and
		     ($I->_dbh->isa( 'Apache::DBI::Cache::db' ) or
		      $I->_dbh->isa( 'Apache::DBI::db' )) );
}

sub start_common {
  my $I=shift;

  my ($cache_tbl,$cache_col)=map {$I->$_} qw/cachetbl cachecol/;

  my $sql=<<"SQL";
SELECT MAX($cache_col)
FROM $cache_tbl
SQL

  my $stmt=$I->_dbh->prepare_cached( $sql );
  $stmt->execute;
  my $cache_version=$stmt->fetchall_arrayref->[0]->[0];

  unless( $cache_version eq $I->_cache_version ) {
    %{$I->_cache}=();
    $I->_cache_version=$cache_version;

    my ($tbl, $key, $uri)=map {$I->$_} qw/table key uri/;
    $sql=<<"SQL";
SELECT DISTINCT $key, $uri FROM $tbl
SQL

    $stmt=$I->_dbh->prepare_cached( $sql );
    $stmt->execute;
    $I->_existing_keys=+{map {("$_->[0]\0$_->[1]"=>1)}
			 @{$stmt->fetchall_arrayref}};
  }

  return 1;
}

sub fetch {
  my $I=shift;
  my ($key, $uri, $with_notes)=@_;

  my $ref;
  my ($id_col, $notes_col)=($I->id, $I->notes);
  if( $with_notes and length $notes_col and length $id_col ) {
    my ($table_name,$key_col,$uri_col,$block_col,$order_col,$action_col)=
      map {$I->$_} qw/table key uri block order action/;

    my $sql=<<"SQL";
SELECT $block_col, $order_col, $action_col, $id_col, $notes_col
FROM $table_name
WHERE $key_col=?
  AND $uri_col=?
ORDER BY $block_col ASC, $order_col ASC
SQL

    my $stmt=$I->_dbh->prepare_cached( $sql );
    $stmt->execute( $key, $uri );
    $ref=$stmt->fetchall_arrayref;
    map {defined $_->[4] ? 1 : ($_->[4]='')} @{$ref};
  } else {
    my $k="$key\0$uri";
    return unless( exists $I->_existing_keys->{$k} );

    $ref=$I->_cache->{$k};
    unless( defined $ref ) {
      my ($table_name,$key_col,$uri_col,$block_col,$order_col,$action_col)=
	map {$I->$_} qw/table key uri block order action/;

      my $sql=<<"SQL";
SELECT $block_col, $order_col, $action_col@{[length $id_col ? ", $id_col" : ""]}
FROM $table_name
WHERE $key_col=?
  AND $uri_col=?
ORDER BY $block_col ASC, $order_col ASC
SQL

      my $stmt=$I->_dbh->prepare_cached( $sql );
      $stmt->execute( $key, $uri );
      $ref=$stmt->fetchall_arrayref;

      $I->_cache->{"$key\0$uri"}=$ref;
    }
  }
  return @{$ref};
}

sub can_notes {length $_[0]->notes;}

sub list_keys {
  my $I=shift;

  my ($table_name,$key_col)=map {$I->$_} qw/table key/;
  my $stmt;
  my $sql=<<"SQL";
SELECT DISTINCT $key_col
FROM $table_name
ORDER BY $key_col ASC
SQL

  $stmt=$I->_dbh->prepare_cached( $sql );
  $stmt->execute;
  return @{$stmt->fetchall_arrayref||[]};
}

sub list_keys_and_uris {
  my $I=shift;

  my ($table_name,$key_col,$uri_col)=map {$I->$_} qw/table key uri/;
  my ($sql, $stmt, @args);
  if( @_ and length $_[0] ) {
    $sql=<<"SQL";
SELECT DISTINCT $key_col, $uri_col
FROM $table_name
WHERE $key_col=?
ORDER BY $key_col ASC, $uri_col ASC
SQL
    push @args, $_[0];
  } else {
    $sql=<<"SQL";
SELECT DISTINCT $key_col, $uri_col
FROM $table_name
ORDER BY $key_col ASC, $uri_col ASC
SQL
  }
  $stmt=$I->_dbh->prepare_cached( $sql );
  $stmt->execute( @args );
  return @{$stmt->fetchall_arrayref||[]};
}

sub begin {
  my $I=shift;
  $I->_dbh->begin_work;
}

sub commit {
  my $I=shift;

  my ($table_name,$col_name)=map {$I->$_} qw/cachetbl cachecol/;
  my $stmt;
  my $sql=<<"SQL";
UPDATE $table_name
SET $col_name=$col_name+1
SQL

  $stmt=$I->_dbh->prepare_cached( $sql );
  $stmt->execute;
  $stmt->finish;

  $I->_dbh->commit;
}

sub rollback {
  my $I=shift;
  $I->_dbh->rollback;
}

sub update {
  my $I=shift;
  my $old=shift;
  my $new=shift;

  my ($table_name,$key_col,$uri_col,$block_col,$order_col,$action_col,
      $id_col, $notes_col)=
	map {$I->$_} qw/table key uri block order action id notes/;
  my ($stmt, $sql);

  if( length $notes_col ) {
    $sql=<<"SQL";
UPDATE $table_name
SET $key_col=?,
    $uri_col=?,
    $block_col=?,
    $order_col=?,
    $action_col=?,
    $notes_col=?
WHERE $key_col=?
  AND $uri_col=?
  AND $block_col=?
  AND $order_col=?
  AND $id_col=?
SQL

    $stmt=$I->_dbh->prepare_cached( $sql );
    my $rc=$stmt->execute( @{$new}[0..5], @{$old}[0..4] );
    $stmt->finish;
    return $rc;
  } else {
    $sql=<<"SQL";
UPDATE $table_name
SET $key_col=?,
    $uri_col=?,
    $block_col=?,
    $order_col=?,
    $action_col=?
WHERE $key_col=?
  AND $uri_col=?
  AND $block_col=?
  AND $order_col=?
  AND $id_col=?
SQL

    $stmt=$I->_dbh->prepare_cached( $sql );
    my $rc=$stmt->execute( @{$new}[0..4], @{$old}[0..4] );
    $stmt->finish;
    return $rc;
  }
}

sub insert {
  my $I=shift;
  my $new=shift;

  my ($table_name,$key_col,$uri_col,$block_col,$order_col,$action_col,
      $id_col, $notes_col)=
	map {$I->$_} qw/table key uri block order action id notes/;
  my ($stmt, $sql);

  my $st=$I->seqtbl;
  if( length $st ) {
    my $sn=$I->seqnamecol;
    my $sv=$I->seqvalcol;
    my $ms=$I->idseqname;

    $stmt=$I->_dbh->prepare_cached( "SELECT $sv FROM $st WHERE $sn = ?" );
    $stmt->execute($ms);
    my ($newid)=@{$stmt->fetchall_arrayref};
    die "ERROR: $st table not set up: missing row with $sn=$ms\n"
      unless( ref $newid eq 'ARRAY' );
    $newid=$newid->[0];

    $stmt=$I->_dbh->prepare_cached( "UPDATE $st SET $sv=$sv+1 WHERE $sn = ?" );
    $stmt->execute($ms);
    $stmt->finish;

    if( length $notes_col ) {
      $sql=<<"SQL";
INSERT INTO $table_name ($key_col,
                         $uri_col,
                         $block_col,
                         $order_col,
                         $action_col,
                         $notes_col,
                         $id_col)
VALUES (?, ?, ?, ?, ?, ?, ?)
SQL

      $stmt=$I->_dbh->prepare_cached( $sql );
      my $rc=$stmt->execute( @{$new}[0..5], $newid );
      $stmt->finish;
      return $rc;
    } else {
      $sql=<<"SQL";
INSERT INTO $table_name ($key_col,
                         $uri_col,
                         $block_col,
                         $order_col,
                         $action_col, $id_col)
VALUES (?, ?, ?, ?, ?, ?)
SQL

      $stmt=$I->_dbh->prepare_cached( $sql );
      my $rc=$stmt->execute( @{$new}[0..4], $newid );
      $stmt->finish;
      return $rc;
    }
  } else {
    if( length $notes_col ) {
      $sql=<<"SQL";
INSERT INTO $table_name ($key_col,
                         $uri_col,
                         $block_col,
                         $order_col,
                         $action_col,
                         $notes_col)
VALUES (?, ?, ?, ?, ?, ?)
SQL

      $stmt=$I->_dbh->prepare_cached( $sql );
      my $rc=$stmt->execute( @{$new}[0..5] );
      $stmt->finish;
      return $rc;
    } else {
      $sql=<<"SQL";
INSERT INTO $table_name ($key_col,
                         $uri_col,
                         $block_col,
                         $order_col,
                         $action_col)
VALUES (?, ?, ?, ?, ?)
SQL

      $stmt=$I->_dbh->prepare_cached( $sql );
      my $rc=$stmt->execute( @{$new}[0..4] );
      $stmt->finish;
      return $rc;
    }
  }
}

sub delete {
  my $I=shift;
  my $old=shift;

  my ($table_name,$key_col,$uri_col,$block_col,$order_col,$action_col,
      $id_col)= map {$I->$_} qw/table key uri block order action id/;
  my $stmt;
  my $sql=<<"SQL";
DELETE FROM $table_name
WHERE $key_col=?
  AND $uri_col=?
  AND $block_col=?
  AND $order_col=?
  AND $id_col=?
SQL

  $stmt=$I->_dbh->prepare_cached( $sql );
  my $rc=$stmt->execute( @{$old} );
  $stmt->finish;
  return $rc;
}

sub clear {
  my ($I)=@_;

  my $table_name=$I->table;
  my $stmt;
  my $sql=<<"SQL";
DELETE FROM $table_name
SQL

  $stmt=$I->_dbh->prepare_cached( $sql );
  my $rc=$stmt->execute;
  $stmt->finish;
  return $rc;
}

sub iterator {
  my ($I)=@_;

  my ($table_name,$key_col,$uri_col,$block_col,$order_col,$action_col,
      $notes_col,$id_col)=
    map {$I->$_} qw/table key uri block order action notes id/;

  $notes_col="''" unless( length $notes_col );
  $id_col="''" unless( length $id_col );
  my $sql=<<"SQL";
SELECT $key_col, $uri_col, $block_col, $order_col, $action_col,
       $notes_col, $id_col
FROM $table_name
ORDER BY $key_col, $uri_col, $block_col ASC, $order_col ASC
SQL

  my $stmt=$I->_dbh->prepare_cached( $sql );
  $stmt->execute;

  return sub {
    my $el;
    if( $el=$stmt->fetchrow_arrayref ) {
      return $el;
    } else {
      undef $stmt;
      return;
    }
  };
}

sub DESTROY {
  my $I=shift;
  if( defined $I->_dbh ) {
    %{$I->_dbh->{CachedKids}}=();
    $I->_dbh->disconnect;
    undef $I->_dbh;
  }
}

1;
__END__