PHP MySQL SQL parser (INSERT and UPDATE)

Question

I am looking to parse INSERT and UPDATE MySQL SQL queries in PHP to determine what changes where made from what original data. Now this would be pretty easy to create, but I want to see if there are any existing libraries in PHP to do this.

Basically what I have is a table with all of the above queries that have been run on a database. I have already separated out the table name and type of query. I am looking to create a full change log for user viewing based on this data, so I need to get the values of the original INSERT and then changes that are made in each UPDATE. In the end I need field name and new value and with the record id(s). I'll do the rest of the checking/beautifying, including the column name to human readable and if a field value hasn't actually changed.

At the moment, I probably don't need to do multiple table UPDATE's, but it would be useful.

What libraries are there to do this?

Answer 1

http://pear.php.net/package/SQL_Parser

http://www.txtsql.com/index.php?module=dev (lexer and tokenizer)

For Perl there's more variety

Answer 2

Facebook released a open-source PHP version of their FQL parser. From what I was it was quite neat code. You could possibly hack that to work with regular SQL.

Answer 3

I found this to be a useful post and knocked together a small blog entry on how I found some of these solutions (along with others):

http://blog.tobyskinner.co.uk/?p=177

In a nutshell, I found that the PEAR:SQL_Parser package has provided the cleanest short-term solution whilst the PEAR:PHP_Parser_Generator package (the generator that FSQL uses) looks like a really robust longer term solution.

I found txtSQL wasn't robust enough and didn't parse SQL well enough to warrant using, at least in my opinion.

There are some other interesting options which I didn't have time to persue, detailed in that post.

Cheers

Toby

Answer 4

A little bit off the question, but maybe a suggestion worth thinking about:

Since MySQL 5.0, the support for triggers is quite good. If you want to keep a record of what changes have been made to a database, instead of storing the sql statements, you could also define insert/update triggers and define another table in which these values can be stored. You could, for example create a simple table having the fields

timestamp, user, field, old_value, new_value

and insert the respective values whenever a DML on one of your watched tables occured. To simplify this even more, you could add the field

table

to the "tracking table" to store all changes to all watched tables in one place.

See the MySQL manual for more infos about this topic.

Answer 5

you can try : dqml2tree sql(dql and dml) query parser written in php

Answer 6

You may give it a chance: www.phpclasses.org/browse/package/5007.html