Create a virtual full-text search table in sqlite

meta-fts.table.sql

CREATE TABLE data(
   id VARCHAR PRIMARY KEY ,
   uuid CHAR NOT NULL  ,
   created_at INTEGER NOT NULL  ,
   updated_at INTEGER NOT NULL  ,
   md5 CHAR NOT NULL  ,
   sha1 CHAR   ,
   imported_at INTEGER NOT NULL  ,
   path TEXT   ,
   name VARCHAR NOT NULL  ,
   mtime INTEGER NOT NULL  ,
   ctime INTEGER   ,
   atime INTEGER   ,
   size INTEGER NOT NULL  ,
   uid INTEGER   ,
   gid INTEGER   ,
   mode INTEGER   ,
   type TEXT   
);
CREATE INDEX md5_index ON data ( 'md5' );
CREATE INDEX sha1_index ON data ( 'sha1' );
CREATE INDEX uuid_index ON data ( 'uuid' );

CREATE VIRTUAL TABLE fts USING fts4 (
   content="data"   ,
   path TEXT   ,
   name VARCHAR NOT NULL  
);

/*
-- Used when `content="data"` is omitted
CREATE TRIGGER data_bu BEFORE UPDATE ON data BEGIN
  DELETE FROM fts WHERE docid=old.rowid;
END;
CREATE TRIGGER data_bd BEFORE DELETE ON data BEGIN
  DELETE FROM fts WHERE docid=old.rowid;
END;

CREATE TRIGGER data_au AFTER UPDATE ON data BEGIN
  INSERT INTO fts(docid, name, path) VALUES(new.rowid, new.name, new.path);
END;
CREATE TRIGGER data_ad AFTER INSERT ON data BEGIN
  INSERT INTO fts(docid, name, path) VALUES(new.rowid, new.name, new.path);
END;
*/

/*
ATTACH 'meta.sqlite3' AS meta;
ATTACH 'meta-fts.sqlite3' AS fts;
INSERT INTO fts.data(
  id,
  uuid,
  created_at,
  updated_at,
  md5,
  sha1,
  imported_at,
  path,
  name,
  mtime,
  ctime,
  atime,
  size,
  uid,
  gid,
  mode,
  type
) SELECT * FROM meta.data;
*/