sz-orm-core 1.2.2

//! Oracle 23ai 真实数据库集成测试
//!
//! 使用 `oracle` crate (rust-oracle) 直接验证 sz-orm-core 的 Oracle 方言、
//! SQL 转义、事务、批量插入性能基线。
//!
//! 环境要求：
//! - Oracle 23ai Free 运行于 127.0.0.1:1521（FREEPDB1 服务名）
//! - Oracle Client 库（oci.dll 等）位于 PATH 中
//! - 可通过环境变量 `SZ_ORM_ORACLE_*` 覆盖默认连接参数
//!
//! 超大数据量场景：10 万条记录批量插入性能基线。

use oracle::Connection as OracleConn;
use std::sync::atomic::{AtomicU64, Ordering};
use std::time::Instant;
use sz_orm_core::dialect::{get_dialect, ColumnDef};
use sz_orm_core::DbType;
use sz_orm_core::Value;

/// 默认 Oracle 连接参数（本机 23ai Free）；可通过环境变量覆盖。
/// 使用专用测试用户 sz_orm_test（已授予 DBA 权限），避免 sys/SYSDBA 特权。
const ORACLE_USER_DEFAULT: &str = "sz_orm_test";
const ORACLE_PASSWORD_DEFAULT: &str = "SzOrmTest2026";
const ORACLE_CONNECT_STRING_DEFAULT: &str = "127.0.0.1:1521/freepdb1.FALSE";

fn oracle_user() -> String {
    std::env::var("SZ_ORM_ORACLE_USER").unwrap_or_else(|_| ORACLE_USER_DEFAULT.to_string())
}

fn oracle_password() -> String {
    std::env::var("SZ_ORM_ORACLE_PASSWORD").unwrap_or_else(|_| ORACLE_PASSWORD_DEFAULT.to_string())
}

fn oracle_connect_string() -> String {
    std::env::var("SZ_ORM_ORACLE_CONNECT_STRING")
        .unwrap_or_else(|_| ORACLE_CONNECT_STRING_DEFAULT.to_string())
}

fn open_conn() -> OracleConn {
    // 普通用户连接（sz_orm_test 已授予 DBA 权限，无需 SYSDBA 特权）
    OracleConn::connect(oracle_user(), oracle_password(), oracle_connect_string())
        .expect("oracle connect failed - is Oracle 23ai running on 127.0.0.1:1521?")
}

static TABLE_COUNTER: AtomicU64 = AtomicU64::new(0);

/// 生成唯一的临时表名（Oracle 标识符 ≤ 30 字节以兼容旧版本；23ai 支持 128 字节但保持短名）。
fn unique_table(prefix: &str) -> String {
    let pid = std::process::id();
    let nanos = std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .unwrap_or_default()
        .as_nanos();
    let counter = TABLE_COUNTER.fetch_add(1, Ordering::Relaxed);
    // 全部小写避免 Oracle 自动大写化导致 quote 不一致
    format!(
        "t_{}_{}_{}",
        prefix,
        pid % 1000,
        (nanos % 100000) as u64 * 1000 + counter
    )
}

/// 使用 OracleDialect 生成 CREATE TABLE 并执行
fn create_test_table(conn: &OracleConn, table: &str) {
    let dialect = get_dialect(DbType::Oracle).expect("oracle dialect");
    let columns = vec![
        ColumnDef {
            name: "id".to_string(),
            sql_type: "NUMBER".to_string(),
            nullable: false,
            default: None,
            auto_increment: true,
            primary_key: true,
        },
        ColumnDef {
            name: "name".to_string(),
            sql_type: "VARCHAR2(255)".to_string(),
            nullable: false,
            default: None,
            auto_increment: false,
            primary_key: false,
        },
        ColumnDef {
            name: "value".to_string(),
            sql_type: "NUMBER".to_string(),
            nullable: true,
            default: None,
            auto_increment: false,
            primary_key: false,
        },
        ColumnDef {
            name: "data".to_string(),
            sql_type: "VARCHAR2(255)".to_string(),
            nullable: true,
            default: None,
            auto_increment: false,
            primary_key: false,
        },
    ];
    let sql = dialect.build_create_table(table, &columns);
    conn.execute(&sql, &[]).expect("create table");
}

fn drop_table_if_exists(conn: &OracleConn, table: &str) {
    let dialect = get_dialect(DbType::Oracle).expect("oracle dialect");
    // Oracle 23ai 支持 DROP TABLE IF EXISTS
    let sql = dialect.build_drop_table(table, true);
    let _ = conn.execute(&sql, &[]);
}

#[test]
fn test_oracle_dialect_quote_and_escape() {
    let dialect = get_dialect(DbType::Oracle).expect("oracle dialect");
    // quote
    assert_eq!(dialect.quote("user"), "\"user\"");
    assert_eq!(dialect.quote("with\"quote"), "\"with\"\"quote\"");
    // escape_string
    assert_eq!(dialect.escape_string("it's"), "it''s");
    // supports_returning
    assert!(dialect.supports_returning());
}

#[test]
fn test_oracle_dialect_pagination_syntax() {
    let dialect = get_dialect(DbType::Oracle).expect("oracle dialect");
    // Oracle 12c+ 使用 OFFSET ... FETCH NEXT 语法
    // build_pagination(sql, page, limit)：page=1 表示第一页（offset=0）
    let sql = dialect.build_pagination("SELECT * FROM t", 3, 10);
    assert!(sql.contains("OFFSET 20 ROWS"), "sql = {sql}");
    assert!(sql.contains("FETCH NEXT 10 ROWS ONLY"), "sql = {sql}");
}

#[test]
fn test_oracle_dialect_type_mapping() {
    let dialect = get_dialect(DbType::Oracle).expect("oracle dialect");
    // auto_increment keyword - Oracle 12c+ 使用 IDENTITY 列
    assert_eq!(
        dialect.auto_increment_keyword(),
        "GENERATED BY DEFAULT AS IDENTITY"
    );
    // supports_if_exists / supports_if_not_exists - Oracle 23ai 支持
    assert!(dialect.supports_if_exists());
    assert!(dialect.supports_if_not_exists());
}

#[test]
fn test_oracle_value_string_escape() {
    let v_str = Value::String("O'Brien".to_string());
    let dialect = get_dialect(DbType::Oracle).expect("oracle dialect");
    let escaped = dialect.escape_string(v_str.as_str().unwrap());
    assert_eq!(escaped, "O''Brien");
}

#[test]
#[ignore = "需要 Oracle 23ai 运行于 127.0.0.1:1521（设置 SZ_ORM_ORACLE_* 环境变量覆盖）"]
fn test_oracle_create_insert_select() {
    let conn = open_conn();
    let table = unique_table("t1");
    drop_table_if_exists(&conn, &table);
    create_test_table(&conn, &table);

    let dialect = get_dialect(DbType::Oracle).unwrap();
    // 插入 3 条记录（Oracle 使用 :1, :2, :3 占位符；列名用 quote() 包裹保持小写）
    let sql = format!(
        "INSERT INTO {} ({}, {}, {}) VALUES (:1, :2, :3)",
        dialect.quote(&table),
        dialect.quote("name"),
        dialect.quote("value"),
        dialect.quote("data"),
    );
    conn.execute(&sql, &[&"alice", &100i64, &"data1"])
        .expect("insert 1");
    conn.execute(&sql, &[&"bob", &200i64, &"data2"])
        .expect("insert 2");
    conn.execute(&sql, &[&"carol", &300i64, &"data3"])
        .expect("insert 3");
    conn.commit().expect("commit");

    // SELECT 全部（OracleDialect pagination 语法）
    let select_sql = dialect.build_pagination(
        &format!(
            "SELECT {}, {}, {} FROM {} ORDER BY {}",
            dialect.quote("id"),
            dialect.quote("name"),
            dialect.quote("value"),
            dialect.quote(&table),
            dialect.quote("id"),
        ),
        1,
        10,
    );
    let rows: Vec<(i64, String, i64)> = conn
        .query_as::<(i64, String, i64)>(&select_sql, &[])
        .expect("query")
        .filter_map(|r| r.ok())
        .collect();
    assert_eq!(rows.len(), 3);
    assert_eq!(rows[0].1, "alice");
    assert_eq!(rows[2].1, "carol");

    // 验证总数
    let count_sql = format!("SELECT COUNT(*) FROM {}", dialect.quote(&table));
    let count: i64 = conn.query_row_as::<i64>(&count_sql, &[]).expect("count");
    assert_eq!(count, 3);

    // 清理
    drop_table_if_exists(&conn, &table);
}

#[test]
#[ignore = "需要 Oracle 23ai 运行于 127.0.0.1:1521（设置 SZ_ORM_ORACLE_* 环境变量覆盖）"]
fn test_oracle_bulk_insert_100k() {
    let conn = open_conn();
    let table = unique_table("tbulk");
    drop_table_if_exists(&conn, &table);
    create_test_table(&conn, &table);

    let dialect = get_dialect(DbType::Oracle).unwrap();
    let total: usize = 100_000;
    let start = Instant::now();

    // Oracle 批量插入：事务 + prepared statement 复用；列名用 quote() 包裹
    let sql = format!(
        "INSERT INTO {} ({}, {}, {}) VALUES (:1, :2, :3)",
        dialect.quote(&table),
        dialect.quote("name"),
        dialect.quote("value"),
        dialect.quote("data"),
    );
    let mut stmt = conn.statement(&sql).build().expect("build statement");
    for i in 0..total {
        stmt.execute(&[
            &format!("user_{}", i),
            &(i as i64),
            &format!("data_{}", i % 1000),
        ])
        .expect("insert");
    }
    conn.commit().expect("commit");
    let elapsed = start.elapsed();
    println!(
        "oracle bulk insert {} rows in {:?} ({:.0} rows/s)",
        total,
        elapsed,
        total as f64 / elapsed.as_secs_f64()
    );

    // 验证总数
    let count_sql = format!("SELECT COUNT(*) FROM {}", dialect.quote(&table));
    let count: i64 = conn.query_row_as::<i64>(&count_sql, &[]).expect("count");
    assert_eq!(count as usize, total);

    // 验证末尾数据
    let last_sql = format!(
        "SELECT {} FROM (SELECT {} FROM {} ORDER BY {} DESC) WHERE ROWNUM = 1",
        dialect.quote("name"),
        dialect.quote("name"),
        dialect.quote(&table),
        dialect.quote("id"),
    );
    let last_name: String = conn
        .query_row_as::<String>(&last_sql, &[])
        .expect("last name");
    assert_eq!(last_name, format!("user_{}", total - 1));

    // 清理
    drop_table_if_exists(&conn, &table);
}

#[test]
#[ignore = "需要 Oracle 23ai 运行于 127.0.0.1:1521（设置 SZ_ORM_ORACLE_* 环境变量覆盖）"]
fn test_oracle_transaction_rollback() {
    let conn = open_conn();
    let table = unique_table("ttx");
    drop_table_if_exists(&conn, &table);
    create_test_table(&conn, &table);

    let dialect = get_dialect(DbType::Oracle).unwrap();
    let sql = format!(
        "INSERT INTO {} ({}, {}, {}) VALUES (:1, :2, :3)",
        dialect.quote(&table),
        dialect.quote("name"),
        dialect.quote("value"),
        dialect.quote("data"),
    );
    conn.execute(&sql, &[&"alice", &100i64, &"data1"])
        .expect("insert");
    conn.commit().expect("commit 1");

    // 开始事务，插入数据但不提交，回滚
    conn.execute(&sql, &[&"bob", &200i64, &"data2"])
        .expect("insert 2");
    conn.rollback().expect("rollback");

    // 只应该看到 alice
    let count_sql = format!("SELECT COUNT(*) FROM {}", dialect.quote(&table));
    let count: i64 = conn.query_row_as::<i64>(&count_sql, &[]).expect("count");
    assert_eq!(count, 1);

    // 清理
    drop_table_if_exists(&conn, &table);
}