LINE1 10 20 30 40 50 60 70 80 90 100 1101use std::fmt::Write as _;23use anyhow::{Context, Result};4use rusqlite::types::Value;5use rusqlite::{Connection, OptionalExtension, Row, params, params_from_iter};6use serde::{Deserialize, Serialize};7use uuid::Uuid;89use crate::attachments::{self, AttachmentInfo};10use crate::db::now;1112pub const MAX_TAG_LEN: usize = 64;13pub const MAX_METADATA_BYTES: usize = 64 * 1024;14const TITLE_LEN: usize = 80;1516#[derive(Debug, Clone, Serialize, Deserialize)]17pub struct Item {18 pub id: String,19 pub title: String,20 pub content: String,21 pub tags: Vec<String>,22 23 pub metadata: serde_json::Value,24 pub attachments: Vec<AttachmentInfo>,25 pub created_at: String,26 pub updated_at: String,27}2829#[derive(Debug, Clone, Serialize, Deserialize)]30pub struct TagCount {31 pub tag: String,32 pub count: i64,33}3435#[derive(Debug, Clone, Copy, PartialEq, Eq)]36pub enum Sort {37 Relevance,38 Created,39 Updated,40 Title,41}4243impl Sort {44 pub fn parse(value: &str) -> Option<Self> {45 match value {46 "relevance" => Some(Self::Relevance),47 "created" => Some(Self::Created),48 "updated" => Some(Self::Updated),49 "title" => Some(Self::Title),50 _ => None,51 }52 }53}5455#[derive(Debug, Clone)]56pub struct ListQuery {57 58 pub q: Option<String>,59 pub tag: Option<String>,60 61 pub metadata: Vec<(String, String)>,62 63 pub sort: Option<Sort>,64 pub ascending: bool,65 pub limit: i64,66 pub offset: i64,67}6869impl Default for ListQuery {70 fn default() -> Self {71 Self {72 q: None,73 tag: None,74 metadata: Vec::new(),75 sort: None,76 ascending: false,77 limit: 50,78 offset: 0,79 }80 }81}82838485pub fn normalize_tag(raw: &str) -> Result<String, String> {86 let tag = raw87 .trim()88 .trim_start_matches('#')89 .to_lowercase()90 .split_whitespace()91 .collect::<Vec<_>>()92 .join("-");93 if tag.is_empty() {94 return Err("tags cannot be empty".to_string());95 }96 if tag.chars().count() > MAX_TAG_LEN {97 return Err(format!("tags are limited to {MAX_TAG_LEN} characters"));98 }99 if !tag100 .chars()101 .all(|c| c.is_alphanumeric() || matches!(c, '-' | '_' | '.'))102 {103 return Err(format!(104 "tag {raw:?} may only contain letters, digits, '-', '_' and '.'"105 ));106 }107 Ok(tag)108}109110111pub fn normalize_tags(raw: &[String]) -> Result<Vec<String>, String> {112 let mut tags: Vec<String> = Vec::with_capacity(raw.len());113 for tag in raw {114 let tag = normalize_tag(tag)?;115 if !tags.contains(&tag) {116 tags.push(tag);117 }118 }119 Ok(tags)120}121122123pub fn valid_metadata_key(key: &str) -> bool {124 !key.is_empty()125 && key126 .chars()127 .all(|c| c.is_ascii_alphanumeric() || matches!(c, '_' | '-'))128}129130131132pub fn check_metadata(metadata: &serde_json::Value) -> Result<(), String> {133 let Some(object) = metadata.as_object() else {134 return Err("metadata must be a JSON object".to_string());135 };136 if let Some(key) = object.keys().find(|key| !valid_metadata_key(key)) {137 return Err(format!(138 "metadata key {key:?} may only contain letters, digits, '_' and '-'"139 ));140 }141 if metadata.to_string().len() > MAX_METADATA_BYTES {142 return Err(format!("metadata is limited to {MAX_METADATA_BYTES} bytes"));143 }144 Ok(())145}146147fn derive_title(content: &str) -> String {148 let line = content149 .lines()150 .map(str::trim)151 .find(|line| !line.is_empty())152 .unwrap_or_default();153 if line.chars().count() <= TITLE_LEN {154 line.to_string()155 } else {156 format!("{}…", line.chars().take(TITLE_LEN).collect::<String>())157 }158}159160pub fn create(161 connection: &mut Connection,162 title: &str,163 content: &str,164 tags: &[String],165 metadata: &serde_json::Value,166) -> Result<Item> {167 let id = Uuid::new_v4().to_string();168 let timestamp = now();169 let title = match title.trim() {170 "" => derive_title(content),171 title => title.to_string(),172 };173174 let transaction = connection.transaction()?;175 transaction.execute(176 "INSERT INTO items (id, title, content, metadata, created_at, updated_at)177 VALUES (?1, ?2, ?3, ?4, ?5, ?5)",178 params![id, title, content, metadata.to_string(), timestamp],179 )?;180 insert_tags(&transaction, &id, tags)?;181 refresh_search(&transaction, &id)?;182 let item = get(&transaction, &id)?.context("created item is missing")?;183 transaction.commit()?;184 Ok(item)185}186187pub fn get(connection: &Connection, id: &str) -> Result<Option<Item>> {188 let item = connection189 .query_row(190 "SELECT id, title, content, metadata, created_at, updated_at191 FROM items WHERE id = ?1",192 [id],193 item_from_row,194 )195 .optional()?;196 let Some(mut item) = item else {197 return Ok(None);198 };199 item.tags = tags_of(connection, &item.id)?;200 item.attachments = attachments::list_for_item(connection, &item.id)?;201 Ok(Some(item))202}203204pub fn list(connection: &Connection, query: &ListQuery) -> Result<Vec<Item>> {205 let mut params = Vec::new();206 let mut sql = String::from(207 "SELECT i.id, i.title, i.content, i.metadata, i.created_at, i.updated_at FROM items i",208 );209 let mut filters = Vec::new();210211 let search = query.q.as_deref().and_then(fts_expression);212 let has_search = search.is_some();213 if let Some(expression) = search {214 let placeholder = bind(&mut params, Value::Text(expression));215 sql.push_str(" JOIN items_fts ON items_fts.item_id = i.id");216 filters.push(format!("items_fts MATCH {placeholder}"));217 }218 if let Some(tag) = &query.tag {219 let placeholder = bind(&mut params, Value::Text(tag.clone()));220 filters.push(format!(221 "EXISTS (SELECT 1 FROM item_tags t WHERE t.item_id = i.id AND t.tag = {placeholder})"222 ));223 }224 for (key, value) in &query.metadata {225 226 let path = bind(&mut params, Value::Text(format!("$.{key}")));227 let expected = bind(&mut params, Value::Text(value.clone()));228 filters.push(format!(229 "CAST(json_extract(i.metadata, {path}) AS TEXT) = {expected}"230 ));231 }232 if !filters.is_empty() {233 sql.push_str(" WHERE ");234 sql.push_str(&filters.join(" AND "));235 }236237 let direction = if query.ascending { "ASC" } else { "DESC" };238 let order = match (query.sort, has_search) {239 (Some(Sort::Relevance) | None, true) => "items_fts.rank".to_string(),240 (Some(Sort::Created), _) => format!("i.created_at {direction}"),241 (Some(Sort::Title), _) => format!("LOWER(i.title) {direction}"),242 (Some(Sort::Relevance | Sort::Updated) | None, _) => {243 format!("i.updated_at {direction}")244 }245 };246 let limit = bind(&mut params, Value::Integer(query.limit));247 let offset = bind(&mut params, Value::Integer(query.offset));248 write!(sql, " ORDER BY {order}, i.id LIMIT {limit} OFFSET {offset}")?;249250 let mut statement = connection.prepare(&sql)?;251 let mut items = statement252 .query_map(params_from_iter(params), item_from_row)?253 .collect::<rusqlite::Result<Vec<Item>>>()?;254 for item in &mut items {255 item.tags = tags_of(connection, &item.id)?;256 item.attachments = attachments::list_for_item(connection, &item.id)?;257 }258 Ok(items)259}260261262pub fn update(263 connection: &mut Connection,264 id: &str,265 title: Option<&str>,266 content: Option<&str>,267 metadata: Option<&serde_json::Value>,268) -> Result<Option<Item>> {269 let transaction = connection.transaction()?;270 let Some(existing) = get(&transaction, id)? else {271 return Ok(None);272 };273 let title = title.unwrap_or(existing.title.as_str());274 let content = content.unwrap_or(existing.content.as_str());275 let metadata = metadata.unwrap_or(&existing.metadata).to_string();276 transaction.execute(277 "UPDATE items SET title = ?2, content = ?3, metadata = ?4, updated_at = ?5 WHERE id = ?1",278 params![id, title, content, metadata, now()],279 )?;280 refresh_search(&transaction, id)?;281 let item = get(&transaction, id)?;282 transaction.commit()?;283 Ok(item)284}285286pub fn delete(connection: &mut Connection, id: &str) -> Result<bool> {287 let transaction = connection.transaction()?;288 transaction.execute("DELETE FROM items_fts WHERE item_id = ?1", [id])?;289 let removed = transaction.execute("DELETE FROM items WHERE id = ?1", [id])?;290 transaction.commit()?;291 Ok(removed > 0)292}293294pub fn add_tags(connection: &mut Connection, id: &str, tags: &[String]) -> Result<Option<Item>> {295 let transaction = connection.transaction()?;296 if !exists(&transaction, id)? {297 return Ok(None);298 }299 insert_tags(&transaction, id, tags)?;300 refresh_search(&transaction, id)?;301 let item = get(&transaction, id)?;302 transaction.commit()?;303 Ok(item)304}305306pub fn remove_tag(connection: &mut Connection, id: &str, tag: &str) -> Result<Option<Item>> {307 let transaction = connection.transaction()?;308 if !exists(&transaction, id)? {309 return Ok(None);310 }311 transaction.execute(312 "DELETE FROM item_tags WHERE item_id = ?1 AND tag = ?2",313 params![id, tag],314 )?;315 refresh_search(&transaction, id)?;316 let item = get(&transaction, id)?;317 transaction.commit()?;318 Ok(item)319}320321pub fn list_tags(connection: &Connection) -> Result<Vec<TagCount>> {322 let mut statement =323 connection.prepare("SELECT tag, COUNT(*) FROM item_tags GROUP BY tag ORDER BY tag")?;324 let tags = statement325 .query_map([], |row| {326 Ok(TagCount {327 tag: row.get(0)?,328 count: row.get(1)?,329 })330 })?331 .collect::<rusqlite::Result<Vec<TagCount>>>()?;332 Ok(tags)333}334335fn exists(connection: &Connection, id: &str) -> Result<bool> {336 Ok(connection.query_row(337 "SELECT EXISTS (SELECT 1 FROM items WHERE id = ?1)",338 [id],339 |row| row.get(0),340 )?)341}342343fn insert_tags(connection: &Connection, id: &str, tags: &[String]) -> Result<()> {344 for tag in tags {345 connection.execute(346 "INSERT OR IGNORE INTO item_tags (item_id, tag) VALUES (?1, ?2)",347 params![id, tag],348 )?;349 }350 Ok(())351}352353fn tags_of(connection: &Connection, id: &str) -> Result<Vec<String>> {354 let mut statement =355 connection.prepare("SELECT tag FROM item_tags WHERE item_id = ?1 ORDER BY tag")?;356 let tags = statement357 .query_map([id], |row| row.get(0))?358 .collect::<rusqlite::Result<Vec<String>>>()?;359 Ok(tags)360}361362363364pub fn refresh_search(connection: &Connection, id: &str) -> Result<()> {365 connection.execute("DELETE FROM items_fts WHERE item_id = ?1", [id])?;366 connection.execute(367 "INSERT INTO items_fts (item_id, title, content, tags, files)368 SELECT i.id, i.title, i.content,369 COALESCE((SELECT group_concat(t.tag, ' ') FROM item_tags t WHERE t.item_id = i.id), ''),370 COALESCE((SELECT group_concat(a.filename, ' ') FROM attachments a WHERE a.item_id = i.id), '')371 FROM items i372 WHERE i.id = ?1",373 [id],374 )?;375 Ok(())376}377378379380fn fts_expression(input: &str) -> Option<String> {381 let terms: Vec<String> = input382 .split_whitespace()383 .map(|term| format!("\"{}\"*", term.replace('"', "\"\"")))384 .collect();385 (!terms.is_empty()).then(|| terms.join(" "))386}387388fn bind(params: &mut Vec<Value>, value: Value) -> String {389 params.push(value);390 format!("?{}", params.len())391}392393fn item_from_row(row: &Row<'_>) -> rusqlite::Result<Item> {394 let metadata_text: String = row.get(3)?;395 let metadata = serde_json::from_str(&metadata_text).map_err(|error| {396 rusqlite::Error::FromSqlConversionFailure(3, rusqlite::types::Type::Text, Box::new(error))397 })?;398 Ok(Item {399 id: row.get(0)?,400 title: row.get(1)?,401 content: row.get(2)?,402 tags: Vec::new(),403 metadata,404 attachments: Vec::new(),405 created_at: row.get(4)?,406 updated_at: row.get(5)?,407 })408}409410#[cfg(test)]411mod tests {412 use serde_json::json;413414 use super::*;415 use crate::db::open_in_memory;416417 fn tags(values: &[&str]) -> Vec<String> {418 values.iter().map(|value| (*value).to_string()).collect()419 }420421 #[test]422 fn tags_are_normalized_and_validated() {423 assert_eq!(normalize_tag(" #Rust Lang ").unwrap(), "rust-lang");424 assert_eq!(normalize_tag("v1.2_beta").unwrap(), "v1.2_beta");425 assert!(normalize_tag(" ").is_err());426 assert!(normalize_tag("a/b").is_err());427 assert!(normalize_tag(&"x".repeat(MAX_TAG_LEN + 1)).is_err());428 assert_eq!(429 normalize_tags(&tags(&["Rust", "rust", "web"])).unwrap(),430 tags(&["rust", "web"])431 );432 }433434 #[test]435 fn metadata_must_be_a_bounded_object() {436 assert!(check_metadata(&json!({"source": "slack"})).is_ok());437 assert!(check_metadata(&json!(["not", "an", "object"])).is_err());438 assert!(check_metadata(&json!({"big": "x".repeat(MAX_METADATA_BYTES)})).is_err());439 }440441 #[test]442 fn create_derives_title_and_round_trips() {443 let mut connection = open_in_memory().unwrap();444 let item = create(445 &mut connection,446 "",447 "\n\n First line here\nsecond",448 &tags(&["notes"]),449 &json!({"source": "test"}),450 )451 .unwrap();452 assert_eq!(item.title, "First line here");453 assert_eq!(item.tags, tags(&["notes"]));454 assert_eq!(item.metadata, json!({"source": "test"}));455456 let fetched = get(&connection, &item.id).unwrap().unwrap();457 assert_eq!(fetched.content, "\n\n First line here\nsecond");458 }459460 #[test]461 fn search_matches_prefixes_and_tags_and_ignores_punctuation() {462 let mut connection = open_in_memory().unwrap();463 let rust = create(464 &mut connection,465 "Ownership",466 "borrow checker notes",467 &tags(&["rust"]),468 &json!({}),469 )470 .unwrap();471 create(472 &mut connection,473 "Recipe",474 "flour and eggs",475 &tags(&["food"]),476 &json!({}),477 )478 .unwrap();479480 let found = |q: &str| {481 let query = ListQuery {482 q: Some(q.to_string()),483 ..ListQuery::default()484 };485 list(&connection, &query)486 .unwrap()487 .into_iter()488 .map(|item| item.id)489 .collect::<Vec<_>>()490 };491 assert_eq!(found("borr"), vec![rust.id.clone()]);492 assert_eq!(found("rust"), vec![rust.id.clone()]);493 assert_eq!(found("flour borrow"), Vec::<String>::new());494 assert_eq!(found("\"unbalanced"), Vec::<String>::new());495 }496497 #[test]498 fn tag_filter_and_tag_counts() {499 let mut connection = open_in_memory().unwrap();500 create(&mut connection, "a", "one", &tags(&["x", "y"]), &json!({})).unwrap();501 create(&mut connection, "b", "two", &tags(&["x"]), &json!({})).unwrap();502503 let query = ListQuery {504 tag: Some("y".to_string()),505 ..ListQuery::default()506 };507 assert_eq!(list(&connection, &query).unwrap().len(), 1);508509 let counts = list_tags(&connection).unwrap();510 assert_eq!(counts[0].tag, "x");511 assert_eq!(counts[0].count, 2);512 }513514 #[test]515 fn metadata_filters_match_top_level_keys_as_text() {516 let mut connection = open_in_memory().unwrap();517 create(518 &mut connection,519 "a",520 "one",521 &[],522 &json!({"source": "slack", "priority": 2}),523 )524 .unwrap();525 create(526 &mut connection,527 "b",528 "two",529 &[],530 &json!({"source": "email"}),531 )532 .unwrap();533534 let filter = |pairs: &[(&str, &str)]| ListQuery {535 metadata: pairs536 .iter()537 .map(|(k, v)| ((*k).to_string(), (*v).to_string()))538 .collect(),539 ..ListQuery::default()540 };541 let titles = |query: &ListQuery| {542 list(&connection, query)543 .unwrap()544 .into_iter()545 .map(|item| item.title)546 .collect::<Vec<_>>()547 };548 assert_eq!(titles(&filter(&[("source", "slack")])), vec!["a"]);549 assert_eq!(titles(&filter(&[("priority", "2")])), vec!["a"]);550 assert_eq!(551 titles(&filter(&[("source", "slack"), ("priority", "3")])),552 Vec::<String>::new()553 );554 }555556 #[test]557 fn update_add_remove_and_delete_keep_search_in_sync() {558 let mut connection = open_in_memory().unwrap();559 let item = create(&mut connection, "t", "old body", &[], &json!({})).unwrap();560561 update(&mut connection, &item.id, None, Some("fresh body"), None)562 .unwrap()563 .unwrap();564 let query = |q: &str| ListQuery {565 q: Some(q.to_string()),566 ..ListQuery::default()567 };568 assert!(list(&connection, &query("old")).unwrap().is_empty());569 assert_eq!(list(&connection, &query("fresh")).unwrap().len(), 1);570571 add_tags(&mut connection, &item.id, &tags(&["later"]))572 .unwrap()573 .unwrap();574 assert_eq!(list(&connection, &query("later")).unwrap().len(), 1);575576 remove_tag(&mut connection, &item.id, "later")577 .unwrap()578 .unwrap();579 assert!(list(&connection, &query("later")).unwrap().is_empty());580581 assert!(delete(&mut connection, &item.id).unwrap());582 assert!(list(&connection, &query("fresh")).unwrap().is_empty());583 assert!(get(&connection, &item.id).unwrap().is_none());584 }585586 #[test]587 fn update_changes_only_given_fields() {588 let mut connection = open_in_memory().unwrap();589 let item = create(&mut connection, "keep", "body", &[], &json!({"a": 1})).unwrap();590 let changed = update(591 &mut connection,592 &item.id,593 None,594 None,595 Some(&json!({"b": 2})),596 )597 .unwrap()598 .unwrap();599 assert_eq!(changed.title, "keep");600 assert_eq!(changed.content, "body");601 assert_eq!(changed.metadata, json!({"b": 2}));602 }603604 #[test]605 fn missing_items_report_none() {606 let mut connection = open_in_memory().unwrap();607 assert!(608 update(&mut connection, "nope", Some("x"), None, None)609 .unwrap()610 .is_none()611 );612 assert!(613 add_tags(&mut connection, "nope", &tags(&["x"]))614 .unwrap()615 .is_none()616 );617 assert!(!delete(&mut connection, "nope").unwrap());618 }619}