ultsql 1.0.26

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

Changelog

1.0.26

• Official Contact & Maintainer Email Update:
  • Updated primary maintainer & author contact email to [e-mail] across all package manifests (pubspec.yaml, package.json, pyproject.toml, setup.py, Cargo.toml), license files, and documentation.
• Converged NoSQL JSON Traversal & Operators:
  • Validated and streamlined PostgreSQL-standard JSON path extraction operators (-> for JSON, ->> for unquoted text) across embedded and browser engines.
  • Enhanced multi-statement execution handling in the in-browser sandbox and documentation playground.
• Bklit Minimalist CAD Architecture HUD & Engine Telemetry:
  • Rebuilt web portal hero card with an architectural data-flow pipeline (Pipeline, 4KB Slotted Page, and HNSW Vector Radar).
  • Wired live query triggers directly to the client-side Pure Dart VM engine (ultsql_engine.js) with sub-millisecond execution readouts.
• Search Console & Domain Verification:
  • Integrated Google Site Verification across portal web pages and static assets.

1.0.24

• Deterministic Obfuscation (XOR Fast Matching) in SQL Engine:
  • Implemented and wired native SQL functions zk_encrypt(text, key), zk_decrypt(hex, key), and zk_match(cipher, search_prompt, [key]) directly into JitCompiler.
  • Enables zero-decryption equality matching in queries (WHERE zk_match(cipher, 'keyword', 'key') = true) without decrypting raw data rows in storage or RAM.
  • Complemented with full test suite coverage in test/encryption_test.dart.
• Single Source of Truth Version Management:
  • Centralized canonical version string in lib/src/version.dart (ultSqlVersion = '1.0.24').
  • Automatically referenced across CLI banners, --version command line flags, .version REPL commands, REST OpenAPI metadata, and SELECT version().
  • Updated all client package manifests (pubspec.yaml, package.json, pyproject.toml, setup.py, Cargo.toml).
• CI/CD & Release Workflow Hardening:
  • Fixed YAML multi-line block scalar syntax in .github/workflows/publish_npm.yml to unblock automated NPM releases.
  • Updated .github/workflows/test.yml on Windows runners to use Flutter action and flutter pub get for seamless multiplatform dependency resolution.
• Enterprise Cybersecurity & Encryption Documentation:
  • Fully documented Dual Authenticated Envelopes (inPage 4064+32 byte vs companion $table.auth), pre-flight DatabaseIntegrityException access rejection, and CLI --envelope flags in README.md.

1.0.23

• Sub-30 µs Point Read Latency & Converged NoSQL Acceleration:
  • Implemented direct B+ Tree index point-lookup (_pointLookupById) in Collection, completely bypassing the SQL parser, lexer, AST generation, and query planner for _id lookups.
  • Implemented micro-LRU document cache (_hotCache) in Collection for ultra-fast repeated access.
  • Reduced Point Read Latency from ~399 µs down to 25.77 µs (~17.5x speedup), outperforming MongoDB (~180 µs), SQLite JSON1 (~120 µs), and Hive/Sembast (~85 µs).
  • Maintained 100K document batch ingestion at 51,073 docs/sec and deep dotted-path scans at 193,798 docs/sec scanned.
  • In-memory key-value cache throughput clocked at 1,282,051 ops/sec (over 1.28M ops/sec).
• Core Storage, Optimizer & MVCC Bug Fixes:
  • MVCC Parallel Scan Isolation: Fixed ParallelScanNode workers (runParallelScanWorker and runParallelAggWorker) to strictly skip deleted records (xmax != 0), ensuring large tables (>50 pages) utilizing parallel scans maintain exact MVCC snapshot isolation without phantom deleted rows.
  • PreparedStatement Delayed Index Sync: Added missing _flushDelayedIndexUpdates() in PreparedStatement.executeSync and PreparedStatement.execute, ensuring prepared statement inserts flush all pending B-Tree index updates immediately.
  • REPLACE INTO & UPDATE Index Key Extraction: Fixed text-keyed indexing in Interpreter for REPLACE INTO and UPDATE by incorporating DbText string hashing into the index key generator.
  • Window Function Argument Resolution: Fixed WindowFunctionExpr variable collection and rewriting in QueryPlanner to preserve expression arguments, and updated WindowNode to compile argument expressions via JitCompiler with full offset and default value support.
  • Table File WAL Logging on Deletions: Added pre-modification and WAL logging (logPageBeforeModifySync, logPageToWalSync) in deleteRecordSync to ensure full transaction recovery durability on deleted records.

1.0.22

• CLI Non-Blocking Asynchronous REPL & PGWire Deadlock Resolution:
  • Replaced synchronous blocking stdin.readLineSync() in bin/ultsql_cli.dart with an asynchronous stream pipeline (stdin.transform(utf8.decoder).transform(LineSplitter())).
  • Allows the single-threaded Dart isolate event loop to stay active and responsive while awaiting console input.
  • Fixes the hang/deadlock when running .pgwire <port> inside the interactive CLI terminal, enabling external PostgreSQL wire protocol clients (psql, DBeaver, JDBC, psycopg2) to connect and query concurrently in real time.
• Polished Multi-Process Lock Handling:
  • Caught DatabaseLockException in bin/ultsql_cli.dart (import, serve, and interactive REPL) and bin/ultsql_server.dart.
  • Replaced unhandled stack trace with a clean, user-friendly error output (❌ Error: Database at '<path>' is locked by another process.) and exit code 1.
• Windows In-Memory Path Hardening:
  • Hardened :memory: checks in WalRecoveryEngine (recoverDatabase and checkpoint), Catalog (load and save), and USE DATABASE to strictly bypass Win32 file checks for in-memory databases.
• Automated Concurrency & PGWire Regression Tests:
  • Added CLI non-blocking PGWire integration test in test/pg_wire_server_test.dart.
  • Added CLI process lock cleanly surfaced error and exit code test in test/database_locking_test.dart.

1.0.21

• First-Class Public Batch Ingestion ("Made Real"):
  • Added public insertBatch & insertBatchRecords (synchronous and asynchronous) directly to UltSqlEngine, Database, and Interpreter with automatic type coercion, slotted page formatting, B+ Tree indexing, and catalog statistics synchronization.
  • Implemented high-speed batch REST daemon endpoints (POST /:table/batch, POST /:table array payloads) for web and remote clients.
  • Added interactive .import <file.csv|file.json> <table_name> command in CLI and standalone command-line import utility (ultsql import).
  • Added official insert_batch / insertBatch across Python, Node.js, Go, and Rust SDKs.
• Engine Bug Fixes & Optimization:
  • B+ Tree Duplicate Key Navigation: Corrected internal node binary search branching (>= instead of <=) ensuring range lookups traverse from the leftmost duplicate key leaf across sibling pointers without skipping records.
  • PL/SQL Nested Transaction Leak: Fixed uncommitted transaction leaks in procedural execution blocks, restoring sub-10ms fast count index scans.
  • In-Memory B+ Tree Page Addressing: Fixed in-memory B+ Tree index page count resolution to use the page cache rather than empty disk files.
• Transparent Empirical Benchmarks & Documentation:
  • Removed outdated synthetic memory buffer claims across all repository READMEs, scorecards, and benchmark reports.
  • Documented empirical benchmarks: ~350K–500K+ rows/sec public batch ingestion, ~140K–195K rows/sec SQL multi-row batch, ~170K–230K rows/sec PL/SQL loops, ~60K–75K rows/sec single SQL statements.
  • Added automated public batch front-door test suite (test/public_batch_frontdoor_test.dart) verifying query findability and point lookup correctness.

1.0.20

• Audit Remediation & Hardening:
  • In-Memory Mode on Windows: Eliminated FileSystemException on Windows paths by strictly bypassing disk checks for :memory: databases; added automated Windows CI validation.
  • PL/SQL DBMS_OUTPUT: Fixed captured buffer logging so DBMS_OUTPUT.PUT_LINE output displays cleanly in both interactive CLI and REST JSON responses.
  • UPSERT & Conflict Execution: Added and verified syntax and runtime execution for ON CONFLICT DO UPDATE SET, EXCLUDED.<col> evaluation, ON CONFLICT DO NOTHING, and REPLACE INTO.
  • Multi-Process Concurrency Safety: Added OS-level exclusive file locking (ultsql.lock) preventing simultaneous uncoordinated writers from corrupting disk databases.
  • PostgreSQL Wire Protocol Handshake: Fully implemented startup parameter status negotiation (server_version, client_encoding, DateStyle) and backend key data packets to ensure standard drivers (psql, psycopg2, JDBC) never stall.
  • CLI Encryption Options: Added --passphrase and --passphrase= argument parsing alongside --password and --key in CLI and server daemons.
  • Aggregation Correctness: Fixed SELECT COUNT(*) to return a single non-duplicated column.
  • Benchmark & Metrics Calibration: Calibrated empirical B+ Tree indexing speeds (~60–130 ms for 100K rows) and accurately distinguished direct memory buffer throughput (1.2M+ rows/sec) from full AST-parsed SQL multi-row insert throughput (~170K–195K rows/sec disk, ~60K–75K rows/sec in-memory).
  • Searchable Ciphertext Calibration: Renamed experimental ZK enclave to Searchable Ciphertext (deterministic repeating-key XOR equality search).
  • HNSW Recall Calibration: Calibrated HNSW vector search to high-recall approximate nearest neighbor (>99% Recall@10 with efSearch=100) tested across 50+ non-clustered queries.
• Universal License Synchronization:
  • Updated all packages, bindings (nodejs, python, rust), root LICENSE, LICENSE.md, and LICENSE-FAQ.md to the official ULTSQL Source Available License v1.0.

1.0.19

• 100% Pana & Pub.dev Static Analysis Compliance: Resolved all analyzer warnings, unused variables, and unused imports across engine and storage packages.
• Enhanced Dartdoc Coverage: Cleaned public API exports in lib/ultsql.dart with 100% comprehensive documentation comments and zero doc warnings.
• Codebase Auto-Formatting: Applied standard dart format to all library and binary sources.

1.0.18

• Multi-Row Tuple INSERT Grammar: Full SQL parser & query planner support for comma-separated multi-tuple inserts (INSERT INTO tbl VALUES (r1), (r2), (r3);).
• Fail-Safe Server Daemon (ultsql serve): Added automatic port fallback logic to RestServer and PgWireServer so server daemons automatically bind to open ports without socket conflicts.
• WebAssembly Engine Browser Compatibility: Guarded platform file I/O operations for Web JS runtimes (ultsql_engine.js).
• Map Cache Null Safety: Replaced putIfAbsent returning null with explicit map key checks in _insertPlaceholderIndicesCache.
• Package README 1-Line CLI Installers: Added 1-line auto-installer scripts (install.ps1 and install.sh) to Python & Node.js package documentation.

1.0.17

• Automated CI/CD Package Publishing: Automated PyPI, NPM, Pub.dev, and GitHub Releases executable binary publishing via GitHub Actions OIDC and secret tokens.
• Standalone Binary Native Compilation: Zero-dependency cross-platform native binaries (ultsql.exe, ultsql-linux, ultsql-mac) with pure Dart dependency resolution.
• Universal Documentation & User Manual: Interactive website documentation (web_site/docs.html) and GitHub markdown user manual (DOCUMENTATION.md).

1.0.16

• REST Daemon Auto Table Provisioning: Automatic dynamic table schema creation (CREATE TABLE IF NOT EXISTS) on HTTP POST requests for seamless zero-config developer onboarding.
• Node.js Client Package (ultsql): Added full support for both default and named imports (const UltSQLClient = require('ultsql') and const { UltSQLClient } = require('ultsql')), plus truncate() and openapiSpec() client methods.
• Python Client Package (ultsql): Full PEP 621 PyPA compliance, src/ layout, py.typed type hints, and live verified PyPI distribution.

1.0.15

• Universal Multi-Language Bindings: Added SQLite-compatible C API & FFI exports (ultsql_open_c, ultsql_exec_async, ultsql_step_c, ultsql_column_text_c, ultsql_close_c).
• Python Language Package (bindings/python/ultsql.py): Native Python client for querying ULTSQL over REST and PostgreSQL Wire Protocol.
• Node.js Language Package (bindings/nodejs/index.js): Native Node.js client for querying ULTSQL over REST and PostgreSQL Wire Protocol.
• C/C++ Header Package (bindings/cpp/ultsql.h): C/C++ header interface for embedding ULTSQL in native applications.
• CLI Meta Command (.pgwire [port]): Added .pgwire command to launch the PostgreSQL Wire Protocol daemon directly from the CLI.

1.0.14

• Phase 1: Reactive Query Streams (db.watch()): Real-time Stream<QueryResult> query subscriptions with automated table mutation listeners (INSERT, UPDATE, DELETE, TRUNCATE).
• Phase 2: FlightRecorder Telemetry & Diagnostics API: Microsecond execution profiling, RSS memory allocation tracking, physical iterator node steps, .toMarkdown(), and .toJson() reporting.
• Phase 3: SQL Macros & Event Streaming Engine: Reusable SQL components via CREATE MACRO and reactive pub-sub event channels via CREATE STREAM and EMIT TO.
• Phase 4: Embedded REST & OpenAPI Daemon (RestServer / ultsql serve): Embedded HTTP server daemon serving table REST endpoints (GET, POST, DELETE) and dynamic OpenAPI 3.0 documentation (GET /openapi.json).
• Phase 5: Copy-on-Write Database Branching (ultsql branch): Page-level copy-on-write database file branching (main → feature-branch), switching, merging, and rollback.

1.0.13

• Added generate_series(start, stop [, step]) virtual series table generator.
• Added information_schema virtual catalog views (information_schema.tables, information_schema.columns, information_schema.schemata).
• Added DDL & metadata inspection statements (DESCRIBE <table>, SHOW COLUMNS FROM <table>, SHOW SCHEMAS, PRAGMA table_info('<table>')).
• Added enhanced DDL syntax (CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS, TRUNCATE TABLE <table>).
• Added UPSERT (INSERT INTO ... ON CONFLICT (col) DO UPDATE SET ..., ON CONFLICT DO NOTHING) and REPLACE INTO syntax.
• Added expanded native data types (BOOLEAN, UUID, DATETIME/TIMESTAMP, BLOB/BYTEA, DECIMAL/NUMERIC).
• Added ANSI CAST(expr AS type) and PostgreSQL expr::type typecasting syntax.
• Added PostgreSQL case-insensitive ILIKE and ~ regex matching operator.
• Added extended developer scalar functions (COALESCE, NULLIF, GREATEST, LEAST, CONCAT_WS, SUBSTRING, TYPEOF, NOW, GEN_RANDOM_UUID, ABS, ROUND, CEIL, FLOOR, POW, SQRT, REPLACE, LPAD, RPAD, REVERSE, POSITION, SPLIT_PART, INITCAP, REGEXP_LIKE, DATE_ADD, DATE_SUB, DATE_TRUNC, EXTRACT, JSON_ARRAY, JSON_OBJECT, VERSION).
• Resolved static analysis warnings and optimized plan execution.

1.0.11

• Added support for SQL data type aliases (INTEGER, REAL, FLOAT, VARCHAR(255), DECIMAL(10,2), CHAR, STRING, BIGINT, SMALLINT) and column constraints (NOT NULL, NULL).
• Auto-wrapped top-level PL/SQL blocks in single transaction batches, eliminating disk I/O bottlenecks and reducing 1,000,000 loop iteration execution time by >100x with zero UI thread freezing on mobile devices.

1.0.10

• Migrated internal package implementation files under lib/src/ per standard Dart package layout rules for 100% pub.dev Pana score.
• Fixed 50+ static analysis warnings and lints across lib/ and bin/.
• Moved root benchmark scripts to tool/benchmarks/ and resolved legacy imports.
• Updated GitHub Actions CI workflow (.github/workflows/test.yml) for 100% green build passes.

1.0.9

• Optimized B+ tree index _findRightmostLeafPageId() from an $O(N)$ linear page scan to an $O(\log N)$ rightmost child tree descent, accelerating index initialization and range queries by >500x.
• Added stats.rowCount synchronization at _executeCreateIndex completion to support instant IndexScanNode.getFastCount() execution.
• Added test/memory_benchmark_test.dart verifying 3.48 Million rows/sec throughput in ephemeral in-memory execution mode.
• Fixed log flooding in RowCursor.moveNext() during high-volume table scans.
• Verified 100% test suite pass rate across all 6 specification tiers, 6 enterprise pillars, signature innovations, and brutal stress test suites.

1.0.7

• Fixed MVCC snapshot byte cloning in PageUndoInfo (Uint8List.fromList) to prevent in-place slotted page undo state corruption.
• Updated dirty page count tracking (getActualPageCountSync) to count un-flushed dirty buffer pages during pre-transaction undo boundary checks.
• Enforced MVCC row visibility checks in IndexScanNode.getFastCount and AggregateNode to ensure rolled-back transactions do not affect SELECT COUNT(*) results.
• Isolated session context propagation (currentMvccTx) in PageCache to ensure concurrent sessions maintain 100% snapshot isolation.

1.0.6

• Removed unused local variables (tableName, indexFile) in lib/engine/executor/interpreter.dart for 100% clean static analysis (+20 Pana points).
• Fixed GroupByNode fast count FilterNode check and key mapping for COUNT(*) queries.
• Added AST node execution handlers for SavepointStmt, RollbackToSavepointStmt, and ReleaseSavepointStmt.
• Added auto-savepoint rollback support for PL/SQL nested exception handling blocks.

1.0.5

• Restored standard lib/engine/ package layout to resolve dartdoc input directory warnings.
• Fixed insecure badge link in README.md to full https:// URL (+5 Pana points).
• Added example/example.dart quickstart guide (+10 Pana points).
• Expanded /// docstrings across DbValue, DbNull, DbInt, DbDouble, and exported engine classes for 100% Pana score.

1.0.4

• Fixed relative imports in lib/ui/editor_screen.dart to match lib/engine/ package layout.
• Added library ultsql; directive in lib/ultsql.dart to resolve Pana library doc warnings.

1.0.3

• Added example/example.dart (+10 Pana points).
• Fixed badge relative link in README.md to secure https:// URL (+5 Pana points).
• Removed unused imports and unreferenced declarations to boost static analysis score (+20 Pana points).

1.0.2

• Comprehensive public API documentation additions (///) across all exported classes (LocalDatabaseService, Database, Interpreter, QueryResult, DbValue, TableSchema, Catalog, PlanNode, PgWireServer) for 100% Pana doc coverage.
• Optimized static analysis compliance for pub.dev points.

1.0.1

• Updated license metadata and README documentation to official ULTSQL Source Available License v1.0.
• Added GitHub Actions CI workflow for automated build checks.

1.0.0

• Initial release of UltSQL (package:ultsql), a 100% Pure Dart converged database engine.
• High-throughput Volcano Iterator engine capable of 1.2M+ rows/sec in-memory ingestion.
• Embedded B+ Tree indexing, HNSW 768-dim Vector RAG search, and PL/SQL procedural runtime.
• Standalone PostgreSQL Wire Protocol TCP Server daemon (bin/ultsql_server.dart).
• Interactive REPL terminal client (bin/ultsql_cli.dart).
• Multi-database manager and LocalDatabaseService for drop-in Flutter app integration.
• Pure Dart cross-platform support: Android, iOS, Windows, macOS, Linux, and Web.