package PublicCheck::MidStoreSql;
use strict;
use PublicCheck::MidStore;
use DBI;
our(@ISA) = qw(PublicCheck::MidStore);
our($CONF_KEY) = 'PublicCheck';

# Configuration settings we know about.
my(%CONF_DEFAULT) = (public_sql_password => 0,
					 public_sql_username => 0,
                     public_sql_dsn      => 0,
					 public_sql_statement => 'SELECT count(message_id) FROM mid_history WHERE message_id=? AND username=?',
					 public_sql_strict_statement => 'SELECT count(message_id) FROM mid_history WHERE message_id=? AND username=? AND email=?'
					);

sub new {
	my($this) = shift;
	$this = ref($this) || $this;
	my($self) = $this->SUPER::new(@_);
	%{$self} = %CONF_DEFAULT;
	return($self);
}

sub parse_config {
	my($self,$plugin,$opt) = @_;
	# Users can't set anything.
	if($opt->{'user_config'}){
		return;
	}
	my($key) = $opt->{key};
	if(defined($CONF_DEFAULT{$key})){
			$self->{$key} = $opt->{value}; # We shouldn't put them in $self, but since it's global should be OK.
			$plugin->inhibit_further_callbacks();
			return(1);
	}
	return(0);
}

sub _new_connection {
	my($self,$plugin) = (shift,shift);
	my($dsn) = $self->{public_sql_dsn} || 0;
	my($password) = $self->{public_sql_password} || 0;
	my($username) = $self->{public_sql_username} || 0;
	if(! $dsn){
		$plugin->debug("public_sql_dsn not set, can't check");
		return();
	}
	$plugin->debug("Connecting to $dsn");
	my $dbh =  DBI->connect($dsn, $username, $password,{});
	if(! $dbh){
		$plugin->debug($dbh->errstr);
	}
	return($dbh);
}

#
# Perhaps a bad idea... database handle is kept around.
#
sub connection {
	my($self,$plugin) = (shift,shift);
	if(! $self->{DBH}){
		$self->{DBH} = $self->_new_connection($plugin);
	}
	# Database go down?
	if(! $self->{DBH}->ping()){
		$self->{DBH} = $self->_new_connection($plugin);
	}
	return($self->{DBH});
}

sub exists {
	my($self,$plugin,$stat,$mid,$email) = @_;
	$email ||= 0;
	my($main) = $plugin->{main};
	my($user) = $main->{username};
	$plugin->debug("Username: $user");
	if($email){
		return($self->strict_exists($plugin,$user,$mid,$email));
	}else{
		return($self->basic_exists($plugin,$user,$mid));
	}
}
sub basic_exists {
	my($self,$plugin,$user,$mid) = (shift,shift,shift,shift);
	my($dbh) = $self->connection($plugin) || return(0);
	my($sql) = $self->{public_sql_statement};
	my($rv);
	eval {
		my $sh = $dbh->prepare($sql) || die "$sql: " . $dbh->errstr;
		$sh->execute($mid,$user);
		($rv,undef) = $sh->fetchrow_array();
		$sh->finish();
	};
	if($@){
		$plugin->debug("Database error: $@");		
	}
	return($rv);

	
}
sub strict_exists {
	my($self,$plugin,$user,$mid,$email) = (shift,shift,shift,shift,shift);
	my($dbh) = $self->connection($plugin) || return(0);
	my($sql) = $self->{public_sql_strict_statement};
	my($rv);
	eval {
		my $sh = $dbh->prepare($sql) || die "$sql: " . $dbh->errstr;
		$sh->execute($mid,$user,$email);
		($rv,undef) = $sh->fetchrow_array();
		$sh->finish();
	};
	if($@){
		$plugin->debug("Database error: $@");		
	}
	return($rv);
}
sub DESTROY {
	my($self) = shift();
	if($self->{DBH}){
		$self->{DBH}->close();
	}
	delete($self->{DBH});
}

1;

__END__

=pod

=head1 NAME

PublicCheck::MidStoreSql

Storage for PublicCheck::To using an SQL database.

=head1 DESCRIPTION

This is a storage mechanism for storing outbound message ID's, required by
PublicCheck::To to check references.

=head1 CONFIGURATION

=over 4

=item public_sql_password

The password to connect to the database.

=item public_sql_username

The username required to connect.

=item public_sql_dsn

The database DSN, (See perldoc DBI)

=back

Additionally, the SQL used to construct the two queries can be changed, allowing
you to use a different table, note however that the order of the
question marks is significant. (and fixed)

=over 4

=item public_sql_statement 

Used whithout the 'strict' level reference checks.

	SELECT count(message_id) FROM mid_history WHERE message_id=? AND username=?

=item public_sql_strict_statement

Used when strict level checks are in place.

eval:public_check_references('strict')

	SELECT count(message_id) FROM mid_history WHERE message_id=? AND username=? AND email=?

=back

=head1 SQL TABLE

You'll have to create your own SQL table, something like this:


  CREATE TABLE mid_history (
        username varchar(100) NOT NULL default '',
        email varchar(200) NOT NULL default '',
        message_id   varchar(127) NOT NULL,
        lastupdate timestamp NOT NULL,
        PRIMARY KEY  (username,message_id)
  ) TYPE=MyISAM;

The lastupdate is not required, but handy if you ever wanted to expire old ones.


=head1 POPULATING DATABASE

Not done, it's a local matter which needs to be done with an out-bound
email filter.