CSV to SQL Importer — Zig source

//! csv-to-sql — pure CSV → SQL import generator. Zig 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 owns result.sql (free with the
//! allocator).
const std = @import("std");

fn strContainsAny(s: []const u8, chars: []const u8) bool {
    for (s) |c| {
        for (chars) |x| if (c == x) return true;
    }
    return false;
}

/// RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
/// trailing field without a newline still completes its row. Caller frees.
pub fn csvToRows(a: std.mem.Allocator, csv: []const u8) ![][]const []const u8 {
    var rows = std.ArrayList([]const []const u8).init(a);
    var field = std.ArrayList(u8).init(a);
    var row = std.ArrayList([]const u8).init(a);
    var in_q = false;
    var i: usize = 0;
    while (i < csv.len) : (i += 1) {
        const ch = csv[i];
        if (in_q) {
            if (ch == '"') {
                if (i + 1 < csv.len and csv[i + 1] == '"') {
                    try field.append('"');
                    i += 1;
                } else {
                    in_q = false;
                }
            } else {
                try field.append(ch);
            }
        } else if (ch == '"') {
            in_q = true;
        } else if (ch == ',') {
            try row.append(try field.toOwnedSlice());
        } else if (ch == '\n') {
            try row.append(try field.toOwnedSlice());
            try rows.append(try row.toOwnedSlice());
            row = std.ArrayList([]const u8).init(a);
        } else if (ch != '\r') {
            try field.append(ch);
        }
    }
    if (field.items.len > 0 or row.items.len > 0) {
        try row.append(try field.toOwnedSlice());
        try rows.append(try row.toOwnedSlice());
    }
    return rows.toOwnedSlice();
}

fn isIdentChar(c: u8) bool {
    return (c >= 'A' and c <= 'Z') or (c >= 'a' and c <= 'z') or
        (c >= '0' and c <= '9') or c == '_';
}

/// Verbatim numeric literal: -?(digits[.digits] | .digits)(e[+-]?digits)?
/// "5." is rejected (digits required after the dot) — matches the TS regex.
fn isNumeric(s: []const u8) bool {
    var i: usize = 0;
    const n = s.len;
    if (i < n and s[i] == '-') i += 1;
    const int_start = i;
    while (i < n and s[i] >= '0' and s[i] <= '9') i += 1;
    const had_int = i > int_start;
    var had_frac = false;
    var saw_dot = false;
    if (i < n and s[i] == '.') {
        saw_dot = true;
        i += 1;
        const fs = i;
        while (i < n and s[i] >= '0' and s[i] <= '9') i += 1;
        had_frac = i > fs;
    }
    if (saw_dot and !had_frac) return false;
    if (!had_int and !had_frac) return false;
    if (i < n and (s[i] == 'e' or s[i] == 'E')) {
        i += 1;
        if (i < n and (s[i] == '+' or s[i] == '-')) i += 1;
        const es = i;
        while (i < n and s[i] >= '0' and s[i] <= '9') i += 1;
        if (i == es) return false;
    }
    return i == n;
}

fn escapeSqlString(a: std.mem.Allocator, s: []const u8, mysql: bool) ![]const u8 {
    var out = std.ArrayList(u8).init(a);
    for (s) |c| {
        switch (c) {
            '\'' => try out.appendSlice("''"),
            '\\' => if (mysql) try out.appendSlice("\\\\") else try out.append(c),
            0x00 => if (mysql) try out.appendSlice("\\0") else try out.append(c),
            '\n' => if (mysql) try out.appendSlice("\\n") else try out.append(c),
            '\r' => if (mysql) try out.appendSlice("\\r") else try out.append(c),
            0x1a => if (mysql) try out.appendSlice("\\Z") else try out.append(c),
            else => try out.append(c),
        }
    }
    return out.toOwnedSlice();
}

fn fieldLiteral(a: std.mem.Allocator, v: []const u8, mysql: bool, infer_types: bool) ![]const u8 {
    if (infer_types) {
        if (v.len == 0) return "NULL";
        if (isNumeric(v)) return v; // verbatim — no float round-trip
    }
    const esc = try escapeSqlString(a, v, mysql);
    defer a.free(esc);
    return std.fmt.allocPrint(a, "'{s}'", .{esc});
}

fn sanitizeIdent(a: std.mem.Allocator, name: []const u8) ![]const u8 {
    const out = try a.alloc(u8, name.len);
    for (name, 0..) |c, i| out[i] = if (isIdentChar(c)) c else '_';
    return out;
}

const SanitizedHeaders = struct {
    cols: [][]const u8,
    /// Allocations for generated names (dupes, colN) the caller must free.
    owned: [][]const u8,
};

fn sanitizeHeaders(a: std.mem.Allocator, headers: []const []const u8) !SanitizedHeaders {
    var owned = std.ArrayList([]const u8).init(a);
    const cols = try a.alloc([]const u8, headers.len);
    for (headers, 0..) |h, i| {
        const trimmed = std.mem.trim(u8, h, " \t");
        const raw = try sanitizeIdent(a, trimmed);
        defer a.free(raw);
        var id: []const u8 = raw;
        if (id.len == 0) {
            const gen = try std.fmt.allocPrint(a, "col{d}", .{i + 1});
            try owned.append(gen);
            id = gen;
        }
        // de-duplicate: count earlier equals
        var dupes: usize = 0;
        for (cols[0..i]) |prev| {
            if (std.mem.eql(u8, prev, id)) dupes += 1;
        }
        if (dupes > 0) {
            const suff = try std.fmt.allocPrint(a, "{s}_{d}", .{ id, dupes + 1 });
            try owned.append(suff);
            id = suff;
        }
        cols[i] = try a.dupe(u8, id);
    }
    return .{ .cols = cols, .owned = try owned.toOwnedSlice() };
}

fn csvEscape(a: std.mem.Allocator, f: []const u8) ![]const u8 {
    if (!strContainsAny(f, ",\"\n\r")) return a.dupe(u8, f);
    var out = std.ArrayList(u8).init(a);
    try out.append('"');
    for (f) |c| {
        if (c == '"') try out.append('"');
        try out.append(c);
    }
    try out.append('"');
    return out.toOwnedSlice();
}

pub const Format = enum { insert, copy, load_data };
pub const Dialect = enum { standard, mysql, postgres };

pub const Options = struct {
    table: []const u8 = "",
    format: Format = .insert,
    dialect: Dialect = .standard,
    batch_size: usize = 100,
    infer_types: bool = true,
    quote_identifiers: bool = true,
    file_name: []const u8 = "import.csv",
};

pub const Result = struct {
    ok: bool,
    sql: []const u8,
    rows: usize,
    /// Named err — "error" is a Zig keyword.
    err: ?[]const u8,
};

fn quoteIdent(a: std.mem.Allocator, name: []const u8, mysql: bool, quote_ids: bool) ![]const u8 {
    if (!quote_ids) return a.dupe(u8, name);
    if (mysql) return std.fmt.allocPrint(a, "`{s}`", .{name});
    return std.fmt.allocPrint(a, "\"{s}\"", .{name});
}

pub fn csvToSql(a: std.mem.Allocator, csv: []const u8, opts: Options) !Result {
    const text = std.mem.trim(u8, csv, " \t\n\r");
    const rows = try csvToRows(a, text);
    if (text.len == 0 or rows.len < 2) {
        return .{ .ok = false, .sql = "", .rows = 0, .err = "No rows to import." };
    }

    const sh = try sanitizeHeaders(a, rows[0]);
    const cols = sh.cols;
    const data = rows[1..];
    const mysql = opts.dialect == .mysql;
    const ident_mysql = switch (opts.format) {
        .load_data => true,
        .copy => false,
        .insert => mysql,
    };

    const tbl_raw = try sanitizeIdent(a, opts.table);
    const tbl_name: []const u8 = if (tbl_raw.len == 0) "tbl" else tbl_raw;
    const tbl = try quoteIdent(a, tbl_name, ident_mysql, opts.quote_identifiers);

    var col_list = std.ArrayList(u8).init(a);
    for (cols, 0..) |c, i| {
        if (i > 0) try col_list.appendSlice(", ");
        const q = try quoteIdent(a, c, ident_mysql, opts.quote_identifiers);
        try col_list.appendSlice(q);
    }

    if (opts.format == .copy or opts.format == .load_data) {
        var payload = std.ArrayList(u8).init(a);
        for (cols, 0..) |c, i| {
            if (i > 0) try payload.append(',');
            const e = try csvEscape(a, c);
            try payload.appendSlice(e);
        }
        for (data) |r| {
            try payload.append('\n');
            for (cols, 0..) |_, ci| {
                if (ci > 0) try payload.append(',');
                const v = if (ci < r.len) r[ci] else "";
                const e = try csvEscape(a, v);
                try payload.appendSlice(e);
            }
        }
        if (opts.format == .copy) {
            const sql = try std.fmt.allocPrint(a, "COPY {s} ({s}) FROM STDIN WITH (FORMAT csv, HEADER true);\n{s}\n\\.", .{ tbl, col_list.items, payload.items });
            return .{ .ok = true, .sql = sql, .rows = data.len, .err = null };
        }
        var clean = std.ArrayList(u8).init(a);
        for (opts.file_name) |c| {
            if (isIdentChar(c) or c == '.' or c == '-' or c == '/') try clean.append(c);
        }
        const file: []const u8 = if (clean.items.len == 0) "import.csv" else clean.items;
        const sql = try std.fmt.allocPrint(a, "LOAD DATA LOCAL INFILE '{s}'\nINTO TABLE {s}\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\nLINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n{s}", .{ file, tbl, payload.items });
        return .{ .ok = true, .sql = sql, .rows = data.len, .err = null };
    }

    const size = @max(1, opts.batch_size);
    var stmts = std.ArrayList(u8).init(a);
    var start: usize = 0;
    while (start < data.len) : (start += size) {
        const end = @min(data.len, start + size);
        if (start > 0) try stmts.append('\n');
        try stmts.appendSlice("INSERT INTO ");
        try stmts.appendSlice(tbl);
        try stmts.appendSlice(" (");
        try stmts.appendSlice(col_list.items);
        try stmts.appendSlice(") VALUES\n");
        for (start..end, 0..) |ri, k| {
            if (k > 0) try stmts.appendSlice(",\n");
            try stmts.appendSlice("  (");
            for (cols, 0..) |_, ci| {
                if (ci > 0) try stmts.appendSlice(", ");
                const v = if (ci < data[ri].len) data[ri][ci] else "";
                const lit = try fieldLiteral(a, v, mysql, opts.infer_types);
                try stmts.appendSlice(lit);
            }
            try stmts.append(')');
        }
        try stmts.append(';');
    }
    return .{ .ok = true, .sql = stmts.items, .rows = data.len, .err = null };
}

// Example:
//   var gpa = std.heap.GeneralPurposeAllocator(.{}){};
//   const r = try csvToSql(gpa.allocator(), "id,name\n1,Ada\n2,", .{ .table = "users" });
//   if (r.ok) std.debug.print("{s}\n", .{r.sql});
//   // INSERT INTO "users" ("id", "name") VALUES
//   //   (1, 'Ada'),
//   //   (2, NULL);