SQL Playground — C# source

// sql-playground — SQL statement splitting, read-only classification, CSV / Markdown serialization (C#).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace SqlPlayground;

public static class SqlPlayground
{
    // Split on ';' outside '...' literals; a doubled '' inside a string is an escaped quote.
    public static List<string> SplitStatements(string sql) {
        var stmts = new List<string>();
        var current = new StringBuilder();
        bool inString = false;
        for (var i = 0; i < sql.Length; i++) {
            var ch = sql[i];
            if (inString) {
                current.Append(ch);
                if (ch == '\'') {
                    if (i + 1 < sql.Length && sql[i + 1] == '\'') current.Append(sql[++i]); // '' escape
                    else inString = false;
                }
            } else if (ch == '\'') {
                inString = true;
                current.Append(ch);
            } else if (ch == ';') {
                var t = current.ToString().Trim();
                if (t.Length > 0) stmts.Add(t);
                current.Clear();
            } else {
                current.Append(ch);
            }
        }
        var last = current.ToString().Trim();
        if (last.Length > 0) stmts.Add(last);
        return stmts;
    }

    // A leading SELECT / WITH / VALUES / EXPLAIN / PRAGMA marks the statement read-only.
    public static bool IsReadOnlyStatement(string stmt) =>
        Regex.IsMatch(stmt.Trim(), @"^(SELECT|WITH|VALUES|EXPLAIN|PRAGMA)\b", RegexOptions.IgnoreCase);

    // RFC-4180-ish CSV field: quote when it holds , " CR or LF; double embedded quotes.
    // A null cell is SQL NULL and renders as that text.
    static string CsvField(string? cell) {
        var v = cell ?? "NULL";
        return v.IndexOfAny(",\"\r\n".ToCharArray()) < 0 ? v : '"' + v.Replace("\"", "\"\"") + '"';
    }

    public static string RowsToCsv(string[] columns, string?[][] rows) {
        var lines = new List<string> { string.Join(",", columns.Select(CsvField)) };
        lines.AddRange(rows.Select(row => string.Join(",", row.Select(CsvField))));
        return string.Join("\n", lines) + "\n";
    }

    // GitHub-flavored Markdown table; pipes inside cells are backslash-escaped.
    public static string RowsToMarkdown(string[] columns, string?[][] rows) {
        string Cell(string? v) => (v ?? "NULL").Replace("|", "\\|");
        var header = "| " + string.Join(" | ", columns) + " |";
        var sep = "| " + string.Join(" | ", columns.Select(_ => "---")) + " |";
        var body = rows.Select(row => "| " + string.Join(" | ", row.Select(Cell)) + " |");
        return string.Join("\n", new[] { header, sep }.Concat(body)) + "\n";
    }

    public static void Main() {
        const string script = "CREATE TABLE t (id INT);  SELECT * FROM t WHERE note = 'a''b;c' ; ;DROP TABLE t";
        foreach (var s in SplitStatements(script))
            Console.WriteLine($"[{(IsReadOnlyStatement(s) ? "read-only" : "write")}] {s}");
        var cols = new[] { "name", "note" };
        string?[][] rows = { new[] { "O'Brien", "has, \"quotes\"" }, new string?[] { null, "pipe|inside" } };
        Console.WriteLine();
        Console.Write(RowsToCsv(cols, rows));
        Console.Write(RowsToMarkdown(cols, rows));
    }
}