aboutsummaryrefslogtreecommitdiff
path: root/Emby.Server.Implementations/Data/SqliteItemRepository.cs
diff options
context:
space:
mode:
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteItemRepository.cs')
-rw-r--r--Emby.Server.Implementations/Data/SqliteItemRepository.cs187
1 files changed, 107 insertions, 80 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index ab60cee61..638c7a9b4 100644
--- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
@@ -219,7 +219,8 @@ namespace Emby.Server.Implementations.Data
{
connection.RunQueries(queries);
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var existingColumnNames = GetColumnNames(db, "AncestorIds");
AddColumn(db, "AncestorIds", "AncestorIdText", "Text", existingColumnNames);
@@ -495,7 +496,8 @@ namespace Emby.Server.Implementations.Data
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
using (var saveImagesStatement = base.PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id"))
{
@@ -546,7 +548,8 @@ namespace Emby.Server.Implementations.Data
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
SaveItemsInTranscation(db, tuples);
}, TransactionMode);
@@ -1004,7 +1007,7 @@ namespace Emby.Server.Implementations.Data
return;
}
- var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ var parts = value.Split('|', StringSplitOptions.RemoveEmptyEntries);
foreach (var part in parts)
{
@@ -1054,7 +1057,7 @@ namespace Emby.Server.Implementations.Data
return;
}
- var parts = value.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ var parts = value.Split('|' , StringSplitOptions.RemoveEmptyEntries);
var list = new List<ItemImageInfo>();
foreach (var part in parts)
{
@@ -1093,7 +1096,7 @@ namespace Emby.Server.Implementations.Data
public ItemImageInfo ItemImageInfoFromValueString(string value)
{
- var parts = value.Split(new[] { '*' }, StringSplitOptions.None);
+ var parts = value.Split('*', StringSplitOptions.None);
if (parts.Length < 3)
{
@@ -1529,7 +1532,7 @@ namespace Emby.Server.Implementations.Data
{
if (!reader.IsDBNull(index))
{
- item.Genres = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ item.Genres = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries);
}
index++;
@@ -1590,7 +1593,7 @@ namespace Emby.Server.Implementations.Data
{
IEnumerable<MetadataField> GetLockedFields(string s)
{
- foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries))
+ foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries))
{
if (Enum.TryParse(i, true, out MetadataField parsedValue))
{
@@ -1609,7 +1612,7 @@ namespace Emby.Server.Implementations.Data
{
if (!reader.IsDBNull(index))
{
- item.Studios = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ item.Studios = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries);
}
index++;
@@ -1619,7 +1622,7 @@ namespace Emby.Server.Implementations.Data
{
if (!reader.IsDBNull(index))
{
- item.Tags = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ item.Tags = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries);
}
index++;
@@ -1633,7 +1636,7 @@ namespace Emby.Server.Implementations.Data
{
IEnumerable<TrailerType> GetTrailerTypes(string s)
{
- foreach (var i in s.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries))
+ foreach (var i in s.Split('|', StringSplitOptions.RemoveEmptyEntries))
{
if (Enum.TryParse(i, true, out TrailerType parsedValue))
{
@@ -1808,7 +1811,7 @@ namespace Emby.Server.Implementations.Data
{
if (!reader.IsDBNull(index))
{
- item.ProductionLocations = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToArray();
+ item.ProductionLocations = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries).ToArray();
}
index++;
@@ -1845,14 +1848,14 @@ namespace Emby.Server.Implementations.Data
{
if (item is IHasArtist hasArtists && !reader.IsDBNull(index))
{
- hasArtists.Artists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ hasArtists.Artists = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries);
}
index++;
if (item is IHasAlbumArtist hasAlbumArtists && !reader.IsDBNull(index))
{
- hasAlbumArtists.AlbumArtists = reader.GetString(index).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
+ hasAlbumArtists.AlbumArtists = reader.GetString(index).Split('|', StringSplitOptions.RemoveEmptyEntries);
}
index++;
@@ -2032,7 +2035,8 @@ namespace Emby.Server.Implementations.Data
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
// First delete chapters
db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob);
@@ -2263,7 +2267,6 @@ namespace Emby.Server.Implementations.Data
return query.IncludeItemTypes.Contains("Trailer", StringComparer.OrdinalIgnoreCase);
}
-
private static readonly HashSet<string> _artistExcludeParentTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
"Series",
@@ -2400,11 +2403,11 @@ namespace Emby.Server.Implementations.Data
if (string.IsNullOrEmpty(item.OfficialRating))
{
- builder.Append("((OfficialRating is null) * 10)");
+ builder.Append("(OfficialRating is null * 10)");
}
else
{
- builder.Append("((OfficialRating=@ItemOfficialRating) * 10)");
+ builder.Append("(OfficialRating=@ItemOfficialRating * 10)");
}
if (item.ProductionYear.HasValue)
@@ -2413,8 +2416,26 @@ namespace Emby.Server.Implementations.Data
builder.Append("+(Select Case When Abs(COALESCE(ProductionYear, 0) - @ItemProductionYear) < 5 Then 5 Else 0 End )");
}
- //// genres, tags
- builder.Append("+ ((Select count(CleanValue) from ItemValues where ItemId=Guid and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId)) * 10)");
+ // genres, tags, studios, person, year?
+ builder.Append("+ (Select count(1) * 10 from ItemValues where ItemId=Guid and CleanValue in (select CleanValue from itemvalues where ItemId=@SimilarItemId))");
+
+ if (item is MusicArtist)
+ {
+ // Match albums where the artist is AlbumArtist against other albums.
+ // It is assumed that similar albums => similar artists.
+ builder.Append(
+ @"+ (WITH artistValues AS (
+ SELECT DISTINCT albumValues.CleanValue
+ FROM ItemValues albumValues
+ INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId
+ INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND artistAlbums.TYPE = 1 AND artistItem.Guid = @SimilarItemId
+ ), similarArtist AS (
+ SELECT albumValues.ItemId
+ FROM ItemValues albumValues
+ INNER JOIN ItemValues artistAlbums ON albumValues.ItemId = artistAlbums.ItemId
+ INNER JOIN TypedBaseItems artistItem ON artistAlbums.CleanValue = artistItem.CleanName AND artistAlbums.TYPE = 1 AND artistItem.Guid = A.Guid
+ ) SELECT COUNT(DISTINCT(CleanValue)) * 10 FROM ItemValues WHERE ItemId IN (SELECT ItemId FROM similarArtist) AND CleanValue IN (SELECT CleanValue FROM artistValues))");
+ }
builder.Append(") as SimilarityScore");
@@ -2922,7 +2943,8 @@ namespace Emby.Server.Implementations.Data
var result = new QueryResult<BaseItem>();
using (var connection = GetConnection(true))
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var statements = PrepareAll(db, statementTexts);
@@ -3291,7 +3313,6 @@ namespace Emby.Server.Implementations.Data
}
}
-
var isReturningZeroItems = query.Limit.HasValue && query.Limit <= 0;
var statementTexts = new List<string>();
@@ -3326,7 +3347,8 @@ namespace Emby.Server.Implementations.Data
var result = new QueryResult<Guid>();
using (var connection = GetConnection(true))
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var statements = PrepareAll(db, statementTexts);
@@ -3910,7 +3932,7 @@ namespace Emby.Server.Implementations.Data
if (query.IsPlayed.HasValue)
{
// We should probably figure this out for all folders, but for right now, this is the only place where we need it
- if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], typeof(Series).Name, StringComparison.OrdinalIgnoreCase))
+ if (query.IncludeItemTypes.Length == 1 && string.Equals(query.IncludeItemTypes[0], nameof(Series), StringComparison.OrdinalIgnoreCase))
{
if (query.IsPlayed.Value)
{
@@ -4751,29 +4773,29 @@ namespace Emby.Server.Implementations.Data
{
var list = new List<string>();
- if (IsTypeInQuery(typeof(Person).Name, query))
+ if (IsTypeInQuery(nameof(Person), query))
{
- list.Add(typeof(Person).Name);
+ list.Add(nameof(Person));
}
- if (IsTypeInQuery(typeof(Genre).Name, query))
+ if (IsTypeInQuery(nameof(Genre), query))
{
- list.Add(typeof(Genre).Name);
+ list.Add(nameof(Genre));
}
- if (IsTypeInQuery(typeof(MusicGenre).Name, query))
+ if (IsTypeInQuery(nameof(MusicGenre), query))
{
- list.Add(typeof(MusicGenre).Name);
+ list.Add(nameof(MusicGenre));
}
- if (IsTypeInQuery(typeof(MusicArtist).Name, query))
+ if (IsTypeInQuery(nameof(MusicArtist), query))
{
- list.Add(typeof(MusicArtist).Name);
+ list.Add(nameof(MusicArtist));
}
- if (IsTypeInQuery(typeof(Studio).Name, query))
+ if (IsTypeInQuery(nameof(Studio), query))
{
- list.Add(typeof(Studio).Name);
+ list.Add(nameof(Studio));
}
return list;
@@ -4828,12 +4850,12 @@ namespace Emby.Server.Implementations.Data
var types = new[]
{
- typeof(Episode).Name,
- typeof(Video).Name,
- typeof(Movie).Name,
- typeof(MusicVideo).Name,
- typeof(Series).Name,
- typeof(Season).Name
+ nameof(Episode),
+ nameof(Video),
+ nameof(Movie),
+ nameof(MusicVideo),
+ nameof(Series),
+ nameof(Season)
};
if (types.Any(i => query.IncludeItemTypes.Contains(i, StringComparer.OrdinalIgnoreCase)))
@@ -4901,7 +4923,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
connection.ExecuteAll(sql);
}, TransactionMode);
@@ -4952,7 +4975,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var idBlob = id.ToByteArray();
@@ -4996,26 +5020,33 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
CheckDisposed();
- var commandText = "select Distinct Name from People";
+ var commandText = new StringBuilder("select Distinct p.Name from People p");
+
+ if (query.User != null && query.IsFavorite.HasValue)
+ {
+ commandText.Append(" LEFT JOIN TypedBaseItems tbi ON tbi.Name=p.Name AND tbi.Type='");
+ commandText.Append(typeof(Person).FullName);
+ commandText.Append("' LEFT JOIN UserDatas ON tbi.UserDataKey=key AND userId=@UserId");
+ }
var whereClauses = GetPeopleWhereClauses(query, null);
if (whereClauses.Count != 0)
{
- commandText += " where " + string.Join(" AND ", whereClauses);
+ commandText.Append(" where ").Append(string.Join(" AND ", whereClauses));
}
- commandText += " order by ListOrder";
+ commandText.Append(" order by ListOrder");
if (query.Limit > 0)
{
- commandText += " LIMIT " + query.Limit;
+ commandText.Append(" LIMIT ").Append(query.Limit);
}
using (var connection = GetConnection(true))
{
var list = new List<string>();
- using (var statement = PrepareStatement(connection, commandText))
+ using (var statement = PrepareStatement(connection, commandText.ToString()))
{
// Run this again to bind the params
GetPeopleWhereClauses(query, statement);
@@ -5039,7 +5070,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
CheckDisposed();
- var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People";
+ var commandText = "select ItemId, Name, Role, PersonType, SortOrder from People p";
var whereClauses = GetPeopleWhereClauses(query, null);
@@ -5081,19 +5112,13 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
if (!query.ItemId.Equals(Guid.Empty))
{
whereClauses.Add("ItemId=@ItemId");
- if (statement != null)
- {
- statement.TryBind("@ItemId", query.ItemId.ToByteArray());
- }
+ statement?.TryBind("@ItemId", query.ItemId.ToByteArray());
}
if (!query.AppearsInItemId.Equals(Guid.Empty))
{
- whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)");
- if (statement != null)
- {
- statement.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray());
- }
+ whereClauses.Add("p.Name in (Select Name from People where ItemId=@AppearsInItemId)");
+ statement?.TryBind("@AppearsInItemId", query.AppearsInItemId.ToByteArray());
}
var queryPersonTypes = query.PersonTypes.Where(IsValidPersonType).ToList();
@@ -5101,10 +5126,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
if (queryPersonTypes.Count == 1)
{
whereClauses.Add("PersonType=@PersonType");
- if (statement != null)
- {
- statement.TryBind("@PersonType", queryPersonTypes[0]);
- }
+ statement?.TryBind("@PersonType", queryPersonTypes[0]);
}
else if (queryPersonTypes.Count > 1)
{
@@ -5118,10 +5140,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
if (queryExcludePersonTypes.Count == 1)
{
whereClauses.Add("PersonType<>@PersonType");
- if (statement != null)
- {
- statement.TryBind("@PersonType", queryExcludePersonTypes[0]);
- }
+ statement?.TryBind("@PersonType", queryExcludePersonTypes[0]);
}
else if (queryExcludePersonTypes.Count > 1)
{
@@ -5133,19 +5152,24 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
if (query.MaxListOrder.HasValue)
{
whereClauses.Add("ListOrder<=@MaxListOrder");
- if (statement != null)
- {
- statement.TryBind("@MaxListOrder", query.MaxListOrder.Value);
- }
+ statement?.TryBind("@MaxListOrder", query.MaxListOrder.Value);
}
if (!string.IsNullOrWhiteSpace(query.NameContains))
{
- whereClauses.Add("Name like @NameContains");
- if (statement != null)
- {
- statement.TryBind("@NameContains", "%" + query.NameContains + "%");
- }
+ whereClauses.Add("p.Name like @NameContains");
+ statement?.TryBind("@NameContains", "%" + query.NameContains + "%");
+ }
+
+ if (query.IsFavorite.HasValue)
+ {
+ whereClauses.Add("isFavorite=@IsFavorite");
+ statement?.TryBind("@IsFavorite", query.IsFavorite.Value);
+ }
+
+ if (query.User != null)
+ {
+ statement?.TryBind("@UserId", query.User.InternalId);
}
return whereClauses;
@@ -5359,7 +5383,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
itemCountColumns = new Dictionary<string, string>()
{
- { "itemTypes", "(" + itemCountColumnQuery + ") as itemTypes"}
+ { "itemTypes", "(" + itemCountColumnQuery + ") as itemTypes" }
};
}
@@ -5414,6 +5438,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
NameStartsWithOrGreater = query.NameStartsWithOrGreater,
Tags = query.Tags,
OfficialRatings = query.OfficialRatings,
+ StudioIds = query.StudioIds,
GenreIds = query.GenreIds,
Genres = query.Genres,
Years = query.Years,
@@ -5586,7 +5611,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
return counts;
}
- var allTypes = typeString.Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries)
+ var allTypes = typeString.Split('|', StringSplitOptions.RemoveEmptyEntries)
.ToLookup(x => x);
foreach (var type in allTypes)
@@ -5746,7 +5771,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var itemIdBlob = itemId.ToByteArray();
@@ -5900,7 +5926,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var itemIdBlob = id.ToByteArray();
@@ -6006,7 +6033,6 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
}
}
-
/// <summary>
/// Gets the chapter.
/// </summary>
@@ -6235,7 +6261,8 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type
using (var connection = GetConnection())
{
- connection.RunInTransaction(db =>
+ connection.RunInTransaction(
+ db =>
{
var itemIdBlob = id.ToByteArray();
generated by cgit v1.2.3 (git 2.46.0) at 2025-12-07 20:22:16 +0000