Full text search

[qiaolin-pan]

I'm wondering about FTS support.
For sure it is an sql's feature, but do you have something similar for such queries?

[radex]

@pakyo-pan There's no FTS support in Watermelon right now, but it would be very nice to have it. If you want to research the issue and propose a solution, I'd be happy to take a look, give you pointers as for implementation, etc.

[Rob117]

If it's possible to access the underlying SQLite database, could FTS5 be bridged to work with this, or would that be more work than writing it from scratch?

[radex]

@Rob117 sure, using SQLite capabilities is possible. Although it would be ideal if it could also work on the web (via LokiJS). But, of course, first things first. If you'd like to work on this, I can give you a few pointers

[Rob117]

Maybe Fuse.js would work better for that? Replicating the contents into Fuse on write/delete could provide a short-term solution, but it makes things tricky because there has to be a way to save relations between the generated documents and their original DB records.

Does WatermelonDB generate uuids for each record under the hood?

[radex]

You mean for web only? Maybe… I would fear performance implications of just dumping lots of data into yet another library, but perhaps when it's just for one field, set up for a specific query, then it might work.

yes, watermelon generates unique ids for records, but not in UUID format, if that's what you mean

[Rob117]

@radex I'm going to be working on an app this year - let me see if I can get a solution rigged up. I'm much more likely to find something involving gluing Fuse to Watermelon, so I'll go that route to at least see what I can do.

[Rob117]

@radex Is the db built with SQLite fts5 enabled?

If not, where would I change that flag in the source build?

[stigi]

I used FTS in another SQLite project and the FTS tables can live quite autonomously using SQLite triggers to update themselves.

I had the idea to introduce a schema field isSearchable and if set, that column would become FTS indexed. We'd need to add an additional Query type (or wait for #103) as well.

Not sure how to add that feature to the LokiJS adapter though.

[stigi]

Here's an example that would add a sqlite FTS index for table message over the fields subject and body

-- Creating the FTS4 virtual table table
CREATE VIRTUAL TABLE "message_FTS" USING fts4(subject, body);

-- Populating message_FTS with existing content from message
INSERT INTO "message_FTS"(rowid, subject, body) SELECT rowid, subject, body from "message";

-- Setting up triggers to keep message_FTS current
CREATE TRIGGER message_FTS_DELETE AFTER DELETE ON message
   BEGIN
     DELETE FROM message_FTS WHERE rowid = OLD.rowid;
   END;
CREATE TRIGGER message_FTS_INSERT AFTER INSERT ON message
   BEGIN
     INSERT INTO message_FTS (rowid, subject, body) VALUES (NEW.rowid, NEW.subject, NEW.body);
   END;
CREATE TRIGGER message_FTS_UPDATE AFTER UPDATE ON message
   BEGIN
     UPDATE message_FTS SET subject = NEW.subject, body=NEW.body WHERE rowid = NEW.rowid;
   END;

The query would use a join like this message_FTS MATCH :filterQuery AND message_FTS.rowid == message.rowid.

I believe we based this on FTS4 which was widely enabled by default on Android and iOS.

[radex]

@radex Is the db built with SQLite fts5 enabled?
If not, where would I change that flag in the source build?

🍉 uses system-supplied SQLite. I'm not sure if iOS and Android SQLite supports FTS. If so, let's just use that.

It would be great if 🍉 allowed the user to specify a custom SQLite build BTW. e.g. for encryption or other extensions.

I had the idea to introduce a schema field isSearchable and if set, that column would become FTS indexed. We'd need to add an additional Query type (or wait for #103) as well.

an isSearchable schema and migrations field seems to make sense to me, given the need for FTS to set up its indexer.

It would be best to have a Query for this. e.g. comments.query(Q.where('body', Q.textMatches('xxxxx'))) — not very difficult to implement, I can give pointers to all the necessary places :)

Not sure how to add that feature to the LokiJS adapter though.

Me neither. I'm OK with implementing this for only one platform first, if necessary — as long as there's an invariant / dev warning that this won't work.

There might be a dumb implementation for web that only works for exact matches Q.like('%foo%'').

Or we might use Fuse or something like it on the web worker thread to do the search

[radex]

Let me know how I can help you go about implementing this. We'd be very interested at @Nozbe to have full text search in 🍉 — no time to implement it ourselves, but I'm more than happy to give advice and pointers to where all the pieces need work

[stigi]

I'm dabbling around on this branch https://github.com/stigi/WatermelonDB/tree/fts

Hope I can get something in a PR next week.

[fungilation]

hey @stigi any issue with FTS5? I have keen interest in adding this support for my RN app (so no Web support is fine with me). Any pointers and blockers in integrating it would be appreciated.

And heads up on any other ongoing effort @radex? It was nice meeting you at Chain React btw.

[radex]

@fungilation My understanding is that @stigi abandoned this work, and no one as far as I know is working on FTS currently, so I encourage you to take @stigi’s branch where he left off and continue this work :) You can start sending smaller pull requests and I wil be happy to review them and give pointers/feedback

[stigi]

@fungilation I talked to @radex during the react-native eu conf.
I stopped because the syntax around the query builder didn't come natural to me. I believe I got the table creation and propagation done and it's mostly only missing the query (See #197 (comment))