Sqlite FTS5 matches nothing in Electron app

Viewed 37

I'm making a note-taking app of Electron, using sqlite3(v3.31.0) as backend. Here is my creation statement for normal table Note:

create table `Note` (
  `id` char(36),
  `icon` text not null,
  `title` text not null,
  `body` text not null,
  `parentId` char(36) not null,
  `sortOrder` integer not null,
  `userCreatedAt` datetime not null,
  `userModifiedAt` datetime not null,
  `childrenSortBy` text check (`childrenSortBy` in ('DEFAULT', 'TITLE', 'CREATED_AT', 'UPDATED_AT', 'CUSTOM')) not null,
  `childrenSortDirect` text check (`childrenSortDirect` in ('ASC', 'DESC', 'DEFAULT')) not null,
  foreign key(`parentId`) references `Note`(`id`),
  primary key (`id`)
)

and here is my creation statement for virtual table:

CREATE VIRTUAL TABLE Note_FTS USING fts5(
    title,
    body,
    content='Note'
);
CREATE TRIGGER Note_FTS_ai AFTER INSERT ON Note BEGIN
    INSERT INTO Note_FTS(
        rowid,
        title,
        body
    ) VALUES (
        new.rowid,
        new.title, 
        new.body
    );
END;
CREATE TRIGGER Note_FTS_ad AFTER DELETE ON Note BEGIN
    INSERT INTO Note_FTS(
        Note_FTS,
        rowid,
        title,
        body
    ) VALUES(
        'delete',
        old.rowid,
        old.title,
        old.body
    );
END;
CREATE TRIGGER Note_FTS_au AFTER UPDATE ON Note BEGIN
    INSERT INTO Note_FTS(
        Note_FTS,
        rowid,
        title,
        body
    ) VALUES(
        'delete',
        old.rowid,
        old.title,
        old.body
    );
    INSERT INTO Note_FTS(
        rowid, 
        title,
        body
    ) VALUES (
        new.rowid,
        new.title, 
        new.body
    );
END;

After creation, I inserted a row into Note table, and Note_FTS was populated by trigger: enter image description here enter image description here

So far so good. But when I use a query:

SELECT * 
FROM Note_FTS
WHERE Note_FTS MATCH 'aaa';

Sqlite return 0 row as result. Did I miss something or do anything wrong?

0 Answers
Related