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.