Skip to content

README.md

Latest commit

 

History

History
197 lines (166 loc) · 11.4 KB

README.md

File metadata and controls

197 lines (166 loc) · 11.4 KB
Raw

Database

Shared database library for IPTVnator providing Drizzle ORM schema and connection utilities.

Usage

// Full access (electron-backend)
import { getDatabase, initDatabase } from '@iptvnator/shared/database';

// Read-only access (agent-backend)
import { getReadOnlyDatabase } from '@iptvnator/shared/database';

// Schema and types
import { content, categories, playlists, type Content } from '@iptvnator/shared/database';

Exports

Schema (schema.ts)

  • Tables: playlists, categories, content, recentlyViewed, favorites
  • Types: Playlist, Category, Content, RecentlyViewed, Favorite (and New* variants)

Connection (connection.ts)

  • getDatabase(options?) - Full read-write access
  • getReadOnlyDatabase() - Read-only access for agent queries
  • initDatabase(options?) - Initialize with custom options
  • closeDatabase() - Close connection
  • getDatabasePath() - Get database file path

Connection observer (connection-observer.ts)

  • setDatabaseConnectionObserver(observer | null) - Called by initDatabase with each connection it opens, before any statement runs on it. The Electron main process registers one only with IPTVNATOR_PERF_CAPTURE=1, to count main-thread SQL statements. The module has no runtime dependencies and is also importable as @iptvnator/shared/database/connection-observer.

Activity-sync storage

@iptvnator/shared/database/activity-sync provides opt-in SQLite stores for activity state, local pending operations and server idempotency receipts. The module does not alter initDatabase() or migrate/upload legacy playback rows. Its constructors create only additive activity tables and indexes, and local projection/outbox changes share one synchronous transaction. Optional delivery leases live inside those JSON records and are checked during compare-and-swap; legacy records without leases remain valid. No schema migration is required. Server state and receipts also commit together, including durable reset barriers. The owning runtime must verify the selected instance/source incarnation and provide the connection. Electron read/list/pending/compare-and-swap operations run in the database worker through the fixed DB_ACTIVITY_LOCAL IPC. NAS routes use a separate server store and persisted source incarnations. See the self-hosted activity contract.

Favorites-sync storage

@iptvnator/shared/database/favorites-sync provides separate additive local projection/outbox tables and server state/receipt tables. Constructors preserve all historical favorites, catalogs, playlists, history and activity tables. Membership changes and local sync intent commit together; server removals retain a persistent revision. Electron uses the worker-only DB_FAVORITES_LOCAL CAS bridge. The owning runtime verifies instance, consent and source incarnation. See the favorites contract.

History-sync storage

@iptvnator/shared/database/history-sync owns additive source-scoped local/server history tables and durable operation receipts. It does not migrate, delete or upload legacy recently-viewed rows. Local projection/outbox updates share a transaction; native renderer writes use DB_HISTORY_LOCAL compare-and-swap. Server touches, removals and clears persist with their receipt decision. Server acceptance time uses a persisted per-instance clock in the same transaction as state and receipts. Its first write seeds from pre-clock source states; normal writes read only the instance clock, preserving cross-source order on restart or clock rollback without scanning all histories for every operation. Historical schema tests preserve populated 0.19–0.24 tables through rollback and reopening. See Recently Viewed sync.

Database Location

The SQLite database is stored at <data root>/databases/iptvnator.db. The data root is resolved in this order:

  1. IPTVNATOR_E2E_DATA_DIR (E2E runs only)
  2. IPTVNATOR_DATA_DIR (an operator-selected data root for deployments with a mounted volume, for example /data)
  3. ~/.iptvnator

Electron selects a separate database when connected to a source server: <data root>/server-profiles/<sha256(instanceId)>/databases/iptvnator.db. The main process chooses this profile before opening a database or starting its worker. Disconnecting returns to the retained standalone database.

Upgrade Compatibility And Migrations

M3U automatic EPG import uses nullable playlists.auto_import_epg: NULL keeps the historical default-on behavior, and 0 records an import opt-out. It is projected by metadata reads without loading the playlist payload. The additive migration runs before the canonical payload-last rebuild and is safe to repeat.

EPG metadata reads preserve recorded provenance: missing detected_epg_urls decodes as an empty detected set, never as the enabled epg_urls list. Legacy enabled guides remain stored and usable with unknown origin through full reads, metadata projections and subsequent writes.

Users may skip releases. The application must apply every required migration in dependency order when opening an older database, preserving user data without requiring intermediate application installations or a database reset. This is the repository policy in AGENTS.md, which CLAUDE.md imports.

src/lib/connection.ts owns initialization: createTables() creates missing schema objects, then runMigrations() applies column/index migrations and dedicated schema/data upgrades. One-off data migrations can record completion in app_state. Keep existing migration paths when adding new ones.

Xtream favorites are migrated by src/lib/favorites-table.ts before legacy content/category deduplication. The transactional table rebuild backfills typed provider identity and saved display metadata while retaining favorite IDs, dates and positions. Duplicate favorites of the same playlist/type/provider ID collapse to the earliest favorite record. content_id becomes nullable with ON DELETE SET NULL; owning playlist deletion still cascades. The legacy content deduplicator updates references without replacing favorite IDs. Favorites whose owning playlist was already missing remain stored. The rebuild temporarily disables foreign-key enforcement outside its transaction, permits only those pre-existing playlist violations by favorite ID, and rolls back if it introduces another violation. The original enforcement setting is restored on both success and failure. The additive identity_conflict column also upgrades already-durable tables without rebuilding them. Its default is false; catalog reconciliation persists known negative identity evidence independently of missing rows, and full saved identity confirmation clears it. Repeated initialization preserves it. Historical upgrade tests include tagged 0.19–0.24 schemas, duplicate categories, orphaned favorites, repeated startup and forced rollbacks. Keep snapshot migration before content deduplication so the originally favorited row's metadata survives.

When changing initialization:

  • Create required tables before migrating them. CREATE TABLE IF NOT EXISTS leaves existing columns unchanged.
  • Add missing columns before creating indexes or triggers, or running queries, that depend on those columns. Put indexes depending on migrated columns in INDEX_MIGRATION_STATEMENTS, after COLUMN_MIGRATION_STATEMENTS, rather than in the earlier schema creation phase.
  • Preserve existing data and make migrations safe on repeated startup. Record completion only after the associated migration succeeds.
  • Exercise the actual initialization path with real SQLite and historical schema fixtures containing representative playlists, favorites, history, and playback positions. Verify both schema changes and preservation of those rows; mocked SQL calls alone do not establish upgrade compatibility.
  • Include direct upgrades across skipped releases, the previous release, a fresh install, and repeated startup. If a release changes migration ordering, cover each distinct affected historical schema.

Two startup costs found on a 4 GB profile (3.9M catalog titles, 182 playlists) are guarded in runMigrations(), and measured weekly by the launch-large journey on a generated profile of that shape (performance journeys):

  • deduplicateXtreamCache() returns at once when both unique indexes (src/lib/xtream-cache-unique-indexes.ts: categories_playlist_type_xtream_unique, content_category_type_xtream_unique) exist: they make duplicates impossible, and its two GROUP BY ... HAVING COUNT(*) > 1 scans cost a full pass over content on every launch otherwise (7.8 s cold on that profile). A profile whose unique index could not be created yet still gets the repair.
  • payload must be the last column of playlists (PLAYLISTS_COLUMNS in src/lib/playlists-table.ts): SQLite reads a row's columns in order and walks the overflow pages of every column before the one it needs, so a column after an M3U payload costs the whole payload on each read (getAppPlaylistMetas took 4-22 s for 70 MB of payloads). Tables created before 0.19 got payload appended by ALTER TABLE with the EPG URL columns after it, and fresh installs up to 0.24 created last_usage after it; ensurePlaylistsPayloadLast() rebuilds such a table once (new table, copy, drop, rename, with foreign keys off) and is a no-op afterwards. It commits only if the tables referencing playlists hold no more foreign key violations than before; rows a legacy profile already orphaned do not block it. src/lib/playlists-table.spec.ts runs the rebuild and its rollback on real SQLite. The rebuild rewrites every payload, about 4 s on that profile; later starts open the database in about 40 ms instead of 5-8 s. Never append a column after payload; add it to PLAYLISTS_COLUMNS before payload and to COLUMN_MIGRATION_STATEMENTS, and the rebuild moves it on the next start.

The #1580 index-ordering fix is included in 0.24 through PR #1550. src/lib/connection-upgrades.spec.ts exercises initDatabase() with real SQLite under the Electron runtime, using fresh-install schema snapshots from tags 0.19–0.24 and a fresh current database. The epg_channel_id column is absent in 0.19, present from 0.20, and indexed from 0.23. Each case checks current Drizzle tables, columns/types, and named indexes/uniqueness, as well as preserved user rows, foreign keys, database integrity, index availability, and repeated startup; an existing EPG index must keep its definition and root page, and payload must end up as the last playlists column (the 0.19 snapshot reaches the current schema through the ALTER list, so it reproduces an upgraded profile's order). Snapshots live in src/lib/testing/fixtures/ and are independent of the current schema, so moving the index ahead of its column migration makes the 0.19 case fail again.

Run this coverage with pnpm nx test database --runInBand. The Electron UI and worker upgrade flow is also covered by pnpm nx run electron-backend-e2e:e2e-ci--src/legacy-playlist-migration.e2e.ts. For an orphaned 0.24 favorites table, initial startup and restart through the real Electron UI and worker are covered by pnpm nx run electron-backend-e2e:e2e-ci--src/favorites-legacy.e2e.ts.