mixtape_tools/sqlite/table/
describe.rs

1//! Describe table tool
2
3use crate::prelude::*;
4use crate::sqlite::error::SqliteToolError;
5use crate::sqlite::manager::with_connection;
6use crate::sqlite::types::{ColumnDefinition, TableInfo, Verbosity};
7
8/// Input for describing a table
9#[derive(Debug, Deserialize, JsonSchema)]
10pub struct DescribeTableInput {
11    /// Table name to describe
12    pub table: String,
13
14    /// Database file path. If not specified, uses the default database.
15    #[serde(default)]
16    pub db_path: Option<String>,
17
18    /// Level of detail to include (default: summary)
19    #[serde(default)]
20    pub verbosity: Verbosity,
21}
22
23/// Tool for getting detailed schema information about a table
24///
25/// Returns column definitions including names, types, constraints,
26/// and optionally row count and index information.
27pub struct DescribeTableTool;
28
29impl Tool for DescribeTableTool {
30    type Input = DescribeTableInput;
31
32    fn name(&self) -> &str {
33        "sqlite_describe_table"
34    }
35
36    fn description(&self) -> &str {
37        "Get detailed schema information for a table including column definitions, types, and constraints."
38    }
39
40    async fn execute(&self, input: Self::Input) -> Result<ToolResult, ToolError> {
41        let table_name = input.table.clone();
42        let verbosity = input.verbosity;
43
44        let info = with_connection(input.db_path, move |conn| {
45            // Check if table exists and get its type
46            let table_type: String = conn
47                .query_row(
48                    "SELECT type FROM sqlite_master WHERE name = ? AND type IN ('table', 'view')",
49                    [&table_name],
50                    |row| row.get(0),
51                )
52                .map_err(|_| SqliteToolError::TableNotFound(table_name.clone()))?;
53
54            // Get column info using PRAGMA
55            let mut stmt = conn.prepare(&format!("PRAGMA table_info('{}')", table_name))?;
56
57            let columns: Vec<ColumnDefinition> = stmt
58                .query_map([], |row| {
59                    let pk: i32 = row.get(5)?;
60                    let notnull: i32 = row.get(3)?;
61                    let default: Option<String> = row.get(4)?;
62
63                    Ok(ColumnDefinition {
64                        name: row.get(1)?,
65                        data_type: row.get(2)?,
66                        nullable: notnull == 0,
67                        primary_key: pk > 0,
68                        default,
69                    })
70                })?
71                .filter_map(|r| r.ok())
72                .collect();
73
74            // Get row count if detailed
75            let row_count = if verbosity == Verbosity::Detailed && table_type == "table" {
76                conn.query_row(
77                    &format!("SELECT COUNT(*) FROM \"{}\"", table_name),
78                    [],
79                    |row| row.get(0),
80                )
81                .ok()
82            } else {
83                None
84            };
85
86            Ok(TableInfo {
87                name: table_name,
88                table_type,
89                columns,
90                row_count,
91            })
92        })
93        .await?;
94
95        Ok(ToolResult::Json(serde_json::to_value(info)?))
96    }
97}
98
99#[cfg(test)]
100mod tests {
101    use super::*;
102    use crate::sqlite::test_utils::{unwrap_json, TestDatabase};
103
104    #[tokio::test]
105    async fn test_describe_table() {
106        let db = TestDatabase::with_schema(
107            "CREATE TABLE users (
108                id INTEGER PRIMARY KEY,
109                name TEXT NOT NULL,
110                email TEXT,
111                age INTEGER DEFAULT 0
112            );",
113        )
114        .await;
115
116        let result = DescribeTableTool
117            .execute(DescribeTableInput {
118                table: "users".to_string(),
119                db_path: Some(db.key()),
120                verbosity: Verbosity::Detailed,
121            })
122            .await
123            .unwrap();
124
125        let json = unwrap_json(result);
126        assert_eq!(json["name"].as_str().unwrap(), "users");
127        assert_eq!(json["columns"].as_array().unwrap().len(), 4);
128
129        // Check id column
130        let id_col = &json["columns"][0];
131        assert_eq!(id_col["name"].as_str().unwrap(), "id");
132        assert!(id_col["primary_key"].as_bool().unwrap());
133
134        // Check name column (NOT NULL)
135        let name_col = &json["columns"][1];
136        assert_eq!(name_col["name"].as_str().unwrap(), "name");
137        assert!(!name_col["nullable"].as_bool().unwrap());
138    }
139
140    #[test]
141    fn test_tool_metadata() {
142        let tool = DescribeTableTool;
143        assert_eq!(tool.name(), "sqlite_describe_table");
144        assert!(!tool.description().is_empty());
145    }
146
147    #[tokio::test]
148    async fn test_describe_view() {
149        let db = TestDatabase::with_schema(
150            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
151             CREATE VIEW active_users AS SELECT id, name FROM users WHERE id > 0;",
152        )
153        .await;
154
155        let result = DescribeTableTool
156            .execute(DescribeTableInput {
157                table: "active_users".to_string(),
158                db_path: Some(db.key()),
159                verbosity: Verbosity::Summary,
160            })
161            .await
162            .unwrap();
163
164        let json = unwrap_json(result);
165        assert_eq!(json["name"], "active_users");
166        assert_eq!(json["type"], "view");
167        assert_eq!(json["columns"].as_array().unwrap().len(), 2);
168        assert_eq!(json["columns"][0]["name"], "id");
169        assert_eq!(json["columns"][1]["name"], "name");
170    }
171
172    #[tokio::test]
173    async fn test_describe_table_no_primary_key() {
174        let db = TestDatabase::with_schema(
175            "CREATE TABLE logs (timestamp TEXT, message TEXT, level INTEGER);",
176        )
177        .await;
178
179        let result = DescribeTableTool
180            .execute(DescribeTableInput {
181                table: "logs".to_string(),
182                db_path: Some(db.key()),
183                verbosity: Verbosity::Summary,
184            })
185            .await
186            .unwrap();
187
188        let json = unwrap_json(result);
189        assert_eq!(json["columns"].as_array().unwrap().len(), 3);
190        // No column should be a primary key
191        for col in json["columns"].as_array().unwrap() {
192            assert!(!col["primary_key"].as_bool().unwrap());
193        }
194    }
195
196    #[tokio::test]
197    async fn test_describe_table_composite_primary_key() {
198        let db = TestDatabase::with_schema(
199            "CREATE TABLE order_items (
200                order_id INTEGER,
201                product_id INTEGER,
202                quantity INTEGER,
203                PRIMARY KEY (order_id, product_id)
204            );",
205        )
206        .await;
207
208        let result = DescribeTableTool
209            .execute(DescribeTableInput {
210                table: "order_items".to_string(),
211                db_path: Some(db.key()),
212                verbosity: Verbosity::Summary,
213            })
214            .await
215            .unwrap();
216
217        let json = unwrap_json(result);
218        // Both order_id and product_id should be marked as primary keys
219        let cols = json["columns"].as_array().unwrap();
220        let order_id = cols.iter().find(|c| c["name"] == "order_id").unwrap();
221        let product_id = cols.iter().find(|c| c["name"] == "product_id").unwrap();
222        let quantity = cols.iter().find(|c| c["name"] == "quantity").unwrap();
223
224        assert!(order_id["primary_key"].as_bool().unwrap());
225        assert!(product_id["primary_key"].as_bool().unwrap());
226        assert!(!quantity["primary_key"].as_bool().unwrap());
227    }
228
229    #[tokio::test]
230    async fn test_describe_table_verbosity_summary() {
231        let db = TestDatabase::with_schema(
232            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
233             INSERT INTO users VALUES (1, 'Alice');
234             INSERT INTO users VALUES (2, 'Bob');
235             INSERT INTO users VALUES (3, 'Charlie');",
236        )
237        .await;
238
239        let result = DescribeTableTool
240            .execute(DescribeTableInput {
241                table: "users".to_string(),
242                db_path: Some(db.key()),
243                verbosity: Verbosity::Summary,
244            })
245            .await
246            .unwrap();
247
248        let json = unwrap_json(result);
249        // Summary should NOT include row_count
250        assert!(json.get("row_count").is_none() || json["row_count"].is_null());
251    }
252
253    #[tokio::test]
254    async fn test_describe_table_verbosity_detailed() {
255        let db = TestDatabase::with_schema(
256            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
257             INSERT INTO users VALUES (1, 'Alice');
258             INSERT INTO users VALUES (2, 'Bob');
259             INSERT INTO users VALUES (3, 'Charlie');",
260        )
261        .await;
262
263        let result = DescribeTableTool
264            .execute(DescribeTableInput {
265                table: "users".to_string(),
266                db_path: Some(db.key()),
267                verbosity: Verbosity::Detailed,
268            })
269            .await
270            .unwrap();
271
272        let json = unwrap_json(result);
273        // Detailed should include row_count
274        assert_eq!(json["row_count"], 3);
275    }
276
277    #[tokio::test]
278    async fn test_describe_view_no_row_count_even_detailed() {
279        let db = TestDatabase::with_schema(
280            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
281             INSERT INTO users VALUES (1, 'Alice');
282             CREATE VIEW all_users AS SELECT * FROM users;",
283        )
284        .await;
285
286        let result = DescribeTableTool
287            .execute(DescribeTableInput {
288                table: "all_users".to_string(),
289                db_path: Some(db.key()),
290                verbosity: Verbosity::Detailed,
291            })
292            .await
293            .unwrap();
294
295        let json = unwrap_json(result);
296        // Views should NOT have row_count even in Detailed mode
297        assert!(json.get("row_count").is_none() || json["row_count"].is_null());
298    }
299
300    #[tokio::test]
301    async fn test_describe_table_not_found() {
302        let db = TestDatabase::new().await;
303
304        let result = DescribeTableTool
305            .execute(DescribeTableInput {
306                table: "nonexistent".to_string(),
307                db_path: Some(db.key()),
308                verbosity: Verbosity::Summary,
309            })
310            .await;
311
312        assert!(result.is_err());
313        let err = result.unwrap_err();
314        assert!(err.to_string().contains("not found") || err.to_string().contains("nonexistent"));
315    }
316
317    #[tokio::test]
318    async fn test_describe_table_with_default_values() {
319        let db = TestDatabase::with_schema(
320            "CREATE TABLE config (
321                key TEXT PRIMARY KEY,
322                value TEXT DEFAULT 'empty',
323                count INTEGER DEFAULT 0,
324                active INTEGER DEFAULT 1
325            );",
326        )
327        .await;
328
329        let result = DescribeTableTool
330            .execute(DescribeTableInput {
331                table: "config".to_string(),
332                db_path: Some(db.key()),
333                verbosity: Verbosity::Summary,
334            })
335            .await
336            .unwrap();
337
338        let json = unwrap_json(result);
339        let cols = json["columns"].as_array().unwrap();
340        let value_col = cols.iter().find(|c| c["name"] == "value").unwrap();
341        let count_col = cols.iter().find(|c| c["name"] == "count").unwrap();
342
343        assert_eq!(value_col["default"], "'empty'");
344        assert_eq!(count_col["default"], "0");
345    }
346
347    #[tokio::test]
348    async fn test_describe_table_nullable_columns() {
349        let db = TestDatabase::with_schema(
350            "CREATE TABLE users (
351                id INTEGER PRIMARY KEY,
352                name TEXT NOT NULL,
353                email TEXT,
354                phone TEXT NOT NULL
355            );",
356        )
357        .await;
358
359        let result = DescribeTableTool
360            .execute(DescribeTableInput {
361                table: "users".to_string(),
362                db_path: Some(db.key()),
363                verbosity: Verbosity::Summary,
364            })
365            .await
366            .unwrap();
367
368        let json = unwrap_json(result);
369        let cols = json["columns"].as_array().unwrap();
370        let name_col = cols.iter().find(|c| c["name"] == "name").unwrap();
371        let email_col = cols.iter().find(|c| c["name"] == "email").unwrap();
372        let phone_col = cols.iter().find(|c| c["name"] == "phone").unwrap();
373
374        assert!(!name_col["nullable"].as_bool().unwrap());
375        assert!(email_col["nullable"].as_bool().unwrap());
376        assert!(!phone_col["nullable"].as_bool().unwrap());
377    }
378
379    #[tokio::test]
380    async fn test_describe_table_data_types() {
381        let db = TestDatabase::with_schema(
382            "CREATE TABLE mixed_types (
383                id INTEGER,
384                name TEXT,
385                price REAL,
386                data BLOB,
387                anything
388            );",
389        )
390        .await;
391
392        let result = DescribeTableTool
393            .execute(DescribeTableInput {
394                table: "mixed_types".to_string(),
395                db_path: Some(db.key()),
396                verbosity: Verbosity::Summary,
397            })
398            .await
399            .unwrap();
400
401        let json = unwrap_json(result);
402        let cols = json["columns"].as_array().unwrap();
403        assert_eq!(
404            cols.iter().find(|c| c["name"] == "id").unwrap()["type"],
405            "INTEGER"
406        );
407        assert_eq!(
408            cols.iter().find(|c| c["name"] == "name").unwrap()["type"],
409            "TEXT"
410        );
411        assert_eq!(
412            cols.iter().find(|c| c["name"] == "price").unwrap()["type"],
413            "REAL"
414        );
415        assert_eq!(
416            cols.iter().find(|c| c["name"] == "data").unwrap()["type"],
417            "BLOB"
418        );
419        // Column with no type should have empty string
420        assert_eq!(
421            cols.iter().find(|c| c["name"] == "anything").unwrap()["type"],
422            ""
423        );
424    }
425}
mixtape_tools/sqlite/table/describe.rs

mixtape_tools/sqlite/table/
describe.rs
