tegdb 0.5.0

// examples/comprehensive_database_test.rs
use tegdb::{Database, Result, SqlValue};

fn main() -> Result<()> {
    // Use a temporary path with .teg extension
    let db_path = std::env::temp_dir().join("comprehensive_database_test.teg");
    let _ = std::fs::remove_file(&db_path);

    // Create/open database
    let mut db = Database::open(format!("file://{}", db_path.display()))?;

    println!("=== Setting up database ===");

    // Create table
    db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT(32) NOT NULL, age INTEGER)")?;
    println!("✓ Table created");

    // Insert test data
    db.execute("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)")?;
    db.execute("INSERT INTO users (id, name, age) VALUES (2, 'Bob', 25)")?;
    db.execute("INSERT INTO users (id, name, age) VALUES (3, 'Carol', 35)")?;
    println!("✓ Test data inserted");

    // Query all data
    let result = db.query("SELECT id, name, age FROM users")?;
    println!("\n=== Initial data ===");
    print_query_result(&result);

    println!("\n=== Testing explicit transaction ===");

    // Test explicit transaction
    {
        let mut tx = db.begin_transaction()?;

        // Try UPDATE within transaction
        let updated = tx.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")?;
        println!("UPDATE affected {updated} rows");

        // Try DELETE within transaction
        let deleted = tx.execute("DELETE FROM users WHERE name = 'Bob'")?;
        println!("DELETE affected {deleted} rows");

        // Try SELECT within transaction to see changes
        let tx_result = tx.query("SELECT id, name, age FROM users")?;
        println!("Data within transaction:");
        print_query_result(&tx_result);

        // Commit transaction
        tx.commit()?;
        println!("✓ Transaction committed");
    }

    // Query again to see final state
    let final_result = db.query("SELECT id, name, age FROM users")?;
    println!("\n=== Final data after transaction ===");
    print_query_result(&final_result);

    // Test simple operations
    println!("\n=== Testing simple operations ===");
    db.execute("INSERT INTO users (id, name, age) VALUES (4, 'David', 28)")?;
    let updated = db.execute("UPDATE users SET age = 36 WHERE name = 'Carol'")?;
    println!("Simple UPDATE affected {updated} rows");

    let simple_result = db.query("SELECT id, name, age FROM users")?;
    println!("Final state:");
    print_query_result(&simple_result);

    // Clean up
    let _ = std::fs::remove_file(&db_path);
    Ok(())
}

fn print_query_result(result: &tegdb::QueryResult) {
    println!("Columns: {:?}", result.columns());
    println!("Rows: {}", result.rows().len());

    for row in result.rows().iter() {
        let id_pos = result.columns().iter().position(|c| c == "id").unwrap();
        let name_pos = result.columns().iter().position(|c| c == "name").unwrap();
        let age_pos = result.columns().iter().position(|c| c == "age").unwrap();

        let id = match &row[id_pos] {
            SqlValue::Integer(i) => *i,
            _ => 0,
        };
        let name = match &row[name_pos] {
            SqlValue::Text(s) => s.clone(),
            _ => "Unknown".to_string(),
        };
        let age = match &row[age_pos] {
            SqlValue::Integer(i) => *i,
            _ => 0,
        };

        println!("  ID: {id}, Name: {name}, Age: {age}");
    }
}