-
Notifications
You must be signed in to change notification settings - Fork 10
Modules
Sander edited this page Feb 9, 2026
·
9 revisions
plex-postgresql/
├── src/
│ ├── 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 frame collection)
│ ├── 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_metadata.c sqlite3_column_name/count/decltype
│ ├── db_interpose_exec.c sqlite3_exec interception
│ ├── db_interpose.h Interpose declarations and macros
│ ├── pg_types.h Core type definitions
│ ├── pg_config.c/h Configuration loading + SQL classification
│ ├── pg_logging.c/h Thread-safe logging infrastructure
│ ├── pg_client.c/h Connection pool management + auto-reconnect
│ ├── pg_statement.c/h Statement lifecycle, reference counting
│ ├── pg_query_cache.c/h Query result caching (thread-local, TTL-based)
│ ├── sql_translator.c SQL translation orchestrator + TLS cache
│ ├── sql_translator_internal.h Internal translator interfaces
│ ├── sql_tr_helpers.c String utilities (strdup, replace, etc.)
│ ├── sql_tr_placeholders.c ? → $1 placeholder translation
│ ├── sql_tr_functions.c Function translations (iif, strftime, etc.)
│ ├── sql_tr_query.c Query structure fixes (ORDER BY, LIMIT, CASE booleans)
│ ├── sql_tr_groupby.c GROUP BY strict mode rewriting + NULLS FIRST
│ ├── sql_tr_types.c Type translations (BLOB→BYTEA, DDL types)
│ ├── sql_tr_quotes.c Quote translations (backticks, brackets)
│ ├── sql_tr_keywords.c Keyword translations (GLOB, COLLATE NOCASE)
│ ├── sql_tr_upsert.c UPSERT/ON CONFLICT handling (28 table mappings)
│ └── fishhook.c macOS runtime symbol rebinding
├── include/
│ ├── sql_translator.h Translator public interface
│ └── 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 (25 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 (656 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
|
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 |
| Module | Responsibility |
|---|---|
sql_translator.c |
Main orchestrator, thread-local cache management |
sql_tr_helpers.c |
String utilities (strdup, replace, etc.) |
sql_tr_placeholders.c |
? → $1, :name → $2
|
sql_tr_functions.c |
iif → CASE, strftime → EXTRACT, IFNULL → COALESCE, typeof → pg_typeof, unixepoch, json_each
|
sql_tr_query.c |
Query structure fixes (ORDER BY, LIMIT -1, forward-ref joins, CASE boolean 0/1 → FALSE/TRUE) |
sql_tr_groupby.c |
GROUP BY strict mode rewriting (PostgreSQL requires all non-aggregate columns), NULLS FIRST ordering |
sql_tr_types.c |
BLOB → BYTEA, DDL type translation (datetime, integer, etc.), sqlite_master → pg_catalog
|
sql_tr_quotes.c |
Backticks → double quotes, bracket quotes → double quotes |
sql_tr_keywords.c |
GLOB → LIKE, COLLATE NOCASE → ILIKE, operator spacing |
sql_tr_upsert.c |
INSERT OR REPLACE → ON CONFLICT DO UPDATE with 28 table-specific conflict target mappings, special column handling (updated_at COALESCE, view_count GREATEST) |
| Module | Responsibility |
|---|---|
pg_client.c |
Connection pool (50 connections default, max 100), auto-reconnect on failure |
pg_statement.c |
Statement lifecycle, reference counting, metadata settings upsert |
pg_query_cache.c |
Query result caching (thread-local, TTL-based eviction) |
pg_config.c |
Environment variable configuration, SQL classification (should_redirect, is_write/read_operation) |
pg_logging.c |
Thread-safe logging, deadlock prevention |
┌─────────────────────────────────────────────────────────────────┐
│ 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
698 tests across 22 suites. CI runs 656 tests (18 suites); 3 suites (test-api, test-expanded, test-params) require the shim loaded via LD_PRELOAD and 1 suite (test-stack-macos) is macOS-only.
| Makefile Target | Test File | Tests | Description |
|---|---|---|---|
test-sql |
test_sql_translator.c |
198 | Full SQL translation pipeline |
test-config |
test_pg_config.c |
63 | SQL classification (should_redirect, should_skip) |
test-upsert |
test_upsert.c |
59 | UPSERT: 28 conflict targets, special columns |
test-types |
test_type_normalization.c |
42 | decltype normalization for SOCI |
test-soci |
test_decltype_soci_compat.c |
41 | SOCI std::bad_cast prevention |
test-groupby |
test_group_by_rewriter.c |
31 | GROUP BY rewriting + NULLS FIRST |
test-crash |
test_crash_scenarios.c |
27 | Crash scenarios from production |
test-bind |
test_bind_helpers.c |
27 | Binary detection, hex encoding |
test-common |
test_common_helpers.c |
25 | is_library_db_path, simple_str_replace |
test-cache |
test_query_cache.c |
25 | Query result cache (TTL, eviction) |
test-statement |
test_statement_helpers.c |
23 | metadata_settings upsert, ID extraction |
test-recursion |
test_recursion.c |
17 | Recursion guards, stack protection |
test-exception |
test_exception_handler.c |
17 | C++ exception interception |
test-buffer |
test_buffer_pool.c |
14 | column_text buffer expansion |
test-reaper |
test_pool_reaper.c |
12 | Connection pool idle reaper |
test-fts |
test_fts_quotes.c |
10 | FTS escaped quote handling |
test-fork |
test_fork_safety.c |
9 | pthread_atfork handlers |
test-logging |
test_logging_deadlock.c |
9 | Logging deadlock prevention |
test-tls |
test_tls_cache.c |
7 | Thread-local storage cache |
test-api |
test_sqlite_api.c |
-- | SQLite API (requires shim) |
test-expanded |
test_expanded_sql.c |
-- | expanded_sql + booleans (requires shim) |
test-params |
test_bind_parameter_index.c |
-- | Named params (requires shim) |
make unit-test # All 698 tests (22 suites, requires shim + PostgreSQL)
make ci-test # CI-safe 656 tests (18 suites, no shim needed)
make test-sql # Just SQL translator (198 tests)
make test-upsert # Just UPSERT (59 tests)