ferrule_core/
explain.rs

1use ferrule_sql::{Backend, SqlError};
2
3/// Tag indicating the expected output format of an EXPLAIN plan.
4#[derive(Debug, Clone, Copy, PartialEq, Eq)]
5pub enum ExplainOutput {
6    Json,
7    Text,
8    Xml,
9}
10
11/// Return the EXPLAIN-wrapped SQL, the expected output format, and whether the
12/// wrapped SQL contains multiple statements (which requires `execute_multi`).
13///
14/// When `analyze` is `true` and the statement is non-modifying, the
15/// backend-specific "actual execution" variant is used. For modifying
16/// statements `ANALYZE` is stripped to avoid side effects.
17pub fn explain_sql(
18    sql: &str,
19    backend: Backend,
20    analyze: bool,
21) -> Result<(String, ExplainOutput, bool), SqlError> {
22    let trimmed = sql.trim();
23    if trimmed.is_empty() {
24        return Err(SqlError::QueryFailed("Empty SQL for EXPLAIN".into()));
25    }
26
27    let modifying = is_modifying(trimmed);
28    let safe = modifying || !analyze;
29
30    match backend {
31        #[cfg(feature = "postgres")]
32        Backend::Postgres => {
33            if safe {
34                Ok((
35                    format!("EXPLAIN (FORMAT JSON, COSTS) {}", trimmed),
36                    ExplainOutput::Json,
37                    false,
38                ))
39            } else {
40                Ok((
41                    format!(
42                        "EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS, TIMING, COSTS) {}",
43                        trimmed
44                    ),
45                    ExplainOutput::Json,
46                    false,
47                ))
48            }
49        }
50        #[cfg(feature = "mysql")]
51        Backend::MySql => {
52            // MySQL 8 EXPLAIN on DML does not execute; safe and analyze are the same.
53            Ok((
54                format!("EXPLAIN FORMAT=JSON {}", trimmed),
55                ExplainOutput::Json,
56                false,
57            ))
58        }
59        #[cfg(feature = "sqlite")]
60        Backend::Sqlite => Ok((
61            format!("EXPLAIN QUERY PLAN {}", trimmed),
62            ExplainOutput::Text,
63            false,
64        )),
65        #[cfg(feature = "mssql")]
66        Backend::MsSql => {
67            if safe {
68                Ok((
69                    format!("SET SHOWPLAN_XML ON; {}; SET SHOWPLAN_XML OFF;", trimmed),
70                    ExplainOutput::Xml,
71                    true,
72                ))
73            } else {
74                Ok((
75                    format!(
76                        "SET STATISTICS XML ON; {}; SET STATISTICS XML OFF;",
77                        trimmed
78                    ),
79                    ExplainOutput::Xml,
80                    true,
81                ))
82            }
83        }
84        #[cfg(feature = "oracle")]
85        Backend::Oracle => {
86            let display_opts = if safe { "" } else { "'','','ALLSTATS LAST'" };
87            let wrapped = format!(
88                "EXPLAIN PLAN FOR {}; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('','',{}));",
89                trimmed, display_opts
90            );
91            Ok((wrapped, ExplainOutput::Text, true))
92        }
93        #[allow(unreachable_patterns)]
94        _ => {
95            // Fallback for any backend that gets here without a specific impl.
96            Ok((format!("EXPLAIN {}", trimmed), ExplainOutput::Text, false))
97        }
98    }
99}
100
101/// Detect whether a SQL statement is modifying (DML/DDL).
102///
103/// A statement is considered modifying if a DML/DDL keyword appears at
104/// top-level (parenthesis depth ≤ 0). This correctly handles `WITH`
105/// CTEs that are followed by `INSERT` / `UPDATE` / `DELETE` / `MERGE`
106/// — the bug that the previous first-token-only check missed.
107pub fn is_modifying(sql: &str) -> bool {
108    let mut parenthesis_depth = 0i32;
109    for token in sql.split_whitespace() {
110        for ch in token.chars() {
111            if ch == '(' {
112                parenthesis_depth += 1;
113            } else if ch == ')' {
114                parenthesis_depth -= 1;
115            }
116        }
117        if parenthesis_depth <= 0 {
118            let upper = token.to_ascii_uppercase();
119            if matches!(
120                upper.as_str(),
121                "INSERT" | "UPDATE" | "DELETE" | "CREATE" | "DROP" | "ALTER" | "TRUNCATE" | "MERGE"
122            ) {
123                return true;
124            }
125        }
126    }
127    false
128}
129
130#[cfg(test)]
131mod tests {
132    use super::*;
133
134    #[test]
135    fn test_is_modifying_insert() {
136        assert!(is_modifying("INSERT INTO t VALUES (1)"));
137    }
138
139    #[test]
140    fn test_is_modifying_update() {
141        assert!(is_modifying("update t set x=1"));
142    }
143
144    #[test]
145    fn test_is_modifying_delete() {
146        assert!(is_modifying("  DELETE FROM t"));
147    }
148
149    #[test]
150    fn test_is_modifying_create() {
151        assert!(is_modifying("CREATE TABLE t (id INT)"));
152    }
153
154    #[test]
155    fn test_is_modifying_not_select() {
156        assert!(!is_modifying("SELECT * FROM t"));
157    }
158
159    #[test]
160    fn test_is_modifying_not_with() {
161        assert!(!is_modifying("WITH cte AS (SELECT 1) SELECT * FROM cte"));
162    }
163
164    #[test]
165    fn test_is_modifying_with_insert() {
166        assert!(is_modifying(
167            "WITH cte AS (SELECT 1) INSERT INTO t VALUES (1)"
168        ));
169    }
170
171    #[test]
172    fn test_is_modifying_with_update() {
173        assert!(is_modifying("WITH cte AS (SELECT 1) UPDATE t SET x = 1"));
174    }
175
176    #[test]
177    fn test_is_modifying_with_merge() {
178        assert!(is_modifying(
179            "WITH src AS (SELECT * FROM stg) MERGE INTO t USING src ON t.id = src.id"
180        ));
181    }
182
183    #[test]
184    fn test_is_modifying_with_delete() {
185        assert!(is_modifying(
186            "WITH cte AS (SELECT id FROM t) DELETE FROM t WHERE id IN (SELECT id FROM cte)"
187        ));
188    }
189
190    #[test]
191    fn test_is_modifying_subselect_not_flagged() {
192        // Sub-selects inside parentheses should not be flagged
193        assert!(!is_modifying(
194            "SELECT * FROM t WHERE id IN (SELECT id FROM u)"
195        ));
196    }
197
198    #[test]
199    fn test_is_modifying_cte_in_subquery_not_flagged() {
200        // WITH inside a subquery should not be flagged
201        assert!(!is_modifying(
202            "SELECT * FROM (WITH cte AS (SELECT 1) SELECT * FROM cte) AS sub"
203        ));
204    }
205
206    #[cfg(feature = "postgres")]
207    #[test]
208    fn test_postgres_explain_wrap() {
209        let (sql, out, is_multi) = explain_sql("SELECT 1", Backend::Postgres, false).unwrap();
210        assert!(sql.contains("EXPLAIN (FORMAT JSON, COSTS)"));
211        assert_eq!(out, ExplainOutput::Json);
212        assert!(!is_multi);
213    }
214
215    #[cfg(feature = "postgres")]
216    #[test]
217    fn test_postgres_explain_analyze() {
218        let (sql, out, is_multi) = explain_sql("SELECT 1", Backend::Postgres, true).unwrap();
219        assert!(sql.contains("ANALYZE"));
220        assert_eq!(out, ExplainOutput::Json);
221        assert!(!is_multi);
222    }
223
224    #[cfg(feature = "postgres")]
225    #[test]
226    fn test_safe_explain_for_modifying() {
227        let (sql, _out, is_multi) =
228            explain_sql("INSERT INTO t VALUES (1)", Backend::Postgres, true).unwrap();
229        assert!(!sql.contains("ANALYZE"));
230        assert!(sql.contains("EXPLAIN (FORMAT JSON, COSTS)"));
231        assert!(!is_multi);
232    }
233
234    #[cfg(feature = "sqlite")]
235    #[test]
236    fn test_sqlite_explain_wrap() {
237        let (sql, out, is_multi) = explain_sql("SELECT 1", Backend::Sqlite, false).unwrap();
238        assert!(sql.contains("EXPLAIN QUERY PLAN"));
239        assert_eq!(out, ExplainOutput::Text);
240        assert!(!is_multi);
241    }
242}
ferrule_core/explain.rs

ferrule_core/
explain.rs
