# sql5 v2.0 - Client-Server Architecture
## Overview
v2.0 重架構為 client-server 模式：
- **Python package** (`sql5` on PyPI): pure Python client
- **Rust binary** (`sql5`): server process, 提供所有 SQL 功能
```
┌─────────────────┐ subprocess ┌─────────────────┐
│ Python client │ ←── JSON over stdio ──→ │ Rust server │
│ (sql5.connect) │ │ sql5 --server │
└─────────────────┘ └─────────────────┘
```
## Server Mode
Rust sql5 新增 `--server` flag：
```bash
sql5 --server # in-memory mode
sql5 --server mydb.db # disk persistence
```
Server 接收 JSON request，輸出 JSON response：
```json
// Request (stdin)
{"method": "execute", "sql": "SELECT 1"}
// Response (stdout)
{"ok": true, "columns": ["1"], "rows": [[1]], "affected": 0}
// Error response
{"ok": false, "error": "table not found"}
```
### Protocol
| `execute` | Execute SQL, return results |
| `close` | Shutdown server |
### Server Exit
- 收到 `{"method": "close"}` 後正常退出
- stdin 關閉時自動退出
- 最終會 flush 資料庫到磁碟（如果有開啟磁碟模式）
## Python Client
### Installation
```bash
pip install sql5
```
### Usage
```python
import sql5
# In-memory database
db = sql5.connect()
# Disk database
db = sql5.connect("/path/to/mydb.db")
# Execute SQL
cursor = db.execute("CREATE TABLE users (id INTEGER, name TEXT)")
db.execute("INSERT INTO users VALUES (1, 'Alice')")
# Fetch results
cursor = db.execute("SELECT * FROM users")
print(cursor.fetchone()) # [1, 'Alice']
print(cursor.fetchall()) # [[1, 'Alice'], [2, 'Bob']]
# Parameterized queries
db.execute("INSERT INTO users VALUES (?, ?)", (2, 'Bob'))
# Context manager
with sql5.connect() as db:
db.execute("CREATE TABLE t (id INTEGER)")
db.execute("INSERT INTO t VALUES (1)")
db.close()
```
### Binary Auto-Download
Python package 包含 `_binary.py`，會從 GitHub Releases 自動下載對應平台的 Rust binary。
- 支援 macOS (arm64/x86_64), Linux, Windows
- Binary 快取在 `~/.cache/sql5/`
### Environment Variables
| `SQL5_BINARY` | 指定 sql5 binary 路徑（用於開發/測試） |
## Project Structure
```
sql5/
├── src/ # Rust source
│ ├── main.rs # CLI entry + --server flag
│ └── interface/
│ ├── mod.rs
│ ├── repl.rs # REPL mode
│ └── server.rs # Server mode (JSON stdio)
├── sql5_pypi/ # Python package
│ ├── sql5/
│ │ ├── __init__.py # exports connect, Connection, Cursor, Error
│ │ ├── client.py # Python client (subprocess wrapper)
│ │ ├── _binary.py # auto-download Rust binary
│ │ └── __main__.py # CLI entry
│ ├── pyproject.toml
│ ├── setup.cfg
│ └── pypi.sh # publish to PyPI
├── test_sql5_server.py # integration tests
└── Cargo.toml
```
## CI/CD Pipeline
```
git tag v2.0.0
↓
GitHub Actions:
1. cargo build --release (4 platforms)
2. Upload binaries to GitHub Release
3. twine upload to PyPI (pure Python wheel)
4. python -m build in sql5_pypi/
5. twine upload dist/* to PyPI
```
Python wheel 是 `py3-none-any`，支援所有平台（因為所有平台的邏輯都在 Rust binary 裡，Python 只是 client）。
## Version History
| Version | Date | Changes |
|---------|------|---------|
| v2.0.0 | 2026-05-04 | Client-server architecture: Python client + Rust server |
## Design Decisions
### Why Client-Server?
1. **Pure Python package**: PyPI 上只需要一個 `py3-none-any` wheel，不需要 PyO3 編譯
2. **Cross-platform**: 所有平台支援由 Rust binary 提供，Python client 保持簡單
3. **Consistent API**: Python `sql5.connect()` 語法與 `sqlite3` 一致
4. **Separate concerns**: SQL parsing/execution 在 Rust，Python 負責通訊
### Alternatives Considered
**PyO3 Extension**: 技術上可行但缺點多：
- 20 種 wheel 組合（Python 3.8-3.12 × 4 platforms）
- 編譯時間長，CI 複雜
- Rust stable 版本兼容性問題
**Subprocess + JSON**: 當前方案，簡單有效。
### Limitations
- 每個 `Connection` 啟動一個 Rust subprocess
- 不支援多用戶並發（每個 database 只能有一個 server instance）
- IPC 延遲約 0.1-1ms（對大多數應用可忽略）
### Future Improvements
- [ ] TCP socket mode（支援多用戶）
- [ ] Connection pooling
- [ ] Async support (asyncio)
- [ ] Prepared statements