PDO For WordPress – Status
I have been working on an update to PDO for WordPress. Before anyone asks, there is no ETA …
I am of the view that the queries in WP are becoming ever more complex and are relying quite a lot on implicit type casting within mysql (for example, user entered dates should always be cast to a date type before use in a comparison, but they are not). In particular this is true with date functions but the similar mysql specific problems exist elsewhere.
Yes – we could continue to hack and patch with regex and UDFs, but I question whether this is the right approach longterm.
The goal of PDO for WordPress was to open up WP to all the flavours supported by PDO, not just sqlite (although personally I never had any aim to write a driver beyond sqlite).
To this end, I believe that the right approach is now to parse each query into its constituent parts, and then to reassemble the query in the target language.
To those in the know, creating a parser that can then be used for recompiling is really not straight forward, nor a short piece of work (I have been working on the structure and starting on the select queries for some weeks now). I am also concerned that this might have an impact on performance, although in the back of my mind I am hoping that the impact might be positive.
There are mysql parsers out there. None have so far passed my tests of a few relatively complex queries being thrown at them. The best I have found is ParseCompile by Tom Schaeffer, but even this piece of comprehensive wizardry fails with my test query.
I started off trying to ‘fix’ the versions already out there, but as always trying to understand and follow someone else’s code seems more difficult than rewriting it. So I have tried to take lessons from Mr Schaeffer and others and am building my own.
This should make it rather easier to write compiler grammars for non mysql query languages.
For those interested my test query (which I believe to be valid mysql) is:
SELECT CONCAT_WS(', ', u.lname, u.fname) AS UserName, u.email AS Email, u.mobile AS Mobile, cc.countryName AS Country, DATE_FORMAT ( FROM_UNIXTIME(u.reg_date),'%Y-%m-%d') AS JoinDate, DATE_FORMAT ( u.last_login_date, '%Y-%m-%d') AS LastLogin, u.package_id AS 'Product ID', IFNULL(ro.FPFiled,0) AS 'FP Filed', IFNULL(ro.FPDraft,0) AS 'FP Draft', IFNULL(rv.c,0) AS Validations, ifnull(r.name, 'RR') AS Reseller FROM itc_user u LEFT OUTER JOIN jaCountries cc ON u.country = cc.countryCode LEFT OUTER JOIN resellers r ON u.reseller_id = r.reseller_id LEFT OUTER JOIN ( SELECT owner_user_id, SUM( CASE WHEN ifplid != '' AND ifplid IS NOT NULL THEN 1 ELSE 0 END) AS `FPFiled`, SUM( CASE WHEN ifplid = '' OR ifplid IS NULL THEN 1 ELSE 0 END) AS `FPDraft` FROM itc_route GROUP BY owner_user_id ) AS ro ON ro.owner_user_id = u.user_id LEFT OUTER JOIN ( SELECT user_id, COUNT(*) AS c FROM route_validations GROUP BY user_id ) AS rv ON rv.user_id = u.user_id WHERE u.package_id = 99 AND ( IFNULL(ro.FPFiled,0) >= 1 OR IFNULL(ro.FPDraft,0) >= 3 ) ORDER BY u.lname ASC, u.fname ASC, u.reg_date ASC |
If anyone has leads on a full function good quality php parser for mysql, please let me know in the comments. Similarly if anyone would like to help with the mysql parser then I’d be grateful.
8 Comments
Leave a comment
Your comment
An idea may be make à sal parser with lex and yacc source from mysql source and lime ?
i see that there is a pear package sql-parser.
I think you are on the right way
hi justin …
in some case the rewrite of one query need more thane on query it’s case of “insert on duplicate key update”
lokking at the code i think it would be possible to catch the wpdb::prepare method and use a ditionary based translator.
Recent article with positive responses in the comments : epilogica.info/articles/how-to/wordpress/installing-sqlite-PDO.htm
Is the PDO code in Git or some other public source control system so others can help out?
Hey Justin,
I’m pretty interested in this project. If you’d like assistance with portions of it, i’d be happy to work with you.
If you’re already using SVN via wordpress.org, then you’re already technologically set up to receive some help.
If not, I’m sure it would be possible to drum up more interest by hosting the code on something like github or one of the similar services. I’ve definitely got a local audience who’s most interested in using a feature like this to ease local development of wordpress plugins and themes, as running a whole mysql server locally is a bit of a pain.
Feel free to contact me if you’re interested!
Hi,
your PDO (SQLite) For WordPress seems to have the mentioned permission issue and I don’t find any permission issue for apache itself. Would you be able to advise, please ?
Is ‘PDO For WordPress’ dead?
@ Ingo »
no. at least not deliberately dead. i keep promising myself to pick it up again and test for incompatibility with mysql, but never seem to have the time. a number of people have asked to fork it over the last three years (despite permission not being required) and I have always said yes. You may find that the plugin exists in an alternate repo that is more up to date.
the difficulty in maintaining compatibility is multi-fold:
1. there is no easy way to perform schema changes in sqlite. the usual method is to create a temp table, roll into that, delete the old table, create the new table, roll back to the new table. given the way that dbdelta works in wordpress, this would have to be done per-column which is a very processor expensive operation and data integrity might be seriously compromised.
2. wordpress and plugin designers use weak type comparisons. for example, date comparisons are often not constructed with leading zeros and the like. mysql handles this ok because there is a built in date type, but sqlite will store dates as strings and then we are relying on normal comparisons. so for that to work we must have the dates in the same format, both for inserts and comparisons. currently the code tests for this in a halfhearted manner (using weak regex) and these checks are expensive enough. to make the better would add quite a lot of overhead into the query. An alternative might be to store the original mysql schema in a sqlite table and then do a check of the schema type to determine whether the column should be a date type… but again that is additional queries and additional overhead.
3. mysql is increasingly using complex indices and, importantly, index hinting. whilst complex indices are (of course) supported in sqlite, index hinting is not. sqlite does not support index hinting. it does support index requirements, but that is not the same. i have not fully thought through the impact of (i) dropping all index hinting; or (ii) migrating all queries from index hinting to index requirement.
the last (soluble) problem is that for some reason the create table migration patterns are not very robust and thus some tables just fail to be created (for plugins). this can be made better relatively easily but doing so feels like a cop out on the other issues.
as i posted a year ago, the right approach now is to build a proper, high quality parser and reassembly. the latter is straightforward once the former is done. better minds than mine have attempted a mysql parser/lexer before and some have been relatively successful. but to work for WP, the parser/lexer must be wholly complete, ie a reverse engineer of the C code. that is a big job…