CSV to SQL Importer — Java source

// csv-to-sql — pure CSV → SQL import generator. Java 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.
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

// Package-private: display snippets keep the repo filename (csv-to-sql.java),
// which need not match the class name.
final class CsvToSql {

    /** RFC 4180 parser mirroring the TS loop: lenient quotes, CR dropped, a
     *  trailing field without a newline still completes its row. */
    public static List<String[]> csvToRows(String csv) {
        List<String[]> rows = new ArrayList<>();
        StringBuilder field = new StringBuilder();
        List<String> row = new ArrayList<>();
        boolean inQ = false;
        for (int i = 0; i < csv.length(); i++) {
            char ch = csv.charAt(i);
            if (inQ) {
                if (ch == '"') {
                    if (i + 1 < csv.length() && csv.charAt(i + 1) == '"') { field.append('"'); i++; }
                    else inQ = false;
                } else field.append(ch);
            } else if (ch == '"') inQ = true;
            else if (ch == ',') { row.add(field.toString()); field.setLength(0); }
            else if (ch == '\n') { row.add(field.toString()); rows.add(row.toArray(new String[0])); row.clear(); field.setLength(0); }
            else if (ch != '\r') field.append(ch);
        }
        if (field.length() > 0 || !row.isEmpty()) { row.add(field.toString()); rows.add(row.toArray(new String[0])); }
        return rows;
    }

    // Verbatim numeric literal — matched text emitted as-is (no float
    // round-trip, so "007" and "1e3" pass through unchanged).
    private static final Pattern NUMERIC = Pattern.compile("^-?(\\d+(\\.\\d+)?|\\.\\d+)([eE][+-]?\\d+)?$");

    public static String escapeSqlString(String s, String dialect) {
        String out = s.replace("'", "''");
        if ("mysql".equals(dialect)) {
            out = out.replace("\\", "\\\\").replace("\0", "\\0")
                     .replace("\n", "\\n").replace("\r", "\\r")
                     .replace("\u001a", "\\Z");
        }
        return out;
    }

    public static String fieldLiteral(String value, String dialect, boolean inferTypes) {
        if (inferTypes) {
            if (value.isEmpty()) return "NULL";
            if (NUMERIC.matcher(value).matches()) return value;
        }
        return "'" + escapeSqlString(value, dialect) + "'";
    }

    public static String sanitizeIdent(String name) {
        StringBuilder b = new StringBuilder();
        for (char c : name.toCharArray()) {
            boolean ok = (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')
                      || (c >= '0' && c <= '9') || c == '_';
            b.append(ok ? c : '_');
        }
        return b.toString();
    }

    private static String[] sanitizeHeaders(String[] headers) {
        Map<String, Integer> seen = new HashMap<>();
        String[] out = new String[headers.length];
        for (int i = 0; i < headers.length; i++) {
            String id = sanitizeIdent(headers[i].trim());
            if (id.isEmpty()) id = "col" + (i + 1);
            int n = seen.merge(id, 1, Integer::sum);
            if (n > 1) id = id + "_" + n;
            out[i] = id;
        }
        return out;
    }

    private static String csvEscape(String field) {
        boolean needs = field.indexOf(',') >= 0 || field.indexOf('"') >= 0
                     || field.indexOf('\n') >= 0 || field.indexOf('\r') >= 0;
        return needs ? "\"" + field.replace("\"", "\"\"") + "\"" : field;
    }

    public static final class Options {
        public String table = "";
        public String format = "insert";        // insert | copy | load-data
        public String dialect = "standard";     // standard | mysql | postgres
        public int batchSize = 100;
        public boolean inferTypes = true;
        public boolean quoteIdentifiers = true;
        public String fileName = "import.csv";
    }

    public static final class Result {
        public boolean ok;
        public String sql = "";
        public int rows;
        public String error;
    }

    private static String quoteIdent(String name, boolean mysql, boolean quoteIds) {
        if (!quoteIds) return name;
        return mysql ? "`" + name + "`" : "\"" + name + "\"";
    }

    private static String cellAt(String[] row, int ci) {
        return ci < row.length ? row[ci] : "";
    }

    public static Result convert(String csv, Options opts) {
        Result res = new Result();
        String text = csv.trim();
        List<String[]> rows = csvToRows(text);
        if (text.isEmpty() || rows.size() < 2) {
            res.error = "No rows to import.";
            return res;
        }

        String[] cols = sanitizeHeaders(rows.get(0));
        List<String[]> data = rows.subList(1, rows.size());
        boolean mysql = "mysql".equals(opts.dialect);
        boolean identMysql = "load-data".equals(opts.format) ? true
                          : "copy".equals(opts.format) ? false
                          : mysql;

        String tblRaw = sanitizeIdent(opts.table);
        String tbl = quoteIdent(tblRaw.isEmpty() ? "tbl" : tblRaw, identMysql, opts.quoteIdentifiers);
        List<String> quotedCols = new ArrayList<>();
        for (String c : cols) quotedCols.add(quoteIdent(c, identMysql, opts.quoteIdentifiers));
        String colList = String.join(", ", quotedCols);

        if ("copy".equals(opts.format) || "load-data".equals(opts.format)) {
            List<String> payload = new ArrayList<>();
            List<String> header = new ArrayList<>();
            for (String c : cols) header.add(csvEscape(c));
            payload.add(String.join(",", header));
            for (String[] r : data) {
                List<String> fields = new ArrayList<>();
                for (int ci = 0; ci < cols.length; ci++) fields.add(csvEscape(cellAt(r, ci)));
                payload.add(String.join(",", fields));
            }
            String body = String.join("\n", payload);
            if ("copy".equals(opts.format)) {
                res.ok = true;
                res.rows = data.size();
                res.sql = "COPY " + tbl + " (" + colList
                        + ") FROM STDIN WITH (FORMAT csv, HEADER true);\n" + body + "\n\\.";
            } else {
                StringBuilder clean = new StringBuilder();
                for (char c : opts.fileName.toCharArray()) {
                    boolean ok = (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')
                              || (c >= '0' && c <= '9') || c == '.' || c == '_' || c == '-' || c == '/';
                    if (ok) clean.append(c);
                }
                String file = clean.length() == 0 ? "import.csv" : clean.toString();
                res.ok = true;
                res.rows = data.size();
                res.sql = "LOAD DATA LOCAL INFILE '" + file + "'\nINTO TABLE " + tbl + "\n"
                        + "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\n"
                        + "LINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n" + body;
            }
            return res;
        }

        int size = Math.max(1, opts.batchSize);
        List<String> stmts = new ArrayList<>();
        for (int start = 0; start < data.size(); start += size) {
            List<String> values = new ArrayList<>();
            int end = Math.min(data.size(), start + size);
            for (int r = start; r < end; r++) {
                List<String> vals = new ArrayList<>();
                for (int ci = 0; ci < cols.length; ci++) {
                    vals.add(fieldLiteral(cellAt(data.get(r), ci), opts.dialect, opts.inferTypes));
                }
                values.add("  (" + String.join(", ", vals) + ")");
            }
            stmts.add("INSERT INTO " + tbl + " (" + colList + ") VALUES\n"
                    + String.join(",\n", values) + ";");
        }
        res.ok = true;
        res.rows = data.size();
        res.sql = String.join("\n", stmts);
        return res;
    }

    // Example:
    //   Options o = new Options();
    //   o.table = "users";
    //   Result r = CsvToSql.convert("id,name\n1,Ada\n2,", o);
    //   System.out.println(r.sql);
    //   // INSERT INTO "users" ("id", "name") VALUES
    //   //   (1, 'Ada'),
    //   //   (2, NULL);
}