Home / GitHub Page

Documentation for the search filter PR

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/

  1. filterParser.ts
  2. 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
)
3 Likes