Astrologue-Website/developer/invercargill-sql/index.html

<!doctype html>
<html lang="en-nz">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Invercargill-Sql — Astrologue Developer</title>
  <meta name="description" content="A SQL abstraction built on Invercargill data structures: provider-neutral connections, lazy Enumerable rows, fluent parameter binding, transactions, migrations and an ORM with entity mappers and projections. Free software, LGPL-3.0-or-later.">
  <meta name="color-scheme" content="light dark">
  <meta name="theme-color" content="#f2ebda" media="(prefers-color-scheme: light)">
  <meta name="theme-color" content="#0a0a12" media="(prefers-color-scheme: dark)">
  <link rel="icon" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 64 64'%3E%3Ccircle cx='32' cy='32' r='27' fill='none' stroke='%23a8801f' stroke-width='4'/%3E%3Cpath d='M32 8l6 18 18 6-18 6-6 18-6-18-18-6 18-6z' fill='%23a8801f'/%3E%3Ccircle cx='32' cy='32' r='4' fill='%23003078'/%3E%3C/svg%3E">
  <meta property="og:title" content="Invercargill-Sql — Astrologue Developer">
  <meta property="og:description" content="A generic SQL abstraction on Invercargill data structures: lazy Enumerable rows over a provider-neutral API, with migrations and an ORM.">
  <meta property="og:type" content="website">
  <link rel="stylesheet" href="../../assets/css/style.css">
</head>
<body>
  <a class="skip-link" href="#main">Skip to content</a>

  <header class="masthead">
    <div class="container masthead__inner">
      <a class="brand" href="../../">
        <img class="brand__logo brand__logo--light" src="../../assets/img/astrologue-light.png" width="392" height="89" alt="Astrologue">
        <img class="brand__logo brand__logo--dark" src="../../assets/img/astrologue-dark.png" width="392" height="89" alt="Astrologue">
      </a>
      <nav class="site-nav" aria-label="Primary">
        <ul class="site-nav__list">
          <li><a href="../../">Home</a></li>
          <li><a href="../../#news">News</a></li>
          <li><a href="../../#download">Download</a></li>
          <li><a href="../" aria-current="page">Developer</a></li>
          <li><a class="nav-cta" href="../../#download">Get Astrologue</a></li>
        </ul>
      </nav>
    </div>
  </header>

  <main id="main">

    <nav class="breadcrumb" aria-label="Breadcrumb">
      <div class="container">
        <ol>
          <li><a href="../../">Astrologue</a></li>
          <li><a href="../">Developer</a></li>
          <li aria-current="page">Invercargill-Sql</li>
        </ol>
      </div>
    </nav>

    <section class="docs-header">
      <div class="container">
        <p class="kicker"><span>Data · Rows, schemas and migrations</span></p>
        <h1>Invercargill-Sql</h1>
        <p class="lede">A generic SQL abstraction built on Invercargill data structures. Connections are provider-neutral, query results return as lazy Enumerable rows, and above that sits a migration system and an ORM with entity mappers and projections. SQLite is the engine beneath today.</p>
        <dl class="docs-facts">
          <div><dt>Version</dt> <dd><code>0.1</code></dd></div>
          <div><dt>Licence</dt> <dd>LGPL-3.0-or-later</dd></div>
          <div><dt>Package</dt> <dd><code>usm&nbsp;install&nbsp;invercargill-sql</code></dd></div>
          <div><dt>Language</dt> <dd>Vala</dd></div>
        </dl>
      </div>
    </section>

    <section class="band band--plain" id="docs">
      <div class="container docs-layout">

        <nav class="docs-toc" aria-label="On this page">
          <h2>On this page</h2>
          <ul>
            <li><a href="#overview">Overview</a></li>
            <li><a href="#features">Features</a></li>
            <li><a href="#installation">Installation</a></li>
            <li><a href="#usage">Usage &amp; documentation</a></li>
            <li><a href="#package">Package &amp; source</a></li>
          </ul>
        </nav>

        <div class="docs-prose">

          <header class="section-head" id="overview">
            <p class="kicker"><span>Overview</span></p>
            <h2>Why it exists</h2>
          </header>
          <p>Databases in Vala usually mean provider-specific APIs and eager row fetching. Invercargill-Sql takes the other road: a small public surface — <code>ConnectionFactory</code>, <code>Connection</code>, <code>Command</code>, <code>Transaction</code> — under which the concrete engine is an internal detail. Query results come back as <code>Enumerable&lt;Properties&gt;</code>, so a result set is not a materialised list you then re-shape but a lazy sequence you query with the same operations as everything else in the stack.</p>
          <p>On that ground stands the rest of the data story: a fluent migration system that builds and versions schemas, an ORM that maps GObject classes onto tables with schema introspection, and projections that shape joins and aggregates into purpose-built read models. Spry's user and permission tables are defined here, as migrations.</p>

          <header class="section-head" id="features">
            <p class="kicker"><span>Features</span></p>
            <h2>What it hands you</h2>
          </header>
          <ul class="docs-features">
            <li><strong>Generic engine interface.</strong> Abstract away specific SQL server implementations; SQLite ships as the first-class engine.</li>
            <li><strong>Lazy Enumerable rows.</strong> Query results return as <code>Enumerable&lt;Properties&gt;</code> — filter, project and cache them with Invercargill itself.</li>
            <li><strong>Connection strings.</strong> URI-style strings such as <code>sqlite::memory:</code> or <code>sqlite:///db.sqlite?mode=ro</code>, parsed into scheme, database and options.</li>
            <li><strong>Parameterized queries.</strong> A fluent API for type-safe parameter binding.</li>
            <li><strong>Transactions.</strong> Full commit/rollback management.</li>
            <li><strong>Async throughout.</strong> Every operation has an async variant for non-blocking I/O.</li>
            <li><strong>Migrations.</strong> A fluent builder for tables, columns, indexes and foreign keys, with dependency-aware ordering and <code>up</code>/<code>down</code> reversibility.</li>
            <li><strong>ORM.</strong> Entity mappers, sessions, typed queries and projections — including join and aggregate read models.</li>
          </ul>

          <header class="section-head" id="installation">
            <p class="kicker"><span>Installation</span></p>
            <h2>Getting the library</h2>
          </header>
          <figure class="code-plate">
            <pre><code>usm install invercargill
usm install invercargill-sql</code></pre>
            <figcaption>USM resolves SQLite and the other build dependencies itself.</figcaption>
          </figure>
          <p>From source:</p>
          <figure class="code-plate">
            <pre><code>git clone &lt;the Invercargill-Sql repository&gt;
cd Invercargill-Sql
meson setup build
ninja -C build
ninja -C build install</code></pre>
            <figcaption>The test suite runs with <code>meson test -C build</code>.</figcaption>
          </figure>
          <figure class="code-plate">
            <pre><code>valac --pkg invercargill-sql my-program.vala</code></pre>
            <figcaption>The binding pulls in its <code>.deps</code> chain automatically.</figcaption>
          </figure>

          <header class="section-head" id="usage">
            <p class="kicker"><span>Usage &amp; documentation</span></p>
            <h2>Connecting and querying</h2>
          </header>
          <p>Use <code>ConnectionFactory</code> to create connections — no database-specific types in sight:</p>
          <figure class="code-plate">
            <pre><code>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();
}</code></pre>
            <figcaption>From the README. Connection strings also cover <code>sqlite://./relative/path.db</code>, <code>sqlite::memory:</code> and read-only or read-write modes via <code>?mode=</code>.</figcaption>
          </figure>
          <p>DDL and inserts go through fluent commands with bound parameters:</p>
          <figure class="code-plate">
            <pre><code>// 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();</code></pre>
            <figcaption>From the README.</figcaption>
          </figure>
          <p>Query results are lazy — enumerate them, read columns by name, or keep querying:</p>
          <figure class="code-plate">
            <pre><code>var results = conn.create_command("SELECT * FROM users")
    .execute_query();

foreach (var row in results) {
    string? name  = row.get("name")?.as_string_or_null();
    string? email = row.get("email")?.as_string_or_null();
    int64?  id    = row.get("id")?.as&lt;int64?&gt;();

    print(@"User: $name ($email)\n");
}

// Results are Enumerable&lt;Properties&gt; — the whole toolkit applies
var activeUsers = results.where(row =&gt; row.get("active")?.as&lt;bool&gt;() ?? false);
var names       = results.select(row =&gt; row.get("name")?.as_string_or_null() ?? "unknown");
var cached      = results.cache();</code></pre>
            <figcaption>From the README — the second half shows Invercargill's modifiers applied directly to a result set.</figcaption>
          </figure>
          <p>Transactions commit or roll back as a unit, and every operation has an async twin:</p>
          <figure class="code-plate">
            <pre><code>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();

    txn.commit();
} catch (SqlError e) {
    txn.rollback();
    warning("Transaction failed: %s", e.message);
}</code></pre>
            <figcaption>From the README. Async: <code>conn.open_async()</code>, <code>conn.execute_query_async(...)</code>, <code>conn.close_async()</code>.</figcaption>
          </figure>

          <h3>Migrations — schemas as code</h3>
          <p>Subclass <code>Migration</code>, declare a namespace, serial and name, and build your schema with the fluent builder:</p>
          <figure class="code-plate">
            <pre><code>using InvercargillSql;
using InvercargillSql.Migrations;

public class V001_CreateUsers : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateUsers"; } }

    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("users", t =&gt; {
            t.column&lt;int64?&gt;("id")
                .primary_key()
                .auto_increment();
            t.column&lt;string&gt;("name")
                .not_null();
            t.column&lt;string&gt;("email")
                .unique();
            t.column&lt;int64?&gt;("age");
            t.column&lt;bool?&gt;("is_active");

            // Create index on email for fast lookups
            t.index("idx_users_email").on_column("email");
        });
    }

    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("users");
    }
}</code></pre>
            <figcaption>Verbatim from <code>examples/migrations/v001-create-users.vala</code>.</figcaption>
          </figure>
          <figure class="code-plate">
            <pre><code>var runner = new MigrationRunner(conn, dialect);
runner.register_migration(new V001_CreateUsers());
runner.register_migration(new V002_CreateProducts());
runner.register_migration(new V003_CreateOrders());

runner.migrate_to_latest();
var applied = runner.get_applied_migrations();</code></pre>
            <figcaption>From <code>examples/demo.vala</code> — the runner orders migrations and remembers what is applied.</figcaption>
          </figure>

          <h3>The ORM — entities, sessions, projections</h3>
          <p>Map a GObject class onto a table once; sessions do the rest:</p>
          <figure class="code-plate">
            <pre><code>public class User : Object {
    public int64 id { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public int64 age { get; set; }
    public bool is_active { get; set; }

    public static void configure_mapper(EntityMapperBuilder&lt;User&gt; b) {
        b.table("users")
            .column&lt;int64?&gt;("id", u =&gt; u.id, (u, v) =&gt; u.id = v)
            .column&lt;string&gt;("name", u =&gt; u.name, (u, v) =&gt; u.name = v)
            .column&lt;string&gt;("email", u =&gt; u.email, (u, v) =&gt; u.email = v)
            .column&lt;int64?&gt;("age", u =&gt; u.age, (u, v) =&gt; u.age = v)
            .column&lt;bool?&gt;("is_active", u =&gt; u.is_active, (u, v) =&gt; u.is_active = v);
    }
}</code></pre>
            <figcaption>From <code>examples/entities/user.vala</code> — the mapper introspects the live schema at registration.</figcaption>
          </figure>
          <figure class="code-plate">
            <pre><code>var registry = new TypeRegistry();
registry.register_with_schema&lt;User&gt;("users", conn, dialect, User.configure_mapper);
var session = new OrmSession(conn, registry, dialect);

// Insert
var alice = new User();
alice.name = "Alice";
alice.email = "alice@example.com";
session.insert(alice);

// Query with typed predicates and ordering
var users_over_25 = session.query&lt;User&gt;()
    .where(expr("age &gt; $0", new NativeElement&lt;int?&gt;(25)))
    .materialise();

var active_users = session.query&lt;User&gt;()
    .where(expr("is_active == $0", new NativeElement&lt;bool?&gt;(true)))
    .order_by(expr("age"))
    .materialise();

// Update and delete
alice.age = 31;
session.update(alice);
session.delete(alice);</code></pre>
            <figcaption>From <code>examples/demo.vala</code> — <code>expr()</code> builds parameterised predicates; pagination is <code>.limit(2).offset(1)</code>.</figcaption>
          </figure>
          <p>Projections complete the ORM: purpose-built read models — simple, joined and aggregate — registered per type and queried through the same sessions, as <code>UserSummary</code>, <code>OrderDetail</code> and <code>SalesReport</code> in the demo show.</p>
          <p>The repository's <code>examples/demo.vala</code> runs the whole story — migrations, registration, inserts, queries, updates, deletes, projections — against an in-memory database; its <code>docs/ARCHITECTURE.md</code> and <code>plans/</code> directory record how the layered design was reached.</p>

          <header class="section-head" id="package">
            <p class="kicker"><span>Package &amp; source</span></p>
            <h2>The ledger</h2>
          </header>
          <dl class="docs-package">
            <div><dt>Version</dt><dd><code>0.1</code> (from <code>MANIFEST.usm</code>)</dd></div>
            <div><dt>Licence</dt><dd>LGPL-3.0-or-later — free to run, study, share and improve; the LGPL keeps it free to link from any program.</dd></div>
            <div><dt>Package</dt><dd><code>usm install invercargill-sql</code></dd></div>
            <div><dt>Provides</dt><dd><code>libinvercargill-sql.so</code>, <code>invercargill-sql.h</code>, <code>invercargill-sql.vapi</code> + <code>.deps</code>, <code>invercargill_sql-1.0.gir</code>, <code>invercargill_sql-1.0.typelib</code>, <code>invercargill-sql.pc</code></dd></div>
            <div><dt>Needs at runtime</dt><dd>GLib, GObject, Gio, <code>libinvercargill-1.so</code> and <code>libsqlite3.so.0</code>.</dd></div>
            <div><dt>Source</dt><dd>The repository <code>Tilo15/Invercargill-Sql</code> on the Astrologue forge (git over ssh, port 222). Free software: the licence guarantees your right to the source.</dd></div>
          </dl>

          <nav class="docs-siblings" aria-label="Neighbours in the catalogue">
            <p>Its neighbours:</p>
            <ul>
              <li><a href="../inversion/">Inversion</a></li>
              <li><a href="../invercargill-sql-inversion/">Invercargill-Sql-Inversion</a></li>
              <li><a href="../spry/">Spry</a></li>
            </ul>
          </nav>

          <nav class="docs-pager" aria-label="Project pages">
            <a class="docs-pager__link docs-pager__link--prev" href="../inversion/">
              <span>Previous</span>
              <strong>Inversion</strong>
            </a>
            <p class="docs-pager__up"><a href="../">All eleven</a></p>
            <a class="docs-pager__link docs-pager__link--next" href="../invercargill-sql-inversion/">
              <span>Next in the catalogue</span>
              <strong>Invercargill-Sql-Inversion</strong>
            </a>
          </nav>

        </div>
      </div>
    </section>

  </main>

  <footer class="site-footer">
    <div class="container">
      <div class="footer-grid">
        <div class="foot-brand">
          <img class="brand__logo brand__logo--dark" src="../../assets/img/astrologue-dark.png" width="392" height="89" alt="Astrologue">
          <p>An operating system for change — kept for self sufficiency, resilience, and software freedom, so regular people can hold the power of general purpose computers in their own hands.</p>
        </div>
        <nav aria-label="The four freedoms">
          <p class="foot-title">The Four Freedoms</p>
          <ol class="freedoms">
            <li><strong>Run</strong> the software as you wish, for any purpose.</li>
            <li><strong>Study</strong> the source and change it to do your computing as you wish.</li>
            <li><strong>Share</strong> copies so you can help others.</li>
            <li><strong>Modify</strong> and publish your versions so everyone benefits.</li>
          </ol>
        </nav>
        <nav aria-label="Site">
          <p class="foot-title">Site</p>
          <ul class="foot-list">
            <li><a href="../../">Home</a></li>
            <li><a href="../../#news">News</a></li>
            <li><a href="../../#download">Download</a></li>
            <li><a href="../">Developer</a></li>
          </ul>
        </nav>
      </div>
      <p class="colophon">Set in Cinzel and EB Garamond (SIL Open Font License). Astrologue is free software, licensed GPL-3.0-or-later: take it, tend it, pass it on. The libraries are LGPL-3.0-or-later, so they remain free to link.</p>
    </div>
  </footer>
</body>
</html>