database-mcp-sqlite 0.6.0

//! `SQLite` table schema introspection.

use std::collections::HashMap;

use database_mcp_server::AppError;
use database_mcp_server::types::TableSchemaResponse;
use database_mcp_sql::identifier::validate_identifier;
use database_mcp_sql::timeout::execute_with_timeout;
use serde_json::{Value, json};
use sqlx::Row;
use sqlx::sqlite::SqliteRow;

use super::SqliteAdapter;
use super::types::GetTableSchemaRequest;

impl SqliteAdapter {
    /// Returns column definitions with foreign key relationships.
    ///
    /// # Errors
    ///
    /// Returns [`AppError`] if validation fails or the query errors.
    pub(crate) async fn get_table_schema(
        &self,
        request: &GetTableSchemaRequest,
    ) -> Result<TableSchemaResponse, AppError> {
        let table = &request.table_name;
        validate_identifier(table)?;

        // 1. Get basic schema
        let pragma_sql = format!("PRAGMA table_info({})", Self::quote_identifier(table));
        let rows: Vec<SqliteRow> = execute_with_timeout(
            self.config.query_timeout,
            &pragma_sql,
            sqlx::query(&pragma_sql).fetch_all(&self.pool),
        )
        .await?;

        if rows.is_empty() {
            return Err(AppError::TableNotFound(table.clone()));
        }

        let mut columns: HashMap<String, Value> = HashMap::new();
        for row in &rows {
            let col_name: String = row.try_get("name").unwrap_or_default();
            let col_type: String = row.try_get("type").unwrap_or_default();
            let notnull: i32 = row.try_get("notnull").unwrap_or(0);
            let default: Option<String> = row.try_get("dflt_value").ok();
            let pk: i32 = row.try_get("pk").unwrap_or(0);
            columns.insert(
                col_name,
                json!({
                    "type": col_type,
                    "nullable": notnull == 0,
                    "key": if pk > 0 { "PRI" } else { "" },
                    "default": default,
                    "extra": Value::Null,
                    "foreign_key": Value::Null,
                }),
            );
        }

        // 2. Get FK info via PRAGMA
        let fk_pragma_sql = format!("PRAGMA foreign_key_list({})", Self::quote_identifier(table));
        let fk_rows: Vec<SqliteRow> = execute_with_timeout(
            self.config.query_timeout,
            &fk_pragma_sql,
            sqlx::query(&fk_pragma_sql).fetch_all(&self.pool),
        )
        .await?;

        for fk_row in &fk_rows {
            let from_col: String = fk_row.try_get("from").unwrap_or_default();
            if let Some(col_info) = columns.get_mut(&from_col)
                && let Some(obj) = col_info.as_object_mut()
            {
                let ref_table: String = fk_row.try_get("table").unwrap_or_default();
                let ref_col: String = fk_row.try_get("to").unwrap_or_default();
                let on_update: String = fk_row.try_get("on_update").unwrap_or_default();
                let on_delete: String = fk_row.try_get("on_delete").unwrap_or_default();
                obj.insert(
                    "foreign_key".to_string(),
                    json!({
                        "constraint_name": Value::Null,
                        "referenced_table": ref_table,
                        "referenced_column": ref_col,
                        "on_update": on_update,
                        "on_delete": on_delete,
                    }),
                );
            }
        }

        Ok(TableSchemaResponse {
            table_name: table.clone(),
            columns: json!(columns),
        })
    }
}