database_mcp_sqlite/
schema.rs

1//! `SQLite` table schema introspection.
2
3use std::collections::HashMap;
4
5use database_mcp_backend::error::AppError;
6use database_mcp_backend::identifier::validate_identifier;
7use serde_json::{Value, json};
8use sqlx::Row;
9use sqlx::sqlite::SqliteRow;
10
11use super::SqliteBackend;
12
13impl SqliteBackend {
14    /// Returns column definitions with foreign key relationships.
15    ///
16    /// # Errors
17    ///
18    /// Returns [`AppError`] if validation fails or the query errors.
19    pub async fn get_table_schema(&self, _database: &str, table: &str) -> Result<Value, AppError> {
20        validate_identifier(table)?;
21
22        // 1. Get basic schema
23        let rows: Vec<SqliteRow> = sqlx::query(&format!("PRAGMA table_info({})", Self::quote_identifier(table)))
24            .fetch_all(&self.pool)
25            .await
26            .map_err(|e| AppError::Query(e.to_string()))?;
27
28        if rows.is_empty() {
29            return Err(AppError::TableNotFound(table.to_string()));
30        }
31
32        let mut columns: HashMap<String, Value> = HashMap::new();
33        for row in &rows {
34            let col_name: String = row.try_get("name").unwrap_or_default();
35            let col_type: String = row.try_get("type").unwrap_or_default();
36            let notnull: i32 = row.try_get("notnull").unwrap_or(0);
37            let default: Option<String> = row.try_get("dflt_value").ok();
38            let pk: i32 = row.try_get("pk").unwrap_or(0);
39            columns.insert(
40                col_name,
41                json!({
42                    "type": col_type,
43                    "nullable": notnull == 0,
44                    "key": if pk > 0 { "PRI" } else { "" },
45                    "default": default,
46                    "extra": Value::Null,
47                    "foreign_key": Value::Null,
48                }),
49            );
50        }
51
52        // 2. Get FK info via PRAGMA
53        let fk_rows: Vec<SqliteRow> =
54            sqlx::query(&format!("PRAGMA foreign_key_list({})", Self::quote_identifier(table)))
55                .fetch_all(&self.pool)
56                .await
57                .map_err(|e| AppError::Query(e.to_string()))?;
58
59        for fk_row in &fk_rows {
60            let from_col: String = fk_row.try_get("from").unwrap_or_default();
61            if let Some(col_info) = columns.get_mut(&from_col)
62                && let Some(obj) = col_info.as_object_mut()
63            {
64                let ref_table: String = fk_row.try_get("table").unwrap_or_default();
65                let ref_col: String = fk_row.try_get("to").unwrap_or_default();
66                let on_update: String = fk_row.try_get("on_update").unwrap_or_default();
67                let on_delete: String = fk_row.try_get("on_delete").unwrap_or_default();
68                obj.insert(
69                    "foreign_key".to_string(),
70                    json!({
71                        "constraint_name": Value::Null,
72                        "referenced_table": ref_table,
73                        "referenced_column": ref_col,
74                        "on_update": on_update,
75                        "on_delete": on_delete,
76                    }),
77                );
78            }
79        }
80
81        Ok(json!({
82            "table_name": table,
83            "columns": columns,
84        }))
85    }
86}
database_mcp_sqlite/schema.rs

database_mcp_sqlite/
schema.rs
