mixtape_tools/sqlite/maintenance/
export_schema.rs

1//! Export schema tool
2
3use crate::prelude::*;
4use crate::sqlite::manager::with_connection;
5use crate::sqlite::types::{ColumnDefinition, SchemaFormat, TableInfo};
6
7/// Input for exporting schema
8#[derive(Debug, Deserialize, JsonSchema)]
9pub struct ExportSchemaInput {
10    /// Database file path. If not specified, uses the default database.
11    #[serde(default)]
12    pub db_path: Option<String>,
13
14    /// Export format (sql or json)
15    #[serde(default)]
16    pub format: SchemaFormat,
17
18    /// Specific tables to export. If empty, exports all tables.
19    #[serde(default)]
20    pub tables: Vec<String>,
21}
22
23/// Tool for exporting database schema (SAFE)
24///
25/// Exports the database schema in SQL or JSON format.
26/// Can export all tables or specific tables.
27pub struct ExportSchemaTool;
28
29impl Tool for ExportSchemaTool {
30    type Input = ExportSchemaInput;
31
32    fn name(&self) -> &str {
33        "sqlite_export_schema"
34    }
35
36    fn description(&self) -> &str {
37        "Export the database schema in SQL or JSON format. Can export all tables or specific tables."
38    }
39
40    async fn execute(&self, input: Self::Input) -> Result<ToolResult, ToolError> {
41        let format = input.format;
42        let filter_tables = input.tables;
43
44        let result = with_connection(input.db_path, move |conn| {
45            // Get all tables/views
46            let mut stmt = conn.prepare(
47                "SELECT name, type, sql FROM sqlite_master
48                 WHERE type IN ('table', 'view')
49                 AND name NOT LIKE 'sqlite_%'
50                 ORDER BY type, name",
51            )?;
52
53            let objects: Vec<(String, String, Option<String>)> = stmt
54                .query_map([], |row| {
55                    Ok((row.get(0)?, row.get(1)?, row.get(2)?))
56                })?
57                .filter_map(|r| r.ok())
58                .filter(|(name, _, _)| {
59                    filter_tables.is_empty() || filter_tables.contains(name)
60                })
61                .collect();
62
63            match format {
64                SchemaFormat::Sql => {
65                    // Export as SQL statements
66                    let mut sql = String::new();
67                    for (name, obj_type, create_sql) in &objects {
68                        if let Some(s) = create_sql {
69                            sql.push_str(&format!("-- {} '{}'\n", obj_type, name));
70                            sql.push_str(s);
71                            sql.push_str(";\n\n");
72                        }
73                    }
74
75                    // Also export indexes
76                    let mut idx_stmt = conn.prepare(
77                        "SELECT name, sql FROM sqlite_master WHERE type = 'index' AND sql IS NOT NULL"
78                    )?;
79                    let indexes: Vec<(String, String)> = idx_stmt
80                        .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
81                        .filter_map(|r| r.ok())
82                        .collect();
83
84                    if !indexes.is_empty() {
85                        sql.push_str("-- Indexes\n");
86                        for (name, create_sql) in indexes {
87                            sql.push_str(&format!("-- index '{}'\n", name));
88                            sql.push_str(&create_sql);
89                            sql.push_str(";\n\n");
90                        }
91                    }
92
93                    Ok(serde_json::json!({
94                        "format": "sql",
95                        "schema": sql,
96                        "table_count": objects.len()
97                    }))
98                }
99                SchemaFormat::Json => {
100                    // Export as structured JSON
101                    let mut tables = Vec::new();
102
103                    for (name, table_type, _) in &objects {
104                        // Get column info
105                        let mut col_stmt =
106                            conn.prepare(&format!("PRAGMA table_info('{}')", name))?;
107
108                        let columns: Vec<ColumnDefinition> = col_stmt
109                            .query_map([], |row| {
110                                let pk: i32 = row.get(5)?;
111                                let notnull: i32 = row.get(3)?;
112                                Ok(ColumnDefinition {
113                                    name: row.get(1)?,
114                                    data_type: row.get(2)?,
115                                    nullable: notnull == 0,
116                                    primary_key: pk > 0,
117                                    default: row.get(4)?,
118                                })
119                            })?
120                            .filter_map(|r| r.ok())
121                            .collect();
122
123                        tables.push(TableInfo {
124                            name: name.clone(),
125                            table_type: table_type.clone(),
126                            columns,
127                            row_count: None,
128                        });
129                    }
130
131                    Ok(serde_json::json!({
132                        "format": "json",
133                        "tables": tables,
134                        "table_count": tables.len()
135                    }))
136                }
137            }
138        })
139        .await?;
140
141        Ok(ToolResult::Json(result))
142    }
143}
144
145#[cfg(test)]
146mod tests {
147    use super::*;
148    use crate::sqlite::test_utils::{unwrap_json, TestDatabase};
149
150    #[tokio::test]
151    async fn test_export_schema_sql() {
152        let db = TestDatabase::with_schema(
153            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
154             CREATE TABLE posts (id INTEGER, user_id INTEGER);",
155        )
156        .await;
157
158        let tool = ExportSchemaTool;
159        let input = ExportSchemaInput {
160            db_path: Some(db.key()),
161            format: SchemaFormat::Sql,
162            tables: vec![],
163        };
164
165        let result = tool.execute(input).await.unwrap();
166        let json = unwrap_json(result);
167
168        assert_eq!(json["format"].as_str().unwrap(), "sql");
169        assert_eq!(json["table_count"].as_i64().unwrap(), 2);
170        let schema = json["schema"].as_str().unwrap();
171        assert!(schema.contains("CREATE TABLE"));
172    }
173
174    #[tokio::test]
175    async fn test_export_schema_json() {
176        let db =
177            TestDatabase::with_schema("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);")
178                .await;
179
180        let tool = ExportSchemaTool;
181        let input = ExportSchemaInput {
182            db_path: Some(db.key()),
183            format: SchemaFormat::Json,
184            tables: vec![],
185        };
186
187        let result = tool.execute(input).await.unwrap();
188        let json = unwrap_json(result);
189
190        assert_eq!(json["format"].as_str().unwrap(), "json");
191        let tables = json["tables"].as_array().unwrap();
192        assert_eq!(tables.len(), 1);
193        assert_eq!(tables[0]["name"].as_str().unwrap(), "users");
194    }
195
196    #[test]
197    fn test_tool_metadata() {
198        let tool = ExportSchemaTool;
199        assert_eq!(tool.name(), "sqlite_export_schema");
200        assert!(!tool.description().is_empty());
201    }
202}
mixtape_tools/sqlite/maintenance/export_schema.rs

mixtape_tools/sqlite/maintenance/
export_schema.rs
