001// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
002// The Molly framework is freely distributable under the terms of an
003// MIT-style license. For details, see the molly pages web site at:
004// http://www.mollypages.org/. Use, modify, have fun !
005
006package fc.web.servlet;
007
008import java.io.*;
009import java.sql.*;
010import java.util.*;
011import javax.servlet.*;
012import javax.servlet.http.*;
013
014import fc.io.*;
015import fc.jdbc.*;
016import fc.util.*;
017
018/** 
019Stores session data into a database. This approach is <i>almost</i>
020<u>always</u> the right thing to do. Don't store session data in the servlet
021container memory (memory backed sessions) and don't use serialization
022based session persistence as implemented by various session containers.
023Use database sessions for storing secure and/or large amounts of data 
024and store non-secure data directly in cookies.
025<p>
026Database sessions enable front-end webservers to scale easily. (No
027messy/brittle session migration hacks are required !). Scaling can be near
028infinite, but after a point (say 100 front ends), the back-end databases may
029also have to be partitioned (and the appropriate partition/machine invoked for
030a given session-id).
031<p>
032Note, memory sessions are not needed even for maintaining html form-state
033because each form's state can be uniquely created from an initial form
034state (common to all users) along with the per-usr data submitted per
035request. This is exactly how the {@link fc.web.forms.Form form} API works.
036However, memory sessions <u>are</u> useful for certain transient things/caches 
037or for very <i>quick'n'dirty</i> apps, where using a database is more trouble 
038than it's worth.
039<p>
040Given fast ethernet connections and forward marching processor/peripheral
041speeds, JDBC session data is typically retrieved on the order of 1-2
042milliseconds (or even lesser).
043<p>
044Using this class requires the following database table schema in
045the database:
046<pre>
047create table <b>sessiondata</b> ( 
048  session_id    varchar(50),
049  name         varchar(100) not null,
050  value        text  -- [or some other character/text type]
051  );
052  
053create table <b>sessionmaster</b> (
054  session_id    varchar(50) primary key,  -- the session id
055  created      timestamp not null,   -- session create time
056  accessed     timestamp not null,   -- session last accessed       
057  is_expired   bool default 'f',     -- true if expired 
058  -- a reference to the user table that associates a user_id
059  -- with one or more session_id's. Optional so should be 
060  -- nullable if used.
061  user_id    <font color=green>&lt;SQL_TYPE_FROM_YOUR_USER_TABLE&gt;</font>
062  );
063  
064alter table sessiondata add 
065   FOREIGN KEY (session_id) REFERENCES sessionmaster (session_id) 
066   on delete cascade;
067
068-- The following if a user table is present
069alter table sessionmaster add
070   FOREIGN KEY (user_id) REFERENCES <font color=green>
071    NAME_OF_USER_TABLE</font>;
072</pre>
073<p>
074When creating these tables, it is also advisable to <b>index</b> the
075<tt>session_id</tt> and <tt>user_id</tt> columns for faster performance.
076<p>
077The above tables can also be created by this class itself. Invoke the main
078method without any arguments to see usage information. The table names
079<font color=blue>sessionmaster</font> and <font color=blue>sessiondata</font>
080come from final static variables in this class. If you want to use different
081names, change these variables and recompile this class.
082<p>
083More than one [name, value] pair can be stored in the <font
084color=blue>sessiondata</font> table. This class will automatically store
085[name, value] pairs as seperate rows and can return a particular [name,
086value] pair or all [name, value] pairs for the specified session.
087<p>
088Note: This class allows saving data only as Strings/text. However,
089arbitrary binary data can also be stored but the caller should first
090base64 encode that data and then save it as a string. Upon retrieval, that
091data can be base64 decoded.
092<p>
093Note 2: Under some scenarios, it is <b>important</b> to have a separate
094cleaner process that periodically deletes expired sessions from the
095database. This process should run via cron, some other stand-alone java
096code etc., and should delete sessions which are marked expired or whose
097<tt>create_time - last_used_time</tt> is greater than the session expiry
098time. Under other scenarios, sessions may be deleted after a set amount of
099time from the creation data regardless of when the session was last
100accessed.
101<p>
102Note 3: Typically, expired session data is simply deleted from the session
103tables in the database. (for example, amazon.com users have to persist
104their cart manually by clicking on a "save-for-later" button - which moves
105data to more persistent tables -- otherwise their cart session is deleted
106from the database when the session expires). It is however possible that
107instead of deleting sessions, the sessions are instead marked as "expired"
108but not deleted from the database. (this is done via the {@link
109#setDeleteExpiredSessions} method.
110
111@author hursh jain
112*/
113public final class JDBCSession
114{
115Log log = Log.get("fc.web.servlet.JDBCSession");
116
117public static final String SESSIONDATA_TABLE  = "sessiondata";
118public static final String SESSIONMASTER_TABLE  = "sessionmaster";
119
120//queries
121String  data_insert;
122String  data_update;  
123String  data_get; 
124String  data_getall;   
125String  data_delete;
126String  delete_session;    
127String  expire_session; 
128String  new_session_with_user; 
129String  new_session; 
130String  tie_session_to_user; 
131String  session_exists;
132String  session_for_user;
133String  session_access_update;
134
135//by default expired sessions are deleted from the db. if this is set to false,
136//they are instead marked as expired in the db.
137boolean deleteExpiredSessions = true;
138
139//sessions eligible for removal after this time of inactivity.
140int   expireSeconds = 60*60*8; //8 hours default
141
142private static JDBCSession instance;
143private JDBCSession()
144  {
145  }
146
147/*
148Impl note: haven't used the fc.jdbc.dbo API to abstract our two database
149sesssion-tables, maybe later but for right now it's simpler (and
150more orthogonal) to handcode the sql for our prepared statements directly.
151*/
152
153/**
154Returns an instance of JDBCSession.
155*/
156public static JDBCSession getInstance() 
157  {
158  return init(null);
159  }
160
161
162/**
163Returns an instance of JDBCSession.
164
165@param  logger          the logging destination for methods in this class. Specify
166                <tt>null</tt> to use a default logger.
167*/
168public static JDBCSession getInstance(Log logger) 
169  {
170  return init(logger);
171  }
172
173private static JDBCSession init(Log logger)
174  {
175  if (instance != null) {
176    return instance;
177    }
178    
179  instance = new JDBCSession();
180  instance.configure(logger);
181  return instance;
182  }
183
184private void configure(Log logger)
185  {
186  if (logger != null)
187    log = logger;
188  
189  data_insert =
190   "insert into " + SESSIONDATA_TABLE +  
191    " (session_id, name, value) values (?, ?, ?) ";
192  
193  data_update =  
194   "update " + SESSIONDATA_TABLE + 
195    " set value=? where session_id=? and name=?";
196
197  data_get = 
198   "select sd.name, sd.value from " 
199    + SESSIONDATA_TABLE   + " as sd, "  
200    + SESSIONMASTER_TABLE + " as sma "
201    + " where sd.session_id=? "
202    + " and sd.name=? "
203    + " and sd.session_id = sma.session_id "
204    + " and sma.is_expired=false"; 
205      
206  data_getall =    
207   "select name, value from " + SESSIONDATA_TABLE +
208    " where session_id=?";
209
210  data_delete =
211   "delete from " + SESSIONDATA_TABLE +  
212    " where session_id=? and name=?";
213    
214  //cascades to sessiondata
215  delete_session =   
216   "delete from " + SESSIONMASTER_TABLE +  
217    " where session_id=? "; 
218
219  expire_session = 
220      "update " + SESSIONMASTER_TABLE +  " set is_expired=? where session_id=? "; 
221      
222  new_session_with_user = 
223   "insert into " + SESSIONMASTER_TABLE + 
224    " (session_id, created, accessed, username) values " 
225    + " (?, ?, ?, ?)";
226
227  new_session = 
228   "insert into " + SESSIONMASTER_TABLE + 
229    " (session_id, created, accessed) values " + 
230    " (?, ?, ?)";
231  
232  tie_session_to_user = 
233    "update " + SESSIONMASTER_TABLE + 
234      " set username=? where session_id=?";
235
236  session_exists =
237   "select session_id, created, accessed, is_expired from " 
238    + SESSIONMASTER_TABLE + " where session_id=?";
239    
240  session_for_user =
241   "select session_id, created, accessed, is_expired from " 
242    + SESSIONMASTER_TABLE + " where username=?";
243
244  session_access_update =
245    "update " + SESSIONMASTER_TABLE + 
246    " set accessed=? where session_id=?";
247    
248  }
249
250/**
251By default expired sessions are deleted from the db. If this is set to
252false, they are instead marked as expired in the db.
253*/
254public void setDeleteExpiredSessions(final boolean val)
255  {
256  deleteExpiredSessions = val;
257  }
258
259//session mgmt
260
261/**
262Creates a new session.
263<p>
264
265@param  con     a jdbc connection
266@param  sessionID value for sessionID, the {@link SessionUtil#newSessionID()}
267          can be used to generate this. Should not be null. It is
268          <b>not</b> recommended that the servlet container
269          generated <tt>jsession_id</tt> cookie be used for this
270          value. This sessionID is then later used to retrieve and
271          work with the created session and this sessionID will
272          typically be stored as a cookie or URL encoded on the
273          client.
274@param  userID    a userID to associate with this session. Note,
275          userID's in user tables are typically auto generated
276          numerical sequences. <b>Stringify numerical values before
277          passing them into this method</b>. This value should
278          <b>not</b> be <tt>null</tt>, otherwise a runtime
279          exception will be thrown.
280@throws SQLException 
281          if a SQL error occurs. Note, also thrown if the
282          session_id already exists in the database (since all
283          session_id's must be unique).
284*/
285public void create(
286  final Connection con, final String sessionID, final String userID) 
287throws SQLException 
288  {
289  Argcheck.notnull(sessionID, "sessionID arg was null");
290  Argcheck.notnull(userID, "userID argument was null");
291  
292  /*
293  insert into sessionmaster 
294  (session_id, created, accessed, username) values (?, ?, ?, ?)
295  */
296
297  final PreparedStatement ps = prepareStatement(con, new_session_with_user);
298  ps.setString(1, sessionID);
299
300  final long now = System.currentTimeMillis();
301  final java.sql.Timestamp ts = new java.sql.Timestamp(now);
302  ps.setTimestamp(2, ts);
303  ps.setTimestamp(3, ts);
304  ps.setString(4, userID);
305    
306  log.bug("Query to run: ", ps);
307  
308  final int n = ps.executeUpdate();
309  if (n != 1) {
310    log.warn("Query:", ps, "should have returned 1 but returned:", new Integer(n));
311    }
312  }
313
314/**
315Creates a new session. The specified session can later be optionally to a userID
316by invoking the {@link #tieToUser} method.
317<p>
318Note, sessionID's are typically be stored as a cookie or URL encoded on
319the client and are thus unique per browser/client). A user is not
320required to login to have session data.
321
322@param  con      a jdbc connection
323@param  sessionID  value for sessionID, the {@link SessionUtil#newSessionID()}
324           can be used to generate this. Should not be null. It is
325           <b>not</b> recommended that the servlet container
326           generated <tt>jsession_id</tt> cookie be used for this
327           value. This sessionID is then later used to retrieve and
328           work with the created session.
329@throws SQLException if a SQL error occurs. Note, also thrown if the
330           session_id already exists in the database (since all
331           session_id's must be unique).
332*/
333public void create(final Connection con, final String sessionID) 
334throws SQLException 
335  {
336  Argcheck.notnull(sessionID, "sessionID arg was null");
337
338  /*
339  insert into sessionmaster 
340  (session_id, created, accessed) values (?, ?, ?)
341  */
342
343  final PreparedStatement ps = prepareStatement(con, new_session);
344  ps.setString(1, sessionID);
345
346  final long now = System.currentTimeMillis();
347  final java.sql.Timestamp ts = new java.sql.Timestamp(now);
348  ps.setTimestamp(2, ts);
349  ps.setTimestamp(3, ts);
350    
351  log.bug("Query to run: ", ps);
352  
353  final int n = ps.executeUpdate();
354  if (n != 1) {
355    log.warn("Query:", ps, "should have returned 1 but returned:", new Integer(n));
356    }
357  }
358
359/**
360Expires the session. By default, deletes all session data associated with
361the specified sessionID from the database. If {@link
362#deleteExpiredSessions} is set to <tt>true</tt>, then the session is marked
363as expired in the database but the rows are not deleted from the db.
364<p>
365Either way, after this method returns, the sessionID will not longer be
366valid.
367*/
368public void expire(final Connection con, final String sessionID) 
369throws SQLException 
370  {
371  Argcheck.notnull(sessionID, "sessionID arg was null");
372
373  PreparedStatement ps = null;
374  
375  if (deleteExpiredSessions)
376    {
377    /* delete from sessionmaster where session_id=? */
378    ps = prepareStatement(con, delete_session);
379    ps.setString(1, sessionID);
380    }
381  else {
382     /* update sessionmaster set is_expired=?[=>true] where session_id=? */ 
383    ps = prepareStatement(con, expire_session);
384    ps.setBoolean(1, true);
385    ps.setString(2, sessionID);
386    }
387
388  log.bug("Query to run: ", ps);
389  
390  final int n = ps.executeUpdate();
391  if (n != 1) {
392    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]");
393    } 
394  }
395
396
397/**
398Associates the specified sessionID with the specified userID. <p> Note:
399Depending on the application, more than 1 sessionID can be associated with
400the same userID in the session master table.
401*/
402public void tieToUser(
403  final Connection con, final String sessionID, final String userID) 
404throws SQLException
405  {
406  Argcheck.notnull(sessionID, "sessionID arg was null");
407  Argcheck.notnull(userID, "userID arg was null");
408  
409  /* update sessionmaster set username=? where session_id=? */  
410  
411  final PreparedStatement ps = prepareStatement(con, tie_session_to_user);
412  ps.setString(1, userID);
413  ps.setString(2, sessionID); 
414
415  log.bug("Query to run: ", ps);
416  final int n = ps.executeUpdate();
417  if (n != 1) {
418    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]");
419    }
420  }
421
422
423/**
424Utility method that deletes (or marked as expired depending on {@link
425#setDeleteExpiredSessions}) all sessions in the database that have exceeded
426the maximum inactive time.
427*/
428public void expireInactiveSessions(final Connection con) 
429throws SQLException
430  {
431  long now = System.currentTimeMillis(); //in gmt
432  Timestamp ts = new Timestamp(now);
433  
434  String query = null;
435      
436  /* 
437  this works fine in postgres: 
438    timestamp1 - timestamp2 > interval X second 
439  mysql doesn't like it, to subtract dates/timestamps, we need
440  some mysql specific functions.
441  
442  however, 
443    timestamp + interval X second 
444  gives us a timestamp (in both db's) and can be directly compared
445  to another timestamp.
446  */
447  
448  if (deleteExpiredSessions) {
449    query  = "delete from " + SESSIONMASTER_TABLE + " where " +
450    /*
451    "('" + ts + "' - accessed ) > interval '" +expireSeconds+ "' second"; 
452    */
453    "('" + ts + "' + interval '" + expireSeconds + "' second ) > accessed ";
454    }
455  else {
456    query = "update " + SESSIONMASTER_TABLE 
457    + " set is_expired=? where " +
458    /*
459    + "('" + ts + "' - accessed ) > interval '" +expireSeconds+ "' second"; 
460    */
461    "(timestamp '" + ts + "' + interval '" + expireSeconds + "' second ) > accessed ";
462    }
463  
464  final PreparedStatement ps = con.prepareStatement(query);
465  ps.setBoolean(1, true);
466  log.bug("Query to run: ", ps);
467  final int n = ps.executeUpdate();
468  log.info(new Integer(n), " sessions reaped by: ", query);
469  }
470  
471/**
472Sessions inactive for greater than these number of seconds will be 
473eligible for expiry. <p><b>Note:</b> these expired sessions will still not
474be expired until the {@link expireInactiveSessions()} method is invoked.
475<p>Defaults to <tt>8 hours (=60*60*8 seconds)</tt>
476*/
477public void setExpireTime(final int seconds) {
478  expireSeconds = seconds;
479  }
480
481/**
482Returns the current expire interval (seconds after which
483sessions can be considered eligible for removal).
484*/
485public int getExpireTime() {
486  return expireSeconds;
487  }
488
489/** 
490Returns true is the specified sessionID is valid (i.e., the specified
491sessionID exists in the database and has not expired). 
492<p> 
493Note: this method does <b>not</b> expire the session itself or check for
494non-expired validity. Sessions should be expired as/when needed by calling the
495{@link expire} method.
496*/ 
497public boolean exists(final Connection con, final String sessionID) 
498throws SQLException 
499  {
500  Argcheck.notnull(sessionID, "sessionID arg was null");
501  
502  /*
503  select session_id, created, accessed, is_expired from sessionmaster 
504  where session_id=?
505  */
506  final PreparedStatement ps = prepareStatement(con, session_exists);
507  ps.setString(1, sessionID);
508  
509  log.bug("Query to run: ", ps);
510  
511  final ResultSet rs = ps.executeQuery();
512  boolean exists = false; 
513    if (rs.next()) 
514      {  //sessionID exists
515    boolean is_expired = rs.getBoolean(4); 
516    if (! is_expired) { //and is unexpired
517      exists = true;
518      }
519      }
520    
521    return exists;
522  }
523
524
525/**
526Returns session information from the session master table. This information
527is returned as a {@link Session.Info info} object encapsulating the master
528row for the given sessionID. <p> Returns <tt>null</tt> if the given
529sessionID has expired and/or was not found in the database.
530*/
531public JDBCSession.Info sessionInfo(
532  final Connection con, final String sessionID) 
533throws SQLException
534  {
535  Argcheck.notnull(sessionID, "sessionID arg was null");
536  
537  /*
538  select session_id, created, accessed, is_expired from 
539      sessionmaster where session_id=?
540  */
541  final PreparedStatement ps = prepareStatement(con, session_exists);
542  ps.setString(1, sessionID);
543  
544  log.bug("Query to run: ", ps);
545
546  JDBCSession.Info info = null;
547  
548  ResultSet rs = ps.executeQuery();
549
550    if (rs.next()) {  //sessionID exists
551    info = new JDBCSession.Info();
552    info.sessionID = sessionID;
553    info.created = rs.getTimestamp(2);
554    info.accessed = rs.getTimestamp(3);   
555    info.is_expired = rs.getBoolean(4);
556      }
557    
558    return info;
559  }
560
561/**
562Returns a List containing {@link Info session information} about all
563sessions associated with the specified ID. Returns an empty list if no
564sessions are found for the specified userID.
565<p>
566Note, the specified userID can be null in which case all sessions with null
567userID's will be returned.
568*/
569public List getForUser(final Connection con, final String userID) 
570throws SQLException
571  { 
572  if (userID == null)
573    log.warn("userID arg was null, was this intentional ?");
574
575  /*
576  select session_id, created, accessed, is_expired from 
577      sessionmaster where username=?
578  */
579  final PreparedStatement ps = prepareStatement(con, session_for_user);
580  ps.setString(1, userID);
581  
582  log.bug("Query to run: ", ps);
583
584  List list = new ArrayList();
585  ResultSet rs = ps.executeQuery();
586
587    while (rs.next()) {  //sessionID exists
588    JDBCSession.Info info = new JDBCSession.Info();
589    info.sessionID = rs.getString(1);
590    info.created = rs.getTimestamp(2);
591    info.accessed = rs.getTimestamp(3);   
592    info.is_expired = rs.getBoolean(4);
593      list.add(info);
594      }
595    
596    return list;
597  }
598
599/**
600Same as {@link #getForUser(Connection, String)} but takes a numeric userID.
601*/
602public List getForUser(final Connection con, final int userID) 
603throws SQLException
604  {
605  return getForUser(con, String.valueOf(userID));
606  }
607
608/**
609Information about a session.
610*/
611public static class Info
612  {
613  String sessionID;
614  Timestamp created;
615  Timestamp accessed;
616  boolean is_expired;
617  
618  public String getSessionID() { return sessionID; }
619  public Timestamp getCreated() { return created; }
620  public Timestamp getAccessed() { return accessed; }
621  public boolean getIsExpired() { return is_expired; }
622  
623  private ToString tostr;
624  {
625  tostr = new ToString(this, 
626      ToString.Style.VisibleLevel.DEFAULT);   
627  }
628  public String toString() {
629    return tostr.reflect().render();
630    }
631  }
632 
633
634//--------- value management ----------------
635
636/** 
637Returns a map of all [key, value] pairs associated with the specified
638sessionID. Returns <code>null</code> if the specified sessionID is not found 
639in the database or if the specified session has expired.
640*/
641public Map getAll(final Connection con, final String sessionID) 
642throws SQLException 
643  {
644  Argcheck.notnull(con, "con argument was null");
645  Argcheck.notnull(sessionID, "sessionID argument was null");
646
647  if (! exists(con, sessionID))
648    return null;
649
650  /* 
651  select name, value from sessiondata where session_id=? 
652  */
653
654  final PreparedStatement ps = prepareStatement(con, data_getall);
655  ps.setString(1, sessionID);
656  log.bug("Query to run: ", ps);
657
658  final Map map = new HashMap();
659  final ResultSet rs = ps.executeQuery();
660  boolean hasdata = false;
661
662  if (rs.next())
663    {
664    hasdata = true;
665    String name = rs.getString(1);
666    String value = rs.getString(2); //can be null
667    map.put(name, value);
668    }
669
670  while (rs.next()) 
671    {
672    String name = rs.getString(1);
673    String value = rs.getString(2); //can be null
674    map.put(name, value);
675    }
676
677  if (hasdata) {
678    updateSessionAccessTime(con, sessionID);
679    }
680    
681  return map;
682  }
683
684/**
685Returns the value associated with the specified sessionID and key.
686<p>
687Returns <tt>null</tt> if the specified session has expired, or the specified
688key does not exist in the database or exists but contains a <tt>null</tt> in
689the database.
690*/
691public String get(
692  final Connection con, final String sessionID, final String key) 
693throws SQLException 
694  {
695  Argcheck.notnull(con, "con argument was null");
696  Argcheck.notnull(sessionID, "sessionID argument was null");
697  Argcheck.notnull(key, "key argument was null");
698
699   /*
700   "select sd.name, sd.value from " 
701    + sessiondata   + " as sd, " 
702    + sessionmaster + " as sma "
703    + " where sd.session_id=? " +
704    + " and sd.name=? "
705    + " and sd.session_id = sma.session_id " +
706    + " and sma.is_expired=false"; 
707  */
708
709  final PreparedStatement ps = prepareStatement(con, data_get);
710  ps.setString(1, sessionID);
711  ps.setString(2, key);   //key param is called "name" in db
712
713  log.bug("Query to run: ", ps);
714
715  String result = null;
716  final ResultSet rs = ps.executeQuery();
717  if (rs.next()) 
718    {
719    result = rs.getString(2); //can be null if that IS the associated value
720    updateSessionAccessTime(con, sessionID);
721    }
722  
723  return result;
724  }
725  
726
727/**
728Deletes <b>both the key and value</b> specified by the sessionID and key.
729Does nothing if the sessionID or key does not exist in the database.
730*/
731public void delete(
732  Connection con, final String sessionID, final String key) 
733throws SQLException 
734  {
735  Argcheck.notnull(con, "con argument was null");
736  Argcheck.notnull(sessionID, "sessionID argument was null");
737  Argcheck.notnull(key, "key argument was null");
738    
739  /* Delete from sessiondata where session_id=? and name=? */
740    
741  PreparedStatement ps = prepareStatement(con, data_delete);
742  ps.setString(1, sessionID);
743  ps.setString(2, key);
744
745  log.bug("Query to run: ", ps);
746  int n = ps.executeUpdate();
747  if (n == 0) {
748    log.warn(ps, "key=", key, "[key not deleted, does it exist in the database ?]");
749    }
750  
751  updateSessionAccessTime(con, sessionID);
752  }
753  
754/**
755Saves the tuple [sessionID, key, value] in the database. <p> The specified
756sessionID must exist and be valid in the database otherwise a SQLException
757will be thrown.
758*/
759public void add(
760 final Connection con, final String sessionID, final String key, final String value) 
761throws  SQLException 
762  {
763  Argcheck.notnull(con, "con argument was null");
764  Argcheck.notnull(sessionID, "sessionID argument was null");
765  Argcheck.notnull(key, "key argument was null");
766  
767  if (! exists(con, sessionID))
768    throw new SQLException("The specified sessionID:[" + sessionID + "] has expired");
769
770  /*
771  insert into sessiondata (session_id, name, value) values (?, ?, ?) ";
772  */
773  
774  final PreparedStatement ps = prepareStatement(con, data_insert);
775  ps.setString(1, sessionID);
776  ps.setString(2, key);
777  ps.setString(3, value);
778  
779  log.bug("Query to run: ", ps);
780
781  final int n = ps.executeUpdate();
782  
783  if (n != 1) {
784    log.bug("insert error, preparedstatment", ps,  " returned", new Integer(n));
785    throw new SQLException("Error saving data, inserted row count != 1");
786    }
787  
788  updateSessionAccessTime(con, sessionID);
789  }
790  
791/**
792Adds all [key, value] pairs in the specified map to the session with the
793specified sessionID.
794*/
795public void addAll(
796  final Connection con, final String sessionID, final Map data)
797throws SQLException 
798  {
799  Argcheck.notnull(con, "con argument was null");
800  Argcheck.notnull(sessionID, "sessionID argument was null");
801  Argcheck.notnull(data, "data argument was null");
802
803  Set set = data.entrySet();      
804  int size = set.size();
805  if (size == 0) {
806    log.warn("nothing to do, map contains no data");
807    return;
808    }
809
810  if (! exists(con, sessionID))
811    throw new SQLException("The specified sessionID:[" + sessionID + "] has expired");
812
813  /*
814   insert into sessiondata 
815   (session_id, name, value) values (?, ?, ?) ";
816  */
817  
818  final PreparedStatement ps = prepareStatement(con, data_insert);
819
820  final Iterator entries = set.iterator();
821  while (entries.hasNext()) {
822    Map.Entry e = (Map.Entry) entries.next();
823    String key = e.getKey().toString();
824    String val = e.getValue().toString();
825    ps.setString(1, sessionID);
826    ps.setString(2, key);
827    ps.setString(3, val);
828    ps.addBatch();
829    }
830    
831  log.bug("Query to run: ", ps);
832
833  final int[] result = ps.executeBatch();
834  
835  /*  
836  if (result) {
837    log.bug("insert error, preparedstatment", ps,  " returned 0 items inserted");
838    throw new SQLException("Error saving data, inserted row count == 0");
839    }
840  */
841  updateSessionAccessTime(con, sessionID);
842  }
843
844/**
845An alias for the {@link add #add} method. 
846*/
847public void put(Connection con, 
848  final String sessionID, final String key, final String value) 
849throws  SQLException 
850  {
851  add(con, sessionID, key, value);
852  }
853
854/**
855An alias for the {@link #addAll addAll} method. 
856*/
857public void putAll(final Connection con, final String sessionID, final Map data) 
858throws SQLException 
859  {
860  addAll(con, sessionID, data);
861  }
862
863/**
864Updates the value for the specified sessionID and key in the database.
865<p>
866The specified sessionID and keys must exist in the database prior to
867calling this method,otherwise a SQLException will be thrown.
868*/
869public String update(
870  final Connection con, 
871  final String sessionID, final String key, final String newvalue) 
872throws SQLException 
873  {
874  Argcheck.notnull(con, "con argument was null");
875  Argcheck.notnull(sessionID, "sessionID argument was null");
876  Argcheck.notnull(key, "key argument was null");
877
878  if (! exists(con, sessionID))
879    throw new SQLException("The specified sessionID:[" + sessionID + "] has expired");
880        
881  /* 
882  update sessiondata set value=? 
883      where session_id=? and name=?;
884  */
885              
886  final PreparedStatement ps = prepareStatement(con, data_update);
887  ps.setString(1, newvalue);
888  ps.setString(2, sessionID);
889  ps.setString(3, key);
890  
891  log.bug("Query to run: ", ps);
892
893  String result = null;
894  final ResultSet rs = ps.executeQuery();
895  if (rs.next()) 
896    {
897    result = rs.getString(2); //can be null
898    updateSessionAccessTime(con, sessionID);
899    }
900
901  return result;
902  }
903
904
905/**
906Internal function: updates the session accessed time. should be called from
907any method that gets/sets session data.
908*/
909private void updateSessionAccessTime(final Connection con, final String sessionID) 
910throws SQLException
911  {
912  Argcheck.notnull(sessionID, "sessionID arg was null");
913  
914  final PreparedStatement ps = prepareStatement(con,session_access_update); 
915  long now = System.currentTimeMillis(); //in gmt
916  Timestamp ts = new Timestamp(now);
917    /*
918    update sessionmaster set accessed=? where session_id=?
919    */
920  ps.setTimestamp(1, ts);
921  ps.setString(2, sessionID); 
922  
923  log.bug("Query to run: ", ps);
924  
925  int n = ps.executeUpdate();
926  if (n != 1) {
927    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]");
928    }
929  }
930
931//returns a preparedstatement. clearsParameters() for
932//recycled statements
933private final PreparedStatement prepareStatement(
934    final Connection con, final String sql) 
935throws SQLException
936  {
937  if (! (con instanceof fc.jdbc.PooledConnection) ) { 
938    return con.prepareStatement(sql);
939    }
940  PooledConnection pc = (PooledConnection) con;
941  //System.out.println("sql = " + sql);
942  PreparedStatement ps = pc.getCachedPreparedStatement(sql);
943  return ps;
944  }
945
946public static void main(String args[]) throws Exception
947  {
948  Args myargs = new Args(args);
949  myargs.setUsage(
950  "java fc.web.servlet.JDBCSession -conf conf-file [-loglevel level]\n"
951  + "-test  [tests the database for session tables]\n"
952  + "   -- or --\n"
953  + "-create  [creates session tables in the database]\n"
954  + "   -userTableName=<name of Users table in the database> \n"
955  + "   -userIDColName=the name of username/userid column in Users table.)] \n"
956  );
957  
958  if (myargs.flagExists("loglevel")) {
959    String level = myargs.get("loglevel");
960    Log.getDefault().setLevel(level);
961    }
962
963  String propfile = myargs.getRequired("conf");
964
965  FilePropertyMgr fprops = new FilePropertyMgr(new File(propfile));
966  ConnectionMgr mgr = new SimpleConnectionMgr(fprops);
967
968  String url = fprops.get("jdbc.url");
969  boolean mysql = false;
970  if (url.indexOf("mysql") != -1)
971    mysql = true;
972
973  Connection con = mgr.getConnection();
974
975  if (myargs.flagExists("test")) {
976    test(con);
977    }
978  else if (myargs.flagExists("create")) {
979    String userTableName = myargs.get("userTableName", "users");
980    createJDBCTables(con, userTableName, mysql);  
981    }
982  else
983    myargs.showError();
984  
985  con.close();
986  }
987
988/**
989Creates database tables for storing session data. This method can be
990called programmatically but is typically invoked by the main method.
991Invoke the main method without any flags to get usage information.
992*/
993public static void createJDBCTables(
994  Connection con, String  userTableName, boolean mysql)
995throws Exception
996  {
997  Argcheck.notnull(con, "connection param was null");
998  Argcheck.notnull(userTableName, "userTableName param was null");
999
1000  Log log = Log.getDefault(); 
1001  try {
1002    QueryUtil.startTransaction(con);
1003    
1004    Statement st = null;
1005    String tmp = null;
1006    
1007    tmp = 
1008    "create table " + SESSIONDATA_TABLE + " ("
1009     + " session_id varchar(50),"
1010     + " name  varchar(100) not null,"
1011     + " value text )"
1012     ;
1013    
1014    st = con.createStatement();
1015    log.info("Running: " + tmp);
1016    st.execute(tmp);
1017
1018    tmp = "create INDEX IDX_" + SESSIONDATA_TABLE 
1019      + " ON " + SESSIONDATA_TABLE + "(session_id)";
1020
1021    st = con.createStatement();
1022    log.info("Running: " + tmp);
1023    st.execute(tmp);
1024
1025    tmp = 
1026    "create table " + SESSIONMASTER_TABLE + " ("
1027     + " session_id   varchar(50) primary key, "
1028     + " created      timestamp not null,"
1029     + " accessed     timestamp not null,"
1030     
1031     /*mysql == myshit but then you already knew that*/
1032     + ((mysql) ? 
1033      " is_expired  bool default 0," :    
1034      " is_expired  bool default 'f',") 
1035  
1036     + " username     varchar(255)"
1037     + " )"
1038     ;
1039
1040    st = con.createStatement();
1041    log.info("Running: " + tmp);
1042    st.execute(tmp);
1043
1044    tmp = "create INDEX IDX_" + SESSIONMASTER_TABLE + "_1"
1045      + " ON " + SESSIONMASTER_TABLE + "(username)";  
1046
1047    st = con.createStatement();
1048    log.info("Running: " + tmp);
1049    st.execute(tmp);
1050        
1051    tmp = 
1052    "alter table " + SESSIONDATA_TABLE + " add \n"  
1053    + " FOREIGN KEY (session_id) REFERENCES "
1054    +  SESSIONMASTER_TABLE + " (session_id) "
1055    + " on delete cascade "
1056    ;
1057
1058    st = con.createStatement();
1059    log.info("Running: " + tmp);
1060    st.execute(tmp);
1061  
1062    /* this will only work, generally, if the userid is the same
1063    type in both tables, which is not necessarily the case
1064    */
1065    /*
1066    tmp =
1067    "alter table " + SESSIONMASTER_TABLE + " add "
1068    + " FOREIGN KEY (" + userIDColName + ") REFERENCES "
1069    + userTableName;
1070    ;
1071    st = con.createStatement();
1072    log.info("Running: " + tmp);
1073    st.execute(tmp);
1074    */
1075    QueryUtil.endTransaction(con);
1076    }
1077  catch (Exception e) {
1078    QueryUtil.abortTransaction(con);
1079    /* of course, myshit 5.x does not rollback create table sql statements, 
1080    even when using InnoDB */
1081    log.error("*** JDBC SESSION TABLES WERE NOT CREATED PROPERLY (IF AT ALL)****");
1082    throw e;
1083    }
1084  
1085  log.info("*** JDBC SESSION TABLE SUCCESSFULLY CREATED ***");
1086  }
1087
1088static void test(Connection con) throws Exception {
1089  long start = 0;
1090  String val = null;
1091  
1092  try {
1093    Log log = Log.getDefault();
1094    //log.setLevel(SystemLog.DEBUG);
1095    JDBCSession sess = getInstance(log);
1096    
1097    String id = SessionUtil.newSessionID();
1098    //create
1099    System.out.println(">>>creating new session");
1100    sess.create(con, id); 
1101    System.out.println("....done");
1102    
1103    System.out.println(">>>session exists ?");
1104    System.out.println(sess.exists(con, id));   
1105    
1106    System.out.println(">>>session info");
1107    System.out.println(sess.sessionInfo(con, id));    
1108    
1109    System.out.println(">>>expiring session");
1110    sess.expire(con, id);   
1111
1112    System.out.println(">>>session info");
1113    System.out.println(sess.sessionInfo(con, id));    
1114
1115    System.out.println(">>>session exists ?");
1116    System.out.println(sess.exists(con, id));   
1117
1118    System.out.println(">>>creating another session");
1119    id = SessionUtil.newSessionID();
1120    sess.create(con, id); 
1121    System.out.println("....done");
1122    
1123    System.out.println(">>>session info");
1124    System.out.println(sess.sessionInfo(con, id));    
1125    
1126    System.out.println(">>>setting session expire seconds to 5 seconds");
1127    sess.setExpireTime(5);    
1128    
1129    System.out.println(">>>set delete expired seconds to false");
1130    sess.setDeleteExpiredSessions(false);   
1131
1132    System.out.println(">>>sleeping 5 seconds");
1133    Thread.currentThread().sleep(5000);   
1134    System.out.println(">>>expiring all invalid sessions...");
1135    sess.expireInactiveSessions(con);   
1136  
1137    System.out.println(">>>adding value foo=bar to an expired session");
1138    try {
1139      sess.add(con, id, "foo", "bar");    
1140      }
1141    catch (SQLException e) {
1142      ;System.out.println("Expecting the following exception");
1143      e.printStackTrace();
1144      }
1145      
1146    System.out.println(">>>getting value for foo");
1147    for (int n = 0; n < 10; n++) {
1148      start = System.currentTimeMillis();
1149      val = sess.get(con, id, "foo");
1150      System.out.println("time: " + (System.currentTimeMillis() - start) + " ms");
1151      }
1152    System.out.println(val);    
1153
1154    id = SessionUtil.newSessionID();
1155    sess.create(con, id); 
1156
1157    Map map = new HashMap();
1158    map.put("foo2", "bar2");
1159    map.put("foo3", "bar3");
1160    System.out.println(">>>adding map " + map);
1161    sess.addAll(con, id, map);    
1162
1163    System.out.println(">>>getting all values");
1164    System.out.println(sess.getAll(con, id));   
1165
1166      System.out.println(">>> tie session to user");
1167    sess.tieToUser(con, id, "1");     
1168    //sess.tieToUser(con, id, 1);     
1169
1170      System.out.println(">>> session for userID=1");
1171    start = System.currentTimeMillis();
1172      List list = sess.getForUser(con, 1);
1173    System.out.println("time: " + (System.currentTimeMillis() - start) + " ms");
1174    System.out.println(list);     
1175    
1176/*
1177    sess.setExpireSeconds(1);
1178    sess.setDeleteExpiredSessions(true);    
1179    Thread.currentThread().sleep(1100);   
1180    sess.expireInactiveSessions(con);   
1181*/    
1182    }
1183  catch (Exception e) {
1184    e.printStackTrace();
1185    } 
1186  }
1187}