public async Task UpsertCharactersInVideos(List <VideoCharacterActorExportDto> characters, Dictionary <string, long> ids)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
using (var txn = db.BeginTransaction()) {
foreach (var c in characters)
{
if (c.CharacterId == null || c.VideoId == null)
{
continue;
}
var charId = ids[c.CharacterId];
var videoId = ids[c.VideoId];
var actorId = c.ActorId == null ? DatabaseConstants.DEFAULT_ID : ids[c.ActorId];
var command = new SqliteCommand($"UPDATE actor_for_video_character SET creator_id = @ActorId WHERE character_id = {charId} AND video_id = {videoId}", db, txn);
command.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(actorId));
command.ExecuteNonQuery();
command = new SqliteCommand($"INSERT OR IGNORE INTO actor_for_video_character(character_id, video_id, creator_id) VALUES({charId}, {videoId}, @ActorId)", db, txn);
command.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(actorId));
command.ExecuteNonQuery();
}
txn.Commit();
}
}
}
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);
}
public long CreateLibrary(CreateLibraryDto library, string guid = null)
{
if (guid == null)
{
guid = UniqueIdUtil.GenerateUniqueId();
}
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
long createdLibraryId = -1;
using (var txn = db.BeginTransaction()) {
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = "INSERT INTO library VALUES(NULL, @Name, @BackgroundId, false, @UniqueId)";
command.Parameters.AddWithValue("@Name", library.Name);
command.Parameters.AddWithValue("@BackgroundId", QueryUtil.GetNullableIdForStorage(library.BackgroundImageId));
command.Parameters.AddWithValue("@UniqueId", guid);
command.Transaction = txn;
command.ExecuteNonQuery();
createdLibraryId = QueryUtil.GetLastInsertedPrimaryKey(db, txn);
foreach (var setting in LibrarySettings.DEFAULT_SETTINGS)
{
var createSettingsCommand = new SqliteCommand($"INSERT INTO library_setting VALUES({createdLibraryId}, '{setting.Key}', '{setting.Value}')", db, txn);
createSettingsCommand.ExecuteNonQuery();
}
txn.Commit();
}
return(createdLibraryId);
}
}
protected long CreateVideoSequence(CreateVideoSequenceDto dto, SqliteConnection db, SqliteTransaction txn)
{
var sequenceId = CreateVideoSequence(db, txn, dto);
var columnNames = new List <string>()
{
"sequence_id", GetParentColumnName()
};
columnNames.AddRange(GetChildTableAdditionalColumns());
var columnValues = new List <string>()
{
sequenceId.ToString(), "@ParentColumnValue"
};
columnValues.AddRange(GetChildTableAdditionalValues(dto));
var command = new SqliteCommand($"INSERT INTO {GetChildTableName()}({string.Join(",", columnNames)}) VALUES ({string.Join(",", columnValues)})", db, txn);
command.Parameters.AddWithValue("@ParentColumnValue", QueryUtil.GetNullableIdForStorage(GetParentColumnValue(dto)));
command.ExecuteNonQuery();
return(sequenceId);
}
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);
}
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();
}
}
private SqliteCommand GetUpdateLibraryCommand(SqliteConnection db, Library library)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"UPDATE library SET name = @Name, background_image_file_id = @BackgroundId WHERE library_id = {library.LibraryId}";
command.Parameters.AddWithValue("@Name", library.Name);
command.Parameters.AddWithValue("@BackgroundId", QueryUtil.GetNullableIdForStorage(library.BackgroundImageId));
return(command);
}
protected SqliteCommand GetCreateVideoSequenceCommand(SqliteConnection db, CreateVideoSequenceDto dto)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = "INSERT INTO video_sequence(title, description, cover_file) VALUES(@Name, @Description, @CoverId)";
command.Parameters.AddWithValue("@Name", dto.Title);
command.Parameters.AddWithValue("@Description", dto.Description);
command.Parameters.AddWithValue("@CoverId", QueryUtil.GetNullableIdForStorage(dto.CoverFile));
return(command);
}
private void InsertCharacterInVideoDetails(long videoId, List <ActorForCharacterFullDto> characters, SqliteConnection db, SqliteTransaction txn)
{
foreach (var character in characters)
{
var tagCommand = new SqliteCommand($"INSERT INTO actor_for_video_character(character_id, video_id, creator_id) VALUES(@CharacterId, {videoId}, @CreatorId)", db, txn);
tagCommand.Parameters.AddWithValue("@CharacterId", character.Character.CharacterId);
tagCommand.Parameters.AddWithValue("@CreatorId", QueryUtil.GetNullableIdForStorage(character.ActorId));
tagCommand.ExecuteNonQuery();
}
}
protected SqliteCommand GetUpdateVideoSequenceCommand(SqliteConnection db, VideoSequence sequence, string idColumn, object idValue)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"UPDATE video_sequence SET title = @Name, description = @Description, cover_file = @CoverFile WHERE {idColumn} = @VideoSequenceId";
command.Parameters.AddWithValue("@Name", sequence.Title);
command.Parameters.AddWithValue("@Description", sequence.Description);
command.Parameters.AddWithValue("@CoverFile", QueryUtil.GetNullableIdForStorage(sequence.CoverFile));
command.Parameters.AddWithValue("@VideoSequenceId", idValue);
return(command);
}
private SqliteCommand GetUpdateLocationCommand(SqliteConnection db, Location location, string idColumn, object idValue)
{
var command = new SqliteCommand($"UPDATE location SET name = @Name, description = @Description, library_id = @LibraryId, publisher_id = @PublisherId, cover_file_id = @CoverId WHERE {idColumn} = @LocationId", db);
command.Parameters.AddWithValue("@Name", location.Name);
command.Parameters.AddWithValue("@Description", location.Description);
command.Parameters.AddWithValue("@LibraryId", location.LibraryId);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(location.PublisherId));
command.Parameters.AddWithValue("@CoverId", QueryUtil.GetNullableIdForStorage(location.CoverFileId));
command.Parameters.AddWithValue("@LocationId", idValue);
return(command);
}
private SqliteCommand GetUpdatePublisherCommand(SqliteConnection db, Publisher publisher, string idColumn, object idValue)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"UPDATE publisher SET name = @Name, site_url = @Url, logo_image = @LogoId, description = @Description, library_id = @LibraryId WHERE {idColumn} = @PublisherId";
command.Parameters.AddWithValue("@Name", publisher.Name);
command.Parameters.AddWithValue("@Url", publisher.SiteURL);
command.Parameters.AddWithValue("@Description", publisher.Description);
command.Parameters.AddWithValue("@LibraryId", publisher.LibraryId);
command.Parameters.AddWithValue("@LogoId", QueryUtil.GetNullableIdForStorage(publisher.LogoFileId));
command.Parameters.AddWithValue("@PublisherId", idValue);
return(command);
}
private SqliteCommand GetCreateLocationCommand(SqliteConnection db, CreateLocationDto location, string guid, bool ignoreDuplicates)
{
var ignoreClause = ignoreDuplicates ? "OR IGNORE" : "";
var command = new SqliteCommand($"INSERT {ignoreClause} INTO location(name, description, library_id, deleted, deletion_due_to_cascade, publisher_id, cover_file_id, unique_id) VALUES (@Name, @Description, @LibraryId, false, false, @PublisherId, @CoverId, @UniqueId)", db);
command.Parameters.AddWithValue("@Name", location.Name);
command.Parameters.AddWithValue("@Description", location.Description);
command.Parameters.AddWithValue("@LibraryId", location.LibraryId);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(location.PublisherId));
command.Parameters.AddWithValue("@CoverId", QueryUtil.GetNullableIdForStorage(location.CoverFileId));
command.Parameters.AddWithValue("@UniqueId", guid);
return(command);
}
private SqliteCommand GetCreatePublisherCommand(SqliteConnection db, CreatePublisherDto dto, string guid, bool ignoreDuplicates)
{
var ignoreClause = ignoreDuplicates ? "OR IGNORE" : "";
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"INSERT {ignoreClause} INTO publisher(name, site_url, logo_image, description, library_id, deleted, deletion_due_to_cascade, unique_id) VALUES(@Name, @Url, @LogoId, @Description, @LibraryId, false, false, @UniqueId)";
command.Parameters.AddWithValue("@Name", dto.Name);
command.Parameters.AddWithValue("@Url", dto.SiteURL);
command.Parameters.AddWithValue("@Description", dto.Description);
command.Parameters.AddWithValue("@LibraryId", dto.LibraryId);
command.Parameters.AddWithValue("@LogoId", QueryUtil.GetNullableIdForStorage(dto.LogoFileId));
command.Parameters.AddWithValue("@UniqueId", guid);
return(command);
}
private SqliteCommand GetUpdateSeriesCommand(SqliteConnection db, Series series, string idColumn, object idValue)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"UPDATE series SET name = @Name, site_url = @Url, logo_image = @LogoId, description = @Description, publisher_id = @PublisherId, library_id = @LibraryId, calendar_id = @CalendarId, worth_watching = @WorthWatching WHERE {idColumn} = @SeriesId";
command.Parameters.AddWithValue("@Name", series.Name);
command.Parameters.AddWithValue("@Url", series.SiteURL);
command.Parameters.AddWithValue("@Description", series.Description);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(series.PublisherId));
command.Parameters.AddWithValue("@LibraryId", series.LibraryId);
command.Parameters.AddWithValue("@LogoId", QueryUtil.GetNullableIdForStorage(series.LogoFileId));
command.Parameters.AddWithValue("@SeriesId", idValue);
command.Parameters.AddWithValue("@CalendarId", QueryUtil.GetNullableIdForStorage(series.CalendarId));
command.Parameters.AddWithValue("@WorthWatching", series.WorthWatching);
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);
}
