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