// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
// The Molly framework is freely distributable under the terms of an
// MIT-style license. For details, see the molly pages web site at:
// http://www.mollypages.org/. Use, modify, have fun !

package fc.web.servlet;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

import fc.io.*;
import fc.jdbc.*;
import fc.util.*;

/** 
Stores session data into a database. This approach is <i>almost</i>
<u>always</u> the right thing to do. Don't store session data in the servlet
container memory (memory backed sessions) and don't use serialization
based session persistence as implemented by various session containers.
Use database sessions for storing secure and/or large amounts of data 
and store non-secure data directly in cookies.
<p>
Database sessions enable front-end webservers to scale easily. (No
messy/brittle session migration hacks are required !). Scaling can be near
infinite, but after a point (say 100 front ends), the back-end databases may
also have to be partitioned (and the appropriate partition/machine invoked for
a given session-id).
<p>
Note, memory sessions are not needed even for maintaining html form-state
because each form's state can be uniquely created from an initial form
state (common to all users) along with the per-usr data submitted per
request. This is exactly how the {@link fc.web.forms.Form form} API works.
However, memory sessions <u>are</u> useful for certain transient things/caches 
or for very <i>quick'n'dirty</i> apps, where using a database is more trouble 
than it's worth.
<p>
Given fast ethernet connections and forward marching processor/peripheral
speeds, JDBC session data is typically retrieved on the order of 1-2
milliseconds (or even lesser).
<p>
Using this class requires the following database table schema in
the database:
<pre>
create table <b>sessiondata</b> ( 
  session_id    varchar(50),
  name         varchar(100) not null,
  value        text  -- [or some other character/text type]
  );
  
create table <b>sessionmaster</b> (
  session_id    varchar(50) primary key,  -- the session id
  created      timestamp not null,   -- session create time
  accessed     timestamp not null,   -- session last accessed       
  is_expired   bool default 'f',     -- true if expired 
  -- a reference to the user table that associates a user_id
  -- with one or more session_id's. Optional so should be 
  -- nullable if used.
  user_id    <font color=green>&lt;SQL_TYPE_FROM_YOUR_USER_TABLE&gt;</font>
  );
  
alter table sessiondata add 
   FOREIGN KEY (session_id) REFERENCES sessionmaster (session_id) 
   on delete cascade;

-- The following if a user table is present
alter table sessionmaster add
   FOREIGN KEY (user_id) REFERENCES <font color=green>
    NAME_OF_USER_TABLE</font>;
</pre>
<p>
When creating these tables, it is also advisable to <b>index</b> the
<tt>session_id</tt> and <tt>user_id</tt> columns for faster performance.
<p>
The above tables can also be created by this class itself. Invoke the main
method without any arguments to see usage information. The table names
<font color=blue>sessionmaster</font> and <font color=blue>sessiondata</font>
come from final static variables in this class. If you want to use different
names, change these variables and recompile this class.
<p>
More than one [name, value] pair can be stored in the <font
color=blue>sessiondata</font> table. This class will automatically store
[name, value] pairs as seperate rows and can return a particular [name,
value] pair or all [name, value] pairs for the specified session.
<p>
Note: This class allows saving data only as Strings/text. However,
arbitrary binary data can also be stored but the caller should first
base64 encode that data and then save it as a string. Upon retrieval, that
data can be base64 decoded.
<p>
Note 2: Under some scenarios, it is <b>important</b> to have a separate
cleaner process that periodically deletes expired sessions from the
database. This process should run via cron, some other stand-alone java
code etc., and should delete sessions which are marked expired or whose
<tt>create_time - last_used_time</tt> is greater than the session expiry
time. Under other scenarios, sessions may be deleted after a set amount of
time from the creation data regardless of when the session was last
accessed.
<p>
Note 3: Typically, expired session data is simply deleted from the session
tables in the database. (for example, amazon.com users have to persist
their cart manually by clicking on a "save-for-later" button - which moves
data to more persistent tables -- otherwise their cart session is deleted
from the database when the session expires). It is however possible that
instead of deleting sessions, the sessions are instead marked as "expired"
but not deleted from the database. (this is done via the {@link
#setDeleteExpiredSessions} method.

@author hursh jain
*/
public final class JDBCSession
{
Log log = Log.get("fc.web.servlet.JDBCSession");

public static final String SESSIONDATA_TABLE  = "sessiondata";
public static final String SESSIONMASTER_TABLE  = "sessionmaster";

//queries
String  data_insert;
String  data_update;  
String  data_get; 
String  data_getall;   
String  data_delete;
String  delete_session;    
String  expire_session; 
String  new_session_with_user; 
String  new_session; 
String  tie_session_to_user; 
String  session_exists;
String  session_for_user;
String  session_access_update;

//by default expired sessions are deleted from the db. if this is set to false,
//they are instead marked as expired in the db.
boolean deleteExpiredSessions = true;

//sessions eligible for removal after this time of inactivity.
int   expireSeconds = 60*60*8; //8 hours default

private static JDBCSession instance;
private JDBCSession()
  {
  }

/*
Impl note: haven't used the fc.jdbc.dbo API to abstract our two database
sesssion-tables, maybe later but for right now it's simpler (and
more orthogonal) to handcode the sql for our prepared statements directly.
*/

/**
Returns an instance of JDBCSession.
*/
public static JDBCSession getInstance() 
  {
  return init(null);
  }


/**
Returns an instance of JDBCSession.

@param  logger          the logging destination for methods in this class. Specify
                <tt>null</tt> to use a default logger.
*/
public static JDBCSession getInstance(Log logger) 
  {
  return init(logger);
  }

private static JDBCSession init(Log logger)
  {
  if (instance != null) {
    return instance;
    }
    
  instance = new JDBCSession();
  instance.configure(logger);
  return instance;
  }

private void configure(Log logger)
  {
  if (logger != null)
    log = logger;
  
  data_insert =
   "insert into " + SESSIONDATA_TABLE +  
    " (session_id, name, value) values (?, ?, ?) ";
  
  data_update =  
   "update " + SESSIONDATA_TABLE + 
    " set value=? where session_id=? and name=?";

  data_get = 
   "select sd.name, sd.value from " 
    + SESSIONDATA_TABLE   + " as sd, "  
    + SESSIONMASTER_TABLE + " as sma "
    + " where sd.session_id=? "
    + " and sd.name=? "
    + " and sd.session_id = sma.session_id "
    + " and sma.is_expired=false"; 
      
  data_getall =    
   "select name, value from " + SESSIONDATA_TABLE +
    " where session_id=?";

  data_delete =
   "delete from " + SESSIONDATA_TABLE +  
    " where session_id=? and name=?";
    
  //cascades to sessiondata
  delete_session =   
   "delete from " + SESSIONMASTER_TABLE +  
    " where session_id=? "; 

  expire_session = 
      "update " + SESSIONMASTER_TABLE +  " set is_expired=? where session_id=? "; 
      
  new_session_with_user = 
   "insert into " + SESSIONMASTER_TABLE + 
    " (session_id, created, accessed, username) values " 
    + " (?, ?, ?, ?)";

  new_session = 
   "insert into " + SESSIONMASTER_TABLE + 
    " (session_id, created, accessed) values " + 
    " (?, ?, ?)";
  
  tie_session_to_user = 
    "update " + SESSIONMASTER_TABLE + 
      " set username=? where session_id=?";

  session_exists =
   "select session_id, created, accessed, is_expired from " 
    + SESSIONMASTER_TABLE + " where session_id=?";
    
  session_for_user =
   "select session_id, created, accessed, is_expired from " 
    + SESSIONMASTER_TABLE + " where username=?";

  session_access_update =
    "update " + SESSIONMASTER_TABLE + 
    " set accessed=? where session_id=?";
    
  }

/**
By default expired sessions are deleted from the db. If this is set to
false, they are instead marked as expired in the db.
*/
public void setDeleteExpiredSessions(final boolean val)
  {
  deleteExpiredSessions = val;
  }

//session mgmt

/**
Creates a new session.
<p>

@param  con     a jdbc connection
@param  sessionID value for sessionID, the {@link SessionUtil#newSessionID()}
          can be used to generate this. Should not be null. It is
          <b>not</b> recommended that the servlet container
          generated <tt>jsession_id</tt> cookie be used for this
          value. This sessionID is then later used to retrieve and
          work with the created session and this sessionID will
          typically be stored as a cookie or URL encoded on the
          client.
@param  userID    a userID to associate with this session. Note,
          userID's in user tables are typically auto generated
          numerical sequences. <b>Stringify numerical values before
          passing them into this method</b>. This value should
          <b>not</b> be <tt>null</tt>, otherwise a runtime
          exception will be thrown.
@throws SQLException 
          if a SQL error occurs. Note, also thrown if the
          session_id already exists in the database (since all
          session_id's must be unique).
*/
public void create(
  final Connection con, final String sessionID, final String userID) 
throws SQLException 
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");
  Argcheck.notnull(userID, "userID argument was null");
  
  /*
  insert into sessionmaster 
  (session_id, created, accessed, username) values (?, ?, ?, ?)
  */

  final PreparedStatement ps = prepareStatement(con, new_session_with_user);
  ps.setString(1, sessionID);

  final long now = System.currentTimeMillis();
  final java.sql.Timestamp ts = new java.sql.Timestamp(now);
  ps.setTimestamp(2, ts);
  ps.setTimestamp(3, ts);
  ps.setString(4, userID);
    
  log.bug("Query to run: ", ps);
  
  final int n = ps.executeUpdate();
  if (n != 1) {
    log.warn("Query:", ps, "should have returned 1 but returned:", new Integer(n));
    }
  }

/**
Creates a new session. The specified session can later be optionally to a userID
by invoking the {@link #tieToUser} method.
<p>
Note, sessionID's are typically be stored as a cookie or URL encoded on
the client and are thus unique per browser/client). A user is not
required to login to have session data.

@param  con      a jdbc connection
@param  sessionID  value for sessionID, the {@link SessionUtil#newSessionID()}
           can be used to generate this. Should not be null. It is
           <b>not</b> recommended that the servlet container
           generated <tt>jsession_id</tt> cookie be used for this
           value. This sessionID is then later used to retrieve and
           work with the created session.
@throws SQLException if a SQL error occurs. Note, also thrown if the
           session_id already exists in the database (since all
           session_id's must be unique).
*/
public void create(final Connection con, final String sessionID) 
throws SQLException 
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");

  /*
  insert into sessionmaster 
  (session_id, created, accessed) values (?, ?, ?)
  */

  final PreparedStatement ps = prepareStatement(con, new_session);
  ps.setString(1, sessionID);

  final long now = System.currentTimeMillis();
  final java.sql.Timestamp ts = new java.sql.Timestamp(now);
  ps.setTimestamp(2, ts);
  ps.setTimestamp(3, ts);
    
  log.bug("Query to run: ", ps);
  
  final int n = ps.executeUpdate();
  if (n != 1) {
    log.warn("Query:", ps, "should have returned 1 but returned:", new Integer(n));
    }
  }

/**
Expires the session. By default, deletes all session data associated with
the specified sessionID from the database. If {@link
#deleteExpiredSessions} is set to <tt>true</tt>, then the session is marked
as expired in the database but the rows are not deleted from the db.
<p>
Either way, after this method returns, the sessionID will not longer be
valid.
*/
public void expire(final Connection con, final String sessionID) 
throws SQLException 
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");

  PreparedStatement ps = null;
  
  if (deleteExpiredSessions)
    {
    /* delete from sessionmaster where session_id=? */
    ps = prepareStatement(con, delete_session);
    ps.setString(1, sessionID);
    }
  else {
     /* update sessionmaster set is_expired=?[=>true] where session_id=? */ 
    ps = prepareStatement(con, expire_session);
    ps.setBoolean(1, true);
    ps.setString(2, sessionID);
    }

  log.bug("Query to run: ", ps);
  
  final int n = ps.executeUpdate();
  if (n != 1) {
    log.warn("Query:", ps, "should have returned 1 but returned: ", new Integer(n), " [This can happen if the sessionID did not exist in the database]");
    } 
  }


/**
Associates the specified sessionID with the specified userID. <p> Note:
Depending on the application, more than 1 sessionID can be associated with
the same userID in the session master table.
*/
public void tieToUser(
  final Connection con, final String sessionID, final String userID) 
throws SQLException
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");
  Argcheck.notnull(userID, "userID arg was null");
  
  /* update sessionmaster set username=? where session_id=? */  
  
  final PreparedStatement ps = prepareStatement(con, tie_session_to_user);
  ps.setString(1, userID);
  ps.setString(2, sessionID); 

  log.bug("Query to run: ", ps);
  final int n = ps.executeUpdate();
  if (n != 1) {
    log.warn("Query:", ps, " should have returned 1 but returned: ", new Integer(n), " [This can happen if the sessionID did not exist in the database]");
    }
  }


/**
Utility method that deletes (or marked as expired depending on {@link
#setDeleteExpiredSessions}) all sessions in the database that have exceeded
the maximum inactive time.
*/
public void expireInactiveSessions(final Connection con) 
throws SQLException
  {
  long now = System.currentTimeMillis(); //in gmt
  Timestamp ts = new Timestamp(now);
  
  String query = null;
      
  /* 
  this works fine in postgres: 
    timestamp1 - timestamp2 > interval X second 
  mysql doesn't like it, to subtract dates/timestamps, we need
  some mysql specific functions.
  
  however, 
    timestamp + interval X second 
  gives us a timestamp (in both db's) and can be directly compared
  to another timestamp.
  */
  
  if (deleteExpiredSessions) {
    query  = "delete from " + SESSIONMASTER_TABLE + " where " +
    /*
    "('" + ts + "' - accessed ) > interval '" +expireSeconds+ "' second"; 
    */
    "('" + ts + "' + interval '" + expireSeconds + "' second ) > accessed ";
    }
  else {
    query = "update " + SESSIONMASTER_TABLE 
    + " set is_expired=? where " +
    /*
    + "('" + ts + "' - accessed ) > interval '" +expireSeconds+ "' second"; 
    */
    "(timestamp '" + ts + "' + interval '" + expireSeconds + "' second ) > accessed ";
    }
  
  final PreparedStatement ps = con.prepareStatement(query);
  ps.setBoolean(1, true);
  log.bug("Query to run: ", ps);
  final int n = ps.executeUpdate();
  log.info(new Integer(n), " sessions reaped by: ", query);
  }
  
/**
Sessions inactive for greater than these number of seconds will be 
eligible for expiry. <p><b>Note:</b> these expired sessions will still not
be expired until the {@link expireInactiveSessions()} method is invoked.
<p>Defaults to <tt>8 hours (=60*60*8 seconds)</tt>
*/
public void setExpireTime(final int seconds) {
  expireSeconds = seconds;
  }

/**
Returns the current expire interval (seconds after which
sessions can be considered eligible for removal).
*/
public int getExpireTime() {
  return expireSeconds;
  }

/** 
Returns true is the specified sessionID is valid (i.e., the specified
sessionID exists in the database and has not expired). 
<p> 
Note: this method does <b>not</b> expire the session itself or check for
non-expired validity. Sessions should be expired as/when needed by calling the
{@link expire} method.
*/ 
public boolean exists(final Connection con, final String sessionID) 
throws SQLException 
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");
  
  /*
  select session_id, created, accessed, is_expired from sessionmaster 
  where session_id=?
  */
  final PreparedStatement ps = prepareStatement(con, session_exists);
  ps.setString(1, sessionID);
  
  log.bug("Query to run: ", ps);
  
  final ResultSet rs = ps.executeQuery();
  boolean exists = false; 
    if (rs.next()) 
      {  //sessionID exists
    boolean is_expired = rs.getBoolean(4); 
    if (! is_expired) { //and is unexpired
      exists = true;
      }
      }
    
    return exists;
  }


/**
Returns session information from the session master table. This information
is returned as a {@link Session.Info info} object encapsulating the master
row for the given sessionID. <p> Returns <tt>null</tt> if the given
sessionID has expired and/or was not found in the database.
*/
public JDBCSession.Info sessionInfo(
  final Connection con, final String sessionID) 
throws SQLException
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");
  
  /*
  select session_id, created, accessed, is_expired from 
      sessionmaster where session_id=?
  */
  final PreparedStatement ps = prepareStatement(con, session_exists);
  ps.setString(1, sessionID);
  
  log.bug("Query to run: ", ps);

  JDBCSession.Info info = null;
  
  ResultSet rs = ps.executeQuery();

    if (rs.next()) {  //sessionID exists
    info = new JDBCSession.Info();
    info.sessionID = sessionID;
    info.created = rs.getTimestamp(2);
    info.accessed = rs.getTimestamp(3);   
    info.is_expired = rs.getBoolean(4);
      }
    
    return info;
  }

/**
Returns a List containing {@link Info session information} about all
sessions associated with the specified ID. Returns an empty list if no
sessions are found for the specified userID.
<p>
Note, the specified userID can be null in which case all sessions with null
userID's will be returned.
*/
public List getForUser(final Connection con, final String userID) 
throws SQLException
  { 
  if (userID == null)
    log.warn("userID arg was null, was this intentional ?");

  /*
  select session_id, created, accessed, is_expired from 
      sessionmaster where username=?
  */
  final PreparedStatement ps = prepareStatement(con, session_for_user);
  ps.setString(1, userID);
  
  log.bug("Query to run: ", ps);

  List list = new ArrayList();
  ResultSet rs = ps.executeQuery();

    while (rs.next()) {  //sessionID exists
    JDBCSession.Info info = new JDBCSession.Info();
    info.sessionID = rs.getString(1);
    info.created = rs.getTimestamp(2);
    info.accessed = rs.getTimestamp(3);   
    info.is_expired = rs.getBoolean(4);
      list.add(info);
      }
    
    return list;
  }

/**
Same as {@link #getForUser(Connection, String)} but takes a numeric userID.
*/
public List getForUser(final Connection con, final int userID) 
throws SQLException
  {
  return getForUser(con, String.valueOf(userID));
  }

/**
Information about a session.
*/
public static class Info
  {
  String sessionID;
  Timestamp created;
  Timestamp accessed;
  boolean is_expired;
  
  public String getSessionID() { return sessionID; }
  public Timestamp getCreated() { return created; }
  public Timestamp getAccessed() { return accessed; }
  public boolean getIsExpired() { return is_expired; }
  
  private ToString tostr;
  {
  tostr = new ToString(this, 
      ToString.Style.VisibleLevel.DEFAULT);   
  }
  public String toString() {
    return tostr.reflect().render();
    }
  }
 

//--------- value management ----------------

/** 
Returns a map of all [key, value] pairs associated with the specified
sessionID. Returns <code>null</code> if the specified sessionID is not found 
in the database or if the specified session has expired.
*/
public Map getAll(final Connection con, final String sessionID) 
throws SQLException 
  {
  Argcheck.notnull(con, "con argument was null");
  Argcheck.notnull(sessionID, "sessionID argument was null");

  if (! exists(con, sessionID))
    return null;

  /* 
  select name, value from sessiondata where session_id=? 
  */

  final PreparedStatement ps = prepareStatement(con, data_getall);
  ps.setString(1, sessionID);
  log.bug("Query to run: ", ps);

  final Map map = new HashMap();
  final ResultSet rs = ps.executeQuery();
  boolean hasdata = false;

  if (rs.next())
    {
    hasdata = true;
    String name = rs.getString(1);
    String value = rs.getString(2); //can be null
    map.put(name, value);
    }

  while (rs.next()) 
    {
    String name = rs.getString(1);
    String value = rs.getString(2); //can be null
    map.put(name, value);
    }

  if (hasdata) {
    updateSessionAccessTime(con, sessionID);
    }
    
  return map;
  }

/**
Returns the value associated with the specified sessionID and key.
<p>
Returns <tt>null</tt> if the specified session has expired, or the specified
key does not exist in the database or exists but contains a <tt>null</tt> in
the database.
*/
public String get(
  final Connection con, final String sessionID, final String key) 
throws SQLException 
  {
  Argcheck.notnull(con, "con argument was null");
  Argcheck.notnull(sessionID, "sessionID argument was null");
  Argcheck.notnull(key, "key argument was null");

   /*
   "select sd.name, sd.value from " 
    + sessiondata   + " as sd, " 
    + sessionmaster + " as sma "
    + " where sd.session_id=? " +
    + " and sd.name=? "
    + " and sd.session_id = sma.session_id " +
    + " and sma.is_expired=false"; 
  */

  final PreparedStatement ps = prepareStatement(con, data_get);
  ps.setString(1, sessionID);
  ps.setString(2, key);   //key param is called "name" in db

  log.bug("Query to run: ", ps);

  String result = null;
  final ResultSet rs = ps.executeQuery();
  if (rs.next()) 
    {
    result = rs.getString(2); //can be null if that IS the associated value
    updateSessionAccessTime(con, sessionID);
    }
  
  return result;
  }
  

/**
Deletes <b>both the key and value</b> specified by the sessionID and key.
Does nothing if the sessionID or key does not exist in the database.
*/
public void delete(
  Connection con, final String sessionID, final String key) 
throws SQLException 
  {
  Argcheck.notnull(con, "con argument was null");
  Argcheck.notnull(sessionID, "sessionID argument was null");
  Argcheck.notnull(key, "key argument was null");
    
  /* Delete from sessiondata where session_id=? and name=? */
    
  PreparedStatement ps = prepareStatement(con, data_delete);
  ps.setString(1, sessionID);
  ps.setString(2, key);

  log.bug("Query to run: ", ps);
  int n = ps.executeUpdate();
  if (n == 0) {
    log.warn(ps, "key=", key, "[key not deleted, does it exist in the database ?]");
    }
  
  updateSessionAccessTime(con, sessionID);
  }
  
/**
Saves the tuple [sessionID, key, value] in the database. <p> The specified
sessionID must exist and be valid in the database otherwise a SQLException
will be thrown.
*/
public void add(
 final Connection con, final String sessionID, final String key, final String value) 
throws  SQLException 
  {
  Argcheck.notnull(con, "con argument was null");
  Argcheck.notnull(sessionID, "sessionID argument was null");
  Argcheck.notnull(key, "key argument was null");
  
  if (! exists(con, sessionID))
    throw new SQLException("The specified sessionID:[" + sessionID + "] has expired");

  /*
  insert into sessiondata (session_id, name, value) values (?, ?, ?) ";
  */
  
  final PreparedStatement ps = prepareStatement(con, data_insert);
  ps.setString(1, sessionID);
  ps.setString(2, key);
  ps.setString(3, value);
  
  log.bug("Query to run: ", ps);

  final int n = ps.executeUpdate();
  
  if (n != 1) {
    log.bug("insert error, preparedstatment", ps,  " returned", new Integer(n));
    throw new SQLException("Error saving data, inserted row count != 1");
    }
  
  updateSessionAccessTime(con, sessionID);
  }
  
/**
Adds all [key, value] pairs in the specified map to the session with the
specified sessionID.
*/
public void addAll(
  final Connection con, final String sessionID, final Map data)
throws SQLException 
  {
  Argcheck.notnull(con, "con argument was null");
  Argcheck.notnull(sessionID, "sessionID argument was null");
  Argcheck.notnull(data, "data argument was null");

  Set set = data.entrySet();      
  int size = set.size();
  if (size == 0) {
    log.warn("nothing to do, map contains no data");
    return;
    }

  if (! exists(con, sessionID))
    throw new SQLException("The specified sessionID:[" + sessionID + "] has expired");

  /*
   insert into sessiondata 
   (session_id, name, value) values (?, ?, ?) ";
  */
  
  final PreparedStatement ps = prepareStatement(con, data_insert);

  final Iterator entries = set.iterator();
  while (entries.hasNext()) {
    Map.Entry e = (Map.Entry) entries.next();
    String key = e.getKey().toString();
    String val = e.getValue().toString();
    ps.setString(1, sessionID);
    ps.setString(2, key);
    ps.setString(3, val);
    ps.addBatch();
    }
    
  log.bug("Query to run: ", ps);

  final int[] result = ps.executeBatch();
  
  /*  
  if (result) {
    log.bug("insert error, preparedstatment", ps,  " returned 0 items inserted");
    throw new SQLException("Error saving data, inserted row count == 0");
    }
  */
  updateSessionAccessTime(con, sessionID);
  }

/**
An alias for the {@link add #add} method. 
*/
public void put(Connection con, 
  final String sessionID, final String key, final String value) 
throws  SQLException 
  {
  add(con, sessionID, key, value);
  }

/**
An alias for the {@link #addAll addAll} method. 
*/
public void putAll(final Connection con, final String sessionID, final Map data) 
throws SQLException 
  {
  addAll(con, sessionID, data);
  }

/**
Updates the value for the specified sessionID and key in the database.
<p>
The specified sessionID and keys must exist in the database prior to
calling this method,otherwise a SQLException will be thrown.
*/
public String update(
  final Connection con, 
  final String sessionID, final String key, final String newvalue) 
throws SQLException 
  {
  Argcheck.notnull(con, "con argument was null");
  Argcheck.notnull(sessionID, "sessionID argument was null");
  Argcheck.notnull(key, "key argument was null");

  if (! exists(con, sessionID))
    throw new SQLException("The specified sessionID:[" + sessionID + "] has expired");
        
  /* 
  update sessiondata set value=? 
      where session_id=? and name=?;
  */
              
  final PreparedStatement ps = prepareStatement(con, data_update);
  ps.setString(1, newvalue);
  ps.setString(2, sessionID);
  ps.setString(3, key);
  
  log.bug("Query to run: ", ps);

  String result = null;
  final ResultSet rs = ps.executeQuery();
  if (rs.next()) 
    {
    result = rs.getString(2); //can be null
    updateSessionAccessTime(con, sessionID);
    }

  return result;
  }


/**
Internal function: updates the session accessed time. should be called from
any method that gets/sets session data.
*/
private void updateSessionAccessTime(final Connection con, final String sessionID) 
throws SQLException
  {
  Argcheck.notnull(sessionID, "sessionID arg was null");
  
  final PreparedStatement ps = prepareStatement(con,session_access_update); 
  long now = System.currentTimeMillis(); //in gmt
  Timestamp ts = new Timestamp(now);
    /*
    update sessionmaster set accessed=? where session_id=?
    */
  ps.setTimestamp(1, ts);
  ps.setString(2, sessionID); 
  
  log.bug("Query to run: ", ps);
  
  int n = ps.executeUpdate();
  if (n != 1) {
    log.warn("Query:", ps, "should have returned 1 but returned", new Integer(n), "[This can happen if the sessionID did not exist in the database]");
    }
  }

//returns a preparedstatement. clearsParameters() for
//recycled statements
private final PreparedStatement prepareStatement(
    final Connection con, final String sql) 
throws SQLException
  {
  if (! (con instanceof fc.jdbc.PooledConnection) ) { 
    return con.prepareStatement(sql);
    }
  PooledConnection pc = (PooledConnection) con;
  //System.out.println("sql = " + sql);
  PreparedStatement ps = pc.getCachedPreparedStatement(sql);
  return ps;
  }

public static void main(String args[]) throws Exception
  {
  Args myargs = new Args(args);
  myargs.setUsage(
  "java fc.web.servlet.JDBCSession -conf conf-file [-loglevel level]\n"
  + "-test  [tests the database for session tables]\n"
  + "   -- or --\n"
  + "-create  [creates session tables in the database]\n"
  + "   -userTableName=<name of Users table in the database> \n"
  + "   -userIDColName=the name of username/userid column in Users table.)] \n"
  );
  
  if (myargs.flagExists("loglevel")) {
    String level = myargs.get("loglevel");
    Log.getDefault().setLevel(level);
    }

  String propfile = myargs.getRequired("conf");

  FilePropertyMgr fprops = new FilePropertyMgr(new File(propfile));
  ConnectionMgr mgr = new SimpleConnectionMgr(fprops);

  String url = fprops.get("jdbc.url");
  boolean mysql = false;
  if (url.indexOf("mysql") != -1)
    mysql = true;

  Connection con = mgr.getConnection();

  if (myargs.flagExists("test")) {
    test(con);
    }
  else if (myargs.flagExists("create")) {
    String userTableName = myargs.get("userTableName", "users");
    createJDBCTables(con, userTableName, mysql);  
    }
  else
    myargs.showError();
  
  con.close();
  }

/**
Creates database tables for storing session data. This method can be
called programmatically but is typically invoked by the main method.
Invoke the main method without any flags to get usage information.
*/
public static void createJDBCTables(
  Connection con, String  userTableName, boolean mysql)
throws Exception
  {
  Argcheck.notnull(con, "connection param was null");
  Argcheck.notnull(userTableName, "userTableName param was null");

  Log log = Log.getDefault(); 
  try {
    QueryUtil.startTransaction(con);
    
    Statement st = null;
    String tmp = null;
    
    tmp = 
    "create table " + SESSIONDATA_TABLE + " ("
     + " session_id varchar(50),"
     + " name  varchar(100) not null,"
     + " value text )"
     ;
    
    st = con.createStatement();
    log.info("Running: " + tmp);
    st.execute(tmp);

    tmp = "create INDEX IDX_" + SESSIONDATA_TABLE 
      + " ON " + SESSIONDATA_TABLE + "(session_id)";

    st = con.createStatement();
    log.info("Running: " + tmp);
    st.execute(tmp);

    tmp = 
    "create table " + SESSIONMASTER_TABLE + " ("
     + " session_id   varchar(50) primary key, "
     + " created      timestamp not null,"
     + " accessed     timestamp not null,"
     
     /*mysql == myshit but then you already knew that*/
     + ((mysql) ? 
      " is_expired  bool default 0," :    
      " is_expired  bool default 'f',") 
  
     + " username     varchar(255)"
     + " )"
     ;

    st = con.createStatement();
    log.info("Running: " + tmp);
    st.execute(tmp);

    tmp = "create INDEX IDX_" + SESSIONMASTER_TABLE + "_1"
      + " ON " + SESSIONMASTER_TABLE + "(username)";  

    st = con.createStatement();
    log.info("Running: " + tmp);
    st.execute(tmp);
        
    tmp = 
    "alter table " + SESSIONDATA_TABLE + " add \n"  
    + " FOREIGN KEY (session_id) REFERENCES "
    +  SESSIONMASTER_TABLE + " (session_id) "
    + " on delete cascade "
    ;

    st = con.createStatement();
    log.info("Running: " + tmp);
    st.execute(tmp);
  
    /* this will only work, generally, if the userid is the same
    type in both tables, which is not necessarily the case
    */
    /*
    tmp =
    "alter table " + SESSIONMASTER_TABLE + " add "
    + " FOREIGN KEY (" + userIDColName + ") REFERENCES "
    + userTableName;
    ;
    st = con.createStatement();
    log.info("Running: " + tmp);
    st.execute(tmp);
    */
    QueryUtil.endTransaction(con);
    }
  catch (Exception e) {
    QueryUtil.abortTransaction(con);
    /* of course, myshit 5.x does not rollback create table sql statements, 
    even when using InnoDB */
    log.error("*** JDBC SESSION TABLES WERE NOT CREATED PROPERLY (IF AT ALL)****");
    throw e;
    }
  
  log.info("*** JDBC SESSION TABLE SUCCESSFULLY CREATED ***");
  }

static void test(Connection con) throws Exception {
  long start = 0;
  String val = null;
  
  try {
    Log log = Log.getDefault();
    //log.setLevel(SystemLog.DEBUG);
    JDBCSession sess = getInstance(log);
    
    String id = SessionUtil.newSessionID();
    //create
    System.out.println(">>>creating new session");
    sess.create(con, id); 
    System.out.println("....done");
    
    System.out.println(">>>session exists ?");
    System.out.println(sess.exists(con, id));   
    
    System.out.println(">>>session info");
    System.out.println(sess.sessionInfo(con, id));    
    
    System.out.println(">>>expiring session");
    sess.expire(con, id);   

    System.out.println(">>>session info");
    System.out.println(sess.sessionInfo(con, id));    

    System.out.println(">>>session exists ?");
    System.out.println(sess.exists(con, id));   

    System.out.println(">>>creating another session");
    id = SessionUtil.newSessionID();
    sess.create(con, id); 
    System.out.println("....done");
    
    System.out.println(">>>session info");
    System.out.println(sess.sessionInfo(con, id));    
    
    System.out.println(">>>setting session expire seconds to 5 seconds");
    sess.setExpireTime(5);    
    
    System.out.println(">>>set delete expired seconds to false");
    sess.setDeleteExpiredSessions(false);   

    System.out.println(">>>sleeping 5 seconds");
    Thread.currentThread().sleep(5000);   
    System.out.println(">>>expiring all invalid sessions...");
    sess.expireInactiveSessions(con);   
  
    System.out.println(">>>adding value foo=bar to an expired session");
    try {
      sess.add(con, id, "foo", "bar");    
      }
    catch (SQLException e) {
      ;System.out.println("Expecting the following exception");
      e.printStackTrace();
      }
      
    System.out.println(">>>getting value for foo");
    for (int n = 0; n < 10; n++) {
      start = System.currentTimeMillis();
      val = sess.get(con, id, "foo");
      System.out.println("time: " + (System.currentTimeMillis() - start) + " ms");
      }
    System.out.println(val);    

    id = SessionUtil.newSessionID();
    sess.create(con, id); 

    Map map = new HashMap();
    map.put("foo2", "bar2");
    map.put("foo3", "bar3");
    System.out.println(">>>adding map " + map);
    sess.addAll(con, id, map);    

    System.out.println(">>>getting all values");
    System.out.println(sess.getAll(con, id));   

      System.out.println(">>> tie session to user");
    sess.tieToUser(con, id, "1");     
    //sess.tieToUser(con, id, 1);     

      System.out.println(">>> session for userID=1");
    start = System.currentTimeMillis();
      List list = sess.getForUser(con, 1);
    System.out.println("time: " + (System.currentTimeMillis() - start) + " ms");
    System.out.println(list);     
    
/*
    sess.setExpireSeconds(1);
    sess.setDeleteExpiredSessions(true);    
    Thread.currentThread().sleep(1100);   
    sess.expireInactiveSessions(con);   
*/    
    }
  catch (Exception e) {
    e.printStackTrace();
    } 
  }
}