Open mqus opened 4 years ago
This is not really an update but more thoughts about the feature:
I thought that it may be possible to have somewhat of an effect system where Queries with a Stream
as the return value are simply run again as soon as the input entity changes, regardless if it is a SELECT statement or a DELETE statement. On one hand this would make an excellent system for building triggers. On the other hand SQLite already supports triggers and we can easily introduce loops as we don't know exactly if any change has been actually made or if the query was simply a no-op. personally I find this effect system intriguing but I'm aware that it would probably introduce more problems than it would solve.
Apart from that, I went through the possible sqlite statements to parse and want to restrict this parsing effort to just a few of them. Other statements would not make sense or are too complicated to get right. As mentioned above, statements can have dependencies (updates to a dependency would change the output of the statement) and affected entities (executing the statement would change those entities)
To include:
SELECT
-Statements (including joins, Unions, etcpp): Only has dependencies.DELETE
-Statements: Affects at most one entity. Could have dependencies.INSERT/REPLACE
-Statements: Affects at most one entity. Could have dependencies.UPDATE
-Statements: Affects at most one entity. Could have dependencies.To (explicitly) exclude (for now):
CREATE/DROP TRIGGER
-Statements: Are a special case, as they also introduce new dependencies into other statements, same as the view creation. But as they are usually not specified with an annotation but in the databaseBuilder with a callback, they will be tricky to detect.SELECT id FROM :table
"), is not allowed by the grammar right nowSELECT * FROM table :qualifiers
"), is not allowed by the grammar right nowAnother thought I had right now: Another feature we can gain with a query parser is deriving the type to output for a @Query function. I didn't think deeply about this yet.
META: I should probably just start to code instead of writing novels here :wink:
This sounds amazing! Thanks for your efforts. Did you look into the sqlparser package by any chance to find out if it can solve our problems?
Another thought I had right now: Another feature we can gain with a query parser is deriving the type to output for a @query function. I didn't think deeply about this yet.
This featured would be quite interesting and has actually been requested by users already (#94, #130)
This sounds amazing! Thanks for your efforts. Did you look into the sqlparser package by any chance to find out if it can solve our problems?
No I didn't and this seems far easier to work with than the antlr approach. I'll definitely look into it.
This featured would be quite interesting and has actually been requested by users already (#94, #130)
Yeah I made it sound like I just had that idea but in truth I knew it but had forgotten that this is also something we can do with the query parsing and wanted to add it, just so I wont forget it again.
Here is a list of features that depend on the query parser being implemented:
I updated the issue text to summarize the current status.
Awesome! Thanks for your investment!
Introduction
This issue tracks the effort of adding a parser for SQLite queries, which will enable us to do static analysis on the queries, get dependencies of DatabaseViews, be aware of affected databases when allowing arbitrary queries and much more.
I have started implementing this for a prototype and want to build on the work done in #320 .
I updated the top post to better reflect the current status.
Roadmap/TODO
CREATE VIEW
and Views in sqlparser (https://github.com/simolus3/moor/pull/563)@xyz
and$abc
variables))~ invalid, has to be solved from our side:Old post, not really up2date anymore because I abandoned the antlr approach in favor of the much more mature sqlparser (as mentioned below):