zcatsql/examples/basic.zig

//! Basic example demonstrating zcatsql usage
//!
//! This example shows:
//! - Opening a database
//! - Creating tables
//! - Inserting data with prepared statements
//! - Querying data
//! - Transactions

const std = @import("std");
const sqlite = @import("zcatsql");

pub fn main() !void {
    std.debug.print("zcatsql basic example\n", .{});
    std.debug.print("SQLite version: {s}\n\n", .{sqlite.version()});

    // Open an in-memory database
    var db = try sqlite.openMemory();
    defer db.close();

    // Enable foreign keys (recommended)
    try db.setForeignKeys(true);

    // Create tables
    try db.exec(
        \\CREATE TABLE users (
        \\    id INTEGER PRIMARY KEY,
        \\    name TEXT NOT NULL,
        \\    email TEXT UNIQUE,
        \\    created_at TEXT DEFAULT CURRENT_TIMESTAMP
        \\);
        \\
        \\CREATE TABLE posts (
        \\    id INTEGER PRIMARY KEY,
        \\    user_id INTEGER NOT NULL REFERENCES users(id),
        \\    title TEXT NOT NULL,
        \\    content TEXT
        \\);
    );

    std.debug.print("Tables created successfully\n\n", .{});

    // Insert users with prepared statement
    {
        var stmt = try db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
        defer stmt.finalize();

        const users = [_][2][]const u8{
            .{ "Alice", "alice@example.com" },
            .{ "Bob", "bob@example.com" },
            .{ "Charlie", "charlie@example.com" },
        };

        for (users) |user| {
            try stmt.bindText(1, user[0]);
            try stmt.bindText(2, user[1]);
            _ = try stmt.step();
            try stmt.reset();
            try stmt.clearBindings();
        }

        std.debug.print("Inserted {} users\n", .{db.totalChanges()});
    }

    // Insert posts
    {
        var stmt = try db.prepare("INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)");
        defer stmt.finalize();

        try stmt.bindInt(1, 1); // Alice
        try stmt.bindText(2, "Hello World");
        try stmt.bindText(3, "This is my first post!");
        _ = try stmt.step();
        try stmt.reset();
        try stmt.clearBindings();

        try stmt.bindInt(1, 1); // Alice
        try stmt.bindText(2, "Zig is awesome");
        try stmt.bindNull(3); // No content
        _ = try stmt.step();
        try stmt.reset();
        try stmt.clearBindings();

        try stmt.bindInt(1, 2); // Bob
        try stmt.bindText(2, "SQLite rocks");
        try stmt.bindText(3, "Zero dependencies!");
        _ = try stmt.step();
    }

    std.debug.print("Inserted posts\n\n", .{});

    // Query users
    std.debug.print("All users:\n", .{});
    std.debug.print("-" ** 50 ++ "\n", .{});
    {
        var stmt = try db.prepare("SELECT id, name, email FROM users ORDER BY name");
        defer stmt.finalize();

        while (try stmt.step()) {
            const id = stmt.columnInt(0);
            const name = stmt.columnText(1) orelse "(null)";
            const email = stmt.columnText(2) orelse "(null)";
            std.debug.print("  [{d}] {s} <{s}>\n", .{ id, name, email });
        }
    }

    // Query posts with JOIN
    std.debug.print("\nPosts with authors:\n", .{});
    std.debug.print("-" ** 50 ++ "\n", .{});
    {
        var stmt = try db.prepare(
            \\SELECT p.title, u.name, p.content
            \\FROM posts p
            \\JOIN users u ON p.user_id = u.id
            \\ORDER BY p.id
        );
        defer stmt.finalize();

        while (try stmt.step()) {
            const title = stmt.columnText(0) orelse "(null)";
            const author = stmt.columnText(1) orelse "(null)";
            const content = stmt.columnText(2) orelse "(no content)";
            std.debug.print("  \"{s}\" by {s}\n", .{ title, author });
            std.debug.print("    {s}\n", .{content});
        }
    }

    // Demonstrate transaction rollback
    std.debug.print("\nTransaction demo:\n", .{});
    std.debug.print("-" ** 50 ++ "\n", .{});
    {
        // Count before
        var count_stmt = try db.prepare("SELECT COUNT(*) FROM users");
        defer count_stmt.finalize();
        _ = try count_stmt.step();
        const count_before = count_stmt.columnInt(0);
        std.debug.print("  Users before transaction: {d}\n", .{count_before});

        // Start transaction and insert, then rollback
        try db.begin();
        try db.exec("INSERT INTO users (name, email) VALUES ('Temporary', 'temp@example.com')");
        try db.rollback();

        // Count after rollback
        try count_stmt.reset();
        _ = try count_stmt.step();
        const count_after = count_stmt.columnInt(0);
        std.debug.print("  Users after rollback: {d}\n", .{count_after});
        std.debug.print("  Transaction was rolled back successfully!\n", .{});
    }

    std.debug.print("\nDone!\n", .{});
}