Moor: Generate moor classes from CREATE TABLE statements

[Mike278]

How difficult would it be support CREATE TABLE statements in the sqlparser? I think it would be really cool to be able to generate the Dart table .part files from SQL. It'd give people more options when migrating to moor, as there are lots of tools to generate DDL from an existing database.

[simolus3]

Oh I like that idea! We could then also have an alternative to #77 by copying SQLDelight's approach and have users write .moor files like this:

// Let's say this file is called test.moor
import Users from 'db.dart'; // let users import Dart-defined tables, haven't settled on the api yet

CREATE TABLE subscriptions (
  id INT NOT NULL AUTOINCREMENT,
  user INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  price INT NOT NULL
);

payingUsers:
SELECT SUM(s.price), AS amount u.* FROM subscriptions s INNER JOIN users u ON u.id = s.user GROUP BY u.id
````
and interop with Dart like this:
```dart
// Let's say this is called db.dart
class Users extends Table {
 // Some columns (id and name, probably)
}

@UseMoor(tables: [Users], includes: 'test.moor')
class Database {
  // We should be able to select(subscriptions) here. A `payingUsers` future / stream should have been generated as well.
}

It sounds like quite a bit of work, but I think the user experience will be worth it.

Having an own file for queries would also make writing an analyzer plugin to provide syntax highlights / hints easier, but that would still be a lot of work...

[simolus3]

A first version of this is now on develop - we have an Set<String> include parameter on both UseMoor and UseDao. That string basically works like Dart imports - it can refer to a relative file or to any file in the same or a different package with the package: prefix.

For now, the .moor files can only contain CREATE TABLE statements and look like this: https://github.com/simolus3/moor/blob/634b1adb72c67623516ca1a2dbad4efb0370b12a/moor/test/data/tables/test.moor#L1-L10
We then write appropriate table, data and companion classes for each table found in a file. These generated tables can be used like any other table in moor. However, the generated table can't be used in UseDao.tables - it needs to be included again with UseDao.include.

I'll write some integration tests to ensure this works as intended. I'll also try to see if we can have an analyzer plugin to provide basic syntax highlighting for these files.

[Mike278]

This is so awesome! Thank you so much for your work.

I had a play around with it and these were the only issues I came across:

• Generator error if the last column has only a type defined:

CREATE TABLE my_table (
    a INT,
    b TEXT
);
--------------
Error running MoorGenerator
line 4, column 2: Error: Expected closing parenthesis}

4 │ );
  │  ^

• Generator error when using the DEFAULT constraint:

CREATE TABLE my_table (
    a TEXT DEFAULT 'something',
    b INT UNIQUE
);
--------------
Error running MoorGenerator
NoSuchMethodError: The getter 'span' was called on null.

• Table-level customConstraints aren't generated (UNIQUE (a) does not appear in the generated code)

CREATE TABLE my_table (
    a TEXT,
    b TEXT UNIQUE,
    UNIQUE (a)
);

• Generator error when defining both precision and scale. This one is minor as it only really affects users migrating from dialects other than SQLite.

CREATE TABLE my_table (
    a FLOAT(10, 2),
    b TEXT UNIQUE
);
--------------
Error running MoorGenerator
line 2, column 17: Error: Expected a column name}

2 │     a FLOAT(10, 2),
  │                 ^

[simolus3]

Thanks for trying it out and reporting the problems, they should all be fixed now.

[Mike278]

Yep all fixed - I was able to generate all 25 of my tables with no issues :+1:

It looks like the CREATE TABLE statement executed by moor has an extra PRIMARY KEY definition. For example a table defined like:

create table config (
    config_key TEXT not null primary key,
    config_value TEXT
);

will throw the following exception:
DatabaseException(table "config" has more than one primary key (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE IF NOT EXISTS config (config_key VARCHAR not null primary key, config_value VARCHAR , PRIMARY KEY (config_key));)

[simolus3]

Closing this issue as it's done on the develop branch and it will be part of the next moor version. Some advanced features planned for .moor files, like import statements, type converters and queries will probably not make it into the next version. I'll create new issues to track those features when I have a better understanding on how they could be implemented and what a good api could look like.