sbol_db_sqlite/
stats.rs

1//! SQLite maintenance/observability views.
2//!
3//! SQLite is a single-writer embedded engine with no session/lock/per-statement
4//! timing catalogs, so the session, lock, and slow-query surfaces are empty and
5//! [`SqliteStats::has_slow_query_stats`] reports `false`; the lab UI gates those
6//! panels on the backend's capabilities. The table/index/schema surfaces read
7//! `sqlite_master` and the `PRAGMA` introspection functions. On-disk sizing uses
8//! the `dbstat` virtual table when available and falls back to zero when SQLite
9//! was built without it.
10
11use async_trait::async_trait;
12use sbol_db_core::DomainError;
13use sbol_db_storage::{
14    Activity, BlockingLock, DatabaseSize, DbStats, IncomingForeignKey, IndexStats,
15    OutgoingForeignKey, RelationalColumn, RelationalSchema, RelationalTable, SlowQuery,
16    TableColumn, TableSchema, TableStats,
17};
18use sqlx::Row;
19
20use crate::pool::db_err;
21use crate::SqlitePool;
22
23/// The introspection capability for a SQLite backend. Cloneable; clones share
24/// the pool.
25#[derive(Clone)]
26pub struct SqliteStats {
27    pool: SqlitePool,
28}
29
30/// Quote a SQLite identifier for safe interpolation into a `PRAGMA` call, which
31/// can't take a bound parameter. Embedded double-quotes are doubled.
32fn quote_ident(name: &str) -> String {
33    format!("\"{}\"", name.replace('"', "\"\""))
34}
35
36impl SqliteStats {
37    pub fn new(pool: SqlitePool) -> Self {
38        Self { pool }
39    }
40
41    /// The user tables, ordered by name: everything in `sqlite_master` that
42    /// isn't an internal `sqlite_*` object or the migration bookkeeping table.
43    async fn user_tables(&self) -> Result<Vec<String>, DomainError> {
44        let rows = sqlx::query(
45            "SELECT name FROM sqlite_master \
46             WHERE type = 'table' \
47               AND name NOT LIKE 'sqlite_%' \
48               AND name <> '_sqlx_migrations' \
49             ORDER BY name",
50        )
51        .fetch_all(&self.pool)
52        .await
53        .map_err(db_err)?;
54        rows.into_iter()
55            .map(|r| r.try_get::<String, _>("name").map_err(db_err))
56            .collect()
57    }
58
59    /// Total on-disk bytes a table or index occupies, via the `dbstat` virtual
60    /// table. `dbstat` is an optional build, so any error degrades to zero.
61    async fn object_bytes(&self, name: &str) -> i64 {
62        sqlx::query_scalar::<_, i64>("SELECT COALESCE(SUM(pgsize), 0) FROM dbstat WHERE name = ?")
63            .bind(name)
64            .fetch_one(&self.pool)
65            .await
66            .unwrap_or(0)
67    }
68
69    /// Whether a name exists as a user table in `sqlite_master`.
70    async fn table_exists(&self, name: &str) -> Result<bool, DomainError> {
71        let found: Option<String> =
72            sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
73                .bind(name)
74                .fetch_optional(&self.pool)
75                .await
76                .map_err(db_err)?;
77        Ok(found.is_some())
78    }
79}
80
81#[async_trait]
82impl DbStats for SqliteStats {
83    async fn schema_overview(&self) -> Result<RelationalSchema, DomainError> {
84        let mut tables = Vec::new();
85        for name in self.user_tables().await? {
86            let rows = sqlx::query(&format!("PRAGMA table_info({})", quote_ident(&name)))
87                .fetch_all(&self.pool)
88                .await
89                .map_err(db_err)?;
90            let columns = rows
91                .into_iter()
92                .map(|r| {
93                    Ok::<_, DomainError>(RelationalColumn {
94                        name: r.try_get("name").map_err(db_err)?,
95                        column_type: r.try_get("type").map_err(db_err)?,
96                        nullable: r.try_get::<i64, _>("notnull").map_err(db_err)? == 0,
97                    })
98                })
99                .collect::<Result<_, _>>()?;
100            tables.push(RelationalTable { name, columns });
101        }
102        Ok(RelationalSchema { tables })
103    }
104
105    async fn database_size(&self) -> Result<DatabaseSize, DomainError> {
106        let page_count: i64 = sqlx::query_scalar("PRAGMA page_count")
107            .fetch_one(&self.pool)
108            .await
109            .map_err(db_err)?;
110        let page_size: i64 = sqlx::query_scalar("PRAGMA page_size")
111            .fetch_one(&self.pool)
112            .await
113            .map_err(db_err)?;
114        Ok(DatabaseSize {
115            database: "main".to_string(),
116            total_bytes: page_count * page_size,
117        })
118    }
119
120    async fn table_stats(&self, limit: i64, offset: i64) -> Result<Vec<TableStats>, DomainError> {
121        let names: Vec<String> = self
122            .user_tables()
123            .await?
124            .into_iter()
125            .skip(offset.max(0) as usize)
126            .take(limit.max(0) as usize)
127            .collect();
128
129        let mut stats = Vec::with_capacity(names.len());
130        for name in names {
131            let rows_estimate: i64 =
132                sqlx::query_scalar(&format!("SELECT COUNT(*) FROM {}", quote_ident(&name)))
133                    .fetch_one(&self.pool)
134                    .await
135                    .map_err(db_err)?;
136            let total_bytes = self.object_bytes(&name).await;
137            stats.push(TableStats {
138                name,
139                rows_estimate,
140                total_bytes,
141                index_bytes: 0,
142                n_live_tup: rows_estimate,
143                n_dead_tup: 0,
144                last_vacuum: None,
145                last_autovacuum: None,
146                last_analyze: None,
147            });
148        }
149        Ok(stats)
150    }
151
152    async fn index_stats(&self, limit: i64) -> Result<Vec<IndexStats>, DomainError> {
153        let rows = sqlx::query(
154            "SELECT name, tbl_name FROM sqlite_master \
155             WHERE type = 'index' AND name NOT LIKE 'sqlite_%' \
156             ORDER BY name LIMIT ?",
157        )
158        .bind(limit)
159        .fetch_all(&self.pool)
160        .await
161        .map_err(db_err)?;
162
163        let mut stats = Vec::with_capacity(rows.len());
164        for r in rows {
165            let index: String = r.try_get("name").map_err(db_err)?;
166            let table: String = r.try_get("tbl_name").map_err(db_err)?;
167            let bytes = self.object_bytes(&index).await;
168            stats.push(IndexStats {
169                table,
170                index,
171                idx_scan: 0,
172                bytes,
173            });
174        }
175        Ok(stats)
176    }
177
178    async fn activity(&self, _limit: i64) -> Result<Vec<Activity>, DomainError> {
179        Ok(vec![])
180    }
181
182    async fn blocking_locks(&self) -> Result<Vec<BlockingLock>, DomainError> {
183        Ok(vec![])
184    }
185
186    async fn table_schema(&self, name: &str) -> Result<Option<TableSchema>, DomainError> {
187        if !self.table_exists(name).await? {
188            return Ok(None);
189        }
190
191        let column_rows = sqlx::query(&format!("PRAGMA table_info({})", quote_ident(name)))
192            .fetch_all(&self.pool)
193            .await
194            .map_err(db_err)?;
195        let columns = column_rows
196            .into_iter()
197            .map(|r| {
198                Ok::<_, DomainError>(TableColumn {
199                    name: r.try_get("name").map_err(db_err)?,
200                    column_type: r.try_get("type").map_err(db_err)?,
201                    nullable: r.try_get::<i64, _>("notnull").map_err(db_err)? == 0,
202                    default_expr: r.try_get("dflt_value").map_err(db_err)?,
203                    ordinal: r.try_get::<i64, _>("cid").map_err(db_err)? as i32 + 1,
204                    comment: None,
205                    is_primary_key: r.try_get::<i64, _>("pk").map_err(db_err)? != 0,
206                })
207            })
208            .collect::<Result<_, _>>()?;
209
210        let foreign_keys_out = self.foreign_keys_out(name).await?;
211        let foreign_keys_in = self.foreign_keys_in(name).await?;
212
213        Ok(Some(TableSchema {
214            name: name.to_string(),
215            comment: None,
216            columns,
217            foreign_keys_out,
218            foreign_keys_in,
219        }))
220    }
221
222    async fn has_slow_query_stats(&self) -> Result<bool, DomainError> {
223        Ok(false)
224    }
225
226    async fn slow_queries(&self, _limit: i64) -> Result<Vec<SlowQuery>, DomainError> {
227        Ok(vec![])
228    }
229
230    async fn optimize(&self) -> Result<(), DomainError> {
231        sqlx::query("VACUUM;")
232            .execute(&self.pool)
233            .await
234            .map_err(db_err)?;
235        sqlx::query("ANALYZE;")
236            .execute(&self.pool)
237            .await
238            .map_err(db_err)?;
239        Ok(())
240    }
241}
242
243impl SqliteStats {
244    /// Outgoing foreign keys for `name`, grouped by `PRAGMA foreign_key_list`'s
245    /// `id` column (one constraint can span several columns).
246    async fn foreign_keys_out(&self, name: &str) -> Result<Vec<OutgoingForeignKey>, DomainError> {
247        let rows = sqlx::query(&format!("PRAGMA foreign_key_list({})", quote_ident(name)))
248            .fetch_all(&self.pool)
249            .await
250            .map_err(db_err)?;
251
252        // Preserve first-seen order of each constraint id.
253        let mut order: Vec<i64> = Vec::new();
254        let mut by_id: std::collections::HashMap<i64, OutgoingForeignKey> =
255            std::collections::HashMap::new();
256        for r in rows {
257            let id: i64 = r.try_get("id").map_err(db_err)?;
258            let from: String = r.try_get("from").map_err(db_err)?;
259            let to: String = r.try_get("to").map_err(db_err)?;
260            let target_table: String = r.try_get("table").map_err(db_err)?;
261            let entry = by_id.entry(id).or_insert_with(|| {
262                order.push(id);
263                OutgoingForeignKey {
264                    name: format!("fk_{id}"),
265                    columns: Vec::new(),
266                    target_table,
267                    target_columns: Vec::new(),
268                }
269            });
270            entry.columns.push(from);
271            entry.target_columns.push(to);
272        }
273        Ok(order
274            .into_iter()
275            .filter_map(|id| by_id.remove(&id))
276            .collect())
277    }
278
279    /// Incoming foreign keys: scan every other user table's
280    /// `PRAGMA foreign_key_list` for references back to `name`.
281    async fn foreign_keys_in(&self, name: &str) -> Result<Vec<IncomingForeignKey>, DomainError> {
282        let mut incoming = Vec::new();
283        for source in self.user_tables().await? {
284            if source == name {
285                continue;
286            }
287            let rows = sqlx::query(&format!(
288                "PRAGMA foreign_key_list({})",
289                quote_ident(&source)
290            ))
291            .fetch_all(&self.pool)
292            .await
293            .map_err(db_err)?;
294
295            let mut order: Vec<i64> = Vec::new();
296            let mut by_id: std::collections::HashMap<i64, IncomingForeignKey> =
297                std::collections::HashMap::new();
298            for r in rows {
299                let target_table: String = r.try_get("table").map_err(db_err)?;
300                if target_table != name {
301                    continue;
302                }
303                let id: i64 = r.try_get("id").map_err(db_err)?;
304                let from: String = r.try_get("from").map_err(db_err)?;
305                let to: String = r.try_get("to").map_err(db_err)?;
306                let entry = by_id.entry(id).or_insert_with(|| {
307                    order.push(id);
308                    IncomingForeignKey {
309                        name: format!("fk_{id}"),
310                        source_table: source.clone(),
311                        source_columns: Vec::new(),
312                        target_columns: Vec::new(),
313                    }
314                });
315                entry.source_columns.push(from);
316                entry.target_columns.push(to);
317            }
318            for id in order {
319                if let Some(fk) = by_id.remove(&id) {
320                    incoming.push(fk);
321                }
322            }
323        }
324        Ok(incoming)
325    }
326}
327
328#[cfg(test)]
329mod tests {
330    use super::*;
331    use crate::{connect, run_migrations};
332
333    async fn stats() -> SqliteStats {
334        let pool = connect("sqlite::memory:").await.unwrap();
335        run_migrations(&pool).await.unwrap();
336        SqliteStats::new(pool)
337    }
338
339    #[tokio::test]
340    async fn schema_overview_lists_known_tables() {
341        let stats = stats().await;
342        let schema = stats.schema_overview().await.unwrap();
343        let names: Vec<&str> = schema.tables.iter().map(|t| t.name.as_str()).collect();
344        assert!(names.contains(&"sbol_graphs"));
345        assert!(names.contains(&"sbol_triples"));
346        assert!(names.contains(&"sbol_objects"));
347        assert!(!names.contains(&"_sqlx_migrations"));
348        let graphs = schema
349            .tables
350            .iter()
351            .find(|t| t.name == "sbol_graphs")
352            .unwrap();
353        assert!(!graphs.columns.is_empty());
354    }
355
356    #[tokio::test]
357    async fn database_size_is_positive() {
358        let stats = stats().await;
359        let size = stats.database_size().await.unwrap();
360        assert_eq!(size.database, "main");
361        assert!(size.total_bytes > 0);
362    }
363
364    #[tokio::test]
365    async fn table_schema_returns_columns() {
366        let stats = stats().await;
367        let schema = stats.table_schema("sbol_triples").await.unwrap().unwrap();
368        assert_eq!(schema.name, "sbol_triples");
369        assert!(!schema.columns.is_empty());
370        let unknown = stats.table_schema("does_not_exist").await.unwrap();
371        assert!(unknown.is_none());
372    }
373
374    #[tokio::test]
375    async fn table_stats_and_indexes() {
376        let stats = stats().await;
377        let tables = stats.table_stats(100, 0).await.unwrap();
378        assert!(tables.iter().any(|t| t.name == "sbol_triples"));
379        // sbol_triples references sbol_graphs, so it has an outgoing FK.
380        let schema = stats.table_schema("sbol_triples").await.unwrap().unwrap();
381        assert!(!schema.foreign_keys_out.is_empty());
382        let _ = stats.index_stats(50).await.unwrap();
383    }
384
385    #[tokio::test]
386    async fn empty_observability_surfaces() {
387        let stats = stats().await;
388        assert!(stats.activity(10).await.unwrap().is_empty());
389        assert!(stats.blocking_locks().await.unwrap().is_empty());
390        assert!(!stats.has_slow_query_stats().await.unwrap());
391        assert!(stats.slow_queries(10).await.unwrap().is_empty());
392    }
393}
sbol_db_sqlite/stats.rs

sbol_db_sqlite/
stats.rs
