CRUD functions by default?

[willhaslett]

Moor rocks, but I'm missing something, or this is a feature request. It seems like one should get vanilla CRUD functions, maybe along with a .watch() Stream for all tables by default. ActiveRecord has a nice idiom where you can call these (among others) on any instance of any model:

.where()                     (read)
.new({optional params map})  (instantiate)
.create({params map})        (instantiate and save)
.update({params map}
.destroy()

It seems like Moor could (simply?) have something similar where the functions are created as part of the build? Is the current intent that one types each of those functions for each table in database.dart? Instead of a params map, one could pass in a FoosCompanion to get the benefits of that class, but ideally, one would just pass in legal parameters.

[simolus3]

We can't really have those methods on the generated model, because from there it's not obvious which database to use for the actual query.

We could add a generateCRUD parameter to @UseMoor and @UseDao that would generate these methods on the database or dao. It could be something like

class MyDatabase extends _$MyDatabase {
  // These methods would be created automatically:
  Selectable<Data1> readTable1([Expression<bool> Function(Table1) predicate]);
  Future<int> newTable1(/*Parameters copied from Table1Companion.insert*/);
  Future<void> updateTable1(Data1 row);
  Future<void> deleteTable1(Data1 row);
}

Is that what you're looking for?

[willhaslett]

Yes, I think this would be a great addition to the API! I would throw in a watchTable1() too for good measure. Then, the hand-written functions that we define in database.dart can focus on needs that we have beyond basic CRUD.

[simolus3]

If you have a "primary" table per dao, you could use something like:

abstract class CrudDao<T extends Table, D extends DataClass,
    DB extends GeneratedDatabase> extends DatabaseAccessor<DB> {
  CrudDao(DB attachedDatabase) : super(attachedDatabase);

  TableInfo<T, D> get primaryTable;

  Selectable<D> selectAll() => select(primaryTable);

  Future<List<D>> listAll() => selectAll().get();
  Stream<List<D>> watchAll() => selectAll().watch();

  Future<int> insertNew(Insertable<D> row) {
    return into(primaryTable).insert(row);
  }

  Future<void> replace(Insertable<D> row) {
    return update(primaryTable).replace(row);
  }
}

Then you could write your daos like this:

@UseDao(tables: [Items])
class ItemsDao extends CrudDao<Items, Item, MyDatabase> {
  class ItemsDao(MyDatabase db): super(db);

  TableInfo<Items, Item> get primaryTable => items;
}

This will give them most of the methods your interested in. I'm considering adding that class to moor (instead of having an option to generate additional methods). It's a bit more cumbersome to use that generated methods and only works if there's a single primary table per dao, but it's much easier to implement and maintain.

[willhaslett]

I'm probably interpreting something wrong. I declared the CrudDao class verbatim as above, then tested it with one of my tables as follows. This is just as above, but I think that where you have class ItemsDao..., class might be a typo.

@UseDao(tables: [AssessmentTypes])
class AssessmentTypesDao
    extends CrudDao<AssessmentTypes, AssessmentType, MtDatabase> {
  AssessmentTypesDao(MtDatabase db) : super(db);

  TableInfo<AssessmentTypes, AssessmentType> get primaryTable =>
      assessmentTypes;
}

The database builds successfully, but I'm not seeing any sign of the generated methods for the DAO:

// **************************************************************************
// DaoGenerator
// **************************************************************************

mixin _$AssessmentTypesDaoMixin on DatabaseAccessor<MtDatabase> {
  $AssessmentTypesTable get assessmentTypes => attachedDatabase.assessmentTypes;
}

Where am I going wrong? If I get this working, it will save a whole lot of boilerplate!

[simolus3]

Right, but AssessmentTypesDao should inherit the right methods from CrudDao? E.g. you could use yourDatabase.assessmentTypesDao.listAll() and get a Future<List<AssessmentType>>. I forgot to add the mixin though:

@UseDao(tables: [AssessmentTypes])
class AssessmentTypesDao
    extends CrudDao<AssessmentTypes, AssessmentType, MtDatabase>
    with _$AssessmentTypesDaoMixin {

[willhaslett]

Right of course, it has those methods by instantiating the abstract class. Thank you.

[willhaslett]

All of my tables use an auto-incrementing id primary key. Can I access that column in the abstract class even though the getter isn't defined for Table? I would need that for the "R" in CRUD, I think.

(btw, I would just test that, but I'm in the middle of overhauling the whole data model for this app. If only Dart had a REPL :))

[simolus3]

Can I access that column in the abstract class even though the getter isn't defined for Table?

You can use primaryTable.columnsByName['id'] as Expression<int>.

[willhaslett]

Sorry, hopefully almost done here. Under the gun so asking: how do I use that, specifically within the abstract class? Tried defining a getter, substituting it for t.id,...

Current abstract class:

abstract class CrudDao<T extends Table, D extends DataClass,
    DB extends GeneratedDatabase> extends DatabaseAccessor<DB> {
  CrudDao(DB attachedDatabase) : super(attachedDatabase);
  TableInfo<T, D> get primaryTable;

  // primaryTable.columnsByName['id'] as Expression<int>;

  Selectable<D> _all() => select(primaryTable);
  Future<List<D>> all() => _all().get();
  Stream<List<D>> watch() => _all().watch();
  Future<D> find(int id) {
    return (select(primaryTable)..where((t) => t.id.equals(id))).getSingle(); // t.id complains
  }


  Future<int> create(Insertable<D> row) {
    return into(primaryTable).insert(row);
  }

  Future<void> replace(Insertable<D> row) {
    return update(primaryTable).replace(row);
  }
}

[simolus3]

Sure! You can use it like this:

  Future<D> find(int id) {
    final idColumn = primaryTable.columnsByName['id'] as Expression<int>;
    return (select(primaryTable)..where((_) => idColumn.equals(id))).getSingle();
  }

[willhaslett]

It works! Thanks so much for considering this feature and getting this test implementation going! If you do decide to fold it into the package, the final win would be to obviate this small boilerplate that is currently needed to get a CrudDao for a given model. My knowledge isn't deep enough to know if that's do-able or not.

@UseDao(tables: [ValueTypes])
class ValueTypesDao extends CrudDao<ValueTypes, ValueType, MtDatabase>
    with _$ValueTypesDaoMixin {
  ValueTypesDao(MtDatabase db) : super(db);
  TableInfo<ValueTypes, ValueType> get primaryTable => valueTypes;
}

@UseDao(tables: [QuestionInterfaces])
class QuestionInterfacesDao
    extends CrudDao<QuestionInterfaces, QuestionInterface, MtDatabase>
    with _$QuestionInterfacesDaoMixin {
  QuestionInterfacesDao(MtDatabase db) : super(db);
  TableInfo<QuestionInterfaces, QuestionInterface> get primaryTable =>
      questionInterfaces;
}

@UseDao(tables: [Measurements])
class MeasurementsDao extends CrudDao<Measurements, Measurement, MtDatabase>
    with _$MeasurementsDaoMixin {
  MeasurementsDao(MtDatabase db) : super(db);
  TableInfo<Measurements, Measurement> get primaryTable => measurements;
}