1use serde_json::{json, Value};
2use tokio_postgres::Client;
3use crate::errors::Result as MCPResult;
4
5const MAX_IDENTIFIER_LEN: usize = 255;
6
7fn validate_identifier(val: &str, label: &str) -> Result<(), crate::errors::MCPError> {
8 if val.is_empty() || val.len() > MAX_IDENTIFIER_LEN {
9 return Err(crate::errors::MCPError::InvalidParams(
10 format!("'{label}' must be 1-{MAX_IDENTIFIER_LEN} characters")
11 ));
12 }
13 Ok(())
14}
15
16fn qi(ident: &str) -> String {
17 crate::validation::quote_ident(ident)
18}
19
20pub async fn add_column(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
21 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
22 let column = params.as_ref().and_then(|p| p.get("column").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
23 let data_type = params.as_ref().and_then(|p| p.get("data_type").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'data_type'".into()))?;
24 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
25 let not_null = params.as_ref().and_then(|p| p.get("not_null").and_then(|v| v.as_bool())).unwrap_or(false);
26 let default = params.as_ref().and_then(|p| p.get("default").and_then(|v| v.as_str()));
27
28 validate_identifier(table, "table")?;
29 validate_identifier(column, "column")?;
30 validate_identifier(schema, "schema")?;
31
32 let mut sql = format!("ALTER TABLE {}.{} ADD COLUMN {}", qi(schema), qi(table), qi(column));
33 sql.push_str(&format!(" {}", data_type));
34 if let Some(d) = default {
35 sql.push_str(&format!(" DEFAULT {}", d));
36 }
37 if not_null {
38 sql.push_str(" NOT NULL");
39 }
40
41 client.execute(&sql, &[]).await?;
42 Ok(json!({ "success": true, "sql": sql }))
43}
44
45pub async fn drop_column(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
46 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
47 let column = params.as_ref().and_then(|p| p.get("column").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
48 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
49 let cascade = params.as_ref().and_then(|p| p.get("cascade").and_then(|v| v.as_bool())).unwrap_or(false);
50
51 validate_identifier(table, "table")?;
52 validate_identifier(column, "column")?;
53 validate_identifier(schema, "schema")?;
54
55 let mut sql = format!("ALTER TABLE {}.{} DROP COLUMN {}", qi(schema), qi(table), qi(column));
56 if cascade { sql.push_str(" CASCADE"); }
57
58 client.execute(&sql, &[]).await?;
59 Ok(json!({ "success": true, "sql": sql }))
60}
61
62pub async fn rename_column(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
63 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
64 let column = params.as_ref().and_then(|p| p.get("column").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
65 let new_name = params.as_ref().and_then(|p| p.get("new_name").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
66 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
67
68 validate_identifier(table, "table")?;
69 validate_identifier(column, "column")?;
70 validate_identifier(new_name, "new_name")?;
71 validate_identifier(schema, "schema")?;
72
73 let sql = format!("ALTER TABLE {}.{} RENAME COLUMN {} TO {}", qi(schema), qi(table), qi(column), qi(new_name));
74 client.execute(&sql, &[]).await?;
75 Ok(json!({ "success": true, "sql": sql }))
76}
77
78pub async fn alter_column_type(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
79 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
80 let column = params.as_ref().and_then(|p| p.get("column").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
81 let data_type = params.as_ref().and_then(|p| p.get("data_type").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'data_type'".into()))?;
82 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
83 let using = params.as_ref().and_then(|p| p.get("using").and_then(|v| v.as_str()));
84
85 validate_identifier(table, "table")?;
86 validate_identifier(column, "column")?;
87 validate_identifier(schema, "schema")?;
88
89 let mut sql = format!("ALTER TABLE {}.{} ALTER COLUMN {} TYPE {}", qi(schema), qi(table), qi(column), data_type);
90 if let Some(expr) = using {
91 sql.push_str(&format!(" USING {}", expr));
92 }
93
94 client.execute(&sql, &[]).await?;
95 Ok(json!({ "success": true, "sql": sql }))
96}
97
98pub async fn rename_table(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
99 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
100 let new_name = params.as_ref().and_then(|p| p.get("new_name").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
101 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
102
103 validate_identifier(table, "table")?;
104 validate_identifier(new_name, "new_name")?;
105 validate_identifier(schema, "schema")?;
106
107 let sql = format!("ALTER TABLE {}.{} RENAME TO {}", qi(schema), qi(table), qi(new_name));
108 client.execute(&sql, &[]).await?;
109 Ok(json!({ "success": true, "sql": sql }))
110}
111
112pub async fn rename_index(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
113 let index = params.as_ref().and_then(|p| p.get("index").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'index'".into()))?;
114 let new_name = params.as_ref().and_then(|p| p.get("new_name").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
115 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
116
117 validate_identifier(index, "index")?;
118 validate_identifier(new_name, "new_name")?;
119 validate_identifier(schema, "schema")?;
120
121 let sql = format!("ALTER INDEX {}.{} RENAME TO {}", qi(schema), qi(index), qi(new_name));
122 client.execute(&sql, &[]).await?;
123 Ok(json!({ "success": true, "sql": sql }))
124}
125
126pub async fn rename_schema(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
127 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'schema'".into()))?;
128 let new_name = params.as_ref().and_then(|p| p.get("new_name").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
129
130 validate_identifier(schema, "schema")?;
131 validate_identifier(new_name, "new_name")?;
132
133 let sql = format!("ALTER SCHEMA {} RENAME TO {}", qi(schema), qi(new_name));
134 client.execute(&sql, &[]).await?;
135 Ok(json!({ "success": true, "sql": sql }))
136}
137
138pub async fn add_foreign_key(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
139 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
140 let columns = params.as_ref().and_then(|p| p.get("columns").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'columns'".into()))?;
141 let ref_table = params.as_ref().and_then(|p| p.get("ref_table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'ref_table'".into()))?;
142 let ref_columns = params.as_ref().and_then(|p| p.get("ref_columns").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'ref_columns'".into()))?;
143 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
144 let constraint_name = params.as_ref().and_then(|p| p.get("constraint_name").and_then(|v| v.as_str()));
145 let on_delete = params.as_ref().and_then(|p| p.get("on_delete").and_then(|v| v.as_str()));
146 let on_update = params.as_ref().and_then(|p| p.get("on_update").and_then(|v| v.as_str()));
147
148 validate_identifier(table, "table")?;
149 validate_identifier(ref_table, "ref_table")?;
150 validate_identifier(schema, "schema")?;
151
152 let mut sql = format!("ALTER TABLE {}.{} ADD", qi(schema), qi(table));
153 if let Some(cname) = constraint_name {
154 sql.push_str(&format!(" CONSTRAINT {}", qi(cname)));
155 }
156 sql.push_str(&format!(" FOREIGN KEY ({}) REFERENCES {}.{} ({})", columns, qi(schema), qi(ref_table), ref_columns));
157 if let Some(od) = on_delete {
158 sql.push_str(&format!(" ON DELETE {}", od));
159 }
160 if let Some(ou) = on_update {
161 sql.push_str(&format!(" ON UPDATE {}", ou));
162 }
163
164 client.execute(&sql, &[]).await?;
165 Ok(json!({ "success": true, "sql": sql }))
166}
167
168pub async fn drop_foreign_key(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
169 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
170 let constraint = params.as_ref().and_then(|p| p.get("constraint").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'constraint'".into()))?;
171 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
172 let cascade = params.as_ref().and_then(|p| p.get("cascade").and_then(|v| v.as_bool())).unwrap_or(false);
173
174 validate_identifier(table, "table")?;
175 validate_identifier(constraint, "constraint")?;
176 validate_identifier(schema, "schema")?;
177
178 let mut sql = format!("ALTER TABLE {}.{} DROP CONSTRAINT {}", qi(schema), qi(table), qi(constraint));
179 if cascade { sql.push_str(" CASCADE"); }
180
181 client.execute(&sql, &[]).await?;
182 Ok(json!({ "success": true, "sql": sql }))
183}
184
185pub async fn add_unique_constraint(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
186 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
187 let columns = params.as_ref().and_then(|p| p.get("columns").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'columns'".into()))?;
188 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
189 let constraint_name = params.as_ref().and_then(|p| p.get("constraint_name").and_then(|v| v.as_str()));
190
191 validate_identifier(table, "table")?;
192 validate_identifier(schema, "schema")?;
193
194 let mut sql = format!("ALTER TABLE {}.{} ADD", qi(schema), qi(table));
195 if let Some(cname) = constraint_name {
196 validate_identifier(cname, "constraint_name")?;
197 sql.push_str(&format!(" CONSTRAINT {}", qi(cname)));
198 }
199 sql.push_str(&format!(" UNIQUE ({})", columns));
200
201 client.execute(&sql, &[]).await?;
202 Ok(json!({ "success": true, "sql": sql }))
203}
204
205pub async fn drop_constraint(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
206 let table = params.as_ref().and_then(|p| p.get("table").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
207 let constraint = params.as_ref().and_then(|p| p.get("constraint").and_then(|v| v.as_str())).ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'constraint'".into()))?;
208 let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str())).unwrap_or("public");
209 let cascade = params.as_ref().and_then(|p| p.get("cascade").and_then(|v| v.as_bool())).unwrap_or(false);
210
211 validate_identifier(table, "table")?;
212 validate_identifier(constraint, "constraint")?;
213 validate_identifier(schema, "schema")?;
214
215 let mut sql = format!("ALTER TABLE {}.{} DROP CONSTRAINT {}", qi(schema), qi(table), qi(constraint));
216 if cascade { sql.push_str(" CASCADE"); }
217
218 client.execute(&sql, &[]).await?;
219 Ok(json!({ "success": true, "sql": sql }))
220}