onis / onis-common / src / db.rs

//! SQLite database helpers for onis using sqlx.
//!
//! Two database types:
//! - Per-DID databases: one per user, stores their DNS records
//! - Reverse index: shared database mapping domains → DIDs + verification status
//!
//! Migrations live in:
//!   migrations/user/     — per-DID database migrations
//!   migrations/index/    — reverse index migrations

use std::path::Path;

use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
use thiserror::Error;

use crate::config::DatabaseConfig;

#[derive(Debug, Error)]
pub enum DbError {
    #[error("sqlx error: {0}")]
    Sqlx(#[from] sqlx::Error),
    #[error("migrate error: {0}")]
    Migrate(#[from] sqlx::migrate::MigrateError),
    #[error("io error: {0}")]
    Io(#[from] std::io::Error),
}

/// Opens (or creates) a per-DID SQLite database.
///
/// Runs migrations from `migrations/user/` on first open.
pub async fn open_user_db(path: &Path, db_config: &DatabaseConfig) -> Result<SqlitePool, DbError> {
    if let Some(parent) = path.parent() {
        tokio::fs::create_dir_all(parent).await?;
    }

    let opts = SqliteConnectOptions::new()
        .filename(path)
        .create_if_missing(true)
        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
        .busy_timeout(std::time::Duration::from_secs(db_config.busy_timeout));

    let pool = SqlitePoolOptions::new()
        .max_connections(db_config.user_max_connections)
        .connect_with(opts)
        .await?;

    let migrator = sqlx::migrate!("../migrations/user");
    tracing::info!("migrations to run: {}", migrator.migrations.len());
    migrator.run(&pool).await?;

    Ok(pool)
}

/// Opens (or creates) the shared reverse index database.
///
/// Runs migrations from `migrations/index/` on first open.
pub async fn open_index_db(path: &Path, db_config: &DatabaseConfig) -> Result<SqlitePool, DbError> {
    if let Some(parent) = path.parent() {
        tokio::fs::create_dir_all(parent).await?;
    }

    let opts = SqliteConnectOptions::new()
        .filename(path)
        .create_if_missing(true)
        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
        .busy_timeout(std::time::Duration::from_secs(db_config.busy_timeout));

    let pool = SqlitePoolOptions::new()
        .max_connections(db_config.index_max_connections)
        .connect_with(opts)
        .await?;

    sqlx::migrate!("../migrations/index").run(&pool).await?;

    Ok(pool)
}

/// Converts a DID to a filesystem-safe path for its database.
///
/// e.g. `did:plc:adtzorbhmmjbzxsl2y4vqlqs` → `{base}/ad/tz/did_plc_adtzorbhmmjbzxsl2y4vqlqs.db`
/// e.g. `did:web:vim.sh` → `{base}/vi/m./did_web_vim.sh.db`
pub fn did_to_db_path(base: &Path, did: &str) -> std::path::PathBuf {
    let safe = did.replace(':', "_");

    // use first 4 chars after "did_(plc|web)_" for sharding
    // XXX: this will break if another did method is added
    //      thats method is longer than 3 characters.
    let shard = if safe.len() > 8 {
        &safe[8..]
    } else {
        &safe
    };

    let (a, b) = if shard.len() >= 4 {
        (&shard[..2], &shard[2..4])
    } else {
        ("xx", "xx")
    };
    base.join(a).join(b).join(format!("{safe}.db"))
}

#[cfg(test)]
mod tests {
    use super::*;
    use std::path::PathBuf;

    #[test]
    fn did_plc_standard() {
        let base = PathBuf::from("/data/dbs");
        let path = did_to_db_path(&base, "did:plc:adtzorbhmmjbzxsl2y4vqlqs");
        assert_eq!(
            path,
            PathBuf::from("/data/dbs/ad/tz/did_plc_adtzorbhmmjbzxsl2y4vqlqs.db")
        );
    }

    #[test]
    fn did_web_domain() {
        let base = PathBuf::from("/data/dbs");
        let path = did_to_db_path(&base, "did:web:example.com");
        assert_eq!(
            path,
            PathBuf::from("/data/dbs/ex/am/did_web_example.com.db")
        );
    }

    #[test]
    fn did_web_short_domain() {
        let base = PathBuf::from("/data/dbs");
        let path = did_to_db_path(&base, "did:web:vim.sh");
        assert_eq!(
            path,
            PathBuf::from("/data/dbs/vi/m./did_web_vim.sh.db")
        );
    }

    #[test]
    fn did_web_subdomain() {
        let base = PathBuf::from("/data/dbs");
        let path = did_to_db_path(&base, "did:web:sub.example.com");
        assert_eq!(
            path,
            PathBuf::from("/data/dbs/su/b./did_web_sub.example.com.db")
        );
    }

    #[test]
    fn did_web_very_short_falls_back() {
        let base = PathBuf::from("/data/dbs");
        let path = did_to_db_path(&base, "did:web:a.b");
        assert_eq!(
            path,
            PathBuf::from("/data/dbs/xx/xx/did_web_a.b.db")
        );
    }

    #[test]
    fn did_plc_short_falls_back() {
        let base = PathBuf::from("/data/dbs");
        let path = did_to_db_path(&base, "did:plc:abc");
        assert_eq!(
            path,
            PathBuf::from("/data/dbs/xx/xx/did_plc_abc.db")
        );
    }

    #[test]
    fn different_dids_produce_different_paths() {
        let base = PathBuf::from("/data/dbs");
        let a = did_to_db_path(&base, "did:plc:aaaa1111bbbb2222");
        let b = did_to_db_path(&base, "did:plc:cccc3333dddd4444");
        assert_ne!(a, b);
    }

    #[test]
    fn same_identifier_different_method_produces_different_paths() {
        let base = PathBuf::from("/data/dbs");
        let plc = did_to_db_path(&base, "did:plc:example.com");
        let web = did_to_db_path(&base, "did:web:example.com");
        assert_ne!(plc, web);
        assert_eq!(plc.parent(), web.parent());
    }
}