Porting wp-db to use PDO for PHP5 and MDB2 for PHP4

I myself was thinking of doing something similar
A wrapper around wp-db.php would be perfect or something of that nature.
On my other sites I wrap the queries like so:

DB-Wrapper.php
DB_Query($sql)
{
if(!validsql)
{
return ErrorHandler($errormessage);
}
else
{
return dBQuery($sql)
}
}
dbQuery() is located in DB-MySQL.php
(so that would probably be wp-db.php then)

Don’t know how MDB2 is functioning though, could look into it.
PDO is for PHP5, maybe something PHP4 compatible is possible too?

MDB2 is an abstraction layer maintained as part of the PEAR project. It is compatible with php4. PDO is, as you say, PHP5 only.

the reason for supporting both would be to provide compatibility for PHP4 and PHP5 across a broad church of databases. when MDB2 finally supports PDO then the native support for PDO in the proposed wrapper could be dropped. The advantage of PDO over MDB2 is that (i) it’s compiled in, so fast; (ii) it supports sqlite3 databases whereas MDB2 currently only supports sqlite2.

the advantage/requirement for sqlite support is to make the blog database easily backedup(able) and portable. sqlite databases are a single file in the filesystem. It also potentially extends the availability of wordpress to hosts or hosting plans that don’t include mysql or for which the mysql size limit is crazy-low (i.e. 1and1). THat’s a secondary benefit though, to my mind.

the process is a bit more complex than you were alluding. the actual abstraction of wp-db is easy. what i would be looking to do is to disassemble the queries on the fly and undo the escaping and quoting that is present in the existing query. then reassemble the query with parameter placeholders

Select * from table where id=?

and use prepare … execute methods to perform the queries and natively protect from sql injection attacks.

The complexity is in the sql parsing engine. The closest that I have found ‘out of the box’ is maintained in PEAR (SQL_Parser) but it has bugs (doesn’t recognise HAVING clauses or some functions) that are non-trivial to fix. So i’m still searching for a good parsing engine before jumping off the deep end and writing my own lexer, parser and compiler.

If anyone knows of a good engine (written in php) that is available under GNU GPL then please provide a link.

thanks
Justin Adie

The one thing still holding me back from using WP (and which is therefore driving me toward MT4) is the issue of sqlite support. For my part, I would prefer sqlite3 support + PHP4 support due to the environment I would need to initially deploy WP in. There are many factors that make sqlite3 vastly preferable for me over MySQL.

jpadie: If you’re looking for votes in favor of getting WP to run on sqlite3, please count mine as a vigorous yes. Alas, SQL and PHP aren’t areas where I have any domain expertise, so all I can offer is moral support and a willingness to test.

It would, of course, be particularly nice if some of the official WP folks could also weigh in on the likelihood of including sqlite3 support in future versions of mainline WP. It has been very disheartening to see open feature requests related to WP+sqlite closed over the past two years.

Thanks.

Hi there

i have scaled back my plans to support a variety of databases to just sqlite and mysql. i may extend to other PDO supported databases later. i suspect that true ansi-sql databases will work without further change.

I have finished rewriting the db abstraction layer to use sqlite via the PDO interface and to provide emulation for mysql functions that are unsupported in ansi sql (at least those that are used in wordpress). I have not tested it in a live environment, though.

there is no need to maintain a set of diffs or a fork of wordpress either – my implementation *should* work independent of upgrades unless there is a need to upgrade the db schema itself.

I will publish the fix most likely over the weekend – hopefully on a working sqlite blog! i’ll post the url back here.

Another thing to note is that i have had to rewrite the schema to support sqlite as, for example, enum data types are not supported in sqlite. hopefully i have done this transparently to the user in the install process.

PDO, of course, is not compatible with php4 and mdb2 does not support sqlite3. is php4 a genuine requirement? i may be able to work something in if it is. i have a feeling i can do so with minimum changes.

Justin

Justin —

My initial deployment will be on a stock OSX box, whose binaries I’m strongly disinclined to fiddle with at the moment. sqlite 3.1.3 (far from the latest, I know) and php 4.4.7 are what’s installed, hence my desire for the sqlite3 + php 4 playing nicely together.

If you do have a way to allow this combination to work, it would certainly be quite useful here.

Thanks.

i’ll see how easy it is. I *think* it’s only about a four line change.

at the moment i’ve just hit a damned annoying stumbling block in that the mysql and sqlite implementations of the date_format string constitute different things. ie %s doesn’t mean the same in mysql to sqlite…. back to the drawing board for that function!

like you, i was reticent to recompile the php binaries for OS X but i did so in the end because it’s pointless to develop for a version of PHP that has had its end of life announced. It took a couple of hours to get sqlite, php, apache and mysql all talking nicely to each other. i didn’t have the developer tools installed, nor X11 – that was the majority of the time spent.

for short projects I develop using a userland installation of apache/php/mysql etc. called MAMP. very useful but beware of upgrades – they overwrite the mysql database and the pear directories.

If you were to publish the changes I would be interested in working on getting MSSQL going.

d4ljohn
this should be pretty simple as many of the bespoke functions with mysql have been abstracted to php as they are not supported in sql ANSI.

but your query leads be to a design decision which will be based on your response to:
? can you return a date from mssql as a unix datestamp?

your interest will also lead me to abstract the sqlite rewriting engine to a separate class. It will be neater that way anyway.

on the sqlite/PDO port: : i have hit a couple of stumbling blocks:

• abstracting the IF functionality within mysql
• parsing serialised objects for prepared statements
• a really weird memory eating problem in post-new.php when WP assembles the category list.

both of these can be overcome, but with other time pressures i’m likely to be a few more days before I publish.

? can you return a date from mssql as a unix datestamp?

Yes. That can definitely be done. How to do it would depend on the circumstances. Here’s a nice link:

https://mysql.databases.aspfaq.com/how-do-i-convert-a-sql-server-datetime-value-to-a-unix-timestamp.html

after some considerable delay … i now have a sqlite blog up and working. i will publish the code on the blog in a few days.

Justin

first public release is now available for download.