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