Moor: Declare statements and queries from moor files

[simolus3]

We should provide mechanisms that let users write queries in moor files. This feature should support a modular approach, allowing users to declare their schema and their queries in different files. Further, this should be fully compatible with Dart code:

1. Users can write Dart queries for tables declared in moor files (we already support this)
2. Users can write SQL queries in moor files for tables declared in Dart

I have written done some thoughts I had on how this could be approached. Of course, all of this is open for questions, suggestions and discussion. I would very much appreciate any feedback on this.

Grammar

Copying SQLDelight's grammar for queries and (a subset of) Dart's grammar for import statements seems appropriate, e.g

import 'another.moor';
import 'library.dart';
CREATE TABLE foo (bar INT NOT NULL PRIMARY KEY AUTOINCREMENT);

selectRange: SELECT * FROM foo WHERE bar BETWEEN :low AND :high;

Import statements must appear before CREATE TABLE statements, which in turn must appear before declared statements. We can already parse this grammar on the develop branch.

Resolving

Intuitively, I would assume that everything that is transitively reachable via import statements in moor and include parameters in Dart should be made available when generating a database class or resolving queries. So we should probably establish a two-step mechanism when parsing:

1. Parse .moor files, and extract their structure (we know which tables and queries are available). We can also read the tables declared as classes in a Dart file.
2. Using the results from step 1, analyze the queries to resolve their return type and to find semantic errors.

When a Dart file is imported in a .moor file, we should make all the tables declared in Dart available to the queries in the moor file.

Generated code

Currently, the part file generated for the main database class contains all the dataclasses, companions and table info classes, even if the tables are declared in other files. That's not optimal for build caching, but we've always used that approach and it's been working out fine so far. It's only going to cause problems when users have multiple databases that use the same tables, and I can't think of any use cases for that setup. Changing that logic now would be breaking to existing users, who would have to change their 'import' statements.

I think we can generate code for all queries defined in a .moor file in the database class (@UseMoor or @UseDao) that includes them. Still, all tables have to be added to a @UseMoor class to be used with @UseDao. So, if a user wants to use tables and queries from a .moor file in a dao, they'd have to write two .moor files: One that defines the tables (and gets included from the database) and another one that imports the first file and adds own queries. The second file would be included on the dao.

Further notes

So far, we create tables in the order in which they are found. For complex import cases, we should make sure to always order them so that a REFERENCES constraint will point to a table that has already been created. We can also warn about circular references.

After this is implemented, we can deprecate the queries parameter on UseMoor and UseDao class. __Edit__: There are use cases for them that are very hard to represent in moor files, so it's probably best to keep them. We can write an analyzer plugin for moor files that performs syntax highlighting, folding, outline views, errors, auto-complete, navigation and occurrences on moor files.

Finally, I think we shouldn't generate watch and get methods for queries defined in .moor files. Instead, we generate the Selectable query method and let users call get/watch/getSingle/watchSingle as they like.

[Mike278]

I think the main thing I'd miss by writing queries in .moor files is the flexibility that comes with using Dart strings.

Some examples:

• Using a Dart enum:

const example = "SELECT * FROM foo WHERE status != '${Status.foo_deleted}'"

• Reusing the absent constant in multiple queries:

const int absent = -1;
const String searchFooSQL = '''
  SELECT * FROM foo
  WHERE
    (CASE WHEN $_absent IN :bar THEN 1 ELSE bar IN :bar END) AND
    (CASE WHEN '$_absent' = :baz THEN 1 ELSE baz = :baz END)
  LIMIT :_limit
  OFFSET :_offset
''';
// Using generated code:
searchFoos(search) {
  return db.searchFoo(
      search.bar ?? _absent, 
      search.baz ?? '$_absent', 
      search.limit ?? _absent,
      search.offset ?? _absent,
  );
}

• permittedCategories is reused as a CTE:

const permittedCategories = "SELECT * FROM category WHERE user_id = :user_id AND status != '${Status.category_deleted}'"
const favouriteCategoryIds = '''
  WITH permittedCategories AS ($permittedCategories) 
  SELECT category_id FROM permittedCategories WHERE is_fav = 1
''''
const todosWithCategoryMatch = '''
  WITH permittedCategories AS ($permittedCategories)
  SELECT todo.*
  FROM todo
  LEFT JOIN todo_category USING (todo_id)
  WHERE category_id IN (SELECT category_id FROM permitted_categories WHERE label LIKE :label)
'''

SQLDelight had some discussion around named expressions that sounded interesting.

[simolus3]

Thank you for the feedback and the examples. All of those a valid use cases for compile-time queries that are constructed in Dart, so I think we can keep that feature. My main concern was that tooling support is going to be worse, but that's not really a good reason to phase out the feature entirely. We can instruct users to write queries in moor files for auto-completion and other goodies but still leave it in for the advanced use cases you mentioned.

permittedCategories is reused as a CTE:

Hm, maybe we could provide syntax sugar like

permittedCategories: SELECT * FROM category ...
favouriteCategoryIds: WITH permittedCategories AS permittedCategories ...

That inlines the first query into the second when generating the query code. We're very far away from that though, we can't even parse CTEs at the moment. Even if we did, we still wouldn't support Dart expressions in that query, so this doesn't solve the problem entirely. In the end, this just looks like a lot of work for little benefit.

SQLDelight had some discussion around named expressions that sounded interesting.

Thanks for the link, that looks interesting. Most of that thread resolves around dynamic expressions that are created at runtime. Given that we already have a Dart api to write sql expressions, maybe we could eventually have something like (syntax is just a placeholder):

query1: SELECT * FROM tbl WHERE foo = $expr; -- assuming foo is a VARCHAR
query2: SELECT * FROM tbl WHERE $expr;

And then generate a method like

Selectable<Tbl> query1(Expression<String, StringType> expr) {
  final context = GenerationContext.fromDb(this);
  context.buffer.write('SELECT * FROM tbl WHERE foo = ');
  expr.writeInto(context);
  context.buffer.write(';');
  return ...;
}

Selectable<Tbl> query2(Expression<bool, BoolType> expr) {...}

There will be some interesting things to work out when

1. the Dart expression is used as a result column: SELECT $expr FROM table. I think we can afford to not support this case - it only makes sense if we know both the column name and the static type at build time.
2. The Dart expression introduces additional variables when written into the context.

Of course, some concerns from the referenced issue also apply to moor

I definitely see the usefulness but the project cant allow arbitrary sql to be passed in, it's a leaky API that lets you circumvent SQLDelight entirely. Is there a bug in your where clause? No problem now you can pass it as a String and everything works fine, instead of reporting an issue here.
So the only option is introducing new syntax like you suggested. Which I'm not saying is impossible, but its so far off from what we're currently doing that I'd rather take small steps towards it like optionally switching between predeclared where clauses and then see how those feel.

Moor is positioned better in the last regard because we already support representing some simple expressions in Dart and can then generate sql at runtime without loosing type safety directly. I very much like the idea and it sounds like it's comparatively easy to pull off.

[Mike278]

Tooling is key when dealing with raw SQL strings, so I definitely agree that it's best to push users towards writing queries in .moor files.

Integrating moor's existing query API with the compiled .moor queries is a really cool idea - it would actually replace the need for building query strings in Dart in most cases. I guess you’d have to disallow the ‘$expr’ where it would prevent the return type from being resolved statically?

[simolus3]

If we don't know the static type of expressions, we could still generate a method that simply doesn't require type annotations, so for something like SELECT * FROM tbl WHERE ? = $expr we would just generate a query(dynamic var0, Expression expr) method. We could generate a query<T>(T var0, Expression<T, SqlType<T>> expr) method, but recognizing these patterns would require significant work which I think isn't worth it. The first method would obviously be less type-safe to use, but there will always be Dart queries that compile just fine without generating valid sql queries (for instance using a CustomExpression).

I can only think of two cases that we need to disallow

1. The column names of a select statements won't be known at build time, e.g. when using SELECT a = $expr FROM tbl. When a is a column in tbl, we infer expr to have the same type, but the name of the column depends on what's used for expr at runtime. This would break our mapping code.
2. A column type of a select statement can't be inferred: SELECT $expr AS e.

I've opened #143 for further discussion.

[simolus3]

A first preview of this is now on develop. Moor files can now

• declare their own imports via import "other.moor".
• declare queries via the <name>: <crud_statement> syntax

For UseMoor, UseDao and .moor files, all tables and queries that are transitively reachable via include and import statements will be written.

Potential problems to pay attention to:

• is there a significant increase in build times?
• are there caching issues for incremental builds? We roll our own file graph and cache to track imports in moor files. We notify the build package about stuff we cached ourselves though

Somethings that are left to do

• when we hit a REFERENCES constraint in a table, validate that the referenced table is available
• topological sort of tables via their REFERENCES statements so the CREATE TABLE statements are issued in the correct order. We can use that opportunity to warn about circular references as well.
• how should we handle multiple tables with the same name being available? The sqlparser will probably just emit a AnalysisError.ambigousReference for queries, but even without queries we're going to generate multiple fields with the same name in that case.

Sadly, .moor files can't reliably import other Dart files yet (dart-lang/build#493)

[Mike278]

This looks awesome! Very easy to migrate from @UseMoor( queries: {....

The only issue I ran into is the parsing of IN ?. For example: SELECT * FROM my_table WHERE my_column IN ? throws the error:

Error running MoorGenerator
RangeError: Value not in range: 592

[simolus3]

Thanks, I've fixed that and a bunch of other problems caused by not finding the source of a query in a moor file correctly.

(I also pushed some breaking changes because the next release will be a major bump. I hope it's not to much work to migrate, the only notable change is removing the parameter from the transaction callback and the operateOn parameters)

[Mike278]

It's working great now, thanks. Having the tables and queries all in the same file makes things a lot more organized, and migrating from the breaking changes was also very simple.

[Mike278]

We can write an analyzer plugin for moor files that performs syntax highlighting, folding, outline views, errors, auto-complete, navigation and occurrences on moor files.

(I didn't see an existing issue for the analyzer plugin so feel free to move this as you see fit).
Navigation and syntax highlighting are the first features I'd personally see as most valuable. Being able to ctrl+click on generated dart methods and jump to the .moor file method and vice versa would be so cool - although I get the feeling that's probably the most challenging/complex feature!

[Mike278]

With the latest develop, I'm getting an error parsing the following:

create table tbl (col text);
create table tbl2 (col2 text);
_subselect: select * from tbl2 where col2 in (select col from tbl);

[INFO] moor_generator:moor_generator on ****.dart:line 34, column 47: Could not parse this expression
   ╷
34 │ _subselect: select * from tbl2 where col2 in (select col from tbl);
   │                                               ^^^^^^
   ╵
[INFO] Running build completed, took 110ms

[simolus3]

Yikes, I forgot about that case when making tuples not parse as expressions. I've also added a bunch of test cases for the different components that can appear after IN.

[simolus3]

We now resolve references created by foreign key clauses and sort tables topologically based on that. We also issue an warning for circular references between tables.

[Mike278]

Ooo cool. We do that too but for sorting an offline sync queue - maybe we’ll be able to use moor’s graph instead of rolling our own.

[simolus3]

Hm, it happens at build time and I didn't bother writing a generic implementation that works for all graphs, it just looks up the references directly from the table. Nice solution for synchronizing data with dependencies though, applying theoretical algorithms to solve practical problems elegantly is always so satisfying :)

[Mike278]

Yeah no worries, we actually build "groups" of dependencies for concurrent uploads instead of a single flat list so I wasn't expecting to be able to use a generic implementation anyway. Having an API for looking up references at runtime might be useful, but it'd only be a tiny performance improvement over PRAGMA foreign_key_list so I doubt the added complexity is worth it.

applying theoretical algorithms to solve practical problems elegantly is always so satisfying :)

Definitely :smile: Your sqlparser is a perfect example!

[Mike278]

I ran into a few new issues on develop (let me know if I should've made a new issue instead):

create table tbl (col text);
deleteAll: delete from tbl;

Error running MoorGenerator
NoSuchMethodError: The getter 'span' was called on null.
Receiver: null
Tried calling: span



md5-6474b745903ed598343705663c43d2ae



[SEVERE] moor_generator:moor_generator on lib/src/database/***.dart:
Error running MoorGenerator
Bad state: Reached end of source
[SEVERE] moor_generator:moor_generator on lib/src/database/***.dart:
Error running DaoGenerator
NoSuchMethodError: The getter 'resolvedImports' was called on null.
Receiver: null
Tried calling: resolvedImports

Using -- on any other line works, and using /* ... */ on the last line works.

[simolus3]

Thanks - I've fixed both problems on develop.