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.
#include <algorithm>
#include <iostream>
#include <map>
#include <string>
#include <vector>

// RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
// trailing field without a newline still completes its row.
std::vector<std::vector<std::string>> csvToRows(const std::string &csv) {
    std::vector<std::vector<std::string>> rows;
    std::string field;
    std::vector<std::string> row;
    bool inQ = false;
    for (size_t i = 0; i < csv.size(); i++) {
        char ch = csv[i];
        if (inQ) {
            if (ch == '"') {
                if (i + 1 < csv.size() && csv[i + 1] == '"') { field += '"'; i++; }
                else inQ = false;
            } else field += ch;
        } else if (ch == '"') inQ = true;
        else if (ch == ',') { row.push_back(field); field.clear(); }
        else if (ch == '\n') { row.push_back(field); rows.push_back(row); row.clear(); field.clear(); }
        else if (ch != '\r') field += ch;
    }
    if (!field.empty() || !row.empty()) { row.push_back(field); rows.push_back(row); }
    return rows;
}

bool isIdentChar(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.
bool isNumeric(const std::string &s) {
    size_t i = 0, n = s.size();
    if (i < n && s[i] == '-') i++;
    size_t intStart = i;
    while (i < n && isdigit((unsigned char)s[i])) i++;
    bool hadInt = i > intStart;
    bool hadFrac = false, sawDot = false;
    if (i < n && s[i] == '.') {
        sawDot = true; i++;
        size_t fs = i;
        while (i < n && isdigit((unsigned char)s[i])) i++;
        hadFrac = i > fs;
    }
    if (sawDot && !hadFrac) return false;
    if (!hadInt && !hadFrac) return false;
    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 && isdigit((unsigned char)s[i])) i++;
        if (i == es) return false;
    }
    return i == n;
}

std::string escapeSqlString(const std::string &s, bool mysql) {
    std::string out;
    for (char c : s) {
        if (c == '\'') out += "''";
        else if (mysql && c == '\\') out += "\\\\";
        else if (mysql && c == '\0') out += "\\0";
        else if (mysql && c == '\n') out += "\\n";
        else if (mysql && c == '\r') out += "\\r";
        else if (mysql && c == '\x1a') out += "\\Z";
        else out += c;
    }
    return out;
}

std::string fieldLiteral(const std::string &v, bool mysql, bool inferTypes) {
    if (inferTypes) {
        if (v.empty()) return "NULL";
        if (isNumeric(v)) return v; // verbatim — no float round-trip
    }
    return "'" + escapeSqlString(v, mysql) + "'";
}

std::string sanitizeIdent(const std::string &name) {
    std::string out;
    for (char c : name) out += isIdentChar(c) ? c : '_';
    return out;
}

std::vector<std::string> sanitizeHeaders(const std::vector<std::string> &headers) {
    std::map<std::string, int> seen;
    std::vector<std::string> out;
    for (size_t i = 0; i < headers.size(); i++) {
        std::string h = headers[i];
        // trim
        h.erase(0, h.find_first_not_of(" \t"));
        h.erase(h.find_last_not_of(" \t") + 1);
        std::string id = sanitizeIdent(h);
        if (id.empty()) id = "col" + std::to_string(i + 1);
        seen[id]++;
        if (seen[id] > 1) id += "_" + std::to_string(seen[id]);
        out.push_back(id);
    }
    return out;
}

std::string csvEscape(const std::string &f) {
    if (f.find_first_of(",\n\r\"") == std::string::npos) return f;
    std::string out = "\"";
    for (char c : f) {
        if (c == '"') out += '"';
        out += c;
    }
    return out + "\"";
}

struct Options {
    std::string table;
    std::string format = "insert";        // insert | copy | load-data
    std::string dialect = "standard";     // standard | mysql | postgres
    long batchSize = 100;
    bool inferTypes = true;
    bool quoteIdentifiers = true;
    std::string fileName = "import.csv";
};

struct Result {
    bool ok = false;
    std::string sql;
    long rows = 0;
    std::string error;
};

std::string quoteIdent(const std::string &name, bool mysql, bool quoteIds) {
    if (!quoteIds) return name;
    return mysql ? "`" + name + "`" : "\"" + name + "\"";
}

Result csvToSql(const std::string &csv, const Options &opts) {
    Result res;
    auto fail = [&](const char *msg) { res.error = msg; return res; };

    // trim
    size_t b = csv.find_first_not_of(" \t\n\r");
    size_t e = csv.find_last_not_of(" \t\n\r");
    std::string text = b == std::string::npos ? "" : csv.substr(b, e - b + 1);

    auto rows = csvToRows(text);
    if (text.empty() || rows.size() < 2) return fail("No rows to import.");

    auto cols = sanitizeHeaders(rows[0]);
    auto data = std::vector<std::vector<std::string>>(rows.begin() + 1, rows.end());
    bool mysql = opts.dialect == "mysql";
    bool fmtCopy = opts.format == "copy";
    bool fmtLoad = opts.format == "load-data";
    bool identMysql = fmtLoad ? true : fmtCopy ? false : mysql;

    std::string tblRaw = sanitizeIdent(opts.table);
    std::string tbl = quoteIdent(tblRaw.empty() ? "tbl" : tblRaw, identMysql, opts.quoteIdentifiers);
    std::string colList;
    for (size_t c = 0; c < cols.size(); c++) {
        if (c) colList += ", ";
        colList += quoteIdent(cols[c], identMysql, opts.quoteIdentifiers);
    }
    auto cellAt = [&](const std::vector<std::string> &r, size_t ci) {
        return ci < r.size() ? r[ci] : "";
    };

    if (fmtCopy || fmtLoad) {
        std::vector<std::string> payload;
        std::string header;
        for (size_t c = 0; c < cols.size(); c++) {
            if (c) header += ',';
            header += csvEscape(cols[c]);
        }
        payload.push_back(header);
        for (const auto &r : data) {
            std::string line;
            for (size_t c = 0; c < cols.size(); c++) {
                if (c) line += ',';
                line += csvEscape(cellAt(r, c));
            }
            payload.push_back(line);
        }
        std::string body;
        for (size_t i = 0; i < payload.size(); i++) {
            if (i) body += '\n';
            body += payload[i];
        }
        if (fmtCopy) {
            res.sql = "COPY " + tbl + " (" + colList +
                      ") FROM STDIN WITH (FORMAT csv, HEADER true);\n" + body + "\n\\.";
        } else {
            std::string clean;
            for (char c : opts.fileName)
                if (isIdentChar(c) || c == '.' || c == '-' || c == '/') clean += c;
            if (clean.empty()) clean = "import.csv";
            res.sql = "LOAD DATA LOCAL INFILE '" + clean + "'\nINTO TABLE " + tbl +
                      "\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"
                      "\nLINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n" + body;
        }
        res.ok = true;
        res.rows = (long)data.size();
        return res;
    }

    long size = std::max(1L, opts.batchSize);
    std::vector<std::string> stmts;
    for (size_t start = 0; start < data.size(); start += size) {
        std::string values;
        size_t end = std::min(data.size(), start + size);
        for (size_t r = start; r < end; r++) {
            if (r > start) values += ",\n";
            values += "  (";
            for (size_t c = 0; c < cols.size(); c++) {
                if (c) values += ", ";
                values += fieldLiteral(cellAt(data[r], c), mysql, opts.inferTypes);
            }
            values += ")";
        }
        stmts.push_back("INSERT INTO " + tbl + " (" + colList + ") VALUES\n" + values + ";");
    }
    res.ok = true;
    for (size_t i = 0; i < stmts.size(); i++) {
        if (i) res.sql += '\n';
        res.sql += stmts[i];
    }
    res.rows = (long)data.size();
    return res;
}

// Example:
//   Options o; o.table = "users";
//   Result r = csvToSql("id,name\n1,Ada\n2,", o);
//   std::cout << r.sql << "\n";
//   // INSERT INTO "users" ("id", "name") VALUES
//   //   (1, 'Ada'),
//   //   (2, NULL);