Query from moor file is unable to find a table named like SQL keyword

[Dev-Owl]

We have a table called Group (SQLite keyword, I know it's not great, but I have to reuse an existing db) the table is created via a dart file as shown below:

import 'package:moor/moor.dart';
import 'moor/utcDateConverter.dart';

class Group extends Table {
  String get tableName => "Group";
  IntColumn get id => integer().named("ID")();
  TextColumn get name => text().named("Name").nullable()();
  IntColumn get subDomainId => integer().named("SubDomainID").nullable()();
  IntColumn get lastChange => integer().named("LastChange").map(new DateTimeConverter()).nullable()();
  BoolColumn get markDeleted => boolean().named("MarkDeleted").nullable()();
  IntColumn get exported => integer().named("Exported").nullable()();

  @override
    Set<Column> get primaryKey => {id};
}

This dart file is included in a moor file that tries to use the table, the query uses "Group" as shown below. During code generation we get a warning like:

[WARNING] moor_generator:moor_generator on lib/database/dbContext.dart:
line 609, column 25: Could not find Group. Available are: sqlite_master, sqlite_sequence, `Group`, TaskList, TaskConsumable, SubDomain, Storage, StockHistoryReason, StockHistory, SoftwareModule, MaintenanceCategory, Log_Category, LogMessage, LastChangeDate, ItemUnit, ItemSupplier, ItemSubfolder, ItemStorage, ItemShortcut, ItemCurrency, Item, HourCounterLog, HourCounterGroup, HourCounter, ExpressionTemplate, EquipmentType, ERLog_StartedRound, ERLog_RoundEntry, ERLog_RoundDefRevPoint, ERLog_RoundDefRev, ERLog_RoundDef, ERLog_Expression, ERLog_EntryPointHistory, ERLog_EntryPoint, ERLog_Entry, ERLog_Domain, ERLog_Category, Domain, DocumentSubfolder, DocumentModified, DocumentLink, DocumentFile, CrewLogin, CrewGuestDatabasePositionsERLog_Domain, CrewAndGuestDatabaseUserRole, CrewAndGuestDatabasePosition, CrewAndGuestDatabaseDepartment, ComponentsToEquipmentType, ComponentsMaintenanceInterval, ComponentsMaintenanceEvent, ComponentSubfolder, ComponentIntervalConsumable, ComponentHistory, Component, Company, ExpressionWarningMessage, sqlite_master, sqlite_sequence
      
609 │         LEFT OUTER JOIN "Group" ON Component.GroupID = "Group".ID
                               ^^^^^    

As you can see it did list the table Group but somehow did not recognize it later in the query. I tried writing the query with different flavors of escaping things but had no luck.

Any idea on how we can get that sorted?

[simolus3]

That's weird. I can't reproduce this with a simple example in a moor file:

CREATE TABLE "Group" (
  id INTEGER NOT NULL PRIMARY KEY
);

x: SELECT * FROM "Group"; -- works as intended

To help me track this down, can you check whether this works if you define the Group table in the same moor file? Can you post the full query that fails?

[skyne98]

Hey there, working with @Dev-Owl on the same project!

After moving the Group table to the main moor file, as you suggested:

CREATE TABLE "Group" (
  ID INTEGER NOT NULL PRIMARY KEY,
  Name varchar,
  SubDomainID integer,
  LastChange integer MAPPED BY `const DateTimeConverter()`,
  MarkDeleted integer,
  Exported integer
);

Error still unfortunately occurs:

[WARNING] moor_generator:moor_generator on lib/database/dbContext.dart:
line 745, column 25: Could not find Group. Available are: sqlite_master, sqlite_sequence, TaskList, TaskConsumable, SubDomain, Storage, StockHistoryReason, StockHistory, SoftwareModule, MaintenanceCategory, Log_Category, LogMessage, LastChangeDate, ItemUnit, ItemSupplier, ItemSubfolder, ItemStorage, ItemShortcut, ItemCurrency, Item, HourCounterLog, HourCounterGroup, HourCounter, ExpressionTemplate, EquipmentType, ERLog_StartedRound, ERLog_RoundEntry, ERLog_RoundDefRevPoint, ERLog_RoundDefRev, ERLog_RoundDef, ERLog_Expression, ERLog_EntryPointHistory, ERLog_EntryPoint, ERLog_Entry, ERLog_Domain, ERLog_Category, Domain, DocumentSubfolder, DocumentModified, DocumentLink, DocumentFile, CrewLogin, CrewGuestDatabasePositionsERLog_Domain, CrewAndGuestDatabaseUserRole, CrewAndGuestDatabasePosition, CrewAndGuestDatabaseDepartment, ComponentsToEquipmentType, ComponentsMaintenanceInterval, ComponentsMaintenanceEvent, ComponentSubfolder, ComponentIntervalConsumable, ComponentHistory, Component, Company, ExpressionWarningMessage, sqlite_master, sqlite_sequence
    ╷
745 │         LEFT OUTER JOIN "Group" ON Component.GroupID = "Group".ID
    │                         ^^^^^^^
    ╵

Also, just for reference, the Group class was included into the main Database class like this:

@UseMoor(tables: [
  ExpressionType,
  LogExpression,
  SystemSetting,
  Group
], include: {
  'moor/intiaialDB.moor'
}, daos: [
  PlaygroundDao,
  SyncDao,
  LoginDao,
  DashboardDao,
  MaintenanceDao,
  MaintenanceOverviewDao,
  ComponentDao
])

...where 'moor/intiaialDB.moor' is the file where all other tables and the query originates from.

Query for the reference too:

getPeriodicTaskDetailsById: SELECT ComponentsMaintenanceInterval.ID AS ID
            ,ComponentsMaintenanceInterval.Name AS Name
            ,ComponentsMaintenanceInterval.Priority AS Priority
            ,ComponentsMaintenanceInterval.Responsible AS Responsible
            ,ComponentsMaintenanceInterval.Commentary AS Commentary
            ,ComponentsMaintenanceInterval.TimeBasedNextDueDate AS TimeBasedNextDueDate
            ,ComponentsMaintenanceInterval.TimeBasedLastDueDate AS TimeBasedLastDueDate
            ,ComponentsMaintenanceInterval.TimeBasedInterval
            ,HCTime.Name AS HourCounterTime
            ,ComponentsMaintenanceInterval.EventBasedID AS EventBasedID
            ,ComponentsMaintenanceInterval.HourBasedLastDueValue AS HourBasedLastDueValue
            ,ComponentsMaintenanceInterval.HourBasedInterval AS HourBasedInterval
            ,ComponentsMaintenanceInterval.EstimatedDueDate AS EstimatedDueDate
            ,HourCounter.Value AS HCValue
            ,HourCounter.Daily AS HCDDaily
            ,HourCounter.Name AS HourCounterName
            ,Component.ID AS ComponentID
            ,Component.Name AS ComponentName
            ,Component.IsCritical AS IsCritical
            ,"Group".ID AS GroupID
            ,"Group".Name AS GroupName
            ,Domain.OwnerDepartmentID
            ,ComponentsMaintenanceInterval.Costs
            ,0 AS isOneTime
        FROM ComponentsMaintenanceInterval
        LEFT OUTER JOIN Component ON ComponentsMaintenanceInterval.ComponentID = Component.ID
        LEFT OUTER JOIN HourCounter AS HCTime ON ComponentsMaintenanceInterval.TimeBasedHoursID = HCTime.ID
        LEFT OUTER JOIN HourCounter ON ComponentsMaintenanceInterval.HourBasedHoursID = HourCounter.ID
        LEFT OUTER JOIN "Group" ON Component.GroupID = "Group".ID
        LEFT OUTER JOIN SubDomain ON "Group".SubDomainID = SubDomain.ID
        LEFT OUTER JOIN Domain ON SubDomain.DomainID = Domain.ID
        WHERE ComponentsMaintenanceInterval.MarkDeleted = 0 AND ComponentsMaintenanceInterval.ID = :id;

Thank you for your time and your amazing project! On stand-by if you need any other info :)

[skyne98]

Also, to clarify the problem a little more, the error encountered above is not alone, it seems to be a general problem with the recognition of escaped tables. More examples of warnings:

[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 610, column 4: Expression has an unknown type, the generated code can be inaccurate.
    ╷
610 │         ,"Group".Name AS GroupName
    │          ^^^^^^^^^^^^
    ╵
[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 656, column 4: Expression has an unknown type, the generated code can be inaccurate.
    ╷
656 │         ,"Group".ID AS GroupID
    │          ^^^^^^^^^^
    ╵
[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 657, column 4: Expression has an unknown type, the generated code can be inaccurate.
    ╷
657 │         ,"Group".Name AS GroupName
    │          ^^^^^^^^^^^^
    ╵
[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 703, column 14: Expression has an unknown type, the generated code can be inaccurate.
    ╷
703 │             ,"Group".ID AS GroupID
    │              ^^^^^^^^^^
    ╵
[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 704, column 14: Expression has an unknown type, the generated code can be inaccurate.
    ╷
704 │             ,"Group".Name AS GroupName
    │              ^^^^^^^^^^^^
    ╵
[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 736, column 14: Expression has an unknown type, the generated code can be inaccurate.
    ╷
736 │             ,"Group".ID AS GroupID
    │              ^^^^^^^^^^
    ╵
[INFO] moor_generator:moor_generator on lib/database/dbContext.dart:line 737, column 14: Expression has an unknown type, the generated code can be inaccurate.
    ╷
737 │             ,"Group".Name AS GroupName
    │              ^^^^^^^^^^^^
    ╵

[skyne98]

Ok, quick update, just figured out that I included a syntax error inside the SQL version of the table, after fixing which generation seems to finish without any warnings! Seems like the issue only exists for the tables defined in Dart.

[simolus3]

Ok, quick update, just figured out that I included a syntax error inside the SQL version of the table, after fixing which generation seems to finish without any warnings

Can you give me a bit more information about that? I looks like this isn't a moor bug, but the error messages were rather unhelpful. If the table definition was invalid without the generator complaining, I want to change that.

[skyne98]

To clarify things after a couple of conflicting messages from my side: it seems like (at least in case of our project, moor: ^3.4.0) defining a table with a keyword name like Group in the .moor file in SQL works as expected, however, when defining the same Group table in Dart and then adding it to tables: [] in the attribute (as @Dev-Owl showed in his example) it seems that queries declared in the .moor file (which is also included in the attribute) cannot find the table, therefore throwing warnings like these:

[WARNING] moor_generator:moor_generator on lib/database/dbContext.dart:
line 745, column 25: Could not find Group. Available are: sqlite_master, sqlite_sequence, TaskList, TaskConsumable, SubDomain, Storage, StockHistoryReason, StockHistory, SoftwareModule, MaintenanceCategory, Log_Category, LogMessage, LastChangeDate, ItemUnit, ItemSupplier, ItemSubfolder, ItemStorage, ItemShortcut, ItemCurrency, Item, HourCounterLog, HourCounterGroup, HourCounter, ExpressionTemplate, EquipmentType, ERLog_StartedRound, ERLog_RoundEntry, ERLog_RoundDefRevPoint, ERLog_RoundDefRev, ERLog_RoundDef, ERLog_Expression, ERLog_EntryPointHistory, ERLog_EntryPoint, ERLog_Entry, ERLog_Domain, ERLog_Category, Domain, DocumentSubfolder, DocumentModified, DocumentLink, DocumentFile, CrewLogin, CrewGuestDatabasePositionsERLog_Domain, CrewAndGuestDatabaseUserRole, CrewAndGuestDatabasePosition, CrewAndGuestDatabaseDepartment, ComponentsToEquipmentType, ComponentsMaintenanceInterval, ComponentsMaintenanceEvent, ComponentSubfolder, ComponentIntervalConsumable, ComponentHistory, Component, Company, ExpressionWarningMessage, sqlite_master, sqlite_sequence
    ╷
745 │         LEFT OUTER JOIN "Group" ON Component.GroupID = "Group".ID
    │                         ^^^^^^^
    ╵

I think you should be able to reproduce the issue if you try to declare the table programmatically instead of the .moor file.

[Dev-Owl]

Sorry, was unable to check back yesterday... But as my workmate mentioned, if we include the model from dart into the moor file we get the error I posted. Following your suggestion we moved the model into the moor file and the issue is gone. I'm happy to share whatever you need to reproduce this.

[OroshiX]

I had the same issue, but I found out that I had to add an import to the file where the tables were defined in the *.moor file like described in the documentation