# Invercargill-Sql Architecture

A connection-centric SQL library for Vala with backend abstraction, initially implementing SQLite support.

## Overview

Invercargill-Sql provides a simple, minimal API for database operations with the following key characteristics:

- **Namespace:** `InvercargillSql`
- **Architecture:** Connection-centric with interfaces for backend abstraction
- **Initial Implementation:** SQLite only
- **Result Type:** Queries return `Invercargill.Enumerable<Invercargill.Properties>` for lazy enumeration
- **Collection Types:** Uses Invercargill data structures exclusively (no Gee/libgee)
- **Fluent API:** Command supports method chaining for parameter setting

## Architecture Diagram

```mermaid
classDiagram
    direction TB
    
    namespace Interfaces {
        interface Connection
        interface Command
        interface Transaction
    }
    
    namespace SQLite_Implementation {
        class SqliteConnection
        class SqliteCommand
        class SqliteTransaction
        class SqliteResultEnumerable
    }
    
    namespace Invercargill_Types {
        class Enumerable~T~
        interface Properties
        interface Element
        class Tracker~T~
        class PropertyDictionary
        class Dictionary~K_V~
        class Buffer~T~
    }
    
    Connection <.. SqliteConnection : implements
    Command <.. SqliteCommand : implements
    Transaction <.. SqliteTransaction : implements
    
    SqliteConnection --> SqliteCommand : creates
    SqliteConnection --> SqliteTransaction : creates
    SqliteCommand --> PropertyDictionary : contains parameters
    SqliteCommand --> SqliteResultEnumerable : returns
    
    SqliteResultEnumerable --|> Enumerable~Properties~ : extends
    PropertyDictionary ..|> Properties : implements
    PropertyDictionary --|> Dictionary~string_Element~ : extends
    SqliteResultEnumerable --> Tracker~Properties~ : provides
    
    note for Connection "Backend abstraction interface"
    note for SqliteConnection "Wraps Sqlite.Database"
    note for SqliteCommand "Wraps Sqlite.Statement with fluent API"
    note for SqliteResultEnumerable "Lazy enumeration over SQLite statement"
    note for PropertyDictionary "Parameters via Properties interface"
```

## Interface Hierarchy

### Connection Interface

The primary abstraction for database connections:

```vala
namespace InvercargillSql {
    
    [GenericAccessors]
    public interface Connection : Object {
        /** Opens the database connection */
        public abstract void open() throws SqlError;
        
        /** Opens the database connection asynchronously */
        public async virtual void open_async() throws SqlError;
        
        /** Closes the database connection */
        public abstract void close() throws SqlError;
        
        /** Creates a new command for executing SQL */
        public abstract Command create_command(string sql);
        
        /** Creates a new transaction */
        public abstract Transaction begin_transaction() throws SqlError;
        
        /** Executes raw SQL without parameters */
        public abstract void execute(string sql) throws SqlError;
        
        /** Executes raw SQL asynchronously */
        public async virtual void execute_async(string sql) throws SqlError;
        
        /** Gets the last inserted row ID */
        public abstract int64 last_insert_rowid { get; }
        
        /** Gets the number of rows affected by the last operation */
        public abstract int changes { get; }
        
        /** Checks if the connection is open */
        public abstract bool is_open { get; }
    }
}
```

### Command Interface (Fluent API)

Handles parameterized query execution with fluent parameter binding:

```vala
namespace InvercargillSql {
    
    public interface Command : Object {
        /** The SQL command text */
        public abstract string command_text { get; set; }
        
        /** Parameters for the command - uses Invercargill.Properties */
        public abstract Invercargill.Properties parameters { get; }
        
        /** 
         * Sets a parameter by name with automatic type wrapping.
         * Returns this for fluent chaining.
         */
        public abstract Command with_parameter<T>(string name, T value);
        
        /** 
         * Sets a parameter to null.
         * Returns this for fluent chaining.
         */
        public abstract Command with_null(string name);
        
        /** Executes a query and returns results as Enumerable~Properties~ */
        public abstract Invercargill.Enumerable<Invercargill.Properties> execute_query() throws SqlError;
        
        /** Executes a query asynchronously */
        public async virtual Invercargill.Enumerable<Invercargill.Properties> execute_query_async() throws SqlError;
        
        /** Executes a non-query command like INSERT, UPDATE, DELETE */
        public abstract int execute_non_query() throws SqlError;
        
        /** Executes a non-query command asynchronously */
        public async virtual int execute_non_query_async() throws SqlError;
        
        /** Executes a scalar query returning a single value */
        public abstract Invercargill.Element? execute_scalar() throws SqlError;
        
        /** Executes a scalar query asynchronously */
        public async virtual Invercargill.Element? execute_scalar_async() throws SqlError;
        
        /** Resets the command for re-execution with new parameters */
        public abstract Command reset();
    }
}
```

### Transaction Interface

Handles database transactions:

```vala
namespace InvercargillSql {
    
    public interface Transaction : Object {
        /** Commits the transaction */
        public abstract void commit() throws SqlError;
        
        /** Rolls back the transaction */
        public abstract void rollback() throws SqlError;
        
        /** Gets whether the transaction is active */
        public abstract bool is_active { get; }
    }
}
```

## Parameter Management with Properties

### Why Properties Interface?

The `Command.parameters` property uses `Invercargill.Properties` directly instead of a custom `ParameterCollection` interface. This provides:

1. **Consistency**: Same interface used for query results and parameters
2. **Element API**: Parameter values accessed via standard `Element` methods
3. **Type Safety**: `set_native<T>()` handles automatic wrapping
4. **Simplicity**: No additional interfaces to maintain

### Properties Interface Definition

From Invercargill core:

```vala
namespace Invercargill {
    public interface Properties : Associative<string, Element> {
        public abstract void set_native<T>(string key, T value) throws ElementError;
    }
}
```

### Parameter Usage Pattern

```vala
// Parameters implement Properties, so you can:
var cmd = conn.create_command("SELECT * FROM users WHERE id = :id AND status = :status");

// Fluent API with type inference
cmd.with_parameter("id", 42)
   .with_parameter("status", "active")
   .execute_query();

// Or access parameters directly via Properties interface
cmd.parameters.set_native("name", "John Doe");
var existing_param = cmd.parameters.get("name");  // Returns Element?
```

## SQLite Implementation Classes

### SqliteConnection

Wraps `Sqlite.Database` and implements `Connection`:

```vala
namespace InvercargillSql {
    
    public class SqliteConnection : Object, Connection {
        private Sqlite.Database _db;
        private bool _is_open;
        
        public string filename { get; construct; }
        public int flags { get; construct; default = Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE; }
        
        public SqliteConnection(string filename, int flags = Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE) {
            Object(filename: filename, flags: flags);
        }
        
        public bool is_open { get { return _is_open; } }
        
        public int64 last_insert_rowid { 
            get { return _db.last_insert_rowid(); } 
        }
        
        public int changes { 
            get { return _db.changes(); } 
        }
        
        public void open() throws SqlError {
            int result = Sqlite.Database.open_v2(filename, out _db, flags);
            if (result != Sqlite.OK) {
                throw new SqlError.CONNECTION_FAILED(
                    "Failed to open database: %s".printf(_db.errmsg())
                );
            }
            _is_open = true;
        }
        
        public async virtual void open_async() throws SqlError {
            SourceFunc callback = open_async.callback;
            new Thread<void*>(null, () => {
                try {
                    open();
                } catch (SqlError e) {
                    // Store error for main thread
                }
                Idle.add((owned)callback);
                return null;
            });
            yield;
        }
        
        public void close() throws SqlError {
            _is_open = false;
        }
        
        public Command create_command(string sql) {
            return new SqliteCommand(this, sql);
        }
        
        public Transaction begin_transaction() throws SqlError {
            execute("BEGIN TRANSACTION");
            return new SqliteTransaction(this);
        }
        
        public void execute(string sql) throws SqlError {
            ensure_open();
            string errmsg;
            int result = _db.exec(sql, null, null, out errmsg);
            if (result != Sqlite.OK) {
                throw new SqlError.EXECUTION_FAILED(
                    "SQL execution failed: %s".printf(errmsg ?? _db.errmsg())
                );
            }
        }
        
        public async virtual void execute_async(string sql) throws SqlError {
            SourceFunc callback = execute_async.callback;
            new Thread<void*>(null, () => {
                try {
                    execute(sql);
                } catch (SqlError e) {
                    // Store error for main thread
                }
                Idle.add((owned)callback);
                return null;
            });
            yield;
        }
        
        internal Sqlite.Database get_database() {
            return _db;
        }
        
        internal void ensure_open() throws SqlError {
            if (!_is_open) {
                throw new SqlError.CONNECTION_CLOSED("Connection is not open");
            }
        }
    }
}
```

### SqliteCommand

Wraps `Sqlite.Statement` and implements `Command` with fluent API:

```vala
namespace InvercargillSql {
    
    public class SqliteCommand : Object, Command {
        private weak SqliteConnection _connection;
        private Sqlite.Statement _stmt;
        private string _command_text;
        private Invercargill.DataStructures.PropertyDictionary _parameters;
        
        public string command_text {
            get { return _command_text; }
            set { 
                _command_text = value; 
                prepare_statement();
            }
        }
        
        public Invercargill.Properties parameters { 
            get { return _parameters; } 
        }
        
        internal SqliteCommand(SqliteConnection connection, string sql) {
            _connection = connection;
            _parameters = new Invercargill.DataStructures.PropertyDictionary();
            _command_text = sql;
            prepare_statement();
        }
        
        public Command with_parameter<T>(string name, T value) {
            try {
                _parameters.set_native<T>(name, value);
            } catch (Invercargill.ElementError e) {
                warning("Failed to set parameter %s: %s", name, e.message);
            }
            return this;
        }
        
        public Command with_null(string name) {
            _parameters.set(name, new Invercargill.NullElement());
            return this;
        }
        
        public Invercargill.Enumerable<Invercargill.Properties> execute_query() throws SqlError {
            _connection.ensure_open();
            bind_parameters();
            // Create enumerable and cache it to prevent double enumeration issues
            return new SqliteResultEnumerable(_stmt, _connection).cache();
        }
        
        public async virtual Invercargill.Enumerable<Invercargill.Properties> execute_query_async() 
            throws SqlError {
            SourceFunc callback = execute_query_async.callback;
            Invercargill.Enumerable<Invercargill.Properties>? result = null;
            SqlError? error = null;
            
            new Thread<void*>(null, () => {
                try {
                    result = execute_query();
                } catch (SqlError e) {
                    error = e;
                }
                Idle.add((owned)callback);
                return null;
            });
            yield;
            
            if (error != null) {
                throw error;
            }
            return result;
        }
        
        public int execute_non_query() throws SqlError {
            _connection.ensure_open();
            bind_parameters();
            
            int result = _stmt.step();
            _stmt.reset();
            
            if (result != Sqlite.DONE && result != Sqlite.ROW) {
                throw new SqlError.EXECUTION_FAILED(
                    "Command execution failed: %s".printf(
                        _connection.get_database().errmsg()
                    )
                );
            }
            
            return _connection.changes;
        }
        
        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;
        }
        
        public Invercargill.Element? execute_scalar() throws SqlError {
            _connection.ensure_open();
            bind_parameters();
            
            int result = _stmt.step();
            if (result == Sqlite.ROW) {
                var element = column_to_element(0);
                _stmt.reset();
                return element;
            }
            
            _stmt.reset();
            if (result != Sqlite.DONE) {
                throw new SqlError.EXECUTION_FAILED("Scalar query failed");
            }
            return null;
        }
        
        public async virtual Invercargill.Element? execute_scalar_async() throws SqlError {
            SourceFunc callback = execute_scalar_async.callback;
            Invercargill.Element? result = null;
            SqlError? error = null;
            
            new Thread<void*>(null, () => {
                try {
                    result = execute_scalar();
                } catch (SqlError e) {
                    error = e;
                }
                Idle.add((owned)callback);
                return null;
            });
            yield;
            
            if (error != null) {
                throw error;
            }
            return result;
        }
        
        public Command reset() {
            _stmt.reset();
            _stmt.clear_bindings();
            _parameters.clear();
            return this;
        }
        
        private void prepare_statement() throws SqlError {
            int result = _connection.get_database().prepare_v2(
                _command_text, 
                -1, 
                out _stmt
            );
            if (result != Sqlite.OK) {
                throw new SqlError.PREPARATION_FAILED(
                    "Failed to prepare statement: %s".printf(
                        _connection.get_database().errmsg()
                    )
                );
            }
        }
        
        private void bind_parameters() throws SqlError {
            // Iterate over parameters using Properties interface
            foreach (var kvp in _parameters) {
                string name = kvp.key;
                Invercargill.Element value = kvp.value;
                
                int index = _stmt.bind_parameter_index(name);
                if (index > 0) {
                    bind_value(index, value);
                }
            }
        }
        
        private void bind_value(int index, Invercargill.Element value) throws SqlError {
            if (value.is_null()) {
                _stmt.bind_null(index);
            } else if (value.assignable_to<int64>()) {
                int64 v;
                if (value.try_get_as(out v)) {
                    _stmt.bind_int64(index, v);
                }
            } else if (value.assignable_to<double>()) {
                double v;
                if (value.try_get_as(out v)) {
                    _stmt.bind_double(index, v);
                }
            } else if (value.assignable_to<string>()) {
                string v;
                if (value.try_get_as(out v)) {
                    _stmt.bind_text(index, v);
                }
            } else if (value.assignable_to<uint8[]>()) {
                uint8[] v;
                if (value.try_get_as(out v)) {
                    _stmt.bind_blob(index, v);
                }
            }
        }
        
        private Invercargill.Element column_to_element(int col) {
            int type = _stmt.column_type(col);
            switch (type) {
                case Sqlite.INTEGER:
                    return new Invercargill.NativeElement<int64>(_stmt.column_int64(col));
                case Sqlite.FLOAT:
                    return new Invercargill.NativeElement<double>(_stmt.column_double(col));
                case Sqlite.TEXT:
                    return new Invercargill.NativeElement<string>(_stmt.column_text(col));
                case Sqlite.BLOB:
                    unowned uint8[] blob = _stmt.column_blob(col);
                    int bytes = _stmt.column_bytes(col);
                    uint8[] copy = new uint8[bytes];
                    Memory.copy(copy, blob, bytes);
                    return new Invercargill.NativeElement<uint8[]>(copy);
                case Sqlite.NULL:
                default:
                    return new Invercargill.NullElement();
            }
        }
    }
}
```

## Result Handling with Enumerable

### The Enumerable Pattern

Invercargill's `Enumerable<T>` is an abstract class that provides lazy enumeration capabilities. To implement a custom Enumerable, you must:

1. **Extend `Enumerable<T>`**
2. **Implement three abstract methods:**
   - `get_tracker()` - Returns a `Tracker<T>` for iteration
   - `peek_count()` - Returns the count if known, or `null` if unknown
   - `get_info()` - Returns metadata about the enumerable

### Tracker Pattern

The `Tracker<T>` class is similar to an iterator but with a different API:

```vala
namespace Invercargill {
    public abstract class Tracker<T> {
        public abstract bool has_next();  // Check if more items exist
        public abstract T get_next();      // Get next item and advance
        
        // Helper methods:
        public bool next();   // Advances and returns true if successful
        public T get();       // Gets current item without advancing
    }
}
```

### SqliteResultEnumerable

Implements `Enumerable<Properties>` for lazy row-by-row enumeration:

```vala
namespace InvercargillSql {
    
    /// <summary>
    /// Lazy enumerable over SQLite query results.
    /// Enumerates the underlying SQLite statement row by row.
    /// </summary>
    public class SqliteResultEnumerable : Invercargill.Enumerable<Invercargill.Properties> {
        private Sqlite.Statement _stmt;
        private weak SqliteConnection _connection;
        private Invercargill.DataStructures.Buffer<string>? _column_names;
        private int _column_count;
        private bool _enumerated;
        
        internal SqliteResultEnumerable(Sqlite.Statement stmt, SqliteConnection connection) {
            _stmt = stmt;
            _connection = connection;
            _enumerated = false;
            
            // Cache column metadata
            _column_count = stmt.column_count();
            _column_names = new Invercargill.DataStructures.Buffer<string>(_column_count);
            
            for (int i = 0; i < _column_count; i++) {
                _column_names.set(i, stmt.column_name(i));
            }
        }
        
        /// <summary>
        /// Returns a tracker for iterating over result rows.
        /// </summary>
        public override Invercargill.Tracker<Invercargill.Properties> get_tracker() {
            return new Invercargill.AdvanceTracker<Invercargill.Properties>(advance_row);
        }
        
        /// <summary>
        /// Returns null since SQLite doesn't provide row count before enumeration.
        /// </summary>
        public override uint? peek_count() {
            return null;  // Count unknown until fully enumerated
        }
        
        /// <summary>
        /// Returns metadata about this enumerable.
        /// </summary>
        public override Invercargill.EnumerableInfo get_info() {
            return new Invercargill.EnumerableInfo.infer_ultimate(
                this, 
                Invercargill.EnumerableCategory.EXTERNAL
            );
        }
        
        /// <summary>
        /// Advances to the next row and returns it via the out parameter.
        /// Called by the AdvanceTracker.
        /// </summary>
        private bool advance_row(out Invercargill.Properties? row) {
            if (_enumerated) {
                row = null;
                return false;
            }
            
            int result = _stmt.step();
            
            if (result == Sqlite.ROW) {
                // Create a Properties from the current row
                var props = new Invercargill.DataStructures.PropertyDictionary();
                
                for (int i = 0; i < _column_count; i++) {
                    var element = column_to_element(i);
                    string col_name;
                    _column_names.try_get(i, out col_name);
                    props.set(col_name, element);
                }
                
                row = props;
                return true;
            }
            
            // No more rows - cleanup
            _stmt.reset();
            _enumerated = true;
            row = null;
            return false;
        }
        
        private Invercargill.Element column_to_element(int col) {
            int type = _stmt.column_type(col);
            switch (type) {
                case Sqlite.INTEGER:
                    return new Invercargill.NativeElement<int64>(_stmt.column_int64(col));
                case Sqlite.FLOAT:
                    return new Invercargill.NativeElement<double>(_stmt.column_double(col));
                case Sqlite.TEXT:
                    return new Invercargill.NativeElement<string>(_stmt.column_text(col));
                case Sqlite.BLOB:
                    unowned uint8[] blob = _stmt.column_blob(col);
                    int bytes = _stmt.column_bytes(col);
                    uint8[] copy = new uint8[bytes];
                    Memory.copy(copy, blob, bytes);
                    return new Invercargill.NativeElement<uint8[]>(copy);
                case Sqlite.NULL:
                default:
                    return new Invercargill.NullElement();
            }
        }
    }
}
```

### Why Use .cache()?

The `.cache()` method is crucial for preventing double enumeration issues:

```vala
// Without cache - statement would be stepped twice (problematic!)
var results = conn.create_command("SELECT * FROM users").execute_query();
var count = results.count();        // First enumeration
foreach (var row in results) { ... } // Second enumeration - ERROR!

// With cache - results are stored after first enumeration
var results = conn.create_command("SELECT * FROM users").execute_query();  // Already cached internally
var count = results.count();        // First enumeration caches results
foreach (var row in results) { ... } // Uses cached results - OK!
```

The `execute_query()` method automatically applies `.cache()` before returning:

```vala
public Invercargill.Enumerable<Invercargill.Properties> execute_query() throws SqlError {
    _connection.ensure_open();
    bind_parameters();
    return new SqliteResultEnumerable(_stmt, _connection).cache();
}
```

## Error Handling

### SqlError Domain

```vala
namespace InvercargillSql {
    
    public errordomain SqlError {
        /** Failed to open database connection */
        CONNECTION_FAILED,
        
        /** Connection was closed when operation attempted */
        CONNECTION_CLOSED,
        
        /** Failed to prepare SQL statement */
        PREPARATION_FAILED,
        
        /** Failed to execute SQL command */
        EXECUTION_FAILED,
        
        /** Invalid parameter name or index */
        INVALID_PARAMETER,
        
        /** Transaction operation failed */
        TRANSACTION_FAILED,
        
        /** Data type conversion error */
        TYPE_ERROR,
        
        /** General SQL error */
        GENERAL_ERROR
    }
}
```

## Async Design

### Thread-Based Implementation

Since SQLite is a synchronous library, async methods use threads to avoid blocking the main loop:

```vala
// All async methods follow this pattern:
public async virtual Invercargill.Enumerable<Invercargill.Properties> execute_query_async() 
    throws SqlError {
    SourceFunc callback = execute_query_async.callback;
    Invercargill.Enumerable<Invercargill.Properties>? result = null;
    SqlError? error = null;
    
    new Thread<void*>(null, () => {
        try {
            result = execute_query();
        } catch (SqlError e) {
            error = e;
        }
        Idle.add((owned)callback);
        return null;
    });
    yield;
    
    if (error != null) {
        throw error;
    }
    return result;
}
```

### Methods with Async Variants

| Synchronous Method | Async Variant |
|-------------------|---------------|
| `Connection.open()` | `Connection.open_async()` |
| `Connection.execute()` | `Connection.execute_async()` |
| `Command.execute_query()` | `Command.execute_query_async()` |
| `Command.execute_non_query()` | `Command.execute_non_query_async()` |
| `Command.execute_scalar()` | `Command.execute_scalar_async()` |

## File Structure

```
Invercargill-Sql/
├── src/
│   ├── meson.build              # Build configuration
│   ├── sql-error.vala           # SqlError domain definition
│   ├── interfaces/
│   │   ├── connection.vala      # Connection interface
│   │   ├── command.vala         # Command interface with fluent API
│   │   └── transaction.vala     # Transaction interface
│   └── sqlite/
│       ├── sqlite-connection.vala     # SqliteConnection implementation
│       ├── sqlite-command.vala        # SqliteCommand implementation
│       ├── sqlite-transaction.vala    # SqliteTransaction implementation
│       └── sqlite-result-enumerable.vala  # SqliteResultEnumerable lazy enumeration
├── tests/
│   ├── meson.build
│   ├── test-connection.vala     # Connection tests
│   ├── test-command.vala        # Command and fluent API tests
│   └── test-results.vala        # Result enumerable tests
├── docs/
│   └── ARCHITECTURE.md          # This document
├── vapi/
│   └── InvercargillSql.deps     # Dependencies file
├── InvercargillSql.pc.in        # pkg-config template
└── meson.build                  # Root build configuration
```

### File Descriptions

| File | Purpose |
|------|---------|
| `sql-error.vala` | Defines the `SqlError` error domain |
| `interfaces/connection.vala` | `Connection` interface definition |
| `interfaces/command.vala` | `Command` interface with fluent `with_parameter<T>()` API |
| `interfaces/transaction.vala` | `Transaction` interface definition |
| `sqlite/sqlite-connection.vala` | SQLite implementation of `Connection` |
| `sqlite/sqlite-command.vala` | SQLite implementation of `Command` with fluent API |
| `sqlite/sqlite-transaction.vala` | SQLite transaction management |
| `sqlite/sqlite-result-enumerable.vala` | Lazy enumerable extending `Enumerable<Properties>` |

## Usage Examples

### Basic Connection and Query

```vala
using InvercargillSql;

void main() {
    try {
        // Create and open connection
        var conn = new SqliteConnection("mydb.sqlite");
        conn.open();
        
        // Execute a simple query - returns Enumerable<Properties>
        var results = conn.create_command("SELECT * FROM users")
                         .execute_query();
        
        // Iterate lazily over results
        foreach (var row in results) {
            string? name = row.get("name")?.as_string_or_null();
            int? age = row.get("age")?.as_int_or_null();
            print("User: %s, Age: %d\n", name ?? "Unknown", age ?? 0);
        }
        
        conn.close();
    } catch (SqlError e) {
        stderr.printf("Database error: %s\n", e.message);
    }
}
```

### Fluent API for Parameterized Queries

```vala
using InvercargillSql;

void find_user_by_id(Connection conn, int64 user_id) throws SqlError {
    // Fluent chaining with with_parameter
    var results = conn.create_command("SELECT * FROM users WHERE id = :id")
                     .with_parameter("id", user_id)
                     .execute_query();
    
    // Use Enumerable methods
    var user = results.first_or_default();
    if (user != null) {
        print("Found user: %s\n", user.get("name")?.as_string_or_null() ?? "Unknown");
    }
}

void insert_user(Connection conn, string name, string email, int age) throws SqlError {
    // Multiple parameters in fluent chain
    int affected = conn.create_command(
        "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)"
    )
    .with_parameter("name", name)
    .with_parameter("email", email)
    .with_parameter("age", age)
    .execute_non_query();
    
    print("Inserted %d row(s)\n", affected);
    print("New ID: %lld\n", conn.last_insert_rowid);
}

void complex_query_example(Connection conn) throws SqlError {
    // Fluent with reset for re-execution
    var cmd = conn.create_command("SELECT * FROM products WHERE category = :cat AND price < :max_price");
    
    // First query
    var cheap_electronics = cmd
        .with_parameter("cat", "electronics")
        .with_parameter("max_price", 100.0)
        .execute_query();
    
    // Reset and re-use with different parameters
    var cheap_books = cmd
        .reset()
        .with_parameter("cat", "books")
        .with_parameter("max_price", 25.0)
        .execute_query();
}
```

### Using Enumerable LINQ-Style Methods

```vala
using InvercargillSql;
using Invercargill;

void query_with_transforms(Connection conn) throws SqlError {
    var results = conn.create_command("SELECT * FROM users")
                     .execute_query();
    
    // Filter with where
    var active_users = results.where(row => {
        var status = row.get("status")?.as_string_or_null();
        return status == "active";
    });
    
    // Transform with select
    var names = results.select(row => row.get("name")?.as_string_or_null() ?? "Unknown");
    
    // Combine operations
    var adult_names = conn.create_command("SELECT * FROM users")
                         .execute_query()
                         .where(row => row.get("age")?.as_int_or_null() >= 18)
                         .select(row => row.get("name")?.as_string_or_null() ?? "Unknown")
                         .to_array();
    
    // Get first or default
    var first_admin = conn.create_command("SELECT * FROM users WHERE role = :role")
                         .with_parameter("role", "admin")
                         .execute_query()
                         .first_or_default();
    
    // Check if any exist
    bool has_users = conn.create_command("SELECT * FROM users LIMIT 1")
                        .execute_query()
                        .any();
}
```

### Async Operations with Fluent API

```vala
using InvercargillSql;

async void load_users_async(Connection conn) throws SqlError {
    var results = yield conn.create_command("SELECT * FROM users")
                          .execute_query_async();
    
    // Count uses cached results
    print("Loaded %u users\n", results.count());
}

async void insert_user_async(Connection conn, string name, string email) throws SqlError {
    int affected = yield conn.create_command(
        "INSERT INTO users (name, email) VALUES (:name, :email)"
    )
    .with_parameter("name", name)
    .with_parameter("email", email)
    .execute_non_query_async();
    
    print("Inserted %d row(s) asynchronously\n", affected);
}
```

### Transactions with Fluent Commands

```vala
using InvercargillSql;

void transfer_funds(Connection conn, int64 from_id, int64 to_id, double amount) 
    throws SqlError {
    var transaction = conn.begin_transaction();
    
    try {
        // Debit source account - fluent style
        conn.create_command(
            "UPDATE accounts SET balance = balance - :amount WHERE id = :id"
        )
        .with_parameter("amount", amount)
        .with_parameter("id", from_id)
        .execute_non_query();
        
        // Credit destination account
        conn.create_command(
            "UPDATE accounts SET balance = balance + :amount WHERE id = :id"
        )
        .with_parameter("amount", amount)
        .with_parameter("id", to_id)
        .execute_non_query();
        
        transaction.commit();
        print("Transfer completed successfully\n");
    } catch (SqlError e) {
        transaction.rollback();
        throw e;
    }
}
```

### Working with Element Values

```vala
using InvercargillSql;
using Invercargill;

void process_row(Properties row) {
    // Safe value extraction with null handling
    string? name = row.get("name")?.as_string_or_null();
    
    // Direct value access with default
    int age = row.get("age")?.as_int_or_null() ?? 0;
    
    // Type checking before extraction
    var balance_element = row.get("balance");
    if (balance_element != null && !balance_element.is_null()) {
        if (balance_element.assignable_to<double>()) {
            double balance;
            if (balance_element.try_get_as(out balance)) {
                print("Balance: %.2f\n", balance);
            }
        }
    }
    
    // Using try_get_as pattern
    int64? id = null;
    var id_element = row.get("id");
    if (id_element != null) {
        int64 temp;
        if (id_element.try_get_as(out temp)) {
            id = temp;
        }
    }
}
```

### Accessing Parameters via Properties Interface

```vala
using InvercargillSql;
using Invercargill;

void inspect_command(Command cmd) {
    // Parameters implement Properties, so we can iterate
    foreach (var kvp in cmd.parameters) {
        print("Parameter: %s, Type: %s\n", kvp.key, kvp.value.type_name());
    }
    
    // Check if parameter exists
    if (cmd.parameters.has("user_id")) {
        var param = cmd.parameters.get("user_id");
        int64? id = param?.as_int64_or_null();
        print("User ID parameter: %lld\n", id ?? 0);
    }
    
    // Get all parameter names
    var param_names = cmd.parameters.keys;
    foreach (string name in param_names) {
        print("Param: %s\n", name);
    }
}
```

## Dependencies

- **sqlite3** - SQLite C library
- **invercargill-1** - Invercargill collection library (provides `Properties`, `Enumerable<T>`, `Tracker<T>`, `Series<T>`, `Dictionary<K,V>`, `Buffer<T>`, `Element`)
- **glib-2.0** - GLib core library
- **gobject-2.0** - GObject type system

## Collection Types Reference

This library uses Invercargill data structures exclusively:

| Type | Purpose | Key Features |
|------|---------|--------------|
| `Properties` | Parameter storage, result rows | String-keyed `Element` values, `set_native<T>()` |
| `PropertyDictionary` | Concrete `Properties` implementation | Extends `Dictionary<string, Element>` |
| `Enumerable<T>` | Lazy sequence base | Abstract class with `get_tracker()`, `peek_count()`, `get_info()` |
| `Tracker<T>` | Iterator pattern | `has_next()`, `get_next()` methods |
| `Buffer<T>` | Fixed-size indexed collection | Column name storage |
| `Dictionary<K,V>` | Key-value storage | Hash-based, implements `Associative` |
| `Element` | Type-erased value | `NativeElement<T>`, `NullElement` |

## Future Considerations

### Additional Backend Support

The interface-based design allows for future implementations:

- `PostgresConnection` / `PostgresCommand` for PostgreSQL
- `MysqlConnection` / `MysqlCommand` for MySQL
- `MssqlConnection` / `MssqlCommand` for SQL Server

### Connection Pooling

A `ConnectionPool` class could be added to manage multiple connections:

```vala
public interface ConnectionPool : Object {
    public abstract Connection acquire() throws SqlError;
    public abstract void release(Connection conn);
    public async virtual Connection acquire_async() throws SqlError;
}
```

### Prepared Statement Caching

Commands could cache prepared statements for repeated execution:

```vala
// Future enhancement for SqliteConnection
private Invercargill.DataStructures.Dictionary<string, Sqlite.Statement> _statement_cache;
```

## Connection String Factory

The Connection String Factory provides a simple, extensible mechanism for creating database connections from URI-style connection strings.

### Connection String Format

Connection strings follow a standard URI format:

```
scheme://[user[:password]@]host[:port]/database[?option=value&option2=value2]
```

#### Supported Schemes

| Scheme | Implementation | Example |
|--------|---------------|---------|
| `sqlite` | `SqliteConnection` | `sqlite:///path/to/db.sqlite` |
| `sqlite` (in-memory) | `SqliteConnection` | `sqlite::memory:` or `sqlite://:memory:` |

#### SQLite Connection String Examples

```
# Absolute path (three slashes)
sqlite:///home/user/data/mydb.sqlite

# Relative path (two slashes + relative)
sqlite://./data/mydb.sqlite

# In-memory database
sqlite::memory:
sqlite://:memory:

# With options
sqlite:///mydb.sqlite?mode=ro&cache=shared
```

### Class Design

```mermaid
classDiagram
    direction TB
    
    class ConnectionFactory {
        -Dictionary~string_ConnectionProvider~ _providers
        +create(string connection_string) Connection
        +create_async(string connection_string) async Connection
        +register_provider(string scheme, ConnectionProvider provider) void
        +get_provider(string scheme) ConnectionProvider?
    }
    
    class ConnectionString {
        +string scheme
        +string? user
        +string? password
        +string host
        +uint16? port
        +string database
        +Dictionary~string_string~ options
        +parse(string connection_string) ConnectionString
        +to_string() string
    }
    
    class ConnectionProvider {
        <<interface>>
        +create_connection(ConnectionString conn_str) Connection
        +create_connection_async(ConnectionString conn_str) async Connection
    }
    
    class SqliteProvider {
        +create_connection(ConnectionString conn_str) Connection
        +create_connection_async(ConnectionString conn_str) async Connection
    }
    
    class Connection {
        <<interface>>
    }
    
    class SqliteConnection {
        +string filename
        +int flags
    }
    
    ConnectionFactory --> ConnectionString : parses
    ConnectionFactory --> ConnectionProvider : uses
    ConnectionProvider <|.. SqliteProvider : implements
    SqliteProvider --> SqliteConnection : creates
    SqliteConnection ..|> Connection : implements
    
    note for ConnectionFactory "Static class with built-in SQLite provider"
    note for ConnectionString "Immutable parsed connection data"
    note for SqliteProvider "Converts ConnectionString to SqliteConnection"
```

### Class Definitions

#### ConnectionString

Immutable data class holding parsed connection string components:

```vala
namespace InvercargillSql {
    
    /**
     * Represents a parsed connection string.
     * Immutable after creation.
     */
    public class ConnectionString : Object {
        
        /** The database scheme (e.g., "sqlite", "postgresql") */
        public string scheme { get; construct; }
        
        /** Username for authentication, or null */
        public string? user { get; construct; }
        
        /** Password for authentication, or null */
        public string? password { get; construct; }
        
        /** Host name or path component */
        public string host { get; construct; }
        
        /** Port number, or null if default */
        public uint16? port { get; construct; }
        
        /** Database name or file path */
        public string database { get; construct; }
        
        /** Query string options as key-value pairs */
        public Invercargill.DataStructures.Dictionary<string, string> options { get; construct; }
        
        /**
         * Parses a connection string URI into components.
         *
         * @param connection_string The URI-style connection string
         * @return A new ConnectionString instance
         * @throws SqlError.CONNECTION_FAILED if parsing fails
         */
        public static ConnectionString parse(string connection_string) throws SqlError {
            // Implementation parses URI format
        }
        
        /**
         * Reconstructs the connection string from components.
         */
        public string to_string() {
            // Implementation reconstructs URI
        }
        
        /**
         * Gets an option value by name.
         * @return The option value, or null if not present
         */
        public string? get_option(string name) {
            return options.get(name);
        }
    }
}
```

#### ConnectionProvider Interface

Interface for database-specific connection creation:

```vala
namespace InvercargillSql {
    
    /**
     * Interface for database backend providers.
     * Implement this to add support for new database types.
     */
    public interface ConnectionProvider : Object {
        
        /**
         * Creates a connection from a parsed connection string.
         *
         * @param conn_str The parsed connection string
         * @return A new Connection instance (not yet opened)
         * @throws SqlError if connection creation fails
         */
        public abstract Connection create_connection(ConnectionString conn_str) throws SqlError;
        
        /**
         * Creates a connection asynchronously.
         */
        public async virtual Connection create_connection_async(ConnectionString conn_str)
            throws SqlError {
            SourceFunc callback = create_connection_async.callback;
            Connection? result = null;
            SqlError? error = null;
            
            new Thread<void*>(null, () => {
                try {
                    result = create_connection(conn_str);
                } catch (SqlError e) {
                    error = e;
                }
                Idle.add((owned)callback);
                return null;
            });
            yield;
            
            if (error != null) {
                throw error;
            }
            return result;
        }
    }
}
```

#### SqliteProvider

SQLite-specific provider implementation:

```vala
namespace InvercargillSql {
    
    /**
     * SQLite connection provider.
     * Handles sqlite:// scheme connection strings.
     */
    public class SqliteProvider : Object, ConnectionProvider {
        
        public Connection create_connection(ConnectionString conn_str) throws SqlError {
            string filename;
            int flags = Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE;
            
            // Handle special :memory: database
            if (conn_str.database == ":memory:") {
                filename = ":memory:";
            } else {
                // Build file path from host and database components
                filename = build_file_path(conn_str);
            }
            
            // Apply options
            var mode = conn_str.get_option("mode");
            if (mode != null) {
                flags = parse_mode(mode);
            }
            
            return new SqliteConnection(filename, flags);
        }
        
        private string build_file_path(ConnectionString conn_str) {
            // For sqlite:///path/to/db, host is empty and database is /path/to/db
            // For sqlite://./relative/path, host is . and database is relative/path
            if (conn_str.host.length > 0 && conn_str.host != ".") {
                return Path.build_filename(conn_str.host, conn_str.database);
            } else if (conn_str.host == ".") {
                return conn_str.database;  // Relative path
            }
            return conn_str.database;  // Absolute path in database
        }
        
        private int parse_mode(string mode) {
            switch (mode.down()) {
                case "ro":
                case "readonly":
                    return Sqlite.OPEN_READONLY;
                case "rw":
                case "readwrite":
                    return Sqlite.OPEN_READWRITE;
                case "rwc":
                case "readwritecreate":
                    return Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE;
                default:
                    return Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE;
            }
        }
    }
}
```

#### ConnectionFactory

Static factory class for creating connections:

```vala
namespace InvercargillSql {
    
    /**
     * Factory for creating database connections from connection strings.
     *
     * Supports automatic provider resolution based on URI scheme.
     * Built-in support for SQLite; additional providers can be registered.
     */
    public class ConnectionFactory : Object {
        
        private static Invercargill.DataStructures.Dictionary<string, ConnectionProvider> _providers;
        private static bool _initialized = false;
        
        /**
         * Initializes the factory with built-in providers.
         */
        private static void ensure_initialized() {
            if (_initialized) return;
            
            _providers = new Invercargill.DataStructures.Dictionary<string, ConnectionProvider>();
            _providers.set("sqlite", new SqliteProvider());
            _initialized = true;
        }
        
        /**
         * Creates a connection from a connection string.
         *
         * The connection is created but NOT opened. Call open() on the
         * returned connection before use.
         *
         * @param connection_string URI-style connection string
         * @return A new Connection instance appropriate for the scheme
         * @throws SqlError.CONNECTION_FAILED if scheme is unknown or parsing fails
         *
         * @example
         * var conn = ConnectionFactory.create("sqlite:///mydb.sqlite");
         * conn.open();
         */
        public static Connection create(string connection_string) throws SqlError {
            ensure_initialized();
            
            var parsed = ConnectionString.parse(connection_string);
            var provider = _providers.get(parsed.scheme);
            
            if (provider == null) {
                throw new SqlError.CONNECTION_FAILED(
                    "Unknown database scheme: %s".printf(parsed.scheme)
                );
            }
            
            return provider.create_connection(parsed);
        }
        
        /**
         * Creates a connection asynchronously.
         */
        public async static Connection create_async(string connection_string) throws SqlError {
            ensure_initialized();
            
            var parsed = ConnectionString.parse(connection_string);
            var provider = _providers.get(parsed.scheme);
            
            if (provider == null) {
                throw new SqlError.CONNECTION_FAILED(
                    "Unknown database scheme: %s".printf(parsed.scheme)
                );
            }
            
            return yield provider.create_connection_async(parsed);
        }
        
        /**
         * Creates and opens a connection in one step.
         */
        public static Connection create_and_open(string connection_string) throws SqlError {
            var conn = create(connection_string);
            conn.open();
            return conn;
        }
        
        /**
         * Creates and opens a connection asynchronously.
         */
        public async static Connection create_and_open_async(string connection_string)
            throws SqlError {
            var conn = create(connection_string);
            yield conn.open_async();
            return conn;
        }
        
        /**
         * Registers a provider for a scheme.
         * Replaces any existing provider for the scheme.
         *
         * @param scheme The URI scheme (e.g., "postgresql")
         * @param provider The provider implementation
         */
        public static void register_provider(string scheme, ConnectionProvider provider) {
            ensure_initialized();
            _providers.set(scheme.down(), provider);
        }
        
        /**
         * Gets the provider for a scheme, or null if not registered.
         */
        public static ConnectionProvider? get_provider(string scheme) {
            ensure_initialized();
            return _providers.get(scheme.down());
        }
    }
}
```

### Usage Examples

#### Basic Connection Creation

```vala
using InvercargillSql;

void basic_example() {
    try {
        // Create connection from connection string
        var conn = ConnectionFactory.create("sqlite:///home/user/mydb.sqlite");
        conn.open();
        
        // Use the connection
        var results = conn.create_command("SELECT * FROM users").execute_query();
        
        conn.close();
    } catch (SqlError e) {
        stderr.printf("Error: %s\n", e.message);
    }
}
```

#### Create and Open in One Step

```vala
using InvercargillSql;

void create_and_open_example() throws SqlError {
    // Create and open in a single call
    var conn = ConnectionFactory.create_and_open("sqlite::memory:");
    
    // Connection is ready to use immediately
    conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)");
    
    conn.close();
}
```

#### In-Memory Database

```vala
using InvercargillSql;

void in_memory_example() throws SqlError {
    // Both forms are equivalent
    var conn1 = ConnectionFactory.create("sqlite::memory:");
    var conn2 = ConnectionFactory.create("sqlite://:memory:");
    
    conn1.open();
    conn2.open();
    
    // Each is a separate in-memory database
    // Data is NOT shared between them
}
```

#### Read-Only Connection

```vala
using InvercargillSql;

void read_only_example() throws SqlError {
    // Open in read-only mode using query string option
    var conn = ConnectionFactory.create_and_open(
        "sqlite:///data/archive.sqlite?mode=ro"
    );
    
    // This would throw an error - database is read-only
    // conn.execute("INSERT INTO table VALUES (1, 'test')");
}
```

#### Async Connection Creation

```vala
using InvercargillSql;

async void async_example() throws SqlError {
    // Create and open asynchronously
    var conn = yield ConnectionFactory.create_and_open_async(
        "sqlite:///large_database.sqlite"
    );
    
    // Connection ready for async operations
    var results = yield conn.create_command("SELECT * FROM data")
                           .execute_query_async();
    
    conn.close();
}
```

### Extending for New Database Types

To add support for a new database (e.g., PostgreSQL):

#### 1. Create a Provider Implementation

```vala
namespace InvercargillSql {
    
    public class PostgresProvider : Object, ConnectionProvider {
        
        public Connection create_connection(ConnectionString conn_str) throws SqlError {
            // Extract connection parameters
            string host = conn_str.host ?? "localhost";
            uint16 port = conn_str.port ?? 5432;
            string database = conn_str.database;
            string? user = conn_str.user;
            string? password = conn_str.password;
            
            // Apply options (sslmode, connect_timeout, etc.)
            var sslmode = conn_str.get_option("sslmode") ?? "prefer";
            
            // Create the connection (requires PostgresConnection implementation)
            return new PostgresConnection(host, port, database, user, password) {
                ssl_mode = sslmode
            };
        }
    }
}
```

#### 2. Register the Provider

```vala
// At application startup
ConnectionFactory.register_provider("postgresql", new PostgresProvider());
ConnectionFactory.register_provider("postgres", new PostgresProvider());
```

#### 3. Use the New Provider

```vala
// Now you can use PostgreSQL connection strings
var conn = ConnectionFactory.create_and_open(
    "postgresql://myuser:mypass@db.example.com:5432/mydb?sslmode=require"
);
```

### File Structure Addition

```
src/
├── connection-string.vala       # ConnectionString parsing class
├── connection-factory.vala      # ConnectionFactory static class
├── providers/
│   ├── connection-provider.vala # ConnectionProvider interface
│   └── sqlite-provider.vala     # SQLite provider implementation
└── ...
```

### Design Rationale

1. **Simple API**: Single `ConnectionFactory.create()` method for all database types
2. **Extensible**: New databases added via `ConnectionProvider` interface without modifying factory
3. **Standard Format**: URI format is familiar and widely used (JDBC, PDO, etc.)
4. **Immutable Parsing**: `ConnectionString` is immutable after parsing for thread safety
5. **No Gee Dependency**: Uses Invercargill `Dictionary<string, string>` for options
6. **Async Support**: All operations have async variants
7. **SQLite Special Cases**: Handles `:memory:` and relative paths correctly

## Summary

This architecture provides:

1. **Backend Abstraction** - Interfaces allow swapping database implementations
2. **Type Safety** - Integration with Invercargill's Element type system
3. **Async Support** - Non-blocking operations for GUI applications
4. **Fluent API** - Chainable `with_parameter<T>()` methods for clean code
5. **Properties Integration** - Parameters use standard `Invercargill.Properties` interface
6. **Lazy Enumeration** - Results returned as `Enumerable<Properties>` for memory efficiency
7. **No Gee Dependency** - Uses Invercargill collections exclusively
8. **Testability** - Interfaces enable easy mocking and testing
9. **Connection String Factory** - URI-based connection creation with extensible provider system