Category Archives: Databases

MySQL to PostgreSQL

David Fetter informed me that he has some changes to a document I wrote back in 2005 about migration tips going from MySQL to PostgreSQL. As such I started to revamping the document, moving from DocBook 4.3 to 4.5 in the process. The result will be located at I hope to have his comments merged in in the remainder of the week. Feel free to send me updates after it got properly updated with David’s notes.

SQLAlchemy and simple WHERE clauses using AND

These posts use code from the Trac project. I’m using the question mark notation for in-place variable substitution, this is where you normally use either direct variables or an indirection mechanism.

If you have done SQL before you are familiar with the syntax such as:

SELECT name FROM auth_cookie WHERE cookie = ? AND ipnr = ?;

So, how does one do this with SQLAlchemy?

With SQLAlchemy (SA in short) you first declare a schema within Python:

auth_cookie = Table('auth_cookie', metadata,
        Column('cookie', String, primary_key=True),
        Column('name', String, primary_key=True),
        Column('ipnr', String, primary_key=True),
        Column('time', Integer))

Next you import this schema (living within Trac as trac/db/ as follows:

from trac.db.schema import auth_cookie

This allows direct manipulation using direct calls to auth_cookie. So for a SQL select we need to extend our code as follows:

from sqlalchemy import select

This allows us to build an almost equivalent statement as follows:

statement = select([], auth_cookie.c.cookie==?)

To add the AND clause SA has a very simple function to add into your code:

from sqlalchemy import and_, select

This allows us to extend the previous statement as such:

statement = select([], and_(auth_cookie.c.cookie==?, auth_cookie.c.ipnr==?)

Similarly there’s an or_() function as well, which works exactly the same.

Now the difficulty arose due to the fact this SQL query changed its WHERE-clause depending on an if/else. The regular case was the first statement we created, the other case added the cookie’s IP number into the equation. So how to deal with that?

statement = select([], auth_cookie.c.cookie==?)
if self.check_ip:

As you can see, depending on whether or not check_ip is set it changes the statement in-place and expands the WHERE-clause with an AND for ipnr.

FreeBSD, SQLite, FTS2 and SQLAlchemy

I was trying to use the SQLite 3.4.1 installed port with Python and SQLAlchemy and the moment I wanted to create a table within the database Python crashed.

After a bunch of debugging it turns out that enabling the FTS2 option of the port causes these crashes. The sqlite3Fts2InitHashTable() call is where it fails. I notified the port maintainer and in the mean time rebuilt without FTS2.

Bugzilla additional database support

I’ve been using Bugzilla for a while now for the TenDRA Project. I am quite happy with it. Right now I use it on a MySQL database, but everybody that really knows me knows I prefer PostgreSQL over MySQL because of the real database features it has.
To counter any knee-jerk reactions, no, MySQL is not bad, I am just used to features real databases provide (views, stored procedures, etc) and once you are used to that, MySQL feels, …, inadequate.

Bugzilla has, in the last year, been working hard on making the program database independent.

See the following URLs for developer details on progress:

Bugzilla PostgreSQL meta bug
Bugzilla Oracle meta bug
Bugzilla Sybase meta bug
Bugzilla Firebird meta bug

This is what I also emailed (the URLs that is) to the authors of IEEE’s Software magazine column about Open Source. They wrote that Bugzilla is not database independent, and while that is undisputable (aside from the ‘hackish’ port to PostgreSQL on the side, which I also worked on) there is significant work being done behind the scenes on making it database independent.