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];
169
170/// Opens the database and brings its schema up to date.
171pub fn open(path: &std::path::Path) -> Result<Connection, Box<dyn std::error::Error>> {
172 let mut db = Connection::open(path)?;
173 db.execute_batch("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;")?;
174 migrate(&mut db)?;
175 Ok(db)
176}
177
178fn migrate(db: &mut Connection) -> Result<(), Box<dyn std::error::Error>> {
179 let version: i64 = db.pragma_query_value(None, "user_version", |r| r.get(0))?;
180 let version = usize::try_from(version)?;
181 if version > MIGRATIONS.len() {
182 return Err(format!(
183 "the database has schema version {version}, newer than this server knows"
184 )
185 .into());
186 }
187 // Table rebuilds drop tables that others reference. SQLite ignores this pragma inside a transaction.
188 db.pragma_update(None, "foreign_keys", false)?;
189 for (i, sql) in MIGRATIONS.iter().enumerate().skip(version) {
190 let tx = db.transaction()?;
191 tx.execute_batch(sql)?;
192 let broken: bool = tx.query_row(
193 "SELECT EXISTS (SELECT 1 FROM pragma_foreign_key_check)",
194 [],
195 |r| r.get(0),
196 )?;
197 if broken {
198 return Err(format!("migration {} breaks a foreign key", i + 1).into());
199 }
200 tx.pragma_update(None, "user_version", i as i64 + 1)?;
201 tx.commit()?;
202 }
203 db.pragma_update(None, "foreign_keys", true)?;
204 Ok(())
205}
206
207#[derive(Parser)]
208#[command(about = "opentracker server")]
209struct Cli {
210 #[arg(long, env = "OT_ADDR", default_value = "127.0.0.1:8080")]
211 addr: String,
212 #[arg(long, env = "OT_DB", default_value = "ot.db")]
213 db: PathBuf,
214 /// The built web UI.
215 #[arg(long, env = "OT_WEB_DIR", default_value = "web/dist")]
216 web_dir: PathBuf,
217 /// The address browsers use, for example https://track.example.com.
218 /// Passkeys need it behind a reverse proxy. An https URL also marks the session cookie Secure.
219 #[arg(long, env = "OT_PUBLIC_URL", value_parser = parse_public_url)]
220 public_url: Option<Url>,
221 /// Days to keep points. Users can lower this for themselves. 0 keeps points forever.
222 #[arg(long, env = "OT_RETENTION_DAYS", default_value_t = 30)]
223 retention_days: i64,
224 /// Set when one reverse proxy forwards all traffic. Rate limits then use the last X-Forwarded-For entry.
225 /// Clients must not reach the server port directly, or they can set that header themselves.
226 #[arg(long, env = "OT_BEHIND_PROXY")]
227 behind_proxy: bool,
228 #[command(subcommand)]
229 command: Option<Command>,
230}
231
232#[derive(Subcommand)]
233enum Command {
234 /// Create a user or reset their password. Reads the password from OT_PASSWORD or stdin.
235 ///
236 /// A reset also removes all passkeys, revokes device tokens and turns off two-factor sign-in, so a lost device cannot sign in.
237 Passwd { username: String },
238}
239
240fn parse_public_url(s: &str) -> Result<Url, String> {
241 let url = Url::parse(s).map_err(|e| e.to_string())?;
242 if !matches!(url.scheme(), "http" | "https") || url.path() != "/" {
243 return Err("must be http(s)://host[:port] without a path".into());
244 }
245 Ok(url)
246}
247
248#[derive(Clone)]
249pub struct AppState {
250 // ponytail: one connection behind a global lock. Fine for a few users; use a pool if requests queue up.
251 db: Arc<Mutex<Connection>>,
252 limiter: Arc<auth::Limiter>,
253 ceremonies: Arc<passkeys::Ceremonies>,
254 pairings: Arc<device::Pairings>,
255 public_url: Option<Url>,
256 /// 0 means forever.
257 max_retention_days: i64,
258 behind_proxy: bool,
259}
260
261impl AppState {
262 pub fn db(&self) -> MutexGuard<'_, Connection> {
263 self.db.lock().unwrap_or_else(PoisonError::into_inner)
264 }
265
266 pub fn https(&self) -> bool {
267 self.public_url
268 .as_ref()
269 .is_some_and(|u| u.scheme() == "https")
270 }
271}
272
273#[derive(Debug)]
274pub enum Error {
275 BadRequest(String),
276 Unauthorized,
277 Forbidden,
278 NotFound,
279 Conflict(String),
280 TooManyRequests,
281 Internal(String),
282}
283
284impl IntoResponse for Error {
285 fn into_response(self) -> Response {
286 match self {
287 Error::BadRequest(msg) => (StatusCode::BAD_REQUEST, msg).into_response(),
288 Error::Unauthorized => StatusCode::UNAUTHORIZED.into_response(),
289 Error::Forbidden => StatusCode::FORBIDDEN.into_response(),
290 Error::NotFound => StatusCode::NOT_FOUND.into_response(),
291 Error::Conflict(msg) => (StatusCode::CONFLICT, msg).into_response(),
292 Error::TooManyRequests => StatusCode::TOO_MANY_REQUESTS.into_response(),
293 Error::Internal(msg) => {
294 eprintln!("internal error: {msg}");
295 StatusCode::INTERNAL_SERVER_ERROR.into_response()
296 }
297 }
298 }
299}
300
301impl From<rusqlite::Error> for Error {
302 fn from(e: rusqlite::Error) -> Self {
303 Error::Internal(e.to_string())
304 }
305}
306
307pub fn now() -> i64 {
308 SystemTime::now()
309 .duration_since(UNIX_EPOCH)
310 .unwrap()
311 .as_secs() as i64
312}
313
314#[tokio::main]
315async fn main() {
316 let cli = Cli::parse();
317 let db = open(&cli.db).unwrap_or_else(|e| {
318 eprintln!("cannot open {}: {e}", cli.db.display());
319 std::process::exit(1);
320 });
321
322 match &cli.command {
323 Some(Command::Passwd { username }) => passwd(&db, username),
324 None => serve(cli, db).await,
325 }
326}
327
328/// Inserts a user. The first user ever becomes the admin.
329pub fn insert_user(
330 db: &Connection,
331 username: &str,
332 pw_hash: &str,
333 is_admin: bool,
334) -> Result<i64, Error> {
335 let first: bool = db.query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| r.get(0))?;
336 db.execute(
337 "INSERT INTO users (username, pw_hash, is_admin, webauthn_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
338 params![username, pw_hash, is_admin || first, uuid::Uuid::new_v4().to_string(), now()],
339 )
340 .map_err(|e| match e {
341 rusqlite::Error::SqliteFailure(f, _) if f.extended_code == rusqlite::ffi::SQLITE_CONSTRAINT_UNIQUE => {
342 Error::Conflict("that username is taken".into())
343 }
344 e => e.into(),
345 })?;
346 Ok(db.last_insert_rowid())
347}
348
349/// Sets a password and removes every other way in: passkeys, two-factor sign-in, sessions and device tokens.
350/// A reset often follows a lost device, and its passkey or token must not keep working.
351pub fn reset_password(db: &Connection, user_id: i64, pw_hash: &str) -> rusqlite::Result<()> {
352 db.execute(
353 "UPDATE users SET pw_hash = ?1, two_factor = 0 WHERE id = ?2",
354 params![pw_hash, user_id],
355 )?;
356 db.execute("DELETE FROM passkeys WHERE user_id = ?1", [user_id])?;
357 db.execute("DELETE FROM sessions WHERE user_id = ?1", [user_id])?;
358 // A random hash matches no token. The devices and their history stay, and the owner pairs them again.
359 db.execute(
360 "UPDATE devices SET token_hash = randomblob(32) WHERE user_id = ?1 AND token_hash IS NOT NULL",
361 [user_id],
362 )?;
363 Ok(())
364}
365
366pub fn check_username(name: &str) -> Result<&str, Error> {
367 let name = name.trim();
368 if name.is_empty() || name.chars().count() > 64 {
369 return Err(Error::BadRequest(
370 "username must have 1 to 64 characters".into(),
371 ));
372 }
373 Ok(name)
374}
375
376fn passwd(db: &Connection, username: &str) {
377 let password = std::env::var("OT_PASSWORD").unwrap_or_else(|_| {
378 // ponytail: the password echoes on the terminal. Use rpassword if that matters.
379 eprint!("password for {username}: ");
380 let mut line = String::new();
381 std::io::stdin()
382 .read_line(&mut line)
383 .expect("read password");
384 line.trim_end_matches(['\r', '\n']).to_owned()
385 });
386 if let Err(msg) = auth::check_new_password(&password) {
387 eprintln!("{msg}");
388 std::process::exit(1);
389 }
390 let hash = auth::hash_password(&password);
391 let existing: Option<i64> = db
392 .query_row(
393 "SELECT id FROM users WHERE username = ?1",
394 [username],
395 |r| r.get(0),
396 )
397 .optional()
398 .expect("query user");
399 match existing {
400 Some(id) => {
401 reset_password(db, id, &hash).expect("update user");
402 println!(
403 "password reset for {username}: passkeys removed, device tokens revoked, two-factor sign-in off"
404 );
405 }
406 None => {
407 let username = check_username(username).unwrap_or_else(|e| {
408 eprintln!("{e:?}");
409 std::process::exit(1);
410 });
411 insert_user(db, username, &hash, false).expect("insert user");
412 println!("created user {username}");
413 }
414 }
415}
416
417async fn serve(cli: Cli, db: Connection) {
418 let state = AppState {
419 db: Arc::new(Mutex::new(db)),
420 limiter: Arc::default(),
421 ceremonies: Arc::default(),
422 pairings: Arc::default(),
423 public_url: cli.public_url,
424 max_retention_days: cli.retention_days.max(0),
425 behind_proxy: cli.behind_proxy,
426 };
427 tokio::spawn(cleanup(state.clone()));
428
429 let listener = tokio::net::TcpListener::bind(&cli.addr)
430 .await
431 .expect("bind");
432 println!(
433 "listening on http://{}, serving {}",
434 cli.addr,
435 cli.web_dir.display()
436 );
437 if state
438 .db()
439 .query_row("SELECT NOT EXISTS (SELECT 1 FROM users)", [], |r| {
440 r.get::<_, bool>(0)
441 })
442 .unwrap_or(false)
443 {
444 println!("no users yet: open the web UI to create the admin account");
445 }
446 let app = routes::router(state, &cli.web_dir);
447 axum::serve(
448 listener,
449 app.into_make_service_with_connect_info::<std::net::SocketAddr>(),
450 )
451 .await
452 .expect("serve");
453}
454
455/// The days of points to keep for a user, or None for forever.
456pub fn effective_retention(user_days: Option<i64>, max_days: i64) -> Option<i64> {
457 match (user_days, max_days) {
458 (None, 0) => None,
459 (None, max) => Some(max),
460 (Some(days), 0) => Some(days),
461 (Some(days), max) => Some(days.min(max)),
462 }
463}
464
465/// Deletes points older than the user's retention. Each device keeps its newest point, so it stays on the map.
466pub fn purge_points(
467 db: &Connection,
468 user_id: i64,
469 user_days: Option<i64>,
470 max_days: i64,
471) -> rusqlite::Result<usize> {
472 match effective_retention(user_days, max_days) {
473 None => Ok(0),
474 Some(days) => db.execute(
475 "DELETE FROM points
476 WHERE device_id IN (SELECT id FROM devices WHERE user_id = ?1) AND ts < ?2
477 AND ts < (SELECT MAX(ts) FROM points p WHERE p.device_id = points.device_id)",
478 [user_id, now() - days * 86400],
479 ),
480 }
481}
482
483async fn cleanup(state: AppState) {
484 let mut tick = tokio::time::interval(Duration::from_secs(3600));
485 loop {
486 tick.tick().await;
487 let now = now();
488 let db = state.db();
489 let result = db
490 .execute_batch(&format!(
491 "DELETE FROM sessions WHERE expires_at <= {now};
492 DELETE FROM shares WHERE expires_at <= {now};"
493 ))
494 .and_then(|()| {
495 let users: Vec<(i64, Option<i64>)> = db
496 .prepare("SELECT id, retention_days FROM users")?
497 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))?
498 .collect::<rusqlite::Result<_>>()?;
499 for (id, days) in users {
500 purge_points(&db, id, days, state.max_retention_days)?;
501 }
502 Ok(())
503 });
504 if let Err(e) = result {
505 eprintln!("cleanup failed: {e}");
506 }
507 drop(db);
508 state.limiter.prune();
509 }
510}
511
512#[cfg(test)]
513pub fn test_db() -> Connection {
514 let mut db = Connection::open_in_memory().unwrap();
515 migrate(&mut db).unwrap();
516 db
517}
518
519#[cfg(test)]
520mod tests {
521 use super::*;
522
523 #[test]
524 fn retention_never_exceeds_the_server_limit() {
525 assert_eq!(effective_retention(None, 0), None);
526 assert_eq!(effective_retention(None, 30), Some(30));
527 assert_eq!(effective_retention(Some(7), 0), Some(7));
528 assert_eq!(effective_retention(Some(7), 30), Some(7));
529 assert_eq!(effective_retention(Some(90), 30), Some(30));
530 }
531
532 #[test]
533 fn public_url_is_an_origin() {
534 assert!(parse_public_url("https://track.example.com").is_ok());
535 assert!(parse_public_url("http://localhost:8080/").is_ok());
536 assert!(parse_public_url("https://example.com/track").is_err());
537 assert!(parse_public_url("ftp://example.com").is_err());
538 }
539
540 #[test]
541 fn reset_removes_every_other_way_in() {
542 let db = test_db();
543 let id = insert_user(&db, "a", "old", false).unwrap();
544 db.execute_batch(
545 "UPDATE users SET two_factor = 1;
546 INSERT INTO passkeys (user_id, cred_id, passkey, name, created_at) VALUES (1, x'01', '{}', 'k', 0);
547 INSERT INTO sessions (token_hash, user_id, expires_at) VALUES (x'02', 1, 9999999999);
548 INSERT INTO devices (user_id, name, token_hash, created_at) VALUES (1, 'phone', x'03', 0), (1, 'Web', NULL, 0);",
549 )
550 .unwrap();
551 reset_password(&db, id, "new").unwrap();
552 let count = |sql: &str| -> i64 { db.query_row(sql, [], |r| r.get(0)).unwrap() };
553 assert_eq!(count("SELECT COUNT(*) FROM passkeys"), 0);
554 assert_eq!(count("SELECT COUNT(*) FROM sessions"), 0);
555 assert_eq!(count("SELECT two_factor FROM users"), 0);
556 assert_eq!(
557 count("SELECT COUNT(*) FROM devices WHERE token_hash = x'03'"),
558 0
559 );
560 assert_eq!(count("SELECT COUNT(*) FROM devices"), 2);
561 }
562
563 #[test]
564 fn migrations_keep_old_points() {
565 let mut db = Connection::open_in_memory().unwrap();
566 db.execute_batch(MIGRATIONS[0]).unwrap();
567 db.execute_batch(
568 "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0), (2, 'b', '2', 0);
569 INSERT INTO devices (id, user_id, name, token_hash, created_at, last_seen_at)
570 VALUES (7, 1, 'old', x'01', 0, 5), (8, 1, 'new', x'02', 0, 9);
571 INSERT INTO points (user_id, ts, lat, lon) VALUES (1, 100, 1, 1), (2, 100, 2, 2);",
572 )
573 .unwrap();
574 migrate(&mut db).unwrap();
575 migrate(&mut db).unwrap();
576 let owners: Vec<(i64, String)> = db
577 .prepare("SELECT d.user_id, d.name FROM points p JOIN devices d ON d.id = p.device_id ORDER BY d.user_id")
578 .unwrap()
579 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
580 .unwrap()
581 .collect::<rusqlite::Result<_>>()
582 .unwrap();
583 assert_eq!(owners, [(1, "new".into()), (2, "Web".into())]);
584 }
585
586 #[test]
587 fn retention_keeps_each_devices_newest_point() {
588 let db = test_db();
589 db.execute_batch(
590 "INSERT INTO users (id, username, webauthn_id, created_at) VALUES (1, 'a', '1', 0);
591 INSERT INTO devices (id, user_id, name, token_hash, created_at) VALUES (1, 1, 'p', x'01', 0), (2, 1, 'q', x'02', 0);
592 INSERT INTO points (device_id, ts, lat, lon) VALUES (1, 10, 0, 0), (1, 20, 0, 0), (2, 15, 0, 0);",
593 )
594 .unwrap();
595 purge_points(&db, 1, Some(1), 30).unwrap();
596 let left: Vec<(i64, i64)> = db
597 .prepare("SELECT device_id, ts FROM points ORDER BY device_id")
598 .unwrap()
599 .query_map([], |r| Ok((r.get(0)?, r.get(1)?)))
600 .unwrap()
601 .collect::<rusqlite::Result<_>>()
602 .unwrap();
603 assert_eq!(left, [(1, 20), (2, 15)]);
604 }
605
606 #[test]
607 fn first_user_is_admin() {
608 let db = test_db();
609 let a = insert_user(&db, "a", "", false).unwrap();
610 let b = insert_user(&db, "b", "", false).unwrap();
611 let admin = |id: i64| -> bool {
612 db.query_row("SELECT is_admin FROM users WHERE id = ?1", [id], |r| {
613 r.get(0)
614 })
615 .unwrap()
616 };
617 assert!(admin(a));
618 assert!(!admin(b));
619 assert!(matches!(
620 insert_user(&db, "A", "", false),
621 Err(Error::Conflict(_))
622 ));
623 }
624}
625