//! SQLite schema management for QuickDID. //! //! This module provides functionality to create and manage the SQLite database //! schema used by the SQLite-backed handle resolver cache. use anyhow::Result; use sqlx::{Sqlite, SqlitePool, migrate::MigrateDatabase}; use std::path::Path; /// SQL schema for the handle resolution cache table. const CREATE_HANDLE_RESOLUTION_CACHE_TABLE: &str = r#" CREATE TABLE IF NOT EXISTS handle_resolution_cache ( key INTEGER PRIMARY KEY, result BLOB NOT NULL, created INTEGER NOT NULL, updated INTEGER NOT NULL ); CREATE INDEX IF NOT EXISTS idx_handle_resolution_cache_updated ON handle_resolution_cache(updated); "#; /// SQL schema for the handle resolution queue table. const CREATE_HANDLE_RESOLUTION_QUEUE_TABLE: &str = r#" CREATE TABLE IF NOT EXISTS handle_resolution_queue ( id INTEGER PRIMARY KEY AUTOINCREMENT, work TEXT NOT NULL, queued_at INTEGER NOT NULL ); CREATE INDEX IF NOT EXISTS idx_handle_resolution_queue_queued_at ON handle_resolution_queue(queued_at); "#; /// Create or connect to a SQLite database and ensure schema is initialized. /// /// # Arguments /// /// * `database_url` - SQLite database URL (e.g., "sqlite:./quickdid.db" or "sqlite::memory:") /// /// # Returns /// /// Returns a SqlitePool connected to the database with schema initialized. /// /// # Example /// /// ```no_run /// use quickdid::sqlite_schema::create_sqlite_pool; /// /// # async fn example() -> anyhow::Result<()> { /// // File-based database /// let pool = create_sqlite_pool("sqlite:./quickdid.db").await?; /// /// // In-memory database (for testing) /// let pool = create_sqlite_pool("sqlite::memory:").await?; /// # Ok(()) /// # } /// ``` pub async fn create_sqlite_pool(database_url: &str) -> Result { tracing::info!("Initializing SQLite database: {}", database_url); // Extract the database path from the URL for file-based databases if let Some(path) = database_url.strip_prefix("sqlite:") && path != ":memory:" && !path.is_empty() { // Create the database file if it doesn't exist if !Sqlite::database_exists(database_url).await? { tracing::info!("Creating SQLite database file: {}", path); Sqlite::create_database(database_url).await?; } // Ensure the parent directory exists if let Some(parent) = Path::new(path).parent() && !parent.exists() { tracing::info!("Creating directory: {}", parent.display()); std::fs::create_dir_all(parent)?; } } // Connect to the database let pool = SqlitePool::connect(database_url).await?; tracing::info!("Connected to SQLite database"); // Create the schema create_schema(&pool).await?; Ok(pool) } /// Create the database schema if it doesn't exist. /// /// # Arguments /// /// * `pool` - SQLite connection pool /// /// # Example /// /// ```no_run /// use quickdid::sqlite_schema::create_schema; /// use sqlx::SqlitePool; /// /// # async fn example() -> anyhow::Result<()> { /// let pool = SqlitePool::connect("sqlite::memory:").await?; /// create_schema(&pool).await?; /// # Ok(()) /// # } /// ``` pub async fn create_schema(pool: &SqlitePool) -> Result<()> { tracing::debug!("Creating SQLite schema if not exists"); // Execute the schema creation SQL sqlx::query(CREATE_HANDLE_RESOLUTION_CACHE_TABLE) .execute(pool) .await?; sqlx::query(CREATE_HANDLE_RESOLUTION_QUEUE_TABLE) .execute(pool) .await?; tracing::info!("SQLite schema initialized"); Ok(()) } /// Clean up expired entries from the handle resolution cache. /// /// This function removes entries that are older than the specified TTL. /// It should be called periodically to prevent the database from growing indefinitely. /// /// # Arguments /// /// * `pool` - SQLite connection pool /// * `ttl_seconds` - TTL in seconds for cache entries /// /// # Returns /// /// Returns the number of entries deleted. /// /// # Example /// /// ```no_run /// use quickdid::sqlite_schema::cleanup_expired_entries; /// use sqlx::SqlitePool; /// /// # async fn example() -> anyhow::Result<()> { /// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?; /// let deleted_count = cleanup_expired_entries(&pool, 7776000).await?; // 90 days /// println!("Deleted {} expired entries", deleted_count); /// # Ok(()) /// # } /// ``` pub async fn cleanup_expired_entries(pool: &SqlitePool, ttl_seconds: u64) -> Result { let current_timestamp = std::time::SystemTime::now() .duration_since(std::time::UNIX_EPOCH) .unwrap_or_default() .as_secs() as i64; let cutoff_timestamp = current_timestamp - (ttl_seconds as i64); let result = sqlx::query("DELETE FROM handle_resolution_cache WHERE updated < ?1") .bind(cutoff_timestamp) .execute(pool) .await?; let deleted_count = result.rows_affected(); if deleted_count > 0 { tracing::info!("Cleaned up {} expired cache entries", deleted_count); } Ok(deleted_count) } /// Get statistics about the handle resolution cache. /// /// # Arguments /// /// * `pool` - SQLite connection pool /// /// # Returns /// /// Returns a tuple of (total_entries, database_size_bytes). /// /// # Example /// /// ```no_run /// use quickdid::sqlite_schema::get_cache_stats; /// use sqlx::SqlitePool; /// /// # async fn example() -> anyhow::Result<()> { /// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?; /// let (total_entries, size_bytes) = get_cache_stats(&pool).await?; /// println!("Cache has {} entries, {} bytes", total_entries, size_bytes); /// # Ok(()) /// # } /// ``` pub async fn get_cache_stats(pool: &SqlitePool) -> Result<(i64, i64)> { // Get total entries let total_entries: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_cache") .fetch_one(pool) .await?; // Get database page size and page count to calculate total size let page_size: i64 = sqlx::query_scalar("PRAGMA page_size") .fetch_one(pool) .await?; let page_count: i64 = sqlx::query_scalar("PRAGMA page_count") .fetch_one(pool) .await?; let size_bytes = page_size * page_count; Ok((total_entries, size_bytes)) } /// Clean up old entries from the handle resolution queue. /// /// This function removes entries that are older than the specified age. /// /// # Arguments /// /// * `pool` - SQLite connection pool /// * `max_age_seconds` - Maximum age in seconds for queue entries to be kept /// /// # Returns /// /// Returns the number of entries deleted. /// /// # Example /// /// ```no_run /// use quickdid::sqlite_schema::cleanup_queue_entries; /// use sqlx::SqlitePool; /// /// # async fn example() -> anyhow::Result<()> { /// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?; /// let deleted_count = cleanup_queue_entries(&pool, 86400).await?; // 1 day /// println!("Deleted {} old queue entries", deleted_count); /// # Ok(()) /// # } /// ``` pub async fn cleanup_queue_entries(pool: &SqlitePool, max_age_seconds: u64) -> Result { let current_timestamp = std::time::SystemTime::now() .duration_since(std::time::UNIX_EPOCH) .unwrap_or_default() .as_secs() as i64; let cutoff_timestamp = current_timestamp - (max_age_seconds as i64); let result = sqlx::query("DELETE FROM handle_resolution_queue WHERE queued_at < ?1") .bind(cutoff_timestamp) .execute(pool) .await?; let deleted_count = result.rows_affected(); if deleted_count > 0 { tracing::info!("Cleaned up {} old queue entries", deleted_count); } Ok(deleted_count) } /// Get statistics about the handle resolution queue. /// /// # Arguments /// /// * `pool` - SQLite connection pool /// /// # Returns /// /// Returns the total number of entries in the queue. /// /// # Example /// /// ```no_run /// use quickdid::sqlite_schema::get_queue_stats; /// use sqlx::SqlitePool; /// /// # async fn example() -> anyhow::Result<()> { /// let pool = SqlitePool::connect("sqlite:./quickdid.db").await?; /// let total = get_queue_stats(&pool).await?; /// println!("Queue: {} total entries", total); /// # Ok(()) /// # } /// ``` pub async fn get_queue_stats(pool: &SqlitePool) -> Result { // Get total entries let total_entries: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_queue") .fetch_one(pool) .await?; Ok(total_entries) } #[cfg(test)] mod tests { use super::*; #[tokio::test] async fn test_create_sqlite_pool_memory() { let pool = create_sqlite_pool("sqlite::memory:") .await .expect("Failed to create in-memory SQLite pool"); // Verify the table was created let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_cache") .fetch_one(&pool) .await .expect("Failed to query table"); assert_eq!(count, 0); } #[tokio::test] async fn test_cleanup_expired_entries() { let pool = create_sqlite_pool("sqlite::memory:") .await .expect("Failed to create in-memory SQLite pool"); // Insert a test entry that's already expired let old_timestamp = std::time::SystemTime::now() .duration_since(std::time::UNIX_EPOCH) .unwrap() .as_secs() as i64 - 3600; // 1 hour ago sqlx::query( "INSERT INTO handle_resolution_cache (key, result, created, updated) VALUES (1, ?1, ?2, ?2)" ) .bind(&b"test_data"[..]) .bind(old_timestamp) .execute(&pool) .await .expect("Failed to insert test data"); // Clean up entries older than 30 minutes (1800 seconds) let deleted = cleanup_expired_entries(&pool, 1800) .await .expect("Failed to cleanup expired entries"); assert_eq!(deleted, 1); // Verify the entry was deleted let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM handle_resolution_cache") .fetch_one(&pool) .await .expect("Failed to query table"); assert_eq!(count, 0); } #[tokio::test] async fn test_get_cache_stats() { let pool = create_sqlite_pool("sqlite::memory:") .await .expect("Failed to create in-memory SQLite pool"); // Insert a test entry let current_timestamp = std::time::SystemTime::now() .duration_since(std::time::UNIX_EPOCH) .unwrap() .as_secs() as i64; sqlx::query( "INSERT INTO handle_resolution_cache (key, result, created, updated) VALUES (1, ?1, ?2, ?2)" ) .bind(&b"test_data"[..]) .bind(current_timestamp) .execute(&pool) .await .expect("Failed to insert test data"); let (total_entries, size_bytes) = get_cache_stats(&pool) .await .expect("Failed to get cache stats"); assert_eq!(total_entries, 1); assert!(size_bytes > 0); } }