public async Task UpsertCalendars(List <Calendar> calendars, Dictionary <string, long> calendarIds) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var calendar in calendars) { var command = GetUpdateCalendarCommand(db, calendar, "unique_id", calendar.UniqueId); command.Transaction = txn; command.ExecuteNonQuery(); var createDto = calendar.GetCreateCalendarDto(); command = GetCreateCalendarCommand(db, createDto, calendar.UniqueId, true); command.Transaction = txn; command.ExecuteNonQuery(); var retreivedCalendar = GetCalendar(calendar.UniqueId, "unique_id", db, txn); calendar.CalendarId = retreivedCalendar.CalendarId; calendarIds[calendar.UniqueId] = calendar.CalendarId; } txn.Commit(); } } }
public void UpsertVideosInSequences(List <VideosInSequencesExportDto> videos, Dictionary <string, long> ids) { videos.Sort((a, b) => a.Order.CompareTo(b.Order)); using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { var command = new SqliteCommand("INSERT OR IGNORE INTO video_in_sequence(sequence_id, video_id, order_in_list) VALUES(@SequenceId, @VideoId, @Order)", db, txn); command.Parameters.AddWithValue("@SequenceId", -1); command.Parameters.AddWithValue("@VideoId", -1); command.Parameters.AddWithValue("@Order", -1); foreach (var v in videos) { if (v.VideoId == null || v.SequenceId == null) { continue; } var sequenceId = ids[v.SequenceId]; var videoId = ids[v.VideoId]; var order = GetOrderNumberForNewVideoInSequence(sequenceId, db, txn); command.Parameters["@SequenceId"].Value = sequenceId; command.Parameters["@VideoId"].Value = videoId; command.Parameters["@Order"].Value = order; command.ExecuteNonQuery(); } txn.Commit(); } } }
public async Task UpsertPublishers(List <ExportedPublisherSimpleDto> publishers, Dictionary <string, long> publisherIds) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var exportedPublisher in publishers) { var publisher = exportedPublisher.Details; var command = GetUpdatePublisherCommand(db, publisher, "unique_id", publisher.UniqueId); command.Transaction = txn; command.ExecuteNonQuery(); var createDto = publisher.GetCreatePublisherDto(); command = GetCreatePublisherCommand(db, createDto, publisher.UniqueId, true); command.Transaction = txn; command.ExecuteNonQuery(); var retrievedPublisher = GetPublisher(publisher.UniqueId, db, txn); publisher.PublisherId = retrievedPublisher.PublisherId; publisherIds[publisher.UniqueId] = publisher.PublisherId; } txn.Commit(); } } }
public Calendar GetCalendar(long calendarId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); return(GetCalendar(calendarId, "calendar_id", db, null)); } }
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(); } } }
public async Task UpsertCreatorsOfVideos(List <VideoCreatorExportDto> creators, Dictionary <string, long> ids) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var c in creators) { if (c.CreatorId == null || c.VideoId == null) { continue; } var creatorId = ids[c.CreatorId]; var videoId = ids[c.VideoId]; var role = string.IsNullOrWhiteSpace(c.Role) ? "" : c.Role; var command = new SqliteCommand($"UPDATE video_creator SET role = @Role WHERE creator_id = {creatorId} AND video_id = {videoId}", db, txn); command.Parameters.AddWithValue("@Role", role); command.ExecuteNonQuery(); command = new SqliteCommand($"INSERT OR IGNORE INTO video_creator(creator_id, video_id, role) VALUES({creatorId}, {videoId}, @Role)", db, txn); command.Parameters.AddWithValue("@Role", role); command.ExecuteNonQuery(); } txn.Commit(); } } }
private async Task <List <CreatorOfVideoFullDto> > GetVideosCharacterIsIn(long characterId, string characterIdColumnName) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var list = GetVideosCharacterIsInWithoutActors(characterId, db, characterIdColumnName); var creatorIds = new List <string>(list.Where(c => c.ActorId != DatabaseConstants.DEFAULT_ID).Select(c => c.ActorId.ToString())); var creatorIdsString = string.Join(",", creatorIds); var command = new SqliteCommand($"SELECT character_id, name FROM character WHERE character_id IN ({creatorIdsString}) AND deleted = false", db); var query = command.ExecuteReader(); var idToVideoMap = new Dictionary <long, CreatorOfVideoFullDto>(); foreach (var vid in list) { idToVideoMap[vid.ActorId] = vid; } while (query.Read()) { var id = query.GetInt64(0); var name = query.GetString(1); idToVideoMap[id].ActorName = name; } return(list); } }
public async Task UpsertVideos(List <ExportedVideoSimpleDto> videos, Dictionary <string, long> videoIds) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { var videoIdCommand = db.CreateCommand(); videoIdCommand.CommandText = "SELECT video_id FROM video WHERE unique_id = @VideoId"; videoIdCommand.Parameters.AddWithValue("@VideoId", ""); foreach (var v in videos) { var video = v.Details; var command = GetUpdateVideoCommand(db, video, "unique_id", video.UniqueId); command.Transaction = txn; command.ExecuteNonQuery(); var createDto = video.GetCreateVideoDto(); command = GetCreateVideoCommand(db, createDto, video.UniqueId, true); command.Transaction = txn; command.ExecuteNonQuery(); videoIdCommand.Parameters["@VideoId"].Value = video.UniqueId; var videoId = (long)videoIdCommand.ExecuteScalar(); videoIds[video.UniqueId] = videoId; video.VideoId = videoId; } txn.Commit(); } } }
public async Task UpsertPlaylists(List <ExportedPlaylistSimpleDto> sequences, Dictionary <string, long> sequenceIds) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var s in sequences) { var sequence = s.Details; var sequenceId = DatabaseConstants.DEFAULT_ID; if (DoesSequenceWithGUIDExist(sequence.UniqueId, db, txn)) { sequenceId = GetSequenceIdFromGUID(sequence.UniqueId, db, txn); var command = GetUpdateVideoSequenceCommand(db, sequence, "sequence_id", sequenceId); command.Transaction = txn; command.ExecuteNonQuery(); } else { var createDto = sequence.GetCreatePlaylistDto(); sequenceId = CreateVideoSequence(createDto, db, txn); } sequenceIds[sequence.UniqueId] = sequenceId; sequence.SequenceId = sequenceId; } txn.Commit(); } } }
private async Task <List <Video> > GetOrderedVideosList(string orderQuery) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand(orderQuery, db); var reader = command.ExecuteReader(); var ids = new List <long>(); var map = new Dictionary <long, Video>(); while (reader.Read()) { ids.Add(reader.GetInt64(0)); } command = new SqliteCommand($"{SELECT_BASE} WHERE deleted = false AND video_id IN ({string.Join(",", ids)})", db); reader = command.ExecuteReader(); var list = new List <Video>(); while (reader.Read()) { var video = ParseVideo(reader); list.Add(video); map[video.VideoId] = video; } return(new List <Video>(ids.Select((id) => map[id]))); } }
public async Task UpsertLocations(List <ExportedLocationSimpleDto> locations, Dictionary <string, long> idsMap) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var loc in locations) { var location = loc.Details; var command = GetUpdateLocationCommand(db, location, "unique_id", location.UniqueId); command.Transaction = txn; command.ExecuteNonQuery(); var createDto = location.GetCreateLocationDto(); command = GetCreateLocationCommand(db, createDto, location.UniqueId, true); command.Transaction = txn; command.ExecuteNonQuery(); var retrievedCalendar = GetLocation(location.UniqueId, db, txn); location.LocationId = retrievedCalendar.LocationId; idsMap[location.UniqueId] = location.LocationId; } txn.Commit(); } } }
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); } }
public void UpsertTagsOnSeries(List <SeriesTagExportDto> tags, Dictionary <string, long> ids) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { var command = new SqliteCommand("INSERT OR IGNORE INTO tag_on_series(tag_id, series_id) SELECT tag_id, @SeriesId as 'series_id' FROM tag WHERE unique_id = @TagId", db, txn); command.Parameters.AddWithValue("@TagId", -1); command.Parameters.AddWithValue("@SeriesId", -1); foreach (var t in tags) { if (t.TagId == null || t.SeriesId == null) { continue; } var seriesId = ids[t.SeriesId]; command.Parameters["@TagId"].Value = t.TagId; command.Parameters["@SeriesId"].Value = seriesId; command.ExecuteNonQuery(); } txn.Commit(); } } }
public void UpsertTagsOnCharacters(List <CharacterTagExportDto> tags, Dictionary <string, long> ids) { if (tags.Count == 0) { return; } using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { var command = new SqliteCommand($"INSERT OR IGNORE INTO tag_on_character(tag_id, character_id) SELECT tag_id, @CharacterId AS 'character_id' FROM tag WHERE unique_id = @TagId", db, txn); command.Parameters.AddWithValue("@CharacterId", -1); command.Parameters.AddWithValue("@TagId", -1); foreach (var t in tags) { if (t.TagId == null || t.CharacterId == null) { continue; } var charId = ids[t.CharacterId]; command.Parameters["@CharacterId"].Value = charId; command.Parameters["@TagId"].Value = t.TagId; command.ExecuteNonQuery(); } txn.Commit(); } } }
public void SyncWatchedVideos(List <ExternallyWatchedVideoDto> videos) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { var updateWatchHistoryCommand = new SqliteCommand($"INSERT INTO watch_history(video_id, watch_date) SELECT video_id, @WatchDate as watch_date FROM video WHERE source_url = @VideoUrl", db, txn); updateWatchHistoryCommand.Parameters.AddWithValue("@VideoUrl", ""); updateWatchHistoryCommand.Parameters.AddWithValue("@WatchDate", DateTime.Now); // note: The case clause is used instead of MAX(last_watch_date, @WatchDate) because the MAX approach doesn't seem to work with parameterized queries var updateTimesWatchedCommand = new SqliteCommand($"UPDATE video SET last_watch_date = (CASE WHEN last_watch_date > @WatchDate THEN last_watch_date ELSE @WatchDate END), times_watched = times_watched + 1 WHERE source_url = @VideoUrl", db, txn); updateTimesWatchedCommand.Parameters.AddWithValue("@VideoUrl", ""); updateTimesWatchedCommand.Parameters.AddWithValue("@WatchDate", DateTime.Now); foreach (var v in videos) { if (v.VideoUrl == null) { continue; } updateWatchHistoryCommand.Parameters["@VideoUrl"].Value = v.VideoUrl; updateWatchHistoryCommand.Parameters["@WatchDate"].Value = v.Time; updateWatchHistoryCommand.ExecuteNonQuery(); updateTimesWatchedCommand.Parameters["@VideoUrl"].Value = v.VideoUrl; updateTimesWatchedCommand.Parameters["@WatchDate"].Value = v.Time; updateTimesWatchedCommand.ExecuteNonQuery(); } txn.Commit(); } } }
public async Task UpsertCharacters(List <ExportedCharacterSimpleDto> characters, Dictionary <string, long> characterIds) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var c in characters) { var character = c.Details; var command = GetUpdateCharacterCommand(db, character, "unique_id", character.UniqueId); command.Transaction = txn; command.ExecuteNonQuery(); var createDto = character.GetCreateCharacterDto(); command = GetCreateCharacterCommand(db, createDto, character.UniqueId, true); command.Transaction = txn; command.ExecuteNonQuery(); var retrieved = GetCharacter(character.UniqueId, db, txn); characterIds[character.UniqueId] = retrieved.CharacterId; character.CharacterId = retrieved.CharacterId; } txn.Commit(); } } }
public CharacterTag GetCharacterTag(long tagId) { using (var db = DataAccessUtil.CreateSqlConnection()) { var reader = ExecuteQueryForTag(db, tagId); return(new CharacterTag(tagId, reader.GetString(0), reader.GetString(1), reader.GetString(2))); } }
public void UpsertFilesOnVideos(List <VideoMediaFilesExportDto> files, Dictionary <string, long> ids) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { var command = new SqliteCommand("INSERT OR IGNORE INTO video_media_file(video_id, media_id) VALUES(@VideoId, @FileId)", db, txn); command.Parameters.AddWithValue("@VideoId", ""); command.Parameters.AddWithValue("@FileId", ""); foreach (var f in files) { if (f.VideoId == null || f.MediaFileId == null) { continue; } var videoId = ids[f.VideoId]; var fileId = ids[f.MediaFileId]; command.Parameters["@VideoId"].Value = videoId; command.Parameters["@FileId"].Value = fileId; command.ExecuteNonQuery(); } txn.Commit(); } } }
public async Task UpsertSeries(List <ExportedSeriesSimpleDto> seriesList, Dictionary <string, long> seriesIds) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); using (var txn = db.BeginTransaction()) { foreach (var s in seriesList) { var series = s.Details; var command = GetUpdateSeriesCommand(db, series, "unique_id", series.UniqueId); command.Transaction = txn; command.ExecuteNonQuery(); var createDto = series.GetCreateSeriesDto(); command = GetCreateSeriesCommand(db, createDto, series.UniqueId, true); command.Transaction = txn; command.ExecuteNonQuery(); var retrievedSeries = GetSeries(series.UniqueId, db, txn); seriesIds[series.UniqueId] = retrievedSeries.SeriesId; series.SeriesId = retrievedSeries.SeriesId; } txn.Commit(); } } }
private MediaFile GetMediaFile(string fileId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); return(GetMediaFile(fileId, db, null)); } }
public void RemoveCreatorFromVideo(VideoCreator creator, long videoId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"DELETE FROM video_creator WHERE creator_id = {creator.CharacterDetails.CharacterId} AND video_id = {videoId}", db); command.ExecuteNonQuery(); } }
private void UpdateDeletedStatus(long characterId, bool deleted) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"UPDATE character SET deleted = {deleted}, deletion_due_to_cascade = false WHERE character_id = {characterId}", db); command.ExecuteNonQuery(); } }
public void RemoveCharacterFromVideo(long videoId, long characterId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var clearCommand = new SqliteCommand($@"DELETE FROM actor_for_video_character WHERE video_id = {videoId} AND character_id = {characterId}", db); clearCommand.ExecuteNonQuery(); } }
public void PermanentlyRemoveCharacter(long characterId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"DELETE FROM character WHERE character_id = {characterId}", db); command.ExecuteNonQuery(); } }
public async Task PermanentlyRemoveSeries(long seriesId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"DELETE FROM series WHERE series_id = {seriesId}", db); command.ExecuteNonQuery(); } }
public void UpdateSeries(Series series) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = GetUpdateSeriesCommand(db, series, "series_id", series.SeriesId); command.ExecuteNonQuery(); } }
public void DeleteMediaFile(long fileId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"DELETE FROM media_file WHERE media_id = {fileId}", db); command.ExecuteNonQuery(); } }
public async Task RemoveFileFromCharacter(long characterId, MediaFile file) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"DELETE FROM character_media_file WHERE character_id = {characterId} AND media_id = {file.MediaId}", db); command.ExecuteNonQuery(); } }
public void AddFileToCharacter(long characterId, long fileId) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"INSERT INTO character_media_file(character_id, media_id) VALUES({characterId}, {fileId})", db); command.ExecuteNonQuery(); } }
public async Task AddFileToVideo(long videoId, MediaFile file) { using (var db = DataAccessUtil.CreateSqlConnection()) { db.Open(); var command = new SqliteCommand($"INSERT INTO video_media_file(video_id, media_id) VALUES({videoId}, {file.MediaId})", db); command.ExecuteNonQuery(); } }