Better Search

[0xcaff]

Currently search uses a simple LIKE"%query%" for queries. We should switch to full text search so that search works better. It will also be needed for implementing forte-music/schema#51 when it lands.

There doesn't seem to be SQlite full text search support in diesel. Here are some issues I've encountered while looking into this.

• SQLite FTS5 Virtual Table Default Primary Key diesel-rs/diesel#1748
• How would a sql function like bm25 which takes a table as an argument be declared in diesel?

[0xcaff]

Here's a minimal example of using full text search:

main.rs

#[macro_use]
extern crate diesel;

mod database;

use database::email;
use diesel::prelude::*;

fn main() {
    let conn = SqliteConnection::establish("./test.sqlite").unwrap();

    let values = email::table
        .select((email::rowid, email::rank))
        .filter(email::whole_col.eq("john"))
        .first::<(i32, f32)>(&conn)
        .unwrap();

    println!("{:?}", values);
}

database.rs

table! {
    email(rowid) {
        rowid -> Integer,

        #[sql_name = "email"]
        whole_col -> Text,
        rank -> Float,
    }
}

up.sql

CREATE VIRTUAL TABLE email USING fts5(sender, title, body);

INSERT INTO email (sender, title, body) VALUES ('John Doe', 'Hello World', 'Welcome john to the land of the awesome');

Running

/usr/bin/cargo run --color=always --package diesel-fts --bin diesel-fts
   Compiling diesel-fts v0.1.0 (file:///home/me/projects/diesel-fts)
    Finished dev [unoptimized + debuginfo] target(s) in 1.39 secs
     Running `target/debug/diesel-fts`
(1, -0.000001375)

Process finished with exit code 0

[0xcaff]

Search is just a simple problem.

https://www.elastic.co/blog/found-elasticsearch-from-the-bottom-up

https://github.com/tantivy-search/tantivy