SQL Playground — Ruby source

# sql-playground — SQL statement splitting, read-only classification, CSV / Markdown serialization (Ruby).

READ_ONLY = /\A(SELECT|WITH|VALUES|EXPLAIN|PRAGMA)\b/i

# Split on ';' outside '...' literals; a doubled '' inside a string is an escaped quote.
def split_statements(sql)
  stmts = []
  current = +''
  in_string = false
  i = 0
  while i < sql.length
    ch = sql[i]
    if in_string
      current << ch
      if ch == "'"
        if sql[i + 1] == "'"
          current << sql[i + 1]
          i += 1 # '' escape — consume both characters
        else
          in_string = false
        end
      end
    elsif ch == "'"
      in_string = true
      current << ch
    elsif ch == ';'
      stmts << current.strip unless current.strip.empty?
      current = +''
    else
      current << ch
    end
    i += 1
  end
  stmts << current.strip unless current.strip.empty?
  stmts
end

# A leading SELECT / WITH / VALUES / EXPLAIN / PRAGMA marks the statement read-only.
def read_only?(stmt)
  stmt.strip.match?(READ_ONLY)
end

# RFC-4180-ish CSV field: quote when it holds , " CR or LF; double embedded quotes.
# A nil cell is SQL NULL and renders as that text.
def csv_field(cell)
  v = cell || 'NULL'
  return v unless v.match?(/[,\"\r\n]/) # quote only , " CR LF

  '"' + v.gsub('"', '""') + '"'
end

def rows_to_csv(columns, rows)
  lines = [columns.map { |c| csv_field(c) }.join(',')]
  rows.each { |row| lines << row.map { |cell| csv_field(cell) }.join(',') }
  lines.join("\n") + "\n"
end

# GitHub-flavored Markdown table; pipes inside cells are backslash-escaped.
def rows_to_markdown(columns, rows)
  esc = ->(v) { (v || 'NULL').gsub('|', '\|') }
  lines = [
    "| #{columns.map { |c| esc.call(c) }.join(' | ')} |",
    "| #{columns.map { '---' }.join(' | ')} |"
  ]
  rows.each { |row| lines << "| #{row.map { |cell| esc.call(cell) }.join(' | ')} |" }
  lines.join("\n") + "\n"
end

script = "CREATE TABLE t (id INT);  SELECT * FROM t WHERE note = 'a''b;c' ; ;DROP TABLE t"
split_statements(script).each { |s| puts "[#{read_only?(s) ? 'read-only' : 'write'}] #{s}" }
puts
print rows_to_csv(%w[name note], [['O\'Brien', 'has, "quotes"'], [nil, 'pipe|inside']])
print rows_to_markdown(%w[name note], [['O\'Brien', 'has, "quotes"'], [nil, 'pipe|inside']])