Making a full-text search module that works on both desktop and mobile (Pt. 1)

I'm a solo developer of Inkdrop, a Markdown note-taking app for programmers which supports macOS, windows, linux, android and iOS. It is built on top of Electron for desktop platforms and React Native for mobile platforms.
I'm currently working on rebuilding full-text search feature as I declared in our roadmap.
This article is my work progress note on making it.

---

To implement a full-text search feature, I would like to try using FTS of SQLite3. FTS is a full-text search engine for SQLite3. There are multiple versions: FTS3, FTS4 and FTS5. I'm gonna try FTS5.

Use node-websql to have a compatibility between mobile (React Native) and desktop (Electron).
It depends on node-sqlite3 so it works well on Electron apps.

First, I would like to try this module out from nodejs.

Trying FTS5

It worked like a charm out of the box!

const openDatabase = require('websql')

var db = openDatabase('mydb.db', '1.0', 'description', 1)

db.transaction(txn => {
  txn.executeSql('DROP TABLE IF EXISTS notes_fts')
  txn.executeSql(
    'CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(note_id, body)'
  )
  txn.executeSql(
    'INSERT INTO notes_fts (note_id, body) VALUES (:note_id, :body)',
    [1, 'Hello, world!']
  )
  txn.executeSql(
    'SELECT * FROM `notes_fts` where body MATCH :keyword',
    ['hello OR hoge'],
    function(tx, res) {
      for (let i = 0; i < res.rows.length; ++i) {
        console.log('hit:', res.rows.item(i))
      }
    }
  )

  txn.executeSql(
    'SELECT * FROM `notes_fts` where body MATCH :keyword',
    ['worl*'],
    function(tx, res) {
      for (let i = 0; i < res.rows.length; ++i) {
        console.log('hit2:', res.rows.item(i))
      }
    }
  )
})

It supports forward matching, OR operator and even NOT operator...that's awesome.

How about mobile platforms?

Android doesn't support FTS5 by default

Seems like it needs to install requery/sqlite-android: Android SQLite support library.
But according to this page: sqlite - SQLite3 FTS4, MATCH and Android - Stack Overflow
FTS4 can be used since API >= 11.
Confirmed it works in emulator!

iOS supports FTS5 by default

That's a good news. According to this article: Leveraging SQLite Full-Text Search on iOS | Inside PSPDFKit
FTS5 is supported from iOS 11.

Difference between FTS5 and FTS4

Overall, the FTS5 module has much better performance than its predecessors. However, it is missing some functionality when compared to FTS4, in that the auxiliary functions to extract information about matches aren’t as fully featured. Functions like matchinfo() and offsets() do not exist, and the snippet() function is not as useful as in FTS4.

So, the conclusion is to go with FTS4. But if possible, it would be better to switch to FTS5.

Sync index with pouchdb

Inkdrop's database is implemented with PouchDB.

Use Changes feed to sync the index for FTS.

Try UPDATE first, if failed, then INSERT.

txn.executeSql(
  'UPDATE notes_fts set body = :body where note_id = :note_id',
  ['Hello, world!', 1],
  (txn, res) => {
    if (res.rowsAffected === 0) {
      console.log('The row does not exist. Trying inserting.')
      txn.executeSql(
        'INSERT INTO notes_fts (note_id, body) VALUES (:note_id, :body)',
        [1, 'Hello, world!'],
        (txn, res) => console.log('inserted:', res)
      )
    }
  }
)

Or, it would be good to SELECT first to check if the row exists because SELECT query is smaller.

---

To be continued.

Comments

[z0al]

Great article Takuya. Thank you so much. I had this post bookmarked for a few years because I knew one day I will need it :)

I have a few questions:

So, the conclusion is to go with FTS4. But if possible, it would be better to switch to FTS5.

1. Did you end up going with fts4 or fts5 at the end?
2. Do you use contentless or contentful (external content) table?
3. If you used contentless tables with fts4. How do you workaround deleting records?
4. if you used external content tables. How much more space does it (approximately) take compared to the original size (without FTS)?

[Takuya Matsuyama]

I ended up only using FTS5 because I managed to update the SQLite version on Android: github.com/craftzdog/react-native-...
I use neither contentless nor contentful because the actual content is stored in PouchDB. Since they are just text notes, the size wouldn't matter.

[z0al]

Thank you for your reply

I use neither contentless nor contentful because the actual content is stored in PouchDB

I'm a bit confused. I thought that's what contentless tables were about?

Contentless FTS4 Tables: In order to create an FTS4 table that does not store a copy of the indexed documents at all, the content option should be set to an empty string.

From the SQLite docs