private SqliteCommand GetCreateVideoCommand(SqliteConnection db, CreateVideoDto dto, string guid, bool ignoreDuplicates)
{
var ignoreClause = ignoreDuplicates ? "OR IGNORE" : "";
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"INSERT {ignoreClause} INTO video(title, duration_in_seconds, external_rating, user_rating, description, notes, source_url, site_url, series_id, watch_status, publisher_id, library_id, deleted, deletion_due_to_cascade, times_watched, release_date, timeline_date, cover_image, unique_id) VALUES(@Title, @Duration, @ExternalRating, @UserRating, @Description, @Notes, @SourceURL, @SiteURL, @SeriesId, @WatchStatus, @PublisherId, @LibraryId, false, false, @TimesWatched, @ReleaseDate, @TimelineDate, @CoverImage, @UniqueId)";
command.Parameters.AddWithValue("@Title", dto.Title);
command.Parameters.AddWithValue("@Duration", QueryUtil.GetNullableValueForStorage(dto.DurationInSeconds));
command.Parameters.AddWithValue("@ExternalRating", dto.ExternalRating);
command.Parameters.AddWithValue("@UserRating", dto.UserRating);
command.Parameters.AddWithValue("@Description", dto.Description);
command.Parameters.AddWithValue("@Notes", dto.Notes);
command.Parameters.AddWithValue("@SourceURL", dto.SourceURL);
command.Parameters.AddWithValue("@SiteURL", dto.SiteURL);
command.Parameters.AddWithValue("@SeriesId", QueryUtil.GetNullableIdForStorage(dto.SeriesId));
command.Parameters.AddWithValue("@WatchStatus", dto.WatchStatus);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(dto.PublisherId));
command.Parameters.AddWithValue("@LibraryId", dto.LibraryId);
command.Parameters.AddWithValue("@ReleaseDate", QueryUtil.GetNullableValueForStorage(dto.ReleaseDate));
command.Parameters.AddWithValue("@TimelineDate", QueryUtil.GetNullableValueForStorage(dto.TimelineDate));
command.Parameters.AddWithValue("@CoverImage", QueryUtil.GetNullableIdForStorage(dto.CoverFileId));
command.Parameters.AddWithValue("@UniqueId", guid);
command.Parameters.AddWithValue("@TimesWatched", dto.TimesWatched);
return(command);
}
private SqliteCommand GetUpdateVideoCommand(SqliteConnection db, Video video, string idColumn, object idValue)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $@"
UPDATE video SET title = @Title, times_watched = @TimesWatched, last_watch_date = @LastWatchDate,
duration_in_seconds = @Duration, external_rating = @ExternalRating, user_rating = @UserRating,
description = @Description, notes = @Notes, source_url = @SourceURL, site_url = @SiteURL,
series_id = @SeriesId, watch_status = @WatchStatus, publisher_id = @PublisherId, library_id = @LibraryId,
release_date = @ReleaseDate, timeline_date = @TimelineDate, cover_image = @CoverImage
WHERE {idColumn} = @VideoId";
command.Parameters.AddWithValue("@Title", video.Title);
command.Parameters.AddWithValue("@TimesWatched", video.TimesWatched);
command.Parameters.AddWithValue("@LastWatchDate", QueryUtil.GetNullableValueForStorage(video.LastWatchDate));
command.Parameters.AddWithValue("@Duration", QueryUtil.GetNullableValueForStorage(video.DurationInSeconds));
command.Parameters.AddWithValue("@ExternalRating", video.ExternalRating);
command.Parameters.AddWithValue("@UserRating", video.UserRating);
command.Parameters.AddWithValue("@Description", video.Description);
command.Parameters.AddWithValue("@Notes", video.Notes);
command.Parameters.AddWithValue("@SourceURL", video.SourceURL);
command.Parameters.AddWithValue("@SiteURL", video.SiteURL);
command.Parameters.AddWithValue("@SeriesId", QueryUtil.GetNullableIdForStorage(video.SeriesId));
command.Parameters.AddWithValue("@WatchStatus", video.WatchStatus);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(video.PublisherId));
command.Parameters.AddWithValue("@LibraryId", video.LibraryId);
command.Parameters.AddWithValue("@VideoId", idValue);
command.Parameters.AddWithValue("@ReleaseDate", QueryUtil.GetNullableValueForStorage(video.ReleaseDate));
command.Parameters.AddWithValue("@TimelineDate", QueryUtil.GetNullableValueForStorage(video.TimelineDate));
command.Parameters.AddWithValue("@CoverImage", QueryUtil.GetNullableIdForStorage(video.CoverFileId));
return(command);
}
private SqliteCommand GetUpdateCharacterCommand(SqliteConnection db, Character character, string idColumn, object idValue)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"UPDATE character SET name = @Name, description = @Description, library_id = @LibraryId, birth_date = @BirthDate, career_start_date = @CareerStartDate, career_end_date = @CareerEndDate, rating = @Rating, calendar_id = @CalendarId, cover_file_id = @CoverFileId WHERE {idColumn} = @CharacterId";
command.Parameters.AddWithValue("@Name", character.Name);
command.Parameters.AddWithValue("@Description", character.Description);
command.Parameters.AddWithValue("@LibraryId", character.LibraryId);
command.Parameters.AddWithValue("@BirthDate", QueryUtil.GetNullableValueForStorage(character.BirthDate));
command.Parameters.AddWithValue("@CareerStartDate", QueryUtil.GetNullableValueForStorage(character.CareerStartDate));
command.Parameters.AddWithValue("@CareerEndDate", QueryUtil.GetNullableValueForStorage(character.CareerEndDate));
command.Parameters.AddWithValue("@Rating", character.Rating);
command.Parameters.AddWithValue("@CoverFileId", QueryUtil.GetNullableIdForStorage(character.CoverMediaId));
command.Parameters.AddWithValue("@CharacterId", idValue);
command.Parameters.AddWithValue("@CalendarId", QueryUtil.GetNullableIdForStorage(character.CalendarId));
return(command);
}
private SqliteCommand GetCreateCharacterCommand(SqliteConnection db, CreateCharacterDto dto, string guid, bool ignoreDuplicates)
{
var ignoreClause = ignoreDuplicates ? "OR IGNORE" : "";
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"INSERT {ignoreClause} INTO character(name, description, library_id, birth_date, career_start_date, career_end_date, rating, deleted, deletion_due_to_cascade, cover_file_id, calendar_id, creator, unique_id) VALUES(@Name, @Description, @LibraryId, @BirthDate, @CareerStartDate, @CareerEndDate, @Rating, false, false, @CoverFileId, @CalendarId, @IsCreator, @UniqueId)";
command.Parameters.AddWithValue("@Name", dto.Name);
command.Parameters.AddWithValue("@Description", dto.Description);
command.Parameters.AddWithValue("@LibraryId", dto.LibraryId);
command.Parameters.AddWithValue("@BirthDate", QueryUtil.GetNullableValueForStorage(dto.BirthDate));
command.Parameters.AddWithValue("@CareerStartDate", QueryUtil.GetNullableValueForStorage(dto.CareerStartDate));
command.Parameters.AddWithValue("@CareerEndDate", QueryUtil.GetNullableValueForStorage(dto.CareerEndDate));
command.Parameters.AddWithValue("@Rating", dto.Rating);
command.Parameters.AddWithValue("@CoverFileId", QueryUtil.GetNullableIdForStorage(dto.CoverMediaId));
command.Parameters.AddWithValue("@CalendarId", QueryUtil.GetNullableIdForStorage(dto.CalendarId));
command.Parameters.AddWithValue("@IsCreator", dto.IsCreator);
command.Parameters.AddWithValue("@UniqueId", guid);
return(command);
}
