| Nicolas Lehuen 2006-08-09, 7:12 am |
| Hi Earle,
Thanks for your input.
For the sake of performance, you way want to call a single "DELETE FROM
session WHERE accessed < ?" request instead of fetching all the session id
and access time from the database and calling a delete per session. See for
example this code :
http://svn.apache.org/viewvc/httpd/....py?view=markup
I think mysql has also the support for INSERT OR UPDATE requests which are
quite handy when implementing do_save.
Regards,
Nicolas
2006/8/9, Earle Ady <earle@bluelavatech.com>:
>
> Hello everyone!
>
> The topic of a mysql session implementation has come up here several
> times in the past. We have recently written one in-house and have
> been running it on a production infrastructure for some time without
> incident. As it seems a fairly common component which people can
> benefit from, it's being provided below for consideration.
>
> Currently this was built directly into Session.py and takes
> configuration options through httpd.conf. Clearly this could be
> something which may need to be completely segmented from mod_python
> given the reliance on mysql being installed. Hopefully this can get
> the conversation going.
>
> Please note that no connection pooling has been implemented in this
> version, and the benefits of doing this may or may not prove valuable
> depending on the scope of its use. For our clustered session
> environment it was unnecessary.
>
> httpd.conf example:
> PythonOption session MySQLSession
> PythonOption session.db sessions
> PythonOption session.db_host db.whatever.com
> PythonOption session.db_user user
> PythonOption session.db_password pass
> PythonOption session.timeout 3600
>
> Mahalo,
> earle.
>
> 33d32
> < import MySQLdb
> 704,829d702
> <
> <
> ########################################
################################
> ###
> < ## MySQLSession
> <
> < class MySQLSession(BaseSession):
> <
> < def __init__(self, req, sid=0, secret=None, timeout=0, lock=1):
> <
> < self.db = None
> < self.db_host = None
> < self.db_user = None
> < self.db_password = None
> < self.timeout = timeout
> < self.req = req
> <
> < opts = req.get_options()
> <
> < # parse httpd.conf for DB information
> < for opt in [ 'db', 'db_host', 'db_user', 'db_password' ]:
> < if opts.has_key('.'.join([ 'session', opt ])):
> < setattr(self, opt, opts['.'.join([ 'session', opt ])])
> < else:
> < self.req.log_error("MySQLSession: missing
> PythonOption: session.%s" % (opt, ),
> < apache.APLOG_NOTICE)
> <
> < raise apache.SERVER_RETURN,
> apache.HTTP_INTERNAL_SERVER_ERROR
> <
> < if opts.has_key('session.timeout'):
> < self.timeout = opts['session.timeout']
> <
> < BaseSession.__init__(self, req, sid=sid, secret=secret,
> < timeout=timeout, lock=lock)
> <
> < def do_cleanup(self):
> <
> < data = [self._req.server, ]
> <
> < self._req.register_cleanup(mysqldb_cleanup, data)
> < self._req.log_error("MySQLSession: registered database
> cleanup.",
> < apache.APLOG_NOTICE)
> <
> < def do_load(self):
> < self.conn = MySQLdb.connect(host = self.db_host, user =
> self.db_user,
> < passwd = self.db_password, db =
> self.db, use_unicode=1,
> < init_command="set names utf8")
> <
> < self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
> <
> < try:
> < self.cursor.execute("SELECT * FROM session WHERE id=%
> s" , self._sid)
> < res = self.cursor.fetchone()
> <
> < if res is not None and res.has_key('data'):
> < retVal = cPickle.loads(res['data'].tostring())
> < else:
> < retVal = None
> < except:
> < retVal = None
> <
> < self.cursor.close()
> < self.conn.close()
> <
> < return retVal
> <
> < def do_save(self, dict):
> < self.conn = MySQLdb.connect(host = self.db_host, user =
> self.db_user,
> < passwd = self.db_password, db =
> self.db, use_unicode=1,
> < init_command="set names utf8")
> <
> < self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
> < self.cursor.execute("SELECT * from session where id=%s" ,
> self._sid)
> <
> < res = self.cursor.fetchone()
> <
> < if res is None:
> < self.cursor.execute("INSERT INTO session (id, data,
> created, accessed, timeout) VALUES (%s, %s, NOW(), NOW(), %s)",
> (self._sid, cPickle.dumps(dict), self._timeout, ))
> < else:
> < self.cursor.execute("UPDATE session SET data=%s,
> accessed=NOW() WHERE id=%s" , (cPickle.dumps(dict), self._sid, ))
> <
> <
> < self.cursor.close()
> < self.conn.close()
> <
> <
> < def do_delete(self):
> < self.conn = MySQLdb.connect(host = self.db_host, user =
> self.db_user,
> < passwd = self.db_password, db =
> self.db, use_unicode=1,
> < init_command="set names utf8")
> <
> < self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
> <
> < self.cursor.execute("DELETE FROM session WHERE id=%s",
> (self._sid,))
> <
> < self.cursor.close()
> < self.conn.close()
> <
> <
> < def mysqldb_cleanup(data):
> < try:
> < conn = MySQLdb.connect(host = self.db_host, user =
> self.db_user,
> < passwd = self.db_password, db = self.db,
> use_unicode=1,
> < init_command="set names utf8")
> <
> < cursor = conn.cursor(MySQLdb.cursors.DictCursor)
> < server = data
> <
> < expired = []
> <
> < sessions = cursor.execute("SELECT *, UNIXTIME(accessed) AS
> uaccessed, UNIXTIME(created) AS ucreated FROM session")
> <
> < for session in sessions:
> < if ((time.time() - session['accessed']) > session
> ['timeout']):
> < expired.append(session)
> <
> < for session in expired:
> < cursor.execute("DELETE FROM session WHERE id=%s",
> (session['id'],))
> <
> < except:
> < pass
> <
> < try:
> < cursor.close()
> < conn.cose()
> < except:
> < pass
> <
> <
> 856,857d728
> < elif sess_type == 'MySQLSession':
> < sess = MySQLSession
>
>
>
>
>
>
|