public PostgresDAL(string conn)
{
help = new PostgreSqlHelper(conn);
// var strSql = @" select format('%s.%s',cols.table_schema,quote_ident(cols.table_name)) 表名
//,cols.ordinal_position 序号
//,cols.column_name 列名
//,col_description((cols.table_schema || '.' ||cols.table_name)::regclass::oid,cols.ordinal_position ) as 中文名
//,case when position('_' in cols.udt_name) > 0 then regexp_replace(cols.udt_name,'(_)(.*)','\2[]') else cols.udt_name end 数据类型
//,case cols.data_type when 'character varying' then cols.character_maximum_length when 'numeric' then cols.numeric_precision else null end 长度
//,cols.numeric_scale 小数位数
//, CASE WHEN position( 'extval(' in cols.column_default) > 1 THEN '√' ELSE '' END 标识
//, case when EXISTS ( select a.table_schema,a.table_name,b.constraint_name,a.ordinal_position as position,a.column_name as key_column
//from information_schema.table_constraints b inner join information_schema.key_column_usage a on a.constraint_name = b.constraint_name
// and a.constraint_schema = b.constraint_schema and a.constraint_name = b.constraint_name
//where b.constraint_type = 'PRIMARY KEY' and a.table_schema = cols.table_schema and a.table_name = cols.table_name and a.column_name = cols.column_name) then '√' ELSE '' END 主键
//,case when cols.is_nullable = 'YES' THEN '√' ELSE '' END 允许空
//,cols.column_default 默认值
//,'' as 列说明
//from
//information_schema.columns cols inner join information_schema.tables tbs on cols.TABLE_NAME = tbs.TABLE_NAME
//where tbs.table_type = 'BASE TABLE'
// ORDER BY 1, 2 ";
// dtStruct = help.DirectQuery(strSql);
// if (type == 2012)
// strSql = @"select Row_Number() over ( order by getdate() ) as 序号, t1.name as 表名,
// case when t2.minor_id = 0 then isnull(t2.value, '') else '' end as 表说明
//from sysobjects t1
//left join sys.extended_properties t2 on t1.id=t2.major_id
//where type='u' and ( minor_id=0 or minor_id is null )";
// else if (type == 2008 || type == 2005)
//strSql = @" select ROW_NUMBER () OVER (ORDER BY table_schema) 序号, table_schema as 域, format('%s',quote_ident(table_name)) 表名, obj_description(to_regclass(table_schema || '.'|| quote_ident(table_name))) 表说明 from information_schema.tables where table_type = 'BASE TABLE' and table_schema not in ('pg_catalog','information_schema') ";
//dt = help.DirectQuery(strSql);
}
public async Task UpdateAsnInfo(List <AsInfo> requests)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
string command = "UPDATE public.ip_address_details SET as_number = @as_number, description = @description, country_code = @country_code, asn_updated = @asn_updated WHERE ip_address = @ip_address";
DateTime now = DateTime.UtcNow;
foreach (var asInfo in requests)
{
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter($"@ip_address", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(asInfo.IpAddress)
});
parameters.Add(new NpgsqlParameter($"@as_number", NpgsqlDbType.Numeric)
{
Value = asInfo.AsNumber
});
parameters.Add(new NpgsqlParameter($"@description", NpgsqlDbType.Varchar, 250)
{
Value = asInfo.Description
});
parameters.Add(new NpgsqlParameter($"@country_code", NpgsqlDbType.Char, 2)
{
Value = asInfo.CountryCode
});
parameters.Add(new NpgsqlParameter($"@asn_updated", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(now)
});
await PostgreSqlHelper.ExecuteNonQueryAsync(connectionString, command, parameters.ToArray());
}
}
public async Task UpdateReverseDns(List <IpAddressDetailsUpdateDto> entriesToUpdate)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
string command = "UPDATE public.ip_address_details SET reverse_dns_data = @reverse_dns_data, reverse_dns_updated = @reverse_dns_updated WHERE ip_address = @ip_address AND date = @date";
DateTime now = DateTime.UtcNow;
foreach (IpAddressDetailsUpdateDto entryToUpdate in entriesToUpdate)
{
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter($"@date", NpgsqlDbType.Date)
{
Value = new NpgsqlDate(entryToUpdate.Date)
});
parameters.Add(new NpgsqlParameter($"@ip_address", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(entryToUpdate.IpAddress)
});
parameters.Add(new NpgsqlParameter($"@reverse_dns_data", NpgsqlDbType.Json)
{
Value = JsonConvert.SerializeObject(entryToUpdate.ReverseDnsResponses)
});
parameters.Add(new NpgsqlParameter($"@reverse_dns_updated", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(now)
});
await PostgreSqlHelper.ExecuteNonQueryAsync(connectionString, command, parameters.ToArray());
}
}
public async Task BackfillReverseDns()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
List <string> entriesToBeUpdated = new List <string>();
Console.WriteLine($"Getting all records with no reverse dns entry at {stopwatch.ElapsedMilliseconds} ms");
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(Environment.GetEnvironmentVariable("ConnectionString"), "SELECT DISTINCT ip_address FROM public.ip_address_details where reverse_dns_data is null"))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
entriesToBeUpdated.Add(reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString());
}
}
Console.WriteLine($"Publishing {entriesToBeUpdated.Count} records at {stopwatch.ElapsedMilliseconds} ms");
List <ReverseDnsBackfillBatch> batchMessages = entriesToBeUpdated.Batch(500).Select(batch => new ReverseDnsBackfillBatch(Guid.NewGuid().ToString(), batch.ToList())).ToList();
IEnumerable <Task> publishTasks = batchMessages.Select(batchMessage => _publisher.Publish(batchMessage, _enricherConfig.SnsTopicArn));
await Task.WhenAll(publishTasks);
Console.WriteLine($"Done at {stopwatch.ElapsedMilliseconds} ms");
Console.WriteLine("Press a key");
Console.ReadLine();
}
public async Task <List <string> > GetPublicSuffixList()
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <string> suffixes = new List <string>();
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(connectionString, PublicSuffixDaoResource.SelectPublicSuffixList))
{
while (await reader.ReadAsync())
{
suffixes.Add(reader.GetString("suffix"));
}
}
return(suffixes);
}
private async Task <List <AsInfo> > GetBatch(List <string> ipAddresses, string selectQuery)
{
List <AsInfo> asnInfos = new List <AsInfo>();
if (ipAddresses.Any())
{
string query = string.Join(" UNION ",
ipAddresses.Select((_, i) =>
$"({string.Format(selectQuery, $"@ip_address{i}", $"@original_ip_address{i}")})"));
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
for (int i = 0; i < ipAddresses.Count; i++)
{
string ipAddress = ipAddresses[i];
parameters.Add(new NpgsqlParameter($"ip_address{i}", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(ipAddress)
});
parameters.Add(new NpgsqlParameter($"original_ip_address{i}", NpgsqlDbType.Varchar)
{
Value = ipAddress
});
}
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (DbDataReader reader =
await PostgreSqlHelper.ExecuteReaderAsync(connectionString, query, parameters.ToArray()))
{
while (await reader.ReadAsync())
{
asnInfos.Add(new AsInfo
{
AsNumber = reader.GetInt32("asn"),
IpAddress = reader.GetString("original_ip_address"),
Description = reader.GetString("description"),
CountryCode = reader.GetString("country")
});
}
}
}
return(asnInfos);
}
public DatabaseInfo CreateDatabaseInfo()
{
DatabaseInfo database = new DatabaseInfo();
using (helper = new PostgreSqlHelper(Connect))
{
string sql = @"SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE 'pg%'
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;";
DataTable nameTable = helper.GetDataTable(sql);
for (int i = 0; i < nameTable.Rows.Count; ++i)
{
database.AddTable(CreateTable(nameTable.Rows[i][0].ToString()));
}
}
return(database);
}
public async Task BackfillAsn()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Console.WriteLine($"Getting all records with no ASN details at {stopwatch.ElapsedMilliseconds} ms");
List <string> ipAddressesToBeUpdated = new List <string>();
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(Environment.GetEnvironmentVariable("ConnectionString"), "SELECT distinct ip_address FROM public.ip_address_details where as_number is null"))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
ipAddressesToBeUpdated.Add(reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString());
}
}
Console.WriteLine($"Publishing {ipAddressesToBeUpdated.Count} records at {stopwatch.ElapsedMilliseconds} ms");
List <AsnBackfillBatch> batchMessages = new List <AsnBackfillBatch>();
foreach (IEnumerable <string> batch in ipAddressesToBeUpdated.Batch(ipAddressesToBeUpdated.Count))
{
batchMessages.Add(new AsnBackfillBatch(Guid.NewGuid().ToString(), batch.ToList()));
}
IEnumerable <Task> publishTasks = batchMessages.Select(batchMessage => _publisher.Publish(batchMessage, _enricherConfig.SnsTopicArn));
var a = publishTasks.Count();
Console.WriteLine($"Created {a} tasks");
await Task.WhenAll(publishTasks);
Console.WriteLine($"Done at {stopwatch.ElapsedMilliseconds} ms");
Console.WriteLine("Press a key");
Console.ReadLine();
}
public async Task <List <IpAddressDetails> > GetIpAddressDetails(List <IpAddressDetailsRequest> ipAddressDetailsRequests)
{
List <IpAddressDetailsRequest> distinctDetailsRequests = ipAddressDetailsRequests
.GroupBy(x => new { x.Date, x.IpAddress })
.Select(x => x.First())
.ToList();
_log.LogInformation($"Retrieving address details for {distinctDetailsRequests.Count} distinct requests from {ipAddressDetailsRequests.Count}");
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <IpAddressDetails> result = new List <IpAddressDetails>();
List <IGrouping <DateTime, IpAddressDetailsRequest> > requestsByDate = ipAddressDetailsRequests
.GroupBy(x => x.Date)
.ToList();
List <string> queries = new List <string>();
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
for (int i = 0; i < requestsByDate.Count; i++)
{
IEnumerable <NpgsqlParameter> ipAddressParams = requestsByDate[i].Select((request, j) =>
{
bool ipAddressValue = IPAddress.TryParse(request.IpAddress, out IPAddress ipAddress);
if (!ipAddressValue)
{
_log.LogInformation($"Unable to parse IP address {request.IpAddress}");
throw new ArgumentException($"Unable to parse IP address {request.IpAddress}");
}
NpgsqlParameter parameter = new NpgsqlParameter($"ip_address_{i}_{j}", NpgsqlDbType.Inet)
{
Value = ipAddress
};
return(parameter);
});
NpgsqlParameter dateParams = new NpgsqlParameter($"date_{i}", NpgsqlDbType.Date)
{
Value = requestsByDate[i].Key
};
parameters.AddRange(ipAddressParams);
parameters.Add(dateParams);
string queryForDate = string.Format(IpAddressIntelligenceDaoResources.SelectIpAddressDetails, string.Join(',', requestsByDate[i].Select((_, j) => $"@ip_address_{i}_{j}")), $"@date_{i}");
queries.Add(queryForDate);
}
string query = string.Join(" union all ", queries);
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(connectionString, query, parameters.ToArray()))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
string ipAddress = reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString();
DateTime date = reader.GetDateTime("date");
int asNumberOrdinal = reader.GetOrdinal("as_number");
int?asNumber = reader.IsDBNull(asNumberOrdinal) ? (int?)null : reader.GetInt32("as_number");
int descriptionOrdinal = reader.GetOrdinal("description");
string description = reader.IsDBNull(descriptionOrdinal) ? null : reader.GetString("description");
int countryCodeOrdinal = reader.GetOrdinal("country_code");
string countryCode = reader.IsDBNull(countryCodeOrdinal) ? null : reader.GetString("country_code");
int blocklistDataOrdinal = reader.GetOrdinal("blocklist_data");
List <BlocklistAppearance> blocklistData = reader.IsDBNull(blocklistDataOrdinal) ? null : JsonConvert.DeserializeObject <List <BlocklistAppearance> >(reader.GetString("blocklist_data"));
int reverseDnsDataOrdinal = reader.GetOrdinal("reverse_dns_data");
List <ReverseDnsResponse> reverseDnsData = reader.IsDBNull(reverseDnsDataOrdinal) ? null : JsonConvert.DeserializeObject <List <ReverseDnsResponse> >(reader.GetString("reverse_dns_data"));
int asnUpdatedOrdinal = reader.GetOrdinal("asn_updated");
DateTime?asnUpdated = reader.IsDBNull(asnUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("asn_updated");
int blocklistUpdatedOrdinal = reader.GetOrdinal("blocklist_updated");
DateTime?blocklistUpdated = reader.IsDBNull(blocklistUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("blocklist_updated");
int reverseDnsUpdatedOrdinal = reader.GetOrdinal("reverse_dns_updated");
DateTime?reverseDnsUpdated = reader.IsDBNull(reverseDnsUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("reverse_dns_updated");
IpAddressDetails ipAddressDetails = new IpAddressDetails(
ipAddress, date, asNumber, description, countryCode, blocklistData, reverseDnsData, asnUpdated,
blocklistUpdated, reverseDnsUpdated);
result.Add(ipAddressDetails);
}
}
_log.LogInformation($"Found {result.Count} IpAddressDetails in database from request for {ipAddressDetailsRequests.Count}");
return(result);
}
public async Task SaveIpAddressDetails(List <IpAddressDetails> ipAddressDetailsResponse)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <string> values = new List <string>();
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
for (int i = 0; i < ipAddressDetailsResponse.Count; i++)
{
parameters.Add(new NpgsqlParameter($"@date_{i}", NpgsqlDbType.Date)
{
Value = new NpgsqlDate(ipAddressDetailsResponse[i].Date)
});
parameters.Add(new NpgsqlParameter($"@ip_address_{i}", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(ipAddressDetailsResponse[i].IpAddress)
});
parameters.Add(ipAddressDetailsResponse[i].AsNumber.HasValue
? new NpgsqlParameter($"@as_number_{i}", NpgsqlDbType.Integer)
{
Value = ipAddressDetailsResponse[i].AsNumber.Value
}
: new NpgsqlParameter($"@as_number_{i}", NpgsqlDbType.Integer)
{
Value = DBNull.Value
});
parameters.Add(!string.IsNullOrEmpty(ipAddressDetailsResponse[i].Description)
? new NpgsqlParameter($"@description_{i}", NpgsqlDbType.Varchar, 250)
{
Value = ipAddressDetailsResponse[i].Description
}
: new NpgsqlParameter($"@description_{i}", NpgsqlDbType.Varchar)
{
Value = DBNull.Value
});
parameters.Add(!string.IsNullOrEmpty(ipAddressDetailsResponse[i].CountryCode)
? new NpgsqlParameter($"@country_code_{i}", NpgsqlDbType.Char, 2)
{
Value = ipAddressDetailsResponse[i].CountryCode
}
: new NpgsqlParameter($"@country_code_{i}", NpgsqlDbType.Char, 2)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].ReverseDnsResponses != null && ipAddressDetailsResponse[i].ReverseDnsResponses.Count > 0
? new NpgsqlParameter($"@reverse_dns_data_{i}", NpgsqlDbType.Json)
{
Value = JsonConvert.SerializeObject(ipAddressDetailsResponse[i].ReverseDnsResponses)
}
: new NpgsqlParameter($"@reverse_dns_data_{i}", NpgsqlDbType.Json)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].BlockListOccurrences != null && ipAddressDetailsResponse[i].BlockListOccurrences.Count > 0
? new NpgsqlParameter($"@blocklist_data_{i}", NpgsqlDbType.Json)
{
Value = JsonConvert.SerializeObject(ipAddressDetailsResponse[i].BlockListOccurrences)
}
: new NpgsqlParameter($"@blocklist_data_{i}", NpgsqlDbType.Json)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].AsnLookupTimestamp.HasValue
? new NpgsqlParameter($"@asn_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(ipAddressDetailsResponse[i].AsnLookupTimestamp.Value)
}
: new NpgsqlParameter($"@asn_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].BlocklistLookupTimestamp.HasValue
? new NpgsqlParameter($"@blocklist_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(ipAddressDetailsResponse[i].BlocklistLookupTimestamp.Value)
}
: new NpgsqlParameter($"@blocklist_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].ReverseDnsLookupTimestamp.HasValue
? new NpgsqlParameter($"@reverse_dns_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(ipAddressDetailsResponse[i].ReverseDnsLookupTimestamp.Value)
}
: new NpgsqlParameter($"@reverse_dns_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = DBNull.Value
});
values.Add($"(@date_{i}, @ip_address_{i}, @as_number_{i}, @description_{i}, @country_code_{i}, @reverse_dns_data_{i}, @blocklist_data_{i}, @asn_updated_{i}, @blocklist_updated_{i}, @reverse_dns_updated_{i})");
}
string command = string.Format(IpAddressIntelligenceDaoResources.InsertIpAddressDetails, string.Join(",", values));
await PostgreSqlHelper.ExecuteNonQueryAsync(connectionString, command, parameters.ToArray());
}
public async Task <List <IpAddressDetails> > GetIpAddressDetails(string ipAddress)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
string command = "SELECT * from public.ip_address_details WHERE ip_address = @ip_address";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter($"@ip_address", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(ipAddress)
});
List <IpAddressDetails> results = new List <IpAddressDetails>();
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(connectionString, command, parameters.ToArray()))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
string ip = reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString();
int asNumberOrdinal = reader.GetOrdinal("as_number");
int?asNumber = reader.IsDBNull(asNumberOrdinal) ? (int?)null : reader.GetInt32("as_number");
int descriptionOrdinal = reader.GetOrdinal("description");
string description = reader.IsDBNull(descriptionOrdinal) ? null : reader.GetString("description");
int countryCodeOrdinal = reader.GetOrdinal("country_code");
string countryCode = reader.IsDBNull(countryCodeOrdinal) ? null : reader.GetString("country_code");
int asnUpdatedOrdinal = reader.GetOrdinal("asn_updated");
DateTime?asnUpdated = reader.IsDBNull(asnUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("asn_updated");
int blocklistUpdatedOrdinal = reader.GetOrdinal("blocklist_updated");
DateTime?blocklistUpdated = reader.IsDBNull(blocklistUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("blocklist_updated");
int reverseDnsUpdatedOrdinal = reader.GetOrdinal("reverse_dns_updated");
DateTime?reverseDnsUpdated = reader.IsDBNull(reverseDnsUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("reverse_dns_updated");
string blocklistData = reader.GetString("blocklist_data");
string reverseDnsData = reader.GetString("reverse_dns_data");
IpAddressDetails ipAddressDetails = new IpAddressDetails(
ip,
reader.GetDateTime("date"),
asNumber,
description,
countryCode,
string.IsNullOrEmpty(blocklistData) ? null : JsonConvert.DeserializeObject <List <BlocklistAppearance> >(blocklistData),
string.IsNullOrEmpty(reverseDnsData) ? null : JsonConvert.DeserializeObject <List <ReverseDnsResponse> >(reverseDnsData),
asnUpdated,
blocklistUpdated,
reverseDnsUpdated);
results.Add(ipAddressDetails);
}
}
return(results);
}
