Postgress backend for CGI::Session

I'd like to contribute this original code, and would
appreciate any critique.  I've used it successfully in
a re-implementation of the hangman program from
``Writing Apache Modules in Perl and C''.

I am not convinced this code will scale, and I'd like
to participate in a discussion of whether and how one
would pre-declare the structure of an appropriate
backend database.

In PostgreSQL 6.5 I used ``varchar'' for the fields
and field values; in PostgreSQL 7.1 I switch to
``text'' fields, since it would no longer cast
``varchar'' fields to integer or other numeric types. 
 Anyway, the code:



=begin
= Synopsis
   require pgsession.rb
   aSession = CGI::Session.new( aCGI,
      'database_manager' => CGI::Session::PostgreSQL,
      < aHash >* )
   value = aSession[key]
   aSession[key] = value
   aSession.delete
   aSession.update
= Description

This file extends the class (({CGI::Session})) to
support a PostgreSQL
backend.  It will be persistent in the same way that
(({CGI::Session::FileStore})) is, but uses a postgres
database named (({web})) with two tables defined as
follows:
 #!/bin/sh
 /usr/bin/psql web <<EOF
 DROP INDEX sess_val_idx;
 DROP TABLE session_mods;
 DROP TABLE session_values;

 CREATE TABLE session_mods (
   session_id   char(16) PRIMARY KEY,
   modified     timestamp
 );

 CREATE TABLE session_values (
   session_id   char(16) REFERENCES session_mods (
session_id )
     ON DELETE CASCADE
     ON UPDATE CASCADE,
   varname      text,
   varval       text
 );

 CREATE UNIQUE INDEX sess_val_idx ON session_values
(session_id, varname);
 EOF

Note that the (({REFERENCES})) clause is recognized
but not supported
in PostgreSQL 6.5; it may be supported in later
versions, but this
code does not depend on its PostgreSQL implementation.
 This
implementation supports a timestamp field named
(({modified})) within
the database, so that the database administrator has
an opportunity
(outside this class) to expire outdated sessions.  The
following text
extensively quotes the library descriptions found in
Thomas and Hunt's
((*Programming Ruby*)).

= Class Methods
--- CGI.Session.new( aCGI, aHash )
    Returns a new session object for the (({CGI}))
query.  Options that may be given in ((|aHash|))
include:
    : Option
      Description
    : (({session_key}))
      Name of CGI key for session identification.
    : (({session_id}))
      Value of session id.  The
(({CGI::Session::PostgreSQL})) implementation
      will pad or truncate this string to sixteen
characters.
    : (({new_session}))
      If (({true})), create a new session id for this
session.
    : (({database_manager}))
      Class to use to save sessions; may be
(({CGI::Session::FileStore})),
      (({CGI::Session::MemoryStore})) or
(({CGI::Session::PostgreSQL}))
      (or use defined if you're brave).  Default is
(({FileStore})).
    : (({tmpdir}))
      For (({FileStore})), directory for session
files.
    : (({prefix}))
      For (({FileStore})), prefix of session
filenames.

= Instance Methods
--- []
    Returns the value for the given key.

--- []=
    Sets the value for the given key.

--- delete
    Calls the (({delete})) method of the udnerlying
database manager.  For
    (({PostgreSQL})), deletes the database entries
corresponding to the
    session.  For (({FileStore})), deletes the
physical file containing the
    session.  For (({MemoryStore})), removes the
session from memory.

--- update
    Calls the (({update})) method of the underlying
database manager.  For
    (({PostreSQL})), replaces the table entries
corresponding to the session.
    For (({FileStore})), writes the session data out
to disk.  Has no effect
    with (({MemoryStore})).
  
=end

require 'cgi/session.rb'

class CGI
  class Session
    class PostgreSQL
      @@DBH = nil
      require "postgres"
      DB	= 'localhost'
      DB_PORT	= 5432
      DB_OPTIONS = nil
      DB_TTY = nil
      DB_NAME = "web"
      DB_PASSWORD = ""
      DB_SESS_TABLE = "session_mods"
      DB_VALS_TABLE = "session_values"
      def initialize(session, option={})
	if (!@@DBH)
	  begin
	    @@DBH = PGconn.connect(DB, DB_PORT, DB_OPTIONS,
DB_TTY, DB_NAME, DB_LOGIN, DB_PASSWORD) 
	  rescue PGError
	    $stderr.print "Database problem: " + $!
	    raise
	  end
	end

        @session_id = session.session_id[0..15]
	begin
	  r = @@DBH.exec("SELECT * FROM #{DB_SESS_TABLE}
WHERE session_id = '#{@session_id}'")
	rescue PGError
	  $stderr.print "Database problem: " + $!
	  raise
	end

	if r.num_tuples == 0
	  # normal case: new session id
	  @hash = {}
	  cmd =
"BEGIN;
  INSERT INTO #{DB_SESS_TABLE} (session_id, modified)
VALUES ( '#{@session_id}', 'now' );
  DELETE FROM #{DB_VALS_TABLE} WHERE session_id =
'#{@session_id}';
COMMIT"
	else
	  cmd ="UPDATE #{DB_SESS_TABLE} SET modified = 'now'
WHERE session_id = '#{@session_id}'"
	end

	begin
	  $stderr.print cmd + "\n"
	  @@DBH.exec("#{cmd}")
	rescue PGError
	  $stderr.print "Database problem: " + $!
	  raise
	end
      end

      def restore
	unless @hash
	  @hash = {}
	  begin
            cmd = "SELECT varname, varval FROM
#{DB_VALS_TABLE} WHERE session_id =
'#{@session_id}'\n"
	    $stderr.print cmd
	    r = @@DBH.exec(cmd)
	  rescue PGError
	    $stderr.print "Database problem: " + $!
	    raise
	  end
	  for i in 0 ... r.num_tuples
	    k = CGI::unescape(r.getvalue(i, 0))
	    v = CGI::unescape(r.getvalue(i, 1))
	    @hash[k] = v
	  end
	end
	@hash
      end

      def update
	cmd =
"BEGIN;
  DELETE FROM #{DB_VALS_TABLE} WHERE session_id =
'#{@session_id}';
"
	@hash.each {
	  | k, v |
	  cmd <<
"  INSERT INTO #{DB_VALS_TABLE} (session_id, varname,
varval)
    VALUES ( '#{@session_id}', '" +
    CGI::escape(k).gsub(/'/, "''") +
    "', '" +
    CGI::escape(v).gsub(/'/, "''") +
    "' );
"
	}
	cmd <<
"COMMIT\n"
        begin
	  $stderr.print cmd
	  @@DBH.exec(cmd)
	rescue
	  $stderr.print "Database problem: " + $!
	  raise
	end
      end

      def close
        begin
	  @@DBH.close
	rescue
	  $stderr.print "Database problem: " + $!
	  raise
	end
	@@DBH = nil
      end

      def delete
	cmd =
"BEGIN;
  DELETE FROM #{DB_VALS_TABLE} WHERE session_id =
'#{@session_id}';
  DELETE FROM #{DB_SESS_TABLE} WHERE session_id =
'#{@session_id}';
COMMIT
"
        begin
	  $stderr.print cmd
	  @@DBH.exec(cmd)
	rescue
	  $stderr.print "Database problem: " + $!
	  raise
	end
      end
    end
  end
end


__________________________________________________
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com