| 5 | /// Integration tests for batch UPDATE operations |
| 6 | #[tokio::test] |
| 7 | async fn test_batch_update_with_values() -> Result<(), Box<dyn std::error::Error>> { |
| 8 | let timestamp = SystemTime::now().duration_since(UNIX_EPOCH).unwrap().as_nanos(); |
| 9 | let db_path = format!("/tmp/batch_update_test_{timestamp}_1.db"); |
| 10 | let db_handler = Arc::new(DbHandler::new(&db_path)?); |
| 11 | |
| 12 | // Create test table |
| 13 | db_handler.execute("CREATE TABLE batch_users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)").await?; |
| 14 | |
| 15 | // Insert test data |
| 16 | for i in 1..=5 { |
| 17 | db_handler.execute(&format!("INSERT INTO batch_users (id, name, age) VALUES ({}, 'User{}', {})", i, i, i * 10)).await?; |
| 18 | } |
| 19 | |
| 20 | // Test batch UPDATE with VALUES syntax |
| 21 | let query = r#" |
| 22 | UPDATE batch_users AS u |
| 23 | SET name = v.new_name, age = v.new_age |
| 24 | FROM (VALUES |
| 25 | (1, 'Alice', 25), |
| 26 | (2, 'Bob', 30), |
| 27 | (3, 'Charlie', 35) |
| 28 | ) AS v(id, new_name, new_age) |
| 29 | WHERE u.id = v.id |
| 30 | "#; |
| 31 | |
| 32 | let result = db_handler.execute(query).await?; |
| 33 | println!("Batch update affected {} rows", result.rows_affected); |
| 34 | |
| 35 | // Verify the updates worked |
| 36 | let select_result = db_handler.query("SELECT id, name, age FROM batch_users ORDER BY id").await?; |
| 37 | |
| 38 | // Check that rows 1-3 were updated |
| 39 | let expected_data = [ |
| 40 | (1, "Alice", 25), |
| 41 | (2, "Bob", 30), |
| 42 | (3, "Charlie", 35), |
| 43 | (4, "User4", 40), // Unchanged |
| 44 | (5, "User5", 50), // Unchanged |
| 45 | ]; |
| 46 | |
| 47 | for (i, row) in select_result.rows.iter().enumerate() { |
| 48 | let id: i32 = String::from_utf8(row[0].as_ref().unwrap().clone())?.parse()?; |
| 49 | let name = String::from_utf8(row[1].as_ref().unwrap().clone())?; |
| 50 | let age: i32 = String::from_utf8(row[2].as_ref().unwrap().clone())?.parse()?; |
| 51 | |
| 52 | let (expected_id, expected_name, expected_age) = expected_data[i]; |
| 53 | assert_eq!(id, expected_id, "ID mismatch at row {i}"); |
| 54 | assert_eq!(name, expected_name, "Name mismatch at row {i}"); |
| 55 | assert_eq!(age, expected_age, "Age mismatch at row {i}"); |
| 56 | } |
| 57 | |
| 58 | println!("✅ Batch UPDATE with VALUES test passed"); |
| 59 | |
| 60 | // Cleanup |
| 61 | let _ = std::fs::remove_file(&db_path); |
| 62 | let _ = std::fs::remove_file(format!("{db_path}-wal")); |
| 63 | let _ = std::fs::remove_file(format!("{db_path}-shm")); |
| 64 | |