Make Dart placeholders easier to use with table aliases

[CDDelta]

I have this query below but when I run it on web I get the error Error: no such column: file_entries.name:

import '../tables/file_entries.moor';
import '../tables/file_revisions.moor';
import '../tables/network_transactions.moor';

filesInFolderWithRevisionTransactions ($order = '') AS FileWithLatestRevisionTransactions:
    SELECT file.*, metadataTx.**, dataTx.** FROM file_entries AS file
    JOIN network_transactions AS metadataTx ON metadataTx.id = (
        SELECT metadataTxId FROM file_revisions AS rev
        WHERE driveId = :driveId AND fileId = file.id
        ORDER BY rev.dateCreated DESC
        LIMIT 1)
    JOIN network_transactions AS dataTx ON dataTx.id = (
        SELECT dataTxId FROM file_revisions AS rev
        WHERE driveId = :driveId AND fileId = file.id
        ORDER BY rev.dateCreated DESC
        LIMIT 1)
    WHERE driveId = :driveId AND parentFolderId = :parentFolderId
    ORDER BY $order;

The problem is the file_entries table does have a name column. I would expect this query to work as build_runner runs without problems.

Any ideas about what's happening here? This seems like a bug to me. Does this possibly have to do with the fact that name is a reserved keyword in some variants of SQL?

I have a PR here that shows this issue (though it is a Flutter profile build so the error shows up in the console differently) if you want more context on how the schema looks like. I can look into creating a repro case if needed. 😃

[simolus3]

You mention that it doesn't work on the web, did you try other platforms too? FFI is much easier for me to debug, so I'll try to reproduce this with that platform unless you know it already works with FFI :D

I didn't take a look at your table definitions yet, but did you add file_entries.name later on without writing a migration?

[CDDelta]

Hi @simolus3, I can confirm that this problem persists with FFI.

This is what I get.

Unhandled Exception: SqliteException(1): no such column: file_entries.name, SQL logic error (code 1)

file_entries.name wasn't added in a migration. Other queries using SELECT * FROM file_entries return the name correctly, something must be going on with how complex this query is.

Let me know if I can help in any way :)

[simolus3]

Thanks for confirming that it also happens with the ffi-based implementation. And sorry for the back and forth on this, but I think I need more information to reproduce this. The following doesn't seem to be enough (on the file-status-indicator branch):

import 'package:ardrive/models/database/database.dart';
import 'package:moor/ffi.dart';

Future<void> main() async {
  final db = Database(VmDatabase.memory(logStatements: true));
  await db.driveDao.filesInFolderWithRevisionTransactions(
      driveId: 'foo', parentFolderId: 'bar');
}

I probably need to insert something to hit this. If you could write some code that triggers the exception I can take a look at what exactly is going wrong here.

Maybe enabling logStatements: true on your database could help too. I'm a bit surprised about this error since your query never even references file_entries.name, not even implicitly with **-expanding.

[Mike278]

Possibly related to #616?

[CDDelta]

Possibly related to #616?

Doesn't seem to be the case but thank you :)

I can look into creating a repro case if needed

I have set up a repro case. Just replace main.dart with the below.

It looks like it's the ORDER BY portion of the query that is causing problems. If no order is specified, the query returns successfully. I don't believe I have the OrderingTerm specified wrongly.

import 'package:flutter/material.dart';
import 'package:moor/moor.dart';

import 'models/models.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();

  final db = Database();

  await db.into(db.fileEntries).insertOnConflictUpdate(
        FileEntriesCompanion.insert(
            id: 'file',
            driveId: 'drive',
            name: 'test.png',
            parentFolderId: 'parentFolder',
            path: '/test.png',
            size: 3,
            lastModifiedDate: DateTime.now(),
            dataTxId: 'dataTx'),
      );

  await db.into(db.fileRevisions).insertOnConflictUpdate(
      FileRevisionsCompanion.insert(
          fileId: 'file',
          driveId: 'drive',
          name: 'test.png',
          parentFolderId: 'parentFolder',
          size: 3,
          lastModifiedDate: DateTime.now(),
          metadataTxId: 'metaTx',
          dataTxId: 'dataTx',
          action: RevisionAction.create));

  await db.into(db.fileRevisions).insertOnConflictUpdate(
      FileRevisionsCompanion.insert(
          fileId: 'file',
          driveId: 'drive',
          name: 'test',
          parentFolderId: 'parentFolder',
          size: 3,
          lastModifiedDate: DateTime.now(),
          metadataTxId: 'metaTx',
          dataTxId: 'dataTx',
          action: RevisionAction.create));

  await db.into(db.networkTransactions).insertOnConflictUpdate(
      NetworkTransactionsCompanion.insert(id: 'metaTx'));

  await db.into(db.networkTransactions).insertOnConflictUpdate(
      NetworkTransactionsCompanion.insert(id: 'dataTx'));

  final noOrderSuccess = await db.driveDao
      .filesInFolderAtPathWithRevisionTransactions(
        driveId: 'drive',
        path: '',
      )
      .get();

  final orderFailsA = await db.driveDao
      .filesInFolderWithRevisionTransactions(
          driveId: 'drive',
          parentFolderId: 'parentFolder',
          order: OrderBy([
            OrderingTerm(
                expression: db.fileEntries.name, mode: OrderingMode.asc)
          ]))
      .get();

  final orderFailsB = await db.driveDao
      .filesInFolderAtPathWithRevisionTransactions(
          driveId: 'drive',
          path: '',
          order: OrderBy([
            OrderingTerm(
                expression: db.fileEntries.lastUpdated, mode: OrderingMode.asc)
          ]))
      .get();

  print('success');
}

I'm a bit surprised about this error since your query never even references file_entries.name

Maybe I'm not understanding this correctly but I believe it does when I do * with file right?

SELECT file.*, metadataTx.**, dataTx.** FROM file_entries AS file

Either way, it seems like the main point of concern is with the OrderingTerm. :)

[Mike278]

OrderingTerm(expression: db.fileEntries.name, mode: OrderingMode.asc) probably generates ORDER BY file_entries.name ASC, but the query aliases it with FROM file_entries AS file

create table if not exists a (value int);
select * from a as b order by a.value;

gives "SQL error or missing database (no such column: a.value)"

[simolus3]

Thank you @CDDelta for finding a small repro and thanks Mike for finding the bug!

Indeed, this workaround fixes the problem:

final entries = db.alias(db.fileEntries, 'file');

final orderFailsA = await db.driveDao
    .filesInFolderWithRevisionTransactions(
        driveId: 'drive',
        parentFolderId: 'parentFolder',
        order: OrderBy(
            [OrderingTerm(expression: entries.name, mode: OrderingMode.asc)]))
    .get();

final orderFailsB = await db.driveDao
    .filesInFolderAtPathWithRevisionTransactions(
        driveId: 'drive',
        path: '',
        order: OrderBy([
          OrderingTerm(
              expression: entries.lastUpdated, mode: OrderingMode.asc)
        ]))
    .get();

Of course, this gets rather annoying to use all the time. Unfortunately this is a design error I overlooked when adding Dart placeholders to moor files, it won't be easy to fix this now. What I can imagine is some kind of api that passes the right table names to a function. So instead of

Selectable<FileWithLatestRevisionTransactions>
    filesInFolderAtPathWithRevisionTransactions(
        {@required String driveId,
        @required String path,
        OrderBy order = const OrderBy.nothing()}) {

we might generate

Selectable<FileWithLatestRevisionTransactions>
    filesInFolderAtPathWithRevisionTransactions(
        {required String driveId,
        required String path,
        OrderBy Function(FileEntries file, MetadataTx metadataTx, DataTx dataTx)? order}) {

The generated code would then pass the right aliases to the order function. Obviously this would have to be an opt-in build option.

[CDDelta]

Thank you @Mike278 and @simolus3 for helping with this. I got the query working now 😄

What I can imagine is some kind of api that passes the right table names to a function.

This proposal sounds good to me, thank you for your awesome work here!

[simolus3]

I forgot to update this, but this feature is supported through the scoped_dart_components build option.