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