//! [`SqlAccountStore`]: accounts in PostgreSQL or Oracle through [`crate::sql::Db`], and
//! [`create_tables`] for their schema.
//!
//! The tables follow ASP.NET Core Identity's schema (version 3, with passkeys) under neutral
//! names with a configurable prefix (`zeyn_` unless set): `users`, `roles`, `user_roles`,
//! `user_claims`, `role_claims`, `user_logins`, `user_tokens` and `user_passkeys`. Names are 256
//! characters at most, as in `IdentityUserContext`; normalized user and role names are unique;
//! the normalized e-mail is indexed. SQL text is fixed in this file and identifiers come only from
//! the checked prefix; every value is a bound parameter.
use super::model::{Claim, Passkey, Role, User, UserLogin};
use super::store::AccountStore;
use crate::auth::b64url;
use crate::ctx::BoxFuture;
use crate::sql::{Arg, Cast, Cell, Col, Db, Sql};
use crate::{Error, Result};
use std::sync::Arc;
/// Accounts in a SQL database. Create the tables with [`create_tables`] (or [`Self::create_tables`]
/// in development) before use.
///
/// ```no_run
/// # async fn run(db: zeyn::sql::Db) -> zeyn::Result<()> {
/// use zeyn::accounts::SqlAccountStore;
/// let store = SqlAccountStore::new(db).prefix("shop_")?;
/// store.create_tables().await?;
/// # Ok(()) }
/// ```
#[derive(Clone)]
pub struct SqlAccountStore {
db: Db,
prefix: Arc<str>,
}
/// The `CREATE TABLE` and `CREATE INDEX` statements of the account tables for a dialect
/// (`postgres` or `oracle`) and table prefix. Put them in a migration.
///
/// ```
/// let sql = zeyn::accounts::create_tables("postgres", "zeyn_").unwrap();
/// assert!(sql[0].starts_with("CREATE TABLE zeyn_users ("));
/// assert!(sql.iter().any(|s| s.contains("UNIQUE (normalized_user_name)")));
/// let ora = zeyn::accounts::create_tables("oracle", "zeyn_").unwrap();
/// assert!(ora[0].contains("NUMBER(1)"));
/// ```
pub fn create_tables(dialect: &str, prefix: &str) -> Result<Vec<String>> {
let p = checked_prefix(prefix)?;
let oracle = match dialect {
"postgres" => false,
"oracle" => true,
other => return Err(Error::Internal(format!("no account tables for the `{other}` dialect"))),
};
let s = |n: u32| if oracle { format!("VARCHAR2({n})") } else { format!("VARCHAR({n})") };
let big = if oracle { "NUMBER(19)" } else { "BIGINT" };
let flag = if oracle { "NUMBER(1)" } else { "BOOLEAN" };
let text = if oracle { "CLOB" } else { "TEXT" };
let auto =
if oracle { "NUMBER(19) GENERATED BY DEFAULT AS IDENTITY" } else { "BIGINT GENERATED BY DEFAULT AS IDENTITY" };
let (name, key) = (s(256), s(64));
Ok(vec![
format!(
"CREATE TABLE {p}users (id {key} PRIMARY KEY, user_name {name} NOT NULL, normalized_user_name {name} NOT NULL, email {name}, normalized_email {name}, email_confirmed {flag} NOT NULL, password_hash {}, security_stamp {key} NOT NULL, concurrency_stamp {key} NOT NULL, phone_number {name}, phone_number_confirmed {flag} NOT NULL, two_factor_enabled {flag} NOT NULL, lockout_end {big}, lockout_enabled {flag} NOT NULL, access_failed_count {big} NOT NULL, extra {text}, CONSTRAINT {p}users_name UNIQUE (normalized_user_name))",
s(512)
),
format!("CREATE INDEX {p}users_email ON {p}users (normalized_email)"),
format!(
"CREATE TABLE {p}roles (id {key} PRIMARY KEY, name {name} NOT NULL, normalized_name {name} NOT NULL, concurrency_stamp {key} NOT NULL, CONSTRAINT {p}roles_name UNIQUE (normalized_name))"
),
format!(
"CREATE TABLE {p}user_roles (user_id {key} NOT NULL REFERENCES {p}users (id) ON DELETE CASCADE, role_id {key} NOT NULL REFERENCES {p}roles (id) ON DELETE CASCADE, PRIMARY KEY (user_id, role_id))"
),
format!("CREATE INDEX {p}user_roles_role ON {p}user_roles (role_id)"),
format!(
"CREATE TABLE {p}user_claims (id {auto} PRIMARY KEY, user_id {key} NOT NULL REFERENCES {p}users (id) ON DELETE CASCADE, claim_type {name} NOT NULL, claim_value {})",
s(1024)
),
format!("CREATE INDEX {p}user_claims_user ON {p}user_claims (user_id)"),
format!(
"CREATE TABLE {p}role_claims (id {auto} PRIMARY KEY, role_id {key} NOT NULL REFERENCES {p}roles (id) ON DELETE CASCADE, claim_type {name} NOT NULL, claim_value {})",
s(1024)
),
format!("CREATE INDEX {p}role_claims_role ON {p}role_claims (role_id)"),
format!(
"CREATE TABLE {p}user_logins (login_provider {} NOT NULL, provider_key {name} NOT NULL, display_name {name}, user_id {key} NOT NULL REFERENCES {p}users (id) ON DELETE CASCADE, PRIMARY KEY (login_provider, provider_key))",
s(128)
),
format!("CREATE INDEX {p}user_logins_user ON {p}user_logins (user_id)"),
format!(
"CREATE TABLE {p}user_tokens (user_id {key} NOT NULL REFERENCES {p}users (id) ON DELETE CASCADE, login_provider {} NOT NULL, name {} NOT NULL, value {text}, PRIMARY KEY (user_id, login_provider, name))",
s(128),
s(128)
),
format!(
"CREATE TABLE {p}user_passkeys (credential_id {} PRIMARY KEY, user_id {key} NOT NULL REFERENCES {p}users (id) ON DELETE CASCADE, data {text} NOT NULL)",
s(1400)
),
format!("CREATE INDEX {p}user_passkeys_user ON {p}user_passkeys (user_id)"),
])
}
fn checked_prefix(prefix: &str) -> Result<String> {
let ok = prefix.len() <= 16
&& prefix.chars().all(|c| c.is_ascii_lowercase() || c.is_ascii_digit() || c == '_')
&& !prefix.starts_with(|c: char| c.is_ascii_digit());
if ok {
Ok(prefix.to_string())
} else {
Err(Error::Internal("the account table prefix may hold lower case letters, digits and `_`, at most 16".into()))
}
}
const USER_COLS: &str = "id, user_name, normalized_user_name, email, normalized_email, email_confirmed, password_hash, security_stamp, concurrency_stamp, phone_number, phone_number_confirmed, two_factor_enabled, lockout_end, lockout_enabled, access_failed_count, extra";
const USER_TYPES: [Col; 16] = [
Col::Text,
Col::Text,
Col::Text,
Col::Text,
Col::Text,
Col::Bool,
Col::Text,
Col::Text,
Col::Text,
Col::Text,
Col::Bool,
Col::Bool,
Col::Int,
Col::Bool,
Col::Int,
Col::Text,
];
fn text(c: &mut std::vec::IntoIter<Cell>) -> Option<String> {
match c.next() {
Some(Cell::Text(s)) => Some(s),
_ => None,
}
}
fn flag(c: &mut std::vec::IntoIter<Cell>) -> bool {
matches!(c.next(), Some(Cell::Bool(true)))
}
fn int(c: &mut std::vec::IntoIter<Cell>) -> Option<i64> {
match c.next() {
Some(Cell::Int(n)) => Some(n),
_ => None,
}
}
fn user_of(row: Vec<Cell>) -> User {
let mut c = row.into_iter();
User {
id: text(&mut c).unwrap_or_default(),
user_name: text(&mut c).unwrap_or_default(),
normalized_user_name: text(&mut c).unwrap_or_default(),
email: text(&mut c),
normalized_email: text(&mut c),
email_confirmed: flag(&mut c),
password_hash: text(&mut c),
security_stamp: text(&mut c).unwrap_or_default(),
concurrency_stamp: text(&mut c).unwrap_or_default(),
phone_number: text(&mut c),
phone_number_confirmed: flag(&mut c),
two_factor_enabled: flag(&mut c),
lockout_end: int(&mut c),
lockout_enabled: flag(&mut c),
access_failed_count: int(&mut c).unwrap_or(0) as i32,
extra: text(&mut c).and_then(|s| serde_json::from_str(&s).ok()).unwrap_or_default(),
}
}
fn opt(s: &Option<String>) -> Arg {
match s {
Some(v) => Arg::Text(v.clone()),
None => Arg::Null(Col::Text),
}
}
fn user_args(u: &User) -> Vec<Arg> {
vec![
Arg::Text(u.id.clone()),
Arg::Text(u.user_name.clone()),
Arg::Text(u.normalized_user_name.clone()),
opt(&u.email),
opt(&u.normalized_email),
Arg::Bool(u.email_confirmed),
opt(&u.password_hash),
Arg::Text(u.security_stamp.clone()),
Arg::Text(u.concurrency_stamp.clone()),
opt(&u.phone_number),
Arg::Bool(u.phone_number_confirmed),
Arg::Bool(u.two_factor_enabled),
u.lockout_end.map_or(Arg::Null(Col::Int), Arg::Int),
Arg::Bool(u.lockout_enabled),
Arg::Int(i64::from(u.access_failed_count)),
Arg::Text(serde_json::to_string(&u.extra).unwrap_or_else(|_| "{}".into())),
]
}
/// A unique violation on insert is a conflict.
fn conflict(e: Error) -> Error {
match e {
Error::Validation(_) => Error::Conflict,
e => e,
}
}
impl SqlAccountStore {
/// The store over `db`, with tables named `zeyn_users` and so on.
pub fn new(db: Db) -> Self {
SqlAccountStore { db, prefix: Arc::from("zeyn_") }
}
/// Another table prefix: lower case letters, digits and `_`, at most 16 characters.
pub fn prefix(mut self, prefix: &str) -> Result<Self> {
self.prefix = Arc::from(checked_prefix(prefix)?);
Ok(self)
}
/// Creates the tables. For development and tests; production schemas belong in migrations
/// made from [`create_tables`].
pub async fn create_tables(&self) -> Result<()> {
for statement in create_tables(self.db.dialect().name(), &self.prefix)? {
self.db.run(Sql { text: statement, args: Vec::new(), cols: Vec::new() }).await?;
}
Ok(())
}
fn oracle(&self) -> bool {
self.db.dialect().name() == "oracle"
}
/// SQL with `{p}` replaced by the prefix and each `?` by the dialect's placeholder.
fn sql(&self, template: &str, args: Vec<Arg>, cols: &[Col]) -> Sql {
let template = template.replace("{p}", &self.prefix);
let mut text = String::with_capacity(template.len() + 16);
let mut n = 0;
for ch in template.chars() {
if ch == '?' {
n += 1;
self.db.dialect().param(&mut text, n, Cast::Plain);
} else {
text.push(ch);
}
}
Sql { text, args, cols: cols.to_vec() }
}
async fn users_where(&self, condition: &str, args: Vec<Arg>) -> Result<Vec<User>> {
let sql = self.sql(&format!("SELECT {USER_COLS} FROM {{p}}users WHERE {condition}"), args, &USER_TYPES);
Ok(self.db.rows(sql).await?.into_iter().map(user_of).collect())
}
async fn one_user(&self, condition: &str, args: Vec<Arg>) -> Result<Option<User>> {
Ok(self.users_where(condition, args).await?.into_iter().next())
}
async fn run(&self, template: &str, args: Vec<Arg>) -> Result<u64> {
self.db.run(self.sql(template, args, &[])).await
}
/// A page clause around `inner` (which has its ORDER BY), for both dialects.
fn paged(&self, inner: &str, offset: u64, limit: u32) -> (String, Vec<Arg>) {
if self.oracle() {
(
format!(
"SELECT * FROM (SELECT a.*, ROWNUM zeyn_rn FROM ({inner}) a WHERE ROWNUM <= ?) WHERE zeyn_rn > ?"
),
vec![Arg::Int(offset as i64 + i64::from(limit)), Arg::Int(offset as i64)],
)
} else {
(format!("{inner} LIMIT ? OFFSET ?"), vec![Arg::Int(i64::from(limit)), Arg::Int(offset as i64)])
}
}
async fn count(&self, template: &str, args: Vec<Arg>) -> Result<u64> {
let rows = self.db.rows(self.sql(template, args, &[Col::Int])).await?;
Ok(match rows.into_iter().next().and_then(|r| r.into_iter().next()) {
Some(Cell::Int(n)) => n.max(0) as u64,
_ => 0,
})
}
async fn claims(&self, table: &str, owner: &str, id: &str) -> Result<Vec<Claim>> {
let sql = self.sql(
&format!("SELECT claim_type, claim_value FROM {{p}}{table} WHERE {owner} = ? ORDER BY id"),
vec![Arg::Text(id.into())],
&[Col::Text, Col::Text],
);
Ok(self
.db
.rows(sql)
.await?
.into_iter()
.map(|r| {
let mut c = r.into_iter();
Claim { kind: text(&mut c).unwrap_or_default(), value: text(&mut c).unwrap_or_default() }
})
.collect())
}
fn roles_of_rows(rows: Vec<Vec<Cell>>) -> Vec<Role> {
rows.into_iter()
.map(|r| {
let mut c = r.into_iter();
Role {
id: text(&mut c).unwrap_or_default(),
name: text(&mut c).unwrap_or_default(),
normalized_name: text(&mut c).unwrap_or_default(),
concurrency_stamp: text(&mut c).unwrap_or_default(),
}
})
.collect()
}
}
/// Escapes `%`, `_` and `\` for a `LIKE ... ESCAPE '\'` pattern.
fn like(s: &str) -> String {
let mut out = String::from("%");
for c in s.chars() {
if matches!(c, '%' | '_' | '\\') {
out.push('\\');
}
out.push(c);
}
out.push('%');
out
}
const ROLE_COLS: [Col; 4] = [Col::Text, Col::Text, Col::Text, Col::Text];
impl AccountStore for SqlAccountStore {
fn create_user(&self, user: &User) -> BoxFuture<Result<()>> {
let (s, u) = (self.clone(), user.clone());
Box::pin(async move {
s.run(
&format!(
"INSERT INTO {{p}}users ({USER_COLS}) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
),
user_args(&u),
)
.await
.map_err(conflict)?;
Ok(())
})
}
fn update_user(&self, user: &User, expected: &str) -> BoxFuture<Result<()>> {
let (s, u, expected) = (self.clone(), user.clone(), expected.to_string());
Box::pin(async move {
let mut args = user_args(&u);
let id = args.remove(0);
args.push(id);
args.push(Arg::Text(expected));
let changed = s
.run(
"UPDATE {p}users SET user_name = ?, normalized_user_name = ?, email = ?, normalized_email = ?, email_confirmed = ?, password_hash = ?, security_stamp = ?, concurrency_stamp = ?, phone_number = ?, phone_number_confirmed = ?, two_factor_enabled = ?, lockout_end = ?, lockout_enabled = ?, access_failed_count = ?, extra = ? WHERE id = ? AND concurrency_stamp = ?",
args,
)
.await
.map_err(conflict)?;
if changed == 0 { Err(Error::Conflict) } else { Ok(()) }
})
}
fn delete_user(&self, id: &str) -> BoxFuture<Result<()>> {
let (s, id) = (self.clone(), id.to_string());
Box::pin(async move {
for t in ["user_roles", "user_claims", "user_logins", "user_tokens", "user_passkeys"] {
s.run(&format!("DELETE FROM {{p}}{t} WHERE user_id = ?"), vec![Arg::Text(id.clone())]).await?;
}
if s.run("DELETE FROM {p}users WHERE id = ?", vec![Arg::Text(id)]).await? == 0 {
Err(Error::NotFound)
} else {
Ok(())
}
})
}
fn user_by_id(&self, id: &str) -> BoxFuture<Result<Option<User>>> {
let (s, id) = (self.clone(), id.to_string());
Box::pin(async move { s.one_user("id = ?", vec![Arg::Text(id)]).await })
}
fn user_by_name(&self, normalized: &str) -> BoxFuture<Result<Option<User>>> {
let (s, n) = (self.clone(), normalized.to_string());
Box::pin(async move { s.one_user("normalized_user_name = ?", vec![Arg::Text(n)]).await })
}
fn user_by_email(&self, normalized: &str) -> BoxFuture<Result<Option<User>>> {
let (s, n) = (self.clone(), normalized.to_string());
Box::pin(async move {
Ok(s.users_where("normalized_email = ? ORDER BY id", vec![Arg::Text(n)]).await?.into_iter().next())
})
}
fn users(&self, search: Option<&str>, offset: u64, limit: u32) -> BoxFuture<Result<(Vec<User>, u64)>> {
let (s, search) = (self.clone(), search.map(like));
Box::pin(async move {
let (filter, mut args) = match &search {
Some(q) => (
"WHERE normalized_user_name LIKE ? ESCAPE '\\' OR normalized_email LIKE ? ESCAPE '\\'",
vec![Arg::Text(q.clone()), Arg::Text(q.clone())],
),
None => ("", Vec::new()),
};
let total = s.count(&format!("SELECT COUNT(*) FROM {{p}}users {filter}"), args.clone()).await?;
let (paged, extra) = s.paged(
&format!("SELECT {USER_COLS} FROM {{p}}users {filter} ORDER BY normalized_user_name"),
offset,
limit,
);
args.extend(extra);
let rows = s.db.rows(s.sql(&paged, args, &USER_TYPES)).await?;
Ok((rows.into_iter().map(user_of).collect(), total))
})
}
fn create_role(&self, role: &Role) -> BoxFuture<Result<()>> {
let (s, r) = (self.clone(), role.clone());
Box::pin(async move {
s.run(
"INSERT INTO {p}roles (id, name, normalized_name, concurrency_stamp) VALUES (?, ?, ?, ?)",
vec![Arg::Text(r.id), Arg::Text(r.name), Arg::Text(r.normalized_name), Arg::Text(r.concurrency_stamp)],
)
.await
.map_err(conflict)?;
Ok(())
})
}
fn update_role(&self, role: &Role, expected: &str) -> BoxFuture<Result<()>> {
let (s, r, expected) = (self.clone(), role.clone(), expected.to_string());
Box::pin(async move {
let n = s
.run(
"UPDATE {p}roles SET name = ?, normalized_name = ?, concurrency_stamp = ? WHERE id = ? AND concurrency_stamp = ?",
vec![Arg::Text(r.name), Arg::Text(r.normalized_name), Arg::Text(r.concurrency_stamp), Arg::Text(r.id), Arg::Text(expected)],
)
.await
.map_err(conflict)?;
if n == 0 { Err(Error::Conflict) } else { Ok(()) }
})
}
fn delete_role(&self, id: &str) -> BoxFuture<Result<()>> {
let (s, id) = (self.clone(), id.to_string());
Box::pin(async move {
s.run("DELETE FROM {p}user_roles WHERE role_id = ?", vec![Arg::Text(id.clone())]).await?;
s.run("DELETE FROM {p}role_claims WHERE role_id = ?", vec![Arg::Text(id.clone())]).await?;
if s.run("DELETE FROM {p}roles WHERE id = ?", vec![Arg::Text(id)]).await? == 0 {
Err(Error::NotFound)
} else {
Ok(())
}
})
}
fn role_by_id(&self, id: &str) -> BoxFuture<Result<Option<Role>>> {
let (s, id) = (self.clone(), id.to_string());
Box::pin(async move {
let rows =
s.db.rows(s.sql(
"SELECT id, name, normalized_name, concurrency_stamp FROM {p}roles WHERE id = ?",
vec![Arg::Text(id)],
&ROLE_COLS,
))
.await?;
Ok(Self::roles_of_rows(rows).into_iter().next())
})
}
fn role_by_name(&self, normalized: &str) -> BoxFuture<Result<Option<Role>>> {
let (s, n) = (self.clone(), normalized.to_string());
Box::pin(async move {
let rows =
s.db.rows(s.sql(
"SELECT id, name, normalized_name, concurrency_stamp FROM {p}roles WHERE normalized_name = ?",
vec![Arg::Text(n)],
&ROLE_COLS,
))
.await?;
Ok(Self::roles_of_rows(rows).into_iter().next())
})
}
fn roles(&self, offset: u64, limit: u32) -> BoxFuture<Result<(Vec<Role>, u64)>> {
let s = self.clone();
Box::pin(async move {
let total = s.count("SELECT COUNT(*) FROM {p}roles", Vec::new()).await?;
let (paged, args) = s.paged(
"SELECT id, name, normalized_name, concurrency_stamp FROM {p}roles ORDER BY normalized_name",
offset,
limit,
);
Ok((Self::roles_of_rows(s.db.rows(s.sql(&paged, args, &ROLE_COLS)).await?), total))
})
}
fn add_to_role(&self, user_id: &str, role_id: &str) -> BoxFuture<Result<()>> {
let (s, u, r) = (self.clone(), user_id.to_string(), role_id.to_string());
Box::pin(async move {
let exists = s
.count(
"SELECT COUNT(*) FROM {p}user_roles WHERE user_id = ? AND role_id = ?",
vec![Arg::Text(u.clone()), Arg::Text(r.clone())],
)
.await?;
if exists == 0 {
s.run("INSERT INTO {p}user_roles (user_id, role_id) VALUES (?, ?)", vec![Arg::Text(u), Arg::Text(r)])
.await?;
}
Ok(())
})
}
fn remove_from_role(&self, user_id: &str, role_id: &str) -> BoxFuture<Result<()>> {
let (s, u, r) = (self.clone(), user_id.to_string(), role_id.to_string());
Box::pin(async move {
s.run("DELETE FROM {p}user_roles WHERE user_id = ? AND role_id = ?", vec![Arg::Text(u), Arg::Text(r)])
.await?;
Ok(())
})
}
fn roles_of(&self, user_id: &str) -> BoxFuture<Result<Vec<Role>>> {
let (s, u) = (self.clone(), user_id.to_string());
Box::pin(async move {
let sql = s.sql(
"SELECT r.id, r.name, r.normalized_name, r.concurrency_stamp FROM {p}roles r JOIN {p}user_roles ur ON ur.role_id = r.id WHERE ur.user_id = ? ORDER BY r.normalized_name",
vec![Arg::Text(u)],
&ROLE_COLS,
);
Ok(Self::roles_of_rows(s.db.rows(sql).await?))
})
}
fn users_in_role(&self, role_id: &str, limit: u32) -> BoxFuture<Result<Vec<User>>> {
let (s, r) = (self.clone(), role_id.to_string());
Box::pin(async move {
let cols = USER_COLS.split(", ").map(|c| format!("u.{c}")).collect::<Vec<_>>().join(", ");
let (paged, mut extra) = s.paged(&format!("SELECT {cols} FROM {{p}}users u JOIN {{p}}user_roles ur ON ur.user_id = u.id WHERE ur.role_id = ? ORDER BY u.normalized_user_name"), 0, limit);
let mut args = vec![Arg::Text(r)];
args.append(&mut extra);
Ok(s.db.rows(s.sql(&paged, args, &USER_TYPES)).await?.into_iter().map(user_of).collect())
})
}
fn claims_of(&self, user_id: &str) -> BoxFuture<Result<Vec<Claim>>> {
let (s, u) = (self.clone(), user_id.to_string());
Box::pin(async move { s.claims("user_claims", "user_id", &u).await })
}
fn add_claim(&self, user_id: &str, claim: &Claim) -> BoxFuture<Result<()>> {
let (s, u, c) = (self.clone(), user_id.to_string(), claim.clone());
Box::pin(async move {
s.run(
"INSERT INTO {p}user_claims (user_id, claim_type, claim_value) VALUES (?, ?, ?)",
vec![Arg::Text(u), Arg::Text(c.kind), Arg::Text(c.value)],
)
.await?;
Ok(())
})
}
fn remove_claim(&self, user_id: &str, claim: &Claim) -> BoxFuture<Result<()>> {
let (s, u, c) = (self.clone(), user_id.to_string(), claim.clone());
Box::pin(async move {
s.run(
"DELETE FROM {p}user_claims WHERE user_id = ? AND claim_type = ? AND claim_value = ?",
vec![Arg::Text(u), Arg::Text(c.kind), Arg::Text(c.value)],
)
.await?;
Ok(())
})
}
fn role_claims(&self, role_id: &str) -> BoxFuture<Result<Vec<Claim>>> {
let (s, r) = (self.clone(), role_id.to_string());
Box::pin(async move { s.claims("role_claims", "role_id", &r).await })
}
fn add_role_claim(&self, role_id: &str, claim: &Claim) -> BoxFuture<Result<()>> {
let (s, r, c) = (self.clone(), role_id.to_string(), claim.clone());
Box::pin(async move {
s.run(
"INSERT INTO {p}role_claims (role_id, claim_type, claim_value) VALUES (?, ?, ?)",
vec![Arg::Text(r), Arg::Text(c.kind), Arg::Text(c.value)],
)
.await?;
Ok(())
})
}
fn remove_role_claim(&self, role_id: &str, claim: &Claim) -> BoxFuture<Result<()>> {
let (s, r, c) = (self.clone(), role_id.to_string(), claim.clone());
Box::pin(async move {
s.run(
"DELETE FROM {p}role_claims WHERE role_id = ? AND claim_type = ? AND claim_value = ?",
vec![Arg::Text(r), Arg::Text(c.kind), Arg::Text(c.value)],
)
.await?;
Ok(())
})
}
fn add_login(&self, user_id: &str, login: &UserLogin) -> BoxFuture<Result<()>> {
let (s, u, l) = (self.clone(), user_id.to_string(), login.clone());
Box::pin(async move {
s.run(
"INSERT INTO {p}user_logins (login_provider, provider_key, display_name, user_id) VALUES (?, ?, ?, ?)",
vec![Arg::Text(l.provider), Arg::Text(l.provider_key), opt(&l.display_name), Arg::Text(u)],
)
.await
.map_err(conflict)?;
Ok(())
})
}
fn remove_login(&self, user_id: &str, provider: &str, key: &str) -> BoxFuture<Result<()>> {
let (s, u, p, k) = (self.clone(), user_id.to_string(), provider.to_string(), key.to_string());
Box::pin(async move {
s.run(
"DELETE FROM {p}user_logins WHERE user_id = ? AND login_provider = ? AND provider_key = ?",
vec![Arg::Text(u), Arg::Text(p), Arg::Text(k)],
)
.await?;
Ok(())
})
}
fn logins_of(&self, user_id: &str) -> BoxFuture<Result<Vec<UserLogin>>> {
let (s, u) = (self.clone(), user_id.to_string());
Box::pin(async move {
let sql = s.sql("SELECT login_provider, provider_key, display_name FROM {p}user_logins WHERE user_id = ? ORDER BY login_provider", vec![Arg::Text(u)], &[Col::Text, Col::Text, Col::Text]);
Ok(s.db
.rows(sql)
.await?
.into_iter()
.map(|r| {
let mut c = r.into_iter();
UserLogin {
provider: text(&mut c).unwrap_or_default(),
provider_key: text(&mut c).unwrap_or_default(),
display_name: text(&mut c),
}
})
.collect())
})
}
fn user_by_login(&self, provider: &str, key: &str) -> BoxFuture<Result<Option<User>>> {
let (s, p, k) = (self.clone(), provider.to_string(), key.to_string());
Box::pin(async move {
s.one_user(
"id IN (SELECT user_id FROM {p}user_logins WHERE login_provider = ? AND provider_key = ?)",
vec![Arg::Text(p), Arg::Text(k)],
)
.await
})
}
fn token(&self, user_id: &str, provider: &str, name: &str) -> BoxFuture<Result<Option<String>>> {
let (s, u, p, n) = (self.clone(), user_id.to_string(), provider.to_string(), name.to_string());
Box::pin(async move {
let sql = s.sql(
"SELECT value FROM {p}user_tokens WHERE user_id = ? AND login_provider = ? AND name = ?",
vec![Arg::Text(u), Arg::Text(p), Arg::Text(n)],
&[Col::Text],
);
let rows = s.db.rows(sql).await?;
let Some(row) = rows.into_iter().next() else { return Ok(None) };
Ok(Some(text(&mut row.into_iter()).unwrap_or_default()))
})
}
fn set_token(&self, user_id: &str, provider: &str, name: &str, value: &str) -> BoxFuture<Result<()>> {
let (s, u, p, n, v) =
(self.clone(), user_id.to_string(), provider.to_string(), name.to_string(), value.to_string());
Box::pin(async move {
let changed = s
.run(
"UPDATE {p}user_tokens SET value = ? WHERE user_id = ? AND login_provider = ? AND name = ?",
vec![Arg::Text(v.clone()), Arg::Text(u.clone()), Arg::Text(p.clone()), Arg::Text(n.clone())],
)
.await?;
if changed == 0 {
s.run(
"INSERT INTO {p}user_tokens (user_id, login_provider, name, value) VALUES (?, ?, ?, ?)",
vec![Arg::Text(u), Arg::Text(p), Arg::Text(n), Arg::Text(v)],
)
.await?;
}
Ok(())
})
}
fn remove_token(&self, user_id: &str, provider: &str, name: &str) -> BoxFuture<Result<()>> {
let (s, u, p, n) = (self.clone(), user_id.to_string(), provider.to_string(), name.to_string());
Box::pin(async move {
s.run(
"DELETE FROM {p}user_tokens WHERE user_id = ? AND login_provider = ? AND name = ?",
vec![Arg::Text(u), Arg::Text(p), Arg::Text(n)],
)
.await?;
Ok(())
})
}
fn passkeys_of(&self, user_id: &str) -> BoxFuture<Result<Vec<Passkey>>> {
let (s, u) = (self.clone(), user_id.to_string());
Box::pin(async move {
let rows = s
.db
.rows(s.sql("SELECT data FROM {p}user_passkeys WHERE user_id = ?", vec![Arg::Text(u)], &[Col::Text]))
.await?;
Ok(rows
.into_iter()
.filter_map(|r| text(&mut r.into_iter()))
.filter_map(|d| serde_json::from_str(&d).ok())
.collect())
})
}
fn passkey(&self, credential_id: &[u8]) -> BoxFuture<Result<Option<(String, Passkey)>>> {
let (s, id) = (self.clone(), b64url(credential_id));
Box::pin(async move {
let rows =
s.db.rows(s.sql(
"SELECT user_id, data FROM {p}user_passkeys WHERE credential_id = ?",
vec![Arg::Text(id)],
&[Col::Text, Col::Text],
))
.await?;
Ok(rows.into_iter().next().and_then(|r| {
let mut c = r.into_iter();
let user = text(&mut c)?;
let data = serde_json::from_str(&text(&mut c)?).ok()?;
Some((user, data))
}))
})
}
fn save_passkey(&self, user_id: &str, passkey: &Passkey) -> BoxFuture<Result<()>> {
let (s, u, p) = (self.clone(), user_id.to_string(), passkey.clone());
Box::pin(async move {
let id = b64url(&p.credential_id);
let data = serde_json::to_string(&p).map_err(|e| Error::Internal(e.to_string()))?;
let changed = s
.run(
"UPDATE {p}user_passkeys SET data = ? WHERE credential_id = ? AND user_id = ?",
vec![Arg::Text(data.clone()), Arg::Text(id.clone()), Arg::Text(u.clone())],
)
.await?;
if changed == 0 {
s.run(
"INSERT INTO {p}user_passkeys (credential_id, user_id, data) VALUES (?, ?, ?)",
vec![Arg::Text(id), Arg::Text(u), Arg::Text(data)],
)
.await
.map_err(conflict)?;
}
Ok(())
})
}
fn remove_passkey(&self, user_id: &str, credential_id: &[u8]) -> BoxFuture<Result<()>> {
let (s, u, id) = (self.clone(), user_id.to_string(), b64url(credential_id));
Box::pin(async move {
s.run(
"DELETE FROM {p}user_passkeys WHERE user_id = ? AND credential_id = ?",
vec![Arg::Text(u), Arg::Text(id)],
)
.await?;
Ok(())
})
}
}