Recipe 20.2. Using MySQL-Based Storage in Ruby Applications

Problem

You want to use session storage for Ruby scripts.

Solution

Use the CGI::Session class interface. By default, it uses temporary files for backing store, but you can configure it to use MySQL instead.

Discussion

The CGI::Session class manages session storage. It identifies sessions by means of cookies, which it adds transparently to the responses sent to the client. CGI::Session allows you to supply a storage-management class to be used in place of the default manager that uses temporary files. We'll use the mysql-session package, which is based on the Ruby DBI interface and stores session records using MySQL. mysql-session is available from the Ruby Application Archive. See Appendix A for information about obtaining and installing it.

To use mysql-session in a script, you need to access these modules:

require "cgi" require "cgi/session" require "mysqlstore"

To create a session, first create a CGI object . Then invoke CGI::Session.new, which takes several arguments. The first is a CGI object associated with the script. (This object must exist before you can open the session.) Other arguments provide information about the session itself. Those following are relevant no matter which storage manager you use:

session_key: The session key that the session manager uses as the name of the cookie to be sent to the client. The default key value is _session_key; we will use RUBYSESSID.
new_session: This argument should be true to force a new session to be created, or false to use an existing session, which is assumed to have already been created during a previous request. It's also possible to create a session if it does not exist and use the current session if it does. To enable that behavior, omit the new_session argument (which is what the example script in this recipe does).
database_manager: The name of the class that provides storage management for session records. If this argument is omitted, the session manager uses temporary files.

To use the mysql-session package as the storage manager, the database_manager argument should be CGI::Session::MySQLStore. In that case, mysql-session enables several other arguments for the CGI::Session.new method. You can pass in arguments that instruct the session manager to establish its own connection to MySQL, or you can open your own connection and pass its database handle to the session manager.

The following discussion shows both approaches, but either way, we'll need a table for storing session records. For mysql-session, create a table named ruby_session with the following structure:

CREATE TABLE ruby_session (   session_id    VARCHAR(255) NOT NULL,   session_value MEDIUMBLOB NOT NULL,   update_time   DATETIME NOT NULL,   PRIMARY KEY (session_id) );

Now we return to session creation. To have the session manager open its own connection to MySQL, create the session like this:

cgi = CGI.new("html4") sess_id = cgi.cookies["RUBYSESSID"] session = CGI::Session.new(                   cgi,                   "session_key" => "RUBYSESSID",                   "database_manager" => CGI::Session::MySQLStore,                   "db.host" => "127.0.0.1",                   "db.user" => "cbuser",                   "db.pass" => "cbpass",                   "db.name" => "cookbook",                   "db.table" => "ruby_session",                   "db.hold_conn" => 1                 )

The db. xxx parameters used in that code tell mysql-session how to connect to the server, as well as the database and table to use for session records:

db.host: The host where the MySQL server is running.
db.user, db.pass: The username and password of the MySQL account to use.
db.name, db.table: The database and table name for the session table.
db.hold_conn: By default, mysql-session opens and closes a connection each time it needs to send a statement to the MySQL server. If you supply the db.hold_conn parameter with a value of 1, mysql-session opens the connection only once and holds it open until the session ends.

Another way to create a session is to open your own database connection first and pass the database handle as a parameter:

cgi = CGI.new("html4") sess_id = cgi.cookies["RUBYSESSID"] session = CGI::Session.new(                   cgi,                   "session_key" => "RUBYSESSID",                   "database_manager" => CGI::Session::MySQLStore,                   "db.dbh" => dbh,                   "db.name" => "cookbook",                   "db.table" => "ruby_session"                 )

In this case, the db.host, db.user, db.pass, and db.hold_conn parameters are not used. In addition, you are responsible for closing the connection after the session is no longer needed.

Whichever way you create the session, its ID is available while it is open as the session.session_id attribute.

To close the session, invoke the close method of the session object. If you want to destroy the session instead, invoke its delete method.

The session manager stores data using key/value pairs, using strings for the values. It does not know the types of the values that you store. I find the following strategy useful for dealing with type-conversion issues:

After opening the session, extract values from the session and convert them from "generic" string form to properly typed values.
Work with the typed values until it is time to close the session.
Convert the typed values to string form, store them in the session, and close it.

The following script uses the CGI::Session session manager to track the number of requests in a session and the time of each request. After 10 requests, the script deletes the session to cause a new session to begin for the next request:

#!/usr/bin/ruby -w # sess_track.rb - session request counting/timestamping demonstration require "Cookbook" require "cgi" require "cgi/session" require "mysqlstore" title = "Ruby Session Tracker"; dbh = Cookbook::connect cgi = CGI.new("html4") session = CGI::Session.new(                   cgi,                   "session_key" => "RUBYSESSID",                   "database_manager" => CGI::Session::MySQLStore,                   "db.dbh" => dbh,                   "db.name" => "cookbook",                   "db.table" => "ruby_session"                ) # extract string values from session count = session["count"] timestamp = session["timestamp"] # convert variables to proper types count = (count.nil? ? 0 : count.to_i) timestamp = "" if timestamp.nil? timestamp = timestamp.split(",") # increment counter and add current timestamp to timestamp array count = count + 1 timestamp << Time.now().strftime("%Y-%m-%d %H:%M:%S") # construct content of page body page = "" page << cgi.p {"This session has been active for #{count} requests."} page << cgi.p {"The requests occurred at these times:"} list = "" timestamp.each do |t|   list << cgi.li { t.to_s } end page << cgi.ul { list } if count < 10   # convert session variables to strings, store them back   # into the session, and close (and save) the session   session["count"] = count.to_s   session["timestamp"] = timestamp.join(",")   session.close() else   # destroy session after 10 invocations   session.delete() end dbh.disconnect # generate the output page cgi.out {   cgi.html {     cgi.head { cgi.title { title } } +     cgi.body("bgcolor" => "white") { page }   } }

CGI::Session makes no provision for expiring sessions, but you can discard old session records using the technique discussed in Section 20.1. If you do this, you should index the update_time column to make DELETE statements faster:

ALTER TABLE ruby_session ADD INDEX (update_time);