rws_server/model/
db_schema.rs

1//! The database's schema as data (18.30.0): [`DbPool::schema`] reads every
2//! table of the connected schema — columns (type, nullability, default),
3//! primary key, indexes and foreign keys — from the backend's catalog
4//! (`sqlite_master` and the `pragma_*` functions on SQLite,
5//! `information_schema` and `pg_catalog` on PostgreSQL,
6//! `information_schema` on MySQL), and [`DbSchema::to_json`] writes it for
7//! tools: `rws db schema --json` and the dev MCP server's `db_schema`.
8//!
9//! "The connected schema" is SQLite's main database, PostgreSQL's
10//! `current_schema()` (the first of the `search_path`), MySQL's
11//! `DATABASE()`. Views are left out; rws's `_schema_migrations` table is
12//! listed (it is part of the schema).
13//!
14//! ```rust,no_run
15//! # extern crate rws_server as rust_web_server;
16//! # #[cfg(feature = "model-sqlite")]
17//! # async fn example() -> Result<(), rust_web_server::model::DbError> {
18//! use rust_web_server::model::DbPool;
19//!
20//! let pool = DbPool::memory().await?;
21//! pool.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)", &[]).await?;
22//! let schema = pool.schema().await?;
23//! let users = schema.table("users").unwrap();
24//! assert_eq!(vec!["id".to_string()], users.primary_key);
25//! assert!(users.indexes.iter().any(|i| i.unique && i.columns == ["email"]));
26//! println!("{}", schema.to_json());
27//! # Ok(())
28//! # }
29//! ```
30
31use super::backend::Backend;
32use super::pool::DbPool;
33use super::{DbError, ModelRow, Value};
34
35/// Every table of the connected schema, by name.
36#[derive(Debug, Clone, PartialEq)]
37#[non_exhaustive]
38pub struct DbSchema {
39    /// The backend read.
40    pub backend: Backend,
41    /// The tables, sorted by name.
42    pub tables: Vec<TableSchema>,
43}
44
45/// One table.
46#[derive(Debug, Clone, PartialEq, Eq)]
47#[non_exhaustive]
48pub struct TableSchema {
49    pub name: String,
50    /// In the table's order.
51    pub columns: Vec<ColumnSchema>,
52    /// The primary key's columns, in key order; empty without one.
53    pub primary_key: Vec<String>,
54    /// Indexes, by name — the primary key's and those made for unique
55    /// constraints included where the backend lists them (SQLite has no
56    /// index for an `INTEGER PRIMARY KEY`).
57    pub indexes: Vec<IndexSchema>,
58    /// Foreign keys, by name (SQLite: in declaration order, unnamed).
59    pub foreign_keys: Vec<ForeignKeySchema>,
60}
61
62/// One column.
63#[derive(Debug, Clone, PartialEq, Eq)]
64#[non_exhaustive]
65pub struct ColumnSchema {
66    pub name: String,
67    /// The type as the backend spells it (`INTEGER`, `bigint`,
68    /// `varchar(255)`, …).
69    pub data_type: String,
70    pub nullable: bool,
71    /// The default's SQL text, `None` without one.
72    pub default: Option<String>,
73    /// Part of the primary key.
74    pub primary_key: bool,
75}
76
77/// One index.
78#[derive(Debug, Clone, PartialEq, Eq)]
79#[non_exhaustive]
80pub struct IndexSchema {
81    pub name: String,
82    /// In index order; expression parts are left out.
83    pub columns: Vec<String>,
84    pub unique: bool,
85    /// The primary key's index.
86    pub primary: bool,
87}
88
89/// One foreign key.
90#[derive(Debug, Clone, PartialEq, Eq)]
91#[non_exhaustive]
92pub struct ForeignKeySchema {
93    /// The constraint's name (`None` on SQLite, whose constraints have
94    /// none).
95    pub name: Option<String>,
96    pub columns: Vec<String>,
97    pub references_table: String,
98    /// The referenced columns, matching `columns` one to one.
99    pub references_columns: Vec<String>,
100    /// `NO ACTION`, `RESTRICT`, `CASCADE`, `SET NULL` or `SET DEFAULT`.
101    pub on_update: String,
102    pub on_delete: String,
103}
104
105impl DbSchema {
106    /// The table named `name` (ignoring ASCII case).
107    pub fn table(&self, name: &str) -> Option<&TableSchema> {
108        self.tables.iter().find(|t| t.name.eq_ignore_ascii_case(name))
109    }
110
111    /// The schema as JSON: `{"backend":…,"tables":[{"name":…,"columns":[{"name":…,"type":…,
112    /// "nullable":…,"default":…,"primary_key":…}],"primary_key":[…],"indexes":[{"name":…,
113    /// "columns":[…],"unique":…,"primary":…}],"foreign_keys":[{"name":…,"columns":[…],
114    /// "references":{"table":…,"columns":[…]},"on_update":…,"on_delete":…}]}]}`.
115    pub fn to_json(&self) -> String {
116        let tables: Vec<String> = self.tables.iter().map(TableSchema::to_json).collect();
117        format!(r#"{{"backend":{},"tables":[{}]}}"#, quoted(self.backend.name()), tables.join(","))
118    }
119}
120
121impl TableSchema {
122    /// The column named `name` (ignoring ASCII case).
123    pub fn column(&self, name: &str) -> Option<&ColumnSchema> {
124        self.columns.iter().find(|c| c.name.eq_ignore_ascii_case(name))
125    }
126
127    fn to_json(&self) -> String {
128        let columns: Vec<String> = self
129            .columns
130            .iter()
131            .map(|c| {
132                format!(
133                    r#"{{"name":{},"type":{},"nullable":{},"default":{},"primary_key":{}}}"#,
134                    quoted(&c.name),
135                    quoted(&c.data_type),
136                    c.nullable,
137                    c.default.as_deref().map(quoted).unwrap_or_else(|| "null".to_string()),
138                    c.primary_key
139                )
140            })
141            .collect();
142        let indexes: Vec<String> = self
143            .indexes
144            .iter()
145            .map(|i| format!(r#"{{"name":{},"columns":{},"unique":{},"primary":{}}}"#, quoted(&i.name), list(&i.columns), i.unique, i.primary))
146            .collect();
147        let foreign_keys: Vec<String> = self
148            .foreign_keys
149            .iter()
150            .map(|f| {
151                format!(
152                    r#"{{"name":{},"columns":{},"references":{{"table":{},"columns":{}}},"on_update":{},"on_delete":{}}}"#,
153                    f.name.as_deref().map(quoted).unwrap_or_else(|| "null".to_string()),
154                    list(&f.columns),
155                    quoted(&f.references_table),
156                    list(&f.references_columns),
157                    quoted(&f.on_update),
158                    quoted(&f.on_delete)
159                )
160            })
161            .collect();
162        format!(
163            r#"{{"name":{},"columns":[{}],"primary_key":{},"indexes":[{}],"foreign_keys":[{}]}}"#,
164            quoted(&self.name),
165            columns.join(","),
166            list(&self.primary_key),
167            indexes.join(","),
168            foreign_keys.join(",")
169        )
170    }
171}
172
173fn quoted(s: &str) -> String {
174    format!("\"{}\"", crate::core::json::escape(s))
175}
176
177fn list(items: &[String]) -> String {
178    format!("[{}]", items.iter().map(|s| quoted(s)).collect::<Vec<_>>().join(","))
179}
180
181/// A column's value as text (`None` for NULL), whatever type the driver
182/// decoded it as.
183fn text(row: &ModelRow, column: &str) -> Option<String> {
184    let value = row.columns.iter().find(|(name, _)| name.eq_ignore_ascii_case(column)).map(|(_, v)| v.clone())?;
185    match value {
186        Value::Null => None,
187        Value::Bool(b) => Some(b.to_string()),
188        Value::Int(n) => Some(n.to_string()),
189        Value::Float(f) => Some(f.to_string()),
190        Value::Text(s) => Some(s),
191        Value::Bytes(b) => Some(String::from_utf8_lossy(&b).into_owned()),
192    }
193}
194
195fn truthy(row: &ModelRow, column: &str) -> bool {
196    matches!(text(row, column).as_deref(), Some("1" | "true" | "t" | "YES" | "yes"))
197}
198
199fn table(name: String) -> TableSchema {
200    TableSchema { name, columns: Vec::new(), primary_key: Vec::new(), indexes: Vec::new(), foreign_keys: Vec::new() }
201}
202
203/// Add column `column` of index `index` to `indexes` (a new index when it
204/// differs from the last one).
205fn push_index(indexes: &mut Vec<IndexSchema>, index: String, unique: bool, primary: bool, column: Option<String>) {
206    if indexes.last().map(|i| i.name != index).unwrap_or(true) {
207        indexes.push(IndexSchema { name: index, columns: Vec::new(), unique, primary });
208    }
209    if let (Some(last), Some(column)) = (indexes.last_mut(), column) {
210        last.columns.push(column);
211    }
212}
213
214#[allow(clippy::too_many_arguments)]
215fn push_foreign_key(
216    keys: &mut Vec<ForeignKeySchema>,
217    name: Option<String>,
218    group: &str,
219    column: String,
220    references_table: String,
221    references_column: Option<String>,
222    on_update: String,
223    on_delete: String,
224    groups: &mut Vec<String>,
225) {
226    if groups.last().map(|g| g != group).unwrap_or(true) {
227        groups.push(group.to_string());
228        keys.push(ForeignKeySchema { name, columns: Vec::new(), references_table, references_columns: Vec::new(), on_update, on_delete });
229    }
230    if let Some(last) = keys.last_mut() {
231        last.columns.push(column);
232        if let Some(c) = references_column {
233            last.references_columns.push(c);
234        }
235    }
236}
237
238impl DbPool {
239    /// The connected schema's tables (18.30.0), read from the primary: see
240    /// [`db_schema`](super::db_schema).
241    pub async fn schema(&self) -> Result<DbSchema, DbError> {
242        let pool = self.primary();
243        let mut tables = match pool.backend() {
244            #[cfg(feature = "model-sqlite")]
245            Backend::Sqlite => sqlite(pool).await?,
246            #[cfg(feature = "model-postgres")]
247            Backend::Postgres => postgres(pool).await?,
248            #[cfg(feature = "model-mysql")]
249            Backend::MySql => mysql(pool).await?,
250        };
251        tables.sort_by(|a, b| a.name.cmp(&b.name));
252        for t in &mut tables {
253            let primary_key = t.primary_key.clone();
254            for c in &mut t.columns {
255                c.primary_key = primary_key.iter().any(|k| k.eq_ignore_ascii_case(&c.name));
256            }
257        }
258        // A foreign key naming no columns references the primary key.
259        let keys: Vec<(String, Vec<String>)> = tables.iter().map(|t| (t.name.clone(), t.primary_key.clone())).collect();
260        for t in &mut tables {
261            for fk in &mut t.foreign_keys {
262                if fk.references_columns.is_empty() {
263                    if let Some((_, pk)) = keys.iter().find(|(name, _)| name.eq_ignore_ascii_case(&fk.references_table)) {
264                        fk.references_columns = pk.clone();
265                    }
266                }
267            }
268        }
269        Ok(DbSchema { backend: pool.backend(), tables })
270    }
271}
272
273#[cfg(feature = "model-sqlite")]
274async fn sqlite(pool: &DbPool) -> Result<Vec<TableSchema>, DbError> {
275    let names = pool
276        .query_rows("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name", &[])
277        .await?;
278    let mut tables = Vec::new();
279    for row in names {
280        let Some(name) = text(&row, "name") else { continue };
281        let arg = [Value::Text(name.clone())];
282        let mut t = table(name);
283        let mut key: Vec<(i64, String)> = Vec::new();
284        for row in pool
285            .query_rows(r#"SELECT name, type, "notnull" AS not_null, dflt_value, pk FROM pragma_table_info(?) ORDER BY cid"#, &arg)
286            .await?
287        {
288            let column = text(&row, "name").unwrap_or_default();
289            if let Some(position) = text(&row, "pk").and_then(|p| p.parse::<i64>().ok()).filter(|p| *p > 0) {
290                key.push((position, column.clone()));
291            }
292            t.columns.push(ColumnSchema {
293                name: column,
294                data_type: text(&row, "type").unwrap_or_default(),
295                nullable: !truthy(&row, "not_null"),
296                default: text(&row, "dflt_value"),
297                primary_key: false,
298            });
299        }
300        key.sort();
301        t.primary_key = key.into_iter().map(|(_, c)| c).collect();
302        for row in pool.query_rows(r#"SELECT name, "unique" AS is_unique, origin FROM pragma_index_list(?) ORDER BY name"#, &arg).await? {
303            let Some(index) = text(&row, "name") else { continue };
304            let (unique, primary) = (truthy(&row, "is_unique"), text(&row, "origin").as_deref() == Some("pk"));
305            let columns = pool.query_rows("SELECT name FROM pragma_index_info(?) ORDER BY seqno", &[Value::Text(index.clone())]).await?;
306            push_index(&mut t.indexes, index.clone(), unique, primary, None);
307            for c in columns {
308                push_index(&mut t.indexes, index.clone(), unique, primary, text(&c, "name"));
309            }
310        }
311        let mut groups = Vec::new();
312        for row in pool
313            .query_rows(
314                r#"SELECT id, "table" AS ref_table, "from" AS from_column, "to" AS to_column, on_update, on_delete FROM pragma_foreign_key_list(?) ORDER BY id, seq"#,
315                &arg,
316            )
317            .await?
318        {
319            push_foreign_key(
320                &mut t.foreign_keys,
321                None,
322                &text(&row, "id").unwrap_or_default(),
323                text(&row, "from_column").unwrap_or_default(),
324                text(&row, "ref_table").unwrap_or_default(),
325                text(&row, "to_column"),
326                text(&row, "on_update").unwrap_or_else(|| "NO ACTION".to_string()),
327                text(&row, "on_delete").unwrap_or_else(|| "NO ACTION".to_string()),
328                &mut groups,
329            );
330        }
331        tables.push(t);
332    }
333    Ok(tables)
334}
335
336/// The tables `table_sql` lists, filled with the columns, indexes and
337/// foreign keys the other queries answer (each ordered by table, then the
338/// object, then position).
339#[cfg(any(feature = "model-postgres", feature = "model-mysql"))]
340async fn catalog(pool: &DbPool, table_sql: &str, column_sql: &str, index_sql: &str, key_sql: &str) -> Result<Vec<TableSchema>, DbError> {
341    let mut tables: Vec<TableSchema> = pool.query_rows(table_sql, &[]).await?.iter().filter_map(|r| text(r, "table_name")).map(table).collect();
342    let find = |tables: &[TableSchema], name: &str| tables.iter().position(|t| t.name == name);
343    for row in pool.query_rows(column_sql, &[]).await? {
344        let Some(i) = text(&row, "table_name").and_then(|n| find(&tables, &n)) else { continue };
345        tables[i].columns.push(ColumnSchema {
346            name: text(&row, "column_name").unwrap_or_default(),
347            data_type: text(&row, "data_type").unwrap_or_default(),
348            nullable: truthy(&row, "is_nullable"),
349            default: text(&row, "column_default"),
350            primary_key: false,
351        });
352    }
353    for row in pool.query_rows(index_sql, &[]).await? {
354        let Some(i) = text(&row, "table_name").and_then(|n| find(&tables, &n)) else { continue };
355        let (index, primary) = (text(&row, "index_name").unwrap_or_default(), truthy(&row, "is_primary"));
356        let column = text(&row, "column_name");
357        if primary {
358            tables[i].primary_key.extend(column.clone());
359        }
360        push_index(&mut tables[i].indexes, index, truthy(&row, "is_unique"), primary, column);
361    }
362    let mut groups: Vec<Vec<String>> = vec![Vec::new(); tables.len()];
363    for row in pool.query_rows(key_sql, &[]).await? {
364        let Some(i) = text(&row, "table_name").and_then(|n| find(&tables, &n)) else { continue };
365        let name = text(&row, "constraint_name").unwrap_or_default();
366        push_foreign_key(
367            &mut tables[i].foreign_keys,
368            Some(name.clone()),
369            &name,
370            text(&row, "column_name").unwrap_or_default(),
371            text(&row, "ref_table").unwrap_or_default(),
372            text(&row, "ref_column"),
373            text(&row, "on_update").unwrap_or_else(|| "NO ACTION".to_string()),
374            text(&row, "on_delete").unwrap_or_else(|| "NO ACTION".to_string()),
375            &mut groups[i],
376        );
377    }
378    Ok(tables)
379}
380
381#[cfg(feature = "model-postgres")]
382async fn postgres(pool: &DbPool) -> Result<Vec<TableSchema>, DbError> {
383    catalog(
384        pool,
385        "SELECT table_name::text AS table_name FROM information_schema.tables \
386         WHERE table_schema = current_schema() AND table_type = 'BASE TABLE' ORDER BY table_name",
387        "SELECT table_name::text AS table_name, column_name::text AS column_name, data_type::text AS data_type, \
388         is_nullable::text AS is_nullable, column_default::text AS column_default \
389         FROM information_schema.columns WHERE table_schema = current_schema() ORDER BY table_name, ordinal_position",
390        "SELECT t.relname::text AS table_name, i.relname::text AS index_name, ix.indisunique AS is_unique, \
391         ix.indisprimary AS is_primary, a.attname::text AS column_name \
392         FROM pg_index ix \
393         JOIN pg_class t ON t.oid = ix.indrelid \
394         JOIN pg_class i ON i.oid = ix.indexrelid \
395         JOIN pg_namespace ns ON ns.oid = t.relnamespace \
396         CROSS JOIN LATERAL unnest(ix.indkey::int2[]) WITH ORDINALITY AS k(attnum, ord) \
397         JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum \
398         WHERE ns.nspname = current_schema() \
399         ORDER BY t.relname, i.relname, k.ord",
400        "SELECT t.relname::text AS table_name, c.conname::text AS constraint_name, a.attname::text AS column_name, \
401         rt.relname::text AS ref_table, ra.attname::text AS ref_column, \
402         CASE c.confupdtype WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' ELSE 'NO ACTION' END AS on_update, \
403         CASE c.confdeltype WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' ELSE 'NO ACTION' END AS on_delete \
404         FROM pg_constraint c \
405         JOIN pg_class t ON t.oid = c.conrelid \
406         JOIN pg_class rt ON rt.oid = c.confrelid \
407         JOIN pg_namespace ns ON ns.oid = t.relnamespace \
408         CROSS JOIN LATERAL unnest(c.conkey, c.confkey) WITH ORDINALITY AS k(attnum, refnum, ord) \
409         JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.attnum \
410         JOIN pg_attribute ra ON ra.attrelid = c.confrelid AND ra.attnum = k.refnum \
411         WHERE c.contype = 'f' AND ns.nspname = current_schema() \
412         ORDER BY t.relname, c.conname, k.ord",
413    )
414    .await
415}
416
417#[cfg(feature = "model-mysql")]
418async fn mysql(pool: &DbPool) -> Result<Vec<TableSchema>, DbError> {
419    catalog(
420        pool,
421        "SELECT CAST(TABLE_NAME AS CHAR) AS table_name FROM information_schema.TABLES \
422         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME",
423        "SELECT CAST(TABLE_NAME AS CHAR) AS table_name, CAST(COLUMN_NAME AS CHAR) AS column_name, \
424         CAST(COLUMN_TYPE AS CHAR) AS data_type, CAST(IS_NULLABLE AS CHAR) AS is_nullable, \
425         CAST(COLUMN_DEFAULT AS CHAR) AS column_default \
426         FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME, ORDINAL_POSITION",
427        "SELECT CAST(TABLE_NAME AS CHAR) AS table_name, CAST(INDEX_NAME AS CHAR) AS index_name, \
428         CAST(NON_UNIQUE = 0 AS SIGNED) AS is_unique, CAST(INDEX_NAME = 'PRIMARY' AS SIGNED) AS is_primary, \
429         CAST(COLUMN_NAME AS CHAR) AS column_name \
430         FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() \
431         ORDER BY TABLE_NAME, INDEX_NAME = 'PRIMARY' DESC, INDEX_NAME, SEQ_IN_INDEX",
432        "SELECT CAST(k.TABLE_NAME AS CHAR) AS table_name, CAST(k.CONSTRAINT_NAME AS CHAR) AS constraint_name, \
433         CAST(k.COLUMN_NAME AS CHAR) AS column_name, CAST(k.REFERENCED_TABLE_NAME AS CHAR) AS ref_table, \
434         CAST(k.REFERENCED_COLUMN_NAME AS CHAR) AS ref_column, CAST(r.UPDATE_RULE AS CHAR) AS on_update, \
435         CAST(r.DELETE_RULE AS CHAR) AS on_delete \
436         FROM information_schema.KEY_COLUMN_USAGE k \
437         JOIN information_schema.REFERENTIAL_CONSTRAINTS r \
438           ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND r.TABLE_NAME = k.TABLE_NAME \
439         WHERE k.TABLE_SCHEMA = DATABASE() AND k.REFERENCED_TABLE_NAME IS NOT NULL \
440         ORDER BY k.TABLE_NAME, k.CONSTRAINT_NAME, k.ORDINAL_POSITION",
441    )
442    .await
443}
rws_server/model/db_schema.rs

rws_server/model/
db_schema.rs
