Recently on a project we came across the need to generate a bunch of different reports from the database. Due to different requirements we weren’t able to use the ORM (Doctrine2 on the specific project), so we wrote the queries by hand. As we continued to build the different reports we noticed sometimes we’d typo a join, for example join something on `id` versus `user_id`. These small typos would cause the reports to still run fine, however have the incorrect data, often it was difficult to pinpoint the exact issue in the given report, as only certain conditions could reproduce the results. After a while Ashish said it’d be great if we had some sort of sanity checker to make sure the queries we were writing were going across the proper joins. To me, this was:
At first I thought about just using Regular Expressions to parse out the join parts of the SQL queries. However, I found http://code.google.com/p/php-sql-parser/ which appears to do the job. I downloaded it and wrote a class which uses it and some expressions to discover FK’s in the database. I ended up with something which, albeit not the most elegant, gets the job done. Here is an example output of it:
Basically it will run through whatever query you give it, and make sure that the columns you are joining on are defined in the DB. If you are trying to join on a column that is defined as a constraint, it will output the part of the join that failed the check as well as what FK’s currently do exist. Another issue this may help with, is if your database is missing a constraint (FK) that should be defined it will point it out.
I wrote this really quickly, so let me know (or make a pull request) if you find any bugs. I’ve put the code up on Github. Let me know if it helps out!