Skip to content

AsaDB v1.2.1

Latest
Latest

Choose a tag to compare

View all tags
@kocoygroup-id kocoygroup-id released this 12 Jul 22:34
v1.2.1
f909285
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

AsaDB v1.2.1 - The Storage Engine Release

AsaDB v1.2.1 is the largest storage, stability, and data-safety update since v1.2.0. The SQL surface remains familiar, but the engine underneath it has changed substantially: user rows now live in versioned 4 KB disk pages backed by a bounded buffer pool and persistent B+Tree indexes.

This release also fixes several high-impact correctness and usability issues, including unsafe targeted deletes, duplicate columns, two-click query execution, overlapping result sounds, and unreliable large-file imports.

01_asadb_v1 2 1_workspace

AsAPanel running two SQL statements against a compact summary of the measured 10k, 50k, and 100k storage benchmarks. The displayed UI run completed in 21 ms.

Highlights

  • Fixed 4 KB slotted pages for persistent user-table storage.
  • Persistent B+Tree equality, range, and ordered scans.
  • Bounded Clock-style buffer pool with pin/unpin protection.
  • Streaming SQL imports with transactional batches and rollback.
  • Recovery support through undo records, mutation backups, checksums, and atomic catalog replacement.
  • Stable 100,000-row stress test with approximately 230 MB peak working set.
  • One-click, single-flight Run SQL execution.
  • Single-channel result audio with no overlapping playback.
  • Correct targeted DELETE ... WHERE ... behavior on both small and large tables.
  • Duplicate ALTER TABLE ADD COLUMN protection and legacy duplicate cleanup.

From Prolog Heap To Page-Backed Storage

Earlier AsaDB versions kept normal user rows too close to the Prolog heap. That was convenient for a prototype, but memory use grew with the dataset and large imports exposed the limits quickly.

In v1.2.1, Prolog remains responsible for SQL parsing, planning, execution control, and recovery orchestration. User rows are stored separately in versioned page files under the matching .asa.store directory.

Each slotted page contains:

  • page ID and page type;
  • live-record count;
  • free-space boundaries;
  • slot directory;
  • previous and next sibling pointers;
  • page checksum.

Typed binary records replace normal list-backed Prolog row terms for migrated user tables. Page scans keep a file stream open instead of repeatedly reopening the heap file.

Persistent B+Tree Indexes

AsaDB now persists B+Tree index pages to disk. Linked leaf pages support ordered and range iteration, while indexed ORDER BY ... LIMIT can stop at the requested result window instead of sorting the complete table.

Index construction uses bounded external sorted runs. It does not create one giant in-memory list for a 100,000-row index build.

Current trade-off: writes that cannot preserve an index safely invalidate it. The next indexed access rebuilds that index from bounded disk runs. Fully incremental split/merge maintenance remains future work.

Bounded Buffer Pool

The new buffer pool provides:

  • configurable page limits;
  • page caching;
  • pin and unpin protection;
  • dirty-page tracking;
  • Clock-style replacement;
  • protected eviction for pages still in use;
  • incremental flushing;
  • cache hit, miss, eviction, dirty, pinned, and byte statistics.

The default pool is intentionally conservative at 64 pages because a 4 KB byte list has additional runtime overhead inside SWI-Prolog.

Streaming Import And Bounded Results

Backend SQL imports now read 256 KB blocks and execute bounded statement batches. Imports run inside a transaction, report statement/error progress, and roll back when a batch fails.

The importer periodically triggers garbage collection and stack trimming so old parse trees do not accumulate with the complete file. Multipart upload diagnostics remain in the server log and can no longer corrupt the HTTP JSON response.

Web results are capped by max_result_rows and rendered incrementally. Simple aggregate scans use incremental accumulators, and ordered top-N fallback uses bounded chunks.

Measured Performance

Test environment: Windows, SWI-Prolog, generated three-column rows, and 100-row multi-value INSERT statements. Peak working set includes the runtime, parser, index builder, page cache, and benchmark process.

Workload Import First indexed lookup including build Indexed ORDER/LIMIT UPDATE DELETE 500-row result Peak RAM
10,000 rows 7.1 s 6.1 s 80 ms 144 ms 109 ms 804 ms not separately sampled
50,000 rows 34.7 s 31.6 s 46 ms 234 ms 213 ms 948 ms 229.9 MB
100,000 rows 73.9 s 76.1 s 19 ms 519 ms 401 ms 654 ms 229.8 MB
02_asadb_v1 2 1_benchmark

All three benchmark workloads completed successfully. Measured peak memory stayed effectively flat from 50,000 to 100,000 rows.

The 100,000-row test also shut down the engine, reopened the database, verified the row count after UPDATE and DELETE, and validated recovery-visible state.

An earlier v1.2.1 prototype used approximately 820 MB and took almost 11 minutes for the same scenario. The final benchmark path completed in approximately 247 seconds with a 229.8 MB peak working set. The improvement came from changing the data path rather than increasing the memory limit.

Data Integrity And Recovery

  • Catalog checkpoints use temporary and backup files before replacement.
  • Append operations record the original file size and last page for undo.
  • UPDATE and DELETE mutations use transaction snapshots or page backups.
  • Startup recovery handles interrupted append, mutation, checkpoint, and open-transaction artifacts.
  • Failed import batches roll back instead of leaving a partially accepted file.

This is not an ARIES implementation and does not provide MVCC, but v1.2.1 provides a substantially safer recovery path than previous releases.

SQL Correctness Fixes

  • Fixed targeted DELETE ... WHERE ID = value on small and large tables.
  • Made table and column matching case-insensitive for ID, id, and mixed-case usage.
  • Prevented repeated ALTER TABLE ADD COLUMN from creating duplicate physical columns.
  • Normalized legacy duplicate columns and row keys during migration.
  • Preserved beginner-style bare text values such as Denji instead of silently converting them to NULL.
  • Preserved stable ordering for rows with equal sort keys.

Run SQL And AsAPanel Fixes

  • One click now starts exactly one backend request.
  • Rapid repeated clicks reuse the active promise and cannot duplicate writes.
  • The Run button is locked only during active execution and lightweight catalog refresh.
  • The panel no longer downloads the complete database state after every command.
  • Previous audio is stopped before the next success or failure effect starts.
  • Muted audio priming never blocks SQL execution.
  • Success and failure effects share one active channel.
  • Large results use bounded rows and incremental DOM rendering.

Browser-level validation measured one request for two rapid clicks, one additional request for the next run, a 7 ms empty-table backend query, an approximately 146 ms warm result/UI cycle, and at most one concurrent result sound.

03_asadb_v1 2 1_diagnostics

AsAPanel's current Indonesian feedback persona catches SELEC, catches FORM, and requests a missing semicolon while preserving the line number. English localization is planned as a future improvement.

SQL Compatibility

The existing SQL command surface remains available, including:

  • CREATE/DROP DATABASE and TABLE;
  • INSERT, SELECT, UPDATE, DELETE, and TRUNCATE;
  • ALTER TABLE column workflows;
  • WHERE, LIKE, BETWEEN, IN, IS NULL, ORDER BY, and LIMIT;
  • INNER, LEFT, and RIGHT JOIN;
  • GROUP BY with COUNT, SUM, AVG, MIN, and MAX;
  • basic scalar, IN, and EXISTS subqueries;
  • UNION, CASE WHEN, and CONCAT;
  • views and transaction commands;
  • basic users, grants, and metadata routines;
  • SQL command, multi-row INSERT, server file, and Choose File import paths.

Runtime Configuration

The portable package includes asadb.conf:

page_size = 4096
buffer_pool_pages = 64
import_batch_size = 8
flush_interval = 32
max_result_rows = 500
cache_policy = clock

page_size remains fixed at 4096 for format compatibility. Other limits can be tuned without rebuilding the executable.

Upgrade Notes

  1. Back up important v1.2.0 databases before upgrading.
  2. Keep every .asa catalog together with its matching .asa.store directory.
  3. Compatible list-backed v2 user tables migrate to v3 page storage during startup.
  4. The migrated catalog is saved at the next checkpoint.
  5. Start the Windows package with the single AsaDB.exe launcher.

Known Limitations

  • AsaDB is not benchmark-equivalent to PostgreSQL, MySQL, or CouchDB.
  • It does not yet provide MVCC or ARIES recovery.
  • Some writes invalidate and lazily rebuild affected B+Tree indexes.
  • Complex JOIN, GROUP BY, and expression plans may materialize more intermediate data than simple indexed scans.
  • The local panel is designed for localhost use and should not be exposed directly to the public internet.
  • The public portable build is currently Windows-focused.

Download

Download AsaDB-v1.2.1-Windows.zip from the Assets section below, extract it, and run:

AsaDB.exe

SHA256:

eb575811f3571f943bb4eaf38df41b2ea17a489f82ef995fdd1c4bf547d6e3f6  AsaDB-v1.2.1-Windows.zip

The matching AsaDB-v1.2.1-Windows.zip.sha256 file is also included in the release assets.

Feedback

Feedback on page layout, incremental B+Tree maintenance, query-planner statistics, recovery design, and Prolog memory trade-offs is especially welcome.

Repository: https://github.com/kocoygroup-id/AsaDB

If this kind of database-engineering experiment is useful or interesting to you, a GitHub star would genuinely help guide the next release.

Assets 4
Loading