Skip to content
1        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    /// Free-form JSON object supplied by the caller (for example `{"source": "slack"}`).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    /// Full-text query. Each word is matched as a prefix and all words must match.58    pub q: Option<String>,59    pub tag: Option<String>,60    /// Exact-match filters on top-level metadata keys, compared as text.61    pub metadata: Vec<(String, String)>,62    /// `None` means relevance when `q` is set, otherwise last updated.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}8283/// Lowercases and trims a tag, turning inner whitespace into `-`.84/// Returns a message suitable for showing to the caller when the tag is unusable.85pub 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}109110/// Normalizes and de-duplicates tags, keeping the first occurrence order.111pub 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}121122/// Metadata keys are plain identifiers, so the same names work as `meta.<key>` filters.123pub fn valid_metadata_key(key: &str) -> bool {124    !key.is_empty()125        && key126            .chars()127            .all(|c| c.is_ascii_alphanumeric() || matches!(c, '_' | '-'))128}129130/// Metadata must be a JSON object with identifier keys, of bounded size.131/// Returns the message for the caller.132pub 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        // The JSON path and the value are both bound parameters, so keys cannot inject SQL.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}260261/// Changes only the fields that are given. Returns `None` when the item does not exist.262pub 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}361362/// Rebuilds the search row for one item so it always mirrors the item, its tags, and363/// the filenames of its attachments.364pub 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}377378/// Turns free text into an FTS5 expression: every word is quoted (so punctuation379/// cannot break the query syntax) and matched as a prefix.380fn 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}