[Question]: Create index, where can I find the generated code?

[Manuelbaun]

Hey guys, first, amazing job creating moor!

When I create a table and use a Dao for the queries, it is also possible to call include and use a moor file like here:

@UseDao(tables: [Todos], include: {'todo.moor'})

in my moor file I just create an index for now:

CREATE INDEX todo_titles ON todos(title, content);

and that is my table:

@DataClassName('Todo')
class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  TextColumn get content => text()();
  DateTimeColumn get targetDate => dateTime().nullable()();
  IntColumn get category => integer()
      .nullable()
      .customConstraint('NULLABLE REFERENCES categories(id)')();

  // add more columns
  @override
  Set<Column> get primaryKey => {id};
}

1. I was wondering, where does the CREATE statement go, I cannot find it somewhere. It is not in the todo.g.dart nor in the database.g.dart file.

2. How could I create indexes with fluent-syntax? I couldn't find it in the docs.

[simolus3]

I was wondering, where does the CREATE statement go, I cannot find it somewhere.

The index should only get one field in the generated database: https://github.com/simolus3/moor/blob/ac20d9b324e79d5ae012e36bf691c490df473564/moor/test/data/tables/custom_tables.g.dart#L1402-L1403

If it's not there, do you get any error messages during the build?

How could I create indexes with fluent-syntax

Do you mean in Dart? That's not possible at the moment.

[Manuelbaun]

Do you mean in Dart? That's not possible at the moment. alright I got that.

If it's not there, do you get any error messages during the build?

no there is no error message showing and I can't find it in my generated files.

what I noticed though, is, when I use queries, after the query, I cannot create Index. Then it is complaining about missing column.

no error message:

CREATE INDEX todo_titles ON todos(title);

getEntry: SELECT * FROM todos WHERE id = :id;
allEntries: SELECT * FROM todos;
createEntry: INSERT INTO todos (title, content) VALUES (:title, :content);
deleteById: DELETE FROM todos WHERE id = :id;

with error message:

getEntry: SELECT * FROM todos WHERE id = :id;
allEntries: SELECT * FROM todos;
createEntry: INSERT INTO todos (title, content) VALUES (:title, :content);
deleteById: DELETE FROM todos WHERE id = :id;

CREATE INDEX todo_titles ON todos(title);

[INFO] Running build...
[WARNING] moor_generator:moor_generator on lib/infrastucture/database_moor/tables/todo.dart:
There were some errors while running moor_generator on package:song_app/infrastucture/database_moor/tables/todo.dart:
[WARNING] moor_generator:moor_generator on lib/infrastucture/database_moor/tables/todo.dart:
line 9, column 8: Expected a colon (:) followed by a query
  ╷
9 │ CREATE INDEX todo_titles ON todos(title);
  │        ^^^^^
  ╵
[WARNING] moor_generator:moor_generator on lib/infrastucture/database_moor/tables/todo.dart:
There were some errors while running moor_generator on package:song_app/infrastucture/database_moor/tables/todo.dart:
[WARNING] moor_generator:moor_generator on lib/infrastucture/database_moor/tables/todo.dart:
line 9, column 8: Expected a colon (:) followed by a query
  ╷
9 │ CREATE INDEX todo_titles ON todos(title);
  │        ^^^^^
  ╵
[INFO] Running build completed, took 581ms

[INFO] Caching finalized dependency graph...
[INFO] Caching finalized dependency graph completed, took 166ms

[INFO] Succeeded after 756ms with 5 outputs (6 actions)

In both cases, with or without error, It does not create an index somewhere. Does it have to be directly on the UseMoor decorator?

---

Ok I just tested it, when I use it on the UseMoor Decorator, then it will generate the index.

[simolus3]

what I noticed though, is, when I use queries, after the query, I cannot create Index

Yeah, the order of statements in moor files has to be

1. imports
2. table / trigger / index definitions
3. queries

I've changed the error message to point that out as well.

It's surprising to see that you didn't get an error with the first snippet though - you need to import Dart tables for them to be visible in moor files. I tried to reproduce this and got the error

line 3, column 29: Target table todos could not be found.
  ╷
3 │ CREATE INDEX todo_titles ON todos(title);
  │                             ^^^^^
  ╵