Hacking Wordpress

UPDATE: 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

Comments

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

Adam Trachtenberg

This looks real cool! Couple of quick comments (without really digging into the code):

1) I suggest mysql_real_escape_string() instead of mysql_escape_string(). This is the better/smarter version. (In mysqli, they're actually aliases for each other because old "escape_string()" function went away.)

2) If SQLite is slow during install, try wrapping all the commands inside a transaction. Do it as "BEGIN; /* rest of SQL here */ COMMIT;". If you have a lot of little commands, this can speed things up a lot.

3) I don't use PostgreSQL. You're on your own here. :)

2005-02-24 11:44 am

Tor Bjornrud

FYI, Wordpress isn't going to have php5 support any time soon. There's also very little internal pressure for robust database abstraction.
~Tor

2005-02-24 11:50 am

Tor Bjornrud

Ooops forgot my link.
http://textdrive.com/pipermail/hackers/2004-August/001209.html

2005-02-24 11:51 am

auroraeosrose

Ah, the vagaries of find and replace, yeah, I'll do the different escape string for mysql - tells you how much I actually use it :)

Tor, I'm afraid you've confused me, as far as I know Wordpress already has php5 support - it runs just fine on php5 - in fact this site runs on 5.1 (I like bleeding edge ;) If you mean it won't be designed to utilize php5 features, I'm well aware of that, this was just a stopgap and exercise in boredom for me, I have my own stuff I use on a regular basis and develop. Although it would be if at least the mysqli option could be pushed upstream :)

2005-02-24 12:37 pm

ryan king

Great work.

I personally would like to see WP move towards using real database abstraction. PEAR:DB and MDB seem good candidates to me. (And of course, PDO will be nice, too).

2005-02-27 9:48 pm

auroraeosrose

well, pdo isn't REALLY an abstraction layer, more of a unified way to do db calls - you'd still have to add a limit emulator and a few more things, but it would definitely ease the way

I'm not too fond of pear:db or mdb myself, although pear:db is getting better all the time it's still on the heavy side, and mdb defines the word heavy...why do db abstraction layers think bigger is better?

2005-02-28 2:25 am

Jason Heffner

This was a great job! I've been waiting for sqlite support in WordPress for awhile. It looks like it is fully functional. The only problem I seem to have is with the Presentation Link not working and was wondering if you had any ideas.

I wish I had more time to work on projects and submit code back to them.

2005-03-30 8:32 am

sten

gah... looks like it needs sqlite 3.x. I spent quite a while troubleshooting, till I saw the line like "PRAGMA short_column_names = ON". AFAIK, no worky in sqlite 2.8.x

/me goes off to rebuild sqlite.so.....

2005-04-01 10:54 am

auroraeosrose

it does? I thought that worked earlier too - 2.8.14 is the default for 5.1 sqlite, which is what I used when I wrote the thing. just keeps sqlite from doing a stupid alias.column when returning associative arrays (really a stupid idea...anyway) - it works with 2.8.14 :)

2005-04-01 3:49 pm

iwanttokeepanon

I *love* your "hacks". I do not have root access at work and I used your patches to install WordPress w/ php 5.0.4 and Sqlite. I do not use Apache, rather a smaller C / thttpd derivitave. It keeps my intranet website small and fast.

One thing I have noticed is that all my titles and post content get the quote (") character escaped ("). And if I edit that post w/o deleting the backslash (\), the new post looks like "; i.e. it gets the artificial backslash escaped too. Crap. I tried to compile PHP w/ '--disable-magic-quotes', but to no avail.

Can anybody tell me what is going on??? I sniffed the POST data and the backslashes are not sent by the user-agent!! Off hand, PHP seems to be the culprit.

Thx again for a great WP/PHP boost to limited admins ...

2005-05-05 6:55 pm

iwanttokeepanon

My previous comment had the backslashes removed. Hmmmm. Does mysql statements ignore those? Do I need another regexp in sqlite to emulate mysql. I think PHP is backslasing stuff that sqlite inserts literally. Plz help if u can ...

2005-05-05 7:31 pm

ZoneFog

This is exactly what I was looking for, thanks for all your hard work.
I was wondering if you have testing this with WP 1.5.1.2? Because I am absolutely not able to install WP 1.5.1.2 once I add you changes.

Thanks Again.

2005-06-03 7:18 pm

auroraeosrose

I haven't tried it with wp 1.5.1.2 - I'm not exactly sure what changes have been made but if one of the files I have to alter was altered again, that'll be the culpruit

2005-06-06 4:29 am

Notizblog » Blog Archive » WP Hack: DB Abstraction Layer

[...] e a bit deeper and discovered something - a (very) basic db abstraction layer is possible. So I made one. Konnte ich nicht ausprobieren, da bei sqlite (darauf wollte ich hina [...]

2005-06-21 8:02 am

Interested User

Have you submitted your code back to the Wordpress people? I would definitely like to see this as part of the actual original tree and not a set of patches...

2005-06-26 8:38 am

auroraeosrose

Considering that even a simple patch http://trac.wordpress.org/ticket/1394 - (changing add_slashes to mysql_(real)_escape_string) has not been looked at or addressed I highly doubt anything would come of submitting anymore - especially since a similiar patch (for mysqli only) has already been rejected

2005-06-27 3:32 am

Elleroth

Hmm. I get some funky errors after editing and copying files. I just get a blank page whenever I try to view the index and get the following error in my logs:

[client 192.168.0.6] PHP Fatal error: Call to undefined function get_currentuserinfo() in /home/elleroth/public_html/wp-blog-header.php on line 174

Server currently runs:

Apache 2.0.54
PHP 5.0.4
MySQL 4.1.11 standard

Any ideas? :/

2005-07-03 12:19 pm

Diego

Elleroth, the problem is a missing line in the patched wp-settings.php

Insert this code at line 119:

require (ABSPATH . WPINC . '/pluggable-functions.php');

Just for example the new inseted line must be this:

if ( defined('WP_CACHE') && function_exists('wp_cache_postload') )
wp_cache_postload();

P.S: Great work! It's super, i was looking for this :P

2005-07-07 11:25 am

Diego

Sorry, but in my last post i mean:

Just for example the CODE AFTER new inseted line must be this:

if ( defined(’WP_CACHE’) && function_exists(’wp_cache_postload’) )
wp_cache_postload();

2005-07-07 11:26 am

二三街角 » Blog Archive » 幾個 WP 相關的 project

[...] | Add to del.icio.us or furl | Print this post K2 alpha 關於 K2 的資料. SQLite, PgSQL, MySQLi support for WP, 另外參考一下 wp-hackers 上的討論串, [...]

2005-07-07 4:49 pm

auroraeosrose

I need to
1. upgrade this blog
2. diff the hack against the new wordpress files
3. post the new (fixed) version

but I'll be gone for a few weeks here on vacation so gimme some time :)

2005-07-07 6:29 pm

Diego

I have tested you patch with 1.5.1.3 and it works. (i just added the missing line to make it work).
if you install "svn" you can make a diff file very quickly :P

I hope they will use your patch in the main wordpress tree. It's super, you have done a fantastic work :D

2005-07-08 12:23 am

auroraeosrose

Wow, look at that...July 2 the diff is finally accepted for the add_slashes bug

now if they'd take a look at http://trac.wordpress.org/ticket/1395 maybe I'll make some noise for mysqli support and sqlite support

Sqlite will most likely come BEFORE any other db, why? because of the very powerful way you can register functions for sqlite right from php - why is this useful? because wordpress is VERY dependent on mysql date functions (which I replicated in php for sqlite), there was a thread on hackers about it...personally I prefer to use the db specific date stuff instead of unix timestamps, it's more useful and flexible. The point is to write the functionality into your db abstraction layer - in my pff classes I have a function called, well, functions, that you send a generalized function name (date, time, substring) and any arguments for it, and each db driver then writes out the proper function to use

Postgres is a little more strict with their sql syntax, but they also can use udfs to fake mysql functions so if wordpress would clean it's sql act up a little I could take out the regex stuff in the hack - which does add time onto everything

also, for some odd, strange reason instead of using last_insert_id type stuff there are several places in the wordpress source that use 'show table status' to get the autoincrement value for a table (stupid)

I have svn installed - tortoisesvn is my favorite thing :) , I'll just need to diff every file I changed against the current release - blah

Thanks for the praise - hope you enjoy

2005-07-08 7:28 am

Diego

There are some problems with sql LIMIT statement used by wordpress for some queries (LIMIT is supported by sqlite for select but not for update o delete operations). I have "commented" the LIMIT statement and it works.

I have also a problem with php function header() when i "Unapprove" a comment in the admin section (Manage > Posts). It's the classic problem when you call too much header() to set http header.

2005-07-08 11:05 am

auroraeosrose

well, if you take a look at the sqlite database class you'll see I'm intercepting some queries and fixing them (preg_match is my friend...occasionally)

so if sqlite doesn't like limit on update or delete (why the heck would you need it anyway...ah nevermind, this is mysql nuttiness) - just match UPDATE(*)LIMIT(*) and clip it off at the limit statement

As far as the header stuff goes, I didn't touch it, so don't ask me - take a look at the files I did edit, the only things I messed with were strictly query related, so the header problem is beyond my scope - this site runs the same code and I don't have any problems

2005-07-08 3:04 pm

Nagilum’s Cookie Jar » cakebox on diet

[...] During the last weeks or months I have grown a bit dissatisfied with the performance of my beloved cakebox. Since this is mostly due to the lack of memory which can’t be helped by adding some more I have now started in looking into reducing the amount of memory needed for running my services. The first thing I will try to do is to get rid of MySQL since it uses about 60MB of RAM/Swap. MySQL was mainly used by the spamfilter dspam, but I have already switched that one to sqlite. It is currently still used by Horde/Imp, the other installed webmailer and WordPress. Horde/Imp has native sqlite support, so this will be easy. The other webmailer seems to be a dead project and I havent used it for quite some time so I’ll simply remove it. Wordpress does not yet support sqlite, so this could become interesting. Maybe I’ll fix that personally or simply use an existing patch. Security wise I see only WordPress as a problem as the other DBs do not contain any passwords. When I’m done with all this I can replace phpMyAdmin with the SQLite counterpart and finally deactive MySQL *horray*. Ofcourse I will also look into other areas for possible memory hogs which can be trimmed, especially apache seems be quite wasteful at the moment.. [...]

2005-11-23 2:31 am

Nagilum

Hmm, I don't know how this last post got into this blog, however I've been trying to get wordpress to use sqlite, without success so far. Unfortunately no error messages available.
However I still created a patch file from the zip file and the 1.5.2 Release (I have no 1.5.3 version, since I prefer to remain stable, so that might be the cause of the hickup), so if you prefer a patch to see what has actually been changed or save some bandwidth fetch it from here:
http://www.nagilum.de/unix/wp.patch.bz2 (14k)
to apply the patch extract wordpress for example to /srv/wordpress the goto /srv and do:
bzip2 -dc wp.patch.bz2|patch -p0

2005-11-24 7:22 am

auroraeosrose

You link to this blog and it magically shows up here - that's how the "last post" got here. Try looking up "trackback" in wikipedia :)

I'm not sure if you're using the latest version of this patch - I fixed some bugs as well as changed things for the 1.5.3 patch. Try using the 1.5.2 patches regular files and the 1.5.3 versions db-sqlite.php file. You also might have some problems with sqlite if you're running php4 with the pecl version of sqlite instead of php5 with the built in version - different features are available with the two.

2005-11-24 12:54 pm

Justin Adie

two years later and i have just (independently) finished a first public release of a PDO port for wordpress. there is a sqlite driver provided too. http://rathercurious.net/archives/20

should have googled first!

2007-11-14 11:33 am

Post a Reply