mixtape_tools/sqlite/
types.rs

1//! Shared types for SQLite tools
2
3use schemars::JsonSchema;
4use serde::{Deserialize, Serialize};
5
6/// Column definition for table creation and schema introspection
7#[derive(Debug, Clone, Serialize, Deserialize, JsonSchema)]
8pub struct ColumnDefinition {
9    /// Column name
10    pub name: String,
11
12    /// SQLite data type (TEXT, INTEGER, REAL, BLOB, NULL)
13    #[serde(rename = "type")]
14    pub data_type: String,
15
16    /// Whether the column allows NULL values
17    #[serde(default)]
18    pub nullable: bool,
19
20    /// Whether this column is (part of) the primary key
21    #[serde(default)]
22    pub primary_key: bool,
23
24    /// Default value expression (if any)
25    #[serde(default)]
26    pub default: Option<String>,
27}
28
29/// Information about a table
30#[derive(Debug, Clone, Serialize, Deserialize, JsonSchema)]
31pub struct TableInfo {
32    /// Table name
33    pub name: String,
34
35    /// Table type (table, view, etc.)
36    #[serde(rename = "type")]
37    pub table_type: String,
38
39    /// Column definitions
40    pub columns: Vec<ColumnDefinition>,
41
42    /// Number of rows (approximate for large tables)
43    #[serde(skip_serializing_if = "Option::is_none")]
44    pub row_count: Option<i64>,
45}
46
47/// Result of a query execution
48#[derive(Debug, Clone, Serialize, Deserialize, JsonSchema)]
49pub struct QueryResult {
50    /// Column names
51    pub columns: Vec<String>,
52
53    /// Row data as arrays of JSON values
54    pub rows: Vec<Vec<serde_json::Value>>,
55
56    /// Number of rows returned
57    pub row_count: usize,
58
59    /// Number of rows affected (for write operations)
60    #[serde(skip_serializing_if = "Option::is_none")]
61    pub rows_affected: Option<usize>,
62}
63
64/// Database metadata and statistics
65#[derive(Debug, Clone, Serialize, Deserialize, JsonSchema)]
66pub struct DatabaseInfo {
67    /// Database file path
68    pub path: String,
69
70    /// File size in bytes
71    pub size_bytes: u64,
72
73    /// Number of tables
74    pub table_count: usize,
75
76    /// Number of indexes
77    pub index_count: usize,
78
79    /// Number of views
80    pub view_count: usize,
81
82    /// Number of triggers
83    pub trigger_count: usize,
84
85    /// SQLite version
86    pub sqlite_version: String,
87
88    /// Page size in bytes
89    pub page_size: i64,
90
91    /// Page count
92    pub page_count: i64,
93
94    /// Whether the database is in WAL mode
95    pub wal_mode: bool,
96}
97
98/// Export format for schema operations
99#[derive(Debug, Clone, Copy, Default, PartialEq, Eq, Serialize, Deserialize, JsonSchema)]
100#[serde(rename_all = "lowercase")]
101pub enum SchemaFormat {
102    /// SQL statements (CREATE TABLE, etc.)
103    #[default]
104    Sql,
105    /// JSON representation
106    Json,
107}
108
109/// Verbosity level for describe operations
110#[derive(Debug, Clone, Copy, Default, PartialEq, Eq, Serialize, Deserialize, JsonSchema)]
111#[serde(rename_all = "lowercase")]
112pub enum Verbosity {
113    /// Brief summary
114    #[default]
115    Summary,
116    /// Full details
117    Detailed,
118}
119
120/// Convert a JSON value to a rusqlite-compatible SQL parameter.
121///
122/// This handles the conversion from serde_json values to types that rusqlite
123/// can bind to prepared statement parameters.
124pub fn json_to_sql(value: &serde_json::Value) -> Box<dyn rusqlite::ToSql> {
125    match value {
126        serde_json::Value::Null => Box::new(Option::<i64>::None),
127        serde_json::Value::Bool(b) => Box::new(*b as i64),
128        serde_json::Value::Number(n) => {
129            if let Some(i) = n.as_i64() {
130                Box::new(i)
131            } else if let Some(f) = n.as_f64() {
132                Box::new(f)
133            } else {
134                Box::new(n.to_string())
135            }
136        }
137        serde_json::Value::String(s) => Box::new(s.clone()),
138        serde_json::Value::Array(_) | serde_json::Value::Object(_) => Box::new(value.to_string()),
139    }
140}
141
142#[cfg(test)]
143mod tests {
144    use super::*;
145    use rusqlite::Connection;
146
147    /// Helper to test json_to_sql by inserting a value and reading it back
148    fn roundtrip_json_value(json: serde_json::Value) -> rusqlite::types::Value {
149        let conn = Connection::open_in_memory().unwrap();
150        conn.execute("CREATE TABLE test (val)", []).unwrap();
151
152        let param = json_to_sql(&json);
153        conn.execute("INSERT INTO test VALUES (?1)", [param.as_ref()])
154            .unwrap();
155
156        conn.query_row("SELECT val FROM test", [], |row| row.get(0))
157            .unwrap()
158    }
159
160    #[test]
161    fn test_json_to_sql_null() {
162        let result = roundtrip_json_value(serde_json::Value::Null);
163        assert_eq!(result, rusqlite::types::Value::Null);
164    }
165
166    #[test]
167    fn test_json_to_sql_bool_true() {
168        let result = roundtrip_json_value(serde_json::json!(true));
169        assert_eq!(result, rusqlite::types::Value::Integer(1));
170    }
171
172    #[test]
173    fn test_json_to_sql_bool_false() {
174        let result = roundtrip_json_value(serde_json::json!(false));
175        assert_eq!(result, rusqlite::types::Value::Integer(0));
176    }
177
178    #[test]
179    fn test_json_to_sql_integer() {
180        let result = roundtrip_json_value(serde_json::json!(42));
181        assert_eq!(result, rusqlite::types::Value::Integer(42));
182    }
183
184    #[test]
185    fn test_json_to_sql_negative_integer() {
186        let result = roundtrip_json_value(serde_json::json!(-100));
187        assert_eq!(result, rusqlite::types::Value::Integer(-100));
188    }
189
190    #[test]
191    fn test_json_to_sql_large_integer() {
192        let large = i64::MAX;
193        let result = roundtrip_json_value(serde_json::json!(large));
194        assert_eq!(result, rusqlite::types::Value::Integer(large));
195    }
196
197    #[test]
198    fn test_json_to_sql_float() {
199        let result = roundtrip_json_value(serde_json::json!(1.234));
200        match result {
201            rusqlite::types::Value::Real(f) => assert!((f - 1.234).abs() < 0.001),
202            _ => panic!("Expected Real, got {:?}", result),
203        }
204    }
205
206    #[test]
207    fn test_json_to_sql_float_negative() {
208        let result = roundtrip_json_value(serde_json::json!(-2.5));
209        match result {
210            rusqlite::types::Value::Real(f) => assert!((f - (-2.5)).abs() < 0.001),
211            _ => panic!("Expected Real, got {:?}", result),
212        }
213    }
214
215    #[test]
216    fn test_json_to_sql_string() {
217        let result = roundtrip_json_value(serde_json::json!("hello world"));
218        assert_eq!(
219            result,
220            rusqlite::types::Value::Text("hello world".to_string())
221        );
222    }
223
224    #[test]
225    fn test_json_to_sql_empty_string() {
226        let result = roundtrip_json_value(serde_json::json!(""));
227        assert_eq!(result, rusqlite::types::Value::Text("".to_string()));
228    }
229
230    #[test]
231    fn test_json_to_sql_unicode_string() {
232        let result = roundtrip_json_value(serde_json::json!("こんにちは 🎉"));
233        assert_eq!(
234            result,
235            rusqlite::types::Value::Text("こんにちは 🎉".to_string())
236        );
237    }
238
239    #[test]
240    fn test_json_to_sql_array() {
241        let result = roundtrip_json_value(serde_json::json!([1, 2, 3]));
242        assert_eq!(result, rusqlite::types::Value::Text("[1,2,3]".to_string()));
243    }
244
245    #[test]
246    fn test_json_to_sql_nested_array() {
247        let result = roundtrip_json_value(serde_json::json!([[1, 2], [3, 4]]));
248        assert_eq!(
249            result,
250            rusqlite::types::Value::Text("[[1,2],[3,4]]".to_string())
251        );
252    }
253
254    #[test]
255    fn test_json_to_sql_object() {
256        let result = roundtrip_json_value(serde_json::json!({"key": "value"}));
257        assert_eq!(
258            result,
259            rusqlite::types::Value::Text("{\"key\":\"value\"}".to_string())
260        );
261    }
262
263    #[test]
264    fn test_json_to_sql_complex_object() {
265        let json = serde_json::json!({
266            "name": "test",
267            "values": [1, 2, 3],
268            "nested": {"a": 1}
269        });
270        let result = roundtrip_json_value(json);
271        match result {
272            rusqlite::types::Value::Text(s) => {
273                // Parse back to verify it's valid JSON
274                let parsed: serde_json::Value = serde_json::from_str(&s).unwrap();
275                assert_eq!(parsed["name"], "test");
276                assert_eq!(parsed["values"][0], 1);
277            }
278            _ => panic!("Expected Text, got {:?}", result),
279        }
280    }
281
282    #[test]
283    fn test_json_to_sql_empty_array() {
284        let result = roundtrip_json_value(serde_json::json!([]));
285        assert_eq!(result, rusqlite::types::Value::Text("[]".to_string()));
286    }
287
288    #[test]
289    fn test_json_to_sql_empty_object() {
290        let result = roundtrip_json_value(serde_json::json!({}));
291        assert_eq!(result, rusqlite::types::Value::Text("{}".to_string()));
292    }
293}
mixtape_tools/sqlite/types.rs

mixtape_tools/sqlite/
types.rs
