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 void Test_UpperCase_BulkInsert() { // Use Upper Case Schema Name: subject = new PostgreSQLCopyHelper<TestEntity>("SAMPLE", "UNIT_TEST") .MapSmallInt("col_smallint", x => x.SmallInt); // Try to work with the Bulk Inserter: var entity0 = new TestEntity() { SmallInt = Int16.MinValue }; var entity1 = new TestEntity() { SmallInt = Int16.MaxValue }; subject.SaveAll(connection, new[] { entity0, entity1 }); var result = connection.GetAll("sample", "unit_test"); // Check if we have the amount of rows: Assert.AreEqual(2, result.Count); Assert.IsNotNull(result[0][0]); Assert.IsNotNull(result[1][0]); Assert.AreEqual(Int16.MinValue, (Int16)result[0][0]); Assert.AreEqual(Int16.MaxValue, (Int16)result[1][0]); }
public void Test_Interval() { CreateTable("interval"); var begin = new LocalDateTime(2020, 1, 23, 0, 12); var end = new LocalDateTime(2020, 12, 8, 12, 44); var subject = new PostgreSQLCopyHelper <AllNodaTypesEntity>("sample", "noda_time_test") .MapInterval("col_noda", x => x.Period); var entity = new AllNodaTypesEntity { Period = Period.Between(begin, end) }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (Period)rows[0][0]; Assert.AreEqual(entity.Period, actual); }
public void Test_LocalDate() { CreateTable("date"); var subject = new PostgreSQLCopyHelper <AllNodaTypesEntity>("sample", "noda_time_test") .MapDate("col_noda", x => x.LocalDate); var entity = new AllNodaTypesEntity { LocalDate = new LocalDate(2011, 1, 2) }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (LocalDate)rows[0][0]; Assert.AreEqual(entity.LocalDate, actual); }
public void Test_OffsetDateTime() { CreateTable("timestamptz"); var subject = new PostgreSQLCopyHelper <AllNodaTypesEntity>("sample", "noda_time_test") .MapTimeStampTz("col_noda", x => x.OffsetDateTime); var entity = new AllNodaTypesEntity { OffsetDateTime = new OffsetDateTime(new LocalDateTime(2001, 11, 21, 0, 32), Offset.FromHours(2)) }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (Instant)rows[0][0]; Assert.AreEqual(entity.OffsetDateTime.ToInstant(), actual); }
public void Test_ZonedDateTime() { CreateTable("timestamptz"); var subject = new PostgreSQLCopyHelper <AllNodaTypesEntity>("sample", "noda_time_test") .MapTimeStampTz("col_noda", x => x.ZonedDateTime); var timezone = DateTimeZoneProviders.Tzdb.GetZoneOrNull("Africa/Kigali"); var instant = Instant.FromUtc(2011, 1, 5, 22, 50, 0) + Duration.FromMilliseconds(193); var entity = new AllNodaTypesEntity { ZonedDateTime = new ZonedDateTime(instant, timezone) }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); // TODO: How does Postgres <-> NodaTime convert Timezones? There is a good test here, but // I couldn't see through it yet: // // https://github.com/npgsql/npgsql/blob/766658172f08abb0b87a6b7f01a7ea4b49952a29/test/Npgsql.PluginTests/NodaTimeTests.cs // var actual = (Instant)rows[0][0]; Assert.AreEqual(instant, actual); }
public void Test_Instant() { CreateTable("timestamp"); var subject = new PostgreSQLCopyHelper <AllNodaTypesEntity>("sample", "noda_time_test") .MapTimeStamp("col_noda", x => x.Instant); var entity = new AllNodaTypesEntity { Instant = Instant.FromUtc(2011, 1, 2, 0, 0) }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (Instant)rows[0][0]; Assert.AreEqual(entity.Instant, actual); }
public void Test_LocalDateTime() { CreateTable("timestamp"); var subject = new PostgreSQLCopyHelper <AllNodaTypesEntity>("sample", "noda_time_test") .MapTimeStamp("col_noda", x => x.LocalDateTime); var entity = new AllNodaTypesEntity { LocalDateTime = new LocalDateTime(2011, 1, 2, 21, 0, 0) }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (Instant)rows[0][0]; var localTime = entity.LocalDateTime; var zonedTime = localTime.InZoneStrictly(DateTimeZone.Utc); var expected = zonedTime.ToInstant(); Assert.AreEqual(expected, actual); }
private static void loadMovies() { List <Movie> values = File.ReadAllLines(@"C:\Users\Vanya\Desktop\2 семестр\Data retrieval\IR-2019-master\data\IMDB Movie Titles.csv") .Skip(1) .Select(v => Movie.FromCsv(v)) .ToList(); var copyHelper = new PostgreSQLCopyHelper <Movie>("public", "movies") .MapInteger("id", x => x.Id) .MapInteger("year", x => x.Year) .MapText("name", x => x.Name) ; using (var connection = new NpgsqlConnection(connString)) { try { connection.Open(); copyHelper.SaveAll(connection, values); int lineCount = values.Count(); Console.Write(lineCount + " Records Inserted!"); connection.Close(); } catch (Exception er) { Console.Write(er.Message); } } }
private void BatchInsert(IEnumerable <Account> accounts) { var helperAccounts = new PostgreSQLCopyHelper <Account>("public", "accounts") .MapBigInt("id", x => x.Id) .MapText("sname", x => x.SName) .MapText("fname", x => x.FName) .MapText("country", x => x.Country) .MapText("city", x => x.City) .MapText("phone", x => x.Phone) .MapText("email", x => x.EMail) .MapInteger("sex", x => (int?)x.Sex) .MapTimeStamp("birth", x => x.Birth) .MapTimeStamp("joined", x => x.Joined) .MapText("status", x => x.Status) .MapNullable("premium_start", x => x.PremiumStart, NpgsqlDbType.Bigint) .MapNullable("premium_finish", x => x.PremiumFinish, NpgsqlDbType.Bigint) .MapArray("interests", x => x.Interests) .MapArray("like_ids", x => x.LikeIds) .MapArray("like_tss", x => x.LikeTSs); using (var connection = new NpgsqlConnection(Configuration.GetConnectionString("DefaultConnection"))) { connection.Open(); helperAccounts.SaveAll(connection, accounts); } }
public void Test_MixedCaseEntity_BulkInsert() { subject = new PostgreSQLCopyHelper <MixedCaseEntity>("sample", "\"MixedCaseEntity\"") .MapInteger("\"Property_One\"", x => x.Property_One) .MapText("\"Property_Two\"", x => x.Property_Two); // Try to work with the Bulk Inserter: var entity0 = new MixedCaseEntity { Property_One = 44, Property_Two = "hello everyone" }; var entity1 = new MixedCaseEntity { Property_One = 89, Property_Two = "Isn't it nice to write in Camel Case!" }; subject.SaveAll(connection, new[] { entity0, entity1 }); var result = connection.GetAll("sample", "\"MixedCaseEntity\""); // Check if we have the amount of rows: Assert.AreEqual(2, result.Count); Assert.IsNotNull(result[0][0]); Assert.IsNotNull(result[1][0]); Assert.AreEqual(entity0.Property_One, (Int32)result[0][0]); Assert.AreEqual(entity0.Property_Two, (string)result[0][1]); Assert.AreEqual(entity1.Property_One, (Int32)result[1][0]); Assert.AreEqual(entity1.Property_Two, (string)result[1][1]); }
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 void Test_MixedCaseEntity_BulkInsert() { subject = new PostgreSQLCopyHelper<MixedCaseEntity>("sample", "\"MixedCaseEntity\"") .MapInteger("\"Property_One\"", x => x.Property_One) .MapText("\"Property_Two\"", x => x.Property_Two); // Try to work with the Bulk Inserter: var entity0 = new MixedCaseEntity { Property_One = 44, Property_Two = "hello everyone" }; var entity1 = new MixedCaseEntity { Property_One = 89, Property_Two = "Isn't it nice to write in Camel Case!" }; subject.SaveAll(connection, new[] { entity0, entity1 }); var result = connection.GetAll("sample", "\"MixedCaseEntity\""); // Check if we have the amount of rows: Assert.AreEqual(2, result.Count); Assert.IsNotNull(result[0][0]); Assert.IsNotNull(result[1][0]); Assert.AreEqual(entity0.Property_One, (Int32)result[0][0]); Assert.AreEqual(entity0.Property_Two, (string)result[0][1]); Assert.AreEqual(entity1.Property_One, (Int32)result[1][0]); Assert.AreEqual(entity1.Property_Two, (string)result[1][1]); }
public void Test_OffsetTime() { CreateTable("timetz"); var subject = new PostgreSQLCopyHelper <NullableNodaTypesEntity>("sample", "noda_time_test") .MapTimeTz("col_noda", x => x.OffsetTime); var entity = new NullableNodaTypesEntity { OffsetTime = null }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (OffsetTime)rows[0][0]; Assert.AreEqual(entity.OffsetTime, actual); }
public void Test_DoubleArray() { CreateTable("double precision"); var subject = new PostgreSQLCopyHelper <DoubleArrayEntity>("sample", "unit_test") .MapArray("col_array", x => x.Array); var entity0 = new DoubleArrayEntity() { Array = new Double[] { 1.32, 2.124 } }; subject.SaveAll(connection, new[] { entity0 }); var result = GetAll(); // Check if we have the amount of rows: Assert.AreEqual(1, result.Count); // Check if the Result is not null: Assert.IsNotNull(result[0][0]); // And now check the values: var resultArray = (Double[])result[0][0]; Assert.AreEqual(1.32, resultArray[0], 1e-5); Assert.AreEqual(2.124, resultArray[1], 1e-5); }
public void Test_LocalDateTime() { CreateTable("timestamp"); var subject = new PostgreSQLCopyHelper <NullableNodaTypesEntity>("sample", "noda_time_test") .MapTimeStamp("col_noda", x => x.LocalDateTime); var entity = new NullableNodaTypesEntity { LocalDateTime = null }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); var actual = (Instant)rows[0][0]; var expected = entity.LocalDateTime; Assert.AreEqual(expected, actual); }
public void Test_UpperCase_BulkInsert() { // Use Upper Case Schema Name: subject = new PostgreSQLCopyHelper <TestEntity>("SAMPLE", "UNIT_TEST") .MapSmallInt("col_smallint", x => x.SmallInt); // Try to work with the Bulk Inserter: var entity0 = new TestEntity() { SmallInt = Int16.MinValue }; var entity1 = new TestEntity() { SmallInt = Int16.MaxValue }; subject.SaveAll(connection, new[] { entity0, entity1 }); var result = GetAll(); // Check if we have the amount of rows: Assert.AreEqual(2, result.Count); Assert.IsNotNull(result[0][0]); Assert.IsNotNull(result[1][0]); Assert.AreEqual(Int16.MinValue, (Int16)result[0][0]); Assert.AreEqual(Int16.MaxValue, (Int16)result[1][0]); }
public async Task <ulong> BulkImport(IList <Cfda> models) { var copyHelper = new PostgreSQLCopyHelper <Cfda>("import", "cfdas") .MapText("audityear", x => x.Audityear) .MapText("dbkey", x => x.Dbkey) .MapText("ein", x => x.Ein) .MapText("cfda", x => x.CFDAValue) .MapText("awardidentification", x => x.AwardIdentification) .MapText("rd", x => x.Rd) .MapText("federalprogramname", x => x.FederalProgramName) .MapText("amount", x => x.Amount) .MapText("clustername", x => x.Clustername) .MapText("stateclustername", x => x.StateClustername) .MapText("programtotal", x => x.ProgramTotal) .MapText("clustertotal", x => x.Clustername) .MapText("direct", x => x.Direct) .MapText("passthroughaward", x => x.PassthroughAward) .MapText("passthroughamount", x => x.PassthroughAmount) .MapText("majorprogram", x => x.MajorProgram) .MapText("typereport_mp", x => x.TypeReportMP) .MapText("typerequirement", x => x.TypeRequirement) .MapText("qcosts2", x => x.QCosts2) .MapText("findings", x => x.Findings) .MapText("findingrefnums", x => x.FindingRefNums) .MapText("arra", x => x.ARRA) .MapText("loans", x => x.Loans) .MapText("loanbalance", x => x.LoanBalance) .MapText("findingscount", x => x.FindingsCount) .MapText("elecauditsid", x => x.ElecauditsId) .MapText("otherclustername", x => x.OtherClustername) .MapText("cfdaprogramname", x => x.CFDAProgramname); return(await DatabaseManager.DoBulkImport(copyHelper, models)); }
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 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); }
protected override void OnSetupInTransaction() { CreateTable(); subject = new PostgreSQLCopyHelper <TestEntity>("sample", "unit_test") .MapSmallInt("col_smallint", x => x.SmallInt) .MapInteger("col_integer", x => x.Integer) .MapMoney("col_money", x => x.Money) .MapBigInt("col_bigint", x => x.BigInt) .MapTimeStamp("col_timestamp", x => x.Timestamp) .MapReal("col_real", x => x.Real) .MapDouble("col_double", x => x.DoublePrecision) .MapByteArray("col_bytea", x => x.ByteArray) .MapUUID("col_uuid", x => x.UUID) .MapInetAddress("col_inet", x => x.IpAddress) .MapMacAddress("col_macaddr", x => x.MacAddress) .MapDate("col_date", x => x.Date) .MapInterval("col_interval", x => x.TimeSpan) .MapNumeric("col_numeric", x => x.Numeric) .MapJson("col_json", x => x.Json) .MapJsonb("col_jsonb", x => x.Jsonb) .MapTimeStampTz("col_timestamptz_date_time_offset", x => x.DateTimeOffset_TimestampTz) .MapTimeStampTz("col_timestamptz_date_time", x => x.DateTime_TimestampTz) .MapTimeTz("col_timetz_time_span", x => x.TimeSpan_TimeTz) .MapTimeTz("col_timetz_date_time", x => x.DateTime_TimeTz) .MapTimeTz("col_timetz_date_time_offset", x => x.DateTimeOffset_TimeTz); }
public void Test_StringArray() { CreateTable("text"); var subject = new PostgreSQLCopyHelper <StringArrayEntity>("sample", "unit_test") .MapArray("col_array", x => x.Array); var entity0 = new StringArrayEntity() { Array = new[] { "A", "B" } }; var recordsSaved = subject.SaveAll(connection, new[] { entity0 }); var result = GetAll(); // Check if we have the amount of rows: Assert.AreEqual(1, result.Count); Assert.AreEqual(1, recordsSaved); // Check if the Result is not null: Assert.IsNotNull(result[0][0]); // And now check the values: var resultArray = (String[])result[0][0]; Assert.AreEqual("A", resultArray[0]); Assert.AreEqual("B", resultArray[1]); }
public void Test_ByteArrayArray() { CreateTable("bytea"); var subject = new PostgreSQLCopyHelper <ByteArrayArrayEntity>("sample", "unit_test") .MapArray("col_array", x => x.Array); var entity0 = new ByteArrayArrayEntity() { Array = new byte[][] { new byte[] { 1, 2 } } }; subject.SaveAll(connection, new[] { entity0 }); var result = GetAll(); // Check if we have the amount of rows: Assert.AreEqual(1, result.Count); // Check if the Result is not null: Assert.IsNotNull(result[0][0]); // And now check the values: var resultArray = (byte[][])result[0][0]; Assert.AreEqual(1, resultArray[0][0]); Assert.AreEqual(2, resultArray[0][1]); }
public void Test_ZonedDateTime() { CreateTable("timestamptz"); var subject = new PostgreSQLCopyHelper <NullableNodaTypesEntity>("sample", "noda_time_test") .MapTimeStampTz("col_noda", x => x.ZonedDateTime); var entity = new NullableNodaTypesEntity { ZonedDateTime = null }; var entities = new[] { entity }; subject.SaveAll(connection, entities); // Check what's written to DB: var rows = GetAll(); // TODO: How does Postgres <-> NodaTime convert Timezones? There is a good test here, but // I couldn't see through it yet: // // https://github.com/npgsql/npgsql/blob/766658172f08abb0b87a6b7f01a7ea4b49952a29/test/Npgsql.PluginTests/NodaTimeTests.cs // var actual = (Instant)rows[0][0]; Assert.AreEqual(entity.ZonedDateTime, actual); }
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 ulong StoreExternalIds(PostgreSQLCopyHelper <exid_in_db> copyHelper, IEnumerable <exid_in_db> entities) { using (var conn = new NpgsqlConnection(connString)) { conn.Open(); return(copyHelper.SaveAll(conn, entities)); } }
public ulong StoreCityNUTS(PostgreSQLCopyHelper <city_nuts_in_db> copyHelper, IEnumerable <city_nuts_in_db> entities) { using (var conn = new NpgsqlConnection(connString)) { conn.Open(); return(copyHelper.SaveAll(conn, entities)); } }
private static PostgreSQLCopyHelper <GameData> MapToSteamGamesListTableSchema() { PostgreSQLCopyHelper <GameData> gameDataBulkCopyHelper = new PostgreSQLCopyHelper <GameData>("demo", "game_list") .MapText("app_id", gameData => gameData.AppId) .MapText("name", gameData => gameData.Name); return(gameDataBulkCopyHelper); }
public ulong StoreIPAddresses(PostgreSQLCopyHelper <ip_address_in_db> copyHelper, IEnumerable <ip_address_in_db> entities) { using (var conn = new NpgsqlConnection(connString)) { conn.Open(); return(copyHelper.SaveAll(conn, entities)); } }
public ulong StoreRelationships(PostgreSQLCopyHelper <relationship_in_db> copyHelper, IEnumerable <relationship_in_db> entities) { using (var conn = new NpgsqlConnection(connString)) { conn.Open(); return(copyHelper.SaveAll(conn, entities)); } }
public ulong StorePMIDLinks(PostgreSQLCopyHelper <PMIDLink> copyHelper, IEnumerable <PMIDLink> entities) { using (var conn = new NpgsqlConnection(connString)) { conn.Open(); return(copyHelper.SaveAll(conn, entities)); } }
public ulong StoreStudyIds(PostgreSQLCopyHelper <StudyId> copyHelper, IEnumerable <StudyId> entities) { // stores the study id data in a temporary table using (var conn = new NpgsqlConnection(connString)) { conn.Open(); return(copyHelper.SaveAll(conn, entities)); } }