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