Invercargill-Sql/src/tests/sqlite-test.vala

using InvercargillSql;
using Sqlite;

/**
 * Comprehensive SQLite integration tests including Element type conversions.
 * 
 * These tests verify that data can be written to and read from SQLite
 * with proper type conversions through the Element interface.
 */
public int main(string[] args) {
    print("=== SQLite Integration Tests ===\n\n");
    
    try {
        // Element type conversion tests
        print("--- Integer Element Tests ---\n");
        test_integer_element_conversions();
        test_integer_element_bool_conversion();
        test_integer_element_datetime_conversion();
        test_integer_element_null_handling();
        
        print("\n--- Real Element Tests ---\n");
        test_real_element_conversions();
        test_real_element_null_handling();
        
        print("\n--- Text Element Tests ---\n");
        test_text_element_conversions();
        test_text_element_bool_parsing();
        test_text_element_null_handling();
        
        print("\n--- Blob Element Tests ---\n");
        test_blob_element_round_trip();
        test_blob_element_null_handling();
        
        print("\n--- Null Element Tests ---\n");
        test_null_element_handling();
        
        print("\n--- Round-trip Conversion Tests ---\n");
        test_datetime_round_trip();
        test_bool_round_trip();
        test_nullable_types_round_trip();
        
        print("\n--- Complex Integration Tests ---\n");
        test_mixed_type_table();
        test_element_assignable_types();
        
        print("\n=== All SQLite integration tests passed! ===\n");
        return 0;
    } catch (Error e) {
        printerr("\n=== Test failed: %s ===\n", e.message);
        return 1;
    }
}

// ============================================================================
// Integer Element Tests
// ============================================================================

void test_integer_element_conversions() throws SqlError {
    print("Test: Integer element conversions... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    // Create table with integer column
    conn.execute("CREATE TABLE test_int (id INTEGER PRIMARY KEY, value INTEGER)");
    
    // Insert various integer values
    conn.create_command("INSERT INTO test_int (value) VALUES (:val)")
        .with_parameter("val", 42)
        .execute_non_query();
    conn.create_command("INSERT INTO test_int (value) VALUES (:val)")
        .with_parameter("val", -123)
        .execute_non_query();
    int64? max_val = int64.MAX;
    conn.create_command("INSERT INTO test_int (value) VALUES (:val)")
        .with_parameter<int64?>("val", max_val)
        .execute_non_query();
    
    // Query back and verify conversions
    var results = conn.create_command("SELECT value FROM test_int ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 3);
    
    // First row: 42
    var elem = array[0].get("value");
    assert(elem != null);
    
    // Test as<int64>()
    int64? val_int64 = null;
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == 42);
    
    // Test as<int>()
    int? val_int = null;
    assert(elem.try_get_as<int?>(out val_int));
    assert(val_int == 42);
    
    // Test as<double>()
    double? val_double = null;
    assert(elem.try_get_as<double?>(out val_double));
    assert(val_double == 42.0);
    
    // Test as<float>()
    float? val_float = null;
    assert(elem.try_get_as<float?>(out val_float));
    assert(val_float >= 41.9f && val_float <= 42.1f);
    
    // Test as<string>()
    string? val_str = null;
    assert(elem.try_get_as<string>(out val_str));
    assert(val_str == "42");
    
    // Second row: -123
    elem = array[1].get("value");
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == -123);
    
    // Third row: int64.MAX
    elem = array[2].get("value");
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == int64.MAX);
    
    print("PASSED\n");
    conn.close();
}

void test_integer_element_bool_conversion() throws SqlError {
    print("Test: Integer to bool conversion... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_bool (id INTEGER PRIMARY KEY, flag INTEGER)");
    
    // Insert 0 (false) and 1 (true) and other non-zero values
    conn.create_command("INSERT INTO test_bool (flag) VALUES (0)").execute_non_query();
    conn.create_command("INSERT INTO test_bool (flag) VALUES (1)").execute_non_query();
    conn.create_command("INSERT INTO test_bool (flag) VALUES (42)").execute_non_query();
    conn.create_command("INSERT INTO test_bool (flag) VALUES (-1)").execute_non_query();
    
    var results = conn.create_command("SELECT flag FROM test_bool ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 4);
    
    // 0 should be false
    bool? val = null;
    assert(array[0].get("flag").try_get_as<bool?>(out val));
    assert(val == false);
    
    // 1 should be true
    assert(array[1].get("flag").try_get_as<bool?>(out val));
    assert(val == true);
    
    // 42 (non-zero) should be true
    assert(array[2].get("flag").try_get_as<bool?>(out val));
    assert(val == true);
    
    // -1 (non-zero) should be true
    assert(array[3].get("flag").try_get_as<bool?>(out val));
    assert(val == true);
    
    print("PASSED\n");
    conn.close();
}

void test_integer_element_datetime_conversion() throws SqlError {
    print("Test: Integer to DateTime conversion... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_datetime (id INTEGER PRIMARY KEY, timestamp INTEGER)");
    
    // Insert a known Unix timestamp (2024-01-15 12:30:45 UTC)
    int64? known_timestamp = 1705324245;
    conn.create_command("INSERT INTO test_datetime (timestamp) VALUES (:ts)")
        .with_parameter<int64?>("ts", known_timestamp)
        .execute_non_query();
    
    var results = conn.create_command("SELECT timestamp FROM test_datetime")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    DateTime? dt = null;
    var elem = array[0].get("timestamp");
    assert(elem.try_get_as<DateTime>(out dt));
    assert(dt != null);
    assert(dt.to_unix() == known_timestamp);
    
    print("PASSED\n");
    conn.close();
}

void test_integer_element_null_handling() throws SqlError {
    print("Test: Integer element null handling... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_null_int (id INTEGER PRIMARY KEY, value INTEGER)");
    
    // Insert a NULL value
    conn.create_command("INSERT INTO test_null_int (value) VALUES (NULL)").execute_non_query();
    // Insert a non-null value for comparison
    conn.create_command("INSERT INTO test_null_int (value) VALUES (42)").execute_non_query();
    
    var results = conn.create_command("SELECT value FROM test_null_int ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 2);
    
    // First row is NULL
    var elem = array[0].get("value");
    assert(elem.is_null() == true);
    
    int64? val = null;
    assert(elem.try_get_as<int64?>(out val) == false);
    assert(val == null);
    
    // Second row is not null
    elem = array[1].get("value");
    assert(elem.is_null() == false);
    assert(elem.try_get_as<int64?>(out val));
    assert(val == 42);
    
    print("PASSED\n");
    conn.close();
}

// ============================================================================
// Real Element Tests
// ============================================================================

void test_real_element_conversions() throws SqlError {
    print("Test: Real element conversions... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_real (id INTEGER PRIMARY KEY, value REAL)");
    
    // Insert various real values
    double? val1 = 3.14159;
    conn.create_command("INSERT INTO test_real (value) VALUES (:val)")
        .with_parameter<double?>("val", val1)
        .execute_non_query();
    double? val2 = -2.71828;
    conn.create_command("INSERT INTO test_real (value) VALUES (:val)")
        .with_parameter<double?>("val", val2)
        .execute_non_query();
    double? val3 = 42.0;
    conn.create_command("INSERT INTO test_real (value) VALUES (:val)")
        .with_parameter<double?>("val", val3)
        .execute_non_query();
    
    var results = conn.create_command("SELECT value FROM test_real ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 3);
    
    // First row: 3.14159
    var elem = array[0].get("value");
    assert(elem != null);
    
    // Test as<double>()
    double? val_double = null;
    assert(elem.try_get_as<double?>(out val_double));
    assert(val_double >= 3.14158 && val_double <= 3.14160);
    
    // Test as<float>()
    float? val_float = null;
    assert(elem.try_get_as<float?>(out val_float));
    assert(val_float >= 3.1415f && val_float <= 3.1417f);
    
    // Test as<int64>() - truncation
    int64? val_int64 = null;
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == 3);
    
    // Test as<int>() - truncation
    int? val_int = null;
    assert(elem.try_get_as<int?>(out val_int));
    assert(val_int == 3);
    
    // Test as<string>()
    string? val_str = null;
    assert(elem.try_get_as<string>(out val_str));
    assert(val_str != null);
    assert(val_str.contains("3.14"));
    
    // Second row: -2.71828 (verify negative truncation)
    elem = array[1].get("value");
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == -2);
    
    // Third row: 42.0 (whole number)
    elem = array[2].get("value");
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == 42);
    
    print("PASSED\n");
    conn.close();
}

void test_real_element_null_handling() throws SqlError {
    print("Test: Real element null handling... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_null_real (id INTEGER PRIMARY KEY, value REAL)");
    
    conn.create_command("INSERT INTO test_null_real (value) VALUES (NULL)").execute_non_query();
    
    var results = conn.create_command("SELECT value FROM test_null_real")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    var elem = array[0].get("value");
    assert(elem.is_null() == true);
    
    double? val = null;
    assert(elem.try_get_as<double?>(out val) == false);
    assert(val == null);
    
    print("PASSED\n");
    conn.close();
}

// ============================================================================
// Text Element Tests
// ============================================================================

void test_text_element_conversions() throws SqlError {
    print("Test: Text element conversions... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_text (id INTEGER PRIMARY KEY, value TEXT)");
    
    // Insert various text values
    conn.create_command("INSERT INTO test_text (value) VALUES ('Hello, World!')").execute_non_query();
    conn.create_command("INSERT INTO test_text (value) VALUES ('42')").execute_non_query();
    conn.create_command("INSERT INTO test_text (value) VALUES ('-123')").execute_non_query();
    conn.create_command("INSERT INTO test_text (value) VALUES ('3.14159')").execute_non_query();
    
    var results = conn.create_command("SELECT value FROM test_text ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 4);
    
    // First row: "Hello, World!"
    var elem = array[0].get("value");
    string? val_str = null;
    assert(elem.try_get_as<string>(out val_str));
    assert(val_str == "Hello, World!");
    
    // Second row: "42" - parse as int
    elem = array[1].get("value");
    int64? val_int64 = null;
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == 42);
    
    int? val_int = null;
    assert(elem.try_get_as<int?>(out val_int));
    assert(val_int == 42);
    
    // Third row: "-123" - parse as negative int
    elem = array[2].get("value");
    assert(elem.try_get_as<int64?>(out val_int64));
    assert(val_int64 == -123);
    
    // Fourth row: "3.14159" - parse as double
    elem = array[3].get("value");
    double? val_double = null;
    assert(elem.try_get_as<double?>(out val_double));
    assert(val_double >= 3.14158 && val_double <= 3.14160);
    
    float? val_float = null;
    assert(elem.try_get_as<float?>(out val_float));
    assert(val_float >= 3.1415f && val_float <= 3.1417f);
    
    print("PASSED\n");
    conn.close();
}

void test_text_element_bool_parsing() throws SqlError {
    print("Test: Text element bool parsing... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_text_bool (id INTEGER PRIMARY KEY, value TEXT)");
    
    // Insert various boolean representations
    conn.create_command("INSERT INTO test_text_bool (value) VALUES ('1')").execute_non_query();
    conn.create_command("INSERT INTO test_text_bool (value) VALUES ('0')").execute_non_query();
    conn.create_command("INSERT INTO test_text_bool (value) VALUES ('true')").execute_non_query();
    conn.create_command("INSERT INTO test_text_bool (value) VALUES ('false')").execute_non_query();
    conn.create_command("INSERT INTO test_text_bool (value) VALUES ('TRUE')").execute_non_query();
    conn.create_command("INSERT INTO test_text_bool (value) VALUES ('True')").execute_non_query();
    
    var results = conn.create_command("SELECT value FROM test_text_bool ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 6);
    
    bool? val = null;
    
    // "1" -> true
    assert(array[0].get("value").try_get_as<bool?>(out val));
    assert(val == true);
    
    // "0" -> false
    assert(array[1].get("value").try_get_as<bool?>(out val));
    assert(val == false);
    
    // "true" -> true
    assert(array[2].get("value").try_get_as<bool?>(out val));
    assert(val == true);
    
    // "false" -> false
    assert(array[3].get("value").try_get_as<bool?>(out val));
    assert(val == false);
    
    // "TRUE" -> true (case insensitive)
    assert(array[4].get("value").try_get_as<bool?>(out val));
    assert(val == true);
    
    // "True" -> true (case insensitive)
    assert(array[5].get("value").try_get_as<bool?>(out val));
    assert(val == true);
    
    print("PASSED\n");
    conn.close();
}

void test_text_element_null_handling() throws SqlError {
    print("Test: Text element null handling... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_null_text (id INTEGER PRIMARY KEY, value TEXT)");
    
    conn.create_command("INSERT INTO test_null_text (value) VALUES (NULL)").execute_non_query();
    
    var results = conn.create_command("SELECT value FROM test_null_text")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    var elem = array[0].get("value");
    assert(elem.is_null() == true);
    
    string? val = null;
    // Note: null text element may return empty string or fail
    // depending on implementation
    elem.try_get_as<string>(out val);
    
    print("PASSED\n");
    conn.close();
}

// ============================================================================
// Blob Element Tests
// ============================================================================

void test_blob_element_round_trip() throws SqlError {
    print("Test: Blob element round-trip... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_blob (id INTEGER PRIMARY KEY, data BLOB)");
    
    // Create test binary data using Invercargill.Wrap.byte_array
    uint8[] original_bytes = { 0x00, 0x01, 0x02, 0xFF, 0xFE, 0xAB, 0xCD };
    var original_data = Invercargill.Wrap.byte_array(original_bytes);
    
    // Insert using parameter
    conn.create_command("INSERT INTO test_blob (data) VALUES (:data)")
        .with_parameter<Invercargill.BinaryData>("data", original_data)
        .execute_non_query();
    
    // Query back
    var results = conn.create_command("SELECT data FROM test_blob")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    // Get the blob data back
    var elem = array[0].get("data");
    assert(elem != null);
    assert(elem.is_null() == false);
    
    // Verify the element's string representation indicates blob data
    string elem_str = elem.to_string();
    assert(elem_str.contains("bytes") || elem_str.contains("Blob"));
    
    print("PASSED\n");
    conn.close();
}

void test_blob_element_null_handling() throws SqlError {
    print("Test: Blob element null handling... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_null_blob (id INTEGER PRIMARY KEY, data BLOB)");
    
    conn.create_command("INSERT INTO test_null_blob (data) VALUES (NULL)").execute_non_query();
    
    var results = conn.create_command("SELECT data FROM test_null_blob")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    var elem = array[0].get("data");
    assert(elem.is_null() == true);
    
    print("PASSED\n");
    conn.close();
}

// ============================================================================
// Null Element Tests
// ============================================================================

void test_null_element_handling() throws SqlError {
    print("Test: Null element handling... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_null (id INTEGER PRIMARY KEY, int_val INTEGER, str_val TEXT, real_val REAL)");
    
    // Insert row with all NULLs
    conn.create_command("INSERT INTO test_null (int_val, str_val, real_val) VALUES (NULL, NULL, NULL)")
        .execute_non_query();
    
    var results = conn.create_command("SELECT int_val, str_val, real_val FROM test_null")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    // All columns should be null
    var int_elem = array[0].get("int_val");
    var str_elem = array[0].get("str_val");
    var real_elem = array[0].get("real_val");
    
    assert(int_elem.is_null() == true);
    assert(str_elem.is_null() == true);
    assert(real_elem.is_null() == true);
    
    // Verify try_get_as returns false for null elements
    int64? int_val = null;
    string? str_val = null;
    double? real_val = null;
    
    assert(int_elem.try_get_as<int64?>(out int_val) == false);
    assert(str_elem.try_get_as<string>(out str_val) == false);
    assert(real_elem.try_get_as<double?>(out real_val) == false);
    
    // Verify assignable_to_type returns true for null elements
    assert(int_elem.assignable_to_type(typeof(int64?)) == true);
    assert(str_elem.assignable_to_type(typeof(string)) == true);
    assert(real_elem.assignable_to_type(typeof(double?)) == true);
    
    print("PASSED\n");
    conn.close();
}

// ============================================================================
// Round-trip Conversion Tests
// ============================================================================

void test_datetime_round_trip() throws SqlError {
    print("Test: DateTime round-trip conversion... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_dt_roundtrip (id INTEGER PRIMARY KEY, created_at INTEGER)");
    
    // Create a DateTime and convert to Unix timestamp
    var original_dt = new DateTime.local(2024, 6, 15, 14, 30, 45.0);
    int64? timestamp = original_dt.to_unix();
    
    // Insert the timestamp
    conn.create_command("INSERT INTO test_dt_roundtrip (created_at) VALUES (:ts)")
        .with_parameter<int64?>("ts", timestamp)
        .execute_non_query();
    
    // Query back and convert to DateTime
    var results = conn.create_command("SELECT created_at FROM test_dt_roundtrip")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    DateTime? retrieved_dt = null;
    var elem = array[0].get("created_at");
    assert(elem.try_get_as<DateTime>(out retrieved_dt));
    assert(retrieved_dt != null);
    
    // Verify the timestamp matches
    assert(retrieved_dt.to_unix() == timestamp);
    
    print("PASSED\n");
    conn.close();
}

void test_bool_round_trip() throws SqlError {
    print("Test: Bool round-trip conversion... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_bool_roundtrip (id INTEGER PRIMARY KEY, is_active INTEGER)");
    
    // Insert true and false values
    conn.create_command("INSERT INTO test_bool_roundtrip (is_active) VALUES (:val)")
        .with_parameter("val", true)
        .execute_non_query();
    conn.create_command("INSERT INTO test_bool_roundtrip (is_active) VALUES (:val)")
        .with_parameter("val", false)
        .execute_non_query();
    
    var results = conn.create_command("SELECT is_active FROM test_bool_roundtrip ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 2);
    
    bool? val = null;
    
    // First row should be true
    assert(array[0].get("is_active").try_get_as<bool?>(out val));
    assert(val == true);
    
    // Second row should be false
    assert(array[1].get("is_active").try_get_as<bool?>(out val));
    assert(val == false);
    
    print("PASSED\n");
    conn.close();
}

void test_nullable_types_round_trip() throws SqlError {
    print("Test: Nullable types round-trip... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_nullable (id INTEGER PRIMARY KEY, int_val INTEGER, real_val REAL, str_val TEXT)");
    
    // Insert with values
    double? real_param = 3.14;
    conn.create_command("INSERT INTO test_nullable (int_val, real_val, str_val) VALUES (:i, :r, :s)")
        .with_parameter("i", 42)
        .with_parameter<double?>("r", real_param)
        .with_parameter("s", "hello")
        .execute_non_query();
    
    // Insert with NULLs
    conn.create_command("INSERT INTO test_nullable (int_val, real_val, str_val) VALUES (NULL, NULL, NULL)")
        .execute_non_query();
    
    var results = conn.create_command("SELECT int_val, real_val, str_val FROM test_nullable ORDER BY id")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 2);
    
    // First row has values
    int64? i = null;
    double? r = null;
    string? s = null;
    
    assert(array[0].get("int_val").try_get_as<int64?>(out i));
    assert(i == 42);
    assert(array[0].get("real_val").try_get_as<double?>(out r));
    assert(r >= 3.13 && r <= 3.15);
    assert(array[0].get("str_val").try_get_as<string>(out s));
    assert(s == "hello");
    
    // Second row has NULLs
    assert(array[1].get("int_val").is_null());
    assert(array[1].get("real_val").is_null());
    assert(array[1].get("str_val").is_null());
    
    print("PASSED\n");
    conn.close();
}

// ============================================================================
// Complex Integration Tests
// ============================================================================

void test_mixed_type_table() throws SqlError {
    print("Test: Mixed type table... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    // Create a table with various column types
    conn.execute("""
        CREATE TABLE mixed_table (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            age INTEGER,
            salary REAL,
            is_active INTEGER,
            created_at INTEGER,
            data BLOB
        )
    """);
    
    // Insert a row with mixed types
    var now = new DateTime.now_local();
    uint8[] blob_bytes = { 0xDE, 0xAD, 0xBE, 0xEF };
    var blob_data = Invercargill.Wrap.byte_array(blob_bytes);
    double? salary_val = 75000.50;
    int64? created_val = now.to_unix();
    
    conn.create_command("""
        INSERT INTO mixed_table (name, age, salary, is_active, created_at, data)
        VALUES (:name, :age, :salary, :active, :created, :data)
    """)
        .with_parameter("name", "John Doe")
        .with_parameter("age", 30)
        .with_parameter<double?>("salary", salary_val)
        .with_parameter("active", true)
        .with_parameter<int64?>("created", created_val)
        .with_parameter<Invercargill.BinaryData>("data", blob_data)
        .execute_non_query();
    
    // Query back
    var results = conn.create_command("SELECT * FROM mixed_table").execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    var row = array[0];
    
    // Verify name (string)
    string? name = null;
    assert(row.get("name").try_get_as<string>(out name));
    assert(name == "John Doe");
    
    // Verify age (int)
    int? age = null;
    assert(row.get("age").try_get_as<int?>(out age));
    assert(age == 30);
    
    // Verify salary (double)
    double? salary = null;
    assert(row.get("salary").try_get_as<double?>(out salary));
    assert(salary >= 75000.49 && salary <= 75000.51);
    
    // Verify is_active (bool)
    bool? active = null;
    assert(row.get("is_active").try_get_as<bool?>(out active));
    assert(active == true);
    
    // Verify created_at (DateTime)
    DateTime? created = null;
    assert(row.get("created_at").try_get_as<DateTime>(out created));
    assert(created != null);
    assert(created.to_unix() == now.to_unix());
    
    // Verify data (blob)
    var data_elem = row.get("data");
    assert(data_elem.is_null() == false);
    
    print("PASSED\n");
    conn.close();
}

void test_element_assignable_types() throws SqlError {
    print("Test: Element assignable_types... ");
    
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    conn.execute("CREATE TABLE test_assignable (id INTEGER PRIMARY KEY, int_val INTEGER, real_val REAL, str_val TEXT)");
    
    conn.create_command("INSERT INTO test_assignable (int_val, real_val, str_val) VALUES (42, 3.14, 'test')")
        .execute_non_query();
    
    var results = conn.create_command("SELECT int_val, real_val, str_val FROM test_assignable")
        .execute_query();
    var array = results.to_array();
    
    assert(array.length == 1);
    
    var int_elem = array[0].get("int_val");
    var real_elem = array[0].get("real_val");
    var str_elem = array[0].get("str_val");
    
    // Integer element should be assignable to int64, int, bool, DateTime, double, float, string
    assert(int_elem.assignable_to_type(typeof(int64)) == true);
    assert(int_elem.assignable_to_type(typeof(int64?)) == true);
    assert(int_elem.assignable_to_type(typeof(int)) == true);
    assert(int_elem.assignable_to_type(typeof(int?)) == true);
    assert(int_elem.assignable_to_type(typeof(bool)) == true);
    assert(int_elem.assignable_to_type(typeof(bool?)) == true);
    assert(int_elem.assignable_to_type(typeof(DateTime)) == true);
    assert(int_elem.assignable_to_type(typeof(double)) == true);
    assert(int_elem.assignable_to_type(typeof(float)) == true);
    assert(int_elem.assignable_to_type(typeof(string)) == true);
    
    // Real element should be assignable to double, float, int64, int, string
    assert(real_elem.assignable_to_type(typeof(double)) == true);
    assert(real_elem.assignable_to_type(typeof(float)) == true);
    assert(real_elem.assignable_to_type(typeof(int64)) == true);
    assert(real_elem.assignable_to_type(typeof(int)) == true);
    assert(real_elem.assignable_to_type(typeof(string)) == true);
    
    // Text element should be assignable to string, int64, int, double, float, bool
    assert(str_elem.assignable_to_type(typeof(string)) == true);
    assert(str_elem.assignable_to_type(typeof(int64)) == true);
    assert(str_elem.assignable_to_type(typeof(int)) == true);
    assert(str_elem.assignable_to_type(typeof(double)) == true);
    assert(str_elem.assignable_to_type(typeof(float)) == true);
    assert(str_elem.assignable_to_type(typeof(bool)) == true);
    
    print("PASSED\n");
    conn.close();
}