# Invercargill-Sql

A Vala SQL library with tight integration into the [Invercargill](https://github.com/example/invercargill) ecosystem, providing a generic database abstraction layer with lazy enumeration support.

## Features

- **Generic Engine Interface**: Abstract away specific SQL server implementations
- **SQLite Support**: First-class SQLite implementation included (internal)
- **Invercargill Integration**: Query results returned as `Enumerable<Properties>` for lazy enumeration
- **Parameterized Queries**: Fluent API for type-safe parameter binding
- **Async Support**: All operations have async variants for non-blocking I/O
- **Transaction Support**: Full transaction management with commit/rollback
- **Connection String Support**: Create connections using URI-style connection strings

## Dependencies

- `glib-2.0`
- `gobject-2.0`
- `gio-2.0`
- `invercargill-1`
- `sqlite3` (internal dependency only)

## Building

```bash
meson setup build
meson compile -C build
```

## Running Tests

```bash
meson test -C build
```

## Installation

```bash
meson install -C build
```

## Usage

### Basic Connection

Use `ConnectionFactory` to create connections - no need to reference any database-specific types:

```vala
using InvercargillSql;

void main() throws SqlError {
    // Create and open a connection using a connection string
    var conn = ConnectionFactory.create_and_open("sqlite:///mydb.db");
    
    // Or create and open separately
    var conn2 = ConnectionFactory.create("sqlite:///another.db");
    conn2.open();
    
    // Execute commands...
    
    conn.close();
    conn2.close();
}
```

### Connection String Formats

The library supports URI-style connection strings:

```vala
// SQLite connections
"sqlite:///absolute/path/to/db.sqlite"     // Absolute path
"sqlite://./relative/path.db"              // Relative path
"sqlite::memory:"                          // In-memory database
"sqlite://:memory:"                        // In-memory database (URI form)
"sqlite:///db.sqlite?mode=ro"              // Read-only mode
"sqlite:///db.sqlite?mode=rw"              // Read-write (must exist)
"sqlite:///db.sqlite?mode=rwc"             // Read-write, create if needed (default)
```

### Creating Tables and Inserting Data

```vala
// Create a table
conn.create_command("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT
    )
""").execute_non_query();

// Insert data with parameters
conn.create_command("INSERT INTO users (name, email) VALUES (:name, :email)")
    .with_parameter("name", "Alice")
    .with_parameter("email", "alice@example.com")
    .execute_non_query();
```

### Querying Data

```vala
// Execute a query - results are lazily enumerated
var results = conn.create_command("SELECT * FROM users")
    .execute_query();

// Enumerate results
foreach (var row in results) {
    // Access columns by name using the Properties interface
    string? name = row.get("name")?.as_string_or_null();
    string? email = row.get("email")?.as_string_or_null();
    int64? id = row.get("id")?.as<int64?>();
    
    print(@"User: $name ($email)\n");
}
```

### Parameterized Queries

The library supports fluent parameter binding:

```vala
// Chain parameter bindings
var cmd = conn.create_command(
    "SELECT * FROM users WHERE name = :name AND active = :active"
);
cmd.with_parameter("name", "Bob")
   .with_parameter("active", true);

var results = cmd.execute_query();
```

### Transactions

```vala
// Start a transaction
var txn = conn.begin_transaction();

try {
    conn.create_command("INSERT INTO users (name) VALUES ('Charlie')")
        .execute_non_query();
    conn.create_command("INSERT INTO users (name) VALUES ('Diana')")
        .execute_non_query();
    
    // Commit if all succeeds
    txn.commit();
} catch (SqlError e) {
    // Rollback on error
    txn.rollback();
    warning("Transaction failed: %s", e.message);
}
```

### Async Operations

All database operations have async variants:

```vala
async void query_async(Connection conn) throws SqlError {
    // Open connection asynchronously
    yield conn.open_async();
    
    // Execute query asynchronously
    var results = yield conn.execute_query_async(
        "SELECT * FROM users WHERE active = :active",
        props => props.set_native("active", true)
    );
    
    foreach (var row in results) {
        print(@"Found: %s\n", row.get("name")?.as_string_or_null() ?? "null");
    }
    
    // Close asynchronously
    yield conn.close_async();
}
```

### Working with Results

Results are returned as `Enumerable<Properties>`, giving you access to all Invercargill LINQ-style operations:

```vala
var results = conn.create_command("SELECT * FROM users")
    .execute_query();

// Filter results
var activeUsers = results.where(row => row.get("active")?.as<bool>() ?? false);

// Project to different types
var names = results.select(row => row.get("name")?.as_string_or_null() ?? "unknown");

// Cache results to prevent re-enumeration
var cachedResults = results.cache();

// Convert to array
var array = results.to_array();
```

## Architecture

The library follows a layered architecture with clean separation between public interfaces and internal implementations:

```
┌─────────────────────────────────────────┐
│           User Application               │
├─────────────────────────────────────────┤
│         Public API Layer                 │
│   ConnectionFactory  ConnectionString    │
│   Connection  Command  Transaction       │
│   ConnectionFlags  SqlError              │
├─────────────────────────────────────────┤
│         Internal Implementation          │
│   SqliteProvider  SqliteConnection       │
│   SqliteCommand  SqliteResultEnumerable  │
├─────────────────────────────────────────┤
│         Native Drivers                   │
│          SQLite (via VAPI)               │
└─────────────────────────────────────────┘
```

### Key Public Interfaces

- **`ConnectionFactory`**: Static factory for creating connections from connection strings
- **`ConnectionString`**: Parsed connection string with scheme, host, database, and options
- **`Connection`**: Database connection management
- **`Command`**: SQL command execution with parameter binding
- **`Transaction`**: Transaction lifecycle management
- **`ConnectionFlags`**: Database-agnostic connection flags
- **`SqlError`**: Error domain for all SQL-related errors

### Internal Implementations

The following classes are internal to the library and should not be referenced directly:

- `SqliteProvider`: SQLite connection provider
- `SqliteConnection`: SQLite-specific connection handling
- `SqliteCommand`: SQLite statement wrapper with parameter binding
- `SqliteResultEnumerable`: Lazy enumeration over SQLite result sets
- `SqliteTransaction`: SQLite transaction management

## Error Handling

The library uses `SqlError` for all error conditions:

```vala
public errordomain SqlError {
    CONNECTION_FAILED,       // Failed to connect to database
    CONNECTION_CLOSED,       // Operation on closed connection
    INVALID_CONNECTION_STRING, // Malformed connection string
    PREPARATION_FAILED,      // SQL syntax error
    EXECUTION_FAILED,        // Runtime execution error
    INVALID_PARAMETER,       // Invalid parameter name or type
    TRANSACTION_FAILED,      // Transaction error
    TYPE_ERROR,              // Type conversion error
    GENERAL_ERROR            // Other errors
}
```