CSV to SQL Importer — C source

/* csv-to-sql — pure CSV → SQL import generator. C port (canonical TS:
 * src/lib/csv-to-sql.ts; Go twin: cli/csv-to-sql). RFC 4180 parse, header
 * sanitizing into SQL identifiers, then batched INSERTs, a Postgres COPY
 * block, or a MySQL LOAD DATA statement. Numeric-looking text emits bare and
 * verbatim; empty fields become NULL. Caller frees result.sql. */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/* ---- small growable string ---- */

typedef struct { char *buf; size_t len, cap; } Str;

static void str_reserve(Str *s, size_t need) {
    if (need >= s->cap) {
        size_t cap = s->cap ? s->cap : 64;
        while (need >= cap) cap *= 2;
        s->buf = realloc(s->buf, cap);
        s->cap = cap;
    }
}

static void str_push(Str *s, char c) {
    str_reserve(s, s->len + 1);
    s->buf[s->len++] = c;
    s->buf[s->len] = '\0';
}

static void str_app(Str *s, const char *t) {
    size_t tl = strlen(t);
    str_reserve(s, s->len + tl);
    memcpy(s->buf + s->len, t, tl + 1);
    s->len += tl;
}

static void str_clear(Str *s) {
    if (s->buf) s->buf[s->len = 0] = '\0';
}

/* ---- RFC 4180 parser (mirrors the TS loop) ---- */

typedef struct { char **fields; size_t n; } Row;
typedef struct { Row *rows; size_t n; } Table;

static Row row_push_field(Row r, const char *f) {
    r.fields = realloc(r.fields, (r.n + 2) * sizeof(char *));
    r.fields[r.n++] = strdup(f);
    r.fields[r.n] = NULL;
    return r;
}

static Table table_push_row(Table t, Row r) {
    t.rows = realloc(t.rows, (t.n + 1) * sizeof(Row));
    t.rows[t.n++] = r;
    return t;
}

static Table csv_to_rows(const char *csv) {
    Table t = {0};
    Str field = {0};
    Row row = {0};
    int in_q = 0;
    for (size_t i = 0, n = strlen(csv); i < n; i++) {
        char ch = csv[i];
        if (in_q) {
            if (ch == '"') {
                if (i + 1 < n && csv[i + 1] == '"') { str_push(&field, '"'); i++; }
                else in_q = 0;
            } else str_push(&field, ch);
        } else if (ch == '"') {
            in_q = 1;
        } else if (ch == ',') {
            row = row_push_field(row, field.buf ? field.buf : "");
            str_clear(&field);
        } else if (ch == '\n') {
            row = row_push_field(row, field.buf ? field.buf : "");
            t = table_push_row(t, row);
            row = (Row){0};
            str_clear(&field);
        } else if (ch != '\r') {
            str_push(&field, ch);
        }
    }
    if (field.len > 0 || row.n > 0) {
        row = row_push_field(row, field.buf ? field.buf : "");
        t = table_push_row(t, row);
    }
    free(field.buf);
    return t;
}

static void free_table(Table t) {
    for (size_t r = 0; r < t.n; r++) {
        for (size_t f = 0; f < t.rows[r].n; f++) free(t.rows[r].fields[f]);
        free(t.rows[r].fields);
    }
    free(t.rows);
}

/* ---- SQL helpers ---- */

static int is_ident_char(char c) {
    return (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') ||
           (c >= '0' && c <= '9') || c == '_';
}

/* Verbatim numeric literal: -?(digits[.digits] | .digits)(e[+-]?digits)?
 * "5." is rejected (digits required after the dot) — matches the TS regex. */
static int is_numeric(const char *s) {
    size_t i = 0, n = strlen(s);
    if (i < n && s[i] == '-') i++;
    size_t int_start = i;
    while (i < n && s[i] >= '0' && s[i] <= '9') i++;
    int had_int = i > int_start;
    int had_frac = 0, saw_dot = 0;
    if (i < n && s[i] == '.') {
        saw_dot = 1; i++;
        size_t fs = i;
        while (i < n && s[i] >= '0' && s[i] <= '9') i++;
        had_frac = i > fs;
    }
    if (saw_dot && !had_frac) return 0;
    if (!had_int && !had_frac) return 0;
    if (i < n && (s[i] == 'e' || s[i] == 'E')) {
        i++;
        if (i < n && (s[i] == '+' || s[i] == '-')) i++;
        size_t es = i;
        while (i < n && s[i] >= '0' && s[i] <= '9') i++;
        if (i == es) return 0;
    }
    return i == n;
}

static void escape_sql_string(Str *out, const char *s, int mysql) {
    for (const char *p = s; *p; p++) {
        if (*p == '\'') str_app(out, "''");
        else if (mysql && *p == '\\') str_app(out, "\\\\");
        else if (mysql && (unsigned char)*p == 0x00) str_app(out, "\\0");
        else if (mysql && *p == '\n') str_app(out, "\\n");
        else if (mysql && *p == '\r') str_app(out, "\\r");
        else if (mysql && (unsigned char)*p == 0x1a) str_app(out, "\\Z");
        else str_push(out, *p);
    }
}

static void field_literal(Str *out, const char *v, int mysql, int infer_types) {
    if (infer_types && v[0] == '\0') { str_app(out, "NULL"); return; }
    if (infer_types && is_numeric(v)) { str_app(out, v); return; } /* verbatim */
    str_push(out, '\'');
    escape_sql_string(out, v, mysql);
    str_push(out, '\'');
}

/* Sanitize into a malloc'd identifier. */
static char *sanitize_ident(const char *name) {
    char *out = malloc(strlen(name) + 1);
    size_t j = 0;
    for (const char *p = name; *p; p++) out[j++] = is_ident_char(*p) ? *p : '_';
    out[j] = '\0';
    return out;
}

static void quote_ident(Str *out, const char *name, int mysql, int quote_ids) {
    if (!quote_ids) { str_app(out, name); return; }
    str_app(out, mysql ? "`" : "\"");
    str_app(out, name);
    str_app(out, mysql ? "`" : "\"");
}

static void csv_escape(Str *out, const char *f) {
    if (!strpbrk(f, ",\n\r\"")) { str_app(out, f); return; }
    str_push(out, '"');
    for (const char *p = f; *p; p++) {
        if (*p == '"') str_push(out, '"');
        str_push(out, *p);
    }
    str_push(out, '"');
}

/* ---- main entry ---- */

typedef struct { int ok; char *sql; long rows; const char *error; } CsvToSqlResult;

static char *cell(Table *t, size_t r, size_t c) {
    return c < t->rows[r].n ? t->rows[r].fields[c] : "";
}

CsvToSqlResult csv_to_sql(const char *csv, const char *table, const char *format,
                          const char *dialect, long batch_size, int infer_types,
                          int quote_ids, const char *file_name) {
    CsvToSqlResult res = {0};

    /* trim surrounding whitespace */
    const char *p = csv;
    while (*p == ' ' || *p == '\t' || *p == '\n' || *p == '\r') p++;
    const char *e = p + strlen(p);
    while (e > p && (e[-1] == ' ' || e[-1] == '\t' || e[-1] == '\n' || e[-1] == '\r')) e--;
    size_t tlen = (size_t)(e - p);
    int empty = tlen == 0;

    char *text = malloc(tlen + 1);
    memcpy(text, p, tlen);
    text[tlen] = '\0';

    Table t = csv_to_rows(text);
    if (empty || t.n < 2) {
        free_table(t); free(text);
        res.error = "No rows to import.";
        return res;
    }
    size_t n_cols = t.rows[0].n;
    long n_data = (long)t.n - 1;

    int mysql = strcmp(dialect, "mysql") == 0;
    int fmt_copy = strcmp(format, "copy") == 0;
    int fmt_load = strcmp(format, "load-data") == 0;
    int ident_mysql = fmt_load ? 1 : fmt_copy ? 0 : mysql;

    /* sanitize + de-duplicate headers, then quote the column list */
    char **cols = malloc(n_cols * sizeof(char *));
    for (size_t c = 0; c < n_cols; c++) {
        char *h = strdup(t.rows[0].fields[c]);
        size_t hn = strlen(h);
        while (hn && (h[0] == ' ' || h[0] == '\t')) memmove(h, h + 1, hn--);
        while (hn && (h[hn - 1] == ' ' || h[hn - 1] == '\t')) h[--hn] = '\0';
        char *id = sanitize_ident(h);
        free(h);
        if (!id[0]) { free(id); id = malloc(16); snprintf(id, 16, "col%zu", c + 1); }
        int dup = 0;
        for (size_t k = 0; k < c; k++) if (strcmp(cols[k], id) == 0) dup++;
        if (dup > 0) {
            char *suff = malloc(strlen(id) + 8);
            snprintf(suff, strlen(id) + 8, "%s_%d", id, dup + 1);
            free(id);
            id = suff;
        }
        cols[c] = id;
    }

    char *tbl_raw = sanitize_ident(table);
    Str tbl = {0};
    quote_ident(&tbl, tbl_raw[0] ? tbl_raw : "tbl", ident_mysql, quote_ids);
    Str col_list = {0};
    for (size_t c = 0; c < n_cols; c++) {
        if (c) str_app(&col_list, ", ");
        quote_ident(&col_list, cols[c], ident_mysql, quote_ids);
    }

    Str sql = {0};
    if (fmt_copy || fmt_load) {
        /* payload: sanitized header + data rows, re-emitted as clean CSV */
        for (size_t c = 0; c < n_cols; c++) {
            if (c) str_push(&sql, ',');
            csv_escape(&sql, cols[c]);
        }
        for (size_t r = 1; r < t.n; r++) {
            str_push(&sql, '\n');
            for (size_t c = 0; c < n_cols; c++) {
                if (c) str_push(&sql, ',');
                csv_escape(&sql, cell(&t, r, c));
            }
        }
        Str out = {0};
        if (fmt_copy) {
            str_app(&out, "COPY ");
            str_app(&out, tbl.buf);
            str_app(&out, " (");
            str_app(&out, col_list.buf);
            str_app(&out, ") FROM STDIN WITH (FORMAT csv, HEADER true);\n");
            str_app(&out, sql.buf);
            str_app(&out, "\n\\.");
        } else {
            char *clean = malloc(strlen(file_name) + 1);
            size_t cn = 0;
            for (const char *fp = file_name; *fp; fp++)
                if (is_ident_char(*fp) || *fp == '.' || *fp == '-' || *fp == '/')
                    clean[cn++] = *fp;
            clean[cn] = '\0';
            if (!cn) { strcpy(clean, "import.csv"); }
            str_app(&out, "LOAD DATA LOCAL INFILE '");
            str_app(&out, clean);
            str_app(&out, "'\nINTO TABLE ");
            str_app(&out, tbl.buf);
            str_app(&out, "\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\nLINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n");
            str_app(&out, sql.buf);
            free(clean);
        }
        sql = out;
    } else {
        long size = batch_size > 0 ? batch_size : 100;
        for (long start = 1; start < (long)t.n; start += size) {
            if (start > 1) str_push(&sql, '\n');
            str_app(&sql, "INSERT INTO ");
            str_app(&sql, tbl.buf);
            str_app(&sql, " (");
            str_app(&sql, col_list.buf);
            str_app(&sql, ") VALUES\n");
            long end = start + size;
            if (end > (long)t.n) end = (long)t.n;
            for (long r = start; r < end; r++) {
                if (r > start) str_app(&sql, ",\n");
                str_app(&sql, "  (");
                for (size_t c = 0; c < n_cols; c++) {
                    if (c) str_app(&sql, ", ");
                    field_literal(&sql, cell(&t, (size_t)r, c), mysql, infer_types);
                }
                str_push(&sql, ')');
            }
            str_push(&sql, ';');
        }
    }

    for (size_t c = 0; c < n_cols; c++) free(cols[c]);
    free(cols); free(tbl_raw); free(tbl.buf); free(col_list.buf);
    free_table(t); free(text);
    res.ok = 1;
    res.sql = sql.buf;
    res.rows = n_data;
    return res;
}

/* Example:
 *   CsvToSqlResult r = csv_to_sql("id,name\n1,Ada\n2,", "users",
 *                                 "insert", "standard", 100, 1, 1, "import.csv");
 *   printf("%s\n", r.sql);
 *   // INSERT INTO "users" ("id", "name") VALUES
 *   //   (1, 'Ada'),
 *   //   (2, NULL);
 *   free(r.sql); */