Skip to content

How It Works

Jump to bottom
Sander edited this page Feb 22, 2026 · 3 revisions

How It Works

Overview

macOS:  Plex -> SQLite API -> DYLD_INTERPOSE shim -> SQL Translator -> PostgreSQL
Linux:  Plex -> SQLite API -> LD_PRELOAD shim    -> SQL Translator -> PostgreSQL
Docker: Plex -> SQLite API -> LD_PRELOAD shim    -> SQL Translator -> PostgreSQL (container)

The shim intercepts all sqlite3_* calls, translates SQL syntax (placeholders, functions, types), and executes on PostgreSQL via libpq.

Architecture

Layer 4+3: C interposer (~9,400 lines)        — fishhook, DYLD_INTERPOSE, LD_PRELOAD
Layer 2:   Rust PG modules (hybrid C/Rust)     — pool, statement, cache, config, logging
Layer 1:   Rust SQL translator (sqlparser-rs)   — full AST-based SQLite → PostgreSQL translation

src/
├── db_interpose_common.c      # Shared: function pointers, exception handling, fork handlers
├── db_interpose_core.c        # macOS: fishhook + DYLD_INTERPOSE initialization
├── db_interpose_core_linux.c  # Linux: LD_PRELOAD + dlsym(RTLD_NEXT)
├── db_interpose_*.c           # Shared: open, exec, prepare, bind, step, column, metadata
├── sql_translator_rust_bridge.c  # C bridge to Rust sql-translator
├── pg_*.c                     # Thin C shims calling Rust PG modules
rust/sql-translator/src/
├── lib.rs                     # Rust SQL translator (sqlparser-rs AST transforms)
├── 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              # Rust logging, config, pool, statement, cache
└── ...                        # Additional Rust modules

SQL Translation

Since v1.0.0, the SQL translator is fully implemented in Rust using sqlparser-rs for AST-based parsing and transformation (replacing the old C string-manipulation translator). The translator handles SQLite-specific syntax automatically:

SQLite PostgreSQL
COLLATE NOCASE LOWER() comparisons
WHERE column LIKE '%x%' COLLATE NOCASE WHERE column ILIKE '%x%'
WHERE 0 / WHERE 1 WHERE FALSE / WHERE TRUE
iif(cond, a, b) CASE WHEN cond THEN a ELSE b END
strftime('%s', x) EXTRACT(EPOCH FROM x)::bigint
IFNULL(a, b) COALESCE(a, b)
title MATCH 'action -comedy' FTS with ! negation
title MATCH 'term1 AND term2' FTS with & operator
title MATCH '"exact phrase"' FTS with <-> adjacency
? placeholders $1, $2, ... numbered params

Features

  • Connection pooling — efficient reuse of PostgreSQL connections (150 slots, atomic CAS state machine, idle reaper)
  • Single-row streaming — READ queries stream results row by row via PQsetSingleRowMode (see below)
  • Connection isolation — streaming connections are protected from concurrent use; pool and helper functions automatically use alternate connections
  • SQL translation — automatic SQLite -> PostgreSQL syntax conversion
  • Prepared statements — query caching for performance
  • Schema initialization — auto-creates PostgreSQL schema on first run
  • Circular reference protection — triggers prevent self-referential parent_id crashes
  • Stack overflow protection — multi-layer defense against crashes (see below)
  • Auto-build — wrapper automatically rebuilds shim if dylib is missing

Single-Row Streaming

Since v0.9.28, all READ queries use PostgreSQL's PQsetSingleRowMode to stream results one row at a time instead of loading entire result sets into memory.

How it works:

  1. The shim sends the query with PQsendQueryParams (async)
  2. Activates PQsetSingleRowMode — PostgreSQL sends rows one at a time (PGRES_SINGLE_TUPLE)
  3. Each sqlite3_step() call fetches the next row
  4. The final PGRES_TUPLES_OK sentinel signals end of results

Connection isolation (v0.9.29): While a connection is streaming, a streaming_active flag prevents other operations from using it. Internal functions like resolve_column_tables() and preload_decltype_cache() automatically borrow a separate pool connection. The pool's fast path and slow path both skip streaming connections when looking for an available slot.

Fallback: If PQsetSingleRowMode fails (e.g., older PostgreSQL), the shim falls back to eager fetch (loading all rows at once) automatically. No configuration needed.

Stack Protection

Plex uses small thread stacks (544KB) which can overflow during complex queries. The shim provides multi-layer protection:

Layer Threshold Action
Worker delegation < 400KB remaining Delegate to 8MB worker thread
Hard protection (normal) < 64KB remaining Return SQLITE_NOMEM
Hard protection (worker) < 32KB remaining Return SQLITE_NOMEM

This prevents stack overflow crashes that occurred with deep recursive queries (e.g., OnDeck with 218 recursive frames).

PostgreSQL Type Mapping

The shim translates SQLite types to PostgreSQL equivalents:

  • INTEGER -> INT4 (32-bit) or INT8 (64-bit based on context)
  • BIGINT -> INT8 (64-bit)
  • Aggregate functions (count, sum, max, min, avg) -> Declared as TEXT with 64-bit values

Aggregates are declared as TEXT to work around SOCI Issue #1190 — Plex's ORM has a bug parsing BIGINT values from aggregate functions. The TEXT declaration forces SOCI to use text-to-integer conversion which works correctly. This is transparent to Plex.

Database Triggers

The PostgreSQL schema includes triggers that maintain data integrity:

Trigger Table Purpose
prevent_self_ref_parent metadata_items Prevents parent_id = id (circular reference)
check_cross_section_parent metadata_items Prevents parent in different library section
metadata_items_search_update metadata_items Maintains title_sort from title
metadata_items_set_available_at metadata_items Sets available_at from created_at
trg_fix_orphan_season metadata_items Fixes seasons with no parent show
trg_fix_orphan_season_media media_parts Fixes orphan seasons via media part inserts
statistics_media_reject_empty statistics_media Rejects rows with NULL/0 timestamp
trg_clean_statistics_resources statistics_resources Periodic cleanup of old statistics

Run ./scripts/doctor.sh to check if all triggers are present and fix any missing ones.

Clone this wiki locally