OXYGENHUB
# ea14c4ec5c3f oxygenhub / crates / hub / src / db / sessions.rs
4.6 KiB · Rust · last changed Postgres, step 1: every query numbers its placeholders $1, $2, ... 20 days ago source blame history raw
1 
//! Browser sessions as server-side records (oxygenhub#223).
2
3 
use super::{now_ms, Pool};
4 
use sqlx::FromRow;
5
6 
/// A session ends after this long unused.
7 
pub const IDLE_MS: i64 = 14 * 24 * 60 * 60_000;
8 
/// And after this long whatever happens.
9 
pub const ABSOLUTE_MS: i64 = 30 * 24 * 60 * 60_000;
10 
/// `seen_ms` is written at most this often per session.
11 
const TOUCH_EVERY_MS: i64 = 60_000;
12
13 
#[derive(Clone, Debug, FromRow)]
14 
pub struct Session {
15 
    pub id: i64,
16 
    pub account_id: i64,
17 
    pub salt: String,
18 
    pub created_ms: i64,
19 
    pub seen_ms: i64,
20 
    pub address: String,
21 
    pub agent: String,
22 
}
23
24 
impl Session {
25 
    /// Whether it is still good at `now`, for an account whose salt is `salt`.
26 
    pub fn live(&self, salt: &str, now: i64) -> bool {
27 
        self.salt == salt && now - self.seen_ms < IDLE_MS && now - self.created_ms < ABSOLUTE_MS
28 
    }
29 
}
30
31 
const COLUMNS: &str = "id, account_id, salt, created_ms, seen_ms, address, agent";
32
33 
pub async fn create(pool: &Pool, account_id: i64, hash: &str, salt: &str) -> sqlx::Result<i64> {
34 
    let now = now_ms();
35 
    let r = sqlx::query("INSERT INTO sessions (account_id, hash, salt, created_ms, seen_ms) VALUES ($1, $2, $3, $4, $5)")
36 
        .bind(account_id)
37 
        .bind(hash)
38 
        .bind(salt)
39 
        .bind(now)
40 
        .bind(now)
41 
        .execute(pool)
42 
        .await?;
43 
    Ok(r.last_insert_rowid())
44 
}
45
46 
pub async fn by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Session>> {
47 
    sqlx::query_as(&format!("SELECT {COLUMNS} FROM sessions WHERE hash = $1")).bind(hash).fetch_optional(pool).await
48 
}
49
50 
/// Note a use: the time (at most once a minute) and, the first time, the
51 
/// address and browser it came from.
52 
pub async fn touch(pool: &Pool, s: &Session, address: &str, agent: &str, now: i64) -> sqlx::Result<()> {
53 
    if s.agent.is_empty() && s.address.is_empty() && (!agent.is_empty() || !address.is_empty()) {
54 
        sqlx::query("UPDATE sessions SET seen_ms = $1, address = $2, agent = $3 WHERE id = $4")
55 
            .bind(now)
56 
            .bind(address)
57 
            .bind(agent)
58 
            .bind(s.id)
59 
            .execute(pool)
60 
            .await?;
61 
    } else if now - s.seen_ms >= TOUCH_EVERY_MS {
62 
        sqlx::query("UPDATE sessions SET seen_ms = $1 WHERE id = $2").bind(now).bind(s.id).execute(pool).await?;
63 
    }
64 
    Ok(())
65 
}
66
67 
/// An account's live sessions, most recently used first.
68 
pub async fn of_account(pool: &Pool, account_id: i64, salt: &str) -> sqlx::Result<Vec<Session>> {
69 
    let now = now_ms();
70 
    let all: Vec<Session> = sqlx::query_as(&format!("SELECT {COLUMNS} FROM sessions WHERE account_id = $1 ORDER BY seen_ms DESC"))
71 
        .bind(account_id)
72 
        .fetch_all(pool)
73 
        .await?;
74 
    Ok(all.into_iter().filter(|s| s.live(salt, now)).collect())
75 
}
76
77 
/// End one session of an account's; whether there was one.
78 
pub async fn end(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<bool> {
79 
    let r = sqlx::query("DELETE FROM sessions WHERE id = $1 AND account_id = $2").bind(id).bind(account_id).execute(pool).await?;
80 
    Ok(r.rows_affected() > 0)
81 
}
82
83 
pub async fn end_hash(pool: &Pool, hash: &str) -> sqlx::Result<()> {
84 
    sqlx::query("DELETE FROM sessions WHERE hash = $1").bind(hash).execute(pool).await?;
85 
    Ok(())
86 
}
87
88 
/// Every session of an account's but `keep`.
89 
pub async fn end_others(pool: &Pool, account_id: i64, keep: i64) -> sqlx::Result<()> {
90 
    sqlx::query("DELETE FROM sessions WHERE account_id = $1 AND id != $2").bind(account_id).bind(keep).execute(pool).await?;
91 
    Ok(())
92 
}
93
94 
/// Forget sessions that can no longer be used; how many.
95 
pub async fn sweep(pool: &Pool) -> sqlx::Result<u64> {
96 
    let now = now_ms();
97 
    let r = sqlx::query(
98 
        "DELETE FROM sessions WHERE seen_ms < $1 OR created_ms < $2 \
99 
         OR salt != (SELECT session_salt FROM accounts WHERE accounts.id = sessions.account_id)",
100 
    )
101 
    .bind(now - IDLE_MS)
102 
    .bind(now - ABSOLUTE_MS)
103 
    .execute(pool)
104 
    .await?;
105 
    Ok(r.rows_affected())
106 
}
107
108 
#[cfg(test)]
109 
mod tests {
110 
    use super::*;
111
112 
    #[test]
113 
    fn a_session_ends_on_a_new_salt_when_idle_or_when_old() {
114 
        let t0 = 1_000_000_000_000;
115 
        let s =
116 
            Session { id: 1, account_id: 1, salt: "a".into(), created_ms: t0, seen_ms: t0, address: String::new(), agent: String::new() };
117 
        assert!(s.live("a", t0 + 1000));
118 
        assert!(!s.live("b", t0 + 1000), "a new salt: signed out everywhere");
119 
        assert!(!s.live("a", t0 + IDLE_MS), "unused for 14 days");
120 
        let busy = Session { seen_ms: t0 + ABSOLUTE_MS - 1000, ..s.clone() };
121 
        assert!(busy.live("a", t0 + ABSOLUTE_MS - 1));
122 
        assert!(!busy.live("a", t0 + ABSOLUTE_MS), "30 days, however busy");
123 
    }
124 
}