replace drift_flutter with drift_sqlite_async

mobile/lib/infrastructure/repositories/db.repository.dart

@@ -1,7 +1,8 @@
 import 'dart:async';
+import 'dart:io';
 
 import 'package:drift/drift.dart';
-import 'package:drift_flutter/drift_flutter.dart';
+import 'package:drift_sqlite_async/drift_sqlite_async.dart';
 import 'package:flutter/foundation.dart';
 import 'package:immich_mobile/infrastructure/entities/asset_edit.entity.dart';
 import 'package:immich_mobile/infrastructure/entities/asset_face.entity.dart';
@@ -31,6 +32,10 @@ import 'package:immich_mobile/infrastructure/entities/user_metadata.entity.dart'
 import 'package:immich_mobile/infrastructure/repositories/db.repository.drift.dart';
 import 'package:immich_mobile/infrastructure/repositories/db.repository.steps.dart';
 import 'package:logging/logging.dart';
+import 'package:path/path.dart' as p;
+import 'package:path_provider/path_provider.dart';
+import 'package:sqlite3/sqlite3.dart';
+import 'package:sqlite_async/sqlite_async.dart';
 
 @DriftDatabase(
   tables: [
@@ -60,8 +65,9 @@ import 'package:logging/logging.dart';
   include: {'package:immich_mobile/infrastructure/entities/merged_asset.drift'},
 )
 class Drift extends $Drift {
-  Drift([QueryExecutor? executor])
-    : super(executor ?? driftDatabase(name: 'immich', native: const DriftNativeOptions(shareAcrossIsolates: true)));
+  Drift(super.executor);
+
+  Drift.sqlite(SqliteConnection db) : super(SqliteAsyncDriftConnection(db));
 
   Future<void> reset() async {
     // https://github.com/simolus3/drift/commit/bd80a46264b6dd833ef4fd87fffc03f5a832ab41#diff-3f879e03b4a35779344ef16170b9353608dd9c42385f5402ec6035aac4dd8a04R76-R94
@@ -305,3 +311,18 @@ class DriftDatabaseRepository {
 
   Future<T> transaction<T>(Future<T> Function() callback) => _db.transaction(callback);
 }
+
+Future<SqliteConnection> openSqliteConnection({required String name}) async {
+  final dbFolder = await getApplicationDocumentsDirectory();
+  final file = File(p.join(dbFolder.path, '$name.sqlite'));
+  return SqliteDatabase(path: file.path);
+}
+
+Future<void> configureSqliteCache() async {
+  // Make sqlite3 pick a more suitable location for temporary files - the
+  // one from the system may be inaccessible due to sand-boxing.
+  final cacheBase = (await getTemporaryDirectory()).path;
+  // We can't access /tmp on Android, which sqlite3 would try by default.
+  // Explicitly tell it about the correct temporary directory.
+  sqlite3.tempDirectory = cacheBase;
+}

mobile/lib/infrastructure/repositories/logger_db.repository.dart

@@ -1,14 +1,14 @@
 import 'package:drift/drift.dart';
-import 'package:drift_flutter/drift_flutter.dart';
+import 'package:drift_sqlite_async/drift_sqlite_async.dart';
 import 'package:immich_mobile/infrastructure/entities/log.entity.dart';
 import 'package:immich_mobile/infrastructure/repositories/logger_db.repository.drift.dart';
+import 'package:sqlite_async/sqlite_async.dart';
 
 @DriftDatabase(tables: [LogMessageEntity])
 class DriftLogger extends $DriftLogger {
-  DriftLogger([QueryExecutor? executor])
-    : super(
-        executor ?? driftDatabase(name: 'immich_logs', native: const DriftNativeOptions(shareAcrossIsolates: true)),
-      );
+  DriftLogger.fromExecutor(super.executor);
+
+  DriftLogger.sqlite(SqliteConnection db) : super(SqliteAsyncDriftConnection(db));
 
   @override
   int get schemaVersion => 1;
@@ -19,7 +19,8 @@ class DriftLogger extends $DriftLogger {
       await customStatement('PRAGMA foreign_keys = ON');
       await customStatement('PRAGMA synchronous = NORMAL');
       await customStatement('PRAGMA journal_mode = WAL');
-      await customStatement('PRAGMA busy_timeout = 500');
+      await customStatement('PRAGMA busy_timeout = 30000'); // 30s
+      await customStatement('PRAGMA cache_size = -32000'); // 32MB
       await customStatement('PRAGMA temp_store = MEMORY');
     },
   );

mobile/lib/infrastructure/repositories/sync_stream.repository.dart

@@ -197,16 +197,6 @@ class SyncStreamRepository extends DriftDatabaseRepository {
     try {
       await _db.batch((batch) {
         for (final asset in data) {
-          // Avoid SqliteException(2067) when server re-issues a new id for
-          // the same (ownerId, checksum). #22522 #27186
-          _enqueueRemoteAssetDedupe(
-            batch,
-            id: asset.id,
-            ownerId: asset.ownerId,
-            checksum: asset.checksum,
-            libraryId: asset.libraryId,
-          );
-
           final companion = RemoteAssetEntityCompanion(
             name: Value(asset.originalFileName),
             type: Value(asset.type.toAssetType()),
@@ -246,15 +236,6 @@ class SyncStreamRepository extends DriftDatabaseRepository {
     try {
       await _db.batch((batch) {
         for (final asset in data) {
-          // See updateAssetsV1 for why this dedupe is required. #22522 #27186
-          _enqueueRemoteAssetDedupe(
-            batch,
-            id: asset.id,
-            ownerId: asset.ownerId,
-            checksum: asset.checksum,
-            libraryId: asset.libraryId,
-          );
-
           final companion = RemoteAssetEntityCompanion(
             name: Value(asset.originalFileName),
             type: Value(asset.type.toAssetType()),
@@ -290,39 +271,6 @@ class SyncStreamRepository extends DriftDatabaseRepository {
     }
   }
 
-  /// Queues a DELETE that prunes any stale remote_asset row matching the
-  /// partial UNIQUE index for the incoming asset:
-  ///   - libraryId IS NULL  -> (owner_id, checksum)
-  ///   - libraryId NOT NULL -> (owner_id, library_id, checksum)
-  /// The current id is excluded so a same-id update does not delete itself.
-  void _enqueueRemoteAssetDedupe(
-    Batch batch, {
-    required String id,
-    required String ownerId,
-    required String checksum,
-    required String? libraryId,
-  }) {
-    if (libraryId == null) {
-      batch.deleteWhere(
-        _db.remoteAssetEntity,
-        (row) =>
-            row.ownerId.equals(ownerId) &
-            row.checksum.equals(checksum) &
-            row.libraryId.isNull() &
-            row.id.equals(id).not(),
-      );
-    } else {
-      batch.deleteWhere(
-        _db.remoteAssetEntity,
-        (row) =>
-            row.ownerId.equals(ownerId) &
-            row.checksum.equals(checksum) &
-            row.libraryId.equals(libraryId) &
-            row.id.equals(id).not(),
-      );
-    }
-  }
-
   Future<void> updateAssetsExifV1(Iterable<SyncAssetExifV1> data, {String debugLabel = 'user'}) async {
     try {
       await _db.batch((batch) {

mobile/lib/utils/bootstrap.dart

@@ -43,8 +43,9 @@ void configureFileDownloaderNotifications() {
 
 abstract final class Bootstrap {
   static Future<(Drift, DriftLogger)> initDomain({bool listenStoreUpdates = true, bool shouldBufferLogs = true}) async {
-    final drift = Drift();
-    final logDb = DriftLogger();
+    await configureSqliteCache();
+    final drift = Drift.sqlite(await openSqliteConnection(name: 'immich'));
+    final logDb = DriftLogger.sqlite(await openSqliteConnection(name: 'immich_logs'));
     final DriftStoreRepository storeRepo = DriftStoreRepository(drift);
 
     await StoreService.init(storeRepository: storeRepo, listenUpdates: listenStoreUpdates);

mobile/pubspec.lock

@@ -370,11 +370,11 @@ packages:
       url: "https://pub.dev"
     source: hosted
     version: "2.32.1"
-  drift_flutter:
+  drift_sqlite_async:
     dependency: "direct main"
     description:
-      name: drift_flutter
-      sha256: "887fdec622174dc7eaefd0048403e34ee07cc18626ac8a7544cc3b8a4a172166"
+      name: drift_sqlite_async
+      sha256: "1b6e99562fc5d35fe5e3696741720a8aca47f4c3eee35d4b9b94be819f53a6f6"
       url: "https://pub.dev"
     source: hosted
     version: "0.3.0"
@@ -1619,30 +1619,38 @@ packages:
       url: "https://pub.dev"
     source: hosted
     version: "1.10.2"
-  sqlcipher_flutter_libs:
-    dependency: transitive
-    description:
-      name: sqlcipher_flutter_libs
-      sha256: "38d62d659d2fb8739bf25a42c9a350d1fdd6c29a5a61f13a946778ec75d27929"
-      url: "https://pub.dev"
-    source: hosted
-    version: "0.7.0+eol"
   sqlite3:
-    dependency: transitive
+    dependency: "direct main"
     description:
       name: sqlite3
       sha256: "56da3e13ed7d28a66f930aa2b2b29db6736a233f08283326e96321dd812030f5"
       url: "https://pub.dev"
     source: hosted
     version: "3.3.1"
-  sqlite3_flutter_libs:
+  sqlite3_connection_pool:
     dependency: transitive
     description:
-      name: sqlite3_flutter_libs
-      sha256: "3ed7553eee7bb368f8950f58ba29f634e06e813c029aff6a0d60862b96de8454"
+      name: sqlite3_connection_pool
+      sha256: "90b25972c7699d84da97df1c5919804275560b4ab8a158bbec890434b9718f65"
       url: "https://pub.dev"
     source: hosted
-    version: "0.6.0+eol"
+    version: "0.2.4"
+  sqlite3_web:
+    dependency: transitive
+    description:
+      name: sqlite3_web
+      sha256: d876398a9f2cbf115d93fc34901f8fa129b58b13b5fa9377156ed3a9a05695e3
+      url: "https://pub.dev"
+    source: hosted
+    version: "0.7.1"
+  sqlite_async:
+    dependency: "direct main"
+    description:
+      name: sqlite_async
+      sha256: "4c243c5386eba3a7102f98999388a7e0a7f2632e4e06dafb3b4f5a44170a26f6"
+      url: "https://pub.dev"
+    source: hosted
+    version: "0.14.1"
   sqlparser:
     dependency: transitive
     description:

mobile/pubspec.yaml

@@ -19,7 +19,7 @@ dependencies:
   crypto: ^3.0.7
   device_info_plus: ^12.4.0
   drift: ^2.32.1
-  drift_flutter: ^0.3.0
+  drift_sqlite_async: 0.3.0
   dynamic_color: ^1.8.1
   easy_localization: ^3.0.8
   ffi: ^2.2.0
@@ -66,6 +66,8 @@ dependencies:
   share_plus: ^10.1.4
   sliver_tools: ^0.2.12
   stream_transform: ^2.1.1
+  sqlite3: ^3.3.1
+  sqlite_async: 0.14.1
   thumbhash: 0.1.0+1
   timezone: ^0.9.4
   url_launcher: ^6.3.2

mobile/test/domain/repositories/sync_stream_repository_test.dart

@@ -28,7 +28,6 @@ SyncAssetV1 _createAsset({
   String ownerId = 'user-1',
   int? width,
   int? height,
-  String? libraryId,
 }) {
   return SyncAssetV1(
     id: id,
@@ -46,38 +45,7 @@ SyncAssetV1 _createAsset({
     height: height,
     deletedAt: null,
     duration: null,
-    libraryId: libraryId,
-    livePhotoVideoId: null,
-    stackId: null,
-    thumbhash: null,
-    isEdited: false,
-  );
-}
-
-SyncAssetV2 _createAssetV2({
-  required String id,
-  required String checksum,
-  required String fileName,
-  String ownerId = 'user-1',
-  String? libraryId,
-}) {
-  return SyncAssetV2(
-    id: id,
-    checksum: checksum,
-    originalFileName: fileName,
-    type: AssetTypeEnum.IMAGE,
-    ownerId: ownerId,
-    isFavorite: false,
-    fileCreatedAt: DateTime(2024, 1, 1),
-    fileModifiedAt: DateTime(2024, 1, 1),
-    createdAt: DateTime(2024, 1, 1),
-    localDateTime: DateTime(2024, 1, 1),
-    visibility: AssetVisibility.timeline,
-    width: null,
-    height: null,
-    deletedAt: null,
-    duration: 0,
-    libraryId: libraryId,
+    libraryId: null,
     livePhotoVideoId: null,
     stackId: null,
     thumbhash: null,
@@ -272,82 +240,4 @@ void main() {
       expect(after.backupSelection, equals(BackupSelection.none));
     });
   });
-
-  group('SyncStreamRepository - updateAssetsV1 dedupe (#22522 #27186)', () {
-    test('replaces stale row when new id arrives with same (ownerId, checksum) and library is null', () async {
-      await sut.updateUsersV1([_createUser()]);
-      await sut.updateAssetsV1([_createAsset(id: 'old-id', checksum: 'AAA', fileName: 'photo.jpg')]);
-
-      // Server re-issues a new id for the same content (replace-with-upload, immich-go, etc.)
-      await sut.updateAssetsV1([_createAsset(id: 'new-id', checksum: 'AAA', fileName: 'photo.jpg')]);
-
-      final rows = await db.remoteAssetEntity.select().get();
-      expect(rows, hasLength(1));
-      expect(rows.single.id, equals('new-id'));
-      expect(rows.single.checksum, equals('AAA'));
-    });
-
-    test('replaces stale row by (ownerId, libraryId, checksum) when library is not null', () async {
-      await sut.updateUsersV1([_createUser()]);
-      await sut.updateAssetsV1([
-        _createAsset(id: 'old-id', checksum: 'AAA', fileName: 'photo.jpg', libraryId: 'lib-1'),
-      ]);
-
-      await sut.updateAssetsV1([
-        _createAsset(id: 'new-id', checksum: 'AAA', fileName: 'photo.jpg', libraryId: 'lib-1'),
-      ]);
-
-      final rows = await db.remoteAssetEntity.select().get();
-      expect(rows, hasLength(1));
-      expect(rows.single.id, equals('new-id'));
-      expect(rows.single.libraryId, equals('lib-1'));
-    });
-
-    test('library and non-library rows with same (ownerId, checksum) coexist', () async {
-      await sut.updateUsersV1([_createUser()]);
-      await sut.updateAssetsV1([
-        _createAsset(id: 'lib-row', checksum: 'AAA', fileName: 'photo.jpg', libraryId: 'lib-1'),
-        _createAsset(id: 'main-row', checksum: 'AAA', fileName: 'photo.jpg'),
-      ]);
-
-      final rows = await db.remoteAssetEntity.select().get();
-      expect(rows, hasLength(2), reason: 'library NULL and NOT NULL match different partial indexes');
-      expect(rows.map((r) => r.id).toSet(), equals({'lib-row', 'main-row'}));
-    });
-
-    test('different owners with same checksum coexist', () async {
-      await sut.updateUsersV1([_createUser(id: 'user-1')]);
-      await sut.updateUsersV1([_createUser(id: 'user-2')]);
-      await sut.updateAssetsV1([
-        _createAsset(id: 'a-id', checksum: 'AAA', fileName: 'photo.jpg', ownerId: 'user-1'),
-        _createAsset(id: 'b-id', checksum: 'AAA', fileName: 'photo.jpg', ownerId: 'user-2'),
-      ]);
-
-      final rows = await db.remoteAssetEntity.select().get();
-      expect(rows, hasLength(2));
-    });
-
-    test('same id arriving again updates in place (no self-delete)', () async {
-      await sut.updateUsersV1([_createUser()]);
-      await sut.updateAssetsV1([_createAsset(id: 'same-id', checksum: 'AAA', fileName: 'photo.jpg')]);
-
-      await sut.updateAssetsV1([_createAsset(id: 'same-id', checksum: 'AAA', fileName: 'renamed.jpg')]);
-
-      final rows = await db.remoteAssetEntity.select().get();
-      expect(rows, hasLength(1));
-      expect(rows.single.id, equals('same-id'));
-      expect(rows.single.name, equals('renamed.jpg'), reason: 'ON CONFLICT(id) DO UPDATE path still works');
-    });
-
-    test('updateAssetsV2 dedupes the same way', () async {
-      await sut.updateUsersV1([_createUser()]);
-      await sut.updateAssetsV2([_createAssetV2(id: 'old-id', checksum: 'AAA', fileName: 'photo.jpg')]);
-
-      await sut.updateAssetsV2([_createAssetV2(id: 'new-id', checksum: 'AAA', fileName: 'photo.jpg')]);
-
-      final rows = await db.remoteAssetEntity.select().get();
-      expect(rows, hasLength(1));
-      expect(rows.single.id, equals('new-id'));
-    });
-  });
 }

mobile/test/domain/services/local_sync_service_test.dart

@@ -131,7 +131,7 @@ void main() {
         durationMs: 0,
         orientation: 0,
         isFavorite: false,
-        playbackStyle: PlatformAssetPlaybackStyle.image
+        playbackStyle: PlatformAssetPlaybackStyle.image,
       );
 
       final assetsToRestore = [LocalAssetStub.image1];
@@ -215,7 +215,7 @@ void main() {
         isFavorite: false,
         createdAt: 1700000000,
         updatedAt: 1732000000,
-        playbackStyle: PlatformAssetPlaybackStyle.image
+        playbackStyle: PlatformAssetPlaybackStyle.image,
       );
 
       final localAsset = platformAsset.toLocalAsset();