ch-database 0.2.1

Database and object storage primitives for SQLx, SpacetimeDB, and S3
Documentation
/// Splits a migration script into individual statements and trims whitespace.
///
/// Unlike a plain `split(';')`, this parser ignores semicolons inside:
/// - single-quoted strings
/// - double-quoted strings
/// - backtick identifiers
/// - line comments (`-- ...`, `# ...`)
/// - block comments (`/* ... */`)
pub fn iter_migration_statements(sql: &str) -> impl Iterator<Item = &str> {
    #[derive(Clone, Copy, PartialEq, Eq)]
    enum Mode {
        Normal,
        SingleQuote,
        DoubleQuote,
        Backtick,
        LineComment,
        BlockComment,
    }

    let bytes = sql.as_bytes();
    let mut mode = Mode::Normal;
    let mut statement_start = 0usize;
    let mut i = 0usize;
    std::iter::from_fn(move || {
        while i < bytes.len() {
            let b = bytes[i];
            let next = bytes.get(i + 1).copied();

            match mode {
                Mode::Normal => match b {
                    b'\'' => mode = Mode::SingleQuote,
                    b'"' => mode = Mode::DoubleQuote,
                    b'`' => mode = Mode::Backtick,
                    b'-' if next == Some(b'-')
                        && bytes.get(i + 2).is_none_or(|byte| {
                            byte.is_ascii_whitespace() || byte.is_ascii_control()
                        }) =>
                    {
                        mode = Mode::LineComment;
                        i += 1;
                    }
                    b'#' => mode = Mode::LineComment,
                    b'/' if next == Some(b'*') => {
                        mode = Mode::BlockComment;
                        i += 1;
                    }
                    b';' => {
                        let statement = sql[statement_start..i]
                            .trim()
                            .trim_start_matches('\u{feff}')
                            .trim();
                        statement_start = i + 1;
                        if !statement.is_empty() {
                            i += 1;
                            return Some(statement);
                        }
                    }
                    _ => {}
                },
                Mode::SingleQuote => {
                    if b == b'\\' {
                        // Skip escaped character in string.
                        i += 1;
                    } else if b == b'\'' {
                        mode = Mode::Normal;
                    }
                }
                Mode::DoubleQuote => {
                    if b == b'\\' {
                        i += 1;
                    } else if b == b'"' {
                        mode = Mode::Normal;
                    }
                }
                Mode::Backtick => {
                    if b == b'`' {
                        mode = Mode::Normal;
                    }
                }
                Mode::LineComment => {
                    if b == b'\n' || b == b'\r' {
                        mode = Mode::Normal;
                    }
                }
                Mode::BlockComment => {
                    if b == b'*' && next == Some(b'/') {
                        mode = Mode::Normal;
                        i += 1;
                    }
                }
            }

            i += 1;
        }

        let tail = sql[statement_start..]
            .trim()
            .trim_start_matches('\u{feff}')
            .trim();
        statement_start = bytes.len();
        (!tail.is_empty()).then_some(tail)
    })
}

#[cfg(test)]
mod tests {
    use super::iter_migration_statements;

    #[test]
    fn distinguishes_mysql_comments_from_subtraction() {
        assert_eq!(
            iter_migration_statements("SELECT 3--1; SELECT 2; -- comment;\nSELECT 4;")
                .collect::<Vec<_>>(),
            ["SELECT 3--1", "SELECT 2", "-- comment;\nSELECT 4"]
        );
    }

    #[test]
    fn handles_empty_statements_and_repeated_exhaustion() {
        let mut statements = iter_migration_statements(";; SELECT 1;; SELECT 2 ");
        assert_eq!(statements.next(), Some("SELECT 1"));
        assert_eq!(statements.next(), Some("SELECT 2"));
        assert_eq!(statements.next(), None);
        assert_eq!(statements.next(), None);
    }

    #[test]
    fn preserves_quoted_escapes_unicode_and_hash_comments() {
        let sql = "SELECT 'it''s; gültig', `a``;b`; # comment;\nSELECT 'a\\\';b';";
        assert_eq!(iter_migration_statements(sql).count(), 2);
    }

    #[test]
    fn strips_utf8_bom_from_first_statement() {
        let sql = "\u{feff}CREATE TABLE first (id INT); CREATE TABLE second (id INT);";
        let statements: Vec<_> = iter_migration_statements(sql).collect();
        assert_eq!(
            statements,
            vec![
                "CREATE TABLE first (id INT)",
                "CREATE TABLE second (id INT)"
            ]
        );
    }

    #[test]
    fn ignores_semicolons_in_line_comments() {
        let sql = "-- comment with ; inside\nCREATE TABLE t1 (id INT);";
        let statements: Vec<_> = iter_migration_statements(sql).collect();
        assert_eq!(
            statements,
            vec!["-- comment with ; inside\nCREATE TABLE t1 (id INT)"]
        );
    }

    #[test]
    fn ignores_semicolons_in_block_comments() {
        let sql = "/* comment ; inside */\nCREATE TABLE t1 (id INT);";
        let statements: Vec<_> = iter_migration_statements(sql).collect();
        assert_eq!(
            statements,
            vec!["/* comment ; inside */\nCREATE TABLE t1 (id INT)"]
        );
    }

    #[test]
    fn ignores_semicolons_in_strings() {
        let sql = "INSERT INTO t1 (txt) VALUES ('a;b;c'); INSERT INTO t1 (txt) VALUES (\"x;y\");";
        let statements: Vec<_> = iter_migration_statements(sql).collect();
        assert_eq!(
            statements,
            vec![
                "INSERT INTO t1 (txt) VALUES ('a;b;c')",
                "INSERT INTO t1 (txt) VALUES (\"x;y\")",
            ]
        );
    }
}