SQL Parsing

[farnsworth2008]

My understanding is that currently SQL in LiquiBase within the <SQL/> tags is parsed into statements and then executed without translation.   I do something similar in GarinDriver and see this as an area where we can share technology.

Often vendors have slightly different SQL syntax rules so a good universal parser is hard to implement.   For example MySQL uses java style string encoding while other vendors use SQL99 style (O''Brian vs. O\'Brian).  The MySQL guys also support # style comments in addition to the SQL99 -- and /* */ styles.

I am not sure how robust the current LiquiBase parser is.   My initial guess from looking at the code and reading some issues on your forum/jira is that it will have trouble with many of the more complex scripts especially the ones with comments and semis in strange places.

The garin.parse package currently handles all the complex SQL that I can find from MySQL including all weird comment rules.   I wrote this package after looking around and not finding any similar package in the open source community.

My parser framework uses an approach where each parse mode is a separate object and as such it is hopefully easy to extend and/or use.

If LiquiBase would like, you are free to embed my parsing package in the main liquibase code (it is eclipse public licence code).   For me the benefit is that it would provide more testing of the package and help find weird scripts that I might not otherwise come across.  When we find weird but legal scripts we can add them to the test case and I think it should be fairly easy to adapt my parser to handle some pretty wacky rules if needed.  

The current test case for my parse package is here:
http://code.google.com/p/garindriver/source/browse/trunk/GarinDriver/tst/src/garin/standard/TestParser.java

The test case works on some sample files (also in SVN).  The sample file for MySQL testing is fairly complex but still parses correctly.
http://code.google.com/p/garindriver/source/browse/trunk/GarinDriver/tst/parse/mySQL.sql

Let me know if you decide to use the package and/or if you already have a similar package and/or find a better one.