# 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 & PostgreSQL Support**: First-class SQLite and PostgreSQL implementations; the ORM, migrations and projections run unchanged over either backend
- **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)
- `libpq` (PostgreSQL client library, for the postgresql:// adapter)

## Building

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

## Running Tests

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

Database-backed tests run twice: over in-memory SQLite (always) and over
PostgreSQL when a server is reachable. The PostgreSQL runs go through
`scripts/run-postgres-tests.sh`, which brings up (or reuses) a local server
on socket `/tmp/pgrun`, port 5433, and skips (exit 77) when it cannot, so
default builds stay green without PostgreSQL. Point the suites at another
server with `PG_TEST_CONN`, e.g.
`PG_TEST_CONN='postgresql://user:pass@db.example.com:5432/mydb' meson test -C build`.

Each PostgreSQL test program isolates itself in a disposable schema
(`t_<seed>_<n>`), so programs can share one server; leftover schemas are
cleaned up on the next run. The ORM, migration and projection suites are
backend-parameterised through `src/tests/test-backend.vala`
(`TestBackendContext`), exercising the same test code against both dialects.

The ORM demo accepts a connection string argument and picks its dialect from
the scheme, e.g. `./build/examples/orm-demo "postgresql://postgres@/sqltest?host=/tmp/pgrun&port=5433"`.

## 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)
```

### PostgreSQL Connections

The library also ships a PostgreSQL adapter built on libpq (scheme
`postgresql`, with `postgres` accepted as an alias). Both providers are
registered automatically — no extra setup is needed:

```vala
// TCP connection
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgresql://user:password@localhost:5432/mydb"
);

// Unix socket directory via the host option
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgresql://user@/run/postgresql/mydb?host=/run/postgresql"
);

// Short scheme alias, explicit socket dir and port
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgres://postgres@/mydb?host=/tmp/pgrun&port=5433"
);

// Extra libpq options (connect_timeout, sslmode, application_name, ...)
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgresql://user@db.example.com:5432/mydb?sslmode=require&connect_timeout=10"
);
```

Notes on the adapter:

- Components left out of the URI are omitted from the conninfo handed to
  libpq, so the usual environment defaults (PGHOST, PGPORT, PGUSER,
  ~/.pgpass) still apply.
- Commands keep the same `:name` placeholder style as SQLite; the adapter
  translates them to libpq `$n` positions at execution time. Placeholders
  inside string literals, quoted identifiers, dollar-quoted strings and
  comments are ignored, as are PostgreSQL `::type` cast operators.
- Values are bound in text format. Booleans bind as `t`/`f`, DateTime as
  ISO 8601 text, and `Invercargill.BinaryData` as bytea hex (`\x...`),
  so `bytea` columns round-trip without casts.
- `connection.last_insert_rowid` is backed by `SELECT lastval()`, valid
  after any INSERT that used a sequence on that connection.
- `DialectFactory` also ships a PostgreSQL dialect: `DialectFactory.create("postgresql")`
  (or `"postgres"`) returns a shared `PostgresDialect` instance that generates
  named-parameter CRUD SQL, identity-column DDL, `information_schema`
  introspection and standard projection SELECTs. See `src/dialects/postgres-dialect.vala`.

### 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  │
│   PostgresProvider  PostgresConnection   │
│   PostgresCommand  PostgresResult...     │
├─────────────────────────────────────────┤
│         Native Drivers                   │
│   SQLite (via VAPI)   libpq (Postgres)   │
└─────────────────────────────────────────┘
```

### 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
- `PostgresProvider`: PostgreSQL connection provider (libpq)
- `PostgresConnection`: PostgreSQL-specific connection handling
- `PostgresCommand`: libpq command wrapper translating `:name` to `$n`
- `PostgresResultEnumerable`: Materialised enumeration over `PGresult`
- `PostgresTransaction`: PostgreSQL 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
}
```