queryTests method

dynamic queryTests()

Implementation

queryTests() {
  group('Query', () {
    late Cursor cursor;

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

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

    group('with single filters', () {
      group('equality', () {
        test('find all matching', () async {
          await insertRow(connection, "one", 1);
          await insertRow(connection, "two", 2);
          await insertRow(connection, "three", 3);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.equals,
                right: StringValue("two")),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 1);
          expect(res[0]["number"], 2);
        });

        test('returns nothing if no matches', () async {
          await insertRow(connection, "one", 1);
          await insertRow(connection, "two", 2);
          await insertRow(connection, "three", 3);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.equals,
                right: StringValue("four")),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 0);
        });
      });

      group('inequality', () {
        test('find all matching', () async {
          await insertRow(connection, "one", 1);
          await insertRow(connection, "two", 2);
          await insertRow(connection, "three", 3);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.notEquals,
                right: StringValue("two")),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();
          expect(res.length, 2);
          expect(res[0]["number"], 1);
          expect(res[1]["number"], 3);
        });

        test('returns nothing if no matches', () async {
          await insertRow(connection, "one", 1);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.notEquals,
                right: StringValue("one")),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 0);
        });
      });

      group('comparison', () {
        test('find all matching with strings', () async {
          await insertRow(connection, "a", 1);
          await insertRow(connection, "b", 2);
          await insertRow(connection, "c", 3);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.greaterThan,
                right: StringValue("b")),
          );

          await cursor.execute(op);
          var res = await cursor.fetchall();

          expect(res.length, 1);
          expect(res[0]["number"], 3);

          op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.greaterThanOrEqual,
                right: StringValue("b")),
          );
          await cursor.execute(op);
          res = await cursor.fetchall();
          expect(res.length, 2);
          expect(res[0]["number"], 2);
          expect(res[1]["number"], 3);

          op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.lessThan,
                right: StringValue("b")),
          );
          await cursor.execute(op);
          res = await cursor.fetchall();

          expect(res.length, 1);
          expect(res[0]["number"], 1);

          op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.lessThanOrEqual,
                right: StringValue("b")),
          );
          await cursor.execute(op);
          res = await cursor.fetchall();

          expect(res.length, 2);
          expect(res[0]["number"], 1);
          expect(res[1]["number"], 2);
        });

        test('find all matching with strings', () async {
          await insertRow(connection, "a", 1);
          await insertRow(connection, "b", 2);
          await insertRow(connection, "c", 3);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.greaterThan,
                right: StringValue("b")),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 1);
          expect(res[0]["number"], 3);
        });

        test('returns nothing if no matches', () async {
          await insertRow(connection, "one", 1);
          await insertRow(connection, "two", 2);
          await insertRow(connection, "three", 3);

          var op = Query(
            from: schema,
            where: SingleFilter(
                left: Property("string"),
                operator: Operator.equals,
                right: StringValue("four")),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 0);
        });
      });
    });

    group('with negated filters', () {
      test("it works with single negation", () async {
        await insertRow(connection, "a", 1);
        await insertRow(connection, "b", 2);
        await insertRow(connection, "c", 3);

        var op = Query(
          from: schema,
          where: NotCondition(
              not: SingleFilter(
            left: Property("string"),
            operator: Operator.equals,
            right: StringValue("b"),
          )),
        );

        await cursor.execute(op);
        final res = await cursor.fetchall();

        expect(res.length, 2, reason: res.toString());
        expect(res[0]["number"], 1);
        expect(res[1]["number"], 3);
      });

      test("it works with double negation", () async {
        await insertRow(connection, "a", 1);
        await insertRow(connection, "b", 2);
        await insertRow(connection, "c", 3);

        var op = Query(
          from: schema,
          where: NotCondition(
            not: NotCondition(
              not: SingleFilter(
                left: Property("string"),
                operator: Operator.equals,
                right: StringValue("b"),
              ),
            ),
          ),
        );

        await cursor.execute(op);
        final res = await cursor.fetchall();

        expect(res.length, 1);
        expect(res[0]["number"], 2);
      });
    });

    group(
      'with combined filters',
      () {
        test('basic conjunction', () async {
          await insertRow(connection, "a", 1);
          await insertRow(connection, "b", 2);
          await insertRow(connection, "c", 3);

          var op = Query(
            from: schema,
            where: CombinedFilter(
              combinator: Combinator.and,
              conditions: [
                SingleFilter(
                  left: Property("string"),
                  operator: Operator.greaterThan,
                  right: StringValue("b"),
                ),
                SingleFilter(
                  left: Property("number"),
                  operator: Operator.lessThan,
                  right: NumericValue(4),
                ),
              ],
            ),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 1);
          expect(res[0]["number"], 3);
        });

        test('basic disjunction', () async {
          await insertRow(connection, "a", 1);
          await insertRow(connection, "b", 2);
          await insertRow(connection, "c", 3);

          var op = Query(
            from: schema,
            where: CombinedFilter(
              combinator: Combinator.or,
              conditions: [
                SingleFilter(
                  left: Property("string"),
                  operator: Operator.equals,
                  right: StringValue("b"),
                ),
                SingleFilter(
                  left: Property("number"),
                  operator: Operator.equals,
                  right: NumericValue(3),
                ),
              ],
            ),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 2);
          expect(res[0]["number"], 2);
          expect(res[1]["number"], 3);
        });

        test('basic disjunction with duplicates', () async {
          await insertRow(connection, "a", 1);
          await insertRow(connection, "b", 2);
          await insertRow(connection, "c", 3);
          await insertRow(connection, "d", 4);
          await insertRow(connection, "e", 5);
          await insertRow(connection, "f", 6);

          var op = Query(
            from: schema,
            where: CombinedFilter(
              combinator: Combinator.or,
              conditions: [
                SingleFilter(
                  left: Property("string"),
                  operator: Operator.greaterThan,
                  right: StringValue("c"),
                ),
                SingleFilter(
                  left: Property("number"),
                  operator: Operator.greaterThan,
                  right: NumericValue(3),
                ),
              ],
            ),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();

          expect(res.length, 3);
          expect(res.map((res) => res["number"]), unorderedEquals([4, 5, 6]));
        });

        test('complex combined filters', () async {
          await insertRow(connection, "a", 1);
          await insertRow(connection, "b", 2);
          await insertRow(connection, "c", 3);
          await insertRow(connection, "d", 4);
          await insertRow(connection, "e", 5);
          await insertRow(connection, "f", 6);

          var op = Query(
            from: schema,
            where: CombinedFilter(
              combinator: Combinator.and,
              conditions: [
                // number > 1 and !number >= 5 -> n > 1 and n < 5
                // exclude 1, 5 and 6
                CombinedFilter(
                  combinator: Combinator.and,
                  conditions: [
                    SingleFilter(
                      left: Property("number"),
                      operator: Operator.greaterThan,
                      right: NumericValue(1),
                    ),
                    NotCondition(
                      not: SingleFilter(
                        left: Property("number"),
                        operator: Operator.greaterThanOrEqual,
                        right: NumericValue(5),
                      ),
                    ),
                  ],
                ),
                // !(number = 2 or number = 3) -> number != b and number != 3
                // exclude 2 and 3
                NotCondition(
                  not: CombinedFilter(
                    combinator: Combinator.or,
                    conditions: [
                      SingleFilter(
                        left: Property("number"),
                        operator: Operator.equals,
                        right: NumericValue(2),
                      ),
                      SingleFilter(
                        left: Property("number"),
                        operator: Operator.equals,
                        right: NumericValue(3),
                      ),
                    ],
                  ),
                ),
              ],
            ),
          );

          await cursor.execute(op);
          final res = await cursor.fetchall();
          expect(res.length, 1);
          expect(res[0]["number"], 4);
        });
      },
    );

    group("with ordering", () {
      test('orders by one property', () async {
        await insertRow(connection, "a", 1);
        await insertRow(connection, "f", 6);
        await insertRow(connection, "c", 3);
        await insertRow(connection, "b", 2);
        await insertRow(connection, "e", 5);
        await insertRow(connection, "d", 4);

        var op = Query(
            from: schema,
            orderBy: [OrderBy("string", direction: OrderByDirection.desc)]);
        await cursor.execute(op);
        var res = await cursor.fetchall();

        expect(
          res.map((e) => e["string"]),
          orderedEquals(["f", "e", "d", "c", "b", "a"]),
        );
      });

      test('orders by multiple property', () async {
        await insertRow(connection, "a", 6);
        await insertRow(connection, "a", 5);
        await insertRow(connection, "b", 4);
        await insertRow(connection, "b", 3);
        await insertRow(connection, "c", 2);
        await insertRow(connection, "c", 1);

        var op = Query(from: schema, orderBy: [
          OrderBy("string", direction: OrderByDirection.asc),
          OrderBy("number", direction: OrderByDirection.asc),
        ]);
        await cursor.execute(op);
        var res = await cursor.fetchall();

        expect(
          res.map((e) => e["number"]),
          orderedEquals([5, 6, 3, 4, 1, 2]),
        );
      });
    }, skip: requireFeature(DatabaseFeature.orderBy));

    group("with limit", () {
      test('with no filter', () async {
        await insertRow(connection, "one", 1);
        await insertRow(connection, "two", 2);
        await insertRow(connection, "three", 3);

        var op = Query(
          from: schema,
          limit: 2,
        );

        await cursor.execute(op);
        final res = await cursor.fetchall();

        expect(res.length, 2);
      });

      test('with filter', () async {
        await insertRow(connection, "one", 1);
        await insertRow(connection, "two", 2);
        await insertRow(connection, "three", 3);
        await insertRow(connection, "four", 4);

        var op = Query(
          from: schema,
          where: SingleFilter(
            left: Property("number"),
            operator: Operator.greaterThan,
            right: NumericValue(1),
          ),
          limit: 2,
        );

        await cursor.execute(op);
        final res = await cursor.fetchall();

        expect(res.length, 2);
      });

      test('with order by one property', () async {
        await insertRow(connection, "a", 1);
        await insertRow(connection, "f", 6);
        await insertRow(connection, "c", 3);
        await insertRow(connection, "b", 2);
        await insertRow(connection, "e", 5);
        await insertRow(connection, "d", 4);

        var op = Query(
          from: schema,
          orderBy: [OrderBy("string", direction: OrderByDirection.desc)],
          limit: 2,
        );
        await cursor.execute(op);
        var res = await cursor.fetchall();

        expect(
          res.map((e) => e["string"]),
          orderedEquals(["f", "e"]),
        );
      }, skip: requireFeature(DatabaseFeature.orderBy));

      test('with order by multiple property', () async {
        await insertRow(connection, "a", 6);
        await insertRow(connection, "a", 5);
        await insertRow(connection, "b", 4);
        await insertRow(connection, "b", 3);
        await insertRow(connection, "c", 2);
        await insertRow(connection, "c", 1);

        var op = Query(
          from: schema,
          orderBy: [
            OrderBy("string", direction: OrderByDirection.asc),
            OrderBy("number", direction: OrderByDirection.asc),
          ],
          limit: 3,
        );
        await cursor.execute(op);
        var res = await cursor.fetchall();

        expect(
          res.map((e) => e["number"]),
          orderedEquals([5, 6, 3]),
        );
      }, skip: requireFeature(DatabaseFeature.orderBy));
    });
  });
}