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    
006    package fc.web.servlet;
007    
008    import java.io.*;
009    import java.sql.*;
010    import java.util.*;
011    import javax.servlet.*;
012    import javax.servlet.http.*;
013    
014    import fc.io.*;
015    import fc.jdbc.*;
016    import fc.util.*;
017    
018    /** 
019    Stores 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
021    container memory (memory backed sessions) and don't use serialization
022    based session persistence as implemented by various session containers.
023    Use database sessions for storing secure and/or large amounts of data 
024    and store non-secure data directly in cookies.
025    <p>
026    Database sessions enable front-end webservers to scale easily. (No
027    messy/brittle session migration hacks are required !). Scaling can be near
028    infinite, but after a point (say 100 front ends), the back-end databases may
029    also have to be partitioned (and the appropriate partition/machine invoked for
030    a given session-id).
031    <p>
032    Note, memory sessions are not needed even for maintaining html form-state
033    because each form's state can be uniquely created from an initial form
034    state (common to all users) along with the per-usr data submitted per
035    request. This is exactly how the {@link fc.web.forms.Form form} API works.
036    However, memory sessions <u>are</u> useful for certain transient things/caches 
037    or for very <i>quick'n'dirty</i> apps, where using a database is more trouble 
038    than it's worth.
039    <p>
040    Given fast ethernet connections and forward marching processor/peripheral
041    speeds, JDBC session data is typically retrieved on the order of 1-2
042    milliseconds (or even lesser).
043    <p>
044    Using this class requires the following database table schema in
045    the database:
046    <pre>
047    create 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      
053    create 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      
064    alter 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
069    alter table sessionmaster add
070       FOREIGN KEY (user_id) REFERENCES <font color=green>
071        NAME_OF_USER_TABLE</font>;
072    </pre>
073    <p>
074    When 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>
077    The above tables can also be created by this class itself. Invoke the main
078    method without any arguments to see usage information. The table names
079    <font color=blue>sessionmaster</font> and <font color=blue>sessiondata</font>
080    come from final static variables in this class. If you want to use different
081    names, change these variables and recompile this class.
082    <p>
083    More than one [name, value] pair can be stored in the <font
084    color=blue>sessiondata</font> table. This class will automatically store
085    [name, value] pairs as seperate rows and can return a particular [name,
086    value] pair or all [name, value] pairs for the specified session.
087    <p>
088    Note: This class allows saving data only as Strings/text. However,
089    arbitrary binary data can also be stored but the caller should first
090    base64 encode that data and then save it as a string. Upon retrieval, that
091    data can be base64 decoded.
092    <p>
093    Note 2: Under some scenarios, it is <b>important</b> to have a separate
094    cleaner process that periodically deletes expired sessions from the
095    database. This process should run via cron, some other stand-alone java
096    code 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
098    time. Under other scenarios, sessions may be deleted after a set amount of
099    time from the creation data regardless of when the session was last
100    accessed.
101    <p>
102    Note 3: Typically, expired session data is simply deleted from the session
103    tables in the database. (for example, amazon.com users have to persist
104    their cart manually by clicking on a "save-for-later" button - which moves
105    data to more persistent tables -- otherwise their cart session is deleted
106    from the database when the session expires). It is however possible that
107    instead of deleting sessions, the sessions are instead marked as "expired"
108    but not deleted from the database. (this is done via the {@link
109    #setDeleteExpiredSessions} method.
110    
111    @author hursh jain
112    */
113    public final class JDBCSession
114    {
115    Log log = Log.get("fc.web.servlet.JDBCSession");
116    
117    public static final String SESSIONDATA_TABLE  = "sessiondata";
118    public static final String SESSIONMASTER_TABLE  = "sessionmaster";
119    
120    //queries
121    String  data_insert;
122    String  data_update;  
123    String  data_get; 
124    String  data_getall;   
125    String  data_delete;
126    String  delete_session;    
127    String  expire_session; 
128    String  new_session_with_user; 
129    String  new_session; 
130    String  tie_session_to_user; 
131    String  session_exists;
132    String  session_for_user;
133    String  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.
137    boolean deleteExpiredSessions = true;
138    
139    //sessions eligible for removal after this time of inactivity.
140    int   expireSeconds = 60*60*8; //8 hours default
141    
142    private static JDBCSession instance;
143    private JDBCSession()
144      {
145      }
146    
147    /*
148    Impl note: haven't used the fc.jdbc.dbo API to abstract our two database
149    sesssion-tables, maybe later but for right now it's simpler (and
150    more orthogonal) to handcode the sql for our prepared statements directly.
151    */
152    
153    /**
154    Returns an instance of JDBCSession.
155    */
156    public static JDBCSession getInstance() 
157      {
158      return init(null);
159      }
160    
161    
162    /**
163    Returns 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    */
168    public static JDBCSession getInstance(Log logger) 
169      {
170      return init(logger);
171      }
172    
173    private 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    
184    private 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    /**
251    By default expired sessions are deleted from the db. If this is set to
252    false, they are instead marked as expired in the db.
253    */
254    public void setDeleteExpiredSessions(final boolean val)
255      {
256      deleteExpiredSessions = val;
257      }
258    
259    //session mgmt
260    
261    /**
262    Creates 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    */
285    public void create(
286      final Connection con, final String sessionID, final String userID) 
287    throws 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    /**
315    Creates a new session. The specified session can later be optionally to a userID
316    by invoking the {@link #tieToUser} method.
317    <p>
318    Note, sessionID's are typically be stored as a cookie or URL encoded on
319    the client and are thus unique per browser/client). A user is not
320    required 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    */
333    public void create(final Connection con, final String sessionID) 
334    throws 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    /**
360    Expires the session. By default, deletes all session data associated with
361    the specified sessionID from the database. If {@link
362    #deleteExpiredSessions} is set to <tt>true</tt>, then the session is marked
363    as expired in the database but the rows are not deleted from the db.
364    <p>
365    Either way, after this method returns, the sessionID will not longer be
366    valid.
367    */
368    public void expire(final Connection con, final String sessionID) 
369    throws 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    /**
398    Associates the specified sessionID with the specified userID. <p> Note:
399    Depending on the application, more than 1 sessionID can be associated with
400    the same userID in the session master table.
401    */
402    public void tieToUser(
403      final Connection con, final String sessionID, final String userID) 
404    throws 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    /**
424    Utility method that deletes (or marked as expired depending on {@link
425    #setDeleteExpiredSessions}) all sessions in the database that have exceeded
426    the maximum inactive time.
427    */
428    public void expireInactiveSessions(final Connection con) 
429    throws 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    /**
472    Sessions inactive for greater than these number of seconds will be 
473    eligible for expiry. <p><b>Note:</b> these expired sessions will still not
474    be expired until the {@link expireInactiveSessions()} method is invoked.
475    <p>Defaults to <tt>8 hours (=60*60*8 seconds)</tt>
476    */
477    public void setExpireTime(final int seconds) {
478      expireSeconds = seconds;
479      }
480    
481    /**
482    Returns the current expire interval (seconds after which
483    sessions can be considered eligible for removal).
484    */
485    public int getExpireTime() {
486      return expireSeconds;
487      }
488    
489    /** 
490    Returns true is the specified sessionID is valid (i.e., the specified
491    sessionID exists in the database and has not expired). 
492    <p> 
493    Note: this method does <b>not</b> expire the session itself or check for
494    non-expired validity. Sessions should be expired as/when needed by calling the
495    {@link expire} method.
496    */ 
497    public boolean exists(final Connection con, final String sessionID) 
498    throws 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    /**
526    Returns session information from the session master table. This information
527    is returned as a {@link Session.Info info} object encapsulating the master
528    row for the given sessionID. <p> Returns <tt>null</tt> if the given
529    sessionID has expired and/or was not found in the database.
530    */
531    public JDBCSession.Info sessionInfo(
532      final Connection con, final String sessionID) 
533    throws 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    /**
562    Returns a List containing {@link Info session information} about all
563    sessions associated with the specified ID. Returns an empty list if no
564    sessions are found for the specified userID.
565    <p>
566    Note, the specified userID can be null in which case all sessions with null
567    userID's will be returned.
568    */
569    public List getForUser(final Connection con, final String userID) 
570    throws 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    /**
600    Same as {@link #getForUser(Connection, String)} but takes a numeric userID.
601    */
602    public List getForUser(final Connection con, final int userID) 
603    throws SQLException
604      {
605      return getForUser(con, String.valueOf(userID));
606      }
607    
608    /**
609    Information about a session.
610    */
611    public 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    /** 
637    Returns a map of all [key, value] pairs associated with the specified
638    sessionID. Returns <code>null</code> if the specified sessionID is not found 
639    in the database or if the specified session has expired.
640    */
641    public Map getAll(final Connection con, final String sessionID) 
642    throws 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    /**
685    Returns the value associated with the specified sessionID and key.
686    <p>
687    Returns <tt>null</tt> if the specified session has expired, or the specified
688    key does not exist in the database or exists but contains a <tt>null</tt> in
689    the database.
690    */
691    public String get(
692      final Connection con, final String sessionID, final String key) 
693    throws 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    /**
728    Deletes <b>both the key and value</b> specified by the sessionID and key.
729    Does nothing if the sessionID or key does not exist in the database.
730    */
731    public void delete(
732      Connection con, final String sessionID, final String key) 
733    throws 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    /**
755    Saves the tuple [sessionID, key, value] in the database. <p> The specified
756    sessionID must exist and be valid in the database otherwise a SQLException
757    will be thrown.
758    */
759    public void add(
760     final Connection con, final String sessionID, final String key, final String value) 
761    throws  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    /**
792    Adds all [key, value] pairs in the specified map to the session with the
793    specified sessionID.
794    */
795    public void addAll(
796      final Connection con, final String sessionID, final Map data)
797    throws 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    /**
845    An alias for the {@link add #add} method. 
846    */
847    public void put(Connection con, 
848      final String sessionID, final String key, final String value) 
849    throws  SQLException 
850      {
851      add(con, sessionID, key, value);
852      }
853    
854    /**
855    An alias for the {@link #addAll addAll} method. 
856    */
857    public void putAll(final Connection con, final String sessionID, final Map data) 
858    throws SQLException 
859      {
860      addAll(con, sessionID, data);
861      }
862    
863    /**
864    Updates the value for the specified sessionID and key in the database.
865    <p>
866    The specified sessionID and keys must exist in the database prior to
867    calling this method,otherwise a SQLException will be thrown.
868    */
869    public String update(
870      final Connection con, 
871      final String sessionID, final String key, final String newvalue) 
872    throws 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    /**
906    Internal function: updates the session accessed time. should be called from
907    any method that gets/sets session data.
908    */
909    private void updateSessionAccessTime(final Connection con, final String sessionID) 
910    throws 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
933    private final PreparedStatement prepareStatement(
934        final Connection con, final String sql) 
935    throws 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    
946    public 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    /**
989    Creates database tables for storing session data. This method can be
990    called programmatically but is typically invoked by the main method.
991    Invoke the main method without any flags to get usage information.
992    */
993    public static void createJDBCTables(
994      Connection con, String  userTableName, boolean mysql)
995    throws 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    
1088    static 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    }