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