[Cgi-session-user] Re: CGI::Session::Driver::postgresql

[Mark S. <ma...@su...>]

Thanks Matt.

To confirm: 

The BYTEA updates are needed because binary characters are involved,
but could be avoided otherwise.

Is there any penalty for using BYTEA when the data is plain text? 

I'm wondering if we should still make plain-text backends an option, for
people who use a serializer that works with plain text.

Also, I believe the PostgreSQL driver for 3.x used the 'text' type. It
would be very nice if those folks could upgrade directly to 4.x without
changing the data type of one of their database columns.

Is there any reason to just recommend people use the plain-text Dumper
format with PostgreSQL  to keey things simple? 

    Mark

On Wed, Aug 17, 2005 at 04:10:40PM -0500, Matt LeBlanc wrote:
> Sorry it took so long to provide you with this patch. It's a quick hack 
> so feel free to update it as you see fit.
> 
> As a reminder, this patch fixes problems one might experience when (s)he 
> uses Storable or FreezeThaw as the serializer.
> 
> Thanks,
> Matt LeBlanc

> --- postgresql-old.pm	Wed Aug 17 15:51:37 2005
> +++ postgresql.pm	Wed Aug 17 16:03:54 2005
> @@ -15,13 +15,48 @@
>  
>  
>  use strict;
> +use Carp "croak";
>  #use diagnostics;
>  
>  use CGI::Session::Driver::DBI;
> +use DBD::Pg "PG_BYTEA";
>  
>  $CGI::Session::Driver::postgresql::VERSION = '2.01';
>  @CGI::Session::Driver::postgresql::ISA     = qw( CGI::Session::Driver::DBI );
>  
> +sub store {
> +    my $self = shift;
> +    my ($sid, $datastr) = @_;
> +    croak "store(): usage error" unless $sid && $datastr;
> +
> +    my $dbh = $self->{Handle};
> +    my $sth = $dbh->prepare("SELECT id FROM " . $self->table_name . " WHERE id=?");
> +    unless ( defined $sth ) {
> +        return $self->set_error( "store(): \$sth->prepare failed with message " . $dbh->errstr );
> +    }
> +
> +    $sth->execute( $sid ) or return $self->set_error( "store(): \$sth->execute failed with message " . $dbh->errstr );
> +    if ( $sth->fetchrow_array ) {
> +        __ex_and_ret($dbh,"UPDATE " . $self->table_name . " SET a_session=? WHERE id=?",$datastr,$sid) 
> +            or return $self->set_error( "store(): \$dbh->do failed " . $dbh->errstr );
> +    } else {
> +        __ex_and_ret($dbh,"INSERT INTO " . $self->table_name . " (a_session,id) VALUES(?, ?)",$datastr, $sid) 
> +            or return $self->set_error( "store(): \$dbh->do failed " . $dbh->errstr );
> +    }
> +    return 1;
> +}
> +
> +sub __ex_and_ret {
> +    my ($dbh,$sql,$datastr,$sid) = @_;
> +    eval {
> +        local @SIG{qw(__DIE__ __WARN__)};
> +        my $sth = $dbh->prepare($sql) or return 0;
> +        $sth->bind_param(1,undef,{pg_type => PG_BYTEA}) or return 0;
> +        $sth->execute($datastr,$sid) or return 0;
> +    };
> +    return 0 if $@;
> +    return 1;
> +}
>  
>  1;
>  
> @@ -38,7 +73,18 @@
>  
>  =head1 DESCRIPTION
>  
> -CGI::Session::PostgreSQL is a CGI::Session driver to store session data in a PostgreSQL table. More details see L<CGI::Session::Driver::DBI|CGI::Session::Driver::DBI>, its parent class.
> +CGI::Session::PostgreSQL is a CGI::Session driver to store session data in a PostgreSQL table.
> +
> +=head1 STORAGE
> +
> +Before you can use any DBI-based session drivers you need to make sure compatible database table is created for CGI::Session to work with. Following command will produce minimal requirements in most SQL databases:
> +
> +    CREATE TABLE sessions (
> +        id CHAR(32) NOT NULL PRIMARY KEY,
> +        a_session BYTEA NOT NULL
> +    );
> +
> +For more details see L<CGI::Session::Driver::DBI|CGI::Session::Driver::DBI>, its parent class.
>  
>  =head1 COPYRIGHT
>  

--
 . . . . . . . . . . . . . . . . . . . . . . . . . . . 
   Mark Stosberg            Principal Developer  
   ma...@su...     Summersault, LLC     
   765-939-9301 ext 202     database driven websites
 . . . . . http://www.summersault.com/ . . . . . . . .