Moor: Triggers can not insert rowid of FTS table

[arianvp]

The following example succeeds to compile:

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b TEXT, c TEXT);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(b, c) VALUES (new.b, new.c);
END;

However, if I try to set the rowid to the PRIMARY key of tbl, it fails:

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b TEXT, c TEXT);


CREATE VIRTUAL TABLE fts_idx USING fts5(b TEXT, c TEXT, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

NoSuchMethodError: The getter 'name' was called on null.
Receiver: null
Tried calling: name
dart:core                                                                       Object.noSuchMethod
package:moor_generator/src/analyzer/sql_queries/lints/linter.dart 191:25        _LintingVisitor.visitInsertStatement.<fn>.<fn>
dart:collection                                                                 ListMixin.any
package:moor_generator/src/analyzer/sql_queries/lints/linter.dart 191:12        _LintingVisitor.visitInsertStatement.<fn>
dart:core                                                                       Iterable.isNotEmpty
package:moor_generator/src/analyzer/sql_queries/lints/linter.dart 193:22        _LintingVisitor.visitInsertStatement
package:sqlparser/src/ast/statements/insert.dart 40:20                          InsertStatement.accept
package:sqlparser/src/ast/visitor.dart 572:12                                   VisitChildrenExtension.visitList
package:sqlparser/src/ast/visitor.dart 566:12                                   VisitChildrenExtension.visitChildren
package:sqlparser/src/ast/visitor.dart 548:12                                   RecursiveVisitor.defaultNode
package:sqlparser/src/ast/visitor.dart 356:12                                   RecursiveVisitor.visitBlock
package:sqlparser/src/ast/statements/block.dart 13:20                           Block.accept
package:sqlparser/src/ast/visitor.dart 572:12                                   VisitChildrenExtension.visitList
package:sqlparser/src/ast/visitor.dart 566:12                                   VisitChildrenExtension.visitChildren
package:sqlparser/src/ast/visitor.dart 548:12                                   RecursiveVisitor.defaultNode
package:sqlparser/src/ast/visitor.dart 178:12                                   RecursiveVisitor.visitStatement
package:sqlparser/src/ast/visitor.dart 174:12                                   RecursiveVisitor.visitSchemaStatement
package:sqlparser/src/ast/visitor.dart 153:12                                   RecursiveVisitor.visitCreateTriggerStatement
package:sqlparser/src/ast/statements/create_trigger.dart 31:20                  CreateTriggerStatement.accept
package:sqlparser/src/ast/ast.dart 147:12                                       AstNode.acceptWithoutArg
package:moor_generator/src/analyzer/sql_queries/lints/linter.dart 23:18         Linter.reportLints
package:moor_generator/src/analyzer/moor/entity_handler.dart 66:12              EntityHandler._lint
package:moor_generator/src/analyzer/moor/entity_handler.dart 42:9               EntityHandler.handle
package:moor_generator/src/analyzer/runner/steps/analyze_moor.dart 24:55        AnalyzeMoorStep.analyze
package:moor_generator/src/analyzer/runner/task.dart 223:45                     Task._analyze
package:moor_generator/src/analyzer/runner/task.dart 61:13                      Task.runTask
package:moor_generator/src/backends/build/moor_builder.dart 34:18               MoorBuilder.analyzeDartFile
package:moor_generator/src/backends/build/generators/moor_generator.dart 27:34  MoorGenerator.generate
package:source_gen/src/builder.dart 301:33                                      _generate
package:source_gen/src/builder.dart 79:15                                       _Builder._generateForLibrary
package:source_gen/src/builder.dart 71:11                                       _Builder.build
package:moor_generator/src/backends/build/moor_builder.dart 87:18               MoorSharedPartBuilder.build
package:build                                                                   runBuilder
package:build_runner_core/src/generate/build_impl.dart 486:19                   _SingleBuild._runForInput.<fn>.<fn>.<fn>
package:build_runner_core/src/generate/performance_tracker.dart 302:15          _NoOpBuilderActionTracker.trackStage
package:build_runner_core/src/generate/build_impl.dart 484:23                   _SingleBuild._runForInput.<fn>.<fn>
package:build_runner_core/src/generate/build_impl.dart                          _SingleBuild._runForInput.<fn>.<fn>
package:timing/src/timing.dart 222:44                                           NoOpTimeTracker.track
package:build_runner_core/src/generate/build_impl.dart 441:22                   _SingleBuild._runForInput.<fn>
package:pool/pool.dart 127:28                                                   Pool.withResource
package:build_runner_core/src/generate/build_impl.dart 437:17                   _SingleBuild._runForInput
package:build_runner_core/src/generate/build_impl.dart 375:38                   _SingleBuild._runBuilder.<fn>
dart:async                                                                      Future.wait
package:build_runner_core/src/generate/build_impl.dart 374:36                   _SingleBuild._runBuilder
package:build_runner_core/src/generate/build_impl.dart 320:20                   _SingleBuild._runPhases.<fn>.<fn>
dart:async                                                                      _completeOnAsyncReturn
package:build_runner_core/src/generate/build_impl.dart                          _SingleBuild._matchingPrimaryInputs

[INFO] Build:Running build completed, took 38ms

[INFO] Build:Caching finalized dependency graph...
[INFO] Build:Caching finalized dependency graph completed, took 19ms

[SEVERE] Build:
Failed after 60ms

[arianvp]

Workaround: dont mention the column names in INSERT INTO:

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx VALUES (new.a, new.b, new.c);
END;

However; this is problematic once you want to trigger ON DELETE as then you need to insert 4 tuples:

CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;

CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

and the same error returns; as when you leave the explicit column names away; moor will error out and say that 4-tuple was provided whilst 3-tuple was expecte

[simolus3]

Thanks for the report! I fixed this in a037de6621360b840239e079da2e6371a6dfdbfe. You can try it out by putting this in your pubspec:

dependency_overrides:
  sqlparser:
    git:
      url: https://github.com/simolus3/moor.git
      ref: beta
      path: sqlparser

If that works for you I can release the fix to pub.

[arianvp]

it works!

[SteveAlexander]

I had this issue. The fix works in the analyzer, but when I run build_runner, I get the same error

[SEVERE] moor_generator:moor_generator on thing.dart:

NoSuchMethodError: The getter 'name' was called on null.
Receiver: null
Tried calling: name

[ngxingyu]

I used this in pubspec.yaml and it works for me currently.

dependency_overrides:
  sqlparser:
    git:
      url: https://github.com/simolus3/moor.git
      ref: develop
      path: sqlparser

[SteveAlexander]

@simolus3 The dependency overrides posted by @ngxingyu above are necessary for using FTS with triggers in Moor.

Any ideas whether this will be reaching the main line of development, and eventually a pub release?

I'd prefer to use triggers for the work I'm doing just now, but I don't want to introduce dependency overrides into the project longer term.

thanks

[simolus3]

Any ideas whether this will be reaching the main line of development

develop is the main line of development for moor :) But I hope to release the next moor version at the end of this week, so you won't need the dependency override for long.