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