//! Per-user baskets: a full column of values a user may edit, kept in their //! own SQLite file so MariaDB is never written to. //! //! A basket is a *named column* -- a trait's values, or a covariate's -- that //! a user can change and then map. The mapping endpoints already take values //! per sample (`samples`), so a basket is storage, not a new compute path: //! [`resolve`] hands back the same `(name, value)` pairs the client would //! otherwise have posted. //! //! Storage choices, and why: //! //! * **Full vector, not a diff.** The caller keeps the original alongside the //! edited column, so the comparison is theirs to make; the server keeps one //! column, which is what a mapping needs and what an export should contain. //! * **Text, with a parsed copy beside it.** `raw` is what the user typed //! (`5.2`, `NA`, `x`), `value` is the number or NULL, and `status` says //! which. Parsing once, at creation, is what lets a basket be *refused* with //! a line number instead of silently mapping something the user did not //! mean -- `NA` and `0` are different claims, as GEMMA's own handling of //! them showed. //! * **A revision and a digest.** A basket is mutable, so every mapping names //! the revision it used and the reply carries the digest: two runs on one //! name must not be able to differ silently. //! //! One SQLite file per user (`/.sqlite`, 0600, WAL), //! created on first write. The `sub` from the token is the owner: tokens //! rotate, users do not. use std::path::{Path, PathBuf}; use rusqlite::{Connection, OptionalExtension, params}; use crate::indexer::sha256::hex_digest; /// What a basket holds: a trait's column or a covariate's. pub const KINDS: [&str; 2] = ["trait", "covariate"]; /// Text that means "no value" rather than "a number". The same set the /// compute endpoints accept, plus what spreadsheets export. const MISSING: [&str; 9] = ["", "na", "n/a", "null", "nan-ish", "x", "-", ".", "?"]; /// One cell: what was typed, what it parsed to, and which of the two it is. #[derive(Debug, Clone, serde::Serialize, serde::Deserialize, utoipa::ToSchema)] pub struct Cell { pub sample: String, /// As the user gave it. `null` means an explicit missing. #[serde(skip_serializing_if = "Option::is_none")] pub raw: Option, /// The parsed number, when it is one. #[serde(skip_serializing_if = "Option::is_none")] pub value: Option, /// `ok`, `missing` or `bad`. pub status: String, } /// A basket without its column (the listing shape). #[derive(Debug, Clone, serde::Serialize, utoipa::ToSchema)] pub struct BasketInfo { pub name: String, /// `trait` or `covariate`. pub kind: String, /// Where the column came from, when it came from a published trait. #[serde(skip_serializing_if = "Option::is_none")] pub base_dataset: Option, #[serde(skip_serializing_if = "Option::is_none")] pub base_trait: Option, #[serde(skip_serializing_if = "Option::is_none")] pub description: Option, /// Bumped by every edit; a mapping names the one it used. pub revision: i64, /// sha256 over (name, revision, sample, raw) -- the identity of these /// values, so a reply can be reproduced after an edit. pub digest: String, /// Cells carrying a number, and cells marked missing. pub values: usize, pub missing: usize, pub created_at: i64, pub updated_at: i64, } /// One stored revision: what was saved, when, and how big. #[derive(Debug, Clone, serde::Serialize, serde::Deserialize, utoipa::ToSchema)] pub struct Version { /// 1 for the column as first stored, +1 per edit. pub revision: i64, /// Identifies these exact values. pub digest: String, /// Unix seconds (the API reports Unix time everywhere). pub created_at: i64, pub values: usize, pub missing: usize, /// The basket's description, when this revision was made. #[serde(skip_serializing_if = "Option::is_none")] pub description: Option, } /// Why a basket operation failed. #[derive(Debug)] pub enum BasketError { /// No basket with that name for this user. NotFound(String), /// The request could not be stored as asked (a bad name, a bad value). Invalid(String), /// The store itself failed. Store(String), } impl std::fmt::Display for BasketError { fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { match self { BasketError::NotFound(what) | BasketError::Invalid(what) | BasketError::Store(what) => { write!(f, "{what}") } } } } fn now() -> i64 { std::time::SystemTime::now() .duration_since(std::time::UNIX_EPOCH) .map(|elapsed| elapsed.as_secs() as i64) .unwrap_or(0) } /// A filesystem-safe form of a user's subject: letters, digits, `-`, `_`, /// `.`; anything else becomes `_`. Empty means the caller passed nothing. pub fn file_stem(owner: &str) -> String { let stem: String = owner .trim() .chars() .map(|character| { if character.is_ascii_alphanumeric() || matches!(character, '-' | '_' | '.') { character } else { '_' } }) .collect(); stem.trim_matches('.').to_string() } /// The per-user database file: `/.sqlite`, so an operator /// can see whose baskets a file holds and a token rotation does not orphan /// them -- the token changes, the `sub` does not. /// /// A subject that is not safe as a filename is sanitised, and when sanitising /// changed anything a short digest of the original is appended, so two users /// cannot collide on one file (`a/b` and `a_b`). pub fn database(dir: &Path, owner: &str) -> PathBuf { let stem = file_stem(owner); let stem = if stem.is_empty() { format!("user-{}", &hex_digest(owner.as_bytes())[..16]) } else if stem != owner.trim() { format!("{stem}-{}", &hex_digest(owner.as_bytes())[..16]) } else { stem }; let path = dir.join(format!("{stem}.sqlite")); // Baskets written before the file was named (when it was a bare hash of // the subject) are moved on first use rather than orphaned. if !path.exists() { let legacy = dir.join(format!("{}.sqlite", hex_digest(owner.as_bytes()))); // A closed connection checkpoints its WAL, so the main file is the // only one that has to travel. if legacy.exists() { let _ = std::fs::rename(&legacy, &path); } } path } /// Parse one cell. Returns the parsed number, or `None` for a missing value, /// and the status to record. pub fn parse(raw: Option<&str>) -> (Option, &'static str) { let Some(raw) = raw else { return (None, "missing") }; let trimmed = raw.trim(); if MISSING.contains(&trimmed.to_lowercase().as_str()) { return (None, "missing"); } match trimmed.parse::() { // A NaN or an infinity is not a measurement; refuse it by name. Ok(value) if value.is_finite() => (Some(value), "ok"), _ => (None, "bad"), } } /// A basket name must be usable in a URL and readable in a reply. pub fn validate_name(name: &str) -> Result<(), BasketError> { let name = name.trim(); if name.is_empty() || name.len() > 64 { return Err(BasketError::Invalid( "a basket name must be 1 to 64 characters".to_string(), )); } if !name .chars() .all(|c| c.is_ascii_alphanumeric() || matches!(c, '-' | '_' | '.')) { return Err(BasketError::Invalid(format!( "'{name}': a basket name may hold letters, digits, '-', '_' and '.'" ))); } Ok(()) } pub(crate) fn open(dir: &Path, owner: &str) -> Result { std::fs::create_dir_all(dir) .map_err(|error| BasketError::Store(format!("{}: {error}", dir.display())))?; let path = database(dir, owner); let connection = Connection::open(&path) .map_err(|error| BasketError::Store(format!("{}: {error}", path.display())))?; // One writer per user, and a reader must not fail because of it. connection .busy_timeout(std::time::Duration::from_secs(5)) .and_then(|_| connection.pragma_update(None, "journal_mode", "WAL")) .and_then(|_| connection.pragma_update(None, "foreign_keys", "ON")) .map_err(|error| BasketError::Store(format!("{}: {error}", path.display())))?; connection .execute_batch( "CREATE TABLE IF NOT EXISTS basket ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, kind TEXT NOT NULL, base_dataset TEXT, base_trait INTEGER, description TEXT, revision INTEGER NOT NULL, digest TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS basket_version ( id INTEGER PRIMARY KEY, basket_id INTEGER NOT NULL REFERENCES basket(id) ON DELETE CASCADE, revision INTEGER NOT NULL, digest TEXT NOT NULL, created_at INTEGER NOT NULL, values_count INTEGER NOT NULL, missing_count INTEGER NOT NULL, description TEXT, UNIQUE(basket_id, revision) ); CREATE TABLE IF NOT EXISTS genotype ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, base_version TEXT NOT NULL, description TEXT, individuals INTEGER NOT NULL, markers INTEGER NOT NULL, value_kind TEXT NOT NULL, new_individuals INTEGER NOT NULL, overlap INTEGER NOT NULL, digest TEXT NOT NULL, contents TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS matrix ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, genotype_version TEXT NOT NULL, description TEXT, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS matrix_column ( matrix_id INTEGER NOT NULL REFERENCES matrix(id) ON DELETE CASCADE, position INTEGER NOT NULL, column_name TEXT NOT NULL, basket_name TEXT NOT NULL, PRIMARY KEY (matrix_id, position), UNIQUE(matrix_id, column_name) ); CREATE TABLE IF NOT EXISTS cell ( basket_id INTEGER NOT NULL REFERENCES basket(id) ON DELETE CASCADE, revision INTEGER NOT NULL, sample TEXT NOT NULL, raw TEXT, value REAL, status TEXT NOT NULL, PRIMARY KEY (basket_id, revision, sample) );", ) .map_err(|error| BasketError::Store(error.to_string()))?; // Earlier builds kept one revision in `cell` (no `revision` column). The // upgrade turns that column into revision 1 of the history: the cells are // copied across under the basket's current revision, and a `basket_version` // row records the digest and timestamp it already had. Nothing is lost. let legacy_cell: bool = connection .query_row( "SELECT COUNT(*) FROM pragma_table_info('cell') WHERE name = 'revision'", [], |row| Ok(row.get::<_, i64>(0)? == 0), ) .map_err(|error| BasketError::Store(error.to_string()))?; if legacy_cell { connection .execute_batch( "ALTER TABLE cell RENAME TO cell_legacy; CREATE TABLE IF NOT EXISTS basket_version ( id INTEGER PRIMARY KEY, basket_id INTEGER NOT NULL REFERENCES basket(id) ON DELETE CASCADE, revision INTEGER NOT NULL, digest TEXT NOT NULL, created_at INTEGER NOT NULL, values_count INTEGER NOT NULL, missing_count INTEGER NOT NULL, description TEXT, UNIQUE(basket_id, revision) ); CREATE TABLE IF NOT EXISTS cell ( basket_id INTEGER NOT NULL REFERENCES basket(id) ON DELETE CASCADE, revision INTEGER NOT NULL, sample TEXT NOT NULL, raw TEXT, value REAL, status TEXT NOT NULL, PRIMARY KEY (basket_id, revision, sample) ); INSERT INTO cell (basket_id, revision, sample, raw, value, status) SELECT legacy.basket_id, basket.revision, legacy.sample, legacy.raw, legacy.value, legacy.status FROM cell_legacy AS legacy JOIN basket ON basket.id = legacy.basket_id; INSERT INTO basket_version (basket_id, revision, digest, created_at, values_count, missing_count, description) SELECT basket.id, basket.revision, basket.digest, basket.updated_at, (SELECT COUNT(*) FROM cell WHERE cell.basket_id = basket.id AND cell.revision = basket.revision AND cell.status = 'ok'), (SELECT COUNT(*) FROM cell WHERE cell.basket_id = basket.id AND cell.revision = basket.revision AND cell.status = 'missing'), basket.description FROM basket; DROP TABLE cell_legacy;", ) .map_err(|error| BasketError::Store(error.to_string()))?; } // Files written before `PRAGMA foreign_keys` was set carry rows whose // basket was deleted without the cascade: the rowid is then reused and the // next create collides with them. Clearing orphans on open makes such a // file heal instead of failing forever; it is two indexed deletes on small // tables. let _ = connection.execute_batch( "DELETE FROM cell WHERE basket_id NOT IN (SELECT id FROM basket); DELETE FROM basket_version WHERE basket_id NOT IN (SELECT id FROM basket); DELETE FROM matrix_column WHERE matrix_id NOT IN (SELECT id FROM matrix);", ); // 0600: a user's column is theirs. #[cfg(unix)] { use std::os::unix::fs::PermissionsExt; let _ = std::fs::set_permissions(&path, std::fs::Permissions::from_mode(0o600)); } Ok(connection) } /// The digest that identifies these exact values. fn digest(name: &str, revision: i64, cells: &[Cell]) -> String { let mut ordered: Vec<&Cell> = cells.iter().collect(); ordered.sort_by(|left, right| left.sample.cmp(&right.sample)); let mut text = format!("{name}\u{0}{revision}\u{0}"); for cell in ordered { text.push_str(&cell.sample); text.push('\u{0}'); text.push_str(cell.raw.as_deref().unwrap_or("\u{1}")); text.push('\u{0}'); } hex_digest(text.as_bytes()) } /// Parse and validate a column, refusing it whole if any cell is not a number /// and not a missing marker. pub fn cells(named: &[(String, Option)]) -> Result, BasketError> { if named.is_empty() { return Err(BasketError::Invalid( "a basket needs at least one value".to_string(), )); } let mut seen = std::collections::HashSet::new(); let mut parsed = Vec::with_capacity(named.len()); let mut bad = Vec::new(); for (sample, raw) in named { if !seen.insert(sample.as_str()) { return Err(BasketError::Invalid(format!( "'{sample}' appears twice in the column" ))); } let (value, status) = parse(raw.as_deref()); if status == "bad" { bad.push(format!( "{sample}: '{}'", raw.as_deref().unwrap_or_default() )); } parsed.push(Cell { sample: sample.clone(), raw: raw.clone(), value, status: status.to_string(), }); } if !bad.is_empty() { return Err(BasketError::Invalid(format!( "not a number and not a missing marker (NA, x, -, ., ?): {}", bad.iter().take(5).cloned().collect::>().join(", ") ))); } Ok(parsed) } /// Create a basket. `kind` is one of [`KINDS`]. #[allow(clippy::too_many_arguments)] pub fn create( dir: &Path, owner: &str, name: &str, kind: &str, base: Option<(&str, u32)>, description: Option<&str>, cells: &[Cell], ) -> Result { validate_name(name)?; let name = name.trim(); if !KINDS.contains(&kind) { return Err(BasketError::Invalid(format!( "kind '{kind}' not recognized; use 'trait' or 'covariate'" ))); } let mut connection = open(dir, owner)?; let exists: Option = connection .query_row( "SELECT id FROM basket WHERE name = ?1", params![name], |row| row.get(0), ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; if exists.is_some() { return Err(BasketError::Invalid(format!( "a basket named '{name}' already exists; PATCH it, or pick another name" ))); } let revision = 1; let digest = digest(name, revision, cells); let at = now(); let transaction = connection .transaction() .map_err(|error| BasketError::Store(error.to_string()))?; transaction .execute( "INSERT INTO basket (name, kind, base_dataset, base_trait, description, revision, \ digest, created_at, updated_at) \ VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?8)", params![ name, kind, base.map(|(dataset, _)| dataset), base.map(|(_, trait_id)| trait_id), description, revision, digest, at ], ) .map_err(|error| BasketError::Store(error.to_string()))?; let id = transaction.last_insert_rowid(); let values = cells.iter().filter(|cell| cell.value.is_some()).count(); let missing = cells.len() - values; transaction .execute( "INSERT INTO basket_version (basket_id, revision, digest, created_at, values_count, \ missing_count, description) \ VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)", params![id, revision, digest, at, values as i64, missing as i64, description], ) .map_err(|error| BasketError::Store(error.to_string()))?; for cell in cells { transaction .execute( "INSERT INTO cell (basket_id, revision, sample, raw, value, status) \ VALUES (?1, ?2, ?3, ?4, ?5, ?6)", params![id, revision, cell.sample, cell.raw, cell.value, cell.status], ) .map_err(|error| BasketError::Store(error.to_string()))?; } transaction .commit() .map_err(|error| BasketError::Store(error.to_string()))?; get(dir, owner, name, false) } /// Replace a basket's column, bumping its revision. pub fn revise( dir: &Path, owner: &str, name: &str, cells: &[Cell], ) -> Result { let mut connection = open(dir, owner)?; let current: Option<(i64, i64)> = connection .query_row( "SELECT id, revision FROM basket WHERE name = ?1", params![name], |row| Ok((row.get(0)?, row.get(1)?)), ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; let Some((id, revision)) = current else { return Err(BasketError::NotFound(format!("no basket named '{name}'"))); }; let revision = revision + 1; let digest = digest(name, revision, cells); let transaction = connection .transaction() .map_err(|error| BasketError::Store(error.to_string()))?; // The previous revision's cells stay: an edit adds a version, it does not // erase the one whose digest a mapping may still be quoting. let values = cells.iter().filter(|cell| cell.value.is_some()).count(); let missing = cells.len() - values; let at = now(); transaction .execute( "INSERT INTO basket_version (basket_id, revision, digest, created_at, values_count, \ missing_count, description) \ VALUES (?1, ?2, ?3, ?4, ?5, ?6, NULL)", params![id, revision, digest, at, values as i64, missing as i64], ) .map_err(|error| BasketError::Store(error.to_string()))?; for cell in cells { transaction .execute( "INSERT INTO cell (basket_id, revision, sample, raw, value, status) \ VALUES (?1, ?2, ?3, ?4, ?5, ?6)", params![id, revision, cell.sample, cell.raw, cell.value, cell.status], ) .map_err(|error| BasketError::Store(error.to_string()))?; } transaction .execute( "UPDATE basket SET revision = ?2, digest = ?3, updated_at = ?4 WHERE id = ?1", params![id, revision, digest, at], ) .map_err(|error| BasketError::Store(error.to_string()))?; transaction .commit() .map_err(|error| BasketError::Store(error.to_string()))?; get(dir, owner, name, false) } /// One basket, with or without its column. pub fn get( dir: &Path, owner: &str, name: &str, with_cells: bool, ) -> Result { let connection = open(dir, owner)?; let row: Option<(i64, String, String, Option, Option, Option, i64, String, i64, i64)> = connection .query_row( "SELECT id, name, kind, base_dataset, base_trait, description, revision, digest, \ created_at, updated_at \ FROM basket WHERE name = ?1", params![name.trim()], |row| { Ok(( row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?, row.get(4)?, row.get(5)?, row.get(6)?, row.get(7)?, row.get(8)?, row.get(9)?, )) }, ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; let Some((id, name, kind, base_dataset, base_trait, description, revision, digest, created_at, updated_at)) = row else { return Err(BasketError::NotFound(format!("no basket named '{name}'"))); }; let counts: (i64, i64) = connection .query_row( "SELECT COALESCE(SUM(status = 'ok'), 0), COALESCE(SUM(status = 'missing'), 0) \ FROM cell WHERE basket_id = ?1", params![id], |row| Ok((row.get(0)?, row.get(1)?)), ) .map_err(|error| BasketError::Store(error.to_string()))?; let _ = with_cells; Ok(BasketInfo { name, kind, base_dataset, base_trait, description, revision, digest, values: counts.0 as usize, missing: counts.1 as usize, created_at, updated_at, }) } /// Every basket this user has, newest first. pub fn list(dir: &Path, owner: &str) -> Result, BasketError> { let names: Vec = { let connection = open(dir, owner)?; let mut statement = connection .prepare("SELECT name FROM basket ORDER BY updated_at DESC, name") .map_err(|error| BasketError::Store(error.to_string()))?; let rows = statement .query_map([], |row| row.get::<_, String>(0)) .map_err(|error| BasketError::Store(error.to_string()))?; rows.collect::, _>>() .map_err(|error| BasketError::Store(error.to_string()))? }; names .into_iter() .map(|name| get(dir, owner, &name, false)) .collect() } /// A basket's column for one revision (`None` = the current one), in the /// order its samples were stored. pub fn cells_of( dir: &Path, owner: &str, name: &str, revision: Option, ) -> Result, BasketError> { let connection = open(dir, owner)?; let found: Option<(i64, i64)> = connection .query_row( "SELECT id, revision FROM basket WHERE name = ?1", params![name.trim()], |row| Ok((row.get(0)?, row.get(1)?)), ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; let Some((id, head)) = found else { return Err(BasketError::NotFound(format!("no basket named '{name}'"))); }; let revision = revision.unwrap_or(head); let exists: bool = connection .query_row( "SELECT COUNT(*) FROM basket_version WHERE basket_id = ?1 AND revision = ?2", params![id, revision], |row| Ok(row.get::<_, i64>(0)? > 0), ) .map_err(|error| BasketError::Store(error.to_string()))?; if !exists { return Err(BasketError::NotFound(format!( "basket '{name}' has no revision {revision} (see /baskets/{name}/revisions)" ))); } let mut statement = connection .prepare( "SELECT sample, raw, value, status FROM cell \ WHERE basket_id = ?1 AND revision = ?2 ORDER BY rowid", ) .map_err(|error| BasketError::Store(error.to_string()))?; let rows = statement .query_map(params![id, revision], |row| { Ok(Cell { sample: row.get(0)?, raw: row.get(1)?, value: row.get(2)?, status: row.get(3)?, }) }) .map_err(|error| BasketError::Store(error.to_string()))?; rows.collect::, _>>() .map_err(|error| BasketError::Store(error.to_string())) } /// Delete a basket. pub fn delete(dir: &Path, owner: &str, name: &str) -> Result<(), BasketError> { let mut connection = open(dir, owner)?; let ids: Vec = { let mut statement = connection .prepare("SELECT id FROM basket WHERE name = ?1") .map_err(|error| BasketError::Store(error.to_string()))?; let rows = statement .query_map(params![name.trim()], |row| row.get::<_, i64>(0)) .map_err(|error| BasketError::Store(error.to_string()))?; rows.collect::, _>>() .map_err(|error| BasketError::Store(error.to_string()))? }; if ids.is_empty() { return Err(BasketError::NotFound(format!("no basket named '{name}'"))); } let transaction = connection .transaction() .map_err(|error| BasketError::Store(error.to_string()))?; for id in &ids { // Explicit, not only the cascade: a file written before `PRAGMA // foreign_keys` was set has rows the cascade never reached, and a // reused rowid would then collide with them. for table in ["cell", "basket_version"] { transaction .execute(&format!("DELETE FROM {table} WHERE basket_id = ?1"), params![id]) .map_err(|error| BasketError::Store(error.to_string()))?; } transaction .execute("DELETE FROM basket WHERE id = ?1", params![id]) .map_err(|error| BasketError::Store(error.to_string()))?; } transaction .commit() .map_err(|error| BasketError::Store(error.to_string()))?; Ok(()) } /// What a mapping gets: the same `(name, value)` pairs it would have been /// posted, plus the revision and digest to record in the reply. #[derive(Debug, Clone)] pub struct Resolved { pub name: String, pub revision: i64, pub digest: String, pub values: Vec<(String, Option)>, pub missing: usize, } /// Every stored revision of a basket, oldest first. pub fn revisions(dir: &Path, owner: &str, name: &str) -> Result, BasketError> { let connection = open(dir, owner)?; let id: Option = connection .query_row( "SELECT id FROM basket WHERE name = ?1", params![name.trim()], |row| row.get(0), ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; let Some(id) = id else { return Err(BasketError::NotFound(format!("no basket named '{name}'"))); }; let mut statement = connection .prepare( "SELECT revision, digest, created_at, values_count, missing_count, description \ FROM basket_version WHERE basket_id = ?1 ORDER BY revision", ) .map_err(|error| BasketError::Store(error.to_string()))?; let rows = statement .query_map(params![id], |row| { Ok(Version { revision: row.get(0)?, digest: row.get(1)?, created_at: row.get(2)?, values: row.get::<_, i64>(3)? as usize, missing: row.get::<_, i64>(4)? as usize, description: row.get(5)?, }) }) .map_err(|error| BasketError::Store(error.to_string()))?; rows.collect::, _>>() .map_err(|error| BasketError::Store(error.to_string())) } /// Resolve a basket for a mapping. pub fn resolve( dir: &Path, owner: &str, requested: &str, ) -> Result { // `name` maps the current revision; `name@3` pins one, so a result quoted // from yesterday can be reproduced after an edit. let (name, revision) = match requested.split_once('@') { Some((name, revision)) => ( name.to_string(), Some(revision.parse::().map_err(|_| { BasketError::Invalid(format!( "'{requested}': the revision after '@' must be a number" )) })?), ), None => (requested.to_string(), None), }; let info = get(dir, owner, &name, false)?; let cells = cells_of(dir, owner, &name, revision)?; let revision = revision.unwrap_or(info.revision); let digest = revisions(dir, owner, &name)? .into_iter() .find(|version| version.revision == revision) .map(|version| version.digest) .unwrap_or(info.digest); Ok(Resolved { name: info.name, revision, digest, missing: cells.iter().filter(|cell| cell.value.is_none()).count(), values: cells .into_iter() .map(|cell| (cell.sample, cell.value)) .collect(), }) } #[cfg(test)] mod tests { use super::*; fn scratch() -> PathBuf { let dir = std::env::temp_dir().join(format!( "gn4-baskets-{}-{}", std::process::id(), std::time::SystemTime::now() .duration_since(std::time::UNIX_EPOCH) .map(|elapsed| elapsed.as_nanos()) .unwrap_or(0) )); let _ = std::fs::remove_dir_all(&dir); dir } fn column(pairs: &[(&str, Option<&str>)]) -> Vec { let named: Vec<(String, Option)> = pairs .iter() .map(|(sample, raw)| (sample.to_string(), raw.map(str::to_string))) .collect(); cells(&named).unwrap() } #[test] fn cells_parse_numbers_missing_and_refuse_the_rest() { assert_eq!(parse(Some("5.2")), (Some(5.2), "ok")); assert_eq!(parse(Some(" -3.5e2 ")), (Some(-350.0), "ok")); for missing in ["", "NA", "na", "N/A", "null", "x", "X", "-", ".", "?"] { assert_eq!(parse(Some(missing)), (None, "missing"), "{missing}"); } assert_eq!(parse(None), (None, "missing")); assert_eq!(parse(Some("n/a-ish")), (None, "bad")); assert_eq!(parse(Some("12abc")), (None, "bad")); assert_eq!(parse(Some("NaN")), (None, "bad")); assert_eq!(parse(Some("inf")), (None, "bad")); } #[test] fn a_bad_cell_is_refused_with_the_sample_named() { let error = cells(&[ ("BXD1".to_string(), Some("1.0".to_string())), ("BXD2".to_string(), Some("not a number".to_string())), ]) .unwrap_err() .to_string(); assert!(error.contains("BXD2"), "{error}"); assert!(error.contains("not a number"), "{error}"); // Duplicates are a mistake, not a merge. let duplicate = cells(&[ ("BXD1".to_string(), Some("1.0".to_string())), ("BXD1".to_string(), Some("2.0".to_string())), ]) .unwrap_err() .to_string(); assert!(duplicate.contains("twice"), "{duplicate}"); } #[test] fn a_basket_round_trips_and_a_revision_changes_the_digest() { let dir = scratch(); let first = column(&[ ("BXD1", Some("1.0")), ("BXD2", Some("2.5")), ("BXD3", Some("NA")), ]); let created = create(&dir, "user-a", "wbc-edit", "trait", Some(("BXD", 10001)), Some("two values"), &first).unwrap(); assert_eq!(created.revision, 1); assert_eq!(created.values, 2); assert_eq!(created.missing, 1); assert_eq!(created.base_trait, Some(10001)); let before = created.digest.clone(); // An edit is a new revision, and a new identity. let second = column(&[("BXD1", Some("1.6")), ("BXD2", Some("2.5")), ("BXD3", Some("NA"))]); let revised = revise(&dir, "user-a", "wbc-edit", &second).unwrap(); assert_eq!(revised.revision, 2); assert_ne!(revised.digest, before); let cells = cells_of(&dir, "user-a", "wbc-edit", None).unwrap(); assert_eq!(cells[0].value, Some(1.6)); assert_eq!(cells[0].raw.as_deref(), Some("1.6"), "the text is kept"); assert_eq!(cells[2].status, "missing"); let resolved = resolve(&dir, "user-a", "wbc-edit").unwrap(); assert_eq!(resolved.values.len(), 3); assert_eq!(resolved.missing, 1); assert_eq!(resolved.revision, 2); // Another user cannot see it: the file is theirs. assert!(matches!( get(&dir, "user-b", "wbc-edit", false).unwrap_err(), BasketError::NotFound(_) )); assert!(list(&dir, "user-b").unwrap().is_empty()); assert_eq!(list(&dir, "user-a").unwrap().len(), 1); delete(&dir, "user-a", "wbc-edit").unwrap(); assert!(list(&dir, "user-a").unwrap().is_empty()); std::fs::remove_dir_all(&dir).ok(); } #[test] fn orphans_from_before_the_cascade_are_cleared() { let dir = scratch(); let cells = column(&[("BXD1", Some("1.0"))]); create(&dir, "u", "gone", "trait", None, None, &cells).unwrap(); // Simulate the old bug: the basket row deleted, its versions and cells // left behind. { let path = database(&dir, "u"); let connection = Connection::open(&path).unwrap(); connection .execute("DELETE FROM basket WHERE name = 'gone'", []) .unwrap(); } // Opening again clears them, so the reused rowid cannot collide. let again = create(&dir, "u", "new", "trait", None, None, &cells).unwrap(); assert_eq!(again.revision, 1); let versions = revisions(&dir, "u", "new").unwrap(); assert_eq!(versions.len(), 1); std::fs::remove_dir_all(&dir).ok(); } #[test] fn an_older_file_is_upgraded_with_its_column_intact() { let dir = scratch(); std::fs::create_dir_all(&dir).unwrap(); // The pre-versioning schema: one column per basket, no history. { let path = database(&dir, "legacy-user"); let connection = Connection::open(&path).unwrap(); connection .execute_batch( "CREATE TABLE basket ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, kind TEXT NOT NULL, base_dataset TEXT, base_trait INTEGER, description TEXT, revision INTEGER NOT NULL, digest TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL ); CREATE TABLE cell ( basket_id INTEGER NOT NULL REFERENCES basket(id) ON DELETE CASCADE, sample TEXT NOT NULL, raw TEXT, value REAL, status TEXT NOT NULL, PRIMARY KEY (basket_id, sample) ); INSERT INTO basket VALUES (1, 'old-basket', 'trait', 'BXD', 10001, 'from before', 1, 'abc', 100, 200); INSERT INTO cell VALUES (1, 'BXD1', '1.0', 1.0, 'ok'); INSERT INTO cell VALUES (1, 'BXD2', 'NA', NULL, 'missing');", ) .unwrap(); } // Opening it upgrades the schema; the column is revision 1, not lost. let info = get(&dir, "legacy-user", "old-basket", false).unwrap(); assert_eq!(info.revision, 1); assert_eq!(info.values, 1); assert_eq!(info.missing, 1); let cells = cells_of(&dir, "legacy-user", "old-basket", None).unwrap(); assert_eq!(cells.len(), 2); assert_eq!(cells[0].value, Some(1.0)); let versions = revisions(&dir, "legacy-user", "old-basket").unwrap(); assert_eq!(versions.len(), 1); assert_eq!(versions[0].created_at, 200, "the timestamp it already had"); // And the history works from there. let next = column(&[("BXD1", Some("2.0")), ("BXD2", Some("NA"))]); revise(&dir, "legacy-user", "old-basket", &next).unwrap(); assert_eq!(revisions(&dir, "legacy-user", "old-basket").unwrap().len(), 2); assert_eq!( cells_of(&dir, "legacy-user", "old-basket", Some(1)).unwrap()[0].value, Some(1.0) ); std::fs::remove_dir_all(&dir).ok(); } #[test] fn every_revision_is_kept_with_its_digest_and_time() { let dir = scratch(); let first = column(&[("BXD1", Some("1.0")), ("BXD2", Some("2.0"))]); create(&dir, "u", "v", "trait", None, Some("as published"), &first).unwrap(); let second = column(&[("BXD1", Some("1.5")), ("BXD2", Some("NA"))]); revise(&dir, "u", "v", &second).unwrap(); let third = column(&[("BXD1", Some("1.5")), ("BXD2", Some("2.5"))]); revise(&dir, "u", "v", &third).unwrap(); let versions = revisions(&dir, "u", "v").unwrap(); assert_eq!(versions.len(), 3, "an edit adds a version, it does not erase one"); assert_eq!(versions[0].revision, 1); assert_eq!(versions[0].description.as_deref(), Some("as published")); assert_eq!(versions[1].values, 1); assert_eq!(versions[1].missing, 1); assert!(versions[0].created_at <= versions[2].created_at); assert_ne!(versions[0].digest, versions[2].digest); // An older revision is still readable, and still resolves. let old = cells_of(&dir, "u", "v", Some(1)).unwrap(); assert_eq!(old[0].value, Some(1.0)); let pinned = resolve(&dir, "u", "v@1").unwrap(); assert_eq!(pinned.revision, 1); assert_eq!(pinned.digest, versions[0].digest); assert_eq!(pinned.values[0].1, Some(1.0)); // Pinning one that never existed is a refusal, not a fallback. assert!(matches!( resolve(&dir, "u", "v@9").unwrap_err(), BasketError::NotFound(_) )); // `name@x` is a bad revision number, said so by name. assert!(matches!( resolve(&dir, "u", "v@today").unwrap_err(), BasketError::Invalid(_) )); std::fs::remove_dir_all(&dir).ok(); } #[test] fn the_file_is_named_for_the_user() { let dir = std::path::Path::new("/tmp/baskets"); assert_eq!( database(dir, "robw"), std::path::PathBuf::from("/tmp/baskets/robw.sqlite"), "one look tells an operator whose baskets these are" ); // Unsafe characters are sanitised and disambiguated, so two different // users cannot land in one file. let awkward = database(dir, "a/b"); assert!(awkward.to_string_lossy().contains("a_b-"), "{}", awkward.display()); assert_ne!(database(dir, "a/b"), database(dir, "a_b")); // An empty subject still gets a file of its own. assert!(database(dir, "").to_string_lossy().contains("user-")); let traversal = file_stem("../etc/passwd"); assert!(!traversal.contains('/'), "{traversal}"); assert!(traversal.ends_with("etc_passwd"), "{traversal}"); } #[test] fn names_are_url_safe_and_short() { assert!(validate_name("wbc-edit").is_ok()); assert!(validate_name("my.basket_2").is_ok()); assert!(validate_name("").is_err()); assert!(validate_name("has space").is_err()); assert!(validate_name("../etc/passwd").is_err()); assert!(validate_name(&"x".repeat(65)).is_err()); } } // --------------------------------------------------------------------------- // Matrices: a named table of traits and covariates for one genotype version, // uploaded at once. A matrix is not a second kind of storage -- each of its // columns is a basket (`/`), so it inherits the version // history, the digest, the per-user file and the fact that every compute // endpoint already knows how to map a basket. What the matrix adds is the // upload: one file, checked against a genotype panel, and the grouping that // keeps its columns together. // --------------------------------------------------------------------------- /// One column of an uploaded matrix. #[derive(Debug, Clone, serde::Serialize, utoipa::ToSchema)] pub struct MatrixColumn { /// The column's header, e.g. "body_weight". pub name: String, /// `trait` or `covariate`. pub kind: String, /// How a mapping names it: `{"basket": "."}`. pub basket: String, pub values: usize, pub missing: usize, } /// An uploaded matrix. #[derive(Debug, Clone, serde::Serialize, utoipa::ToSchema)] pub struct MatrixInfo { pub name: String, /// The genotype version its individuals were checked against. pub genotype_version: String, #[serde(skip_serializing_if = "Option::is_none")] pub description: Option, /// Individuals in the upload, in its own order. pub individuals: usize, pub columns: Vec, pub created_at: i64, pub updated_at: i64, } /// A parsed upload: the individuals down the first column, then each column. pub struct Parsed { pub individuals: Vec, /// `(header, kind, cells)` in file order; kind is "trait" or "covariate". pub columns: Vec<(String, String, Vec>)>, } /// Parse a delimited matrix: the first row is the header, the first column the /// individuals, every other column a trait or covariate. /// /// `kinds` maps a column header to "covariate"; anything not named there is a /// trait. The separator is taken from `separator`, else guessed from the /// header (tab, comma or semicolon). pub fn parse_matrix( text: &str, separator: Option, kinds: &std::collections::BTreeMap, ) -> Result { let lines: Vec<&str> = text .lines() .map(|line| line.trim_end_matches('\r')) .filter(|line| !line.trim().is_empty() && !line.starts_with('#')) .collect(); if lines.len() < 2 { return Err(BasketError::Invalid( "a matrix needs a header row and at least one individual".to_string(), )); } let separator = separator.unwrap_or_else(|| { let header = lines[0]; if header.contains('\t') { '\t' } else if header.contains(';') && !header.contains(',') { ';' } else { ',' } }); let fields = |line: &str| -> Vec { line.split(separator) .map(|field| field.trim().trim_matches('"').to_string()) .collect() }; let header = fields(lines[0]); if header.len() < 2 { return Err(BasketError::Invalid(format!( "the header has {} column(s); the first is the individual and the rest are traits or \ covariates", header.len() ))); } let width = header.len(); let mut individuals = Vec::with_capacity(lines.len() - 1); let mut columns: Vec<(String, String, Vec>)> = header[1..] .iter() .map(|name| { let kind = kinds .get(name) .filter(|kind| KINDS.contains(&kind.as_str())) .cloned() .unwrap_or_else(|| "trait".to_string()); (name.clone(), kind, Vec::with_capacity(lines.len() - 1)) }) .collect(); for (index, line) in lines[1..].iter().enumerate() { let row = fields(line); if row.len() != width { return Err(BasketError::Invalid(format!( "row {} has {} field(s) but the header has {width}: {}", index + 2, row.len(), line.chars().take(60).collect::() ))); } individuals.push(row[0].clone()); for (column, value) in columns.iter_mut().zip(&row[1..]) { // A cell that means "no value" is stored as such; a cell that is // neither a number nor a missing marker is refused later, by name. column.2.push(if value.is_empty() { None } else { Some(value.clone()) }); } } let mut seen = std::collections::HashSet::new(); for name in &individuals { if name.is_empty() { return Err(BasketError::Invalid( "the first column must name an individual on every row".to_string(), )); } if !seen.insert(name.as_str()) { return Err(BasketError::Invalid(format!( "individual '{name}' appears twice" ))); } } Ok(Parsed { individuals, columns, }) } /// Check an upload against a genotype panel: same number of individuals, and /// every one of them in the panel. Returns the panel members the upload left /// out (there should be none, given the count matches). pub fn check_panel(uploaded: &[String], panel: &[String]) -> Result<(), BasketError> { if uploaded.len() != panel.len() { return Err(BasketError::Invalid(format!( "the matrix has {} individual(s) but this genotype version has {}; the first column \ must name each individual once", uploaded.len(), panel.len() ))); } let in_panel: std::collections::HashSet<&str> = panel.iter().map(String::as_str).collect(); let unknown: Vec<&str> = uploaded .iter() .map(String::as_str) .filter(|name| !in_panel.contains(name)) .collect(); if !unknown.is_empty() { return Err(BasketError::Invalid(format!( "{} name(s) are not in this genotype version: {}", unknown.len(), unknown.iter().take(5).copied().collect::>().join(", ") ))); } Ok(()) } /// Store an uploaded matrix: one basket per column, grouped under the matrix /// name. #[allow(clippy::too_many_arguments)] pub fn create_matrix( dir: &Path, owner: &str, name: &str, genotype_version: &str, description: Option<&str>, parsed: &Parsed, ) -> Result { validate_name(name)?; if parsed.columns.is_empty() { return Err(BasketError::Invalid( "the matrix has no trait or covariate columns".to_string(), )); } { let connection = open(dir, owner)?; let exists: Option = connection .query_row( "SELECT id FROM matrix WHERE name = ?1", params![name.trim()], |row| row.get(0), ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; if exists.is_some() { return Err(BasketError::Invalid(format!( "a matrix named '{name}' already exists; delete it, or pick another name" ))); } } let at = now(); // Every column is a basket, so a mapping names `/` and the // engines need to know nothing new. let mut stored = Vec::with_capacity(parsed.columns.len()); for (column, kind, raw) in &parsed.columns { // A dot, not a slash: a basket name has to survive a URL path // segment, and `/baskets/{name}` could not carry a slash. let basket_name = format!("{}.{column}", name.trim()); let named: Vec<(String, Option)> = parsed .individuals .iter() .cloned() .zip(raw.iter().cloned()) .collect(); let cells = cells(&named)?; let info = create( dir, owner, &basket_name, kind, Some((genotype_version, 0)), description, &cells, )?; stored.push(info); } let mut connection = open(dir, owner)?; let transaction = connection .transaction() .map_err(|error| BasketError::Store(error.to_string()))?; transaction .execute( "INSERT INTO matrix (name, genotype_version, description, created_at, updated_at) \ VALUES (?1, ?2, ?3, ?4, ?4)", params![name.trim(), genotype_version, description, at], ) .map_err(|error| BasketError::Store(error.to_string()))?; let id = transaction.last_insert_rowid(); for (position, (column, _, _)) in parsed.columns.iter().enumerate() { transaction .execute( "INSERT INTO matrix_column (matrix_id, position, column_name, basket_name) \ VALUES (?1, ?2, ?3, ?4)", params![ id, position as i64, column, format!("{}.{column}", name.trim()) ], ) .map_err(|error| BasketError::Store(error.to_string()))?; } transaction .commit() .map_err(|error| BasketError::Store(error.to_string()))?; get_matrix(dir, owner, name) } /// One matrix, with its columns. pub fn get_matrix(dir: &Path, owner: &str, name: &str) -> Result { let connection = open(dir, owner)?; let row: Option<(i64, String, String, Option, i64, i64)> = connection .query_row( "SELECT id, name, genotype_version, description, created_at, updated_at \ FROM matrix WHERE name = ?1", params![name.trim()], |row| { Ok(( row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?, row.get(4)?, row.get(5)?, )) }, ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; let Some((id, name, genotype_version, description, created_at, updated_at)) = row else { return Err(BasketError::NotFound(format!("no matrix named '{name}'"))); }; let listed: Vec<(String, String)> = { let mut statement = connection .prepare( "SELECT column_name, basket_name FROM matrix_column \ WHERE matrix_id = ?1 ORDER BY position", ) .map_err(|error| BasketError::Store(error.to_string()))?; let rows = statement .query_map(params![id], |row| Ok((row.get(0)?, row.get(1)?))) .map_err(|error| BasketError::Store(error.to_string()))?; rows.collect::, _>>() .map_err(|error| BasketError::Store(error.to_string()))? }; let mut columns = Vec::with_capacity(listed.len()); let mut individuals = 0usize; for (column_name, basket_name) in listed { let info = get(dir, owner, &basket_name, false)?; if individuals == 0 { individuals = info.values + info.missing; } columns.push(MatrixColumn { name: column_name, kind: info.kind, basket: basket_name, values: info.values, missing: info.missing, }); } Ok(MatrixInfo { name, genotype_version, description, individuals, columns, created_at, updated_at, }) } /// Every matrix this user uploaded, newest first. pub fn list_matrices(dir: &Path, owner: &str) -> Result, BasketError> { let names: Vec = { let connection = open(dir, owner)?; let mut statement = connection .prepare("SELECT name FROM matrix ORDER BY updated_at DESC, name") .map_err(|error| BasketError::Store(error.to_string()))?; let rows = statement .query_map([], |row| row.get::<_, String>(0)) .map_err(|error| BasketError::Store(error.to_string()))?; rows.collect::, _>>() .map_err(|error| BasketError::Store(error.to_string()))? }; names .into_iter() .map(|name| get_matrix(dir, owner, &name)) .collect() } /// Delete a matrix and the baskets it owns. pub fn delete_matrix(dir: &Path, owner: &str, name: &str) -> Result<(), BasketError> { let info = get_matrix(dir, owner, name)?; for column in &info.columns { delete(dir, owner, &column.basket)?; } let mut connection = open(dir, owner)?; let id: Option = connection .query_row( "SELECT id FROM matrix WHERE name = ?1", params![name.trim()], |row| row.get(0), ) .optional() .map_err(|error| BasketError::Store(error.to_string()))?; let Some(id) = id else { return Err(BasketError::NotFound(format!("no matrix named '{name}'"))); }; let transaction = connection .transaction() .map_err(|error| BasketError::Store(error.to_string()))?; transaction .execute("DELETE FROM matrix_column WHERE matrix_id = ?1", params![id]) .map_err(|error| BasketError::Store(error.to_string()))?; transaction .execute("DELETE FROM matrix WHERE id = ?1", params![id]) .map_err(|error| BasketError::Store(error.to_string()))?; transaction .commit() .map_err(|error| BasketError::Store(error.to_string()))?; Ok(()) }