ultsql 1.0.6

A 100% Pure-Dart converged database engine combining Relational SQL, NoSQL JSON, HNSW Vector RAG, and PL/SQL with zero C dependencies.

🚀 ULTSQL — Ultra-High Performance Converged Multimodal Database Engine

UltSQL is a ground-up, zero-dependency, 4-in-1 converged database engine written in 100% pure Dart. It seamlessly combines Relational SQL, PL/SQL Procedural Execution, NoSQL Dotted-Path Document Querying, and AI-Native Vector RAG Search into a single, high-throughput storage model with zero native C dependencies or unsafe memory pointers.

---

🌟 Standalone Engine Metrics

Capability / Benchmark	UltSQL Performance	Feature Status
In-Memory Batch Write Throughput	1,200,000+ rows/sec	⚡ High-Throughput Memory Engine
B+ Tree Index Build (100K Rows)	17 ms	🏆 Ultra-Fast Sub-Second Indexing
768-Dim HNSW AI Vector RAG	6 ms (100% Recall)	🧠 Native AI Embedded Vector Engine
Network TCP Wire Protocol Server	Port 5432 Supported	🌐 Remote Client Network Connections
Self-Healing Corrupted Recovery	Auto-Repairs CRC Mismatches	🛠️ Zero-DBA Self-Healing
P2P Offline Device-to-Device Sync	LWW-Element-Set CRDT Sync	📲 Local-First P2P Mesh Sync
Universal Direct File SQL Queries	CSV, JSON, LOG Files	📁 Zero-ETL Direct Queries
Zero-Knowledge Ciphertext Search	Homomorphic XOR Search	🔐 Secure Privacy Enclave

---

🏛️ System Architecture

UltSQL uses a multi-layered Volcano-iterator query engine over custom slotted-page disk/memory tables, LRU page caching, B+ Trees, and HNSW vector graphs:

graph TD
    UI[Flutter IDE Console / Client App] -->|SQL / PL-SQL / NL Prompt| Interpreter[Interpreter Engine]
    Interpreter -->|Natural Language AI| NlEngine[NL-to-SQL AI Compiler]
    Interpreter -->|Lexical Analysis| Lexer[Hand-Written Lexer]
    Lexer -->|Tokens| Parser[Hand-Written Parser]
    Parser -->|AST Tree| QueryPlanner[Optimizing Query Planner]
    QueryPlanner -->|Physical Execution Plan| VolcanoEngine[Volcano Iterator Execution Engine]
    
    VolcanoEngine -->|Page Operations| PageCache[LRU Page Cache Buffer]
    PageCache -->|CRC32 Page Verification| Pager[Slotted Page Pager]
    Pager -->|Storage Engines| StorageAdapters
    
    subgraph StorageAdapters[Converters & Adapters]
      MemoryStore[MemoryTable: 1.2M+ rows/sec]
      RowStore[.db: Row-Oriented Slotted Pages]
      ColumnStore[.col_*: Columnar Parquet Store]
      BTreeIndex[.idx: B+ Tree Indexes]
      HnswIndex[.hnsw: HNSW Vector Graph]
      FileAdapter[Universal CSV / JSON / LOG Adapter]
    end
    
    VolcanoEngine -->|Network Server| PgWireServer[TCP Wire Protocol Server]
    VolcanoEngine -->|P2P Mesh| P2pNode[CRDT P2P Peer Node]

---

📑 Table of Contents

1. Architectural Highlights
2. The 15 Signature Innovations
3. Enterprise 6-Pillars Foundation
4. Storage Modes: Switchable Performance
5. SQL & PL/SQL Feature Guide
6. NoSQL Dotted-Path JSON Querying
7. AI-Native HNSW Vector RAG Search
8. Network TCP Wire Protocol Server
9. Self-Healing & Auto-Indexing Telemetry
10. P2P Offline Device-to-Device Sync
11. Direct File SQL Queries (CSV / JSON / LOG)
12. Zero-Knowledge Security Enclave
13. Engine Performance Metrics
14. Getting Started & Installation

---

💎 The 15 Signature Innovations

UltSQL introduces 15 signature database innovations engineered specifically for high-throughput client and cloud workloads:

1. ⚡ 1.2M+ Rows/sec In-Memory Batch Engine: Zero-allocation linear byte array memory ingestion.
2. 🏆 Ultra-Fast B+ Tree Bulk Indexing: insertSortedBatchSync constructs 100K-row B+ Trees in 17 ms.
3. 🧠 Native HNSW Vector RAG Graph: Cosine & Euclidean similarity search over 768-dim embeddings in 6 ms.
4. 🌐 Network TCP Wire Protocol Server: Accepts incoming connections from standard database drivers.
5. 🛠️ Self-Healing Page Auto-Repair: Auto-detects CRC32 page corruption and rebuilds intact state from WAL logs.
6. 🤖 Autonomous Telemetry Auto-Indexer: Monitors query scan frequencies and automatically provisions B+ Tree indexes.
7. 📁 Universal Direct File SQL Adapter: Runs live SQL queries over standard .csv, .json, and .log files without importing into tables.
8. 🗣️ AI Natural Language to SQL Compiler: Translates natural language prompts into executable SQL statements.
9. 🔐 Zero-Knowledge Encrypted Enclave: Performs fast ciphertext searches over homomorphically XOR-encrypted data.
10. 📲 P2P Offline LWW CRDT Sync: Merges peer database changes over local network without central servers.
11. 📦 Zero-Allocation RowMap Tuple Wrapper: Replaces Dart Map instantiations with zero-allocation array index views.
12. ⚡ JIT Compiled Expression Expressions: Compiles SQL WHERE conditions into native Dart closure delegates.
13. 📊 Auto-Optimized Columnar Parquet Store: Automatically converts tables with VECTOR or analytical data into columnar layout.
14. 🔄 MVCC Multi-Version Concurrency Control: Provides lock-free readers and repeatable read transaction isolation.
15. 🛡️ AES-256 Transparent Page Encryption: Encrypts storage pages on disk using 256-bit AES-CBC.

---

⚖️ Storage Modes: Switchable Performance

Switch between in-memory speed and durable disk storage with a single line of code:

1. ⚡ In-Memory Storage Mode (1,200,000+ rows/sec)

For high-frequency streaming, real-time AI vector search, and temporary session caches:

final db = Database(':memory:');
await db.init();

2. 💾 Durable Disk Storage Mode (360,000+ rows/sec)

For persistent local application data with ACID crash safety and auto-healing WAL recovery:

final db = Database('/path/to/app_data/my_database');
await db.init();

3. 🔄 Hybrid Ingest & Snapshot

final prep = db.prepare("INSERT INTO users VALUES (?, ?, ?);");
prep.executeBatchSync(batchRows);
await db.flushWalSync(); // Flush WAL snapshot to disk

---

🛠️ SQL & PL/SQL Feature Guide

Data Definition Language (DDL)

CREATE TABLE users (
  id INT PRIMARY KEY,
  name TEXT,
  balance DOUBLE,
  metadata JSON,
  embedding VECTOR
);

Data Manipulation Language (DML)

INSERT INTO users VALUES (1, 'Alice', 1500.50, '{"role": "admin", "department": "Engineering"}', '[0.12, 0.85, -0.44]');
INSERT INTO users VALUES (2, 'Bob', 820.00, '{"role": "developer", "department": "AI"}', '[0.91, 0.05, 0.12]');

PL/SQL Procedural Script Execution

DECLARE
  counter INT := 0;
  total DOUBLE := 0.0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting calculation...');
  
  WHILE counter < 5 LOOP
    counter := counter + 1;
    total := total + (counter * 100.5);
    
    IF counter % 2 = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Iteration ' || counter || ': EVEN total=' || total);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Iteration ' || counter || ': ODD total=' || total);
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Calculations Completed.');
END;

---

🌐 Network TCP Wire Protocol Server

UltSQL embeds a full Network TCP Wire Protocol server (pgwire_server.dart). Connect directly using network database drivers:

final pgServer = PgWireServer(db: db, port: 5432);
await pgServer.start();
print('TCP Wire Protocol Server running on port 5432...');

---

🧠 AI-Native Vector RAG Search

Create an HNSW index and execute sub-7ms vector similarity queries:

CREATE INDEX idx_products_emb ON products (embedding) USING HNSW;

SELECT name, vector_distance(embedding, '[0.12, 0.85, -0.44]') AS dist
FROM products
ORDER BY dist ASC
LIMIT 5;

---

📲 P2P Offline Device-to-Device Sync

Synchronize database states between offline mobile devices using Conflict-Free Replicated Data Types (CRDT):

final localNode = P2pSyncNode(nodeId: 'device_A', db: db);

// Merge peer update record
localNode.applyPeerUpdate(P2pUpdateRecord(
  entityId: 'user_101',
  timestamp: DateTime.now().millisecondsSinceEpoch,
  data: {'name': 'Alice Updated', 'balance': 2000.0},
));

---

📁 Direct File SQL Queries (CSV / JSON / LOG)

Execute standard SQL queries directly over external files without ETL or table imports:

final fileAdapter = UniversalFileAdapter();

// Query external CSV file directly using SQL
final csvResults = fileAdapter.queryCsvSync(
  filePath: '/data/logs.csv',
  sqlQuery: "SELECT * FROM file WHERE status = 'ERROR'",
);

---

📊 Standalone Engine Performance Metrics

Empirical performance measurements recorded on 100,000 records on local disk:

======================================================
🔥 ULTSQL STANDALONE ENGINE PERFORMANCE (100,000 ROWS) 🔥
======================================================
1. Bulk Insert Throughput (100,000 Rows):
   - UltSQL (Memory Mode): 82 ms (1,219,512 rows/sec)
   - UltSQL (Disk Mode): 278 ms (359,712 rows/sec)

2. B+ Tree Index Build (100,000 Rows):
   - UltSQL: 17 ms (Ultra-Fast B+ Tree Indexing)

3. Multimodal Features:
   - 768-Dim HNSW Vector Search: 6 ms (100% Recall Accuracy)
   - Network TCP Wire Server: Port 5432 Supported
   - Self-Healing Page Repair: CRC Auto-Recovery Supported
   - P2P Device-to-Device Sync: LWW-CRDT Sync Supported
======================================================

---

🚀 Getting Started & Installation

Prerequisites

• Dart SDK 3.4+ or Flutter SDK 3.22+

Installation

1. Clone repository:

   git clone https://github.com/ompatel3158/ULTSQL.git
   cd ULTSQL
   

2. Install dependencies:

   flutter pub get
   

3. Run the comprehensive test suite:

   flutter test
   

4. Run the interactive UI Console IDE:

   flutter run
   

---

📜 License

UltSQL is licensed under the BSD 3-Clause License (the official license used by Flutter & Google). Built with ❤️ in pure Dart.