use std::{fmt, time::Duration};
use sqlx::SqlitePool;
use time::OffsetDateTime;
use super::{
OnlineUser, OnlineUsers, PresenceError, validate_config, validate_limit, validate_user_id,
};
pub const SQLITE_SCHEMA: &str = include_str!("sqlite_v1.sql");
#[derive(Clone)]
pub struct SqlitePresence {
pool: SqlitePool,
scope: String,
online_timeout: Duration,
}
impl fmt::Debug for SqlitePresence {
fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
formatter
.debug_struct("SqlitePresence")
.field("online_timeout", &self.online_timeout)
.finish_non_exhaustive()
}
}
impl SqlitePresence {
pub fn new(
pool: SqlitePool,
scope: impl Into<String>,
online_timeout: Duration,
) -> Result<Self, PresenceError> {
let scope = scope.into();
validate_config(&scope, online_timeout)?;
Ok(Self {
pool,
scope,
online_timeout,
})
}
pub fn scope(&self) -> &str {
&self.scope
}
pub const fn online_timeout(&self) -> Duration {
self.online_timeout
}
pub async fn heartbeat(&self, user_id: &str) -> Result<OffsetDateTime, PresenceError> {
validate_user_id(user_id)?;
let timestamps: Vec<i64> = sqlx::query_scalar(
"INSERT INTO main.footing_presence (scope, user_id, last_seen)
VALUES (?1, ?2, CAST(round(unixepoch('subsec') * 1000) AS INTEGER))
ON CONFLICT (scope, user_id) DO UPDATE
SET last_seen = max(footing_presence.last_seen, excluded.last_seen)
RETURNING last_seen",
)
.bind(&self.scope)
.bind(user_id)
.fetch_all(&self.pool)
.await?;
decode_timestamp(
timestamps
.into_iter()
.next()
.ok_or(sqlx::Error::RowNotFound)?,
)
}
pub async fn count_online(&self) -> Result<i64, PresenceError> {
Ok(sqlx::query_scalar(
"SELECT count(*) FROM main.footing_presence
WHERE scope = ?1
AND last_seen > CAST(round(unixepoch('subsec') * 1000) AS INTEGER) - ?2",
)
.bind(&self.scope)
.bind(self.timeout_millis())
.fetch_one(&self.pool)
.await?)
}
pub async fn list_online(
&self,
after_user_id: Option<&str>,
limit: u32,
) -> Result<OnlineUsers, PresenceError> {
validate_limit(limit)?;
if let Some(after) = after_user_id {
validate_user_id(after)?;
}
let rows: Vec<(String, i64)> = sqlx::query_as(
"SELECT user_id, last_seen FROM main.footing_presence
WHERE scope = ?1
AND last_seen > CAST(round(unixepoch('subsec') * 1000) AS INTEGER) - ?2
AND user_id > ?3 COLLATE BINARY
ORDER BY user_id LIMIT ?4",
)
.bind(&self.scope)
.bind(self.timeout_millis())
.bind(after_user_id.unwrap_or(""))
.bind(i64::from(limit) + 1)
.fetch_all(&self.pool)
.await?;
let users = rows
.into_iter()
.map(|(user_id, last_seen)| {
decode_timestamp(last_seen).map(|last_seen| OnlineUser { user_id, last_seen })
})
.collect::<Result<Vec<_>, _>>()?;
Ok(OnlineUsers::from_users(users, limit))
}
pub async fn prune_expired(&self, limit: u32) -> Result<u64, PresenceError> {
validate_limit(limit)?;
Ok(sqlx::query(
"DELETE FROM main.footing_presence
WHERE scope = ?1 AND user_id IN (
SELECT user_id FROM main.footing_presence
WHERE scope = ?1
AND last_seen <= CAST(round(unixepoch('subsec') * 1000) AS INTEGER) - ?2
ORDER BY last_seen, user_id LIMIT ?3
)",
)
.bind(&self.scope)
.bind(self.timeout_millis())
.bind(i64::from(limit))
.execute(&self.pool)
.await?
.rows_affected())
}
fn timeout_millis(&self) -> i64 {
self.online_timeout.as_millis() as i64
}
}
fn decode_timestamp(milliseconds: i64) -> Result<OffsetDateTime, PresenceError> {
OffsetDateTime::from_unix_timestamp_nanos(i128::from(milliseconds) * 1_000_000).map_err(
|error| {
sqlx::Error::ColumnDecode {
index: "last_seen".into(),
source: Box::new(error),
}
.into()
},
)
}