Creating SQL triggers for full text search index in SQLite

Question

I'm trying to create triggers for a regular table to then update a full text index in SQLite, but I'm getting some errors and I'm not sure where I've gone wrong.

The app I'm making is a bookmarking app and the database I save the bookmark data to is created using the following SQL statement:

create table "pages" (
  "pageUrl" text not null unique on conflict replace, 
  "dateCreated" integer not null, 
  "pageDomain" text not null, 
  "pageTitle" text null, 
  "pageText" text null, 
  "pageDescription" text null, 
  "archiveLink" text null, 
  "safeBrowsing" text null, 
  primary key ("pageUrl")
);

Then the full text search index is created with:

create virtual table fts using fts5(
  content='pages', 
  content_rowid='pageUrl', 
  pageDomain, 
  pageTitle, 
  pageText, 
  pageDescription
);

So then I'd like to update the fts index when the "pages" table is updated via an insert or a delete.

The trigger I have for insert:

create trigger afterPagesInsert after insert on pages begin
  insert into fts(
    rowid, 
    pageDomain, 
    pageTitle, 
    pageText, 
    pageDescription
  )
  values(
    new.pageUrl, 
    new.pageDomain, 
    new.pageTitle, 
    new.pageText, 
    new.pageDescription
  );
end;

The trigger I have for the delete:

create trigger afterPagesDelete after delete on pages begin
  insert into fts(
    fts,
    rowid,
    pageDomain,
    pageTitle,
    pageText,
    pageDescription
  )
  values(
    'delete',
    old.pageUrl,
    old.pageDomain,
    old.pageTitle,
    old.pageText,
    old.pageDescription
  );
end;

Here's an example of an sql insert statement I'm using:

insert into "pages" (
  "pageUrl",
  "dateCreated",
  "pageDomain",
  "pageTitle",
  "pageText",
  "pageDescription",
  "archiveLink",
  "safeBrowsing"
  )
values(
  'https://www.reddit.com/',
  1456465040177,
  'reddit.com',
  'reddit: the front page of the internet',
  'reddit: the front page of the internet',
  'reddit: the front page of the internet',
  NULL,
  NULL
  )

And the delete statement:

delete from "pages" where "pageUrl" = 'https://www.reddit.com/'

But, I'm getting an error of SQLITE_MISMATCH: datatype mismatch] errno: 20, code: 'SQLITE_MISMATCH' for both the insert and the delete trigger, which I guess seems to indicate that the wrong data is going in to the wrong column, but I'm not sure why. I've gone through the triggers section in the External Content Tables section in the docs here and I've followed what was listed, so I'm not sure where I'm going wrong.

Any help would be appreciated.

note: I'm using the fts5 version of the SQLite full text search: https://www.sqlite.org/fts5.html

Answer 1

The content_rowid must refer to the rowid of the actual table, i.e., the INTEGER PRIMARY KEY column.