===============================================================================
 ISQL VLR SUBSYSTEM - ARCHITECTURE  (task #72)                     rev 0, draft
 Variable-Length-Record tables.  Author: Muhammad Anisur Rahman.
===============================================================================

Status: DESIGN DRAFT (architecture only - no code yet). Next steps after this
doc: (2) API names, (3) implementation. This file is the architecture; it is
deliberately NOT folded into WEBSUPPORT_ICPP_PLAN.md.


0. NAMING  (resolve the collision first)
-------------------------------------------------------------------------------
"V-table" is ambiguous - it already reads as "vector table" (the storage-float
vector feature, HasStorageField). So this subsystem is named VLR = Variable
Length Record. Files/libs/symbols use the VLR prefix, never "V". A table's
record format is one of:
    FIXED  - the legacy fixed-width record (unchanged, default today)
    VLR    - variable-length record (this subsystem)
Vector/storage-float fields are an orthogonal field TYPE and can appear in
either format.


1. GOAL & SCOPE
-------------------------------------------------------------------------------
GOAL: stop wasting disk on fixed-width padding. A CHAR/VARCHAR/blob field today
occupies its declared width in every row even when the value is short; VLR
stores only the bytes actually used, so a table of mostly-short values shrinks
dramatically.

SCOPE / POLICY DECISIONS (from design discussion):
 - VLR becomes the DEFAULT record format for NEWLY created user tables, on both
   the native-OS-filesystem backend AND the container backend. (A CREATE option
   can still force FIXED.)
 - LEGACY fixed-width tables STAY exactly as they are - untouched code path,
   O(1) field access. No migration is forced; both formats coexist.
 - SYSTEM CATALOGS stay FIXED-WIDTH, always: dblist, systable, sysdb, hostname
   (and the kmg* access/group/index/lock tables). The catalog layer must remain
   dead-simple and seekable; it is never VLR.


2. WHERE THE FORMAT IS FLAGGED
-------------------------------------------------------------------------------
The on-disk TABLE_HEADER already carries a `tabletype` char (today 'K'/'E' mark
the extended-fieldname variant). Add a record-format discriminator:
    tabletype value (or a new header byte) = VLR marker, e.g. 'V'.
On open, OpenTableEx reads the header; if the format is VLR it routes the
TABLEOBJECT through the vlrlib code path (see section 8). Fixed tables are
unaffected. The header also gains VLR-only fields (all fixed-position, since the
header itself is always fixed): free-list head offset, high-water offset, and
the recno-index companion filename/inline root (section 5).


3. RECORD LAYOUT ON DISK
-------------------------------------------------------------------------------
Records are NOT at fixed offsets. Each record is self-describing and begins with
a fixed 24-byte prologue (refined during module-2 implementation):

    [ reclen:4 ][ flags:4 ][ recno:8 ][ next_record_offset:8 ][ field area ... ]

    reclen   total bytes of this record incl. the 24-byte prologue (u32; a single
             record is < 4GB, fine).
    flags    record-level bits (bit0 = DELETED).
    recno    the record's number, stored IN the record. Needed so the .vlx index
             is fully rebuildable from a chain walk after a crash (arch sec 5),
             and lets a scan report recno without a side lookup.
    next_record_offset  u64 file offset of the next live record (forward-linked
             chain for scans without the index), or 0 = end. u64 (not u32) so
             tables can exceed 4GB, matching the 64-bit engine. Deleted records
             are unlinked from this chain.

    (The earlier "[reclen:4][next_offset:4]" sketch is superseded by the above.)

Records are scattered through the file (append / free-slot reuse), so a scan
follows next_record_offset; a point lookup uses the recno index (section 5).


4. FIELD LAYOUT WITHIN A RECORD
-------------------------------------------------------------------------------
Fields also have NO fixed offset inside the record - they are packed. Two
candidate encodings; VLR uses (a):

 (a) LENGTH-PREFIXED, walked in declared order  [CHOSEN]
       per field:  [ flags : 1 ][ len : var ][ value bytes ]
       flags bit0 = NULL (then len=0, no value bytes).
       len is a varint (1 byte for <128, else multi-byte) to keep short fields
       tiny. Field access is O(fields): to read field k you walk fields 0..k
       summing their lengths. Acceptable - rows are small and field counts low;
       a per-record field-offset cache in RECORDOBJECT makes repeated access O(1)
       within a fetched row.
    -> Decision on the earlier open question "do you add a next-field offset in
       the field descriptor?": NO persistent per-field offset is stored on disk
       (that is what wastes space). The offset is COMPUTED on fetch and cached in
       memory only.

 (b) rejected: storing an explicit field-offset table at the record head - costs
     4*nfields per row, defeats the space goal.

TYPE SEMANTICS under VLR:
 - CHAR(n):  SEMANTICALLY FIXED - still space-padded to n and compared as width
   n (SQL CHAR semantics preserved), but on disk it is length-prefixed so
   trailing spaces need not all be stored (store the trimmed bytes + len; re-pad
   to n on read). Behavior identical to legacy CHAR; storage smaller.
 - VARCHAR(n): truly variable - store exactly the bytes given (<= n), len-prefixed.
 - NUMBER/INT/KINT64/DATE/TIME/DOUBLE: fixed binary width, still len-prefixed for
   uniformity (len is constant for these).
 - NULL: flags bit0; distinct from empty string (len 0, not-null).
 - storage-float VECTOR / blob / dir fields: length-prefixed payload; large blobs
   may still spill to the blob/ store with only a handle inline (unchanged).


5. RECNO -> OFFSET COMPANION INDEX
-------------------------------------------------------------------------------
Because records move, recno cannot be offset*recsize. A persistent companion
maps record number -> current file offset:

    <table>.vlx   (VLR indeX)  - an on-disk B-tree, KINT64 recno -> KINT64 offset.

Reuse the CLRS B-tree already written for the transaction table
(kmrnga17.cpp TransBt*, min-degree 32) but PERSISTED to a node file (the
transaction one is in-memory). FindTransRecNo / the record-locate path is
modified: for a VLR table, "find record N" = vlx lookup -> offset -> read
prologue. INSERT adds (recno,offset); UPDATE-that-moves changes the offset;
DELETE removes the key. The vlx is rebuilt from the next_record_offset chain if
lost (recovery).


6. GROWTH / SHRINK / MOVE  (no forwarding pointers)
-------------------------------------------------------------------------------
An UPDATE can make a row larger or smaller than its current slot.
 - SHRINK: write in place; the leftover tail becomes free space (section 7). No
   move, vlx unchanged.
 - GROW that still fits reclen slack: write in place.
 - GROW beyond the slot: the record is RELOCATED - written to a free slot (best
   fit) or appended at high-water; the old slot is freed; the vlx entry is
   UPDATED to the new offset; the next_record_offset chain is re-linked.
   -> Deliberately NO forwarding pointer left at the old location (unlike some
      engines). Legacy fixed tables cannot relocate a row (fixed slots), which is
      exactly why they must stay FIXED; VLR relocates + reindexes instead, which
      is clean and leaves no tombstone chains to chase.


7. FREE-SPACE MANAGEMENT / FRAGMENTATION
-------------------------------------------------------------------------------
Scatter + shrink + relocate leave holes. Manage them so the file does not grow
unbounded:
 - FREE LIST: a chain of free extents { offset, len, next } threaded through the
   holes themselves (the freed bytes store the node), head kept in the header.
 - ALLOCATION: best-fit over the free list; split a larger hole, returning the
   remainder to the list; else bump the high-water offset.
 - COALESCING: adjacent free extents merge on free.
 - COMPACTION: an explicit maintenance op (and an auto-trigger when
   free_bytes/file_bytes exceeds a threshold) walks the live chain, rewrites
   records densely from offset 0, rebuilds vlx, truncates the file. Runs under
   the table WRITE lock.


8. vlrlib - THE SEPARATE LIBRARY (redirection boundary)
-------------------------------------------------------------------------------
vlrlib is a SELF-CONTAINED library. The legacy engine does NOT grow VLR logic
inline; instead:
 - When the legacy table API (open/insert/fetch/update/delete/scan) sees a table
   whose format is VLR, it REDIRECTS control to the matching vlrlib entry point
   and RELAYS ONLY THE RETURN VALUE. The legacy layer takes no responsibility for
   VLR internals (offsets, packing, free list, vlx) - that all lives in vlrlib.
 - vlrlib depends only on: the low-level file primitives (KAM_OpenFile/ReadFile/
   WriteFile - already per-thread-cwd safe after #39), the header/field
   descriptors, and the B-tree. It does NOT reach back into query execution.
 - This keeps the blast radius small and lets VLR be tested in isolation.

Interface shape (names finalized in the next step, "API names"):
    vlr_open / vlr_close
    vlr_insert(record) -> recno
    vlr_fetch(recno) / vlr_next(scan)     (cursor over the next_offset chain)
    vlr_get_field(rec, fieldno) / vlr_set_field
    vlr_update(recno, record)             (handles in-place vs relocate)
    vlr_delete(recno)
    vlr_compact / vlr_freespace_stats
    vlr_index_* (vlx)                     (wraps the persisted B-tree)


9. ENCRYPTION KEY LAYOUT
-------------------------------------------------------------------------------
Requirement: a clean key hierarchy for VLR (fields are scattered, so key handling
cannot assume fixed offsets). Design:
 - KEY HIERARCHY (3 levels):
     MASTER (instance/db) key  ->  TABLE key  ->  per-RECORD IV.
   The table key is wrapped (encrypted) by the master key and stored ONCE in the
   table header (fixed region) - never per row. The master key is never on disk
   with the data (supplied/derived at connect, same trust model as today's login).
 - RECORD ENCRYPTION: each record's field-area is encrypted with the table key
   using a per-record IV = f(recno, reclen-nonce). The 8-byte prologue (reclen,
   next_offset) stays PLAINTEXT so scans/free-list/relocation work without
   decrypting - only the payload is ciphertext.
 - The vlx (recno->offset) may stay plaintext (it leaks only sizes/positions, not
   values) or be encrypted as a whole file with the table key - a build option.
 - FIELD-LEVEL keys (optional, future): a field flagged "sensitive" gets its
   value encrypted with a distinct column key wrapped by the table key; the
   length prefix stays plaintext so packing/walk still works.
 - Rationale: keeping prologue + lengths plaintext preserves O(fields) walking
   and free-space management without a decrypt on every structural touch, while
   values are protected. No key material is stored beside the data in the clear.


10. COEXISTENCE / INVARIANTS
-------------------------------------------------------------------------------
 - FIXED and VLR tables coexist in the same database; format is per-table.
 - System catalogs are always FIXED.
 - Concurrency: VLR uses the SAME table WRITE lock as today; relocation/compaction
   take it exclusively. Per-thread cwd (#39) already makes file opens safe.
 - Recovery: vlx and free list are reconstructible from the header + the
   next_offset live chain, so a crash mid-relocate is repairable by a scan.
 - Endianness / CPU32: prologue and lengths use the existing fixed-width on-disk
   integer conventions (honor the CPUSIZE/endian header fields).

-------------------------------------------------------------------------------
RESOLVED DEFAULTS (user: "proceed with sensible defaults", 2026-08-06)
 1. LENGTH PREFIX = varint (1 byte for len<128, high-bit continuation for more).
    Max space savings, which is the subsystem's purpose.
 2. COMPACTION = offline, takes the table WRITE lock exclusively; auto-triggers
    when free_bytes >= 30% of file_bytes (also callable manually). Online
    compaction deferred (future).
 3. .vlx INDEX = plaintext by default (leaks only sizes/positions, not values);
    a per-table option can encrypt it with the table key.
 4. DEFAULT FORMAT = opt-in for the FIRST implementation cut: CREATE TABLE ...
    ( recordformat = vlr ) selects VLR; a GlobalCfg flag (VlrDefaultOn) flips the
    engine-wide default to VLR once the subsystem is proven. Existing behavior
    (FIXED default) is unchanged until that flag is set. Honors the eventual
    "VLR is the default for both backends" intent without a risky big-bang.
===============================================================================
