search

simolus3 / drift

Drift is an easy to use, reactive, typesafe persistence library for Dart & Flutter.
https://drift.simonbinder.eu/
MIT License
2.51k stars 357 forks source link

Issues with migration and insertAllOnConflictUpdate #2675

Open heshaShawky opened 10 months ago

heshaShawky commented 10 months ago

Describe the bug

I made an update on a table ( adding a column ), I did the migration correctly and tested it and everything went well on my phone ( Xiaomi 11t Android version 13 )

void _migrationV5(Migrator m) async {
  m.alterTable(
    TableMigration(transactions, columnTransformer: {
      transactions.transactionInDetails: Constant(
        json.encode([]),
      ),
    }, newColumns: [
      transactions.transactionInDetails,
    ]),
  );
}

I published the update to find many phones crashing due to the new column not existing!

So I fixed this by upgrading the database version and did the following ( fixed the creating/updating ...etc ) but still I don't know why the AlterTable didn't work 

void _migrationV6(m) {
  m.addColumn(transactions, transactions.transactionInDetails);
}

now I have another issue that didn't exist before this database upgrade :(

I let users backup their data on my server by getting those data as JSON and saving it.

To install those data again I am using the following function that was working just fine and still my personal phone, but not on another like ( Huawei P30 pro )

Future<Map?> setupBackupDatabase({required String source}) async {
  if (source.isEmpty) {
    return null;
  }

  final Map data = json.decode(source);

  final List<dynamic> transactionsJson = data['transactions'] ?? [];
  final List<Transaction> transactions = transactionsJson
      .map<Transaction>((json) => _database.transactions.map(json))
      .toList();

  final List<dynamic> categoriesJson = data['categories'] ?? [];
  final List<Category> categories = categoriesJson
      .map<Category>((json) => _database.categories.map(json))
      .toList();

  final List<dynamic> walletsJson = data['wallets'] ?? [];
  final List<Wallet> wallets =
      walletsJson.map<Wallet>((json) => _database.wallets.map(json)).toList();

  await _database.batch(
    (batch) {
      batch.insertAllOnConflictUpdate(_database.transactions, transactions);
      batch.insertAllOnConflictUpdate(_database.categories, categories);
      batch.insertAllOnConflictUpdate(_database.wallets, wallets);
    },
  );

  return data;
}

The error I get is the following ( from using insertAllOnConflictUpdate )

```bash
I/flutter (30607): Drift: Sent BEGIN TRANSACTION with args []
I/flutter (30607): Drift: Executing BatchedStatements([INSERT INTO "transactions" ("id", "value", "transaction_in_details", "date", "category_id", "wallet_id", "priority", "synced") VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "value" = ?, "transaction_in_details" = ?, "date" = ?, "categor
I/flutter (30607): y_id" = ?, "wallet_id" = ?, "priority" = ?, "synced" = ?, INSERT INTO "categories" ("id", "name", "logo", "type", "priority", "c_order") VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "name" = ?, "logo" = ?, "type" = ?, "priority" = ?, "c_order" = ?, INSERT INTO "categories" ("i
I/flutter (30607): d", "parent", "name", "logo", "type", "priority", "c_order") VALUES (?, ?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "parent" = ?, "name" = ?, "logo" = ?, "type" = ?, "priority" = ?, "c_order" = ?, INSERT INTO "categories" ("id", "name", "logo", "type", "c_order") VALUES (?, ?, ?, ?,
I/flutter (30607): ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "name" = ?, "logo" = ?, "type" = ?, "c_order" = ?, INSERT INTO "wallets" ("id", "value", "name", "does_it_has_expire_date", "money_source", "is_active") VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "value" = ?, "name" = ?, "does_it_
I/flutter (30607): has_expire_date" = ?, "money_source" = ?, "is_active" = ?], [ArgumentsForBatchedStatement(0, [857a4d80-66db-11ee-bcd2-8fa640af0e00, 125.0, [], 1696880351, 52, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1, 857a4d80-66db-11ee-bcd2-8fa640af0e00, 125.0, [], 1696880351, 52, 021b91dc-d24a-4ea4-8794-5ab6c822
I/flutter (30607): c682, 0, 1]), ArgumentsForBatchedStatement(0, [8c2e4e60-66db-11ee-bcd2-8fa640af0e00, 580.0, [], 1696880363, 18, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1, 8c2e4e60-66db-11ee-bcd2-8fa640af0e00, 580.0, [], 1696880363, 18, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1]), ArgumentsForBatchedStatement(1, [
I/flutter (30607): 1, تحويل اموال, assets/images/categories/002-currency-exchange.svg, 1, 0, 0, 1, تحويل اموال, assets/images/categories/002-currency-exchange.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [2, الغداء, assets/images/categories/003-spoon-and-fork-crossed.svg, 1, 0, 0, 2, الغداء, assets/images/categorie
I/flutter (30607): s/003-spoon-and-fork-crossed.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [3, 2, المقاهي, assets/images/categories/004-coffee.svg, 1, 2, 0, 3, 2, المقاهي, assets/images/categories/004-coffee.svg, 1, 2, 0]), ArgumentsForBatchedStatement(2, [4, 2, المطاعم, assets/images/categories/003-spoon-and-for
I/flutter (30607): k-crossed.svg, 1, 0, 0, 4, 2, المطاعم, assets/images/categories/003-spoon-and-fork-crossed.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [5, المواصلات, assets/images/categories/005-bus.svg, 1, 0, 0, 5, المواصلات, assets/images/categories/005-bus.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [6,
I/flutter (30607): 5, الوقود, assets/images/categories/006-gas-station.svg, 1, 0, 0, 6, 5, الوقود, assets/images/categories/006-gas-station.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [7, 5, الصيانة, assets/images/categories/007-maintenance.svg, 1, 0, 0, 7, 5, الصيانة, assets/images/categories/007-maintenance.svg,
I/flutter (30607):  1, 0, 0]), ArgumentsForBatchedStatement(2, [8, 5, الجراج, assets/images/categories/008-parking-sign.svg, 1, 0, 0, 8, 5, الجراج, assets/images/categories/008-parking-sign.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [9, 5, الأجرة, assets/images/categories/005-bus.svg, 1, 0, 0, 9, 5, الأجرة, asset
I/flutter (30607): s/images/categories/005-bus.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [10, فواتير, assets/images/categories/010-invoice.svg, 1, 0, 0, 10, فواتير, assets/images/categories/010-invoice.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [11, 10, الكهرباء, assets/images/categories/011-rounded-plug.sv
I/flutter (30607): g, 1, 0, 0, 11, 10, الكهرباء, assets/images/categories/011-rounded-plug.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [12, 10, الغاز, assets/images/categories/flame.svg, 1, 0, 0, 12, 10, الغاز, assets/images/categories/flame.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [13, 10, الأنترنت, assets
I/flutter (30607): /images/categories/012-global.svg, 1, 0, 0, 13, 10, الأنترنت, assets/images/categories/012-global.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [14, 10, الأتصالات, assets/images/categories/013-smartphone.svg, 1, 0, 0, 14, 10, الأتصالات, assets/images/categories/013-smartphone.svg, 1, 0, 0]), Argum
I/flutter (30607): entsForBatchedStatement(2, [15, 10, الأيجار, assets/images/categories/014-rent-hanging-signal.svg, 1, 0, 0, 15, 10, الأيجار, assets/images/categories/014-rent-hanging-signal.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [16, 10, التلفاز, assets/images/categories/015-television.svg, 1, 0, 0, 16, 10
I/flutter (30607): , التلفاز, assets/images/categories/015-television.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [17, 10, المياه, assets/images/categories/016-drop.svg, 1, 0, 0, 17, 10, المياه, assets/images/categories/016-drop.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [18, الأسرة, assets/images/categories/
I/flutter (30607): 017-family-silhouette.svg, 1, 0, 0, 18, الأسرة, assets/images/categories/017-family-silhouette.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [19, 18, الأطفال, assets/images/categories/018-happy-children.svg, 1, 0, 0, 19, 18, الأطفال, assets/images/categories/018-happy-children.svg, 1, 0, 0]), Argu
I/flutter (30607): mentsForBatchedStatement(2, [20, 18, الصيانة المنزلية, assets/images/categories/019-settings.svg, 1, 0, 0, 20, 18, الصيانة المنزلية, assets/images/categories/019-settings.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [21, 18, الخدمات, assets/images/categories/019-settings.svg, 1, 0, 0, 21, 18, الخ
I/flutter (30607): دمات, assets/images/categories/019-settings.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [22, 18, الحيوانات الأليفة, assets/images/categories/020-pawprint.svg, 1, 0, 0, 22, 18, الحيوانات الأليفة, assets/images/categories/020-pawprint.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [23, التعليم, a
I/flutter (30607): ssets/images/categories/021-college-graduation.svg, 1, 0, 0, 23, التعليم, assets/images/categories/021-college-graduation.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [24, 23, كتب دراسية, assets/images/categories/022-book-of-black-cover-closed.svg, 1, 0, 0, 24, 23, كتب دراسية, assets/images/categ
I/flutter (30607): ories/022-book-of-black-cover-closed.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [25, 23, الدورات التدريبية, assets/images/categories/023-online-course.svg, 1, 0, 0, 25, 23, الدورات التدريبية, assets/images/categories/023-online-course.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [26, إستثمار
I/flutter (30607): , assets/images/categories/024-investment.svg, 1, 0, 0, 26, إستثمار, assets/images/categories/024-investment.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [27, الترفيه, assets/images/categories/025-theater.svg, 1, 2, 0, 27, الترفيه, assets/images/categories/025-theater.svg, 1, 2, 0]), ArgumentsFor
I/flutter (30607): BatchedStatement(2, [28, 27, الألعاب, assets/images/categories/026-games.svg, 1, 2, 0, 28, 27, الألعاب, assets/images/categories/026-games.svg, 1, 2, 0]), ArgumentsForBatchedStatement(2, [29, 27, افلام وصوتيات, assets/images/categories/027-film.svg, 1, 2, 0, 29, 27, افلام وصوتيات, assets/images/cate
I/flutter (30607): gories/027-film.svg, 1, 2, 0]), ArgumentsForBatchedStatement(1, [30, الرسوم والأشتراكات, assets/images/categories/001-coin.svg, 1, 0, 0, 30, الرسوم والأشتراكات, assets/images/categories/001-coin.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [31, التبرعات والهدايا, assets/images/categories/029-char
I/flutter (30607): ity.svg, 1, 0, 0, 31, التبرعات والهدايا, assets/images/categories/029-charity.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [32, 31, الصدقة, assets/images/categories/028-heart.svg, 1, 0, 0, 32, 31, الصدقة, assets/images/categories/028-heart.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [33, 31,
I/flutter (30607): الزكاة, assets/images/categories/028-heart.svg, 1, 0, 0, 33, 31, الزكاة, assets/images/categories/028-heart.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [34, 31, الهدايا, assets/images/categories/030-giftbox.svg, 1, 0, 0, 34, 31, الهدايا, assets/images/categories/030-giftbox.svg, 1, 0, 0]), Argum
I/flutter (30607): entsForBatchedStatement(1, [35, الصحة واللياقة البدنية, assets/images/categories/031-cardiogram.svg, 1, 0, 0, 35, الصحة واللياقة البدنية, assets/images/categories/031-cardiogram.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [36, 35, الأطباء, assets/images/categories/031-cardiogram.svg, 1, 0, 0, 36
I/flutter (30607): , 35, الأطباء, assets/images/categories/031-cardiogram.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [37, 35, الأدوية, assets/images/categories/034-drugs.svg, 1, 0, 0, 37, 35, الأدوية, assets/images/categories/034-drugs.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [38, 35, العناية الشخصية, asse
I/flutter (30607): ts/images/categories/028-heart.svg, 1, 0, 0, 38, 35, العناية الشخصية, assets/images/categories/028-heart.svg, 1, 0, 0]), ArgumentsForBatchedStatement(2, [39, 35, الأنشطة الرياضية, assets/images/categories/036-dumbbell.svg, 1, 0, 0, 39, 35, الأنشطة الرياضية, assets/images/categories/036-dumbbell.svg,
I/flutter (30607):  1, 0, 0]), ArgumentsForBatchedStatement(1, [40, التأمينات, assets/images/categories/037-insurance.svg, 1, 0, 0, 40, التأمينات, assets/images/categories/037-insurance.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [41, التسوق, assets/images/categories/038-shopping-bag.svg, 1, 1, 0, 41, التسوق, asse
I/flutter (30607): ts/images/categories/038-shopping-bag.svg, 1, 1, 0]), ArgumentsForBatchedStatement(2, [42, 41, اكسسوارات, assets/images/categories/032-necklace.svg, 1, 1, 0, 42, 41, اكسسوارات, assets/images/categories/032-necklace.svg, 1, 1, 0]), ArgumentsForBatchedStatement(2, [43, 41, ملابس, assets/images/categor
I/flutter (30607): ies/033-casual-t-shirt.svg, 1, 1, 0, 43, 41, ملابس, assets/images/categories/033-casual-t-shirt.svg, 1, 1, 0]), ArgumentsForBatchedStatement(2, [44, 41, الكترونيات, assets/images/categories/039-electronics.svg, 1, 1, 0, 44, 41, الكترونيات, assets/images/categories/039-electronics.svg, 1, 1, 0]), Arg
I/flutter (30607): umentsForBatchedStatement(2, [45, 41, أحذية, assets/images/categories/040-sport-shoe.svg, 1, 1, 0, 45, 41, أحذية, assets/images/categories/040-sport-shoe.svg, 1, 1, 0]), ArgumentsForBatchedStatement(1, [46, السفر, assets/images/categories/041-airplane-around-earth.svg, 1, 0, 0, 46, السفر, assets/ima
I/flutter (30607): ges/categories/041-airplane-around-earth.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [47, السحب النقدي, assets/images/categories/042-withdrawal.svg, 1, 0, 0, 47, السحب النقدي, assets/images/categories/042-withdrawal.svg, 1, 0, 0]), ArgumentsForBatchedStatement(1, [48, أخري, assets/images/categor
I/flutter (30607): ies/043-file.svg, 1, 0, 0, 48, أخري, assets/images/categories/043-file.svg, 1, 0, 0]), ArgumentsForBatchedStatement(3, [49, الراتب, assets/images/categories/001-coin.svg, 0, 0, 49, الراتب, assets/images/categories/001-coin.svg, 0, 0]), ArgumentsForBatchedStatement(3, [50, المكافآت, assets/images/cat
I/flutter (30607): egories/028-heart.svg, 0, 0, 50, المكافآت, assets/images/categories/028-heart.svg, 0, 0]), ArgumentsForBatchedStatement(3, [51, الهدايا, assets/images/categories/030-giftbox.svg, 0, 0, 51, الهدايا, assets/images/categories/030-giftbox.svg, 0, 0]), ArgumentsForBatchedStatement(3, [52, المبيعات, asset
I/flutter (30607): s/images/categories/024-investment.svg, 0, 0, 52, المبيعات, assets/images/categories/024-investment.svg, 0, 0]), ArgumentsForBatchedStatement(3, [53, الأضافي, assets/images/categories/001-coin.svg, 0, 0, 53, الأضافي, assets/images/categories/001-coin.svg, 0, 0]), ArgumentsForBatchedStatement(3, [54,
I/flutter (30607):  اخري, assets/images/categories/043-file.svg, 0, 0, 54, اخري, assets/images/categories/043-file.svg, 0, 0]), ArgumentsForBatchedStatement(3, [5119, ايداع نقدي, assets/images/categories/001-coin.svg, 0, 0, 5119, ايداع نقدي, assets/images/categories/001-coin.svg, 0, 0]), ArgumentsForBatchedStatement(4
I/flutter (30607): , [021b91dc-d24a-4ea4-8794-5ab6c822c682, 125000.0, مصاريف البيت , 0, 1, 0, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 125000.0, مصاريف البيت , 0, 1, 0])]) in a batch
E/SQLiteLog(30607): (1) near "ON": syntax error
I/flutter (30607): Drift: Sent ROLLBACK TRANSACTION with args []
I/flutter (30607): DatabaseException(near "ON": syntax error (Sqlite code 1 SQLITE_ERROR): , while compiling: INSERT INTO "transactions" ("id", "value", "transaction_in_details", "date", "category_id", "wallet_id", "priority", "synced") VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "value" = ?, "transaction_in_details" = ?, "date" = ?, "category_id" = ?, "wallet_id" = ?, "priority" = ?, "synced" = ?, (OS error - 2:No such file or directory)) sql 'INSERT INTO "transactions" ("id", "value", "transaction_in_details", "date", "category_id", "wallet_id", "priority", "synced") VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "value" = ?, "transaction_in_details" = ?, "date" = ?, "category_id" = ?, "wallet_id" = ?, "priority" = ?, "synced" = ?' args [857a4d80-66db-11ee-bcd2-8fa640af0e00, 125.0, [], 1696880351, 52, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1, 857a4d80-66db-11ee-bcd2-8fa640af0e00, 125.0, [], 1696880351, 52, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1] #0      wrapDatab
I/flutter (30607): ----------------FIREBASE CRASHLYTICS----------------
I/flutter (30607): DatabaseException(near "ON": syntax error (Sqlite code 1 SQLITE_ERROR): , while compiling: INSERT INTO "transactions" ("id", "value", "transaction_in_details", "date", "category_id", "wallet_id", "priority", "synced") VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "value" = ?, "transaction_in_details" = ?, "date" = ?, "category_id" = ?, "wallet_id" = ?, "priority" = ?, "synced" = ?, (OS error - 2:No such file or directory)) sql 'INSERT INTO "transactions" ("id", "value", "transaction_in_details", "date", "category_id", "wallet_id", "priority", "synced") VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("id") DO UPDATE SET "id" = ?, "value" = ?, "transaction_in_details" = ?, "date" = ?, "category_id" = ?, "wallet_id" = ?, "priority" = ?, "synced" = ?' args [857a4d80-66db-11ee-bcd2-8fa640af0e00, 125.0, [], 1696880351, 52, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1, 857a4d80-66db-11ee-bcd2-8fa640af0e00, 125.0, [], 1696880351, 52, 021b91dc-d24a-4ea4-8794-5ab6c822c682, 0, 1]
I/flutter (30607):
I/flutter (30607): #0      wrapDatabaseException
exception_impl.dart:11
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #1      SqfliteDatabaseMixin.txnApplyBatch.<anonymous closure>
database_mixin.dart:699
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #2      BasicLock.synchronized
basic_lock.dart:33
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #3      SqfliteDatabaseMixin.txnSynchronized
database_mixin.dart:490
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #4      _SqfliteDelegate.runBatched
drift_sqflite.dart:77
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #5      Batch._commit
batch.dart:199
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #6      DatabaseRepository.setupBackupDatabase
database_repository.dart:86
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #7      SettingsService.setupBackup
settings_service.dart:241
I/flutter (30607): <asynchronous suspension>
I/flutter (30607): #8      BackupCubit.install
I/flutter (30607): ----------------------------------------------------
simolus3 commented 10 months ago

You should be awaiting methods on the migrator, but that is unlikely to cause the issue.

Are you using a database backed by the sqflite package? The E/SQLiteLog indicates that. That package is using the sqlite3 version from the operating system, which can be outdated. Older sqlite3 versions don't support upserts.

Is there a particular reason you're not using a NativeDatabase with sqlite3_flutter_libs as described in the setup guide? That setup includes its own sqlite3 library with your application, which is typically much newer.

heshaShawky commented 10 months ago

You should be awaiting methods on the migrator, but that is unlikely to cause the issue.

Are you using a database backed by the sqflite package? The E/SQLiteLog indicates that. That package is using the sqlite3 version from the operating system, which can be outdated. Older sqlite3 versions don't support upserts.

Is there a particular reason you're not using a NativeDatabase with sqlite3_flutter_libs as described in the setup guide? That setup includes its own sqlite3 library with your application, which is typically much newer.

I am using the same as the guide 

dependencies:
  drift: ^2.10.0
  drift_sqflite: ^2.0.1
  sqlite3_flutter_libs: ^0.5.17

Also, I have solved the issue of backup by doing this,

but now on some user devices, he is getting an error creating a record ( mostly a migration thing ) but it is only on his device so far, so I don't know why there is a difference between devices like that and what I did wrong to cause this :(

// await _database.batch(
//   (batch) {
//     batch.insertAllOnConflictUpdate(_database.transactions, transactions);
//     batch.insertAllOnConflictUpdate(_database.categories, categories);
//     batch.insertAllOnConflictUpdate(_database.wallets, wallets);
//   },
// );

await _database.batch(
  (batch) {
    batch.insertAll(_database.transactions, transactions,
        mode: InsertMode.insertOrReplace);
    batch.insertAll(_database.categories, categories,
        mode: InsertMode.insertOrReplace);
    batch.insertAll(_database.wallets, wallets,
        mode: InsertMode.insertOrReplace);
  },
);
simolus3 commented 10 months ago

he is getting an error creating a record ( mostly a migration thing ) but it is only on his device so far

Do you have access to the stack trace?

heshaShawky commented 10 months ago

he is getting an error creating a record ( mostly a migration thing ) but it is only on his device so far

Do you have access to the stack trace?

Yes from Firebase Crashlytics ( it says that the field doesn't exist, even when he uninstalls the app and reinstalls it again, so it should there's no migration there but still get an error that causes his screen to go grey blank )

Fatal Exception: io.flutter.plugins.firebase.crashlytics.FlutterError: DatabaseException(no such column: transaction_in_details (code 1): , while compiling: UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (no such column: transaction_in_details (code 1): , while compiling: UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;)
#################################################################) sql 'UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;' args [60.0, null, فروج, 1652615700, null, 2, 7410de8c-8189-49c4-b75f-9c121db28f49, [], 0, null, {"lat":null,"lng":null,"address":null}, 1, 04257e41-628b-41d6-b690-35643c0fd378]
       at .wrapDatabaseException(exception_impl.dart:11)
       at SqfliteDatabaseMixin.txnApplyBatch.<fn>(database_mixin.dart:699)
       at BasicLock.synchronized(basic_lock.dart:33)
       at SqfliteDatabaseMixin.txnSynchronized(database_mixin.dart:490)
       at _SqfliteDelegate.runBatched(drift_sqflite.dart:77)
       at Batch._commit(batch.dart:199)
       at DatabaseRepository.transactionsBackedup(database_repository.dart:110)
       at SettingsService.backup(settings_service.dart:202)
       at BackupCubit.backup(backup_cubit.dart:31)
heshaShawky commented 10 months ago

I hope there's an update with this, why on some phones does the new column not exist?

simolus3 commented 10 months ago

Sorry, I still don't fully understand what's happening here. Given that this happens on some phones only, I think it's due to an older sqlite3 version. Since you already have a dependency on sqlite3_flutter_libs, you could try using a NativeDatabase to use more recent sqlite3 versions. That won't fix the problem for existing users where the database is missing the column though.

How are you calling the _migrationV6 functions, are there try/catch blocks in the onUpgrade callback?

heshaShawky commented 10 months ago

Here's my whole database file

The weird thing is, it was all fine before this last migration!

Also I don't know how to do what you saying about using more recent SQLite 3, how to achieve that 

@singleton
@DriftDatabase(tables: [
  Users,
  Settings,
  Wallets,
  Transactions,
  Categories,
], daos: [
  UsersDao,
  SettingsDao,
  WalletsDao,
  TransactionsDao,
  CategoryDao,
], include: {
  'categories.drift'
})
class AppDatabase extends _$AppDatabase {
  static const Uuid uuid = Uuid();

  AppDatabase.connect(DatabaseConnection connection)
      : super.connect(connection);

  AppDatabase()
      : super(SqfliteQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        ));

  @override
  int get schemaVersion => 6;

  @override
  MigrationStrategy get migration =>
      MigrationStrategy(beforeOpen: _beforeOpen, onUpgrade: _onUpgrade);

  Future<void> _beforeOpen(OpeningDetails details) async {
    if (details.wasCreated) {
      // create new settings row on database first created
      await into(settings).insert(
        const Setting(
          isFirstLaunch: true,
          id: 1,
        ),
      );
    } else if (details.hadUpgrade && details.versionBefore == 3) {
      // create a backup after users updating
      getIt<ISettingsService>().backup();
    }
  }

  Future<void> _onUpgrade(Migrator m, int from, int to) async {
    switch (from) {
      case 1:
        await _migrationV2(m);
        break;
      case 2:
        await _migrationV3(m);
        break;
      case 3:
        await _migrationV4(m);
        break;
      case 4:
        await _migrationV5(m);
        break;
      case 5:
        await _migrationV6(m);
        break;

      default:
    }
  }

  Future<void> _migrationV2(Migrator m) async {
    await Future.wait([
      m.addColumn(wallets, wallets.endDate),
      m.addColumn(wallets, wallets.doesItHasExpireDate),
    ]);
  }

  Future<void> _migrationV3(Migrator m) async {
    await Future.wait(
      [
        m.addColumn(settings, settings.isProtected),
        m.alterTable(
          TableMigration(
            users,
            columnTransformer: {
              users.email: const Constant(null),
              users.token: const Constant(null),
              users.profilePicture: const Constant(null),
            },
            newColumns: [users.email, users.token, users.profilePicture],
          ),
        ),
        m.alterTable(
          TableMigration(
            wallets,
            columnTransformer: {
              wallets.id: wallets.id.cast<String>(),
              wallets.countryAndCurrency: const Constant(null)
            },
            newColumns: [wallets.countryAndCurrency],
          ),
        ),
        m.alterTable(
          TableMigration(
            categories,
            columnTransformer: {
              categories.id: categories.id.cast<String>(),
              categories.parent: categories.parent.cast<String>(),
              categories.cOrder: const Constant(0),
            },
            newColumns: [categories.cOrder],
          ),
        ),
        m.alterTable(
          TableMigration(
            transactions,
            columnTransformer: {
              transactions.id: transactions.id.cast<String>(),
              transactions.walletId: transactions.walletId.cast<String>(),
              transactions.categoryId: transactions.categoryId.cast<String>(),
              transactions.priority: const Constant(null),
              transactions.contact: const Constant(null),
              transactions.location: const Constant(null),
            },
            newColumns: [
              transactions.priority,
              transactions.contact,
              transactions.location
            ],
          ),
        )
      ],
    );
  }

  Future<void> _migrationV4(Migrator m) async {
    // users migration
    m.addColumn(users, users.isActive);

    // transactions migration
    m.alterTable(
      TableMigration(transactions, columnTransformer: {
        transactions.synced: const Constant(false),
      }, newColumns: [
        transactions.synced
      ]),
    );

    // wallets migration
    m.alterTable(
      TableMigration(wallets, columnTransformer: {
        wallets.moneySource: const CustomExpression('moeny_resource'),
        wallets.isActive: const Constant(false),
      }, newColumns: [
        wallets.isActive
      ]),
    );

    // updating categories
    getIt<ICategoryService>().updateDefaultCategories(
        (await getIt<ISettingsService>().getSavedSettings())?.language ?? 'en');

    final List<Transaction> oldTransactions = await (select(transactions)
          ..where((tbl) => tbl.contact.isNotNull()))
        .get();

    for (var i = 0; i < oldTransactions.length; i++) {
      final Transaction transaction = oldTransactions[i];
      final Contact? contact =
          await getIt<ContactPickerService>().find(transaction.contact);

      update(transactions).replace(
        transaction.copyWith(
          contact: Value(contact),
        ),
      );
    }

    // updating users database
    customUpdate(
        'UPDATE wallets SET user_id = ( SELECT id FROM users LIMIT 1 )');
  }

  Future<void> _migrationV5(Migrator m) async {
    await m.alterTable(
      TableMigration(transactions, columnTransformer: {
        transactions.transactionInDetails: Constant(
          json.encode([]),
        ),
      }, newColumns: [
        transactions.transactionInDetails,
      ]),
    );
  }

  Future<void> _migrationV6(Migrator m) async {
    await m.addColumn(transactions, transactions.transactionInDetails);
  }
}
simolus3 commented 10 months ago

Your onUpgrade callback is only doing a single migration step :scream: If a user has a version of your app with schema version 2 installed and now updates to the latest version, _onUpgrade(m, 2, 6) is called. The switch then calls _migrationV3 and calls it a day, missing the migrations from 4 to 5 and 5 to 6.

Your migration should look like this:

  Future<void> _onUpgrade(Migrator m, int from, int to) async {
    for (var current = from; current < to; current++) {
      switch (current) {
        case 1:
          await _migrationV2(m);
          break;
        case 2:
          await _migrationV3(m);
          break;
        case 3:
          await _migrationV4(m);
          break;
        case 4:
          await _migrationV5(m);
          break;
        case 5:
          await _migrationV6(m);
          break;
        default:
          throw StateError('Unhandled migration from version $from');
      }
      current++;
    }
  }

This is likely the source of the problem - old users will never get the latest version of your database. Unfortunately this is pretty hard to fix afterwards - the database having the latest schema version doesn't mean much if the schema could contain anything. If losing local data is an acceptable solution, you could upgrade the schema version to 7 and write a migration script that resets the database:

  Future<void> _onUpgrade(Migrator m, int from, int to) async {
    if (from < 7) {
      // Older migrations were broken, reset the database in v7 to fix this.
      final reversedEntities = allSchemaEntities.toList().reversed;

      for (final entity in reversedEntities) {
        await m.drop(entity);
      }

      // Re-create them now
      await m.createAll();
      return;
    }

    for (var current = from; current < to; current++) {
      switch (current) {
        // This is where you'd put migrations from v7 onwards if you need
        // a v8 version later.
        default:
          throw StateError('Unhandled migration from version $from');
      }
      current++;
    }
  }

Also I don't know how to do what you saying about using more recent SQLite 3, how to achieve that

Replace

  AppDatabase()
      : super(SqfliteQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        ));

with

  AppDatabase()
      : super(LazyDatabase(() async {
          final dbFolder = await getDatabasesPath();
          final file = File(p.join(dbFolder, 'db.sqlite'));

          return NativeDatabase.createInBackground(file);
        }));

You'd also have to add imports:

import 'package:drift/native.dart';
import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart' show getDatabasesPath;
heshaShawky commented 10 months ago

Yes, I think you are right :'(

But The phones that are affected I got someone to try to uninstall the app and reinstall it so they sure have the latest version of the database directly without a need to migrate from the old version I think but they still have a problem with that column!

also why did you put 2 times currrent++ on the loop and, let me know if that way will work to update and not losing users data

@singleton
@DriftDatabase(tables: [
  Users,
  Settings,
  Wallets,
  Transactions,
  Categories,
], daos: [
  UsersDao,
  SettingsDao,
  WalletsDao,
  TransactionsDao,
  CategoryDao,
], include: {
  'categories.drift'
})
class AppDatabase extends _$AppDatabase {
  static const Uuid uuid = Uuid();

  AppDatabase.connect(DatabaseConnection connection)
      : super.connect(connection);

  AppDatabase()
      : super(SqfliteQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        ));

  @override
  int get schemaVersion => 6;

  @override
  MigrationStrategy get migration =>
      MigrationStrategy(beforeOpen: _beforeOpen, onUpgrade: _onUpgrade);

  Future<void> _beforeOpen(OpeningDetails details) async {
    if (details.wasCreated) {
      // create new settings row on database first created
      await into(settings).insert(
        const Setting(
          isFirstLaunch: true,
          id: 1,
        ),
      );
    } else if (details.hadUpgrade && details.versionBefore == 3) {
      // create a backup after users updating
      getIt<ISettingsService>().backup();
    }
  }

  Future<void> _onUpgrade(Migrator m, int from, int to) async {
    if (from < 7) {
      // get backup to my server for signed users
      await getIt<SettingsService>().backup();

      // let's get a backup first!
      final List<Transaction> transactionsList = (await customSelect(
              'SELECT * FROM transactions',
              readsFrom: {transactions}).get())
          .map((e) => transactions.map(e.data))
          .toList();

      final List<User> usersList =
          (await customSelect('SELECT * FROM users', readsFrom: {users}).get())
              .map((e) => users.map(e.data))
              .toList();

      final List<Wallet> walletsList =
          (await customSelect('SELECT * FROM wallets', readsFrom: {wallets})
                  .get())
              .map((e) => wallets.map(e.data))
              .toList();

      final List<Category> categoriesList = (await customSelect(
              'SELECT * FROM categories',
              readsFrom: {categories}).get())
          .map((e) => categories.map(e.data))
          .toList();

      final List<Setting> settingsList =
          (await customSelect('SELECT * FROM settings', readsFrom: {settings})
                  .get())
              .map((e) => settings.map(e.data))
              .toList();

      // Older migrations were broken, reset the database in v7 to fix this.
      final reversedEntities = allSchemaEntities.toList().reversed;

      for (final entity in reversedEntities) {
        await m.drop(entity);
      }

      // Re-create them now
      await m.createAll();

      // no add all data to database
      await batch((batch) {
        batch.insertAll(settings, settingsList);
        batch.insertAll(users, usersList);
        batch.insertAll(categories, categoriesList);
        batch.insertAll(wallets, walletsList);
        batch.insertAll(transactions, transactionsList);
      });
      return;
    }

    for (var current = from; current < to; current++) {
      switch (current) {
        // nothing here as there's nothing to do for now 
        default:
          throw StateError('Unhandled migration from version $from');
      }
      current++; // why you adding another increase in here
    }
  }
}
heshaShawky commented 10 months ago

How is it still I get this error from some devices after that upgrade which is supposed to drop the database tables and make a new one!, this becomes really weird 

Fatal Exception: io.flutter.plugins.firebase.crashlytics.FlutterError: DatabaseException(no such column: transaction_in_details (code 1 SQLITE_ERROR[1]): , while compiling: UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;) sql 'UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;' args [1340.0, null, null, 1661062693, null, 52, 554c520c-93f9-4f19-b41f-9ef43e162eb2, [], 0, null, {"lat":null,"lng":null,"address":null}, 1, 54cf6657-5439-4339-9132-409361dceafd]
       at .wrapDatabaseException(exception_impl.dart:11)
       at SqfliteDatabaseMixin.txnApplyBatch.<fn>(database_mixin.dart:699)
       at BasicLock.synchronized(basic_lock.dart:33)
       at SqfliteDatabaseMixin.txnSynchronized(database_mixin.dart:490)
       at _SqfliteDelegate.runBatched(drift_sqflite.dart:77)
       at Batch._commit(batch.dart:199)
       at DatabaseRepository.transactionsBackedup(database_repository.dart:99)
       at SettingsService.backup(settings_service.dart:202)
       at AppDatabase._onUpgrade(database.dart:88)
       at GeneratedDatabase.beforeOpen.<fn>(db_base.dart:129)
       at DelegatedDatabase._runMigrations(engines.dart:458)
       at DelegatedDatabase.ensureOpen.<fn>(engines.dart:426)
       at Selectable.getSingleOrNull(query.dart:240)
       at SettingsService.getSavedSettings(settings_service.dart:108)
       at SettingsCubit.getSavedSettings(settings_cubit.dart:34)

Fatal Exception: io.flutter.plugins.firebase.crashlytics.FlutterError: DatabaseException(no such column: transaction_in_details (code 1 SQLITE_ERROR): , while compiling: UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;) sql 'UPDATE "transactions" SET "value" = ?, "transaction_in_details" = ?, "description" = ?, "date" = ?, "repeated_period" = ?, "category_id" = ?, "wallet_id" = ?, "attachments" = ?, "priority" = ?, "contact" = ?, "location" = ?, "synced" = ? WHERE "id" = ?;' args [10.0, null, null, 1684484028, null, 18, 7b5de104-3bae-4ab7-bceb-470bb69f6886, null, 1, null, null, 1, 14cff190-f61d-11ed-958a-13ab8a76bf30]
       at .wrapDatabaseException(exception_impl.dart:11)
       at SqfliteDatabaseMixin.txnApplyBatch.<fn>(database_mixin.dart:699)
       at BasicLock.synchronized(basic_lock.dart:33)
       at SqfliteDatabaseMixin.txnSynchronized(database_mixin.dart:490)
       at _SqfliteDelegate.runBatched(drift_sqflite.dart:77)
       at Batch._commit(batch.dart:199)
       at DatabaseRepository.transactionsBackedup(database_repository.dart:99)
       at SettingsService.backup(settings_service.dart:202)
       at AppDatabase._onUpgrade(database.dart:88)
       at GeneratedDatabase.beforeOpen.<fn>(db_base.dart:129)
       at DelegatedDatabase._runMigrations(engines.dart:458)
       at DelegatedDatabase.ensureOpen.<fn>(engines.dart:426)
       at Selectable.getSingleOrNull(query.dart:240)
       at SettingsService.getSavedSettings(settings_service.dart:108)
       at SettingsCubit.getSavedSettings(settings_cubit.dart:34)
@singleton
@DriftDatabase(tables: [
  Users,
  Settings,
  Wallets,
  Transactions,
  Categories,
], daos: [
  UsersDao,
  SettingsDao,
  WalletsDao,
  TransactionsDao,
  CategoryDao,
], include: {
  'categories.drift'
})
class AppDatabase extends _$AppDatabase {
  static const Uuid uuid = Uuid();

  AppDatabase.connect(DatabaseConnection connection)
      : super.connect(connection);

  AppDatabase()
      : super(SqfliteQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        ));

  @override
  int get schemaVersion => 7;

  @override
  MigrationStrategy get migration =>
      MigrationStrategy(beforeOpen: _beforeOpen, onUpgrade: _onUpgrade);

  Future<void> _beforeOpen(OpeningDetails details) async {
    if (details.wasCreated) {
      // create new settings row on database first created
      await into(settings).insert(
        const Setting(
          isFirstLaunch: true,
          id: 1,
        ),
      );
    } else if (details.hadUpgrade && details.versionBefore == 3) {
      // create a backup after users updating
      getIt<ISettingsService>().backup();
    }
  }

  Future<void> _onUpgrade(Migrator m, int from, int to) async {
    if (from < 7) {
      try {
        // get backup to my server for signed users
        await getIt<ISettingsService>().backup();
      } catch (e, stack) {
        print('$e  $stack');

        getIt<FirebaseCrashlytics>()
            .recordError(e, stack, fatal: true, printDetails: true);
      }

      // let's get a backup first!
      final List<Transaction> transactionsList = (await customSelect(
              'SELECT * FROM transactions',
              readsFrom: {transactions}).get())
          .map((e) => transactions.map(e.data))
          .toList();

      final List<User> usersList =
          (await customSelect('SELECT * FROM users', readsFrom: {users}).get())
              .map((e) => users.map(e.data))
              .toList();

      final List<Wallet> walletsList =
          (await customSelect('SELECT * FROM wallets', readsFrom: {wallets})
                  .get())
              .map((e) => wallets.map(e.data))
              .toList();

      final List<Category> categoriesList = (await customSelect(
              'SELECT * FROM categories',
              readsFrom: {categories}).get())
          .map((e) => categories.map(e.data))
          .toList();

      final List<Setting> settingsList =
          (await customSelect('SELECT * FROM settings', readsFrom: {settings})
                  .get())
              .map((e) => settings.map(e.data))
              .toList();

      // Older migrations were broken, reset the database in v7 to fix this.
      final reversedEntities = allSchemaEntities.toList().reversed;

      for (final entity in reversedEntities) {
        await m.drop(entity);
      }

      // Re-create them now
      await m.createAll();

      try {
        // no add all data to database
        await batch((batch) {
          batch.insertAll(settings, settingsList);
          batch.insertAll(users, usersList);
          batch.insertAll(categories, categoriesList);
          batch.insertAll(wallets, walletsList);
          batch.insertAll(transactions, transactionsList);
        });
      } catch (e, stack) {
        print('$e  $stack');

        await getIt<ISettingsService>().setupBackup();

        getIt<FirebaseCrashlytics>()
            .recordError(e, stack, fatal: true, printDetails: true);
      }
      return;
    }
  }

}

Here's my transactions table too 

part of '../database.dart';

class Transactions extends Table {
  TextColumn get id =>
      text().nullable().clientDefault(() => AppDatabase.uuid.v4())();
  RealColumn get value => real()();
  TextColumn get transactionInDetails => text().nullable()(); // this field is exist here, how is not in the database?!
  TextColumn get description => text().nullable()();
  DateTimeColumn get date => dateTime().withDefault(Constant(DateTime.now()))();
  IntColumn get repeatedPeriod => intEnum<RepetitionPeriod>().nullable()();
  TextColumn get categoryId =>
      text().nullable().customConstraint('NULL REFERENCES categories(id)')();
  TextColumn get walletId =>
      text().nullable().customConstraint('NULL REFERENCES wallets(id)')();
  TextColumn get attachments =>
      text().map(const TransactionsAttachmentsConverter()).nullable()();
  IntColumn get priority => intEnum<TransactionPriority>().nullable()();
  TextColumn get contact => text().map(const ContactConverter()).nullable()();
  TextColumn get location => text().map(const LocationConverter()).nullable()();
  BoolColumn get synced =>
      boolean().withDefault(const Constant(false)).nullable()();

  @override
  Set<Column> get primaryKey => {id};
}