JSON to SQL INSERT — Zig source

//! json-to-sql — pure JSON → SQL INSERT converter. Zig port (canonical TS:
//! src/lib/jsonToSql.ts; Go twin: cli/json-to-sql). Uses std.json.Value whose
//! ObjectMap is a StringArrayHashMap — first-seen key order survives, so the
//! column union matches the TS reference. Parse failures return through Res;
//! only allocation errors propagate as Zig errors.
const std = @import("std");
const Value = std.json.Value;
pub const Res = struct { ok: bool, sql: []const u8, rows: usize, err: ?[]const u8 = null };

/// Escape for a single-quoted SQL literal: '' doubling everywhere; MySQL mode
/// additionally escapes the backslash and statement-terminating control bytes.
pub fn escapeSqlString(alloc: std.mem.Allocator, s: []const u8, mysql: bool) ![]const u8 {
    var out = std.ArrayList(u8).init(alloc);
    for (s) |c| switch (c) {
        '\'' => try out.appendSlice("''"),
        '\\' => try out.appendSlice(if (mysql) "\\\\" else "\\"),
        0 => try out.appendSlice(if (mysql) "\\0" else "\x00"),
        '\n' => try out.appendSlice(if (mysql) "\\n" else "\n"),
        '\r' => try out.appendSlice(if (mysql) "\\r" else "\r"),
        0x1a => try out.appendSlice(if (mysql) "\\Z" else "\x1a"), // SUB — ends MySQL input
        else => try out.append(c),
    };
    return out.toOwnedSlice();
}
/// JSON.stringify-style compact re-emission (no whitespace; '/' and non-ASCII stay raw).
pub fn compact(alloc: std.mem.Allocator, v: Value) ![]const u8 {
    var out = std.ArrayList(u8).init(alloc);
    try writeCompact(&out, v);
    return out.toOwnedSlice();
}
fn writeCompact(out: *std.ArrayList(u8), v: Value) anyerror!void {
    switch (v) {
        .null => try out.appendSlice("null"),
        .bool => |b| try out.appendSlice(if (b) "true" else "false"),
        .integer => |n| try out.writer().print("{d}", .{n}),
        .float => |f| try out.writer().print("{d}", .{f}),
        .number_string => |raw| try out.appendSlice(raw), // out-of-range token verbatim
        .string => |s| {
            try out.append('"');
            for (s) |c| switch (c) {
                '"' => try out.appendSlice("\\\""),
                '\\' => try out.appendSlice("\\\\"),
                '\n' => try out.appendSlice("\\n"),
                '\r' => try out.appendSlice("\\r"),
                '\t' => try out.appendSlice("\\t"),
                else => try out.append(c),
            };
            try out.append('"');
        },
        .array => |a| {
            try out.append('[');
            for (a.items, 0..) |x, k| { if (k > 0) try out.append(','); try writeCompact(out, x); }
            try out.append(']');
        },
        .object => |o| {
            try out.append('{');
            var it = o.iterator();
            var first = true;
            while (it.next()) |e| {
                if (!first) try out.append(',');
                first = false;
                try writeCompact(out, .{ .string = e.key_ptr.* });
                try out.append(':');
                try writeCompact(out, e.value_ptr.*);
            }
            try out.append('}');
        },
    }
}
/// Render a decoded JSON value as a SQL literal.
pub fn sqlLiteral(alloc: std.mem.Allocator, v: Value, mysql: bool) ![]const u8 {
    return switch (v) {
        .null => "NULL",
        .bool => |b| if (mysql) (if (b) "1" else "0") else (if (b) "TRUE" else "FALSE"),
        .integer => |n| try std.fmt.allocPrint(alloc, "{d}", .{n}),
        .float => |f| try std.fmt.allocPrint(alloc, "{d}", .{f}),
        .number_string => |raw| raw,
        .string => |s| try std.fmt.allocPrint(alloc, "'{s}'", .{try escapeSqlString(alloc, s, mysql)}),
        else => |val| try std.fmt.allocPrint(alloc, "'{s}'", .{try escapeSqlString(alloc, try compact(alloc, val), mysql)}), // OBJ/ARR → JSON text
    };
}
/// Sanitize to [A-Za-z0-9_] (default "tbl"), then quote per dialect.
fn ident(alloc: std.mem.Allocator, name: []const u8, mysql: bool, quote: bool) ![]const u8 {
    var buf = std.ArrayList(u8).init(alloc);
    for (name) |c| try buf.append(if ((c >= 'a' and c <= 'z') or (c >= 'A' and c <= 'Z') or (c >= '0' and c <= '9') or c == '_') c else '_');
    const base: []const u8 = if (buf.items.len == 0) "tbl" else buf.items;
    if (!quote) return alloc.dupe(u8, base);
    if (mysql) return std.fmt.allocPrint(alloc, "`{s}`", .{base});
    return std.fmt.allocPrint(alloc, "\"{s}\"", .{base});
}
/// Convert a JSON document into a single multi-row INSERT. A bare object is
/// one row; an array is many. Heterogeneous rows unify via first-seen columns.
pub fn jsonToInsert(alloc: std.mem.Allocator, json: []const u8, table: []const u8, mysql: bool, quote: bool) !Res {
    const parsed = std.json.parseFromSliceLeaky(Value, alloc, json, .{}) catch
        return Res{ .ok = false, .sql = "", .rows = 0, .err = "Invalid JSON" };
    var one = [1]Value{parsed};
    const rows: []const Value = if (parsed == .array) parsed.array.items else one[0..];
    if (rows.len == 0) return Res{ .ok = false, .sql = "", .rows = 0, .err = "No rows to insert." };
    for (rows) |r| if (r != .object) return Res{ .ok = false, .sql = "", .rows = 0, .err = "Rows must be objects." };
    var cols = std.ArrayList([]const u8).init(alloc); // first-seen union of every row's keys
    for (rows) |r| for (r.object.keys()) |k| {
        var seen = false;
        for (cols.items) |c| { if (std.mem.eql(u8, c, k)) seen = true; }
        if (!seen) try cols.append(k);
    };
    var sql = std.ArrayList(u8).init(alloc);
    try sql.writer().print("INSERT INTO {s} (", .{try ident(alloc, table, mysql, quote)});
    for (cols.items, 0..) |c, k| { if (k > 0) try sql.appendSlice(", "); try sql.writer().print("{s}", .{try ident(alloc, c, mysql, quote)}); }
    try sql.appendSlice(") VALUES\n");
    for (rows, 0..) |r, ri| {
        try sql.appendSlice("  (");
        for (cols.items, 0..) |c, ci| {
            if (ci > 0) try sql.appendSlice(", ");
            const cell = r.object.get(c); // absent -> NULL
            try sql.appendSlice(if (cell) |v| try sqlLiteral(alloc, v, mysql) else "NULL");
        }
        try sql.appendSlice(if (ri + 1 == rows.len) ");" else "),\n");
    }
    return Res{ .ok = true, .sql = try sql.toOwnedSlice(), .rows = rows.len };
}
pub fn main() !void {
    const r = try jsonToInsert(std.heap.page_allocator, "[{\"id\":1,\"name\":\"O'Hara\"},{\"id\":2,\"age\":30.5,\"tags\":[\"a\",\"b\"]}]", "users", false, true);
    std.debug.print("{s}\n", .{r.sql}); // INSERT INTO "users" ("id", "name", "age", "tags") VALUES ...
}