sqlcx_core/parser/
mysql.rs

1use std::collections::HashMap;
2use std::sync::LazyLock;
3
4use regex::Regex;
5
6use crate::annotations::extract_annotations;
7use crate::error::Result;
8use crate::ir::{ColumnDef, EnumDef, QueryDef, SqlType, SqlTypeCategory, TableDef};
9use crate::parser::joins::{has_outer_join, resolve_multi_table_columns};
10use crate::parser::{
11    DatabaseParser, build_params, ensure_supported_select_expr, make_unknown_column,
12    split_column_defs, split_query_blocks,
13};
14
15// ── Static regex patterns ────────────────────────────────────────────────────
16
17static ENUM_TYPE_RE: LazyLock<Regex> = LazyLock::new(|| {
18    Regex::new(r"(?i)^ENUM\s*\(\s*((?:'[^']*'(?:\s*,\s*'[^']*')*)?)\s*\)").unwrap()
19});
20
21static ENUM_VAL_RE: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"'([^']*)'").unwrap());
22
23static TINYINT_BOOL_RE: LazyLock<Regex> =
24    LazyLock::new(|| Regex::new(r"(?i)^TINYINT\s*\(\s*1\s*\)").unwrap());
25
26static BASE_TYPE_RE: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"(?i)^(\w+)").unwrap());
27
28static CONSTRAINT_RE: LazyLock<Regex> = LazyLock::new(|| {
29    Regex::new(r"(?i)^(PRIMARY\s+KEY|CONSTRAINT|UNIQUE|CHECK|FOREIGN\s+KEY|KEY\s+)").unwrap()
30});
31
32static COL_NAME_RE: LazyLock<Regex> =
33    LazyLock::new(|| Regex::new(r"^(?:`(\w+)`|(\w+))\s+").unwrap());
34
35static ENUM_COL_RE: LazyLock<Regex> =
36    LazyLock::new(|| Regex::new(r"(?i)^(ENUM\s*\([^)]*\))").unwrap());
37
38static COL_TYPE_RE: LazyLock<Regex> =
39    LazyLock::new(|| Regex::new(r"(?i)^(\w+(?:\s*\([^)]*\))?)").unwrap());
40
41static NOT_NULL_RE: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"(?i)\bNOT\s+NULL\b").unwrap());
42
43static DEFAULT_RE: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"(?i)\bDEFAULT\b").unwrap());
44
45static PK_INLINE_RE: LazyLock<Regex> =
46    LazyLock::new(|| Regex::new(r"(?i)\bPRIMARY\s+KEY\b").unwrap());
47
48static UNIQUE_RE: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"(?i)\bUNIQUE\b").unwrap());
49
50static AUTO_INC_RE: LazyLock<Regex> =
51    LazyLock::new(|| Regex::new(r"(?i)\bAUTO_INCREMENT\b").unwrap());
52
53static GENERATED_RE: LazyLock<Regex> =
54    LazyLock::new(|| Regex::new(r"(?i)\bGENERATED\s+ALWAYS\s+AS\b").unwrap());
55
56static TABLE_RE: LazyLock<Regex> = LazyLock::new(|| {
57    Regex::new(
58        r"(?is)CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:`?(\w+)`?)\s*\(([\s\S]*?)\)\s*(?:ENGINE\s*=\s*\w+\s*)?;",
59    )
60    .unwrap()
61});
62
63static TABLE_RE_FALLBACK: LazyLock<Regex> = LazyLock::new(|| {
64    Regex::new(r"(?is)CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:`?(\w+)`?)\s*\(([\s\S]*?)\)\s*;")
65        .unwrap()
66});
67
68static TABLE_PK_RE: LazyLock<Regex> =
69    LazyLock::new(|| Regex::new(r"(?i)^PRIMARY\s+KEY\s*\(\s*([\w\s,`]+)\s*\)").unwrap());
70
71static INSERT_RE: LazyLock<Regex> = LazyLock::new(|| {
72    Regex::new(
73        r"(?i)INSERT\s+INTO\s+`?\w+`?\s*\(\s*([\w\s,`]+)\s*\)\s*VALUES\s*\(\s*([?,\s]+)\s*\)",
74    )
75    .unwrap()
76});
77
78static WHERE_PARAM_RE: LazyLock<Regex> = LazyLock::new(|| {
79    Regex::new(
80        r"(?i)(?:(\w+)\s*\(\s*(\w+)\s*\)|(\w+))\s*(?:=|!=|<>|<=?|>=?|(?:NOT\s+)?(?:I?LIKE|IN|IS))\s*\?",
81    )
82    .unwrap()
83});
84
85static FROM_TABLE_RE: LazyLock<Regex> =
86    LazyLock::new(|| Regex::new(r"(?i)(?:FROM|INTO|UPDATE)\s+`?(\w+)`?").unwrap());
87
88static SELECT_RE: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"(?i)^\s*SELECT\b").unwrap());
89
90static SELECT_COLS_RE: LazyLock<Regex> =
91    LazyLock::new(|| Regex::new(r"(?i)SELECT\s+([\s\S]+?)\s+FROM\b").unwrap());
92
93static ALIAS_RE: LazyLock<Regex> =
94    LazyLock::new(|| Regex::new(r"(?i)^`?(\w+)`?\s+as\s+`?(\w+)`?$").unwrap());
95
96// ── Type mapping ─────────────────────────────────────────────────────────────
97
98fn type_category(normalized: &str) -> Option<SqlTypeCategory> {
99    match normalized {
100        "text" | "varchar" | "char" | "character" | "tinytext" | "mediumtext" | "longtext" => {
101            Some(SqlTypeCategory::String)
102        }
103        "int" | "integer" | "tinyint" | "smallint" | "mediumint" | "bigint" | "serial"
104        | "float" | "double" | "real" | "decimal" | "numeric" => Some(SqlTypeCategory::Number),
105        "boolean" | "bool" => Some(SqlTypeCategory::Boolean),
106        "date" | "datetime" | "timestamp" | "time" | "year" => Some(SqlTypeCategory::Date),
107        "json" => Some(SqlTypeCategory::Json),
108        "binary" | "varbinary" | "blob" | "tinyblob" | "mediumblob" | "longblob" => {
109            Some(SqlTypeCategory::Binary)
110        }
111        _ => None,
112    }
113}
114
115/// Resolve a raw MySQL type string into a SqlType.
116/// Handles ENUM('a','b'), TINYINT(1) -> Boolean, and standard types.
117fn resolve_sql_type(raw: &str) -> SqlType {
118    let trimmed = raw.trim();
119
120    // Inline ENUM: ENUM('a', 'b', 'c')
121    if let Some(cap) = ENUM_TYPE_RE.captures(trimmed) {
122        let values: Vec<String> = ENUM_VAL_RE
123            .captures_iter(&cap[1])
124            .map(|v| v[1].to_string())
125            .collect();
126        return SqlType {
127            raw: trimmed.to_string(),
128            normalized: "enum".to_string(),
129            category: SqlTypeCategory::Enum,
130            element_type: None,
131            enum_name: None,
132            enum_values: Some(values),
133            json_shape: None,
134        };
135    }
136
137    // TINYINT(1) -> Boolean
138    if TINYINT_BOOL_RE.is_match(trimmed) {
139        return SqlType {
140            raw: trimmed.to_string(),
141            normalized: "tinyint(1)".to_string(),
142            category: SqlTypeCategory::Boolean,
143            element_type: None,
144            enum_name: None,
145            enum_values: None,
146            json_shape: None,
147        };
148    }
149
150    // Strip parenthesized size/precision: VARCHAR(255) -> varchar, DECIMAL(10,2) -> decimal
151    let base = BASE_TYPE_RE
152        .captures(trimmed)
153        .map(|c| c[1].to_lowercase())
154        .unwrap_or_else(|| trimmed.to_lowercase());
155
156    // Strip trailing UNSIGNED/SIGNED/ZEROFILL from the base if present in rest
157    let normalized = base.clone();
158
159    if let Some(cat) = type_category(&normalized) {
160        return SqlType {
161            raw: trimmed.to_string(),
162            normalized,
163            category: cat,
164            element_type: None,
165            enum_name: None,
166            enum_values: None,
167            json_shape: None,
168        };
169    }
170
171    SqlType {
172        raw: trimmed.to_string(),
173        normalized,
174        category: SqlTypeCategory::Unknown,
175        element_type: None,
176        enum_name: None,
177        enum_values: None,
178        json_shape: None,
179    }
180}
181
182// ── Schema parsing ───────────────────────────────────────────────────────────
183
184struct ParsedColumn {
185    col: ColumnDef,
186    is_pk: bool,
187    is_unique: bool,
188}
189
190fn parse_column_line(line: &str) -> Option<ParsedColumn> {
191    let line = line.trim();
192    if line.is_empty() {
193        return None;
194    }
195
196    // Skip constraint lines
197    if CONSTRAINT_RE.is_match(line) {
198        return None;
199    }
200
201    // Extract column name — may be backtick-quoted
202    let name_cap = COL_NAME_RE.captures(line)?;
203    let col_name = name_cap
204        .get(1)
205        .or_else(|| name_cap.get(2))?
206        .as_str()
207        .to_lowercase();
208    let after_name = &line[name_cap[0].len()..];
209
210    // Extract the type — could be ENUM(...), TINYINT(1), or simple word with optional (N)
211    let raw_type: String;
212    let rest: &str;
213
214    if let Some(cap) = ENUM_COL_RE.captures(after_name) {
215        raw_type = cap[1].to_string();
216        rest = &after_name[cap[0].len()..];
217    } else {
218        // Match type with optional parenthesized params: INT, VARCHAR(255), DECIMAL(10,2)
219        if let Some(cap) = COL_TYPE_RE.captures(after_name) {
220            raw_type = cap[1].to_string();
221            rest = &after_name[cap[0].len()..];
222        } else {
223            raw_type = "unknown".to_string();
224            rest = after_name;
225        }
226    }
227
228    let is_not_null = NOT_NULL_RE.is_match(rest);
229    let has_default_kw = DEFAULT_RE.is_match(rest);
230    let is_pk = PK_INLINE_RE.is_match(rest);
231    let is_unique = UNIQUE_RE.is_match(rest);
232    let is_auto_inc = AUTO_INC_RE.is_match(rest);
233    let is_generated = GENERATED_RE.is_match(rest);
234
235    let sql_type = resolve_sql_type(&raw_type);
236
237    Some(ParsedColumn {
238        col: ColumnDef {
239            name: col_name,
240            alias: None,
241            source_table: None,
242            sql_type,
243            nullable: !is_not_null,
244            has_default: has_default_kw || is_auto_inc || is_generated,
245        },
246        is_pk,
247        is_unique,
248    })
249}
250
251fn parse_schema_tables(sql: &str) -> Vec<TableDef> {
252    let mut tables = Vec::new();
253    let captures: Vec<_> = TABLE_RE.captures_iter(sql).collect();
254    let captures = if captures.is_empty() {
255        TABLE_RE_FALLBACK.captures_iter(sql).collect()
256    } else {
257        captures
258    };
259
260    for cap in &captures {
261        let table_name = cap[1].to_lowercase();
262        let body = &cap[2];
263
264        let mut columns = Vec::new();
265        let mut primary_key: Vec<String> = Vec::new();
266        let mut unique_constraints: Vec<Vec<String>> = Vec::new();
267
268        let raw_lines: Vec<&str> = body.lines().collect();
269        let mut pending_comment = String::new();
270        let mut non_comment_buf = String::new();
271        let mut comment_map: HashMap<usize, String> = HashMap::new();
272
273        for raw_line in &raw_lines {
274            let trimmed = raw_line.trim();
275            if trimmed.starts_with("--") {
276                if !pending_comment.is_empty() {
277                    pending_comment.push('\n');
278                }
279                pending_comment.push_str(trimmed);
280            } else {
281                let before = split_column_defs(&non_comment_buf)
282                    .iter()
283                    .filter(|d| !d.is_empty())
284                    .count();
285                if !non_comment_buf.is_empty() {
286                    non_comment_buf.push('\n');
287                }
288                non_comment_buf.push_str(raw_line);
289                let after = split_column_defs(&non_comment_buf)
290                    .iter()
291                    .filter(|d| !d.is_empty())
292                    .count();
293
294                if after > before && !pending_comment.is_empty() {
295                    comment_map.insert(before, pending_comment.clone());
296                    pending_comment.clear();
297                } else if after == before {
298                    // Still accumulating same def
299                } else {
300                    pending_comment.clear();
301                }
302            }
303        }
304
305        let lines = split_column_defs(&non_comment_buf);
306
307        for (i, line) in lines.iter().enumerate() {
308            let trimmed = line.trim();
309
310            // Table-level PRIMARY KEY constraint
311            if let Some(pk_cap) = TABLE_PK_RE.captures(trimmed) {
312                for col in pk_cap[1].split(',') {
313                    primary_key.push(col.trim().trim_matches('`').to_lowercase());
314                }
315                continue;
316            }
317
318            let Some(mut parsed) = parse_column_line(trimmed) else {
319                continue;
320            };
321
322            // Apply annotations from comment above this column
323            if let Some(comment) = comment_map.get(&i) {
324                let (_, ann) = extract_annotations(comment);
325                if let Some(values) = ann.enums.get(&parsed.col.name) {
326                    parsed.col.sql_type.category = SqlTypeCategory::Enum;
327                    parsed.col.sql_type.enum_values = Some(values.clone());
328                }
329                if let Some(shape) = ann.json_shapes.get(&parsed.col.name) {
330                    parsed.col.sql_type.json_shape = Some(shape.clone());
331                }
332            }
333
334            if parsed.is_pk {
335                primary_key.push(parsed.col.name.clone());
336            }
337            if parsed.is_unique {
338                unique_constraints.push(vec![parsed.col.name.clone()]);
339            }
340            columns.push(parsed.col);
341        }
342
343        // PK columns are implicitly NOT NULL
344        for col in &mut columns {
345            if primary_key.contains(&col.name) {
346                col.nullable = false;
347            }
348        }
349
350        tables.push(TableDef {
351            name: table_name,
352            columns,
353            primary_key,
354            unique_constraints,
355        });
356    }
357
358    tables
359}
360
361// ── Query parsing ────────────────────────────────────────────────────────────
362
363/// Count `?` placeholders left-to-right, returning 1-based indices.
364fn extract_param_indices(sql: &str) -> Vec<u32> {
365    let mut count = 0u32;
366    let mut indices = Vec::new();
367    for ch in sql.chars() {
368        if ch == '?' {
369            count += 1;
370            indices.push(count);
371        }
372    }
373    indices
374}
375
376/// For MySQL `?` placeholders, infer which column each `?` maps to.
377fn infer_param_columns(sql: &str) -> HashMap<u32, String> {
378    let mut result = HashMap::new();
379
380    // INSERT pattern: INSERT INTO tbl (col1, col2) VALUES (?, ?)
381    if let Some(cap) = INSERT_RE.captures(sql) {
382        let cols: Vec<String> = cap[1]
383            .split(',')
384            .map(|s| s.trim().trim_matches('`').to_lowercase())
385            .collect();
386        // Count ?'s in the VALUES clause
387        let values_str = &cap[2];
388        let mut idx = 0u32;
389        for ch in values_str.chars() {
390            if ch == '?' {
391                idx += 1;
392                if (idx as usize) <= cols.len() {
393                    result.insert(idx, cols[idx as usize - 1].clone());
394                }
395            }
396        }
397        return result;
398    }
399
400    // WHERE/SET pattern: col = ? or col LIKE ?
401    let sql_keywords: std::collections::HashSet<&str> = [
402        "not", "and", "or", "where", "set", "when", "then", "else", "case", "between", "exists",
403        "any", "all", "some", "having",
404    ]
405    .into_iter()
406    .collect();
407
408    // We need to count ? positions to get the right index
409    let mut question_positions: Vec<usize> = Vec::new();
410    for (i, ch) in sql.char_indices() {
411        if ch == '?' {
412            question_positions.push(i);
413        }
414    }
415
416    for cap in WHERE_PARAM_RE.captures_iter(sql) {
417        // Find which ? this match refers to by position
418        let match_end = cap.get(0).unwrap().end();
419        // The ? is at match_end - 1
420        let q_pos = match_end - 1;
421        if let Some(idx_0based) = question_positions.iter().position(|&p| p == q_pos) {
422            let idx = (idx_0based + 1) as u32;
423            if cap.get(1).is_some() && cap.get(2).is_some() {
424                result.insert(idx, cap[2].to_lowercase());
425            } else if let Some(m) = cap.get(3) {
426                let word = m.as_str().to_lowercase();
427                if !sql_keywords.contains(word.as_str()) {
428                    result.insert(idx, word);
429                }
430            }
431        }
432    }
433
434    result
435}
436
437fn find_from_table<'a>(sql: &str, tables: &'a [TableDef]) -> Option<&'a TableDef> {
438    let cap = FROM_TABLE_RE.captures(sql)?;
439    let table_name = cap[1].to_lowercase();
440    tables.iter().find(|t| t.name == table_name)
441}
442
443fn resolve_return_columns(
444    sql: &str,
445    table: Option<&TableDef>,
446    schema_tables: &[TableDef],
447    source_file: &str,
448) -> Result<Vec<ColumnDef>> {
449    if !SELECT_RE.is_match(sql) {
450        return Ok(Vec::new());
451    }
452
453    let Some(cap) = SELECT_COLS_RE.captures(sql) else {
454        return Ok(Vec::new());
455    };
456    let cols_part = cap[1].trim();
457
458    // Multi-table JOIN path: route qualified columns through the shared
459    // resolver when the outer FROM contains a JOIN. `has_outer_join` scopes
460    // the check to the outer FROM body so subquery JOINs don't false-trigger.
461    if has_outer_join(sql) {
462        return resolve_multi_table_columns(cols_part, sql, schema_tables, source_file);
463    }
464
465    if cols_part == "*" {
466        return Ok(table.map(|t| t.columns.clone()).unwrap_or_default());
467    }
468
469    let Some(table) = table else {
470        return Ok(Vec::new());
471    };
472
473    let col_names: Vec<&str> = cols_part.split(',').map(|s| s.trim()).collect();
474
475    col_names
476        .iter()
477        .map(|&col_expr| -> Result<ColumnDef> {
478            ensure_supported_select_expr(col_expr, source_file)?;
479            let expr_lower = col_expr.to_lowercase();
480            if let Some(alias_cap) = ALIAS_RE.captures(&expr_lower) {
481                let actual = &alias_cap[1];
482                let alias = alias_cap[2].to_string();
483                Ok(table
484                    .columns
485                    .iter()
486                    .find(|c| c.name == actual)
487                    .map(|c| {
488                        let mut col = c.clone();
489                        col.alias = Some(alias);
490                        col
491                    })
492                    .unwrap_or_else(|| make_unknown_column(actual)))
493            } else {
494                let name = expr_lower.trim_matches('`');
495                Ok(table
496                    .columns
497                    .iter()
498                    .find(|c| c.name == name)
499                    .cloned()
500                    .unwrap_or_else(|| make_unknown_column(name)))
501            }
502        })
503        .collect()
504}
505
506// ── Public API ───────────────────────────────────────────────────────────────
507
508pub struct MySqlParser;
509
510impl MySqlParser {
511    pub fn new() -> Self {
512        Self
513    }
514}
515
516impl Default for MySqlParser {
517    fn default() -> Self {
518        Self::new()
519    }
520}
521
522impl DatabaseParser for MySqlParser {
523    fn parse_schema(&self, sql: &str) -> Result<(Vec<TableDef>, Vec<EnumDef>)> {
524        // MySQL has no standalone CREATE TYPE ... AS ENUM; enums are inline on columns
525        let tables = parse_schema_tables(sql);
526        Ok((tables, Vec::new()))
527    }
528
529    fn parse_queries(
530        &self,
531        sql: &str,
532        tables: &[TableDef],
533        enums: &[EnumDef],
534        source_file: &str,
535    ) -> Result<Vec<QueryDef>> {
536        let _ = enums;
537        let blocks = split_query_blocks(sql);
538        let mut queries = Vec::new();
539
540        for block in blocks {
541            let table = find_from_table(&block.sql, tables);
542            let param_indices = extract_param_indices(&block.sql);
543            let inferred_cols = infer_param_columns(&block.sql);
544            let params = build_params(&block.comments, table, param_indices, inferred_cols);
545            let returns = resolve_return_columns(&block.sql, table, tables, source_file)?;
546
547            let clean_sql = block
548                .sql
549                .trim_end()
550                .trim_end_matches(';')
551                .trim()
552                .to_string();
553
554            queries.push(QueryDef {
555                name: block.name,
556                command: block.command,
557                sql: clean_sql,
558                params,
559                returns,
560                source_file: source_file.to_string(),
561            });
562        }
563
564        Ok(queries)
565    }
566}
567
568// ── Tests ────────────────────────────────────────────────────────────────────
569
570#[cfg(test)]
571mod tests {
572    use super::*;
573    use crate::ir::{QueryCommand, SqlTypeCategory};
574    use crate::parser::DatabaseParser;
575
576    const SCHEMA_SQL: &str = include_str!("../../../../tests/fixtures/mysql_schema.sql");
577    const QUERIES_SQL: &str = include_str!("../../../../tests/fixtures/mysql_queries/users.sql");
578
579    #[test]
580    fn parses_users_table() {
581        let parser = MySqlParser::new();
582        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
583        let users = tables.iter().find(|t| t.name == "users").unwrap();
584        assert_eq!(users.columns.len(), 11);
585        assert_eq!(users.primary_key, vec!["id"]);
586        let id = &users.columns[0];
587        assert_eq!(id.sql_type.category, SqlTypeCategory::Number);
588        assert!(id.has_default); // AUTO_INCREMENT
589    }
590
591    #[test]
592    fn parses_inline_enum() {
593        let parser = MySqlParser::new();
594        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
595        let users = tables.iter().find(|t| t.name == "users").unwrap();
596        let role = users.columns.iter().find(|c| c.name == "role").unwrap();
597        assert_eq!(role.sql_type.category, SqlTypeCategory::Enum);
598        assert_eq!(
599            role.sql_type.enum_values,
600            Some(vec!["admin".into(), "user".into(), "guest".into()])
601        );
602    }
603
604    #[test]
605    fn parses_tinyint_as_boolean() {
606        let parser = MySqlParser::new();
607        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
608        let users = tables.iter().find(|t| t.name == "users").unwrap();
609        let active = users
610            .columns
611            .iter()
612            .find(|c| c.name == "is_active")
613            .unwrap();
614        assert_eq!(active.sql_type.category, SqlTypeCategory::Boolean);
615    }
616
617    #[test]
618    fn parses_json_column() {
619        let parser = MySqlParser::new();
620        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
621        let users = tables.iter().find(|t| t.name == "users").unwrap();
622        let prefs = users
623            .columns
624            .iter()
625            .find(|c| c.name == "preferences")
626            .unwrap();
627        assert_eq!(prefs.sql_type.category, SqlTypeCategory::Json);
628        assert!(prefs.nullable);
629    }
630
631    #[test]
632    fn parses_blob_as_binary() {
633        let parser = MySqlParser::new();
634        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
635        let users = tables.iter().find(|t| t.name == "users").unwrap();
636        let avatar = users.columns.iter().find(|c| c.name == "avatar").unwrap();
637        assert_eq!(avatar.sql_type.category, SqlTypeCategory::Binary);
638    }
639
640    #[test]
641    fn parses_generated_column_as_default() {
642        let parser = MySqlParser::new();
643        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
644        let users = tables.iter().find(|t| t.name == "users").unwrap();
645        let full_name = users
646            .columns
647            .iter()
648            .find(|c| c.name == "full_name")
649            .unwrap();
650        assert!(full_name.has_default);
651    }
652
653    #[test]
654    fn parses_posts_table() {
655        let parser = MySqlParser::new();
656        let (tables, _) = parser.parse_schema(SCHEMA_SQL).unwrap();
657        let posts = tables.iter().find(|t| t.name == "posts").unwrap();
658        assert_eq!(posts.columns.len(), 6);
659    }
660
661    #[test]
662    fn parses_query_with_positional_params() {
663        let parser = MySqlParser::new();
664        let (tables, enums) = parser.parse_schema(SCHEMA_SQL).unwrap();
665        let queries = parser
666            .parse_queries(QUERIES_SQL, &tables, &enums, "mysql_queries/users.sql")
667            .unwrap();
668        let get_user = queries.iter().find(|q| q.name == "GetUser").unwrap();
669        assert_eq!(get_user.command, QueryCommand::One);
670        assert_eq!(get_user.params.len(), 1);
671        assert_eq!(get_user.params[0].name, "id");
672        assert_eq!(get_user.returns.len(), 11);
673    }
674
675    #[test]
676    fn parses_insert_params() {
677        let parser = MySqlParser::new();
678        let (tables, enums) = parser.parse_schema(SCHEMA_SQL).unwrap();
679        let queries = parser
680            .parse_queries(QUERIES_SQL, &tables, &enums, "mysql_queries/users.sql")
681            .unwrap();
682        let create = queries.iter().find(|q| q.name == "CreateUser").unwrap();
683        assert_eq!(create.command, QueryCommand::Exec);
684        assert_eq!(create.params.len(), 3);
685    }
686
687    #[test]
688    fn parses_param_overrides() {
689        let parser = MySqlParser::new();
690        let (tables, enums) = parser.parse_schema(SCHEMA_SQL).unwrap();
691        let queries = parser
692            .parse_queries(QUERIES_SQL, &tables, &enums, "mysql_queries/users.sql")
693            .unwrap();
694        let dr = queries
695            .iter()
696            .find(|q| q.name == "ListUsersByDateRange")
697            .unwrap();
698        assert_eq!(dr.params[0].name, "start_date");
699        assert_eq!(dr.params[1].name, "end_date");
700    }
701
702    // ── INNER JOIN path tests ────────────────────────────────────────────────
703
704    fn join_schema() -> &'static str {
705        "CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, org_id INT NOT NULL);\n\
706         CREATE TABLE orgs (id INT PRIMARY KEY, slug VARCHAR(255) NOT NULL);"
707    }
708
709    #[test]
710    fn inner_join_resolves_qualified_columns() {
711        let parser = MySqlParser::new();
712        let (tables, enums) = parser.parse_schema(join_schema()).unwrap();
713        let sql = "-- name: GetUserWithOrg :one\nSELECT users.name, orgs.slug FROM users INNER JOIN orgs ON users.org_id = orgs.id WHERE users.id = ?;";
714        let queries = parser.parse_queries(sql, &tables, &enums, "q.sql").unwrap();
715        assert_eq!(queries[0].returns.len(), 2);
716        assert_eq!(queries[0].returns[0].source_table.as_deref(), Some("users"));
717        assert_eq!(queries[0].returns[1].source_table.as_deref(), Some("orgs"));
718    }
719
720    #[test]
721    fn inner_join_rejects_select_star() {
722        let parser = MySqlParser::new();
723        let (tables, enums) = parser.parse_schema(join_schema()).unwrap();
724        let sql =
725            "-- name: All :many\nSELECT * FROM users INNER JOIN orgs ON users.org_id = orgs.id;";
726        let err = parser
727            .parse_queries(sql, &tables, &enums, "q.sql")
728            .unwrap_err();
729        assert!(
730            err.to_string()
731                .contains("SELECT * across multi-table JOINs")
732        );
733    }
734
735    #[test]
736    fn left_join_rejected_with_v12_pointer() {
737        let parser = MySqlParser::new();
738        let (tables, enums) = parser.parse_schema(join_schema()).unwrap();
739        let sql = "-- name: WithLeft :many\nSELECT users.id FROM users LEFT JOIN orgs ON users.org_id = orgs.id;";
740        let err = parser
741            .parse_queries(sql, &tables, &enums, "q.sql")
742            .unwrap_err();
743        assert!(err.to_string().contains("v1.1 supports INNER JOIN only"));
744    }
745}
sqlcx_core/parser/mysql.rs

sqlcx_core/parser/
mysql.rs
