[bob1029]

We have started looking at something along the same axis of "improving understanding of your queries". Our product has nearly 10k SQL queries that need to be managed for each logical installation.

By converting a SQL query into an AST, you can start applying business logic to the actual syntax of the query. Put another way, you can query you queries. You can also run reports across all SQL to determine things like "show me everything in the product which references this table & column", or "Which queries reference a specific magic string constant?". More advanced reports can be made too, such as "Which queries join tables A, B & C together?"

We haven't taken it to the next step yet, but hypothetically we can go from AST back into SQL and start doing some super crazy shit like patching hand-written queries programmatically. Once something is in AST form, you are basically working with playdoh that another tool like LINQ (and a bit of recursion) can trivially cut through.

[gavinray]

  > but hypothetically we can go from AST back into SQL and start doing some super crazy shit like patching hand-written queries programmatically.

And you've stumbled into rewrite-rules and the realms of query-planners/optimizers!

If you enjoy this stuff, it's really fun to learn about.

[greggsy]

I’m not a DBA, but is there any IP around what OP and others are attempting to do? Surely anything that makes your life easier would have been patented by Oracle or the likes to eke every dollar out of the market?

[hermitdev]

The animated step by step is new to me, but any DB worth using is going to include tools that explain the execution of any query you give to the DB. It's known as a "Query Plan". And, despite the name, is not limited to queries. Query plans aren't friendly to the uninitiated, but give you far more technical information you need to actually tune the query and ensure things like "am I actually using the indices I have on this table?".

I'm not a DBA, but I have over 20 years experience using RDBMSes of Oracle, DB2, SQL Server, Sybase, etc.

[v-erne]

Just out of curiosity - where do You get those queries from ? By which I mean - Are they static templates, and You get them from source code, or they are dynamic and You gather them from logs (and risk that some rare queries will be left out) ?

[bvm-101]

Believe it or not, did not know there were tools to parse SQL to AST before this project. I was trying to parse sql manually XD.