Parsing SQL Query into a DOM-like tree to enable automatic permutation?

Question

I have a large and complicated sql view that I am attempting to debug. There is a record not showing in the view and I need to determine which clause or join is causing the record to now show up. At the moment I am doing this in a very manual way, removing clauses one at a time and running the query to see if the required row shows up.

I think that it would be great if I could do this programmatically because I end up diving into queries like this about once a fortnight.

---

Does anybody know if there is a way to parse an SQL query into a tree of objects (for example those in sqlalchemy.sql.expression) so I am able to permuate the tree and execute the results?

Ermine

Hmm...I'm curious to know if there's a way to step through the execution plan in any of the popular RDBMs' which might achieve the same end-goal. If there isn't something out there that does what you suggest, you can probably write it. You'd want to transform the joins into embedded SQL and break up the query so that each embedded block can be executed and inspected individually. – Kuopio

Thanks @Mario, I use Toad as my Database UI and I have seen nothing like this in the options, but your idea for breaking up the sql and doing this myself sounds like a good (but complicated) idea! – Ermine

Answer 1

If you don't already have the view defined in SQLAlchemy, I don't think it can help you.

You could try something like sqlparse which might get you some of the way there. You could permute it's output and execute the permutations as raw sql using SQLA.

Rinse

Thanks, I don't have the view defined in SQLAlchemy, and was hoping SQLAlchemy might be able to do this for me, sqlparse looks like it may be the best solution. – Ermine