razeri_plugin_contract/routine_render/

procedure.rs

// Copyright (C) 2026 Gabriel Shea
// Licensed under either of the Apache License, Version 2.0 or the MIT
// license, at your option.

//! The stored-procedure grammars.

use super::{
    indented, inline_parameters, note_above, or_replace, parameter_types, plain_name, quoted_name,
    quoted_text, refusal_comment, terminated_statements,
};
use crate::routine_form::ProcedureForm;
use crate::routine_spec::{
    ParameterDirection, RoutineBody, RoutineDeclaration, RoutineSecurity, RoutineSpec,
};

/// **Spell this procedure in a named grammar.**
///
/// The grammar a dialect is nearly the same as, written out, so a dialect that
/// answers [`ProcedureForm::Custom`] can start from it and say what differs
/// instead of writing a `CREATE` from nothing.
///
/// Parameters:
/// - `form`: the grammar to spell it in. `Custom` is not an error to pass — it
///   answers with the agnostic form under a comment naming the grammar, so a
///   fallback branch that reaches here gets something to read. `ClickHouse` and
///   `Databricks` do the same, because neither deploys a procedure as SQL;
/// - `routine`: the procedure, in the shape a `Custom` dialect receives it.
#[must_use]
pub fn render_procedure_as(form: ProcedureForm, routine: &RoutineSpec) -> String {
    // No wildcard arm, deliberately. The enum is `#[non_exhaustive]` for
    // dialects outside this crate, and inside it a new form has to be
    // spelled here or the build stops — which is the point: a form nobody
    // wrote a grammar for would otherwise render as something it is not.
    match form {
        ProcedureForm::PlPgSql => pl_pgsql(routine),
        ProcedureForm::MySql => my_sql(routine),
        ProcedureForm::TSql => t_sql(routine),
        ProcedureForm::SnowflakeScripting => snowflake_scripting(routine),
        ProcedureForm::GoogleSql => google_sql(routine),
        ProcedureForm::Agnostic => agnostic(routine),
        ProcedureForm::ClickHouse => note_above(NOT_A_SQL_GRAMMAR_CLICKHOUSE, &agnostic(routine)),
        ProcedureForm::Databricks => note_above(NOT_A_SQL_GRAMMAR_DATABRICKS, &agnostic(routine)),
        ProcedureForm::Custom(grammar) => {
            note_above(&asked_for_its_own(grammar), &agnostic(routine))
        }
        ProcedureForm::Unsupported(orientation) => {
            refusal_comment("procedure", &routine.name, orientation)
        }
    }
}

/// Why `ClickHouse` has no `CREATE` here.
const NOT_A_SQL_GRAMMAR_CLICKHOUSE: &str = "ClickHouse deploys a procedure as an executable program with an XML declaration beside it, \
     not as a statement. The agnostic form below says what the routine is; the deployment is not \
     something a grammar spells.";

/// Why Databricks has no `CREATE` here.
const NOT_A_SQL_GRAMMAR_DATABRICKS: &str = "Databricks deploys this procedure as a Python function rather than as a statement. The \
     agnostic form below says what the routine is; the function is not something a grammar spells.";

/// What `Custom` gets, and why it is not an error to ask.
fn asked_for_its_own(grammar: &str) -> String {
    format!(
        "You asked for {grammar}, which is your dialect's own grammar and not one spelled here. \
         The agnostic form below is a neutral starting point; the named forms are reachable by \
         naming one."
    )
}

/// `IN` / `OUT` / `INOUT` / `VARIADIC`, the spelling most engines share.
fn direction_keyword(direction: ParameterDirection) -> &'static str {
    match direction {
        ParameterDirection::In => "IN",
        ParameterDirection::Out => "OUT",
        ParameterDirection::InOut => "INOUT",
        ParameterDirection::Variadic => "VARIADIC",
    }
}

/// The body's statements, or the block the design framed itself.
fn body_lines(routine: &RoutineSpec, pad: &str) -> Vec<String> {
    match &routine.body {
        RoutineBody::Statements(statements) => terminated_statements(statements, pad, ";"),
        RoutineBody::Block(block) => vec![block.clone()],
        RoutineBody::Query(query) => vec![format!("{};", indented(query, pad))],
        RoutineBody::Expression(expression) => {
            vec![format!("{};", indented(expression, pad))]
        }
    }
}

/// Every declaration the procedure opens with: the spec's own, in order.
fn declarations(routine: &RoutineSpec) -> &[RoutineDeclaration] {
    &routine.declarations
}

// ───────────────────────────── PL/pgSQL ─────────────────────────────

/// PL/pgSQL: a dollar-quoted body, `DECLARE` above `BEGIN`, and the description
/// as its own `COMMENT ON` statement.
fn pl_pgsql(routine: &RoutineSpec) -> String {
    let mut lines =
        vec![format!("CREATE {}PROCEDURE {}(", or_replace(routine), plain_name(routine))];
    let last = routine.parameters.len().saturating_sub(1);
    for (index, parameter) in routine.parameters.iter().enumerate() {
        let default =
            parameter.default.as_ref().map_or_else(String::new, |d| format!(" DEFAULT {d}"));
        let comma = if index == last { "" } else { "," };
        lines.push(format!(
            "    {} {} {}{}{}",
            direction_keyword(parameter.direction),
            parameter.name,
            parameter.data_type,
            default,
            comma
        ));
    }
    lines.push(")".to_owned());
    lines.push("LANGUAGE plpgsql".to_owned());
    lines.push(
        match routine.security {
            RoutineSecurity::Caller => "SECURITY INVOKER",
            RoutineSecurity::Owner => "SECURITY DEFINER",
        }
        .to_owned(),
    );
    lines.push("AS $$".to_owned());
    if !declarations(routine).is_empty() {
        lines.push("DECLARE".to_owned());
        for declaration in declarations(routine) {
            lines.push(match &declaration.default {
                Some(default) => {
                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
                }
                None => format!("    {} {};", declaration.name, declaration.data_type),
            });
        }
    }
    lines.push("BEGIN".to_owned());
    lines.extend(body_lines(routine, "    "));
    lines.push("END;".to_owned());
    lines.push("$$;".to_owned());
    if let Some(comment) = &routine.comment {
        lines.push(String::new());
        lines.push(format!(
            "COMMENT ON PROCEDURE {}({}) IS '{}';",
            plain_name(routine),
            parameter_types(routine),
            quoted_text(comment)
        ));
    }
    lines.join("\n")
}

// ────────────────────────── MySQL / MariaDB ──────────────────────────

/// `MySQL`'s compound statement. There is no dollar quoting here — the body is
/// protected by the client's own `DELIMITER` directive, which is what a rendered
/// `.sql` file applied through a client needs, and a replace is a `DROP` in
/// front because the engine has no `OR REPLACE` for a procedure.
fn my_sql(routine: &RoutineSpec) -> String {
    let mut lines = Vec::new();
    if routine.or_replace {
        lines.push(format!("DROP PROCEDURE IF EXISTS {};", plain_name(routine)));
        lines.push(String::new());
    }
    lines.push("DELIMITER $$".to_owned());
    lines.push(format!("CREATE PROCEDURE {}(", plain_name(routine)));
    let last = routine.parameters.len().saturating_sub(1);
    for (index, parameter) in routine.parameters.iter().enumerate() {
        let comma = if index == last { "" } else { "," };
        lines.push(format!(
            "    {} {} {}{}",
            direction_keyword(parameter.direction),
            parameter.name,
            parameter.data_type,
            comma
        ));
    }
    lines.push(")".to_owned());
    if let Some(comment) = &routine.comment {
        lines.push(format!("COMMENT '{}'", quoted_text(comment)));
    }
    lines.push("BEGIN".to_owned());
    for declaration in declarations(routine) {
        lines.push(match &declaration.default {
            Some(default) => format!(
                "    DECLARE {} {} DEFAULT {};",
                declaration.name, declaration.data_type, default
            ),
            None => format!("    DECLARE {} {};", declaration.name, declaration.data_type),
        });
    }
    lines.extend(body_lines(routine, "    "));
    lines.push("END$$".to_owned());
    lines.push("DELIMITER ;".to_owned());
    lines.join("\n")
}

// ──────────────────────────────── T-SQL ────────────────────────────────

/// T-SQL: bracketed names, `@`-prefixed parameters, `SET NOCOUNT ON` at the top
/// of the body, and the description in its own batch behind an existence check.
///
/// The batch separator is what makes the description a statement *beside* the
/// procedure rather than its last line: a procedure body here runs to the end of
/// the batch, and `sp_addextendedproperty` is not idempotent, so a redeploy
/// without the check fails on the description.
fn t_sql(routine: &RoutineSpec) -> String {
    let create = if routine.or_replace { "CREATE OR ALTER PROCEDURE" } else { "CREATE PROCEDURE" };
    let mut lines = vec![format!("{create} {}", quoted_name(routine, '[', ']'))];
    let last = routine.parameters.len().saturating_sub(1);
    for (index, parameter) in routine.parameters.iter().enumerate() {
        let default = parameter.default.as_ref().map_or_else(String::new, |d| format!(" = {d}"));
        let output = match parameter.direction {
            ParameterDirection::Out | ParameterDirection::InOut => " OUTPUT",
            _ => "",
        };
        let comma = if index == last { "" } else { "," };
        lines.push(format!(
            "    @{} {}{}{}{}",
            parameter.name, parameter.data_type, default, output, comma
        ));
    }
    if routine.security == RoutineSecurity::Owner {
        lines.push("WITH EXECUTE AS OWNER".to_owned());
    }
    lines.push("AS".to_owned());
    lines.push("BEGIN".to_owned());
    lines.push("    SET NOCOUNT ON;".to_owned());
    for declaration in declarations(routine) {
        lines.push(match &declaration.default {
            Some(default) => {
                format!(
                    "    DECLARE @{} {} = {};",
                    declaration.name, declaration.data_type, default
                )
            }
            None => format!("    DECLARE @{} {};", declaration.name, declaration.data_type),
        });
    }
    if !declarations(routine).is_empty() {
        lines.push(String::new());
    }
    lines.extend(body_lines(routine, "    "));
    lines.push("END;".to_owned());
    if let Some(comment) = &routine.comment {
        let schema = routine.schema.as_deref().unwrap_or("dbo");
        lines.push(String::new());
        lines.push("GO".to_owned());
        lines.push(format!(
            "IF NOT EXISTS (SELECT 1 FROM sys.extended_properties p JOIN sys.objects o ON \
             o.object_id = p.major_id JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE \
             p.name = N'MS_Description' AND p.minor_id = 0 AND s.name = N'{schema}' AND o.name = \
             N'{}')",
            routine.name
        ));
        lines.push(format!(
            "    EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'{}', \
             @level0type = N'SCHEMA', @level0name = N'{schema}', @level1type = N'PROCEDURE', \
             @level1name = N'{}';",
            quoted_text(comment),
            routine.name
        ));
    }
    lines.join("\n")
}

// ───────────────────────── Snowflake Scripting ─────────────────────────

/// Snowflake Scripting. A procedure here declares a return type even when it
/// returns nothing a caller reads, so the form returns a status string and the
/// body ends by handing one back.
fn snowflake_scripting(routine: &RoutineSpec) -> String {
    let mut lines = vec![
        format!(
            "CREATE {}PROCEDURE {}({})",
            or_replace(routine),
            plain_name(routine),
            inline_parameters(routine)
        ),
        "RETURNS STRING".to_owned(),
        "LANGUAGE SQL".to_owned(),
        match routine.security {
            RoutineSecurity::Caller => "EXECUTE AS CALLER",
            RoutineSecurity::Owner => "EXECUTE AS OWNER",
        }
        .to_owned(),
        "AS".to_owned(),
        "$$".to_owned(),
    ];
    if !declarations(routine).is_empty() {
        lines.push("DECLARE".to_owned());
        for declaration in declarations(routine) {
            lines.push(match &declaration.default {
                Some(default) => {
                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
                }
                None => format!("    {} {};", declaration.name, declaration.data_type),
            });
        }
    }
    lines.push("BEGIN".to_owned());
    lines.extend(body_lines(routine, "    "));
    lines.push("    RETURN 'SUCCESS';".to_owned());
    lines.push("END;".to_owned());
    lines.push("$$;".to_owned());
    if let Some(comment) = &routine.comment {
        lines.push(String::new());
        lines.push(format!(
            "COMMENT ON PROCEDURE {}({}) IS '{}';",
            plain_name(routine),
            parameter_types(routine),
            quoted_text(comment)
        ));
    }
    lines.join("\n")
}

// ────────────────────────────── GoogleSQL ──────────────────────────────

/// `GoogleSQL` scripting. The body is not quoted at all, and the description is
/// an `OPTIONS` clause rather than a statement of its own.
fn google_sql(routine: &RoutineSpec) -> String {
    let mut lines = vec![format!(
        "CREATE {}PROCEDURE {}({})",
        or_replace(routine),
        quoted_name(routine, '`', '`'),
        inline_parameters(routine)
    )];
    if let Some(comment) = &routine.comment {
        lines.push(format!("OPTIONS (description = '{}')", comment.replace('\'', "\\'")));
    }
    lines.push("BEGIN".to_owned());
    for declaration in declarations(routine) {
        lines.push(match &declaration.default {
            Some(default) => format!(
                "    DECLARE {} {} DEFAULT {};",
                declaration.name, declaration.data_type, default
            ),
            None => format!("    DECLARE {} {};", declaration.name, declaration.data_type),
        });
    }
    lines.extend(body_lines(routine, "    "));
    lines.push("END;".to_owned());
    lines.join("\n")
}

// ─────────────────────────────── Agnostic ───────────────────────────────

/// Razeri's own annotated form: ANSI SQL carrying `@razeri:` headers. It is a
/// design artifact — no engine parses it — and it is the honest answer for a
/// grammar that has no spelling here.
fn agnostic(routine: &RoutineSpec) -> String {
    let mut lines = vec![
        "-- @razeri:type procedure".to_owned(),
        format!(
            "-- @razeri:security {}",
            match routine.security {
                RoutineSecurity::Caller => "invoker",
                RoutineSecurity::Owner => "definer",
            }
        ),
    ];
    if let Some(comment) = &routine.comment {
        lines.push(format!("-- @razeri:comment {comment}"));
    }
    lines.push(String::new());
    lines.push(format!("CREATE {}PROCEDURE {}(", or_replace(routine), plain_name(routine)));
    let last = routine.parameters.len().saturating_sub(1);
    for (index, parameter) in routine.parameters.iter().enumerate() {
        let comma = if index == last { "" } else { "," };
        lines.push(format!(
            "    {} {} {}{}",
            direction_keyword(parameter.direction),
            parameter.name,
            parameter.data_type,
            comma
        ));
    }
    lines.push(")".to_owned());
    lines.push("AS $$".to_owned());
    if !declarations(routine).is_empty() {
        lines.push("DECLARE".to_owned());
        for declaration in declarations(routine) {
            lines.push(match &declaration.default {
                Some(default) => {
                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
                }
                None => format!("    {} {};", declaration.name, declaration.data_type),
            });
        }
    }
    lines.push("BEGIN".to_owned());
    lines.extend(body_lines(routine, "    "));
    lines.push("END;".to_owned());
    lines.push("$$;".to_owned());
    lines.join("\n")
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::routine_spec::{RoutineKind, RoutineParameter};

    /// A parameter, built the way a renderer reads one.
    fn parameter(name: &str, data_type: &str, direction: ParameterDirection) -> RoutineParameter {
        RoutineParameter {
            name: name.to_owned(),
            data_type: data_type.to_owned(),
            declared_type: None,
            direction,
            default: None,
        }
    }

    /// The procedure every test below spells: a schema, a description, an input
    /// and an output, one declaration with a value, and two statements.
    fn a_procedure() -> RoutineSpec {
        let mut spec = RoutineSpec::new(RoutineKind::Procedure, "load_customer");
        spec.schema = Some("retail_core".to_owned());
        spec.or_replace = true;
        spec.comment = Some("Loads the day's customers.".to_owned());
        spec.parameters = vec![
            parameter("p_run_id", "BIGINT", ParameterDirection::In),
            parameter("p_loaded", "BIGINT", ParameterDirection::Out),
        ];
        spec.declarations = vec![RoutineDeclaration {
            name: "v_seen".to_owned(),
            data_type: "DECIMAL(19, 4)".to_owned(),
            declared_type: None,
            default: Some("0".to_owned()),
        }];
        spec.body = RoutineBody::Statements(vec![
            "DELETE FROM stage_customer".to_owned(),
            "SELECT COUNT(*) FROM stage_customer INTO v_seen".to_owned(),
        ]);
        spec
    }

    /// PL/pgSQL, whole.
    #[test]
    fn the_pl_pgsql_form_is_a_dollar_quoted_body_and_a_comment_statement() {
        assert_eq!(
            render_procedure_as(ProcedureForm::PlPgSql, &a_procedure()),
            "CREATE OR REPLACE PROCEDURE retail_core.load_customer(\n    IN p_run_id BIGINT,\n    \
             OUT p_loaded BIGINT\n)\nLANGUAGE plpgsql\nSECURITY INVOKER\nAS $$\nDECLARE\n    \
             v_seen DECIMAL(19, 4) := 0;\nBEGIN\n    DELETE FROM stage_customer;\n    SELECT \
             COUNT(*) FROM stage_customer INTO v_seen;\nEND;\n$$;\n\nCOMMENT ON PROCEDURE \
             retail_core.load_customer(BIGINT, BIGINT) IS 'Loads the day''s customers.';"
        );
    }

    /// `MySQL`, whole. A replace is a `DROP` in front, and the body is wrapped
    /// in the client's `DELIMITER` directive rather than in dollar quotes.
    #[test]
    fn the_mysql_form_wears_the_client_delimiter_and_drops_first() {
        let rendered = render_procedure_as(ProcedureForm::MySql, &a_procedure());
        assert!(
            rendered.starts_with(
                "DROP PROCEDURE IF EXISTS retail_core.load_customer;\n\nDELIMITER $$\nCREATE \
                 PROCEDURE retail_core.load_customer("
            ),
            "{rendered}"
        );
        assert!(
            rendered.contains("\n    DECLARE v_seen DECIMAL(19, 4) DEFAULT 0;\n"),
            "{rendered}"
        );
        assert!(rendered.ends_with("END$$\nDELIMITER ;"), "{rendered}");
        assert!(!rendered.contains("$$;"), "dollar quoting is not MySQL's: {rendered}");
    }

    /// T-SQL, whole. The description is its own batch behind an existence check,
    /// because a procedure body here runs to the end of the batch.
    #[test]
    fn the_t_sql_form_brackets_names_and_puts_the_description_in_its_own_batch() {
        let rendered = render_procedure_as(ProcedureForm::TSql, &a_procedure());
        assert!(
            rendered.starts_with(concat!(
                "CREATE OR ALTER PROCEDURE [retail_core].[load_customer]\n",
                "    @p_run_id BIGINT,\n",
                "    @p_loaded BIGINT OUTPUT\n",
                "AS\nBEGIN\n    SET NOCOUNT ON;\n",
                "    DECLARE @v_seen DECIMAL(19, 4) = 0;\n"
            )),
            "{rendered}"
        );
        assert!(rendered.contains("\nEND;\n\nGO\nIF NOT EXISTS"), "{rendered}");
        assert!(rendered.contains("sp_addextendedproperty"), "{rendered}");
    }

    /// Snowflake Scripting returns a status string, because the form declares a
    /// return type whether or not a caller reads one.
    #[test]
    fn the_snowflake_form_declares_a_return_and_hands_one_back() {
        let rendered = render_procedure_as(ProcedureForm::SnowflakeScripting, &a_procedure());
        assert!(rendered.contains("\nRETURNS STRING\nLANGUAGE SQL\nEXECUTE AS CALLER\nAS\n$$\n"));
        assert!(rendered.contains("\n    RETURN 'SUCCESS';\nEND;\n$$;"), "{rendered}");
    }

    /// `GoogleSQL` does not quote the body and carries the description in
    /// `OPTIONS`.
    #[test]
    fn the_google_sql_form_leaves_the_body_unquoted() {
        let rendered = render_procedure_as(ProcedureForm::GoogleSql, &a_procedure());
        assert!(rendered.starts_with(
            "CREATE OR REPLACE PROCEDURE `retail_core`.`load_customer`(p_run_id BIGINT, p_loaded \
             BIGINT)\nOPTIONS (description = 'Loads the day\\'s customers.')\nBEGIN\n"
        ), "{rendered}");
        assert!(!rendered.contains("$$"), "GoogleSQL has no dollar quoting: {rendered}");
    }

    /// **The execution context is the spec's, not a default the renderer keeps
    /// to itself.** Three grammars, three spellings, one fact.
    #[test]
    fn the_execution_context_is_spelled_by_each_grammar() {
        let mut owner = a_procedure();
        owner.security = RoutineSecurity::Owner;
        assert!(render_procedure_as(ProcedureForm::PlPgSql, &owner).contains("SECURITY DEFINER"));
        assert!(render_procedure_as(ProcedureForm::TSql, &owner).contains("WITH EXECUTE AS OWNER"));
        assert!(
            render_procedure_as(ProcedureForm::SnowflakeScripting, &owner)
                .contains("EXECUTE AS OWNER")
        );
    }

    /// **A refusal is a comment and never a statement.** It lands where DDL was
    /// expected, and text that parsed would define something over the real name.
    #[test]
    fn an_unsupported_form_answers_in_the_dialects_own_words_and_spells_no_statement() {
        let rendered = render_procedure_as(
            ProcedureForm::Unsupported("this engine keeps load logic in the application"),
            &a_procedure(),
        );
        assert!(rendered.contains("this engine keeps load logic in the application"));
        assert!(rendered.lines().all(|line| line.starts_with("--")), "{rendered}");
        assert!(!rendered.contains("CREATE"), "{rendered}");
    }

    /// **Asking for your own grammar is not an error.** A dialect that reaches
    /// here with `Custom` gets the agnostic form and a sentence, not a panic.
    #[test]
    fn a_custom_form_answers_with_the_agnostic_form_and_names_the_grammar() {
        let rendered = render_procedure_as(ProcedureForm::Custom("PL/SQL"), &a_procedure());
        assert!(rendered.starts_with("-- You asked for PL/SQL"), "{rendered}");
        assert!(rendered.contains("@razeri:type procedure"), "{rendered}");
    }

    /// **The two forms that are not SQL say so.** Neither engine deploys a
    /// procedure as a statement, and a `CREATE` that pretended otherwise would
    /// fail on the engine rather than in review.
    #[test]
    fn the_two_forms_that_are_not_sql_say_so_rather_than_spelling_a_create() {
        for (form, word) in [
            (ProcedureForm::ClickHouse, "executable program"),
            (ProcedureForm::Databricks, "Python function"),
        ] {
            let rendered = render_procedure_as(form, &a_procedure());
            assert!(rendered.contains(word), "{rendered}");
            assert!(rendered.starts_with("--"), "{rendered}");
        }
    }

    /// A procedure with no parameters and nothing to declare is still a whole
    /// statement, which is the shape most easily got wrong.
    #[test]
    fn a_bare_procedure_is_still_a_whole_statement() {
        let mut spec = RoutineSpec::new(RoutineKind::Procedure, "tick");
        spec.body = RoutineBody::Statements(vec!["UPDATE heartbeat SET seen = 1".to_owned()]);
        assert_eq!(
            render_procedure_as(ProcedureForm::PlPgSql, &spec),
            "CREATE PROCEDURE tick(\n)\nLANGUAGE plpgsql\nSECURITY INVOKER\nAS $$\nBEGIN\n    \
             UPDATE heartbeat SET seen = 1;\nEND;\n$$;"
        );
    }
}