razeri_plugin_contract/routine_render/
procedure.rs

1// Copyright (C) 2026 Gabriel Shea
2// Licensed under either of the Apache License, Version 2.0 or the MIT
3// license, at your option.
4
5//! The stored-procedure grammars.
6
7use super::{
8    indented, inline_parameters, note_above, or_replace, parameter_types, plain_name, quoted_name,
9    quoted_text, refusal_comment, terminated_statements,
10};
11use crate::routine_form::ProcedureForm;
12use crate::routine_spec::{
13    ParameterDirection, RoutineBody, RoutineDeclaration, RoutineSecurity, RoutineSpec,
14};
15
16/// **Spell this procedure in a named grammar.**
17///
18/// The grammar a dialect is nearly the same as, written out, so a dialect that
19/// answers [`ProcedureForm::Custom`] can start from it and say what differs
20/// instead of writing a `CREATE` from nothing.
21///
22/// Parameters:
23/// - `form`: the grammar to spell it in. `Custom` is not an error to pass — it
24///   answers with the agnostic form under a comment naming the grammar, so a
25///   fallback branch that reaches here gets something to read. `ClickHouse` and
26///   `Databricks` do the same, because neither deploys a procedure as SQL;
27/// - `routine`: the procedure, in the shape a `Custom` dialect receives it;
28/// - `describes_procedures`: the dialect's own
29///   [`DdlRenderer::supports_procedure_comment`](crate::DdlRenderer::supports_procedure_comment).
30///   `false` leaves the routine's description out, whatever the form, so an
31///   engine that has no statement for it is never handed one.
32#[must_use]
33pub fn render_procedure_as(
34    form: ProcedureForm,
35    routine: &RoutineSpec,
36    describes_procedures: bool,
37) -> String {
38    // The description is left out here, once, so no grammar below has to ask.
39    let undescribed;
40    let routine = if describes_procedures || routine.comment.is_none() {
41        routine
42    } else {
43        undescribed = RoutineSpec { comment: None, ..routine.clone() };
44        &undescribed
45    };
46    // No wildcard arm, deliberately. The enum is `#[non_exhaustive]` for
47    // dialects outside this crate, and inside it a new form has to be
48    // spelled here or the build stops — which is the point: a form nobody
49    // wrote a grammar for would otherwise render as something it is not.
50    match form {
51        ProcedureForm::PlPgSql => pl_pgsql(routine),
52        ProcedureForm::MySql => my_sql(routine),
53        ProcedureForm::TSql => t_sql(routine),
54        ProcedureForm::SnowflakeScripting => snowflake_scripting(routine),
55        ProcedureForm::GoogleSql => google_sql(routine),
56        ProcedureForm::Agnostic => agnostic(routine),
57        ProcedureForm::ClickHouse => note_above(NOT_A_SQL_GRAMMAR_CLICKHOUSE, &agnostic(routine)),
58        ProcedureForm::Databricks => note_above(NOT_A_SQL_GRAMMAR_DATABRICKS, &agnostic(routine)),
59        ProcedureForm::Custom(grammar) => {
60            note_above(&asked_for_its_own(grammar), &agnostic(routine))
61        }
62        ProcedureForm::Unsupported(orientation) => {
63            refusal_comment("procedure", &routine.name, orientation)
64        }
65    }
66}
67
68/// Why `ClickHouse` has no `CREATE` here.
69const NOT_A_SQL_GRAMMAR_CLICKHOUSE: &str = "ClickHouse deploys a procedure as an executable program with an XML declaration beside it, \
70     not as a statement. The agnostic form below says what the routine is; the deployment is not \
71     something a grammar spells.";
72
73/// Why Databricks has no `CREATE` here.
74const NOT_A_SQL_GRAMMAR_DATABRICKS: &str = "Databricks deploys this procedure as a Python function rather than as a statement. The \
75     agnostic form below says what the routine is; the function is not something a grammar spells.";
76
77/// What `Custom` gets, and why it is not an error to ask.
78fn asked_for_its_own(grammar: &str) -> String {
79    format!(
80        "You asked for {grammar}, which is your dialect's own grammar and not one spelled here. \
81         The agnostic form below is a neutral starting point; the named forms are reachable by \
82         naming one."
83    )
84}
85
86/// `IN` / `OUT` / `INOUT` / `VARIADIC`, the spelling most engines share.
87fn direction_keyword(direction: ParameterDirection) -> &'static str {
88    match direction {
89        ParameterDirection::In => "IN",
90        ParameterDirection::Out => "OUT",
91        ParameterDirection::InOut => "INOUT",
92        ParameterDirection::Variadic => "VARIADIC",
93    }
94}
95
96/// The body's statements, or the block the design framed itself.
97fn body_lines(routine: &RoutineSpec, pad: &str) -> Vec<String> {
98    match &routine.body {
99        RoutineBody::Statements(statements) => terminated_statements(statements, pad, ";"),
100        RoutineBody::Block(block) => vec![block.clone()],
101        RoutineBody::Query(query) => vec![format!("{};", indented(query, pad))],
102        RoutineBody::Expression(expression) => {
103            vec![format!("{};", indented(expression, pad))]
104        }
105    }
106}
107
108/// Every declaration the procedure opens with: the spec's own, in order.
109fn declarations(routine: &RoutineSpec) -> &[RoutineDeclaration] {
110    &routine.declarations
111}
112
113// ───────────────────────────── PL/pgSQL ─────────────────────────────
114
115/// PL/pgSQL: a dollar-quoted body, `DECLARE` above `BEGIN`, and the description
116/// as its own `COMMENT ON` statement.
117fn pl_pgsql(routine: &RoutineSpec) -> String {
118    let mut lines =
119        vec![format!("CREATE {}PROCEDURE {}(", or_replace(routine), plain_name(routine))];
120    let last = routine.parameters.len().saturating_sub(1);
121    for (index, parameter) in routine.parameters.iter().enumerate() {
122        let default =
123            parameter.default.as_ref().map_or_else(String::new, |d| format!(" DEFAULT {d}"));
124        let comma = if index == last { "" } else { "," };
125        lines.push(format!(
126            "    {} {} {}{}{}",
127            direction_keyword(parameter.direction),
128            parameter.name,
129            parameter.data_type,
130            default,
131            comma
132        ));
133    }
134    lines.push(")".to_owned());
135    lines.push("LANGUAGE plpgsql".to_owned());
136    lines.push(
137        match routine.security {
138            RoutineSecurity::Caller => "SECURITY INVOKER",
139            RoutineSecurity::Owner => "SECURITY DEFINER",
140        }
141        .to_owned(),
142    );
143    lines.push("AS $$".to_owned());
144    if !declarations(routine).is_empty() {
145        lines.push("DECLARE".to_owned());
146        for declaration in declarations(routine) {
147            lines.push(match &declaration.default {
148                Some(default) => {
149                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
150                }
151                None => format!("    {} {};", declaration.name, declaration.data_type),
152            });
153        }
154    }
155    lines.push("BEGIN".to_owned());
156    lines.extend(body_lines(routine, "    "));
157    lines.push("END;".to_owned());
158    lines.push("$$;".to_owned());
159    if let Some(comment) = &routine.comment {
160        lines.push(String::new());
161        lines.push(format!(
162            "COMMENT ON PROCEDURE {}({}) IS '{}';",
163            plain_name(routine),
164            parameter_types(routine),
165            quoted_text(comment)
166        ));
167    }
168    lines.join("\n")
169}
170
171// ────────────────────────── MySQL / MariaDB ──────────────────────────
172
173/// `MySQL`'s compound statement. There is no dollar quoting here — the body is
174/// protected by the client's own `DELIMITER` directive, which is what a rendered
175/// `.sql` file applied through a client needs, and a replace is a `DROP` in
176/// front because the engine has no `OR REPLACE` for a procedure.
177fn my_sql(routine: &RoutineSpec) -> String {
178    let mut lines = Vec::new();
179    if routine.or_replace {
180        lines.push(format!("DROP PROCEDURE IF EXISTS {};", plain_name(routine)));
181        lines.push(String::new());
182    }
183    lines.push("DELIMITER $$".to_owned());
184    lines.push(format!("CREATE PROCEDURE {}(", plain_name(routine)));
185    let last = routine.parameters.len().saturating_sub(1);
186    for (index, parameter) in routine.parameters.iter().enumerate() {
187        let comma = if index == last { "" } else { "," };
188        lines.push(format!(
189            "    {} {} {}{}",
190            direction_keyword(parameter.direction),
191            parameter.name,
192            parameter.data_type,
193            comma
194        ));
195    }
196    lines.push(")".to_owned());
197    if let Some(comment) = &routine.comment {
198        lines.push(format!("COMMENT '{}'", quoted_text(comment)));
199    }
200    lines.push("BEGIN".to_owned());
201    for declaration in declarations(routine) {
202        lines.push(match &declaration.default {
203            Some(default) => format!(
204                "    DECLARE {} {} DEFAULT {};",
205                declaration.name, declaration.data_type, default
206            ),
207            None => format!("    DECLARE {} {};", declaration.name, declaration.data_type),
208        });
209    }
210    lines.extend(body_lines(routine, "    "));
211    lines.push("END$$".to_owned());
212    lines.push("DELIMITER ;".to_owned());
213    lines.join("\n")
214}
215
216// ──────────────────────────────── T-SQL ────────────────────────────────
217
218/// T-SQL: bracketed names, `@`-prefixed parameters, `SET NOCOUNT ON` and
219/// `SET XACT_ABORT ON` at the top of the body, and the description in its own
220/// batch behind an existence check.
221///
222/// The batch separator is what makes the description a statement *beside* the
223/// procedure rather than its last line: a procedure body here runs to the end of
224/// the batch, and `sp_addextendedproperty` is not idempotent, so a redeploy
225/// without the check fails on the description.
226fn t_sql(routine: &RoutineSpec) -> String {
227    let create = if routine.or_replace { "CREATE OR ALTER PROCEDURE" } else { "CREATE PROCEDURE" };
228    let mut lines = vec![format!("{create} {}", quoted_name(routine, '[', ']'))];
229    let last = routine.parameters.len().saturating_sub(1);
230    for (index, parameter) in routine.parameters.iter().enumerate() {
231        let default = parameter.default.as_ref().map_or_else(String::new, |d| format!(" = {d}"));
232        let output = match parameter.direction {
233            ParameterDirection::Out | ParameterDirection::InOut => " OUTPUT",
234            _ => "",
235        };
236        let comma = if index == last { "" } else { "," };
237        lines.push(format!(
238            "    @{} {}{}{}{}",
239            parameter.name, parameter.data_type, default, output, comma
240        ));
241    }
242    if routine.security == RoutineSecurity::Owner {
243        lines.push("WITH EXECUTE AS OWNER".to_owned());
244    }
245    lines.push("AS".to_owned());
246    lines.push("BEGIN".to_owned());
247    lines.push("    SET NOCOUNT ON;".to_owned());
248    // Any runtime error rolls back the transaction the call runs in. Without
249    // it most errors end only their own statement, and a `COMMIT` after the
250    // call keeps every row written before the error.
251    lines.push("    SET XACT_ABORT ON;".to_owned());
252    for declaration in declarations(routine) {
253        lines.push(match &declaration.default {
254            Some(default) => {
255                format!(
256                    "    DECLARE @{} {} = {};",
257                    declaration.name, declaration.data_type, default
258                )
259            }
260            None => format!("    DECLARE @{} {};", declaration.name, declaration.data_type),
261        });
262    }
263    if !declarations(routine).is_empty() {
264        lines.push(String::new());
265    }
266    lines.extend(body_lines(routine, "    "));
267    lines.push("END;".to_owned());
268    if let Some(comment) = &routine.comment {
269        let schema = routine.schema.as_deref().unwrap_or("dbo");
270        lines.push(String::new());
271        lines.push("GO".to_owned());
272        lines.push(format!(
273            "IF NOT EXISTS (SELECT 1 FROM sys.extended_properties p JOIN sys.objects o ON \
274             o.object_id = p.major_id JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE \
275             p.name = N'MS_Description' AND p.minor_id = 0 AND s.name = N'{schema}' AND o.name = \
276             N'{}')",
277            routine.name
278        ));
279        lines.push(format!(
280            "    EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'{}', \
281             @level0type = N'SCHEMA', @level0name = N'{schema}', @level1type = N'PROCEDURE', \
282             @level1name = N'{}';",
283            quoted_text(comment),
284            routine.name
285        ));
286    }
287    lines.join("\n")
288}
289
290// ───────────────────────── Snowflake Scripting ─────────────────────────
291
292/// Snowflake Scripting. A procedure here declares a return type even when it
293/// returns nothing a caller reads, so the form returns a status string and the
294/// body ends by handing one back.
295fn snowflake_scripting(routine: &RoutineSpec) -> String {
296    let mut lines = vec![
297        format!(
298            "CREATE {}PROCEDURE {}({})",
299            or_replace(routine),
300            plain_name(routine),
301            inline_parameters(routine)
302        ),
303        "RETURNS STRING".to_owned(),
304        "LANGUAGE SQL".to_owned(),
305        match routine.security {
306            RoutineSecurity::Caller => "EXECUTE AS CALLER",
307            RoutineSecurity::Owner => "EXECUTE AS OWNER",
308        }
309        .to_owned(),
310        "AS".to_owned(),
311        "$$".to_owned(),
312    ];
313    if !declarations(routine).is_empty() {
314        lines.push("DECLARE".to_owned());
315        for declaration in declarations(routine) {
316            lines.push(match &declaration.default {
317                Some(default) => {
318                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
319                }
320                None => format!("    {} {};", declaration.name, declaration.data_type),
321            });
322        }
323    }
324    lines.push("BEGIN".to_owned());
325    lines.extend(body_lines(routine, "    "));
326    lines.push("    RETURN 'SUCCESS';".to_owned());
327    lines.push("END;".to_owned());
328    lines.push("$$;".to_owned());
329    if let Some(comment) = &routine.comment {
330        lines.push(String::new());
331        lines.push(format!(
332            "COMMENT ON PROCEDURE {}({}) IS '{}';",
333            plain_name(routine),
334            parameter_types(routine),
335            quoted_text(comment)
336        ));
337    }
338    lines.join("\n")
339}
340
341// ────────────────────────────── GoogleSQL ──────────────────────────────
342
343/// `GoogleSQL` scripting. The body is not quoted at all, and the description is
344/// an `OPTIONS` clause rather than a statement of its own.
345fn google_sql(routine: &RoutineSpec) -> String {
346    let mut lines = vec![format!(
347        "CREATE {}PROCEDURE {}({})",
348        or_replace(routine),
349        quoted_name(routine, '`', '`'),
350        inline_parameters(routine)
351    )];
352    if let Some(comment) = &routine.comment {
353        lines.push(format!("OPTIONS (description = '{}')", comment.replace('\'', "\\'")));
354    }
355    lines.push("BEGIN".to_owned());
356    for declaration in declarations(routine) {
357        lines.push(match &declaration.default {
358            Some(default) => format!(
359                "    DECLARE {} {} DEFAULT {};",
360                declaration.name, declaration.data_type, default
361            ),
362            None => format!("    DECLARE {} {};", declaration.name, declaration.data_type),
363        });
364    }
365    lines.extend(body_lines(routine, "    "));
366    lines.push("END;".to_owned());
367    lines.join("\n")
368}
369
370// ─────────────────────────────── Agnostic ───────────────────────────────
371
372/// Razeri's own annotated form: ANSI SQL carrying `@razeri:` headers. It is a
373/// design artifact — no engine parses it — and it is the honest answer for a
374/// grammar that has no spelling here.
375fn agnostic(routine: &RoutineSpec) -> String {
376    let mut lines = vec![
377        crate::comment::block_comment("@razeri:type procedure"),
378        crate::comment::block_comment(&format!(
379            "@razeri:security {}",
380            match routine.security {
381                RoutineSecurity::Caller => "invoker",
382                RoutineSecurity::Owner => "definer",
383            }
384        )),
385    ];
386    if let Some(comment) = &routine.comment {
387        lines.push(crate::comment::block_comment(&format!("@razeri:comment {comment}")));
388    }
389    lines.push(String::new());
390    lines.push(format!("CREATE {}PROCEDURE {}(", or_replace(routine), plain_name(routine)));
391    let last = routine.parameters.len().saturating_sub(1);
392    for (index, parameter) in routine.parameters.iter().enumerate() {
393        let comma = if index == last { "" } else { "," };
394        lines.push(format!(
395            "    {} {} {}{}",
396            direction_keyword(parameter.direction),
397            parameter.name,
398            parameter.data_type,
399            comma
400        ));
401    }
402    lines.push(")".to_owned());
403    lines.push("AS $$".to_owned());
404    if !declarations(routine).is_empty() {
405        lines.push("DECLARE".to_owned());
406        for declaration in declarations(routine) {
407            lines.push(match &declaration.default {
408                Some(default) => {
409                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
410                }
411                None => format!("    {} {};", declaration.name, declaration.data_type),
412            });
413        }
414    }
415    lines.push("BEGIN".to_owned());
416    lines.extend(body_lines(routine, "    "));
417    lines.push("END;".to_owned());
418    lines.push("$$;".to_owned());
419    lines.join("\n")
420}
421
422#[cfg(test)]
423mod tests {
424    use super::*;
425    use crate::routine_spec::{RoutineKind, RoutineParameter};
426
427    /// A parameter, built the way a renderer reads one.
428    fn parameter(name: &str, data_type: &str, direction: ParameterDirection) -> RoutineParameter {
429        RoutineParameter {
430            name: name.to_owned(),
431            data_type: data_type.to_owned(),
432            declared_type: None,
433            direction,
434            default: None,
435        }
436    }
437
438    /// The procedure every test below spells: a schema, a description, an input
439    /// and an output, one declaration with a value, and two statements.
440    fn a_procedure() -> RoutineSpec {
441        let mut spec = RoutineSpec::new(RoutineKind::Procedure, "load_customer");
442        spec.schema = Some("retail_core".to_owned());
443        spec.or_replace = true;
444        spec.comment = Some("Loads the day's customers.".to_owned());
445        spec.parameters = vec![
446            parameter("p_run_id", "BIGINT", ParameterDirection::In),
447            parameter("p_loaded", "BIGINT", ParameterDirection::Out),
448        ];
449        spec.declarations = vec![RoutineDeclaration {
450            name: "v_seen".to_owned(),
451            data_type: "DECIMAL(19, 4)".to_owned(),
452            declared_type: None,
453            default: Some("0".to_owned()),
454        }];
455        spec.body = RoutineBody::Statements(vec![
456            "DELETE FROM stage_customer".to_owned(),
457            "SELECT COUNT(*) FROM stage_customer INTO v_seen".to_owned(),
458        ]);
459        spec
460    }
461
462    /// PL/pgSQL, whole.
463    #[test]
464    fn the_pl_pgsql_form_is_a_dollar_quoted_body_and_a_comment_statement() {
465        assert_eq!(
466            render_procedure_as(ProcedureForm::PlPgSql, &a_procedure(), true),
467            "CREATE OR REPLACE PROCEDURE retail_core.load_customer(\n    IN p_run_id BIGINT,\n    \
468             OUT p_loaded BIGINT\n)\nLANGUAGE plpgsql\nSECURITY INVOKER\nAS $$\nDECLARE\n    \
469             v_seen DECIMAL(19, 4) := 0;\nBEGIN\n    DELETE FROM stage_customer;\n    SELECT \
470             COUNT(*) FROM stage_customer INTO v_seen;\nEND;\n$$;\n\nCOMMENT ON PROCEDURE \
471             retail_core.load_customer(BIGINT, BIGINT) IS 'Loads the day''s customers.';"
472        );
473    }
474
475    /// `MySQL`, whole. A replace is a `DROP` in front, and the body is wrapped
476    /// in the client's `DELIMITER` directive rather than in dollar quotes.
477    #[test]
478    fn the_mysql_form_wears_the_client_delimiter_and_drops_first() {
479        let rendered = render_procedure_as(ProcedureForm::MySql, &a_procedure(), true);
480        assert!(
481            rendered.starts_with(
482                "DROP PROCEDURE IF EXISTS retail_core.load_customer;\n\nDELIMITER $$\nCREATE \
483                 PROCEDURE retail_core.load_customer("
484            ),
485            "{rendered}"
486        );
487        assert!(
488            rendered.contains("\n    DECLARE v_seen DECIMAL(19, 4) DEFAULT 0;\n"),
489            "{rendered}"
490        );
491        assert!(rendered.ends_with("END$$\nDELIMITER ;"), "{rendered}");
492        assert!(!rendered.contains("$$;"), "dollar quoting is not MySQL's: {rendered}");
493    }
494
495    /// T-SQL, whole. The description is its own batch behind an existence check,
496    /// because a procedure body here runs to the end of the batch.
497    #[test]
498    fn the_t_sql_form_brackets_names_and_puts_the_description_in_its_own_batch() {
499        let rendered = render_procedure_as(ProcedureForm::TSql, &a_procedure(), true);
500        assert!(
501            rendered.starts_with(concat!(
502                "CREATE OR ALTER PROCEDURE [retail_core].[load_customer]\n",
503                "    @p_run_id BIGINT,\n",
504                "    @p_loaded BIGINT OUTPUT\n",
505                "AS\nBEGIN\n    SET NOCOUNT ON;\n    SET XACT_ABORT ON;\n",
506                "    DECLARE @v_seen DECIMAL(19, 4) = 0;\n"
507            )),
508            "{rendered}"
509        );
510        assert!(rendered.contains("\nEND;\n\nGO\nIF NOT EXISTS"), "{rendered}");
511        assert!(rendered.contains("sp_addextendedproperty"), "{rendered}");
512    }
513
514    /// Snowflake Scripting returns a status string, because the form declares a
515    /// return type whether or not a caller reads one.
516    #[test]
517    fn the_snowflake_form_declares_a_return_and_hands_one_back() {
518        let rendered = render_procedure_as(ProcedureForm::SnowflakeScripting, &a_procedure(), true);
519        assert!(rendered.contains("\nRETURNS STRING\nLANGUAGE SQL\nEXECUTE AS CALLER\nAS\n$$\n"));
520        assert!(rendered.contains("\n    RETURN 'SUCCESS';\nEND;\n$$;"), "{rendered}");
521    }
522
523    /// `GoogleSQL` does not quote the body and carries the description in
524    /// `OPTIONS`.
525    #[test]
526    fn the_google_sql_form_leaves_the_body_unquoted() {
527        let rendered = render_procedure_as(ProcedureForm::GoogleSql, &a_procedure(), true);
528        assert!(rendered.starts_with(
529            "CREATE OR REPLACE PROCEDURE `retail_core`.`load_customer`(p_run_id BIGINT, p_loaded \
530             BIGINT)\nOPTIONS (description = 'Loads the day\\'s customers.')\nBEGIN\n"
531        ), "{rendered}");
532        assert!(!rendered.contains("$$"), "GoogleSQL has no dollar quoting: {rendered}");
533    }
534
535    /// **The execution context is the spec's, not a default the renderer keeps
536    /// to itself.** Three grammars, three spellings, one fact.
537    #[test]
538    fn the_execution_context_is_spelled_by_each_grammar() {
539        let mut owner = a_procedure();
540        owner.security = RoutineSecurity::Owner;
541        assert!(
542            render_procedure_as(ProcedureForm::PlPgSql, &owner, true).contains("SECURITY DEFINER")
543        );
544        assert!(
545            render_procedure_as(ProcedureForm::TSql, &owner, true)
546                .contains("WITH EXECUTE AS OWNER")
547        );
548        assert!(
549            render_procedure_as(ProcedureForm::SnowflakeScripting, &owner, true)
550                .contains("EXECUTE AS OWNER")
551        );
552    }
553
554    /// **A refusal is a comment and never a statement.** It lands where DDL was
555    /// expected, and text that parsed would define something over the real name.
556    #[test]
557    fn an_unsupported_form_answers_in_the_dialects_own_words_and_spells_no_statement() {
558        let rendered = render_procedure_as(
559            ProcedureForm::Unsupported("this engine keeps load logic in the application"),
560            &a_procedure(),
561            true,
562        );
563        assert!(rendered.contains("this engine keeps load logic in the application"));
564        assert!(
565            crate::comment::comment_line_flags(&rendered).iter().all(|note| *note),
566            "{rendered}"
567        );
568        assert!(!rendered.contains("CREATE"), "{rendered}");
569    }
570
571    /// **Asking for your own grammar is not an error.** A dialect that reaches
572    /// here with `Custom` gets the agnostic form and a sentence, not a panic.
573    #[test]
574    fn a_custom_form_answers_with_the_agnostic_form_and_names_the_grammar() {
575        let rendered = render_procedure_as(ProcedureForm::Custom("PL/SQL"), &a_procedure(), true);
576        assert!(rendered.starts_with("/* You asked for PL/SQL"), "{rendered}");
577        assert!(rendered.contains("@razeri:type procedure"), "{rendered}");
578    }
579
580    /// **The two forms that are not SQL say so.** Neither engine deploys a
581    /// procedure as a statement, and a `CREATE` that pretended otherwise would
582    /// fail on the engine rather than in review.
583    #[test]
584    fn the_two_forms_that_are_not_sql_say_so_rather_than_spelling_a_create() {
585        for (form, word) in [
586            (ProcedureForm::ClickHouse, "executable program"),
587            (ProcedureForm::Databricks, "Python function"),
588        ] {
589            let rendered = render_procedure_as(form, &a_procedure(), true);
590            assert!(rendered.contains(word), "{rendered}");
591            assert!(rendered.starts_with("/* "), "{rendered}");
592        }
593    }
594
595    /// A procedure with no parameters and nothing to declare is still a whole
596    /// statement, which is the shape most easily got wrong.
597    #[test]
598    fn a_bare_procedure_is_still_a_whole_statement() {
599        let mut spec = RoutineSpec::new(RoutineKind::Procedure, "tick");
600        spec.body = RoutineBody::Statements(vec!["UPDATE heartbeat SET seen = 1".to_owned()]);
601        assert_eq!(
602            render_procedure_as(ProcedureForm::PlPgSql, &spec, true),
603            "CREATE PROCEDURE tick(\n)\nLANGUAGE plpgsql\nSECURITY INVOKER\nAS $$\nBEGIN\n    \
604             UPDATE heartbeat SET seen = 1;\nEND;\n$$;"
605        );
606    }
607
608    /// **An engine that has no statement for a description is never handed
609    /// one.** `COMMENT ON PROCEDURE` is refused outright where the dialect says
610    /// so, `CockroachDB` 24.3 and `Redshift` among them, and a spec carrying a
611    /// description used to bring the statement along whatever the dialect said.
612    #[test]
613    fn a_procedure_for_an_engine_without_descriptions_carries_none() {
614        let spec = a_procedure();
615        assert!(spec.comment.is_some(), "the fixture carries a description");
616        for form in [
617            ProcedureForm::PlPgSql,
618            ProcedureForm::SnowflakeScripting,
619            ProcedureForm::MySql,
620            ProcedureForm::TSql,
621            ProcedureForm::GoogleSql,
622        ] {
623            let declined = render_procedure_as(form, &spec, false);
624            assert!(!declined.contains("COMMENT ON PROCEDURE"), "{form:?}: {declined}");
625            assert!(
626                !declined.contains("Loads the day"),
627                "{form:?} kept the description: {declined}"
628            );
629            assert!(declined.contains("load_customer"), "{form:?} still writes the procedure");
630            let described = render_procedure_as(form, &spec, true);
631            assert!(
632                described.contains("Loads the day"),
633                "{form:?} lost the description: {described}"
634            );
635        }
636    }
637}
razeri_plugin_contract/routine_render/procedure.rs

razeri_plugin_contract/routine_render/
procedure.rs
