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)); } }