JSON to SQL INSERT — Rust source

// json-to-sql — Pure JSON → SQL INSERT converter.
// Language: Rust (edition 2021, std only).
//
// CosmoDev polyglot showcase port of the `json-to-sql` tool, ported from
// src/lib/jsonToSql.ts. Display source — part of CosmoDev's polyglot tool
// pages (https://dev.cosmolabs.org).
//
// Rust's standard library ships no JSON parser, so this file includes a
// small recursive-descent parser. Objects carry their keys in a Vec (not a
// HashMap), which preserves document order — that lets column ordering match
// the TypeScript port's Object.keys() first-seen semantics for free.
//
// The public API is total: malformed input is reported through
// ConvertResult.err, never via panic.

// ---------------------------------------------------------------------------
// Public configuration & result types
// ---------------------------------------------------------------------------

/// SQL flavour controlling escape rules and identifier quoting.
#[derive(Clone, Copy, PartialEq, Eq, Debug)]
pub enum Dialect {
    Standard,
    Mysql,
    Postgres,
}

/// INSERT builder configuration. `Option` fields distinguish "unset" (apply
/// the default) from an explicit value.
pub struct Options {
    pub table: String,
    pub dialect: Option<Dialect>,
    pub quote_identifiers: Option<bool>,
}

impl Default for Options {
    fn default() -> Self {
        Self {
            table: String::new(),
            dialect: None,
            quote_identifiers: None,
        }
    }
}

/// Conversion outcome. `err` is `None` when `ok` is true. Mirrors the
/// TypeScript `Result` interface.
pub struct ConvertResult {
    pub ok: bool,
    pub sql: String,
    pub rows: usize,
    pub err: Option<String>,
}

// ---------------------------------------------------------------------------
// Minimal JSON value tree (document-order preserving)
// ---------------------------------------------------------------------------

#[derive(Clone, Debug)]
enum Json {
    Null,
    Bool(bool),
    Number(f64),
    Str(String),
    Array(Vec<Json>),
    /// `(key, value)` pairs in document order; duplicate keys keep their
    /// first position but hold the last value (JavaScript object semantics).
    Object(Vec<(String, Json)>),
}

impl Json {
    fn is_object(&self) -> bool {
        matches!(self, Json::Object(_))
    }

    /// Look up a key in an object, returning the last-written value.
    fn field(&self, key: &str) -> Option<&Json> {
        if let Json::Object(entries) = self {
            entries.iter().rev().find(|(k, _)| k == key).map(|(_, v)| v)
        } else {
            None
        }
    }
}

// ---------------------------------------------------------------------------
// Recursive-descent JSON parser
// ---------------------------------------------------------------------------

struct Parser<'a> {
    bytes: &'a [u8],
    pos: usize,
}

impl<'a> Parser<'a> {
    fn new(input: &'a str) -> Self {
        Self { bytes: input.as_bytes(), pos: 0 }
    }

    fn skip_ws(&mut self) {
        while self.pos < self.bytes.len() {
            match self.bytes[self.pos] {
                b' ' | b'\t' | b'\n' | b'\r' => self.pos += 1,
                _ => break,
            }
        }
    }

    fn peek(&self) -> Option<u8> {
        self.bytes.get(self.pos).copied()
    }

    fn parse_value(&mut self) -> Result<Json, String> {
        self.skip_ws();
        match self.peek() {
            Some(b'{') => self.parse_object(),
            Some(b'[') => self.parse_array(),
            Some(b'"') => Ok(Json::Str(self.parse_string()?)),
            Some(b't') | Some(b'f') => self.parse_bool(),
            Some(b'n') => self.parse_null(),
            Some(c) if c == b'-' || c.is_ascii_digit() => self.parse_number(),
            Some(_) => Err(format!("unexpected character at byte {}", self.pos)),
            None => Err("unexpected end of JSON input".into()),
        }
    }

    fn parse_object(&mut self) -> Result<Json, String> {
        self.pos += 1; // consume '{'
        let mut entries: Vec<(String, Json)> = Vec::new();
        self.skip_ws();
        if self.peek() == Some(b'}') {
            self.pos += 1;
            return Ok(Json::Object(entries));
        }
        loop {
            self.skip_ws();
            if self.peek() != Some(b'"') {
                return Err("expected string key in object".into());
            }
            let key = self.parse_string()?;
            self.skip_ws();
            if self.peek() != Some(b':') {
                return Err("expected ':' after object key".into());
            }
            self.pos += 1; // consume ':'
            let val = self.parse_value()?;
            // First-seen position, last-write value (JS object semantics).
            if let Some(slot) = entries.iter_mut().find(|(k, _)| k == &key) {
                slot.1 = val;
            } else {
                entries.push((key, val));
            }
            self.skip_ws();
            match self.peek() {
                Some(b',') => self.pos += 1,
                Some(b'}') => {
                    self.pos += 1;
                    break;
                }
                _ => return Err("expected ',' or '}' in object".into()),
            }
        }
        Ok(Json::Object(entries))
    }

    fn parse_array(&mut self) -> Result<Json, String> {
        self.pos += 1; // consume '['
        let mut items = Vec::new();
        self.skip_ws();
        if self.peek() == Some(b']') {
            self.pos += 1;
            return Ok(Json::Array(items));
        }
        loop {
            let val = self.parse_value()?;
            items.push(val);
            self.skip_ws();
            match self.peek() {
                Some(b',') => self.pos += 1,
                Some(b']') => {
                    self.pos += 1;
                    break;
                }
                _ => return Err("expected ',' or ']' in array".into()),
            }
        }
        Ok(Json::Array(items))
    }

    /// Parse a `"..."` literal, honouring all JSON escapes including
    /// UTF-16 surrogate pairs for code points outside the BMP.
    fn parse_string(&mut self) -> Result<String, String> {
        self.pos += 1; // consume opening quote
        let mut out = String::new();
        while let Some(c) = self.peek() {
            match c {
                b'"' => {
                    self.pos += 1;
                    return Ok(out);
                }
                b'\\' => {
                    self.pos += 1;
                    let e = self.peek().ok_or("truncated escape sequence")?;
                    self.pos += 1;
                    match e {
                        b'"' => out.push('"'),
                        b'\\' => out.push('\\'),
                        b'/' => out.push('/'),
                        b'b' => out.push('\u{0008}'),
                        b'f' => out.push('\u{000C}'),
                        b'n' => out.push('\n'),
                        b'r' => out.push('\r'),
                        b't' => out.push('\t'),
                        b'u' => out.push(self.parse_unicode_escape()?),
                        _ => return Err(format!("invalid escape \\{}", e as char)),
                    }
                }
                _ => {
                    // Copy a run of literal bytes verbatim up to the next
                    // structural character; validate as UTF-8 in one shot.
                    let start = self.pos;
                    self.pos += 1;
                    while let Some(cc) = self.peek() {
                        if cc == b'"' || cc == b'\\' {
                            break;
                        }
                        self.pos += 1;
                    }
                    out.push_str(
                        std::str::from_utf8(&self.bytes[start..self.pos])
                            .map_err(|_| "invalid UTF-8 in string".to_string())?,
                    );
                }
            }
        }
        Err("unterminated string".into())
    }

    /// Parse the 4 hex digits following `\u`, and a trailing low surrogate if
    /// the first code unit is a high surrogate.
    fn parse_unicode_escape(&mut self) -> Result<char, String> {
        let hi = self.parse_hex4()?;
        if (0xD800..=0xDBFF).contains(&hi) {
            // High surrogate — require a paired `\uXXXX` low surrogate.
            if self.peek() == Some(b'\\') {
                self.pos += 1;
                if self.peek() == Some(b'u') {
                    self.pos += 1;
                    let lo = self.parse_hex4()?;
                    if (0xDC00..=0xDFFF).contains(&lo) {
                        let code = 0x10000 + (((hi as u32) - 0xD800) << 10)
                            + (lo as u32) - 0xDC00;
                        return char::from_u32(code)
                            .ok_or_else(|| "invalid surrogate pair".into());
                    }
                }
            }
            Err("unpaired high surrogate".into())
        } else if (0xDC00..=0xDFFF).contains(&hi) {
            Err("unpaired low surrogate".into())
        } else {
            char::from_u32(hi as u32).ok_or_else(|| "invalid code point".into())
        }
    }

    fn parse_hex4(&mut self) -> Result<u16, String> {
        let mut v: u16 = 0;
        for _ in 0..4 {
            let c = self.peek().ok_or("truncated \\u escape")?;
            self.pos += 1;
            let d = match c {
                b'0'..=b'9' => (c - b'0') as u16,
                b'a'..=b'f' => (c - b'a' + 10) as u16,
                b'A'..=b'F' => (c - b'A' + 10) as u16,
                _ => return Err("invalid hex digit in \\u escape".into()),
            };
            v = v.checked_mul(16).ok_or("\\u overflow")? + d;
        }
        Ok(v)
    }

    fn parse_number(&mut self) -> Result<Json, String> {
        let start = self.pos;
        if self.peek() == Some(b'-') {
            self.pos += 1;
        }
        while let Some(c) = self.peek() {
            match c {
                b'0'..=b'9' | b'.' | b'e' | b'E' | b'+' | b'-' => self.pos += 1,
                _ => break,
            }
        }
        let text = std::str::from_utf8(&self.bytes[start..self.pos])
            .map_err(|_| "invalid UTF-8 in number".to_string())?;
        let f: f64 = text
            .parse()
            .map_err(|_| format!("invalid number literal: {}", text))?;
        Ok(Json::Number(f))
    }

    fn parse_bool(&mut self) -> Result<Json, String> {
        if self.bytes[self.pos..].starts_with(b"true") {
            self.pos += 4;
            Ok(Json::Bool(true))
        } else if self.bytes[self.pos..].starts_with(b"false") {
            self.pos += 5;
            Ok(Json::Bool(false))
        } else {
            Err("invalid literal".into())
        }
    }

    fn parse_null(&mut self) -> Result<Json, String> {
        if self.bytes[self.pos..].starts_with(b"null") {
            self.pos += 4;
            Ok(Json::Null)
        } else {
            Err("invalid literal".into())
        }
    }
}

/// Parse a complete JSON document, rejecting trailing garbage.
fn parse(input: &str) -> Result<Json, String> {
    let mut p = Parser::new(input);
    let v = p.parse_value()?;
    p.skip_ws();
    if p.pos != p.bytes.len() {
        return Err(format!("trailing data at byte {}", p.pos));
    }
    Ok(v)
}

// ---------------------------------------------------------------------------
// Compact JSON re-serializer (for the object/array literal fallback)
// ---------------------------------------------------------------------------

/// Render a Json value back to compact JSON text, matching JavaScript's
/// `JSON.stringify` (no whitespace, `/` left unescaped, control chars as
/// `\uXXXX`, non-ASCII code points emitted literally).
fn stringify(value: &Json) -> String {
    let mut out = String::new();
    stringify_into(value, &mut out);
    out
}

fn stringify_into(value: &Json, out: &mut String) {
    match value {
        Json::Null => out.push_str("null"),
        Json::Bool(b) => out.push_str(if *b { "true" } else { "false" }),
        Json::Number(f) => {
            // JSON forbids NaN/Infinity — emit null, exactly as JS does.
            if !f.is_finite() {
                out.push_str("null");
            } else {
                out.push_str(&format_number(*f));
            }
        }
        Json::Str(s) => {
            out.push('"');
            for c in s.chars() {
                match c {
                    '"' => out.push_str("\\\""),
                    '\\' => out.push_str("\\\\"),
                    '\n' => out.push_str("\\n"),
                    '\r' => out.push_str("\\r"),
                    '\t' => out.push_str("\\t"),
                    '\u{0008}' => out.push_str("\\b"),
                    '\u{000C}' => out.push_str("\\f"),
                    c if (c as u32) < 0x20 => {
                        out.push_str(&format!("\\u{:04x}", c as u32));
                    }
                    c => out.push(c),
                }
            }
            out.push('"');
        }
        Json::Array(items) => {
            out.push('[');
            for (i, it) in items.iter().enumerate() {
                if i > 0 {
                    out.push(',');
                }
                stringify_into(it, out);
            }
            out.push(']');
        }
        Json::Object(entries) => {
            out.push('{');
            for (i, (k, v)) in entries.iter().enumerate() {
                if i > 0 {
                    out.push(',');
                }
                // Object keys are JSON strings — reuse the string serializer.
                stringify_into(&Json::Str(k.clone()), out);
                out.push(':');
                stringify_into(v, out);
            }
            out.push('}');
        }
    }
}

// ---------------------------------------------------------------------------
// SQL conversion
// ---------------------------------------------------------------------------

/// Mirror JavaScript's `String(number)`: integer-valued floats render without
/// a decimal point (`1.0` → `"1"`); fractional values use Rust's shortest
/// round-trippable float Display.
fn format_number(f: f64) -> String {
    if f.fract() == 0.0 && f.abs() < 1e16 {
        format!("{}", f as i64)
    } else {
        format!("{}", f)
    }
}

/// Escape a string for a single-quoted SQL literal. Single quotes are always
/// doubled; MySQL mode additionally neutralises the backslash and the
/// control bytes that terminate a MySQL statement.
pub fn escape_sql_string(s: &str, dialect: Dialect) -> String {
    let mut out = s.replace('\'', "''");
    if dialect == Dialect::Mysql {
        // Backslash first so the escapes added below aren't re-doubled.
        out = out.replace('\\', "\\\\");
        out = out.replace('\u{0000}', "\\0");
        out = out.replace('\n', "\\n");
        out = out.replace('\r', "\\r");
        out = out.replace('\u{001A}', "\\Z"); // SUB — ends MySQL input
    }
    out
}

/// Render a JSON value as a SQL literal.
pub fn sql_literal(value: &Json, dialect: Dialect) -> String {
    match value {
        Json::Null => "NULL".to_string(),
        Json::Bool(b) => {
            if dialect == Dialect::Mysql {
                if *b { "1".into() } else { "0".into() }
            } else if *b {
                "TRUE".into()
            } else {
                "FALSE".into()
            }
        }
        Json::Number(f) => {
            if !f.is_finite() {
                "NULL".into()
            } else {
                format_number(*f)
            }
        }
        Json::Str(s) => format!("'{}'", escape_sql_string(s, dialect)),
        // Objects/arrays → compact JSON text literal.
        other => format!("'{}'", escape_sql_string(&stringify(other), dialect)),
    }
}

/// Wrap an identifier in dialect-appropriate quotes, or leave it bare when
/// identifier quoting is disabled.
fn quote_ident(name: &str, dialect: Dialect, quote: bool) -> String {
    if !quote {
        return name.to_string();
    }
    match dialect {
        Dialect::Mysql => format!("`{}`", name),
        _ => format!("\"{}\"", name),
    }
}

/// Reduce an identifier to [A-Za-z0-9_], substituting `_` for everything else
/// and defaulting to "tbl" when nothing usable remains.
fn sanitize_ident(name: &str) -> String {
    let cleaned: String = name
        .chars()
        .map(|c| {
            if c.is_ascii_alphanumeric() || c == '_' {
                c
            } else {
                '_'
            }
        })
        .collect();
    if cleaned.is_empty() {
        "tbl".to_string()
    } else {
        cleaned
    }
}

/// Convert a JSON document into a single multi-row INSERT statement.
///
/// A bare JSON object is treated as one row; a JSON array as many. Rows may
/// differ in shape — the column list is the union of every key in first-seen
/// order, and missing values are emitted as NULL.
pub fn json_to_insert(json_string: &str, opts: &Options) -> ConvertResult {
    let parsed = match parse(json_string) {
        Ok(v) => v,
        Err(e) => {
            return ConvertResult {
                ok: false,
                sql: String::new(),
                rows: 0,
                err: Some(e),
            }
        }
    };

    // Borrow the row set from the parsed tree: array → many, else → one.
    let rows: Vec<&Json> = match &parsed {
        Json::Array(items) => items.iter().collect(),
        other => vec![other],
    };

    if rows.is_empty() {
        return ConvertResult {
            ok: false,
            sql: String::new(),
            rows: 0,
            err: Some("No rows to insert.".into()),
        };
    }
    if !rows.iter().all(|r| r.is_object()) {
        return ConvertResult {
            ok: false,
            sql: String::new(),
            rows: 0,
            err: Some("Rows must be objects.".into()),
        };
    }

    let dialect = opts.dialect.unwrap_or(Dialect::Standard);
    let quote = opts.quote_identifiers.unwrap_or(true);
    let table = quote_ident(&sanitize_ident(&opts.table), dialect, quote);

    // Union of every row's keys in first-seen order.
    let mut cols: Vec<String> = Vec::new();
    for row in &rows {
        if let Json::Object(entries) = row {
            for (k, _) in entries {
                if !cols.iter().any(|c| c == k) {
                    cols.push(k.clone());
                }
            }
        }
    }
    let col_list = cols
        .iter()
        .map(|c| quote_ident(c, dialect, quote))
        .collect::<Vec<_>>()
        .join(", ");

    let null = Json::Null;
    let value_lines: Vec<String> = rows
        .iter()
        .map(|row| {
            let vals: Vec<String> = cols
                .iter()
                .map(|c| {
                    let val = row.field(c).unwrap_or(&null);
                    sql_literal(val, dialect)
                })
                .collect();
            format!("  ({})", vals.join(", "))
        })
        .collect();

    let sql = format!(
        "INSERT INTO {} ({}) VALUES\n{};",
        table,
        col_list,
        value_lines.join(",\n")
    );
    ConvertResult {
        ok: true,
        sql,
        rows: rows.len(),
        err: None,
    }
}