< Summary - Jellyfin

Information
Class: Emby.Server.Implementations.Data.SqliteItemRepository
Assembly: Emby.Server.Implementations
File(s): /srv/git/jellyfin/Emby.Server.Implementations/Data/SqliteItemRepository.cs
Line coverage
47%
Covered lines: 1389
Uncovered lines: 1552
Coverable lines: 2941
Total lines: 5943
Line coverage: 47.2%
Branch coverage
43%
Covered branches: 776
Total branches: 1765
Branch coverage: 43.9%
Method coverage

Feature is only available for sponsors

Upgrade to PRO version

Coverage history

Coverage history 0 25 50 75 100

Metrics

MethodBranch coverage Crap Score Cyclomatic complexity Line coverage
.ctor(...)100%11100%
.cctor()100%11100%
get_TempStore()100%11100%
Initialize()100%11100%
SaveImages(...)100%210%
SaveItems(...)75%44100%
SaveItemsInTransaction(...)83.33%6.06688.23%
GetPathToSave(...)50%2.15266.66%
RestorePath(...)100%11100%
SaveItem(...)55.26%133.977678.43%
SerializeProviderIds(...)83.33%6.03690.9%
DeserializeProviderIds(...)87.5%8.19885.71%
SerializeImages(...)83.33%6.05688.88%
DeserializeImages(...)100%1010100%
AppendItemImageInfo(...)75%44100%
ItemImageInfoFromValueString(...)97.05%34.013498.18%
RetrieveItem(...)50%4.43470%
TypeRequiresDeserialization(...)100%3030100%
GetItem(...)100%210%
GetItem(...)63.17%4047.7125861.53%
SplitToGuids(...)0%620%
GetChapters(...)0%620%
GetChapter(...)0%620%
GetChapter(...)0%4260%
SaveChapters(...)0%620%
InsertChapters(...)0%4260%
EnableJoinUserData(...)100%2222100%
HasField(...)100%3737100%
HasProgramAttributes(...)83.33%6.29680%
HasServiceName(...)83.33%6.29680%
HasStartDate(...)83.33%6.29680%
HasEpisodeAttributes(...)100%22100%
HasTrailerTypes(...)100%22100%
HasArtistFields(...)83.33%6.29680%
HasSeriesFields(...)75%4.13480%
SetFinalColumnsToSelect(...)77.08%292.134852.68%
BindSearchParams(...)12.5%37.14823.07%
BindSimilarParams(...)10%55.531023.07%
GetJoinUserDataText(...)100%22100%
GetGroupBy(...)75%9875%
GetCount(...)0%7280%
GetItemList(...)71.42%34.272880%
FixUnicodeChars(...)100%210%
AddItem(...)0%110100%
GetItems(...)8.33%1802.03488.69%
GetOrderByText(...)25%21.221260%
MapOrderByField(...)17.64%564.843422.85%
GetItemIdsList(...)43.75%20.361674.28%
IsAlphaNumeric(...)0%7280%
IsValidPersonType(...)100%210%
GetWhereClauses(...)39.32%137197.3261829.02%
GetProviderIdClause(...)0%620%
GetItemByNameTypesInQuery(...)100%1010100%
IsTypeInQuery(...)75%4.59466.66%
GetCleanValue(...)50%2.15266.66%
EnableGroupByPresentationUniqueKey(...)65%20.782087.5%
UpdateInheritedValues()100%11100%
DeleteItem(...)50%2292.3%
ExecuteWithSingleParam(...)100%11100%
GetPeopleNames(...)0%4260%
GetPeople(...)0%4260%
GetPeopleWhereClauses(...)0%1482380%
UpdateAncestors(...)87.5%8896%
GetAllArtists(...)100%210%
GetArtists(...)100%210%
GetAlbumArtists(...)100%210%
GetStudios(...)100%210%
GetGenres(...)100%210%
GetMusicGenres(...)100%210%
GetStudioNames()100%11100%
GetAllArtistNames()100%11100%
GetMusicGenreNames()100%11100%
GetGenreNames()100%11100%
GetItemValueNames(...)80%10.051092.3%
GetItemValues(...)0%2756520%
GetItemCounts(...)0%420200%
GetItemValuesToSave(...)50%4.1481.81%
UpdateItemValues(...)50%2.01288.88%
InsertItemValues(...)16.66%26.84616.66%
UpdatePeople(...)0%2040%
InsertPeople(...)0%4260%
GetPerson(...)0%7280%
GetMediaStreams(...)0%110100%
SaveMediaStreams(...)0%620%
InsertMediaStreams(...)0%110100%
GetMediaStream(...)0%6806820%
GetMediaAttachments(...)0%4260%
SaveMediaAttachments(...)0%620%
InsertMediaAttachments(...)0%7280%
GetMediaAttachment(...)0%110100%
BuildMediaAttachmentInsertPrefix()100%22100%
.ctor(...)50%22100%
Dispose()25%12.19420%

File(s)

/srv/git/jellyfin/Emby.Server.Implementations/Data/SqliteItemRepository.cs

#LineLine coverage
 1#nullable disable
 2
 3#pragma warning disable CS1591
 4
 5using System;
 6using System.Collections.Generic;
 7using System.Diagnostics;
 8using System.Globalization;
 9using System.IO;
 10using System.Linq;
 11using System.Runtime.CompilerServices;
 12using System.Text;
 13using System.Text.Json;
 14using System.Threading;
 15using Emby.Server.Implementations.Playlists;
 16using Jellyfin.Data.Enums;
 17using Jellyfin.Extensions;
 18using Jellyfin.Extensions.Json;
 19using MediaBrowser.Controller;
 20using MediaBrowser.Controller.Channels;
 21using MediaBrowser.Controller.Configuration;
 22using MediaBrowser.Controller.Drawing;
 23using MediaBrowser.Controller.Entities;
 24using MediaBrowser.Controller.Entities.Audio;
 25using MediaBrowser.Controller.Entities.Movies;
 26using MediaBrowser.Controller.Entities.TV;
 27using MediaBrowser.Controller.Extensions;
 28using MediaBrowser.Controller.LiveTv;
 29using MediaBrowser.Controller.Persistence;
 30using MediaBrowser.Controller.Playlists;
 31using MediaBrowser.Model.Dto;
 32using MediaBrowser.Model.Entities;
 33using MediaBrowser.Model.Globalization;
 34using MediaBrowser.Model.LiveTv;
 35using MediaBrowser.Model.Querying;
 36using Microsoft.Data.Sqlite;
 37using Microsoft.Extensions.Configuration;
 38using Microsoft.Extensions.Logging;
 39
 40namespace Emby.Server.Implementations.Data
 41{
 42    /// <summary>
 43    /// Class SQLiteItemRepository.
 44    /// </summary>
 45    public class SqliteItemRepository : BaseSqliteRepository, IItemRepository
 46    {
 47        private const string FromText = " from TypedBaseItems A";
 48        private const string ChaptersTableName = "Chapters2";
 49
 50        private const string SaveItemCommandText =
 51            @"replace into TypedBaseItems
 52            (guid,type,data,Path,StartDate,EndDate,ChannelId,IsMovie,IsSeries,EpisodeTitle,IsRepeat,CommunityRating,Cust
 53            values (@guid,@type,@data,@Path,@StartDate,@EndDate,@ChannelId,@IsMovie,@IsSeries,@EpisodeTitle,@IsRepeat,@C
 54
 55        private readonly IServerConfigurationManager _config;
 56        private readonly IServerApplicationHost _appHost;
 57        private readonly ILocalizationManager _localization;
 58        // TODO: Remove this dependency. GetImageCacheTag() is the only method used and it can be converted to a static 
 59        private readonly IImageProcessor _imageProcessor;
 60
 61        private readonly TypeMapper _typeMapper;
 62        private readonly JsonSerializerOptions _jsonOptions;
 63
 4664        private readonly ItemFields[] _allItemFields = Enum.GetValues<ItemFields>();
 65
 166        private static readonly string[] _retrieveItemColumns =
 167        {
 168            "type",
 169            "data",
 170            "StartDate",
 171            "EndDate",
 172            "ChannelId",
 173            "IsMovie",
 174            "IsSeries",
 175            "EpisodeTitle",
 176            "IsRepeat",
 177            "CommunityRating",
 178            "CustomRating",
 179            "IndexNumber",
 180            "IsLocked",
 181            "PreferredMetadataLanguage",
 182            "PreferredMetadataCountryCode",
 183            "Width",
 184            "Height",
 185            "DateLastRefreshed",
 186            "Name",
 187            "Path",
 188            "PremiereDate",
 189            "Overview",
 190            "ParentIndexNumber",
 191            "ProductionYear",
 192            "OfficialRating",
 193            "ForcedSortName",
 194            "RunTimeTicks",
 195            "Size",
 196            "DateCreated",
 197            "DateModified",
 198            "guid",
 199            "Genres",
 1100            "ParentId",
 1101            "Audio",
 1102            "ExternalServiceId",
 1103            "IsInMixedFolder",
 1104            "DateLastSaved",
 1105            "LockedFields",
 1106            "Studios",
 1107            "Tags",
 1108            "TrailerTypes",
 1109            "OriginalTitle",
 1110            "PrimaryVersionId",
 1111            "DateLastMediaAdded",
 1112            "Album",
 1113            "LUFS",
 1114            "NormalizationGain",
 1115            "CriticRating",
 1116            "IsVirtualItem",
 1117            "SeriesName",
 1118            "SeasonName",
 1119            "SeasonId",
 1120            "SeriesId",
 1121            "PresentationUniqueKey",
 1122            "InheritedParentalRatingValue",
 1123            "ExternalSeriesId",
 1124            "Tagline",
 1125            "ProviderIds",
 1126            "Images",
 1127            "ProductionLocations",
 1128            "ExtraIds",
 1129            "TotalBitrate",
 1130            "ExtraType",
 1131            "Artists",
 1132            "AlbumArtists",
 1133            "ExternalId",
 1134            "SeriesPresentationUniqueKey",
 1135            "ShowId",
 1136            "OwnerId"
 1137        };
 138
 1139        private static readonly string _retrieveItemColumnsSelectQuery = $"select {string.Join(',', _retrieveItemColumns
 140
 1141        private static readonly string[] _mediaStreamSaveColumns =
 1142        {
 1143            "ItemId",
 1144            "StreamIndex",
 1145            "StreamType",
 1146            "Codec",
 1147            "Language",
 1148            "ChannelLayout",
 1149            "Profile",
 1150            "AspectRatio",
 1151            "Path",
 1152            "IsInterlaced",
 1153            "BitRate",
 1154            "Channels",
 1155            "SampleRate",
 1156            "IsDefault",
 1157            "IsForced",
 1158            "IsExternal",
 1159            "Height",
 1160            "Width",
 1161            "AverageFrameRate",
 1162            "RealFrameRate",
 1163            "Level",
 1164            "PixelFormat",
 1165            "BitDepth",
 1166            "IsAnamorphic",
 1167            "RefFrames",
 1168            "CodecTag",
 1169            "Comment",
 1170            "NalLengthSize",
 1171            "IsAvc",
 1172            "Title",
 1173            "TimeBase",
 1174            "CodecTimeBase",
 1175            "ColorPrimaries",
 1176            "ColorSpace",
 1177            "ColorTransfer",
 1178            "DvVersionMajor",
 1179            "DvVersionMinor",
 1180            "DvProfile",
 1181            "DvLevel",
 1182            "RpuPresentFlag",
 1183            "ElPresentFlag",
 1184            "BlPresentFlag",
 1185            "DvBlSignalCompatibilityId",
 1186            "IsHearingImpaired",
 1187            "Rotation"
 1188        };
 189
 1190        private static readonly string _mediaStreamSaveColumnsInsertQuery =
 1191            $"insert into mediastreams ({string.Join(',', _mediaStreamSaveColumns)}) values ";
 192
 1193        private static readonly string _mediaStreamSaveColumnsSelectQuery =
 1194            $"select {string.Join(',', _mediaStreamSaveColumns)} from mediastreams where ItemId=@ItemId";
 195
 1196        private static readonly string[] _mediaAttachmentSaveColumns =
 1197        {
 1198            "ItemId",
 1199            "AttachmentIndex",
 1200            "Codec",
 1201            "CodecTag",
 1202            "Comment",
 1203            "Filename",
 1204            "MIMEType"
 1205        };
 206
 1207        private static readonly string _mediaAttachmentSaveColumnsSelectQuery =
 1208            $"select {string.Join(',', _mediaAttachmentSaveColumns)} from mediaattachments where ItemId=@ItemId";
 209
 1210        private static readonly string _mediaAttachmentInsertPrefix = BuildMediaAttachmentInsertPrefix();
 211
 1212        private static readonly BaseItemKind[] _programTypes = new[]
 1213        {
 1214            BaseItemKind.Program,
 1215            BaseItemKind.TvChannel,
 1216            BaseItemKind.LiveTvProgram,
 1217            BaseItemKind.LiveTvChannel
 1218        };
 219
 1220        private static readonly BaseItemKind[] _programExcludeParentTypes = new[]
 1221        {
 1222            BaseItemKind.Series,
 1223            BaseItemKind.Season,
 1224            BaseItemKind.MusicAlbum,
 1225            BaseItemKind.MusicArtist,
 1226            BaseItemKind.PhotoAlbum
 1227        };
 228
 1229        private static readonly BaseItemKind[] _serviceTypes = new[]
 1230        {
 1231            BaseItemKind.TvChannel,
 1232            BaseItemKind.LiveTvChannel
 1233        };
 234
 1235        private static readonly BaseItemKind[] _startDateTypes = new[]
 1236        {
 1237            BaseItemKind.Program,
 1238            BaseItemKind.LiveTvProgram
 1239        };
 240
 1241        private static readonly BaseItemKind[] _seriesTypes = new[]
 1242        {
 1243            BaseItemKind.Book,
 1244            BaseItemKind.AudioBook,
 1245            BaseItemKind.Episode,
 1246            BaseItemKind.Season
 1247        };
 248
 1249        private static readonly BaseItemKind[] _artistExcludeParentTypes = new[]
 1250        {
 1251            BaseItemKind.Series,
 1252            BaseItemKind.Season,
 1253            BaseItemKind.PhotoAlbum
 1254        };
 255
 1256        private static readonly BaseItemKind[] _artistsTypes = new[]
 1257        {
 1258            BaseItemKind.Audio,
 1259            BaseItemKind.MusicAlbum,
 1260            BaseItemKind.MusicVideo,
 1261            BaseItemKind.AudioBook
 1262        };
 263
 1264        private static readonly Dictionary<BaseItemKind, string> _baseItemKindNames = new()
 1265        {
 1266            { BaseItemKind.AggregateFolder, typeof(AggregateFolder).FullName },
 1267            { BaseItemKind.Audio, typeof(Audio).FullName },
 1268            { BaseItemKind.AudioBook, typeof(AudioBook).FullName },
 1269            { BaseItemKind.BasePluginFolder, typeof(BasePluginFolder).FullName },
 1270            { BaseItemKind.Book, typeof(Book).FullName },
 1271            { BaseItemKind.BoxSet, typeof(BoxSet).FullName },
 1272            { BaseItemKind.Channel, typeof(Channel).FullName },
 1273            { BaseItemKind.CollectionFolder, typeof(CollectionFolder).FullName },
 1274            { BaseItemKind.Episode, typeof(Episode).FullName },
 1275            { BaseItemKind.Folder, typeof(Folder).FullName },
 1276            { BaseItemKind.Genre, typeof(Genre).FullName },
 1277            { BaseItemKind.Movie, typeof(Movie).FullName },
 1278            { BaseItemKind.LiveTvChannel, typeof(LiveTvChannel).FullName },
 1279            { BaseItemKind.LiveTvProgram, typeof(LiveTvProgram).FullName },
 1280            { BaseItemKind.MusicAlbum, typeof(MusicAlbum).FullName },
 1281            { BaseItemKind.MusicArtist, typeof(MusicArtist).FullName },
 1282            { BaseItemKind.MusicGenre, typeof(MusicGenre).FullName },
 1283            { BaseItemKind.MusicVideo, typeof(MusicVideo).FullName },
 1284            { BaseItemKind.Person, typeof(Person).FullName },
 1285            { BaseItemKind.Photo, typeof(Photo).FullName },
 1286            { BaseItemKind.PhotoAlbum, typeof(PhotoAlbum).FullName },
 1287            { BaseItemKind.Playlist, typeof(Playlist).FullName },
 1288            { BaseItemKind.PlaylistsFolder, typeof(PlaylistsFolder).FullName },
 1289            { BaseItemKind.Season, typeof(Season).FullName },
 1290            { BaseItemKind.Series, typeof(Series).FullName },
 1291            { BaseItemKind.Studio, typeof(Studio).FullName },
 1292            { BaseItemKind.Trailer, typeof(Trailer).FullName },
 1293            { BaseItemKind.TvChannel, typeof(LiveTvChannel).FullName },
 1294            { BaseItemKind.TvProgram, typeof(LiveTvProgram).FullName },
 1295            { BaseItemKind.UserRootFolder, typeof(UserRootFolder).FullName },
 1296            { BaseItemKind.UserView, typeof(UserView).FullName },
 1297            { BaseItemKind.Video, typeof(Video).FullName },
 1298            { BaseItemKind.Year, typeof(Year).FullName }
 1299        };
 300
 301        /// <summary>
 302        /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
 303        /// </summary>
 304        /// <param name="config">Instance of the <see cref="IServerConfigurationManager"/> interface.</param>
 305        /// <param name="appHost">Instance of the <see cref="IServerApplicationHost"/> interface.</param>
 306        /// <param name="logger">Instance of the <see cref="ILogger{SqliteItemRepository}"/> interface.</param>
 307        /// <param name="localization">Instance of the <see cref="ILocalizationManager"/> interface.</param>
 308        /// <param name="imageProcessor">Instance of the <see cref="IImageProcessor"/> interface.</param>
 309        /// <param name="configuration">Instance of the <see cref="IConfiguration"/> interface.</param>
 310        /// <exception cref="ArgumentNullException">config is null.</exception>
 311        public SqliteItemRepository(
 312            IServerConfigurationManager config,
 313            IServerApplicationHost appHost,
 314            ILogger<SqliteItemRepository> logger,
 315            ILocalizationManager localization,
 316            IImageProcessor imageProcessor,
 317            IConfiguration configuration)
 46318            : base(logger)
 319        {
 46320            _config = config;
 46321            _appHost = appHost;
 46322            _localization = localization;
 46323            _imageProcessor = imageProcessor;
 324
 46325            _typeMapper = new TypeMapper();
 46326            _jsonOptions = JsonDefaults.Options;
 327
 46328            DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db");
 329
 46330            CacheSize = configuration.GetSqliteCacheSize();
 46331        }
 332
 333        /// <inheritdoc />
 334        protected override int? CacheSize { get; }
 335
 336        /// <inheritdoc />
 674337        protected override TempStoreMode TempStore => TempStoreMode.Memory;
 338
 339        /// <summary>
 340        /// Opens the connection to the database.
 341        /// </summary>
 342        public override void Initialize()
 343        {
 22344            base.Initialize();
 345
 346            const string CreateMediaStreamsTableCommand
 347                    = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEX
 348
 349            const string CreateMediaAttachmentsTableCommand
 350                    = "create table if not exists mediaattachments (ItemId GUID, AttachmentIndex INT, Codec TEXT, CodecT
 351
 22352            string[] queries =
 22353            {
 22354                "create table if not exists TypedBaseItems (guid GUID primary key NOT NULL, type TEXT NOT NULL, data BLO
 22355
 22356                "create table if not exists AncestorIds (ItemId GUID NOT NULL, AncestorId GUID NOT NULL, AncestorIdText 
 22357                "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
 22358                "create index if not exists idx_AncestorIds5 on AncestorIds(AncestorIdText,ItemId)",
 22359
 22360                "create table if not exists ItemValues (ItemId GUID NOT NULL, Type INT NOT NULL, Value TEXT NOT NULL, Cl
 22361
 22362                "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrd
 22363
 22364                "drop index if exists idxPeopleItemId",
 22365                "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)",
 22366                "create index if not exists idxPeopleName on People(Name)",
 22367
 22368                "create table if not exists " + ChaptersTableName + " (ItemId GUID, ChapterIndex INT NOT NULL, StartPosi
 22369
 22370                CreateMediaStreamsTableCommand,
 22371                CreateMediaAttachmentsTableCommand,
 22372
 22373                "pragma shrink_memory"
 22374            };
 375
 22376            string[] postQueries =
 22377            {
 22378                "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
 22379                "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
 22380
 22381                "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
 22382                "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVir
 22383                "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)",
 22384
 22385                // covering index
 22386                "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)",
 22387
 22388                // series
 22389                "create index if not exists idx_TypeSeriesPresentationUniqueKey1 on TypedBaseItems(Type,SeriesPresentati
 22390
 22391                // series counts
 22392                // seriesdateplayed sort order
 22393                "create index if not exists idx_TypeSeriesPresentationUniqueKey3 on TypedBaseItems(SeriesPresentationUni
 22394
 22395                // live tv programs
 22396                "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)",
 22397
 22398                // covering index for getitemvalues
 22399                "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)",
 22400
 22401                // used by movie suggestions
 22402                "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniq
 22403                "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)",
 22404
 22405                // latest items
 22406                "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,Presen
 22407                "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,Pr
 22408
 22409                // resume
 22410                "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,P
 22411
 22412                // items by name
 22413                "create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)",
 22414                "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)",
 22415
 22416                // Used to update inherited tags
 22417                "create index if not exists idx_ItemValues8 on ItemValues(Type, ItemId, Value)",
 22418
 22419                "CREATE INDEX IF NOT EXISTS idx_TypedBaseItemsUserDataKeyType ON TypedBaseItems(UserDataKey, Type)",
 22420                "CREATE INDEX IF NOT EXISTS idx_PeopleNameListOrder ON People(Name, ListOrder)"
 22421            };
 422
 22423            using (var connection = GetConnection())
 22424            using (var transaction = connection.BeginTransaction())
 425            {
 22426                connection.Execute(string.Join(';', queries));
 427
 22428                var existingColumnNames = GetColumnNames(connection, "AncestorIds");
 22429                AddColumn(connection, "AncestorIds", "AncestorIdText", "Text", existingColumnNames);
 430
 22431                existingColumnNames = GetColumnNames(connection, "TypedBaseItems");
 432
 22433                AddColumn(connection, "TypedBaseItems", "Path", "Text", existingColumnNames);
 22434                AddColumn(connection, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames);
 22435                AddColumn(connection, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames);
 22436                AddColumn(connection, "TypedBaseItems", "ChannelId", "Text", existingColumnNames);
 22437                AddColumn(connection, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames);
 22438                AddColumn(connection, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames);
 22439                AddColumn(connection, "TypedBaseItems", "CustomRating", "Text", existingColumnNames);
 22440                AddColumn(connection, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames);
 22441                AddColumn(connection, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames);
 22442                AddColumn(connection, "TypedBaseItems", "Name", "Text", existingColumnNames);
 22443                AddColumn(connection, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames);
 22444                AddColumn(connection, "TypedBaseItems", "MediaType", "Text", existingColumnNames);
 22445                AddColumn(connection, "TypedBaseItems", "Overview", "Text", existingColumnNames);
 22446                AddColumn(connection, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames);
 22447                AddColumn(connection, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames);
 22448                AddColumn(connection, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames);
 22449                AddColumn(connection, "TypedBaseItems", "ParentId", "GUID", existingColumnNames);
 22450                AddColumn(connection, "TypedBaseItems", "Genres", "Text", existingColumnNames);
 22451                AddColumn(connection, "TypedBaseItems", "SortName", "Text", existingColumnNames);
 22452                AddColumn(connection, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames);
 22453                AddColumn(connection, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames);
 22454                AddColumn(connection, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames);
 22455                AddColumn(connection, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames);
 22456                AddColumn(connection, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames);
 22457                AddColumn(connection, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames);
 22458                AddColumn(connection, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames);
 22459                AddColumn(connection, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames);
 22460                AddColumn(connection, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames);
 22461                AddColumn(connection, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames);
 22462                AddColumn(connection, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames);
 22463                AddColumn(connection, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames);
 22464                AddColumn(connection, "TypedBaseItems", "LockedFields", "Text", existingColumnNames);
 22465                AddColumn(connection, "TypedBaseItems", "Studios", "Text", existingColumnNames);
 22466                AddColumn(connection, "TypedBaseItems", "Audio", "Text", existingColumnNames);
 22467                AddColumn(connection, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames);
 22468                AddColumn(connection, "TypedBaseItems", "Tags", "Text", existingColumnNames);
 22469                AddColumn(connection, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames);
 22470                AddColumn(connection, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames);
 22471                AddColumn(connection, "TypedBaseItems", "UnratedType", "Text", existingColumnNames);
 22472                AddColumn(connection, "TypedBaseItems", "TopParentId", "Text", existingColumnNames);
 22473                AddColumn(connection, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames);
 22474                AddColumn(connection, "TypedBaseItems", "CriticRating", "Float", existingColumnNames);
 22475                AddColumn(connection, "TypedBaseItems", "CleanName", "Text", existingColumnNames);
 22476                AddColumn(connection, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames);
 22477                AddColumn(connection, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames);
 22478                AddColumn(connection, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames);
 22479                AddColumn(connection, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames);
 22480                AddColumn(connection, "TypedBaseItems", "Album", "Text", existingColumnNames);
 22481                AddColumn(connection, "TypedBaseItems", "LUFS", "Float", existingColumnNames);
 22482                AddColumn(connection, "TypedBaseItems", "NormalizationGain", "Float", existingColumnNames);
 22483                AddColumn(connection, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames);
 22484                AddColumn(connection, "TypedBaseItems", "SeriesName", "Text", existingColumnNames);
 22485                AddColumn(connection, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames);
 22486                AddColumn(connection, "TypedBaseItems", "SeasonName", "Text", existingColumnNames);
 22487                AddColumn(connection, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames);
 22488                AddColumn(connection, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames);
 22489                AddColumn(connection, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames);
 22490                AddColumn(connection, "TypedBaseItems", "Tagline", "Text", existingColumnNames);
 22491                AddColumn(connection, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames);
 22492                AddColumn(connection, "TypedBaseItems", "Images", "Text", existingColumnNames);
 22493                AddColumn(connection, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames);
 22494                AddColumn(connection, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames);
 22495                AddColumn(connection, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames);
 22496                AddColumn(connection, "TypedBaseItems", "ExtraType", "Text", existingColumnNames);
 22497                AddColumn(connection, "TypedBaseItems", "Artists", "Text", existingColumnNames);
 22498                AddColumn(connection, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames);
 22499                AddColumn(connection, "TypedBaseItems", "ExternalId", "Text", existingColumnNames);
 22500                AddColumn(connection, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames);
 22501                AddColumn(connection, "TypedBaseItems", "ShowId", "Text", existingColumnNames);
 22502                AddColumn(connection, "TypedBaseItems", "OwnerId", "Text", existingColumnNames);
 22503                AddColumn(connection, "TypedBaseItems", "Width", "INT", existingColumnNames);
 22504                AddColumn(connection, "TypedBaseItems", "Height", "INT", existingColumnNames);
 22505                AddColumn(connection, "TypedBaseItems", "Size", "BIGINT", existingColumnNames);
 506
 22507                existingColumnNames = GetColumnNames(connection, "ItemValues");
 22508                AddColumn(connection, "ItemValues", "CleanValue", "Text", existingColumnNames);
 509
 22510                existingColumnNames = GetColumnNames(connection, ChaptersTableName);
 22511                AddColumn(connection, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames);
 512
 22513                existingColumnNames = GetColumnNames(connection, "MediaStreams");
 22514                AddColumn(connection, "MediaStreams", "IsAvc", "BIT", existingColumnNames);
 22515                AddColumn(connection, "MediaStreams", "TimeBase", "TEXT", existingColumnNames);
 22516                AddColumn(connection, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames);
 22517                AddColumn(connection, "MediaStreams", "Title", "TEXT", existingColumnNames);
 22518                AddColumn(connection, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames);
 22519                AddColumn(connection, "MediaStreams", "Comment", "TEXT", existingColumnNames);
 22520                AddColumn(connection, "MediaStreams", "CodecTag", "TEXT", existingColumnNames);
 22521                AddColumn(connection, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames);
 22522                AddColumn(connection, "MediaStreams", "BitDepth", "INT", existingColumnNames);
 22523                AddColumn(connection, "MediaStreams", "RefFrames", "INT", existingColumnNames);
 22524                AddColumn(connection, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames);
 22525                AddColumn(connection, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames);
 526
 22527                AddColumn(connection, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames);
 22528                AddColumn(connection, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames);
 22529                AddColumn(connection, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames);
 530
 22531                AddColumn(connection, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames);
 22532                AddColumn(connection, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames);
 22533                AddColumn(connection, "MediaStreams", "DvProfile", "INT", existingColumnNames);
 22534                AddColumn(connection, "MediaStreams", "DvLevel", "INT", existingColumnNames);
 22535                AddColumn(connection, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames);
 22536                AddColumn(connection, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames);
 22537                AddColumn(connection, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames);
 22538                AddColumn(connection, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames);
 539
 22540                AddColumn(connection, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames);
 541
 22542                AddColumn(connection, "MediaStreams", "Rotation", "INT", existingColumnNames);
 543
 22544                connection.Execute(string.Join(';', postQueries));
 545
 22546                transaction.Commit();
 22547            }
 22548        }
 549
 550        public void SaveImages(BaseItem item)
 551        {
 0552            ArgumentNullException.ThrowIfNull(item);
 553
 0554            CheckDisposed();
 555
 0556            var images = SerializeImages(item.ImageInfos);
 0557            using var connection = GetConnection();
 0558            using var transaction = connection.BeginTransaction();
 0559            using var saveImagesStatement = PrepareStatement(connection, "Update TypedBaseItems set Images=@Images where
 0560            saveImagesStatement.TryBind("@Id", item.Id);
 0561            saveImagesStatement.TryBind("@Images", images);
 562
 0563            saveImagesStatement.ExecuteNonQuery();
 0564            transaction.Commit();
 0565        }
 566
 567        /// <summary>
 568        /// Saves the items.
 569        /// </summary>
 570        /// <param name="items">The items.</param>
 571        /// <param name="cancellationToken">The cancellation token.</param>
 572        /// <exception cref="ArgumentNullException">
 573        /// <paramref name="items"/> or <paramref name="cancellationToken"/> is <c>null</c>.
 574        /// </exception>
 575        public void SaveItems(IReadOnlyList<BaseItem> items, CancellationToken cancellationToken)
 576        {
 59577            ArgumentNullException.ThrowIfNull(items);
 578
 59579            cancellationToken.ThrowIfCancellationRequested();
 580
 59581            CheckDisposed();
 582
 59583            var itemsLen = items.Count;
 59584            var tuples = new ValueTuple<BaseItem, List<Guid>, BaseItem, string, List<string>>[itemsLen];
 236585            for (int i = 0; i < itemsLen; i++)
 586            {
 59587                var item = items[i];
 59588                var ancestorIds = item.SupportsAncestors ?
 59589                    item.GetAncestorIds().Distinct().ToList() :
 59590                    null;
 591
 59592                var topParent = item.GetTopParent();
 593
 59594                var userdataKey = item.GetUserDataKeys().FirstOrDefault();
 59595                var inheritedTags = item.GetInheritedTags();
 596
 59597                tuples[i] = (item, ancestorIds, topParent, userdataKey, inheritedTags);
 598            }
 599
 59600            using var connection = GetConnection();
 59601            using var transaction = connection.BeginTransaction();
 59602            SaveItemsInTransaction(connection, tuples);
 59603            transaction.Commit();
 118604        }
 605
 606        private void SaveItemsInTransaction(ManagedConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, Ba
 607        {
 59608            using (var saveItemStatement = PrepareStatement(db, SaveItemCommandText))
 59609            using (var deleteAncestorsStatement = PrepareStatement(db, "delete from AncestorIds where ItemId=@ItemId"))
 610            {
 59611                var requiresReset = false;
 236612                foreach (var tuple in tuples)
 613                {
 59614                    if (requiresReset)
 615                    {
 0616                        saveItemStatement.Parameters.Clear();
 0617                        deleteAncestorsStatement.Parameters.Clear();
 618                    }
 619
 59620                    var item = tuple.Item;
 59621                    var topParent = tuple.TopParent;
 59622                    var userDataKey = tuple.UserDataKey;
 623
 59624                    SaveItem(item, topParent, userDataKey, saveItemStatement);
 625
 59626                    var inheritedTags = tuple.InheritedTags;
 627
 59628                    if (item.SupportsAncestors)
 629                    {
 59630                        UpdateAncestors(item.Id, tuple.AncestorIds, db, deleteAncestorsStatement);
 631                    }
 632
 59633                    UpdateItemValues(item.Id, GetItemValuesToSave(item, inheritedTags), db);
 634
 59635                    requiresReset = true;
 636                }
 637            }
 59638        }
 639
 640        private string GetPathToSave(string path)
 641        {
 64642            if (path is null)
 643            {
 0644                return null;
 645            }
 646
 64647            return _appHost.ReverseVirtualPath(path);
 648        }
 649
 650        private string RestorePath(string path)
 651        {
 100652            return _appHost.ExpandVirtualPath(path);
 653        }
 654
 655        private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, SqliteCommand saveItemStatement)
 656        {
 59657            Type type = item.GetType();
 658
 59659            saveItemStatement.TryBind("@guid", item.Id);
 59660            saveItemStatement.TryBind("@type", type.FullName);
 661
 59662            if (TypeRequiresDeserialization(type))
 663            {
 20664                saveItemStatement.TryBind("@data", JsonSerializer.SerializeToUtf8Bytes(item, type, _jsonOptions), true);
 665            }
 666            else
 667            {
 39668                saveItemStatement.TryBindNull("@data");
 669            }
 670
 59671            saveItemStatement.TryBind("@Path", GetPathToSave(item.Path));
 672
 59673            if (item is IHasStartDate hasStartDate)
 674            {
 0675                saveItemStatement.TryBind("@StartDate", hasStartDate.StartDate);
 676            }
 677            else
 678            {
 59679                saveItemStatement.TryBindNull("@StartDate");
 680            }
 681
 59682            if (item.EndDate.HasValue)
 683            {
 0684                saveItemStatement.TryBind("@EndDate", item.EndDate.Value);
 685            }
 686            else
 687            {
 59688                saveItemStatement.TryBindNull("@EndDate");
 689            }
 690
 59691            saveItemStatement.TryBind("@ChannelId", item.ChannelId.IsEmpty() ? null : item.ChannelId.ToString("N", Cultu
 692
 59693            if (item is IHasProgramAttributes hasProgramAttributes)
 694            {
 0695                saveItemStatement.TryBind("@IsMovie", hasProgramAttributes.IsMovie);
 0696                saveItemStatement.TryBind("@IsSeries", hasProgramAttributes.IsSeries);
 0697                saveItemStatement.TryBind("@EpisodeTitle", hasProgramAttributes.EpisodeTitle);
 0698                saveItemStatement.TryBind("@IsRepeat", hasProgramAttributes.IsRepeat);
 699            }
 700            else
 701            {
 59702                saveItemStatement.TryBindNull("@IsMovie");
 59703                saveItemStatement.TryBindNull("@IsSeries");
 59704                saveItemStatement.TryBindNull("@EpisodeTitle");
 59705                saveItemStatement.TryBindNull("@IsRepeat");
 706            }
 707
 59708            saveItemStatement.TryBind("@CommunityRating", item.CommunityRating);
 59709            saveItemStatement.TryBind("@CustomRating", item.CustomRating);
 59710            saveItemStatement.TryBind("@IndexNumber", item.IndexNumber);
 59711            saveItemStatement.TryBind("@IsLocked", item.IsLocked);
 59712            saveItemStatement.TryBind("@Name", item.Name);
 59713            saveItemStatement.TryBind("@OfficialRating", item.OfficialRating);
 59714            saveItemStatement.TryBind("@MediaType", item.MediaType.ToString());
 59715            saveItemStatement.TryBind("@Overview", item.Overview);
 59716            saveItemStatement.TryBind("@ParentIndexNumber", item.ParentIndexNumber);
 59717            saveItemStatement.TryBind("@PremiereDate", item.PremiereDate);
 59718            saveItemStatement.TryBind("@ProductionYear", item.ProductionYear);
 719
 59720            var parentId = item.ParentId;
 59721            if (parentId.IsEmpty())
 722            {
 35723                saveItemStatement.TryBindNull("@ParentId");
 724            }
 725            else
 726            {
 24727                saveItemStatement.TryBind("@ParentId", parentId);
 728            }
 729
 59730            if (item.Genres.Length > 0)
 731            {
 0732                saveItemStatement.TryBind("@Genres", string.Join('|', item.Genres));
 733            }
 734            else
 735            {
 59736                saveItemStatement.TryBindNull("@Genres");
 737            }
 738
 59739            saveItemStatement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue);
 740
 59741            saveItemStatement.TryBind("@SortName", item.SortName);
 742
 59743            saveItemStatement.TryBind("@ForcedSortName", item.ForcedSortName);
 744
 59745            saveItemStatement.TryBind("@RunTimeTicks", item.RunTimeTicks);
 59746            saveItemStatement.TryBind("@Size", item.Size);
 747
 59748            saveItemStatement.TryBind("@DateCreated", item.DateCreated);
 59749            saveItemStatement.TryBind("@DateModified", item.DateModified);
 750
 59751            saveItemStatement.TryBind("@PreferredMetadataLanguage", item.PreferredMetadataLanguage);
 59752            saveItemStatement.TryBind("@PreferredMetadataCountryCode", item.PreferredMetadataCountryCode);
 753
 59754            if (item.Width > 0)
 755            {
 0756                saveItemStatement.TryBind("@Width", item.Width);
 757            }
 758            else
 759            {
 59760                saveItemStatement.TryBindNull("@Width");
 761            }
 762
 59763            if (item.Height > 0)
 764            {
 0765                saveItemStatement.TryBind("@Height", item.Height);
 766            }
 767            else
 768            {
 59769                saveItemStatement.TryBindNull("@Height");
 770            }
 771
 59772            if (item.DateLastRefreshed != default(DateTime))
 773            {
 36774                saveItemStatement.TryBind("@DateLastRefreshed", item.DateLastRefreshed);
 775            }
 776            else
 777            {
 23778                saveItemStatement.TryBindNull("@DateLastRefreshed");
 779            }
 780
 59781            if (item.DateLastSaved != default(DateTime))
 782            {
 58783                saveItemStatement.TryBind("@DateLastSaved", item.DateLastSaved);
 784            }
 785            else
 786            {
 1787                saveItemStatement.TryBindNull("@DateLastSaved");
 788            }
 789
 59790            saveItemStatement.TryBind("@IsInMixedFolder", item.IsInMixedFolder);
 791
 59792            if (item.LockedFields.Length > 0)
 793            {
 0794                saveItemStatement.TryBind("@LockedFields", string.Join('|', item.LockedFields));
 795            }
 796            else
 797            {
 59798                saveItemStatement.TryBindNull("@LockedFields");
 799            }
 800
 59801            if (item.Studios.Length > 0)
 802            {
 0803                saveItemStatement.TryBind("@Studios", string.Join('|', item.Studios));
 804            }
 805            else
 806            {
 59807                saveItemStatement.TryBindNull("@Studios");
 808            }
 809
 59810            if (item.Audio.HasValue)
 811            {
 0812                saveItemStatement.TryBind("@Audio", item.Audio.Value.ToString());
 813            }
 814            else
 815            {
 59816                saveItemStatement.TryBindNull("@Audio");
 817            }
 818
 59819            if (item is LiveTvChannel liveTvChannel)
 820            {
 0821                saveItemStatement.TryBind("@ExternalServiceId", liveTvChannel.ServiceName);
 822            }
 823            else
 824            {
 59825                saveItemStatement.TryBindNull("@ExternalServiceId");
 826            }
 827
 59828            if (item.Tags.Length > 0)
 829            {
 0830                saveItemStatement.TryBind("@Tags", string.Join('|', item.Tags));
 831            }
 832            else
 833            {
 59834                saveItemStatement.TryBindNull("@Tags");
 835            }
 836
 59837            saveItemStatement.TryBind("@IsFolder", item.IsFolder);
 838
 59839            saveItemStatement.TryBind("@UnratedType", item.GetBlockUnratedType().ToString());
 840
 59841            if (topParent is null)
 842            {
 37843                saveItemStatement.TryBindNull("@TopParentId");
 844            }
 845            else
 846            {
 22847                saveItemStatement.TryBind("@TopParentId", topParent.Id.ToString("N", CultureInfo.InvariantCulture));
 848            }
 849
 59850            if (item is Trailer trailer && trailer.TrailerTypes.Length > 0)
 851            {
 0852                saveItemStatement.TryBind("@TrailerTypes", string.Join('|', trailer.TrailerTypes));
 853            }
 854            else
 855            {
 59856                saveItemStatement.TryBindNull("@TrailerTypes");
 857            }
 858
 59859            saveItemStatement.TryBind("@CriticRating", item.CriticRating);
 860
 59861            if (string.IsNullOrWhiteSpace(item.Name))
 862            {
 0863                saveItemStatement.TryBindNull("@CleanName");
 864            }
 865            else
 866            {
 59867                saveItemStatement.TryBind("@CleanName", GetCleanValue(item.Name));
 868            }
 869
 59870            saveItemStatement.TryBind("@PresentationUniqueKey", item.PresentationUniqueKey);
 59871            saveItemStatement.TryBind("@OriginalTitle", item.OriginalTitle);
 872
 59873            if (item is Video video)
 874            {
 0875                saveItemStatement.TryBind("@PrimaryVersionId", video.PrimaryVersionId);
 876            }
 877            else
 878            {
 59879                saveItemStatement.TryBindNull("@PrimaryVersionId");
 880            }
 881
 59882            if (item is Folder folder && folder.DateLastMediaAdded.HasValue)
 883            {
 0884                saveItemStatement.TryBind("@DateLastMediaAdded", folder.DateLastMediaAdded.Value);
 885            }
 886            else
 887            {
 59888                saveItemStatement.TryBindNull("@DateLastMediaAdded");
 889            }
 890
 59891            saveItemStatement.TryBind("@Album", item.Album);
 59892            saveItemStatement.TryBind("@LUFS", item.LUFS);
 59893            saveItemStatement.TryBind("@NormalizationGain", item.NormalizationGain);
 59894            saveItemStatement.TryBind("@IsVirtualItem", item.IsVirtualItem);
 895
 59896            if (item is IHasSeries hasSeriesName)
 897            {
 0898                saveItemStatement.TryBind("@SeriesName", hasSeriesName.SeriesName);
 899            }
 900            else
 901            {
 59902                saveItemStatement.TryBindNull("@SeriesName");
 903            }
 904
 59905            if (string.IsNullOrWhiteSpace(userDataKey))
 906            {
 0907                saveItemStatement.TryBindNull("@UserDataKey");
 908            }
 909            else
 910            {
 59911                saveItemStatement.TryBind("@UserDataKey", userDataKey);
 912            }
 913
 59914            if (item is Episode episode)
 915            {
 0916                saveItemStatement.TryBind("@SeasonName", episode.SeasonName);
 917
 0918                var nullableSeasonId = episode.SeasonId.IsEmpty() ? (Guid?)null : episode.SeasonId;
 919
 0920                saveItemStatement.TryBind("@SeasonId", nullableSeasonId);
 921            }
 922            else
 923            {
 59924                saveItemStatement.TryBindNull("@SeasonName");
 59925                saveItemStatement.TryBindNull("@SeasonId");
 926            }
 927
 59928            if (item is IHasSeries hasSeries)
 929            {
 0930                var nullableSeriesId = hasSeries.SeriesId.IsEmpty() ? (Guid?)null : hasSeries.SeriesId;
 931
 0932                saveItemStatement.TryBind("@SeriesId", nullableSeriesId);
 0933                saveItemStatement.TryBind("@SeriesPresentationUniqueKey", hasSeries.SeriesPresentationUniqueKey);
 934            }
 935            else
 936            {
 59937                saveItemStatement.TryBindNull("@SeriesId");
 59938                saveItemStatement.TryBindNull("@SeriesPresentationUniqueKey");
 939            }
 940
 59941            saveItemStatement.TryBind("@ExternalSeriesId", item.ExternalSeriesId);
 59942            saveItemStatement.TryBind("@Tagline", item.Tagline);
 943
 59944            saveItemStatement.TryBind("@ProviderIds", SerializeProviderIds(item.ProviderIds));
 59945            saveItemStatement.TryBind("@Images", SerializeImages(item.ImageInfos));
 946
 59947            if (item.ProductionLocations.Length > 0)
 948            {
 0949                saveItemStatement.TryBind("@ProductionLocations", string.Join('|', item.ProductionLocations));
 950            }
 951            else
 952            {
 59953                saveItemStatement.TryBindNull("@ProductionLocations");
 954            }
 955
 59956            if (item.ExtraIds.Length > 0)
 957            {
 0958                saveItemStatement.TryBind("@ExtraIds", string.Join('|', item.ExtraIds));
 959            }
 960            else
 961            {
 59962                saveItemStatement.TryBindNull("@ExtraIds");
 963            }
 964
 59965            saveItemStatement.TryBind("@TotalBitrate", item.TotalBitrate);
 59966            if (item.ExtraType.HasValue)
 967            {
 0968                saveItemStatement.TryBind("@ExtraType", item.ExtraType.Value.ToString());
 969            }
 970            else
 971            {
 59972                saveItemStatement.TryBindNull("@ExtraType");
 973            }
 974
 59975            string artists = null;
 59976            if (item is IHasArtist hasArtists && hasArtists.Artists.Count > 0)
 977            {
 0978                artists = string.Join('|', hasArtists.Artists);
 979            }
 980
 59981            saveItemStatement.TryBind("@Artists", artists);
 982
 59983            string albumArtists = null;
 59984            if (item is IHasAlbumArtist hasAlbumArtists
 59985                && hasAlbumArtists.AlbumArtists.Count > 0)
 986            {
 0987                albumArtists = string.Join('|', hasAlbumArtists.AlbumArtists);
 988            }
 989
 59990            saveItemStatement.TryBind("@AlbumArtists", albumArtists);
 59991            saveItemStatement.TryBind("@ExternalId", item.ExternalId);
 992
 59993            if (item is LiveTvProgram program)
 994            {
 0995                saveItemStatement.TryBind("@ShowId", program.ShowId);
 996            }
 997            else
 998            {
 59999                saveItemStatement.TryBindNull("@ShowId");
 1000            }
 1001
 591002            Guid ownerId = item.OwnerId;
 591003            if (ownerId.IsEmpty())
 1004            {
 591005                saveItemStatement.TryBindNull("@OwnerId");
 1006            }
 1007            else
 1008            {
 01009                saveItemStatement.TryBind("@OwnerId", ownerId);
 1010            }
 1011
 591012            saveItemStatement.ExecuteNonQuery();
 591013        }
 1014
 1015        internal static string SerializeProviderIds(Dictionary<string, string> providerIds)
 1016        {
 621017            StringBuilder str = new StringBuilder();
 1421018            foreach (var i in providerIds)
 1019            {
 1020                // Ideally we shouldn't need this IsNullOrWhiteSpace check,
 1021                // but we're seeing some cases of bad data slip through
 91022                if (string.IsNullOrWhiteSpace(i.Value))
 1023                {
 1024                    continue;
 1025                }
 1026
 91027                str.Append(i.Key)
 91028                    .Append('=')
 91029                    .Append(i.Value)
 91030                    .Append('|');
 1031            }
 1032
 621033            if (str.Length == 0)
 1034            {
 591035                return null;
 1036            }
 1037
 31038            str.Length -= 1; // Remove last |
 31039            return str.ToString();
 1040        }
 1041
 1042        internal static void DeserializeProviderIds(string value, IHasProviderIds item)
 1043        {
 31044            if (string.IsNullOrWhiteSpace(value))
 1045            {
 01046                return;
 1047            }
 1048
 241049            foreach (var part in value.SpanSplit('|'))
 1050            {
 91051                var providerDelimiterIndex = part.IndexOf('=');
 1052                // Don't let empty values through
 91053                if (providerDelimiterIndex != -1 && part.Length != providerDelimiterIndex + 1)
 1054                {
 91055                    item.SetProviderId(part[..providerDelimiterIndex].ToString(), part[(providerDelimiterIndex + 1)..].T
 1056                }
 1057            }
 31058        }
 1059
 1060        internal string SerializeImages(ItemImageInfo[] images)
 1061        {
 611062            if (images.Length == 0)
 1063            {
 591064                return null;
 1065            }
 1066
 21067            StringBuilder str = new StringBuilder();
 141068            foreach (var i in images)
 1069            {
 51070                if (string.IsNullOrWhiteSpace(i.Path))
 1071                {
 1072                    continue;
 1073                }
 1074
 51075                AppendItemImageInfo(str, i);
 51076                str.Append('|');
 1077            }
 1078
 21079            str.Length -= 1; // Remove last |
 21080            return str.ToString();
 1081        }
 1082
 1083        internal ItemImageInfo[] DeserializeImages(string value)
 1084        {
 51085            if (string.IsNullOrWhiteSpace(value))
 1086            {
 11087                return Array.Empty<ItemImageInfo>();
 1088            }
 1089
 1090            // TODO The following is an ugly performance optimization, but it's extremely unlikely that the data in the 
 41091            var valueSpan = value.AsSpan();
 41092            var count = valueSpan.Count('|') + 1;
 1093
 41094            var position = 0;
 41095            var result = new ItemImageInfo[count];
 301096            foreach (var part in valueSpan.Split('|'))
 1097            {
 111098                var image = ItemImageInfoFromValueString(part);
 1099
 111100                if (image is not null)
 1101                {
 61102                    result[position++] = image;
 1103                }
 1104            }
 1105
 41106            if (position == count)
 1107            {
 21108                return result;
 1109            }
 1110
 21111            if (position == 0)
 1112            {
 11113                return Array.Empty<ItemImageInfo>();
 1114            }
 1115
 1116            // Extremely unlikely, but somehow one or more of the image strings were malformed. Cut the array.
 11117            return result[..position];
 1118        }
 1119
 1120        private void AppendItemImageInfo(StringBuilder bldr, ItemImageInfo image)
 1121        {
 1122            const char Delimiter = '*';
 1123
 51124            var path = image.Path ?? string.Empty;
 1125
 51126            bldr.Append(GetPathToSave(path))
 51127                .Append(Delimiter)
 51128                .Append(image.DateModified.Ticks)
 51129                .Append(Delimiter)
 51130                .Append(image.Type)
 51131                .Append(Delimiter)
 51132                .Append(image.Width)
 51133                .Append(Delimiter)
 51134                .Append(image.Height);
 1135
 51136            var hash = image.BlurHash;
 51137            if (!string.IsNullOrEmpty(hash))
 1138            {
 11139                bldr.Append(Delimiter)
 11140                    // Replace delimiters with other characters.
 11141                    // This can be removed when we migrate to a proper DB.
 11142                    .Append(hash.Replace(Delimiter, '/').Replace('|', '\\'));
 1143            }
 51144        }
 1145
 1146        internal ItemImageInfo ItemImageInfoFromValueString(ReadOnlySpan<char> value)
 1147        {
 1148            const char Delimiter = '*';
 1149
 221150            var nextSegment = value.IndexOf(Delimiter);
 221151            if (nextSegment == -1)
 1152            {
 61153                return null;
 1154            }
 1155
 161156            ReadOnlySpan<char> path = value[..nextSegment];
 161157            value = value[(nextSegment + 1)..];
 161158            nextSegment = value.IndexOf(Delimiter);
 161159            if (nextSegment == -1)
 1160            {
 21161                return null;
 1162            }
 1163
 141164            ReadOnlySpan<char> dateModified = value[..nextSegment];
 141165            value = value[(nextSegment + 1)..];
 141166            nextSegment = value.IndexOf(Delimiter);
 141167            if (nextSegment == -1)
 1168            {
 31169                nextSegment = value.Length;
 1170            }
 1171
 141172            ReadOnlySpan<char> imageType = value[..nextSegment];
 1173
 141174            var image = new ItemImageInfo
 141175            {
 141176                Path = RestorePath(path.ToString())
 141177            };
 1178
 141179            if (long.TryParse(dateModified, CultureInfo.InvariantCulture, out var ticks)
 141180                && ticks >= DateTime.MinValue.Ticks
 141181                && ticks <= DateTime.MaxValue.Ticks)
 1182            {
 121183                image.DateModified = new DateTime(ticks, DateTimeKind.Utc);
 1184            }
 1185            else
 1186            {
 21187                return null;
 1188            }
 1189
 121190            if (Enum.TryParse(imageType, true, out ImageType type))
 1191            {
 111192                image.Type = type;
 1193            }
 1194            else
 1195            {
 11196                return null;
 1197            }
 1198
 1199            // Optional parameters: width*height*blurhash
 111200            if (nextSegment + 1 < value.Length - 1)
 1201            {
 101202                value = value[(nextSegment + 1)..];
 101203                nextSegment = value.IndexOf(Delimiter);
 101204                if (nextSegment == -1 || nextSegment == value.Length)
 1205                {
 11206                    return image;
 1207                }
 1208
 91209                ReadOnlySpan<char> widthSpan = value[..nextSegment];
 1210
 91211                value = value[(nextSegment + 1)..];
 91212                nextSegment = value.IndexOf(Delimiter);
 91213                if (nextSegment == -1)
 1214                {
 61215                    nextSegment = value.Length;
 1216                }
 1217
 91218                ReadOnlySpan<char> heightSpan = value[..nextSegment];
 1219
 91220                if (int.TryParse(widthSpan, NumberStyles.Integer, CultureInfo.InvariantCulture, out var width)
 91221                    && int.TryParse(heightSpan, NumberStyles.Integer, CultureInfo.InvariantCulture, out var height))
 1222                {
 91223                    image.Width = width;
 91224                    image.Height = height;
 1225                }
 1226
 91227                if (nextSegment < value.Length - 1)
 1228                {
 31229                    value = value[(nextSegment + 1)..];
 31230                    var length = value.Length;
 1231
 31232                    Span<char> blurHashSpan = stackalloc char[length];
 3181233                    for (int i = 0; i < length; i++)
 1234                    {
 1561235                        var c = value[i];
 1561236                        blurHashSpan[i] = c switch
 1561237                        {
 31238                            '/' => Delimiter,
 01239                            '\\' => '|',
 1531240                            _ => c
 1561241                        };
 1242                    }
 1243
 31244                    image.BlurHash = new string(blurHashSpan);
 1245                }
 1246            }
 1247
 101248            return image;
 1249        }
 1250
 1251        /// <summary>
 1252        /// Internal retrieve from items or users table.
 1253        /// </summary>
 1254        /// <param name="id">The id.</param>
 1255        /// <returns>BaseItem.</returns>
 1256        /// <exception cref="ArgumentNullException"><paramref name="id"/> is <c>null</c>.</exception>
 1257        /// <exception cref="ArgumentException"><paramr name="id"/> is <seealso cref="Guid.Empty"/>.</exception>
 1258        public BaseItem RetrieveItem(Guid id)
 1259        {
 2401260            if (id.IsEmpty())
 1261            {
 01262                throw new ArgumentException("Guid can't be empty", nameof(id));
 1263            }
 1264
 2401265            CheckDisposed();
 1266
 2401267            using (var connection = GetConnection(true))
 2401268            using (var statement = PrepareStatement(connection, _retrieveItemColumnsSelectQuery))
 1269            {
 2401270                statement.TryBind("@guid", id);
 1271
 4801272                foreach (var row in statement.ExecuteQuery())
 1273                {
 01274                    return GetItem(row, new InternalItemsQuery());
 1275                }
 1276            }
 1277
 2401278            return null;
 01279        }
 1280
 1281        private bool TypeRequiresDeserialization(Type type)
 1282        {
 1451283            if (_config.Configuration.SkipDeserializationForBasicTypes)
 1284            {
 1451285                if (type == typeof(Channel)
 1451286                    || type == typeof(UserRootFolder))
 1287                {
 171288                    return false;
 1289                }
 1290            }
 1291
 1281292            return type != typeof(Season)
 1281293                && type != typeof(MusicArtist)
 1281294                && type != typeof(Person)
 1281295                && type != typeof(MusicGenre)
 1281296                && type != typeof(Genre)
 1281297                && type != typeof(Studio)
 1281298                && type != typeof(PlaylistsFolder)
 1281299                && type != typeof(PhotoAlbum)
 1281300                && type != typeof(Year)
 1281301                && type != typeof(Book)
 1281302                && type != typeof(LiveTvProgram)
 1281303                && type != typeof(AudioBook)
 1281304                && type != typeof(MusicAlbum);
 1305        }
 1306
 1307        private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query)
 1308        {
 01309            return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasServiceName(query
 1310        }
 1311
 1312        private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query, bool enableProgramAttributes, bool h
 1313        {
 861314            var typeString = reader.GetString(0);
 1315
 861316            var type = _typeMapper.GetType(typeString);
 1317
 861318            if (type is null)
 1319            {
 01320                return null;
 1321            }
 1322
 861323            BaseItem item = null;
 1324
 861325            if (TypeRequiresDeserialization(type) && !skipDeserialization)
 1326            {
 1327                try
 1328                {
 61329                    item = JsonSerializer.Deserialize(reader.GetStream(1), type, _jsonOptions) as BaseItem;
 61330                }
 01331                catch (JsonException ex)
 1332                {
 01333                    Logger.LogError(ex, "Error deserializing item with JSON: {Data}", reader.GetString(1));
 01334                }
 1335            }
 1336
 861337            if (item is null)
 1338            {
 1339                try
 1340                {
 801341                    item = Activator.CreateInstance(type) as BaseItem;
 801342                }
 01343                catch
 1344                {
 01345                }
 1346            }
 1347
 861348            if (item is null)
 1349            {
 01350                return null;
 1351            }
 1352
 861353            var index = 2;
 1354
 861355            if (queryHasStartDate)
 1356            {
 861357                if (item is IHasStartDate hasStartDate && reader.TryReadDateTime(index, out var startDate))
 1358                {
 01359                    hasStartDate.StartDate = startDate;
 1360                }
 1361
 861362                index++;
 1363            }
 1364
 861365            if (reader.TryReadDateTime(index++, out var endDate))
 1366            {
 01367                item.EndDate = endDate;
 1368            }
 1369
 861370            if (reader.TryGetGuid(index, out var guid))
 1371            {
 01372                item.ChannelId = guid;
 1373            }
 1374
 861375            index++;
 1376
 861377            if (enableProgramAttributes)
 1378            {
 861379                if (item is IHasProgramAttributes hasProgramAttributes)
 1380                {
 01381                    if (reader.TryGetBoolean(index++, out var isMovie))
 1382                    {
 01383                        hasProgramAttributes.IsMovie = isMovie;
 1384                    }
 1385
 01386                    if (reader.TryGetBoolean(index++, out var isSeries))
 1387                    {
 01388                        hasProgramAttributes.IsSeries = isSeries;
 1389                    }
 1390
 01391                    if (reader.TryGetString(index++, out var episodeTitle))
 1392                    {
 01393                        hasProgramAttributes.EpisodeTitle = episodeTitle;
 1394                    }
 1395
 01396                    if (reader.TryGetBoolean(index++, out var isRepeat))
 1397                    {
 01398                        hasProgramAttributes.IsRepeat = isRepeat;
 1399                    }
 1400                }
 1401                else
 1402                {
 861403                    index += 4;
 1404                }
 1405            }
 1406
 861407            if (reader.TryGetSingle(index++, out var communityRating))
 1408            {
 01409                item.CommunityRating = communityRating;
 1410            }
 1411
 861412            if (HasField(query, ItemFields.CustomRating))
 1413            {
 861414                if (reader.TryGetString(index++, out var customRating))
 1415                {
 01416                    item.CustomRating = customRating;
 1417                }
 1418            }
 1419
 861420            if (reader.TryGetInt32(index++, out var indexNumber))
 1421            {
 01422                item.IndexNumber = indexNumber;
 1423            }
 1424
 861425            if (HasField(query, ItemFields.Settings))
 1426            {
 861427                if (reader.TryGetBoolean(index++, out var isLocked))
 1428                {
 861429                    item.IsLocked = isLocked;
 1430                }
 1431
 861432                if (reader.TryGetString(index++, out var preferredMetadataLanguage))
 1433                {
 01434                    item.PreferredMetadataLanguage = preferredMetadataLanguage;
 1435                }
 1436
 861437                if (reader.TryGetString(index++, out var preferredMetadataCountryCode))
 1438                {
 01439                    item.PreferredMetadataCountryCode = preferredMetadataCountryCode;
 1440                }
 1441            }
 1442
 861443            if (HasField(query, ItemFields.Width))
 1444            {
 861445                if (reader.TryGetInt32(index++, out var width))
 1446                {
 01447                    item.Width = width;
 1448                }
 1449            }
 1450
 861451            if (HasField(query, ItemFields.Height))
 1452            {
 861453                if (reader.TryGetInt32(index++, out var height))
 1454                {
 01455                    item.Height = height;
 1456                }
 1457            }
 1458
 861459            if (HasField(query, ItemFields.DateLastRefreshed))
 1460            {
 861461                if (reader.TryReadDateTime(index++, out var dateLastRefreshed))
 1462                {
 61463                    item.DateLastRefreshed = dateLastRefreshed;
 1464                }
 1465            }
 1466
 861467            if (reader.TryGetString(index++, out var name))
 1468            {
 861469                item.Name = name;
 1470            }
 1471
 861472            if (reader.TryGetString(index++, out var restorePath))
 1473            {
 861474                item.Path = RestorePath(restorePath);
 1475            }
 1476
 861477            if (reader.TryReadDateTime(index++, out var premiereDate))
 1478            {
 01479                item.PremiereDate = premiereDate;
 1480            }
 1481
 861482            if (HasField(query, ItemFields.Overview))
 1483            {
 861484                if (reader.TryGetString(index++, out var overview))
 1485                {
 01486                    item.Overview = overview;
 1487                }
 1488            }
 1489
 861490            if (reader.TryGetInt32(index++, out var parentIndexNumber))
 1491            {
 01492                item.ParentIndexNumber = parentIndexNumber;
 1493            }
 1494
 861495            if (reader.TryGetInt32(index++, out var productionYear))
 1496            {
 01497                item.ProductionYear = productionYear;
 1498            }
 1499
 861500            if (reader.TryGetString(index++, out var officialRating))
 1501            {
 01502                item.OfficialRating = officialRating;
 1503            }
 1504
 861505            if (HasField(query, ItemFields.SortName))
 1506            {
 861507                if (reader.TryGetString(index++, out var forcedSortName))
 1508                {
 01509                    item.ForcedSortName = forcedSortName;
 1510                }
 1511            }
 1512
 861513            if (reader.TryGetInt64(index++, out var runTimeTicks))
 1514            {
 01515                item.RunTimeTicks = runTimeTicks;
 1516            }
 1517
 861518            if (reader.TryGetInt64(index++, out var size))
 1519            {
 01520                item.Size = size;
 1521            }
 1522
 861523            if (HasField(query, ItemFields.DateCreated))
 1524            {
 861525                if (reader.TryReadDateTime(index++, out var dateCreated))
 1526                {
 861527                    item.DateCreated = dateCreated;
 1528                }
 1529            }
 1530
 861531            if (reader.TryReadDateTime(index++, out var dateModified))
 1532            {
 861533                item.DateModified = dateModified;
 1534            }
 1535
 861536            item.Id = reader.GetGuid(index++);
 1537
 861538            if (HasField(query, ItemFields.Genres))
 1539            {
 861540                if (reader.TryGetString(index++, out var genres))
 1541                {
 01542                    item.Genres = genres.Split('|', StringSplitOptions.RemoveEmptyEntries);
 1543                }
 1544            }
 1545
 861546            if (reader.TryGetGuid(index++, out var parentId))
 1547            {
 861548                item.ParentId = parentId;
 1549            }
 1550
 861551            if (reader.TryGetString(index++, out var audioString))
 1552            {
 01553                if (Enum.TryParse(audioString, true, out ProgramAudio audio))
 1554                {
 01555                    item.Audio = audio;
 1556                }
 1557            }
 1558
 1559            // TODO: Even if not needed by apps, the server needs it internally
 1560            // But get this excluded from contexts where it is not needed
 861561            if (hasServiceName)
 1562            {
 861563                if (item is LiveTvChannel liveTvChannel)
 1564                {
 01565                    if (reader.TryGetString(index, out var serviceName))
 1566                    {
 01567                        liveTvChannel.ServiceName = serviceName;
 1568                    }
 1569                }
 1570
 861571                index++;
 1572            }
 1573
 861574            if (reader.TryGetBoolean(index++, out var isInMixedFolder))
 1575            {
 861576                item.IsInMixedFolder = isInMixedFolder;
 1577            }
 1578
 861579            if (HasField(query, ItemFields.DateLastSaved))
 1580            {
 861581                if (reader.TryReadDateTime(index++, out var dateLastSaved))
 1582                {
 861583                    item.DateLastSaved = dateLastSaved;
 1584                }
 1585            }
 1586
 861587            if (HasField(query, ItemFields.Settings))
 1588            {
 861589                if (reader.TryGetString(index++, out var lockedFields))
 1590                {
 01591                    List<MetadataField> fields = null;
 01592                    foreach (var i in lockedFields.AsSpan().Split('|'))
 1593                    {
 01594                        if (Enum.TryParse(i, true, out MetadataField parsedValue))
 1595                        {
 01596                            (fields ??= new List<MetadataField>()).Add(parsedValue);
 1597                        }
 1598                    }
 1599
 01600                    item.LockedFields = fields?.ToArray() ?? Array.Empty<MetadataField>();
 1601                }
 1602            }
 1603
 861604            if (HasField(query, ItemFields.Studios))
 1605            {
 861606                if (reader.TryGetString(index++, out var studios))
 1607                {
 01608                    item.Studios = studios.Split('|', StringSplitOptions.RemoveEmptyEntries);
 1609                }
 1610            }
 1611
 861612            if (HasField(query, ItemFields.Tags))
 1613            {
 861614                if (reader.TryGetString(index++, out var tags))
 1615                {
 01616                    item.Tags = tags.Split('|', StringSplitOptions.RemoveEmptyEntries);
 1617                }
 1618            }
 1619
 861620            if (hasTrailerTypes)
 1621            {
 861622                if (item is Trailer trailer)
 1623                {
 01624                    if (reader.TryGetString(index, out var trailerTypes))
 1625                    {
 01626                        List<TrailerType> types = null;
 01627                        foreach (var i in trailerTypes.AsSpan().Split('|'))
 1628                        {
 01629                            if (Enum.TryParse(i, true, out TrailerType parsedValue))
 1630                            {
 01631                                (types ??= new List<TrailerType>()).Add(parsedValue);
 1632                            }
 1633                        }
 1634
 01635                        trailer.TrailerTypes = types?.ToArray() ?? Array.Empty<TrailerType>();
 1636                    }
 1637                }
 1638
 861639                index++;
 1640            }
 1641
 861642            if (HasField(query, ItemFields.OriginalTitle))
 1643            {
 861644                if (reader.TryGetString(index++, out var originalTitle))
 1645                {
 01646                    item.OriginalTitle = originalTitle;
 1647                }
 1648            }
 1649
 861650            if (item is Video video)
 1651            {
 01652                if (reader.TryGetString(index, out var primaryVersionId))
 1653                {
 01654                    video.PrimaryVersionId = primaryVersionId;
 1655                }
 1656            }
 1657
 861658            index++;
 1659
 861660            if (HasField(query, ItemFields.DateLastMediaAdded))
 1661            {
 861662                if (item is Folder folder && reader.TryReadDateTime(index, out var dateLastMediaAdded))
 1663                {
 01664                    folder.DateLastMediaAdded = dateLastMediaAdded;
 1665                }
 1666
 861667                index++;
 1668            }
 1669
 861670            if (reader.TryGetString(index++, out var album))
 1671            {
 01672                item.Album = album;
 1673            }
 1674
 861675            if (reader.TryGetSingle(index++, out var lUFS))
 1676            {
 01677                item.LUFS = lUFS;
 1678            }
 1679
 861680            if (reader.TryGetSingle(index++, out var normalizationGain))
 1681            {
 01682                item.NormalizationGain = normalizationGain;
 1683            }
 1684
 861685            if (reader.TryGetSingle(index++, out var criticRating))
 1686            {
 01687                item.CriticRating = criticRating;
 1688            }
 1689
 861690            if (reader.TryGetBoolean(index++, out var isVirtualItem))
 1691            {
 861692                item.IsVirtualItem = isVirtualItem;
 1693            }
 1694
 861695            if (item is IHasSeries hasSeriesName)
 1696            {
 01697                if (reader.TryGetString(index, out var seriesName))
 1698                {
 01699                    hasSeriesName.SeriesName = seriesName;
 1700                }
 1701            }
 1702
 861703            index++;
 1704
 861705            if (hasEpisodeAttributes)
 1706            {
 861707                if (item is Episode episode)
 1708                {
 01709                    if (reader.TryGetString(index, out var seasonName))
 1710                    {
 01711                        episode.SeasonName = seasonName;
 1712                    }
 1713
 01714                    index++;
 01715                    if (reader.TryGetGuid(index, out var seasonId))
 1716                    {
 01717                        episode.SeasonId = seasonId;
 1718                    }
 1719                }
 1720                else
 1721                {
 861722                    index++;
 1723                }
 1724
 861725                index++;
 1726            }
 1727
 861728            var hasSeries = item as IHasSeries;
 861729            if (hasSeriesFields)
 1730            {
 861731                if (hasSeries is not null)
 1732                {
 01733                    if (reader.TryGetGuid(index, out var seriesId))
 1734                    {
 01735                        hasSeries.SeriesId = seriesId;
 1736                    }
 1737                }
 1738
 861739                index++;
 1740            }
 1741
 861742            if (HasField(query, ItemFields.PresentationUniqueKey))
 1743            {
 861744                if (reader.TryGetString(index++, out var presentationUniqueKey))
 1745                {
 61746                    item.PresentationUniqueKey = presentationUniqueKey;
 1747                }
 1748            }
 1749
 861750            if (HasField(query, ItemFields.InheritedParentalRatingValue))
 1751            {
 861752                if (reader.TryGetInt32(index++, out var parentalRating))
 1753                {
 01754                    item.InheritedParentalRatingValue = parentalRating;
 1755                }
 1756            }
 1757
 861758            if (HasField(query, ItemFields.ExternalSeriesId))
 1759            {
 861760                if (reader.TryGetString(index++, out var externalSeriesId))
 1761                {
 01762                    item.ExternalSeriesId = externalSeriesId;
 1763                }
 1764            }
 1765
 861766            if (HasField(query, ItemFields.Taglines))
 1767            {
 861768                if (reader.TryGetString(index++, out var tagLine))
 1769                {
 01770                    item.Tagline = tagLine;
 1771                }
 1772            }
 1773
 861774            if (item.ProviderIds.Count == 0 && reader.TryGetString(index, out var providerIds))
 1775            {
 01776                DeserializeProviderIds(providerIds, item);
 1777            }
 1778
 861779            index++;
 1780
 861781            if (query.DtoOptions.EnableImages)
 1782            {
 861783                if (item.ImageInfos.Length == 0 && reader.TryGetString(index, out var imageInfos))
 1784                {
 01785                    item.ImageInfos = DeserializeImages(imageInfos);
 1786                }
 1787
 861788                index++;
 1789            }
 1790
 861791            if (HasField(query, ItemFields.ProductionLocations))
 1792            {
 861793                if (reader.TryGetString(index++, out var productionLocations))
 1794                {
 01795                    item.ProductionLocations = productionLocations.Split('|', StringSplitOptions.RemoveEmptyEntries);
 1796                }
 1797            }
 1798
 861799            if (HasField(query, ItemFields.ExtraIds))
 1800            {
 861801                if (reader.TryGetString(index++, out var extraIds))
 1802                {
 01803                    item.ExtraIds = SplitToGuids(extraIds);
 1804                }
 1805            }
 1806
 861807            if (reader.TryGetInt32(index++, out var totalBitrate))
 1808            {
 01809                item.TotalBitrate = totalBitrate;
 1810            }
 1811
 861812            if (reader.TryGetString(index++, out var extraTypeString))
 1813            {
 01814                if (Enum.TryParse(extraTypeString, true, out ExtraType extraType))
 1815                {
 01816                    item.ExtraType = extraType;
 1817                }
 1818            }
 1819
 861820            if (hasArtistFields)
 1821            {
 861822                if (item is IHasArtist hasArtists && reader.TryGetString(index, out var artists))
 1823                {
 01824                    hasArtists.Artists = artists.Split('|', StringSplitOptions.RemoveEmptyEntries);
 1825                }
 1826
 861827                index++;
 1828
 861829                if (item is IHasAlbumArtist hasAlbumArtists && reader.TryGetString(index, out var albumArtists))
 1830                {
 01831                    hasAlbumArtists.AlbumArtists = albumArtists.Split('|', StringSplitOptions.RemoveEmptyEntries);
 1832                }
 1833
 861834                index++;
 1835            }
 1836
 861837            if (reader.TryGetString(index++, out var externalId))
 1838            {
 01839                item.ExternalId = externalId;
 1840            }
 1841
 861842            if (HasField(query, ItemFields.SeriesPresentationUniqueKey))
 1843            {
 861844                if (hasSeries is not null)
 1845                {
 01846                    if (reader.TryGetString(index, out var seriesPresentationUniqueKey))
 1847                    {
 01848                        hasSeries.SeriesPresentationUniqueKey = seriesPresentationUniqueKey;
 1849                    }
 1850                }
 1851
 861852                index++;
 1853            }
 1854
 861855            if (enableProgramAttributes)
 1856            {
 861857                if (item is LiveTvProgram program && reader.TryGetString(index, out var showId))
 1858                {
 01859                    program.ShowId = showId;
 1860                }
 1861
 861862                index++;
 1863            }
 1864
 861865            if (reader.TryGetGuid(index, out var ownerId))
 1866            {
 01867                item.OwnerId = ownerId;
 1868            }
 1869
 861870            return item;
 1871        }
 1872
 1873        private static Guid[] SplitToGuids(string value)
 1874        {
 01875            var ids = value.Split('|');
 1876
 01877            var result = new Guid[ids.Length];
 1878
 01879            for (var i = 0; i < result.Length; i++)
 1880            {
 01881                result[i] = new Guid(ids[i]);
 1882            }
 1883
 01884            return result;
 1885        }
 1886
 1887        /// <inheritdoc />
 1888        public List<ChapterInfo> GetChapters(BaseItem item)
 1889        {
 01890            CheckDisposed();
 1891
 01892            var chapters = new List<ChapterInfo>();
 01893            using (var connection = GetConnection(true))
 01894            using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModif
 1895            {
 01896                statement.TryBind("@ItemId", item.Id);
 1897
 01898                foreach (var row in statement.ExecuteQuery())
 1899                {
 01900                    chapters.Add(GetChapter(row, item));
 1901                }
 1902            }
 1903
 01904            return chapters;
 1905        }
 1906
 1907        /// <inheritdoc />
 1908        public ChapterInfo GetChapter(BaseItem item, int index)
 1909        {
 01910            CheckDisposed();
 1911
 01912            using (var connection = GetConnection(true))
 01913            using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModif
 1914            {
 01915                statement.TryBind("@ItemId", item.Id);
 01916                statement.TryBind("@ChapterIndex", index);
 1917
 01918                foreach (var row in statement.ExecuteQuery())
 1919                {
 01920                    return GetChapter(row, item);
 1921                }
 1922            }
 1923
 01924            return null;
 01925        }
 1926
 1927        /// <summary>
 1928        /// Gets the chapter.
 1929        /// </summary>
 1930        /// <param name="reader">The reader.</param>
 1931        /// <param name="item">The item.</param>
 1932        /// <returns>ChapterInfo.</returns>
 1933        private ChapterInfo GetChapter(SqliteDataReader reader, BaseItem item)
 1934        {
 01935            var chapter = new ChapterInfo
 01936            {
 01937                StartPositionTicks = reader.GetInt64(0)
 01938            };
 1939
 01940            if (reader.TryGetString(1, out var chapterName))
 1941            {
 01942                chapter.Name = chapterName;
 1943            }
 1944
 01945            if (reader.TryGetString(2, out var imagePath))
 1946            {
 01947                chapter.ImagePath = imagePath;
 01948                chapter.ImageTag = _imageProcessor.GetImageCacheTag(item, chapter);
 1949            }
 1950
 01951            if (reader.TryReadDateTime(3, out var imageDateModified))
 1952            {
 01953                chapter.ImageDateModified = imageDateModified;
 1954            }
 1955
 01956            return chapter;
 1957        }
 1958
 1959        /// <summary>
 1960        /// Saves the chapters.
 1961        /// </summary>
 1962        /// <param name="id">The item id.</param>
 1963        /// <param name="chapters">The chapters.</param>
 1964        public void SaveChapters(Guid id, IReadOnlyList<ChapterInfo> chapters)
 1965        {
 01966            CheckDisposed();
 1967
 01968            if (id.IsEmpty())
 1969            {
 01970                throw new ArgumentNullException(nameof(id));
 1971            }
 1972
 01973            ArgumentNullException.ThrowIfNull(chapters);
 1974
 01975            using var connection = GetConnection();
 01976            using var transaction = connection.BeginTransaction();
 1977            // First delete chapters
 01978            using var command = connection.PrepareStatement($"delete from {ChaptersTableName} where ItemId=@ItemId");
 01979            command.TryBind("@ItemId", id);
 01980            command.ExecuteNonQuery();
 1981
 01982            InsertChapters(id, chapters, connection);
 01983            transaction.Commit();
 01984        }
 1985
 1986        private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, ManagedConnection db)
 1987        {
 01988            var startIndex = 0;
 01989            var limit = 100;
 01990            var chapterIndex = 0;
 1991
 1992            const string StartInsertText = "insert into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTi
 01993            var insertText = new StringBuilder(StartInsertText, 256);
 1994
 01995            while (startIndex < chapters.Count)
 1996            {
 01997                var endIndex = Math.Min(chapters.Count, startIndex + limit);
 1998
 01999                for (var i = startIndex; i < endIndex; i++)
 2000                {
 02001                    insertText.AppendFormat(CultureInfo.InvariantCulture, "(@ItemId, @ChapterIndex{0}, @StartPositionTic
 2002                }
 2003
 02004                insertText.Length -= 1; // Remove trailing comma
 2005
 02006                using (var statement = PrepareStatement(db, insertText.ToString()))
 2007                {
 02008                    statement.TryBind("@ItemId", idBlob);
 2009
 02010                    for (var i = startIndex; i < endIndex; i++)
 2011                    {
 02012                        var index = i.ToString(CultureInfo.InvariantCulture);
 2013
 02014                        var chapter = chapters[i];
 2015
 02016                        statement.TryBind("@ChapterIndex" + index, chapterIndex);
 02017                        statement.TryBind("@StartPositionTicks" + index, chapter.StartPositionTicks);
 02018                        statement.TryBind("@Name" + index, chapter.Name);
 02019                        statement.TryBind("@ImagePath" + index, chapter.ImagePath);
 02020                        statement.TryBind("@ImageDateModified" + index, chapter.ImageDateModified);
 2021
 02022                        chapterIndex++;
 2023                    }
 2024
 02025                    statement.ExecuteNonQuery();
 02026                }
 2027
 02028                startIndex += limit;
 02029                insertText.Length = StartInsertText.Length;
 2030            }
 02031        }
 2032
 2033        private static bool EnableJoinUserData(InternalItemsQuery query)
 2034        {
 16802035            if (query.User is null)
 2036            {
 16702037                return false;
 2038            }
 2039
 102040            var sortingFields = new HashSet<ItemSortBy>(query.OrderBy.Select(i => i.OrderBy));
 2041
 102042            return sortingFields.Contains(ItemSortBy.IsFavoriteOrLiked)
 102043                    || sortingFields.Contains(ItemSortBy.IsPlayed)
 102044                    || sortingFields.Contains(ItemSortBy.IsUnplayed)
 102045                    || sortingFields.Contains(ItemSortBy.PlayCount)
 102046                    || sortingFields.Contains(ItemSortBy.DatePlayed)
 102047                    || sortingFields.Contains(ItemSortBy.SeriesDatePlayed)
 102048                    || query.IsFavoriteOrLiked.HasValue
 102049                    || query.IsFavorite.HasValue
 102050                    || query.IsResumable.HasValue
 102051                    || query.IsPlayed.HasValue
 102052                    || query.IsLiked.HasValue;
 2053        }
 2054
 2055        private bool HasField(InternalItemsQuery query, ItemFields name)
 2056        {
 2057            switch (name)
 2058            {
 2059                case ItemFields.Tags:
 4222060                    return query.DtoOptions.ContainsField(name) || HasProgramAttributes(query);
 2061                case ItemFields.CustomRating:
 2062                case ItemFields.ProductionLocations:
 2063                case ItemFields.Settings:
 2064                case ItemFields.OriginalTitle:
 2065                case ItemFields.Taglines:
 2066                case ItemFields.SortName:
 2067                case ItemFields.Studios:
 2068                case ItemFields.ExtraIds:
 2069                case ItemFields.DateCreated:
 2070                case ItemFields.Overview:
 2071                case ItemFields.Genres:
 2072                case ItemFields.DateLastMediaAdded:
 2073                case ItemFields.PresentationUniqueKey:
 2074                case ItemFields.InheritedParentalRatingValue:
 2075                case ItemFields.ExternalSeriesId:
 2076                case ItemFields.SeriesPresentationUniqueKey:
 2077                case ItemFields.DateLastRefreshed:
 2078                case ItemFields.DateLastSaved:
 76822079                    return query.DtoOptions.ContainsField(name);
 2080                case ItemFields.ServiceName:
 3362081                    return HasServiceName(query);
 2082                default:
 136122083                    return true;
 2084            }
 2085        }
 2086
 2087        private bool HasProgramAttributes(InternalItemsQuery query)
 2088        {
 7152089            if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value))
 2090            {
 02091                return false;
 2092            }
 2093
 7152094            if (query.IncludeItemTypes.Length == 0)
 2095            {
 4482096                return true;
 2097            }
 2098
 2672099            return query.IncludeItemTypes.Any(x => _programTypes.Contains(x));
 2100        }
 2101
 2102        private bool HasServiceName(InternalItemsQuery query)
 2103        {
 6532104            if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value))
 2105            {
 02106                return false;
 2107            }
 2108
 6532109            if (query.IncludeItemTypes.Length == 0)
 2110            {
 4472111                return true;
 2112            }
 2113
 2062114            return query.IncludeItemTypes.Any(x => _serviceTypes.Contains(x));
 2115        }
 2116
 2117        private bool HasStartDate(InternalItemsQuery query)
 2118        {
 6532119            if (query.ParentType is not null && _programExcludeParentTypes.Contains(query.ParentType.Value))
 2120            {
 02121                return false;
 2122            }
 2123
 6532124            if (query.IncludeItemTypes.Length == 0)
 2125            {
 4472126                return true;
 2127            }
 2128
 2062129            return query.IncludeItemTypes.Any(x => _startDateTypes.Contains(x));
 2130        }
 2131
 2132        private bool HasEpisodeAttributes(InternalItemsQuery query)
 2133        {
 9892134            if (query.IncludeItemTypes.Length == 0)
 2135            {
 6802136                return true;
 2137            }
 2138
 3092139            return query.IncludeItemTypes.Contains(BaseItemKind.Episode);
 2140        }
 2141
 2142        private bool HasTrailerTypes(InternalItemsQuery query)
 2143        {
 6532144            if (query.IncludeItemTypes.Length == 0)
 2145            {
 4472146                return true;
 2147            }
 2148
 2062149            return query.IncludeItemTypes.Contains(BaseItemKind.Trailer);
 2150        }
 2151
 2152        private bool HasArtistFields(InternalItemsQuery query)
 2153        {
 6532154            if (query.ParentType is not null && _artistExcludeParentTypes.Contains(query.ParentType.Value))
 2155            {
 02156                return false;
 2157            }
 2158
 6532159            if (query.IncludeItemTypes.Length == 0)
 2160            {
 4472161                return true;
 2162            }
 2163
 2062164            return query.IncludeItemTypes.Any(x => _artistsTypes.Contains(x));
 2165        }
 2166
 2167        private bool HasSeriesFields(InternalItemsQuery query)
 2168        {
 6532169            if (query.ParentType == BaseItemKind.PhotoAlbum)
 2170            {
 02171                return false;
 2172            }
 2173
 6532174            if (query.IncludeItemTypes.Length == 0)
 2175            {
 4472176                return true;
 2177            }
 2178
 2062179            return query.IncludeItemTypes.Any(x => _seriesTypes.Contains(x));
 2180        }
 2181
 2182        private void SetFinalColumnsToSelect(InternalItemsQuery query, List<string> columns)
 2183        {
 409922184            foreach (var field in _allItemFields)
 2185            {
 201602186                if (!HasField(query, field))
 2187                {
 2188                    switch (field)
 2189                    {
 2190                        case ItemFields.Settings:
 622191                            columns.Remove("IsLocked");
 622192                            columns.Remove("PreferredMetadataCountryCode");
 622193                            columns.Remove("PreferredMetadataLanguage");
 622194                            columns.Remove("LockedFields");
 622195                            break;
 2196                        case ItemFields.ServiceName:
 1032197                            columns.Remove("ExternalServiceId");
 1032198                            break;
 2199                        case ItemFields.SortName:
 622200                            columns.Remove("ForcedSortName");
 622201                            break;
 2202                        case ItemFields.Taglines:
 622203                            columns.Remove("Tagline");
 622204                            break;
 2205                        case ItemFields.Tags:
 612206                            columns.Remove("Tags");
 612207                            break;
 2208                        case ItemFields.IsHD:
 2209                            // do nothing
 2210                            break;
 2211                        default:
 9302212                            columns.Remove(field.ToString());
 2213                            break;
 2214                    }
 2215                }
 2216            }
 2217
 3362218            if (!HasProgramAttributes(query))
 2219            {
 1032220                columns.Remove("IsMovie");
 1032221                columns.Remove("IsSeries");
 1032222                columns.Remove("EpisodeTitle");
 1032223                columns.Remove("IsRepeat");
 1032224                columns.Remove("ShowId");
 2225            }
 2226
 3362227            if (!HasEpisodeAttributes(query))
 2228            {
 1032229                columns.Remove("SeasonName");
 1032230                columns.Remove("SeasonId");
 2231            }
 2232
 3362233            if (!HasStartDate(query))
 2234            {
 1032235                columns.Remove("StartDate");
 2236            }
 2237
 3362238            if (!HasTrailerTypes(query))
 2239            {
 1032240                columns.Remove("TrailerTypes");
 2241            }
 2242
 3362243            if (!HasArtistFields(query))
 2244            {
 992245                columns.Remove("AlbumArtists");
 992246                columns.Remove("Artists");
 2247            }
 2248
 3362249            if (!HasSeriesFields(query))
 2250            {
 1032251                columns.Remove("SeriesId");
 2252            }
 2253
 3362254            if (!HasEpisodeAttributes(query))
 2255            {
 1032256                columns.Remove("SeasonName");
 1032257                columns.Remove("SeasonId");
 2258            }
 2259
 3362260            if (!query.DtoOptions.EnableImages)
 2261            {
 02262                columns.Remove("Images");
 2263            }
 2264
 3362265            if (EnableJoinUserData(query))
 2266            {
 12267                columns.Add("UserDatas.UserId");
 12268                columns.Add("UserDatas.lastPlayedDate");
 12269                columns.Add("UserDatas.playbackPositionTicks");
 12270                columns.Add("UserDatas.playcount");
 12271                columns.Add("UserDatas.isFavorite");
 12272                columns.Add("UserDatas.played");
 12273                columns.Add("UserDatas.rating");
 2274            }
 2275
 3362276            if (query.SimilarTo is not null)
 2277            {
 02278                var item = query.SimilarTo;
 2279
 02280                var builder = new StringBuilder();
 02281                builder.Append('(');
 2282
 02283                if (item.InheritedParentalRatingValue == 0)
 2284                {
 02285                    builder.Append("((InheritedParentalRatingValue=0) * 10)");
 2286                }
 2287                else
 2288                {
 02289                    builder.Append(
 02290                        @"(SELECT CASE WHEN COALESCE(InheritedParentalRatingValue, 0)=0
 02291                                THEN 0
 02292                                ELSE 10.0 / (1.0 + ABS(InheritedParentalRatingValue - @InheritedParentalRatingValue))
 02293                                END)");
 2294                }
 2295
 02296                if (item.ProductionYear.HasValue)
 2297                {
 02298                    builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 10 Then 
 02299                    builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 5
 2300                }
 2301
 2302                // genres, tags, studios, person, year?
 02303                builder.Append("+ (Select count(1) * 10 from ItemValues where ItemId=Guid and CleanValue in (select Clea
 02304                builder.Append("+ (Select count(1) * 10 from People where ItemId=Guid and Name in (select Name from Peop
 2305
 02306                if (item is MusicArtist)
 2307                {
 2308                    // Match albums where the artist is AlbumArtist against other albums.
 2309                    // It is assumed that similar albums => similar artists.
 02310                    builder.Append(
 02311                        @"+ (WITH artistValues AS (
 02312                              SELECT DISTINCT albumValues.CleanValue
 02313                              FROM ItemValues albumValues
 02314                              INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId
 02315                              INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND
 02316                            ), similarArtist AS (
 02317                              SELECT albumValues.ItemId
 02318                              FROM ItemValues albumValues
 02319                              INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId
 02320                              INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND
 02321                            ) SELECT COUNT(DISTINCT(CleanValue)) * 10 FROM ItemValues WHERE ItemId IN (SELECT ItemId FRO
 2322                }
 2323
 02324                builder.Append(") as SimilarityScore");
 2325
 02326                columns.Add(builder.ToString());
 2327
 02328                query.ExcludeItemIds = [.. query.ExcludeItemIds, item.Id, .. item.ExtraIds];
 02329                query.ExcludeProviderIds = item.ProviderIds;
 2330            }
 2331
 3362332            if (!string.IsNullOrEmpty(query.SearchTerm))
 2333            {
 02334                var builder = new StringBuilder();
 02335                builder.Append('(');
 2336
 02337                builder.Append("((CleanName like @SearchTermStartsWith or (OriginalTitle not null and OriginalTitle like
 02338                builder.Append("+ ((CleanName = @SearchTermStartsWith COLLATE NOCASE or (OriginalTitle not null and Orig
 2339
 02340                if (query.SearchTerm.Length > 1)
 2341                {
 02342                    builder.Append("+ ((CleanName like @SearchTermContains or (OriginalTitle not null and OriginalTitle 
 02343                    builder.Append("+ (SELECT COUNT(1) * 1 from ItemValues where ItemId=Guid and CleanValue like @Search
 02344                    builder.Append("+ (SELECT COUNT(1) * 2 from ItemValues where ItemId=Guid and CleanValue like @Search
 02345                    builder.Append("+ (SELECT COUNT(1) * 10 from ItemValues where ItemId=Guid and CleanValue like @Searc
 2346                }
 2347
 02348                builder.Append(") as SearchScore");
 2349
 02350                columns.Add(builder.ToString());
 2351            }
 3362352        }
 2353
 2354        private void BindSearchParams(InternalItemsQuery query, SqliteCommand statement)
 2355        {
 3362356            var searchTerm = query.SearchTerm;
 2357
 3362358            if (string.IsNullOrEmpty(searchTerm))
 2359            {
 3362360                return;
 2361            }
 2362
 02363            searchTerm = FixUnicodeChars(searchTerm);
 02364            searchTerm = GetCleanValue(searchTerm);
 2365
 02366            var commandText = statement.CommandText;
 02367            if (commandText.Contains("@SearchTermStartsWith", StringComparison.OrdinalIgnoreCase))
 2368            {
 02369                statement.TryBind("@SearchTermStartsWith", searchTerm + "%");
 2370            }
 2371
 02372            if (commandText.Contains("@SearchTermContains", StringComparison.OrdinalIgnoreCase))
 2373            {
 02374                statement.TryBind("@SearchTermContains", "%" + searchTerm + "%");
 2375            }
 2376
 02377            if (commandText.Contains("@SearchTermEquals", StringComparison.OrdinalIgnoreCase))
 2378            {
 02379                statement.TryBind("@SearchTermEquals", searchTerm);
 2380            }
 02381        }
 2382
 2383        private void BindSimilarParams(InternalItemsQuery query, SqliteCommand statement)
 2384        {
 3362385            var item = query.SimilarTo;
 2386
 3362387            if (item is null)
 2388            {
 3362389                return;
 2390            }
 2391
 02392            var commandText = statement.CommandText;
 2393
 02394            if (commandText.Contains("@ItemOfficialRating", StringComparison.OrdinalIgnoreCase))
 2395            {
 02396                statement.TryBind("@ItemOfficialRating", item.OfficialRating);
 2397            }
 2398
 02399            if (commandText.Contains("@ItemProductionYear", StringComparison.OrdinalIgnoreCase))
 2400            {
 02401                statement.TryBind("@ItemProductionYear", item.ProductionYear ?? 0);
 2402            }
 2403
 02404            if (commandText.Contains("@SimilarItemId", StringComparison.OrdinalIgnoreCase))
 2405            {
 02406                statement.TryBind("@SimilarItemId", item.Id);
 2407            }
 2408
 02409            if (commandText.Contains("@InheritedParentalRatingValue", StringComparison.OrdinalIgnoreCase))
 2410            {
 02411                statement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue);
 2412            }
 02413        }
 2414
 2415        private string GetJoinUserDataText(InternalItemsQuery query)
 2416        {
 3362417            if (!EnableJoinUserData(query))
 2418            {
 3352419                return string.Empty;
 2420            }
 2421
 12422            return " left join UserDatas on UserDataKey=UserDatas.Key And (UserId=@UserId)";
 2423        }
 2424
 2425        private string GetGroupBy(InternalItemsQuery query)
 2426        {
 3362427            var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(query);
 3362428            if (enableGroupByPresentationUniqueKey && query.GroupBySeriesPresentationUniqueKey)
 2429            {
 02430                return " Group by PresentationUniqueKey, SeriesPresentationUniqueKey";
 2431            }
 2432
 3362433            if (enableGroupByPresentationUniqueKey)
 2434            {
 12435                return " Group by PresentationUniqueKey";
 2436            }
 2437
 3352438            if (query.GroupBySeriesPresentationUniqueKey)
 2439            {
 02440                return " Group by SeriesPresentationUniqueKey";
 2441            }
 2442
 3352443            return string.Empty;
 2444        }
 2445
 2446        public int GetCount(InternalItemsQuery query)
 2447        {
 02448            ArgumentNullException.ThrowIfNull(query);
 2449
 02450            CheckDisposed();
 2451
 2452            // Hack for right now since we currently don't support filtering out these duplicates within a query
 02453            if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
 2454            {
 02455                query.Limit = query.Limit.Value + 4;
 2456            }
 2457
 02458            var columns = new List<string> { "count(distinct PresentationUniqueKey)" };
 02459            SetFinalColumnsToSelect(query, columns);
 02460            var commandTextBuilder = new StringBuilder("select ", 256)
 02461                .AppendJoin(',', columns)
 02462                .Append(FromText)
 02463                .Append(GetJoinUserDataText(query));
 2464
 02465            var whereClauses = GetWhereClauses(query, null);
 02466            if (whereClauses.Count != 0)
 2467            {
 02468                commandTextBuilder.Append(" where ")
 02469                    .AppendJoin(" AND ", whereClauses);
 2470            }
 2471
 02472            var commandText = commandTextBuilder.ToString();
 2473
 02474            using (new QueryTimeLogger(Logger, commandText))
 02475            using (var connection = GetConnection(true))
 02476            using (var statement = PrepareStatement(connection, commandText))
 2477            {
 02478                if (EnableJoinUserData(query))
 2479                {
 02480                    statement.TryBind("@UserId", query.User.InternalId);
 2481                }
 2482
 02483                BindSimilarParams(query, statement);
 02484                BindSearchParams(query, statement);
 2485
 2486                // Running this again will bind the params
 02487                GetWhereClauses(query, statement);
 2488
 02489                return statement.SelectScalarInt();
 2490            }
 02491        }
 2492
 2493        public List<BaseItem> GetItemList(InternalItemsQuery query)
 2494        {
 3172495            ArgumentNullException.ThrowIfNull(query);
 2496
 3172497            CheckDisposed();
 2498
 2499            // Hack for right now since we currently don't support filtering out these duplicates within a query
 3172500            if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
 2501            {
 02502                query.Limit = query.Limit.Value + 4;
 2503            }
 2504
 3172505            var columns = _retrieveItemColumns.ToList();
 3172506            SetFinalColumnsToSelect(query, columns);
 3172507            var commandTextBuilder = new StringBuilder("select ", 1024)
 3172508                .AppendJoin(',', columns)
 3172509                .Append(FromText)
 3172510                .Append(GetJoinUserDataText(query));
 2511
 3172512            var whereClauses = GetWhereClauses(query, null);
 2513
 3172514            if (whereClauses.Count != 0)
 2515            {
 3172516                commandTextBuilder.Append(" where ")
 3172517                    .AppendJoin(" AND ", whereClauses);
 2518            }
 2519
 3172520            commandTextBuilder.Append(GetGroupBy(query))
 3172521                .Append(GetOrderByText(query));
 2522
 3172523            if (query.Limit.HasValue || query.StartIndex.HasValue)
 2524            {
 1032525                var offset = query.StartIndex ?? 0;
 2526
 1032527                if (query.Limit.HasValue || offset > 0)
 2528                {
 1032529                    commandTextBuilder.Append(" LIMIT ")
 1032530                        .Append(query.Limit ?? int.MaxValue);
 2531                }
 2532
 1032533                if (offset > 0)
 2534                {
 02535                    commandTextBuilder.Append(" OFFSET ")
 02536                        .Append(offset);
 2537                }
 2538            }
 2539
 3172540            var commandText = commandTextBuilder.ToString();
 3172541            var items = new List<BaseItem>();
 3172542            using (new QueryTimeLogger(Logger, commandText))
 3172543            using (var connection = GetConnection(true))
 3172544            using (var statement = PrepareStatement(connection, commandText))
 2545            {
 3172546                if (EnableJoinUserData(query))
 2547                {
 12548                    statement.TryBind("@UserId", query.User.InternalId);
 2549                }
 2550
 3172551                BindSimilarParams(query, statement);
 3172552                BindSearchParams(query, statement);
 2553
 2554                // Running this again will bind the params
 3172555                GetWhereClauses(query, statement);
 2556
 3172557                var hasEpisodeAttributes = HasEpisodeAttributes(query);
 3172558                var hasServiceName = HasServiceName(query);
 3172559                var hasProgramAttributes = HasProgramAttributes(query);
 3172560                var hasStartDate = HasStartDate(query);
 3172561                var hasTrailerTypes = HasTrailerTypes(query);
 3172562                var hasArtistFields = HasArtistFields(query);
 3172563                var hasSeriesFields = HasSeriesFields(query);
 2564
 8062565                foreach (var row in statement.ExecuteQuery())
 2566                {
 862567                    var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartD
 862568                    if (item is not null)
 2569                    {
 862570                        items.Add(item);
 2571                    }
 2572                }
 2573            }
 2574
 2575            // Hack for right now since we currently don't support filtering out these duplicates within a query
 3172576            if (query.EnableGroupByMetadataKey)
 2577            {
 02578                var limit = query.Limit ?? int.MaxValue;
 02579                limit -= 4;
 02580                var newList = new List<BaseItem>();
 2581
 02582                foreach (var item in items)
 2583                {
 02584                    AddItem(newList, item);
 2585
 02586                    if (newList.Count >= limit)
 2587                    {
 02588                        break;
 2589                    }
 2590                }
 2591
 02592                items = newList;
 2593            }
 2594
 3172595            return items;
 2596        }
 2597
 2598        private string FixUnicodeChars(string buffer)
 2599        {
 02600            buffer = buffer.Replace('\u2013', '-'); // en dash
 02601            buffer = buffer.Replace('\u2014', '-'); // em dash
 02602            buffer = buffer.Replace('\u2015', '-'); // horizontal bar
 02603            buffer = buffer.Replace('\u2017', '_'); // double low line
 02604            buffer = buffer.Replace('\u2018', '\''); // left single quotation mark
 02605            buffer = buffer.Replace('\u2019', '\''); // right single quotation mark
 02606            buffer = buffer.Replace('\u201a', ','); // single low-9 quotation mark
 02607            buffer = buffer.Replace('\u201b', '\''); // single high-reversed-9 quotation mark
 02608            buffer = buffer.Replace('\u201c', '\"'); // left double quotation mark
 02609            buffer = buffer.Replace('\u201d', '\"'); // right double quotation mark
 02610            buffer = buffer.Replace('\u201e', '\"'); // double low-9 quotation mark
 02611            buffer = buffer.Replace("\u2026", "...", StringComparison.Ordinal); // horizontal ellipsis
 02612            buffer = buffer.Replace('\u2032', '\''); // prime
 02613            buffer = buffer.Replace('\u2033', '\"'); // double prime
 02614            buffer = buffer.Replace('\u0060', '\''); // grave accent
 02615            return buffer.Replace('\u00B4', '\''); // acute accent
 2616        }
 2617
 2618        private void AddItem(List<BaseItem> items, BaseItem newItem)
 2619        {
 02620            for (var i = 0; i < items.Count; i++)
 2621            {
 02622                var item = items[i];
 2623
 02624                foreach (var providerId in newItem.ProviderIds)
 2625                {
 02626                    if (string.Equals(providerId.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.Ordinal)
 2627                    {
 2628                        continue;
 2629                    }
 2630
 02631                    if (string.Equals(item.GetProviderId(providerId.Key), providerId.Value, StringComparison.Ordinal))
 2632                    {
 02633                        if (newItem.SourceType == SourceType.Library)
 2634                        {
 02635                            items[i] = newItem;
 2636                        }
 2637
 02638                        return;
 2639                    }
 2640                }
 2641            }
 2642
 02643            items.Add(newItem);
 02644        }
 2645
 2646        public QueryResult<BaseItem> GetItems(InternalItemsQuery query)
 2647        {
 12648            ArgumentNullException.ThrowIfNull(query);
 2649
 12650            CheckDisposed();
 2651
 12652            if (!query.EnableTotalRecordCount || (!query.Limit.HasValue && (query.StartIndex ?? 0) == 0))
 2653            {
 12654                var returnList = GetItemList(query);
 12655                return new QueryResult<BaseItem>(
 12656                    query.StartIndex,
 12657                    returnList.Count,
 12658                    returnList);
 2659            }
 2660
 2661            // Hack for right now since we currently don't support filtering out these duplicates within a query
 02662            if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
 2663            {
 02664                query.Limit = query.Limit.Value + 4;
 2665            }
 2666
 02667            var columns = _retrieveItemColumns.ToList();
 02668            SetFinalColumnsToSelect(query, columns);
 02669            var commandTextBuilder = new StringBuilder("select ", 512)
 02670                .AppendJoin(',', columns)
 02671                .Append(FromText)
 02672                .Append(GetJoinUserDataText(query));
 2673
 02674            var whereClauses = GetWhereClauses(query, null);
 2675
 02676            var whereText = whereClauses.Count == 0 ?
 02677                string.Empty :
 02678                string.Join(" AND ", whereClauses);
 2679
 02680            if (!string.IsNullOrEmpty(whereText))
 2681            {
 02682                commandTextBuilder.Append(" where ")
 02683                    .Append(whereText);
 2684            }
 2685
 02686            commandTextBuilder.Append(GetGroupBy(query))
 02687                .Append(GetOrderByText(query));
 2688
 02689            if (query.Limit.HasValue || query.StartIndex.HasValue)
 2690            {
 02691                var offset = query.StartIndex ?? 0;
 2692
 02693                if (query.Limit.HasValue || offset > 0)
 2694                {
 02695                    commandTextBuilder.Append(" LIMIT ")
 02696                        .Append(query.Limit ?? int.MaxValue);
 2697                }
 2698
 02699                if (offset > 0)
 2700                {
 02701                    commandTextBuilder.Append(" OFFSET ")
 02702                        .Append(offset);
 2703                }
 2704            }
 2705
 02706            var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
 2707
 02708            var itemQuery = string.Empty;
 02709            var totalRecordCountQuery = string.Empty;
 02710            if (!isReturningZeroItems)
 2711            {
 02712                itemQuery = commandTextBuilder.ToString();
 2713            }
 2714
 02715            if (query.EnableTotalRecordCount)
 2716            {
 02717                commandTextBuilder.Clear();
 2718
 02719                commandTextBuilder.Append(" select ");
 2720
 2721                List<string> columnsToSelect;
 02722                if (EnableGroupByPresentationUniqueKey(query))
 2723                {
 02724                    columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" };
 2725                }
 02726                else if (query.GroupBySeriesPresentationUniqueKey)
 2727                {
 02728                    columnsToSelect = new List<string> { "count (distinct SeriesPresentationUniqueKey)" };
 2729                }
 2730                else
 2731                {
 02732                    columnsToSelect = new List<string> { "count (guid)" };
 2733                }
 2734
 02735                SetFinalColumnsToSelect(query, columnsToSelect);
 2736
 02737                commandTextBuilder.AppendJoin(',', columnsToSelect)
 02738                    .Append(FromText)
 02739                    .Append(GetJoinUserDataText(query));
 02740                if (!string.IsNullOrEmpty(whereText))
 2741                {
 02742                    commandTextBuilder.Append(" where ")
 02743                        .Append(whereText);
 2744                }
 2745
 02746                totalRecordCountQuery = commandTextBuilder.ToString();
 2747            }
 2748
 02749            var list = new List<BaseItem>();
 02750            var result = new QueryResult<BaseItem>();
 02751            using var connection = GetConnection(true);
 02752            using var transaction = connection.BeginTransaction();
 02753            if (!isReturningZeroItems)
 2754            {
 02755                using (new QueryTimeLogger(Logger, itemQuery, "GetItems.ItemQuery"))
 02756                using (var statement = PrepareStatement(connection, itemQuery))
 2757                {
 02758                    if (EnableJoinUserData(query))
 2759                    {
 02760                        statement.TryBind("@UserId", query.User.InternalId);
 2761                    }
 2762
 02763                    BindSimilarParams(query, statement);
 02764                    BindSearchParams(query, statement);
 2765
 2766                    // Running this again will bind the params
 02767                    GetWhereClauses(query, statement);
 2768
 02769                    var hasEpisodeAttributes = HasEpisodeAttributes(query);
 02770                    var hasServiceName = HasServiceName(query);
 02771                    var hasProgramAttributes = HasProgramAttributes(query);
 02772                    var hasStartDate = HasStartDate(query);
 02773                    var hasTrailerTypes = HasTrailerTypes(query);
 02774                    var hasArtistFields = HasArtistFields(query);
 02775                    var hasSeriesFields = HasSeriesFields(query);
 2776
 02777                    foreach (var row in statement.ExecuteQuery())
 2778                    {
 02779                        var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasSt
 02780                        if (item is not null)
 2781                        {
 02782                            list.Add(item);
 2783                        }
 2784                    }
 2785                }
 2786            }
 2787
 02788            if (query.EnableTotalRecordCount)
 2789            {
 02790                using (new QueryTimeLogger(Logger, totalRecordCountQuery, "GetItems.TotalRecordCount"))
 02791                using (var statement = PrepareStatement(connection, totalRecordCountQuery))
 2792                {
 02793                    if (EnableJoinUserData(query))
 2794                    {
 02795                        statement.TryBind("@UserId", query.User.InternalId);
 2796                    }
 2797
 02798                    BindSimilarParams(query, statement);
 02799                    BindSearchParams(query, statement);
 2800
 2801                    // Running this again will bind the params
 02802                    GetWhereClauses(query, statement);
 2803
 02804                    result.TotalRecordCount = statement.SelectScalarInt();
 02805                }
 2806            }
 2807
 02808            transaction.Commit();
 2809
 02810            result.StartIndex = query.StartIndex ?? 0;
 02811            result.Items = list;
 02812            return result;
 02813        }
 2814
 2815        private string GetOrderByText(InternalItemsQuery query)
 2816        {
 3362817            var orderBy = query.OrderBy;
 3362818            bool hasSimilar = query.SimilarTo is not null;
 3362819            bool hasSearch = !string.IsNullOrEmpty(query.SearchTerm);
 2820
 3362821            if (hasSimilar || hasSearch)
 2822            {
 02823                List<(ItemSortBy, SortOrder)> prepend = new List<(ItemSortBy, SortOrder)>(4);
 02824                if (hasSearch)
 2825                {
 02826                    prepend.Add((ItemSortBy.SearchScore, SortOrder.Descending));
 02827                    prepend.Add((ItemSortBy.SortName, SortOrder.Ascending));
 2828                }
 2829
 02830                if (hasSimilar)
 2831                {
 02832                    prepend.Add((ItemSortBy.SimilarityScore, SortOrder.Descending));
 02833                    prepend.Add((ItemSortBy.Random, SortOrder.Ascending));
 2834                }
 2835
 02836                orderBy = query.OrderBy = [.. prepend, .. orderBy];
 2837            }
 3362838            else if (orderBy.Count == 0)
 2839            {
 2322840                return string.Empty;
 2841            }
 2842
 1042843            return " ORDER BY " + string.Join(',', orderBy.Select(i =>
 1042844            {
 1042845                var sortBy = MapOrderByField(i.OrderBy, query);
 1042846                var sortOrder = i.SortOrder == SortOrder.Ascending ? "ASC" : "DESC";
 1042847                return sortBy + " " + sortOrder;
 1042848            }));
 2849        }
 2850
 2851        private string MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query)
 2852        {
 1462853            return sortBy switch
 1462854            {
 02855                ItemSortBy.AirTime => "SortName", // TODO
 02856                ItemSortBy.Runtime => "RuntimeTicks",
 612857                ItemSortBy.Random => "RANDOM()",
 12858                ItemSortBy.DatePlayed when query.GroupBySeriesPresentationUniqueKey => "MAX(LastPlayedDate)",
 12859                ItemSortBy.DatePlayed => "LastPlayedDate",
 02860                ItemSortBy.PlayCount => "PlayCount",
 02861                ItemSortBy.IsFavoriteOrLiked => "(Select Case When IsFavorite is null Then 0 Else IsFavorite End )",
 422862                ItemSortBy.IsFolder => "IsFolder",
 02863                ItemSortBy.IsPlayed => "played",
 02864                ItemSortBy.IsUnplayed => "played",
 02865                ItemSortBy.DateLastContentAdded => "DateLastMediaAdded",
 02866                ItemSortBy.Artist => "(select CleanValue from ItemValues where ItemId=Guid and Type=0 LIMIT 1)",
 02867                ItemSortBy.AlbumArtist => "(select CleanValue from ItemValues where ItemId=Guid and Type=1 LIMIT 1)",
 02868                ItemSortBy.OfficialRating => "InheritedParentalRatingValue",
 02869                ItemSortBy.Studio => "(select CleanValue from ItemValues where ItemId=Guid and Type=3 LIMIT 1)",
 02870                ItemSortBy.SeriesDatePlayed => "(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText
 02871                ItemSortBy.SeriesSortName => "SeriesName",
 02872                ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder",
 02873                ItemSortBy.Album => "Album",
 02874                ItemSortBy.DateCreated => "DateCreated",
 02875                ItemSortBy.PremiereDate => "PremiereDate",
 02876                ItemSortBy.StartDate => "StartDate",
 02877                ItemSortBy.Name => "Name",
 02878                ItemSortBy.CommunityRating => "CommunityRating",
 02879                ItemSortBy.ProductionYear => "ProductionYear",
 02880                ItemSortBy.CriticRating => "CriticRating",
 02881                ItemSortBy.VideoBitRate => "VideoBitRate",
 02882                ItemSortBy.ParentIndexNumber => "ParentIndexNumber",
 02883                ItemSortBy.IndexNumber => "IndexNumber",
 02884                ItemSortBy.SimilarityScore => "SimilarityScore",
 02885                ItemSortBy.SearchScore => "SearchScore",
 422886                _ => "SortName"
 1462887            };
 2888        }
 2889
 2890        public List<Guid> GetItemIdsList(InternalItemsQuery query)
 2891        {
 192892            ArgumentNullException.ThrowIfNull(query);
 2893
 192894            CheckDisposed();
 2895
 192896            var columns = new List<string> { "guid" };
 192897            SetFinalColumnsToSelect(query, columns);
 192898            var commandTextBuilder = new StringBuilder("select ", 256)
 192899                .AppendJoin(',', columns)
 192900                .Append(FromText)
 192901                .Append(GetJoinUserDataText(query));
 2902
 192903            var whereClauses = GetWhereClauses(query, null);
 192904            if (whereClauses.Count != 0)
 2905            {
 192906                commandTextBuilder.Append(" where ")
 192907                    .AppendJoin(" AND ", whereClauses);
 2908            }
 2909
 192910            commandTextBuilder.Append(GetGroupBy(query))
 192911                .Append(GetOrderByText(query));
 2912
 192913            if (query.Limit.HasValue || query.StartIndex.HasValue)
 2914            {
 02915                var offset = query.StartIndex ?? 0;
 2916
 02917                if (query.Limit.HasValue || offset > 0)
 2918                {
 02919                    commandTextBuilder.Append(" LIMIT ")
 02920                        .Append(query.Limit ?? int.MaxValue);
 2921                }
 2922
 02923                if (offset > 0)
 2924                {
 02925                    commandTextBuilder.Append(" OFFSET ")
 02926                        .Append(offset);
 2927                }
 2928            }
 2929
 192930            var commandText = commandTextBuilder.ToString();
 192931            var list = new List<Guid>();
 192932            using (new QueryTimeLogger(Logger, commandText))
 192933            using (var connection = GetConnection(true))
 192934            using (var statement = PrepareStatement(connection, commandText))
 2935            {
 192936                if (EnableJoinUserData(query))
 2937                {
 02938                    statement.TryBind("@UserId", query.User.InternalId);
 2939                }
 2940
 192941                BindSimilarParams(query, statement);
 192942                BindSearchParams(query, statement);
 2943
 2944                // Running this again will bind the params
 192945                GetWhereClauses(query, statement);
 2946
 382947                foreach (var row in statement.ExecuteQuery())
 2948                {
 02949                    list.Add(row.GetGuid(0));
 2950                }
 2951            }
 2952
 192953            return list;
 2954        }
 2955
 2956        private bool IsAlphaNumeric(string str)
 2957        {
 02958            if (string.IsNullOrWhiteSpace(str))
 2959            {
 02960                return false;
 2961            }
 2962
 02963            for (int i = 0; i < str.Length; i++)
 2964            {
 02965                if (!char.IsLetter(str[i]) && !char.IsNumber(str[i]))
 2966                {
 02967                    return false;
 2968                }
 2969            }
 2970
 02971            return true;
 2972        }
 2973
 2974        private bool IsValidPersonType(string value)
 2975        {
 02976            return IsAlphaNumeric(value);
 2977        }
 2978
 2979#nullable enable
 2980        private List<string> GetWhereClauses(InternalItemsQuery query, SqliteCommand? statement)
 2981        {
 6722982            if (query.IsResumable ?? false)
 2983            {
 22984                query.IsVirtualItem = false;
 2985            }
 2986
 6722987            var minWidth = query.MinWidth;
 6722988            var maxWidth = query.MaxWidth;
 2989
 6722990            if (query.IsHD.HasValue)
 2991            {
 2992                const int Threshold = 1200;
 02993                if (query.IsHD.Value)
 2994                {
 02995                    minWidth = Threshold;
 2996                }
 2997                else
 2998                {
 02999                    maxWidth = Threshold - 1;
 3000                }
 3001            }
 3002
 6723003            if (query.Is4K.HasValue)
 3004            {
 3005                const int Threshold = 3800;
 03006                if (query.Is4K.Value)
 3007                {
 03008                    minWidth = Threshold;
 3009                }
 3010                else
 3011                {
 03012                    maxWidth = Threshold - 1;
 3013                }
 3014            }
 3015
 6723016            var whereClauses = new List<string>();
 3017
 6723018            if (minWidth.HasValue)
 3019            {
 03020                whereClauses.Add("Width>=@MinWidth");
 03021                statement?.TryBind("@MinWidth", minWidth);
 3022            }
 3023
 6723024            if (query.MinHeight.HasValue)
 3025            {
 03026                whereClauses.Add("Height>=@MinHeight");
 03027                statement?.TryBind("@MinHeight", query.MinHeight);
 3028            }
 3029
 6723030            if (maxWidth.HasValue)
 3031            {
 03032                whereClauses.Add("Width<=@MaxWidth");
 03033                statement?.TryBind("@MaxWidth", maxWidth);
 3034            }
 3035
 6723036            if (query.MaxHeight.HasValue)
 3037            {
 03038                whereClauses.Add("Height<=@MaxHeight");
 03039                statement?.TryBind("@MaxHeight", query.MaxHeight);
 3040            }
 3041
 6723042            if (query.IsLocked.HasValue)
 3043            {
 763044                whereClauses.Add("IsLocked=@IsLocked");
 763045                statement?.TryBind("@IsLocked", query.IsLocked);
 3046            }
 3047
 6723048            var tags = query.Tags.ToList();
 6723049            var excludeTags = query.ExcludeTags.ToList();
 3050
 6723051            if (query.IsMovie == true)
 3052            {
 03053                if (query.IncludeItemTypes.Length == 0
 03054                    || query.IncludeItemTypes.Contains(BaseItemKind.Movie)
 03055                    || query.IncludeItemTypes.Contains(BaseItemKind.Trailer))
 3056                {
 03057                    whereClauses.Add("(IsMovie is null OR IsMovie=@IsMovie)");
 3058                }
 3059                else
 3060                {
 03061                    whereClauses.Add("IsMovie=@IsMovie");
 3062                }
 3063
 03064                statement?.TryBind("@IsMovie", true);
 3065            }
 6723066            else if (query.IsMovie.HasValue)
 3067            {
 03068                whereClauses.Add("IsMovie=@IsMovie");
 03069                statement?.TryBind("@IsMovie", query.IsMovie);
 3070            }
 3071
 6723072            if (query.IsSeries.HasValue)
 3073            {
 03074                whereClauses.Add("IsSeries=@IsSeries");
 03075                statement?.TryBind("@IsSeries", query.IsSeries);
 3076            }
 3077
 6723078            if (query.IsSports.HasValue)
 3079            {
 03080                if (query.IsSports.Value)
 3081                {
 03082                    tags.Add("Sports");
 3083                }
 3084                else
 3085                {
 03086                    excludeTags.Add("Sports");
 3087                }
 3088            }
 3089
 6723090            if (query.IsNews.HasValue)
 3091            {
 03092                if (query.IsNews.Value)
 3093                {
 03094                    tags.Add("News");
 3095                }
 3096                else
 3097                {
 03098                    excludeTags.Add("News");
 3099                }
 3100            }
 3101
 6723102            if (query.IsKids.HasValue)
 3103            {
 03104                if (query.IsKids.Value)
 3105                {
 03106                    tags.Add("Kids");
 3107                }
 3108                else
 3109                {
 03110                    excludeTags.Add("Kids");
 3111                }
 3112            }
 3113
 6723114            if (query.SimilarTo is not null && query.MinSimilarityScore > 0)
 3115            {
 03116                whereClauses.Add("SimilarityScore > " + (query.MinSimilarityScore - 1).ToString(CultureInfo.InvariantCul
 3117            }
 3118
 6723119            if (!string.IsNullOrEmpty(query.SearchTerm))
 3120            {
 03121                whereClauses.Add("SearchScore > 0");
 3122            }
 3123
 6723124            if (query.IsFolder.HasValue)
 3125            {
 03126                whereClauses.Add("IsFolder=@IsFolder");
 03127                statement?.TryBind("@IsFolder", query.IsFolder);
 3128            }
 3129
 6723130            var includeTypes = query.IncludeItemTypes;
 3131            // Only specify excluded types if no included types are specified
 6723132            if (query.IncludeItemTypes.Length == 0)
 3133            {
 4663134                var excludeTypes = query.ExcludeItemTypes;
 4663135                if (excludeTypes.Length == 1)
 3136                {
 03137                    if (_baseItemKindNames.TryGetValue(excludeTypes[0], out var excludeTypeName))
 3138                    {
 03139                        whereClauses.Add("type<>@type");
 03140                        statement?.TryBind("@type", excludeTypeName);
 3141                    }
 3142                    else
 3143                    {
 03144                        Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", excludeTypes[0]);
 3145                    }
 3146                }
 4663147                else if (excludeTypes.Length > 1)
 3148                {
 03149                    var whereBuilder = new StringBuilder("type not in (");
 03150                    foreach (var excludeType in excludeTypes)
 3151                    {
 03152                        if (_baseItemKindNames.TryGetValue(excludeType, out var baseItemKindName))
 3153                        {
 03154                            whereBuilder
 03155                                .Append('\'')
 03156                                .Append(baseItemKindName)
 03157                                .Append("',");
 3158                        }
 3159                        else
 3160                        {
 03161                            Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", excludeType);
 3162                        }
 3163                    }
 3164
 3165                    // Remove trailing comma.
 03166                    whereBuilder.Length--;
 03167                    whereBuilder.Append(')');
 03168                    whereClauses.Add(whereBuilder.ToString());
 3169                }
 3170            }
 2063171            else if (includeTypes.Length == 1)
 3172            {
 843173                if (_baseItemKindNames.TryGetValue(includeTypes[0], out var includeTypeName))
 3174                {
 843175                    whereClauses.Add("type=@type");
 843176                    statement?.TryBind("@type", includeTypeName);
 3177                }
 3178                else
 3179                {
 03180                    Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", includeTypes[0]);
 3181                }
 3182            }
 1223183            else if (includeTypes.Length > 1)
 3184            {
 1223185                var whereBuilder = new StringBuilder("type in (");
 7803186                foreach (var includeType in includeTypes)
 3187                {
 2683188                    if (_baseItemKindNames.TryGetValue(includeType, out var baseItemKindName))
 3189                    {
 2683190                        whereBuilder
 2683191                            .Append('\'')
 2683192                            .Append(baseItemKindName)
 2683193                            .Append("',");
 3194                    }
 3195                    else
 3196                    {
 03197                        Logger.LogWarning("Undefined BaseItemKind to Type mapping: {BaseItemKind}", includeType);
 3198                    }
 3199                }
 3200
 3201                // Remove trailing comma.
 1223202                whereBuilder.Length--;
 1223203                whereBuilder.Append(')');
 1223204                whereClauses.Add(whereBuilder.ToString());
 3205            }
 3206
 6723207            if (query.ChannelIds.Count == 1)
 3208            {
 03209                whereClauses.Add("ChannelId=@ChannelId");
 03210                statement?.TryBind("@ChannelId", query.ChannelIds[0].ToString("N", CultureInfo.InvariantCulture));
 3211            }
 6723212            else if (query.ChannelIds.Count > 1)
 3213            {
 03214                var inClause = string.Join(',', query.ChannelIds.Select(i => "'" + i.ToString("N", CultureInfo.Invariant
 03215                whereClauses.Add($"ChannelId in ({inClause})");
 3216            }
 3217
 6723218            if (!query.ParentId.IsEmpty())
 3219            {
 3763220                whereClauses.Add("ParentId=@ParentId");
 3763221                statement?.TryBind("@ParentId", query.ParentId);
 3222            }
 3223
 6723224            if (!string.IsNullOrWhiteSpace(query.Path))
 3225            {
 03226                whereClauses.Add("Path=@Path");
 03227                statement?.TryBind("@Path", GetPathToSave(query.Path));
 3228            }
 3229
 6723230            if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
 3231            {
 03232                whereClauses.Add("PresentationUniqueKey=@PresentationUniqueKey");
 03233                statement?.TryBind("@PresentationUniqueKey", query.PresentationUniqueKey);
 3234            }
 3235
 6723236            if (query.MinCommunityRating.HasValue)
 3237            {
 03238                whereClauses.Add("CommunityRating>=@MinCommunityRating");
 03239                statement?.TryBind("@MinCommunityRating", query.MinCommunityRating.Value);
 3240            }
 3241
 6723242            if (query.MinIndexNumber.HasValue)
 3243            {
 03244                whereClauses.Add("IndexNumber>=@MinIndexNumber");
 03245                statement?.TryBind("@MinIndexNumber", query.MinIndexNumber.Value);
 3246            }
 3247
 6723248            if (query.MinParentAndIndexNumber.HasValue)
 3249            {
 03250                whereClauses.Add("((ParentIndexNumber=@MinParentAndIndexNumberParent and IndexNumber>=@MinParentAndIndex
 03251                statement?.TryBind("@MinParentAndIndexNumberParent", query.MinParentAndIndexNumber.Value.ParentIndexNumb
 03252                statement?.TryBind("@MinParentAndIndexNumberIndex", query.MinParentAndIndexNumber.Value.IndexNumber);
 3253            }
 3254
 6723255            if (query.MinDateCreated.HasValue)
 3256            {
 03257                whereClauses.Add("DateCreated>=@MinDateCreated");
 03258                statement?.TryBind("@MinDateCreated", query.MinDateCreated.Value);
 3259            }
 3260
 6723261            if (query.MinDateLastSaved.HasValue)
 3262            {
 03263                whereClauses.Add("(DateLastSaved not null and DateLastSaved>=@MinDateLastSavedForUser)");
 03264                statement?.TryBind("@MinDateLastSaved", query.MinDateLastSaved.Value);
 3265            }
 3266
 6723267            if (query.MinDateLastSavedForUser.HasValue)
 3268            {
 03269                whereClauses.Add("(DateLastSaved not null and DateLastSaved>=@MinDateLastSavedForUser)");
 03270                statement?.TryBind("@MinDateLastSavedForUser", query.MinDateLastSavedForUser.Value);
 3271            }
 3272
 6723273            if (query.IndexNumber.HasValue)
 3274            {
 03275                whereClauses.Add("IndexNumber=@IndexNumber");
 03276                statement?.TryBind("@IndexNumber", query.IndexNumber.Value);
 3277            }
 3278
 6723279            if (query.ParentIndexNumber.HasValue)
 3280            {
 03281                whereClauses.Add("ParentIndexNumber=@ParentIndexNumber");
 03282                statement?.TryBind("@ParentIndexNumber", query.ParentIndexNumber.Value);
 3283            }
 3284
 6723285            if (query.ParentIndexNumberNotEquals.HasValue)
 3286            {
 03287                whereClauses.Add("(ParentIndexNumber<>@ParentIndexNumberNotEquals or ParentIndexNumber is null)");
 03288                statement?.TryBind("@ParentIndexNumberNotEquals", query.ParentIndexNumberNotEquals.Value);
 3289            }
 3290
 6723291            var minEndDate = query.MinEndDate;
 6723292            var maxEndDate = query.MaxEndDate;
 3293
 6723294            if (query.HasAired.HasValue)
 3295            {
 03296                if (query.HasAired.Value)
 3297                {
 03298                    maxEndDate = DateTime.UtcNow;
 3299                }
 3300                else
 3301                {
 03302                    minEndDate = DateTime.UtcNow;
 3303                }
 3304            }
 3305
 6723306            if (minEndDate.HasValue)
 3307            {
 03308                whereClauses.Add("EndDate>=@MinEndDate");
 03309                statement?.TryBind("@MinEndDate", minEndDate.Value);
 3310            }
 3311
 6723312            if (maxEndDate.HasValue)
 3313            {
 03314                whereClauses.Add("EndDate<=@MaxEndDate");
 03315                statement?.TryBind("@MaxEndDate", maxEndDate.Value);
 3316            }
 3317
 6723318            if (query.MinStartDate.HasValue)
 3319            {
 03320                whereClauses.Add("StartDate>=@MinStartDate");
 03321                statement?.TryBind("@MinStartDate", query.MinStartDate.Value);
 3322            }
 3323
 6723324            if (query.MaxStartDate.HasValue)
 3325            {
 03326                whereClauses.Add("StartDate<=@MaxStartDate");
 03327                statement?.TryBind("@MaxStartDate", query.MaxStartDate.Value);
 3328            }
 3329
 6723330            if (query.MinPremiereDate.HasValue)
 3331            {
 03332                whereClauses.Add("PremiereDate>=@MinPremiereDate");
 03333                statement?.TryBind("@MinPremiereDate", query.MinPremiereDate.Value);
 3334            }
 3335
 6723336            if (query.MaxPremiereDate.HasValue)
 3337            {
 03338                whereClauses.Add("PremiereDate<=@MaxPremiereDate");
 03339                statement?.TryBind("@MaxPremiereDate", query.MaxPremiereDate.Value);
 3340            }
 3341
 6723342            StringBuilder clauseBuilder = new StringBuilder();
 3343            const string Or = " OR ";
 3344
 6723345            var trailerTypes = query.TrailerTypes;
 6723346            int trailerTypesLen = trailerTypes.Length;
 6723347            if (trailerTypesLen > 0)
 3348            {
 03349                clauseBuilder.Append('(');
 3350
 03351                for (int i = 0; i < trailerTypesLen; i++)
 3352                {
 03353                    var paramName = "@TrailerTypes" + i;
 03354                    clauseBuilder.Append("TrailerTypes like ")
 03355                        .Append(paramName)
 03356                        .Append(Or);
 03357                    statement?.TryBind(paramName, "%" + trailerTypes[i] + "%");
 3358                }
 3359
 03360                clauseBuilder.Length -= Or.Length;
 03361                clauseBuilder.Append(')');
 3362
 03363                whereClauses.Add(clauseBuilder.ToString());
 3364
 03365                clauseBuilder.Length = 0;
 3366            }
 3367
 6723368            if (query.IsAiring.HasValue)
 3369            {
 03370                if (query.IsAiring.Value)
 3371                {
 03372                    whereClauses.Add("StartDate<=@MaxStartDate");
 03373                    statement?.TryBind("@MaxStartDate", DateTime.UtcNow);
 3374
 03375                    whereClauses.Add("EndDate>=@MinEndDate");
 03376                    statement?.TryBind("@MinEndDate", DateTime.UtcNow);
 3377                }
 3378                else
 3379                {
 03380                    whereClauses.Add("(StartDate>@IsAiringDate OR EndDate < @IsAiringDate)");
 03381                    statement?.TryBind("@IsAiringDate", DateTime.UtcNow);
 3382                }
 3383            }
 3384
 6723385            int personIdsLen = query.PersonIds.Length;
 6723386            if (personIdsLen > 0)
 3387            {
 3388                // TODO: Should this query with CleanName ?
 3389
 03390                clauseBuilder.Append('(');
 3391
 03392                Span<byte> idBytes = stackalloc byte[16];
 03393                for (int i = 0; i < personIdsLen; i++)
 3394                {
 03395                    string paramName = "@PersonId" + i;
 03396                    clauseBuilder.Append("(guid in (select itemid from People where Name = (select Name from TypedBaseIt
 03397                        .Append(paramName)
 03398                        .Append("))) OR ");
 3399
 03400                    statement?.TryBind(paramName, query.PersonIds[i]);
 3401                }
 3402
 03403                clauseBuilder.Length -= Or.Length;
 03404                clauseBuilder.Append(')');
 3405
 03406                whereClauses.Add(clauseBuilder.ToString());
 3407
 03408                clauseBuilder.Length = 0;
 3409            }
 3410
 6723411            if (!string.IsNullOrWhiteSpace(query.Person))
 3412            {
 03413                whereClauses.Add("Guid in (select ItemId from People where Name=@PersonName)");
 03414                statement?.TryBind("@PersonName", query.Person);
 3415            }
 3416
 6723417            if (!string.IsNullOrWhiteSpace(query.MinSortName))
 3418            {
 03419                whereClauses.Add("SortName>=@MinSortName");
 03420                statement?.TryBind("@MinSortName", query.MinSortName);
 3421            }
 3422
 6723423            if (!string.IsNullOrWhiteSpace(query.ExternalSeriesId))
 3424            {
 03425                whereClauses.Add("ExternalSeriesId=@ExternalSeriesId");
 03426                statement?.TryBind("@ExternalSeriesId", query.ExternalSeriesId);
 3427            }
 3428
 6723429            if (!string.IsNullOrWhiteSpace(query.ExternalId))
 3430            {
 03431                whereClauses.Add("ExternalId=@ExternalId");
 03432                statement?.TryBind("@ExternalId", query.ExternalId);
 3433            }
 3434
 6723435            if (!string.IsNullOrWhiteSpace(query.Name))
 3436            {
 63437                whereClauses.Add("CleanName=@Name");
 63438                statement?.TryBind("@Name", GetCleanValue(query.Name));
 3439            }
 3440
 3441            // These are the same, for now
 6723442            var nameContains = query.NameContains;
 6723443            if (!string.IsNullOrWhiteSpace(nameContains))
 3444            {
 03445                whereClauses.Add("(CleanName like @NameContains or OriginalTitle like @NameContains)");
 03446                if (statement is not null)
 3447                {
 03448                    nameContains = FixUnicodeChars(nameContains);
 03449                    statement.TryBind("@NameContains", "%" + GetCleanValue(nameContains) + "%");
 3450                }
 3451            }
 3452
 6723453            if (!string.IsNullOrWhiteSpace(query.NameStartsWith))
 3454            {
 03455                whereClauses.Add("SortName like @NameStartsWith");
 03456                statement?.TryBind("@NameStartsWith", query.NameStartsWith + "%");
 3457            }
 3458
 6723459            if (!string.IsNullOrWhiteSpace(query.NameStartsWithOrGreater))
 3460            {
 03461                whereClauses.Add("SortName >= @NameStartsWithOrGreater");
 3462                // lowercase this because SortName is stored as lowercase
 03463                statement?.TryBind("@NameStartsWithOrGreater", query.NameStartsWithOrGreater.ToLowerInvariant());
 3464            }
 3465
 6723466            if (!string.IsNullOrWhiteSpace(query.NameLessThan))
 3467            {
 03468                whereClauses.Add("SortName < @NameLessThan");
 3469                // lowercase this because SortName is stored as lowercase
 03470                statement?.TryBind("@NameLessThan", query.NameLessThan.ToLowerInvariant());
 3471            }
 3472
 6723473            if (query.ImageTypes.Length > 0)
 3474            {
 8243475                foreach (var requiredImage in query.ImageTypes)
 3476                {
 2063477                    whereClauses.Add("Images like '%" + requiredImage + "%'");
 3478                }
 3479            }
 3480
 6723481            if (query.IsLiked.HasValue)
 3482            {
 03483                if (query.IsLiked.Value)
 3484                {
 03485                    whereClauses.Add("rating>=@UserRating");
 03486                    statement?.TryBind("@UserRating", UserItemData.MinLikeValue);
 3487                }
 3488                else
 3489                {
 03490                    whereClauses.Add("(rating is null or rating<@UserRating)");
 03491                    statement?.TryBind("@UserRating", UserItemData.MinLikeValue);
 3492                }
 3493            }
 3494
 6723495            if (query.IsFavoriteOrLiked.HasValue)
 3496            {
 03497                if (query.IsFavoriteOrLiked.Value)
 3498                {
 03499                    whereClauses.Add("IsFavorite=@IsFavoriteOrLiked");
 3500                }
 3501                else
 3502                {
 03503                    whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavoriteOrLiked)");
 3504                }
 3505
 03506                statement?.TryBind("@IsFavoriteOrLiked", query.IsFavoriteOrLiked.Value);
 3507            }
 3508
 6723509            if (query.IsFavorite.HasValue)
 3510            {
 03511                if (query.IsFavorite.Value)
 3512                {
 03513                    whereClauses.Add("IsFavorite=@IsFavorite");
 3514                }
 3515                else
 3516                {
 03517                    whereClauses.Add("(IsFavorite is null or IsFavorite=@IsFavorite)");
 3518                }
 3519
 03520                statement?.TryBind("@IsFavorite", query.IsFavorite.Value);
 3521            }
 3522
 6723523            if (EnableJoinUserData(query))
 3524            {
 23525                if (query.IsPlayed.HasValue)
 3526                {
 3527                    // We should probably figure this out for all folders, but for right now, this is the only place whe
 03528                    if (query.IncludeItemTypes.Length == 1 && query.IncludeItemTypes[0] == BaseItemKind.Series)
 3529                    {
 03530                        if (query.IsPlayed.Value)
 3531                        {
 03532                            whereClauses.Add("PresentationUniqueKey not in (select S.SeriesPresentationUniqueKey from Ty
 3533                        }
 3534                        else
 3535                        {
 03536                            whereClauses.Add("PresentationUniqueKey in (select S.SeriesPresentationUniqueKey from TypedB
 3537                        }
 3538                    }
 3539                    else
 3540                    {
 03541                        if (query.IsPlayed.Value)
 3542                        {
 03543                            whereClauses.Add("(played=@IsPlayed)");
 3544                        }
 3545                        else
 3546                        {
 03547                            whereClauses.Add("(played is null or played=@IsPlayed)");
 3548                        }
 3549
 03550                        statement?.TryBind("@IsPlayed", query.IsPlayed.Value);
 3551                    }
 3552                }
 3553            }
 3554
 6723555            if (query.IsResumable.HasValue)
 3556            {
 23557                if (query.IsResumable.Value)
 3558                {
 23559                    whereClauses.Add("playbackPositionTicks > 0");
 3560                }
 3561                else
 3562                {
 03563                    whereClauses.Add("(playbackPositionTicks is null or playbackPositionTicks = 0)");
 3564                }
 3565            }
 3566
 6723567            if (query.ArtistIds.Length > 0)
 3568            {
 03569                clauseBuilder.Append('(');
 03570                for (var i = 0; i < query.ArtistIds.Length; i++)
 3571                {
 03572                    clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName f
 03573                        .Append(i)
 03574                        .Append(") and Type<=1)) OR ");
 03575                    statement?.TryBind("@ArtistIds" + i, query.ArtistIds[i]);
 3576                }
 3577
 03578                clauseBuilder.Length -= Or.Length;
 03579                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03580                clauseBuilder.Length = 0;
 3581            }
 3582
 6723583            if (query.AlbumArtistIds.Length > 0)
 3584            {
 03585                clauseBuilder.Append('(');
 03586                for (var i = 0; i < query.AlbumArtistIds.Length; i++)
 3587                {
 03588                    clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName f
 03589                        .Append(i)
 03590                        .Append(") and Type=1)) OR ");
 03591                    statement?.TryBind("@ArtistIds" + i, query.AlbumArtistIds[i]);
 3592                }
 3593
 03594                clauseBuilder.Length -= Or.Length;
 03595                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03596                clauseBuilder.Length = 0;
 3597            }
 3598
 6723599            if (query.ContributingArtistIds.Length > 0)
 3600            {
 03601                clauseBuilder.Append('(');
 03602                for (var i = 0; i < query.ContributingArtistIds.Length; i++)
 3603                {
 03604                    clauseBuilder.Append("((select CleanName from TypedBaseItems where guid=@ArtistIds")
 03605                        .Append(i)
 03606                        .Append(") in (select CleanValue from ItemValues where ItemId=Guid and Type=0) AND (select Clean
 03607                        .Append(i)
 03608                        .Append(") not in (select CleanValue from ItemValues where ItemId=Guid and Type=1)) OR ");
 03609                    statement?.TryBind("@ArtistIds" + i, query.ContributingArtistIds[i]);
 3610                }
 3611
 03612                clauseBuilder.Length -= Or.Length;
 03613                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03614                clauseBuilder.Length = 0;
 3615            }
 3616
 6723617            if (query.AlbumIds.Length > 0)
 3618            {
 03619                clauseBuilder.Append('(');
 03620                for (var i = 0; i < query.AlbumIds.Length; i++)
 3621                {
 03622                    clauseBuilder.Append("Album in (select Name from typedbaseitems where guid=@AlbumIds")
 03623                        .Append(i)
 03624                        .Append(") OR ");
 03625                    statement?.TryBind("@AlbumIds" + i, query.AlbumIds[i]);
 3626                }
 3627
 03628                clauseBuilder.Length -= Or.Length;
 03629                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03630                clauseBuilder.Length = 0;
 3631            }
 3632
 6723633            if (query.ExcludeArtistIds.Length > 0)
 3634            {
 03635                clauseBuilder.Append('(');
 03636                for (var i = 0; i < query.ExcludeArtistIds.Length; i++)
 3637                {
 03638                    clauseBuilder.Append("(guid not in (select itemid from ItemValues where CleanValue = (select CleanNa
 03639                        .Append(i)
 03640                        .Append(") and Type<=1)) OR ");
 03641                    statement?.TryBind("@ExcludeArtistId" + i, query.ExcludeArtistIds[i]);
 3642                }
 3643
 03644                clauseBuilder.Length -= Or.Length;
 03645                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03646                clauseBuilder.Length = 0;
 3647            }
 3648
 6723649            if (query.GenreIds.Count > 0)
 3650            {
 03651                clauseBuilder.Append('(');
 03652                for (var i = 0; i < query.GenreIds.Count; i++)
 3653                {
 03654                    clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName f
 03655                        .Append(i)
 03656                        .Append(") and Type=2)) OR ");
 03657                    statement?.TryBind("@GenreId" + i, query.GenreIds[i]);
 3658                }
 3659
 03660                clauseBuilder.Length -= Or.Length;
 03661                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03662                clauseBuilder.Length = 0;
 3663            }
 3664
 6723665            if (query.Genres.Count > 0)
 3666            {
 03667                clauseBuilder.Append('(');
 03668                for (var i = 0; i < query.Genres.Count; i++)
 3669                {
 03670                    clauseBuilder.Append("@Genre")
 03671                        .Append(i)
 03672                        .Append(" in (select CleanValue from ItemValues where ItemId=Guid and Type=2) OR ");
 03673                    statement?.TryBind("@Genre" + i, GetCleanValue(query.Genres[i]));
 3674                }
 3675
 03676                clauseBuilder.Length -= Or.Length;
 03677                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03678                clauseBuilder.Length = 0;
 3679            }
 3680
 6723681            if (tags.Count > 0)
 3682            {
 03683                clauseBuilder.Append('(');
 03684                for (var i = 0; i < tags.Count; i++)
 3685                {
 03686                    clauseBuilder.Append("@Tag")
 03687                        .Append(i)
 03688                        .Append(" in (select CleanValue from ItemValues where ItemId=Guid and Type=4) OR ");
 03689                    statement?.TryBind("@Tag" + i, GetCleanValue(tags[i]));
 3690                }
 3691
 03692                clauseBuilder.Length -= Or.Length;
 03693                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03694                clauseBuilder.Length = 0;
 3695            }
 3696
 6723697            if (excludeTags.Count > 0)
 3698            {
 03699                clauseBuilder.Append('(');
 03700                for (var i = 0; i < excludeTags.Count; i++)
 3701                {
 03702                    clauseBuilder.Append("@ExcludeTag")
 03703                        .Append(i)
 03704                        .Append(" not in (select CleanValue from ItemValues where ItemId=Guid and Type=4) OR ");
 03705                    statement?.TryBind("@ExcludeTag" + i, GetCleanValue(excludeTags[i]));
 3706                }
 3707
 03708                clauseBuilder.Length -= Or.Length;
 03709                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03710                clauseBuilder.Length = 0;
 3711            }
 3712
 6723713            if (query.StudioIds.Length > 0)
 3714            {
 03715                clauseBuilder.Append('(');
 03716                for (var i = 0; i < query.StudioIds.Length; i++)
 3717                {
 03718                    clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName f
 03719                        .Append(i)
 03720                        .Append(") and Type=3)) OR ");
 03721                    statement?.TryBind("@StudioId" + i, query.StudioIds[i]);
 3722                }
 3723
 03724                clauseBuilder.Length -= Or.Length;
 03725                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03726                clauseBuilder.Length = 0;
 3727            }
 3728
 6723729            if (query.OfficialRatings.Length > 0)
 3730            {
 03731                clauseBuilder.Append('(');
 03732                for (var i = 0; i < query.OfficialRatings.Length; i++)
 3733                {
 03734                    clauseBuilder.Append("OfficialRating=@OfficialRating").Append(i).Append(Or);
 03735                    statement?.TryBind("@OfficialRating" + i, query.OfficialRatings[i]);
 3736                }
 3737
 03738                clauseBuilder.Length -= Or.Length;
 03739                whereClauses.Add(clauseBuilder.Append(')').ToString());
 03740                clauseBuilder.Length = 0;
 3741            }
 3742
 6723743            clauseBuilder.Append('(');
 6723744            if (query.HasParentalRating ?? false)
 3745            {
 03746                clauseBuilder.Append("InheritedParentalRatingValue not null");
 03747                if (query.MinParentalRating.HasValue)
 3748                {
 03749                    clauseBuilder.Append(" AND InheritedParentalRatingValue >= @MinParentalRating");
 03750                    statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value);
 3751                }
 3752
 03753                if (query.MaxParentalRating.HasValue)
 3754                {
 03755                    clauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating");
 03756                    statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
 3757                }
 3758            }
 6723759            else if (query.BlockUnratedItems.Length > 0)
 3760            {
 3761                const string ParamName = "@UnratedType";
 03762                clauseBuilder.Append("(InheritedParentalRatingValue is null AND UnratedType not in (");
 3763
 03764                for (int i = 0; i < query.BlockUnratedItems.Length; i++)
 3765                {
 03766                    clauseBuilder.Append(ParamName).Append(i).Append(',');
 03767                    statement?.TryBind(ParamName + i, query.BlockUnratedItems[i].ToString());
 3768                }
 3769
 3770                // Remove trailing comma
 03771                clauseBuilder.Length--;
 03772                clauseBuilder.Append("))");
 3773
 03774                if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue)
 3775                {
 03776                    clauseBuilder.Append(" OR (");
 3777                }
 3778
 03779                if (query.MinParentalRating.HasValue)
 3780                {
 03781                    clauseBuilder.Append("InheritedParentalRatingValue >= @MinParentalRating");
 03782                    statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value);
 3783                }
 3784
 03785                if (query.MaxParentalRating.HasValue)
 3786                {
 03787                    if (query.MinParentalRating.HasValue)
 3788                    {
 03789                        clauseBuilder.Append(" AND ");
 3790                    }
 3791
 03792                    clauseBuilder.Append("InheritedParentalRatingValue <= @MaxParentalRating");
 03793                    statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
 3794                }
 3795
 03796                if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue)
 3797                {
 03798                    clauseBuilder.Append(')');
 3799                }
 3800
 03801                if (!(query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue))
 3802                {
 03803                    clauseBuilder.Append(" OR InheritedParentalRatingValue not null");
 3804                }
 3805            }
 6723806            else if (query.MinParentalRating.HasValue)
 3807            {
 03808                clauseBuilder.Append("InheritedParentalRatingValue is null OR (InheritedParentalRatingValue >= @MinParen
 03809                statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value);
 3810
 03811                if (query.MaxParentalRating.HasValue)
 3812                {
 03813                    clauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating");
 03814                    statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
 3815                }
 3816
 03817                clauseBuilder.Append(')');
 3818            }
 6723819            else if (query.MaxParentalRating.HasValue)
 3820            {
 1143821                clauseBuilder.Append("InheritedParentalRatingValue is null OR InheritedParentalRatingValue <= @MaxParent
 1143822                statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
 3823            }
 5583824            else if (!query.HasParentalRating ?? false)
 3825            {
 03826                clauseBuilder.Append("InheritedParentalRatingValue is null");
 3827            }
 3828
 6723829            if (clauseBuilder.Length > 1)
 3830            {
 1143831                whereClauses.Add(clauseBuilder.Append(')').ToString());
 1143832                clauseBuilder.Length = 0;
 3833            }
 3834
 6723835            if (query.HasOfficialRating.HasValue)
 3836            {
 03837                if (query.HasOfficialRating.Value)
 3838                {
 03839                    whereClauses.Add("(OfficialRating not null AND OfficialRating<>'')");
 3840                }
 3841                else
 3842                {
 03843                    whereClauses.Add("(OfficialRating is null OR OfficialRating='')");
 3844                }
 3845            }
 3846
 6723847            if (query.HasOverview.HasValue)
 3848            {
 03849                if (query.HasOverview.Value)
 3850                {
 03851                    whereClauses.Add("(Overview not null AND Overview<>'')");
 3852                }
 3853                else
 3854                {
 03855                    whereClauses.Add("(Overview is null OR Overview='')");
 3856                }
 3857            }
 3858
 6723859            if (query.HasOwnerId.HasValue)
 3860            {
 03861                if (query.HasOwnerId.Value)
 3862                {
 03863                    whereClauses.Add("OwnerId not null");
 3864                }
 3865                else
 3866                {
 03867                    whereClauses.Add("OwnerId is null");
 3868                }
 3869            }
 3870
 6723871            if (!string.IsNullOrWhiteSpace(query.HasNoAudioTrackWithLanguage))
 3872            {
 03873                whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.
 03874                statement?.TryBind("@HasNoAudioTrackWithLanguage", query.HasNoAudioTrackWithLanguage);
 3875            }
 3876
 6723877            if (!string.IsNullOrWhiteSpace(query.HasNoInternalSubtitleTrackWithLanguage))
 3878            {
 03879                whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.
 03880                statement?.TryBind("@HasNoInternalSubtitleTrackWithLanguage", query.HasNoInternalSubtitleTrackWithLangua
 3881            }
 3882
 6723883            if (!string.IsNullOrWhiteSpace(query.HasNoExternalSubtitleTrackWithLanguage))
 3884            {
 03885                whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.
 03886                statement?.TryBind("@HasNoExternalSubtitleTrackWithLanguage", query.HasNoExternalSubtitleTrackWithLangua
 3887            }
 3888
 6723889            if (!string.IsNullOrWhiteSpace(query.HasNoSubtitleTrackWithLanguage))
 3890            {
 03891                whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.
 03892                statement?.TryBind("@HasNoSubtitleTrackWithLanguage", query.HasNoSubtitleTrackWithLanguage);
 3893            }
 3894
 6723895            if (query.HasSubtitles.HasValue)
 3896            {
 03897                if (query.HasSubtitles.Value)
 3898                {
 03899                    whereClauses.Add("((select type from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.
 3900                }
 3901                else
 3902                {
 03903                    whereClauses.Add("((select type from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.
 3904                }
 3905            }
 3906
 6723907            if (query.HasChapterImages.HasValue)
 3908            {
 03909                if (query.HasChapterImages.Value)
 3910                {
 03911                    whereClauses.Add("((select imagepath from Chapters2 where Chapters2.ItemId=A.Guid and imagepath not 
 3912                }
 3913                else
 3914                {
 03915                    whereClauses.Add("((select imagepath from Chapters2 where Chapters2.ItemId=A.Guid and imagepath not 
 3916                }
 3917            }
 3918
 6723919            if (query.HasDeadParentId.HasValue && query.HasDeadParentId.Value)
 3920            {
 383921                whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)");
 3922            }
 3923
 6723924            if (query.IsDeadArtist.HasValue && query.IsDeadArtist.Value)
 3925            {
 383926                whereClauses.Add("CleanName not in (Select CleanValue From ItemValues where Type in (0,1))");
 3927            }
 3928
 6723929            if (query.IsDeadStudio.HasValue && query.IsDeadStudio.Value)
 3930            {
 383931                whereClauses.Add("CleanName not in (Select CleanValue From ItemValues where Type = 3)");
 3932            }
 3933
 6723934            if (query.IsDeadPerson.HasValue && query.IsDeadPerson.Value)
 3935            {
 03936                whereClauses.Add("Name not in (Select Name From People)");
 3937            }
 3938
 6723939            if (query.Years.Length == 1)
 3940            {
 03941                whereClauses.Add("ProductionYear=@Years");
 03942                statement?.TryBind("@Years", query.Years[0].ToString(CultureInfo.InvariantCulture));
 3943            }
 6723944            else if (query.Years.Length > 1)
 3945            {
 03946                var val = string.Join(',', query.Years);
 03947                whereClauses.Add("ProductionYear in (" + val + ")");
 3948            }
 3949
 6723950            var isVirtualItem = query.IsVirtualItem ?? query.IsMissing;
 6723951            if (isVirtualItem.HasValue)
 3952            {
 23953                whereClauses.Add("IsVirtualItem=@IsVirtualItem");
 23954                statement?.TryBind("@IsVirtualItem", isVirtualItem.Value);
 3955            }
 3956
 6723957            if (query.IsSpecialSeason.HasValue)
 3958            {
 03959                if (query.IsSpecialSeason.Value)
 3960                {
 03961                    whereClauses.Add("IndexNumber = 0");
 3962                }
 3963                else
 3964                {
 03965                    whereClauses.Add("IndexNumber <> 0");
 3966                }
 3967            }
 3968
 6723969            if (query.IsUnaired.HasValue)
 3970            {
 03971                if (query.IsUnaired.Value)
 3972                {
 03973                    whereClauses.Add("PremiereDate >= DATETIME('now')");
 3974                }
 3975                else
 3976                {
 03977                    whereClauses.Add("PremiereDate < DATETIME('now')");
 3978                }
 3979            }
 3980
 6723981            if (query.MediaTypes.Length == 1)
 3982            {
 03983                whereClauses.Add("MediaType=@MediaTypes");
 03984                statement?.TryBind("@MediaTypes", query.MediaTypes[0].ToString());
 3985            }
 6723986            else if (query.MediaTypes.Length > 1)
 3987            {
 03988                var val = string.Join(',', query.MediaTypes.Select(i => $"'{i}'"));
 03989                whereClauses.Add("MediaType in (" + val + ")");
 3990            }
 3991
 6723992            if (query.ItemIds.Length > 0)
 3993            {
 03994                var includeIds = new List<string>();
 03995                var index = 0;
 03996                foreach (var id in query.ItemIds)
 3997                {
 03998                    includeIds.Add("Guid = @IncludeId" + index);
 03999                    statement?.TryBind("@IncludeId" + index, id);
 04000                    index++;
 4001                }
 4002
 04003                whereClauses.Add("(" + string.Join(" OR ", includeIds) + ")");
 4004            }
 4005
 6724006            if (query.ExcludeItemIds.Length > 0)
 4007            {
 04008                var excludeIds = new List<string>();
 04009                var index = 0;
 04010                foreach (var id in query.ExcludeItemIds)
 4011                {
 04012                    excludeIds.Add("Guid <> @ExcludeId" + index);
 04013                    statement?.TryBind("@ExcludeId" + index, id);
 04014                    index++;
 4015                }
 4016
 04017                whereClauses.Add(string.Join(" AND ", excludeIds));
 4018            }
 4019
 6724020            if (query.ExcludeProviderIds is not null && query.ExcludeProviderIds.Count > 0)
 4021            {
 04022                var excludeIds = new List<string>();
 4023
 04024                var index = 0;
 04025                foreach (var pair in query.ExcludeProviderIds)
 4026                {
 04027                    if (string.Equals(pair.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.OrdinalIgnoreC
 4028                    {
 4029                        continue;
 4030                    }
 4031
 04032                    var paramName = "@ExcludeProviderId" + index;
 04033                    excludeIds.Add("(ProviderIds is null or ProviderIds not like " + paramName + ")");
 04034                    statement?.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%");
 04035                    index++;
 4036
 04037                    break;
 4038                }
 4039
 04040                if (excludeIds.Count > 0)
 4041                {
 04042                    whereClauses.Add(string.Join(" AND ", excludeIds));
 4043                }
 4044            }
 4045
 6724046            if (query.HasAnyProviderId is not null && query.HasAnyProviderId.Count > 0)
 4047            {
 04048                var hasProviderIds = new List<string>();
 4049
 04050                var index = 0;
 04051                foreach (var pair in query.HasAnyProviderId)
 4052                {
 04053                    if (string.Equals(pair.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.OrdinalIgnoreC
 4054                    {
 4055                        continue;
 4056                    }
 4057
 4058                    // TODO this seems to be an idea for a better schema where ProviderIds are their own table
 4059                    //      but this is not implemented
 4060                    // hasProviderIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pa
 4061
 4062                    // TODO this is a really BAD way to do it since the pair:
 4063                    //      Tmdb, 1234 matches Tmdb=1234 but also Tmdb=1234567
 4064                    //      and maybe even NotTmdb=1234.
 4065
 4066                    // this is a placeholder for this specific pair to correlate it in the bigger query
 04067                    var paramName = "@HasAnyProviderId" + index;
 4068
 4069                    // this is a search for the placeholder
 04070                    hasProviderIds.Add("ProviderIds like " + paramName);
 4071
 4072                    // this replaces the placeholder with a value, here: %key=val%
 04073                    statement?.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%");
 04074                    index++;
 4075
 04076                    break;
 4077                }
 4078
 04079                if (hasProviderIds.Count > 0)
 4080                {
 04081                    whereClauses.Add("(" + string.Join(" OR ", hasProviderIds) + ")");
 4082                }
 4083            }
 4084
 6724085            if (query.HasImdbId.HasValue)
 4086            {
 04087                whereClauses.Add(GetProviderIdClause(query.HasImdbId.Value, "imdb"));
 4088            }
 4089
 6724090            if (query.HasTmdbId.HasValue)
 4091            {
 04092                whereClauses.Add(GetProviderIdClause(query.HasTmdbId.Value, "tmdb"));
 4093            }
 4094
 6724095            if (query.HasTvdbId.HasValue)
 4096            {
 04097                whereClauses.Add(GetProviderIdClause(query.HasTvdbId.Value, "tvdb"));
 4098            }
 4099
 6724100            var queryTopParentIds = query.TopParentIds;
 4101
 6724102            if (queryTopParentIds.Length > 0)
 4103            {
 104104                var includedItemByNameTypes = GetItemByNameTypesInQuery(query);
 104105                var enableItemsByName = (query.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0;
 4106
 104107                if (queryTopParentIds.Length == 1)
 4108                {
 104109                    if (enableItemsByName && includedItemByNameTypes.Count == 1)
 4110                    {
 04111                        whereClauses.Add("(TopParentId=@TopParentId or Type=@IncludedItemByNameType)");
 04112                        statement?.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]);
 4113                    }
 104114                    else if (enableItemsByName && includedItemByNameTypes.Count > 1)
 4115                    {
 04116                        var itemByNameTypeVal = string.Join(',', includedItemByNameTypes.Select(i => "'" + i + "'"));
 04117                        whereClauses.Add("(TopParentId=@TopParentId or Type in (" + itemByNameTypeVal + "))");
 4118                    }
 4119                    else
 4120                    {
 104121                        whereClauses.Add("(TopParentId=@TopParentId)");
 4122                    }
 4123
 104124                    statement?.TryBind("@TopParentId", queryTopParentIds[0].ToString("N", CultureInfo.InvariantCulture))
 4125                }
 04126                else if (queryTopParentIds.Length > 1)
 4127                {
 04128                    var val = string.Join(',', queryTopParentIds.Select(i => "'" + i.ToString("N", CultureInfo.Invariant
 4129
 04130                    if (enableItemsByName && includedItemByNameTypes.Count == 1)
 4131                    {
 04132                        whereClauses.Add("(Type=@IncludedItemByNameType or TopParentId in (" + val + "))");
 04133                        statement?.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]);
 4134                    }
 04135                    else if (enableItemsByName && includedItemByNameTypes.Count > 1)
 4136                    {
 04137                        var itemByNameTypeVal = string.Join(',', includedItemByNameTypes.Select(i => "'" + i + "'"));
 04138                        whereClauses.Add("(Type in (" + itemByNameTypeVal + ") or TopParentId in (" + val + "))");
 4139                    }
 4140                    else
 4141                    {
 04142                        whereClauses.Add("TopParentId in (" + val + ")");
 4143                    }
 4144                }
 4145            }
 4146
 6724147            if (query.AncestorIds.Length == 1)
 4148            {
 524149                whereClauses.Add("Guid in (select itemId from AncestorIds where AncestorId=@AncestorId)");
 524150                statement?.TryBind("@AncestorId", query.AncestorIds[0]);
 4151            }
 4152
 6724153            if (query.AncestorIds.Length > 1)
 4154            {
 04155                var inClause = string.Join(',', query.AncestorIds.Select(i => "'" + i.ToString("N", CultureInfo.Invarian
 04156                whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds wh
 4157            }
 4158
 6724159            if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey))
 4160            {
 04161                var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUni
 04162                whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds wh
 04163                statement?.TryBind("@AncestorWithPresentationUniqueKey", query.AncestorWithPresentationUniqueKey);
 4164            }
 4165
 6724166            if (!string.IsNullOrWhiteSpace(query.SeriesPresentationUniqueKey))
 4167            {
 04168                whereClauses.Add("SeriesPresentationUniqueKey=@SeriesPresentationUniqueKey");
 04169                statement?.TryBind("@SeriesPresentationUniqueKey", query.SeriesPresentationUniqueKey);
 4170            }
 4171
 6724172            if (query.ExcludeInheritedTags.Length > 0)
 4173            {
 04174                var paramName = "@ExcludeInheritedTags";
 04175                if (statement is null)
 4176                {
 04177                    int index = 0;
 04178                    string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(_ => paramName + index++));
 04179                    whereClauses.Add("((select CleanValue from ItemValues where ItemId=Guid and Type=6 and cleanvalue in
 4180                }
 4181                else
 4182                {
 04183                    for (int index = 0; index < query.ExcludeInheritedTags.Length; index++)
 4184                    {
 04185                        statement.TryBind(paramName + index, GetCleanValue(query.ExcludeInheritedTags[index]));
 4186                    }
 4187                }
 4188            }
 4189
 6724190            if (query.IncludeInheritedTags.Length > 0)
 4191            {
 04192                var paramName = "@IncludeInheritedTags";
 04193                if (statement is null)
 4194                {
 04195                    int index = 0;
 04196                    string includedTags = string.Join(',', query.IncludeInheritedTags.Select(_ => paramName + index++));
 4197                    // Episodes do not store inherit tags from their parents in the database, and the tag may be still r
 4198                    // In addtion to the tags for the episodes themselves, we need to manually query its parent (the sea
 04199                    if (includeTypes.Length == 1 && includeTypes.FirstOrDefault() is BaseItemKind.Episode)
 4200                    {
 04201                        whereClauses.Add($"""
 04202                                          ((select CleanValue from ItemValues where ItemId=Guid and Type=6 and CleanValu
 04203                                          OR (select CleanValue from ItemValues where ItemId=ParentId and Type=6 and Cle
 04204                                          """);
 4205                    }
 4206                    else
 4207                    {
 04208                        whereClauses.Add("((select CleanValue from ItemValues where ItemId=Guid and Type=6 and cleanvalu
 4209                    }
 4210                }
 4211                else
 4212                {
 04213                    for (int index = 0; index < query.IncludeInheritedTags.Length; index++)
 4214                    {
 04215                        statement.TryBind(paramName + index, GetCleanValue(query.IncludeInheritedTags[index]));
 4216                    }
 4217                }
 4218            }
 4219
 6724220            if (query.SeriesStatuses.Length > 0)
 4221            {
 04222                var statuses = new List<string>();
 4223
 04224                foreach (var seriesStatus in query.SeriesStatuses)
 4225                {
 04226                    statuses.Add("data like  '%" + seriesStatus + "%'");
 4227                }
 4228
 04229                whereClauses.Add("(" + string.Join(" OR ", statuses) + ")");
 4230            }
 4231
 6724232            if (query.BoxSetLibraryFolders.Length > 0)
 4233            {
 04234                var folderIdQueries = new List<string>();
 4235
 04236                foreach (var folderId in query.BoxSetLibraryFolders)
 4237                {
 04238                    folderIdQueries.Add("data like '%" + folderId.ToString("N", CultureInfo.InvariantCulture) + "%'");
 4239                }
 4240
 04241                whereClauses.Add("(" + string.Join(" OR ", folderIdQueries) + ")");
 4242            }
 4243
 6724244            if (query.VideoTypes.Length > 0)
 4245            {
 04246                var videoTypes = new List<string>();
 4247
 04248                foreach (var videoType in query.VideoTypes)
 4249                {
 04250                    videoTypes.Add("data like '%\"VideoType\":\"" + videoType + "\"%'");
 4251                }
 4252
 04253                whereClauses.Add("(" + string.Join(" OR ", videoTypes) + ")");
 4254            }
 4255
 6724256            if (query.Is3D.HasValue)
 4257            {
 04258                if (query.Is3D.Value)
 4259                {
 04260                    whereClauses.Add("data like '%Video3DFormat%'");
 4261                }
 4262                else
 4263                {
 04264                    whereClauses.Add("data not like '%Video3DFormat%'");
 4265                }
 4266            }
 4267
 6724268            if (query.IsPlaceHolder.HasValue)
 4269            {
 04270                if (query.IsPlaceHolder.Value)
 4271                {
 04272                    whereClauses.Add("data like '%\"IsPlaceHolder\":true%'");
 4273                }
 4274                else
 4275                {
 04276                    whereClauses.Add("(data is null or data not like '%\"IsPlaceHolder\":true%')");
 4277                }
 4278            }
 4279
 6724280            if (query.HasSpecialFeature.HasValue)
 4281            {
 04282                if (query.HasSpecialFeature.Value)
 4283                {
 04284                    whereClauses.Add("ExtraIds not null");
 4285                }
 4286                else
 4287                {
 04288                    whereClauses.Add("ExtraIds is null");
 4289                }
 4290            }
 4291
 6724292            if (query.HasTrailer.HasValue)
 4293            {
 04294                if (query.HasTrailer.Value)
 4295                {
 04296                    whereClauses.Add("ExtraIds not null");
 4297                }
 4298                else
 4299                {
 04300                    whereClauses.Add("ExtraIds is null");
 4301                }
 4302            }
 4303
 6724304            if (query.HasThemeSong.HasValue)
 4305            {
 04306                if (query.HasThemeSong.Value)
 4307                {
 04308                    whereClauses.Add("ExtraIds not null");
 4309                }
 4310                else
 4311                {
 04312                    whereClauses.Add("ExtraIds is null");
 4313                }
 4314            }
 4315
 6724316            if (query.HasThemeVideo.HasValue)
 4317            {
 04318                if (query.HasThemeVideo.Value)
 4319                {
 04320                    whereClauses.Add("ExtraIds not null");
 4321                }
 4322                else
 4323                {
 04324                    whereClauses.Add("ExtraIds is null");
 4325                }
 4326            }
 4327
 6724328            return whereClauses;
 4329        }
 4330
 4331        /// <summary>
 4332        /// Formats a where clause for the specified provider.
 4333        /// </summary>
 4334        /// <param name="includeResults">Whether or not to include items with this provider's ids.</param>
 4335        /// <param name="provider">Provider name.</param>
 4336        /// <returns>Formatted SQL clause.</returns>
 4337        private string GetProviderIdClause(bool includeResults, string provider)
 4338        {
 04339            return string.Format(
 04340                CultureInfo.InvariantCulture,
 04341                "ProviderIds {0} like '%{1}=%'",
 04342                includeResults ? string.Empty : "not",
 04343                provider);
 4344        }
 4345
 4346#nullable disable
 4347        private List<string> GetItemByNameTypesInQuery(InternalItemsQuery query)
 4348        {
 104349            var list = new List<string>();
 4350
 104351            if (IsTypeInQuery(BaseItemKind.Person, query))
 4352            {
 24353                list.Add(typeof(Person).FullName);
 4354            }
 4355
 104356            if (IsTypeInQuery(BaseItemKind.Genre, query))
 4357            {
 24358                list.Add(typeof(Genre).FullName);
 4359            }
 4360
 104361            if (IsTypeInQuery(BaseItemKind.MusicGenre, query))
 4362            {
 24363                list.Add(typeof(MusicGenre).FullName);
 4364            }
 4365
 104366            if (IsTypeInQuery(BaseItemKind.MusicArtist, query))
 4367            {
 24368                list.Add(typeof(MusicArtist).FullName);
 4369            }
 4370
 104371            if (IsTypeInQuery(BaseItemKind.Studio, query))
 4372            {
 24373                list.Add(typeof(Studio).FullName);
 4374            }
 4375
 104376            return list;
 4377        }
 4378
 4379        private bool IsTypeInQuery(BaseItemKind type, InternalItemsQuery query)
 4380        {
 504381            if (query.ExcludeItemTypes.Contains(type))
 4382            {
 04383                return false;
 4384            }
 4385
 504386            return query.IncludeItemTypes.Length == 0 || query.IncludeItemTypes.Contains(type);
 4387        }
 4388
 4389        private string GetCleanValue(string value)
 4390        {
 624391            if (string.IsNullOrWhiteSpace(value))
 4392            {
 04393                return value;
 4394            }
 4395
 624396            return value.RemoveDiacritics().ToLowerInvariant();
 4397        }
 4398
 4399        private bool EnableGroupByPresentationUniqueKey(InternalItemsQuery query)
 4400        {
 3364401            if (!query.GroupByPresentationUniqueKey)
 4402            {
 1714403                return false;
 4404            }
 4405
 1654406            if (query.GroupBySeriesPresentationUniqueKey)
 4407            {
 04408                return false;
 4409            }
 4410
 1654411            if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
 4412            {
 04413                return false;
 4414            }
 4415
 1654416            if (query.User is null)
 4417            {
 1634418                return false;
 4419            }
 4420
 24421            if (query.IncludeItemTypes.Length == 0)
 4422            {
 14423                return true;
 4424            }
 4425
 14426            return query.IncludeItemTypes.Contains(BaseItemKind.Episode)
 14427                || query.IncludeItemTypes.Contains(BaseItemKind.Video)
 14428                || query.IncludeItemTypes.Contains(BaseItemKind.Movie)
 14429                || query.IncludeItemTypes.Contains(BaseItemKind.MusicVideo)
 14430                || query.IncludeItemTypes.Contains(BaseItemKind.Series)
 14431                || query.IncludeItemTypes.Contains(BaseItemKind.Season);
 4432        }
 4433
 4434        public void UpdateInheritedValues()
 4435        {
 4436            const string Statements = """
 4437delete from ItemValues where type = 6;
 4438insert into ItemValues (ItemId, Type, Value, CleanValue)  select ItemId, 6, Value, CleanValue from ItemValues where Type
 4439insert into ItemValues (ItemId, Type, Value, CleanValue) select AncestorIds.itemid, 6, ItemValues.Value, ItemValues.Clea
 4440FROM AncestorIds
 4441LEFT JOIN ItemValues ON (AncestorIds.AncestorId = ItemValues.ItemId)
 4442where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4;
 4443""";
 194444            using var connection = GetConnection();
 194445            using var transaction = connection.BeginTransaction();
 194446            connection.Execute(Statements);
 194447            transaction.Commit();
 384448        }
 4449
 4450        public void DeleteItem(Guid id)
 4451        {
 24452            if (id.IsEmpty())
 4453            {
 04454                throw new ArgumentNullException(nameof(id));
 4455            }
 4456
 24457            CheckDisposed();
 4458
 24459            using var connection = GetConnection();
 24460            using var transaction = connection.BeginTransaction();
 4461            // Delete people
 24462            ExecuteWithSingleParam(connection, "delete from People where ItemId=@Id", id);
 4463
 4464            // Delete chapters
 24465            ExecuteWithSingleParam(connection, "delete from " + ChaptersTableName + " where ItemId=@Id", id);
 4466
 4467            // Delete media streams
 24468            ExecuteWithSingleParam(connection, "delete from mediastreams where ItemId=@Id", id);
 4469
 4470            // Delete ancestors
 24471            ExecuteWithSingleParam(connection, "delete from AncestorIds where ItemId=@Id", id);
 4472
 4473            // Delete item values
 24474            ExecuteWithSingleParam(connection, "delete from ItemValues where ItemId=@Id", id);
 4475
 4476            // Delete the item
 24477            ExecuteWithSingleParam(connection, "delete from TypedBaseItems where guid=@Id", id);
 4478
 24479            transaction.Commit();
 44480        }
 4481
 4482        private void ExecuteWithSingleParam(ManagedConnection db, string query, Guid value)
 4483        {
 124484            using (var statement = PrepareStatement(db, query))
 4485            {
 124486                statement.TryBind("@Id", value);
 4487
 124488                statement.ExecuteNonQuery();
 124489            }
 124490        }
 4491
 4492        public List<string> GetPeopleNames(InternalPeopleQuery query)
 4493        {
 04494            ArgumentNullException.ThrowIfNull(query);
 4495
 04496            CheckDisposed();
 4497
 04498            var commandText = new StringBuilder("select Distinct p.Name from People p");
 4499
 04500            var whereClauses = GetPeopleWhereClauses(query, null);
 4501
 04502            if (whereClauses.Count != 0)
 4503            {
 04504                commandText.Append(" where ").AppendJoin(" AND ", whereClauses);
 4505            }
 4506
 04507            commandText.Append(" order by ListOrder");
 4508
 04509            if (query.Limit > 0)
 4510            {
 04511                commandText.Append(" LIMIT ").Append(query.Limit);
 4512            }
 4513
 04514            var list = new List<string>();
 04515            using (var connection = GetConnection(true))
 04516            using (var statement = PrepareStatement(connection, commandText.ToString()))
 4517            {
 4518                // Run this again to bind the params
 04519                GetPeopleWhereClauses(query, statement);
 4520
 04521                foreach (var row in statement.ExecuteQuery())
 4522                {
 04523                    list.Add(row.GetString(0));
 4524                }
 4525            }
 4526
 04527            return list;
 4528        }
 4529
 4530        public List<PersonInfo> GetPeople(InternalPeopleQuery query)
 4531        {
 04532            ArgumentNullException.ThrowIfNull(query);
 4533
 04534            CheckDisposed();
 4535
 04536            StringBuilder commandText = new StringBuilder("select ItemId, Name, Role, PersonType, SortOrder from People 
 4537
 04538            var whereClauses = GetPeopleWhereClauses(query, null);
 4539
 04540            if (whereClauses.Count != 0)
 4541            {
 04542                commandText.Append("  where ").AppendJoin(" AND ", whereClauses);
 4543            }
 4544
 04545            commandText.Append(" order by ListOrder");
 4546
 04547            if (query.Limit > 0)
 4548            {
 04549                commandText.Append(" LIMIT ").Append(query.Limit);
 4550            }
 4551
 04552            var list = new List<PersonInfo>();
 04553            using (var connection = GetConnection(true))
 04554            using (var statement = PrepareStatement(connection, commandText.ToString()))
 4555            {
 4556                // Run this again to bind the params
 04557                GetPeopleWhereClauses(query, statement);
 4558
 04559                foreach (var row in statement.ExecuteQuery())
 4560                {
 04561                    list.Add(GetPerson(row));
 4562                }
 4563            }
 4564
 04565            return list;
 4566        }
 4567
 4568        private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, SqliteCommand statement)
 4569        {
 04570            var whereClauses = new List<string>();
 4571
 04572            if (query.User is not null && query.IsFavorite.HasValue)
 4573            {
 04574                whereClauses.Add(@"p.Name IN (
 04575SELECT Name FROM TypedBaseItems WHERE UserDataKey IN (
 04576SELECT key FROM UserDatas WHERE isFavorite=@IsFavorite AND userId=@UserId)
 04577AND Type = @InternalPersonType)");
 04578                statement?.TryBind("@IsFavorite", query.IsFavorite.Value);
 04579                statement?.TryBind("@InternalPersonType", typeof(Person).FullName);
 04580                statement?.TryBind("@UserId", query.User.InternalId);
 4581            }
 4582
 04583            if (!query.ItemId.IsEmpty())
 4584            {
 04585                whereClauses.Add("ItemId=@ItemId");
 04586                statement?.TryBind("@ItemId", query.ItemId);
 4587            }
 4588
 04589            if (!query.AppearsInItemId.IsEmpty())
 4590            {
 04591                whereClauses.Add("p.Name in (Select Name from People where ItemId=@AppearsInItemId)");
 04592                statement?.TryBind("@AppearsInItemId", query.AppearsInItemId);
 4593            }
 4594
 04595            var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList();
 4596
 04597            if (queryPersonTypes.Count == 1)
 4598            {
 04599                whereClauses.Add("PersonType=@PersonType");
 04600                statement?.TryBind("@PersonType", queryPersonTypes[0]);
 4601            }
 04602            else if (queryPersonTypes.Count > 1)
 4603            {
 04604                var val = string.Join(',', queryPersonTypes.Select(i => "'" + i + "'"));
 4605
 04606                whereClauses.Add("PersonType in (" + val + ")");
 4607            }
 4608
 04609            var queryExcludePersonTypes = query.ExcludePersonTypes.Where(IsValidPersonType).ToList();
 4610
 04611            if (queryExcludePersonTypes.Count == 1)
 4612            {
 04613                whereClauses.Add("PersonType<>@PersonType");
 04614                statement?.TryBind("@PersonType", queryExcludePersonTypes[0]);
 4615            }
 04616            else if (queryExcludePersonTypes.Count > 1)
 4617            {
 04618                var val = string.Join(',', queryExcludePersonTypes.Select(i => "'" + i + "'"));
 4619
 04620                whereClauses.Add("PersonType not in (" + val + ")");
 4621            }
 4622
 04623            if (query.MaxListOrder.HasValue)
 4624            {
 04625                whereClauses.Add("ListOrder<=@MaxListOrder");
 04626                statement?.TryBind("@MaxListOrder", query.MaxListOrder.Value);
 4627            }
 4628
 04629            if (!string.IsNullOrWhiteSpace(query.NameContains))
 4630            {
 04631                whereClauses.Add("p.Name like @NameContains");
 04632                statement?.TryBind("@NameContains", "%" + query.NameContains + "%");
 4633            }
 4634
 04635            return whereClauses;
 4636        }
 4637
 4638        private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, ManagedConnection db, SqliteCommand deleteAnce
 4639        {
 594640            if (itemId.IsEmpty())
 4641            {
 04642                throw new ArgumentNullException(nameof(itemId));
 4643            }
 4644
 594645            ArgumentNullException.ThrowIfNull(ancestorIds);
 4646
 594647            CheckDisposed();
 4648
 4649            // First delete
 594650            deleteAncestorsStatement.TryBind("@ItemId", itemId);
 594651            deleteAncestorsStatement.ExecuteNonQuery();
 4652
 594653            if (ancestorIds.Count == 0)
 4654            {
 574655                return;
 4656            }
 4657
 24658            var insertText = new StringBuilder("insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values ");
 4659
 84660            for (var i = 0; i < ancestorIds.Count; i++)
 4661            {
 24662                insertText.AppendFormat(
 24663                    CultureInfo.InvariantCulture,
 24664                    "(@ItemId, @AncestorId{0}, @AncestorIdText{0}),",
 24665                    i.ToString(CultureInfo.InvariantCulture));
 4666            }
 4667
 4668            // Remove trailing comma
 24669            insertText.Length--;
 4670
 24671            using (var statement = PrepareStatement(db, insertText.ToString()))
 4672            {
 24673                statement.TryBind("@ItemId", itemId);
 4674
 84675                for (var i = 0; i < ancestorIds.Count; i++)
 4676                {
 24677                    var index = i.ToString(CultureInfo.InvariantCulture);
 4678
 24679                    var ancestorId = ancestorIds[i];
 4680
 24681                    statement.TryBind("@AncestorId" + index, ancestorId);
 24682                    statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N", CultureInfo.InvariantCulture))
 4683                }
 4684
 24685                statement.ExecuteNonQuery();
 24686            }
 24687        }
 4688
 4689        public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAllArtists(InternalItemsQuery query)
 4690        {
 04691            return GetItemValues(query, new[] { 0, 1 }, typeof(MusicArtist).FullName);
 4692        }
 4693
 4694        public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetArtists(InternalItemsQuery query)
 4695        {
 04696            return GetItemValues(query, new[] { 0 }, typeof(MusicArtist).FullName);
 4697        }
 4698
 4699        public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetAlbumArtists(InternalItemsQuery query)
 4700        {
 04701            return GetItemValues(query, new[] { 1 }, typeof(MusicArtist).FullName);
 4702        }
 4703
 4704        public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetStudios(InternalItemsQuery query)
 4705        {
 04706            return GetItemValues(query, new[] { 3 }, typeof(Studio).FullName);
 4707        }
 4708
 4709        public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetGenres(InternalItemsQuery query)
 4710        {
 04711            return GetItemValues(query, new[] { 2 }, typeof(Genre).FullName);
 4712        }
 4713
 4714        public QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetMusicGenres(InternalItemsQuery query)
 4715        {
 04716            return GetItemValues(query, new[] { 2 }, typeof(MusicGenre).FullName);
 4717        }
 4718
 4719        public List<string> GetStudioNames()
 4720        {
 194721            return GetItemValueNames(new[] { 3 }, Array.Empty<string>(), Array.Empty<string>());
 4722        }
 4723
 4724        public List<string> GetAllArtistNames()
 4725        {
 194726            return GetItemValueNames(new[] { 0, 1 }, Array.Empty<string>(), Array.Empty<string>());
 4727        }
 4728
 4729        public List<string> GetMusicGenreNames()
 4730        {
 194731            return GetItemValueNames(
 194732                new[] { 2 },
 194733                new string[]
 194734                {
 194735                    typeof(Audio).FullName,
 194736                    typeof(MusicVideo).FullName,
 194737                    typeof(MusicAlbum).FullName,
 194738                    typeof(MusicArtist).FullName
 194739                },
 194740                Array.Empty<string>());
 4741        }
 4742
 4743        public List<string> GetGenreNames()
 4744        {
 194745            return GetItemValueNames(
 194746                new[] { 2 },
 194747                Array.Empty<string>(),
 194748                new string[]
 194749                {
 194750                    typeof(Audio).FullName,
 194751                    typeof(MusicVideo).FullName,
 194752                    typeof(MusicAlbum).FullName,
 194753                    typeof(MusicArtist).FullName
 194754                });
 4755        }
 4756
 4757        private List<string> GetItemValueNames(int[] itemValueTypes, IReadOnlyList<string> withItemTypes, IReadOnlyList<
 4758        {
 764759            CheckDisposed();
 4760
 764761            var stringBuilder = new StringBuilder("Select Value From ItemValues where Type", 128);
 764762            if (itemValueTypes.Length == 1)
 4763            {
 574764                stringBuilder.Append('=')
 574765                    .Append(itemValueTypes[0]);
 4766            }
 4767            else
 4768            {
 194769                stringBuilder.Append(" in (")
 194770                    .AppendJoin(',', itemValueTypes)
 194771                    .Append(')');
 4772            }
 4773
 764774            if (withItemTypes.Count > 0)
 4775            {
 194776                stringBuilder.Append(" AND ItemId In (select guid from typedbaseitems where type in (")
 194777                    .AppendJoinInSingleQuotes(',', withItemTypes)
 194778                    .Append("))");
 4779            }
 4780
 764781            if (excludeItemTypes.Count > 0)
 4782            {
 194783                stringBuilder.Append(" AND ItemId not In (select guid from typedbaseitems where type in (")
 194784                    .AppendJoinInSingleQuotes(',', excludeItemTypes)
 194785                    .Append("))");
 4786            }
 4787
 764788            stringBuilder.Append(" Group By CleanValue");
 764789            var commandText = stringBuilder.ToString();
 4790
 764791            var list = new List<string>();
 764792            using (new QueryTimeLogger(Logger, commandText))
 764793            using (var connection = GetConnection(true))
 764794            using (var statement = PrepareStatement(connection, commandText))
 4795            {
 1524796                foreach (var row in statement.ExecuteQuery())
 4797                {
 04798                    if (row.TryGetString(0, out var result))
 4799                    {
 04800                        list.Add(result);
 4801                    }
 4802                }
 4803            }
 4804
 764805            return list;
 4806        }
 4807
 4808        private QueryResult<(BaseItem Item, ItemCounts ItemCounts)> GetItemValues(InternalItemsQuery query, int[] itemVa
 4809        {
 04810            ArgumentNullException.ThrowIfNull(query);
 4811
 04812            if (!query.Limit.HasValue)
 4813            {
 04814                query.EnableTotalRecordCount = false;
 4815            }
 4816
 04817            CheckDisposed();
 4818
 04819            var typeClause = itemValueTypes.Length == 1 ?
 04820                ("Type=" + itemValueTypes[0]) :
 04821                ("Type in (" + string.Join(',', itemValueTypes) + ")");
 4822
 04823            InternalItemsQuery typeSubQuery = null;
 4824
 04825            string itemCountColumns = null;
 4826
 04827            var stringBuilder = new StringBuilder(1024);
 04828            var typesToCount = query.IncludeItemTypes;
 4829
 04830            if (typesToCount.Length > 0)
 4831            {
 04832                stringBuilder.Append("(select group_concat(type, '|') from TypedBaseItems B");
 4833
 04834                typeSubQuery = new InternalItemsQuery(query.User)
 04835                {
 04836                    ExcludeItemTypes = query.ExcludeItemTypes,
 04837                    IncludeItemTypes = query.IncludeItemTypes,
 04838                    MediaTypes = query.MediaTypes,
 04839                    AncestorIds = query.AncestorIds,
 04840                    ExcludeItemIds = query.ExcludeItemIds,
 04841                    ItemIds = query.ItemIds,
 04842                    TopParentIds = query.TopParentIds,
 04843                    ParentId = query.ParentId,
 04844                    IsPlayed = query.IsPlayed
 04845                };
 04846                var whereClauses = GetWhereClauses(typeSubQuery, null);
 4847
 04848                stringBuilder.Append(" where ")
 04849                    .AppendJoin(" AND ", whereClauses)
 04850                    .Append(" AND ")
 04851                    .Append("guid in (select ItemId from ItemValues where ItemValues.CleanValue=A.CleanName AND ")
 04852                    .Append(typeClause)
 04853                    .Append(")) as itemTypes");
 4854
 04855                itemCountColumns = stringBuilder.ToString();
 04856                stringBuilder.Clear();
 4857            }
 4858
 04859            List<string> columns = _retrieveItemColumns.ToList();
 4860            // Unfortunately we need to add it to columns to ensure the order of the columns in the select
 04861            if (!string.IsNullOrEmpty(itemCountColumns))
 4862            {
 04863                columns.Add(itemCountColumns);
 4864            }
 4865
 4866            // do this first before calling GetFinalColumnsToSelect, otherwise ExcludeItemIds will be set by SimilarTo
 04867            var innerQuery = new InternalItemsQuery(query.User)
 04868            {
 04869                ExcludeItemTypes = query.ExcludeItemTypes,
 04870                IncludeItemTypes = query.IncludeItemTypes,
 04871                MediaTypes = query.MediaTypes,
 04872                AncestorIds = query.AncestorIds,
 04873                ItemIds = query.ItemIds,
 04874                TopParentIds = query.TopParentIds,
 04875                ParentId = query.ParentId,
 04876                IsAiring = query.IsAiring,
 04877                IsMovie = query.IsMovie,
 04878                IsSports = query.IsSports,
 04879                IsKids = query.IsKids,
 04880                IsNews = query.IsNews,
 04881                IsSeries = query.IsSeries
 04882            };
 4883
 04884            SetFinalColumnsToSelect(query, columns);
 4885
 04886            var innerWhereClauses = GetWhereClauses(innerQuery, null);
 4887
 04888            stringBuilder.Append(" where Type=@SelectType And CleanName In (Select CleanValue from ItemValues where ")
 04889                .Append(typeClause)
 04890                .Append(" AND ItemId in (select guid from TypedBaseItems");
 04891            if (innerWhereClauses.Count > 0)
 4892            {
 04893                stringBuilder.Append(" where ")
 04894                    .AppendJoin(" AND ", innerWhereClauses);
 4895            }
 4896
 04897            stringBuilder.Append("))");
 4898
 04899            var outerQuery = new InternalItemsQuery(query.User)
 04900            {
 04901                IsPlayed = query.IsPlayed,
 04902                IsFavorite = query.IsFavorite,
 04903                IsFavoriteOrLiked = query.IsFavoriteOrLiked,
 04904                IsLiked = query.IsLiked,
 04905                IsLocked = query.IsLocked,
 04906                NameLessThan = query.NameLessThan,
 04907                NameStartsWith = query.NameStartsWith,
 04908                NameStartsWithOrGreater = query.NameStartsWithOrGreater,
 04909                Tags = query.Tags,
 04910                OfficialRatings = query.OfficialRatings,
 04911                StudioIds = query.StudioIds,
 04912                GenreIds = query.GenreIds,
 04913                Genres = query.Genres,
 04914                Years = query.Years,
 04915                NameContains = query.NameContains,
 04916                SearchTerm = query.SearchTerm,
 04917                SimilarTo = query.SimilarTo,
 04918                ExcludeItemIds = query.ExcludeItemIds
 04919            };
 4920
 04921            var outerWhereClauses = GetWhereClauses(outerQuery, null);
 04922            if (outerWhereClauses.Count != 0)
 4923            {
 04924                stringBuilder.Append(" AND ")
 04925                    .AppendJoin(" AND ", outerWhereClauses);
 4926            }
 4927
 04928            var whereText = stringBuilder.ToString();
 04929            stringBuilder.Clear();
 4930
 04931            stringBuilder.Append("select ")
 04932                .AppendJoin(',', columns)
 04933                .Append(FromText)
 04934                .Append(GetJoinUserDataText(query))
 04935                .Append(whereText)
 04936                .Append(" group by PresentationUniqueKey");
 4937
 04938            if (query.OrderBy.Count != 0
 04939                || query.SimilarTo is not null
 04940                || !string.IsNullOrEmpty(query.SearchTerm))
 4941            {
 04942                stringBuilder.Append(GetOrderByText(query));
 4943            }
 4944            else
 4945            {
 04946                stringBuilder.Append(" order by SortName");
 4947            }
 4948
 04949            if (query.Limit.HasValue || query.StartIndex.HasValue)
 4950            {
 04951                var offset = query.StartIndex ?? 0;
 4952
 04953                if (query.Limit.HasValue || offset > 0)
 4954                {
 04955                    stringBuilder.Append(" LIMIT ")
 04956                        .Append(query.Limit ?? int.MaxValue);
 4957                }
 4958
 04959                if (offset > 0)
 4960                {
 04961                    stringBuilder.Append(" OFFSET ")
 04962                        .Append(offset);
 4963                }
 4964            }
 4965
 04966            var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
 4967
 04968            string commandText = string.Empty;
 4969
 04970            if (!isReturningZeroItems)
 4971            {
 04972                commandText = stringBuilder.ToString();
 4973            }
 4974
 04975            string countText = string.Empty;
 04976            if (query.EnableTotalRecordCount)
 4977            {
 04978                stringBuilder.Clear();
 04979                var columnsToSelect = new List<string> { "count (distinct PresentationUniqueKey)" };
 04980                SetFinalColumnsToSelect(query, columnsToSelect);
 04981                stringBuilder.Append("select ")
 04982                    .AppendJoin(',', columnsToSelect)
 04983                    .Append(FromText)
 04984                    .Append(GetJoinUserDataText(query))
 04985                    .Append(whereText);
 4986
 04987                countText = stringBuilder.ToString();
 4988            }
 4989
 04990            var list = new List<(BaseItem, ItemCounts)>();
 04991            var result = new QueryResult<(BaseItem, ItemCounts)>();
 04992            using (new QueryTimeLogger(Logger, commandText))
 04993            using (var connection = GetConnection(true))
 04994            using (var transaction = connection.BeginTransaction())
 4995            {
 04996                if (!isReturningZeroItems)
 4997                {
 04998                    using (var statement = PrepareStatement(connection, commandText))
 4999                    {
 05000                        statement.TryBind("@SelectType", returnType);
 05001                        if (EnableJoinUserData(query))
 5002                        {
 05003                            statement.TryBind("@UserId", query.User.InternalId);
 5004                        }
 5005
 05006                        if (typeSubQuery is not null)
 5007                        {
 05008                            GetWhereClauses(typeSubQuery, null);
 5009                        }
 5010
 05011                        BindSimilarParams(query, statement);
 05012                        BindSearchParams(query, statement);
 05013                        GetWhereClauses(innerQuery, statement);
 05014                        GetWhereClauses(outerQuery, statement);
 5015
 05016                        var hasEpisodeAttributes = HasEpisodeAttributes(query);
 05017                        var hasProgramAttributes = HasProgramAttributes(query);
 05018                        var hasServiceName = HasServiceName(query);
 05019                        var hasStartDate = HasStartDate(query);
 05020                        var hasTrailerTypes = HasTrailerTypes(query);
 05021                        var hasArtistFields = HasArtistFields(query);
 05022                        var hasSeriesFields = HasSeriesFields(query);
 5023
 05024                        foreach (var row in statement.ExecuteQuery())
 5025                        {
 05026                            var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, h
 05027                            if (item is not null)
 5028                            {
 05029                                var countStartColumn = columns.Count - 1;
 5030
 05031                                list.Add((item, GetItemCounts(row, countStartColumn, typesToCount)));
 5032                            }
 5033                        }
 5034                    }
 5035                }
 5036
 05037                if (query.EnableTotalRecordCount)
 5038                {
 05039                    using (var statement = PrepareStatement(connection, countText))
 5040                    {
 05041                        statement.TryBind("@SelectType", returnType);
 05042                        if (EnableJoinUserData(query))
 5043                        {
 05044                            statement.TryBind("@UserId", query.User.InternalId);
 5045                        }
 5046
 05047                        if (typeSubQuery is not null)
 5048                        {
 05049                            GetWhereClauses(typeSubQuery, null);
 5050                        }
 5051
 05052                        BindSimilarParams(query, statement);
 05053                        BindSearchParams(query, statement);
 05054                        GetWhereClauses(innerQuery, statement);
 05055                        GetWhereClauses(outerQuery, statement);
 5056
 05057                        result.TotalRecordCount = statement.SelectScalarInt();
 05058                    }
 5059                }
 5060
 05061                transaction.Commit();
 05062            }
 5063
 05064            if (result.TotalRecordCount == 0)
 5065            {
 05066                result.TotalRecordCount = list.Count;
 5067            }
 5068
 05069            result.StartIndex = query.StartIndex ?? 0;
 05070            result.Items = list;
 5071
 05072            return result;
 5073        }
 5074
 5075        private static ItemCounts GetItemCounts(SqliteDataReader reader, int countStartColumn, BaseItemKind[] typesToCou
 5076        {
 05077            var counts = new ItemCounts();
 5078
 05079            if (typesToCount.Length == 0)
 5080            {
 05081                return counts;
 5082            }
 5083
 05084            if (!reader.TryGetString(countStartColumn, out var typeString))
 5085            {
 05086                return counts;
 5087            }
 5088
 05089            foreach (var typeName in typeString.AsSpan().Split('|'))
 5090            {
 05091                if (typeName.Equals(typeof(Series).FullName, StringComparison.OrdinalIgnoreCase))
 5092                {
 05093                    counts.SeriesCount++;
 5094                }
 05095                else if (typeName.Equals(typeof(Episode).FullName, StringComparison.OrdinalIgnoreCase))
 5096                {
 05097                    counts.EpisodeCount++;
 5098                }
 05099                else if (typeName.Equals(typeof(Movie).FullName, StringComparison.OrdinalIgnoreCase))
 5100                {
 05101                    counts.MovieCount++;
 5102                }
 05103                else if (typeName.Equals(typeof(MusicAlbum).FullName, StringComparison.OrdinalIgnoreCase))
 5104                {
 05105                    counts.AlbumCount++;
 5106                }
 05107                else if (typeName.Equals(typeof(MusicArtist).FullName, StringComparison.OrdinalIgnoreCase))
 5108                {
 05109                    counts.ArtistCount++;
 5110                }
 05111                else if (typeName.Equals(typeof(Audio).FullName, StringComparison.OrdinalIgnoreCase))
 5112                {
 05113                    counts.SongCount++;
 5114                }
 05115                else if (typeName.Equals(typeof(Trailer).FullName, StringComparison.OrdinalIgnoreCase))
 5116                {
 05117                    counts.TrailerCount++;
 5118                }
 5119
 05120                counts.ItemCount++;
 5121            }
 5122
 05123            return counts;
 5124        }
 5125
 5126        private List<(int MagicNumber, string Value)> GetItemValuesToSave(BaseItem item, List<string> inheritedTags)
 5127        {
 595128            var list = new List<(int, string)>();
 5129
 595130            if (item is IHasArtist hasArtist)
 5131            {
 05132                list.AddRange(hasArtist.Artists.Select(i => (0, i)));
 5133            }
 5134
 595135            if (item is IHasAlbumArtist hasAlbumArtist)
 5136            {
 05137                list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => (1, i)));
 5138            }
 5139
 595140            list.AddRange(item.Genres.Select(i => (2, i)));
 595141            list.AddRange(item.Studios.Select(i => (3, i)));
 595142            list.AddRange(item.Tags.Select(i => (4, i)));
 5143
 5144            // keywords was 5
 5145
 595146            list.AddRange(inheritedTags.Select(i => (6, i)));
 5147
 5148            // Remove all invalid values.
 595149            list.RemoveAll(i => string.IsNullOrWhiteSpace(i.Item2));
 5150
 595151            return list;
 5152        }
 5153
 5154        private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, ManagedConnection db)
 5155        {
 595156            if (itemId.IsEmpty())
 5157            {
 05158                throw new ArgumentNullException(nameof(itemId));
 5159            }
 5160
 595161            ArgumentNullException.ThrowIfNull(values);
 5162
 595163            CheckDisposed();
 5164
 5165            // First delete
 595166            using var command = db.PrepareStatement("delete from ItemValues where ItemId=@Id");
 595167            command.TryBind("@Id", itemId);
 595168            command.ExecuteNonQuery();
 5169
 595170            InsertItemValues(itemId, values, db);
 1185171        }
 5172
 5173        private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, ManagedConnection db)
 5174        {
 5175            const int Limit = 100;
 595176            var startIndex = 0;
 5177
 5178            const string StartInsertText = "insert into ItemValues (ItemId, Type, Value, CleanValue) values ";
 595179            var insertText = new StringBuilder(StartInsertText);
 595180            while (startIndex < values.Count)
 5181            {
 05182                var endIndex = Math.Min(values.Count, startIndex + Limit);
 5183
 05184                for (var i = startIndex; i < endIndex; i++)
 5185                {
 05186                    insertText.AppendFormat(
 05187                        CultureInfo.InvariantCulture,
 05188                        "(@ItemId, @Type{0}, @Value{0}, @CleanValue{0}),",
 05189                        i);
 5190                }
 5191
 5192                // Remove trailing comma
 05193                insertText.Length--;
 5194
 05195                using (var statement = PrepareStatement(db, insertText.ToString()))
 5196                {
 05197                    statement.TryBind("@ItemId", id);
 5198
 05199                    for (var i = startIndex; i < endIndex; i++)
 5200                    {
 05201                        var index = i.ToString(CultureInfo.InvariantCulture);
 5202
 05203                        var currentValueInfo = values[i];
 5204
 05205                        var itemValue = currentValueInfo.Value;
 5206
 05207                        statement.TryBind("@Type" + index, currentValueInfo.MagicNumber);
 05208                        statement.TryBind("@Value" + index, itemValue);
 05209                        statement.TryBind("@CleanValue" + index, GetCleanValue(itemValue));
 5210                    }
 5211
 05212                    statement.ExecuteNonQuery();
 05213                }
 5214
 05215                startIndex += Limit;
 05216                insertText.Length = StartInsertText.Length;
 5217            }
 595218        }
 5219
 5220        public void UpdatePeople(Guid itemId, List<PersonInfo> people)
 5221        {
 05222            if (itemId.IsEmpty())
 5223            {
 05224                throw new ArgumentNullException(nameof(itemId));
 5225            }
 5226
 05227            CheckDisposed();
 5228
 05229            using var connection = GetConnection();
 05230            using var transaction = connection.BeginTransaction();
 5231            // Delete all existing people first
 05232            using var command = connection.CreateCommand();
 05233            command.CommandText = "delete from People where ItemId=@ItemId";
 05234            command.TryBind("@ItemId", itemId);
 05235            command.ExecuteNonQuery();
 5236
 05237            if (people is not null)
 5238            {
 05239                InsertPeople(itemId, people, connection);
 5240            }
 5241
 05242            transaction.Commit();
 05243        }
 5244
 5245        private void InsertPeople(Guid id, List<PersonInfo> people, ManagedConnection db)
 5246        {
 5247            const int Limit = 100;
 05248            var startIndex = 0;
 05249            var listIndex = 0;
 5250
 5251            const string StartInsertText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) va
 05252            var insertText = new StringBuilder(StartInsertText);
 05253            while (startIndex < people.Count)
 5254            {
 05255                var endIndex = Math.Min(people.Count, startIndex + Limit);
 05256                for (var i = startIndex; i < endIndex; i++)
 5257                {
 05258                    insertText.AppendFormat(
 05259                        CultureInfo.InvariantCulture,
 05260                        "(@ItemId, @Name{0}, @Role{0}, @PersonType{0}, @SortOrder{0}, @ListOrder{0}),",
 05261                        i.ToString(CultureInfo.InvariantCulture));
 5262                }
 5263
 5264                // Remove trailing comma
 05265                insertText.Length--;
 5266
 05267                using (var statement = PrepareStatement(db, insertText.ToString()))
 5268                {
 05269                    statement.TryBind("@ItemId", id);
 5270
 05271                    for (var i = startIndex; i < endIndex; i++)
 5272                    {
 05273                        var index = i.ToString(CultureInfo.InvariantCulture);
 5274
 05275                        var person = people[i];
 5276
 05277                        statement.TryBind("@Name" + index, person.Name);
 05278                        statement.TryBind("@Role" + index, person.Role);
 05279                        statement.TryBind("@PersonType" + index, person.Type.ToString());
 05280                        statement.TryBind("@SortOrder" + index, person.SortOrder);
 05281                        statement.TryBind("@ListOrder" + index, listIndex);
 5282
 05283                        listIndex++;
 5284                    }
 5285
 05286                    statement.ExecuteNonQuery();
 05287                }
 5288
 05289                startIndex += Limit;
 05290                insertText.Length = StartInsertText.Length;
 5291            }
 05292        }
 5293
 5294        private PersonInfo GetPerson(SqliteDataReader reader)
 5295        {
 05296            var item = new PersonInfo
 05297            {
 05298                ItemId = reader.GetGuid(0),
 05299                Name = reader.GetString(1)
 05300            };
 5301
 05302            if (reader.TryGetString(2, out var role))
 5303            {
 05304                item.Role = role;
 5305            }
 5306
 05307            if (reader.TryGetString(3, out var type)
 05308                && Enum.TryParse(type, true, out PersonKind personKind))
 5309            {
 05310                item.Type = personKind;
 5311            }
 5312
 05313            if (reader.TryGetInt32(4, out var sortOrder))
 5314            {
 05315                item.SortOrder = sortOrder;
 5316            }
 5317
 05318            return item;
 5319        }
 5320
 5321        public List<MediaStream> GetMediaStreams(MediaStreamQuery query)
 5322        {
 05323            CheckDisposed();
 5324
 05325            ArgumentNullException.ThrowIfNull(query);
 5326
 05327            var cmdText = _mediaStreamSaveColumnsSelectQuery;
 5328
 05329            if (query.Type.HasValue)
 5330            {
 05331                cmdText += " AND StreamType=@StreamType";
 5332            }
 5333
 05334            if (query.Index.HasValue)
 5335            {
 05336                cmdText += " AND StreamIndex=@StreamIndex";
 5337            }
 5338
 05339            cmdText += " order by StreamIndex ASC";
 5340
 05341            using (var connection = GetConnection(true))
 5342            {
 05343                var list = new List<MediaStream>();
 5344
 05345                using (var statement = PrepareStatement(connection, cmdText))
 5346                {
 05347                    statement.TryBind("@ItemId", query.ItemId);
 5348
 05349                    if (query.Type.HasValue)
 5350                    {
 05351                        statement.TryBind("@StreamType", query.Type.Value.ToString());
 5352                    }
 5353
 05354                    if (query.Index.HasValue)
 5355                    {
 05356                        statement.TryBind("@StreamIndex", query.Index.Value);
 5357                    }
 5358
 05359                    foreach (var row in statement.ExecuteQuery())
 5360                    {
 05361                        list.Add(GetMediaStream(row));
 5362                    }
 5363                }
 5364
 05365                return list;
 5366            }
 05367        }
 5368
 5369        public void SaveMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, CancellationToken cancellationToken)
 5370        {
 05371            CheckDisposed();
 5372
 05373            if (id.IsEmpty())
 5374            {
 05375                throw new ArgumentNullException(nameof(id));
 5376            }
 5377
 05378            ArgumentNullException.ThrowIfNull(streams);
 5379
 05380            cancellationToken.ThrowIfCancellationRequested();
 5381
 05382            using var connection = GetConnection();
 05383            using var transaction = connection.BeginTransaction();
 5384            // Delete existing mediastreams
 05385            using var command = connection.PrepareStatement("delete from mediastreams where ItemId=@ItemId");
 05386            command.TryBind("@ItemId", id);
 05387            command.ExecuteNonQuery();
 5388
 05389            InsertMediaStreams(id, streams, connection);
 5390
 05391            transaction.Commit();
 05392        }
 5393
 5394        private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, ManagedConnection db)
 5395        {
 5396            const int Limit = 10;
 05397            var startIndex = 0;
 5398
 05399            var insertText = new StringBuilder(_mediaStreamSaveColumnsInsertQuery);
 05400            while (startIndex < streams.Count)
 5401            {
 05402                var endIndex = Math.Min(streams.Count, startIndex + Limit);
 5403
 05404                for (var i = startIndex; i < endIndex; i++)
 5405                {
 05406                    if (i != startIndex)
 5407                    {
 05408                        insertText.Append(',');
 5409                    }
 5410
 05411                    var index = i.ToString(CultureInfo.InvariantCulture);
 05412                    insertText.Append("(@ItemId, ");
 5413
 05414                    foreach (var column in _mediaStreamSaveColumns.Skip(1))
 5415                    {
 05416                        insertText.Append('@').Append(column).Append(index).Append(',');
 5417                    }
 5418
 05419                    insertText.Length -= 1; // Remove the last comma
 5420
 05421                    insertText.Append(')');
 5422                }
 5423
 05424                using (var statement = PrepareStatement(db, insertText.ToString()))
 5425                {
 05426                    statement.TryBind("@ItemId", id);
 5427
 05428                    for (var i = startIndex; i < endIndex; i++)
 5429                    {
 05430                        var index = i.ToString(CultureInfo.InvariantCulture);
 5431
 05432                        var stream = streams[i];
 5433
 05434                        statement.TryBind("@StreamIndex" + index, stream.Index);
 05435                        statement.TryBind("@StreamType" + index, stream.Type.ToString());
 05436                        statement.TryBind("@Codec" + index, stream.Codec);
 05437                        statement.TryBind("@Language" + index, stream.Language);
 05438                        statement.TryBind("@ChannelLayout" + index, stream.ChannelLayout);
 05439                        statement.TryBind("@Profile" + index, stream.Profile);
 05440                        statement.TryBind("@AspectRatio" + index, stream.AspectRatio);
 05441                        statement.TryBind("@Path" + index, GetPathToSave(stream.Path));
 5442
 05443                        statement.TryBind("@IsInterlaced" + index, stream.IsInterlaced);
 05444                        statement.TryBind("@BitRate" + index, stream.BitRate);
 05445                        statement.TryBind("@Channels" + index, stream.Channels);
 05446                        statement.TryBind("@SampleRate" + index, stream.SampleRate);
 5447
 05448                        statement.TryBind("@IsDefault" + index, stream.IsDefault);
 05449                        statement.TryBind("@IsForced" + index, stream.IsForced);
 05450                        statement.TryBind("@IsExternal" + index, stream.IsExternal);
 5451
 5452                        // Yes these are backwards due to a mistake
 05453                        statement.TryBind("@Width" + index, stream.Height);
 05454                        statement.TryBind("@Height" + index, stream.Width);
 5455
 05456                        statement.TryBind("@AverageFrameRate" + index, stream.AverageFrameRate);
 05457                        statement.TryBind("@RealFrameRate" + index, stream.RealFrameRate);
 05458                        statement.TryBind("@Level" + index, stream.Level);
 5459
 05460                        statement.TryBind("@PixelFormat" + index, stream.PixelFormat);
 05461                        statement.TryBind("@BitDepth" + index, stream.BitDepth);
 05462                        statement.TryBind("@IsAnamorphic" + index, stream.IsAnamorphic);
 05463                        statement.TryBind("@IsExternal" + index, stream.IsExternal);
 05464                        statement.TryBind("@RefFrames" + index, stream.RefFrames);
 5465
 05466                        statement.TryBind("@CodecTag" + index, stream.CodecTag);
 05467                        statement.TryBind("@Comment" + index, stream.Comment);
 05468                        statement.TryBind("@NalLengthSize" + index, stream.NalLengthSize);
 05469                        statement.TryBind("@IsAvc" + index, stream.IsAVC);
 05470                        statement.TryBind("@Title" + index, stream.Title);
 5471
 05472                        statement.TryBind("@TimeBase" + index, stream.TimeBase);
 05473                        statement.TryBind("@CodecTimeBase" + index, stream.CodecTimeBase);
 5474
 05475                        statement.TryBind("@ColorPrimaries" + index, stream.ColorPrimaries);
 05476                        statement.TryBind("@ColorSpace" + index, stream.ColorSpace);
 05477                        statement.TryBind("@ColorTransfer" + index, stream.ColorTransfer);
 5478
 05479                        statement.TryBind("@DvVersionMajor" + index, stream.DvVersionMajor);
 05480                        statement.TryBind("@DvVersionMinor" + index, stream.DvVersionMinor);
 05481                        statement.TryBind("@DvProfile" + index, stream.DvProfile);
 05482                        statement.TryBind("@DvLevel" + index, stream.DvLevel);
 05483                        statement.TryBind("@RpuPresentFlag" + index, stream.RpuPresentFlag);
 05484                        statement.TryBind("@ElPresentFlag" + index, stream.ElPresentFlag);
 05485                        statement.TryBind("@BlPresentFlag" + index, stream.BlPresentFlag);
 05486                        statement.TryBind("@DvBlSignalCompatibilityId" + index, stream.DvBlSignalCompatibilityId);
 5487
 05488                        statement.TryBind("@IsHearingImpaired" + index, stream.IsHearingImpaired);
 5489
 05490                        statement.TryBind("@Rotation" + index, stream.Rotation);
 5491                    }
 5492
 05493                    statement.ExecuteNonQuery();
 05494                }
 5495
 05496                startIndex += Limit;
 05497                insertText.Length = _mediaStreamSaveColumnsInsertQuery.Length;
 5498            }
 05499        }
 5500
 5501        /// <summary>
 5502        /// Gets the media stream.
 5503        /// </summary>
 5504        /// <param name="reader">The reader.</param>
 5505        /// <returns>MediaStream.</returns>
 5506        private MediaStream GetMediaStream(SqliteDataReader reader)
 5507        {
 05508            var item = new MediaStream
 05509            {
 05510                Index = reader.GetInt32(1),
 05511                Type = Enum.Parse<MediaStreamType>(reader.GetString(2), true)
 05512            };
 5513
 05514            if (reader.TryGetString(3, out var codec))
 5515            {
 05516                item.Codec = codec;
 5517            }
 5518
 05519            if (reader.TryGetString(4, out var language))
 5520            {
 05521                item.Language = language;
 5522            }
 5523
 05524            if (reader.TryGetString(5, out var channelLayout))
 5525            {
 05526                item.ChannelLayout = channelLayout;
 5527            }
 5528
 05529            if (reader.TryGetString(6, out var profile))
 5530            {
 05531                item.Profile = profile;
 5532            }
 5533
 05534            if (reader.TryGetString(7, out var aspectRatio))
 5535            {
 05536                item.AspectRatio = aspectRatio;
 5537            }
 5538
 05539            if (reader.TryGetString(8, out var path))
 5540            {
 05541                item.Path = RestorePath(path);
 5542            }
 5543
 05544            item.IsInterlaced = reader.GetBoolean(9);
 5545
 05546            if (reader.TryGetInt32(10, out var bitrate))
 5547            {
 05548                item.BitRate = bitrate;
 5549            }
 5550
 05551            if (reader.TryGetInt32(11, out var channels))
 5552            {
 05553                item.Channels = channels;
 5554            }
 5555
 05556            if (reader.TryGetInt32(12, out var sampleRate))
 5557            {
 05558                item.SampleRate = sampleRate;
 5559            }
 5560
 05561            item.IsDefault = reader.GetBoolean(13);
 05562            item.IsForced = reader.GetBoolean(14);
 05563            item.IsExternal = reader.GetBoolean(15);
 5564
 05565            if (reader.TryGetInt32(16, out var width))
 5566            {
 05567                item.Width = width;
 5568            }
 5569
 05570            if (reader.TryGetInt32(17, out var height))
 5571            {
 05572                item.Height = height;
 5573            }
 5574
 05575            if (reader.TryGetSingle(18, out var averageFrameRate))
 5576            {
 05577                item.AverageFrameRate = averageFrameRate;
 5578            }
 5579
 05580            if (reader.TryGetSingle(19, out var realFrameRate))
 5581            {
 05582                item.RealFrameRate = realFrameRate;
 5583            }
 5584
 05585            if (reader.TryGetSingle(20, out var level))
 5586            {
 05587                item.Level = level;
 5588            }
 5589
 05590            if (reader.TryGetString(21, out var pixelFormat))
 5591            {
 05592                item.PixelFormat = pixelFormat;
 5593            }
 5594
 05595            if (reader.TryGetInt32(22, out var bitDepth))
 5596            {
 05597                item.BitDepth = bitDepth;
 5598            }
 5599
 05600            if (reader.TryGetBoolean(23, out var isAnamorphic))
 5601            {
 05602                item.IsAnamorphic = isAnamorphic;
 5603            }
 5604
 05605            if (reader.TryGetInt32(24, out var refFrames))
 5606            {
 05607                item.RefFrames = refFrames;
 5608            }
 5609
 05610            if (reader.TryGetString(25, out var codecTag))
 5611            {
 05612                item.CodecTag = codecTag;
 5613            }
 5614
 05615            if (reader.TryGetString(26, out var comment))
 5616            {
 05617                item.Comment = comment;
 5618            }
 5619
 05620            if (reader.TryGetString(27, out var nalLengthSize))
 5621            {
 05622                item.NalLengthSize = nalLengthSize;
 5623            }
 5624
 05625            if (reader.TryGetBoolean(28, out var isAVC))
 5626            {
 05627                item.IsAVC = isAVC;
 5628            }
 5629
 05630            if (reader.TryGetString(29, out var title))
 5631            {
 05632                item.Title = title;
 5633            }
 5634
 05635            if (reader.TryGetString(30, out var timeBase))
 5636            {
 05637                item.TimeBase = timeBase;
 5638            }
 5639
 05640            if (reader.TryGetString(31, out var codecTimeBase))
 5641            {
 05642                item.CodecTimeBase = codecTimeBase;
 5643            }
 5644
 05645            if (reader.TryGetString(32, out var colorPrimaries))
 5646            {
 05647                item.ColorPrimaries = colorPrimaries;
 5648            }
 5649
 05650            if (reader.TryGetString(33, out var colorSpace))
 5651            {
 05652                item.ColorSpace = colorSpace;
 5653            }
 5654
 05655            if (reader.TryGetString(34, out var colorTransfer))
 5656            {
 05657                item.ColorTransfer = colorTransfer;
 5658            }
 5659
 05660            if (reader.TryGetInt32(35, out var dvVersionMajor))
 5661            {
 05662                item.DvVersionMajor = dvVersionMajor;
 5663            }
 5664
 05665            if (reader.TryGetInt32(36, out var dvVersionMinor))
 5666            {
 05667                item.DvVersionMinor = dvVersionMinor;
 5668            }
 5669
 05670            if (reader.TryGetInt32(37, out var dvProfile))
 5671            {
 05672                item.DvProfile = dvProfile;
 5673            }
 5674
 05675            if (reader.TryGetInt32(38, out var dvLevel))
 5676            {
 05677                item.DvLevel = dvLevel;
 5678            }
 5679
 05680            if (reader.TryGetInt32(39, out var rpuPresentFlag))
 5681            {
 05682                item.RpuPresentFlag = rpuPresentFlag;
 5683            }
 5684
 05685            if (reader.TryGetInt32(40, out var elPresentFlag))
 5686            {
 05687                item.ElPresentFlag = elPresentFlag;
 5688            }
 5689
 05690            if (reader.TryGetInt32(41, out var blPresentFlag))
 5691            {
 05692                item.BlPresentFlag = blPresentFlag;
 5693            }
 5694
 05695            if (reader.TryGetInt32(42, out var dvBlSignalCompatibilityId))
 5696            {
 05697                item.DvBlSignalCompatibilityId = dvBlSignalCompatibilityId;
 5698            }
 5699
 05700            item.IsHearingImpaired = reader.TryGetBoolean(43, out var result) && result;
 5701
 05702            if (reader.TryGetInt32(44, out var rotation))
 5703            {
 05704                item.Rotation = rotation;
 5705            }
 5706
 05707            if (item.Type is MediaStreamType.Audio or MediaStreamType.Subtitle)
 5708            {
 05709                item.LocalizedDefault = _localization.GetLocalizedString("Default");
 05710                item.LocalizedExternal = _localization.GetLocalizedString("External");
 5711
 05712                if (item.Type is MediaStreamType.Subtitle)
 5713                {
 05714                    item.LocalizedUndefined = _localization.GetLocalizedString("Undefined");
 05715                    item.LocalizedForced = _localization.GetLocalizedString("Forced");
 05716                    item.LocalizedHearingImpaired = _localization.GetLocalizedString("HearingImpaired");
 5717                }
 5718            }
 5719
 05720            return item;
 5721        }
 5722
 5723        public List<MediaAttachment> GetMediaAttachments(MediaAttachmentQuery query)
 5724        {
 05725            CheckDisposed();
 5726
 05727            ArgumentNullException.ThrowIfNull(query);
 5728
 05729            var cmdText = _mediaAttachmentSaveColumnsSelectQuery;
 5730
 05731            if (query.Index.HasValue)
 5732            {
 05733                cmdText += " AND AttachmentIndex=@AttachmentIndex";
 5734            }
 5735
 05736            cmdText += " order by AttachmentIndex ASC";
 5737
 05738            var list = new List<MediaAttachment>();
 05739            using (var connection = GetConnection(true))
 05740            using (var statement = PrepareStatement(connection, cmdText))
 5741            {
 05742                statement.TryBind("@ItemId", query.ItemId);
 5743
 05744                if (query.Index.HasValue)
 5745                {
 05746                    statement.TryBind("@AttachmentIndex", query.Index.Value);
 5747                }
 5748
 05749                foreach (var row in statement.ExecuteQuery())
 5750                {
 05751                    list.Add(GetMediaAttachment(row));
 5752                }
 5753            }
 5754
 05755            return list;
 5756        }
 5757
 5758        public void SaveMediaAttachments(
 5759            Guid id,
 5760            IReadOnlyList<MediaAttachment> attachments,
 5761            CancellationToken cancellationToken)
 5762        {
 05763            CheckDisposed();
 05764            if (id.IsEmpty())
 5765            {
 05766                throw new ArgumentException("Guid can't be empty.", nameof(id));
 5767            }
 5768
 05769            ArgumentNullException.ThrowIfNull(attachments);
 5770
 05771            cancellationToken.ThrowIfCancellationRequested();
 5772
 05773            using (var connection = GetConnection())
 05774            using (var transaction = connection.BeginTransaction())
 05775            using (var command = connection.PrepareStatement("delete from mediaattachments where ItemId=@ItemId"))
 5776            {
 05777                command.TryBind("@ItemId", id);
 05778                command.ExecuteNonQuery();
 5779
 05780                InsertMediaAttachments(id, attachments, connection, cancellationToken);
 5781
 05782                transaction.Commit();
 05783            }
 05784        }
 5785
 5786        private void InsertMediaAttachments(
 5787            Guid id,
 5788            IReadOnlyList<MediaAttachment> attachments,
 5789            ManagedConnection db,
 5790            CancellationToken cancellationToken)
 5791        {
 5792            const int InsertAtOnce = 10;
 5793
 05794            var insertText = new StringBuilder(_mediaAttachmentInsertPrefix);
 05795            for (var startIndex = 0; startIndex < attachments.Count; startIndex += InsertAtOnce)
 5796            {
 05797                var endIndex = Math.Min(attachments.Count, startIndex + InsertAtOnce);
 5798
 05799                for (var i = startIndex; i < endIndex; i++)
 5800                {
 05801                    insertText.Append("(@ItemId, ");
 5802
 05803                    foreach (var column in _mediaAttachmentSaveColumns.Skip(1))
 5804                    {
 05805                        insertText.Append('@')
 05806                            .Append(column)
 05807                            .Append(i)
 05808                            .Append(',');
 5809                    }
 5810
 05811                    insertText.Length -= 1;
 5812
 05813                    insertText.Append("),");
 5814                }
 5815
 05816                insertText.Length--;
 5817
 05818                cancellationToken.ThrowIfCancellationRequested();
 5819
 05820                using (var statement = PrepareStatement(db, insertText.ToString()))
 5821                {
 05822                    statement.TryBind("@ItemId", id);
 5823
 05824                    for (var i = startIndex; i < endIndex; i++)
 5825                    {
 05826                        var index = i.ToString(CultureInfo.InvariantCulture);
 5827
 05828                        var attachment = attachments[i];
 5829
 05830                        statement.TryBind("@AttachmentIndex" + index, attachment.Index);
 05831                        statement.TryBind("@Codec" + index, attachment.Codec);
 05832                        statement.TryBind("@CodecTag" + index, attachment.CodecTag);
 05833                        statement.TryBind("@Comment" + index, attachment.Comment);
 05834                        statement.TryBind("@Filename" + index, attachment.FileName);
 05835                        statement.TryBind("@MIMEType" + index, attachment.MimeType);
 5836                    }
 5837
 05838                    statement.ExecuteNonQuery();
 05839                }
 5840
 05841                insertText.Length = _mediaAttachmentInsertPrefix.Length;
 5842            }
 05843        }
 5844
 5845        /// <summary>
 5846        /// Gets the attachment.
 5847        /// </summary>
 5848        /// <param name="reader">The reader.</param>
 5849        /// <returns>MediaAttachment.</returns>
 5850        private MediaAttachment GetMediaAttachment(SqliteDataReader reader)
 5851        {
 05852            var item = new MediaAttachment
 05853            {
 05854                Index = reader.GetInt32(1)
 05855            };
 5856
 05857            if (reader.TryGetString(2, out var codec))
 5858            {
 05859                item.Codec = codec;
 5860            }
 5861
 05862            if (reader.TryGetString(3, out var codecTag))
 5863            {
 05864                item.CodecTag = codecTag;
 5865            }
 5866
 05867            if (reader.TryGetString(4, out var comment))
 5868            {
 05869                item.Comment = comment;
 5870            }
 5871
 05872            if (reader.TryGetString(5, out var fileName))
 5873            {
 05874                item.FileName = fileName;
 5875            }
 5876
 05877            if (reader.TryGetString(6, out var mimeType))
 5878            {
 05879                item.MimeType = mimeType;
 5880            }
 5881
 05882            return item;
 5883        }
 5884
 5885        private static string BuildMediaAttachmentInsertPrefix()
 5886        {
 15887            var queryPrefixText = new StringBuilder();
 15888            queryPrefixText.Append("insert into mediaattachments (");
 165889            foreach (var column in _mediaAttachmentSaveColumns)
 5890            {
 75891                queryPrefixText.Append(column)
 75892                    .Append(',');
 5893            }
 5894
 15895            queryPrefixText.Length -= 1;
 15896            queryPrefixText.Append(") values ");
 15897            return queryPrefixText.ToString();
 5898        }
 5899
 5900#nullable enable
 5901
 5902        private readonly struct QueryTimeLogger : IDisposable
 5903        {
 5904            private readonly ILogger _logger;
 5905            private readonly string _commandText;
 5906            private readonly string _methodName;
 5907            private readonly long _startTimestamp;
 5908
 5909            public QueryTimeLogger(ILogger logger, string commandText, [CallerMemberName] string methodName = "")
 5910            {
 4125911                _logger = logger;
 4125912                _commandText = commandText;
 4125913                _methodName = methodName;
 4125914                _startTimestamp = logger.IsEnabled(LogLevel.Debug) ? Stopwatch.GetTimestamp() : -1;
 4125915            }
 5916
 5917            public void Dispose()
 5918            {
 4125919                if (_startTimestamp == -1)
 5920                {
 4125921                    return;
 5922                }
 5923
 05924                var elapsedMs = Stopwatch.GetElapsedTime(_startTimestamp).TotalMilliseconds;
 5925
 5926#if DEBUG
 5927                const int SlowThreshold = 100;
 5928#else
 5929                const int SlowThreshold = 10;
 5930#endif
 5931
 05932                if (elapsedMs >= SlowThreshold)
 5933                {
 05934                    _logger.LogDebug(
 05935                        "{Method} query time (slow): {ElapsedMs}ms. Query: {Query}",
 05936                        _methodName,
 05937                        elapsedMs,
 05938                        _commandText);
 5939                }
 05940            }
 5941        }
 5942    }
 5943}
Generated by: ReportGenerator 5.3.8.0
9/19/2024 - 12:16:35 AM
GitHub | reportgenerator.io

Methods/Properties

.ctor(MediaBrowser.Controller.Configuration.IServerConfigurationManager,MediaBrowser.Controller.IServerApplicationHost,Microsoft.Extensions.Logging.ILogger`1<Emby.Server.Implementations.Data.SqliteItemRepository>,MediaBrowser.Model.Globalization.ILocalizationManager,MediaBrowser.Controller.Drawing.IImageProcessor,Microsoft.Extensions.Configuration.IConfiguration)
.cctor()
get_TempStore()
Initialize()
SaveImages(MediaBrowser.Controller.Entities.BaseItem)
SaveItems(System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Controller.Entities.BaseItem>,System.Threading.CancellationToken)
SaveItemsInTransaction(Emby.Server.Implementations.Data.ManagedConnection,System.Collections.Generic.IEnumerable`1<System.ValueTuple`5<MediaBrowser.Controller.Entities.BaseItem,System.Collections.Generic.List`1<System.Guid>,MediaBrowser.Controller.Entities.BaseItem,System.String,System.Collections.Generic.List`1<System.String>>>)
GetPathToSave(System.String)
RestorePath(System.String)
SaveItem(MediaBrowser.Controller.Entities.BaseItem,MediaBrowser.Controller.Entities.BaseItem,System.String,Microsoft.Data.Sqlite.SqliteCommand)
SerializeProviderIds(System.Collections.Generic.Dictionary`2<System.String,System.String>)
DeserializeProviderIds(System.String,MediaBrowser.Model.Entities.IHasProviderIds)
SerializeImages(MediaBrowser.Controller.Entities.ItemImageInfo[])
DeserializeImages(System.String)
AppendItemImageInfo(System.Text.StringBuilder,MediaBrowser.Controller.Entities.ItemImageInfo)
ItemImageInfoFromValueString(System.ReadOnlySpan`1<System.Char>)
RetrieveItem(System.Guid)
TypeRequiresDeserialization(System.Type)
GetItem(Microsoft.Data.Sqlite.SqliteDataReader,MediaBrowser.Controller.Entities.InternalItemsQuery)
GetItem(Microsoft.Data.Sqlite.SqliteDataReader,MediaBrowser.Controller.Entities.InternalItemsQuery,System.Boolean,System.Boolean,System.Boolean,System.Boolean,System.Boolean,System.Boolean,System.Boolean,System.Boolean)
SplitToGuids(System.String)
GetChapters(MediaBrowser.Controller.Entities.BaseItem)
GetChapter(MediaBrowser.Controller.Entities.BaseItem,System.Int32)
GetChapter(Microsoft.Data.Sqlite.SqliteDataReader,MediaBrowser.Controller.Entities.BaseItem)
SaveChapters(System.Guid,System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Model.Entities.ChapterInfo>)
InsertChapters(System.Guid,System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Model.Entities.ChapterInfo>,Emby.Server.Implementations.Data.ManagedConnection)
EnableJoinUserData(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasField(MediaBrowser.Controller.Entities.InternalItemsQuery,MediaBrowser.Model.Querying.ItemFields)
HasProgramAttributes(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasServiceName(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasStartDate(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasEpisodeAttributes(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasTrailerTypes(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasArtistFields(MediaBrowser.Controller.Entities.InternalItemsQuery)
HasSeriesFields(MediaBrowser.Controller.Entities.InternalItemsQuery)
SetFinalColumnsToSelect(MediaBrowser.Controller.Entities.InternalItemsQuery,System.Collections.Generic.List`1<System.String>)
BindSearchParams(MediaBrowser.Controller.Entities.InternalItemsQuery,Microsoft.Data.Sqlite.SqliteCommand)
BindSimilarParams(MediaBrowser.Controller.Entities.InternalItemsQuery,Microsoft.Data.Sqlite.SqliteCommand)
GetJoinUserDataText(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetGroupBy(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetCount(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetItemList(MediaBrowser.Controller.Entities.InternalItemsQuery)
FixUnicodeChars(System.String)
AddItem(System.Collections.Generic.List`1<MediaBrowser.Controller.Entities.BaseItem>,MediaBrowser.Controller.Entities.BaseItem)
GetItems(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetOrderByText(MediaBrowser.Controller.Entities.InternalItemsQuery)
MapOrderByField(Jellyfin.Data.Enums.ItemSortBy,MediaBrowser.Controller.Entities.InternalItemsQuery)
GetItemIdsList(MediaBrowser.Controller.Entities.InternalItemsQuery)
IsAlphaNumeric(System.String)
IsValidPersonType(System.String)
GetWhereClauses(MediaBrowser.Controller.Entities.InternalItemsQuery,Microsoft.Data.Sqlite.SqliteCommand)
GetProviderIdClause(System.Boolean,System.String)
GetItemByNameTypesInQuery(MediaBrowser.Controller.Entities.InternalItemsQuery)
IsTypeInQuery(Jellyfin.Data.Enums.BaseItemKind,MediaBrowser.Controller.Entities.InternalItemsQuery)
GetCleanValue(System.String)
EnableGroupByPresentationUniqueKey(MediaBrowser.Controller.Entities.InternalItemsQuery)
UpdateInheritedValues()
DeleteItem(System.Guid)
ExecuteWithSingleParam(Emby.Server.Implementations.Data.ManagedConnection,System.String,System.Guid)
GetPeopleNames(MediaBrowser.Controller.Entities.InternalPeopleQuery)
GetPeople(MediaBrowser.Controller.Entities.InternalPeopleQuery)
GetPeopleWhereClauses(MediaBrowser.Controller.Entities.InternalPeopleQuery,Microsoft.Data.Sqlite.SqliteCommand)
UpdateAncestors(System.Guid,System.Collections.Generic.List`1<System.Guid>,Emby.Server.Implementations.Data.ManagedConnection,Microsoft.Data.Sqlite.SqliteCommand)
GetAllArtists(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetArtists(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetAlbumArtists(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetStudios(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetGenres(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetMusicGenres(MediaBrowser.Controller.Entities.InternalItemsQuery)
GetStudioNames()
GetAllArtistNames()
GetMusicGenreNames()
GetGenreNames()
GetItemValueNames(System.Int32[],System.Collections.Generic.IReadOnlyList`1<System.String>,System.Collections.Generic.IReadOnlyList`1<System.String>)
GetItemValues(MediaBrowser.Controller.Entities.InternalItemsQuery,System.Int32[],System.String)
GetItemCounts(Microsoft.Data.Sqlite.SqliteDataReader,System.Int32,Jellyfin.Data.Enums.BaseItemKind[])
GetItemValuesToSave(MediaBrowser.Controller.Entities.BaseItem,System.Collections.Generic.List`1<System.String>)
UpdateItemValues(System.Guid,System.Collections.Generic.List`1<System.ValueTuple`2<System.Int32,System.String>>,Emby.Server.Implementations.Data.ManagedConnection)
InsertItemValues(System.Guid,System.Collections.Generic.List`1<System.ValueTuple`2<System.Int32,System.String>>,Emby.Server.Implementations.Data.ManagedConnection)
UpdatePeople(System.Guid,System.Collections.Generic.List`1<MediaBrowser.Controller.Entities.PersonInfo>)
InsertPeople(System.Guid,System.Collections.Generic.List`1<MediaBrowser.Controller.Entities.PersonInfo>,Emby.Server.Implementations.Data.ManagedConnection)
GetPerson(Microsoft.Data.Sqlite.SqliteDataReader)
GetMediaStreams(MediaBrowser.Controller.Persistence.MediaStreamQuery)
SaveMediaStreams(System.Guid,System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Model.Entities.MediaStream>,System.Threading.CancellationToken)
InsertMediaStreams(System.Guid,System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Model.Entities.MediaStream>,Emby.Server.Implementations.Data.ManagedConnection)
GetMediaStream(Microsoft.Data.Sqlite.SqliteDataReader)
GetMediaAttachments(MediaBrowser.Controller.Persistence.MediaAttachmentQuery)
SaveMediaAttachments(System.Guid,System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Model.Entities.MediaAttachment>,System.Threading.CancellationToken)
InsertMediaAttachments(System.Guid,System.Collections.Generic.IReadOnlyList`1<MediaBrowser.Model.Entities.MediaAttachment>,Emby.Server.Implementations.Data.ManagedConnection,System.Threading.CancellationToken)
GetMediaAttachment(Microsoft.Data.Sqlite.SqliteDataReader)
BuildMediaAttachmentInsertPrefix()
.ctor(Microsoft.Extensions.Logging.ILogger,System.String,System.String)
Dispose()