Moor: reset database without using some truncate

[MahdiPishguy]

is any simple way to reset all of data in database (not selecting tables)? or this feature:

Future truncateUser(UserTableCompanion user) => truncate(user);

[simolus3]

You mean deleting all data in a table? This

await delete(users.go();

should do it. We don't expose the TRUNCATE TABLE statement in moor (but it wouldn't be much work to add so let me know if you need it).

To delete all data in all tables, you could add this method to your database

Future resetDb() async {
  for (var table in allTables) {
    await delete(table).go();
  }
}

[MahdiPishguy]

@simolus3 It's not clear to me, could you help me how can i use that in this database structure?

class UserTable extends Table{
  IntColumn get id =>integer().autoIncrement()();

  TextColumn get name =>text().nullable()();
  TextColumn get mobileNumber =>text()();
  TextColumn get email =>text().nullable()();
}

@UseMoor(
    tables:[
      UserTable
    ],
    daos: [
      UserTableDao
    ],
    queries: {
    })
class AppDb extends _$AppDb{
  AppDb() : super(FlutterQueryExecutor.inDatabaseFolder(path: 'db.sqlite',logStatements: true));
  @override
  int get schemaVersion => 1;
}

@UseDao(tables:[UserTable])
class UserTableDao extends DatabaseAccessor<AppDb> with _$UserTableDaoMixin{
  final AppDb db;
  UserTableDao(this.db):super(db);

  Future<UserTableData> getSingleUser() {
    return (
        select(userTable)
          ..orderBy([(t) => OrderingTerm(expression: t.id, mode: OrderingMode.desc)])
          ..limit(1)
        ).getSingle();
  }
  Future<int> insertUser(UserTableCompanion user) => into(userTable).insert(user);
  Future updateUser(UserTableCompanion  user) => update(userTable).replace(user);
  Future deleteUser(UserTableCompanion user) => delete(userTable).delete(user);
}

[simolus3]

If you want to reset all data, you could change your AppDb class as follows:

@UseMoor(...)
class AppDb extends _$AppDb {
  // constructor and schemaVersion getter still need to be here

  Future<void> deleteAllData() {
    return transaction((_) async {
      for (var table in allTables) {
        await delete(table).go();
      }
    });
  }
}

Then, calling deleteAllData will delete all rows in every table. Is that what you need?

[MahdiPishguy]

@simolus3 i get this error:

Unhandled Exception: DatabaseException(no such table: category_table (Sqlite code 1): , while compiling: DELETE FROM category_table;, (OS error - 2:No such file or directory)) sql 'DELETE FROM category_table;' args []}

my database structure:

import 'package:moor_flutter/moor_flutter.dart';

part 'moor_database.g.dart';

//@formatter:off

class UserTable extends Table{
  IntColumn get id =>integer().autoIncrement()();

  TextColumn get name =>text().nullable()();
  TextColumn get mobileNumber =>text()();
  TextColumn get email =>text().nullable()();
  TextColumn get experience =>text().nullable()();
  TextColumn get insurance =>text().nullable()();
  TextColumn get job =>text().nullable()();
  TextColumn get avatar =>text().nullable()();
  TextColumn get apiToken =>text()();

  IntColumn get userActive =>integer()();
  IntColumn get teammate =>integer().nullable()();
  IntColumn get age =>integer().nullable()();
  IntColumn get gender =>integer().nullable()();
  IntColumn get height =>integer().nullable()();
  IntColumn get weight =>integer().nullable()();
  IntColumn get bmi =>integer().nullable()();

  TextColumn get createdAt =>text()();
  TextColumn get updatedAt =>text()();
}

class TicketsTable extends Table{
  IntColumn  get id=>integer().autoIncrement()();
  TextColumn get subject=>text()();
  TextColumn get description=>text()();

  IntColumn  get ticketNumber =>integer()();
  IntColumn  get section  =>integer()();
  IntColumn  get solved  =>integer()();

  TextColumn get createdAt=>text()();
  TextColumn get updatedAt=>text()();
}

class TicketRepliesTable extends Table{
  IntColumn  get id=>integer().autoIncrement()();
  IntColumn  get ticketId=>integer()();

  TextColumn get reply=>text()();
  TextColumn get createdAt=>text()();
  TextColumn get updatedAt=>text()();
}

class PostsTable extends Table{
  IntColumn get id=>integer().autoIncrement()();
  IntColumn  get postId =>integer()();
  TextColumn get title =>text()();
  TextColumn get description =>text()();
  TextColumn get categoryName =>text()();
  TextColumn get featuredImages =>text()();
  TextColumn get createdAt =>text()();
  TextColumn get updatedAt =>text()();
}

class CategoryTable extends Table{
  IntColumn get id=>integer().autoIncrement()();
  TextColumn get title=>text()();
  IntColumn get parent=>integer().autoIncrement()();
  TextColumn get image=>text()();
}

class MedicineTable extends Table{
    IntColumn get id=>integer().autoIncrement()();
    TextColumn get name=>text()();
    TextColumn get timeSection=>text()();
    DateTimeColumn get unixTime=>dateTime()();
}

@UseMoor(
    tables:[
      UserTable, TicketsTable, TicketRepliesTable, PostsTable, CategoryTable,MedicineTable
    ],
    daos: [
      UserTableDao,
      PostsTableDao
    ],
    queries: {
    })
class AppDb extends _$AppDb{
  AppDb() : super(FlutterQueryExecutor.inDatabaseFolder(path: 'db.sqlite',logStatements: true));

  Future<void> deleteAllData() {
    return transaction((_) async {
      for (var table in allTables) {
        await delete(table).go();
      }
    });
  }

  @override
  int get schemaVersion => 1;
}

@UseDao(tables:[UserTable])
class UserTableDao extends DatabaseAccessor<AppDb> with _$UserTableDaoMixin{
  final AppDb db;
  UserTableDao(this.db):super(db);

  Future<UserTableData> getSingleUser() {
    return (
        select(userTable)
          ..orderBy([(t) => OrderingTerm(expression: t.id, mode: OrderingMode.desc)])
          ..limit(1)
        ).getSingle();
  }
  Future<int> insertUser(UserTableCompanion user) => into(userTable).insert(user);
  Future updateUser(UserTableCompanion  user) => update(userTable).replace(user);
  Future deleteUser(UserTableCompanion user) => delete(userTable).delete(user);
}

@UseDao(tables:[PostsTable])
class PostsTableDao extends DatabaseAccessor<AppDb> with _$PostsTableDaoMixin{
  final AppDb db;
  PostsTableDao(this.db):super(db);

  Future<List<PostsTableData>> get getAllPosts => select(postsTable).get();
  Future<int> insertUser(PostsTableCompanion post) => into(postsTable).insert(post);
  Future updateUser(PostsTableCompanion  post) => update(postsTable).replace(post);
  Future deleteUser(PostsTableCompanion post) => delete(postsTable).delete(post);
}


//@formatter:off

[simolus3]

Did you already open the database at some point before writing the CategoryTable class? Moor creates all tables when the database is created, but if you already had the database open before you need to write that table manually.

You can do that by writing a schema migration:

class AppDb extends _$AppDb {
  // inside the AppDb class
  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onUpgrade: (m, from, to) async {
        // create missing tables that were missing in version 1
        if (from <= 1) {
          await m.createTable(categoryTable);
          // you also need this line for every other table not created in the first version
        }
      },
    );
  }
  // rest of the class
}

If you're the only one with your app installed, you could also try deleting the app's data on the phone once. If you then re-run the app, moor should create all tables, so you don't need the schema migration.

[MahdiPishguy]

@simolus3 problem was on my code and database structure, thanks,
solved :)

[MahdiPishguy]

@simolus3

how about single table?

Future<void> deleteUserData() {
    return transaction((_) async {
      delete(allTables[0]).go();
    });
  }

[simolus3]

You can just use the table name directly in that case, you don't even need a transaction:

Future<void> deleteUserData() {
  return delete(userTable).go();
}

[MahdiPishguy]

@simolus3 my English is not very good. but i like to add this features into documentation

[MahdiPishguy]

how can i resolve this error:

error: The argument type 'Future<Null> Function(dynamic)' can't be assigned to the parameter type 'Future<dynamic> Function()'. (argument_type_not_assignable at [cheetah] lib\data\database\database.dart:90)

code:

  Future<void> deleteAllData() {
    return transaction((_) async {
      for (var table in allTables) {
        await delete(table).go();
      }
    });
  }

[simolus3]

It's a change introduced in the upgrade to moor 2.0. Just remove the _, so line two from your snippet would be return transaction(() async {. See the "Breaking changes" section from the changelog for details.