vibesql_executor/advanced_objects/
procedures.rs

1//! Executor for PROCEDURE objects and CALL statement (SQL:1999 Feature P001)
2
3use vibesql_ast::*;
4use vibesql_storage::Database;
5
6use crate::errors::ExecutorError;
7
8/// Execute CREATE PROCEDURE statement (SQL:1999 Feature P001)
9///
10/// Creates a stored procedure in the database catalog with optional characteristics.
11///
12/// # Parameters
13///
14/// Procedures support three parameter modes:
15/// - **IN**: Input-only parameters (read by procedure)
16/// - **OUT**: Output-only parameters (written by procedure to session variables)
17/// - **INOUT**: Both input and output (read and written)
18///
19/// # Characteristics (Phase 6)
20///
21/// - **SQL SECURITY**: DEFINER (default) or INVOKER
22/// - **COMMENT**: Documentation string
23/// - **LANGUAGE**: SQL (only supported language)
24///
25/// # Example
26///
27/// ```sql
28/// CREATE PROCEDURE calculate_stats(
29///   IN input_value INT,
30///   OUT sum_result INT,
31///   OUT count_result INT
32/// )
33///   SQL SECURITY INVOKER
34///   COMMENT 'Calculate sum and count from data_table'
35/// BEGIN
36///   SELECT SUM(value), COUNT(*)
37///   INTO sum_result, count_result
38///   FROM data_table
39///   WHERE value > input_value;
40/// END;
41/// ```
42///
43/// # Errors
44///
45/// Returns error if:
46/// - Procedure name already exists
47/// - Invalid parameter types
48/// - Body parsing fails
49pub fn execute_create_procedure(
50    stmt: &CreateProcedureStmt,
51    db: &mut Database,
52) -> Result<(), ExecutorError> {
53    use vibesql_catalog::{ParameterMode, Procedure, ProcedureBody, ProcedureParam, SqlSecurity};
54
55    // Convert AST parameters to catalog parameters
56    let catalog_params = stmt
57        .parameters
58        .iter()
59        .map(|param| {
60            let mode = match param.mode {
61                vibesql_ast::ParameterMode::In => ParameterMode::In,
62                vibesql_ast::ParameterMode::Out => ParameterMode::Out,
63                vibesql_ast::ParameterMode::InOut => ParameterMode::InOut,
64            };
65            ProcedureParam { mode, name: param.name.clone(), data_type: param.data_type.clone() }
66        })
67        .collect();
68
69    // Convert AST body to catalog body
70    let catalog_body = match &stmt.body {
71        vibesql_ast::ProcedureBody::BeginEnd(statements) => {
72            // Store the parsed AST for execution
73            ProcedureBody::BeginEnd(statements.clone())
74        }
75        vibesql_ast::ProcedureBody::RawSql(sql) => ProcedureBody::RawSql(sql.clone()),
76    };
77
78    // Convert characteristics (Phase 6)
79    let sql_security = stmt
80        .sql_security
81        .as_ref()
82        .map(|sec| match sec {
83            vibesql_ast::SqlSecurity::Definer => SqlSecurity::Definer,
84            vibesql_ast::SqlSecurity::Invoker => SqlSecurity::Invoker,
85        })
86        .unwrap_or(SqlSecurity::Definer);
87
88    let procedure =
89        if stmt.sql_security.is_some() || stmt.comment.is_some() || stmt.language.is_some() {
90            Procedure::with_characteristics(
91                stmt.procedure_name.clone(),
92                db.catalog.get_current_schema().to_string(),
93                catalog_params,
94                catalog_body,
95                sql_security,
96                stmt.comment.clone(),
97                stmt.language.clone().unwrap_or_else(|| "SQL".to_string()),
98            )
99        } else {
100            Procedure::new(
101                stmt.procedure_name.clone(),
102                db.catalog.get_current_schema().to_string(),
103                catalog_params,
104                catalog_body,
105            )
106        };
107
108    db.catalog.create_procedure_with_characteristics(procedure)?;
109    Ok(())
110}
111
112/// Execute DROP PROCEDURE statement (SQL:1999 Feature P001)
113pub fn execute_drop_procedure(
114    stmt: &DropProcedureStmt,
115    db: &mut Database,
116) -> Result<(), ExecutorError> {
117    // Check if procedure exists
118    let procedure_exists = db.catalog.procedure_exists(&stmt.procedure_name);
119
120    // If IF EXISTS is specified and procedure doesn't exist, succeed silently
121    if stmt.if_exists && !procedure_exists {
122        return Ok(());
123    }
124
125    db.catalog.drop_procedure(&stmt.procedure_name)?;
126    Ok(())
127}
128
129/// Extract variable name from an expression for OUT/INOUT parameter binding
130///
131/// Valid patterns:
132/// - ColumnRef without table qualifier (treated as session variable): @var_name
133/// - Function call to session variable function (if we add one)
134///
135/// Returns the variable name (without @ prefix) or error if expression is not a valid variable
136/// reference.
137fn extract_variable_name(expr: &Expression) -> Result<String, ExecutorError> {
138    match expr {
139        Expression::ColumnRef(col_id) if col_id.schema_canonical().is_none() && col_id.table_canonical().is_none() => {
140            // Column reference without table - treat as session variable
141            // If it starts with @, strip it; otherwise use as-is
142            let column = col_id.column_canonical();
143            let var_name = if let Some(stripped) = column.strip_prefix('@') {
144                stripped.to_string()
145            } else {
146                column.to_string()
147            };
148            Ok(var_name)
149        }
150        Expression::ColumnRef(col_id) if col_id.schema_canonical().is_none() && col_id.table_canonical().is_some() => {
151            Err(ExecutorError::Other(
152                "OUT/INOUT parameter target must be a session variable (e.g., @var_name), not a table.column reference".to_string()
153            ))
154        }
155        Expression::Literal(_) => {
156            Err(ExecutorError::Other(
157                "OUT/INOUT parameter target cannot be a literal value".to_string()
158            ))
159        }
160        _ => {
161            Err(ExecutorError::Other(
162                "OUT/INOUT parameter target must be a session variable (e.g., @var_name), not a complex expression".to_string()
163            ))
164        }
165    }
166}
167
168/// Execute CALL statement (SQL:1999 Feature P001)
169///
170/// Executes a stored procedure with parameter binding and procedural statement execution.
171///
172/// # Parameter Binding
173///
174/// - **IN parameters**: Values are evaluated and passed to the procedure
175/// - **OUT parameters**: Must be session variables (@var), initialized to NULL, procedure can
176///   assign values that are returned to caller
177/// - **INOUT parameters**: Must be session variables (@var), values are passed in and can be
178///   modified by the procedure
179///
180/// # Example
181///
182/// ```sql
183/// -- Create procedure with mixed parameter modes
184/// CREATE PROCEDURE update_counter(
185///   IN increment INT,
186///   INOUT counter INT,
187///   OUT message VARCHAR(100)
188/// )
189/// BEGIN
190///   SET counter = counter + increment;
191///   SET message = CONCAT('Counter updated to ', counter);
192/// END;
193///
194/// -- Call procedure
195/// SET @count = 10;
196/// CALL update_counter(5, @count, @msg);
197/// SELECT @count, @msg;
198/// -- Output: count=15, msg='Counter updated to 15'
199/// ```
200///
201/// # Control Flow
202///
203/// The procedure body executes sequentially until:
204/// - All statements complete (normal exit)
205/// - RETURN statement is encountered (early exit)
206/// - Error occurs (execution stops)
207///
208/// After execution, OUT and INOUT parameter values are written back to their
209/// corresponding session variables.
210///
211/// # Errors
212///
213/// Returns error if:
214/// - Procedure not found
215/// - Wrong number of arguments
216/// - OUT/INOUT parameter is not a session variable
217/// - Type mismatch in parameter binding
218/// - Execution error in procedure body
219pub fn execute_call(stmt: &CallStmt, db: &mut Database) -> Result<(), ExecutorError> {
220    use crate::procedural::{execute_procedural_statement, ControlFlow, ExecutionContext};
221
222    // 1. Look up the procedure definition and clone what we need
223    let (parameters, body) = {
224        let procedure = db.catalog.get_procedure(&stmt.procedure_name);
225
226        match procedure {
227            Some(proc) => (proc.parameters.clone(), proc.body.clone()),
228            None => {
229                // Procedure not found - provide helpful error with suggestions
230                let schema_name = db.catalog.get_current_schema().to_string();
231                let available_procedures = db.catalog.list_procedures();
232
233                return Err(ExecutorError::ProcedureNotFound {
234                    procedure_name: stmt.procedure_name.clone(),
235                    schema_name,
236                    available_procedures,
237                });
238            }
239        }
240    };
241
242    // 2. Validate parameter count
243    if stmt.arguments.len() != parameters.len() {
244        // Build parameter signature string
245        let param_sig = parameters
246            .iter()
247            .map(|p| {
248                let mode = match p.mode {
249                    vibesql_catalog::ParameterMode::In => "IN",
250                    vibesql_catalog::ParameterMode::Out => "OUT",
251                    vibesql_catalog::ParameterMode::InOut => "INOUT",
252                };
253                format!("{} {} {:?}", mode, p.name, p.data_type)
254            })
255            .collect::<Vec<_>>()
256            .join(", ");
257
258        return Err(ExecutorError::ParameterCountMismatch {
259            routine_name: stmt.procedure_name.clone(),
260            routine_type: "Procedure".to_string(),
261            expected: parameters.len(),
262            actual: stmt.arguments.len(),
263            parameter_signature: param_sig,
264        });
265    }
266
267    // 3. Create execution context and bind parameters
268    let mut ctx = ExecutionContext::new();
269
270    for (param, arg_expr) in parameters.iter().zip(&stmt.arguments) {
271        match param.mode {
272            vibesql_catalog::ParameterMode::In => {
273                // IN parameter: Evaluate and bind the value
274                let value = crate::procedural::executor::evaluate_expression(arg_expr, db, &ctx)?;
275                ctx.set_parameter(&param.name, value);
276            }
277            vibesql_catalog::ParameterMode::Out => {
278                // OUT parameter: Initialize to NULL
279                ctx.set_parameter(&param.name, vibesql_types::SqlValue::Null);
280
281                // Extract and register target variable name
282                let var_name = extract_variable_name(arg_expr)?;
283                ctx.register_out_parameter(&param.name, var_name);
284            }
285            vibesql_catalog::ParameterMode::InOut => {
286                // INOUT parameter: Evaluate and bind input value
287                let value = crate::procedural::executor::evaluate_expression(arg_expr, db, &ctx)?;
288                ctx.set_parameter(&param.name, value);
289
290                // Extract and register target variable name
291                let var_name = extract_variable_name(arg_expr)?;
292                ctx.register_out_parameter(&param.name, var_name);
293            }
294        }
295    }
296
297    // 4. Execute procedure body
298    match &body {
299        vibesql_catalog::ProcedureBody::BeginEnd(statements) => {
300            // Execute each procedural statement sequentially
301            for stmt in statements {
302                match execute_procedural_statement(stmt, &mut ctx, db)? {
303                    ControlFlow::Continue => {
304                        // Continue to next statement
305                    }
306                    ControlFlow::Return(_) => {
307                        // RETURN in procedures exits early (functions will handle return value
308                        // later)
309                        break;
310                    }
311                    ControlFlow::Leave(_) | ControlFlow::Iterate(_) => {
312                        // Control flow statements not yet supported in Phase 2
313                        return Err(ExecutorError::UnsupportedFeature(
314                            "Control flow (LEAVE/ITERATE) not yet supported in Phase 2".to_string(),
315                        ));
316                    }
317                }
318            }
319            Ok(())
320        }
321        vibesql_catalog::ProcedureBody::RawSql(_) => {
322            // RawSql fallback - would require parsing
323            Err(ExecutorError::UnsupportedFeature(
324                "RawSql procedure bodies require parsing (use BEGIN/END block instead)".to_string(),
325            ))
326        }
327    }?;
328
329    // 5. Return output parameter values to session variables
330    for (param_name, target_var_name) in ctx.get_out_parameters() {
331        // Get the parameter value from the context
332        let value = ctx
333            .get_parameter(param_name)
334            .cloned()
335            .ok_or_else(|| ExecutorError::Other(format!("Parameter '{}' not found", param_name)))?;
336
337        // Store in session variable
338        db.set_session_variable(target_var_name, value);
339    }
340
341    Ok(())
342}
vibesql_executor/advanced_objects/procedures.rs

vibesql_executor/advanced_objects/
procedures.rs
