Investigate grammar functionality again

[rogerbinns]

Fossil builds now produce a parse.sql file It should be possible to understand the grammar from that, and if so be able to do some nice functionality. Of interest in query colouring, parsing, completion etc

parse.sql.txt

[y2kbugger]

Maybe there is already a better way to do this, but I would like to be able to robustly split a script into statements, and access ast components like to know things like: "Statement is a create view statement for a view name XXXX" or "statement is a select statement"

[rogerbinns]

@y2kbugger apsw.ext.query_info gives you that information. Under the hood it prepares the statement with the authorizer active, but does not run the query. In the example linked from that doc, the actions are that information.

What can be extracted is in this handy table.

The only drawback is that the database has to be valid at the time - ie tables etc accessed have to exist.

You may also be interested in SQLGlot which operates directly on SQL and can parse, reformat, tell you what it does, and very nicely can convert SQL from one dialect to another.

[y2kbugger]

Thanks!,

I'm working on migration engine with rich diffs, so its a very specific use case that is especially affected by the constraint database has to be valid at the time.

For anyone finding this later, I've found sqlparse to be reliable for splitting scripts.
https://sqlparse.readthedocs.io/en/latest/api.html#sqlparse.split
I'll have to check out SQLGlot as well.

[rogerbinns]

constraint database has to be valid at the time

That is unfortunately tough to get around. The only way to proceed with SQLite is to then run CREATE etc statements on a dummy database after you know that is what a statement does. IIRC the authorizer also expands views and triggers so you do see exactly what the effect of running the SQL is, versus what the SQL says.

If you are just doing splitting, then you can use SQLite to do that. Keep calling apsw.complete with each semi-colon in the input on the end, and it will return True when you have a complete statement.