pgsql-jellyfin/docs/SCHEMA_CONVERSION_GUIDE.md

SQL Schema Conversion Guide - PascalCase to snake_case

Authoritative Table Mappings (from PostgresDatabaseProvider.OnModelCreating)

Activity Log Schema

Current (SQL)	Target (C#/PostgreSQL)	Entity Class
"ActivityLogs"	activity_logs	ActivityLog

Authentication Schema

Current (SQL)	Target (C#/PostgreSQL)	Entity Class
"ApiKeys"	api_keys	ApiKey
"Devices"	devices	Device
"DeviceOptions"	device_options	DeviceOptions

Display Preferences Schema

Current (SQL)	Target (C#/PostgreSQL)	Entity Class
"DisplayPreferences"	display_preferences	DisplayPreferences
"ItemDisplayPreferences"	item_display_preferences	ItemDisplayPreferences
"CustomItemDisplayPreferences"	custom_item_display_preferences	CustomItemDisplayPreferences
"HomeSections"	home_sections	HomeSection

Users Schema

Current (SQL)	Target (C#/PostgreSQL)	Entity Class
"Users"	users	User
"Permissions"	permissions	Permission
"Preferences"	preferences	Preference
"AccessSchedules"	access_schedules	AccessSchedule

Library Schema

Current (SQL)	Target (C#/PostgreSQL)	Entity Class
"BaseItems"	base_items	BaseItemEntity
"Chapters"	chapters	Chapter
"MediaStreamInfos"	media_stream_infos	MediaStreamInfo
"AttachmentStreamInfos"	attachment_stream_infos	AttachmentStreamInfo
"ImageInfos"	image_infos	ImageInfo
"BaseItemImageInfos"	base_item_image_infos	BaseItemImageInfo
"BaseItemProviders"	base_item_providers	BaseItemProvider
"BaseItemMetadataFields"	base_item_metadata_fields	BaseItemMetadataField
"BaseItemTrailerTypes"	base_item_trailer_types	BaseItemTrailerType
"ItemValues"	item_values	ItemValue
"ItemValuesMaps"	item_values_map	ItemValueMap
"Peoples"	peoples	People
"PeopleBaseItemMaps"	people_base_item_map	PeopleBaseItemMap
"UserData"	user_data	UserData
"AncestorIds"	ancestor_ids	AncestorId
"TrickplayInfos"	trickplay_infos	TrickplayInfo
"MediaSegments"	media_segments	MediaSegment
"KeyframeData"	keyframe_data	KeyframeData
"LibraryOptions"	library_options	LibraryOptionsEntity

Special Tables

Current (SQL)	Target (C#/PostgreSQL)	Notes
"__EFMigrationsHistory"	__ef_migrations_history	System table, special handling

Views (Library Schema)

Current (SQL)	Target (C#/PostgreSQL)	Entity Class
"media_stream_infos_v"	media_stream_infos_v	MediaStreamInfoView (already lowercase)
"video_items_v"	video_items_v	VideoItemView (already lowercase)
"user_playback_history_v"	user_playback_history_v	UserPlaybackHistoryView (already lowercase)
"item_providers_v"	item_providers_v	ItemProviderView (already lowercase)
"item_people_v"	item_people_v	ItemPersonView (already lowercase)
"library_summary_v"	library_summary_v	LibrarySummaryView (already lowercase)

---

Column Naming Rules

SnakeCaseNamingConvention Conversion Rules

// From SnakeCaseNamingConvention.cs - regex patterns applied:
// Rule 1: ([A-Z]+)([A-Z][a-z])  → "$1_$2"   // Consecutive capitals: HTTPServer → HTTP_Server
// Rule 2: ([a-z\d])([A-Z])      → "$1_$2"   // Lowercase/digit to capital: dateCreated → date_Created
// Rule 3: .ToLowerInvariant()                // Lowercase: date_Created → date_created

Common Column Conversions

Single-Word Columns:

• "Id" → id
• "Name" → name
• "Type" → type
• "UserId" → user_id
• "ItemId" → item_id

Multi-Word Columns:

• "DateCreated" → date_created
• "DateModified" → date_modified
• "DateLastActivity" → date_last_activity
• "AccessToken" → access_token
• "IsMovie" → is_movie
• "IsSeries" → is_series
• "ShortOverview" → short_overview
• "LogSeverity" → log_severity
• "RowVersion" → row_version
• "DisplayOrder" → display_order
• "BaseItemId" → base_item_id
• "DeviceId" → device_id
• "ProviderId" → provider_id
• "SortName" → sort_name
• "ProviderIds" → provider_ids
• "DvVersionMajor" → dv_version_major
• "DvVersionMinor" → dv_version_minor
• "DvProfile" → dv_profile
• "DvLevel" → dv_level
• "RpuVersion" → rpu_version
• "CodecTag" → codec_tag
• "IsHDR" → is_hdr
• "IsHDR10" → is_hdr10
• "SeriesPresentationUniqueKey" → series_presentation_unique_key

---

Conversion Strategy in create_database_schema.sql

Phase 1: Table Definition Lines

Find and replace quoted PascalCase table names immediately after CREATE TABLE:

• CREATE TABLE activitylog."ActivityLogs" → CREATE TABLE activitylog.activity_logs
• CREATE TABLE authentication."ApiKeys" → CREATE TABLE authentication.api_keys
• etc. (all 19 tables in schema sections)

Phase 2: Column Definitions

Within each CREATE TABLE block, replace all quoted column names:

• "Id" → id
• "Name" → name
• "DateCreated" → date_created
• etc. (200+ column replacements across all tables)

Phase 3: Sequence Names

Update sequence definitions to match renamed tables:

• SEQUENCE NAME activitylog."ActivityLogs_Id_seq" → SEQUENCE NAME activitylog.activity_logs_id_seq
• etc.

Phase 4: Constraint References

Update all foreign key and constraint definitions:

• CONSTRAINT "fk_base_items_users_user_id" FOREIGN KEY ("BaseItemId") REFERENCES users."Users" ("Id") → CONSTRAINT fk_base_items_users_user_id FOREIGN KEY (base_item_id) REFERENCES users.users (id)

Phase 5: Index References

Update index definitions:

• CREATE INDEX "idx_activity_logs_user_id" ON activitylog."ActivityLogs" ("UserId") → CREATE INDEX idx_activity_logs_user_id ON activitylog.activity_logs (user_id)

Phase 6: Special Table (__EFMigrationsHistory)

Update system table for consistency:

• "__EFMigrationsHistory" → __ef_migrations_history
• All its columns follow the same snake_case rule

---

File Location

• Source: scripts/sql/schema_init/create_database_schema.sql (1879 lines)
• Output: Same file (in-place conversion)

---

Validation Checklist

• All 19 application tables converted from PascalCase to snake_case
• All 200+ columns converted to lowercase/snake_case
• All sequence names updated
• All foreign key references updated
• All indexes updated
• Special table __ef_migrations_history created with lowercase columns
• Views remain unchanged (already lowercase)
• SQL syntax is valid
• No orphaned quotes remain in identifier positions
• Schema still respects owner = jellyfin

---

Generated: 2025-05-01
Reference Documents:

• PostgresDatabaseProvider.cs lines 763-875
• SnakeCaseNamingConvention.cs lines 16-40
• create_database_schema.sql full schema dump