public async Task RemoveLocationFromVideo(long locationId, long videoId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"DELETE FROM video_location WHERE video_id = {videoId} AND location_id = {locationId}", db);
command.ExecuteNonQuery();
}
}
public async Task PermanentlyRemoveVideo(long videoId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"DELETE FROM video WHERE video_id = {videoId}", db);
command.ExecuteNonQuery();
}
}
public async Task UpdateVideo(Video video)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetUpdateVideoCommand(db, video, "video_id", video.VideoId);
command.ExecuteNonQuery();
}
}
private void UpdateDeletedStatus(long videoId, bool deleted)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"UPDATE video SET deleted = {deleted}, deletion_due_to_cascade = false WHERE video_id = {videoId}", db);
command.ExecuteNonQuery();
}
}
private async Task UpdateDeletedStatus(long locationId, bool deleted)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var tagCommand = new SqliteCommand($"UPDATE location SET deleted = {deleted}, deletion_due_to_cascade = false WHERE location_id = {locationId}", db);
tagCommand.ExecuteNonQuery();
}
}
public async Task PermanentlyRemoveLocation(Location location)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var tagCommand = new SqliteCommand($"DELETE FROM location WHERE location_id = @LocationId", db);
tagCommand.Parameters.AddWithValue("@LocationId", location.LocationId);
tagCommand.ExecuteNonQuery();
}
}
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));
}
public void UpdateLibrary(Library library)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetUpdateLibraryCommand(db, library);
command.ExecuteNonQuery();
}
}
public void PermanentlyDeleteLibrary(long libraryId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var deleteTableCommand = new SqliteCommand($"DELETE FROM library WHERE library_id = {libraryId}", db);
deleteTableCommand.ExecuteNonQuery();
}
}
private void SetLibraryDeletedStatus(long libraryId, bool deleted)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var deleteTableCommand = new SqliteCommand($"UPDATE library SET deleted = {deleted} WHERE library_id = {libraryId}", db);
deleteTableCommand.ExecuteNonQuery();
}
}
private void PermanentlyRemoveTag(Tag tag)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var tagCommand = new SqliteCommand($"DELETE FROM tag WHERE tag_id = @TagId", db);
tagCommand.Parameters.AddWithValue("@TagId", tag.TagId);
tagCommand.ExecuteNonQuery();
}
}
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 void UpdateMediaFile(MediaFile file)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetUpdateMediaFileCommand(db, file, "media_id", file.MediaId);
command.ExecuteNonQuery();
}
}
private void UpsertTags(List <Tag> tags, string tableName)
{
if (tags.Count == 0)
{
return;
}
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
using (var txn = db.BeginTransaction()) {
var updateTagCommand = new SqliteCommand("UPDATE tag SET type = @Type, name = @Name WHERE unique_id = @UniqueId", db, txn);
updateTagCommand.Parameters.AddWithValue("@Name", "");
updateTagCommand.Parameters.AddWithValue("@Type", "");
updateTagCommand.Parameters.AddWithValue("@UniqueId", "");
// TODO: library id
var insertTagCommand = new SqliteCommand("INSERT OR IGNORE INTO tag(name, type, deleted, unique_id) VALUES (@Name, @Type, false, @UniqueId)", db, txn);
insertTagCommand.Parameters.AddWithValue("@Name", "");
insertTagCommand.Parameters.AddWithValue("@Type", "");
insertTagCommand.Parameters.AddWithValue("@UniqueId", "");
var selectTagCommand = new SqliteCommand("SELECT tag_id FROM tag WHERE unique_id = @UniqueId", db, txn);
selectTagCommand.Parameters.AddWithValue("@UniqueId", "");
var insertIntoOtherTableCommand = new SqliteCommand($"INSERT OR IGNORE INTO {tableName}(tag_id) VALUES (@TagId)", db, txn);
insertIntoOtherTableCommand.Parameters.AddWithValue("@TagId", 0);
foreach (var tag in tags)
{
if (tag.Name == null || tag.Type == null || tag.UniqueId == null)
{
continue;
}
updateTagCommand.Parameters["@Name"].Value = tag.Name;
updateTagCommand.Parameters["@Type"].Value = tag.Type;
updateTagCommand.Parameters["@UniqueId"].Value = tag.UniqueId;
updateTagCommand.ExecuteNonQuery();
// TODO: library id
insertTagCommand.Parameters["@Name"].Value = tag.Name;
insertTagCommand.Parameters["@Type"].Value = tag.Type;
insertTagCommand.Parameters["@UniqueId"].Value = tag.UniqueId;
insertTagCommand.ExecuteNonQuery();
selectTagCommand.Parameters["@UniqueId"].Value = tag.UniqueId;
var tagId = (long)selectTagCommand.ExecuteScalar();
insertIntoOtherTableCommand.Parameters["@TagId"].Value = tagId;
insertIntoOtherTableCommand.ExecuteNonQuery();
}
txn.Commit();
}
}
}
public void UpdateCharacter(Character character)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetUpdateCharacterCommand(db, character, "character_id", character.CharacterId);
command.ExecuteNonQuery();
}
}
public void UpdateCharacterInVideo(CreatorOfVideoFullDto dto, long characterId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var clearCommand = new SqliteCommand($"UPDATE actor_for_video_character SET video_id = {dto.Video.VideoId}, character_id = {characterId}, creator_id = @ActorId WHERE video_id = {dto.Video.VideoId} AND character_id = {characterId}", db);
clearCommand.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(dto.ActorId));
clearCommand.ExecuteNonQuery();
}
}
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 <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));
}
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 RemoveCharacterFromVideo(long characterId, long videoId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"DELETE FROM actor_for_video_character WHERE character_id = {characterId} AND video_id = {videoId}", db);
command.ExecuteReader();
}
}
private void SetTagDeletedStatus(Tag tag, bool deleted)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var tagCommand = new SqliteCommand($"UPDATE tag SET deleted = {deleted} WHERE tag_id = @TagId", db);
tagCommand.Parameters.AddWithValue("@TagId", tag.TagId);
tagCommand.ExecuteNonQuery();
}
}
public void UpdateLibrarySettings(long libraryId, LibrarySettings settings)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
using (var txn = db.BeginTransaction()) {
var command = new SqliteCommand($"UPDATE library_setting SET value = @Value WHERE library_id = {libraryId} AND name = @Key", db, txn);
command.Parameters.AddWithValue("@Key", nameof(settings.PublisherLabel));
command.Parameters.AddWithValue("@Value", settings.PublisherLabel);
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.SeriesLabel);
command.Parameters["@Value"].Value = settings.SeriesLabel;
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.CharacterLabel);
command.Parameters["@Value"].Value = settings.CharacterLabel;
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.VideoLabel);
command.Parameters["@Value"].Value = settings.VideoLabel;
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.VideoPreviewDateFormat);
command.Parameters["@Value"].Value = settings.VideoPreviewDateFormat;
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.ShowCharacters);
command.Parameters["@Value"].Value = settings.ShowCharacters.ToString();
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.ShowCreators);
command.Parameters["@Value"].Value = settings.ShowCreators.ToString();
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.ShowLocations);
command.Parameters["@Value"].Value = settings.ShowLocations.ToString();
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.ShowPublishers);
command.Parameters["@Value"].Value = settings.ShowPublishers.ToString();
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.ShowSeries);
command.Parameters["@Value"].Value = settings.ShowSeries.ToString();
command.ExecuteNonQuery();
command.Parameters["@Key"].Value = nameof(settings.ShowVideos);
command.Parameters["@Value"].Value = settings.ShowVideos.ToString();
command.ExecuteNonQuery();
txn.Commit();
}
}
}
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));
}
public void AddCharactersInVideo(long videoId, List <ActorForCharacterFullDto> characters)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
using (var txn = db.BeginTransaction()) {
InsertCharacterInVideoDetails(videoId, characters, db, txn);
txn.Commit();
}
}
}
public override async Task <TResult> ReadFromApplicationPackage <TResult>(string filePath)
{
var documentPath = NSFileManager.DefaultManager.GetUrls(NSSearchPathDirectory.ApplicationDirectory, NSSearchPathDomain.User)[0].Path;
var fullFilePath = Path.Combine(documentPath, filePath);
await Task.Delay(1);
var content = File.ReadAllText(fullFilePath);
return(DataAccessUtil.DeserializeObject <TResult>(content));
}
public long CreateSeries(CreateSeriesDto dto)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = GetCreateSeriesCommand(db, dto, UniqueIdUtil.GenerateUniqueId(), false);
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db));
}
}
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 <long> AddWatchedVideo(long videoId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var command = new SqliteCommand($"INSERT INTO watch_history(video_id, watch_date) VALUES({videoId}, {"CURRENT_TIMESTAMP"})", db);
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db));
}
}
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));
}
