public async Task Test_AsyncEnumerable_BulkInsert() { subject = new PostgreSQLCopyHelper <User>("sample", "TestUsers") .MapInteger("Id", x => x.Id) .MapText("Name", x => x.Name); var recordsSaved = new List <User>(); await foreach (var user in FetchUserData()) { recordsSaved.Add(user); } // Try to work with the Bulk Inserter: await subject.SaveAllAsync(connection, FetchUserData()); var result = connection.GetAll("sample", "TestUsers"); // Check if we have the amount of rows: Assert.AreEqual(2, result.Count); Assert.AreEqual(2, recordsSaved.Count); Assert.IsNotNull(result[0][0]); Assert.IsNotNull(result[1][0]); Assert.AreEqual(recordsSaved[0].Id, (int)result[0][0]); Assert.AreEqual(recordsSaved[0].Name, (string)result[0][1]); Assert.AreEqual(recordsSaved[1].Id, (int)result[1][0]); Assert.AreEqual(recordsSaved[1].Name, (string)result[1][1]); }
public async Task Test_CanceledBulkInsertDoesNotThrowWhenCancelledAfterCompletion() { subject = new PostgreSQLCopyHelper <User>("sample", "TestUsers") .MapInteger("Id", x => x.Id) .MapText("Name", x => x.Name); var users = new List <User>(); await foreach (var user in FetchUserData(0)) { users.Add(user); } using var cancellationTokenSource = new CancellationTokenSource(100); var recordsSaved = await subject.SaveAllAsync(connection, FetchUserData(1), cancellationTokenSource.Token); var result = connection.GetAll("sample", "TestUsers"); Assert.AreEqual(2, result.Count); Assert.AreEqual(2, recordsSaved); Assert.IsNotNull(result[0][0]); Assert.IsNotNull(result[1][0]); Assert.AreEqual(users[0].Id, (int)result[0][0]); Assert.AreEqual(users[0].Name, (string)result[0][1]); Assert.AreEqual(users[1].Id, (int)result[1][0]); Assert.AreEqual(users[1].Name, (string)result[1][1]); Assert.AreEqual(2, recordsSaved); }
public async Task StoreGuildUsers(IGuild guild, IReadOnlyCollection <IGuildUser> guildUsers) { var userIds = guildUsers.Select(s => s.Id).ToList(); await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString); var existingGuild = await db.Guilds .Include(i => i.GuildUsers) .FirstAsync(f => f.DiscordGuildId == guild.Id); var users = await db.Users .Include(i => i.Artists) .Where(w => userIds.Contains(w.DiscordUserId)) .Select(s => new GuildUser { GuildId = existingGuild.GuildId, UserId = s.UserId }) .ToListAsync(); var connString = db.Database.GetDbConnection().ConnectionString; var copyHelper = new PostgreSQLCopyHelper <GuildUser>("public", "guild_users") .MapInteger("guild_id", x => x.GuildId) .MapInteger("user_id", x => x.UserId); await using var connection = new NpgsqlConnection(connString); connection.Open(); await using var deleteCurrentArtists = new NpgsqlCommand($"DELETE FROM public.guild_users WHERE guild_id = {existingGuild.GuildId};", connection); await deleteCurrentArtists.ExecuteNonQueryAsync().ConfigureAwait(false); await copyHelper.SaveAllAsync(connection, users).ConfigureAwait(false); Log.Information("Stored guild users for guild with id {guildId}", existingGuild.GuildId); }
public async Task <ulong> DoBulkImport <T>(PostgreSQLCopyHelper <T> copyHelper, IEnumerable <T> entities) where T : class { using IDbConnection cn = await OpenAsAsync(); // Returns count of rows written return(await copyHelper.SaveAllAsync((NpgsqlConnection)cn, entities, CancellationToken.None)); }
public async Task InsertAllAsync(IEnumerable <Case> cases) { var copyHelper = new PostgreSQLCopyHelper <Case>("covidtracker", "cases") .MapVarchar("caseno", q => q.CaseNo) .MapInteger("age", q => q.Age) .MapVarchar("agegroup", q => q.AgeGroup) .MapVarchar("sex", q => q.Sex) .MapTimeStamp("dateconfirmed", q => q.DateConfirmed) .MapTimeStamp("daterecovered", q => q.DateRecovered) .MapTimeStamp("datedied", q => q.DateDied) .MapVarchar("removaltype", q => q.RemovalType) .MapTimeStamp("dateremoved", q => q.DateRemoved) .MapBoolean("admitted", q => q.Admitted) .MapVarchar("healthstatus", q => q.HealthStatus) .MapVarchar("region", q => q.Region) .MapVarchar("province", q => q.Province) .MapVarchar("city", q => q.City) .MapTimeStamp("insertedat", q => q.InsertedAt) .MapTimeStamp("updatedat", q => q.UpdatedAt); using (var con = _connectionFactory()) { await con.OpenAsync(); await copyHelper.SaveAllAsync(con, cases); } }
public Task Test_CanceledBulkInsertThrowsWhenCanceled() { subject = new PostgreSQLCopyHelper <User>("sample", "TestUsers") .MapInteger("Id", x => x.Id) .MapText("Name", x => x.Name); Assert.ThrowsAsync <TaskCanceledException>(async() => { using var cancellationTokenSource = new CancellationTokenSource(15); await subject.SaveAllAsync(connection, FetchUserData(10), cancellationTokenSource.Token); }, $"Should Throw Exception of Type {nameof(TaskCanceledException)}!"); return(Task.CompletedTask); }
private static async Task InsertArtistsIntoDatabase(IReadOnlyList <UserArtist> artists, int userId, NpgsqlConnection connection) { Log.Information($"Inserting artists for user {userId}"); var copyHelper = new PostgreSQLCopyHelper <UserArtist>("public", "user_artists") .MapText("name", x => x.Name) .MapInteger("user_id", x => x.UserId) .MapInteger("playcount", x => x.Playcount); await using var deleteCurrentArtists = new NpgsqlCommand($"DELETE FROM public.user_artists WHERE user_id = {userId};", connection); await deleteCurrentArtists.ExecuteNonQueryAsync(); await copyHelper.SaveAllAsync(connection, artists); }
private async Task InsertTracksIntoDatabase(IReadOnlyList <UserTrack> artists, int userId) { Log.Information($"Inserting tracks for user {userId}"); var copyHelper = new PostgreSQLCopyHelper <UserTrack>("public", "user_tracks") .MapText("name", x => x.Name) .MapText("artist_name", x => x.ArtistName) .MapInteger("user_id", x => x.UserId) .MapInteger("playcount", x => x.Playcount); await using var connection = new NpgsqlConnection(this._connectionString); connection.Open(); await using var deleteCurrentTracks = new NpgsqlCommand($"DELETE FROM public.user_tracks WHERE user_id = {userId};", connection); await deleteCurrentTracks.ExecuteNonQueryAsync(); await copyHelper.SaveAllAsync(connection, artists); }
private static async Task InsertArtistsIntoDatabase(IReadOnlyList <UserArtist> artists, int userId, DateTime now) { await using var db = new FMBotDbContext(ConfigData.Data.Database.ConnectionString); var connString = db.Database.GetDbConnection().ConnectionString; var copyHelper = new PostgreSQLCopyHelper <UserArtist>("public", "user_artists") .MapText("name", x => x.Name) .MapInteger("user_id", x => x.UserId) .MapInteger("playcount", x => x.Playcount) .MapTimeStamp("last_updated", x => x.LastUpdated); await using var connection = new NpgsqlConnection(connString); connection.Open(); await using var deleteCurrentArtists = new NpgsqlCommand($"DELETE FROM public.user_artists WHERE user_id = {userId};", connection); await deleteCurrentArtists.ExecuteNonQueryAsync().ConfigureAwait(false); await copyHelper.SaveAllAsync(connection, artists).ConfigureAwait(false); await using var setIndexTime = new NpgsqlCommand($"UPDATE public.users SET last_indexed='{now:u}' WHERE user_id = {userId};", connection); await setIndexTime.ExecuteNonQueryAsync().ConfigureAwait(false); }
private static async Task InsertPlaysIntoDatabase(IReadOnlyList <UserPlay> userPlays, int userId, NpgsqlConnection connection) { Log.Information($"Inserting plays for user {userId}"); await using var deletePlays = new NpgsqlCommand("DELETE FROM public.user_plays " + "WHERE user_id = @userId", connection); deletePlays.Parameters.AddWithValue("userId", userId); await deletePlays.ExecuteNonQueryAsync(); var copyHelper = new PostgreSQLCopyHelper <UserPlay>("public", "user_plays") .MapText("track_name", x => x.TrackName) .MapText("album_name", x => x.AlbumName) .MapText("artist_name", x => x.ArtistName) .MapTimeStamp("time_played", x => x.TimePlayed) .MapInteger("user_id", x => x.UserId); await copyHelper.SaveAllAsync(connection, userPlays); }
private async Task UpdatePlaysForUser(User user, IEnumerable <LastTrack> newScrobbles, NpgsqlConnection connection) { Log.Information($"Updating plays for user {user.UserId}"); await using var deleteOldPlays = new NpgsqlCommand("DELETE FROM public.user_plays " + "WHERE user_id = @userId AND time_played < @playExpirationDate;", connection); deleteOldPlays.Parameters.AddWithValue("userId", user.UserId); deleteOldPlays.Parameters.AddWithValue("playExpirationDate", DateTime.UtcNow.AddDays(-Constants.DaysToStorePlays)); await deleteOldPlays.ExecuteNonQueryAsync(); var lastPlay = await GetLastStoredPlay(user); var userPlays = newScrobbles .Where(w => w.TimePlayed.HasValue && w.TimePlayed.Value.DateTime > (lastPlay?.TimePlayed ?? DateTime.UtcNow.AddDays(-Constants.DaysToStorePlays).Date)) .Select(s => new UserPlay { TrackName = s.Name, AlbumName = s.AlbumName, ArtistName = s.ArtistName, TimePlayed = s.TimePlayed.Value.DateTime, UserId = user.UserId }).ToList(); var copyHelper = new PostgreSQLCopyHelper <UserPlay>("public", "user_plays") .MapText("track_name", x => x.TrackName) .MapText("album_name", x => x.AlbumName) .MapText("artist_name", x => x.ArtistName) .MapTimeStamp("time_played", x => x.TimePlayed) .MapInteger("user_id", x => x.UserId); await copyHelper.SaveAllAsync(connection, userPlays); }