OrbitQL Complete Documentation

Unified Multi-Model Query Language for Orbit-RS

Table of Contents

1. Overview
2. Language Reference
3. Machine Learning Guide
4. Implementation Status
5. Parser Enhancements
6. LSP/IDE Support
7. Engine Integration
8. Examples
9. Performance & Optimization
10. API Reference

Overview

OrbitQL is Orbit-RS’s native query language designed specifically for multi-model distributed systems. It combines the familiarity of SQL with advanced features for graph traversals, time series analytics, machine learning, and distributed computing, all optimized for the Orbit actor system.

Key Features

• Multi-model unified syntax: Query graphs, documents, time series, and relational data in single queries
• Advanced SQL compatibility: Full support for CTEs, CASE expressions, window functions, and temporal operations
• Machine Learning Integration: Built-in ML functions including XGBoost, LightGBM, CatBoost, and AdaBoost
• Temporal functions: Native NOW(), INTERVAL, and time-based analytics
• Aggregate enhancements: COUNT(DISTINCT), conditional aggregates, and complex expressions
• Distributed by design: Native support for cross-node queries and joins
• Actor-aware: Direct integration with Orbit’s virtual actor system
• Streaming support: Handle large result sets with streaming execution
• ACID compliance: Full transaction support across distributed data
• IDE Support: Complete VS Code extension with Language Server Protocol

Design Principles

1. Familiarity: Build on SQL foundations that developers know
2. Extensibility: Clean extensions for graph and time series operations
3. Performance: Query optimization for distributed systems
4. Type Safety: Compile-time query validation and optimization
5. Composability: Combine different data models seamlessly

Language Reference

Basic Syntax

[WITH recursive_cte AS (...)]
SELECT [DISTINCT] projection
FROM data_sources
[JOIN other_sources ON conditions]
[TRAVERSE graph_operations]
[WHERE conditions]
[GROUP BY grouping_expressions]
[HAVING group_conditions]
[ORDER BY sort_expressions]
[LIMIT count [OFFSET start]]
[FOR UPDATE | FOR SHARE]

Data Types

-- Scalar types
SELECT 
    42 as integer,
    3.14159 as float,
    'Hello World' as string,
    true as boolean,
    null as null_value,
    '2023-10-06T19:00:00Z'::timestamp as time_value,
    '1 hour'::interval as duration;

-- Array types
SELECT 
    ARRAY[1, 2, 3, 4, 5] as numbers,
    ARRAY['red', 'green', 'blue'] as colors;

-- Object types
SELECT 
    OBJECT(
        'name', 'Alice',
        'age', 30,
        'skills', ARRAY['rust', 'sql', 'graph']
    ) as user_profile;

Advanced SQL Features

Common Table Expressions (CTEs)

-- Basic CTE
WITH user_stats AS (
    SELECT user_id, COUNT(*) AS post_count
    FROM posts
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.name, COALESCE(us.post_count, 0) AS recent_posts
FROM users u
LEFT JOIN user_stats us ON u.id = us.user_id;

-- Multiple CTEs
WITH 
    active_users AS (
        SELECT user_id FROM sessions 
        WHERE last_seen > NOW() - INTERVAL '7 days'
    ),
    popular_posts AS (
        SELECT post_id, COUNT(*) AS like_count
        FROM likes
        GROUP BY post_id
        HAVING COUNT(*) > 100
    )
SELECT u.name, p.title, pp.like_count
FROM users u
JOIN active_users au ON u.id = au.user_id
JOIN posts p ON u.id = p.author_id
JOIN popular_posts pp ON p.id = pp.post_id;

CASE Expressions

-- Simple CASE
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_category
FROM users;

-- CASE in aggregations
SELECT 
    department,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
    AVG(CASE WHEN performance_rating = 'excellent' THEN salary END) AS avg_excellent_salary
FROM employees
GROUP BY department;

Temporal Functions

-- NOW() function
SELECT NOW() AS current_time;
SELECT * FROM events WHERE created_at > NOW() - INTERVAL '1 hour';

-- INTERVAL expressions
SELECT 
    event_name,
    created_at,
    NOW() - created_at AS time_ago
FROM events
WHERE created_at BETWEEN NOW() - INTERVAL '7 days' AND NOW();

Enhanced Aggregates

-- COUNT(DISTINCT)
SELECT 
    campaign_id,
    COUNT(*) AS total_clicks,
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(DISTINCT session_id) AS unique_sessions
FROM ad_clicks
GROUP BY campaign_id;

-- Conditional aggregates
SELECT 
    product_category,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN order_status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
    AVG(CASE WHEN rating IS NOT NULL THEN rating END) AS avg_rating
FROM orders
GROUP BY product_category;

Graph Queries

-- Simple outbound traversal
SELECT person.name, friend.name
FROM users person
TRAVERSE OUTBOUND 1..1 STEPS ON follows TO friend
WHERE person.location = 'San Francisco';

-- Multi-hop traversal
SELECT start.name, end.name, path_length
FROM users start
TRAVERSE OUTBOUND 1..5 STEPS ON follows TO end
RETURN PATHS AS path_info
WHERE start.name = 'Alice' AND end.location = 'New York';

Time Series Queries

-- Simple time series query
SELECT series_id, timestamp, value
FROM metrics
WHERE series_id = 'cpu_usage_server_01'
  AND timestamp >= NOW() - INTERVAL '1 hour'
ORDER BY timestamp;

-- Time-based windows
SELECT 
    TIME_BUCKET('15 minutes', timestamp) as time_window,
    series_id,
    AVG(value) as avg_value,
    MAX(value) as max_value
FROM metrics
WHERE timestamp >= NOW() - INTERVAL '6 hours'
GROUP BY TIME_BUCKET('15 minutes', timestamp), series_id;

Multi-Model Queries

-- User activity analysis across all models
SELECT 
    u.user_id,
    u.profile->>'name' as name,
    u.profile->>'location' as location,
    social_metrics.friend_count,
    activity_metrics.recent_activity
FROM user_profiles u
JOIN (
    SELECT 
        user.id,
        COUNT(*) as friend_count
    FROM users user
    TRAVERSE OUTBOUND 1..1 STEPS ON follows TO friend
    GROUP BY user.id
) social_metrics ON u.user_id = social_metrics.id
JOIN (
    SELECT 
        series_id,
        COUNT(*) as recent_activity
    FROM activity_events
    WHERE timestamp >= NOW() - INTERVAL '24 hours'
    GROUP BY series_id
) activity_metrics ON u.user_id = activity_metrics.series_id;

Machine Learning Guide

OrbitQL supports comprehensive machine learning capabilities directly in SQL, including state-of-the-art boosting algorithms.

Quick Start

-- Train a customer segmentation model using XGBoost
SELECT ML_XGBOOST(
    ARRAY[age, income, purchase_frequency, days_since_last_order],
    customer_segment
) as model_accuracy
FROM customer_data
WHERE created_at > NOW() - INTERVAL '1 year';

Model Management

-- Train and save a model
SELECT ML_TRAIN_MODEL(
    'fraud_detector',           -- Model name
    'XGBOOST',                 -- Algorithm
    ARRAY[amount, merchant_category, hour_of_day, user_age],  -- Features
    is_fraud                   -- Target
) as training_result
FROM transactions
WHERE created_at > NOW() - INTERVAL '90 days';

-- Use the trained model for predictions
SELECT 
    transaction_id,
    amount,
    ML_PREDICT('fraud_detector', ARRAY[amount, merchant_category, hour_of_day, user_age]) as fraud_probability
FROM new_transactions
WHERE fraud_probability > 0.8;

Boosting Algorithms

XGBoost

-- Advanced XGBoost with custom parameters
SELECT ML_TRAIN_MODEL(
    'advanced_xgb_model',
    'XGBOOST', 
    ARRAY[age, income, credit_score, debt_ratio],
    loan_default,
    OBJECT(
        'n_estimators', 100,
        'learning_rate', 0.1,
        'max_depth', 6,
        'subsample', 0.8,
        'colsample_bytree', 0.8
    )
) FROM loan_applications;

LightGBM

-- LightGBM for categorical data
SELECT ML_LIGHTGBM(
    ARRAY[category_id, numerical_feature, another_category],
    sales_volume
) FROM product_sales
WHERE product_category IN ('electronics', 'clothing', 'books');

CatBoost

-- CatBoost for handling categorical features automatically
SELECT ML_CATBOOST(
    ARRAY[user_id, product_category, region, season],
    purchase_amount
) FROM ecommerce_transactions;

AdaBoost

-- AdaBoost for binary classification
SELECT ML_ADABOOST(
    ARRAY[feature1, feature2, feature3],
    binary_target
) FROM training_data;

Feature Engineering

-- Feature extraction for ML
SELECT 
    user_id,
    -- User profile features
    CAST(profile->>'age' AS INTEGER) as age,
    profile->>'location' as location,
    JSON_ARRAY_LENGTH(profile->'skills') as skill_count,
    
    -- Social network features
    (SELECT COUNT(*) FROM users TRAVERSE OUTBOUND 1..1 STEPS ON follows WHERE target.active = true) as friend_count,
    
    -- Activity features
    (SELECT COUNT(*) FROM activity_events WHERE series_id = users.user_id AND timestamp >= NOW() - INTERVAL '30 days') as monthly_activity
    
FROM users
WHERE profile->>'active' = 'true';

Implementation Status

Current State: ✅ PRODUCTION-READY WITH ADVANCED SQL FEATURES

Working Features

Core Query Features ✅ FULLY FUNCTIONAL

• Basic SELECT with filtering
• Multi-table JOIN operations
• GROUP BY with aggregation functions
• ORDER BY sorting and LIMIT pagination
• Graph relationship traversal
• Time-series data querying
• Query optimization and caching
• Live query streaming
• Parameterized queries

Advanced SQL Features ✅ NEWLY IMPLEMENTED

• NOW() Function parsing and execution
• INTERVAL expressions with temporal arithmetic
• COUNT(DISTINCT) and enhanced aggregation
• CASE expressions (simple and nested)
• WITH Common Table Expressions (CTEs)
• COALESCE and null-handling functions
• Complex conditional aggregates
• Multi-model queries with advanced SQL features

Test Coverage

Parser Enhancements

Successfully Implemented Features

Performance Impact

Query Validation Success Rate

LSP/IDE Support

VS Code Extension

The VS Code extension (tools/vscode-orbitql/) provides seamless integration:

• File association for .oql and .orbitql files
• Syntax highlighting with OrbitQL-specific grammar
• LSP client integration for all language features
• Configurable server settings
• Command palette integration

LSP Features

Installation

# Build the language server
cargo build --bin orbitql-lsp

# Package the VS Code extension
cd tools/vscode-orbitql
npm install
npm run compile
vsce package

# Install the extension
code --install-extension orbitql-0.1.0.vsix

Engine Integration

Architecture Integration

// 1. OrbitQL Adapter (New)
//    orbit/engine/src/adapters/orbitql.rs
//    - Bridges OrbitQL to unified storage
//    - Type mapping OrbitQL ↔ SqlValue
//    - Filter conversion
//    - Query execution

// 2. Existing OrbitQL (orbit/shared)
//    - Parser and Lexer
//    - AST definitions
//    - Query optimizer
//    - Distributed planner

// 3. Protocol Layer (orbit/protocols)
//    - Wire protocol for OrbitQL
//    - Network serialization
//    - Client libraries

Data Flow

OrbitQL Query String
        ↓
    [Lexer] → Tokens
        ↓
    [Parser] → AST
        ↓
    [Optimizer] → Optimized Plan
        ↓
    [OrbitQL Adapter] → Storage API Calls
        ↓
    [Unified Storage] → Hot/Warm/Cold Tiers
        ↓
    QueryResult

Type Mapping

Examples

Real-time Dashboard Query

WITH 
    -- System health metrics
    system_health AS (
        SELECT 
            'system_health' as metric_type,
            AVG(CASE WHEN series_id LIKE 'cpu_%' THEN value END) as avg_cpu,
            AVG(CASE WHEN series_id LIKE 'memory_%' THEN value END) as avg_memory
        FROM metrics
        WHERE timestamp >= NOW() - INTERVAL '5 minutes'
    ),
    
    -- Active user count
    active_users AS (
        SELECT 
            'active_users' as metric_type,
            COUNT(DISTINCT user_id) as count
        FROM activity_events
        WHERE timestamp >= NOW() - INTERVAL '15 minutes'
    )

SELECT * FROM system_health
UNION ALL
SELECT metric_type, count::TEXT, null, null FROM active_users;

Fraud Detection System

WITH 
    -- Unusual transaction patterns
    suspicious_transactions AS (
        SELECT 
            t.user_id,
            t.amount,
            (t.amount - user_stats.avg_amount) / user_stats.stddev_amount as amount_zscore
        FROM transactions t
        JOIN (
            SELECT 
                user_id,
                AVG(amount) as avg_amount,
                STDDEV(amount) as stddev_amount
            FROM transactions
            WHERE timestamp >= NOW() - INTERVAL '90 days'
            GROUP BY user_id
        ) user_stats ON t.user_id = user_stats.user_id
        WHERE t.timestamp >= NOW() - INTERVAL '1 hour'
          AND ABS(t.amount - user_stats.avg_amount) / user_stats.stddev_amount > 2
    ),
    
    -- Social network risk assessment
    social_risk AS (
        SELECT 
            u.id as user_id,
            COUNT(CASE WHEN friend.profile->>'risk_score'::FLOAT > 0.7 THEN 1 END) as high_risk_friends
        FROM users u
        TRAVERSE OUTBOUND 1..2 STEPS ON [friends, knows] TO friend
        GROUP BY u.id
    )

SELECT 
    st.user_id,
    u.profile->>'name' as user_name,
    st.amount,
    st.amount_zscore,
    sr.high_risk_friends,
    (ABS(st.amount_zscore) * 0.4 + COALESCE(sr.high_risk_friends * 0.1, 0)) as combined_risk_score
FROM suspicious_transactions st
JOIN user_profiles u ON st.user_id = u.user_id
LEFT JOIN social_risk sr ON st.user_id = sr.user_id
WHERE ABS(st.amount_zscore) > 2.5 OR sr.high_risk_friends > 2
ORDER BY combined_risk_score DESC;

Performance & Optimization

Benchmarked Performance

Query Plans

-- Explain query execution plan
EXPLAIN (ANALYZE, VERBOSE, COSTS) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.name;

Parallel Execution

-- Enable parallel processing
SET enable_parallel = true;
SET max_parallel_workers_per_gather = 4;

SELECT /*+ PARALLEL(4) */
    series_id,
    COUNT(*) as point_count,
    AVG(value) as avg_value
FROM metrics
WHERE timestamp >= NOW() - INTERVAL '7 days'
GROUP BY series_id;

API Reference

Query Execution

use orbit_shared::orbitql::{OrbitQLEngine, QueryBuilder, ExecutionOptions};

// Basic query execution
let result = orbitql_engine.execute(
    "SELECT name, age FROM users WHERE age > @min_age",
    params! { "min_age" => 21 }
).await?;

// Query builder pattern
let query = QueryBuilder::new()
    .select(&["u.name", "COUNT(f.id) as friend_count"])
    .from("users u")
    .traverse("OUTBOUND 1..1 STEPS ON follows TO f")
    .group_by(&["u.id", "u.name"])
    .having("COUNT(f.id) > 5")
    .order_by("friend_count DESC")
    .limit(10);

let result = orbitql_engine.execute_query(query).await?;

Streaming Execution

use futures_util::stream::StreamExt;

// Stream large result sets
let mut stream = orbitql_engine.execute_stream(
    "SELECT * FROM large_table WHERE created_at >= @start_date",
    params! { "start_date" => start_date }
).await?;

while let Some(batch) = stream.next().await {
    for row in batch? {
        // Process each row
        println!("{:?}", row);
    }
}

Transaction Support

// Execute in transaction
let tx = orbitql_engine.begin_transaction().await?;

let user_result = tx.execute(
    "INSERT INTO users (name, email) VALUES (@name, @email) RETURNING id",
    params! { "name" => "Alice", "email" => "alice@example.com" }
).await?;

let user_id = user_result.get::<i64>(0, "id")?;

tx.commit().await?;

Summary

OrbitQL represents the next generation of query languages, designed specifically for modern distributed, multi-model systems while maintaining the familiarity and power that developers expect from SQL.

Key Achievements:

• ✅ Production-ready core functionality
• ✅ Advanced SQL features (CTEs, CASE, temporal functions)
• ✅ Machine learning integration
• ✅ Complete IDE support with LSP
• ✅ Comprehensive documentation
• ✅ 90%+ test coverage

OrbitQL is ready for advanced multi-model analytics workloads!