Unique-index lookups maintained from the schema #28

New issue

Closed

opened 2026-08-19 15:42:13 +00:00 by yuris · 0 comments

yuris commented

2026-08-19 15:42:13 +00:00

Owner

cache.get(state, table, key) looks up by primary key only. Any other unique column requires a full scan through cache.iter.

Once the generator emits unique constraints, the cache can maintain a lookup per unique column the same way it maintains the pk index — the Godot SDK generates one index object per constraint doing exactly this (core/module_table_unique_index.gd).

Why it matters in practice

The query shape this serves is the common one in a game: a player table keyed by an auto-increment id, with a unique identity column. "Which row is me?" is a lookup by identity, not by pk, and it runs on every connect and after every reconnect. Today that is O(n) over every cached player, on the main thread.

Shape

Maintained inside cache.apply_database_update alongside the existing pk store:

cache.find(state, "player", "identity", my_identity)

The correctness detail that makes this worth writing carefully: a unique column's value can change. When an update alters the indexed column, the old key must be erased before the new one is written, or the index keeps a stale entry pointing at a row that no longer holds that value. The Godot implementation handles this explicitly, and it is the case a naive version gets wrong.

Interaction with #21: update synthesis and index maintenance are looking at the same delete/insert pair. Whichever lands second should reuse the pairing the first established rather than diffing twice.

Acceptance

Unit tests in dev/test/test_cache.lua: lookup hit and miss, a value change moving the entry and leaving no stale key, a delete removing the entry, and the index surviving drop_table / drop_all.

Priority

Medium, and cheap once the schema carries constraints. Not a blocker — cache.iter is a correct if slow answer — but it is the difference between the cache being a store and being a database.

`cache.get(state, table, key)` looks up by primary key only. Any other unique column requires a full scan through `cache.iter`. Once the generator emits unique constraints, the cache can maintain a lookup per unique column the same way it maintains the pk index — the Godot SDK generates one index object per constraint doing exactly this (`core/module_table_unique_index.gd`). ## Why it matters in practice The query shape this serves is the common one in a game: a `player` table keyed by an auto-increment `id`, with a unique `identity` column. "Which row is *me*?" is a lookup by identity, not by pk, and it runs on every connect and after every reconnect. Today that is O(n) over every cached player, on the main thread. ## Shape Maintained inside `cache.apply_database_update` alongside the existing pk store: ```lua cache.find(state, "player", "identity", my_identity) ``` The correctness detail that makes this worth writing carefully: **a unique column's value can change**. When an update alters the indexed column, the old key must be erased before the new one is written, or the index keeps a stale entry pointing at a row that no longer holds that value. The Godot implementation handles this explicitly, and it is the case a naive version gets wrong. Interaction with #21: update synthesis and index maintenance are looking at the same delete/insert pair. Whichever lands second should reuse the pairing the first established rather than diffing twice. ## Acceptance Unit tests in `dev/test/test_cache.lua`: lookup hit and miss, a value change moving the entry and leaving no stale key, a delete removing the entry, and the index surviving `drop_table` / `drop_all`. ## Priority Medium, and cheap once the schema carries constraints. Not a blocker — `cache.iter` is a correct if slow answer — but it is the difference between the cache being a store and being a database.

yuris referenced this issue

2026-08-19 15:43:23 +00:00

v1.0.0 release checklist #24

yuris referenced this issue

2026-08-19 16:17:35 +00:00

codegen: keep the module's type information #36

yuris referenced this issue

2026-08-19 16:38:45 +00:00

codegen: keep the module's type information #36

yuris referenced this issue from a commit

2026-08-19 17:57:31 +00:00

cache: index rows by every single-column unique constraint (#28)