tursotui_sql/
parser.rs

1//! SQL parsing utilities: statement splitting, FK extraction, source table detection.
2
3/// Foreign key relationship from one column to another table/column.
4#[derive(Debug, Clone)]
5pub struct ForeignKeyInfo {
6    pub from_column: String,
7    pub to_table: String,
8    pub to_column: String,
9}
10
11/// Split a SQL string into individual statements, respecting quoted strings and comments.
12///
13/// Semicolons inside single-quoted strings, double-quoted identifiers,
14/// line comments (`--`), and block comments (`/* */`) are **not** treated as
15/// statement separators.
16pub fn detect_statements(sql: &str) -> Vec<&str> {
17    let mut statements = Vec::new();
18    let bytes = sql.as_bytes();
19    let len = bytes.len();
20    let mut i = 0;
21    let mut stmt_start = 0;
22
23    while i < len {
24        match bytes[i] {
25            b'\'' => {
26                // Single-quoted string — skip until closing quote.
27                // SQLite escapes quotes by doubling: 'it''s'
28                i += 1;
29                while i < len {
30                    if bytes[i] == b'\'' {
31                        i += 1;
32                        if i < len && bytes[i] == b'\'' {
33                            i += 1; // escaped quote, continue
34                        } else {
35                            break; // end of string
36                        }
37                    } else {
38                        i += 1;
39                    }
40                }
41            }
42            b'"' => {
43                // Double-quoted identifier — skip until closing quote.
44                // SQLite allows doubled quotes as escape: "col""name"
45                i += 1;
46                while i < len {
47                    if bytes[i] == b'"' {
48                        i += 1;
49                        if i < len && bytes[i] == b'"' {
50                            i += 1; // escaped quote, continue
51                        } else {
52                            break; // end of identifier
53                        }
54                    } else {
55                        i += 1;
56                    }
57                }
58            }
59            b'-' if i + 1 < len && bytes[i + 1] == b'-' => {
60                // Line comment — skip to end of line
61                i += 2;
62                while i < len && bytes[i] != b'\n' {
63                    i += 1;
64                }
65                if i < len {
66                    i += 1; // skip newline
67                }
68            }
69            b'/' if i + 1 < len && bytes[i + 1] == b'*' => {
70                // Block comment — skip to */
71                i += 2;
72                while i + 1 < len && !(bytes[i] == b'*' && bytes[i + 1] == b'/') {
73                    i += 1;
74                }
75                if i + 1 < len {
76                    i += 2; // skip */
77                }
78            }
79            b';' => {
80                let stmt = sql[stmt_start..i].trim();
81                if !stmt.is_empty() {
82                    statements.push(stmt);
83                }
84                i += 1;
85                stmt_start = i;
86            }
87            _ => {
88                i += 1;
89            }
90        }
91    }
92
93    // Last statement (no trailing semicolon)
94    let last = sql[stmt_start..].trim();
95    if !last.is_empty() {
96        statements.push(last);
97    }
98
99    statements
100}
101
102/// Parse foreign key relationships from a `CREATE TABLE` SQL statement.
103///
104/// Handles both table-level (`FOREIGN KEY (col) REFERENCES ...`) and
105/// inline column-level (`col TYPE REFERENCES ...`) syntax.
106pub fn parse_foreign_keys(create_sql: &str) -> Vec<ForeignKeyInfo> {
107    let upper = create_sql.to_uppercase();
108    let mut fks = Vec::new();
109
110    // Find all "FOREIGN KEY (col) REFERENCES table (col)" patterns
111    let mut search_from = 0;
112    while let Some(fk_pos) = upper[search_from..].find("FOREIGN KEY") {
113        let abs_pos = search_from + fk_pos;
114        search_from = abs_pos + 11;
115
116        // Extract from_column: text between first ( and )
117        let Some(open) = create_sql[search_from..].find('(') else {
118            continue;
119        };
120        let paren_start = search_from + open + 1;
121        let Some(close) = create_sql[paren_start..].find(')') else {
122            continue;
123        };
124        let from_col = create_sql[paren_start..paren_start + close].trim();
125
126        // Find REFERENCES keyword after the closing paren
127        let after_paren = paren_start + close + 1;
128        let rest_upper = &upper[after_paren..];
129        let Some(ref_pos) = rest_upper.find("REFERENCES") else {
130            continue;
131        };
132        let after_ref = after_paren + ref_pos + 10; // len("REFERENCES")
133
134        // Extract target table name (may be quoted)
135        let target_start = create_sql[after_ref..].trim_start();
136        let offset = create_sql.len() - target_start.len();
137        let (to_table, rest) = extract_identifier(target_start);
138        if to_table.is_empty() {
139            continue;
140        }
141
142        // Extract target column: text between ( and )
143        let rest_trimmed = rest.trim_start();
144        if !rest_trimmed.starts_with('(') {
145            continue;
146        }
147        let inner = &rest_trimmed[1..];
148        let Some(end) = inner.find(')') else {
149            continue;
150        };
151        let to_col = inner[..end].trim();
152
153        // Consumed bytes = full quoted span, not just the unquoted content length
154        let consumed = target_start.len() - rest.len();
155        fks.push(ForeignKeyInfo {
156            from_column: unquote(from_col),
157            to_table,
158            to_column: unquote(to_col),
159        });
160
161        search_from = offset + consumed;
162    }
163
164    // Also parse inline column-level FK syntax:
165    //   column_name TYPE [NOT NULL] REFERENCES table_name (column_name)
166    // Scan for REFERENCES not preceded by FOREIGN KEY.
167    let mut search_from = 0;
168    while let Some(ref_pos) = upper[search_from..].find("REFERENCES") {
169        let abs_pos = search_from + ref_pos;
170        search_from = abs_pos + 10;
171
172        // Skip if this REFERENCES is part of a FOREIGN KEY ... REFERENCES
173        // (already handled above). Look for "FOREIGN KEY" OR a closing paren
174        // between the last clause separator and REFERENCES — both indicate
175        // a table-level constraint, not an inline column FK.
176        let before = &upper[..abs_pos];
177        let last_separator = before.rfind([',', '(']).unwrap_or(0);
178        let clause_before = &before[last_separator..];
179        if clause_before.contains("FOREIGN KEY") || clause_before.contains(')') {
180            continue;
181        }
182
183        // Walk backwards from REFERENCES to find the column name.
184        // The text before REFERENCES looks like: "col_name TYPE [NOT NULL] [qualifiers] "
185        let before_ref = create_sql[..abs_pos].trim_end();
186        // Split the clause (from last comma or open paren) into tokens
187        let clause_start = before_ref.rfind([',', '(']).map_or(0, |p| p + 1);
188        let clause = before_ref[clause_start..].trim();
189        // First token in the clause is the column name
190        let from_col = clause.split_whitespace().next().unwrap_or("");
191        if from_col.is_empty() {
192            continue;
193        }
194
195        // Extract target table name after REFERENCES
196        let after_ref = &create_sql[abs_pos + 10..].trim_start();
197        let offset_after = create_sql.len() - after_ref.len();
198        let (to_table, rest) = extract_identifier(after_ref);
199        if to_table.is_empty() {
200            continue;
201        }
202
203        // Extract target column from (col)
204        let rest_trimmed = rest.trim_start();
205        if !rest_trimmed.starts_with('(') {
206            continue;
207        }
208        let inner = &rest_trimmed[1..];
209        let Some(end) = inner.find(')') else {
210            continue;
211        };
212        let to_col = inner[..end].trim();
213
214        let consumed = after_ref.len() - rest.len();
215        fks.push(ForeignKeyInfo {
216            from_column: unquote(from_col),
217            to_table,
218            to_column: unquote(to_col),
219        });
220
221        search_from = offset_after + consumed;
222    }
223
224    fks
225}
226
227/// Extract an identifier (possibly quoted with `"` or `` ` ``) from the start of `s`.
228/// Returns (identifier, `rest_of_string`).
229///
230/// Doubled quotes inside double-quoted identifiers are treated as an escaped
231/// quote character (e.g. `"col""name"` → `col"name`).
232pub fn extract_identifier(s: &str) -> (String, &str) {
233    let s = s.trim_start();
234    if let Some(inner) = s.strip_prefix('"') {
235        // Double-quoted identifier — handle `""` as escaped quote.
236        // Uses char_indices to correctly handle multi-byte UTF-8 identifiers.
237        let mut name = String::new();
238        let mut chars = inner.char_indices();
239        while let Some((i, c)) = chars.next() {
240            if c == '"' {
241                if let Some((_, next_c)) = chars.clone().next()
242                    && next_c == '"'
243                {
244                    // Doubled quote → literal quote in identifier
245                    name.push('"');
246                    chars.next(); // consume second quote
247                    continue;
248                }
249                // Closing quote — rest starts after this quote byte
250                return (name, &inner[i + 1..]);
251            }
252            name.push(c);
253        }
254        // No closing quote — return what we have
255        return (name, "");
256    } else if let Some(inner) = s.strip_prefix('`')
257        && let Some(end) = inner.find('`')
258    {
259        return (inner[..end].to_string(), &inner[end + 1..]);
260    }
261    // Unquoted: read until non-identifier char
262    let end = s
263        .find(|c: char| !c.is_alphanumeric() && c != '_')
264        .unwrap_or(s.len());
265    (s[..end].to_string(), &s[end..])
266}
267
268/// Remove surrounding quotes from a column name if present.
269pub fn unquote(s: &str) -> String {
270    let s = s.trim();
271    if (s.starts_with('"') && s.ends_with('"')) || (s.starts_with('`') && s.ends_with('`')) {
272        s[1..s.len() - 1].to_string()
273    } else {
274        s.to_string()
275    }
276}
277
278/// Remove SQL comments (both line `--` and block `/* */`) from a SQL string.
279///
280/// **Known limitation:** Does not track string literal context. Comment-like
281/// sequences inside single-quoted strings (e.g., `'-- not a comment'`) will
282/// be incorrectly stripped. This is acceptable for its current use case
283/// (`detect_source_table` editability detection on user-entered SQL) but
284/// should not be used for SQL transformation before execution.
285pub fn strip_comments(sql: &str) -> String {
286    let mut result = String::with_capacity(sql.len());
287    let chars: Vec<char> = sql.chars().collect();
288    let len = chars.len();
289    let mut i = 0;
290
291    while i < len {
292        // Block comment
293        if i + 1 < len && chars[i] == '/' && chars[i + 1] == '*' {
294            i += 2;
295            while i + 1 < len && !(chars[i] == '*' && chars[i + 1] == '/') {
296                i += 1;
297            }
298            // skip the closing */
299            if i + 1 < len {
300                i += 2;
301            }
302        // Line comment
303        } else if i + 1 < len && chars[i] == '-' && chars[i + 1] == '-' {
304            i += 2;
305            while i < len && chars[i] != '\n' {
306                i += 1;
307            }
308        } else {
309            result.push(chars[i]);
310            i += 1;
311        }
312    }
313
314    result
315}
316
317/// Detect whether a SQL query targets a single table and return that table name.
318///
319/// Returns `Some(table_name)` if the query is a simple single-table SELECT,
320/// or `None` if the query is too complex to edit safely.
321pub fn detect_source_table(sql: &str) -> Option<String> {
322    let stripped = strip_comments(sql);
323    let trimmed = stripped.trim();
324
325    if trimmed.is_empty() {
326        return None;
327    }
328
329    let upper = trimmed.to_uppercase();
330
331    // Must start with SELECT (case-insensitive)
332    if !upper.starts_with("SELECT") {
333        return None;
334    }
335
336    // Reject queries with complexity keywords (simple string containment)
337    let reject_keywords = ["JOIN", "UNION", "INTERSECT", "EXCEPT", "GROUP BY", "WITH"];
338    for kw in &reject_keywords {
339        if upper.contains(kw) {
340            return None;
341        }
342    }
343
344    // Find FROM keyword position
345    // We search for FROM as a word boundary approximately — find " FROM " or similar
346    let from_pos = find_from_keyword(trimmed)?;
347
348    let after_from = trimmed[from_pos..].trim();
349
350    // Reject subquery in FROM: FROM (
351    if after_from.starts_with('(') {
352        return None;
353    }
354
355    // Extract the table name (possibly quoted)
356    Some(extract_table_name(after_from))
357}
358
359/// Find the position of the table name (the text after "FROM ") in `sql`.
360/// Returns the byte offset into `sql` of the text immediately after FROM and whitespace.
361pub fn find_from_keyword(sql: &str) -> Option<usize> {
362    let upper = sql.to_uppercase();
363    let bytes = upper.as_bytes();
364    let len = bytes.len();
365    let from_bytes = b"FROM";
366
367    let mut i = 0;
368    while i + 4 <= len {
369        if &bytes[i..i + 4] == from_bytes {
370            // Check that FROM is preceded by whitespace or start
371            let preceded_ok = i == 0 || bytes[i - 1].is_ascii_whitespace();
372            // Check that FROM is followed by whitespace or end
373            let followed_ok = i + 4 == len || bytes[i + 4].is_ascii_whitespace();
374
375            if preceded_ok && followed_ok {
376                // Skip "FROM" and leading whitespace
377                let mut pos = i + 4;
378                while pos < len && bytes[pos].is_ascii_whitespace() {
379                    pos += 1;
380                }
381                return Some(pos);
382            }
383        }
384        i += 1;
385    }
386    None
387}
388
389/// Extract a single table name from the beginning of `text`.
390/// Handles double-quoted and backtick-quoted names.
391/// Stops at whitespace, `;`, or end of string.
392pub fn extract_table_name(text: &str) -> String {
393    let mut chars = text.chars();
394    let Some(first) = chars.next() else {
395        return String::new();
396    };
397
398    if first == '"' || first == '`' {
399        let close = first;
400        let mut name = String::new();
401        for c in chars {
402            if c == close {
403                break;
404            }
405            name.push(c);
406        }
407        name
408    } else {
409        let mut name = String::new();
410        name.push(first);
411        for c in chars {
412            if c.is_ascii_whitespace() || c == ';' {
413                break;
414            }
415            name.push(c);
416        }
417        name
418    }
419}
420
421#[cfg(test)]
422mod tests {
423    use super::*;
424
425    // ── detect_statements tests ──────────────────────────────────────
426
427    #[test]
428    fn detect_statements_single() {
429        let stmts = detect_statements("SELECT 1");
430        assert_eq!(stmts, vec!["SELECT 1"]);
431    }
432
433    #[test]
434    fn detect_statements_multiple() {
435        let stmts = detect_statements("INSERT INTO t VALUES (1); SELECT 1");
436        assert_eq!(stmts, vec!["INSERT INTO t VALUES (1)", "SELECT 1"]);
437    }
438
439    #[test]
440    fn detect_statements_trailing_semicolons() {
441        let stmts = detect_statements("SELECT 1;;;");
442        assert_eq!(stmts, vec!["SELECT 1"]);
443    }
444
445    #[test]
446    fn detect_statements_semicolon_in_string() {
447        let stmts = detect_statements("SELECT 'a;b'");
448        assert_eq!(stmts, vec!["SELECT 'a;b'"]);
449    }
450
451    #[test]
452    fn detect_statements_semicolon_in_double_quoted() {
453        let stmts = detect_statements(r#"SELECT "col;name" FROM t"#);
454        assert_eq!(stmts, vec![r#"SELECT "col;name" FROM t"#]);
455    }
456
457    #[test]
458    fn detect_statements_semicolon_in_line_comment() {
459        let stmts = detect_statements("SELECT 1 -- ; comment\n; SELECT 2");
460        assert_eq!(stmts, vec!["SELECT 1 -- ; comment", "SELECT 2"]);
461    }
462
463    #[test]
464    fn detect_statements_semicolon_in_block_comment() {
465        let stmts = detect_statements("SELECT /* ; */ 1; SELECT 2");
466        assert_eq!(stmts, vec!["SELECT /* ; */ 1", "SELECT 2"]);
467    }
468
469    #[test]
470    fn detect_statements_escaped_single_quote() {
471        let stmts = detect_statements("SELECT 'it''s'; SELECT 2");
472        assert_eq!(stmts, vec!["SELECT 'it''s'", "SELECT 2"]);
473    }
474
475    #[test]
476    fn detect_statements_empty_input() {
477        let stmts = detect_statements("");
478        assert!(stmts.is_empty());
479    }
480
481    #[test]
482    fn detect_statements_whitespace_only() {
483        let stmts = detect_statements("  \n  ");
484        assert!(stmts.is_empty());
485    }
486
487    #[test]
488    fn detect_statements_doubled_double_quotes() {
489        let stmts = detect_statements(r#"SELECT "col""name" FROM t; SELECT 2"#);
490        assert_eq!(stmts, vec![r#"SELECT "col""name" FROM t"#, "SELECT 2"]);
491    }
492
493    #[test]
494    fn detect_statements_consecutive_semicolons_mid_input() {
495        let stmts = detect_statements("SELECT 1;; SELECT 2");
496        assert_eq!(stmts, vec!["SELECT 1", "SELECT 2"]);
497    }
498
499    #[test]
500    fn detect_statements_unclosed_string() {
501        // Graceful degradation — treats rest as one statement
502        let stmts = detect_statements("SELECT 'unclosed; SELECT 2");
503        assert_eq!(stmts, vec!["SELECT 'unclosed; SELECT 2"]);
504    }
505
506    #[test]
507    fn detect_statements_unclosed_block_comment() {
508        let stmts = detect_statements("SELECT /* unclosed; SELECT 2");
509        assert_eq!(stmts, vec!["SELECT /* unclosed; SELECT 2"]);
510    }
511
512    // ── strip_comments tests ──────────────────────────────────────────
513
514    #[test]
515    fn strip_comments_known_limitation_string_literals() {
516        // Known limitation: comment-like sequences inside string literals ARE stripped.
517        // The `--` is treated as a line comment, consuming everything to the newline.
518        // This is acceptable because strip_comments is only used for editability
519        // detection (detect_source_table), not for SQL execution.
520        let result = strip_comments("SELECT '-- not a comment' FROM t");
521        // Documents actual behavior: the line-comment eats the rest of the line
522        assert_eq!(result, "SELECT '");
523    }
524
525    // ── parse_foreign_keys tests ──────────────────────────────────────
526
527    #[test]
528    fn parse_fk_single_constraint() {
529        let sql = r"CREATE TABLE employees (
530            id INTEGER PRIMARY KEY,
531            name TEXT NOT NULL,
532            department_id INTEGER NOT NULL,
533            FOREIGN KEY (department_id) REFERENCES departments (id)
534        )";
535        let fks = parse_foreign_keys(sql);
536        assert_eq!(fks.len(), 1);
537        assert_eq!(fks[0].from_column, "department_id");
538        assert_eq!(fks[0].to_table, "departments");
539        assert_eq!(fks[0].to_column, "id");
540    }
541
542    #[test]
543    fn parse_fk_multiple_constraints() {
544        let sql = r"CREATE TABLE project_assignments (
545            id INTEGER PRIMARY KEY,
546            employee_id INTEGER NOT NULL,
547            project_id INTEGER NOT NULL,
548            FOREIGN KEY (employee_id) REFERENCES employees (id),
549            FOREIGN KEY (project_id) REFERENCES projects (id)
550        )";
551        let fks = parse_foreign_keys(sql);
552        assert_eq!(fks.len(), 2);
553        assert_eq!(fks[0].from_column, "employee_id");
554        assert_eq!(fks[0].to_table, "employees");
555        assert_eq!(fks[0].to_column, "id");
556        assert_eq!(fks[1].from_column, "project_id");
557        assert_eq!(fks[1].to_table, "projects");
558        assert_eq!(fks[1].to_column, "id");
559    }
560
561    #[test]
562    fn parse_fk_no_foreign_keys() {
563        let sql = "CREATE TABLE simple (id INTEGER PRIMARY KEY, name TEXT)";
564        let fks = parse_foreign_keys(sql);
565        assert!(fks.is_empty());
566    }
567
568    #[test]
569    fn parse_fk_quoted_identifiers() {
570        let sql = r#"CREATE TABLE "my table" (
571            id INTEGER PRIMARY KEY,
572            "ref_id" INTEGER,
573            FOREIGN KEY ("ref_id") REFERENCES "other table" ("pk_col")
574        )"#;
575        let fks = parse_foreign_keys(sql);
576        assert_eq!(fks.len(), 1);
577        assert_eq!(fks[0].from_column, "ref_id");
578        assert_eq!(fks[0].to_table, "other table");
579        assert_eq!(fks[0].to_column, "pk_col");
580    }
581
582    #[test]
583    fn parse_fk_quoted_table_then_second_fk() {
584        // Regression: the first FK has a quoted table name. The old code advanced
585        // search_from by to_table.len() (unquoted content) instead of the full
586        // quoted span, causing the second FK to be missed or misparsed.
587        let sql = r#"CREATE TABLE t (
588            a INTEGER, b INTEGER,
589            FOREIGN KEY (a) REFERENCES "other table" (id),
590            FOREIGN KEY (b) REFERENCES second (id)
591        )"#;
592        let fks = parse_foreign_keys(sql);
593        assert_eq!(fks.len(), 2);
594        assert_eq!(fks[0].from_column, "a");
595        assert_eq!(fks[0].to_table, "other table");
596        assert_eq!(fks[0].to_column, "id");
597        assert_eq!(fks[1].from_column, "b");
598        assert_eq!(fks[1].to_table, "second");
599        assert_eq!(fks[1].to_column, "id");
600    }
601
602    #[test]
603    fn parse_fk_doubled_quote_in_identifier() {
604        // SQL standard: doubled quotes inside a quoted identifier represent a literal quote.
605        // "col""name" → col"name
606        let sql = r#"CREATE TABLE t (
607            id INTEGER PRIMARY KEY,
608            ref_id INTEGER,
609            FOREIGN KEY (ref_id) REFERENCES "has""quote" (id)
610        )"#;
611        let fks = parse_foreign_keys(sql);
612        assert_eq!(fks.len(), 1);
613        assert_eq!(fks[0].from_column, "ref_id");
614        assert_eq!(fks[0].to_table, "has\"quote");
615        assert_eq!(fks[0].to_column, "id");
616    }
617
618    #[test]
619    fn extract_identifier_non_ascii() {
620        // Non-ASCII characters in quoted identifiers must be preserved correctly.
621        let (name, rest) = extract_identifier(r#""café" extra"#);
622        assert_eq!(name, "café");
623        assert_eq!(rest, " extra");
624    }
625
626    #[test]
627    fn parse_fk_inline_column_constraint() {
628        let sql = "CREATE TABLE t (id INTEGER PRIMARY KEY, ref_id INTEGER REFERENCES other(id))";
629        let fks = parse_foreign_keys(sql);
630        assert_eq!(fks.len(), 1);
631        assert_eq!(fks[0].from_column, "ref_id");
632        assert_eq!(fks[0].to_table, "other");
633        assert_eq!(fks[0].to_column, "id");
634    }
635
636    #[test]
637    fn parse_fk_with_on_delete_cascade() {
638        let sql = r"CREATE TABLE orders (
639            id INTEGER PRIMARY KEY,
640            user_id INTEGER NOT NULL,
641            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
642        )";
643        let fks = parse_foreign_keys(sql);
644        assert_eq!(fks.len(), 1);
645        assert_eq!(fks[0].from_column, "user_id");
646        assert_eq!(fks[0].to_table, "users");
647        assert_eq!(fks[0].to_column, "id");
648    }
649
650    #[test]
651    fn parse_fk_case_insensitive() {
652        let sql = "CREATE TABLE t (id INTEGER PRIMARY KEY, ref_id INTEGER, foreign key (ref_id) references other (id))";
653        let fks = parse_foreign_keys(sql);
654        assert_eq!(fks.len(), 1);
655        assert_eq!(fks[0].from_column, "ref_id");
656        assert_eq!(fks[0].to_table, "other");
657        assert_eq!(fks[0].to_column, "id");
658    }
659
660    #[test]
661    fn parse_fk_inline_with_not_null() {
662        let sql = r"CREATE TABLE albums (
663            id INTEGER PRIMARY KEY,
664            artist_id INTEGER NOT NULL REFERENCES artists (id),
665            title TEXT NOT NULL
666        )";
667        let fks = parse_foreign_keys(sql);
668        assert_eq!(fks.len(), 1);
669        assert_eq!(fks[0].from_column, "artist_id");
670        assert_eq!(fks[0].to_table, "artists");
671        assert_eq!(fks[0].to_column, "id");
672    }
673
674    #[test]
675    fn parse_fk_inline_multiple() {
676        // Multiple inline FKs in the same table (like demo.db tracks table)
677        let sql = r"CREATE TABLE tracks (
678            id INTEGER PRIMARY KEY,
679            album_id INTEGER NOT NULL REFERENCES albums (id),
680            featured_artist_id INTEGER REFERENCES artists (id)
681        )";
682        let fks = parse_foreign_keys(sql);
683        assert_eq!(fks.len(), 2);
684        assert_eq!(fks[0].from_column, "album_id");
685        assert_eq!(fks[0].to_table, "albums");
686        assert_eq!(fks[0].to_column, "id");
687        assert_eq!(fks[1].from_column, "featured_artist_id");
688        assert_eq!(fks[1].to_table, "artists");
689        assert_eq!(fks[1].to_column, "id");
690    }
691
692    #[test]
693    fn parse_fk_inline_quoted_table() {
694        let sql =
695            r#"CREATE TABLE t (id INTEGER PRIMARY KEY, ref_id INTEGER REFERENCES "My Table" (id))"#;
696        let fks = parse_foreign_keys(sql);
697        assert_eq!(fks.len(), 1);
698        assert_eq!(fks[0].from_column, "ref_id");
699        assert_eq!(fks[0].to_table, "My Table");
700        assert_eq!(fks[0].to_column, "id");
701    }
702
703    #[test]
704    fn parse_fk_inline_case_insensitive() {
705        let sql = "CREATE TABLE t (id INTEGER PRIMARY KEY, ref_id INTEGER references other(id))";
706        let fks = parse_foreign_keys(sql);
707        assert_eq!(fks.len(), 1);
708        assert_eq!(fks[0].from_column, "ref_id");
709        assert_eq!(fks[0].to_table, "other");
710        assert_eq!(fks[0].to_column, "id");
711    }
712
713    #[test]
714    fn parse_fk_mixed_inline_and_table_level() {
715        // Both inline and table-level FKs in the same CREATE TABLE — no duplicates
716        let sql = r"CREATE TABLE t (
717            id INTEGER PRIMARY KEY,
718            a_id INTEGER REFERENCES a (id),
719            b_id INTEGER NOT NULL,
720            FOREIGN KEY (b_id) REFERENCES b (id)
721        )";
722        let fks = parse_foreign_keys(sql);
723        assert_eq!(fks.len(), 2);
724        // Table-level FK comes first (parsed in first pass)
725        assert_eq!(fks[0].from_column, "b_id");
726        assert_eq!(fks[0].to_table, "b");
727        // Inline FK comes second (parsed in second pass)
728        assert_eq!(fks[1].from_column, "a_id");
729        assert_eq!(fks[1].to_table, "a");
730    }
731
732    #[test]
733    fn parse_fk_inline_composite_pk_table() {
734        // Like demo.db playlist_tracks — composite PK with inline FKs
735        let sql = r"CREATE TABLE playlist_tracks (
736            playlist_id INTEGER NOT NULL REFERENCES playlists (id),
737            track_id INTEGER NOT NULL REFERENCES tracks (id),
738            position INTEGER NOT NULL,
739            PRIMARY KEY (playlist_id, track_id)
740        )";
741        let fks = parse_foreign_keys(sql);
742        assert_eq!(fks.len(), 2);
743        assert_eq!(fks[0].from_column, "playlist_id");
744        assert_eq!(fks[0].to_table, "playlists");
745        assert_eq!(fks[1].from_column, "track_id");
746        assert_eq!(fks[1].to_table, "tracks");
747    }
748
749    #[test]
750    fn parse_fk_real_testdata_employees() {
751        // Exact SQL from testdb/testdata.db
752        let sql = "CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, department_id INTEGER NOT NULL, salary REAL NOT NULL, hire_date TEXT NOT NULL, title TEXT NOT NULL, FOREIGN KEY (department_id) REFERENCES departments (id))";
753        let fks = parse_foreign_keys(sql);
754        assert_eq!(fks.len(), 1);
755        assert_eq!(fks[0].from_column, "department_id");
756        assert_eq!(fks[0].to_table, "departments");
757        assert_eq!(fks[0].to_column, "id");
758    }
759
760    #[test]
761    fn parse_fk_real_testdata_project_assignments() {
762        // Exact SQL from testdb/testdata.db
763        let sql = "CREATE TABLE project_assignments (id INTEGER PRIMARY KEY, employee_id INTEGER NOT NULL, project_id INTEGER NOT NULL, role TEXT NOT NULL, hours_allocated REAL NOT NULL DEFAULT 40.0, FOREIGN KEY (employee_id) REFERENCES employees (id), FOREIGN KEY (project_id) REFERENCES projects (id))";
764        let fks = parse_foreign_keys(sql);
765        assert_eq!(fks.len(), 2);
766        assert_eq!(fks[0].from_column, "employee_id");
767        assert_eq!(fks[0].to_table, "employees");
768        assert_eq!(fks[1].from_column, "project_id");
769        assert_eq!(fks[1].to_table, "projects");
770    }
771
772    // ── detect_source_table tests ────────────────────────────────────
773
774    #[test]
775    fn test_simple_select_is_editable() {
776        assert_eq!(
777            detect_source_table("SELECT * FROM users"),
778            Some("users".to_string())
779        );
780    }
781
782    #[test]
783    fn test_select_with_where_is_editable() {
784        assert_eq!(
785            detect_source_table("SELECT * FROM users WHERE id = 1"),
786            Some("users".to_string())
787        );
788    }
789
790    #[test]
791    fn test_select_with_limit() {
792        assert_eq!(
793            detect_source_table("SELECT * FROM \"users\" LIMIT 100;"),
794            Some("users".to_string())
795        );
796    }
797
798    #[test]
799    fn test_join_is_not_editable() {
800        assert_eq!(detect_source_table("SELECT * FROM users JOIN orders"), None);
801    }
802
803    #[test]
804    fn test_union_is_not_editable() {
805        assert_eq!(
806            detect_source_table("SELECT * FROM users UNION SELECT * FROM admins"),
807            None
808        );
809    }
810
811    #[test]
812    fn test_group_by_is_not_editable() {
813        assert_eq!(
814            detect_source_table("SELECT count(*) FROM users GROUP BY role"),
815            None
816        );
817    }
818
819    #[test]
820    fn test_cte_is_not_editable() {
821        assert_eq!(
822            detect_source_table("WITH cte AS (SELECT * FROM users) SELECT * FROM cte"),
823            None
824        );
825    }
826
827    #[test]
828    fn test_subquery_in_from_is_not_editable() {
829        assert_eq!(
830            detect_source_table("SELECT * FROM (SELECT * FROM users)"),
831            None
832        );
833    }
834
835    #[test]
836    fn test_non_select_is_not_editable() {
837        assert_eq!(detect_source_table("INSERT INTO users VALUES (1)"), None);
838    }
839
840    #[test]
841    fn test_select_with_comments_is_editable() {
842        assert_eq!(
843            detect_source_table("-- comment\nSELECT * FROM users"),
844            Some("users".to_string())
845        );
846    }
847
848    #[test]
849    fn test_block_comment() {
850        assert_eq!(
851            detect_source_table("/* comment */ SELECT * FROM users"),
852            Some("users".to_string())
853        );
854    }
855
856    #[test]
857    fn test_quoted_table_name() {
858        assert_eq!(
859            detect_source_table("SELECT * FROM \"my table\""),
860            Some("my table".to_string())
861        );
862    }
863
864    #[test]
865    fn test_backtick_quoted_table_name() {
866        assert_eq!(
867            detect_source_table("SELECT * FROM `my table`"),
868            Some("my table".to_string())
869        );
870    }
871
872    #[test]
873    fn test_case_insensitive_keywords() {
874        assert_eq!(
875            detect_source_table("select * from Users"),
876            Some("Users".to_string())
877        );
878    }
879
880    /// Per spec: keyword rejection is simple string containment (space-separated "GROUP BY").
881    /// The identifier `my_group_by_stats` uses underscores, so "GROUP BY" (with a space)
882    /// does NOT appear in the query — this table is correctly treated as editable.
883    /// This test documents the boundary: underscore-separated names are not false-negatives.
884    #[test]
885    fn test_keyword_in_identifier_false_negative() {
886        // "GROUP BY" (with space) is NOT in "my_group_by_stats" (underscores) → Some
887        assert_eq!(
888            detect_source_table("SELECT * FROM my_group_by_stats"),
889            Some("my_group_by_stats".to_string())
890        );
891    }
892
893    #[test]
894    fn test_intersect_rejected() {
895        assert_eq!(
896            detect_source_table("SELECT * FROM a INTERSECT SELECT * FROM b"),
897            None
898        );
899    }
900
901    #[test]
902    fn test_except_rejected() {
903        assert_eq!(
904            detect_source_table("SELECT * FROM a EXCEPT SELECT * FROM b"),
905            None
906        );
907    }
908
909    #[test]
910    fn test_with_clause_rejected() {
911        assert_eq!(
912            detect_source_table("WITH t AS (SELECT 1) SELECT * FROM t"),
913            None
914        );
915    }
916
917    #[test]
918    fn test_empty_query() {
919        assert_eq!(detect_source_table(""), None);
920    }
921
922    #[test]
923    fn test_whitespace_only() {
924        assert_eq!(detect_source_table("   "), None);
925    }
926}
tursotui_sql/parser.rs

tursotui_sql/
parser.rs
