public override void InsertRealmReport(WarcraftAPI.AuctionApiResponse report) { lock (connection) { long timestamp = new DateTimeOffset(report.lastModified).ToUnixTimeSeconds(); long storedTimestamp = long.MinValue; string sql = $@"SELECT * FROM ""last_modified"" WHERE realm_id = {report.realmId};"; NpgsqlCommand cmd = new NpgsqlCommand(sql, connection); using (var reader = cmd.ExecuteReader()) if (reader.Read()) { storedTimestamp = (long)reader[1]; } if (storedTimestamp >= timestamp) { return; } sql = $@"INSERT INTO ""last_modified"" VALUES({report.realmId}, '{timestamp}') ON CONFLICT (realm_id) DO UPDATE SET timestamp = EXCLUDED.timestamp;"; cmd = new NpgsqlCommand(sql, connection); cmd.ExecuteNonQuery(); foreach (var lot in report.auctions) { string values = $@"{timestamp}, {lot.id}, {lot.item.id}, '{(int)Enum.Parse(typeof(TimeLeft), lot.time_left)}', {lot.quantity}, {Math.Max(lot.buyout, lot.unit_price)}"; sql = $@"INSERT INTO ""{report.realmId}"" VALUES ({values});"; cmd = new NpgsqlCommand(sql, connection); cmd.ExecuteNonQuery(); } } }
public override void InsertRealmReport(WarcraftAPI.AuctionApiResponse report) { long timestamp = new DateTimeOffset(report.lastModified).ToUnixTimeSeconds(); foreach (var lot in report.auctions) { string values = $@"{timestamp}, {lot.id}, {lot.item.id}, '{lot.time_left}', {lot.quantity}, {lot.unit_price}, {lot.buyout}, {lot.bid}"; string sql = $@"INSERT OR IGNORE INTO ""{report.realmId}"" VALUES ({values});"; SQLiteCommand sqlite_cmd = connection.CreateCommand(); sqlite_cmd.CommandText = sql; sqlite_cmd.ExecuteNonQuery(); } }
void AuctionNewReportHandler(object sender, AuctionReportEventArgs e) { WarcraftAPI.AuctionApiResponse report = e.Report; string realms = ""; foreach (var realm in ConnectedRealms.GetRealmSlugsById(report.realmId)) { realms += $@"""{realm}"" "; } realms = realms.Trim(); logger.Info($"Inserting new data into database for realm(s): {realms} ({report.realmId})"); DateTime starttime = DateTime.UtcNow; db.InsertRealmReport(report); logger.Info($"Insertion done for realm(s) {realms} ({report.realmId}) in {DateTime.UtcNow.Subtract(starttime)}"); }
public abstract void InsertRealmReport(WarcraftAPI.AuctionApiResponse report);