commit f1647057e14243a9e3d5b0e0151fd2ec6ee92842
parent e57ad4bcdeca11e8c57bf6e874ea0d8adb511c09
Author: Silas Brack <silasbrack@gmail.com>
Date: Sun, 7 Jun 2026 19:09:13 +0200
feat: use SQLite FTS5 for search instead of LIKE
Query the story_fts table directly via rusqlite for proper
full-text search with ranking. Searches both title and text.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
5 files changed, 113 insertions(+), 9 deletions(-)
diff --git a/Cargo.lock b/Cargo.lock
@@ -538,6 +538,18 @@ dependencies = [
]
[[package]]
+name = "fallible-iterator"
+version = "0.3.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "2acce4a10f12dc2fb14a218589d4f1f62ef011b2d0cc4b3cb1bba8e94da14649"
+
+[[package]]
+name = "fallible-streaming-iterator"
+version = "0.1.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7360491ce676a36bf9bb3c56c1aa791658183a54d2744120f27285738d90465a"
+
+[[package]]
name = "fastrand"
version = "2.4.1"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -572,6 +584,12 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "3f9eec918d3f24069decb9af1554cad7c880e2da24a9afd88aca000531ab82c1"
[[package]]
+name = "foldhash"
+version = "0.1.5"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "d9c4f5dac5e15c24eb999c26181a6ca40b39fe946cbe4c263c7209467bc83af2"
+
+[[package]]
name = "foreign-types"
version = "0.3.2"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -744,11 +762,29 @@ dependencies = [
[[package]]
name = "hashbrown"
+version = "0.15.5"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "9229cfe53dfd69f0609a49f65461bd93001ea1ef889cd5529dd176593f5338a1"
+dependencies = [
+ "foldhash",
+]
+
+[[package]]
+name = "hashbrown"
version = "0.17.0"
source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "4f467dd6dccf739c208452f8014c75c18bb8301b050ad1cfb27153803edb0f51"
[[package]]
+name = "hashlink"
+version = "0.10.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7382cf6263419f2d8df38c55d7da83da5c18aef87fc7a7fc1fb1e344edfe14c1"
+dependencies = [
+ "hashbrown 0.15.5",
+]
+
+[[package]]
name = "hkdf"
version = "0.12.4"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -1032,7 +1068,7 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "d466e9454f08e4a911e14806c24e16fba1b4c121d1ea474396f396069cf949d9"
dependencies = [
"equivalent",
- "hashbrown",
+ "hashbrown 0.17.0",
]
[[package]]
@@ -1166,6 +1202,17 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "b6d2cec3eae94f9f509c767b45932f1ada8350c4bdb85af2fcab4a3c14807981"
[[package]]
+name = "libsqlite3-sys"
+version = "0.32.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "fbb8270bb4060bd76c6e96f20c52d80620f1d82a3470885694e41e0f81ef6fe7"
+dependencies = [
+ "cc",
+ "pkg-config",
+ "vcpkg",
+]
+
+[[package]]
name = "linux-raw-sys"
version = "0.12.1"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -1779,6 +1826,20 @@ dependencies = [
]
[[package]]
+name = "rusqlite"
+version = "0.34.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "37e34486da88d8e051c7c0e23c3f15fd806ea8546260aa2fec247e97242ec143"
+dependencies = [
+ "bitflags",
+ "fallible-iterator",
+ "fallible-streaming-iterator",
+ "hashlink",
+ "libsqlite3-sys",
+ "smallvec",
+]
+
+[[package]]
name = "rustc-hash"
version = "2.1.2"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -2073,6 +2134,7 @@ dependencies = [
"axum-extra",
"chrono",
"reqwest 0.12.28",
+ "rusqlite",
"serde",
"serde_json",
"thiserror 2.0.18",
diff --git a/Cargo.toml b/Cargo.toml
@@ -14,5 +14,6 @@ serde_json = "1"
thiserror = "2"
tracing = "0.1"
reqwest = { version = "0.12", features = ["json"] }
+rusqlite = { version = "0.34", features = ["bundled"] }
trailbase-client = "0.8"
url = "2"
diff --git a/src/config.rs b/src/config.rs
@@ -4,6 +4,7 @@ pub struct Config {
pub trailbase_url: String,
pub debug: bool,
pub port: u16,
+ pub db_path: String,
}
impl Config {
@@ -20,10 +21,14 @@ impl Config {
.and_then(|v| v.parse().ok())
.unwrap_or(8000);
+ let db_path = env::var("DB_PATH")
+ .unwrap_or_else(|_| "traildepot/main.db".to_string());
+
Self {
trailbase_url,
debug,
port,
+ db_path,
}
}
}
diff --git a/src/handlers/search.rs b/src/handlers/search.rs
@@ -7,7 +7,7 @@ use crate::error::AppError;
use crate::handlers::auth::get_tokens_from_cookies;
use crate::state::AppState;
use crate::templates::{ApplicationTemplate, SearchResultsTemplate, SearchTemplate};
-use crate::trailbase;
+use crate::trailbase::SearchResult;
pub async fn open_search(jar: CookieJar) -> Result<Html<String>, AppError> {
let is_logged_in = get_tokens_from_cookies(&jar).is_some();
@@ -25,20 +25,54 @@ pub struct SearchForm {
pub async fn search(
State(state): State<AppState>,
- jar: CookieJar,
+ _jar: CookieJar,
Form(form): Form<SearchForm>,
) -> Result<Html<String>, AppError> {
- let client = match get_tokens_from_cookies(&jar) {
- Some(tokens) => trailbase::client_with_tokens(&state.trailbase_url, &tokens)?,
- None => trailbase::new_client(&state.trailbase_url)?,
- };
-
let results = if form.search.is_empty() {
Vec::new()
} else {
- trailbase::search_stories(&client, &form.search, 20).await?
+ let db_path = state.db_path.clone();
+ let query = form.search.clone();
+ tokio::task::spawn_blocking(move || fts_search(&db_path, &query, 20))
+ .await
+ .map_err(|e| AppError::Api(crate::trailbase::ClientError::Auth(format!("Search task failed: {}", e))))?
+ .map_err(|e| AppError::Api(crate::trailbase::ClientError::Auth(format!("Search failed: {}", e))))?
};
let template = SearchResultsTemplate { results };
Ok(Html(template.render()?))
}
+
+fn fts_search(db_path: &str, query: &str, limit: usize) -> Result<Vec<SearchResult>, String> {
+ let conn = rusqlite::Connection::open_with_flags(
+ db_path,
+ rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY | rusqlite::OpenFlags::SQLITE_OPEN_NO_MUTEX,
+ ).map_err(|e| format!("Failed to open database: {}", e))?;
+
+ let mut stmt = conn.prepare(
+ "SELECT s.id, s.title, s.text, s.score, s.comment_count
+ FROM story_fts
+ JOIN story s ON s.id = story_fts.rowid
+ WHERE story_fts MATCH ?1
+ ORDER BY rank
+ LIMIT ?2"
+ ).map_err(|e| format!("Query prepare failed: {}", e))?;
+
+ let rows = stmt.query_map(rusqlite::params![query, limit], |row| {
+ Ok(SearchResult {
+ id: row.get(0)?,
+ title: row.get(1)?,
+ text: row.get(2)?,
+ score: row.get(3)?,
+ comment_count: row.get(4)?,
+ })
+ }).map_err(|e| format!("Query failed: {}", e))?;
+
+ let mut results = Vec::new();
+ for row in rows {
+ if let Ok(r) = row {
+ results.push(r);
+ }
+ }
+ Ok(results)
+}
diff --git a/src/state.rs b/src/state.rs
@@ -4,6 +4,7 @@ use crate::config::Config;
pub struct AppState {
pub trailbase_url: String,
pub http_client: reqwest::Client,
+ pub db_path: String,
}
impl AppState {
@@ -11,6 +12,7 @@ impl AppState {
Self {
trailbase_url: config.trailbase_url.clone(),
http_client: reqwest::Client::new(),
+ db_path: config.db_path.clone(),
}
}
}
