AsaDB v1.2.1

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.

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

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.

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 ...

AsaDB v1.2.0

AsaDB v1.2.0 Release Notes

Summary

v1.2.0 is a UX polish release on top of the v1.0.2 stability base. The database
engine remains Prolog-backed; this release focuses on the SQL command flow,
quick-action icons, and safer packaging.

Improvements From v1.0.2

• Added random query sounds for the SQL command Run button.
• Added four success variants from assets/Effect/Berhasil.
• Added four failure variants from assets/Effect/Gagal.
• Success/failure sound selection avoids repeating the same variant when possible.
• Sound playback is isolated from execution, so blocked audio cannot break a query.
• Replaced CSS-drawn save/drop quick-action icons with the provided PNG assets.
• Fixed static backend routes for the new PNG and MP3 assets.
• Added explicit media-src 'self' CSP permission for local audio playback.
• Query sounds are primed from the Run SQL pointer event before async backend work.
• Rebuilt the Windows package with the new assets included.

Stability Base Kept From v1.0.2

• Backend multipart upload endpoint for Import > Choose File.
• Large selected .sql files stream through the Prolog backend.
• Catalog/sidebar refresh after backend execution.
• Multi-table catalog regression coverage.
• Windows data\... paths and first-run journal creation.
• Duplicate rows preserved in ORDER BY when sort values are equal.
• Numeric-aware filtering/sorting for numeric-looking text.

Audit Coverage

Checked for this release:

• Prolog web backend load.
• Core SQL regression suite.
• Package asset references for sounds and icons.
• Runtime package contains all eight MP3 files.
• Runtime package contains both PNG quick-action icons.
• Package content does not include source/dev folders.

Validation

swipl -q -g "['src/asadb_web.pl'],halt."
swipl -q -s tests\run_tests.pl

JavaScript node --check was not run because Node.js is not installed on this
machine. The packaged JS asset references were still verified inside the final
release folder.

Notes

AsaDB remains a local developer database, not a complete MySQL clone. Keep
backups of important .asa files before heavy imports or destructive SQL.

AsaDB v1.0.2

AsaDB v1.0.2 Release Notes

Summary

v1.0.2 is a stability release for the Windows public package. The main goal is
to make real practice sessions smoother: large SQL imports, catalog refresh,
and multi-table visibility are now more reliable than v1.0.1.

Improvements From v1.0.1

• Added backend multipart upload endpoint: /api/import_upload.
• Import > Choose File sends SQL files to the Prolog backend when possible.
• Large selected .sql files no longer need to be fully loaded into browser memory.
• Backend imports still run statement-by-statement through the Prolog engine.
• Sidebar/table catalog refresh runs after backend execution more reliably.
• Added regression coverage for multiple tables in one active database.
• Fixed Windows data\... paths and first-run journal creation in release folders.
• Preserved duplicate rows in ORDER BY when sort values are equal.
• Improved numeric-aware filtering/sorting for numeric-looking text.

Audit Coverage

Checked for this release:

• SQL parser and executor core.
• ALTER TABLE.
• Joins, grouping, aggregates, subqueries, UNION, CASE, CONCAT.
• Auto increment with NULL and omitted values.
• Views in sidebar/catalog.
• Save/drop database actions.
• Backend large server-file import.
• Backend upload import from Choose File.
• Windows portable launcher path.
• Windows subfolder database path and journal creation.

Validation

swipl -q -s tests\run_tests.pl

HTTP upload import validation:

public_safety_archive_5500.sql
62 statements
0 errors
COUNT(*) = 5500

Package launcher validation:

AsaDB.exe cli data\package_feature.asa web\samples\feature-tour.sql
AsaDB.exe cli data\package_stress.asa "Stress Test\public_safety_archive_5500.sql"

Notes

AsaDB remains a local developer database, not a complete MySQL clone. Keep
backups of important .asa files before heavy imports or destructive SQL.

AsaDB v1.0.1

AsaDB v1.0.1 is a Windows public release focused on stability, permanent database actions, large SQL handling, and a cleaner one-click launcher.

Highlights:

• Added one-click AsaDB.exe launcher.
• Added Save Database and Drop Database actions.
• Improved permanent persistence for local .asa databases.
• Improved large SQL import stability.
• Improved auto increment behavior.
• Improved table scrolling, export flow, and user-friendly messages.
• Public package excludes engine source files.

SHA256:
DE872C018972A8FA8D478DE0584BC1BF724B9B5A0482407CC3B307F11EF2EE5E