Skip to main content

symworx_loadsym_db/
lib.rs

1// symworx-loadsym-db
2//
3// Canonical SQL for bootstrapping a personal training-load catalog.
4// Zero runtime dependencies. No sample data, no personal identifiers.
5//
6// Apply the schema yourself (or via `symload db init`) against a DB file
7// that lives *outside* this repository (e.g. $VELOFIT_HOME/db/loadsym.sqlite).
8
9/// Schema version for migrations / `schema_migrations` table.
10///
11/// v1 — initial catalog  
12/// v2 — expanded `ftp_history` + `activities.ftp_history_id` for time-varying FTP scoring  
13/// v3 — `catalog_meta` key/value (e.g. last ingest watermark)  
14/// v4 — multi-source session linking (`counts_for_load`, `session_groups`, pipeline provenance)
15pub const SCHEMA_VERSION: i32 = 4;
16
17/// PostgreSQL schema (shared / multi-user deployments).
18pub const POSTGRES_SCHEMA: &str = include_str!("../sql/schema.sql");
19
20/// SQLite schema (recommended for single-user local catalogs).
21pub const SQLITE_SCHEMA: &str = include_str!("../sql/schema.sqlite.sql");
22
23/// Returns the schema for the requested dialect.
24///
25/// Accepts: `postgres` / `pg` / `postgresql`, `sqlite` / `sql`.
26/// Unknown values default to SQLite (personal default).
27pub fn get_schema(dialect: &str) -> &'static str {
28    match dialect.to_ascii_lowercase().as_str() {
29        "postgres" | "pg" | "postgresql" => POSTGRES_SCHEMA,
30        "sqlite" | "sql" | "" => SQLITE_SCHEMA,
31        _ => SQLITE_SCHEMA,
32    }
33}
34
35/// Default relative path (under `$VELOFIT_HOME`) for the personal SQLite catalog.
36/// Callers must resolve this against their own archive root — never hardcode usernames.
37pub const DEFAULT_DB_RELATIVE: &str = "db/loadsym.sqlite";
38
39#[cfg(test)]
40mod tests {
41    use super::*;
42
43    #[test]
44    fn schemas_are_not_empty() {
45        assert!(POSTGRES_SCHEMA.len() > 100);
46        assert!(SQLITE_SCHEMA.len() > 100);
47        assert!(SCHEMA_VERSION >= 1);
48        assert!(SQLITE_SCHEMA.contains("CREATE TABLE IF NOT EXISTS activities"));
49        assert!(SQLITE_SCHEMA.contains("INTEGER PRIMARY KEY AUTOINCREMENT"));
50        assert!(SQLITE_SCHEMA.contains("counts_for_load"));
51        assert!(SQLITE_SCHEMA.contains("session_groups"));
52        assert!(SCHEMA_VERSION >= 4);
53    }
54
55    #[test]
56    fn default_dialect_is_sqlite() {
57        assert!(get_schema("sqlite").contains("PRAGMA foreign_keys"));
58        assert!(get_schema("unknown").contains("PRAGMA foreign_keys"));
59        assert!(get_schema("postgres").contains("BIGSERIAL"));
60    }
61}