Invercargill-Sql/examples/demo.vala

using Invercargill;
using Invercargill.DataStructures;
using Invercargill.Expressions;
using InvercargillSql;
using InvercargillSql.Orm;
using InvercargillSql.Orm.Projections;
using InvercargillSql.Migrations;
using InvercargillSql.Dialects;

/**
 * Invercargill-Sql ORM Demo Application
 *
 * This demo showcases the main features of the Invercargill-Sql ORM:
 * - Database migrations
 * - Entity mapping and CRUD operations
 * - Projection queries (simple, join, aggregate)
 */
public int main(string[] args) {
    print("=== Invercargill-Sql ORM Demo ===\n\n");
    
    // Allow optional connection string argument
    string connection_string = args.length > 1
        ? args[1]
        : "sqlite::memory:";
    
    try {
        // Create and open database connection
        var conn = ConnectionFactory.create_and_open(connection_string);

        // Pick the dialect from the connection string scheme so the same
        // demo runs unchanged against SQLite or PostgreSQL.
        bool is_pg = connection_string.has_prefix("postgres://")
            || connection_string.has_prefix("postgresql://");
        var dialect = is_pg
            ? DialectFactory.create("postgresql")
            : DialectFactory.create("sqlite");
        if (dialect == null) {
            dialect = new SqliteDialect();
        }
        var registry = new TypeRegistry();
        
        // --- Running Migrations ---
        print("--- Running Migrations ---\n");
        run_migrations(conn, dialect);
        
        // --- Registering Entity Mappers ---
        print("\n--- Registering Entity Mappers ---\n");
        register_entities(registry, conn, dialect);
        
        // --- Registering Projection Definitions ---
        print("Registering projection definitions...\n");
        register_projections(registry);
        
        // Create session with registry
        var session = new OrmSession(conn, registry, dialect);
        
        // --- Inserting Data ---
        print("\n--- Inserting Data ---\n");
        insert_sample_data(session);
        
        // --- Querying Entities ---
        print("\n--- Querying Entities ---\n");
        demonstrate_entity_queries(session);
        
        // --- Updating Data ---
        print("\n--- Updating Data ---\n");
        demonstrate_update(session);
        
        // --- Deleting Data ---
        print("\n--- Deleting Data ---\n");
        demonstrate_delete(session);
        
        // --- Querying Projections ---
        print("\n--- Querying Projections ---\n");
        demonstrate_projection_queries(session);
        
        print("\n=== Demo Complete ===\n");
        
        conn.close();
        return 0;
        
    } catch (Error e) {
        printerr("\n=== Demo failed: %s ===\n", e.message);
        return 1;
    }
}

void run_migrations(Connection conn, SqlDialect dialect) throws SqlError {
    var runner = new MigrationRunner(conn, dialect);
    
    // Register migrations
    runner.register_migration(new V001_CreateUsers());
    runner.register_migration(new V002_CreateProducts());
    runner.register_migration(new V003_CreateOrders());
    
    // Run all pending migrations
    runner.migrate_to_latest();
    
    // Get applied migrations count
    var applied = runner.get_applied_migrations();
    print("Applied %d migrations\n", (int)applied.length);
}

void register_entities(TypeRegistry registry, Connection conn, SqlDialect dialect) throws SqlError {
    // Register User entity with schema introspection
    registry.register_with_schema<User>("users", conn, dialect, User.configure_mapper);
    print("Registered User entity\n");
    
    // Register Product entity with schema introspection
    registry.register_with_schema<Product>("products", conn, dialect, Product.configure_mapper);
    print("Registered Product entity\n");
    
    // Register Order entity with schema introspection
    registry.register_with_schema<Order>("orders", conn, dialect, Order.configure_mapper);
    print("Registered Order entity\n");
}

void register_projections(TypeRegistry registry) throws ProjectionError {
    // Register projections using static configure_projection methods
    var user_summary_builder = new ProjectionBuilder<UserSummary>(registry);
    UserSummary.configure_projection(user_summary_builder);
    registry.register_projection<UserSummary>(user_summary_builder.build());
    
    var order_detail_builder = new ProjectionBuilder<OrderDetail>(registry);
    OrderDetail.configure_projection(order_detail_builder);
    registry.register_projection<OrderDetail>(order_detail_builder.build());
    
    var sales_report_builder = new ProjectionBuilder<SalesReport>(registry);
    SalesReport.configure_projection(sales_report_builder);
    registry.register_projection<SalesReport>(sales_report_builder.build());
}

void insert_sample_data(OrmSession session) throws SqlError {
    // Insert users
    var alice = new User();
    alice.name = "Alice";
    alice.email = "alice@example.com";
    alice.age = 30;
    alice.is_active = true;
    session.insert(alice);
    print("Inserted user: %s (ID: %lld)\n", alice.name, alice.id);
    
    var bob = new User();
    bob.name = "Bob";
    bob.email = "bob@example.com";
    bob.age = 22;
    bob.is_active = true;
    session.insert(bob);
    print("Inserted user: %s (ID: %lld)\n", bob.name, bob.id);
    
    var charlie = new User();
    charlie.name = "Charlie";
    charlie.email = "charlie@example.com";
    charlie.age = 35;
    charlie.is_active = false;
    session.insert(charlie);
    print("Inserted user: %s (ID: %lld)\n", charlie.name, charlie.id);
    
    // Insert products
    var widget = new Product();
    widget.name = "Widget";
    widget.category = "Electronics";
    widget.price = 29.99;
    widget.stock = 100;
    session.insert(widget);
    print("Inserted product: %s (ID: %lld)\n", widget.name, widget.id);
    
    var gadget = new Product();
    gadget.name = "Gadget";
    gadget.category = "Electronics";
    gadget.price = 49.99;
    gadget.stock = 50;
    session.insert(gadget);
    print("Inserted product: %s (ID: %lld)\n", gadget.name, gadget.id);
    
    var doohickey = new Product();
    doohickey.name = "Doohickey";
    doohickey.category = "Accessories";
    doohickey.price = 9.99;
    doohickey.stock = 200;
    session.insert(doohickey);
    print("Inserted product: %s (ID: %lld)\n", doohickey.name, doohickey.id);
    
    // Insert orders
    int order_count = 0;
    
    // Alice orders 2 Widgets
    var order1 = create_order(alice.id, widget.id, 2, 59.98, "completed");
    session.insert(order1);
    order_count++;
    
    // Alice orders 1 Gadget
    var order2 = create_order(alice.id, gadget.id, 1, 49.99, "completed");
    session.insert(order2);
    order_count++;
    
    // Bob orders 3 Doohickeys
    var order3 = create_order(bob.id, doohickey.id, 3, 29.97, "pending");
    session.insert(order3);
    order_count++;
    
    // Charlie orders 1 Widget and 2 Gadgets
    var order4 = create_order(charlie.id, widget.id, 1, 29.99, "completed");
    session.insert(order4);
    order_count++;
    
    var order5 = create_order(charlie.id, gadget.id, 2, 99.98, "completed");
    session.insert(order5);
    order_count++;
    
    print("Inserted %d orders\n", order_count);
}

Order create_order(int64 user_id, int64 product_id, int64 quantity, double total, string status) {
    var order = new Order();
    order.user_id = user_id;
    order.product_id = product_id;
    order.quantity = quantity;
    order.total = total;
    order.status = status;
    order.created_at = new DateTime.now_utc();
    return order;
}

void demonstrate_entity_queries(OrmSession session) throws SqlError {
    // Query all users
    var all_users = session.query<User>().materialise();
    print("All users: %d\n", (int)all_users.length);
    
    // Query users over 25
    var users_over_25 = session.query<User>()
        .where(expr("age > $0", new NativeElement<int?>(25)))
        .materialise();
    print("Users over 25: %d\n", (int)users_over_25.length);
    
    // Query active users ordered by age
    var active_users = session.query<User>()
        .where(expr("is_active == $0", new NativeElement<bool?>(true)))
        .order_by(expr("age"))
        .materialise();
    
    print("Active users ordered by age: ");
    var first = true;
    foreach (var user in active_users) {
        if (!first) print(", ");
        print("%s(%lld)", user.name, user.age);
        first = false;
    }
    print("\n");
    
    // Query with pagination
    var paged_users = session.query<User>()
        .order_by(expr("id"))
        .limit(2)
        .offset(1)
        .materialise();
    print("Users (page 2, size 2): %d\n", (int)paged_users.length);
}

void demonstrate_update(OrmSession session) throws SqlError {
    // Find Alice by ID
    var alice = session.query<User>()
        .where(expr("id == $0", new NativeElement<int64?>(1)))
        .first();
    
    if (alice != null) {
        // Update Alice's age
        alice.age = 31;
        session.update(alice);
        print("Updated %s's age to %lld\n", alice.name, alice.age);
        
        // Verify the update
        var verified = session.query<User>()
            .where(expr("id == $0", new NativeElement<int64?>(1)))
            .first();
        print("Verified update: %s is now %lld\n", verified.name, verified.age);
    }
}

void demonstrate_delete(OrmSession session) throws SqlError {
    // Find Bob by name (using age as a proxy since string comparison in expressions may not work)
    var bob = session.query<User>()
        .where(expr("age == $0", new NativeElement<int64?>(22)))
        .first();
    
    if (bob != null) {
        print("Deleted user: %s\n", bob.name);
        session.delete(bob);
        
        // Verify deletion
        var remaining = session.query<User>().materialise();
        print("Remaining users: %d\n", (int)remaining.length);
    }
}

void demonstrate_projection_queries(OrmSession session) throws SqlError, ProjectionError {
    // Simple projection - User summaries
    print("\n--- User Summaries ---\n");
    var user_summaries = session.query<UserSummary>()
        .order_by(expr("user_name"))
        .materialise();
    
    foreach (var summary in user_summaries) {
        print("  [%lld] %s <%s>\n", summary.user_id, summary.user_name, summary.email);
    }
    print("Total user summaries: %d\n", (int)user_summaries.length);
    
    // Join projection - Order details
    print("\n--- Order Details ---\n");
    var order_details = session.query<OrderDetail>()
        .order_by(expr("order_id"))
        .materialise();
    
    foreach (var detail in order_details) {
        print("  [%lld] %s ordered %dx %s ($%.2f) - %s\n",
            detail.order_id, detail.user_name, detail.quantity,
            detail.product_name, detail.total, detail.status);
    }
    print("Total order details: %d\n", (int)order_details.length);
    
    // Aggregate projection - Sales by category
    print("\n--- Sales by Category ---\n");
    var sales_reports = session.query<SalesReport>()
        .order_by_desc(expr("total_revenue"))
        .materialise();
    
    foreach (var report in sales_reports) {
        print("  %s: %lld orders, $%.2f revenue, $%.2f avg\n",
            report.category, report.total_orders,
            report.total_revenue, report.avg_order_value);
    }
    print("Total categories: %d\n", (int)sales_reports.length);
    
    // Projection with where clause
    print("\n--- Completed Orders ---\n");
    var completed_orders = session.query<OrderDetail>()
        .where(expr("status == $0", new NativeElement<string>("completed")))
        .materialise();
    print("Completed orders: %d\n", (int)completed_orders.length);
}