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` at the top
219/// of the body, and the description in its own batch behind an existence check.
220///
221/// The batch separator is what makes the description a statement *beside* the
222/// procedure rather than its last line: a procedure body here runs to the end of
223/// the batch, and `sp_addextendedproperty` is not idempotent, so a redeploy
224/// without the check fails on the description.
225fn t_sql(routine: &RoutineSpec) -> String {
226    let create = if routine.or_replace { "CREATE OR ALTER PROCEDURE" } else { "CREATE PROCEDURE" };
227    let mut lines = vec![format!("{create} {}", quoted_name(routine, '[', ']'))];
228    let last = routine.parameters.len().saturating_sub(1);
229    for (index, parameter) in routine.parameters.iter().enumerate() {
230        let default = parameter.default.as_ref().map_or_else(String::new, |d| format!(" = {d}"));
231        let output = match parameter.direction {
232            ParameterDirection::Out | ParameterDirection::InOut => " OUTPUT",
233            _ => "",
234        };
235        let comma = if index == last { "" } else { "," };
236        lines.push(format!(
237            "    @{} {}{}{}{}",
238            parameter.name, parameter.data_type, default, output, comma
239        ));
240    }
241    if routine.security == RoutineSecurity::Owner {
242        lines.push("WITH EXECUTE AS OWNER".to_owned());
243    }
244    lines.push("AS".to_owned());
245    lines.push("BEGIN".to_owned());
246    lines.push("    SET NOCOUNT ON;".to_owned());
247    for declaration in declarations(routine) {
248        lines.push(match &declaration.default {
249            Some(default) => {
250                format!(
251                    "    DECLARE @{} {} = {};",
252                    declaration.name, declaration.data_type, default
253                )
254            }
255            None => format!("    DECLARE @{} {};", declaration.name, declaration.data_type),
256        });
257    }
258    if !declarations(routine).is_empty() {
259        lines.push(String::new());
260    }
261    lines.extend(body_lines(routine, "    "));
262    lines.push("END;".to_owned());
263    if let Some(comment) = &routine.comment {
264        let schema = routine.schema.as_deref().unwrap_or("dbo");
265        lines.push(String::new());
266        lines.push("GO".to_owned());
267        lines.push(format!(
268            "IF NOT EXISTS (SELECT 1 FROM sys.extended_properties p JOIN sys.objects o ON \
269             o.object_id = p.major_id JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE \
270             p.name = N'MS_Description' AND p.minor_id = 0 AND s.name = N'{schema}' AND o.name = \
271             N'{}')",
272            routine.name
273        ));
274        lines.push(format!(
275            "    EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'{}', \
276             @level0type = N'SCHEMA', @level0name = N'{schema}', @level1type = N'PROCEDURE', \
277             @level1name = N'{}';",
278            quoted_text(comment),
279            routine.name
280        ));
281    }
282    lines.join("\n")
283}
284
285// ───────────────────────── Snowflake Scripting ─────────────────────────
286
287/// Snowflake Scripting. A procedure here declares a return type even when it
288/// returns nothing a caller reads, so the form returns a status string and the
289/// body ends by handing one back.
290fn snowflake_scripting(routine: &RoutineSpec) -> String {
291    let mut lines = vec![
292        format!(
293            "CREATE {}PROCEDURE {}({})",
294            or_replace(routine),
295            plain_name(routine),
296            inline_parameters(routine)
297        ),
298        "RETURNS STRING".to_owned(),
299        "LANGUAGE SQL".to_owned(),
300        match routine.security {
301            RoutineSecurity::Caller => "EXECUTE AS CALLER",
302            RoutineSecurity::Owner => "EXECUTE AS OWNER",
303        }
304        .to_owned(),
305        "AS".to_owned(),
306        "$$".to_owned(),
307    ];
308    if !declarations(routine).is_empty() {
309        lines.push("DECLARE".to_owned());
310        for declaration in declarations(routine) {
311            lines.push(match &declaration.default {
312                Some(default) => {
313                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
314                }
315                None => format!("    {} {};", declaration.name, declaration.data_type),
316            });
317        }
318    }
319    lines.push("BEGIN".to_owned());
320    lines.extend(body_lines(routine, "    "));
321    lines.push("    RETURN 'SUCCESS';".to_owned());
322    lines.push("END;".to_owned());
323    lines.push("$$;".to_owned());
324    if let Some(comment) = &routine.comment {
325        lines.push(String::new());
326        lines.push(format!(
327            "COMMENT ON PROCEDURE {}({}) IS '{}';",
328            plain_name(routine),
329            parameter_types(routine),
330            quoted_text(comment)
331        ));
332    }
333    lines.join("\n")
334}
335
336// ────────────────────────────── GoogleSQL ──────────────────────────────
337
338/// `GoogleSQL` scripting. The body is not quoted at all, and the description is
339/// an `OPTIONS` clause rather than a statement of its own.
340fn google_sql(routine: &RoutineSpec) -> String {
341    let mut lines = vec![format!(
342        "CREATE {}PROCEDURE {}({})",
343        or_replace(routine),
344        quoted_name(routine, '`', '`'),
345        inline_parameters(routine)
346    )];
347    if let Some(comment) = &routine.comment {
348        lines.push(format!("OPTIONS (description = '{}')", comment.replace('\'', "\\'")));
349    }
350    lines.push("BEGIN".to_owned());
351    for declaration in declarations(routine) {
352        lines.push(match &declaration.default {
353            Some(default) => format!(
354                "    DECLARE {} {} DEFAULT {};",
355                declaration.name, declaration.data_type, default
356            ),
357            None => format!("    DECLARE {} {};", declaration.name, declaration.data_type),
358        });
359    }
360    lines.extend(body_lines(routine, "    "));
361    lines.push("END;".to_owned());
362    lines.join("\n")
363}
364
365// ─────────────────────────────── Agnostic ───────────────────────────────
366
367/// Razeri's own annotated form: ANSI SQL carrying `@razeri:` headers. It is a
368/// design artifact — no engine parses it — and it is the honest answer for a
369/// grammar that has no spelling here.
370fn agnostic(routine: &RoutineSpec) -> String {
371    let mut lines = vec![
372        "-- @razeri:type procedure".to_owned(),
373        format!(
374            "-- @razeri:security {}",
375            match routine.security {
376                RoutineSecurity::Caller => "invoker",
377                RoutineSecurity::Owner => "definer",
378            }
379        ),
380    ];
381    if let Some(comment) = &routine.comment {
382        lines.push(format!("-- @razeri:comment {comment}"));
383    }
384    lines.push(String::new());
385    lines.push(format!("CREATE {}PROCEDURE {}(", or_replace(routine), plain_name(routine)));
386    let last = routine.parameters.len().saturating_sub(1);
387    for (index, parameter) in routine.parameters.iter().enumerate() {
388        let comma = if index == last { "" } else { "," };
389        lines.push(format!(
390            "    {} {} {}{}",
391            direction_keyword(parameter.direction),
392            parameter.name,
393            parameter.data_type,
394            comma
395        ));
396    }
397    lines.push(")".to_owned());
398    lines.push("AS $$".to_owned());
399    if !declarations(routine).is_empty() {
400        lines.push("DECLARE".to_owned());
401        for declaration in declarations(routine) {
402            lines.push(match &declaration.default {
403                Some(default) => {
404                    format!("    {} {} := {};", declaration.name, declaration.data_type, default)
405                }
406                None => format!("    {} {};", declaration.name, declaration.data_type),
407            });
408        }
409    }
410    lines.push("BEGIN".to_owned());
411    lines.extend(body_lines(routine, "    "));
412    lines.push("END;".to_owned());
413    lines.push("$$;".to_owned());
414    lines.join("\n")
415}
416
417#[cfg(test)]
418mod tests {
419    use super::*;
420    use crate::routine_spec::{RoutineKind, RoutineParameter};
421
422    /// A parameter, built the way a renderer reads one.
423    fn parameter(name: &str, data_type: &str, direction: ParameterDirection) -> RoutineParameter {
424        RoutineParameter {
425            name: name.to_owned(),
426            data_type: data_type.to_owned(),
427            declared_type: None,
428            direction,
429            default: None,
430        }
431    }
432
433    /// The procedure every test below spells: a schema, a description, an input
434    /// and an output, one declaration with a value, and two statements.
435    fn a_procedure() -> RoutineSpec {
436        let mut spec = RoutineSpec::new(RoutineKind::Procedure, "load_customer");
437        spec.schema = Some("retail_core".to_owned());
438        spec.or_replace = true;
439        spec.comment = Some("Loads the day's customers.".to_owned());
440        spec.parameters = vec![
441            parameter("p_run_id", "BIGINT", ParameterDirection::In),
442            parameter("p_loaded", "BIGINT", ParameterDirection::Out),
443        ];
444        spec.declarations = vec![RoutineDeclaration {
445            name: "v_seen".to_owned(),
446            data_type: "DECIMAL(19, 4)".to_owned(),
447            declared_type: None,
448            default: Some("0".to_owned()),
449        }];
450        spec.body = RoutineBody::Statements(vec![
451            "DELETE FROM stage_customer".to_owned(),
452            "SELECT COUNT(*) FROM stage_customer INTO v_seen".to_owned(),
453        ]);
454        spec
455    }
456
457    /// PL/pgSQL, whole.
458    #[test]
459    fn the_pl_pgsql_form_is_a_dollar_quoted_body_and_a_comment_statement() {
460        assert_eq!(
461            render_procedure_as(ProcedureForm::PlPgSql, &a_procedure(), true),
462            "CREATE OR REPLACE PROCEDURE retail_core.load_customer(\n    IN p_run_id BIGINT,\n    \
463             OUT p_loaded BIGINT\n)\nLANGUAGE plpgsql\nSECURITY INVOKER\nAS $$\nDECLARE\n    \
464             v_seen DECIMAL(19, 4) := 0;\nBEGIN\n    DELETE FROM stage_customer;\n    SELECT \
465             COUNT(*) FROM stage_customer INTO v_seen;\nEND;\n$$;\n\nCOMMENT ON PROCEDURE \
466             retail_core.load_customer(BIGINT, BIGINT) IS 'Loads the day''s customers.';"
467        );
468    }
469
470    /// `MySQL`, whole. A replace is a `DROP` in front, and the body is wrapped
471    /// in the client's `DELIMITER` directive rather than in dollar quotes.
472    #[test]
473    fn the_mysql_form_wears_the_client_delimiter_and_drops_first() {
474        let rendered = render_procedure_as(ProcedureForm::MySql, &a_procedure(), true);
475        assert!(
476            rendered.starts_with(
477                "DROP PROCEDURE IF EXISTS retail_core.load_customer;\n\nDELIMITER $$\nCREATE \
478                 PROCEDURE retail_core.load_customer("
479            ),
480            "{rendered}"
481        );
482        assert!(
483            rendered.contains("\n    DECLARE v_seen DECIMAL(19, 4) DEFAULT 0;\n"),
484            "{rendered}"
485        );
486        assert!(rendered.ends_with("END$$\nDELIMITER ;"), "{rendered}");
487        assert!(!rendered.contains("$$;"), "dollar quoting is not MySQL's: {rendered}");
488    }
489
490    /// T-SQL, whole. The description is its own batch behind an existence check,
491    /// because a procedure body here runs to the end of the batch.
492    #[test]
493    fn the_t_sql_form_brackets_names_and_puts_the_description_in_its_own_batch() {
494        let rendered = render_procedure_as(ProcedureForm::TSql, &a_procedure(), true);
495        assert!(
496            rendered.starts_with(concat!(
497                "CREATE OR ALTER PROCEDURE [retail_core].[load_customer]\n",
498                "    @p_run_id BIGINT,\n",
499                "    @p_loaded BIGINT OUTPUT\n",
500                "AS\nBEGIN\n    SET NOCOUNT ON;\n",
501                "    DECLARE @v_seen DECIMAL(19, 4) = 0;\n"
502            )),
503            "{rendered}"
504        );
505        assert!(rendered.contains("\nEND;\n\nGO\nIF NOT EXISTS"), "{rendered}");
506        assert!(rendered.contains("sp_addextendedproperty"), "{rendered}");
507    }
508
509    /// Snowflake Scripting returns a status string, because the form declares a
510    /// return type whether or not a caller reads one.
511    #[test]
512    fn the_snowflake_form_declares_a_return_and_hands_one_back() {
513        let rendered = render_procedure_as(ProcedureForm::SnowflakeScripting, &a_procedure(), true);
514        assert!(rendered.contains("\nRETURNS STRING\nLANGUAGE SQL\nEXECUTE AS CALLER\nAS\n$$\n"));
515        assert!(rendered.contains("\n    RETURN 'SUCCESS';\nEND;\n$$;"), "{rendered}");
516    }
517
518    /// `GoogleSQL` does not quote the body and carries the description in
519    /// `OPTIONS`.
520    #[test]
521    fn the_google_sql_form_leaves_the_body_unquoted() {
522        let rendered = render_procedure_as(ProcedureForm::GoogleSql, &a_procedure(), true);
523        assert!(rendered.starts_with(
524            "CREATE OR REPLACE PROCEDURE `retail_core`.`load_customer`(p_run_id BIGINT, p_loaded \
525             BIGINT)\nOPTIONS (description = 'Loads the day\\'s customers.')\nBEGIN\n"
526        ), "{rendered}");
527        assert!(!rendered.contains("$$"), "GoogleSQL has no dollar quoting: {rendered}");
528    }
529
530    /// **The execution context is the spec's, not a default the renderer keeps
531    /// to itself.** Three grammars, three spellings, one fact.
532    #[test]
533    fn the_execution_context_is_spelled_by_each_grammar() {
534        let mut owner = a_procedure();
535        owner.security = RoutineSecurity::Owner;
536        assert!(
537            render_procedure_as(ProcedureForm::PlPgSql, &owner, true).contains("SECURITY DEFINER")
538        );
539        assert!(
540            render_procedure_as(ProcedureForm::TSql, &owner, true)
541                .contains("WITH EXECUTE AS OWNER")
542        );
543        assert!(
544            render_procedure_as(ProcedureForm::SnowflakeScripting, &owner, true)
545                .contains("EXECUTE AS OWNER")
546        );
547    }
548
549    /// **A refusal is a comment and never a statement.** It lands where DDL was
550    /// expected, and text that parsed would define something over the real name.
551    #[test]
552    fn an_unsupported_form_answers_in_the_dialects_own_words_and_spells_no_statement() {
553        let rendered = render_procedure_as(
554            ProcedureForm::Unsupported("this engine keeps load logic in the application"),
555            &a_procedure(),
556            true,
557        );
558        assert!(rendered.contains("this engine keeps load logic in the application"));
559        assert!(rendered.lines().all(|line| line.starts_with("--")), "{rendered}");
560        assert!(!rendered.contains("CREATE"), "{rendered}");
561    }
562
563    /// **Asking for your own grammar is not an error.** A dialect that reaches
564    /// here with `Custom` gets the agnostic form and a sentence, not a panic.
565    #[test]
566    fn a_custom_form_answers_with_the_agnostic_form_and_names_the_grammar() {
567        let rendered = render_procedure_as(ProcedureForm::Custom("PL/SQL"), &a_procedure(), true);
568        assert!(rendered.starts_with("-- You asked for PL/SQL"), "{rendered}");
569        assert!(rendered.contains("@razeri:type procedure"), "{rendered}");
570    }
571
572    /// **The two forms that are not SQL say so.** Neither engine deploys a
573    /// procedure as a statement, and a `CREATE` that pretended otherwise would
574    /// fail on the engine rather than in review.
575    #[test]
576    fn the_two_forms_that_are_not_sql_say_so_rather_than_spelling_a_create() {
577        for (form, word) in [
578            (ProcedureForm::ClickHouse, "executable program"),
579            (ProcedureForm::Databricks, "Python function"),
580        ] {
581            let rendered = render_procedure_as(form, &a_procedure(), true);
582            assert!(rendered.contains(word), "{rendered}");
583            assert!(rendered.starts_with("--"), "{rendered}");
584        }
585    }
586
587    /// A procedure with no parameters and nothing to declare is still a whole
588    /// statement, which is the shape most easily got wrong.
589    #[test]
590    fn a_bare_procedure_is_still_a_whole_statement() {
591        let mut spec = RoutineSpec::new(RoutineKind::Procedure, "tick");
592        spec.body = RoutineBody::Statements(vec!["UPDATE heartbeat SET seen = 1".to_owned()]);
593        assert_eq!(
594            render_procedure_as(ProcedureForm::PlPgSql, &spec, true),
595            "CREATE PROCEDURE tick(\n)\nLANGUAGE plpgsql\nSECURITY INVOKER\nAS $$\nBEGIN\n    \
596             UPDATE heartbeat SET seen = 1;\nEND;\n$$;"
597        );
598    }
599
600    /// **An engine that has no statement for a description is never handed
601    /// one.** `COMMENT ON PROCEDURE` is refused outright where the dialect says
602    /// so, `CockroachDB` 24.3 and `Redshift` among them, and a spec carrying a
603    /// description used to bring the statement along whatever the dialect said.
604    #[test]
605    fn a_procedure_for_an_engine_without_descriptions_carries_none() {
606        let spec = a_procedure();
607        assert!(spec.comment.is_some(), "the fixture carries a description");
608        for form in [
609            ProcedureForm::PlPgSql,
610            ProcedureForm::SnowflakeScripting,
611            ProcedureForm::MySql,
612            ProcedureForm::TSql,
613            ProcedureForm::GoogleSql,
614        ] {
615            let declined = render_procedure_as(form, &spec, false);
616            assert!(!declined.contains("COMMENT ON PROCEDURE"), "{form:?}: {declined}");
617            assert!(
618                !declined.contains("Loads the day"),
619                "{form:?} kept the description: {declined}"
620            );
621            assert!(declined.contains("load_customer"), "{form:?} still writes the procedure");
622            let described = render_procedure_as(form, &spec, true);
623            assert!(
624                described.contains("Loads the day"),
625                "{form:?} lost the description: {described}"
626            );
627        }
628    }
629}
razeri_plugin_contract/routine_render/procedure.rs

razeri_plugin_contract/routine_render/
procedure.rs
