Invercargill-Sql/plans/examples-plan.md

Examples Directory Plan

This document outlines the structure and implementation plan for an examples directory containing a comprehensive demo program showcasing the Invercargill-Sql ORM features.

Overview

The examples directory will contain a single executable target that demonstrates all high-level ORM features through a cohesive demo application. The example uses a simple e-commerce domain model with users, products, and orders.

Directory Structure

examples/
├── meson.build              # Build configuration for demo executable
├── entities/
│   ├── user.vala            # User entity
│   ├── product.vala         # Product entity
│   └── order.vala           # Order entity
├── projections/
│   ├── user-summary.vala    # Simple projection example
│   ├── order-detail.vala    # Join projection example
│   └── sales-report.vala    # Aggregate projection example
├── migrations/
│   ├── v001-create-users.vala
│   ├── v002-create-products.vala
│   └── v003-create-orders.vala
└── demo.vala                # Main demo program

Domain Model

Entities

Each entity class includes a static configure_mapper method that encapsulates its ORM mapping configuration.

User

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 User() {
        name = "";
        email = "";
    }
    
    public static void configure_mapper(EntityMapperBuilder<User> b) {
        b.table("users")
            .column<int64?>("id", u => u.id, (u, v) => u.id = v)
            .column<string>("name", u => u.name, (u, v) => u.name = v)
            .column<string>("email", u => u.email, (u, v) => u.email = v)
            .column<int64?>("age", u => u.age, (u, v) => u.age = v)
            .column<bool?>("is_active", u => u.is_active, (u, v) => u.is_active = v);
    }
}

Product

public class Product : Object {
    public int64 id { get; set; }
    public string name { get; set; }
    public string category { get; set; }
    public double price { get; set; }
    public int64 stock { get; set; }
    
    public Product() {
        name = "";
        category = "";
    }
    
    public static void configure_mapper(EntityMapperBuilder<Product> b) {
        b.table("products")
            .column<int64?>("id", p => p.id, (p, v) => p.id = v)
            .column<string>("name", p => p.name, (p, v) => p.name = v)
            .column<string>("category", p => p.category, (p, v) => p.category = v)
            .column<double?>("price", p => p.price, (p, v) => p.price = v)
            .column<int64?>("stock", p => p.stock, (p, v) => p.stock = v);
    }
}

Order

public class Order : Object {
    public int64 id { get; set; }
    public int64 user_id { get; set; }
    public int64 product_id { get; set; }
    public int64 quantity { get; set; }
    public double total { get; set; }
    public string status { get; set; }
    public DateTime? created_at { get; set; }
    
    public Order() {
        status = "";
    }
    
    public static void configure_mapper(EntityMapperBuilder<Order> b) {
        b.table("orders")
            .column<int64?>("id", o => o.id, (o, v) => o.id = v)
            .column<int64?>("user_id", o => o.user_id, (o, v) => o.user_id = v)
            .column<int64?>("product_id", o => o.product_id, (o, v) => o.product_id = v)
            .column<int64?>("quantity", o => o.quantity, (o, v) => o.quantity = v)
            .column<double?>("total", o => o.total, (o, v) => o.total = v)
            .column<string>("status", o => o.status, (o, v) => o.status = v)
            .column<DateTime?>("created_at", o => o.created_at, (o, v) => o.created_at = v);
    }
}

Projections

Each projection class includes a static configure_projection method that encapsulates its projection definition.

UserSummary

Simple projection demonstrating basic field selection.

public class UserSummary : Object {
    public int64 user_id { get; set; }
    public string user_name { get; set; }
    public string email { get; set; }
    
    public UserSummary() {
        user_name = "";
        email = "";
    }
    
    public static void configure_projection(ProjectionBuilder<UserSummary> p) {
        p.source<User>("u")
            .select<int64?>("user_id", "u.id", (x, v) => x.user_id = v)
            .select<string>("user_name", "u.name", (x, v) => x.user_name = v)
            .select<string>("email", "u.email", (x, v) => x.email = v);
    }
}

OrderDetail

Join projection demonstrating entity relationships.

public class OrderDetail : Object {
    public int64 order_id { get; set; }
    public string user_name { get; set; }
    public string product_name { get; set; }
    public int64 quantity { get; set; }
    public double total { get; set; }
    public string status { get; set; }
    
    public OrderDetail() {
        user_name = "";
        product_name = "";
        status = "";
    }
    
    public static void configure_projection(ProjectionBuilder<OrderDetail> p) {
        p.source<User>("u")
            .join<Order>("o", "u.id == o.user_id")
            .join<Product>("p", "o.product_id == p.id")
            .select<int64?>("order_id", "o.id", (x, v) => x.order_id = v)
            .select<string>("user_name", "u.name", (x, v) => x.user_name = v)
            .select<string>("product_name", "p.name", (x, v) => x.product_name = v)
            .select<int64?>("quantity", "o.quantity", (x, v) => x.quantity = v)
            .select<double?>("total", "o.total", (x, v) => x.total = v)
            .select<string>("status", "o.status", (x, v) => x.status = v);
    }
}

SalesReport

Aggregate projection demonstrating GROUP BY and aggregate functions.

public class SalesReport : Object {
    public string category { get; set; }
    public int64 total_orders { get; set; }
    public double total_revenue { get; set; }
    public double avg_order_value { get; set; }
    
    public SalesReport() {
        category = "";
    }
    
    public static void configure_projection(ProjectionBuilder<SalesReport> p) {
        p.source<Order>("o")
            .join<Product>("p", "o.product_id == p.id")
            .group_by("p.category")
            .select<string>("category", "p.category", (x, v) => x.category = v)
            .select<int64?>("total_orders", "COUNT(o.id)", (x, v) => x.total_orders = v)
            .select<double?>("total_revenue", "SUM(o.total)", (x, v) => x.total_revenue = v)
            .select<double?>("avg_order_value", "AVG(o.total)", (x, v) => x.avg_order_value = v);
    }
}

Migrations

V001_CreateUsers

Creates the users table with indexes.

V002_CreateProducts

Creates the products table with category index.

V003_CreateOrders

Creates the orders table with foreign key indexes.

Demo Program Structure

The main demo program demo.vala is organized into clear sections:

1. Setup and Migration

• Create in-memory SQLite database
• Register migrations and run to latest
• Register entity mappers using static configure_mapper methods
• Register projection definitions using static configure_projection methods

2. Insert Operations

• Insert sample users
• Insert sample products
• Insert sample orders

3. Query Operations

• Query all entities
• Query with where clause
• Query with order by
• Query with limit/offset pagination

4. Update Operations

• Update single entity
• Verify update with query

5. Delete Operations

• Delete entity by ID
• Verify deletion

6. Projection Queries

• Simple projection query
• Projection with join
• Projection with aggregates and GROUP BY
• Projection with where clause
• Projection with order by

Command Line Interface

The demo program accepts an optional connection string argument:

void main(string[] args) {
    string connection_string = args.length > 1
        ? args[1]
        : "sqlite::memory:";
    
    // ... rest of demo
}

Usage examples:

# Default: in-memory database
./orm-demo

# Custom SQLite file
./orm-demo sqlite:///path/to/database.sqlite

# Read-only connection
./orm-demo sqlite:///data/app.sqlite?mode=ro

Registration Pattern

Using the static configuration methods, registration becomes clean and self-documenting:

// Entity registration using static configure_mapper method
session.register_with_schema<User>("users", User.configure_mapper);
session.register_with_schema<Product>("products", Product.configure_mapper);
session.register_with_schema<Order>("orders", Order.configure_mapper);

// Projection registration using static configure_projection method
session.register_projection<UserSummary>(UserSummary.configure_projection);
session.register_projection<OrderDetail>(OrderDetail.configure_projection);
session.register_projection<SalesReport>(SalesReport.configure_projection);

Build Configuration

The examples/meson.build file:

# Demo executable
demo_sources = files(
    'demo.vala',
    'entities/user.vala',
    'entities/product.vala',
    'entities/order.vala',
    'projections/user-summary.vala',
    'projections/order-detail.vala',
    'projections/sales-report.vala',
    'migrations/v001-create-users.vala',
    'migrations/v002-create-products.vala',
    'migrations/v003-create-orders.vala'
)

demo_exe = executable('orm-demo', demo_sources,
    dependencies: [dependencies, invercargill_sql_dep],
    link_with: invercargill_sql
)

Implementation Checklist

• Create examples/ directory structure
• Create examples/meson.build
• Create entity files:
  • examples/entities/user.vala
  • examples/entities/product.vala
  • examples/entities/order.vala
• Create projection files:
  • examples/projections/user-summary.vala
  • examples/projections/order-detail.vala
  • examples/projections/sales-report.vala
• Create migration files:
  • examples/migrations/v001-create-users.vala
  • examples/migrations/v002-create-products.vala
  • examples/migrations/v003-create-orders.vala
• Create main demo program examples/demo.vala
• Update src/meson.build to include examples subdirectory

Code Style Guidelines

The example code should:

1. Be self-documenting - Use clear variable and function names
2. Minimize comments - Let the code speak for itself
3. Use section headers - Brief print statements to indicate demo sections
4. Follow fluent API patterns - Chain method calls where appropriate
5. Handle errors appropriately - Show proper error handling patterns
6. Be runnable - Complete program that executes without modification

Expected Output

When run, the demo should produce output similar to:

=== Invercargill-Sql ORM Demo ===

--- Running Migrations ---
Applied migration: V001_CreateUsers (version 1)
Applied migration: V002_CreateProducts (version 2)
Applied migration: V003_CreateOrders (version 3)
Current database version: 3

--- Inserting Data ---
Inserted user: Alice (ID: 1)
Inserted user: Bob (ID: 2)
Inserted user: Charlie (ID: 3)
Inserted product: Widget (ID: 1)
Inserted product: Gadget (ID: 2)
Inserted product: Doohickey (ID: 3)
Inserted 5 orders

--- Querying Entities ---
All users: 3
Users over 25: 2
Users ordered by age: Bob(22), Alice(30), Charlie(35)

--- Updating Data ---
Updated Alice's age: 31
Verified update: Alice is now 31

--- Deleting Data ---
Deleted user: Bob
Remaining users: 2

--- Querying Projections ---
User summaries: 2
Order details: 5
Sales by category: 3

=== Demo Complete ===