sal/examples/postgresclient/basic_operations.rhai

// PostgreSQL Basic Operations Example
//
// This example demonstrates how to use the PostgreSQL client module to:
// - Connect to a PostgreSQL database
// - Create a table
// - Insert data
// - Query data
// - Update data
// - Delete data
// - Drop a table
//
// Prerequisites:
// - PostgreSQL server must be running
// - Environment variables should be set for connection details:
//   - POSTGRES_HOST: PostgreSQL server host (default: localhost)
//   - POSTGRES_PORT: PostgreSQL server port (default: 5432)
//   - POSTGRES_USER: PostgreSQL username (default: postgres)
//   - POSTGRES_PASSWORD: PostgreSQL password
//   - POSTGRES_DB: PostgreSQL database name (default: postgres)

// Helper function to check if PostgreSQL is available
fn is_postgres_available() {
    try {
        // Try to execute a simple connection
        let connect_result = pg_connect();
        return connect_result;
    } catch(err) {
        print(`PostgreSQL connection error: ${err}`);
        return false;
    }
}

// Main function
fn main() {
    print("=== PostgreSQL Basic Operations Example ===");

    // Check if PostgreSQL is available
    let postgres_available = is_postgres_available();
    if !postgres_available {
        print("PostgreSQL server is not available. Please check your connection settings.");
        return;
    }

    print("✓ Connected to PostgreSQL server");

    // Define table name
    let table_name = "rhai_example_users";

    // Step 1: Create a table
    print("\n1. Creating table...");
    let create_table_query = `
        CREATE TABLE IF NOT EXISTS ${table_name} (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            age INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `;

    let create_result = pg_execute(create_table_query);
    print(`✓ Table created (result: ${create_result})`);

    // Step 2: Insert data
    print("\n2. Inserting data...");
    let insert_queries = [
        `INSERT INTO ${table_name} (name, email, age) VALUES ('Alice', 'alice@example.com', 30)`,
        `INSERT INTO ${table_name} (name, email, age) VALUES ('Bob', 'bob@example.com', 25)`,
        `INSERT INTO ${table_name} (name, email, age) VALUES ('Charlie', 'charlie@example.com', 35)`
    ];

    for query in insert_queries {
        let insert_result = pg_execute(query);
        print(`✓ Inserted row (result: ${insert_result})`);
    }

    // Step 3: Query all data
    print("\n3. Querying all data...");
    let select_query = `SELECT * FROM ${table_name}`;
    let rows = pg_query(select_query);

    print(`Found ${rows.len()} rows:`);
    for row in rows {
        print(`  ID: ${row.id}, Name: ${row.name}, Email: ${row.email}, Age: ${row.age}, Created: ${row.created_at}`);
    }

    // Step 4: Query specific data
    print("\n4. Querying specific data...");
    let select_one_query = `SELECT * FROM ${table_name} WHERE name = 'Alice'`;
    let alice = pg_query_one(select_one_query);

    print(`Found Alice:`);
    print(`  ID: ${alice.id}, Name: ${alice.name}, Email: ${alice.email}, Age: ${alice.age}`);

    // Step 5: Update data
    print("\n5. Updating data...");
    let update_query = `UPDATE ${table_name} SET age = 31 WHERE name = 'Alice'`;
    let update_result = pg_execute(update_query);
    print(`✓ Updated Alice's age (result: ${update_result})`);

    // Verify update
    let verify_query = `SELECT * FROM ${table_name} WHERE name = 'Alice'`;
    let updated_alice = pg_query_one(verify_query);
    print(`  Updated Alice: ID: ${updated_alice.id}, Name: ${updated_alice.name}, Age: ${updated_alice.age}`);

    // Step 6: Delete data
    print("\n6. Deleting data...");
    let delete_query = `DELETE FROM ${table_name} WHERE name = 'Bob'`;
    let delete_result = pg_execute(delete_query);
    print(`✓ Deleted Bob (result: ${delete_result})`);

    // Verify deletion
    let count_query = `SELECT COUNT(*) as count FROM ${table_name}`;
    let count_result = pg_query_one(count_query);
    print(`  Remaining rows: ${count_result.count}`);

    // Step 7: Drop table
    print("\n7. Dropping table...");
    let drop_query = `DROP TABLE IF EXISTS ${table_name}`;
    let drop_result = pg_execute(drop_query);
    print(`✓ Dropped table (result: ${drop_result})`);

    // Reset connection
    print("\n8. Resetting connection...");
    let reset_result = pg_reset();
    print(`✓ Reset connection (result: ${reset_result})`);

    print("\nExample completed successfully!");
}

// Run the main function
main();