[wp-hackers] Re: Porting Wordpress to PostgreSQL

[Ryan Boren]

Martin wrote:
> I too am only a lurker here but I would be very interested in allowing WP to
> use postgres.
> I have looked into it a bit and found that they are using a db layer (a
> modified ezSQL) that should already allow them to use postgres without too
> much problem.
> The main hurdle I see is that the database schema has some mysqlism that we
> would need to work around. enums for instance, but i think triggers could
> take care of that for us.

enums will be going away in 2.1.  We eliminated many of them when we 
removed the linkcategories table.  There are a couple more to go.

One approach is to transform WP's queries into a syntax appropriate to 
the target DB.  This is done on the fly via a custom wpdb.php.  Someone 
did this with sqlite.

http://trac.wordpress.org/ticket/2317


 > But I suppose that is all a bit premature without first hearing from the
 > devs.. =)

We've been slowly massaging our SQL into more portable forms.  Patches 
to this end are gladly accepted.  There are some fixes in that sqlite 
patch that I'd like to work in.  As for supporting multiple DBs, I'd 
like to start by encouraging the approach above.  Provide a custom 
wpdb.php that translates WP's queries into the necessary form.  This 
will become easier as we get rid of enums and the like.

As for query cleanup, let's do this:

* Remove the remaning enums.  Anyone want to tackle this?  We need to 
provide upgrade code and go through the source looking for those places 
affected by the change.  The main enums remaining are the *_status 
fields.  The can be changed to varchars.  link_visible, autoload, and 
option_can_override can be changed to bools (tinyints).  There might be 
some plugin compatibility problems with changing link_visible to a 
tinyint instead of a string.  Need to see how many plugins check 
link_visible.  I think option_can_override is dead.  We might be able to 
remove it entirely.  I don't think any plugins use autoload directly, so 
we should be able to change it without breaking plugins.

* General cleanups.  Make sure values are quoted, etc. Don't provide a 
value for auto-incremented IDs. There are some fixes like this in the 
sqlite patch that should be incorporated into core.  A separate patch 
for those would be nice.

Ryan