splitSqlStatements method

List<String> splitSqlStatements(String sql)

Robust SQL splitter (fixed dollar-quote detection).

Implementation

List<String> splitSqlStatements(String sql) {
  final List<String> out = [];
  final int n = sql.length;
  if (n == 0) return out;

  String delimiter = ';';
  int start = 0;

  bool inSingle = false;
  bool inDouble = false;
  bool inBacktick = false;
  bool inLineComment = false;
  bool inBlockComment = false;
  bool escape = false;

  String? dollarTag; // e.g. $abc$
  bool atLineStart = true;

  String? readDelimiterAt(int pos) {
    const keyword = 'DELIMITER';
    if (pos + keyword.length > n) return null;
    final part = sql.substring(pos, pos + keyword.length);
    if (part.toUpperCase() != keyword) return null;
    int q = pos + keyword.length;
    if (q < n &&
        sql[q] != ' ' &&
        sql[q] != '\t' &&
        sql[q] != '\r' &&
        sql[q] != '\n') {
      return null;
    }
    while (q < n && (sql[q] == ' ' || sql[q] == '\t')) {
      q++;
    }
    int r = q;
    while (r < n && sql[r] != '\n' && sql[r] != '\r') {
      r++;
    }
    final newDelim = sql.substring(q, r).trim();
    return newDelim.isEmpty ? null : newDelim;
  }

  bool isTagChar(String ch) {
    // letters, digits, underscore
    final code = ch.codeUnitAt(0);
    return (code >= 48 && code <= 57) || // 0-9
        (code >= 65 && code <= 90) || // A-Z
        (code >= 97 && code <= 122) || // a-z
        (code == 95); // _
  }

  int i = 0;
  while (i < n) {
    final ch = sql[i];
    final next = (i + 1 < n) ? sql[i + 1] : null;

    if (inLineComment) {
      if (ch == '\n' || ch == '\r') {
        inLineComment = false;
        atLineStart = true;
      }
      i++;
      continue;
    }

    if (inBlockComment) {
      if (ch == '*' && next == '/') {
        inBlockComment = false;
        i += 2;
        atLineStart = false;
        continue;
      }
      if (ch == '\n' || ch == '\r') {
        atLineStart = true;
      } else if (ch != ' ' && ch != '\t') {
        atLineStart = false;
      }
      i++;
      continue;
    }

    if (escape) {
      escape = false;
      atLineStart = false;
      i++;
      continue;
    }

    if (atLineStart) {
      int j = i;
      while (j < n && (sql[j] == ' ' || sql[j] == '\t')) {
        j++;
      }
      final newDelim = readDelimiterAt(j);
      if (newDelim != null) {
        int k = j;
        while (k < n && sql[k] != '\n' && sql[k] != '\r') {
          k++;
        }
        i = k + 1;
        start = i;
        delimiter = newDelim;
        atLineStart = true;
        continue;
      }
    }

    if (!inSingle && !inDouble && !inBacktick) {
      if (ch == '-' && next == '-') {
        inLineComment = true;
        i += 2;
        continue;
      }
      if (ch == '#') {
        inLineComment = true;
        i++;
        continue;
      }
      if (ch == '/' && next == '*') {
        inBlockComment = true;
        i += 2;
        continue;
      }
    }

    // dollar-quoted string detection (fixed)
    if (!inSingle && !inDouble && !inBacktick && ch == r'$') {
      int k = i + 1;
      // read tag name characters (letters/digits/underscore), if any
      while (k < n && isTagChar(sql[k])) {
        k++;
      }
      if (k < n && sql[k] == r'$') {
        // we have a tag like $tag$
        final tag = sql.substring(i, k + 1);
        // if not currently in a dollar-quote, open it; if already opened it would be closed by matching later
        if (dollarTag == null) {
          dollarTag = tag;
          i = k + 1;
          atLineStart = false;
          continue;
        }
      }
    }

    if (dollarTag != null) {
      final tagLen = dollarTag.length;
      if (i + tagLen <= n && sql.substring(i, i + tagLen) == dollarTag) {
        dollarTag = null;
        i += tagLen;
        atLineStart = false;
        continue;
      } else {
        if (ch == '\n' || ch == '\r') {
          atLineStart = true;
        } else if (ch != ' ' && ch != '\t') {
          atLineStart = false;
        }
        i++;
        continue;
      }
    }

    if (!inDouble && !inBacktick && ch == "'") {
      if (inSingle) {
        if (next == "'") {
          i += 2;
          atLineStart = false;
          continue;
        } else {
          inSingle = false;
          i++;
          atLineStart = false;
          continue;
        }
      } else {
        inSingle = true;
        i++;
        atLineStart = false;
        continue;
      }
    }

    if (!inSingle && !inBacktick && ch == '"') {
      if (inDouble) {
        if (next == '"') {
          i += 2;
          atLineStart = false;
          continue;
        } else {
          inDouble = false;
          i++;
          atLineStart = false;
          continue;
        }
      } else {
        inDouble = true;
        i++;
        atLineStart = false;
        continue;
      }
    }

    if (!inSingle && !inDouble && ch == '`') {
      inBacktick = !inBacktick;
      i++;
      atLineStart = false;
      continue;
    }

    if ((inSingle || inDouble) && ch == r'\') {
      escape = true;
      i++;
      atLineStart = false;
      continue;
    }

    if (ch == '\n' || ch == '\r') {
      atLineStart = true;
    } else if (ch != ' ' && ch != '\t') {
      atLineStart = false;
    }

    if (!inSingle &&
        !inDouble &&
        !inBacktick &&
        !inLineComment &&
        !inBlockComment &&
        dollarTag == null) {
      final dlen = delimiter.length;
      if (dlen > 0 && i + dlen <= n) {
        if (sql.substring(i, i + dlen) == delimiter) {
          final stmt = sql.substring(start, i).trim();
          if (stmt.isNotEmpty) out.add(stmt);
          i = i + dlen;
          start = i;
          atLineStart = true;
          continue;
        }
      }
    }

    i++;
  }

  if (start < n) {
    final tail = sql.substring(start).trim();
    if (tail.isNotEmpty) out.add(tail);
  }

  return out;
}