# Phase 9: Async CRUD Methods for OrmSession - Implementation Plan

---

> ## ⚠️ CRITICAL IMPLEMENTATION NOTES - READ FIRST ⚠️
>
> ### Code Style Requirements
>
> 1. **DO NOT use GLib.List, GLib.HashTable, or Libgee collections**
>    - Use ONLY `Invercargill.DataStructures` for all collections
>    - `Vector<T>` instead of `GLib.List<T>` or arrays
>    - `Dictionary<TKey, TValue>` instead of `GLib.HashTable` or `GLib.Map`
>    - `HashSet<T>` for set operations
>
> 2. **Reference the Invercargill Library**
>    - Analyze `../Invercargill/src/lib/` for DataStructures and Expressions patterns
>    - Key directories: `DataStructures/`, `Expressions/`, `Mapping/`
>    - Use `Invercargill.Expressions` for all expression handling
>
> 3. **No Raw SQL in High-Level APIs**
>    - All expression parameters use **Invercargill.Expressions** syntax
>    - Raw SQL is only generated internally by dialect implementations
>
> ### Async Pattern Requirements
>
> 1. **Thread Offloading at Lowest Level**
>    - SQLite has no native async API - thread offloading is acceptable
>    - Thread offloading happens in interfaces (`Command`, `Connection`, `Transaction`)
>    - Higher-level code should use `yield` to propagate async correctly
>
> 2. **No Blocking Async Methods**
>    - Async methods must NOT simply call their sync counterparts
>    - Use `yield` to call async methods on dependencies
>    - Maintain the async chain all the way down to the interface level

---

## Executive Summary

This document provides a detailed implementation plan for Phase 9: Adding async versions of the CRUD methods (`insert_async`, `update_async`, `delete_async`) to [`OrmSession`](src/orm/orm-session.vala).

## Current State Analysis

### Existing Sync Methods in OrmSession

The [`OrmSession`](src/orm/orm-session.vala) class currently provides three synchronous CRUD methods:

| Method | Lines | Description |
|--------|-------|-------------|
| [`insert<T>(T entity)`](src/orm/orm-session.vala:80) | 80-125 | Inserts an entity, back-populates auto-generated primary key |
| [`update<T>(T entity)`](src/orm/orm-session.vala:133) | 133-170 | Updates an entity by primary key |
| [`delete<T>(T entity)`](src/orm/orm-session.vala:178) | 178-199 | Deletes an entity by primary key |

### Async Pattern Reference

The codebase already has a well-established async pattern in [`Command`](src/interfaces/command.vala):

```vala
public async virtual int execute_non_query_async() throws SqlError {
    SourceFunc callback = execute_non_query_async.callback;
    int result = 0;
    SqlError? error = null;
    
    new Thread<void*>(null, () => {
        try {
            result = execute_non_query();
        } catch (SqlError e) {
            error = e;
        }
        Idle.add((owned)callback);
        return null;
    });
    yield;
    
    if (error != null) {
        throw error;
    }
    return result;
}
```

The async methods in `OrmSession` should use `yield command.execute_non_query_async()` to properly propagate async behavior.

## Implementation Plan

### Part 1: Add `insert_async<T>()` Method

**File:** [`src/orm/orm-session.vala`](src/orm/orm-session.vala)

**Location:** After the existing [`insert<T>()`](src/orm/orm-session.vala:80) method (around line 125)

**Implementation:**

```vala
/**
 * Inserts an entity into the database asynchronously.
 * 
 * This method performs the same operation as insert() but in a non-blocking
 * manner, allowing the main loop to process other events while waiting
 * for the database operation to complete.
 * 
 * After insertion, if the entity has an auto-increment primary key,
 * the key value is back-populated into the entity.
 * 
 * @param entity The entity to insert
 * @throws SqlError if insertion fails
 */
public async void insert_async<T>(T entity) throws Error {
    var mapper = get_mapper<T>();
    Invercargill.Properties properties;
    try {
        properties = mapper.map_from(entity);
    } catch (Error e) {
        throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
    }
    
    // Build column list, excluding auto-increment columns
    var columns = new Vector<string>();
    foreach (var col in mapper.columns) {
        if (!mapper.is_auto_increment(col.name)) {
            columns.add(col.name);
        }
    }
    
    var sql = _dialect.build_insert_sql(mapper.table_name, columns);
    var command = _connection.create_command(sql);
    
    // Add parameters in column order (excluding auto-increment)
    foreach (var col in mapper.columns) {
        if (mapper.is_auto_increment(col.name)) {
            continue;  // Skip auto-increment columns
        }
        var value = properties.get(col.name);
        if (value != null) {
            command.with_parameter<Invercargill.Element>(col.name, value);
        } else {
            command.with_null(col.name);
        }
    }
    
    // Execute asynchronously - THIS IS THE KEY CHANGE
    yield command.execute_non_query_async();
    
    // Back-populate the generated primary key
    var pk_column = mapper.get_effective_primary_key();
    if (pk_column != null && mapper.is_auto_increment(pk_column)) {
        int64 generated_id = _connection.last_insert_rowid;
        try {
            mapper.set_property_value(entity, pk_column, generated_id);
        } catch (Error e) {
            throw new SqlError.GENERAL_ERROR("Failed to back-populate primary key: %s".printf(e.message));
        }
    }
}
```

### Part 2: Add `update_async<T>()` Method

**File:** [`src/orm/orm-session.vala`](src/orm/orm-session.vala)

**Location:** After the existing [`update<T>()`](src/orm/orm-session.vala:133) method (around line 170)

**Implementation:**

```vala
/**
 * Updates an entity in the database asynchronously.
 * 
 * This method performs the same operation as update() but in a non-blocking
 * manner, allowing the main loop to process other events while waiting
 * for the database operation to complete.
 * 
 * The entity is identified by its primary key.
 * 
 * @param entity The entity to update
 * @throws SqlError if update fails
 */
public async void update_async<T>(T entity) throws Error {
    var mapper = get_mapper<T>();
    Invercargill.Properties properties;
    try {
        properties = mapper.map_from(entity);
    } catch (Error e) {
        throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
    }
    
    var columns = new Vector<string>();
    foreach (var col in mapper.columns) {
        columns.add(col.name);
    }
    
    var pk_column = mapper.get_effective_primary_key();
    var sql = _dialect.build_update_sql(mapper.table_name, columns, pk_column);
    var command = _connection.create_command(sql);
    
    // Add parameters for SET clause
    foreach (var col in mapper.columns) {
        var value = properties.get(col.name);
        if (value != null) {
            command.with_parameter<Invercargill.Element>(col.name, value);
        } else {
            command.with_null(col.name);
        }
    }
    
    // Add primary key parameter for WHERE clause
    var pk_value = properties.get(pk_column);
    if (pk_value != null) {
        command.with_parameter<Invercargill.Element>(pk_column, pk_value);
    } else {
        command.with_null(pk_column);
    }
    
    // Execute asynchronously - THIS IS THE KEY CHANGE
    yield command.execute_non_query_async();
}
```

### Part 3: Add `delete_async<T>()` Method

**File:** [`src/orm/orm-session.vala`](src/orm/orm-session.vala)

**Location:** After the existing [`delete<T>()`](src/orm/orm-session.vala:178) method (around line 199)

**Implementation:**

```vala
/**
 * Deletes an entity from the database asynchronously.
 * 
 * This method performs the same operation as delete() but in a non-blocking
 * manner, allowing the main loop to process other events while waiting
 * for the database operation to complete.
 * 
 * The entity is identified by its primary key.
 * 
 * @param entity The entity to delete
 * @throws SqlError if deletion fails
 */
public async void delete_async<T>(T entity) throws Error {
    var mapper = get_mapper<T>();
    Invercargill.Properties properties;
    try {
        properties = mapper.map_from(entity);
    } catch (Error e) {
        throw new SqlError.GENERAL_ERROR("Failed to map entity: %s".printf(e.message));
    }
    
    var pk_column = mapper.get_effective_primary_key();
    var sql = _dialect.build_delete_sql(mapper.table_name, pk_column);
    var command = _connection.create_command(sql);
    
    var pk_value = properties.get(pk_column);
    if (pk_value != null) {
        command.with_parameter<Invercargill.Element>(pk_column, pk_value);
    } else {
        command.with_null(pk_column);
    }
    
    // Execute asynchronously - THIS IS THE KEY CHANGE
    yield command.execute_non_query_async();
}
```

## Architecture Flow

```mermaid
sequenceDiagram
    participant User Code
    participant OrmSession
    participant Command
    participant Thread Pool
    participant SQLite
    
    User Code->>OrmSession: insert_async entity
    OrmSession->>OrmSession: Map entity to properties
    OrmSession->>OrmSession: Build INSERT SQL
    OrmSession->>Command: create_command sql
    OrmSession->>Command: with_parameter for each column
    OrmSession->>Command: execute_non_query_async
    Command->>Thread Pool: new Thread
    Thread Pool->>SQLite: execute_non_query
    SQLite-->>Thread Pool: success
    Thread Pool->>Command: Idle.add callback
    Command-->>OrmSession: yield returns
    OrmSession->>OrmSession: Back-populate primary key
    OrmSession-->>User Code: async completes
```

## Files to Modify

| File | Changes |
|------|---------|
| `src/orm/orm-session.vala` | Add `insert_async<T>()`, `update_async<T>()`, `delete_async<T>()` methods |
| `src/tests/orm-test.vala` | Add async tests for new methods |

## Implementation Order

1. **Step 1:** Add `insert_async<T>()` method
   - Follows the same logic as `insert<T>()` but uses `yield command.execute_non_query_async()`
   - Includes back-population of auto-generated primary key

2. **Step 2:** Add `update_async<T>()` method
   - Follows the same logic as `update<T>()` but uses `yield command.execute_non_query_async()`

3. **Step 3:** Add `delete_async<T>()` method
   - Follows the same logic as `delete<T>()` but uses `yield command.execute_non_query_async()`

4. **Step 4:** Add tests for all three async methods
   - Test basic functionality
   - Test that async methods actually yield
   - Test that results match sync counterparts

## Test Cases

### Tests for `insert_async<T>()`

```vala
/**
 * Test: Async insert basic functionality.
 */
async void test_insert_async_basic() throws Error {
    print("Test: insert_async basic... ");
    var session = setup_test_session();
    
    var user = new TestUser();
    user.name = "AsyncUser";
    user.email = "async@example.com";
    user.age = 30;
    
    assert(user.id == 0);
    
    yield session.insert_async(user);
    
    // Verify back-population
    assert(user.id > 0);
    
    // Verify data was persisted
    var results = session.query<TestUser>()
        .where("id == " + user.id.to_string())
        .materialise();
    var arr = results.to_array();
    assert(arr.length == 1);
    assert(arr[0].name == "AsyncUser");
    
    print("PASSED\n");
}

/**
 * Test: Async insert yields to main loop.
 */
async void test_insert_async_yields() throws Error {
    print("Test: insert_async yields... ");
    var session = setup_test_session();
    
    bool callback_executed = false;
    var source = new IdleSource();
    source.set_callback(() => {
        callback_executed = true;
        return Source.REMOVE;
    });
    source.attach(MainContext.default());
    
    var user = new TestUser();
    user.name = "YieldTest";
    user.age = 25;
    
    yield session.insert_async(user);
    
    // If async properly yielded, the idle callback should have executed
    assert(callback_executed);
    
    print("PASSED\n");
}
```

### Tests for `update_async<T>()`

```vala
/**
 * Test: Async update basic functionality.
 */
async void test_update_async_basic() throws Error {
    print("Test: update_async basic... ");
    var session = setup_test_session();
    
    // Insert a user first
    var user = new TestUser();
    user.name = "Original";
    user.age = 25;
    session.insert(user);
    
    // Update the user
    user.name = "Updated";
    user.age = 26;
    yield session.update_async(user);
    
    // Verify update
    var updated = session.query<TestUser>()
        .where("id == " + user.id.to_string())
        .first();
    assert(updated != null);
    assert(updated.name == "Updated");
    assert(updated.age == 26);
    
    print("PASSED\n");
}
```

### Tests for `delete_async<T>()`

```vala
/**
 * Test: Async delete basic functionality.
 */
async void test_delete_async_basic() throws Error {
    print("Test: delete_async basic... ");
    var session = setup_test_session();
    
    // Insert a user first
    var user = new TestUser();
    user.name = "ToDelete";
    user.age = 99;
    session.insert(user);
    var user_id = user.id;
    
    // Delete the user
    yield session.delete_async(user);
    
    // Verify deletion
    var results = session.query<TestUser>()
        .where("id == " + user_id.to_string())
        .materialise();
    var arr = results.to_array();
    assert(arr.length == 0);
    
    print("PASSED\n");
}
```

## Design Decisions

### Why Use `yield command.execute_non_query_async()`

The async methods use `yield command.execute_non_query_async()` instead of calling the sync version because:

1. **Proper async propagation** - The async/await pattern requires the whole call chain to be async
2. **Main loop integration** - Yielding allows the main loop to process other events
3. **Consistent API** - Matches the pattern used in Phase 5 for query methods

### Back-Population in `insert_async`

The `insert_async` method maintains the same back-population behavior as the sync version:

1. After the async INSERT completes, check if there's an auto-increment primary key
2. Retrieve the generated ID via `_connection.last_insert_rowid`
3. Set the property value on the entity using `mapper.set_property_value()`

This ensures that after `yield session.insert_async(entity)`, the entity's ID property contains the generated value.

## Summary

This phase adds async versions of the three CRUD methods to `OrmSession`:

| Method | Purpose | Key Implementation Detail |
|--------|---------|---------------------------|
| `insert_async<T>()` | Async insert with back-population | Uses `yield command.execute_non_query_async()` |
| `update_async<T>()` | Async update by primary key | Uses `yield command.execute_non_query_async()` |
| `delete_async<T>()` | Async delete by primary key | Uses `yield command.execute_non_query_async()` |

These methods follow the established async patterns from Phase 5 and integrate seamlessly with the existing `Command.execute_non_query_async()` implementation.