private static void ClearDatabase(MySqlConnection conn, BaseURLElement baseUrl)
{
Console.WriteLine("Clearing records for: " + baseUrl.URL);
Queue <string> tableNames = new Queue <string>();
try
{
RunSQL("SET FOREIGN_KEY_CHECKS=0;", conn);
using (MySqlCommand command = new MySqlCommand("show full tables where Table_Type != 'VIEW'", conn))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string table = reader.GetString(0);
if (table.StartsWith("esd_"))
{
continue;
}
tableNames.Enqueue(table);
}
}
}
while (tableNames.Count > 0)
{
string table = tableNames.Dequeue();
try
{
using (MySqlCommand command = new MySqlCommand(string.Format("DELETE FROM `{0}` WHERE api_id={1};", table, baseUrl.ID), conn))
{
command.ExecuteNonQuery();
}
}
catch (Exception e)
{
tableNames.Enqueue(table);
}
}
}
finally
{
RunSQL("SET FOREIGN_KEY_CHECKS=1;", conn);
}
}
private static void ProcessEndPoint(BaseURLElement baseUrl, dynamic resources, List <string> resourceNames, HashDatabase hashDatabase)
{
try
{
Console.WriteLine("Starting to process: " + baseUrl.URL);
while (IsServerKeyUnique(baseUrl.ServerKey))
{
Console.WriteLine("Pausing: " + baseUrl.URL);
Thread.Sleep(30000);
}
lock (locker)
{
CurrentServerKeys.Add(baseUrl.ServerKey);
}
Dictionary <string, Dictionary <string, string> > keyReWrite = new Dictionary <string, Dictionary <string, string> >();
DelayeredResult delayeredResult = Delayering.DelayerPaginatedData(baseUrl.URL, new APIValidatorSettings()
{
RequestRate = baseUrl.RequestRate
}).GetAwaiter().GetResult();
if (!HasUpdated(delayeredResult, hashDatabase.Get(baseUrl.URL)))
{
Console.WriteLine("Data not changed skipping: " + baseUrl.URL);
return;
}
List <Row> rows = new List <Row>();
foreach (KeyValuePair <string, Dictionary <string, dynamic> > kvp in delayeredResult.Collection)
{
dynamic resource = GetResource(resources, kvp.Key);
string resourceName = Resources.FindResourceName(kvp.Key, resourceNames);
foreach (KeyValuePair <string, dynamic> vals in kvp.Value)
{
if (resource == null)
{
continue;
}
Row row = new Row(resourceName);
foreach (KeyValuePair <string, dynamic> fieldKvp in vals.Value)
{
string columnName = fieldKvp.Key.ToLower();
FieldStatus fieldStatus = ResourceFieldStatus(resource, columnName);
if (fieldKvp.Value == null || !fieldStatus.IsVisible)
{
continue;
}
row.Fields.Add(COLUMN_ESCAPE + columnName + COLUMN_ESCAPE);
if (fieldKvp.Value is String)
{
if (string.IsNullOrEmpty(fieldKvp.Value) && fieldStatus.IsNullByDefault)
{
row.Values.Add(NULL);
continue;
}
row.Values.Add(QUOTATION + fieldKvp.Value + QUOTATION);
continue;
}
if (fieldKvp.Value.Type == Newtonsoft.Json.Linq.JTokenType.String)
{
if (string.IsNullOrEmpty(fieldKvp.Value.Value) && fieldStatus.IsNullByDefault)
{
row.Values.Add(NULL);
continue;
}
row.Values.Add(QUOTATION + MySqlHelper.EscapeString(Convert.ToString(fieldKvp.Value.Value)) + QUOTATION);
continue;
}
if (fieldKvp.Value.Type == Newtonsoft.Json.Linq.JTokenType.Date)
{
row.Values.Add(QUOTATION + MySqlHelper.EscapeString(Convert.ToString(fieldKvp.Value.Value)) + QUOTATION);
continue;
}
row.Values.Add(QUOTATION + Convert.ToString(fieldKvp.Value) + QUOTATION);
}
if (!row.Fields.Contains(ID_COLUMN))
{
row.Fields.Add(ID_COLUMN);
row.Values.Add(QUOTATION + Guid.NewGuid().ToString() + QUOTATION);
}
else
{
int index = row.Fields.IndexOf(ID_COLUMN);
if (index > -1 && resourceName != "service" && resourceName != "organization" && resourceName != "location" && resourceName != "taxonomy")
{
if (!keyReWrite.ContainsKey(resourceName))
{
keyReWrite.Add(resourceName, new Dictionary <string, string>());
}
string originalKey = row.Values[index].Replace(QUOTATION, string.Empty);
if (!keyReWrite[resourceName].ContainsKey(originalKey))
{
keyReWrite[resourceName].Add(originalKey, Guid.NewGuid().ToString());
}
row.Values[index] = QUOTATION + keyReWrite[resourceName][originalKey] + QUOTATION;
}
}
Console.WriteLine("Reading: " + resourceName);
rows.Add(row);
}
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
{
conn.Open();
try
{
ClearDatabase(conn, baseUrl);
List <Row> retryRows = new List <Row>();
int retries = 0;
do
{
retryRows = new List <Row>();
foreach (Row row in rows)
{
try
{
Console.WriteLine("Executing 1 row for " + baseUrl.URL);
RunSQL(row.ToSQL(keyReWrite, baseUrl.ID), conn);
}
catch (MySqlException e)
{
if (e.Number != 1062)
{
retryRows.Add(row);
}
}
catch (Exception e)
{
if (!e.Message.StartsWith("Cannot add or update a child row: a foreign key constraint fails"))
{
retryRows.Add(row);
}
}
}
retries++;
if (retryRows.Count > 0)
{
Console.WriteLine("Retrying: " + retryRows.Count);
}
}while (retryRows.Count > 0 && retries <= 5);
}
finally
{
RunSQL("INSERT IGNORE INTO link_taxonomy SELECT id, 'service_type', service_id, taxonomy_id, api_id FROM service_taxonomy;", conn);
RunSQL("UPDATE location INNER JOIN physical_address ON location.id = physical_address.location_id AND postal_code IS NOT NULL AND postal_code <> '' AND (location.latitude IS NULL OR location.longitude IS NULL) INNER JOIN esd_postcode ON REPLACE(`postal_code`, ' ', '') = esd_postcode.code SET location.latitude = esd_postcode.latitude, location.longitude = esd_postcode.longitude; ", conn);
}
hashDatabase.Hashes[baseUrl.URL] = delayeredResult.Hashes;
}
hashDatabase.Save();
}
finally
{
CurrentServerKeys.Remove(baseUrl.ServerKey);
}
}