Database changes
Previous schema
notes_normalized (
id, (indexed)
title,
body
)
notes_fts (
id,
title, (indexed)
body, (indexed)
)
New schema
notes_normalized(
id, (indexed)
title,
body,
user_created_time, (indexed)
is_todo, (indexed)
todo_completed, (indexed)
parent_id, (indexed)
latitude, (indexed)
longitude, (indexed)
altitude (indexed)
)
notes_fts (
id,
title, (indexed)
body, (indexed)
user_created_time,
is_todo,
todo_completed,
parent_id,
latitude,
longitude,
altitude
)
We also added a migration that rebuilds the existing index.
The reasons why table notes_normalized
and notes_fts
got these new fields is explained here.
Code Changes
We moved services/SearchEngine.js -> services/searchengine/SearchEngine.js
We also added two new modules in searchengine/
- filterParser.ts
- queryBuilder.ts
filterParser.ts
converts the query string into ‘terms’ of particular filter types.
eg. notebook:n1 abc -def
gets converted to terms
{
name: notebook
value: n1
negated: false
}
{
name: text
value: abc
negated: false
}
{
name: text
value: def
negated: true
}
queryBuilder.ts
takes these ‘terms’ and converts it into a valid SQL query.
The SQL query starts like this:
SELECT
notes_fts.id,
notes_fts.title,
offsets(notes_fts) AS offsets,
notes_fts.user_created_time,
notes_fts.user_updated_time,
notes_fts.is_todo,
notes_fts.todo_completed,
notes_fts.parent_id
FROM notes_fts
WHERE
Different conditions are appended to the base SQL query depending on the terms returned by filterParser()
eg. When we search for abc
the SQL query becomes
SELECT
notes_fts.id,
notes_fts.title,
offsets(notes_fts) AS offsets,
notes_fts.user_created_time,
notes_fts.user_updated_time,
notes_fts.is_todo,
notes_fts.todo_completed,
notes_fts.parent_id
FROM notes_fts
WHERE 1 AND notes_fts MATCH ?
with params = ["abc"]
So the existing text search won’t be slowed down because text search will get translated to an FTS match SQL query.
(It might actually be faster since we avoid the LEFT JOIN entirely)
Other filter conditions are appended to the SQL query as necessary using AND
/OR
.
AND|OR ROWID [NOT] IN (
ROWID of notes satisfying FILTER_CONDITION #N
)