diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs | 147 |
1 files changed, 138 insertions, 9 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 3ec45c4de..00ebf7ea6 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -71,6 +71,9 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _deletePeopleCommand; private IDbCommand _savePersonCommand; + + private const int LatestSchemaVersion = 6; + /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. /// </summary> @@ -159,6 +162,19 @@ namespace MediaBrowser.Server.Implementations.Persistence _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"); PrepareStatements(); @@ -171,6 +187,13 @@ namespace MediaBrowser.Server.Implementations.Persistence /// </summary> private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1); + private string[] _retriveItemColumns = + { + "type", + "data", + "IsOffline" + }; + /// <summary> /// Prepares the statements. /// </summary> @@ -201,14 +224,33 @@ namespace MediaBrowser.Server.Implementations.Persistence "ProductionYear", "ParentId", "Genres", - "ParentalRatingValue" + "ParentalRatingValue", + "SchemaVersion", + "SortName", + "RunTimeTicks", + "OfficialRatingDescription", + "HomePageUrl", + "VoteCount", + "DisplayMediaType", + "DateCreated", + "DateModified", + "ForcedSortName", + "IsOffline" }; _saveItemCommand = _connection.CreateCommand(); - _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20, @21, @22, @23, @24)"; + _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 += ")"; _deleteChildrenCommand = _connection.CreateCommand(); _deleteChildrenCommand.CommandText = "delete from ChildrenIds where ParentId=@ParentId"; @@ -350,6 +392,20 @@ namespace MediaBrowser.Server.Implementations.Persistence _saveItemCommand.GetParameter(index++).Value = string.Join("|", item.Genres.ToArray()); _saveItemCommand.GetParameter(index++).Value = item.GetParentalRatingValue(); + _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.Transaction = transaction; _saveItemCommand.ExecuteNonQuery(); @@ -406,7 +462,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems where guid = @guid"; + 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)) @@ -433,11 +489,18 @@ namespace MediaBrowser.Server.Implementations.Persistence return null; } + BaseItem item; + using (var stream = reader.GetMemoryStream(1)) { try { - return _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; + item = _jsonSerializer.DeserializeFromStream(stream, type) as BaseItem; + + if (item == null) + { + return null; + } } catch (SerializationException ex) { @@ -445,6 +508,13 @@ namespace MediaBrowser.Server.Implementations.Persistence return null; } } + + if (!reader.IsDBNull(2)) + { + item.IsOffline = reader.GetBoolean(2); + } + + return item; } /// <summary> @@ -636,7 +706,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems where guid in (select ItemId from ChildrenIds where ParentId = @ParentId)"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid in (select ItemId from ChildrenIds where ParentId = @ParentId)"; cmd.Parameters.Add(cmd, "@ParentId", DbType.Guid).Value = parentId; @@ -666,7 +736,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems where type = @type"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where type = @type"; cmd.Parameters.Add(cmd, "@type", DbType.String).Value = type.FullName; @@ -696,7 +766,7 @@ namespace MediaBrowser.Server.Implementations.Persistence using (var cmd = _connection.CreateCommand()) { - cmd.CommandText = "select type,data from TypedBaseItems"; + cmd.CommandText = "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems"; var whereClauses = GetWhereClauses(query, cmd, false); @@ -712,6 +782,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetOrderByText(query); + if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); @@ -719,6 +791,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += "; select count (guid) from TypedBaseItems" + whereTextWithoutPaging; + _logger.Debug(cmd.CommandText); + var list = new List<BaseItem>(); var count = 0; @@ -747,6 +821,23 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + private string GetOrderByText(InternalItemsQuery query) + { + if (query.SortBy == null || query.SortBy.Length == 0) + { + return string.Empty; + } + + var sortOrder = query.SortOrder == SortOrder.Descending ? "DESC" : "ASC"; + + return " ORDER BY " + string.Join(",", query.SortBy.Select(i => MapOrderByField(i) + " " + sortOrder).ToArray()); + } + + private string MapOrderByField(string name) + { + return name; + } + public List<Guid> GetItemIdsList(InternalItemsQuery query) { if (query == null) @@ -768,6 +859,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetOrderByText(query); + if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); @@ -816,6 +909,8 @@ namespace MediaBrowser.Server.Implementations.Persistence cmd.CommandText += whereText; + cmd.CommandText += GetOrderByText(query); + if (query.Limit.HasValue) { cmd.CommandText += " LIMIT " + query.Limit.Value.ToString(CultureInfo.InvariantCulture); @@ -853,6 +948,18 @@ namespace MediaBrowser.Server.Implementations.Persistence { var whereClauses = new List<string>(); + if (query.IsCurrentSchema.HasValue) + { + if (query.IsCurrentSchema.Value) + { + whereClauses.Add("(SchemaVersion not null AND SchemaVersion=@SchemaVersion)"); + } + else + { + whereClauses.Add("(SchemaVersion is null or SchemaVersion<>@SchemaVersion)"); + } + cmd.Parameters.Add(cmd, "@SchemaVersion", DbType.Int32).Value = LatestSchemaVersion; + } if (query.IsMovie.HasValue) { whereClauses.Add("IsMovie=@IsMovie"); @@ -870,7 +977,6 @@ namespace MediaBrowser.Server.Implementations.Persistence } var includeTypes = query.IncludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); - if (includeTypes.Length == 1) { whereClauses.Add("type=@type"); @@ -881,6 +987,19 @@ namespace MediaBrowser.Server.Implementations.Persistence var inClause = string.Join(",", includeTypes.Select(i => "'" + i + "'").ToArray()); whereClauses.Add(string.Format("type in ({0})", inClause)); } + + var excludeTypes = query.ExcludeItemTypes.SelectMany(MapIncludeItemTypes).ToArray(); + if (excludeTypes.Length == 1) + { + whereClauses.Add("type<>@type"); + cmd.Parameters.Add(cmd, "@type", DbType.String).Value = excludeTypes[0]; + } + else if (excludeTypes.Length > 1) + { + var inClause = string.Join(",", excludeTypes.Select(i => "'" + i + "'").ToArray()); + whereClauses.Add(string.Format("type not in ({0})", inClause)); + } + if (query.ChannelIds.Length == 1) { whereClauses.Add("ChannelId=@ChannelId"); @@ -977,6 +1096,14 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + if (query.HasDeadParentId.HasValue) + { + if (query.HasDeadParentId.Value) + { + whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)"); + } + } + if (addPaging) { if (query.StartIndex.HasValue && query.StartIndex.Value > 0) @@ -985,7 +1112,9 @@ namespace MediaBrowser.Server.Implementations.Persistence string.Empty : " where " + string.Join(" AND ", whereClauses.ToArray()); - whereClauses.Add(string.Format("Id NOT IN (SELECT Id FROM TypedBaseItems {0} ORDER BY DateCreated DESC LIMIT {1})", + var orderBy = GetOrderByText(query); + + whereClauses.Add(string.Format("guid NOT IN (SELECT guid FROM TypedBaseItems {0}" + orderBy + " LIMIT {1})", pagingWhereText, query.StartIndex.Value.ToString(CultureInfo.InvariantCulture))); } |