HeliosDB Procedures

Multi-language stored procedure engine for HeliosDB v3.0 with support for PL/pgSQL, PL/SQL, DB2 SQL PL, and HeliosScript.

Features

Multi-Language Support
- PL/pgSQL (PostgreSQL)
- PL/SQL (Oracle)
- DB2 SQL PL (IBM DB2)
- HeliosScript (Custom Rust-like syntax)
Advanced Runtime
- Variable scoping with nested contexts
- Control flow (IF, LOOP, WHILE, FOR, CASE)
- Exception handling with custom error types
- Cursor support for result set iteration
- Dynamic SQL execution
- Transaction management
Debugging & Profiling
- Breakpoint support
- Step execution (next, into, out)
- Call stack inspection
- Variable inspection
- Performance profiling
- Hotspot analysis
Performance
- Bytecode compilation
- Execution statistics
- Caching of compiled procedures
- Configurable timeouts
- Stack overflow protection

Usage

use heliosdb_procedures::{ProcedureEngine, Value};

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Create engine
    let engine = ProcedureEngine::new();

    // Create procedure
    let procedure = r#"
        CREATE PROCEDURE calculate_total(p_order_id INTEGER)
        LANGUAGE plpgsql
        AS $$
        DECLARE
            v_total DECIMAL(10,2);
        BEGIN
            SELECT SUM(price * quantity)
            INTO v_total
            FROM order_items
            WHERE order_id = p_order_id;

            RETURN v_total;
        END;
        $$;
    "#;

    let proc_name = engine.create_procedure(procedure).await?;

    // Execute procedure
    let params = vec![
        ("p_order_id".to_string(), Value::Integer(12345))
    ];

    let result = engine.execute_procedure(&proc_name, params).await?;
    println!("Result: {:?}", result);

    Ok(())
}

Language Examples

PL/pgSQL (PostgreSQL)

CREATE PROCEDURE update_inventory(p_product_id INTEGER, p_quantity INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_stock INTEGER;
BEGIN
    -- Get current stock
    SELECT stock_quantity INTO v_current_stock
    FROM products
    WHERE id = p_product_id;

    -- Update stock
    UPDATE products
    SET stock_quantity = v_current_stock + p_quantity,
        last_updated = CURRENT_TIMESTAMP
    WHERE id = p_product_id;

    -- Check if reorder needed
    IF v_current_stock + p_quantity < 10 THEN
        RAISE NOTICE 'Low stock warning for product %', p_product_id;
    END IF;

    RETURN v_current_stock + p_quantity;
END;
$$;

PL/SQL (Oracle)

CREATE PROCEDURE process_orders(p_date DATE)
LANGUAGE plsql
AS $$
DECLARE
    TYPE order_array IS TABLE OF orders%ROWTYPE;
    v_orders order_array;
    v_total NUMBER := 0;
BEGIN
    -- Bulk collect orders
    SELECT * BULK COLLECT INTO v_orders
    FROM orders
    WHERE order_date = p_date
    AND status = 'PENDING';

    -- Process each order
    FORALL i IN v_orders.FIRST..v_orders.LAST
        UPDATE orders
        SET status = 'PROCESSED'
        WHERE order_id = v_orders(i).order_id;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

DB2 SQL PL

CREATE PROCEDURE transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount DECIMAL(10,2)
)
LANGUAGE db2sqlpl
AS $$
BEGIN
    DECLARE v_balance DECIMAL(10,2);
    DECLARE SQLSTATE CHAR(5);

    BEGIN ATOMIC
        -- Check balance
        SELECT balance INTO v_balance
        FROM accounts
        WHERE account_id = p_from_account;

        IF v_balance < p_amount THEN
            SIGNAL SQLSTATE '75001'
                SET MESSAGE_TEXT = 'Insufficient funds';
        END IF;

        -- Transfer funds
        UPDATE accounts SET balance = balance - p_amount
        WHERE account_id = p_from_account;

        UPDATE accounts SET balance = balance + p_amount
        WHERE account_id = p_to_account;
    END;
END;
$$;

HeliosScript (Rust-like)

CREATE PROCEDURE analyze_sales(region_id: i64) -> Result<f64, Error>
LANGUAGE heliosscript
AS $$
fn analyze_sales(region_id: i64) -> Result<f64, Error> {
    // Query with iterator methods
    let sales = sql!("SELECT amount FROM sales WHERE region_id = ?", region_id);

    // Calculate total using iterator
    let total: f64 = sales
        .iter()
        .map(|s| s.amount)
        .filter(|a| *a > 0.0)
        .sum();

    // Pattern matching
    match total {
        t if t > 10000.0 => {
            sql!("INSERT INTO high_performers VALUES (?)", region_id)?;
            Ok(t)
        },
        t => Ok(t),
    }
}
$$;

Debugging

use heliosdb_procedures::ProcedureEngine;

let engine = ProcedureEngine::new();

// Start debug session
let session_id = engine.start_debug_session("my_procedure")?;

// Set breakpoint
engine.set_breakpoint(session_id, 10)?;

// Execute with debugging
let params = vec![("p_id".to_string(), Value::Integer(1))];
let result = engine.execute_procedure("my_procedure", params).await?;

// Inspect variables
let variables = engine.get_local_variables(session_id)?;
println!("Variables: {:?}", variables);

// Get call stack
let stack = engine.get_call_stack(session_id)?;
println!("Call stack: {:?}", stack);

Configuration

use heliosdb_procedures::{ProcedureEngine, EngineConfig};

let config = EngineConfig {
    max_execution_time_ms: 30000,  // 30 seconds
    max_recursion_depth: 100,
    enable_profiling: true,
    enable_debugging: true,
    cache_compiled_procedures: true,
};

let engine = ProcedureEngine::with_config(config);

Performance Statistics

let stats = engine.get_stats("my_procedure").unwrap();

println!("Executions: {}", stats.execution_count);
println!("Avg time: {}ms", stats.avg_time_ms);
println!("Min time: {}ms", stats.min_time_ms);
println!("Max time: {}ms", stats.max_time_ms);
println!("Errors: {}", stats.error_count);

Architecture

heliosdb-procedures/
├── src/
│   ├── lib.rs              # Main engine and public API
│   ├── interpreter.rs      # Core interpreter framework
│   ├── parser.rs          # SQL procedural language parser
│   ├── runtime.rs         # Runtime with variables and cursors
│   ├── plpgsql.rs         # PL/pgSQL interpreter
│   ├── plsql.rs           # PL/SQL interpreter
│   ├── db2_sql_pl.rs      # DB2 SQL PL interpreter
│   ├── heliosscript.rs    # HeliosScript interpreter
│   └── debugger.rs        # Debugging support
├── examples/
│   └── basic_procedures.rs # Usage examples
└── Cargo.toml

License

MIT OR Apache-2.0