SQL Playground — Kotlin source

// sql-playground — SQL statement splitting, read-only classification, CSV / Markdown serialization (Kotlin).
import java.util.regex.Pattern

private val READ_ONLY = Pattern.compile("^(SELECT|WITH|VALUES|EXPLAIN|PRAGMA)\\b", Pattern.CASE_INSENSITIVE)

// Split on ';' outside '...' literals; a doubled '' inside a string is an escaped quote.
fun splitStatements(sql: String): List<String> {
    val stmts = mutableListOf<String>()
    val current = StringBuilder()
    var inString = false
    var i = 0
    while (i < sql.length) {
        val ch = sql[i]
        when {
            inString -> {
                current.append(ch)
                if (ch == '\'') {
                    if (i + 1 < sql.length && sql[i + 1] == '\'') current.append(sql[++i]) // '' escape
                    else inString = false
                }
            }
            ch == '\'' -> { inString = true; current.append(ch) }
            ch == ';' -> {
                val t = current.toString().trim()
                if (t.isNotEmpty()) stmts += t
                current.setLength(0)
            }
            else -> current.append(ch)
        }
        i++
    }
    val t = current.toString().trim()
    if (t.isNotEmpty()) stmts += t
    return stmts
}

// A leading SELECT / WITH / VALUES / EXPLAIN / PRAGMA marks the statement read-only.
fun isReadOnlyStatement(stmt: String) = READ_ONLY.matcher(stmt.trim()).find()

// 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.
private fun csvField(cell: String?): String {
    val v = cell ?: "NULL"
    return if (Regex("[,\"\r\n]").containsMatchIn(v)) "\"${v.replace("\"", "\"\"")}\"" else v
}

fun rowsToCsv(columns: List<String>, rows: List<List<String?>>): String {
    val lines = mutableListOf(columns.joinToString(",") { csvField(it) })
    rows.forEach { row -> lines += row.joinToString(",") { csvField(it) } }
    return lines.joinToString("\n") + "\n"
}

// GitHub-flavored Markdown table; pipes inside cells are backslash-escaped.
fun rowsToMarkdown(columns: List<String>, rows: List<List<String?>>): String {
    fun cell(v: String?) = (v ?: "NULL").replace("|", "\\|")
    val header = "| " + columns.joinToString(" | ") + " |"
    val sep = "| " + columns.joinToString(" | ") { "---" } + " |"
    val body = rows.joinToString("\n") { row -> "| " + row.joinToString(" | ") { cell(it) } + " |" }
    return listOf(header, sep, body).joinToString("\n") + "\n"
}

fun main() {
    val script = "CREATE TABLE t (id INT);  SELECT * FROM t WHERE note = 'a''b;c' ; ;DROP TABLE t"
    for (s in splitStatements(script)) {
        println("[${if (isReadOnlyStatement(s)) "read-only" else "write"}] $s")
    }
    val cols = listOf("name", "note")
    val rows = listOf(listOf("O'Brien", "has, \"quotes\""), listOf(null, "pipe|inside"))
    println()
    print(rowsToCsv(cols, rows))
    print(rowsToMarkdown(cols, rows))
}