External Content FTS4 Table

[go1t]

Is there a way to do this in SQLite.swift? :

 CREATE VIRTUAL TABLE table USING fts4(content='some_other_table', id, name)

I'd like to pre-populate my fts4 virtual table, but inserting one by one is very slow since my table contains over 30,000 rows. Is there any recommended way for this in SQLite.swift?

[stephencelis]

Many of the options for fts4 aren't exposed yet but should be. In the meantime, aking this function public will get you closer:

SQLite.swift/SQLite/FTS.swift

Line 36 in 89d1619

private func fts(function: String, columns: [Expression<String>], options: [String: String]) -> Expression<Void> {

Or you can execute the raw SQL instead.

[lukescott]

It would also be nice if there was a way to setup the triggers to keep the data in sync, such as:

CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
  DELETE FROM t3 WHERE docid=old.rowid;
END;
CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
  DELETE FROM t3 WHERE docid=old.rowid;
END;
CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;
CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;

http://www.sqlite.org/fts3.html#section_6_2_2

[mikemee]

Added to Feature Requests.