create indices without .moor file

[jackple]

Is there a plan for creating indices like:

@override
Set<Column> get index => {group, user};

[simolus3]

There's nothing preventing us from declaring indices in Dart. However, we'd probably use a designated class for that, something like

class MyIndex extends Index<Users> { // Where Users is a Dart table
  @override
  Set<Column> get columns => {group, user};
}

That would allow users to override the name of the index or define a custom where clause.

[ycherniavskyi]

@simolus3 could you please explain creating index in code in more detail.

Because I could not find such Index class as in your example. I could find only this.

[simolus3]

@ycherniavskyi That was just an api suggestion, it's not implemented yet.

If you want to define an index without moor files, you could use something like this today:

class YourDatabase extends _$YourDatabase {
  // ...

  @override
  Iterable<SchemaEntity> get allSchemaEntities {
    return [
      ...super.allSchemaEntities,
      Index('my_index', 'CREATE INDEX my_index ON  ...'),
    ];
  }
}

[ycherniavskyi]

Aha, I see.

@simolus3 and thank you for your suggestion, I like it more than which I come with 😊:

class YourDatabase extends _$YourDatabase {
  // ...

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (m) async {
      await m.createAll();
      await m.createIndex(Index('my_index', 'CREATE INDEX my_index ON ...'));
    },
    // ...
  );
}

[Ashok-Varma]

@ycherniavskyi @simolus3

how about this pattern, any limitations for this ?

class User extends Table {
  @override
  Set<Index> get indices => { Index([last_name, address]), Index([first_name, last_name], unique = true)};
}

picking up queues from room

@DataClass(
  tableName = 'user',
  primaryKeys = arrayOf("firstName", "lastName")
  indices = arrayOf(
    Index(value = ["last_name", "address"]),
    Index(value = ["first_name", "last_name"], unique = true)
  )
)
class User extends Table {
}

As we are using Set<Column> get primaryKey => pattern for primary keys, we can do similar for Indices. Remaining might be error prone as we need to depend on user's knowledge on indices tables and strings 'last_name', 'first_name' (can be validated compile time)

[FMorschel]

It's been more than two years since the last comment here.

I would like to show my support for this feature as well!

[simolus3]

Thanks for the reminder. I've implemented support for this on develop, the next drift version will support indices defined in Dart.

The syntax I had in mind for that is an annotation on the table to specify the columns in the index:

https://github.com/simolus3/drift/blob/8300782662333ba78f5eb83605508ac2c7893515/drift/example/main.dart#L15-L24

Of course, the annotation can be repeated to add multiple indices on the same table, and an index can also be made unique. Let me know if you have a use case that can't be expressed with this API, thanks!