diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs | 101 |
1 files changed, 98 insertions, 3 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs index 78e0bce17..7ab8b9151 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs @@ -81,10 +81,13 @@ namespace MediaBrowser.Server.Implementations.Persistence private IDbCommand _deleteUserDataKeysCommand; private IDbCommand _saveUserDataKeysCommand; + private IDbCommand _deleteItemValuesCommand; + private IDbCommand _saveItemValuesCommand; + private IDbCommand _updateInheritedRatingCommand; private IDbCommand _updateInheritedTagsCommand; - public const int LatestSchemaVersion = 78; + public const int LatestSchemaVersion = 79; /// <summary> /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class. @@ -136,6 +139,9 @@ namespace MediaBrowser.Server.Implementations.Persistence "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)", @@ -565,6 +571,17 @@ namespace MediaBrowser.Server.Implementations.Persistence _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> @@ -851,6 +868,7 @@ namespace MediaBrowser.Server.Implementations.Persistence } UpdateUserDataKeys(item.Id, item.GetUserDataKeys().Distinct(StringComparer.OrdinalIgnoreCase).ToList(), transaction); + UpdateItemValues(item.Id, GetItemValues(item), transaction); } transaction.Commit(); @@ -1661,7 +1679,7 @@ namespace MediaBrowser.Server.Implementations.Persistence { var elapsed = (DateTime.UtcNow - startDate).TotalMilliseconds; - if (elapsed >= 500) + if (elapsed >= 400) { Logger.Debug("{2} query time (slow): {0}ms. Query: {1}", Convert.ToInt32(elapsed), @@ -1795,7 +1813,7 @@ namespace MediaBrowser.Server.Implementations.Persistence }).ToArray()); } - private Tuple<string,bool> MapOrderByField(string name) + private Tuple<string, bool> MapOrderByField(string name) { if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase)) { @@ -1838,6 +1856,14 @@ namespace MediaBrowser.Server.Implementations.Persistence { return new Tuple<string, bool>("DateLastMediaAdded", false); } + if (string.Equals(name, ItemSortBy.Artist, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=0 LIMIT 1)", false); + } + if (string.Equals(name, ItemSortBy.AlbumArtist, StringComparison.OrdinalIgnoreCase)) + { + return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=1 LIMIT 1)", false); + } return new Tuple<string, bool>(name, false); } @@ -2433,6 +2459,20 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + if (query.ArtistNames.Length > 0) + { + var clauses = new List<string>(); + var index = 0; + foreach (var artist in query.ArtistNames) + { + clauses.Add("@ArtistName" + index + " in (select value from itemvalues where ItemId=Guid and Type <= 1)"); + cmd.Parameters.Add(cmd, "@ArtistName" + index, DbType.String).Value = artist; + index++; + } + var clause = "(" + string.Join(" OR ", clauses.ToArray()) + ")"; + whereClauses.Add(clause); + } + if (query.Genres.Length > 0) { var clauses = new List<string>(); @@ -2970,6 +3010,11 @@ namespace MediaBrowser.Server.Implementations.Persistence _deleteUserDataKeysCommand.Transaction = transaction; _deleteUserDataKeysCommand.ExecuteNonQuery(); + // Delete item values + _deleteItemValuesCommand.GetParameter(0).Value = id; + _deleteItemValuesCommand.Transaction = transaction; + _deleteItemValuesCommand.ExecuteNonQuery(); + // Delete the item _deleteItemCommand.GetParameter(0).Value = id; _deleteItemCommand.Transaction = transaction; @@ -3162,6 +3207,56 @@ namespace MediaBrowser.Server.Implementations.Persistence } } + private List<Tuple<int, string>> GetItemValues(BaseItem item) + { + var list = new List<Tuple<int, string>>(); + + var hasArtist = item as IHasArtist; + if (hasArtist != null) + { + list.AddRange(hasArtist.Artists.Select(i => new Tuple<int, string>(0, i))); + } + + var hasAlbumArtist = item as IHasAlbumArtist; + if (hasAlbumArtist != null) + { + list.AddRange(hasAlbumArtist.AlbumArtists.Select(i => new Tuple<int, string>(1, i))); + } + + return list; + } + + private void UpdateItemValues(Guid itemId, List<Tuple<int, string>> values, IDbTransaction transaction) + { + if (itemId == Guid.Empty) + { + throw new ArgumentNullException("itemId"); + } + + if (values == null) + { + throw new ArgumentNullException("keys"); + } + + CheckDisposed(); + + // First delete + _deleteItemValuesCommand.GetParameter(0).Value = itemId; + _deleteItemValuesCommand.Transaction = transaction; + + _deleteItemValuesCommand.ExecuteNonQuery(); + + foreach (var pair in values) + { + _saveItemValuesCommand.GetParameter(0).Value = itemId; + _saveItemValuesCommand.GetParameter(1).Value = pair.Item1; + _saveItemValuesCommand.GetParameter(2).Value = pair.Item2; + _saveItemValuesCommand.Transaction = transaction; + + _saveItemValuesCommand.ExecuteNonQuery(); + } + } + private void UpdateUserDataKeys(Guid itemId, List<string> keys, IDbTransaction transaction) { if (itemId == Guid.Empty) |