jeudi 25 février 2016

Creating SQL triggers for full text search index in SQLite

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;

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. 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: http://ift.tt/1UahcOr

Aucun commentaire:

Enregistrer un commentaire