things3_core/database/
pool.rs

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

things3_core/database/
pool.rs
