[mod_python] MySQL Session implementation?

2006/6/20, Sébastien Arnaud <arnaudsj at emedialibrary.org>:
> Thanks Jim, it looks like SQLiteSession is a good start for what I
> want to do.
>
> If I am successful I will share with everybody whatever I come up with.
>
> Thanks!
>
> Sébastien

Hi Sébastien,

One important thing not to overlook if you are going to implement a
MySQLSession class is database connection pooling.

In SQLiteSession, I deliberately did not handle this issue because it
is written in the documentation that connections created in a thread
should not be reused in another thread (see
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading : "# On some
operating systems, a database connection should always be used in the
same thread in which it was originally created. ").

As a result, each and every session load or saved forces a database
connection, which means opening files, reading data structures
(indexes and tables) and syncing them when the connection is closed.
No wonder then that SQLiteSession is slower than FileSession...

As a minimum effort, I could have used thread local variables to make
sure that only one connection is opened by thread, but thread local
variables are a Python 2.4 feature and we need to support Python 2.3.
So I've just let it as is for the moment, since anyway we decided to
put this code in the sandbox until better times.

With MySQL or other DBMS, however, connection pooling can and should
be used for better performances. Writing a connection pool is not very
difficult, but writing it in a stable and resilient way, both
thread-safe and multi-process safe, is a little bit more effort.

One thing to consider is to try to build the connection pool and the
session management code in two different, orthogonal modules, so that
one day we'll be able to release the connection pool for general
purpose.

Regards,
Nicolas