Invercargill-Sql/plans/phase-9-async-crud-methods.md

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.

Current State Analysis

Existing Sync Methods in OrmSession

The OrmSession class currently provides three synchronous CRUD methods:

Method	Lines	Description
insert<T>(T entity)	80-125	Inserts an entity, back-populates auto-generated primary key
update<T>(T entity)	133-170	Updates an entity by primary key
delete<T>(T entity)	178-199	Deletes an entity by primary key

Async Pattern Reference

The codebase already has a well-established async pattern in Command:

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

Location: After the existing insert<T>() method (around line 125)

Implementation:

/**
 * 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

Location: After the existing update<T>() method (around line 170)

Implementation:

/**
 * 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

Location: After the existing delete<T>() method (around line 199)

Implementation:

/**
 * 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

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>()

/**
 * 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>()

/**
 * 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>()

/**
 * 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.