//! opentracker server: one binary, one SQLite file. mod auth; mod device; mod guest; mod passkeys; mod routes; use std::path::PathBuf; use std::sync::{Arc, Mutex, MutexGuard, PoisonError}; use std::time::{Duration, SystemTime, UNIX_EPOCH}; use axum::http::StatusCode; use axum::response::{IntoResponse, Response}; use clap::{Parser, Subcommand}; use rusqlite::{Connection, OptionalExtension, params}; use webauthn_rs::prelude::Url; /// Schema steps, applied in order. `PRAGMA user_version` counts the applied ones. /// Never edit a released step. Append a new one. const MIGRATIONS: &[&str] = &[ // IF NOT EXISTS: databases from before migrations have these tables at version 0. " CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE COLLATE NOCASE, -- NULL when the account signs in with passkeys only. pw_hash TEXT, is_admin INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0, 1)), two_factor INTEGER NOT NULL DEFAULT 0 CHECK (two_factor IN (0, 1)), -- The WebAuthn user handle. Random, so a passkey does not reveal the user id. webauthn_id TEXT NOT NULL UNIQUE, retention_days INTEGER CHECK (retention_days > 0), created_at INTEGER NOT NULL ) STRICT; CREATE TABLE IF NOT EXISTS sessions ( token_hash BLOB PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at INTEGER NOT NULL ) STRICT; CREATE TABLE IF NOT EXISTS passkeys ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, cred_id BLOB NOT NULL UNIQUE, -- webauthn_rs::prelude::Passkey as JSON. passkey TEXT NOT NULL, name TEXT NOT NULL, created_at INTEGER NOT NULL, last_used_at INTEGER ) STRICT; CREATE TABLE IF NOT EXISTS devices ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, name TEXT NOT NULL, token_hash BLOB NOT NULL UNIQUE, created_at INTEGER NOT NULL, last_seen_at INTEGER ) STRICT; -- The key makes uploads idempotent: a retried batch collapses into the rows already there. CREATE TABLE IF NOT EXISTS points ( user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, ts INTEGER NOT NULL, lat REAL NOT NULL, lon REAL NOT NULL, acc REAL, alt REAL, speed REAL, bearing REAL, battery INTEGER, PRIMARY KEY (user_id, ts) ) STRICT, WITHOUT ROWID; CREATE INDEX IF NOT EXISTS points_ts ON points(ts); CREATE TABLE IF NOT EXISTS shares ( id INTEGER PRIMARY KEY, owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, viewer_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at INTEGER, created_at INTEGER NOT NULL, UNIQUE (owner_id, viewer_id), CHECK (owner_id <> viewer_id) ) STRICT; ", // Points belong to a device. The web UI uploads as one device per user, which has no token. " CREATE TABLE devices_new ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, name TEXT NOT NULL, -- NULL for the web device. token_hash BLOB UNIQUE, created_at INTEGER NOT NULL, last_seen_at INTEGER ) STRICT; INSERT INTO devices_new SELECT * FROM devices; DROP TABLE devices; ALTER TABLE devices_new RENAME TO devices; CREATE UNIQUE INDEX devices_web ON devices(user_id) WHERE token_hash IS NULL; -- Older points go to the device that uploaded last, or to a new web device. INSERT INTO devices (user_id, name, created_at) SELECT DISTINCT user_id, 'Web', unixepoch() FROM points WHERE user_id NOT IN (SELECT user_id FROM devices); -- The key makes uploads idempotent: a retried batch collapses into the rows already there. CREATE TABLE points_new ( device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE, ts INTEGER NOT NULL, lat REAL NOT NULL, lon REAL NOT NULL, acc REAL, alt REAL, speed REAL, bearing REAL, battery INTEGER, PRIMARY KEY (device_id, ts) ) STRICT, WITHOUT ROWID; INSERT INTO points_new SELECT (SELECT d.id FROM devices d WHERE d.user_id = p.user_id ORDER BY d.last_seen_at DESC NULLS LAST, d.id LIMIT 1), ts, lat, lon, acc, alt, speed, bearing, battery FROM points p; DROP TABLE points; ALTER TABLE points_new RENAME TO points; CREATE INDEX points_ts ON points(ts); ALTER TABLE shares ADD COLUMN all_devices INTEGER NOT NULL DEFAULT 1 CHECK (all_devices IN (0, 1)); ALTER TABLE shares ADD COLUMN trail INTEGER NOT NULL DEFAULT 1 CHECK (trail IN (0, 1)); ALTER TABLE shares ADD COLUMN precision_m INTEGER NOT NULL DEFAULT 0 CHECK (precision_m >= 0); -- Used only when all_devices is 0. CREATE TABLE share_devices ( share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE, device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE, PRIMARY KEY (share_id, device_id) ) STRICT, WITHOUT ROWID; ", // Trails can start at a time. Guest links are shares without a viewer. " CREATE TABLE shares_new ( id INTEGER PRIMARY KEY, owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- NULL for a guest link. viewer_id INTEGER REFERENCES users(id) ON DELETE CASCADE, expires_at INTEGER, created_at INTEGER NOT NULL, all_devices INTEGER NOT NULL DEFAULT 1 CHECK (all_devices IN (0, 1)), trail INTEGER NOT NULL DEFAULT 1 CHECK (trail IN (0, 1)), trail_since INTEGER, precision_m INTEGER NOT NULL DEFAULT 0 CHECK (precision_m >= 0), -- Guest links only. Kept readable, so the owner can copy the link again. name TEXT, token TEXT UNIQUE, pw_hash TEXT, UNIQUE (owner_id, viewer_id), CHECK (owner_id <> viewer_id), CHECK ((viewer_id IS NULL) = (token IS NOT NULL)) ) STRICT; INSERT INTO shares_new (id, owner_id, viewer_id, expires_at, created_at, all_devices, trail, precision_m) SELECT id, owner_id, viewer_id, expires_at, created_at, all_devices, trail, precision_m FROM shares; DROP TABLE shares; ALTER TABLE shares_new RENAME TO shares; ", // One column for the trail: NULL shows only the current position, 0 the full history. " UPDATE shares SET trail_since = CASE WHEN trail = 0 THEN NULL ELSE COALESCE(trail_since, 0) END; ALTER TABLE shares DROP COLUMN trail; ", // Credential changes need a recent sign-in. 0: existing sessions must sign in again first. // Each point stores its cell for every precision in PRECISIONS_M. `fill_cells` computes them for existing points. " ALTER TABLE sessions ADD COLUMN signed_in_at INTEGER NOT NULL DEFAULT 0; ALTER TABLE points ADD COLUMN lat_100 REAL; ALTER TABLE points ADD COLUMN lon_100 REAL; ALTER TABLE points ADD COLUMN lat_1000 REAL; ALTER TABLE points ADD COLUMN lon_1000 REAL; ALTER TABLE points ADD COLUMN lat_10000 REAL; ALTER TABLE points ADD COLUMN lon_10000 REAL; ALTER TABLE points ADD COLUMN lat_100000 REAL; ALTER TABLE points ADD COLUMN lon_100000 REAL; -- Other precisions round up to the next coarser one, so no share shows more than before. UPDATE shares SET precision_m = CASE WHEN precision_m = 0 THEN 0 WHEN precision_m <= 100 THEN 100 WHEN precision_m <= 1000 THEN 1000 WHEN precision_m <= 10000 THEN 10000 ELSE 100000 END; ", // The name others see, and the device the map selects first. " ALTER TABLE users ADD COLUMN display_name TEXT; ALTER TABLE users ADD COLUMN default_device_id INTEGER REFERENCES devices(id) ON DELETE SET NULL; ", ]; /// The migration that adds the cell columns. const CELLS_MIGRATION: usize = 5; /// Opens the database and brings its schema up to date. pub fn open(path: &std::path::Path) -> Result> { let mut db = Connection::open(path)?; db.execute_batch("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;")?; migrate(&mut db)?; Ok(db) } fn migrate(db: &mut Connection) -> Result<(), Box> { let version: i64 = db.pragma_query_value(None, "user_version", |r| r.get(0))?; let version = usize::try_from(version)?; if version > MIGRATIONS.len() { return Err(format!( "the database has schema version {version}, newer than this server knows" ) .into()); } // Table rebuilds drop tables that others reference. SQLite ignores this pragma inside a transaction. db.pragma_update(None, "foreign_keys", false)?; for (i, sql) in MIGRATIONS.iter().enumerate().skip(version) { let tx = db.transaction()?; tx.execute_batch(sql)?; if i + 1 == CELLS_MIGRATION { fill_cells(&tx)?; } let broken: bool = tx.query_row( "SELECT EXISTS (SELECT 1 FROM pragma_foreign_key_check)", [], |r| r.get(0), )?; if broken { return Err(format!("migration {} breaks a foreign key", i + 1).into()); } tx.pragma_update(None, "user_version", i as i64 + 1)?; tx.commit()?; } db.pragma_update(None, "foreign_keys", true)?; Ok(()) } /// Computes the cells of every point, in time order per device, as an upload would have. fn fill_cells(db: &Connection) -> rusqlite::Result<()> { let devices: Vec = db .prepare("SELECT id FROM devices")? .query_map([], |r| r.get(0))? .collect::>()?; let mut update = db.prepare(&format!( "UPDATE points SET ({}) = ({}) WHERE device_id = ?1 AND ts = ?2", routes::cell_cols(), (3..3 + 2 * api::PRECISIONS_M.len()) .map(|i| format!("?{i}")) .collect::>() .join(", ") ))?; for device in devices { let points: Vec<(i64, f64, f64)> = db .prepare_cached("SELECT ts, lat, lon FROM points WHERE device_id = ?1 ORDER BY ts")? .query_map([device], |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?)))? .collect::>()?; let mut prev = None; for (ts, lat, lon) in points { let cells = routes::cells(prev.as_ref(), lat, lon); let mut values: Vec<&dyn rusqlite::ToSql> = vec![&device, &ts]; values.extend( cells .iter() .flat_map(|(a, b)| [a as &dyn rusqlite::ToSql, b]), ); update.execute(values.as_slice())?; prev = Some(cells); } } Ok(()) } #[derive(Parser)] #[command(about = "opentracker server")] struct Cli { #[arg(long, env = "OT_ADDR", default_value = "127.0.0.1:8080")] addr: String, #[arg(long, env = "OT_DB", default_value = "ot.db")] db: PathBuf, /// The built web UI. #[arg(long, env = "OT_WEB_DIR", default_value = "web/dist")] web_dir: PathBuf, /// The address browsers use, for example https://track.example.com. /// Passkeys need it behind a reverse proxy. An https URL also marks the session cookie Secure. #[arg(long, env = "OT_PUBLIC_URL", value_parser = parse_public_url)] public_url: Option, /// Days to keep points. Users can lower this for themselves. 0 keeps points forever. #[arg(long, env = "OT_RETENTION_DAYS", default_value_t = 30)] retention_days: i64, /// Set when one reverse proxy forwards all traffic. Rate limits then use the last X-Forwarded-For entry. /// Clients must not reach the server port directly, or they can set that header themselves. #[arg(long, env = "OT_TRUSTED_PROXY")] trusted_proxy: bool, #[command(subcommand)] command: Option, } #[derive(Subcommand)] enum Command { /// Create a user or reset their password. Reads the password from OT_PASSWORD or stdin. /// /// A reset also removes all passkeys, revokes device tokens and turns off two-factor sign-in, so a lost device cannot sign in. Passwd { username: String }, } fn parse_public_url(s: &str) -> Result { let url = Url::parse(s).map_err(|e| e.to_string())?; if !matches!(url.scheme(), "http" | "https") || url.path() != "/" { return Err("must be http(s)://host[:port] without a path".into()); } Ok(url) } #[derive(Clone)] pub struct AppState { // ponytail: one connection behind a global lock. Fine for a few users; use a pool if requests queue up. db: Arc>, limiter: Arc, ceremonies: Arc, pairings: Arc, public_url: Option, /// 0 means forever. max_retention_days: i64, trusted_proxy: bool, } impl AppState { pub fn db(&self) -> MutexGuard<'_, Connection> { self.db.lock().unwrap_or_else(PoisonError::into_inner) } pub fn https(&self) -> bool { self.public_url .as_ref() .is_some_and(|u| u.scheme() == "https") } } #[derive(Debug)] pub enum Error { BadRequest(String), Unauthorized, Forbidden, NotFound, Conflict(String), TooManyRequests, Internal(String), } impl IntoResponse for Error { fn into_response(self) -> Response { match self { Error::BadRequest(msg) => (StatusCode::BAD_REQUEST, msg).into_response(), Error::Unauthorized => StatusCode::UNAUTHORIZED.into_response(), Error::Forbidden => StatusCode::FORBIDDEN.into_response(), Error::NotFound => StatusCode::NOT_FOUND.into_response(), Error::Conflict(msg) => (StatusCode::CONFLICT, msg).into_response(), Error::TooManyRequests => StatusCode::TOO_MANY_REQUESTS.into_response(), Error::Internal(msg) => { eprintln!("internal error: {msg}"); StatusCode::INTERNAL_SERVER_ERROR.into_response() } } } } impl From for Error { fn from(e: rusqlite::Error) -> Self { Error::Internal(e.to_string()) } } pub fn now() -> i64 { SystemTime::now() .duration_since(UNIX_EPOCH) .unwrap() .as_secs() as i64 } #[tokio::main] async fn main() { let cli = Cli::parse(); let mut db = open(&cli.db).unwrap_or_else(|e| { eprintln!("cannot open {}: {e}", cli.db.display()); std::process::exit(1); }); match &cli.command { Some(Command::Passwd { username }) => passwd(&mut db, username), None => serve(cli, db).await, } } pub fn no_users(db: &Connection) -> rusqlite::Result { db.query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| r.get(0)) } /// Turns a UNIQUE violation into a 409 with `msg`. pub fn taken(msg: &str) -> impl FnOnce(rusqlite::Error) -> Error + '_ { move |e| match e { rusqlite::Error::SqliteFailure(f, _) if f.extended_code == rusqlite::ffi::SQLITE_CONSTRAINT_UNIQUE => { Error::Conflict(msg.into()) } e => e.into(), } } /// Inserts a user. The first user ever becomes the admin. pub fn insert_user( db: &Connection, username: &str, pw_hash: &str, is_admin: bool, ) -> Result { let first = no_users(db)?; db.execute( "INSERT INTO users (username, pw_hash, is_admin, webauthn_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5)", params![username, pw_hash, is_admin || first, webauthn_rs::prelude::Uuid::new_v4().to_string(), now()], ) .map_err(taken("that username is taken"))?; Ok(db.last_insert_rowid()) } /// Sets a password and removes every other way in: passkeys, two-factor sign-in, sessions and device tokens. /// A reset often follows a lost device, and its passkey or token must not keep working. /// Pairing codes die with the sessions that began them. pub fn reset_password(db: &mut Connection, user_id: i64, pw_hash: &str) -> rusqlite::Result<()> { let tx = db.transaction()?; tx.execute( "UPDATE users SET pw_hash = ?1, two_factor = 0 WHERE id = ?2", params![pw_hash, user_id], )?; tx.execute("DELETE FROM passkeys WHERE user_id = ?1", [user_id])?; tx.execute("DELETE FROM sessions WHERE user_id = ?1", [user_id])?; // A random hash matches no token. The devices and their history stay, and the owner pairs them again. tx.execute( "UPDATE devices SET token_hash = randomblob(32) WHERE user_id = ?1 AND token_hash IS NOT NULL", [user_id], )?; tx.commit() } pub fn check_username(name: &str) -> Result<&str, Error> { let name = name.trim(); if name.is_empty() || name.chars().count() > 64 { return Err(Error::BadRequest( "username must have 1 to 64 characters".into(), )); } Ok(name) } fn passwd(db: &mut Connection, username: &str) { let password = std::env::var("OT_PASSWORD").unwrap_or_else(|_| { // ponytail: the password echoes on the terminal. Use rpassword if that matters. eprint!("password for {username}: "); let mut line = String::new(); std::io::stdin() .read_line(&mut line) .expect("read password"); line.trim_end_matches(['\r', '\n']).to_owned() }); if let Err(msg) = auth::check_new_password(&password) { eprintln!("{msg}"); std::process::exit(1); } let hash = auth::hash_password(&password); let existing: Option = db .query_row( "SELECT id FROM users WHERE username = ?1", [username], |r| r.get(0), ) .optional() .expect("query user"); match existing { Some(id) => { reset_password(db, id, &hash).expect("update user"); println!( "password reset for {username}: passkeys removed, device tokens revoked, two-factor sign-in off" ); } None => { let username = check_username(username).unwrap_or_else(|e| { eprintln!("{e:?}"); std::process::exit(1); }); insert_user(db, username, &hash, false).expect("insert user"); println!("created user {username}"); } } } async fn serve(cli: Cli, db: Connection) { let state = AppState { db: Arc::new(Mutex::new(db)), limiter: Arc::default(), ceremonies: Arc::default(), pairings: Arc::default(), public_url: cli.public_url, max_retention_days: cli.retention_days.max(0), trusted_proxy: cli.trusted_proxy, }; tokio::spawn(cleanup(state.clone())); let listener = tokio::net::TcpListener::bind(&cli.addr) .await .expect("bind"); println!( "listening on http://{}, serving {}", cli.addr, cli.web_dir.display() ); if no_users(&state.db()).unwrap_or(false) { println!("no users yet: open the web UI to create the admin account"); } let app = routes::router(state, &cli.web_dir); axum::serve( listener, app.into_make_service_with_connect_info::(), ) .with_graceful_shutdown(shutdown()) .await .expect("serve"); } /// In a container the server is process 1, which gets no default signal handling. Without this, `podman stop` waits 10 s and kills it. async fn shutdown() { let mut term = tokio::signal::unix::signal(tokio::signal::unix::SignalKind::terminate()) .expect("SIGTERM handler"); tokio::select! { _ = term.recv() => {} _ = tokio::signal::ctrl_c() => {} } } /// The days of points to keep for a user, or None for forever. pub fn effective_retention(user_days: Option, max_days: i64) -> Option { match (user_days, max_days) { (None, 0) => None, (None, max) => Some(max), (Some(days), 0) => Some(days), (Some(days), max) => Some(days.min(max)), } } /// Deletes points older than the user's retention. Each device keeps its newest point, so it stays on the map. pub fn purge_points( db: &Connection, user_id: i64, user_days: Option, max_days: i64, ) -> rusqlite::Result { match effective_retention(user_days, max_days) { None => Ok(0), Some(days) => db.execute( "DELETE FROM points WHERE device_id IN (SELECT id FROM devices WHERE user_id = ?1) AND ts < ?2 AND ts < (SELECT MAX(ts) FROM points p WHERE p.device_id = points.device_id)", [user_id, now().saturating_sub(days.saturating_mul(86400))], ), } } async fn cleanup(state: AppState) { let mut tick = tokio::time::interval(Duration::from_secs(3600)); loop { tick.tick().await; let now = now(); let db = state.db(); let result = db .execute_batch(&format!( "DELETE FROM sessions WHERE expires_at <= {now}; DELETE FROM shares WHERE expires_at <= {now};" )) .and_then(|()| { let users: Vec<(i64, Option)> = db .prepare("SELECT id, retention_days FROM users")? .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))? .collect::>()?; for (id, days) in users { purge_points(&db, id, days, state.max_retention_days)?; } Ok(()) }); if let Err(e) = result { eprintln!("cleanup failed: {e}"); } drop(db); state.limiter.prune(); } } #[cfg(test)] pub fn test_db() -> Connection { let mut db = Connection::open_in_memory().unwrap(); migrate(&mut db).unwrap(); db } #[cfg(test)] pub fn test_state() -> AppState { AppState { db: Arc::new(Mutex::new(test_db())), limiter: Arc::default(), ceremonies: Arc::default(), pairings: Arc::default(), public_url: None, max_retention_days: 0, trusted_proxy: false, } } #[cfg(test)] mod tests { use super::*; #[test] fn retention_never_exceeds_the_server_limit() { assert_eq!(effective_retention(None, 0), None); assert_eq!(effective_retention(None, 30), Some(30)); assert_eq!(effective_retention(Some(7), 0), Some(7)); assert_eq!(effective_retention(Some(7), 30), Some(7)); assert_eq!(effective_retention(Some(90), 30), Some(30)); } #[test] fn public_url_is_an_origin() { assert!(parse_public_url("https://track.example.com").is_ok()); assert!(parse_public_url("http://localhost:8080/").is_ok()); assert!(parse_public_url("https://example.com/track").is_err()); assert!(parse_public_url("ftp://example.com").is_err()); } #[test] fn reset_removes_every_other_way_in() { let mut db = test_db(); let id = insert_user(&db, "a", "old", false).unwrap(); db.execute_batch( "UPDATE users SET two_factor = 1; INSERT INTO passkeys (user_id, cred_id, passkey, name, created_at) VALUES (1, x'01', '{}', 'k', 0); INSERT INTO sessions (token_hash, user_id, expires_at) VALUES (x'02', 1, 9999999999); INSERT INTO devices (user_id, name, token_hash, created_at) VALUES (1, 'phone', x'03', 0), (1, 'Web', NULL, 0);", ) .unwrap(); reset_password(&mut db, id, "new").unwrap(); let count = |sql: &str| -> i64 { db.query_row(sql, [], |r| r.get(0)).unwrap() }; assert_eq!(count("SELECT COUNT(*) FROM passkeys"), 0); assert_eq!(count("SELECT COUNT(*) FROM sessions"), 0); assert_eq!(count("SELECT two_factor FROM users"), 0); assert_eq!( count("SELECT COUNT(*) FROM devices WHERE token_hash = x'03'"), 0 ); assert_eq!(count("SELECT COUNT(*) FROM devices"), 2); } #[test] fn migrations_keep_old_points() { let mut db = Connection::open_in_memory().unwrap(); db.execute_batch(MIGRATIONS[0]).unwrap(); db.execute_batch( "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0); INSERT INTO devices (id, user_id, name, token_hash, created_at, last_seen_at) VALUES (7, 1, 'old', x'01', 0, 5), (8, 1, 'new', x'02', 0, 9); INSERT INTO points (user_id, ts, lat, lon) VALUES (1, 100, 1, 1), (2, 100, 2, 2);", ) .unwrap(); migrate(&mut db).unwrap(); migrate(&mut db).unwrap(); let owners: Vec<(i64, String)> = db .prepare("SELECT d.user_id, d.name FROM points p JOIN devices d ON d.id = p.device_id ORDER BY d.user_id") .unwrap() .query_map([], |r| Ok((r.get(0)?, r.get(1)?))) .unwrap() .collect::>() .unwrap(); assert_eq!(owners, [(1, "new".into()), (2, "Web".into())]); } #[test] fn trail_columns_merge_into_one() { let mut db = Connection::open_in_memory().unwrap(); for sql in &MIGRATIONS[..3] { db.execute_batch(sql).unwrap(); } db.pragma_update(None, "user_version", 3).unwrap(); db.execute_batch( "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0), (3, 'c', '3', 0), (4, 'd', '4', 0); INSERT INTO shares (owner_id, viewer_id, created_at, trail, trail_since) VALUES (1, 2, 0, 0, NULL), (1, 3, 0, 1, NULL), (1, 4, 0, 1, 99);", ) .unwrap(); migrate(&mut db).unwrap(); let since: Vec> = db .prepare("SELECT trail_since FROM shares ORDER BY viewer_id") .unwrap() .query_map([], |r| r.get(0)) .unwrap() .collect::>() .unwrap(); assert_eq!(since, [None, Some(0), Some(99)]); } #[test] fn cells_fill_in_time_order_and_precisions_round_up() { let mut db = Connection::open_in_memory().unwrap(); for sql in &MIGRATIONS[..CELLS_MIGRATION - 1] { db.execute_batch(sql).unwrap(); } db.pragma_update(None, "user_version", CELLS_MIGRATION as i64 - 1) .unwrap(); db.execute_batch( "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0); INSERT INTO devices (id, user_id, name, token_hash, created_at) VALUES (1, 1, 'p', x'01', 0); INSERT INTO points (device_id, ts, lat, lon) VALUES (1, 20, 0.0049, 0), (1, 10, 0.0044, 0); INSERT INTO shares (owner_id, viewer_id, created_at, precision_m) VALUES (1, 2, 0, 500);", ) .unwrap(); migrate(&mut db).unwrap(); let first = routes::cells(None, 0.0044, 0.0); let second = routes::cells(Some(&first), 0.0049, 0.0); // The later point sticks to the earlier point's 1 km cell, though alone it would round to the next. assert_ne!(routes::cells(None, 0.0049, 0.0)[1], second[1]); let stored: (f64, i64) = db .query_row( "SELECT lat_1000, (SELECT precision_m FROM shares) FROM points WHERE ts = 20", [], |r| Ok((r.get(0)?, r.get(1)?)), ) .unwrap(); assert_eq!(stored, (second[1].0, 1000)); } #[test] fn retention_keeps_each_devices_newest_point() { let db = test_db(); db.execute_batch( "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0); INSERT INTO devices (id, user_id, name, token_hash, created_at) VALUES (1, 1, 'p', x'01', 0), (2, 1, 'q', x'02', 0); INSERT INTO points (device_id, ts, lat, lon) VALUES (1, 10, 0, 0), (1, 20, 0, 0), (2, 15, 0, 0);", ) .unwrap(); purge_points(&db, 1, Some(1), 30).unwrap(); let left: Vec<(i64, i64)> = db .prepare("SELECT device_id, ts FROM points ORDER BY device_id") .unwrap() .query_map([], |r| Ok((r.get(0)?, r.get(1)?))) .unwrap() .collect::>() .unwrap(); assert_eq!(left, [(1, 20), (2, 15)]); } #[test] fn first_user_is_admin() { let db = test_db(); let a = insert_user(&db, "a", "", false).unwrap(); let b = insert_user(&db, "b", "", false).unwrap(); let admin = |id: i64| -> bool { db.query_row("SELECT is_admin FROM users WHERE id = ?1", [id], |r| { r.get(0) }) .unwrap() }; assert!(admin(a)); assert!(!admin(b)); assert!(matches!( insert_user(&db, "A", "", false), Err(Error::Conflict(_)) )); } }