ARCHITECTURE.md 54 KB
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
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:
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:
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:
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:
- Consistency: Same interface used for query results and parameters
- Element API: Parameter values accessed via standard
Elementmethods - Type Safety:
set_native<T>()handles automatic wrapping - Simplicity: No additional interfaces to maintain
Properties Interface Definition
From Invercargill core:
namespace Invercargill {
public interface Properties : Associative<string, Element> {
public abstract void set_native<T>(string key, T value) throws ElementError;
}
}
Parameter Usage Pattern
// 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:
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:
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:
- Extend
Enumerable<T> - Implement three abstract methods:
get_tracker()- Returns aTracker<T>for iterationpeek_count()- Returns the count if known, ornullif unknownget_info()- Returns metadata about the enumerable
Tracker Pattern
The Tracker<T> class is similar to an iterator but with a different API:
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:
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:
// 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:
public Invercargill.Enumerable<Invercargill.Properties> execute_query() throws SqlError {
_connection.ensure_open();
bind_parameters();
return new SqliteResultEnumerable(_stmt, _connection).cache();
}
Error Handling
SqlError Domain
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:
// 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
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
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
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
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
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
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
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/PostgresCommandfor PostgreSQLMysqlConnection/MysqlCommandfor MySQLMssqlConnection/MssqlCommandfor SQL Server
Connection Pooling
A ConnectionPool class could be added to manage multiple connections:
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:
// 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
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:
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:
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:
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:
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
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
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
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
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
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
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
// At application startup
ConnectionFactory.register_provider("postgresql", new PostgresProvider());
ConnectionFactory.register_provider("postgres", new PostgresProvider());
3. Use the New Provider
// 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
- Simple API: Single
ConnectionFactory.create()method for all database types - Extensible: New databases added via
ConnectionProviderinterface without modifying factory - Standard Format: URI format is familiar and widely used (JDBC, PDO, etc.)
- Immutable Parsing:
ConnectionStringis immutable after parsing for thread safety - No Gee Dependency: Uses Invercargill
Dictionary<string, string>for options - Async Support: All operations have async variants
- SQLite Special Cases: Handles
:memory:and relative paths correctly
Summary
This architecture provides:
- Backend Abstraction - Interfaces allow swapping database implementations
- Type Safety - Integration with Invercargill's Element type system
- Async Support - Non-blocking operations for GUI applications
- Fluent API - Chainable
with_parameter<T>()methods for clean code - Properties Integration - Parameters use standard
Invercargill.Propertiesinterface - Lazy Enumeration - Results returned as
Enumerable<Properties>for memory efficiency - No Gee Dependency - Uses Invercargill collections exclusively
- Testability - Interfaces enable easy mocking and testing
- Connection String Factory - URI-based connection creation with extensible provider system