UNIQUE violations on iOS

[aran]

Hi! Very cool project. I like the idea of using one storage API across flutter platforms.

I am probably doing something silly, but in trying this out I am seeing unexpected sqflite UNIQUE constraint violation errors in my onUpgradeNeeded implementation. I can upload a whole sample project if it would be helpful. In the sample project, I see the error in the XCode debug area but not in the Flutter Debug Console for some reason. In my real project, I get a bigger stack trace. In each case, I am using the latest versions from pub.dev.

Example error:

Unknown error calling sqlite3_step (2067: UNIQUE constraint failed: database.name) rs

Another example error (from main project, not the code extracted below)

════════ Exception caught by main zone ═════════════════════════════════════════
DatabaseException(Error Domain=SqfliteDarwinDatabase Code=2067 "UNIQUE constraint failed: __stores.name" UserInfo={NSLocalizedDescription=UNIQUE constraint failed: __stores.name}) sql 'INSERT INTO __stores (name, meta) VALUES (?, ?)' args [users, {"name":"users"}]

Another one also from main project, with a couple details elided:

════════ Exception caught by main zone ═════════════════════════════════════════
The following SqfliteDatabaseException was thrown:
DatabaseException(Error Domain=SqfliteDarwinDatabase Code=2067 "UNIQUE constraint failed: __stores.name" UserInfo={NSLocalizedDescription=UNIQUE constraint failed: __stores.name}) sql 'INSERT INTO __stores (name, meta) VALUES (?, ?)' args [documents, {"name":"documents"}]

When the exception was thrown, this was the stack:
#0      wrapDatabaseException (package:sqflite_platform_interface/src/platform_exception.dart:12:7)
platform_exception.dart:12
<asynchronous suspension>
#1      SqfliteDatabaseMixin.txnApplyBatch.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:726:11)
database_mixin.dart:726
<asynchronous suspension>
#2      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:485:16)
<asynchronous suspension>
#3      SqfliteTransactionWrapper.run (package:idb_sqflite/src/sqflite_transaction_wrapper.dart:142:14)
sqflite_transaction_wrapper.dart:142
<asynchronous suspension>
#4      IdbObjectStoreSqflite.create (package:idb_sqflite/src/sqflite_object_store.dart:115:5)
sqflite_object_store.dart:115
<asynchronous suspension>
#5      IdbDatabaseSqflite.applySchemaChanges.createObjectStores (package:idb_sqflite/src/sqflite_database.dart:133:9)
sqflite_database.dart:133
<asynchronous suspension>
#6      IdbDatabaseSqflite.applySchemaChanges (package:idb_sqflite/src/sqflite_database.dart:156:5)
sqflite_database.dart:156
<asynchronous suspension>
#7      IdbObjectStoreSqflite.checkStore (package:idb_sqflite/src/sqflite_object_store.dart:155:7)
sqflite_object_store.dart:155
2
<asynchronous suspension>
#9      MyDatabaseService.onUpgradeNeeded (package:myproj/storage/databases/my_databases.dart:189:7)
my_databases.dart:189
<asynchronous suspension>
#10     IdbDatabaseSqflite._upgrade (package:idb_sqflite/src/sqflite_database.dart:183:7)
sqflite_database.dart:183
<asynchronous suspension>
#11     IdbDatabaseSqflite.open.checkVersion.<anonymous closure> (package:idb_sqflite/src/sqflite_database.dart:296:17)
sqflite_database.dart:296
<asynchronous suspension>
#12     IdbDatabaseMeta.onUpgradeNeeded (package:idb_shim/src/common/common_meta.dart:102:9)
common_meta.dart:102
<asynchronous suspension>
#13     IdbDatabaseSqflite.open.checkVersion (package:idb_sqflite/src/sqflite_database.dart:292:13)
sqflite_database.dart:292
<asynchronous suspension>
#14     IdbDatabaseSqflite.open.<anonymous closure> (package:idb_sqflite/src/sqflite_database.dart:317:9)
sqflite_database.dart:317
<asynchronous suspension>
#15     SqfliteTransactionWrapper.run (package:idb_sqflite/src/sqflite_transaction_wrapper.dart:142:14)
sqflite_transaction_wrapper.dart:142
<asynchronous suspension>
#16     IdbDatabaseSqflite.open (package:idb_sqflite/src/sqflite_database.dart:316:7)
sqflite_database.dart:316
<asynchronous suspension>
#17     IdbFactorySqflite.open (package:idb_sqflite/src/sqflite_factory.dart:46:7)
sqflite_factory.dart:46
<asynchronous suspension>

The below code is extracted from my project where I first saw the bug.

sqflite_io.dart:

import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:sqflite/sqflite.dart' as sqflite show databaseFactory;

Future<String> buildDatabasesPath(String pathHint) async {
  Directory baseDir = await getApplicationDocumentsDirectory();

  var dir = Directory(join(baseDir.path, pathHint, 'databases'));
  if (!dir.existsSync()) {
    dir.createSync(recursive: true);
  }
  return dir.path;
}

Future<DatabaseFactory> getDatabaseFactory({required String pathHint}) async {
  DatabaseFactory? result;
  if (Platform.isIOS || Platform.isAndroid || Platform.isMacOS) {
    result = sqflite.databaseFactory;
  } else if (Platform.isLinux ||
      Platform.isWindows ||
      Platform.environment['FLUTTER_TEST'] == 'true') {
    result = databaseFactoryFfi;
  }

  if (result != null) {
    await result.setDatabasesPath(await buildDatabasesPath(pathHint));
    return result;
  } else {
    throw UnsupportedError('Unsupported platform');
  }
}

idb_io.dart:

import 'package:idb_sqflite/idb_sqflite.dart';
import 'package:idb_repro/storage/sqflite/sqflite.dart' as sqflite;

Future<IdbFactory> getIdbFactory({required String pathHint}) async {
  return getIdbFactorySqflite(
      await sqflite.getDatabaseFactory(pathHint: pathHint));
}

"database_service.dart"

import 'dart:async';

import 'package:flutter/widgets.dart';
import 'package:idb_repro/storage/idb/idb.dart';
import 'package:idb_sqflite/idb_sqflite.dart';

abstract class DatabaseService<DBID> {
  IdbFactory get idbFactory;

  Future<Database> open(DBID dbid) async => await idbFactory.open(dbName(dbid),
      version: version, onUpgradeNeeded: onUpgradeNeeded, onBlocked: onBlocked);

  void onBlocked(Event event) {}
  FutureOr<void> onUpgradeNeeded(VersionChangeEvent event);
  String dbName(DBID dbid);
  int get version;
}

Future<IdbFactory>? _idbFactory;

Future<IdbFactory> globalIdbFactory() {
  WidgetsFlutterBinding.ensureInitialized();
  _idbFactory ??= getIdbFactory(pathHint: "idb_repro");
  return _idbFactory!;
}

const String kUsersStore = 'users';

class ExampleDatabaseService extends DatabaseService<String> {
  @override
  final IdbFactory idbFactory;

  ExampleDatabaseService({required this.idbFactory});

  @override
  String dbName(String dbid) => 'user:{dbid}';

  @override
  int get version => 2;

  @override
  FutureOr<void> onUpgradeNeeded(VersionChangeEvent event) async {
    final db = event.database;
    final existing = Set.from(db.objectStoreNames);
    if (event.oldVersion < 1) {
      // I have seen cases where event.oldVersion is 0 but when we run this code, the db already has the store and version set to the `version` param.
      if (existing.contains(kUsersStore)) {
        print("Unexpectedly found $kUsersStore");
      } else {
        db.createObjectStore(kUsersStore);
      }
    }

   // I've seen the error occur here as well:

    // if (event.oldVersion < 2) {
    //   final futures = <Future<void>>[];
    //   for (var storeName in [kUsersStore]) {
    //     futures.add(event.transaction.objectStore(storeName).clear());
    //   }
//
// in checkStore from this await:
    //   await Future.wait(futures);
    // }
  }
}

example code:

  final dbFactory = await globalIdbFactory();
  final dbService = ExampleDatabaseService(idbFactory: dbFactory);
  final db1 = await dbService.open('user1');