pebble_cms/services/
database.rs

1use crate::Database;
2use anyhow::Result;
3use serde::Serialize;
4
5#[derive(Debug, Serialize)]
6pub struct DatabaseStats {
7    // File info (path intentionally omitted for security)
8    pub file_size_bytes: i64,
9    pub file_size_human: String,
10
11    // SQLite version and settings
12    pub sqlite_version: String,
13    pub journal_mode: String,
14    pub auto_vacuum: String,
15    pub cache_size: i64,
16    pub page_size: i64,
17    pub page_count: i64,
18    pub freelist_count: i64,
19    pub encoding: String,
20
21    // WAL mode stats (if applicable)
22    pub wal_checkpoint: Option<WalCheckpointStats>,
23
24    // Table statistics
25    pub tables: Vec<TableStats>,
26
27    // Index statistics
28    pub indexes: Vec<IndexStats>,
29
30    // Connection pool stats
31    pub pool_size: u32,
32    pub pool_idle: u32,
33
34    // Integrity check
35    pub integrity_check: String,
36
37    // Performance stats
38    pub compile_options: Vec<String>,
39
40    // Memory stats
41    pub memory_used: i64,
42    pub memory_high_water: i64,
43
44    // Cache stats
45    pub cache_hit: i64,
46    pub cache_miss: i64,
47    pub cache_write: i64,
48    pub cache_spill: i64,
49}
50
51#[derive(Debug, Serialize)]
52pub struct WalCheckpointStats {
53    pub wal_pages: i64,
54    pub wal_frames_checkpointed: i64,
55}
56
57#[derive(Debug, Serialize)]
58pub struct TableStats {
59    pub name: String,
60    pub row_count: i64,
61    pub size_estimate: String,
62}
63
64#[derive(Debug, Serialize)]
65pub struct IndexStats {
66    pub name: String,
67    pub table_name: String,
68    pub is_unique: bool,
69    pub columns: String,
70}
71
72pub fn format_bytes(bytes: i64) -> String {
73    const KB: i64 = 1024;
74    const MB: i64 = KB * 1024;
75    const GB: i64 = MB * 1024;
76
77    if bytes >= GB {
78        format!("{:.2} GB", bytes as f64 / GB as f64)
79    } else if bytes >= MB {
80        format!("{:.2} MB", bytes as f64 / MB as f64)
81    } else if bytes >= KB {
82        format!("{:.2} KB", bytes as f64 / KB as f64)
83    } else {
84        format!("{} bytes", bytes)
85    }
86}
87
88pub fn get_database_stats(db: &Database, db_path: &str) -> Result<DatabaseStats> {
89    let conn = db.get()?;
90
91    // Get file size
92    let file_size_bytes = std::fs::metadata(db_path)
93        .map(|m| m.len() as i64)
94        .unwrap_or(0);
95    let file_size_human = format_bytes(file_size_bytes);
96
97    // SQLite version
98    let sqlite_version: String = conn.query_row("SELECT sqlite_version()", [], |row| row.get(0))?;
99
100    // PRAGMA values
101    let journal_mode: String = conn.query_row("PRAGMA journal_mode", [], |row| row.get(0))?;
102
103    let auto_vacuum: i64 = conn.query_row("PRAGMA auto_vacuum", [], |row| row.get(0))?;
104    let auto_vacuum = match auto_vacuum {
105        0 => "none".to_string(),
106        1 => "full".to_string(),
107        2 => "incremental".to_string(),
108        _ => "unknown".to_string(),
109    };
110
111    let cache_size: i64 = conn.query_row("PRAGMA cache_size", [], |row| row.get(0))?;
112
113    let page_size: i64 = conn.query_row("PRAGMA page_size", [], |row| row.get(0))?;
114
115    let page_count: i64 = conn.query_row("PRAGMA page_count", [], |row| row.get(0))?;
116
117    let freelist_count: i64 = conn.query_row("PRAGMA freelist_count", [], |row| row.get(0))?;
118
119    let encoding: String = conn.query_row("PRAGMA encoding", [], |row| row.get(0))?;
120
121    // WAL checkpoint stats (only if in WAL mode)
122    let wal_checkpoint = if journal_mode.to_lowercase() == "wal" {
123        // Try to get WAL stats without forcing a checkpoint
124        let result: Result<(i64, i64), _> =
125            conn.query_row("PRAGMA wal_checkpoint(PASSIVE)", [], |row| {
126                Ok((row.get(1)?, row.get(2)?))
127            });
128        result.ok().map(|(pages, frames)| WalCheckpointStats {
129            wal_pages: pages,
130            wal_frames_checkpointed: frames,
131        })
132    } else {
133        None
134    };
135
136    // Get table statistics
137    let mut tables = Vec::new();
138    {
139        let mut stmt = conn.prepare(
140            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
141        )?;
142        let table_names: Vec<String> = stmt
143            .query_map([], |row| row.get(0))?
144            .filter_map(|r| r.ok())
145            .collect();
146
147        for table_name in table_names {
148            // Get row count
149            let row_count: i64 = conn
150                .query_row(
151                    &format!("SELECT COUNT(*) FROM \"{}\"", table_name),
152                    [],
153                    |row| row.get(0),
154                )
155                .unwrap_or(0);
156
157            // Estimate size using page count from dbstat (if available)
158            let size_estimate = format!("~{} rows", row_count);
159
160            tables.push(TableStats {
161                name: table_name,
162                row_count,
163                size_estimate,
164            });
165        }
166    }
167
168    // Get index statistics
169    let mut indexes = Vec::new();
170    {
171        let mut stmt = conn.prepare(
172            "SELECT name, tbl_name, sql FROM sqlite_master WHERE type='index' AND name NOT LIKE 'sqlite_%' ORDER BY tbl_name, name"
173        )?;
174        let rows = stmt.query_map([], |row| {
175            Ok((
176                row.get::<_, String>(0)?,
177                row.get::<_, String>(1)?,
178                row.get::<_, Option<String>>(2)?,
179            ))
180        })?;
181
182        for row in rows.flatten() {
183            let (name, table_name, sql) = row;
184            let is_unique = sql
185                .as_ref()
186                .map(|s| s.to_uppercase().contains("UNIQUE"))
187                .unwrap_or(false);
188
189            // Get column info for this index
190            let columns: String = conn
191                .query_row(&format!("PRAGMA index_info(\"{}\")", name), [], |row| {
192                    row.get::<_, String>(2)
193                })
194                .unwrap_or_else(|_| {
195                    // Try to get all columns
196                    let mut cols = Vec::new();
197                    if let Ok(mut idx_stmt) =
198                        conn.prepare(&format!("PRAGMA index_info(\"{}\")", name))
199                    {
200                        if let Ok(idx_rows) = idx_stmt.query_map([], |r| r.get::<_, String>(2)) {
201                            cols = idx_rows.filter_map(|r| r.ok()).collect();
202                        }
203                    }
204                    cols.join(", ")
205                });
206
207            indexes.push(IndexStats {
208                name,
209                table_name,
210                is_unique,
211                columns,
212            });
213        }
214    }
215
216    // Quick integrity check (just checks if OK, doesn't return details for large DBs)
217    let integrity_check: String = conn
218        .query_row("PRAGMA quick_check(1)", [], |row| row.get(0))
219        .unwrap_or_else(|_| "unknown".to_string());
220
221    // Compile options
222    let mut compile_options = Vec::new();
223    {
224        let mut stmt = conn.prepare("PRAGMA compile_options")?;
225        let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
226        for row in rows.flatten() {
227            compile_options.push(row);
228        }
229    }
230
231    // Memory stats using sqlite3_status
232    let memory_used: i64 = conn
233        .query_row("SELECT total_changes()", [], |row| row.get(0))
234        .unwrap_or(0);
235
236    // These require the status extension, fallback to 0
237    let memory_high_water: i64 = 0;
238
239    // Cache stats from sqlite3_db_status
240    let (cache_hit, cache_miss, cache_write, cache_spill) = get_cache_stats(&conn);
241
242    // Pool stats - we can get rough estimates
243    let pool_size = 10u32; // Default from Database::open
244    let pool_idle = 0u32; // Not easily accessible through r2d2
245
246    Ok(DatabaseStats {
247        file_size_bytes,
248        file_size_human,
249        sqlite_version,
250        journal_mode,
251        auto_vacuum,
252        cache_size,
253        page_size,
254        page_count,
255        freelist_count,
256        encoding,
257        wal_checkpoint,
258        tables,
259        indexes,
260        pool_size,
261        pool_idle,
262        integrity_check,
263        compile_options,
264        memory_used,
265        memory_high_water,
266        cache_hit,
267        cache_miss,
268        cache_write,
269        cache_spill,
270    })
271}
272
273fn get_cache_stats(conn: &rusqlite::Connection) -> (i64, i64, i64, i64) {
274    // Try to get cache stats - these may not be available on all builds
275    let cache_hit: i64 = conn
276        .query_row(
277            "SELECT * FROM pragma_database_list WHERE name='main'",
278            [],
279            |_| Ok(0i64),
280        )
281        .unwrap_or(0);
282
283    // These stats aren't directly queryable via SQL in standard SQLite
284    // Return placeholder values
285    (cache_hit, 0, 0, 0)
286}
287
288#[derive(Debug, Serialize)]
289pub struct DatabaseAnalysis {
290    pub fragmentation_percent: f64,
291    pub wasted_space_bytes: i64,
292    pub wasted_space_human: String,
293    pub recommendations: Vec<String>,
294}
295
296pub fn analyze_database(db: &Database, db_path: &str) -> Result<DatabaseAnalysis> {
297    let stats = get_database_stats(db, db_path)?;
298
299    let mut recommendations = Vec::new();
300
301    // Check fragmentation (freelist pages vs total pages)
302    let fragmentation_percent = if stats.page_count > 0 {
303        (stats.freelist_count as f64 / stats.page_count as f64) * 100.0
304    } else {
305        0.0
306    };
307
308    let wasted_space_bytes = stats.freelist_count * stats.page_size;
309    let wasted_space_human = format_bytes(wasted_space_bytes);
310
311    if fragmentation_percent > 10.0 {
312        recommendations.push(format!(
313            "Database fragmentation is {:.1}%. Consider running VACUUM to reclaim {} of space.",
314            fragmentation_percent, wasted_space_human
315        ));
316    }
317
318    // Check auto_vacuum
319    if stats.auto_vacuum == "none" && stats.file_size_bytes > 10 * 1024 * 1024 {
320        recommendations.push(
321            "Auto-vacuum is disabled. Consider enabling it for automatic space reclamation."
322                .to_string(),
323        );
324    }
325
326    // Check cache size
327    if stats.cache_size.abs() < 2000 && stats.file_size_bytes > 50 * 1024 * 1024 {
328        recommendations.push(
329            "Cache size is relatively small for the database size. Consider increasing cache_size for better performance."
330                .to_string(),
331        );
332    }
333
334    // Check integrity
335    if stats.integrity_check != "ok" {
336        recommendations.push(format!(
337            "Integrity check returned: {}. Database may have corruption issues.",
338            stats.integrity_check
339        ));
340    }
341
342    // Check for tables without indexes (simple heuristic)
343    for table in &stats.tables {
344        if table.row_count > 1000 {
345            let has_index = stats.indexes.iter().any(|i| i.table_name == table.name);
346            if !has_index && !table.name.ends_with("_fts") && !table.name.contains("_content") {
347                recommendations.push(format!(
348                    "Table '{}' has {} rows but no indexes. Consider adding indexes for frequently queried columns.",
349                    table.name, table.row_count
350                ));
351            }
352        }
353    }
354
355    if recommendations.is_empty() {
356        recommendations.push("Database is healthy. No recommendations at this time.".to_string());
357    }
358
359    Ok(DatabaseAnalysis {
360        fragmentation_percent,
361        wasted_space_bytes,
362        wasted_space_human,
363        recommendations,
364    })
365}
366
367pub fn run_vacuum(db: &Database) -> Result<()> {
368    let conn = db.get()?;
369    conn.execute_batch("VACUUM")?;
370    Ok(())
371}
372
373pub fn run_analyze(db: &Database) -> Result<()> {
374    let conn = db.get()?;
375    conn.execute_batch("ANALYZE")?;
376    Ok(())
377}
378
379pub fn run_integrity_check(db: &Database) -> Result<Vec<String>> {
380    let conn = db.get()?;
381    let mut stmt = conn.prepare("PRAGMA integrity_check")?;
382    let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
383    let results: Vec<String> = rows.filter_map(|r| r.ok()).collect();
384    Ok(results)
385}
pebble_cms/services/database.rs

pebble_cms/services/
database.rs
