Skip to content
HeliosDB HeliosDB Full

HeliosDB Procedures Architecture

HeliosDB Procedures Architecture

Overview

The heliosdb-procedures package provides a multi-language stored procedure engine for HeliosDB v3.0 with support for four procedural languages and advanced debugging capabilities.

Supported Languages

1. PL/pgSQL (PostgreSQL)

  • Full PostgreSQL procedural language compatibility
  • DECLARE blocks with typed variables
  • Control flow: IF/ELSIF/ELSE, LOOP, WHILE, FOR
  • Exception handling: BEGIN/EXCEPTION/END blocks
  • Cursor support: DECLARE CURSOR, FETCH, CLOSE
  • RAISE statements for logging and errors
  • Dynamic SQL with EXECUTE

2. PL/SQL (Oracle)

  • Oracle PL/SQL compatibility
  • %TYPE and %ROWTYPE attributes
  • BULK COLLECT for bulk operations
  • FORALL for bulk DML
  • Exception handling with named exceptions
  • Nested blocks and procedures
  • REF CURSOR support

3. DB2 SQL PL (IBM DB2)

  • IBM DB2 procedural language support
  • SQLSTATE and SQLCODE error handling
  • GET DIAGNOSTICS for detailed error info
  • SIGNAL and RESIGNAL for custom errors
  • Compound SQL (BEGIN ATOMIC … END)
  • FOR loops with implicit cursors
  • Transaction control

4. HeliosScript (Custom)

  • Modern Rust-like syntax
  • Strong typing with type inference
  • Pattern matching (match expressions)
  • Result<T, E> error handling
  • Iterator methods (map, filter, collect)
  • Async/await support
  • sql! macro for embedded SQL

Architecture

┌─────────────────────────────────────────────────────────────┐
│                     ProcedureEngine                         │
│  - Procedure registry                                       │
│  - Execution coordination                                   │
│  - Statistics tracking                                      │
└─────────────────┬───────────────────────────────────────────┘
                  │
                  ├── Interpreters
                  │   ├── PlPgSQLInterpreter
                  │   ├── PlSQLInterpreter
                  │   ├── DB2SqlPLInterpreter
                  │   └── HeliosScriptInterpreter
                  │
                  ├── Parser
                  │   ├── Procedure definition parser
                  │   ├── Language-specific AST parsers
                  │   └── Expression parser
                  │
                  ├── Compiler
                  │   ├── AST → Bytecode compilation
                  │   ├── Instruction set
                  │   └── Optimization passes
                  │
                  ├── Runtime
                  │   ├── Variable scoping
                  │   ├── Cursor management
                  │   ├── Transaction control
                  │   └── Value system
                  │
                  └── Debugger
                      ├── Breakpoint management
                      ├── Step execution
                      ├── Call stack tracking
                      └── Performance profiling

Core Components

1. ProcedureEngine (lib.rs)

Main entry point for the stored procedure system.

Responsibilities:

  • Procedure lifecycle management (CREATE, DROP, EXECUTE)
  • Language dispatcher
  • Execution statistics tracking
  • Caching compiled procedures
  • Debugging session management

Key Methods:

pub fn new() -> Self
pub async fn create_procedure(&self, source: &str) -> Result<String>
pub async fn execute_procedure(&self, name: &str, params: Vec<(String, Value)>) -> Result<Value>
pub fn drop_procedure(&self, name: &str) -> Result<()>
pub fn get_stats(&self, name: &str) -> Option<ExecutionStats>

2. Interpreter Framework (interpreter.rs)

Core interpreter trait and bytecode virtual machine.

Components:

  • Interpreter trait: Common interface for all languages
  • InterpreterContext: Execution context with variables and state
  • BytecodeCompiler: Compiles AST to bytecode
  • BytecodeVM: Executes bytecode instructions
  • Instruction: Bytecode instruction set

Instruction Set:

  • Variable operations: LoadConst, LoadVar, StoreVar
  • Arithmetic: Add, Sub, Mul, Div, Mod, Neg
  • Comparison: Equal, NotEqual, LessThan, GreaterThan, etc.
  • Logical: And, Or, Not
  • Control flow: Jump, JumpIfFalse, Call, Return
  • SQL operations: ExecuteQuery, ExecuteDML, FetchCursor
  • Exception handling: BeginTry, Catch, Throw
  • Transaction control: BeginTransaction, Commit, Rollback

3. Parser (parser.rs)

SQL procedural language parser with nom combinators.

AST Node Types:

  • DeclareVariable: Variable declarations
  • Assignment: Variable assignments
  • If/Loop/While/For: Control flow
  • Select/Insert/Update/Delete: SQL statements
  • DeclareCursor/OpenCursor/FetchCursor: Cursor operations
  • Begin/Exception/End: Exception handling
  • Return/Exit/Continue: Control statements
  • Literal/Variable/BinaryOp/UnaryOp: Expressions

4. Runtime (runtime.rs)

Runtime system with variable scoping and type system.

Value Types:

  • Null, Boolean, Integer, Float, Decimal
  • String, Date, Timestamp
  • Array, Record (structured types)

Features:

  • Variable scoping with parent scopes
  • Type-safe operations (add, sub, mul, div)
  • Cursor management with result sets
  • Transaction depth tracking
  • Constant enforcement

5. Language Interpreters

PlPgSQLInterpreter (plpgsql.rs)

PostgreSQL procedural language implementation.

Features:

  • Full PL/pgSQL syntax support
  • Exception handling with custom error types
  • Cursor support
  • Dynamic SQL
  • RAISE statements

PlSQLInterpreter (plsql.rs)

Oracle PL/SQL implementation.

Oracle-Specific Features:

  • %TYPE and %ROWTYPE attributes
  • BULK COLLECT operations
  • FORALL bulk DML
  • Named exceptions
  • REF CURSOR support

DB2SqlPLInterpreter (db2_sql_pl.rs)

IBM DB2 SQL PL implementation.

DB2-Specific Features:

  • SQLSTATE/SQLCODE error handling
  • GET DIAGNOSTICS statements
  • SIGNAL/RESIGNAL error handling
  • Compound SQL blocks
  • FOR loops with implicit cursors

HeliosScriptInterpreter (heliosscript.rs)

Custom Rust-like procedural language.

Modern Features:

  • Rust-like syntax (let bindings, match expressions)
  • Type inference
  • Pattern matching
  • Iterator methods
  • Result<T, E> error handling
  • sql! macro for embedded SQL

6. Debugger (debugger.rs)

Comprehensive debugging support.

Features:

  • Breakpoint management (line, conditional)
  • Step execution (next, into, out, continue)
  • Call stack inspection
  • Variable inspection
  • Expression evaluation
  • Performance profiling
  • Hotspot analysis

Debug Session:

pub fn start_session(&mut self, procedure_name: &str) -> Result<Uuid>
pub fn set_breakpoint(&mut self, session_id: Uuid, line: usize) -> Result<()>
pub fn step_next(&mut self, session_id: Uuid) -> Result<()>
pub fn get_call_stack(&self, session_id: Uuid) -> Result<Vec<StackFrame>>
pub fn get_local_variables(&self, session_id: Uuid) -> Result<HashMap<String, Value>>

Execution Flow

1. CREATE PROCEDURE
   ├── Parse procedure definition
   ├── Validate language syntax
   ├── Compile to bytecode
   ├── Store metadata and bytecode
   └── Return procedure name


2. EXECUTE PROCEDURE
   ├── Load procedure metadata
   ├── Validate parameters
   ├── Create execution context
   ├── Load interpreter for language
   ├── Execute bytecode in VM
   ├── Handle exceptions
   ├── Update statistics
   └── Return result


3. DEBUG PROCEDURE
   ├── Start debug session
   ├── Set breakpoints
   ├── Execute with debugging enabled
   ├── Pause at breakpoints
   ├── Inspect variables and stack
   ├── Step execution
   └── Continue or stop

Performance Optimizations

  1. Bytecode Compilation: One-time compilation to bytecode
  2. Procedure Caching: Compiled procedures cached in memory
  3. Fast Variable Lookup: DashMap for concurrent access
  4. Lazy Evaluation: Only compile when needed
  5. Stack-based VM: Efficient bytecode execution
  6. Profiling Data: Identify hotspots for optimization

Error Handling

Comprehensive error types:

  • NotFound: Procedure not found
  • ParseError: Syntax error in source
  • RuntimeError: Execution error
  • TypeError: Type mismatch
  • DivisionByZero: Math error
  • SqlError: SQL execution error
  • CursorError: Cursor operation error
  • TransactionError: Transaction error
  • Exception: User-raised exception
  • Timeout: Execution timeout
  • StackOverflow: Recursion limit exceeded

Configuration

pub struct EngineConfig {
    pub max_execution_time_ms: u64,      // Default: 30000 (30s)
    pub max_recursion_depth: usize,      // Default: 100
    pub enable_profiling: bool,          // Default: true
    pub enable_debugging: bool,          // Default: false
    pub cache_compiled_procedures: bool, // Default: true
}

Thread Safety

  • All components are thread-safe using Arc and DashMap
  • Concurrent procedure execution supported
  • Lock-free reads for variables
  • Atomic statistics updates

Future Enhancements

  1. JIT Compilation: Compile hot procedures to native code
  2. Query Optimizer: Optimize embedded SQL
  3. Distributed Execution: Execute procedures across nodes
  4. Package Support: Oracle-style package support
  5. Triggers: Event-driven procedure execution
  6. Scheduled Jobs: Time-based procedure execution
  7. More Languages: T-SQL, JavaScript, Python integration
  8. Performance Hints: Inline hints for optimization
  9. Code Coverage: Track procedure code coverage
  10. Auto-tuning: Automatic performance optimization

Dependencies

  • nom: Parser combinators
  • logos: Lexer generation
  • pest: Parser generation
  • tokio: Async runtime
  • dashmap: Concurrent hash map
  • parking_lot: Fast locks
  • serde/bincode: Serialization
  • rust_decimal: Decimal math
  • chrono: Date/time support
  • uuid: Session IDs
  • tracing: Logging
  • metrics: Performance metrics

Testing

Comprehensive test suite covering:

  • Parser correctness
  • Bytecode compilation
  • VM execution
  • Variable scoping
  • Cursor operations
  • Exception handling
  • Debugger functionality
  • Performance benchmarks

Run tests:

Terminal window
cargo test --package heliosdb-procedures

Examples

See examples/basic_procedures.rs for comprehensive usage examples of all four languages.

Run examples:

Terminal window
cargo run --package heliosdb-procedures --example basic_procedures