Dreaming of DawnUPDATE: 1.5.3 Version is out. No guarantees if it works. - more details
All right, the upgrade is done, and now I have one gripe with wordpress - DB support. Mysqli support in particular. I'm one of those people who likes php5. In fact, I've been using it for long enough now I don't REMEMBER how to code in php4. I also use the mysql 4.1 branch and I can't just tell it to use old_passwords, I have other crap on the machine that uses the new stuff. And trying to compile mysqli and mysql together on php5 using the same libraries is....well let's just say it's a pain in the butt. And I'm lazy, but that's another story.
So I decided to dig into the wordpress source, something I'd resisted doing (I have my own stuff to write, thanks) and discovered they ARE semi intelligent and have a db class. So far so good. So I look at the class and....there's nothing that can't be changed quickly and easily to make the thing work with mysqli - hurray. So I decided to delve a bit deeper and discovered something - a (very) basic db abstraction layer is possible. So I made one.
You can download it from here with all the files and a short readme, or continue this article :).
First of all, I didn't want to mess with any of the queries involved in wordpress. But as far as I could initially tell the only db specific stuff they used were the create statements for installs maybe limit, and perhaps a few functions (I wasn't about to go searching for em). In the end I decided to do sqlite, postgresql, and mysqli to go with the typical mysql installation. Those are the dbs I know of that support a similar limit syntax (feel free to correct me) and sqlite even replace so I could fix any queries with a quick regex. (postgres may get nastier on the replace, but I wasn't about to cross that bridge until I came to it.)
So, the first thing to do was to add a variable to wp-config.php - so I have a db type set for use throughout
There's a new feature in the 1.5 in the settings file, it checks to see if you have the mysql extension - so I changed it to look for the defined db type instead
Then I moved on to the magic line that includes the database. This is where things got a bit fun. We have a DB_TYPE to choose, so let's call the db files (which we'll put in the includes folder) wp-db-(DBTYPE).php - sound good? and make the include magical
ooooh, exciting. So we're done with that file. Now for the fun part. Writing three more versions of the db file and putting them in wp-includes. First step? rename wp-db.php to wp-db-mysql.php - and that one doesn't need any changes - oh wait, yes it does. Line 80. What in all the blazing towers of hades has them using addslashes? So it becomes
[php]
return mysql_escape_string($str);
[/php]
Next I copied wp-db-mysql.php and call it wp-db-mysqli.php. Now because I KNEW that the new mysql would accept the same syntax, all I had to do is replace any mysql_whatever calls with mysqli_whatever calls with one caveat, the order of calls has changed so the db handle is always first with mysqli. I also added support for a port or socket for mysqli - add it to your host string with a : so it would be localhost:3009 or localhost:/var/sock/mysql - the class is smart enough to find it ;)
Hmmm, that took me a whole whopping 5 minutes. All right! Now mysqli can use the same, well everything, that mysql can so those two are jim dandy. So lets do the fun of sqlite. First I created wp-db-sqlite.php Sqlite is an odd duck, because it's file based not server/client based. So we don't need host or user or password, all we care about is DB_NAME which had dang well better be a full path to the file cause I'm not messing with trying to find it. - so I completely skipped out the connection part and just worried about selecting the db. Also to force sqlite not to be stupid and return things in alias.column format we're throwing a pragma setting at the db right away, and to make wordpress think this IS sqlite, we'll register in some php functions as db functions - MY FAVORITE SQLITE FEATURE
Now for the installation stuff. I'm not doing any upgrade hacking at this point - because no one will have a postgresql or sqlite installation to upgrade and mysqli can use the same upgrade stuff. result? the config file now gets written with the db type in it :)
The db schemas had to be redone seperately for sqlite and postgres. Sqlite has an "I don't care" for stuff - pretty much I chose INTEGER PRIMARY KEY for any autoincrement columns, text for any text columns, and integer for any integer columns, voila the end, besides making sure EVERY not null has a default value - sqlite doesn't like that.
Postgresql was pretty fun too, I made liberal use of auto sequences through bigserial - that way I know EXACTLY what my sequence name should be when I have to get last_insert_ids. Enums were faked with char fields and check constraints, that's a nifty trick.
So now sqlite was all ready, and I notice a nifty bug - in install.php the default category is given an id of zero. Well, mysql is stupid/smart enough that instead it gives an id of one to the category. But sqlite takes it at face value. But the category listing recursion doesn't check to see if $parent is the same as $id to halt the recursion so it goes indefinately (or until xdebug kills it for me :) so install.php has to be altered...for one stupid number. I could have fixed the logic in the recursive functions but I'm lazy. I'm trying to keep the edited files to a mimimum...
At first postgres actually didn't give me as many problems as sqlite did. It's strict too, like sqlite, but most problems I'd already found ways around. Until I got to the actual queries in the application. I didn't want to touch the queries but it appears I have to. First of all, in standard sql (and even in mysql) strings are single quoted and identifers are double quoted. Now mysql lets lazy people double quote strings, but about the time you start double quoting strings and somebody has mysql running in ansi mode, all hell is going to break loose. Why do it? Actually, when looking at the code it just looks like someone was using single quotes and got lazy in php - used double quotes so they didn't have to escape them...ARGH, suppose that's a bug ;)
Next we deal with the fact that postgres is non-case sensitive without identifiers - in other words all those IDs become ids - why the tables aren't all lower case is beyond me- but hey it's not my db mess. So the big barf is assigning a user_ID (why not user_id...grrr) so more preg_replace
The next problem was select * with a group by clause, which can make a query barf real fast on postgres. Come to find out it's selecting all from posts and grouping by id - now maybe it's just me but that id is PRIMARY KEY - there's only one in the table, people, come on. So that means special case preg_replace..wahoo fun. Then postgres is REALLY strict about select distinct, so I had to create a little preg mess to fix the order by for those queries.
Now, I discovered that instead of inserting a null and grabbing the last_insert_id, someone decided to do show table status and grab the autoincrement value (bangs head on wall) ARGH - that means it breaks sqlite too - at this point I'm about ready to scream in frustration.
whoever did this is going straight to hell - seriously
[php]
$id_result = $wpdb->get_row("SHOW TABLE STATUS LIKE '$wpdb->posts'");
$post_ID = $id_result->Auto_increment;
[/php]
now I have a REALLY easy fix for pgsql - this is basically emulating sequences nextval stuff, sqlite isn't too bad - just select the max rowid and add one- but still I'm pregging out so much on these nasty queries, and that DOESN'T count the user functions I've added into both sqlite and postgres...
And then we find in categories not ONLY did they do the evil above, but then instead of inserting that new value, they used zero instead - NOT NULL which will really do an autoinc, but a quoted zero -which creams postgres.... And is something now configurable in mysql and will cause errors later. So in essence another bug. - chalk up another file I have to edit. And the page edit page uses if (0 == unset variable) - oh that's smart. It wouldn't be so bad but farther down the page it's used in a query, and although mysql might not choke on where integer column = '' (empty string) postgres certainly does. So again, here I am editing a file. I was really trying to stay away from this...
OK, so all the files are edited and stuff works...as far as I can tell.
Installation should be pretty simple. For a new install download wordpress and throw it on your server, then download the wpdb.zip file and unzip it, take all the files and folders inside and drop them right over top of your existing wordpress files. The right stuff should get overwritten. Now create your config file in the usual way - either use the config creation script (it has been changed) or edit config.sample.php by hand. The only difference is mysqli can add a port or socket to the end of it's host, and there is a space for a db type - choose mysql, mysqli, pgsql, or sqlite
Then do the install just like normal. The install scripts have been rewritten as well. WARNING: sqlite is REALLY slow, I had to up php's set_timeout in the script, and you might have to set that even higher. However, it runs pretty swift after installation so don't let the install speed fool you. You might also want to rethink sqlite on a real site, specially if you're on windows or apache 2, it's VERY easy to get thread lock-ups.
Pgsql is kind of slow for me on windows, don't know why. It may simply be the sheer amount of preg_match and preg_replace I'm doing in the class. Oh well, as far as I can see it works.
Tell me what you think, or don't, but I hope someone enjoys the port :)
Edit: download location moved
There and Back Again » Blog Archive » Mysqli, Sqlite, Pgsql Support for Wordpress 1.5
[...] updates Mysqli, Sqlite, Pgsql Support for Wordpress 1.5 My sister wrote a patch extending wpdb to support the other databases. Its a pretty self contained patch, even going [...]
2005-02-24 10:34 am