234 lines
7.6 KiB
Markdown
234 lines
7.6 KiB
Markdown
# PostgreSQL Database Views
|
||
|
||
All views live in the `library` schema unless otherwise noted. They are read-only and safe to query from pgAdmin, BI tools, or any reporting/debugging context without risk of affecting the application.
|
||
|
||
---
|
||
|
||
## `library."MediaStreamInfos_v"`
|
||
|
||
Reassembles the original flat `MediaStreamInfos` table shape that existed before the `SplitMediaStreamInfos` migration split audio- and video-specific columns into dedicated tables.
|
||
|
||
**Use when:** you want a single flat result of all stream data without writing the three-way JOIN yourself.
|
||
|
||
**Columns of note:**
|
||
|
||
| Column | Source |
|
||
|---|---|
|
||
| `ItemId`, `StreamIndex`, `StreamType`, `Codec`, … | `MediaStreamInfos` (always present) |
|
||
| `ChannelLayout`, `Channels`, `SampleRate`, `IsHearingImpaired` | `AudioStreamDetails` — `NULL` for non-audio streams |
|
||
| `Height`, `Width`, `AverageFrameRate`, `BitDepth`, `ColorTransfer`, `DvProfile`, … | `VideoStreamDetails` — `NULL` for non-video streams |
|
||
|
||
**Example:**
|
||
|
||
```sql
|
||
-- All audio streams for a specific item
|
||
SELECT "StreamIndex", "Codec", "Language", "Channels", "SampleRate", "ChannelLayout"
|
||
FROM library."MediaStreamInfos_v"
|
||
WHERE "ItemId" = '<item-uuid>'
|
||
AND "StreamType" = 0 -- 0 = Audio
|
||
ORDER BY "StreamIndex";
|
||
```
|
||
|
||
---
|
||
|
||
## `library."VideoItems_v"`
|
||
|
||
Every non-folder, non-virtual item that has at least one video stream (movies, episodes, home videos, etc.), joined with the primary video stream's technical details.
|
||
|
||
**Use when:** auditing codec/resolution/HDR distribution across the library, or finding items that need re-encoding.
|
||
|
||
**Columns of note:**
|
||
|
||
| Column | Description |
|
||
|---|---|
|
||
| `Type` | Fully-qualified Jellyfin type name (e.g. `MediaBrowser.Controller.Entities.Movies.Movie`) |
|
||
| `VideoCodec` | Codec of the primary video stream (e.g. `hevc`, `h264`) |
|
||
| `Width` / `Height` | Resolution of the primary video stream |
|
||
| `BitDepth` | Colour bit depth (8, 10, 12) |
|
||
| `HDRFormat` | Derived string: `Dolby Vision`, `HDR10+`, `HDR10`, `HLG`, or `SDR` |
|
||
| `IsDolbyVision` | Boolean shortcut for `DvProfile IS NOT NULL` |
|
||
| `AudioTrackCount` | Number of audio streams on the item |
|
||
| `SubtitleTrackCount` | Number of subtitle streams on the item |
|
||
|
||
**Example queries:**
|
||
|
||
```sql
|
||
-- All 4K HDR movies
|
||
SELECT "Name", "ProductionYear", "VideoCodec", "Width", "Height", "HDRFormat", "TotalBitrate"
|
||
FROM library."VideoItems_v"
|
||
WHERE "Type" LIKE '%Movie%'
|
||
AND "Height" >= 2160
|
||
AND "HDRFormat" <> 'SDR'
|
||
ORDER BY "Name";
|
||
|
||
-- Episodes still encoded in H.264 (candidates for HEVC re-encode)
|
||
SELECT "SeriesName", "SeasonName", "EpisodeNumber", "Name", "Width", "Height"
|
||
FROM library."VideoItems_v"
|
||
WHERE "Type" LIKE '%Episode%'
|
||
AND "VideoCodec" = 'h264'
|
||
ORDER BY "SeriesName", "SeasonNumber", "EpisodeNumber";
|
||
```
|
||
|
||
---
|
||
|
||
## `library."UserPlaybackHistory_v"`
|
||
|
||
Per-user watch history joined with item metadata and a calculated playback progress percentage.
|
||
|
||
**Use when:** reporting on viewing activity, finding in-progress items, or checking which users have watched what.
|
||
|
||
**Columns of note:**
|
||
|
||
| Column | Description |
|
||
|---|---|
|
||
| `Username` | Jellyfin username |
|
||
| `ItemName` | Name of the item |
|
||
| `ItemType` | Jellyfin type name |
|
||
| `Played` | `true` if the item has been marked as played |
|
||
| `PlayCount` | Number of times fully played |
|
||
| `LastPlayedDate` | Timestamp of the most recent playback |
|
||
| `PlaybackPositionTicks` | Raw position (divide by `10,000,000` for seconds) |
|
||
| `ProgressPct` | Calculated percentage through the item (0–100), `NULL` if no runtime |
|
||
| `AudioStreamIndex` | Last-used audio track index |
|
||
| `SubtitleStreamIndex` | Last-used subtitle track index |
|
||
|
||
**Example queries:**
|
||
|
||
```sql
|
||
-- All in-progress items for a user (started but not finished)
|
||
SELECT "ItemName", "ItemType", "SeriesName", "ProgressPct", "LastPlayedDate"
|
||
FROM library."UserPlaybackHistory_v"
|
||
WHERE "Username" = 'alice'
|
||
AND "Played" = false
|
||
AND "ProgressPct" > 0
|
||
ORDER BY "LastPlayedDate" DESC;
|
||
|
||
-- Top 10 most-played items across all users
|
||
SELECT "ItemName", "ItemType", SUM("PlayCount") AS "TotalPlays"
|
||
FROM library."UserPlaybackHistory_v"
|
||
GROUP BY "ItemName", "ItemType"
|
||
ORDER BY "TotalPlays" DESC
|
||
LIMIT 10;
|
||
```
|
||
|
||
---
|
||
|
||
## `library."ItemProviders_v"`
|
||
|
||
Items with their external provider IDs (IMDb, TMDB, TVDB, etc.) pivoted into named columns. Providers not explicitly named appear in a JSONB `OtherProviders` column.
|
||
|
||
**Use when:** cross-referencing Jellyfin items against external databases, finding items with missing metadata IDs, or bulk-exporting IDs for external tools.
|
||
|
||
**Columns of note:**
|
||
|
||
| Column | Description |
|
||
|---|---|
|
||
| `ImdbId` | IMDb identifier (e.g. `tt0111161`) |
|
||
| `TmdbId` | The Movie Database ID |
|
||
| `TvdbId` | TheTVDB series/episode ID |
|
||
| `TvRageId` | TVRage ID (legacy) |
|
||
| `MusicBrainzAlbumId` | MusicBrainz album MBID |
|
||
| `MusicBrainzArtistId` | MusicBrainz artist MBID |
|
||
| `OtherProviders` | JSONB object of any remaining provider key/value pairs |
|
||
|
||
**Example queries:**
|
||
|
||
```sql
|
||
-- Movies missing a TMDB ID
|
||
SELECT "Name", "ProductionYear", "ImdbId"
|
||
FROM library."ItemProviders_v"
|
||
WHERE "Type" LIKE '%Movie%'
|
||
AND "TmdbId" IS NULL
|
||
ORDER BY "Name";
|
||
|
||
-- Look up an item by IMDb ID
|
||
SELECT "ItemId", "Type", "Name", "ProductionYear"
|
||
FROM library."ItemProviders_v"
|
||
WHERE "ImdbId" = 'tt0111161';
|
||
```
|
||
|
||
---
|
||
|
||
## `library."ItemPeople_v"`
|
||
|
||
All cast and crew entries linked to the items they appear in.
|
||
|
||
**Use when:** browsing which items feature a specific actor or director, building cast-focused reports, or auditing people metadata.
|
||
|
||
**Columns of note:**
|
||
|
||
| Column | Description |
|
||
|---|---|
|
||
| `PersonName` | Name of the person |
|
||
| `PersonType` | Role type: `Actor`, `Director`, `Writer`, `Producer`, etc. |
|
||
| `Role` | Specific character name or role description (may be empty) |
|
||
| `SortOrder` / `ListOrder` | Display ordering within the item's people list |
|
||
|
||
**Example queries:**
|
||
|
||
```sql
|
||
-- All items featuring a specific actor
|
||
SELECT "ItemType", "ItemName", "SeriesName", "ProductionYear", "Role"
|
||
FROM library."ItemPeople_v"
|
||
WHERE "PersonName" = 'Bryan Cranston'
|
||
AND "PersonType" = 'Actor'
|
||
ORDER BY "ProductionYear";
|
||
|
||
-- Directors with the most items in the library
|
||
SELECT "PersonName", COUNT(*) AS "DirectedItems"
|
||
FROM library."ItemPeople_v"
|
||
WHERE "PersonType" = 'Director'
|
||
GROUP BY "PersonName"
|
||
ORDER BY "DirectedItems" DESC
|
||
LIMIT 20;
|
||
```
|
||
|
||
---
|
||
|
||
## `library."LibrarySummary_v"`
|
||
|
||
Aggregate statistics broken down by item type — one row per type. Provides a quick dashboard overview of the entire library.
|
||
|
||
**Use when:** getting a high-level snapshot of library size, total runtime, storage usage, and video quality distribution.
|
||
|
||
**Columns of note:**
|
||
|
||
| Column | Description |
|
||
|---|---|
|
||
| `Type` | Jellyfin item type |
|
||
| `ItemCount` | Total number of items of this type |
|
||
| `TotalRuntimeHours` | Sum of all runtimes in hours |
|
||
| `TotalSizeGB` | Sum of all file sizes in gigabytes |
|
||
| `AvgCommunityRating` | Average community rating across items with a rating |
|
||
| `Count4K` | Items with primary video height ≥ 2160 |
|
||
| `Count1080p` | Items with height 1080–2159 |
|
||
| `Count720p` | Items with height 720–1079 |
|
||
| `CountSD` | Items with height < 720 |
|
||
| `CountDolbyVision` | Items with a Dolby Vision video stream |
|
||
| `CountHDR10Plus` | Items with HDR10+ flag set |
|
||
| `CountHDR10` | Items with HDR10 (`smpte2084` transfer, no DV or HDR10+) |
|
||
|
||
**Example:**
|
||
|
||
```sql
|
||
-- Full summary
|
||
SELECT "Type", "ItemCount", "TotalRuntimeHours", "TotalSizeGB",
|
||
"Count4K", "Count1080p", "Count720p", "CountSD",
|
||
"CountDolbyVision", "CountHDR10Plus", "CountHDR10"
|
||
FROM library."LibrarySummary_v";
|
||
```
|
||
|
||
---
|
||
|
||
## StreamType Reference
|
||
|
||
The `StreamType` integer used in `MediaStreamInfos` and `MediaStreamInfos_v` maps to:
|
||
|
||
| Value | Type |
|
||
|---|---|
|
||
| 0 | Audio |
|
||
| 1 | Video |
|
||
| 2 | Subtitle |
|
||
| 3 | EmbeddedImage |
|
||
| 4 | Data / Attachment |
|