Moor: Declare views in .moor files

[simolus3]

This is not planned for 2.0, but it would be nice to eventually support CREATE VIEW and CREATE INDEX statements in moor files.

We should probably generate a dataclass for each CREATE VIEW statement. Just as we do with tables, queries with the same columns should then return that data class instead of introducing another one.

CREATE INDEX statements should be executes as the database gets created. I'm not entirely sure how we would integrate them with the migrations api. Maybe we could have something like

myIndex: CREATE INDEX ...;

and then

onUpgrade: (m, from, to) async {
 // ...
 await m.createIndex(myIndex);
}

[simolus3]

In the next moor version it will be possible to declare indices in moor files. You just write them down like create table statements:

CREATE UNIQUE INDEX user_name_idx ON users (name);

They will be created in onCreate. If you need to create them in an upgrade, you can use Migrator.createIndex(userNameIdx) in onUpgrade.

Support for views requires some more work.

[chrislaurie-bs]

Can I not create a "pseudo view". Declare a table with the same structure as what my view will output. Then use a custom query to return the correct values? It means some unused generated code and an empty table in sqlite.

[simolus3]

You could do that, but it wouldn't really behave like a view. You would still have to embed the full view query into each query that uses it, right? I don't see the advantage of creating a table for this purpose.

[chrislaurie-bs]

It is just there to hook the dao up to. I'm under the impression that a dao need to hook up to a table? This is just a temporary suggestion until the library includes it. In my use case I have big queries with a lot of joins etc. Using views would be a good approach to many of them.

[HosseinYousefi]

Hey @simolus3. I kind of need views, so I want to go ahead and implement it, just wanted to check with you beforehand.

[simolus3]

If you want to contribute to moor then that's much appreciated, thanks! The SQL parser is already capable of parsing CREATE VIEW definitions, the hard parts will probably be code generation and resolving view columns in the generator. Let me know if you have any questions on that.