public async Task <PaginationResult <Character> > SearchForCharacters(Pagination pagination, long libraryId, List <ICharacterSearchQueryGenerator> subqueryGenerators)
{
if (subqueryGenerators.Count == 0)
{
return(PaginationResult <Character> .CreateResultFromCurrentPage(new List <Character>(), pagination));
}
var subqueries = subqueryGenerators.Select((s) => {
return(s.GetSearchQuery());
});
var tables = string.Join(" INTERSECT ", subqueries);
var command = $"SELECT {SELECT_BASE_COLUMNS_STRING} FROM character c, ({tables}) AS ci WHERE c.character_id = ci.character_id AND c.deleted = false AND c.library_id = {libraryId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseCharacter));
}
public async Task <PaginationResult <Series> > SearchForSeries(Pagination pagination, long libraryId, List <ISeriesSearchQueryGenerator> subqueryGenerators)
{
if (subqueryGenerators.Count == 0)
{
return(PaginationResult <Series> .CreateResultFromCurrentPage(new List <Series>(), pagination));
}
var subqueries = subqueryGenerators.Select((s) => {
return(s.GetSearchQuery());
});
var tables = string.Join(" INTERSECT ", subqueries);
var command = $"{SELECT_BASE}, ({tables}) AS si WHERE s.series_id = si.series_id AND s.deleted = false AND s.library_id = {libraryId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseSeries));
}
public async Task <PaginationResult <MediaFile> > SearchForFiles(Pagination pagination, List <IMediaFileSearchQueryGenerator> subqueryGenerators)
{
if (subqueryGenerators.Count == 0)
{
return(PaginationResult <MediaFile> .CreateResultFromCurrentPage(new List <MediaFile>(), pagination));
}
var subqueries = subqueryGenerators.Select((s) => {
return(s.GetSearchQuery());
});
var tables = string.Join(" INTERSECT ", subqueries);
var command = $"{SELECT_BASE}, ({tables}) AS mi WHERE m.media_id = mi.media_id";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseMediaFile));
}
public async Task <PaginationResult <Video> > SearchForVideos(Pagination pagination, long libraryId, List <IVideoSearchQueryGenerator> subqueryGenerators)
{
if (subqueryGenerators.Count == 0)
{
return(PaginationResult <Video> .CreateResultFromCurrentPage(new List <Video>(), pagination));
}
var subqueries = subqueryGenerators.Select((s) => {
return(s.GetSearchQuery());
});
var tables = string.Join(" INTERSECT ", subqueries);
var command = $"SELECT {SELECT_BASE_COLUMNS_STRING} FROM video v, ({tables}) AS vi WHERE v.video_id = vi.video_id AND v.deleted = false AND v.library_id = {libraryId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseVideo));
}
public static async Task <PaginationResult <T> > GetPaginatedResult <T>(Pagination pagination, string baseQuery, Action <SqliteCommand> parameterizeQuery, Func <SqliteDataReader, T> parseRecord)
{
using (var db = CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"{baseQuery} {pagination.GetQueryString()}", db);
parameterizeQuery(command);
var query = command.ExecuteReader();
var records = new List <T>();
while (query.Read())
{
records.Add(parseRecord(query));
}
return(PaginationResult <T> .CreateResultFromCurrentPage(records, pagination));
}
}