Add support for Row Values aka Tuple

[glanium]

Is your feature request related to a problem? Please describe.

I need to build SQL In clause like the following

WHERE (FieldA, FieldB) IN (VALUES(?,?), (?,?), (?,?) .........(?,?))

This is called Row Values.

This is possible now?? Or Is it easy way to achive this? If not, please support it.

thx.

[simolus3]

At the moment, this is not supported. We might be able to do write a nice API for this using records (at least for small tuples).

[simolus3]

In b3ea00b8af17622407308bd1b8beb3bb118e3948, I've added support for constructing in expressions based on other expressions (instead of direct values).

With that commit, we can add support for row value expressions with something like

class _RowValue extends Expression<Never> {
  final List<Expression> values;

  _RowValue(this.values);

  @override
  Precedence get precedence => Precedence.primary;

  @override
  void writeInto(GenerationContext context) {
    context.buffer.write('(');
    var first = true;
    for (final value in values) {
      if (!first) {
        context.buffer.write(', ');
      }

      value.writeInto(context);
      first = false;
    }
    context.buffer.write(')');
  }
}

extension RowValue2<T1 extends Object, T2 extends Object> on (
  Expression<T1>,
  Expression<T2>
) {
  Expression<bool> isIn(List<(T1?, T2?)> values) {
    final (a, b) = this;

    return _RowValue([a, b]).isInExp([
      for (final (a, b) in values) _RowValue([Variable<T1>(a), Variable<T2>(b)])
    ]);
  }
}

It generates the correct result, but I'm still pondering whether this should be added to drift. I'm mainly concerned about a lot of duplicate code necessary since we can't generalize the extension over different record lengths.

[glanium]

thx.

I looked at your code. In sqlite row values In clause, "VALUES" keyword seems required before actual row values. WHERE (FieldA, FieldB) IN (VALUES(?,?), (?,?), (?,?) .........(?,?)) I got error without "VALUES" keywords so sadly special handling might be required

-- added Oops. I tried latest sqlite (3.42.0). Now it works without "VALUES" keywords now