-
Notifications
You must be signed in to change notification settings - Fork 10
Modules
Sander edited this page Feb 22, 2026
·
9 revisions
plex-postgresql/
├── src/ C interposer layer (Layer 4+3) + thin C shims
│ ├── db_interpose_core.c macOS: DYLD_INTERPOSE + fishhook initialization
│ ├── db_interpose_core_linux.c Linux: LD_PRELOAD + dlsym(RTLD_NEXT)
│ ├── db_interpose_common.c/h Shared: exception tracking, fork handlers, symbol loading
│ ├── platform_backtrace.c Unified backtrace (#ifdef for macOS/Linux)
│ ├── db_interpose_open.c sqlite3_open/close interception
│ ├── db_interpose_prepare.c sqlite3_prepare_v2 interception
│ ├── db_interpose_bind.c sqlite3_bind_* interception
│ ├── db_interpose_step.c sqlite3_step interception
│ ├── db_interpose_column.c sqlite3_column_* interception
│ ├── db_interpose_value.c sqlite3_value_* interception
│ ├── db_interpose_metadata.c sqlite3_column_name/count/decltype
│ ├── db_interpose_exec.c sqlite3_exec interception
│ ├── db_interpose.h Interpose declarations and macros
│ ├── sql_translator_rust_bridge.c C-to-Rust FFI bridge for sql-translator
│ ├── str_utils.c Portable string utilities
│ ├── pg_config.c/h Thin C shim → Rust config module
│ ├── pg_logging.c/h Thin C shim → Rust logging module
│ ├── pg_client.c/h Thin C shim → Rust pool module
│ ├── pg_statement.c/h Thin C shim → Rust statement module
│ ├── pg_query_cache.c/h Thin C shim → Rust cache module
│ ├── pg_mem_telemetry.c/h Thin C shim → Rust telemetry module
│ ├── shim_alloc.c/h Thin C shim → Rust alloc tracker
│ ├── pg_types.h Core type definitions
│ └── fishhook.c macOS runtime symbol rebinding
├── rust/sql-translator/src/ Rust crate (Layer 1 + Layer 2)
│ ├── lib.rs SQL translator entry point + FFI exports
│ ├── functions.rs Function translations (iif, strftime, etc.)
│ ├── keywords.rs Keyword translations (GLOB, COLLATE NOCASE)
│ ├── query.rs Query structure (ORDER BY, LIMIT, CASE booleans)
│ ├── quotes.rs Quote translations (backticks, brackets)
│ ├── placeholders.rs ? → $1 placeholder translation
│ ├── pg_logging.rs Logging, log rotation, level filtering
│ ├── pg_config.rs Environment config, SQL classification
│ ├── pg_client.rs Connection pool management
│ ├── pg_statement.rs Statement lifecycle, reference counting
│ ├── pg_query_cache.rs Query result caching (thread-local)
│ ├── pg_mem_telemetry.rs Optional allocation counters
│ └── shim_alloc.rs Lock-free allocation tracker
├── include/
│ ├── sql_translator.h Translator public interface (C FFI)
│ └── fishhook.h fishhook public interface
├── scripts/
│ ├── install_wrappers.sh Install Plex wrappers (macOS)
│ ├── install_wrappers_linux.sh Install Plex wrappers (Linux)
│ ├── uninstall_wrappers.sh Restore original binaries (macOS)
│ ├── uninstall_wrappers_linux.sh Restore original binaries (Linux)
│ ├── migrate_sqlite_to_pg.sh SQLite → PostgreSQL migration
│ ├── migrate_pg_to_sqlite.sh PostgreSQL → SQLite migration (rollback)
│ ├── migrate_lib.sh Shared migration library functions
│ ├── doctor.sh Diagnostic health check for installations
│ ├── docker-entrypoint.sh Docker container entrypoint
│ ├── standalone-entrypoint.sh Standalone Docker entrypoint
│ ├── analyze_fallbacks.sh Analyze fallback queries (passed to SQLite)
│ ├── benchmark.sh PostgreSQL raw benchmark
│ ├── benchmark_compare.sh Shell-based SQLite vs PostgreSQL comparison
│ ├── benchmark_compare.py Python SQLite vs PostgreSQL comparison
│ ├── benchmark_plex_stress.py Library scan + playback simulation
│ ├── benchmark_multiprocess.py Multi-process concurrent access test
│ └── benchmark_locking.py Database locking contention test
├── tests/
│ ├── src/ Unit test sources (38 files)
│ ├── test_group_by_rewriter.c GROUP BY rewriter test (31 tests)
│ ├── bench_cache.c Cache implementation benchmark
│ ├── bench_translation.c Translation pipeline benchmark
│ ├── bench_shim.c Full shim benchmark
│ ├── bench_pipeline.c Pipeline stage benchmark
│ ├── bench_micro.c Micro-operation benchmark
│ ├── bench_libpq.c Raw libpq benchmark
│ └── bench_sqlite_vs_pg.py SQLite vs PostgreSQL latency comparison
├── schema/
│ ├── plex_schema.sql PostgreSQL schema for Plex tables
│ ├── sqlite_schema.sql Reference SQLite schema
│ └── sqlite_column_types.sql Column type mapping reference
├── .github/workflows/
│ ├── ci.yml Unit test CI (1,075+ tests, Linux)
│ ├── docker-publish.yml Docker image publishing
│ ├── release-linux-artifacts.yml Linux release build (aarch64 + x86_64)
│ └── release-macos-artifacts.yml macOS release build (universal binary)
└── docs/ (documentation in wiki only)
Entry point for macOS. Uses DYLD_INTERPOSE for static interposition and fishhook for runtime symbol rebinding to intercept SQLite calls from dynamically loaded libraries (like SOCI).
Entry point for Linux. Uses LD_PRELOAD with dlsym(RTLD_NEXT) to intercept SQLite calls.
Platform-independent shared code: exception type tracking (__cxa_throw interception), fork safety handlers (pthread_atfork), signal handlers for crash diagnostics, common_load_sqlite_symbols() shared dlsym loading for both platforms (~60 functions), symbol verification, and common initialization/cleanup.
Unified backtrace module for both platforms. Uses #ifdef __APPLE__ for platform-specific frame collection (backtrace() on macOS, manual frame walking on Linux) and symbol resolution (backtrace_symbols() on macOS, dladdr() + /proc/self/maps on Linux). Shared rendering with box-drawing characters and LOG_ERROR output.
| Module | Functions Intercepted |
|---|---|
db_interpose_open.c |
sqlite3_open, sqlite3_open_v2, sqlite3_close, sqlite3_close_v2
|
db_interpose_prepare.c |
sqlite3_prepare_v2, sqlite3_prepare16_v2, sqlite3_finalize
|
db_interpose_bind.c |
sqlite3_bind_* (int, int64, double, text, blob, null), sqlite3_clear_bindings, sqlite3_bind_parameter_index
|
db_interpose_step.c |
sqlite3_step, sqlite3_reset
|
db_interpose_column.c |
sqlite3_column_* (int, int64, double, text, blob, bytes, type), sqlite3_column_value, sqlite3_data_count
|
db_interpose_value.c |
sqlite3_value_* (type, text, int, int64, double, bytes, blob) |
db_interpose_metadata.c |
sqlite3_column_name, sqlite3_column_count, sqlite3_column_decltype, sqlite3_changes, sqlite3_last_insert_rowid
|
db_interpose_exec.c |
sqlite3_exec |
Since v1.0.0, the SQL translator is fully implemented in Rust using sqlparser-rs for AST-based parsing and transformation.
| Module | Responsibility |
|---|---|
lib.rs |
Entry point, FFI exports (rust_translate_sql), AST transform pipeline |
functions.rs |
iif → CASE, strftime → EXTRACT, IFNULL → COALESCE, typeof, unixepoch, json_each
|
keywords.rs |
GLOB → LIKE, COLLATE NOCASE → ILIKE, operator spacing |
query.rs |
Query structure (ORDER BY, LIMIT -1, forward-ref joins, CASE boolean, GROUP BY) |
quotes.rs |
Backtick/bracket → double-quote identifier translation |
placeholders.rs |
? → $1, :name → $N numbered parameter translation |
All PG modules have their core logic in Rust with thin C shims for FFI.
| Rust Module | C Shim | Responsibility |
|---|---|---|
pg_client.rs |
pg_client.c |
Connection pool (50 default, max 200, auto-grow), auto-reconnect, SQLSTATE 26000/42P05 detection, DEALLOCATE ALL |
pg_statement.rs |
pg_statement.c |
Statement lifecycle, reference counting, metadata settings upsert |
pg_query_cache.rs |
pg_query_cache.c |
Query result caching (thread-local, TTL-based eviction) |
pg_config.rs |
pg_config.c |
Environment config, SQL classification (should_redirect, is_write/read_operation) |
pg_logging.rs |
pg_logging.c |
Thread-safe logging, level filtering, log rotation |
pg_mem_telemetry.rs |
pg_mem_telemetry.c |
Optional allocation counters |
shim_alloc.rs |
shim_alloc.c |
Lock-free allocation tracker |
┌─────────────────────────────────────────────────────────────────┐
│ Plex Query │
└─────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ Layer 1: SQL Translation Cache (Thread-Local) │
│ ───────────────────────────────────────────────────────────── │
│ • 512 entries per thread │
│ • Lock-free (no mutex contention) │
│ • FNV-1a hash with linear probing │
│ • Hit: 22.6 ns → Miss: 17.5 µs (775x speedup) │
└─────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ Layer 2: Prepared Statement Cache │
│ ───────────────────────────────────────────────────────────── │
│ • PostgreSQL server-side prepared statements │
│ • Avoids re-parsing SQL on PostgreSQL │
│ • Automatic per-connection │
└─────────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ Layer 3: Query Result Cache (Thread-Local) │
│ ───────────────────────────────────────────────────────────── │
│ • Caches SELECT results for identical queries │
│ • TTL-based eviction (configurable) │
│ • Hit rate tracking for statistics │
└─────────────────────────────────────────────────────────────────┘
| Cache Type | Hit Latency | Miss Latency | Speedup |
|---|---|---|---|
| Translation (TLS) | 22.6 ns | 17.5 µs | 775x |
| Query Result | ~1 ns (hash) | ~20 µs (PG query) | 20,000x |
Benchmark of different cache implementations (8 threads, 1M ops/thread):
| Implementation | Latency | Throughput | Notes |
|---|---|---|---|
| Mutex | 507 ns | 15.8 M/sec | Global lock contention |
| RWLock | 2,246 ns | 3.6 M/sec | Worse than mutex (!) |
| Thread-Local | 22.6 ns | 354 M/sec | No contention |
| Lock-Free | 22.9 ns | 350 M/sec | Similar to TLS |
Thread-local storage is 22x faster than mutex-protected global cache.
Plex App
│
▼
sqlite3_prepare_v2(sql)
│
├─ Check: Is this a PostgreSQL table?
│ No → Pass to real SQLite (shadow DB)
│ Yes ↓
│
├─ Translation Cache Lookup (22.6 ns)
│ Hit → Use cached PostgreSQL SQL
│ Miss → sql_translate() (17.5 µs) → Cache result
│
├─ Create pg_stmt_t with translated SQL
│
▼
sqlite3_bind_*(stmt, ...)
│
├─ Store parameters in pg_stmt_t
│
▼
sqlite3_step(stmt)
│
├─ Query Result Cache Lookup
│ Hit → Return cached PGresult
│ Miss ↓
│
├─ PQexecPrepared() → PostgreSQL
│
├─ Cache result if cacheable
│
▼
sqlite3_column_*(stmt, idx)
│
├─ PQgetvalue(result, row, idx)
│
├─ Convert to SQLite type
│
▼
Result → Plex App
sqlite3_open_v2("plex.db")
│
├─ pg_pool_get_connection()
│ ├─ Check TLS cached connection (fast path, 99% of calls)
│ ├─ If miss: Find free slot in pool
│ ├─ If no free: Create new connection (up to pool_size)
│ └─ Return pg_connection_t*
│
▼
... execute queries ...
│
▼
sqlite3_close(db)
│
├─ pg_pool_release_connection()
│ └─ Mark slot as available (don't close actual connection)
│
▼
Connection stays in pool for reuse
SQLite SQL
│
▼
┌──────────────────────────────────────────────────────────┐
│ 1. Schema Prefix │
│ metadata_items → plex.metadata_items │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 2. Placeholder Translation │
│ ? → $1, $2, $3... │
│ :name → $N (with mapping table) │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 3. Function Translation │
│ iif(a,b,c) → CASE WHEN a THEN b ELSE c END │
│ strftime('%s',x) → EXTRACT(EPOCH FROM x)::bigint │
│ IFNULL(a,b) → COALESCE(a,b) │
│ datetime('now') → NOW() │
│ SUBSTR(a,b,c) → SUBSTRING(a FROM b FOR c) │
│ INSTR(a,b) → POSITION(b IN a) │
│ typeof(x) → pg_typeof(x)::text │
│ unixepoch('now') → EXTRACT(EPOCH FROM NOW())::bigint │
│ json_each(x).value → jsonb_array_elements_text(x) │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 4. Type Translation │
│ BLOB → BYTEA │
│ INTEGER PRIMARY KEY → SERIAL (on CREATE) │
│ DDL: datetime, float, boolean, varchar → PG types │
│ sqlite_master → pg_catalog.pg_tables │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 5. Query Structure │
│ CASE WHEN x THEN 1 ELSE 0 END → boolean │
│ WHERE 0 / WHERE 1 → WHERE FALSE / WHERE TRUE │
│ LIMIT -1 → (removed) │
│ Forward-reference joins → reordered │
│ GROUP BY → add missing non-aggregate columns │
│ ORDER BY → add NULLS FIRST where needed │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 6. Keyword Translation │
│ GLOB '*term*' → LIKE '%term%' │
│ COLLATE NOCASE → ILIKE / LOWER() │
│ Operator spacing normalization │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 7. UPSERT Translation │
│ INSERT OR REPLACE → INSERT ... ON CONFLICT DO UPDATE │
│ INSERT OR IGNORE → INSERT ... ON CONFLICT DO NOTHING │
│ 28 table-specific conflict target mappings │
│ Special: COALESCE(updated_at), GREATEST(view_count) │
└──────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────┐
│ 8. Quote Translation │
│ `column` → "column" │
│ [column] → "column" │
└──────────────────────────────────────────────────────────┘
│
▼
PostgreSQL SQL
1,075+ tests total: 525 Rust + ~550 C across 25 suites.
cd rust/sql-translator && cargo test # All 525 Rust tests- 318 lib tests — SQL translation, AST transforms, function/keyword/query/quote/placeholder handling
- 54 batch1 + 51 batch2 + 42 batch3 + 60 batch4 — integration tests covering all Plex query patterns
make unit-test # All C tests (25 suites)
make ci-test # CI-safe subset (no LD_PRELOAD needed)