JSON to SQL INSERT — C source

/* json-to-sql — pure JSON → SQL INSERT converter. C99 port (canonical TS:
 * src/lib/jsonToSql.ts; Go twin: cli/json-to-sql). No stdlib JSON codec, so —
 * like the Rust port — this is a hand-rolled JSON DOM parser (objects keep
 * first-seen key order via pair arrays; number tokens stay raw text) feeding
 * a multi-row INSERT builder. Errors return through Res — never crashed. */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
typedef struct V V;
typedef struct { char *k; V *v; } Pair;                /* insertion order */
struct V { int tag; char *s; Pair *ps; int np; V **vs; int nv; };
/* tags: 0 null, 1 true, 2 false, 3 number(raw), 4 string, 5 object, 6 array */
typedef struct { int ok; char *sql; int rows; const char *err; } Res;
typedef struct { char *p; size_t n, cap; } Buf;
static const char *src; static int i;                  /* parser cursor */
static void ws(void) { while (src[i]==' '||src[i]=='\t'||src[i]=='\n'||src[i]=='\r') i++; }
static char peek(void) { return src[i]; }
static char take(void) { return src[i++]; }
static V *vnew(int tag) { V *v = calloc(1, sizeof *v); v->tag = tag; return v; }
static char *slc(int from) { char *s = malloc(i - from + 1); memcpy(s, src + from, i - from); s[i - from] = 0; return s; }
static char *jstr(void) {                              /* string token -> heap C string */
    char *out = malloc(strlen(src + i) + 1), *o = out;
    if (take() != '"') { free(out); return NULL; }
    while (1) { char c = take(), e; int cp = 0, k;
        if (c == '"') { *o = 0; return out; }
        if (!c) { free(out); return NULL; }            /* unterminated */
        if (c == '\\') { e = take();
            if (e=='"'||e=='\\'||e=='/') *o++ = e;
            else if (e=='b') *o++='\b'; else if (e=='f') *o++='\f'; else if (e=='n') *o++='\n';
            else if (e=='r') *o++='\r'; else if (e=='t') *o++='\t';
            else if (e=='u') {                         /* BMP scalar folded to a byte (demo scope) */
                for (k = 0; k < 4; k++) { char h = take(); cp *= 16;
                    if (h>='0'&&h<='9') cp += h-'0'; else if (h>='a'&&h<='f') cp += h-'a'+10;
                    else if (h>='A'&&h<='F') cp += h-'A'+10; else { free(out); return NULL; } }
                *o++ = (char)cp;
            } else { free(out); return NULL; }
        } else *o++ = c;
    }
}
static V *value(void) {
    ws(); char c = peek(); int from; V *v, *item; char *k;
    if (c == '{') {
        i++; v = vnew(5); ws();
        if (peek() == '}') { i++; return v; }
        while (1) { ws();
            if (!(k = jstr())) return NULL;
            ws(); if (take() != ':') return NULL;
            if (!(item = value())) return NULL;
            v->ps = realloc(v->ps, (v->np + 1) * sizeof(Pair));
            v->ps[v->np].k = k; v->ps[v->np].v = item; v->np++;
            ws(); c = take();
            if (c == '}') return v;
            if (c != ',') return NULL;
        }
    }
    if (c == '[') {
        i++; v = vnew(6); ws();
        if (peek() == ']') { i++; return v; }
        while (1) {
            if (!(item = value())) return NULL;
            v->vs = realloc(v->vs, (v->nv + 1) * sizeof(V *));
            v->vs[v->nv++] = item;
            ws(); c = take();
            if (c == ']') return v;
            if (c != ',') return NULL;
        }
    }
    if (c == '"') { v = vnew(4); if (!(v->s = jstr())) return NULL; return v; }
    if (!strncmp(src + i, "null", 4))  { i += 4; return vnew(0); }
    if (!strncmp(src + i, "true", 4))  { i += 4; return vnew(1); }
    if (!strncmp(src + i, "false", 5)) { i += 5; return vnew(2); }
    from = i;
    while (peek() && strchr("-+.eE0123456789", peek())) i++;
    if (i == from) return NULL;
    v = vnew(3); v->s = slc(from); return v;           /* raw token, String(n)-safe */
}
static void bputs(Buf *b, const char *s) {
    size_t l = strlen(s);
    if (b->n + l + 1 > b->cap) { while (b->cap < b->n + l + 1) b->cap = b->cap ? b->cap * 2 : 256; b->p = realloc(b->p, b->cap); }
    memcpy(b->p + b->n, s, l); b->n += l; b->p[b->n] = 0;
}
static void bputc(Buf *b, char c) { char t[2] = {c, 0}; bputs(b, t); }
static void esc(Buf *o, const char *s, int mysql) {
    for (; *s; s++) {
        if (*s == '\'') bputs(o, "''");
        else if (mysql && *s == '\\') bputs(o, "\\\\");
        else if (mysql && *s == '\n') bputs(o, "\\n");
        else if (mysql && *s == '\r') bputs(o, "\\r");
        else if (mysql && *s == '\032') bputs(o, "\\Z"); /* SUB — ends MySQL input */
        else bputc(o, *s); /* (NUL cannot occur in a C string) */
    }
}
static void cstr(Buf *o, const char *s) {              /* quoted compact JSON string */
    bputc(o, '"');
    for (; *s; s++) {
        if (*s == '"' || *s == '\\') { bputc(o, '\\'); bputc(o, *s); }
        else if (*s == '\n') bputs(o, "\\n"); else if (*s == '\r') bputs(o, "\\r");
        else if (*s == '\t') bputs(o, "\\t"); else bputc(o, *s);
    }
    bputc(o, '"');
}
static void compactjson(Buf *o, const V *v) {
    int k;
    switch (v->tag) {
    case 0: bputs(o, "null"); break; case 1: bputs(o, "true"); break; case 2: bputs(o, "false"); break;
    case 3: bputs(o, v->s); break; case 4: cstr(o, v->s); break;
    case 5: bputc(o, '{'); for (k = 0; k < v->np; k++) { if (k) bputc(o, ','); cstr(o, v->ps[k].k); bputc(o, ':'); compactjson(o, v->ps[k].v); } bputc(o, '}'); break;
    default: bputc(o, '['); for (k = 0; k < v->nv; k++) { if (k) bputc(o, ','); compactjson(o, v->vs[k]); } bputc(o, ']'); break;
    }
}
static void lit(Buf *o, const V *v, int mysql) {
    switch (v->tag) {
    case 0: bputs(o, "NULL"); break;
    case 1: bputs(o, mysql ? "1" : "TRUE"); break; case 2: bputs(o, mysql ? "0" : "FALSE"); break;
    case 3: bputs(o, v->s); break;
    case 4: bputc(o, '\''); esc(o, v->s, mysql); bputc(o, '\''); break;
    default: { Buf j = {0}; compactjson(&j, v); bputc(o, '\''); esc(o, j.p, mysql); bputc(o, '\''); break; } /* OBJ/ARR → JSON text */
    }
}
static void ident(Buf *o, const char *n, int mysql, int quote) {
    char cl[256]; int j = 0;                           /* [^A-Za-z0-9_] -> '_' */
    for (; *n; n++) cl[j++] = ((*n>='a'&&*n<='z')||(*n>='A'&&*n<='Z')||(*n>='0'&&*n<='9')||*n=='_') ? *n : '_';
    cl[j] = 0;
    const char *s = j ? cl : "tbl";
    if (!quote) { bputs(o, s); return; }
    bputc(o, mysql ? '`' : '"'); bputs(o, s); bputc(o, mysql ? '`' : '"');
}
/* 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. */
static Res json_to_insert(const char *json, const char *table, int mysql, int quote) {
    Res r = { 0, 0, 0, 0 }; Buf o = { 0 }; V *single, *root; V **rows; int nrows = 1, ri, ci, ncols = 0;
    const char *cols[256];
    src = json; i = 0;
    if (!(root = value())) { r.err = "Invalid JSON"; return r; }
    if (root->tag == 6) { rows = root->vs; nrows = root->nv; }
    else { single = root; rows = &single; }
    if (nrows == 0) { r.err = "No rows to insert."; return r; }
    for (ri = 0; ri < nrows; ri++) if (rows[ri]->tag != 5) { r.err = "Rows must be objects."; return r; }
    for (ri = 0; ri < nrows; ri++)                     /* first-seen union of every row's keys */
        for (ci = 0; ci < rows[ri]->np; ci++) {
            const char *k = rows[ri]->ps[ci].k; int seen = 0, t;
            for (t = 0; t < ncols; t++) if (!strcmp(cols[t], k)) seen = 1;
            if (!seen) cols[ncols++] = k;
        }
    bputs(&o, "INSERT INTO "); ident(&o, table, mysql, quote); bputs(&o, " (");
    for (ci = 0; ci < ncols; ci++) { if (ci) bputs(&o, ", "); ident(&o, cols[ci], mysql, quote); }
    bputs(&o, ") VALUES\n");
    for (ri = 0; ri < nrows; ri++) {
        int cj; bputs(&o, "  (");
        for (cj = 0; cj < ncols; cj++) {
            V *cell = NULL; int f;
            if (cj) bputs(&o, ", ");
            for (f = 0; f < rows[ri]->np; f++) if (!strcmp(rows[ri]->ps[f].k, cols[cj])) cell = rows[ri]->ps[f].v;
            if (cell) lit(&o, cell, mysql); else bputs(&o, "NULL"); /* absent -> NULL */
        }
        bputs(&o, ri == nrows - 1 ? ");" : "),\n");
    }
    r.ok = 1; r.sql = o.p; r.rows = nrows; return r;
}
int main(void) {
    Res r = json_to_insert("[{\"id\":1,\"name\":\"O'Hara\"},{\"id\":2,\"age\":30.5,\"tags\":[\"a\",\"b\"]}]", "users", 0, 1);
    puts(r.sql); /* INSERT INTO "users" ("id", "name", "age", "tags") VALUES ... */
    return 0;
}