Etalase – pustaka musik
100 000 trek, indeks, pencarian teks penuh, dan hitungan berkelompok dalam satu program Rust yang singkat.
Apa yang dilakukannya
Program membuat tabel trek (judul, artis, tahun, durasi) dan indeks pada artis, menghasilkan 100 000 trek dari seed tetap, mengisi tabel teks penuh dengan tokenizer trigram, lalu menjawab tiga pencarian dan satu kueri berkelompok.
Langkah
- Buat tabel, indeks, dan tabel teks penuh.
- Sisipkan 100 000 trek dalam satu transaksi dengan parameter terikat.
- Isi tabel teks penuh dari trek dalam satu pernyataan.
- Cari dengan MATCH, misalnya sebuah frasa, dua kata yang digabung dengan AND, dan awalan kata.
- Hitung trek per artis dengan GROUP BY.
Pencariannya
// 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(())
}
Jalankan
Keluarannya sama di setiap eksekusi karena datanya berasal dari seed tetap. Waktu eksekusi ditampilkan di keluaran kesalahan.
cd examples/rust
cargo run --release --bin musiclib
Keluaran
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
Waktu eksekusi
Satu kali jalan, tanpa perbandingan dengan basis data lain, sementara.
| Sisipkan 100 000 trek dan isi tabel teks penuh | 2,0 s |
|---|---|
| Cari sebuah frasa (463 hasil) | 4,5 ms |
| Cari dua kata dengan AND (509 hasil) | 6,9 ms |
| Cari awalan kata (6299 hasil) | 11,4 ms |
Dukungan teks penuh saat ini adalah tokenizer trigram pada tabel tanpa konten. Tokenizer lain dan fungsi peringkat belum dibangun.