context: using MediaBrowser.Controller.Entities;
  using MediaBrowser.Controller.Entities.Audio;
  using MediaBrowser.Controller.Entities.Movies;
  using MediaBrowser.Controller.Entities.TV;
  using MediaBrowser.Controller.LiveTv;
  using MediaBrowser.Controller.Persistence;
  using MediaBrowser.Model.Entities;
  using MediaBrowser.Model.Logging;
  using MediaBrowser.Model.Querying;
  using MediaBrowser.Model.Serialization;
  using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Globalization;
  using System.IO;
  using System.Linq;
  using System.Runtime.Serialization;
  using System.Threading;
  using System.Threading.Tasks;
  using MediaBrowser.Common.Extensions;
  using MediaBrowser.Controller.Channels;
  using MediaBrowser.Controller.Configuration;
  using MediaBrowser.Controller.Playlists;
  using MediaBrowser.Model.LiveTv;
  
  namespace MediaBrowser.Server.Implementations.Persistence
  {
      /// <summary>
      /// Class SQLiteItemRepository
      /// </summary>
      public class SqliteItemRepository : BaseSqliteRepository, IItemRepository
      {
          private IDbConnection _connection;
  
          private readonly TypeMapper _typeMapper = new TypeMapper();
  
          /// <summary>
          /// Gets the name of the repository
          /// </summary>
          /// <value>The name.</value>
          public string Name
          {
              get
              {
                  return "SQLite";
              }
          }
  
          /// <summary>
          /// Gets the json serializer.
          /// </summary>
          /// <value>The json serializer.</value>
          private readonly IJsonSerializer _jsonSerializer;
  
          /// <summary>
          /// The _app paths
          /// </summary>
          private readonly IServerConfigurationManager _config;
  
          /// <summary>
          /// The _save item command
          /// </summary>
          private IDbCommand _saveItemCommand;
  
          private readonly string _criticReviewsPath;
  
          private IDbCommand _deleteItemCommand;
  
          private IDbCommand _deletePeopleCommand;
          private IDbCommand _savePersonCommand;
  
          private IDbCommand _deleteChaptersCommand;
          private IDbCommand _saveChapterCommand;
  
          private IDbCommand _deleteStreamsCommand;
          private IDbCommand _saveStreamCommand;
  
          private IDbCommand _deleteAncestorsCommand;
          private IDbCommand _saveAncestorCommand;
  
          private IDbCommand _deleteUserDataKeysCommand;
          private IDbCommand _saveUserDataKeysCommand;
  
          private IDbCommand _deleteItemValuesCommand;
          private IDbCommand _saveItemValuesCommand;
  
          private IDbCommand _updateInheritedRatingCommand;
          private IDbCommand _updateInheritedTagsCommand;
  
          public const int LatestSchemaVersion = 79;
  
          /// <summary>
          /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
          /// </summary>
          public SqliteItemRepository(IServerConfigurationManager config, IJsonSerializer jsonSerializer, ILogManager logManager)
              : base(logManager)
          {
              if (config == null)
              {
                  throw new ArgumentNullException("config");
              }
              if (jsonSerializer == null)
              {
                  throw new ArgumentNullException("jsonSerializer");
              }
  
              _config = config;
              _jsonSerializer = jsonSerializer;
  
              _criticReviewsPath = Path.Combine(_config.ApplicationPaths.DataPath, "critic-reviews");
          }
  
          private const string ChaptersTableName = "Chapters2";
  
          /// <summary>
          /// Opens the connection to the database
          /// </summary>
          /// <returns>Task.</returns>
          public async Task Initialize(IDbConnector dbConnector)
          {
              var dbFile = Path.Combine(_config.ApplicationPaths.DataPath, "library.db");
  
              _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false);
  
              var createMediaStreamsTableCommand
                 = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))";
  
              string[] queries = {
  
                                  "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)",
                                  "create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
                                  "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
                                  "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
  
                                  "create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))",
                                  "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
                                  "create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)",
  
                                  "create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT, PRIMARY KEY (ItemId, UserDataKey))",
                                  "create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)",
  
                                  "create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT)",
                                  "create index if not exists idx_ItemValues on ItemValues(ItemId)",
  
                                  "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
                                  "create index if not exists idxPeopleItemId on People(ItemId)",
                                  "create index if not exists idxPeopleName on People(Name)",
  
                                  "create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
                                  "create index if not exists idx_"+ChaptersTableName+"1 on "+ChaptersTableName+"(ItemId)",
  
                                  createMediaStreamsTableCommand,
                                  "create index if not exists idx_mediastreams1 on mediastreams(ItemId)",
  
                                  //pragmas
                                  "pragma temp_store = memory",
  
                                  "pragma shrink_memory"
                                 };
  
              _connection.RunQueries(queries, Logger);
  
              _connection.AddColumn(Logger, "AncestorIds", "AncestorIdText", "Text");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "Path", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "StartDate", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "EndDate", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "ChannelId", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsMovie", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsSports", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsKids", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "CommunityRating", "Float");
              _connection.AddColumn(Logger, "TypedBaseItems", "CustomRating", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IndexNumber", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsLocked", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "Name", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "OfficialRating", "Text");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "MediaType", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "Overview", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "ParentIndexNumber", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "PremiereDate", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "ProductionYear", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "ParentId", "GUID");
              _connection.AddColumn(Logger, "TypedBaseItems", "Genres", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "ParentalRatingValue", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "SchemaVersion", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "SortName", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "HomePageUrl", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "VoteCount", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "DisplayMediaType", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "DateCreated", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "DateModified", "DATETIME");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "ForcedSortName", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsOffline", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "LocationType", "Text");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "IsSeries", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsLive", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsNews", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsPremiere", "BIT");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "EpisodeTitle", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsRepeat", "BIT");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsHD", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "ExternalEtag", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME");
  
              _connection.AddColumn(Logger, "TypedBaseItems", "DateLastSaved", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsInMixedFolder", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "LockedFields", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "Studios", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "Audio", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "ExternalServiceId", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "Tags", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsFolder", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "InheritedParentalRatingValue", "INT");
              _connection.AddColumn(Logger, "TypedBaseItems", "UnratedType", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "TopParentId", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsItemByName", "BIT");
              _connection.AddColumn(Logger, "TypedBaseItems", "SourceType", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float");
              _connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "DateModifiedDuringLastRefresh", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME");
              _connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text");
              _connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT");
  
              _connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT");
  
              string[] postQueries =
                  {
                  "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
                  "create index if not exists idx_Type on TypedBaseItems(Type)",
                  "create index if not exists idx_TopParentId on TypedBaseItems(TopParentId)"
              };
  
              _connection.RunQueries(postQueries, Logger);
  
              PrepareStatements();
  
              new MediaStreamColumns(_connection, Logger).AddColumns();
  
              var mediaStreamsDbFile = Path.Combine(_config.ApplicationPaths.DataPath, "mediainfo.db");
              if (File.Exists(mediaStreamsDbFile))
              {
                  MigrateMediaStreams(mediaStreamsDbFile);
              }
  
              DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb");
          }
  
          private void MigrateMediaStreams(string file)
          {
              try
              {
                  var backupFile = file + ".bak";
                  File.Copy(file, backupFile, true);
                  DataExtensions.Attach(_connection, backupFile, "MediaInfoOld");
  
                  var columns = string.Join(",", _mediaStreamSaveColumns);
  
                  string[] queries = {
                                  "REPLACE INTO mediastreams("+columns+") SELECT "+columns+" FROM MediaInfoOld.mediastreams;"
                                 };
  
                  _connection.RunQueries(queries, Logger);
              }
              catch (Exception ex)
              {
                  Logger.ErrorException("Error migrating media info database", ex);
              }
              finally
              {
                  TryDeleteFile(file);
              }
          }
  
          private void TryDeleteFile(string file)
          {
              try
              {
                  File.Delete(file);
              }
              catch (Exception ex)
              {
                  Logger.ErrorException("Error deleting file {0}", ex, file);
              }
          }
  
          private readonly string[] _retriveItemColumns =
          {
              "type",
              "data",
              "StartDate",
              "EndDate",
              "IsOffline",
              "ChannelId",
              "IsMovie",
              "IsSports",
              "IsKids",
              "IsSeries",
              "IsLive",
              "IsNews",
              "IsPremiere",
              "EpisodeTitle",
              "IsRepeat",
              "CommunityRating",
              "CustomRating",
              "IndexNumber",
              "IsLocked",
              "PreferredMetadataLanguage",
              "PreferredMetadataCountryCode",
              "IsHD",
              "ExternalEtag",
              "DateLastRefreshed",
              "Name",
              "Path",
              "PremiereDate",
              "Overview",
              "ParentIndexNumber",
              "ProductionYear",
              "OfficialRating",
              "OfficialRatingDescription",
              "HomePageUrl",
              "DisplayMediaType",
              "ForcedSortName",
              "RunTimeTicks",
              "VoteCount",
              "DateCreated",
              "DateModified",
              "guid",
              "Genres",
              "ParentId",
              "Audio",
              "ExternalServiceId",
              "IsInMixedFolder",
              "DateLastSaved",
              "LockedFields",
              "Studios",
              "Tags",
              "SourceType",
              "TrailerTypes",
              "DateModifiedDuringLastRefresh",
              "OriginalTitle",
              "PrimaryVersionId",
              "DateLastMediaAdded",
              "Album",
              "CriticRating",
              "CriticRatingSummary",
              "IsVirtualItem"
          };
  
          private readonly string[] _mediaStreamSaveColumns =
          {
              "ItemId",
              "StreamIndex",
              "StreamType",
              "Codec",
              "Language",
              "ChannelLayout",
              "Profile",
              "AspectRatio",
              "Path",
              "IsInterlaced",
              "BitRate",
              "Channels",
              "SampleRate",
              "IsDefault",
              "IsForced",
              "IsExternal",
              "Height",
              "Width",
              "AverageFrameRate",
              "RealFrameRate",
              "Level",
              "PixelFormat",
              "BitDepth",
              "IsAnamorphic",
              "RefFrames",
              "CodecTag",
              "Comment",
              "NalLengthSize",
              "IsAvc",
              "Title"
          };
  
          /// <summary>
          /// Prepares the statements.
          /// </summary>
          private void PrepareStatements()
          {
              var saveColumns = new List<string>
              {
                  "guid",
                  "type",
                  "data",
                  "Path",
                  "StartDate",
                  "EndDate",
                  "ChannelId",
                  "IsKids",
                  "IsMovie",
                  "IsSports",
                  "IsSeries",
                  "IsLive",
                  "IsNews",
                  "IsPremiere",
                  "EpisodeTitle",
                  "IsRepeat",
                  "CommunityRating",
                  "CustomRating",
                  "IndexNumber",
                  "IsLocked",
                  "Name",
                  "OfficialRating",
                  "MediaType",
                  "Overview",
                  "ParentIndexNumber",
                  "PremiereDate",
                  "ProductionYear",
                  "ParentId",
                  "Genres",
                  "ParentalRatingValue",
                  "InheritedParentalRatingValue",
                  "SchemaVersion",
                  "SortName",
                  "RunTimeTicks",
                  "OfficialRatingDescription",
                  "HomePageUrl",
                  "VoteCount",
                  "DisplayMediaType",
                  "DateCreated",
                  "DateModified",
                  "ForcedSortName",
                  "IsOffline",
                  "LocationType",
                  "PreferredMetadataLanguage",
                  "PreferredMetadataCountryCode",
                  "IsHD",
                  "ExternalEtag",
                  "DateLastRefreshed",
                  "DateLastSaved",
                  "IsInMixedFolder",
                  "LockedFields",
                  "Studios",
                  "Audio",
                  "ExternalServiceId",
                  "Tags",
                  "IsFolder",
                  "UnratedType",
                  "TopParentId",
                  "IsItemByName",
                  "SourceType",
                  "TrailerTypes",
                  "CriticRating",
                  "CriticRatingSummary",
                  "DateModifiedDuringLastRefresh",
                  "InheritedTags",
                  "CleanName",
                  "PresentationUniqueKey",
                  "SlugName",
                  "OriginalTitle",
                  "PrimaryVersionId",
                  "DateLastMediaAdded",
                  "Album",
                  "IsVirtualItem"
              };
              _saveItemCommand = _connection.CreateCommand();
              _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values (";
  
              for (var i = 1; i <= saveColumns.Count; i++)
              {
                  if (i > 1)
                  {
                      _saveItemCommand.CommandText += ",";
                  }
                  _saveItemCommand.CommandText += "@" + i.ToString(CultureInfo.InvariantCulture);
  
                  _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture));
              }
              _saveItemCommand.CommandText += ")";
  
              _deleteItemCommand = _connection.CreateCommand();
              _deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id";
              _deleteItemCommand.Parameters.Add(_deleteItemCommand, "@Id");
  
              // People
              _deletePeopleCommand = _connection.CreateCommand();
              _deletePeopleCommand.CommandText = "delete from People where ItemId=@Id";
              _deletePeopleCommand.Parameters.Add(_deletePeopleCommand, "@Id");
  
              _savePersonCommand = _connection.CreateCommand();
              _savePersonCommand.CommandText = "insert into People (ItemId, Name, Role, PersonType, SortOrder, ListOrder) values (@ItemId, @Name, @Role, @PersonType, @SortOrder, @ListOrder)";
              _savePersonCommand.Parameters.Add(_savePersonCommand, "@ItemId");
              _savePersonCommand.Parameters.Add(_savePersonCommand, "@Name");
              _savePersonCommand.Parameters.Add(_savePersonCommand, "@Role");
              _savePersonCommand.Parameters.Add(_savePersonCommand, "@PersonType");
              _savePersonCommand.Parameters.Add(_savePersonCommand, "@SortOrder");
              _savePersonCommand.Parameters.Add(_savePersonCommand, "@ListOrder");
  
              // Ancestors
              _deleteAncestorsCommand = _connection.CreateCommand();
              _deleteAncestorsCommand.CommandText = "delete from AncestorIds where ItemId=@Id";
              _deleteAncestorsCommand.Parameters.Add(_deleteAncestorsCommand, "@Id");
  
              _saveAncestorCommand = _connection.CreateCommand();
              _saveAncestorCommand.CommandText = "insert into AncestorIds (ItemId, AncestorId, AncestorIdText) values (@ItemId, @AncestorId, @AncestorIdText)";
              _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@ItemId");
              _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@AncestorId");
              _saveAncestorCommand.Parameters.Add(_saveAncestorCommand, "@AncestorIdText");
  
              // Chapters
              _deleteChaptersCommand = _connection.CreateCommand();
              _deleteChaptersCommand.CommandText = "delete from " + ChaptersTableName + " where ItemId=@ItemId";
              _deleteChaptersCommand.Parameters.Add(_deleteChaptersCommand, "@ItemId");
  
              _saveChapterCommand = _connection.CreateCommand();
              _saveChapterCommand.CommandText = "replace into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath) values (@ItemId, @ChapterIndex, @StartPositionTicks, @Name, @ImagePath)";
  
              _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ItemId");
              _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ChapterIndex");
              _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@StartPositionTicks");
              _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@Name");
              _saveChapterCommand.Parameters.Add(_saveChapterCommand, "@ImagePath");
  
              // MediaStreams
              _deleteStreamsCommand = _connection.CreateCommand();
              _deleteStreamsCommand.CommandText = "delete from mediastreams where ItemId=@ItemId";
              _deleteStreamsCommand.Parameters.Add(_deleteStreamsCommand, "@ItemId");
  
              _saveStreamCommand = _connection.CreateCommand();
  
              _saveStreamCommand.CommandText = string.Format("replace into mediastreams ({0}) values ({1})",
                  string.Join(",", _mediaStreamSaveColumns),
                  string.Join(",", _mediaStreamSaveColumns.Select(i => "@" + i).ToArray()));
  
              foreach (var col in _mediaStreamSaveColumns)
              {
                  _saveStreamCommand.Parameters.Add(_saveStreamCommand, "@" + col);
              }
  
              _updateInheritedRatingCommand = _connection.CreateCommand();
              _updateInheritedRatingCommand.CommandText = "Update TypedBaseItems set InheritedParentalRatingValue=@InheritedParentalRatingValue where Guid=@Guid";
              _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@Guid");
              _updateInheritedRatingCommand.Parameters.Add(_updateInheritedRatingCommand, "@InheritedParentalRatingValue");
  
              _updateInheritedTagsCommand = _connection.CreateCommand();
              _updateInheritedTagsCommand.CommandText = "Update TypedBaseItems set InheritedTags=@InheritedTags where Guid=@Guid";
              _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@Guid");
              _updateInheritedTagsCommand.Parameters.Add(_updateInheritedTagsCommand, "@InheritedTags");
  
              // user data
              _deleteUserDataKeysCommand = _connection.CreateCommand();
              _deleteUserDataKeysCommand.CommandText = "delete from UserDataKeys where ItemId=@Id";
              _deleteUserDataKeysCommand.Parameters.Add(_deleteUserDataKeysCommand, "@Id");
  
              _saveUserDataKeysCommand = _connection.CreateCommand();
              _saveUserDataKeysCommand.CommandText = "insert into UserDataKeys (ItemId, UserDataKey, Priority) values (@ItemId, @UserDataKey, @Priority)";
              _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@ItemId");
              _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@UserDataKey");
              _saveUserDataKeysCommand.Parameters.Add(_saveUserDataKeysCommand, "@Priority");
  
              // item values
              _deleteItemValuesCommand = _connection.CreateCommand();
              _deleteItemValuesCommand.CommandText = "delete from ItemValues where ItemId=@Id";
              _deleteItemValuesCommand.Parameters.Add(_deleteItemValuesCommand, "@Id");
  
              _saveItemValuesCommand = _connection.CreateCommand();
              _saveItemValuesCommand.CommandText = "insert into ItemValues (ItemId, Type, Value) values (@ItemId, @Type, @Value)";
              _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@ItemId");
              _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Type");
              _saveItemValuesCommand.Parameters.Add(_saveItemValuesCommand, "@Value");
  
          }
  
          /// <summary>
          /// Save a standard item in the repo
          /// </summary>
          /// <param name="item">The item.</param>
          /// <param name="cancellationToken">The cancellation token.</param>
          /// <returns>Task.</returns>
          /// <exception cref="System.ArgumentNullException">item</exception>
          public Task SaveItem(BaseItem item, CancellationToken cancellationToken)
          {
              if (item == null)
              {
                  throw new ArgumentNullException("item");
              }
  
              return SaveItems(new[] { item }, cancellationToken);
          }
  
          /// <summary>
          /// Saves the items.
          /// </summary>
          /// <param name="items">The items.</param>
          /// <param name="cancellationToken">The cancellation token.</param>
          /// <returns>Task.</returns>
          /// <exception cref="System.ArgumentNullException">
          /// items
          /// or
          /// cancellationToken
          /// </exception>
          public async Task SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken)
          {
              if (items == null)
              {
                  throw new ArgumentNullException("items");
              }
  
              cancellationToken.ThrowIfCancellationRequested();
  
              CheckDisposed();
  
              await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false);
  
              IDbTransaction transaction = null;
  
              try
              {
                  transaction = _connection.BeginTransaction();
  
                  foreach (var item in items)
                  {
                      cancellationToken.ThrowIfCancellationRequested();
  
                      var index = 0;
  
                      _saveItemCommand.GetParameter(index++).Value = item.Id;
                      _saveItemCommand.GetParameter(index++).Value = item.GetType().FullName;
                      _saveItemCommand.GetParameter(index++).Value = _jsonSerializer.SerializeToBytes(item);
  
                      _saveItemCommand.GetParameter(index++).Value = item.Path;
  
                      var hasStartDate = item as IHasStartDate;
                      if (hasStartDate != null)
                      {
                          _saveItemCommand.GetParameter(index++).Value = hasStartDate.StartDate;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.EndDate;
                      _saveItemCommand.GetParameter(index++).Value = item.ChannelId;
  
                      var hasProgramAttributes = item as IHasProgramAttributes;
                      if (hasProgramAttributes != null)
                      {
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsKids;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsMovie;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSports;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsSeries;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsLive;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsNews;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsPremiere;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.EpisodeTitle;
                          _saveItemCommand.GetParameter(index++).Value = hasProgramAttributes.IsRepeat;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.CommunityRating;
                      _saveItemCommand.GetParameter(index++).Value = item.CustomRating;
  
                      _saveItemCommand.GetParameter(index++).Value = item.IndexNumber;
                      _saveItemCommand.GetParameter(index++).Value = item.IsLocked;
  
                      _saveItemCommand.GetParameter(index++).Value = item.Name;
                      _saveItemCommand.GetParameter(index++).Value = item.OfficialRating;
  
                      _saveItemCommand.GetParameter(index++).Value = item.MediaType;
                      _saveItemCommand.GetParameter(index++).Value = item.Overview;
                      _saveItemCommand.GetParameter(index++).Value = item.ParentIndexNumber;
                      _saveItemCommand.GetParameter(index++).Value = item.PremiereDate;
                      _saveItemCommand.GetParameter(index++).Value = item.ProductionYear;
  
                      if (item.ParentId == Guid.Empty)
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = item.ParentId;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray());
                      _saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue() ?? 0;
                      _saveItemCommand.GetParameter(index++).Value = item.GetInheritedParentalRatingValue() ?? 0;
  
                      _saveItemCommand.GetParameter(index++).Value = LatestSchemaVersion;
                      _saveItemCommand.GetParameter(index++).Value = item.SortName;
                      _saveItemCommand.GetParameter(index++).Value = item.RunTimeTicks;
  
                      _saveItemCommand.GetParameter(index++).Value = item.OfficialRatingDescription;
                      _saveItemCommand.GetParameter(index++).Value = item.HomePageUrl;
                      _saveItemCommand.GetParameter(index++).Value = item.VoteCount;
                      _saveItemCommand.GetParameter(index++).Value = item.DisplayMediaType;
                      _saveItemCommand.GetParameter(index++).Value = item.DateCreated;
                      _saveItemCommand.GetParameter(index++).Value = item.DateModified;
  
                      _saveItemCommand.GetParameter(index++).Value = item.ForcedSortName;
                      _saveItemCommand.GetParameter(index++).Value = item.IsOffline;
                      _saveItemCommand.GetParameter(index++).Value = item.LocationType.ToString();
  
                      _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataLanguage;
                      _saveItemCommand.GetParameter(index++).Value = item.PreferredMetadataCountryCode;
                      _saveItemCommand.GetParameter(index++).Value = item.IsHD;
                      _saveItemCommand.GetParameter(index++).Value = item.ExternalEtag;
  
                      if (item.DateLastRefreshed == default(DateTime))
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = item.DateLastRefreshed;
                      }
  
                      if (item.DateLastSaved == default(DateTime))
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = item.DateLastSaved;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.IsInMixedFolder;
                      _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.LockedFields.Select(i => i.ToString()).ToArray());
                      _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Studios.ToArray());
  
                      if (item.Audio.HasValue)
                      {
                          _saveItemCommand.GetParameter(index++).Value = item.Audio.Value.ToString();
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.ServiceName;
  
                      if (item.Tags.Count > 0)
                      {
                          _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Tags.ToArray());
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.IsFolder;
  
                      _saveItemCommand.GetParameter(index++).Value = item.GetBlockUnratedType().ToString();
  
                      var topParent = item.GetTopParent();
                      if (topParent != null)
                      {
                          //Logger.Debug("Item {0} has top parent {1}", item.Id, topParent.Id);
                          _saveItemCommand.GetParameter(index++).Value = topParent.Id.ToString("N");
                      }
                      else
                      {
                          //Logger.Debug("Item {0} has null top parent", item.Id);
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      var isByName = false;
                      var byName = item as IItemByName;
                      if (byName != null)
                      {
                          var dualAccess = item as IHasDualAccess;
                          isByName = dualAccess == null || dualAccess.IsAccessedByName;
                      }
                      _saveItemCommand.GetParameter(index++).Value = isByName;
  
                      _saveItemCommand.GetParameter(index++).Value = item.SourceType.ToString();
  
                      var trailer = item as Trailer;
                      if (trailer != null && trailer.TrailerTypes.Count > 0)
                      {
                          _saveItemCommand.GetParameter(index++).Value = string.Join("|", trailer.TrailerTypes.Select(i => i.ToString()).ToArray());
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.CriticRating;
                      _saveItemCommand.GetParameter(index++).Value = item.CriticRatingSummary;
  
                      if (!item.DateModifiedDuringLastRefresh.HasValue || item.DateModifiedDuringLastRefresh.Value == default(DateTime))
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = item.DateModifiedDuringLastRefresh.Value;
                      }
  
                      var inheritedTags = item.GetInheritedTags();
                      if (inheritedTags.Count > 0)
                      {
                          _saveItemCommand.GetParameter(index++).Value = string.Join("|", inheritedTags.ToArray());
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      if (string.IsNullOrWhiteSpace(item.Name))
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = item.Name.RemoveDiacritics();
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.PresentationUniqueKey;
                      _saveItemCommand.GetParameter(index++).Value = item.SlugName;
                      _saveItemCommand.GetParameter(index++).Value = item.OriginalTitle;
  
                      var video = item as Video;
                      if (video != null)
                      {
                          _saveItemCommand.GetParameter(index++).Value = video.PrimaryVersionId;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      var folder = item as Folder;
                      if (folder != null && folder.DateLastMediaAdded.HasValue)
                      {
                          _saveItemCommand.GetParameter(index++).Value = folder.DateLastMediaAdded.Value;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.GetParameter(index++).Value = item.Album;
  
                      var season = item as Season;
                      if (season != null && season.IsVirtualItem.HasValue)
                      {
                          _saveItemCommand.GetParameter(index++).Value = season.IsVirtualItem.Value;
                      }
                      else
                      {
                          _saveItemCommand.GetParameter(index++).Value = null;
                      }
  
                      _saveItemCommand.Transaction = transaction;
  
                      _saveItemCommand.ExecuteNonQuery();
  
                      if (item.SupportsAncestors)
                      {
                          UpdateAncestors(item.Id, item.GetAncestorIds().Distinct().ToList(), transaction);
                      }
  
                      UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction);
                      UpdateItemValues(item.Id, GetItemValues(item), transaction);
                  }
  
                  transaction.Commit();
              }
              catch (OperationCanceledException)
              {
                  if (transaction != null)
                  {
                      transaction.Rollback();
                  }
  
                  throw;
              }
              catch (Exception e)
              {
                  Logger.ErrorException("Failed to save items:", e);
  
                  if (transaction != null)
                  {
                      transaction.Rollback();
                  }
  
                  throw;
              }
              finally
              {
                  if (transaction != null)
                  {
                      transaction.Dispose();
                  }
  
                  WriteLock.Release();
              }
          }
  
          /// <summary>
          /// Internal retrieve from items or users table
          /// </summary>
          /// <param name="id">The id.</param>
          /// <returns>BaseItem.</returns>
          /// <exception cref="System.ArgumentNullException">id</exception>
          /// <exception cref="System.ArgumentException"></exception>
          public BaseItem RetrieveItem(Guid id)
          {
              if (id == Guid.Empty)
              {
                  throw new ArgumentNullException("id");
              }
  
              CheckDisposed();
  
              using (var cmd = _connection.CreateCommand())
              {
                  cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid";
                  cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = id;
  
                  using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
                  {
                      if (reader.Read())
                      {
                          return GetItem(reader);
                      }
                  }
                  return null;
              }
          }
  
          private BaseItem GetItem(IDataReader reader)
          {
              var typeString = reader.GetString(0);
  
  
gt:             var type = _typeMapper.GetType(typeString);