protected async Task <PaginationResult <VideoSequence> > GetVideoSequences(Pagination pagination, bool deleted, long parentId = DatabaseConstants.DEFAULT_ID)
{
var parentClause = parentId == DatabaseConstants.DEFAULT_ID ? "" : $"c.{GetParentColumnName()} = {parentId} AND";
var command = $"{SELECT_BASE}, {GetChildTableName()} c WHERE {parentClause} vs.sequence_id = c.sequence_id AND deleted = {deleted}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseVideoSequence));
}
public async Task <PaginationResult <MediaFile> > GetMediaFiles(Pagination pagination, string nameFilter = "")
{
var query = $"{SELECT_BASE} WHERE name LIKE @NameFilter";
return(await DataAccessUtil.GetPaginatedResult(pagination, query, (SqliteCommand command) => {
command.Parameters.AddWithValue("@NameFilter", "%" + nameFilter + "%");
}, ParseMediaFile));
}
private async Task <PaginationResult <Series> > GetSeriesInLibrary(Pagination pagination, bool deleted, long libraryId, string nameFilter)
{
var query = $"{SELECT_BASE} WHERE library_id = {libraryId} AND deleted = {deleted} AND name LIKE @NameFilter";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseSeries));
}
public async Task <PaginationResult <Character> > GetVideoCreatorsNotInVideo(Pagination pagination, long videoId, long libraryId, string nameFilter)
{
var query = $"SELECT {SELECT_BASE_COLUMNS_STRING} FROM character c WHERE c.deleted = false AND c.creator = true AND c.name LIKE @NameFilter AND c.library_id = {libraryId} AND c.character_id NOT IN (SELECT vc.creator_id FROM video_creator vc WHERE vc.video_id = {videoId})";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", "%" + nameFilter + "%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseCharacter));
}
public async Task <PaginationResult <Video> > GetVideosCharacterIsNotIn(Pagination pagination, long libraryId, long characterId, string titleFilter)
{
var query = $"SELECT {SELECT_BASE_COLUMNS_STRING} FROM video v WHERE v.library_id = {libraryId} AND v.deleted = false AND v.title LIKE @TitleFilter AND v.video_id NOT IN (SELECT av.video_id FROM actor_for_video_character av WHERE av.character_id = {characterId})";
Action <SqliteCommand> parameterize = (SqliteCommand command) => {
command.Parameters.AddWithValue("@TitleFilter", "%" + titleFilter + "%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseVideo));
}
private async Task <PaginationResult <MediaFile> > GetFilesNotOnEntity(Pagination pagination, long videoId, string nameFilter, string tableName, string entityIdColumn)
{
var query = $"{SELECT_BASE} WHERE name LIKE @NameFilter AND media_id NOT IN (SELECT media_id FROM {tableName} WHERE {entityIdColumn} = {videoId})";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseMediaFile));
}
public async Task <PaginationResult <Library> > GetLibraries(Pagination pagination, string nameFilter, bool deleted = false)
{
var query = $"{SELECT_BASE} WHERE deleted = {deleted} AND name LIKE @NameFilter";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseLibrary));
}
private async Task <PaginationResult <PlaylistDto> > GetPlaylists(Pagination pagination, bool deleted, long parentId = DatabaseConstants.DEFAULT_ID, string nameFilter = "")
{
var parentClause = parentId == DatabaseConstants.DEFAULT_ID ? $"p.{GetParentColumnName()} IS NULL AND" : $"p.{GetParentColumnName()} = {parentId} AND";
var query = $"{SELECT_BASE} WHERE {parentClause} vs.sequence_id = p.sequence_id AND deleted = {deleted} AND vs.title LIKE @NameFilter";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParsePlaylist));
}
private async Task <PaginationResult <Character> > GetCharacters(Pagination pagination, bool deleted, bool creator, long libraryId = DatabaseConstants.DEFAULT_ID, string nameFilter = "")
{
var libraryClause = libraryId == DatabaseConstants.DEFAULT_ID ? "" : $"c.library_id = {libraryId} AND";
var query = $"{SELECT_BASE} WHERE {libraryClause} c.deleted = {deleted} AND c.creator = {creator} AND c.name LIKE @NameFilter";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", "%" + nameFilter + "%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseCharacter));
}
private async Task <PaginationResult <Calendar> > GetCalendars(Pagination pagination, bool deleted, string nameFilter = "", long libraryId = DatabaseConstants.DEFAULT_ID)
{
var libraryClause = libraryId == DatabaseConstants.DEFAULT_ID ? "" : $"library_id = {libraryId} AND";
var query = $"{SELECT_BASE} WHERE {libraryClause} deleted = {deleted} AND name LIKE @NameFilter";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseCalendar));
}
public async Task <PaginationResult <Video> > GetVideosToWatchInLibrary(long libraryId, Pagination pagination, string nameFilter)
{
var query = $"{SELECT_BASE} WHERE library_id = {libraryId} AND deleted = false AND watch_status = @WatchStatus AND v.title LIKE @NameFilter";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
command.Parameters.AddWithValue("@WatchStatus", VideoWatchStatus.NEED_TO_WATCH);
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseVideo));
}
public async Task <PaginationResult <Video> > GetVideosNotInPlaylist(PlaylistDto playlist, long libraryId, string titleFilter, Pagination pagination)
{
var videosToExcludeQuery = $"SELECT vs.video_id FROM video_in_sequence vs WHERE vs.sequence_id = {playlist.SequenceId}";
var libraryClause = libraryId == DatabaseConstants.DEFAULT_ID ? "" : $"v.library_id = {libraryId} AND";
var query = $"{SELECT_BASE} WHERE v.deleted = false AND v.title LIKE @TitleFilter AND {libraryClause} v.video_id NOT IN ({videosToExcludeQuery})";
Action <SqliteCommand> parameterize = (SqliteCommand command) => {
command.Parameters.AddWithValue("@TitleFilter", "%" + titleFilter + "%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseVideo));
}
public async Task <PaginationResult <Series> > GetSeriesToWatchInLibrary(Pagination pagination, long libraryId, string nameFilter)
{
var finishedSeriesNestedSubquery = $"SELECT series_id, SUM(CASE WHEN times_watched > 0 THEN 1 ELSE 0 END) AS watched_count, COUNT(*) AS total_count FROM video WHERE library_id = {libraryId} AND deleted = false GROUP BY series_id HAVING watched_count = total_count";
var finishedSeriesSubquery = $"SELECT series_id FROM ({finishedSeriesNestedSubquery})";
var query = $"{SELECT_BASE} WHERE library_id = {libraryId} AND deleted = false AND name LIKE @NameFilter AND worth_watching = true AND series_id NOT IN ({finishedSeriesSubquery})";
Action <SqliteCommand> parameterize = (command) => {
command.Parameters.AddWithValue("@NameFilter", $"%{nameFilter}%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseSeries));
}
public async Task <PaginationResult <Video> > GetVideosNotInSeriesSequence(SeriesSequence sequence, long seriesId, string titleFilter, Pagination pagination)
{
var videosToExcludeQuery = sequence.IsSeason ?
($"SELECT vs.video_id FROM video_in_sequence vs, series_sequence ss WHERE vs.sequence_id = ss.sequence_id AND ss.series_id = {seriesId} AND ss.is_season = true AND ss.deleted = false") :
($"SELECT vs.video_id FROM video_in_sequence vs WHERE vs.sequence_id = {sequence.SequenceId}");
var query = $"{SELECT_BASE} WHERE v.deleted = false AND v.title LIKE @TitleFilter AND v.series_id = {seriesId} AND v.video_id NOT IN ({videosToExcludeQuery})";
Action <SqliteCommand> parameterize = (SqliteCommand command) => {
command.Parameters.AddWithValue("@TitleFilter", "%" + titleFilter + "%");
};
return(await DataAccessUtil.GetPaginatedResult(pagination, query, parameterize, ParseVideo));
}
private async Task <PaginationResult <Series> > GetSeriesCharacterIsIn(long characterId, string characterIdColumnName, Pagination pagination)
{
var columns = SELECT_BASE_COLUMNS_STRING.Replace("s.series_id", "DISTINCT(s.series_id)");
var query = $"SELECT {columns} FROM series s, actor_for_video_character a, video v WHERE s.deleted = false AND v.deleted = false AND a.video_id = v.video_id AND v.series_id = s.series_id AND a.{characterIdColumnName} = {characterId}";
var secondQuery = "";
if (characterIdColumnName == "creator_id")
{
secondQuery = $"UNION SELECT {columns} FROM series s, video_creator vc, video v WHERE s.deleted = false AND v.deleted = false AND vc.video_id = v.video_id AND v.series_id = s.series_id AND vc.creator_id = {characterId}";
}
return(await DataAccessUtil.GetPaginatedResult(pagination, $"{query} {secondQuery}", ParseSeries));
}
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 <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 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 <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));
}
private async Task <PaginationResult <SeriesSequence> > GetVideoSequencesInSeries(long seriesId, Pagination pagination, bool deleted)
{
var command = $"SELECT vs.sequence_id, vs.title, vs.description, vs.cover_file, c.deleted, c.is_season, c.season_number, c.series_id, c.unique_id FROM video_sequence vs, {GetChildTableName()} c WHERE c.series_id = {seriesId} AND vs.sequence_id = c.sequence_id AND c.deleted = {deleted}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseSeriesSequence));
}
public async Task <PaginationResult <SeriesSequence> > GetAllSequencesInAllSeries(Pagination pagination, long libraryId)
{
var command = $"SELECT vs.sequence_id, vs.title, vs.description, vs.cover_file, c.deleted, c.is_season, c.season_number, c.series_id, c.unique_id FROM video_sequence vs, {GetChildTableName()} c, series s WHERE s.library_id = {libraryId} AND s.deleted = false AND c.series_id = s.series_id AND vs.sequence_id = c.sequence_id AND c.deleted = false";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseSeriesSequence));
}
public async Task <PaginationResult <VideosInSequencesExportDto> > GetAllVideosInSeriesSequences(Pagination pagination, long libraryId)
{
var command = $"SELECT v.unique_id, c.unique_id, vis.order_in_list FROM video v, series s, video_in_sequence vis, {GetChildTableName()} c WHERE v.video_id = vis.video_id AND vis.sequence_id = c.sequence_id AND s.series_id = c.series_id AND v.deleted = false AND s.deleted = false AND v.library_id = {libraryId} AND s.library_id = {libraryId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseVideosInSequencesExport));
}
public async Task <PaginationResult <Character> > GetCharactersWithFile(Pagination pagination, long fileId)
{
var command = $"{SELECT_BASE}, character_media_file vl WHERE vl.character_id = c.character_id AND vl.media_id = {fileId} AND c.deleted = false UNION {SELECT_BASE} WHERE c.cover_file_id = {fileId} AND c.deleted = false";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseCharacter));
}
public async Task <PaginationResult <VideoCreator> > GetVideoCreators(Pagination pagination, long videoId)
{
var command = $"SELECT {SELECT_BASE_COLUMNS_STRING}, vc.role FROM character c, video_creator vc WHERE c.deleted = false AND c.creator = true AND c.character_id = vc.creator_id AND vc.video_id = {videoId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseVideoCreator));
}
public async Task <PaginationResult <MediaFile> > GetFilesForCharacter(Pagination pagination, long characterId)
{
var command = $"{SELECT_BASE}, character_media_file cmf WHERE cmf.character_id = {characterId} AND cmf.media_id = m.media_id";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseMediaFile));
}
private async Task <PaginationResult <Character> > GetCharactersInSeries(long seriesId, Pagination pagination, string characterIdColumnName)
{
var command = $"{SELECT_BASE}, actor_for_video_character a, video v WHERE v.series_id = {seriesId} AND v.deleted = false AND v.video_id = a.video_id AND a.{characterIdColumnName} = c.character_id AND c.deleted = false";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseCharacter));
}
private async Task <PaginationResult <Series> > GetSeriesByPublisher(Pagination pagination, bool deleted, long publisherId)
{
var command = $"{SELECT_BASE} WHERE publisher_id = {publisherId} AND deleted = {deleted}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseSeries));
}
public async Task <PaginationResult <VideoMediaFilesExportDto> > GetAllFilesOnVideos(Pagination pagination, long libraryId)
{
var command = $"SELECT v.unique_id, mf.unique_id FROM video v, media_file mf, video_media_file vmf WHERE vmf.video_id = v.video_id AND vmf.media_id = mf.media_id AND v.deleted = false AND v.library_id = {libraryId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseVideoMediaFile));
}
private async Task <PaginationResult <Series> > GetAllSeries(Pagination pagination, bool deleted)
{
var command = $"{SELECT_BASE} WHERE deleted = {deleted}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseSeries));
}
public async Task <PaginationResult <CharacterMediaFilesExportDto> > GetAllFilesOnCharacters(Pagination pagination, long libraryId)
{
var command = $"SELECT c.unique_id, mf.unique_id FROM character c, media_file mf, character_media_file vmf WHERE vmf.character_id = c.character_id AND vmf.media_id = mf.media_id AND c.deleted = false AND c.library_id = {libraryId}";
return(await DataAccessUtil.GetPaginatedResult(pagination, command, ParseCharacterMediaFile));
}