PHP MySQL SQL parser (INSERT and UPDATE) [closed]

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?

Deformed

Answer 1

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

http://sourceforge.net/projects/txtsql

http://code.google.com/p/php-sql-parser

For Perl there's more variety

Lawhorn

The PEAR package is very green and not even v1.0 though. – Hallowell

Your second link appears to be no longer valid. – Stork

Looks like code.google.com/p/php-sql-parser has been moved to github.com/greenlion/PHP-SQL-Parser (it's the fastest parser I've tried so far but returns a low-level tree rather than class types like select, insert etc) – Kreegar

Answer 2

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.

Moderator

Answer 3

You may give it a chance: SQL Parse and Compile

Ovule

Answer 4

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.

Loopy

I'm not finding the package you mention. Can you provide a more percise link? – Deformed

Answer 5

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.

Ace

It appears you've replaced your blog with random pics. The open bugs in the PEAR SQL parser have me concerned. Sounds like they've built a generic SQL parser without much special attention for MySQL. – Panda

Answer 6

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

Anchorage