=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
= Copyright

Copyright 2002 by Stephen P. Schaefer.  All rights reserved.  Your use
of this software is governed by the terms of the GNU Lesser General
Public License, Version 2.1, February 1999.

= Description

This file extends the class (({CGI::Session})) to support a PostgreSQL
backend.  It requires (({ruby-postgres})) from noborus@zetabits.com.
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      varchar,
   varval       varchar
 );

 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