Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

32.8 KB
Raw Blame
//! Lossless Oracle Scheduler argument values. This codec is separate from general SQL values.

use dbx_types::native_jobs::{NativeJobArgumentValue, NativeJobRow};
use serde_json::{json, Value};

mod characters;
mod civil;
mod identity;
mod scalars;
pub use civil::{civil_date_query, editable_argument, normalize_civil_date};
pub use identity::{anydata_join, anydata_query_prefix, ScalarType};

/// Fixed catalog columns only; no identifier or accessor is supplied by a client.
#[derive(Clone, Copy)]
pub enum AnydataColumn {
    JobArgument,
    ProgramDefault,
}

pub fn anydata_projection(column: AnydataColumn) -> String {
    let column = column.sql();
    let type_name = format!("{column}.GetTypeName()");
    let null_access = ScalarType::ALL
        .into_iter()
        .map(|kind| {
            format!("WHEN '{}' THEN CASE WHEN {column}.{}() IS NULL THEN 1 ELSE 0 END", kind.token(), kind.access())
        })
        .collect::<Vec<_>>()
        .join(" ");
    let values = scalars::projection(column);
    format!(
        "CASE WHEN {column} IS NOT NULL THEN 1 ELSE 0 END AS DBX_ANYDATA_PRESENT, \
         CASE WHEN {column} IS NOT NULL THEN {type_name} END AS DBX_ANYDATA_TYPE, \
         DBX_AT.CODEC AS DBX_ANYDATA_CODEC, DBX_AT.TYPE_NAME AS DBX_ANYDATA_CONSTRUCTOR_TYPE, \
         CASE WHEN {column} IS NOT NULL THEN CASE DBX_AT.CODEC {null_access} ELSE NULL END END AS DBX_ANYDATA_IS_NULL, \
         CASE WHEN {column} IS NOT NULL THEN CASE DBX_AT.CODEC \
         WHEN 'number' THEN TO_CHAR({column}.AccessNumber(), 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') \
         WHEN 'interval-ym' THEN TO_CHAR(EXTRACT(YEAR FROM {column}.AccessIntervalYM()) * 12 + EXTRACT(MONTH FROM {column}.AccessIntervalYM()), 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') \
         WHEN 'interval-ds' THEN TO_CHAR((EXTRACT(DAY FROM {column}.AccessIntervalDS()) * 86400 + EXTRACT(HOUR FROM {column}.AccessIntervalDS()) * 3600 + EXTRACT(MINUTE FROM {column}.AccessIntervalDS()) * 60 + EXTRACT(SECOND FROM {column}.AccessIntervalDS())) * 1000000000, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') \
         WHEN 'urowid' THEN ROWIDTOCHAR({column}.AccessURowid()) ELSE NULL END END AS DBX_ANYDATA_TEXT, {values}"
    )
}

pub fn scalar_envelope(value: &Value) -> Result<ArgumentExpectation, &'static str> {
    let object = value.as_object().ok_or("native_job_argument_value_invalid")?;
    if object.len() != 3 || object.get("$dbx").and_then(Value::as_str) != Some("oracle-anydata-v1") {
        return Err("native_job_argument_value_invalid");
    }
    let kind = match (object.get("typeName"), object.get("scalarType")) {
        (Some(Value::String(name)), None) if name == "SYS.NUMBER" => ScalarType::Number,
        (None, Some(Value::String(token))) => {
            ScalarType::from_token(token).ok_or("native_job_argument_value_invalid")?
        }
        _ => return Err("native_job_argument_value_invalid"),
    };
    let value = object.get("value").ok_or("native_job_argument_value_invalid")?;
    let value = if object.contains_key("scalarType") && value.is_null() {
        None
    } else {
        Some(scalars::normalize(kind, value)?)
    };
    Ok(ArgumentExpectation { scalar_type: kind, value })
}

pub fn scalar_anydata_expression(value: &Value) -> Result<(String, ArgumentExpectation), &'static str> {
    let expected = scalar_envelope(value)?;
    Ok((scalars::expression(&expected)?, expected))
}

/// The optional setter is generated by the Job builder; a missing setter performs no writes.
pub fn scalar_value_block(value: &Value, setter: Option<&str>) -> Result<(String, ArgumentExpectation), &'static str> {
    let expected = scalar_envelope(value)?;
    let character = expected.value.is_some()
        && matches!(
            expected.scalar_type,
            ScalarType::Varchar2 | ScalarType::Char | ScalarType::Nvarchar2 | ScalarType::Nchar
        );
    let extra = if character { characters::declarations() } else { "" };
    let construction = if character {
        characters::construction(&expected)?
    } else {
        format!("l_anydata := {}; ", scalars::expression(&expected)?)
    };
    let validation = scalars::validation(&expected)?;
    let kind = expected.scalar_type.token();
    let prefix = anydata_query_prefix();
    let sql = format!("DECLARE l_anydata SYS.ANYDATA; l_type_name VARCHAR2(4000); l_match_count PLS_INTEGER; {extra} BEGIN {construction} IF l_anydata IS NULL THEN RAISE_APPLICATION_ERROR(-20001, 'native_job_argument_conversion_loss'); END IF; l_type_name := l_anydata.GetTypeName(); {prefix}SELECT COUNT(*) INTO l_match_count FROM DBX_ANYDATA_UNIQUE WHERE CODEC = '{kind}' AND SYS.UTL_RAW.CAST_TO_RAW(TYPE_NAME) = SYS.UTL_RAW.CAST_TO_RAW(l_type_name); IF l_match_count <> 1 THEN RAISE_APPLICATION_ERROR(-20001, 'native_job_argument_type_unverified'); END IF; {validation}{} END;", setter.unwrap_or(""));
    Ok((sql, expected))
}

/// Parse an explicit envelope only. JSON-looking strings retain their original string contract.
pub fn number_envelope(value: &Value) -> Result<ArgumentExpectation, &'static str> {
    let object = value.as_object().ok_or("native_job_argument_value_invalid")?;
    if object.len() != 3
        || object.get("$dbx").and_then(Value::as_str) != Some("oracle-anydata-v1")
        || object.get("typeName").and_then(Value::as_str) != Some("SYS.NUMBER")
    {
        return Err("native_job_argument_value_invalid");
    }
    let value = object.get("value").and_then(Value::as_str).ok_or("native_job_argument_value_invalid")?;
    Ok(ArgumentExpectation { scalar_type: ScalarType::Number, value: Some(Value::String(normalize_number(value)?)) })
}

pub fn number_anydata_expression(value: &Value) -> Result<(String, NativeJobArgumentValue), &'static str> {
    let scalar = number_envelope(value)?;
    let Some(Value::String(number)) = &scalar.value else { unreachable!() };
    Ok((format!("SYS.ANYDATA.ConvertNumber({number})"), scalar.editable_value()))
}

pub fn null_anydata_expression(data_type: &str) -> Result<(String, ArgumentExpectation), &'static str> {
    let kind = match data_type.to_ascii_uppercase().as_str() {
        "VARCHAR2" | "VARCHAR" | "CHAR" => ScalarType::Varchar2,
        "NUMBER" | "INTEGER" => ScalarType::Number,
        "DATE" => ScalarType::Date,
        "TIMESTAMP" => ScalarType::Timestamp,
        "TIMESTAMP WITH TIME ZONE" => ScalarType::TimestampTz,
        "TIMESTAMP WITH LOCAL TIME ZONE" => ScalarType::TimestampLtz,
        "NVARCHAR2" => ScalarType::Nvarchar2,
        "NCHAR" => ScalarType::Nchar,
        "INTERVAL YEAR TO MONTH" => ScalarType::IntervalYm,
        "INTERVAL DAY TO SECOND" => ScalarType::IntervalDs,
        "RAW" => ScalarType::Raw,
        "BINARY_FLOAT" => ScalarType::BinaryFloat,
        "BINARY_DOUBLE" => ScalarType::BinaryDouble,
        "UROWID" => ScalarType::Urowid,
        _ => return Err("native_job_argument_type_required_for_null"),
    };
    Ok((kind.null_expression(), ArgumentExpectation { scalar_type: kind, value: None }))
}

pub fn oracle_argument_matches(row: &NativeJobRow, desired: &NativeJobArgumentValue, data_type: &str) -> bool {
    let expected = match desired {
        NativeJobArgumentValue::Null => null_anydata_expression(data_type).map(|(_, storage)| storage),
        NativeJobArgumentValue::Value { value: Value::String(text) } if text.is_empty() => {
            null_anydata_expression(data_type).map(|(_, storage)| storage)
        }
        NativeJobArgumentValue::Value { value: Value::String(text) } => {
            return decode_argument(row, false) == ArgumentStorage::Text(text.clone())
        }
        NativeJobArgumentValue::Value { value } => scalar_envelope(value),
        _ => return false,
    };
    expected.is_ok_and(|expected| expected.matches(&decode_argument(row, false)))
}

pub fn canonical_argument_row(row: &NativeJobRow, default_value: bool) -> NativeJobRow {
    let storage = decode_argument(row, default_value);
    if matches!(storage, ArgumentStorage::Opaque(_)) {
        return row.clone();
    }
    let mut canonical = row.clone();
    canonical.retain(|key, _| {
        !matches!(
            key.as_str(),
            "DBX_ANYDATA_PRESENT"
                | "DBX_ANYDATA_TYPE"
                | "DBX_ANYDATA_IS_NULL"
                | "DBX_ANYDATA_TEXT"
                | "DBX_ANYDATA_CODEC"
                | "DBX_ANYDATA_CONSTRUCTOR_TYPE"
                | "DBX_ANYDATA_DAY"
                | "DBX_ANYDATA_SECONDS"
                | "DBX_ANYDATA_TZ_REGION"
                | "DBX_ANYDATA_TZ_OFFSET"
                | "DBX_ANYDATA_TZ_ABBR"
                | "DBX_ANYDATA_HEX"
                | "DBX_ANYDATA_LENGTH"
                | "DBX_ARGUMENT_DEFAULT"
                | "DBX_ANYDATA_CIVIL_DATE"
        ) && if default_value {
            !matches!(key.as_str(), "DEFAULT_VALUE" | "DEFAULT_ANYDATA_VALUE")
        } else {
            !matches!(key.as_str(), "VALUE" | "ANYDATA_VALUE")
        }
    });
    canonical.insert("DBX_ARGUMENT_CANONICAL".into(), storage.canonical_value());
    canonical
}

pub fn validate_lightweight_argument(style: Option<&str>, value: &NativeJobArgumentValue) -> Result<(), &'static str> {
    let string_setter_or_reset = match value {
        NativeJobArgumentValue::Unset | NativeJobArgumentValue::Inherit => true,
        NativeJobArgumentValue::Value { value: Value::String(text) } => !text.is_empty(),
        _ => false,
    };
    if style.is_some_and(|style| style.eq_ignore_ascii_case("LIGHTWEIGHT")) && !string_setter_or_reset {
        return Err("native_job_lightweight_anydata_unsupported");
    }
    Ok(())
}

/// Storage identity is part of equality: a VARCHAR override and ANYDATA VARCHAR2 are not interchangeable.
#[derive(Clone, PartialEq)]
pub enum ArgumentStorage {
    Text(String),
    TypedNull { type_name: String, scalar_type: ScalarType },
    Scalar { type_name: String, scalar_type: ScalarType, value: Value },
    Opaque(NativeJobRow),
}

impl ArgumentStorage {
    pub fn editable_value(&self) -> NativeJobArgumentValue {
        match self {
            Self::Text(value) => NativeJobArgumentValue::Value { value: Value::String(value.clone()) },
            Self::TypedNull { scalar_type, .. } => {
                ArgumentExpectation { scalar_type: *scalar_type, value: None }.editable_value()
            }
            Self::Scalar { scalar_type, value, .. } => {
                ArgumentExpectation { scalar_type: *scalar_type, value: Some(value.clone()) }.editable_value()
            }
            Self::Opaque(row) => NativeJobArgumentValue::Opaque { value: json!(row) },
        }
    }

    /// Excludes driver-specific locator text only after a complete value has been decoded.
    pub fn canonical_value(&self) -> Value {
        match self {
            Self::Text(value) => json!({"storage": "varchar", "value": value}),
            Self::TypedNull { type_name, scalar_type } => {
                json!({"storage": "anydata", "typeName": type_name, "scalarType": scalar_type.token(), "isNull": true})
            }
            Self::Scalar { type_name, scalar_type, value } => {
                json!({"storage": "anydata", "typeName": type_name, "scalarType": scalar_type.token(), "isNull": false, "value": value})
            }
            Self::Opaque(row) => json!({"storage": "opaque", "row": row}),
        }
    }
}

#[derive(Clone)]
pub struct ArgumentExpectation {
    pub scalar_type: ScalarType,
    pub value: Option<Value>,
}

impl ArgumentExpectation {
    pub fn editable_value(&self) -> NativeJobArgumentValue {
        match &self.value {
            None => NativeJobArgumentValue::Value {
                value: json!({"$dbx":"oracle-anydata-v1", "scalarType":self.scalar_type.token(), "value":null}),
            },
            Some(value) => NativeJobArgumentValue::Value {
                value: if self.scalar_type == ScalarType::Number {
                    json!({"$dbx":"oracle-anydata-v1", "typeName":"SYS.NUMBER", "value":value})
                } else {
                    json!({"$dbx":"oracle-anydata-v1", "scalarType":self.scalar_type.token(), "value":value})
                },
            },
        }
    }

    pub fn matches(&self, actual: &ArgumentStorage) -> bool {
        match (actual, &self.value) {
            (ArgumentStorage::TypedNull { scalar_type, .. }, None) => *scalar_type == self.scalar_type,
            (ArgumentStorage::Scalar { scalar_type, value, .. }, Some(expected)) => {
                *scalar_type == self.scalar_type && value == expected
            }
            _ => false,
        }
    }
}

fn projected_boolean(value: Option<&Value>) -> Option<bool> {
    match value? {
        Value::Bool(value) => Some(*value),
        Value::Number(value) if value.as_u64() == Some(0) => Some(false),
        Value::Number(value) if value.as_u64() == Some(1) => Some(true),
        Value::String(value) if value == "0" || value.eq_ignore_ascii_case("false") => Some(false),
        Value::String(value) if value == "1" || value.eq_ignore_ascii_case("true") => Some(true),
        _ => None,
    }
}

/// Decode only proven native values. Presence never falls back to a misleading VALUE column.
/// `default_value` selects the Program-default column; projected evidence uses the same aliases.
pub fn decode_argument(row: &NativeJobRow, default_value: bool) -> ArgumentStorage {
    let opaque = || ArgumentStorage::Opaque(row.clone());
    if row.get("OUT_ARGUMENT").is_some_and(|value| projected_boolean(Some(value)) != Some(false)) {
        return opaque();
    }
    match projected_boolean(row.get("DBX_ANYDATA_PRESENT")) {
        Some(false) => {
            let column = if default_value { "DEFAULT_VALUE" } else { "VALUE" };
            row.get(column)
                .and_then(Value::as_str)
                .map(|value| ArgumentStorage::Text(value.into()))
                .unwrap_or_else(opaque)
        }
        Some(true) => {
            let Some(type_name) = row.get("DBX_ANYDATA_TYPE").and_then(Value::as_str) else { return opaque() };
            let Some(scalar_type) = identity::proven_type(row, type_name) else {
                return opaque();
            };
            match projected_boolean(row.get("DBX_ANYDATA_IS_NULL")) {
                Some(true) => ArgumentStorage::TypedNull { type_name: type_name.into(), scalar_type },
                Some(false) => {
                    let Ok(value) = scalars::projected_value(scalar_type, row) else {
                        return opaque();
                    };
                    ArgumentStorage::Scalar { type_name: type_name.into(), scalar_type, value }
                }
                None => opaque(),
            }
        }
        None => opaque(),
    }
}

/// Canonicalize an exactly representable Oracle NUMBER without floating-point conversion.
pub fn normalize_number(value: &str) -> Result<String, &'static str> {
    const INVALID: &str = "native_job_argument_value_invalid";
    let mut parts = value.split(['e', 'E']);
    let mantissa = parts.next().ok_or(INVALID)?;
    let exponent = match parts.next() {
        Some(value) => value.parse::<i64>().map_err(|_| INVALID)?,
        None => 0,
    };
    if parts.next().is_some() {
        return Err(INVALID);
    }
    let negative = mantissa.starts_with('-');
    let unsigned = mantissa.strip_prefix(['+', '-']).unwrap_or(mantissa);
    let mut decimal_seen = false;
    let mut fractional_digits = 0_i64;
    let mut digits = String::with_capacity(unsigned.len());
    for byte in unsigned.bytes() {
        match byte {
            b'.' if !decimal_seen => decimal_seen = true,
            b'0'..=b'9' => {
                digits.push(char::from(byte));
                if decimal_seen {
                    fractional_digits = fractional_digits.checked_add(1).ok_or(INVALID)?;
                }
            }
            _ => return Err(INVALID),
        }
    }
    if digits.is_empty() {
        return Err(INVALID);
    }
    let significant = digits.trim_start_matches('0');
    if significant.is_empty() {
        return Ok("0".into());
    }
    let coefficient = significant.trim_end_matches('0');
    let trailing_zeros = i64::try_from(significant.len() - coefficient.len()).map_err(|_| INVALID)?;
    let low = exponent.checked_sub(fractional_digits).and_then(|n| n.checked_add(trailing_zeros)).ok_or(INVALID)?;
    let high = low.checked_add(i64::try_from(coefficient.len() - 1).map_err(|_| INVALID)?).ok_or(INVALID)?;
    // Each base-100 digit spans powers 2n and 2n+1, also for negative powers.
    // Removing decimal trailing zeroes does not remove a partial base-100 digit.
    if !(-130..=125).contains(&high) || high.div_euclid(2) - low.div_euclid(2) + 1 > 20 {
        return Err(INVALID);
    }
    let sign = if negative { "-" } else { "" };
    let fraction = if coefficient.len() > 1 { format!(".{}", &coefficient[1..]) } else { String::new() };
    Ok(format!("{sign}{}{fraction}E{high}", &coefficient[..1]))
}

#[cfg(test)]
mod tests {
    use super::*;

    fn number_row(value: Value) -> NativeJobRow {
        serde_json::from_value(json!({
            "DBX_ANYDATA_PRESENT": 1, "DBX_ANYDATA_TYPE": "SYS.NUMBER",
            "DBX_ANYDATA_IS_NULL": 0, "DBX_ANYDATA_TEXT": value,
            "ANYDATA_VALUE": "driver locator", "VALUE": "misleading fallback"
        }))
        .unwrap()
    }

    #[test]
    fn explicit_number_envelope_is_canonical_and_safe_to_construct() {
        let input = json!({"$dbx": "oracle-anydata-v1", "typeName": "SYS.NUMBER", "value": "9007199254740993.123"});
        let (expression, expected) = number_anydata_expression(&input).unwrap();
        assert_eq!(expression, "SYS.ANYDATA.ConvertNumber(9.007199254740993123E15)");
        assert!(expected == decode_argument(&number_row(json!("9007199254740993.123")), false).editable_value());
        for input in [
            json!(input.to_string()),
            json!({"$dbx": "oracle-anydata-v1", "typeName": "SYS.NUMBER", "value": null}),
            json!({"$dbx": "oracle-anydata-v1", "typeName": "SYS.NUMBER", "value": 123}),
            json!({"$dbx": "oracle-anydata-v1", "typeName": "SYS.NUMBER", "value": "1); END;--"}),
            json!({"$dbx": "oracle-anydata-v1", "typeName": "APP.NUMBER", "value": "1"}),
            json!({"$dbx": "oracle-anydata-v1", "typeName": "SYS.NUMBER", "value": "1", "sql": "ignored?"}),
        ] {
            assert!(number_anydata_expression(&input).is_err());
        }
    }

    #[test]
    fn every_null_scalar_uses_a_closed_constructor_and_proven_native_identity() {
        for kind in ScalarType::ALL {
            let native_name = kind.fixed_name().unwrap_or("native-name-from-this-constructor");
            let row: NativeJobRow = serde_json::from_value(json!({
                "DBX_ANYDATA_PRESENT":1, "DBX_ANYDATA_TYPE":native_name,
                "DBX_ANYDATA_CODEC":kind.token(), "DBX_ANYDATA_CONSTRUCTOR_TYPE":native_name,
                "DBX_ANYDATA_IS_NULL":1, "VALUE":"misleading fallback"
            }))
            .unwrap();
            let expected = ArgumentExpectation { scalar_type: kind, value: None };
            assert!(expected.matches(&decode_argument(&row, false)), "{}", kind.token());
            let mut changed_name = row.clone();
            changed_name.insert("DBX_ANYDATA_TYPE".into(), json!("other-name"));
            assert!(matches!(decode_argument(&changed_name, false), ArgumentStorage::Opaque(_)));
            for key in ["DBX_ANYDATA_CODEC", "DBX_ANYDATA_CONSTRUCTOR_TYPE"] {
                let mut incomplete = row.clone();
                incomplete.remove(key);
                assert!(matches!(decode_argument(&incomplete, false), ArgumentStorage::Opaque(_)));
            }
            let different = ArgumentExpectation {
                scalar_type: if kind == ScalarType::Date { ScalarType::Number } else { ScalarType::Date },
                value: None,
            };
            assert!(!different.matches(&decode_argument(&row, false)));
        }
        let prefix = anydata_query_prefix();
        for kind in ScalarType::ALL {
            assert!(prefix.contains(&format!("SELECT '{}' AS CODEC", kind.token())));
        }
        assert!(prefix.contains("P0.T2.GetTypeName()"));
        assert!(prefix.contains("GROUP BY SYS.UTL_RAW.CAST_TO_RAW(TYPE_NAME) HAVING COUNT(*) = 1"));
        assert!(!prefix.contains("SYS.NVARCHAR2'"));
        assert!(!prefix.contains("SYS.INTERVALYM'"));
    }

    #[test]
    fn collided_or_incomplete_constructor_evidence_never_turns_a_mismatched_accessor_into_null() {
        for name in ["SYS.CHAR", "SYS.VARCHAR2"] {
            let mut row: NativeJobRow = serde_json::from_value(json!({
                "DBX_ANYDATA_PRESENT":1, "DBX_ANYDATA_TYPE":name,
                "DBX_ANYDATA_CODEC":null, "DBX_ANYDATA_CONSTRUCTOR_TYPE":null,
                "DBX_ANYDATA_IS_NULL":1, "VALUE":"fallback must not be used"
            }))
            .unwrap();
            assert!(matches!(decode_argument(&row, false), ArgumentStorage::Opaque(_)));
            row.insert("DBX_ANYDATA_CODEC".into(), json!("nvarchar2"));
            assert!(matches!(decode_argument(&row, false), ArgumentStorage::Opaque(_)));
        }
        for data_type in [
            "NVARCHAR2",
            "NCHAR",
            "TIMESTAMP WITH LOCAL TIME ZONE",
            "INTERVAL YEAR TO MONTH",
            "INTERVAL DAY TO SECOND",
            "RAW",
            "BINARY_FLOAT",
            "BINARY_DOUBLE",
            "UROWID",
        ] {
            let (sql, expected) = null_anydata_expression(data_type).unwrap();
            assert!(sql.starts_with("SYS.ANYDATA.Convert"));
            assert!(expected.value.is_none());
        }
        assert!(null_anydata_expression("NUMBER); DELETE FROM t;--").is_err());
    }

    #[test]
    fn temporal_envelopes_preserve_days_nine_digits_and_region_identity() {
        let envelope = |kind, value| json!({"$dbx":"oracle-anydata-v1","scalarType":kind,"value":value});
        let date = envelope("date", json!({"day":"0000001","seconds":"00001"}));
        let (sql, expected) = scalar_anydata_expression(&date).unwrap();
        assert_eq!(expected.value, Some(json!({"day":"1","seconds":"1"})));
        assert!(sql.contains("TO_DATE('1', 'J')"));
        assert!(sql.contains("ConvertDate"));
        let ts = envelope("timestamp", json!({"day":"2458849","seconds":"86399.123456789"}));
        let (sql, expected) = scalar_anydata_expression(&ts).unwrap();
        assert!(sql.contains("TIMESTAMP(9)"));
        assert!(sql.contains("86399.123456789"));
        let row: NativeJobRow = serde_json::from_value(json!({"DBX_ANYDATA_PRESENT":1,"DBX_ANYDATA_TYPE":"SYS.TIMESTAMP","DBX_ANYDATA_IS_NULL":0,"DBX_ANYDATA_DAY":"2458849","DBX_ANYDATA_SECONDS":"86399.123456789"})).unwrap();
        assert!(expected.matches(&decode_argument(&row, false)));
        let tz = envelope(
            "timestamp-tz",
            json!({"day":"2459526","seconds":"5400.123456789","region":"America/New_York","offset":"-04:00","abbreviation":"EDT"}),
        );
        let (sql, expected) = scalar_anydata_expression(&tz).unwrap();
        assert!(sql.contains("FROM_TZ("));
        assert!(sql.contains(", '-04:00') AT TIME ZONE 'America/New_York'"));
        let mut other_region = tz;
        other_region["value"]["region"] = json!("US/Eastern");
        assert_ne!(expected.value, scalar_envelope(&other_region).unwrap().value);
        for value in [
            json!({"day":"0","seconds":"0"}),
            json!({"day":"5373485","seconds":"0"}),
            json!({"day":"1","seconds":"86400"}),
            json!({"day":"1","seconds":"1.1234567890"}),
        ] {
            assert!(scalar_envelope(&envelope("timestamp", value)).is_err());
        }
        assert!(scalar_envelope(&envelope("date", json!({"day":"1","seconds":"1.1"}))).is_err());
        let (ltz, _) =
            scalar_anydata_expression(&envelope("timestamp-ltz", json!({"day":"2458849","seconds":"123.000000001"})))
                .unwrap();
        assert!(ltz.contains("'+00:00'"));
        assert!(ltz.contains("TIMESTAMP(9) WITH LOCAL TIME ZONE"));
    }

    #[test]
    fn explicit_typed_null_retains_constructor_identity_without_changing_legacy_nulls() {
        let row: NativeJobRow = serde_json::from_value(json!({"DBX_ANYDATA_PRESENT":1,"DBX_ANYDATA_TYPE":"SYS.CHAR","DBX_ANYDATA_CODEC":"char","DBX_ANYDATA_CONSTRUCTOR_TYPE":"SYS.CHAR","DBX_ANYDATA_IS_NULL":1})).unwrap();
        let NativeJobArgumentValue::Value { value } = decode_argument(&row, false).editable_value() else {
            panic!("typed NULL must retain its constructor")
        };
        assert_eq!(value, json!({"$dbx":"oracle-anydata-v1","scalarType":"char","value":null}));
        let (sql, expected) = scalar_value_block(&value, None).unwrap();
        assert_eq!(expected.scalar_type, ScalarType::Char);
        assert!(expected.value.is_none());
        assert!(expected.matches(&decode_argument(&row, false)));
        assert!(sql.contains("ConvertChar(CAST(NULL AS CHAR(1)))"));
        assert!(sql.contains("l_anydata.AccessChar() IS NOT NULL"));
        assert!(!sql.contains("EXECUTE IMMEDIATE"));
        let mut wrong = row;
        wrong.insert("DBX_ANYDATA_TYPE".into(), json!("SYS.VARCHAR2"));
        wrong.insert("DBX_ANYDATA_CONSTRUCTOR_TYPE".into(), json!("SYS.VARCHAR2"));
        wrong.insert("DBX_ANYDATA_CODEC".into(), json!("varchar2"));
        assert!(!expected.matches(&decode_argument(&wrong, false)));
        assert_eq!(null_anydata_expression("CHAR").unwrap().1.scalar_type, ScalarType::Varchar2);
        assert!(scalar_envelope(&json!({"$dbx":"oracle-anydata-v1","typeName":"SYS.NUMBER","value":null})).is_err());
    }

    #[test]
    fn raw_intervals_and_binary_bits_have_exact_closed_values() {
        let envelope = |kind, value| json!({"$dbx":"oracle-anydata-v1","scalarType":kind,"value":value});
        let (sql, expected) = scalar_anydata_expression(&envelope("raw", json!("00ff"))).unwrap();
        assert_eq!(expected.value, Some(json!("00FF")));
        assert!(sql.contains("HEXTORAW('00FF')"));
        let long = "AB".repeat(5_000);
        let (sql, _) = scalar_anydata_expression(&envelope("raw", json!(long))).unwrap();
        assert_eq!(sql.matches("HEXTORAW(").count(), 3);
        assert!(sql.contains("UTL_RAW.CONCAT"));
        for (kind, value) in [
            ("raw", ""),
            ("raw", "F"),
            ("raw", "01');END;--"),
            ("binary-float", "80000000"),
            ("binary-float", "7FC00001"),
            ("binary-double", "8000000000000000"),
            ("binary-double", "7FF0000000000001"),
        ] {
            assert!(scalar_anydata_expression(&envelope(kind, json!(value))).is_err(), "{kind}/{value}");
        }
        for (kind, value) in
            [("binary-float", "7F800000"), ("binary-double", "FFF0000000000000"), ("binary-float", "00000000")]
        {
            assert!(scalar_anydata_expression(&envelope(kind, json!(value))).unwrap().0.contains("UTL_RAW.BIG_ENDIAN"));
        }
        let (sql, expected) = scalar_anydata_expression(&envelope("interval-ds", json!("-86400123456789"))).unwrap();
        assert!(sql.contains("NUMTODSINTERVAL(-86400.123456789, 'SECOND')"));
        assert_eq!(expected.value, Some(json!("-86400123456789")));
        assert!(scalar_anydata_expression(&envelope("interval-ym", json!("-11999999999"))).is_ok());
        assert!(scalar_anydata_expression(&envelope("interval-ym", json!("12000000000"))).is_err());
        let mut bad = envelope("raw", json!("00"));
        bad["typeName"] = json!("SYS.RAW");
        assert!(scalar_envelope(&bad).is_err());
    }

    #[test]
    fn complete_hex_projection_retains_unicode_spaces_and_unreplayable_binary_values() {
        let mut row: NativeJobRow = serde_json::from_value(json!({"DBX_ANYDATA_PRESENT":1,"DBX_ANYDATA_TYPE":"native-national-type","DBX_ANYDATA_CODEC":"nvarchar2","DBX_ANYDATA_CONSTRUCTOR_TYPE":"native-national-type","DBX_ANYDATA_IS_NULL":0,"DBX_ANYDATA_HEX":"0041005CD83DDE000020","DBX_ANYDATA_LENGTH":"4"})).unwrap();
        let decoded = decode_argument(&row, false);
        assert!(matches!(&decoded, ArgumentStorage::Scalar { value, .. } if *value == json!("A\\😀 ")));
        row.insert("DBX_ANYDATA_LENGTH".into(), json!("5"));
        assert!(matches!(decode_argument(&row, false), ArgumentStorage::Opaque(_)));
        row.insert("DBX_ANYDATA_LENGTH".into(), json!("4"));
        row.insert("DBX_ANYDATA_HEX".into(), json!("D800"));
        assert!(matches!(decode_argument(&row, false), ArgumentStorage::Opaque(_)));
        row.extend(serde_json::from_value::<NativeJobRow>(json!({"DBX_ANYDATA_TYPE":"native-binary-type","DBX_ANYDATA_CODEC":"binary-double","DBX_ANYDATA_CONSTRUCTOR_TYPE":"native-binary-type","DBX_ANYDATA_HEX":"8000000000000000"})).unwrap());
        assert!(matches!(decode_argument(&row, false), ArgumentStorage::Opaque(_)));
    }

    #[test]
    fn anydata_presence_and_native_null_require_complete_evidence() {
        let row = number_row(json!("9007199254740993"));
        assert!(matches!(decode_argument(&row, false), ArgumentStorage::Scalar { .. }));
        for key in ["DBX_ANYDATA_PRESENT", "DBX_ANYDATA_TYPE", "DBX_ANYDATA_IS_NULL", "DBX_ANYDATA_TEXT"] {
            let mut incomplete = row.clone();
            incomplete.remove(key);
            assert!(matches!(decode_argument(&incomplete, false), ArgumentStorage::Opaque(_)), "{key}");
        }
        let mut unknown = row.clone();
        unknown.insert("DBX_ANYDATA_TYPE".into(), json!("APP.OBJECT"));
        assert!(matches!(decode_argument(&unknown, false), ArgumentStorage::Opaque(_)));
        let mut typed_null = row;
        typed_null.insert("DBX_ANYDATA_IS_NULL".into(), json!(1));
        let number_null = decode_argument(&typed_null, false);
        typed_null.insert("DBX_ANYDATA_TYPE".into(), json!("SYS.DATE"));
        assert!(number_null != decode_argument(&typed_null, false));
        assert!(
            matches!(number_null.editable_value(), NativeJobArgumentValue::Value { value } if value == json!({"$dbx":"oracle-anydata-v1","scalarType":"number","value":null}))
        );
    }

    #[test]
    fn canonical_anydata_ignores_locators_but_preserves_type_and_exact_value() {
        let original = number_row(json!("123.4500"));
        let mut changed = number_row(json!("1.2345e2"));
        changed.insert("ANYDATA_VALUE".into(), json!("another locator"));
        assert_eq!(
            decode_argument(&original, false).canonical_value(),
            decode_argument(&changed, false).canonical_value()
        );
        changed.insert("DBX_ANYDATA_TEXT".into(), json!("123.450000000000000000000000000000000001"));
        assert_ne!(
            decode_argument(&original, false).canonical_value(),
            decode_argument(&changed, false).canonical_value()
        );
    }

    #[test]
    fn defaults_prioritize_anydata_and_plain_strings_keep_their_contents() {
        let mut row = number_row(json!("1"));
        row.insert("DEFAULT_VALUE".into(), json!("wrong"));
        assert!(matches!(decode_argument(&row, true), ArgumentStorage::Scalar { .. }));
        row.insert("DBX_ANYDATA_PRESENT".into(), json!(0));
        let text = " {\"$dbx\":\"oracle-anydata-v1\"}  ";
        row.insert("DEFAULT_VALUE".into(), json!(text));
        assert!(matches!(decode_argument(&row, true), ArgumentStorage::Text(value) if value == text));
        row.insert("DEFAULT_VALUE".into(), Value::Null);
        assert!(matches!(decode_argument(&row, true), ArgumentStorage::Opaque(_)));
        row.insert("OUT_ARGUMENT".into(), json!(true));
        assert!(matches!(decode_argument(&row, false), ArgumentStorage::Opaque(_)));
    }

    #[test]
    fn number_preserves_decimal_digits_and_normalizes_equivalent_spellings() {
        for (input, expected) in [
            ("9007199254740993.123456789", "9.007199254740993123456789E15"),
            ("+00123.45000e-2", "1.2345E0"),
            (".00100", "1E-3"),
            ("-100.", "-1E2"),
            ("-0.000e125", "0"),
            ("1e-130", "1E-130"),
            ("9.99e125", "9.99E125"),
        ] {
            assert_eq!(normalize_number(input), Ok(expected.into()), "{input}");
        }
    }

    #[test]
    fn number_precision_counts_base_100_digits_instead_of_decimal_digits() {
        let digits39 = "123456789012345678901234567890123456789";
        let digits40 = "1234567890123456789012345678901234567891";
        assert!(normalize_number(digits39).is_ok());
        assert!(normalize_number(digits40).is_ok());
        assert!(normalize_number(&format!("{digits39}e-1")).is_ok());
        assert!(normalize_number(&format!("{digits40}e-1")).is_err());
        assert!(normalize_number(&format!("{digits40}e1")).is_err());
        assert!(normalize_number(&format!("{digits40}00")).is_ok());
        assert!(normalize_number(&format!("{digits40}1")).is_err());
    }

    #[test]
    fn number_rejects_rounding_underflow_overflow_and_sql_fragments() {
        for value in [
            "1e-131",
            "9e-131",
            "1e126",
            "-1e126",
            "NaN",
            "Infinity",
            "",
            ".",
            "e1",
            "1e",
            "--1",
            "1e1e1",
            "1.2.3",
            " 1",
            "1 ",
            "1,2",
            "1);NULL;--",
            "1",
            "1e9223372036854775808",
        ] {
            assert!(normalize_number(value).is_err(), "{value}");
        }
    }
}