upsertTests method

void upsertTests()

Implementation

void upsertTests() {
  group('Upsert', () {
    group('basic funtionality', () {
      final collection = schema;
      late Cursor cursor;

      setUp(() async {
        connection = await connector.connect(_initDb);
        cursor = await connection.cursor();
      });

      tearDown(() async {
        await connection.close();
      });

      test('with no values', () async {
        final obj = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };
        await cursor.execute(Insert(collection, [obj]));

        await cursor.execute(Upsert(
          collection,
          [],
        ));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();
        expect(fromDb[0], obj);
      });

      test('add single value', () async {
        final obj = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final operation = Upsert(
          collection,
          [obj],
        );
        await cursor.execute(operation);

        await cursor.execute(Query(from: collection));
        // Note: assuming autocommit = true
        final fromDb = await cursor.fetchall();

        expect(fromDb[0], equals(obj));
      });

      test('update single value', () async {
        final obj = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };
        await cursor.execute(Insert(collection, [obj]));

        obj["name"] = "Susannah Dean";
        await cursor.execute(Upsert(
          collection,
          [obj],
        ));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();
        expect(fromDb[0]["name"], "Susannah Dean");
      });

      test('add multiple values', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final obj2 = {
          "id": "detta",
          "name": "Susannah Dean",
          "age": 54,
        };

        final operation = Upsert(
          collection,
          [obj1, obj2],
        );
        await cursor.execute(operation);

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();

        expect(fromDb, unorderedEquals([obj1, obj2]));
      });

      test('update multiple values', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };
        final obj2 = {
          "id": "detta",
          "name": "Susannah Dean",
          "age": 54,
        };
        await cursor.execute(Insert(collection, [obj1, obj2]));

        obj1["name"] = "one";
        obj2["name"] = "two";
        await cursor.execute(Upsert(
          collection,
          [obj1, obj2],
        ));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();
        expect(fromDb.map((e) => e["name"]), unorderedEquals(["one", "two"]));
      });

      test('mix add and update', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };
        final obj2 = {
          "id": "detta",
          "name": "two",
          "age": 54,
        };
        await cursor.execute(Insert(collection, [obj1]));

        obj1["name"] = "one";
        await cursor.execute(Upsert(
          collection,
          [obj1, obj2],
        ));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();
        expect(fromDb.map((e) => e["name"]), unorderedEquals(["one", "two"]));
      });
    });

    group('table with pk only', () {
      final collection = schemaIdOnly;
      late Cursor cursor;

      setUp(() async {
        connection = await connector.connect(_initDbPKOnly);
        cursor = await connection.cursor();
      });

      tearDown(() async {
        await connection.close();
      });

      test('insert single value', () async {
        final obj = {
          "id": 123,
        };

        final operation = Insert(collection, [obj]);
        await cursor.execute(operation);

        await cursor.execute(Query(from: collection));
        // Note: assuming autocommit = true
        final fromDb = await cursor.fetchall();

        expect(fromDb[0], equals(obj));
      });
    });

    group('collection with constraints on single property', () {
      final collection = schemaUnique;
      late Cursor cursor;

      setUp(() async {
        connection = await connector.connect(_initDbUnique);
        cursor = await connection.cursor();
      });

      tearDown(() async {
        await connection.close();
      });

      test('adds without conflicts', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final obj2 = {
          "id": "detta",
          "name": "Susannah Dean",
          "age": 54,
        };

        final operation = Upsert(
          collection,
          [obj1, obj2],
        );
        await cursor.execute(operation);

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();

        expect(fromDb, unorderedEquals([obj1, obj2]));
      });

      test('throws if conflict on unique constraint other than pk', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final operation = Insert(collection, [obj1]);
        await cursor.execute(operation);

        final obj2 = {
          "id": "detta",
          "name": "Roland Deschain",
          "age": 54,
        };

        final conflictingOperation = Upsert(
          collection,
          [obj2],
        );
        final execFuture = cursor.execute(conflictingOperation);
        expectLater(execFuture, throwsA(isA<IntegrityError>()));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();
        expect(fromDb, equals([obj1]));
      });

      test('updates if conflict on pk', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final obj2 = {
          "id": "gunslinger",
          "name": "Susannah Dean",
          "age": 54,
        };

        final operation = Insert(collection, [obj1]);
        await cursor.execute(operation);

        final conflictingOperation = Upsert(
          collection,
          [obj2],
        );
        await cursor.execute(conflictingOperation);

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();
        expect(fromDb, equals([obj2]));
      });

      test('bulk upsert with conflicts on non-pk field throws', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final obj2 = {
          "id": "detta",
          "name": "Roland Deschain",
          "age": 54,
        };

        final operation = Upsert(
          collection,
          [obj1, obj2],
        );
        final failing = cursor.execute(operation);
        expect(failing, throwsA(isA<IntegrityError>()));
      });

      test('bulk upsert with conflicts on pk accepts last object', () async {
        final obj1 = {
          "id": "gunslinger",
          "name": "Roland Deschain",
          "age": 999,
        };

        final obj2 = {
          "id": "gunslinger",
          "name": "Susannah Dean",
          "age": 54,
        };

        final operation = Upsert(
          collection,
          [obj1, obj2],
        );
        await cursor.execute(operation);

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();

        expect(fromDb, equals([obj2]));
      });
    }, skip: requireFeature(DatabaseFeature.uniqueProperty));

    group('compound primary key', () {
      final collection = schemaMultiPk;
      late Cursor cursor;

      setUp(() async {
        connection = await connector.connect(_initDbMultiPK);
        cursor = await connection.cursor();
      });

      tearDown(() async {
        await connection.close();
      });

      test('adds without conflicts', () async {
        var obj1 = {
          "first_name": "Roland",
          "last_name": "Deschain",
          "age": 99,
        };

        var obj2 = {
          "first_name": "Susannah",
          "last_name": "Dean",
          "age": 36,
        };

        await cursor.execute(Insert(collection, [obj1, obj2]));

        var obj3 = {
          "first_name": "Roland",
          "last_name": "Dean",
          "age": 45,
        };

        await cursor.execute(Upsert(collection, [obj3]));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();

        expect(fromDb, unorderedEquals([obj1, obj2, obj3]));
      });

      test('single upsert with conflicts updates', () async {
        var obj1 = {
          "first_name": "Roland",
          "last_name": "Deschain",
          "age": 99,
        };

        var obj2 = {
          "first_name": "Roland",
          "last_name": "Deschain",
          "age": 100,
        };

        final operation = Insert(collection, [obj1]);
        await cursor.execute(operation);

        await cursor.execute(Upsert(collection, [obj2]));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();

        expect(fromDb[0]["age"], equals(100));
        expect(fromDb[0]["age"], equals(100));
      });

      test('bulk upsert with conflicts', () async {
        var obj1 = {
          "first_name": "Roland",
          "last_name": "Deschain",
          "age": 99,
        };

        var obj2 = {
          "first_name": "Roland",
          "last_name": "Deschain",
          "age": 100,
        };

        await cursor.execute(Upsert(collection, [
          obj1,
          obj2,
        ]));

        await cursor.execute(Query(from: collection));
        final fromDb = await cursor.fetchall();

        expect(fromDb, hasLength(1));
        expect(fromDb[0]["age"], equals(100));
      });
    }, skip: requireFeature(DatabaseFeature.compoundPK));
  }, skip: requireFeature(DatabaseFeature.upsert));
}