Hello everyone, I have some trouble using FTS with...

# squarelibraries

[pvasa]

Hello everyone, I have some trouble using FTS with sqldelight on Android. It is a multiplatform setup and haven’t tested on ios yet.

I have a content table and a fts table pointing to it. Both the tables are populated correctly and I can see all the data when doing SELECT * on either but when I try to use MATCH on fts table, it always returns empty dataset.

Is there any extra step required to enable fts?

Am I missing something for the setup?

Below are the queries I use to setup tables

Create content table

CREATE TABLE dbBook (
  isbn INTEGER NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  subtitle TEXT NOT NULL DEFAULT '',
  coverUrl TEXT DEFAULT NULL,
  summary TEXT NOT NULL DEFAULT '',
  authorIds TEXT AS List<Long> NOT NULL,
  revisions TEXT AS List<Long> NOT NULL DEFAULT '',
  publishedOn INTEGER as Date DEFAULT NULL,
  addedAt INTEGER as DateTime NOT NULL DEFAULT ((julianday('now') - 2440587.5) * 86400.0 * 1000)
);

Create fts table

CREATE VIRTUAL TABLE dbBookFts
USING FTS4(
  tokenize=porter,
  content=dbBook,
  isbn INTEGER NOT NULL,
  title TEXT NOT NULL,
  subtitle TEXT NOT NULL,
  summary TEXT NOT NULL
);

Triggers to update fts table

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER dbBook_bu BEFORE UPDATE ON dbBook BEGIN
  DELETE FROM dbBookFts WHERE isbn=old.isbn;
END;
CREATE TRIGGER dbBook_bd BEFORE DELETE ON dbBook BEGIN
  DELETE FROM dbBookFts WHERE isbn=old.isbn;
END;

CREATE TRIGGER dbBook_au AFTER UPDATE ON dbBook BEGIN
  INSERT INTO dbBookFts(isbn, title, subtitle, summary)
  VALUES(new.isbn, new.title, new.subtitle, new.summary);
END;
CREATE TRIGGER dbBook_ai AFTER INSERT ON dbBook BEGIN
  INSERT INTO dbBookFts(isbn, title, subtitle, summary)
  VALUES(new.isbn, new.title, new.subtitle, new.summary);
END;

Query for searching

SELECT dbBook.*
FROM dbBookFts
JOIN dbBook ON dbBookFts.isbn = dbBook.isbn
WHERE dbBookFts MATCH :query;

Even a simple MATCH query is empty
SEELCT * FROM dbBookFts WHERE dbBookFts MATCH :query;

[Dominaezzz]

Does the query return the same thing outside of SQLDelight?

[pvasa]

yes I checked with sqlite3 on mac but not sure if fts is supported/enabled at all there

[pvasa]

Update: I am able to get results in sqlitebrowser but only using fts3

[alec]

i think you want WHERE content MATCH :query ?

[pvasa]

you mean the word “content” or content table? either way it shows error “No column found with name ..”

Isn’t the syntax like SELECT * FROM fts_table WHERE fts_table MATCH 'query';?

[pvasa]

it is with the fts table name https://github.com/AlecStrong/sql-psi/pull/37

[Erik Christensen]

If I'm remembering right, I don't think "content=" is handled correctly.

[pvasa]

I tried by removing content= and thus completely relying on triggers but still same behaviour.

I am seeing this trace from system on Android

SQLiteDatabase: Error inserting period=6962000 preferred_charging_state=1 preferred_network_type=1 target_class=com.google.android.gms.measurement.PackageMeasurementTaskService required_network_type=0 runtime=1590001184152 required_idleness_state=0 source=16 service_kind=0 target_package=com.google.android.gms source_version=201516000 last_runtime=0 user_id=0 job_id=-1 requires_charging=0 tag=Measurement.PackageMeasurementTaskService.UPLOAD_TASK_TAG flex_time=3480000 task_type=0 retry_strategy={"maximum_backoff_seconds":{"3600":0},"initial_backoff_seconds":{"30":0},"retry_policy":{"0":0}}
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: pending_ops.tag, pending_ops.target_class, pending_ops.target_package, pending_ops.user_id (code 2067 SQLITE_CONSTRAINT_UNIQUE[2067])

Although when I run same insert operations on mac on the same database file, everything looks good and I am able to do MATCH queries

[pvasa]

could it be something around how Android driver is handling creation of fts tables?

[Erik Christensen]

It's likely that the Sqlite version is different - and possibly how it's configured. This doesn't seem like a SqlDelight issue specifically though.

[pvasa]

Thanks everyone for your input, for now I have switched to LIKE queries. Sometime later I will look into it more by using sqlite apis directly and maybe try on a different device