Jan Doubravský
245 lines
15 KiB
Markdown
245 lines
15 KiB
Markdown
# SQLmem — Project Specification
|
|
|
|
## Cíl
|
|
|
|
Python modul fungující jako **transparentní cache vrstva mezi SQLAlchemy a databází**. Aplikace volá SQLAlchemy stejně jako dosud — SQLmem sedí mezi nimi, zachytává SELECT dotazy a vrací výsledky z in-memory SQLite cache. Zápisy (INSERT/UPDATE/DELETE) jsou přepouštěny přímo do databáze bez zásahu.
|
|
|
|
---
|
|
|
|
## Architektura
|
|
|
|
```
|
|
Aplikace (SQLAlchemy)
|
|
│
|
|
▼
|
|
[ SQLmem Proxy ]
|
|
┌───────────────────────────────┐
|
|
│ SQL Parser │ → rozezná SELECT vs. zápis
|
|
│ Cache Manager (SQLite RAM) │ → drží data v paměti
|
|
│ Query Executor │ → cache hit / miss logika
|
|
└───────────────────────────────┘
|
|
│
|
|
▼
|
|
Databáze (přes původní SQLAlchemy engine)
|
|
```
|
|
|
|
---
|
|
|
|
## API
|
|
|
|
Modul implementuje **SQLAlchemy event hooks / custom engine wrapper** — aplikace nemusí měnit způsob volání ani formát vrácených dat.
|
|
|
|
```python
|
|
from sqlmem import CachingEngine
|
|
from sqlalchemy import create_engine
|
|
|
|
base_engine = create_engine("postgresql://...")
|
|
engine = CachingEngine(base_engine)
|
|
|
|
# Dál se pracuje stejně jako s běžným SQLAlchemy enginem:
|
|
with engine.connect() as conn:
|
|
result = conn.execute(text("SELECT * FROM users WHERE status = 'active'"))
|
|
```
|
|
|
|
`CachingEngine` vrací objekty kompatibilní s `CursorResult` — `fetchall()`, `fetchone()`, `keys()` atd. fungují stejně.
|
|
|
|
---
|
|
|
|
## Cache backend
|
|
|
|
Dva režimy (volí se `CachingEngine(engine, in_memory=...)` nebo env `SQLMEM_IN_MEMORY`):
|
|
|
|
**In-memory (výchozí, `in_memory=True`)**
|
|
|
|
- **SQLite in-memory** jako primární úložiště — veškeré dotazy běží v RAM.
|
|
- **Persistence na disk** (`cache.db`) ve třech situacích:
|
|
- **Při startu**: pokud soubor existuje, načte se do paměti (`ATTACH` + kopie).
|
|
- **Periodicky každou hodinu**: snapshot in-memory SQLite se zapíše na disk (backup API).
|
|
- **Při vypnutí**: finální zápis na disk před ukončením (signal handler + context manager).
|
|
|
|
**Disk-backed (`in_memory=False`)**
|
|
|
|
- Dotazy běží přímo nad on-disk souborem `cache.db` — **žádná kopie v RAM**, cache může přesáhnout dostupnou paměť.
|
|
- Každý zápis se rovnou ukládá na disk (WAL + `synchronous=NORMAL`); odpadá hodinový backup thread i načítání do paměti při startu.
|
|
- Při otevření se cache s nesedícím `schema_version` zahodí a postaví znovu; `engine.reset()` smaže tabulky a provede `VACUUM` (soubor neodlinkuje).
|
|
|
|
Celé tabulky se při cache miss načtou z databáze (v obou režimech).
|
|
|
|
---
|
|
|
|
## Komponenty
|
|
|
|
### 1. SQL Parser
|
|
- Detekuje typ dotazu (SELECT / zápis); zápisy vyhodí `ReadOnlyError`.
|
|
- Extrahuje názvy tabulek z FROM a JOIN klauzulí (podpora více tabulek).
|
|
- Mapuje požadované sloupce na tabulky přes aliasy (`columns_by_table`).
|
|
- Detekuje `SELECT *` a `alias.*` → tabulka se načte celá (`wildcard_tables`).
|
|
- Parsuje přes dialekt `SQLMEM_SQL_DIALECT` (default `tsql`) a renderuje in-memory dotaz do SQLite (stripuje catalog/schema prefixy).
|
|
- Parametry (`?` / `:name`) předává beze změny do in-memory SQLite.
|
|
|
|
### 2. Column Registry
|
|
|
|
Modul se **za běhu učí**, jaké sloupce z každé tabulky aplikace potřebuje — nevyžaduje žádnou předem danou konfiguraci.
|
|
|
|
**Logika při každém příchozím dotazu:**
|
|
|
|
1. Parser extrahuje `(tabulka, sloupce)` pro každou tabulku v dotazu (i přes JOIN).
|
|
2. Registry provede **union** nově požadovaných sloupců s již známými.
|
|
3. Cache Manager zkontroluje, zda cache pro danou tabulku obsahuje všechny potřebné sloupce:
|
|
- **Ano** → dotaz jde přímo do SQLite RAM (cache hit).
|
|
- **Ne** → re-fetch tabulky z DB s rozšířenou sadou sloupců → přepíše cache → dotaz do SQLite RAM.
|
|
4. `SELECT *` načte celou tabulku a označí ji jako `is_full` → další dotazy na libovolný sloupec jsou cache hit.
|
|
|
|
**Příklad akumulace sloupců:**
|
|
|
|
```
|
|
Dotaz 1: SELECT A, B FROM T3 → Registry: T3 = {A, B} → fetch T3(A,B) z DB
|
|
Dotaz 2: SELECT A, D FROM T3 → Registry: T3 = {A, B, D} → re-fetch T3(A,B,D) z DB
|
|
Dotaz 3: SELECT B FROM T3 → cache hit, žádný DB dotaz
|
|
Dotaz 4: SELECT * FROM T3 → full load všech sloupců, tabulka označena is_full
|
|
Dotaz 5: SELECT A FROM T3 JOIN T4 ON … → každá tabulka cachována zvlášť, JOIN běží v RAM
|
|
```
|
|
|
|
**Metadata tabulka `_sqlmem_columns`** (uložena v SQLite):
|
|
|
|
```sql
|
|
CREATE TABLE _sqlmem_columns (
|
|
table_name TEXT NOT NULL,
|
|
column_name TEXT NOT NULL,
|
|
PRIMARY KEY (table_name, column_name)
|
|
);
|
|
```
|
|
|
|
- Při startu se načte z `cache.db` — Registry ví, co bylo kešováno v minulé session.
|
|
- Při každém rozšíření sady sloupců se záznam aktualizuje.
|
|
|
|
---
|
|
|
|
### 3. Cache Manager
|
|
- Drží in-memory SQLite instanci.
|
|
- Sleduje, které tabulky jsou již načteny.
|
|
- Podporuje TTL (Time-to-Live) pro automatické vypršení cache.
|
|
- Umožňuje manuální invalidaci konkrétní tabulky.
|
|
- **Persistence**:
|
|
- Při inicializaci načte `cache.db` ze disku (pokud existuje) do paměti.
|
|
- Spustí background vlákno, které každou hodinu provede `sqlite3` backup API (`conn.backup(disk_conn)`).
|
|
- Zaregistruje `atexit` handler a `SIGTERM` handler pro finální zápis při vypnutí.
|
|
- **Metadata tabulky** (`_sqlmem_meta`) uložená přímo v SQLite cache:
|
|
|
|
```sql
|
|
CREATE TABLE _sqlmem_meta (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
-- Záznamy:
|
|
-- app_version verze sqlmem balíčku (např. "0.1.0")
|
|
-- schema_version verze schématu cache.db (integer, pro migrace)
|
|
-- created_at ISO timestamp prvního vytvoření cache.db
|
|
|
|
CREATE TABLE _sqlmem_tables (
|
|
table_name TEXT PRIMARY KEY,
|
|
last_refresh_at TEXT NOT NULL, -- ISO 8601 UTC timestamp
|
|
row_count INTEGER
|
|
);
|
|
```
|
|
|
|
- `last_refresh_at` se aktualizuje pokaždé, když se tabulka znovu načte z databáze.
|
|
- Při načtení `cache.db` ze disku se zkontroluje `schema_version` — pokud nesedí, cache se zahodí a načte znovu z DB.
|
|
|
|
### 3. Query Executor
|
|
- **Cache hit**: Spustí dotaz přímo v in-memory SQLite, vrátí výsledek.
|
|
- **Cache miss**: Načte potřebné tabulky z databáze → uloží do cache → spustí dotaz.
|
|
- **Zápisy (INSERT / UPDATE / DELETE)**: Vyhodí výjimku `ReadOnlyError` a dotaz zablokuje. Modul je striktně read-only.
|
|
|
|
---
|
|
|
|
## Rozsah MVP
|
|
|
|
- [ ] `CachingEngine` wrapper kompatibilní se SQLAlchemy
|
|
- [ ] Načtení `cache.db` při startu do in-memory SQLite
|
|
- [ ] Periodický zápis na disk každou hodinu (background vlákno)
|
|
- [ ] Zápis na disk při vypnutí (`atexit` + `SIGTERM`)
|
|
- [ ] Parser pro detekci tabulek, sloupců a typu dotazu
|
|
- [ ] Column Registry s akumulační logikou (union sloupců, wildcard detekce)
|
|
- [ ] Re-fetch cache při rozšíření sady sloupců
|
|
- [ ] Cache Manager s SQLite in-memory backendem
|
|
- [ ] Cache hit/miss logika v Query Executoru
|
|
- [ ] TTL podpora na úrovni tabulky
|
|
- [ ] Manuální invalidace cache (`engine.cache.invalidate("tabulka")`)
|
|
- [ ] Testy pokrývající cache hit, cache miss a blokování zápisů (`ReadOnlyError`)
|
|
|
|
---
|
|
|
|
## Co modul NEŘEŠÍ (mimo scope)
|
|
|
|
- INSERT/UPDATE/DELETE — tyto operace jsou **zakázány** a vyhodí `ReadOnlyError`
|
|
- Redis nebo jiný distribuovaný backend
|
|
- Transakční konzistence
|
|
|
|
---
|
|
|
|
## Logování
|
|
|
|
Řízeno přes `loguru`. Úroveň se nastavuje v `.env`:
|
|
|
|
```env
|
|
SQLMEM_DEBUG=true # DEBUG level — podrobný výpis každého dotazu, cache operace, backup
|
|
# false (výchozí) — INFO a výše
|
|
```
|
|
|
|
| Level | Kdy |
|
|
|---|---|
|
|
| `DEBUG` | Každý příchozí dotaz, extrahované tabulky/sloupce, cache hit/miss/re-fetch, backup start/konec |
|
|
| `INFO` | Start/stop modulu, načtení cache.db, periodický backup, refresh tabulky |
|
|
| `WARNING` | Re-fetch tabulky kvůli novým sloupcům, blížící se TTL expirace |
|
|
| `ERROR` | `ReadOnlyError`, `UnsupportedQueryError`, selhání připojení k DB, selhání zápisu cache.db |
|
|
|
|
---
|
|
|
|
## Hotové funkce (dříve TODO)
|
|
|
|
- [x] **Parametrizované dotazy**: `execute(sql, params)` — poziční `?` i pojmenované `:name`.
|
|
- [x] **Podpora `SELECT *` (wildcard)**: Načte celou tabulku do cache, označí ji jako `is_full` — další dotazy na libovolný sloupec jsou vždy cache hit bez re-fetch.
|
|
- [x] **Podpora JOIN**: Parser extrahuje sloupce z každé joinované tabulky zvlášť, Column Registry je sleduje nezávisle. Cache Manager zajistí, že všechny potřebné tabulky jsou v paměti před spuštěním dotazu.
|
|
- [x] **Třídílné názvy tabulek**: `[catalog].[schema].[table]` se cachuje pod base name, in-memory dotaz prefix stripuje.
|
|
- [x] **Inkrementální (delta) refresh**: per-tabulku `DeltaConfig(change_column, key_columns)` — sync jen změněných řádků přes datový watermark `max(change_column)` (`>=` + idempotentní upsert podle klíče), catch-up na startu + background thread (`SQLMEM_REFRESH_INTERVAL`, default 300 s). PK se auto-zjistí ze zdrojové DB, pro views nutno zadat ručně.
|
|
- [x] **`engine.reset()`**: smaže celou cache (RAM + `cache.db`) pro čistý rebuild po strukturální změně.
|
|
- [x] **Sekundární indexy**: `indexes={"VW_X": ["col", ["a","b"]]}` — indexy na in-memory cache pro zrychlení `WHERE`/`JOIN`; index-sloupce se auto-dotáhnou, indexy se obnoví po každém (re)loadu.
|
|
- [x] **TTL na úrovni tabulky**: `ttl={"VW_X": 300}` — pro tabulky bez timestamp sloupce. Garantuje, že cache není starší než interval (full reload při čtení po expiraci + proaktivně na pozadí).
|
|
- [x] **Disk-backed cache**: `in_memory=False` (nebo `SQLMEM_IN_MEMORY=false`) — dotazy běží přímo nad on-disk `cache.db` (WAL), bez kopie v RAM; cache může přesáhnout paměť, zápisy se rovnou persistují.
|
|
- V disk módu čtení běží přes **per-thread read-only WAL connection** → souběžné čtení neblokuje zápisy ani ostatní čtenáře.
|
|
- [x] **Chyby refresh/load ve `stats`**: `TableStats.last_error` / `last_error_at` / `consecutive_failures` + `Stats.errors`. Delta, který selže před streamem, označí tabulku jako `error` (dřív zůstával `ready`).
|
|
- [x] **`last_upsert` vs `last_refresh`**: `last_upsert` = perzistovaný čas posledního zápisu dat (přežije restart); `last_refresh` = in-memory čas posledního běhu refresh cyklu (liveness — tiká i když cyklus nic nezapsal, `None` do prvního běhu). Prázdný delta cyklus posune `last_refresh`, ne `last_upsert`.
|
|
- [x] **Per-engine konfigurace**: `CachingEngine(..., cache_db_path=, backup_interval=, refresh_interval=, fetch_batch=, dialect=)` — každý parametr defaultuje na env/config; dva enginy s vlastními cache soubory v jednom procesu.
|
|
- [x] **Neblokující startup catch-up**: výchozí chování — startup catch-up (delta/TTL po restartu) běží na pozadí, neblokuje start aplikace. `blocking_startup_refresh=True` pro synchronní dohnání před servírováním.
|
|
- [x] **Quoting identifikátorů**: názvy tabulek/sloupců se kvótují (SQLite `"x"` pro cache, dialekt zdroje — T-SQL `[x]` — pro source), takže rezervovaná slova i mezery fungují.
|
|
- [x] **Lazy source connection**: `execute()` neotevírá spojení ke zdroji při cache hitu (neobsazuje pool slot).
|
|
- [x] **Idempotentní `add_sink`**: opakované volání pro stejný sink je no-op (žádné duplicitní logy).
|
|
- [x] **Ladění SQLite vrstvy (`pragmas=`)**: `CachingEngine(..., pragmas={...})` aplikuje libovolné PRAGMA na cache spojení (`mmap_size`, `cache_size`, `temp_store`, `page_size`, `auto_vacuum`). `page_size` a `auto_vacuum` jsou layout-pragmata — platí jen na čerstvém souboru (page_size na existující cache se ignoruje s warningem). Neznámá pragmata SQLite tiše ignoruje.
|
|
- [x] **`hard_reset()`**: smaže on-disk soubor (+ WAL/SHM) a otevře nový s aktuálními pragmaty — na rozdíl od `reset()` umožní změnit `page_size`/`auto_vacuum`. Jen disk mód (v memory módu fallback na `reset()`).
|
|
- [x] **`vacuum(incremental=, pages=)`**: údržbový VACUUM cache souboru — inkrementální (uvolní volné stránky po delta `INSERT OR REPLACE`, vyžaduje `auto_vacuum=INCREMENTAL`) nebo plný (přepíše soubor, jen v maintenance okně). V memory módu no-op.
|
|
- [x] **Nativní INTEGER ukládání datetime sloupců (`datetime_columns=`)**: `datetime_columns={"VW_X": ["CHANGE_DATE"]}` — vyjmenované datetime sloupce se ukládají jako INTEGER µs-od-epochy místo ~28 B ISO TEXT (úspora místa + nativní celočíselné porovnání indexu). Opt-in per sloupec. Breaking: `SCHEMA_VERSION` 3→4, cache se při upgrade smaže a načte znovu. Watermark se persistuje jako int a `_bind_watermark(epoch_us=True)` ho rekonstruuje zpět na `datetime` pro zdroj.
|
|
- **Param coercion**: `WHERE col > ?` s ISO/`datetime` parametrem se zkoercuje na epoch µs (scoped na tabulky dotazu), takže porovnání INTEGER sloupce sedí (dřív vracelo 0 řádků).
|
|
- **Read-time coercion**: čtení vrací `datetime` objekt místo raw int (transparentní proxy); opt-out `CachingEngine(..., return_datetime=False)`.
|
|
- Veřejný helper `from sqlmem import datetime_to_epoch_us` pro konstrukci parametrů bez duplicitní logiky.
|
|
- [x] **`vacuum(incremental=True)` varuje bez `auto_vacuum=INCREMENTAL`**: dřív tichý no-op; teď zaloguje warning (a jak to opravit) a vrátí se.
|
|
- [x] **`Stats.db_size_bytes`**: velikost cache souboru na disku (0 v memory módu) ve `stats` pro monitoring.
|
|
- [x] **Ochrana proti cache stampede**: `load_table` dělá double-checked locking — po získání `_load_lock` znovu ověří, zda tabulku mezitím nenahrál souběžný loader (cached + sloupce + ne-stale), a redundantní reload přeskočí. Bez toho druhý dotaz během studeného loadu velké tabulky spustil druhý plný reload (212M řádků = +2 h).
|
|
- [x] **Deklarativní inicializace (`tables=[TableSpec(...)]`)**: předem se deklaruje každá tabulka (`TableSpec(name, columns, indexes, refresh=Delta(...)|TTL(...), datetime_columns, preload)`). `preload=True` se na pozadí (nebo blokujícně) přednahraje při startu; co je v persistentní cache čerstvé, se přeskočí (warm restart = instant). Nedeklarovaná tabulka/sloupec (i `SELECT *` na sloupcově omezené tabulce) → `UndeclaredError` (fail-fast) místo tichého líného loadu; `columns=None` = celá tabulka + libovolný sloupec. Plně zpětně kompatibilní: bez `tables=` fungují staré `delta=/ttl=/indexes=/datetime_columns=` jako dřív (kombinace obojího → `ValueError`).
|
|
|
|
## TODO — budoucí funkce
|
|
|
|
- _(zatím žádné otevřené položky)_
|
|
|
|
---
|
|
|
|
## Technologie
|
|
|
|
| Vrstva | Knihovna |
|
|
|---|---|
|
|
| SQL parsing | `sqlglot` |
|
|
| Cache úložiště | `sqlite3` (stdlib) |
|
|
| Integrace | SQLAlchemy events / engine wrapper |
|
|
| Logování | `loguru`, `python-dotenv` |
|
|
| Testování | `pytest` |
|