wjones
7eb2b445cb
- Major query performance boost: replaced correlated subqueries with DistinctBy() in BaseItemRepository, added episode deduplication index, and increased default command timeout to 120s. - Fixed LibraryMonitor shutdown race: added disposal checks and exception handling in FileRefresher to prevent ObjectDisposedException. - Added PowerShell and SQL utilities for fixing Linux paths in config files and database; new scripts for WebDir and path migration. - Auto-fix for WebDir in startup.json on load; new helper scripts and docs. - Added automated weekly DB performance monitoring scripts and reporting. - Expanded documentation and README for all fixes, scripts, and migration steps. - Updated SQL scripts for index creation and diagnostics; improved output handling. - Updated db-config defaults and added new diagnostic/report files.
1302 lines
46 KiB
SQL
1302 lines
46 KiB
SQL
\pset pager off
|
|
|
|
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
|
|
"MigrationId" character varying(150) NOT NULL,
|
|
"ProductVersion" character varying(32) NOT NULL,
|
|
CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
|
|
);
|
|
|
|
START TRANSACTION;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'activitylog') THEN
|
|
CREATE SCHEMA activitylog;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'authentication') THEN
|
|
CREATE SCHEMA authentication;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'displaypreferences') THEN
|
|
CREATE SCHEMA displaypreferences;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'library') THEN
|
|
CREATE SCHEMA library;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'users') THEN
|
|
CREATE SCHEMA users;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE activitylog."ActivityLogs" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"Name" character varying(512) NOT NULL,
|
|
"Overview" character varying(512),
|
|
"ShortOverview" character varying(512),
|
|
"Type" character varying(256) NOT NULL,
|
|
"UserId" uuid NOT NULL,
|
|
"ItemId" character varying(256),
|
|
"DateCreated" timestamp with time zone NOT NULL,
|
|
"LogSeverity" integer NOT NULL,
|
|
"RowVersion" bigint NOT NULL,
|
|
CONSTRAINT "PK_ActivityLogs" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE authentication."ApiKeys" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"DateCreated" timestamp with time zone NOT NULL,
|
|
"DateLastActivity" timestamp with time zone NOT NULL,
|
|
"Name" character varying(64) NOT NULL,
|
|
"AccessToken" text NOT NULL,
|
|
CONSTRAINT "PK_ApiKeys" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."BaseItems" (
|
|
"Id" uuid NOT NULL,
|
|
"Type" text NOT NULL,
|
|
"Data" text,
|
|
"Path" text,
|
|
"StartDate" timestamp with time zone,
|
|
"EndDate" timestamp with time zone,
|
|
"ChannelId" uuid,
|
|
"IsMovie" boolean NOT NULL,
|
|
"CommunityRating" real,
|
|
"CustomRating" text,
|
|
"IndexNumber" integer,
|
|
"IsLocked" boolean NOT NULL,
|
|
"Name" text,
|
|
"OfficialRating" text,
|
|
"MediaType" text,
|
|
"Overview" text,
|
|
"ParentIndexNumber" integer,
|
|
"PremiereDate" timestamp with time zone,
|
|
"ProductionYear" integer,
|
|
"Genres" text,
|
|
"SortName" text,
|
|
"ForcedSortName" text,
|
|
"RunTimeTicks" bigint,
|
|
"DateCreated" timestamp with time zone,
|
|
"DateModified" timestamp with time zone,
|
|
"IsSeries" boolean NOT NULL,
|
|
"EpisodeTitle" text,
|
|
"IsRepeat" boolean NOT NULL,
|
|
"PreferredMetadataLanguage" text,
|
|
"PreferredMetadataCountryCode" text,
|
|
"DateLastRefreshed" timestamp with time zone,
|
|
"DateLastSaved" timestamp with time zone,
|
|
"IsInMixedFolder" boolean NOT NULL,
|
|
"Studios" text,
|
|
"ExternalServiceId" text,
|
|
"Tags" text,
|
|
"IsFolder" boolean NOT NULL,
|
|
"InheritedParentalRatingValue" integer,
|
|
"InheritedParentalRatingSubValue" integer,
|
|
"UnratedType" text,
|
|
"CriticRating" real,
|
|
"CleanName" text,
|
|
"PresentationUniqueKey" text,
|
|
"OriginalTitle" text,
|
|
"PrimaryVersionId" text,
|
|
"DateLastMediaAdded" timestamp with time zone,
|
|
"Album" text,
|
|
"LUFS" real,
|
|
"NormalizationGain" real,
|
|
"IsVirtualItem" boolean NOT NULL,
|
|
"SeriesName" text,
|
|
"SeasonName" text,
|
|
"ExternalSeriesId" text,
|
|
"Tagline" text,
|
|
"ProductionLocations" text,
|
|
"ExtraIds" text,
|
|
"TotalBitrate" integer,
|
|
"ExtraType" integer,
|
|
"Artists" text,
|
|
"AlbumArtists" text,
|
|
"ExternalId" text,
|
|
"SeriesPresentationUniqueKey" text,
|
|
"ShowId" text,
|
|
"OwnerId" text,
|
|
"Width" integer,
|
|
"Height" integer,
|
|
"Size" bigint,
|
|
"Audio" integer,
|
|
"ParentId" uuid,
|
|
"TopParentId" uuid,
|
|
"SeasonId" uuid,
|
|
"SeriesId" uuid,
|
|
CONSTRAINT "PK_BaseItems" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_BaseItems_BaseItems_ParentId" FOREIGN KEY ("ParentId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE displaypreferences."CustomItemDisplayPreferences" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
"Client" character varying(32) NOT NULL,
|
|
"Key" text NOT NULL,
|
|
"Value" text,
|
|
CONSTRAINT "PK_CustomItemDisplayPreferences" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE authentication."DeviceOptions" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"DeviceId" text NOT NULL,
|
|
"CustomName" text,
|
|
CONSTRAINT "PK_DeviceOptions" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."ItemValues" (
|
|
"ItemValueId" uuid NOT NULL,
|
|
"Type" integer NOT NULL,
|
|
"Value" text NOT NULL,
|
|
"CleanValue" text NOT NULL,
|
|
CONSTRAINT "PK_ItemValues" PRIMARY KEY ("ItemValueId")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."MediaSegments" (
|
|
"Id" uuid NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
"Type" integer NOT NULL,
|
|
"EndTicks" bigint NOT NULL,
|
|
"StartTicks" bigint NOT NULL,
|
|
"SegmentProviderId" text NOT NULL,
|
|
CONSTRAINT "PK_MediaSegments" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."Peoples" (
|
|
"Id" uuid NOT NULL,
|
|
"Name" text NOT NULL,
|
|
"PersonType" text,
|
|
CONSTRAINT "PK_Peoples" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."TrickplayInfos" (
|
|
"ItemId" uuid NOT NULL,
|
|
"Width" integer NOT NULL,
|
|
"Height" integer NOT NULL,
|
|
"TileWidth" integer NOT NULL,
|
|
"TileHeight" integer NOT NULL,
|
|
"ThumbnailCount" integer NOT NULL,
|
|
"Interval" integer NOT NULL,
|
|
"Bandwidth" integer NOT NULL,
|
|
CONSTRAINT "PK_TrickplayInfos" PRIMARY KEY ("ItemId", "Width")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE users."Users" (
|
|
"Id" uuid NOT NULL,
|
|
"Username" character varying(255) NOT NULL,
|
|
"Password" character varying(65535),
|
|
"MustUpdatePassword" boolean NOT NULL,
|
|
"AudioLanguagePreference" character varying(255),
|
|
"AuthenticationProviderId" character varying(255) NOT NULL,
|
|
"PasswordResetProviderId" character varying(255) NOT NULL,
|
|
"InvalidLoginAttemptCount" integer NOT NULL,
|
|
"LastActivityDate" timestamp with time zone,
|
|
"LastLoginDate" timestamp with time zone,
|
|
"LoginAttemptsBeforeLockout" integer,
|
|
"MaxActiveSessions" integer NOT NULL,
|
|
"SubtitleMode" integer NOT NULL,
|
|
"PlayDefaultAudioTrack" boolean NOT NULL,
|
|
"SubtitleLanguagePreference" character varying(255),
|
|
"DisplayMissingEpisodes" boolean NOT NULL,
|
|
"DisplayCollectionsView" boolean NOT NULL,
|
|
"EnableLocalPassword" boolean NOT NULL,
|
|
"HidePlayedInLatest" boolean NOT NULL,
|
|
"RememberAudioSelections" boolean NOT NULL,
|
|
"RememberSubtitleSelections" boolean NOT NULL,
|
|
"EnableNextEpisodeAutoPlay" boolean NOT NULL,
|
|
"EnableAutoLogin" boolean NOT NULL,
|
|
"EnableUserPreferenceAccess" boolean NOT NULL,
|
|
"MaxParentalRatingScore" integer,
|
|
"MaxParentalRatingSubScore" integer,
|
|
"RemoteClientBitrateLimit" integer,
|
|
"InternalId" bigint NOT NULL,
|
|
"SyncPlayAccess" integer NOT NULL,
|
|
"CastReceiverId" character varying(32),
|
|
"RowVersion" bigint NOT NULL,
|
|
CONSTRAINT "PK_Users" PRIMARY KEY ("Id")
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."AncestorIds" (
|
|
"ParentItemId" uuid NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
CONSTRAINT "PK_AncestorIds" PRIMARY KEY ("ItemId", "ParentItemId"),
|
|
CONSTRAINT "FK_AncestorIds_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE,
|
|
CONSTRAINT "FK_AncestorIds_BaseItems_ParentItemId" FOREIGN KEY ("ParentItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."AttachmentStreamInfos" (
|
|
"ItemId" uuid NOT NULL,
|
|
"Index" integer NOT NULL,
|
|
"Codec" text,
|
|
"CodecTag" text,
|
|
"Comment" text,
|
|
"Filename" text,
|
|
"MimeType" text,
|
|
CONSTRAINT "PK_AttachmentStreamInfos" PRIMARY KEY ("ItemId", "Index"),
|
|
CONSTRAINT "FK_AttachmentStreamInfos_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."BaseItemImageInfos" (
|
|
"Id" uuid NOT NULL,
|
|
"Path" text NOT NULL,
|
|
"DateModified" timestamp with time zone,
|
|
"ImageType" integer NOT NULL,
|
|
"Width" integer NOT NULL,
|
|
"Height" integer NOT NULL,
|
|
"Blurhash" bytea,
|
|
"ItemId" uuid NOT NULL,
|
|
CONSTRAINT "PK_BaseItemImageInfos" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_BaseItemImageInfos_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."BaseItemMetadataFields" (
|
|
"Id" integer NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
CONSTRAINT "PK_BaseItemMetadataFields" PRIMARY KEY ("Id", "ItemId"),
|
|
CONSTRAINT "FK_BaseItemMetadataFields_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."BaseItemProviders" (
|
|
"ItemId" uuid NOT NULL,
|
|
"ProviderId" text NOT NULL,
|
|
"ProviderValue" text NOT NULL,
|
|
CONSTRAINT "PK_BaseItemProviders" PRIMARY KEY ("ItemId", "ProviderId"),
|
|
CONSTRAINT "FK_BaseItemProviders_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."BaseItemTrailerTypes" (
|
|
"Id" integer NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
CONSTRAINT "PK_BaseItemTrailerTypes" PRIMARY KEY ("Id", "ItemId"),
|
|
CONSTRAINT "FK_BaseItemTrailerTypes_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."Chapters" (
|
|
"ItemId" uuid NOT NULL,
|
|
"ChapterIndex" integer NOT NULL,
|
|
"StartPositionTicks" bigint NOT NULL,
|
|
"Name" text,
|
|
"ImagePath" text,
|
|
"ImageDateModified" timestamp with time zone,
|
|
CONSTRAINT "PK_Chapters" PRIMARY KEY ("ItemId", "ChapterIndex"),
|
|
CONSTRAINT "FK_Chapters_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."KeyframeData" (
|
|
"ItemId" uuid NOT NULL,
|
|
"TotalDuration" bigint NOT NULL,
|
|
"KeyframeTicks" bigint[],
|
|
CONSTRAINT "PK_KeyframeData" PRIMARY KEY ("ItemId"),
|
|
CONSTRAINT "FK_KeyframeData_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."MediaStreamInfos" (
|
|
"ItemId" uuid NOT NULL,
|
|
"StreamIndex" integer NOT NULL,
|
|
"StreamType" integer NOT NULL,
|
|
"Codec" text,
|
|
"Language" text,
|
|
"ChannelLayout" text,
|
|
"Profile" text,
|
|
"AspectRatio" text,
|
|
"Path" text,
|
|
"IsInterlaced" boolean,
|
|
"BitRate" integer,
|
|
"Channels" integer,
|
|
"SampleRate" integer,
|
|
"IsDefault" boolean NOT NULL,
|
|
"IsForced" boolean NOT NULL,
|
|
"IsExternal" boolean NOT NULL,
|
|
"Height" integer,
|
|
"Width" integer,
|
|
"AverageFrameRate" real,
|
|
"RealFrameRate" real,
|
|
"Level" real,
|
|
"PixelFormat" text,
|
|
"BitDepth" integer,
|
|
"IsAnamorphic" boolean,
|
|
"RefFrames" integer,
|
|
"CodecTag" text,
|
|
"Comment" text,
|
|
"NalLengthSize" text,
|
|
"IsAvc" boolean,
|
|
"Title" text,
|
|
"TimeBase" text,
|
|
"CodecTimeBase" text,
|
|
"ColorPrimaries" text,
|
|
"ColorSpace" text,
|
|
"ColorTransfer" text,
|
|
"DvVersionMajor" integer,
|
|
"DvVersionMinor" integer,
|
|
"DvProfile" integer,
|
|
"DvLevel" integer,
|
|
"RpuPresentFlag" integer,
|
|
"ElPresentFlag" integer,
|
|
"BlPresentFlag" integer,
|
|
"DvBlSignalCompatibilityId" integer,
|
|
"IsHearingImpaired" boolean,
|
|
"Rotation" integer,
|
|
"KeyFrames" text,
|
|
"Hdr10PlusPresentFlag" boolean,
|
|
CONSTRAINT "PK_MediaStreamInfos" PRIMARY KEY ("ItemId", "StreamIndex"),
|
|
CONSTRAINT "FK_MediaStreamInfos_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."ItemValuesMap" (
|
|
"ItemId" uuid NOT NULL,
|
|
"ItemValueId" uuid NOT NULL,
|
|
CONSTRAINT "PK_ItemValuesMap" PRIMARY KEY ("ItemValueId", "ItemId"),
|
|
CONSTRAINT "FK_ItemValuesMap_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE,
|
|
CONSTRAINT "FK_ItemValuesMap_ItemValues_ItemValueId" FOREIGN KEY ("ItemValueId") REFERENCES library."ItemValues" ("ItemValueId") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."PeopleBaseItemMap" (
|
|
"Role" text NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
"PeopleId" uuid NOT NULL,
|
|
"SortOrder" integer,
|
|
"ListOrder" integer,
|
|
CONSTRAINT "PK_PeopleBaseItemMap" PRIMARY KEY ("ItemId", "PeopleId", "Role"),
|
|
CONSTRAINT "FK_PeopleBaseItemMap_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE,
|
|
CONSTRAINT "FK_PeopleBaseItemMap_Peoples_PeopleId" FOREIGN KEY ("PeopleId") REFERENCES library."Peoples" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE users."AccessSchedules" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid NOT NULL,
|
|
"DayOfWeek" integer NOT NULL,
|
|
"StartHour" double precision NOT NULL,
|
|
"EndHour" double precision NOT NULL,
|
|
CONSTRAINT "PK_AccessSchedules" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_AccessSchedules_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE authentication."Devices" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid NOT NULL,
|
|
"AccessToken" text NOT NULL,
|
|
"AppName" character varying(64) NOT NULL,
|
|
"AppVersion" character varying(32) NOT NULL,
|
|
"DeviceName" character varying(64) NOT NULL,
|
|
"DeviceId" character varying(256) NOT NULL,
|
|
"IsActive" boolean NOT NULL,
|
|
"DateCreated" timestamp with time zone NOT NULL,
|
|
"DateModified" timestamp with time zone NOT NULL,
|
|
"DateLastActivity" timestamp with time zone NOT NULL,
|
|
CONSTRAINT "PK_Devices" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_Devices_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE displaypreferences."DisplayPreferences" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
"Client" character varying(32) NOT NULL,
|
|
"ShowSidebar" boolean NOT NULL,
|
|
"ShowBackdrop" boolean NOT NULL,
|
|
"ScrollDirection" integer NOT NULL,
|
|
"IndexBy" integer,
|
|
"SkipForwardLength" integer NOT NULL,
|
|
"SkipBackwardLength" integer NOT NULL,
|
|
"ChromecastVersion" integer NOT NULL,
|
|
"EnableNextVideoInfoOverlay" boolean NOT NULL,
|
|
"DashboardTheme" character varying(32),
|
|
"TvHome" character varying(32),
|
|
CONSTRAINT "PK_DisplayPreferences" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_DisplayPreferences_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."ImageInfos" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid,
|
|
"Path" character varying(512) NOT NULL,
|
|
"LastModified" timestamp with time zone NOT NULL,
|
|
CONSTRAINT "PK_ImageInfos" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_ImageInfos_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE displaypreferences."ItemDisplayPreferences" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
"Client" character varying(32) NOT NULL,
|
|
"ViewType" integer NOT NULL,
|
|
"RememberIndexing" boolean NOT NULL,
|
|
"IndexBy" integer,
|
|
"RememberSorting" boolean NOT NULL,
|
|
"SortBy" character varying(64) NOT NULL,
|
|
"SortOrder" integer NOT NULL,
|
|
CONSTRAINT "PK_ItemDisplayPreferences" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_ItemDisplayPreferences_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE users."Permissions" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid,
|
|
"Kind" integer NOT NULL,
|
|
"Value" boolean NOT NULL,
|
|
"RowVersion" bigint NOT NULL,
|
|
"Permission_Permissions_Guid" uuid,
|
|
CONSTRAINT "PK_Permissions" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_Permissions_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE users."Preferences" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"UserId" uuid,
|
|
"Kind" integer NOT NULL,
|
|
"Value" character varying(65535) NOT NULL,
|
|
"RowVersion" bigint NOT NULL,
|
|
"Preference_Preferences_Guid" uuid,
|
|
CONSTRAINT "PK_Preferences" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_Preferences_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE library."UserData" (
|
|
"CustomDataKey" text NOT NULL,
|
|
"ItemId" uuid NOT NULL,
|
|
"UserId" uuid NOT NULL,
|
|
"Rating" double precision,
|
|
"PlaybackPositionTicks" bigint NOT NULL,
|
|
"PlayCount" integer NOT NULL,
|
|
"IsFavorite" boolean NOT NULL,
|
|
"LastPlayedDate" timestamp with time zone,
|
|
"Played" boolean NOT NULL,
|
|
"AudioStreamIndex" integer,
|
|
"SubtitleStreamIndex" integer,
|
|
"Likes" boolean,
|
|
"RetentionDate" timestamp with time zone,
|
|
CONSTRAINT "PK_UserData" PRIMARY KEY ("ItemId", "UserId", "CustomDataKey"),
|
|
CONSTRAINT "FK_UserData_BaseItems_ItemId" FOREIGN KEY ("ItemId") REFERENCES library."BaseItems" ("Id") ON DELETE CASCADE,
|
|
CONSTRAINT "FK_UserData_Users_UserId" FOREIGN KEY ("UserId") REFERENCES users."Users" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE TABLE displaypreferences."HomeSection" (
|
|
"Id" integer GENERATED BY DEFAULT AS IDENTITY,
|
|
"DisplayPreferencesId" integer NOT NULL,
|
|
"Order" integer NOT NULL,
|
|
"Type" integer NOT NULL,
|
|
CONSTRAINT "PK_HomeSection" PRIMARY KEY ("Id"),
|
|
CONSTRAINT "FK_HomeSection_DisplayPreferences_DisplayPreferencesId" FOREIGN KEY ("DisplayPreferencesId") REFERENCES displaypreferences."DisplayPreferences" ("Id") ON DELETE CASCADE
|
|
);
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
|
|
INSERT INTO library."BaseItems" (
|
|
"Id", "Type", "Name", "IsFolder", "IsInMixedFolder", "IsLocked", "IsMovie",
|
|
"IsRepeat", "IsSeries", "IsVirtualItem"
|
|
)
|
|
VALUES (
|
|
'00000000-0000-0000-0000-000000000001'::uuid,
|
|
'PLACEHOLDER',
|
|
'This is a placeholder item for UserData that has been detacted from its original item',
|
|
false, false, false, false, false, false, false
|
|
);
|
|
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_AccessSchedules_UserId" ON users."AccessSchedules" ("UserId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_ActivityLogs_DateCreated" ON activitylog."ActivityLogs" ("DateCreated");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_AncestorIds_ParentItemId" ON library."AncestorIds" ("ParentItemId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_ApiKeys_AccessToken" ON authentication."ApiKeys" ("AccessToken");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItemImageInfos_ItemId" ON library."BaseItemImageInfos" ("ItemId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItemMetadataFields_ItemId" ON library."BaseItemMetadataFields" ("ItemId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItemProviders_ProviderId_ProviderValue_ItemId" ON library."BaseItemProviders" ("ProviderId", "ProviderValue", "ItemId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Id_Type_IsFolder_IsVirtualItem" ON library."BaseItems" ("Id", "Type", "IsFolder", "IsVirtualItem");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_IsFolder_TopParentId_IsVirtualItem_PresentationUn~" ON library."BaseItems" ("IsFolder", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_MediaType_TopParentId_IsVirtualItem_PresentationU~" ON library."BaseItems" ("MediaType", "TopParentId", "IsVirtualItem", "PresentationUniqueKey");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_ParentId" ON library."BaseItems" ("ParentId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Path" ON library."BaseItems" ("Path");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_PresentationUniqueKey" ON library."BaseItems" ("PresentationUniqueKey");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_TopParentId_Id" ON library."BaseItems" ("TopParentId", "Id");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Type_SeriesPresentationUniqueKey_IsFolder_IsVirtu~" ON library."BaseItems" ("Type", "SeriesPresentationUniqueKey", "IsFolder", "IsVirtualItem");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Type_SeriesPresentationUniqueKey_PresentationUniq~" ON library."BaseItems" ("Type", "SeriesPresentationUniqueKey", "PresentationUniqueKey", "SortName");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Type_TopParentId_Id" ON library."BaseItems" ("Type", "TopParentId", "Id");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Type_TopParentId_IsVirtualItem_PresentationUnique~" ON library."BaseItems" ("Type", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Type_TopParentId_PresentationUniqueKey" ON library."BaseItems" ("Type", "TopParentId", "PresentationUniqueKey");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItems_Type_TopParentId_StartDate" ON library."BaseItems" ("Type", "TopParentId", "StartDate");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_BaseItemTrailerTypes_ItemId" ON library."BaseItemTrailerTypes" ("ItemId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_CustomItemDisplayPreferences_UserId_ItemId_Client_Key" ON displaypreferences."CustomItemDisplayPreferences" ("UserId", "ItemId", "Client", "Key");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_DeviceOptions_DeviceId" ON authentication."DeviceOptions" ("DeviceId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_Devices_AccessToken_DateLastActivity" ON authentication."Devices" ("AccessToken", "DateLastActivity");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_Devices_DeviceId" ON authentication."Devices" ("DeviceId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_Devices_DeviceId_DateLastActivity" ON authentication."Devices" ("DeviceId", "DateLastActivity");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_Devices_UserId_DeviceId" ON authentication."Devices" ("UserId", "DeviceId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_DisplayPreferences_UserId_ItemId_Client" ON displaypreferences."DisplayPreferences" ("UserId", "ItemId", "Client");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_HomeSection_DisplayPreferencesId" ON displaypreferences."HomeSection" ("DisplayPreferencesId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_ImageInfos_UserId" ON library."ImageInfos" ("UserId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_ItemDisplayPreferences_UserId" ON displaypreferences."ItemDisplayPreferences" ("UserId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_ItemValues_Type_CleanValue" ON library."ItemValues" ("Type", "CleanValue");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_ItemValues_Type_Value" ON library."ItemValues" ("Type", "Value");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_ItemValuesMap_ItemId" ON library."ItemValuesMap" ("ItemId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_MediaStreamInfos_StreamIndex" ON library."MediaStreamInfos" ("StreamIndex");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_MediaStreamInfos_StreamIndex_StreamType" ON library."MediaStreamInfos" ("StreamIndex", "StreamType");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_MediaStreamInfos_StreamIndex_StreamType_Language" ON library."MediaStreamInfos" ("StreamIndex", "StreamType", "Language");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_MediaStreamInfos_StreamType" ON library."MediaStreamInfos" ("StreamType");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_PeopleBaseItemMap_ItemId_ListOrder" ON library."PeopleBaseItemMap" ("ItemId", "ListOrder");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_PeopleBaseItemMap_ItemId_SortOrder" ON library."PeopleBaseItemMap" ("ItemId", "SortOrder");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_PeopleBaseItemMap_PeopleId" ON library."PeopleBaseItemMap" ("PeopleId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_Peoples_Name" ON library."Peoples" ("Name");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_Permissions_UserId_Kind" ON users."Permissions" ("UserId", "Kind") WHERE "UserId" IS NOT NULL;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_Preferences_UserId_Kind" ON users."Preferences" ("UserId", "Kind") WHERE "UserId" IS NOT NULL;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_UserData_ItemId_UserId_IsFavorite" ON library."UserData" ("ItemId", "UserId", "IsFavorite");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_UserData_ItemId_UserId_LastPlayedDate" ON library."UserData" ("ItemId", "UserId", "LastPlayedDate");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_UserData_ItemId_UserId_PlaybackPositionTicks" ON library."UserData" ("ItemId", "UserId", "PlaybackPositionTicks");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_UserData_ItemId_UserId_Played" ON library."UserData" ("ItemId", "UserId", "Played");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE INDEX "IX_UserData_UserId" ON library."UserData" ("UserId");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
CREATE UNIQUE INDEX "IX_Users_Username" ON users."Users" ("Username");
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260222222702_InitialCreate') THEN
|
|
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
|
|
VALUES ('20260222222702_InitialCreate', '11.0.0-preview.1.26104.118');
|
|
END IF;
|
|
END $EF$;
|
|
COMMIT;
|
|
|
|
START TRANSACTION;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'users') THEN
|
|
CREATE SCHEMA users;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'activitylog') THEN
|
|
CREATE SCHEMA activitylog;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'library') THEN
|
|
CREATE SCHEMA library;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'authentication') THEN
|
|
CREATE SCHEMA authentication;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'displaypreferences') THEN
|
|
CREATE SCHEMA displaypreferences;
|
|
END IF;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "Users" SET SCHEMA users;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "UserData" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "TrickplayInfos" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "Preferences" SET SCHEMA users;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "Permissions" SET SCHEMA users;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "Peoples" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "PeopleBaseItemMap" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "MediaStreamInfos" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "MediaSegments" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "KeyframeData" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "ItemValuesMap" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "ItemValues" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "ItemDisplayPreferences" SET SCHEMA displaypreferences;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "ImageInfos" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "HomeSection" SET SCHEMA displaypreferences;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "DisplayPreferences" SET SCHEMA displaypreferences;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "Devices" SET SCHEMA authentication;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "DeviceOptions" SET SCHEMA authentication;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "CustomItemDisplayPreferences" SET SCHEMA displaypreferences;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "Chapters" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "BaseItemTrailerTypes" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "BaseItems" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "BaseItemProviders" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "BaseItemMetadataFields" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "BaseItemImageInfos" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "AttachmentStreamInfos" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "ApiKeys" SET SCHEMA authentication;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "AncestorIds" SET SCHEMA library;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "ActivityLogs" SET SCHEMA activitylog;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
ALTER TABLE "AccessSchedules" SET SCHEMA users;
|
|
END IF;
|
|
END $EF$;
|
|
|
|
DO $EF$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20260226164304_SyncSchemas') THEN
|
|
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
|
|
VALUES ('20260226164304_SyncSchemas', '11.0.0-preview.1.26104.118');
|
|
END IF;
|
|
END $EF$;
|
|
COMMIT;
|
|
|