| 1 | # ADR-0004: SQL layer (WitSQL) for pywitdb — путь к CASA |
| 2 | |
| 3 | **Статус:** принято |
| 4 | **Дата:** 2026-06-06 |
| 5 | **Репозиторий:** [AI-Guiders/pywitdb](https://github.com/AI-Guiders/pywitdb) |
| 6 | **Движок:** [WitDatabase](https://github.com/dmitrat/WitDatabase) → `OutWit.Database` (`WitSqlEngine`) |
| 7 | **Связано:** [ADR-0001](0001-binding-architecture-bridge-and-native.md), [ADR-0002](0002-bridge-provider-keys-and-reopen.md), [ADR-0003](0003-native-c-abi.md) |
| 8 | **Потребитель (follow-up):** [casa-ontology-payload](https://github.com/AI-Guiders/casa-ontology-payload) — `agent_journal.db`, `agent_field.db` |
| 9 | |
| 10 | --- |
| 11 | |
| 12 | ## Решение (одной фразой) |
| 13 | |
| 14 | **SQL в pywitdb** через **`WitSqlEngine`** (пакет `OutWit.Database`): сначала **bridge** (`op: sql_exec` / NDJSON rows), затем **native C ABI v2**; Python — **тонкий фасад** (`connect` / `execute` / `fetchall`), совместимый с паттернами `sqlite3` в CASA. **Миграция CASA — после** стабильного SQL-контракта, не до. |
| 15 | |
| 16 | --- |
| 17 | |
| 18 | ## Контекст |
| 19 | |
| 20 | | Факт | Следствие | |
| 21 | |------|-----------| |
| 22 | | pywitdb v1 (ADR-0003) — только **KV + txn** | `agent_journal.py` (SQL append + `GROUP BY` + prune) на KV без боли не переезжает | |
| 23 | | CASA field (`agent_field.db`) — bulk routes + chunk | Теоретически KV, но цель — **единый `.witdb`**, не два движка | |
| 24 | | WitSQL ≠ SQLite | Нужна **матрица совместимости**, не слепой перенос DDL | |
| 25 | | Native win-x64 работает (ADR-0003) | SQL-native тяжелее (Engine + AOT trim), bridge — быстрый proof | |
| 26 | |
| 27 | **Primary driver миграции CASA:** единый стек `.witdb`, шифрование, cross-lang с .NET — **не** обещание «быстрее sqlite» на journal workload. Performance — **validate** (бенч после контракта), not assume. |
| 28 | |
| 29 | --- |
| 30 | |
| 31 | ## Архитектура слоёв |
| 32 | |
| 33 | ``` |
| 34 | casa/domain/agent_journal.py (sqlite3 → pywitdb.sql) |
| 35 | ↓ |
| 36 | pywitdb.sql.Connection / Cursor ← Python фасад |
| 37 | ↓ |
| 38 | ┌───────────────────┬───────────────────────┐ |
| 39 | │ bridge │ native (ABI v2) │ |
| 40 | │ op: sql_exec │ witdb_sql_exec(...) │ |
| 41 | │ WitSqlEngine │ WitSqlEngine in AOT │ |
| 42 | └───────────────────┴───────────────────────┘ |
| 43 | ↓ |
| 44 | WitDatabase (Core) + WitSqlEngine (OutWit.Database) |
| 45 | ``` |
| 46 | |
| 47 | **Зависимость:** `OutWit.Database.Core` (store) + **`OutWit.Database`** (SQL engine). Native project сегодня — только Core; SQL добавляет ссылку на Engine и расширяет trim roots. |
| 48 | |
| 49 | --- |
| 50 | |
| 51 | ## Python API (v1 SQL) |
| 52 | |
| 53 | Модуль: `pywitdb.sql` (или `pywitdb._sql` + re-export). |
| 54 | |
| 55 | ```python |
| 56 | import pywitdb.sql as wsql |
| 57 | |
| 58 | with wsql.connect("agent_journal.witdb", password=None, backend="auto") as conn: |
| 59 | conn.execute(_CREATE_SQL) |
| 60 | conn.execute( |
| 61 | "INSERT INTO skill_trace (at, tick, goal_id, skill, plan_step, outcome, payload) " |
| 62 | "VALUES (?, ?, ?, ?, ?, ?, ?)", |
| 63 | (at, tick, goal_id, skill, plan_step, outcome, payload_json), |
| 64 | ) |
| 65 | rows = conn.execute( |
| 66 | "SELECT payload FROM skill_trace WHERE goal_id LIKE ? ORDER BY id DESC LIMIT ?", |
| 67 | (f"%{goal_id}%", limit), |
| 68 | ).fetchall() |
| 69 | last_id = conn.last_insert_rowid() |
| 70 | ``` |
| 71 | |
| 72 | | Метод | Семантика | |
| 73 | |-------|-----------| |
| 74 | | `connect(path, password=..., backend="auto")` | open store + attach `WitSqlEngine` | |
| 75 | | `Connection.execute(sql, params=())` | DDL/DML; returns `Cursor` | |
| 76 | | `Cursor.fetchall()` / `fetchone()` | rows as `tuple` or `Row` (mapping по имени колонки — v1.1) | |
| 77 | | `Connection.commit()` / `rollback()` | txn boundary (map на WitSqlEngine / Core txn) | |
| 78 | | `Connection.last_insert_rowid()` | `LastInsertRowId` после INSERT | |
| 79 | | context manager | close + dispose engine | |
| 80 | |
| 81 | **Параметры:** позиционные `?` в Python → `@p0`, `@p1` или named `@name` в WitSqlEngine (зафиксировать в реализации; предпочтение `?` для sqlite-порта CASA). |
| 82 | |
| 83 | **Non-goals SQL v1:** DB-API 2.0 полнота, async cursor, SQLAlchemy dialect, multi-connection pool. |
| 84 | |
| 85 | --- |
| 86 | |
| 87 | ## Bridge protocol (расширение ADR-0001) |
| 88 | |
| 89 | После `open` на том же subprocess: |
| 90 | |
| 91 | ```json |
| 92 | {"id": 10, "op": "sql_exec", "sql": "CREATE TABLE IF NOT EXISTS skill_trace (...)"} |
| 93 | {"id": 10, "ok": true, "rows_affected": 0} |
| 94 | |
| 95 | {"id": 11, "op": "sql_query", "sql": "SELECT COUNT(*) AS c FROM skill_trace", "params": []} |
| 96 | {"id": 11, "ok": true, "columns": ["c"], "rows": [[42]]} |
| 97 | |
| 98 | {"id": 12, "op": "sql_exec", "sql": "INSERT INTO ...", "params": ["2026-...", 1, "g1", ...]} |
| 99 | {"id": 12, "ok": true, "rows_affected": 1, "last_insert_rowid": 7} |
| 100 | ``` |
| 101 | |
| 102 | | `op` | Назначение | |
| 103 | |------|------------| |
| 104 | | `sql_exec` | DDL, DML без result set | |
| 105 | | `sql_query` | SELECT → columns + rows (JSON-safe types) | |
| 106 | | `sql_batch` | optional v1.1 — несколько stmt в одной txn | |
| 107 | |
| 108 | Ошибки SQL: `error.code` = `sql_error`, message от движка. |
| 109 | |
| 110 | Bridge csproj: + `ProjectReference` на `OutWit.Database` (Engine), не только Core. |
| 111 | |
| 112 | --- |
| 113 | |
| 114 | ## Native C ABI (v2 — расширение ADR-0003) |
| 115 | |
| 116 | **Вариант A (принят):** тот же `WITDB_ABI_VERSION=1`, новые exports (minor-compatible): |
| 117 | |
| 118 | ```c |
| 119 | WitDbStatus witdb_sql_exec( |
| 120 | uintptr_t db, |
| 121 | const char* sql, |
| 122 | const char* params_json, // NULL or JSON array of values |
| 123 | int64_t* out_last_rowid, |
| 124 | int32_t* out_rows_affected); |
| 125 | |
| 126 | WitDbStatus witdb_sql_query( |
| 127 | uintptr_t db, |
| 128 | const char* sql, |
| 129 | const char* params_json, |
| 130 | char** out_result_json, // {"columns":[...],"rows":[...]} |
| 131 | uint32_t* out_result_len); |
| 132 | ``` |
| 133 | |
| 134 | `out_result_json` — caller frees via `witdb_buffer_free`. Вызовы через тот же **CLR worker**, что ADR-0003 (`WitDbClrThread`). |
| 135 | |
| 136 | **Вариант B (отклонён):** bump `WITDB_ABI_VERSION=2` только из-за SQL — избыточно, если KV exports не меняются. |
| 137 | |
| 138 | Native csproj: + Engine reference; `trimming.xml` расширить под `WitSqlEngine` / parser. |
| 139 | |
| 140 | --- |
| 141 | |
| 142 | ## CASA journal — матрица совместимости (acceptance) |
| 143 | |
| 144 | Источник: `casa/domain/agent_journal.py` (`_CREATE_SQL` + запросы). |
| 145 | |
| 146 | | Возможность SQLite в CASA | WitSQL | Действие | |
| 147 | |---------------------------|--------|----------| |
| 148 | | `CREATE TABLE IF NOT EXISTS` | ✅ | как есть | |
| 149 | | `CREATE INDEX IF NOT EXISTS` | ✅ | как есть | |
| 150 | | `INTEGER PRIMARY KEY AUTOINCREMENT` | ⚠️ | явный `AUTOINCREMENT` поддерживается; в тестах движка чаще `BIGINT` (implicit autoincrement на Int64); gate — contract test | |
| 151 | | `TEXT`, `INTEGER` | ✅ | `TEXT`, `INT` / `INTEGER` | |
| 152 | | `INSERT` + `lastrowid` | ✅ | `LastInsertRowId` | |
| 153 | | `ORDER BY id DESC LIMIT ?` | ✅ | параметризованный LIMIT | |
| 154 | | `GROUP BY` + `COUNT(*)` | ✅ | проверить в contract test | |
| 155 | | `LIKE ?` | ✅ | contract test | |
| 156 | | `DELETE ... WHERE id NOT IN (SELECT id ... ORDER BY ... LIMIT ?)` | ✅ | SQLite-compat fix: nested subqueries use `queryExpression` (parser + engine test `WitSqlEngineSqlitePruneTests`) | |
| 157 | | `sqlite3.Row` / dict by name | ⚠️ | Python `Row` mapping в фасаде | |
| 158 | |
| 159 | **Contract test (gate перед CASA ADR):** |
| 160 | `tests/contract/test_casa_journal_sql.py` — поднять schema journal на witdb (bridge), прогнать representative ops из `agent_journal.py` (insert, prune, aggregate, query_skill_trace). |
| 161 | |
| 162 | --- |
| 163 | |
| 164 | ## CASA field (фаза 2, после journal SQL) |
| 165 | |
| 166 | `agent_field.db` можно: |
| 167 | |
| 168 | - **A)** оставить на KV pywitdb (уже близко к модели keys), или |
| 169 | - **B)** перевести на SQL tables (`meta`, `routes`, `chunks`) в том же `.witdb` файле. |
| 170 | |
| 171 | Решение — в **отдельном ADR casa-ontology-payload** после green journal contract. |
| 172 | |
| 173 | --- |
| 174 | |
| 175 | ## Порядок реализации |
| 176 | |
| 177 | | # | Задача | Репо | |
| 178 | |---|--------|------| |
| 179 | | 1 | Bridge: `sql_exec` / `sql_query` + Engine ref | pywitdb `bridge/WitDbBridge` | |
| 180 | | 2 | `pywitdb.sql` фасад на bridge | pywitdb | |
| 181 | | 3 | Contract: CASA journal schema + ops | pywitdb `tests/contract/` | |
| 182 | | 4 | Native: `witdb_sql_*` + trim roots | WitDatabase `OutWit.Database.Native` | |
| 183 | | 5 | `pywitdb.sql` backend native | pywitdb | |
| 184 | | 6 | Бенч journal vs sqlite (optional report) | pywitdb или casa | |
| 185 | | 7 | ADR CASA + миграция `agent_journal.db` → `.witdb` | casa-ontology-payload | |
| 186 | |
| 187 | --- |
| 188 | |
| 189 | ## Последствия |
| 190 | |
| 191 | ### Положительные |
| 192 | |
| 193 | - CASA journal переносится без переписывания на KV. |
| 194 | - Один `.witdb` + шифрование + тот же reopen (ADR-0002). |
| 195 | - Bridge даёт SQL до готовности native AOT matrix. |
| 196 | |
| 197 | ### Отрицательные / риски |
| 198 | |
| 199 | - Engine в NativeAOT — **больший** артефакт и trim surface. |
| 200 | - Диалект: не все sqlite-конструкты; prune-subquery — точка риска. |
| 201 | - Два SQL path (bridge/native) — parity tests обязательны. |
| 202 | |
| 203 | ### Non-goals |
| 204 | |
| 205 | - SQLAlchemy dialect (ADR позже). |
| 206 | - `asyncio` SQL (обёртка `to_thread` — вне scope ADR-0004). |
| 207 | - Замена sqlite в **других** проектах репо (IncomeCascade и т.д.). |
| 208 | |
| 209 | --- |
| 210 | |
| 211 | ## Ссылки |
| 212 | |
| 213 | - WitSQL engine: `witdatabase/Sources/Engine/OutWit.Database/` |
| 214 | - CASA journal: `casa-ontology-payload/casa/domain/agent_journal.py` |
| 215 | - CASA field: `casa-ontology-payload/casa/domain/agent_field.py` |
| 216 | - Native worker: ADR-0003 `WitDbClrThread` |
| 217 | |