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 reference.
136fn extract_variable_name(expr: &Expression) -> Result<String, ExecutorError> {
137    match expr {
138        Expression::ColumnRef { table: None, column } => {
139            // Column reference without table - treat as session variable
140            // If it starts with @, strip it; otherwise use as-is
141            let var_name = if let Some(stripped) = column.strip_prefix('@') {
142                stripped.to_string()
143            } else {
144                column.clone()
145            };
146            Ok(var_name)
147        }
148        Expression::ColumnRef { table: Some(_), column: _ } => {
149            Err(ExecutorError::Other(
150                "OUT/INOUT parameter target must be a session variable (e.g., @var_name), not a table.column reference".to_string()
151            ))
152        }
153        Expression::Literal(_) => {
154            Err(ExecutorError::Other(
155                "OUT/INOUT parameter target cannot be a literal value".to_string()
156            ))
157        }
158        _ => {
159            Err(ExecutorError::Other(
160                "OUT/INOUT parameter target must be a session variable (e.g., @var_name), not a complex expression".to_string()
161            ))
162        }
163    }
164}
165
166/// Execute CALL statement (SQL:1999 Feature P001)
167///
168/// Executes a stored procedure with parameter binding and procedural statement execution.
169///
170/// # Parameter Binding
171///
172/// - **IN parameters**: Values are evaluated and passed to the procedure
173/// - **OUT parameters**: Must be session variables (@var), initialized to NULL,
174///   procedure can assign values that are returned to caller
175/// - **INOUT parameters**: Must be session variables (@var), values are passed in
176///   and can be modified by the procedure
177///
178/// # Example
179///
180/// ```sql
181/// -- Create procedure with mixed parameter modes
182/// CREATE PROCEDURE update_counter(
183///   IN increment INT,
184///   INOUT counter INT,
185///   OUT message VARCHAR(100)
186/// )
187/// BEGIN
188///   SET counter = counter + increment;
189///   SET message = CONCAT('Counter updated to ', counter);
190/// END;
191///
192/// -- Call procedure
193/// SET @count = 10;
194/// CALL update_counter(5, @count, @msg);
195/// SELECT @count, @msg;
196/// -- Output: count=15, msg='Counter updated to 15'
197/// ```
198///
199/// # Control Flow
200///
201/// The procedure body executes sequentially until:
202/// - All statements complete (normal exit)
203/// - RETURN statement is encountered (early exit)
204/// - Error occurs (execution stops)
205///
206/// After execution, OUT and INOUT parameter values are written back to their
207/// corresponding session variables.
208///
209/// # Errors
210///
211/// Returns error if:
212/// - Procedure not found
213/// - Wrong number of arguments
214/// - OUT/INOUT parameter is not a session variable
215/// - Type mismatch in parameter binding
216/// - Execution error in procedure body
217pub fn execute_call(stmt: &CallStmt, db: &mut Database) -> Result<(), ExecutorError> {
218    use crate::procedural::{execute_procedural_statement, ControlFlow, ExecutionContext};
219
220    // 1. Look up the procedure definition and clone what we need
221    let (parameters, body) = {
222        let procedure = db.catalog.get_procedure(&stmt.procedure_name);
223
224        match procedure {
225            Some(proc) => (proc.parameters.clone(), proc.body.clone()),
226            None => {
227                // Procedure not found - provide helpful error with suggestions
228                let schema_name = db.catalog.get_current_schema().to_string();
229                let available_procedures = db.catalog.list_procedures();
230
231                return Err(ExecutorError::ProcedureNotFound {
232                    procedure_name: stmt.procedure_name.clone(),
233                    schema_name,
234                    available_procedures,
235                });
236            }
237        }
238    };
239
240    // 2. Validate parameter count
241    if stmt.arguments.len() != parameters.len() {
242        // Build parameter signature string
243        let param_sig = parameters
244            .iter()
245            .map(|p| {
246                let mode = match p.mode {
247                    vibesql_catalog::ParameterMode::In => "IN",
248                    vibesql_catalog::ParameterMode::Out => "OUT",
249                    vibesql_catalog::ParameterMode::InOut => "INOUT",
250                };
251                format!("{} {} {:?}", mode, p.name, p.data_type)
252            })
253            .collect::<Vec<_>>()
254            .join(", ");
255
256        return Err(ExecutorError::ParameterCountMismatch {
257            routine_name: stmt.procedure_name.clone(),
258            routine_type: "Procedure".to_string(),
259            expected: parameters.len(),
260            actual: stmt.arguments.len(),
261            parameter_signature: param_sig,
262        });
263    }
264
265    // 3. Create execution context and bind parameters
266    let mut ctx = ExecutionContext::new();
267
268    for (param, arg_expr) in parameters.iter().zip(&stmt.arguments) {
269        match param.mode {
270            vibesql_catalog::ParameterMode::In => {
271                // IN parameter: Evaluate and bind the value
272                let value = crate::procedural::executor::evaluate_expression(arg_expr, db, &ctx)?;
273                ctx.set_parameter(&param.name, value);
274            }
275            vibesql_catalog::ParameterMode::Out => {
276                // OUT parameter: Initialize to NULL
277                ctx.set_parameter(&param.name, vibesql_types::SqlValue::Null);
278
279                // Extract and register target variable name
280                let var_name = extract_variable_name(arg_expr)?;
281                ctx.register_out_parameter(&param.name, var_name);
282            }
283            vibesql_catalog::ParameterMode::InOut => {
284                // INOUT parameter: Evaluate and bind input value
285                let value = crate::procedural::executor::evaluate_expression(arg_expr, db, &ctx)?;
286                ctx.set_parameter(&param.name, value);
287
288                // Extract and register target variable name
289                let var_name = extract_variable_name(arg_expr)?;
290                ctx.register_out_parameter(&param.name, var_name);
291            }
292        }
293    }
294
295    // 4. Execute procedure body
296    match &body {
297        vibesql_catalog::ProcedureBody::BeginEnd(statements) => {
298            // Execute each procedural statement sequentially
299            for stmt in statements {
300                match execute_procedural_statement(stmt, &mut ctx, db)? {
301                    ControlFlow::Continue => {
302                        // Continue to next statement
303                    }
304                    ControlFlow::Return(_) => {
305                        // RETURN in procedures exits early (functions will handle return value later)
306                        break;
307                    }
308                    ControlFlow::Leave(_) | ControlFlow::Iterate(_) => {
309                        // Control flow statements not yet supported in Phase 2
310                        return Err(ExecutorError::UnsupportedFeature(
311                            "Control flow (LEAVE/ITERATE) not yet supported in Phase 2".to_string(),
312                        ));
313                    }
314                }
315            }
316            Ok(())
317        }
318        vibesql_catalog::ProcedureBody::RawSql(_) => {
319            // RawSql fallback - would require parsing
320            Err(ExecutorError::UnsupportedFeature(
321                "RawSql procedure bodies require parsing (use BEGIN/END block instead)".to_string(),
322            ))
323        }
324    }?;
325
326    // 5. Return output parameter values to session variables
327    for (param_name, target_var_name) in ctx.get_out_parameters() {
328        // Get the parameter value from the context
329        let value = ctx
330            .get_parameter(param_name)
331            .cloned()
332            .ok_or_else(|| ExecutorError::Other(format!("Parameter '{}' not found", param_name)))?;
333
334        // Store in session variable
335        db.set_session_variable(target_var_name, value);
336    }
337
338    Ok(())
339}
vibesql_executor/advanced_objects/procedures.rs

vibesql_executor/advanced_objects/
procedures.rs
