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}