things3_core/database/
pool.rs

1//! Connection pool configuration, optimizations, and health/metrics types.
2
3use crate::database::stats::DatabaseStats;
4use crate::error::{Result as ThingsResult, ThingsError};
5use chrono::{DateTime, Utc};
6use serde::{Deserialize, Serialize};
7use sqlx::SqlitePool;
8use std::time::Duration;
9use tracing::debug;
10
11/// Database connection pool configuration for optimal performance
12#[derive(Debug, Clone, Serialize, Deserialize)]
13pub struct DatabasePoolConfig {
14    /// Maximum number of connections in the pool
15    pub max_connections: u32,
16    /// Minimum number of connections in the pool
17    pub min_connections: u32,
18    /// Connection timeout
19    pub connect_timeout: Duration,
20    /// Idle timeout for connections
21    pub idle_timeout: Duration,
22    /// Maximum lifetime of a connection
23    pub max_lifetime: Duration,
24    /// Test connections before use
25    pub test_before_acquire: bool,
26    /// SQLite-specific optimizations
27    pub sqlite_optimizations: SqliteOptimizations,
28}
29
30/// SQLite-specific optimization settings
31#[derive(Debug, Clone, Serialize, Deserialize)]
32pub struct SqliteOptimizations {
33    /// Enable WAL mode for better concurrency
34    pub enable_wal_mode: bool,
35    /// Set synchronous mode (NORMAL, FULL, OFF)
36    pub synchronous_mode: String,
37    /// Cache size in pages (negative = KB)
38    pub cache_size: i32,
39    /// Enable foreign key constraints
40    pub enable_foreign_keys: bool,
41    /// Set journal mode
42    pub journal_mode: String,
43    /// Set temp store (MEMORY, FILE, DEFAULT)
44    pub temp_store: String,
45    /// Set mmap size for better performance
46    pub mmap_size: i64,
47    /// Enable query planner optimizations
48    pub enable_query_planner: bool,
49}
50
51impl Default for DatabasePoolConfig {
52    fn default() -> Self {
53        Self {
54            max_connections: 10,
55            min_connections: 1,
56            connect_timeout: Duration::from_secs(30),
57            idle_timeout: Duration::from_secs(600), // 10 minutes
58            max_lifetime: Duration::from_secs(1800), // 30 minutes
59            test_before_acquire: true,
60            sqlite_optimizations: SqliteOptimizations::default(),
61        }
62    }
63}
64
65impl Default for SqliteOptimizations {
66    fn default() -> Self {
67        Self {
68            enable_wal_mode: true,
69            synchronous_mode: "NORMAL".to_string(),
70            cache_size: -20000, // 20MB cache
71            enable_foreign_keys: true,
72            journal_mode: "WAL".to_string(),
73            temp_store: "MEMORY".to_string(),
74            mmap_size: 268_435_456, // 256MB
75            enable_query_planner: true,
76        }
77    }
78}
79
80/// Connection pool health status
81#[derive(Debug, Clone, Serialize, Deserialize)]
82pub struct PoolHealthStatus {
83    pub is_healthy: bool,
84    pub pool_size: u32,
85    pub active_connections: u32,
86    pub idle_connections: u32,
87    pub max_connections: u32,
88    pub min_connections: u32,
89    pub connection_timeout: Duration,
90    pub idle_timeout: Option<Duration>,
91    pub max_lifetime: Option<Duration>,
92}
93
94/// Detailed connection pool metrics
95#[derive(Debug, Clone, Serialize, Deserialize)]
96pub struct PoolMetrics {
97    pub pool_size: u32,
98    pub active_connections: u32,
99    pub idle_connections: u32,
100    pub max_connections: u32,
101    pub min_connections: u32,
102    pub utilization_percentage: f64,
103    pub is_healthy: bool,
104    pub response_time_ms: u64,
105    pub connection_timeout: Duration,
106    pub idle_timeout: Option<Duration>,
107    pub max_lifetime: Option<Duration>,
108}
109
110/// Comprehensive health status including pool and database
111#[derive(Debug, Clone, Serialize, Deserialize)]
112pub struct ComprehensiveHealthStatus {
113    pub overall_healthy: bool,
114    pub pool_health: PoolHealthStatus,
115    pub pool_metrics: PoolMetrics,
116    pub database_stats: DatabaseStats,
117    pub timestamp: DateTime<Utc>,
118}
119
120/// Apply SQLite-specific optimizations to an open pool.
121pub(crate) async fn apply_sqlite_optimizations(
122    pool: &SqlitePool,
123    optimizations: &SqliteOptimizations,
124) -> ThingsResult<()> {
125    sqlx::query(&format!(
126        "PRAGMA journal_mode = {}",
127        optimizations.journal_mode
128    ))
129    .execute(pool)
130    .await
131    .map_err(|e| ThingsError::unknown(format!("Failed to set journal mode: {e}")))?;
132
133    sqlx::query(&format!(
134        "PRAGMA synchronous = {}",
135        optimizations.synchronous_mode
136    ))
137    .execute(pool)
138    .await
139    .map_err(|e| ThingsError::unknown(format!("Failed to set synchronous mode: {e}")))?;
140
141    sqlx::query(&format!("PRAGMA cache_size = {}", optimizations.cache_size))
142        .execute(pool)
143        .await
144        .map_err(|e| ThingsError::unknown(format!("Failed to set cache size: {e}")))?;
145
146    let fk_setting = if optimizations.enable_foreign_keys {
147        "ON"
148    } else {
149        "OFF"
150    };
151    sqlx::query(&format!("PRAGMA foreign_keys = {fk_setting}"))
152        .execute(pool)
153        .await
154        .map_err(|e| ThingsError::unknown(format!("Failed to set foreign keys: {e}")))?;
155
156    sqlx::query(&format!("PRAGMA temp_store = {}", optimizations.temp_store))
157        .execute(pool)
158        .await
159        .map_err(|e| ThingsError::unknown(format!("Failed to set temp store: {e}")))?;
160
161    sqlx::query(&format!("PRAGMA mmap_size = {}", optimizations.mmap_size))
162        .execute(pool)
163        .await
164        .map_err(|e| ThingsError::unknown(format!("Failed to set mmap size: {e}")))?;
165
166    if optimizations.enable_query_planner {
167        sqlx::query("PRAGMA optimize")
168            .execute(pool)
169            .await
170            .map_err(|e| ThingsError::unknown(format!("Failed to optimize database: {e}")))?;
171    }
172
173    debug!(
174        "Applied SQLite optimizations: WAL={}, sync={}, cache={}KB, fk={}, temp={}, mmap={}MB",
175        optimizations.enable_wal_mode,
176        optimizations.synchronous_mode,
177        optimizations.cache_size.abs() / 1024,
178        optimizations.enable_foreign_keys,
179        optimizations.temp_store,
180        optimizations.mmap_size / 1024 / 1024
181    );
182
183    Ok(())
184}
185
186#[cfg(test)]
187mod tests {
188    use super::*;
189
190    #[test]
191    fn test_database_pool_config_default() {
192        let config = DatabasePoolConfig::default();
193        assert_eq!(config.max_connections, 10);
194        assert_eq!(config.min_connections, 1);
195        assert_eq!(config.connect_timeout, Duration::from_secs(30));
196        assert_eq!(config.idle_timeout, Duration::from_secs(600));
197        assert_eq!(config.max_lifetime, Duration::from_secs(1800));
198        assert!(config.test_before_acquire);
199    }
200
201    #[test]
202    fn test_sqlite_optimizations_default() {
203        let opts = SqliteOptimizations::default();
204        assert!(opts.enable_wal_mode);
205        assert_eq!(opts.cache_size, -20000);
206        assert_eq!(opts.synchronous_mode, "NORMAL".to_string());
207        assert_eq!(opts.temp_store, "MEMORY".to_string());
208        assert_eq!(opts.journal_mode, "WAL".to_string());
209        assert_eq!(opts.mmap_size, 268_435_456);
210        assert!(opts.enable_foreign_keys);
211        assert!(opts.enable_query_planner);
212    }
213
214    #[test]
215    fn test_pool_health_status_creation() {
216        let status = PoolHealthStatus {
217            is_healthy: true,
218            pool_size: 8,
219            active_connections: 5,
220            idle_connections: 3,
221            max_connections: 10,
222            min_connections: 1,
223            connection_timeout: Duration::from_secs(30),
224            idle_timeout: Some(Duration::from_secs(600)),
225            max_lifetime: Some(Duration::from_secs(1800)),
226        };
227        assert!(status.is_healthy);
228        assert_eq!(status.active_connections, 5);
229        assert_eq!(status.idle_connections, 3);
230        assert_eq!(status.pool_size, 8);
231    }
232
233    #[test]
234    fn test_pool_metrics_creation() {
235        let metrics = PoolMetrics {
236            pool_size: 8,
237            active_connections: 5,
238            idle_connections: 3,
239            max_connections: 10,
240            min_connections: 1,
241            utilization_percentage: 80.0,
242            is_healthy: true,
243            response_time_ms: 50,
244            connection_timeout: Duration::from_secs(30),
245            idle_timeout: Some(Duration::from_secs(600)),
246            max_lifetime: Some(Duration::from_secs(1800)),
247        };
248        assert!(metrics.is_healthy);
249        assert_eq!(metrics.pool_size, 8);
250        assert_eq!(metrics.active_connections, 5);
251        assert_eq!(metrics.idle_connections, 3);
252        assert!((metrics.utilization_percentage - 80.0).abs() < f64::EPSILON);
253        assert_eq!(metrics.response_time_ms, 50);
254    }
255
256    #[test]
257    fn test_comprehensive_health_status_creation() {
258        let pool_health = PoolHealthStatus {
259            is_healthy: true,
260            pool_size: 8,
261            active_connections: 5,
262            idle_connections: 3,
263            max_connections: 10,
264            min_connections: 1,
265            connection_timeout: Duration::from_secs(30),
266            idle_timeout: Some(Duration::from_secs(600)),
267            max_lifetime: Some(Duration::from_secs(1800)),
268        };
269
270        let pool_metrics = PoolMetrics {
271            pool_size: 8,
272            active_connections: 5,
273            idle_connections: 3,
274            max_connections: 10,
275            min_connections: 1,
276            utilization_percentage: 80.0,
277            is_healthy: true,
278            response_time_ms: 50,
279            connection_timeout: Duration::from_secs(30),
280            idle_timeout: Some(Duration::from_secs(600)),
281            max_lifetime: Some(Duration::from_secs(1800)),
282        };
283
284        let db_stats = DatabaseStats {
285            task_count: 50,
286            project_count: 10,
287            area_count: 5,
288        };
289
290        let health_status = ComprehensiveHealthStatus {
291            overall_healthy: true,
292            pool_health,
293            pool_metrics,
294            database_stats: db_stats,
295            timestamp: Utc::now(),
296        };
297
298        assert!(health_status.overall_healthy);
299        assert_eq!(health_status.database_stats.total_items(), 65);
300    }
301}
things3_core/database/pool.rs

things3_core/database/
pool.rs
