opentracker

opentracker/crates/server/src/main.rs
main.rs
⎇
Raw
1//! opentracker server: one binary, one SQLite file.
2
3mod auth;
4mod device;
5mod guest;
6mod passkeys;
7mod routes;
8
9use std::path::PathBuf;
10use std::sync::{Arc, Mutex, MutexGuard, PoisonError};
11use std::time::{Duration, SystemTime, UNIX_EPOCH};
12
13use axum::http::StatusCode;
14use axum::response::{IntoResponse, Response};
15use clap::{Parser, Subcommand};
16use rusqlite::{Connection, OptionalExtension, params};
17use webauthn_rs::prelude::Url;
18
19/// Schema steps, applied in order. `PRAGMA user_version` counts the applied ones.
20/// Never edit a released step. Append a new one.
21const MIGRATIONS: &[&str] = &[
22 // IF NOT EXISTS: databases from before migrations have these tables at version 0.
23 "
24CREATE TABLE IF NOT EXISTS users (
25 id INTEGER PRIMARY KEY,
26 username TEXT NOT NULL UNIQUE COLLATE NOCASE,
27 -- NULL when the account signs in with passkeys only.
28 pw_hash TEXT,
29 is_admin INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0, 1)),
30 two_factor INTEGER NOT NULL DEFAULT 0 CHECK (two_factor IN (0, 1)),
31 -- The WebAuthn user handle. Random, so a passkey does not reveal the user id.
32 webauthn_id TEXT NOT NULL UNIQUE,
33 retention_days INTEGER CHECK (retention_days > 0),
34 created_at INTEGER NOT NULL
35) STRICT;
36
37CREATE TABLE IF NOT EXISTS sessions (
38 token_hash BLOB PRIMARY KEY,
39 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
40 expires_at INTEGER NOT NULL
41) STRICT;
42
43CREATE TABLE IF NOT EXISTS passkeys (
44 id INTEGER PRIMARY KEY,
45 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
46 cred_id BLOB NOT NULL UNIQUE,
47 -- webauthn_rs::prelude::Passkey as JSON.
48 passkey TEXT NOT NULL,
49 name TEXT NOT NULL,
50 created_at INTEGER NOT NULL,
51 last_used_at INTEGER
52) STRICT;
53
54CREATE TABLE IF NOT EXISTS devices (
55 id INTEGER PRIMARY KEY,
56 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
57 name TEXT NOT NULL,
58 token_hash BLOB NOT NULL UNIQUE,
59 created_at INTEGER NOT NULL,
60 last_seen_at INTEGER
61) STRICT;
62
63-- The key makes uploads idempotent: a retried batch collapses into the rows already there.
64CREATE TABLE IF NOT EXISTS points (
65 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
66 ts INTEGER NOT NULL,
67 lat REAL NOT NULL,
68 lon REAL NOT NULL,
69 acc REAL,
70 alt REAL,
71 speed REAL,
72 bearing REAL,
73 battery INTEGER,
74 PRIMARY KEY (user_id, ts)
75) STRICT, WITHOUT ROWID;
76
77CREATE INDEX IF NOT EXISTS points_ts ON points(ts);
78
79CREATE TABLE IF NOT EXISTS shares (
80 id INTEGER PRIMARY KEY,
81 owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
82 viewer_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
83 expires_at INTEGER,
84 created_at INTEGER NOT NULL,
85 UNIQUE (owner_id, viewer_id),
86 CHECK (owner_id <> viewer_id)
87) STRICT;
88",
89 // Points belong to a device. The web UI uploads as one device per user, which has no token.
90 "
91CREATE TABLE devices_new (
92 id INTEGER PRIMARY KEY,
93 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
94 name TEXT NOT NULL,
95 -- NULL for the web device.
96 token_hash BLOB UNIQUE,
97 created_at INTEGER NOT NULL,
98 last_seen_at INTEGER
99) STRICT;
100INSERT INTO devices_new SELECT * FROM devices;
101DROP TABLE devices;
102ALTER TABLE devices_new RENAME TO devices;
103CREATE UNIQUE INDEX devices_web ON devices(user_id) WHERE token_hash IS NULL;
104
105-- Older points go to the device that uploaded last, or to a new web device.
106INSERT INTO devices (user_id, name, created_at)
107SELECT DISTINCT user_id, 'Web', unixepoch() FROM points
108WHERE user_id NOT IN (SELECT user_id FROM devices);
109
110-- The key makes uploads idempotent: a retried batch collapses into the rows already there.
111CREATE TABLE points_new (
112 device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
113 ts INTEGER NOT NULL,
114 lat REAL NOT NULL,
115 lon REAL NOT NULL,
116 acc REAL,
117 alt REAL,
118 speed REAL,
119 bearing REAL,
120 battery INTEGER,
121 PRIMARY KEY (device_id, ts)
122) STRICT, WITHOUT ROWID;
123INSERT INTO points_new
124SELECT (SELECT d.id FROM devices d WHERE d.user_id = p.user_id
125 ORDER BY d.last_seen_at DESC NULLS LAST, d.id LIMIT 1),
126 ts, lat, lon, acc, alt, speed, bearing, battery
127FROM points p;
128DROP TABLE points;
129ALTER TABLE points_new RENAME TO points;
130CREATE INDEX points_ts ON points(ts);
131
132ALTER TABLE shares ADD COLUMN all_devices INTEGER NOT NULL DEFAULT 1 CHECK (all_devices IN (0, 1));
133ALTER TABLE shares ADD COLUMN trail INTEGER NOT NULL DEFAULT 1 CHECK (trail IN (0, 1));
134ALTER TABLE shares ADD COLUMN precision_m INTEGER NOT NULL DEFAULT 0 CHECK (precision_m >= 0);
135-- Used only when all_devices is 0.
136CREATE TABLE share_devices (
137 share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
138 device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
139 PRIMARY KEY (share_id, device_id)
140) STRICT, WITHOUT ROWID;
141",
142 // Trails can start at a time. Guest links are shares without a viewer.
143 "
144CREATE TABLE shares_new (
145 id INTEGER PRIMARY KEY,
146 owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
147 -- NULL for a guest link.
148 viewer_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
149 expires_at INTEGER,
150 created_at INTEGER NOT NULL,
151 all_devices INTEGER NOT NULL DEFAULT 1 CHECK (all_devices IN (0, 1)),
152 trail INTEGER NOT NULL DEFAULT 1 CHECK (trail IN (0, 1)),
153 trail_since INTEGER,
154 precision_m INTEGER NOT NULL DEFAULT 0 CHECK (precision_m >= 0),
155 -- Guest links only. Kept readable, so the owner can copy the link again.
156 name TEXT,
157 token TEXT UNIQUE,
158 pw_hash TEXT,
159 UNIQUE (owner_id, viewer_id),
160 CHECK (owner_id <> viewer_id),
161 CHECK ((viewer_id IS NULL) = (token IS NOT NULL))
162) STRICT;
163INSERT INTO shares_new (id, owner_id, viewer_id, expires_at, created_at, all_devices, trail, precision_m)
164SELECT id, owner_id, viewer_id, expires_at, created_at, all_devices, trail, precision_m FROM shares;
165DROP TABLE shares;
166ALTER TABLE shares_new RENAME TO shares;
167",
168 // One column for the trail: NULL shows only the current position, 0 the full history.
169 "
170UPDATE shares SET trail_since = CASE WHEN trail = 0 THEN NULL ELSE COALESCE(trail_since, 0) END;
171ALTER TABLE shares DROP COLUMN trail;
172",
173];
174
175/// Opens the database and brings its schema up to date.
176pub fn open(path: &std::path::Path) -> Result<Connection, Box<dyn std::error::Error>> {
177 let mut db = Connection::open(path)?;
178 db.execute_batch("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;")?;
179 migrate(&mut db)?;
180 Ok(db)
181}
182
183fn migrate(db: &mut Connection) -> Result<(), Box<dyn std::error::Error>> {
184 let version: i64 = db.pragma_query_value(None, "user_version", |r| r.get(0))?;
185 let version = usize::try_from(version)?;
186 if version > MIGRATIONS.len() {
187 return Err(format!(
188 "the database has schema version {version}, newer than this server knows"
189 )
190 .into());
191 }
192 // Table rebuilds drop tables that others reference. SQLite ignores this pragma inside a transaction.
193 db.pragma_update(None, "foreign_keys", false)?;
194 for (i, sql) in MIGRATIONS.iter().enumerate().skip(version) {
195 let tx = db.transaction()?;
196 tx.execute_batch(sql)?;
197 let broken: bool = tx.query_row(
198 "SELECT EXISTS (SELECT 1 FROM pragma_foreign_key_check)",
199 [],
200 |r| r.get(0),
201 )?;
202 if broken {
203 return Err(format!("migration {} breaks a foreign key", i + 1).into());
204 }
205 tx.pragma_update(None, "user_version", i as i64 + 1)?;
206 tx.commit()?;
207 }
208 db.pragma_update(None, "foreign_keys", true)?;
209 Ok(())
210}
211
212#[derive(Parser)]
213#[command(about = "opentracker server")]
214struct Cli {
215 #[arg(long, env = "OT_ADDR", default_value = "127.0.0.1:8080")]
216 addr: String,
217 #[arg(long, env = "OT_DB", default_value = "ot.db")]
218 db: PathBuf,
219 /// The built web UI.
220 #[arg(long, env = "OT_WEB_DIR", default_value = "web/dist")]
221 web_dir: PathBuf,
222 /// The address browsers use, for example https://track.example.com.
223 /// Passkeys need it behind a reverse proxy. An https URL also marks the session cookie Secure.
224 #[arg(long, env = "OT_PUBLIC_URL", value_parser = parse_public_url)]
225 public_url: Option<Url>,
226 /// Days to keep points. Users can lower this for themselves. 0 keeps points forever.
227 #[arg(long, env = "OT_RETENTION_DAYS", default_value_t = 30)]
228 retention_days: i64,
229 /// Set when one reverse proxy forwards all traffic. Rate limits then use the last X-Forwarded-For entry.
230 /// Clients must not reach the server port directly, or they can set that header themselves.
231 #[arg(long, env = "OT_BEHIND_PROXY")]
232 behind_proxy: bool,
233 #[command(subcommand)]
234 command: Option<Command>,
235}
236
237#[derive(Subcommand)]
238enum Command {
239 /// Create a user or reset their password. Reads the password from OT_PASSWORD or stdin.
240 ///
241 /// A reset also removes all passkeys, revokes device tokens and turns off two-factor sign-in, so a lost device cannot sign in.
242 Passwd { username: String },
243}
244
245fn parse_public_url(s: &str) -> Result<Url, String> {
246 let url = Url::parse(s).map_err(|e| e.to_string())?;
247 if !matches!(url.scheme(), "http" | "https") || url.path() != "/" {
248 return Err("must be http(s)://host[:port] without a path".into());
249 }
250 Ok(url)
251}
252
253#[derive(Clone)]
254pub struct AppState {
255 // ponytail: one connection behind a global lock. Fine for a few users; use a pool if requests queue up.
256 db: Arc<Mutex<Connection>>,
257 limiter: Arc<auth::Limiter>,
258 ceremonies: Arc<passkeys::Ceremonies>,
259 pairings: Arc<device::Pairings>,
260 public_url: Option<Url>,
261 /// 0 means forever.
262 max_retention_days: i64,
263 behind_proxy: bool,
264}
265
266impl AppState {
267 pub fn db(&self) -> MutexGuard<'_, Connection> {
268 self.db.lock().unwrap_or_else(PoisonError::into_inner)
269 }
270
271 pub fn https(&self) -> bool {
272 self.public_url
273 .as_ref()
274 .is_some_and(|u| u.scheme() == "https")
275 }
276}
277
278#[derive(Debug)]
279pub enum Error {
280 BadRequest(String),
281 Unauthorized,
282 Forbidden,
283 NotFound,
284 Conflict(String),
285 TooManyRequests,
286 Internal(String),
287}
288
289impl IntoResponse for Error {
290 fn into_response(self) -> Response {
291 match self {
292 Error::BadRequest(msg) => (StatusCode::BAD_REQUEST, msg).into_response(),
293 Error::Unauthorized => StatusCode::UNAUTHORIZED.into_response(),
294 Error::Forbidden => StatusCode::FORBIDDEN.into_response(),
295 Error::NotFound => StatusCode::NOT_FOUND.into_response(),
296 Error::Conflict(msg) => (StatusCode::CONFLICT, msg).into_response(),
297 Error::TooManyRequests => StatusCode::TOO_MANY_REQUESTS.into_response(),
298 Error::Internal(msg) => {
299 eprintln!("internal error: {msg}");
300 StatusCode::INTERNAL_SERVER_ERROR.into_response()
301 }
302 }
303 }
304}
305
306impl From<rusqlite::Error> for Error {
307 fn from(e: rusqlite::Error) -> Self {
308 Error::Internal(e.to_string())
309 }
310}
311
312pub fn now() -> i64 {
313 SystemTime::now()
314 .duration_since(UNIX_EPOCH)
315 .unwrap()
316 .as_secs() as i64
317}
318
319#[tokio::main]
320async fn main() {
321 let cli = Cli::parse();
322 let db = open(&cli.db).unwrap_or_else(|e| {
323 eprintln!("cannot open {}: {e}", cli.db.display());
324 std::process::exit(1);
325 });
326
327 match &cli.command {
328 Some(Command::Passwd { username }) => passwd(&db, username),
329 None => serve(cli, db).await,
330 }
331}
332
333/// Inserts a user. The first user ever becomes the admin.
334pub fn insert_user(
335 db: &Connection,
336 username: &str,
337 pw_hash: &str,
338 is_admin: bool,
339) -> Result<i64, Error> {
340 let first: bool = db.query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| r.get(0))?;
341 db.execute(
342 "INSERT INTO users (username, pw_hash, is_admin, webauthn_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
343 params![username, pw_hash, is_admin || first, uuid::Uuid::new_v4().to_string(), now()],
344 )
345 .map_err(|e| match e {
346 rusqlite::Error::SqliteFailure(f, _) if f.extended_code == rusqlite::ffi::SQLITE_CONSTRAINT_UNIQUE => {
347 Error::Conflict("that username is taken".into())
348 }
349 e => e.into(),
350 })?;
351 Ok(db.last_insert_rowid())
352}
353
354/// Sets a password and removes every other way in: passkeys, two-factor sign-in, sessions and device tokens.
355/// A reset often follows a lost device, and its passkey or token must not keep working.
356pub fn reset_password(db: &Connection, user_id: i64, pw_hash: &str) -> rusqlite::Result<()> {
357 db.execute(
358 "UPDATE users SET pw_hash = ?1, two_factor = 0 WHERE id = ?2",
359 params![pw_hash, user_id],
360 )?;
361 db.execute("DELETE FROM passkeys WHERE user_id = ?1", [user_id])?;
362 db.execute("DELETE FROM sessions WHERE user_id = ?1", [user_id])?;
363 // A random hash matches no token. The devices and their history stay, and the owner pairs them again.
364 db.execute(
365 "UPDATE devices SET token_hash = randomblob(32) WHERE user_id = ?1 AND token_hash IS NOT NULL",
366 [user_id],
367 )?;
368 Ok(())
369}
370
371pub fn check_username(name: &str) -> Result<&str, Error> {
372 let name = name.trim();
373 if name.is_empty() || name.chars().count() > 64 {
374 return Err(Error::BadRequest(
375 "username must have 1 to 64 characters".into(),
376 ));
377 }
378 Ok(name)
379}
380
381fn passwd(db: &Connection, username: &str) {
382 let password = std::env::var("OT_PASSWORD").unwrap_or_else(|_| {
383 // ponytail: the password echoes on the terminal. Use rpassword if that matters.
384 eprint!("password for {username}: ");
385 let mut line = String::new();
386 std::io::stdin()
387 .read_line(&mut line)
388 .expect("read password");
389 line.trim_end_matches(['\r', '\n']).to_owned()
390 });
391 if let Err(msg) = auth::check_new_password(&password) {
392 eprintln!("{msg}");
393 std::process::exit(1);
394 }
395 let hash = auth::hash_password(&password);
396 let existing: Option<i64> = db
397 .query_row(
398 "SELECT id FROM users WHERE username = ?1",
399 [username],
400 |r| r.get(0),
401 )
402 .optional()
403 .expect("query user");
404 match existing {
405 Some(id) => {
406 reset_password(db, id, &hash).expect("update user");
407 println!(
408 "password reset for {username}: passkeys removed, device tokens revoked, two-factor sign-in off"
409 );
410 }
411 None => {
412 let username = check_username(username).unwrap_or_else(|e| {
413 eprintln!("{e:?}");
414 std::process::exit(1);
415 });
416 insert_user(db, username, &hash, false).expect("insert user");
417 println!("created user {username}");
418 }
419 }
420}
421
422async fn serve(cli: Cli, db: Connection) {
423 let state = AppState {
424 db: Arc::new(Mutex::new(db)),
425 limiter: Arc::default(),
426 ceremonies: Arc::default(),
427 pairings: Arc::default(),
428 public_url: cli.public_url,
429 max_retention_days: cli.retention_days.max(0),
430 behind_proxy: cli.behind_proxy,
431 };
432 tokio::spawn(cleanup(state.clone()));
433
434 let listener = tokio::net::TcpListener::bind(&cli.addr)
435 .await
436 .expect("bind");
437 println!(
438 "listening on http://{}, serving {}",
439 cli.addr,
440 cli.web_dir.display()
441 );
442 if state
443 .db()
444 .query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| {
445 r.get::<_, bool>(0)
446 })
447 .unwrap_or(false)
448 {
449 println!("no users yet: open the web UI to create the admin account");
450 }
451 let app = routes::router(state, &cli.web_dir);
452 axum::serve(
453 listener,
454 app.into_make_service_with_connect_info::<std::net::SocketAddr>(),
455 )
456 .with_graceful_shutdown(shutdown())
457 .await
458 .expect("serve");
459}
460
461/// In a container the server is process 1, which gets no default signal handling. Without this, `podman stop` waits 10 s and kills it.
462async fn shutdown() {
463 let mut term = tokio::signal::unix::signal(tokio::signal::unix::SignalKind::terminate())
464 .expect("SIGTERM handler");
465 tokio::select! {
466 _ = term.recv() => {}
467 _ = tokio::signal::ctrl_c() => {}
468 }
469}
470
471/// The days of points to keep for a user, or None for forever.
472pub fn effective_retention(user_days: Option<i64>, max_days: i64) -> Option<i64> {
473 match (user_days, max_days) {
474 (None, 0) => None,
475 (None, max) => Some(max),
476 (Some(days), 0) => Some(days),
477 (Some(days), max) => Some(days.min(max)),
478 }
479}
480
481/// Deletes points older than the user's retention. Each device keeps its newest point, so it stays on the map.
482pub fn purge_points(
483 db: &Connection,
484 user_id: i64,
485 user_days: Option<i64>,
486 max_days: i64,
487) -> rusqlite::Result<usize> {
488 match effective_retention(user_days, max_days) {
489 None => Ok(0),
490 Some(days) => db.execute(
491 "DELETE FROM points
492 WHERE device_id IN (SELECT id FROM devices WHERE user_id = ?1) AND ts < ?2
493 AND ts < (SELECT MAX(ts) FROM points p WHERE p.device_id = points.device_id)",
494 [user_id, now() - days * 86400],
495 ),
496 }
497}
498
499async fn cleanup(state: AppState) {
500 let mut tick = tokio::time::interval(Duration::from_secs(3600));
501 loop {
502 tick.tick().await;
503 let now = now();
504 let db = state.db();
505 let result = db
506 .execute_batch(&format!(
507 "DELETE FROM sessions WHERE expires_at <= {now};
508 DELETE FROM shares WHERE expires_at <= {now};"
509 ))
510 .and_then(|()| {
511 let users: Vec<(i64, Option<i64>)> = db
512 .prepare("SELECT id, retention_days FROM users")?
513 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?
514 .collect::<rusqlite::Result<_>>()?;
515 for (id, days) in users {
516 purge_points(&db, id, days, state.max_retention_days)?;
517 }
518 Ok(())
519 });
520 if let Err(e) = result {
521 eprintln!("cleanup failed: {e}");
522 }
523 drop(db);
524 state.limiter.prune();
525 }
526}
527
528#[cfg(test)]
529pub fn test_db() -> Connection {
530 let mut db = Connection::open_in_memory().unwrap();
531 migrate(&mut db).unwrap();
532 db
533}
534
535#[cfg(test)]
536mod tests {
537 use super::*;
538
539 #[test]
540 fn retention_never_exceeds_the_server_limit() {
541 assert_eq!(effective_retention(None, 0), None);
542 assert_eq!(effective_retention(None, 30), Some(30));
543 assert_eq!(effective_retention(Some(7), 0), Some(7));
544 assert_eq!(effective_retention(Some(7), 30), Some(7));
545 assert_eq!(effective_retention(Some(90), 30), Some(30));
546 }
547
548 #[test]
549 fn public_url_is_an_origin() {
550 assert!(parse_public_url("https://track.example.com").is_ok());
551 assert!(parse_public_url("http://localhost:8080/").is_ok());
552 assert!(parse_public_url("https://example.com/track").is_err());
553 assert!(parse_public_url("ftp://example.com").is_err());
554 }
555
556 #[test]
557 fn reset_removes_every_other_way_in() {
558 let db = test_db();
559 let id = insert_user(&db, "a", "old", false).unwrap();
560 db.execute_batch(
561 "UPDATE users SET two_factor = 1;
562 INSERT INTO passkeys (user_id, cred_id, passkey, name, created_at) VALUES (1, x'01', '{}', 'k', 0);
563 INSERT INTO sessions (token_hash, user_id, expires_at) VALUES (x'02', 1, 9999999999);
564 INSERT INTO devices (user_id, name, token_hash, created_at) VALUES (1, 'phone', x'03', 0), (1, 'Web', NULL, 0);",
565 )
566 .unwrap();
567 reset_password(&db, id, "new").unwrap();
568 let count = |sql: &str| -> i64 { db.query_row(sql, [], |r| r.get(0)).unwrap() };
569 assert_eq!(count("SELECT COUNT(*) FROM passkeys"), 0);
570 assert_eq!(count("SELECT COUNT(*) FROM sessions"), 0);
571 assert_eq!(count("SELECT two_factor FROM users"), 0);
572 assert_eq!(
573 count("SELECT COUNT(*) FROM devices WHERE token_hash = x'03'"),
574 0
575 );
576 assert_eq!(count("SELECT COUNT(*) FROM devices"), 2);
577 }
578
579 #[test]
580 fn migrations_keep_old_points() {
581 let mut db = Connection::open_in_memory().unwrap();
582 db.execute_batch(MIGRATIONS[0]).unwrap();
583 db.execute_batch(
584 "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0);
585 INSERT INTO devices (id, user_id, name, token_hash, created_at, last_seen_at)
586 VALUES (7, 1, 'old', x'01', 0, 5), (8, 1, 'new', x'02', 0, 9);
587 INSERT INTO points (user_id, ts, lat, lon) VALUES (1, 100, 1, 1), (2, 100, 2, 2);",
588 )
589 .unwrap();
590 migrate(&mut db).unwrap();
591 migrate(&mut db).unwrap();
592 let owners: Vec<(i64, String)> = db
593 .prepare("SELECT d.user_id, d.name FROM points p JOIN devices d ON d.id = p.device_id ORDER BY d.user_id")
594 .unwrap()
595 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
596 .unwrap()
597 .collect::<rusqlite::Result<_>>()
598 .unwrap();
599 assert_eq!(owners, [(1, "new".into()), (2, "Web".into())]);
600 }
601
602 #[test]
603 fn trail_columns_merge_into_one() {
604 let mut db = Connection::open_in_memory().unwrap();
605 for sql in &MIGRATIONS[..3] {
606 db.execute_batch(sql).unwrap();
607 }
608 db.pragma_update(None, "user_version", 3).unwrap();
609 db.execute_batch(
610 "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);
611 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);",
612 )
613 .unwrap();
614 migrate(&mut db).unwrap();
615 let since: Vec<Option<i64>> = db
616 .prepare("SELECT trail_since FROM shares ORDER BY viewer_id")
617 .unwrap()
618 .query_map([], |r| r.get(0))
619 .unwrap()
620 .collect::<rusqlite::Result<_>>()
621 .unwrap();
622 assert_eq!(since, [None, Some(0), Some(99)]);
623 }
624
625 #[test]
626 fn retention_keeps_each_devices_newest_point() {
627 let db = test_db();
628 db.execute_batch(
629 "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0);
630 INSERT INTO devices (id, user_id, name, token_hash, created_at) VALUES (1, 1, 'p', x'01', 0), (2, 1, 'q', x'02', 0);
631 INSERT INTO points (device_id, ts, lat, lon) VALUES (1, 10, 0, 0), (1, 20, 0, 0), (2, 15, 0, 0);",
632 )
633 .unwrap();
634 purge_points(&db, 1, Some(1), 30).unwrap();
635 let left: Vec<(i64, i64)> = db
636 .prepare("SELECT device_id, ts FROM points ORDER BY device_id")
637 .unwrap()
638 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
639 .unwrap()
640 .collect::<rusqlite::Result<_>>()
641 .unwrap();
642 assert_eq!(left, [(1, 20), (2, 15)]);
643 }
644
645 #[test]
646 fn first_user_is_admin() {
647 let db = test_db();
648 let a = insert_user(&db, "a", "", false).unwrap();
649 let b = insert_user(&db, "b", "", false).unwrap();
650 let admin = |id: i64| -> bool {
651 db.query_row("SELECT is_admin FROM users WHERE id = ?1", [id], |r| {
652 r.get(0)
653 })
654 .unwrap()
655 };
656 assert!(admin(a));
657 assert!(!admin(b));
658 assert!(matches!(
659 insert_user(&db, "A", "", false),
660 Err(Error::Conflict(_))
661 ));
662 }
663}
664