PSOrvia

Showcase – a music library

100 000 tracks, an index, full-text search and grouped counts in one short Rust program.

What it does

The program creates a table of tracks (title, artist, year, length) and an index on the artist, generates 100 000 tracks from a fixed seed, fills a full-text table with a trigram tokenizer and answers three searches and one grouped query.

Steps

  1. Create the table, the index and the full-text table.
  2. Insert 100 000 tracks in one transaction with bound parameters.
  3. Fill the full-text table from the tracks in one statement.
  4. Search with MATCH, for example a phrase, two words combined with AND, and a prefix of a word.
  5. Count tracks per artist with GROUP BY.

The search

// SPDX-License-Identifier: Apache-2.0
// Showcase: a music library of 100 000 tracks with full-text search.
// Usage: musiclib [tracks] [file]   (defaults: 100000 tracks, a temporary file)
use std::time::Instant;

use psob::encoding::key::Value;
use psob::sql::conn::Connection;
use psob::sql::eval::to_text;
use psob::sql::exec::Params;
use psob::{Database, OpenOptions};

const ADJ: [&str; 16] = [
    "Quiet", "Golden", "Broken", "Electric", "Hidden", "Silver", "Midnight", "Wild", "Slow", "Paper", "Distant",
    "Velvet", "Burning", "Frozen", "Lonely", "Bright",
];
const NOUN: [&str; 16] = [
    "River", "Garden", "Machine", "Harbor", "Letter", "Mirror", "Engine", "Forest", "Window", "Season", "Signal",
    "Island", "Morning", "Shadow", "Bridge", "Orchard",
];
const ARTIST: [&str; 12] = [
    "Anna Keller", "The Lanterns", "Mo Okafor", "Blue Harbor", "Ines Duarte", "Kite Season", "Sven Lindqvist",
    "Tomoko Ito", "Orchard Club", "Lena Voss", "North Wing", "Paper Moons",
];

struct Lcg(u64);
impl Lcg {
    fn next(&mut self, n: usize) -> usize {
        self.0 = self.0.wrapping_mul(6364136223846793005).wrapping_add(1442695040888963407);
        ((self.0 >> 33) as usize) % n
    }
}

fn text(s: &str) -> Value {
    Value::Text(s.to_string())
}

fn main() -> psob::Result<()> {
    let args: Vec<String> = std::env::args().collect();
    let n: usize = args.get(1).and_then(|s| s.parse().ok()).unwrap_or(100_000);
    let path = match args.get(2) {
        Some(p) => std::path::PathBuf::from(p),
        None => std::env::temp_dir().join(format!("psob-musiclib-{}.db", std::process::id())),
    };
    let _ = std::fs::remove_file(&path);
    let db = Database::open(&path, OpenOptions::default())?;
    let mut conn = Connection::with_mode(db, psob::sql::SqlMode::Sqlite)?;
    conn.execute("CREATE TABLE tracks(id INTEGER PRIMARY KEY, title TEXT NOT NULL, artist TEXT NOT NULL, year INT, seconds INT)")?;
    conn.execute("CREATE INDEX tracks_artist ON tracks(artist)")?;
    conn.execute("CREATE VIRTUAL TABLE search USING fts5(title, artist, tokenize = 'trigram remove_diacritics 1', content = '')")?;

    let t0 = Instant::now();
    let mut rng = Lcg(42);
    conn.execute("BEGIN")?;
    for i in 1..=n {
        let title = format!("{} {} {}", ADJ[rng.next(16)], NOUN[rng.next(16)], i);
        let artist = ARTIST[rng.next(12)];
        let p = Params {
            positional: vec![
                Value::Integer(i as i64),
                text(&title),
                text(artist),
                Value::Integer(1960 + rng.next(66) as i64),
                Value::Integer(120 + rng.next(300) as i64),
            ],
            named: vec![],
        };
        conn.execute_with("INSERT INTO tracks VALUES (?1, ?2, ?3, ?4, ?5)", &p)?;
    }
    conn.execute("INSERT INTO search(rowid, title, artist) SELECT id, title, artist FROM tracks")?;
    conn.execute("COMMIT")?;
    let load = t0.elapsed();

    let count = conn.query("SELECT count(*) FROM tracks")?;
    println!("tracks: {}", to_text(&count.rows[0][0]));

    for q in ["velvet bridge", "orchard AND kite", "froz"] {
        let t = Instant::now();
        let p = Params { positional: vec![text(q)], named: vec![] };
        let r = conn.query_with(
            "SELECT count(*) FROM search WHERE search MATCH ?1",
            &p,
        )?;
        let hits = to_text(&r.rows[0][0]);
        let top = conn.query_with(
            "SELECT t.title, t.artist FROM search JOIN tracks t ON t.id = search.rowid WHERE search MATCH ?1 ORDER BY t.id LIMIT 1",
            &p,
        )?;
        println!("search '{}': {} hits, first: {} / {}", q, hits, to_text(&top.rows[0][0]), to_text(&top.rows[0][1]));
        eprintln!("  ({:.1} ms)", t.elapsed().as_secs_f64() * 1000.0);
    }
    let by = conn.query("SELECT artist, count(*) AS n FROM tracks GROUP BY artist ORDER BY n DESC, artist LIMIT 3")?;
    for row in &by.rows {
        println!("artist {} has {} tracks", to_text(&row[0]), to_text(&row[1]));
    }
    eprintln!("loaded and indexed in {:.2} s", load.as_secs_f64());
    drop(conn);
    if args.get(2).is_none() {
        let _ = std::fs::remove_file(&path);
    }
    Ok(())
}

Run it

The output is the same on every run because the data comes from a fixed seed. Timings go to the error output.

cd examples/rust
cargo run --release --bin musiclib

Output

tracks: 100000
search 'velvet bridge': 463 hits, first: Velvet Bridge 129 / Mo Okafor
search 'orchard AND kite': 509 hits, first: Golden Orchard 133 / Kite Season
search 'froz': 6299 hits, first: Frozen Bridge 6 / Sven Lindqvist
artist Paper Moons has 8448 tracks
artist Orchard Club has 8447 tracks
artist Sven Lindqvist has 8433 tracks

Timings

Single run, no comparison with other databases, preliminary.

Insert 100 000 tracks and fill the full-text table2.0 s
Search for a phrase (463 hits)4.5 ms
Search for two words with AND (509 hits)6.9 ms
Search for a word prefix (6299 hits)11.4 ms

Full-text support today is the trigram tokenizer on contentless tables. Other tokenizers and ranking functions are not built yet.