Moor: DROP TABLES

[aidooyaw1992]

hi please i would like to reset all tables in my appdatabase, without using truncate. i would like to drop the table and create it again. hope understand what im saying

[simolus3]

You could add this method to your database:

Future createAllTablesAgain() async {
  final migrator = Migrator(this, (sql, [args]) => customStatement(sql));
  for (var table in allTables) {
    await customStatement('DROP TABLE ${table.actualTableName};');
    await migrator.createTable(table);
  }
}

I haven't tested it, but it should work. You need to make sure that no other method is accessing the database at the same time though, otherwise you might get a "table does not exist" error.

I think moor can make creating a migrator easier, I'll try to implement that later today.

[aidooyaw1992]

im about to check

[aidooyaw1992]

thanks....it works perfectly but can you explain your code to me?

[simolus3]

Sure! Let's go through it line-by-line:

final migrator = Migrator(this, (sql, [args]) => customStatement(sql));

The Migrator is responsible to send CREATE TABLE statements to the database. When the database is first opened (or upgraded), moor will create an Migrator and pass it to the onCreate or onUpgrade functions declared on the MigrationStrategy. It's mostly used for schema migrations, but we can also use it to generate CREATE TABLE statements outside of a migration. It takes two arguments: The first one is the database, which the migrator uses to read the sql dialect (we currently only support sqlite). The other parameter is a function the migrator calls to execute sql. Here, we just use customStatement to execute sql and ignore arguments. Ignoring arguments is not a problem on moor 1.7, but it can cause problems with the (not yet released) moor 2.0. I'll implement an api that won't cause problems with moor 2.0 later.

for (var table in allTables)

The allTables field is generated on the _$Database superclass, it contains a list of all tables. This is used in functions like Migrator.createAllTables (the default behavior when the database is first opened). We can also use it to perform a custom action on all tables (like dropping and re-creating the table).

await customStatement('DROP TABLE ${table.actualTableName};');

The table.actualTableName contains the name of the table that will be used in sql, so we can use it to drop the table

await migrator.createTable(table);

Finally, we use the migrator to re-create the table we just dropped. You might now this api from schema upgrades, where it can be used to create a new table. It's a great fit here, because it will write the CREATE TABLE statement and send it to its executing function (customStatement in this case).

[simolus3]

Btw, in moor 2.0 you will be able to replace Migrator(this, (sql, [args]) => customStatement(sql)); with just createMigrator. I hope to release moor 2.0 on Friday.

Please let me know if you have any questions.