tdsql 0.9.0

//! Integration tests against a live SQL Server.
//!
//! These are `#[ignore]`d by default. Start a server and run them with:
//!
//! ```text
//! docker run -e ACCEPT_EULA=Y -e SA_PASSWORD='YourStrong!Passw0rd' \
//!     -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest
//! cargo test -- --ignored
//! ```

use chrono::{DateTime, NaiveDate, NaiveDateTime, NaiveTime, Timelike, Utc};
use rust_decimal::Decimal;
use uuid::Uuid;

use tdsql::{Client, Command, Config, DataValue, Error, SqlType, ToSql};

fn test_config() -> Config {
    Config::new()
        .host("localhost")
        .port(1433)
        .database("master")
        .auth("sa", "YourStrong!Passw0rd")
        .trust_cert()
}

/// One connection, reused for every statement in a test.
async fn connect() -> Client {
    Client::connect(&test_config())
        .await
        .expect("failed to connect; is SQL Server running on localhost:1433?")
}

/// DDL runs through the ordinary client now, rather than a hand-rolled driver.
///
/// `CREATE PROCEDURE` and friends must be the first statement of their own
/// batch, so they go through `batch` rather than the parameterised path.
async fn ddl(client: &mut Client, sql: &str) {
    client.batch(sql).await.unwrap();
}

#[tokio::test]
#[ignore]
async fn basic_query() {
    let mut client = connect().await;
    let rows = client.query("SELECT 1 AS value", &[]).await.unwrap();
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0].get::<i32, _>("value"), 1);
}

#[tokio::test]
#[ignore]
async fn bit_query() {
    let mut client = connect().await;
    let row = client
        .query_one("SELECT CAST(1 AS bit) AS value", &[])
        .await
        .unwrap();
    assert!(row.get::<bool, _>("value"));
}

#[tokio::test]
#[ignore]
async fn positional_params() {
    let mut client = connect().await;
    let row = client
        .query_one("SELECT @P1 AS value, @P2 AS flag", &[&7i32, &true])
        .await
        .unwrap();
    assert_eq!(row.get::<i32, _>("value"), 7);
    assert!(row.get::<bool, _>("flag"));
}

#[tokio::test]
#[ignore]
async fn named_params_in_a_text_batch() {
    let mut client = connect().await;
    let cmd = Command::query("SELECT @id AS id, @flag AS flag")
        .param("id", 7)
        .param("flag", false);

    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds[0][0].get::<i32, _>("id"), 7);
    assert!(!ds[0][0].get::<bool, _>("flag"));
}

#[tokio::test]
#[ignore]
async fn named_params_bind_by_name_whatever_their_order_or_case() {
    let mut client = connect().await;
    let cmd = Command::query("SELECT @P1 AS a, @Name AS n, @P2 AS b")
        .param("P2", 20)
        .param("name", "y")
        .param("P1", 10);

    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds[0][0].get::<i32, _>("a"), 10);
    assert_eq!(ds[0][0].get::<String, _>("n"), "y");
    assert_eq!(ds[0][0].get::<i32, _>("b"), 20);
}

#[tokio::test]
#[ignore]
async fn non_ascii_sql_survives_parameter_rewriting() {
    let mut client = connect().await;
    let cmd = Command::query("SELECT N'café' AS c, @id AS id").param("id", 1);

    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds[0][0].get::<String, _>("c"), "café");
    assert_eq!(ds[0][0].get::<i32, _>("id"), 1);
}

#[tokio::test]
#[ignore]
async fn stored_procedure_without_params() {
    let mut client = connect().await;
    ddl(
        &mut client,
        "IF OBJECT_ID('sp_no_params', 'P') IS NOT NULL DROP PROCEDURE sp_no_params",
    )
    .await;
    ddl(
        &mut client,
        "CREATE PROCEDURE sp_no_params AS BEGIN SELECT 2 AS value; END",
    )
    .await;

    let ds = client
        .query_dataset(&Command::stored_procedure("sp_no_params"))
        .await
        .unwrap();
    assert_eq!(ds[0][0].get::<i32, _>("value"), 2);
}

#[tokio::test]
#[ignore]
async fn stored_procedure_with_named_params() {
    let mut client = connect().await;
    ddl(
        &mut client,
        "IF OBJECT_ID('sp_with_param', 'P') IS NOT NULL DROP PROCEDURE sp_with_param",
    )
    .await;
    ddl(
        &mut client,
        "CREATE PROCEDURE sp_with_param @val INT AS BEGIN SELECT @val AS value; END",
    )
    .await;

    let cmd = Command::stored_procedure("sp_with_param").param("val", 5);
    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds[0][0].get::<i32, _>("value"), 5);
}

#[tokio::test]
#[ignore]
async fn multiple_result_sets_keep_their_order() {
    let mut client = connect().await;
    let cmd = Command::query("SELECT 1 AS a; SELECT 2 AS b; SELECT 3 AS c");

    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds.len(), 3);
    assert_eq!(ds[0][0].get::<i32, _>("a"), 1);
    assert_eq!(ds[1][0].get::<i32, _>("b"), 2);
    assert_eq!(ds[2][0].get::<i32, _>("c"), 3);
    assert_eq!(ds.table_named("table1").unwrap().len(), 1);
}

#[tokio::test]
#[ignore]
async fn query_one_rejects_zero_and_many_rows() {
    let mut client = connect().await;

    let err = client
        .query_one("SELECT 1 AS v WHERE 1 = 0", &[])
        .await
        .unwrap_err();
    assert!(
        matches!(err, Error::UnexpectedRowCount { found: 0 }),
        "{err}"
    );

    let err = client
        .query_one("SELECT 1 AS v UNION ALL SELECT 2", &[])
        .await
        .unwrap_err();
    assert!(
        matches!(err, Error::UnexpectedRowCount { found: 2 }),
        "{err}"
    );
}

#[tokio::test]
#[ignore]
async fn query_opt_handles_zero_and_one_row() {
    let mut client = connect().await;

    let none = client
        .query_opt("SELECT 1 AS v WHERE 1 = 0", &[])
        .await
        .unwrap();
    assert!(none.is_none());

    let some = client.query_opt("SELECT 1 AS v", &[]).await.unwrap();
    assert_eq!(some.unwrap().get::<i32, _>("v"), 1);
}

#[tokio::test]
#[ignore]
async fn query_scalar_reads_the_first_column() {
    let mut client = connect().await;
    let n: i32 = client.query_scalar("SELECT 42", &[]).await.unwrap();
    assert_eq!(n, 42);

    let s: String = client.query_scalar("SELECT N'hello'", &[]).await.unwrap();
    assert_eq!(s, "hello");
}

#[tokio::test]
#[ignore]
async fn null_round_trips() {
    let mut client = connect().await;

    // Reading a NULL.
    let row = client
        .query_one("SELECT CAST(NULL AS int) AS v", &[])
        .await
        .unwrap();
    assert!(row["v"].is_null());
    assert_eq!(row.try_get::<Option<i32>, _>("v").unwrap(), None);
    assert!(row.try_get::<i32, _>("v").is_err());

    // Binding a NULL.
    let row = client
        .query_one("SELECT @P1 AS v", &[&None::<i32>])
        .await
        .unwrap();
    assert!(row["v"].is_null());
}

/// A column of each type, and the `SqlType` a NULL for it binds as.
const NULL_TARGETS: &[(&str, SqlType)] = &[
    ("tinyint", SqlType::TinyInt),
    ("smallint", SqlType::SmallInt),
    ("int", SqlType::Int),
    ("bigint", SqlType::BigInt),
    ("bit", SqlType::Bit),
    ("decimal(10, 2)", SqlType::Decimal),
    ("numeric(38, 10)", SqlType::Numeric),
    ("money", SqlType::Money),
    ("smallmoney", SqlType::SmallMoney),
    ("real", SqlType::Real),
    ("float", SqlType::Float),
    ("char(5)", SqlType::Char),
    ("varchar(10)", SqlType::VarChar),
    ("varchar(max)", SqlType::VarChar),
    ("nchar(5)", SqlType::NChar),
    ("nvarchar(10)", SqlType::NVarChar),
    ("binary(4)", SqlType::Binary),
    ("varbinary(10)", SqlType::VarBinary),
    ("varbinary(max)", SqlType::VarBinary),
    ("uniqueidentifier", SqlType::Guid),
    ("date", SqlType::Date),
    ("time", SqlType::Time),
    ("datetime", SqlType::DateTime),
    ("smalldatetime", SqlType::SmallDateTime),
    ("datetime2", SqlType::DateTime2),
    ("datetimeoffset", SqlType::DateTimeOffset),
];

#[tokio::test]
#[ignore]
async fn a_typed_null_goes_into_and_compares_with_a_column_of_its_type() {
    let mut client = connect().await;
    // `xml` and the legacy LOB types can be stored but not compared.
    let store_only = [
        ("xml", SqlType::Xml),
        ("text", SqlType::Text),
        ("ntext", SqlType::NText),
        ("image", SqlType::Image),
    ];
    for &(column, ty) in NULL_TARGETS.iter().chain(&store_only) {
        let null = DataValue::TypedNull(ty);
        ddl(&mut client, &format!("CREATE TABLE #n (c {column})")).await;
        client
            .execute("INSERT INTO #n (c) VALUES (@P1)", &[&null])
            .await
            .unwrap_or_else(|e| panic!("inserting a {ty:?} NULL into {column}: {e}"));
        let nulls: i32 = client
            .query_scalar("SELECT COUNT(*) FROM #n WHERE c IS NULL", &[])
            .await
            .unwrap();
        assert_eq!(nulls, 1, "{column}");
        if !store_only.contains(&(column, ty)) {
            let matches: i32 = client
                .query_scalar("SELECT COUNT(*) FROM #n WHERE c = @P1", &[&null])
                .await
                .unwrap_or_else(|e| panic!("comparing {column} with a {ty:?} NULL: {e}"));
            assert_eq!(matches, 0, "{column}");
        }
        ddl(&mut client, "DROP TABLE #n").await;
    }
}

#[tokio::test]
#[ignore]
async fn an_untyped_null_goes_into_any_column_but_a_binary_one() {
    let mut client = connect().await;
    let lobs = [
        ("xml", SqlType::Xml),
        ("text", SqlType::Text),
        ("ntext", SqlType::NText),
        ("image", SqlType::Image),
    ];
    for &(column, ty) in NULL_TARGETS.iter().chain(&lobs) {
        ddl(&mut client, &format!("CREATE TABLE #n (c {column})")).await;
        let result = client
            .execute("INSERT INTO #n (c) VALUES (@P1)", &[&DataValue::Null])
            .await;
        let binary = matches!(ty, SqlType::Binary | SqlType::VarBinary | SqlType::Image);
        match result {
            Ok(_) => assert!(!binary, "{column} took an nvarchar NULL"),
            // 257 "Implicit conversion ... is not allowed", or 206 "Operand
            // type clash" for image.
            Err(e) => assert!(
                binary && matches!(e.code(), Some(206 | 257)),
                "{column}: {e}"
            ),
        }
        ddl(&mut client, "DROP TABLE #n").await;
    }
}

#[tokio::test]
#[ignore]
async fn none_goes_into_and_compares_with_a_column_of_its_type() {
    let mut client = connect().await;
    let nones: [(&str, &dyn ToSql); 17] = [
        ("tinyint", &None::<u8>),
        ("smallint", &None::<i16>),
        ("int", &None::<i32>),
        ("bigint", &None::<i64>),
        ("real", &None::<f32>),
        ("float", &None::<f64>),
        ("bit", &None::<bool>),
        ("decimal(10, 2)", &None::<Decimal>),
        ("nvarchar(10)", &None::<String>),
        ("varchar(10)", &None::<&str>),
        ("varbinary(10)", &None::<Vec<u8>>),
        ("varbinary(max)", &None::<&[u8]>),
        ("uniqueidentifier", &None::<Uuid>),
        ("date", &None::<NaiveDate>),
        ("time", &None::<NaiveTime>),
        ("datetime2", &None::<NaiveDateTime>),
        ("datetimeoffset", &None::<DateTime<Utc>>),
    ];
    for (column, none) in nones {
        ddl(&mut client, &format!("CREATE TABLE #n (c {column})")).await;
        client
            .execute("INSERT INTO #n (c) VALUES (@P1)", &[none])
            .await
            .unwrap_or_else(|e| panic!("inserting None into {column}: {e}"));
        let row = client
            .query_one(
                "SELECT COUNT(*) AS n, SUM(CASE WHEN c = @P1 THEN 1 ELSE 0 END) AS eq \
                 FROM #n WHERE c IS NULL",
                &[none],
            )
            .await
            .unwrap_or_else(|e| panic!("comparing {column} with None: {e}"));
        assert_eq!(row.get::<i32, _>("n"), 1, "{column}");
        assert_eq!(row.get::<i32, _>("eq"), 0, "{column}");
        ddl(&mut client, "DROP TABLE #n").await;
    }
}

#[tokio::test]
#[ignore]
async fn an_optional_string_filter_leaves_other_rows_alone() {
    // `int` outranks `varchar`, so an `int` NULL here would convert every `s`
    // to int and fail on 'abc'.
    let mut client = connect().await;
    ddl(
        &mut client,
        "CREATE TABLE #f (s varchar(10)); INSERT INTO #f VALUES ('abc'), ('def')",
    )
    .await;
    let n: i32 = client
        .query_scalar(
            "SELECT COUNT(*) FROM #f WHERE s = COALESCE(@P1, s)",
            &[&None::<String>],
        )
        .await
        .unwrap();
    assert_eq!(n, 2);
}

#[tokio::test]
#[ignore]
async fn missing_column_is_an_error_not_a_panic() {
    let mut client = connect().await;
    let row = client.query_one("SELECT 1 AS v", &[]).await.unwrap();

    let err = row.try_get::<i32, _>("nope").unwrap_err();
    assert!(
        matches!(err, Error::ColumnNotFound(ref c) if c == "nope"),
        "{err}"
    );
}

#[tokio::test]
#[ignore]
async fn a_conversion_failure_fails_the_request_and_leaves_the_connection_clean() {
    let mut client = connect().await;
    ddl(
        &mut client,
        "CREATE PROCEDURE #tdsql_too_big AS \
         BEGIN TRAN; SELECT POWER(CAST(10 AS DECIMAL(38, 0)), 30) AS d; COMMIT",
    )
    .await;

    // 10^30 fits DECIMAL(38,0) on the server but not `rust_decimal`. Left
    // unread, the COMMIT after it would leave the connection on a transaction
    // the server had ended, and the server would reject every later request.
    let err = client
        .batch("BEGIN TRAN; SELECT POWER(CAST(10 AS DECIMAL(38, 0)), 30) AS d; COMMIT")
        .await
        .unwrap_err();
    assert!(matches!(err, Error::Decimal { .. }), "got {err}");
    let err = client
        .query_dataset(&Command::stored_procedure("#tdsql_too_big"))
        .await
        .unwrap_err();
    assert!(matches!(err, Error::Decimal { .. }), "got {err}");

    let depth: i32 = client
        .query_scalar("SELECT @@TRANCOUNT", &[])
        .await
        .unwrap();
    assert_eq!(depth, 0);
}

#[tokio::test]
#[ignore]
async fn all_types_query() {
    let mut client = connect().await;
    let ds = client
        .query_dataset(&Command::query(
            "SELECT \
                CAST(1 AS tinyint) AS tiny_col, \
                CAST(2 AS smallint) AS small_col, \
                CAST(3 AS int) AS int_col, \
                CAST(4 AS bigint) AS big_col, \
                CAST(5.5 AS float) AS float_col, \
                CAST(123.45 AS numeric(5,2)) AS decimal_col, \
                CAST(1 AS bit) AS bit_col, \
                CAST(N'text' AS nvarchar(10)) AS text_col, \
                CAST(0x010203 AS varbinary(3)) AS binary_col, \
                CAST('6F9619FF-8B86-D011-B42D-00CF4FC964FF' AS uniqueidentifier) AS guid_col, \
                CAST('2023-01-01' AS date) AS date_col, \
                CAST('12:34:56' AS time(0)) AS time_col, \
                CAST('2023-01-01T01:02:03' AS datetime2) AS datetime_col, \
                CAST('2023-01-01T01:02:03+02:00' AS datetimeoffset) AS dto_col, \
                CAST(NULL AS int) AS null_col",
        ))
        .await
        .unwrap();

    let row = &ds[0][0];

    // Typed extraction.
    assert_eq!(row.get::<u8, _>("tiny_col"), 1);
    assert_eq!(row.get::<i16, _>("small_col"), 2);
    assert_eq!(row.get::<i32, _>("int_col"), 3);
    assert_eq!(row.get::<i64, _>("big_col"), 4);
    assert_eq!(row.get::<f64, _>("float_col"), 5.5);
    assert_eq!(row.get::<Decimal, _>("decimal_col"), Decimal::new(12345, 2));
    assert!(row.get::<bool, _>("bit_col"));
    assert_eq!(row.get::<String, _>("text_col"), "text");
    assert_eq!(row.get::<Vec<u8>, _>("binary_col"), vec![1, 2, 3]);
    assert_eq!(
        row.get::<Uuid, _>("guid_col"),
        Uuid::parse_str("6F9619FF-8B86-D011-B42D-00CF4FC964FF").unwrap()
    );
    assert_eq!(
        row.get::<NaiveDate, _>("date_col"),
        NaiveDate::from_ymd_opt(2023, 1, 1).unwrap()
    );
    assert_eq!(
        row.get::<NaiveTime, _>("time_col"),
        NaiveTime::from_hms_opt(12, 34, 56).unwrap()
    );
    assert_eq!(
        row.get::<chrono::NaiveDateTime, _>("datetime_col"),
        NaiveDate::from_ymd_opt(2023, 1, 1)
            .unwrap()
            .and_hms_opt(1, 2, 3)
            .unwrap()
    );
    assert_eq!(
        row.get::<DateTime<chrono::FixedOffset>, _>("dto_col"),
        DateTime::parse_from_rfc3339("2023-01-01T01:02:03+02:00").unwrap()
    );
    assert!(matches!(row["null_col"], DataValue::Null));

    // Integers widen on read.
    assert_eq!(row.get::<i64, _>("tiny_col"), 1);
    assert_eq!(row.get::<i32, _>("small_col"), 2);

    // Column types are a real enum now, not the driver's Debug output.
    let cols = ds[0].columns();
    assert_eq!(cols[0].sql_type(), SqlType::TinyInt);
    assert_eq!(cols[1].sql_type(), SqlType::SmallInt);
    assert_eq!(cols[2].sql_type(), SqlType::Int);
    assert_eq!(cols[3].sql_type(), SqlType::BigInt);
    assert_eq!(cols[4].sql_type(), SqlType::Float);
    assert_eq!(cols[5].sql_type(), SqlType::Numeric);
    assert_eq!(cols[6].sql_type(), SqlType::BitN);
    assert_eq!(cols[7].sql_type(), SqlType::NVarChar);
    assert_eq!(cols[8].sql_type(), SqlType::VarBinary);
    assert_eq!(cols[9].sql_type(), SqlType::Guid);
    assert_eq!(cols[10].sql_type(), SqlType::Date);
    assert_eq!(cols[11].sql_type(), SqlType::Time);
    assert_eq!(cols[12].sql_type(), SqlType::DateTime2);
    assert_eq!(cols[13].sql_type(), SqlType::DateTimeOffset);
    assert_eq!(cols[14].sql_type(), SqlType::Int);

    // Column order is preserved.
    assert_eq!(cols[0].name(), "tiny_col");
    assert_eq!(cols[14].name(), "null_col");
}

#[tokio::test]
#[ignore]
async fn duplicate_column_names_stay_addressable() {
    let mut client = connect().await;
    let row = client
        .query_one("SELECT 1 AS a, 2 AS a", &[])
        .await
        .unwrap();

    assert_eq!(row.len(), 2);
    assert_eq!(row.get::<i32, _>(0), 1);
    assert_eq!(row.get::<i32, _>(1), 2);
}

#[tokio::test]
#[ignore]
async fn execute_reports_rows_affected() {
    let mut client = connect().await;
    ddl(
        &mut client,
        "IF OBJECT_ID('dbo.tdsql_exec_test', 'U') IS NOT NULL DROP TABLE dbo.tdsql_exec_test",
    )
    .await;
    ddl(
        &mut client,
        "CREATE TABLE dbo.tdsql_exec_test (id INT PRIMARY KEY, val INT NOT NULL)",
    )
    .await;

    let affected = client
        .execute(
            "INSERT INTO dbo.tdsql_exec_test (id, val) VALUES (1, 10), (2, 20)",
            &[],
        )
        .await
        .unwrap();
    assert_eq!(affected, 2);

    let affected = client
        .execute(
            "UPDATE dbo.tdsql_exec_test SET val = val + 1 WHERE id = @P1",
            &[&1i32],
        )
        .await
        .unwrap();
    assert_eq!(affected, 1);

    // The same connection keeps working across every statement above.
    let total: i32 = client
        .query_scalar("SELECT COUNT(*) FROM dbo.tdsql_exec_test", &[])
        .await
        .unwrap();
    assert_eq!(total, 2);

    ddl(&mut client, "DROP TABLE dbo.tdsql_exec_test").await;
}

#[tokio::test]
#[ignore]
async fn execute_command_on_a_stored_procedure() {
    let mut client = connect().await;
    ddl(
        &mut client,
        "IF OBJECT_ID('dbo.tdsql_sp_rows', 'U') IS NOT NULL DROP TABLE dbo.tdsql_sp_rows",
    )
    .await;
    ddl(
        &mut client,
        "CREATE TABLE dbo.tdsql_sp_rows (id INT PRIMARY KEY)",
    )
    .await;
    ddl(
        &mut client,
        "IF OBJECT_ID('dbo.sp_tdsql_insert', 'P') IS NOT NULL DROP PROCEDURE dbo.sp_tdsql_insert",
    )
    .await;
    ddl(
        &mut client,
        "CREATE PROCEDURE dbo.sp_tdsql_insert @id INT AS BEGIN \
         INSERT INTO dbo.tdsql_sp_rows (id) VALUES (@id); END",
    )
    .await;

    let cmd = Command::stored_procedure("dbo.sp_tdsql_insert").param("id", 1);
    let affected = client.execute_command(&cmd).await.unwrap();
    assert_eq!(affected, 1);

    ddl(&mut client, "DROP PROCEDURE dbo.sp_tdsql_insert").await;
    ddl(&mut client, "DROP TABLE dbo.tdsql_sp_rows").await;
}

#[tokio::test]
#[ignore]
async fn scalar_from_a_stored_procedure() {
    let mut client = connect().await;
    ddl(
        &mut client,
        "IF OBJECT_ID('dbo.sp_scalar_test', 'P') IS NOT NULL DROP PROCEDURE dbo.sp_scalar_test",
    )
    .await;
    ddl(
        &mut client,
        "CREATE PROCEDURE dbo.sp_scalar_test @x INT AS BEGIN SELECT @x + 1 AS result; END",
    )
    .await;

    let cmd = Command::stored_procedure("dbo.sp_scalar_test").param("x", 5);
    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds[0][0].get::<i32, _>("result"), 6);
}

#[tokio::test]
#[ignore]
async fn connects_from_a_connection_string() {
    let mut client = Client::connect_str(
        "Server=tcp:localhost,1433;Database=master;User Id=sa;\
         Password=YourStrong!Passw0rd;TrustServerCertificate=true",
    )
    .await
    .unwrap();

    let n: i32 = client.query_scalar("SELECT 1", &[]).await.unwrap();
    assert_eq!(n, 1);
}

#[tokio::test]
#[ignore]
async fn wide_decimals_read_when_their_trailing_zeros_make_them_fit() {
    let mut client = connect().await;
    let row = client
        .query_one(
            "SELECT CAST(100000000000 AS decimal(38, 18)) AS amount, \
                    CAST(0.5 AS decimal(38, 30)) AS ratio, \
                    CAST(0 AS decimal(38, 38)) AS frac",
            &[],
        )
        .await
        .unwrap();
    assert_eq!(
        row.get::<Decimal, _>("amount"),
        Decimal::new(100_000_000_000, 0)
    );
    assert_eq!(row.get::<Decimal, _>("ratio"), Decimal::new(5, 1));
    assert_eq!(row.get::<Decimal, _>("frac"), Decimal::ZERO);
}

#[tokio::test]
#[ignore]
async fn the_application_name_is_tdsql_unless_set() {
    let mut client = connect().await;
    let name: String = client.query_scalar("SELECT APP_NAME()", &[]).await.unwrap();
    assert_eq!(name, "tdsql");

    let mut named = Client::connect(&test_config().application_name("billing"))
        .await
        .unwrap();
    let name: String = named.query_scalar("SELECT APP_NAME()", &[]).await.unwrap();
    assert_eq!(name, "billing");
}

#[tokio::test]
#[ignore]
async fn a_set_option_run_through_execute_sticks() {
    // Regression: with no parameters, `execute` still went through
    // `sp_executesql`, which undid any SET option on return.
    let mut client = connect().await;
    client.execute("SET LOCK_TIMEOUT 1234", &[]).await.unwrap();
    let timeout: i32 = client
        .query_scalar("SELECT @@LOCK_TIMEOUT", &[])
        .await
        .unwrap();
    assert_eq!(timeout, 1234);

    // With a parameter the call is an sp_executesql scope, as SqlClient's is.
    client
        .execute("SET LOCK_TIMEOUT 99; SELECT @P1", &[&1])
        .await
        .unwrap();
    let timeout: i32 = client
        .query_scalar("SELECT @@LOCK_TIMEOUT", &[])
        .await
        .unwrap();
    assert_eq!(timeout, 1234);
}

#[tokio::test]
#[ignore]
async fn begin_and_commit_run_through_execute_report_what_happened() {
    // Regression: both took effect but were reported as error 266.
    let mut client = connect().await;
    ddl(&mut client, "CREATE TABLE #tdsql_exec_tran (id INT)").await;
    client.execute("BEGIN TRAN", &[]).await.unwrap();
    let depth: i32 = client
        .query_scalar("SELECT @@TRANCOUNT", &[])
        .await
        .unwrap();
    assert_eq!(depth, 1);
    let n = client
        .execute("INSERT INTO #tdsql_exec_tran (id) VALUES (1)", &[])
        .await
        .unwrap();
    assert_eq!(n, 1);
    client.execute("COMMIT", &[]).await.unwrap();
    let depth: i32 = client
        .query_scalar("SELECT @@TRANCOUNT", &[])
        .await
        .unwrap();
    assert_eq!(depth, 0);
}

#[tokio::test]
#[ignore]
async fn a_parameterless_query_keeps_its_session_changes() {
    let mut client = connect().await;
    let rows = client.query("SET DATEFORMAT dmy", &[]).await.unwrap();
    assert!(rows.is_empty());
    let format: String = client
        .query_scalar(
            "SELECT date_format FROM sys.dm_exec_sessions WHERE session_id = @@SPID",
            &[],
        )
        .await
        .unwrap();
    assert_eq!(format, "dmy");
}

#[tokio::test]
#[ignore]
async fn datetime_parameters_can_be_sent_as_datetime_for_datetime_columns() {
    use tdsql::DateTimeParameterType;

    let mut client = connect().await;
    ddl(
        &mut client,
        "IF OBJECT_ID('dbo.tdsql_dt_legacy', 'U') IS NOT NULL DROP TABLE dbo.tdsql_dt_legacy;
         CREATE TABLE dbo.tdsql_dt_legacy (id INT PRIMARY KEY, created DATETIME NOT NULL, note NVARCHAR(10));
         INSERT INTO dbo.tdsql_dt_legacy (id, created) VALUES
           (1, '2024-05-01 10:00:00.000'), (2, '2024-05-01 10:00:00.003'),
           (3, '2024-05-01 10:00:00.007'), (4, '2024-05-01 10:00:00.997'),
           (5, '2024-05-01 10:00:00.123')",
    )
    .await;

    // The default, datetime2: the column widens exactly to .0033333, so a
    // value read back does not match its own row (compat level >= 130).
    let created: NaiveDateTime = client
        .query_scalar("SELECT created FROM dbo.tdsql_dt_legacy WHERE id = 2", &[])
        .await
        .unwrap();
    let n: i32 = client
        .query_scalar(
            "SELECT COUNT(*) FROM dbo.tdsql_dt_legacy WHERE created = @P1",
            &[&created],
        )
        .await
        .unwrap();
    assert_eq!(n, 0, "the trap this setting exists for has gone away");

    let mut legacy =
        Client::connect(&test_config().datetime_parameter_type(DateTimeParameterType::DateTime))
            .await
            .unwrap();
    let rows = legacy
        .query(
            "SELECT id, created FROM dbo.tdsql_dt_legacy ORDER BY id",
            &[],
        )
        .await
        .unwrap();
    assert_eq!(rows.len(), 5);
    for row in &rows {
        let id: i32 = row.get("id");
        let created: NaiveDateTime = row.get("created");
        // Positional, through a Command, and in an UPDATE's WHERE clause.
        let n: i32 = legacy
            .query_scalar(
                "SELECT COUNT(*) FROM dbo.tdsql_dt_legacy WHERE created = @P1",
                &[&created],
            )
            .await
            .unwrap();
        assert_eq!(n, 1, "row {id} ({created})");
        let ds = legacy
            .query_dataset(
                &Command::query("SELECT COUNT(*) AS n FROM dbo.tdsql_dt_legacy WHERE created = @c")
                    .param("c", created),
            )
            .await
            .unwrap();
        assert_eq!(ds[0][0].get::<i32, _>("n"), 1, "row {id} via Command");
        let touched = legacy
            .execute(
                "UPDATE dbo.tdsql_dt_legacy SET note = N'seen' WHERE id = @P1 AND created = @P2",
                &[&id, &created],
            )
            .await
            .unwrap();
        assert_eq!(touched, 1, "row {id} UPDATE");
    }

    // The trade: into a datetime2 column such a parameter carries 1/300 s.
    let fine = NaiveDate::from_ymd_opt(2024, 5, 1)
        .unwrap()
        .and_hms_nano_opt(10, 0, 0, 1_234_500)
        .unwrap();
    let stored: NaiveDateTime = legacy
        .query_scalar("SELECT CAST(@P1 AS datetime2(7))", &[&fine])
        .await
        .unwrap();
    assert_eq!(stored, fine.with_nanosecond(0).unwrap());
    let stored: NaiveDateTime = client
        .query_scalar("SELECT CAST(@P1 AS datetime2(7))", &[&fine])
        .await
        .unwrap();
    assert_eq!(stored, fine);

    ddl(&mut client, "DROP TABLE dbo.tdsql_dt_legacy").await;
}

/// The wire type the server saw for a parameter, as `sql_variant` reports it.
async fn param_type(client: &mut Client, value: &dyn ToSql) -> String {
    client
        .query_scalar(
            "SELECT CAST(SQL_VARIANT_PROPERTY(ISNULL(@P1, ''), 'BaseType') AS nvarchar(30))",
            &[value],
        )
        .await
        .unwrap()
}

#[tokio::test]
#[ignore]
async fn varchar_values_are_sent_as_varchar() {
    let mut client = connect().await;

    assert_eq!(param_type(&mut client, &"abc").await, "nvarchar");
    assert_eq!(
        param_type(&mut client, &DataValue::VarChar("abc".into())).await,
        "varchar"
    );

    // Round trip, by position and by name.
    let name = DataValue::VarChar("Väinämöinen".into());
    let back: String = client.query_scalar("SELECT @P1", &[&name]).await.unwrap();
    assert_eq!(back, "Väinämöinen");
    let cmd = Command::query("SELECT @name AS name").param("name", name);
    let ds = client.query_dataset(&cmd).await.unwrap();
    assert_eq!(ds[0][0].get::<String, _>("name"), "Väinämöinen");

    // Over 8000 bytes it goes as varchar(max), whole.
    let long = DataValue::VarChar("x".repeat(8001));
    let len: i64 = client
        .query_scalar("SELECT DATALENGTH(@P1)", &[&long])
        .await
        .unwrap();
    assert_eq!(len, 8001);

    // A character the server's code page lacks is an error before anything
    // is sent, and the connection is still usable. (The test server's
    // collation is SQL_Latin1_General_CP1_CI_AS, code page 1252.)
    let err = client
        .query_scalar::<String>("SELECT @P1", &[&DataValue::VarChar("日本".into())])
        .await
        .unwrap_err();
    assert!(!err.is_connection_lost(), "{err:?}");
    assert!(err.to_string().contains("SELECT @P1"), "{err}");
    let one: i32 = client.query_scalar("SELECT 1", &[]).await.unwrap();
    assert_eq!(one, 1);
}