-
Notifications
You must be signed in to change notification settings - Fork 5
Modules
cgnl edited this page Mar 28, 2026
·
9 revisions
plex-postgresql/
├── rust/plex-pg-core/src/ 100% Rust shim runtime
│ ├── lib.rs Crate root + module declarations
│ ├── c_abi/ C ABI entry points (sqlite3_* interception)
│ ├── runtime_macos.rs macOS: DYLD_INTERPOSE + fishhook init
│ ├── runtime_linux.rs Linux: LD_PRELOAD + dlsym(RTLD_NEXT)
│ ├── db_interpose_common/ Exception tracking, fork handlers, symbol loading
│ ├── db_interpose_open.rs sqlite3_open/close interception
│ ├── db_interpose_prepare/ sqlite3_prepare_v2 + SQL translation
│ ├── db_interpose_bind/ sqlite3_bind_* interception
│ ├── db_interpose_step/ sqlite3_step + execution control
│ ├── db_interpose_step_read_utils/ READ query execution (eager/streaming)
│ ├── db_interpose_step_write_utils/ WRITE query execution
│ ├── db_interpose_column/ sqlite3_column_* (text, int, blob, type, decltype)
│ ├── db_interpose_value/ sqlite3_value_* interception
│ ├── db_interpose_metadata/ Collation, connection state helpers
│ ├── db_interpose_exec/ sqlite3_exec interception
│ ├── db_interpose_stmt_lifecycle/ Statement finalize/reset
│ ├── pg_client/ Connection pool (50 default, auto-grow, TLS cache)
│ ├── pg_statement/ Statement lifecycle, registry, value pool
│ ├── pg_query_cache.rs Query result caching (thread-local, TTL)
│ ├── pg_config.rs Environment config, SQL classification
│ ├── pg_logging.rs Thread-safe logging, rotation, try_lock fallback
│ ├── functions.rs Function translations (iif, strftime, etc.)
│ ├── keywords/ Keyword translations (GLOB, COLLATE NOCASE)
│ ├── query.rs Query structure (ORDER BY, LIMIT, CASE)
│ ├── quotes.rs Quote translations (backticks → double-quotes)
│ ├── upsert.rs INSERT OR REPLACE → ON CONFLICT DO UPDATE
│ ├── fishhook.rs macOS runtime symbol rebinding
│ └── ffi_types.rs Core type definitions (PgStmt, PgConnection)
├── src/ Legacy C headers (documentation only)
├── include/
│ ├── sql_translator.h Translator public interface (C FFI)
│ └── legacy/ Archived C headers for reference
├── 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)
Since v1.1.0, the entire shim runtime is pure Rust. Since v1.2.0, 92% of unsafe raw pointer dereferences are eliminated — internal functions use safe &mut references, PgStmt uses Vec<T> and std::sync::Mutex, and 138 Rust-only functions are no longer extern "C".
| Module | Functions Intercepted |
|---|---|
db_interpose_open.rs |
sqlite3_open, sqlite3_open_v2, sqlite3_close, sqlite3_close_v2
|
db_interpose_prepare/ |
sqlite3_prepare_v2, sqlite3_prepare16_v2, sqlite3_finalize
|
db_interpose_bind/ |
sqlite3_bind_* (int, int64, double, text, blob, null), sqlite3_clear_bindings
|
db_interpose_step/ |
sqlite3_step, sqlite3_reset
|
db_interpose_column/ |
sqlite3_column_* (int, int64, double, text, blob, bytes, type, decltype, name, count, value) |
db_interpose_value/ |
sqlite3_value_* (type, text, int, int64, double, bytes, blob) |
db_interpose_exec/ |
sqlite3_exec |
db_interpose_stmt_lifecycle/ |
sqlite3_finalize, sqlite3_reset, ring tracking |
All PG modules are pure Rust with #[no_mangle] extern "C" FFI exports.
| Module | Responsibility |
|---|---|
pg_client/ |
Connection pool (50 default, max 200, auto-grow), TLS cache, auto-reconnect, SQLSTATE detection |
pg_statement/ |
Statement lifecycle, registry, reference counting, value pool, TLS cache |
pg_query_cache.rs |
Query result caching (thread-local, TTL-based eviction) |
pg_config.rs |
Environment config, SQL classification |
pg_logging.rs |
Thread-safe logging, level filtering, log rotation, try_lock deadlock prevention |
shim_alloc.rs |
Lock-free allocation tracker |
| Module | Responsibility |
|---|---|
lib.rs |
Entry point, FFI exports, AST transform pipeline |
functions.rs |
iif → CASE, strftime → EXTRACT, IFNULL → COALESCE, typeof, json_each
|
keywords/ |
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 |
upsert.rs |
INSERT OR REPLACE → ON CONFLICT DO UPDATE (28 table mappings) |
┌─────────────────────────────────────────────────────────────────┐
│ 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
712+ tests total, all in Rust.
cd rust/plex-pg-core && cargo test # All 712 Rust tests- 679 lib tests — SQL translation, AST transforms, interpose logic, pool management, statement lifecycle
- 33 integration tests — SQLite API compatibility, expanded SQL, bind parameters, pool behavior, shadow fallback