private SqlDataReader doSQL(string qStr, bool exeReader = true)
{
Utils.maybe_reconnect(this, ref sqlConnection, this.config.try_again_after, this.logger);
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = qStr;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Notification = null;
sqlCommand.CommandTimeout = this.config.defaultSQLQueryTimeout;
try
{
if (exeReader)
{
return(sqlCommand.ExecuteReader());
}
else
{
sqlCommand.ExecuteNonQuery();
return(null);
}
}
catch (Exception error)
{
if (!error.Message.Contains("already an object"))
{
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
this.isReadyToBeStarted = false;
}
}
}
return(null);
}
internal void runQueryToFile(string runQuery, string resultFile, string resultType)
{
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
string cusCodeEnv = String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment);
try
{
Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger);
TableDumper.runQueryToFile(this.sqlConnection, runQuery, resultFile, cusCodeEnv, this.config.defaultSQLQueryTimeout, resultType);
this.logger.write(String.Format("run_query {0} into {1} file successfully and uploaded to s3.", runQuery, resultFile), Logger.LOGLEVEL.INFO);
}
catch (S3UploadException error)
{
this.logger.write(String.Format("Cannot start uploading file {0}.gz to s3 because {1}.", resultFile, error.ToString()), Logger.LOGLEVEL.ERROR);
}
catch (Exception error)
{
this.logger.write(String.Format("Cannot start running run_query command: {0} because {1}.", runQuery, error.ToString()), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
});
}
public ServiceControlCenter(Config eConfig, Logger logger)
: base(eConfig, logger, "control")
{
this.logger = logger;
this.eConfig = eConfig;
this.consumerCallback += (model, args) =>
{
string cmdStr = Encoding.UTF8.GetString(args.Body);
ulong deliveryTag = args.DeliveryTag;
#if (DEBUG)
Console.WriteLine("{0} - {1}", cmdStr, deliveryTag);
#endif
try
{
dynamic deserializeObject = JsonConvert.DeserializeObject(cmdStr);
this.procesCommand(deserializeObject, deliveryTag);
}
catch (Exception error)
{
logger.write(String.Format("Cannot execute the command {0} because {1}", cmdStr, error.Message), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
this.consumerBasicACK(deliveryTag);
};
this.consumerInit();
this.cpuCounter = new PerformanceCounter("Process", "% Processor Time", Process.GetCurrentProcess().ProcessName);
this.ramCounter = new PerformanceCounter("Process", "Working Set - Private", Process.GetCurrentProcess().ProcessName);
}
public void dbSchemaDump()
{
string dumpSchemaQuery = @"SELECT cl.table_name as 'TABLE_NAME',
cl.column_name as 'COLUMN_NAME',
t.constraint_type as 'CONSTRAINT_TYPE',
cl.DATA_TYPE as 'DATA_TYPE'
FROM INFORMATION_SCHEMA.COLUMNS cl
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
ON cl.table_catalog = cu.table_catalog
AND cl.table_schema = cu.table_schema
AND cl.table_name = cu.table_name
AND cl.column_name = cu.column_name
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
ON cu.constraint_catalog = t.constraint_catalog
AND cu.constraint_schema = t.constraint_schema
AND cu.constraint_name = t.constraint_name";
string csvFilename = "schema";
string dumpResult = "failure";
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
try
{
this.logger.write(String.Format(@"Dumping tables schema to {1}\{0}.csv", csvFilename, Path.GetTempPath()), Logger.LOGLEVEL.INFO);
TableDumper.QueryToFile(this.sqlConnection,
dumpSchemaQuery,
csvFilename,
String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment),
this.config.defaultSQLQueryTimeout);
this.logger.write(String.Format("Dumped tables schema into {0} file successfully and uploaded to s3.", csvFilename), Logger.LOGLEVEL.INFO);
dumpResult = "success";
}
catch (S3UploadException error)
{
this.logger.write(String.Format("Cannot start uploading file {0} to s3 because {1}.", csvFilename, error.ToString()), Logger.LOGLEVEL.ERROR);
}
catch (Exception error)
{
this.logger.write(String.Format("Cannot start dumping tables schema because {0}.", error.ToString()), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
this.serviceControlCenter.publishMessage(JsonConvert.SerializeObject(new Dictionary <string, object>()
{
{ "customer", this.config.cprCustomerCode },
{ "environment", this.config.cprCustomerEnvironment },
{ "uuid", Guid.NewGuid().ToString() },
{ "type", "dump_schema" },
{ "result", dumpResult }
}, new JsonSerializerSettings()
{
Formatting = Formatting.Indented,
}), "ecpr-config-s3-response", 2);
});
}
public QueryNotification(
string[] m_tableFields,
string m_tableName,
Logger logger,
Config config,
Dictionary <string, Dictionary <string, string> > json_mapping = null,
bool m_autoCleanupIfRestart = false)
{
jsonSerializerSettings.Converters.Add(new CprPlusDateTimeConvertor());
this.tableName = m_tableName;
this.tableFieldsNotification = m_tableFields;
this.autoCleanupIfRestart = m_autoCleanupIfRestart;
this.logger = logger;
this.config = config;
this.ecpr_ids = new List <int>();
this.json_mapping = json_mapping;
this.reCheckTableFields();
buildStrCommand();
ensureCloneTable();
try
{
ensureEventsTriggerIsCreated();
}catch (Exception error)
{
this.logger.write(String.Format("Cannot create or update trigger for table {0} because {1}", this.tableName, error.Message), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
if (this.autoCleanupIfRestart)
{
var info_msg = String.Format("Clear data in the table ecpr_{0}_logs", this.tableName);
this.logger.write(info_msg, Logger.LOGLEVEL.INFO);
using (SqlConnection _sqlConnection = new SqlConnection(config.cprDatabaseConnectionString))
{
this.executeNonQuery(String.Format("DELETE FROM ecpr_{0}_logs", this.tableName), _sqlConnection);
}
}
this.logger.write(String.Format("Subscribed on table {0}", this.tableName), Logger.LOGLEVEL.INFO);
}
private void longPoll()
{
if (Utils.isStartingFromWindowsRestart())
{
Thread.Sleep(this.config.wait_before_start_from_windows_restart * 1000);
}
logger.write("Checking internet/network connection...", Logger.LOGLEVEL.INFO);
while (!NetworkMonitoring.IsInternetAvailable())
{
logger.write(String.Format("No internet connection. Will try again after {0} secs.", this.config.try_again_after), Logger.LOGLEVEL.ERROR);
Thread.Sleep(this.config.try_again_after * 1000);
}
logger.write("Internet/network connection is succesful.", Logger.LOGLEVEL.INFO);
this.logger.ensureConnect();
logger.write("Checking SQL SERVER connection...", Logger.LOGLEVEL.INFO);
string errorMessage;
while (!Utils.try_connect_to_db(this.config.cprDatabaseConnectionString, out errorMessage))
{
logger.write(String.Format("Had an error while trying to connect to SQL SERVER. Will try again after {0} secs.", this.config.try_again_after), Logger.LOGLEVEL.ERROR);
logger.write(errorMessage, Logger.LOGLEVEL.ERROR);
Thread.Sleep(this.config.try_again_after * 1000);
}
logger.write("SQL SERVER connection is succesful.", Logger.LOGLEVEL.INFO);
logger.write("STARTING ECPR service...", Logger.LOGLEVEL.INFO);
this.serviceControlCenter = new ServiceControlCenter(this.config, this.logger);
this.serviceControlCenter.addManager(this);
this.logger.write(String.Format("Connecting to database {0} ...", this.config.cprDatabase), Logger.LOGLEVEL.INFO);
this.sqlConnection = new SqlConnection(connectionString);
Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger);
System.Timers.Timer ecprTimer = new System.Timers.Timer();
ecprTimer.Elapsed += (sender, e) => Utils.maybe_reconnect(this, ref this.sqlConnection, this.config.try_again_after, this.logger);
ecprTimer.Interval += (this.config.defaultDatabaseConnectionTimeout * 1000 + 5000);
ecprTimer.Enabled = true;
if (isReadyToBeStarted)
{
this.ensureTablesExisting();
this.ensureFieldsExisting();
this.getIgnoreFieldsPK();
}
if (sqlConnection.State == ConnectionState.Open)
{
this.syncControlCenter = new SyncControlCenter(this.config, this.logger);
this.fullsyncControlCenter = new FullSyncControlCenter(this.config, this.logger);
}
if ((syncControlCenter != null && !syncControlCenter.isSubscribed) ||
(serviceControlCenter != null && !serviceControlCenter.isSubscribed))
{
logger.write(
String.Format("Cannot subscribed to amqp channels, will try again in {0} secs !!!!!!!", intervalSecs / 1000),
Logger.LOGLEVEL.ERROR);
}
if (!this.config.needToFetchSchemaAndData)
{
dbSchemaPush();
dbDataPush();
this.config.needToFetchSchemaAndData = true;
}
if (sqlConnection.State == ConnectionState.Open)
{
this.isReadyToBeStarted = true;
foreach (string tableName in this.config.cprTables.Keys)
{
try
{
// Make sure table is exists before subscribe
bool tableExists = false;
SqlDataReader sqlDataReader = this.doSQL(String.Format("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME ='{0}'", tableName));
if (sqlDataReader != null)
{
if (sqlDataReader.HasRows)
{
tableExists = true;
}
sqlDataReader.Close();
}
if (tableExists)
{
var qN = new QueryNotification(
this.config.cprTables[tableName].ToArray(),
tableName,
this.logger,
this.config,
this.config.restListAPIJSONMapping,
this.config.autoCleanupIfRestart);
qN.subscribe();
}
else
{
this.logger.write(String.Format("Cannot subscribed the table {0} for query notification because it doesn't exists in database. Ignored it!", tableName), Logger.LOGLEVEL.ERROR);
}
}
catch (Exception error)
{
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
this.isReadyToBeStarted = false;
}
}
}
if (this.config.diff_tables != null && this.isReadyToBeStarted)
{
this.logger.write("Enable DIFF Tables feature.", Logger.LOGLEVEL.INFO);
cprDataAnalysis = new CPRDataAnalysis(this.config, this.logger);
cprDataAnalysis.Init();
}
this._eventsThread = new Thread(eventsProcess);
this._eventsThread.IsBackground = false;
this._eventsThread.Start();
this._eventsTimer = new System.Timers.Timer(this.intervalSecs);
this._eventsTimer.AutoReset = false;
this._eventsTimer.Elapsed += (sender, e) => this._inProcessEvent.Set();
this._eventsTimer.Start();
logger.write("SERVICE STARTED!!!", Logger.LOGLEVEL.INFO);
}
internal void dumpTable(List <string> tableList, List <string> exclude_columns = null, string export_format = "csv", string filterCond = null)
{
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
foreach (string tableName in tableList)
{
try
{
string fieldsName = "*";
if (this.config.dumpIgnoreFields != null && this.config.dumpIgnoreFields.ContainsKey(tableName.ToUpper()))
{
exclude_columns = new List <string>(this.config.dumpIgnoreFields[tableName.ToUpper()]);
}
if (exclude_columns != null && exclude_columns.Count > 0)
{
using (var command = new SqlCommand(String.Format("select lower(COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '{0}' and COLUMN_NAME not in ({1})", tableName, "'" + String.Join("','", exclude_columns.ToArray()) + "'"), this.sqlConnection))
{
command.CommandTimeout = this.config.defaultSQLQueryTimeout;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
exclude_columns.Clear();
while (reader.Read())
{
exclude_columns.Add(reader.GetValue(0).ToString());
}
fieldsName = String.Join(",", exclude_columns.ToArray());
}
}
}
}
this.logger.write(String.Format("Dumping table {0} to {1}", tableName, Path.GetTempPath()), Logger.LOGLEVEL.INFO);
using (SqlConnection _sqlConnection = new SqlConnection(this.config.cprDatabaseConnectionString))
{
try
{
if (_sqlConnection.State != ConnectionState.Open)
{
_sqlConnection.Open();
}
}
catch (Exception) { }
if (export_format.ToLower() == "json")
{
TableDumper.DumpTableToFile_JSON(_sqlConnection, tableName, fieldsName, filterCond, String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment), this.config.defaultSQLQueryTimeout);
}
else
{
TableDumper.DumpTableToFile(_sqlConnection, tableName, fieldsName, filterCond, String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment), this.config.defaultSQLQueryTimeout);
}
}
this.logger.write(String.Format("Dumped table {0} into {1} file successfully and uploaded to s3.", tableName, export_format), Logger.LOGLEVEL.INFO);
string downloadLink = S3Uploader.GenerateDownloadLink(tableName, this.config.cprCustomerCode, this.config.cprCustomerEnvironment, export_format);
this.serviceControlCenter.publishMessage(JsonConvert.SerializeObject(new Dictionary <string, object>()
{
{ "customer", this.config.cprCustomerCode },
{ "environment", this.config.cprCustomerEnvironment },
{ "uuid", Guid.NewGuid().ToString() },
{ "type", "url" },
{ "table_name", tableName },
{ "url", downloadLink }
}, new JsonSerializerSettings()
{
Formatting = Formatting.Indented,
}), "ecpr-config-s3-response", 2);
}
catch (S3UploadException error)
{
this.logger.write(String.Format("Cannot start uploading file {0}.{1}.gz to s3 because {2}.", tableName, export_format, error.ToString()), Logger.LOGLEVEL.ERROR);
}
catch (Exception error)
{
this.logger.write(String.Format("Cannot start dumping table {0} because {1}.", tableName, error.ToString()), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
}
});
}
private void procesCommand(dynamic cmdObj, ulong deliveryTag)
{
if (cmdObj.config != null)
{
string newConfigURL = cmdObj.config;
this.logger.write(String.Format("Change ecpr config to {0}, it need to be restarted the window service.", newConfigURL), Logger.LOGLEVEL.INFO);
this.consumerBasicACK(deliveryTag);
this.eConfig.changeConfigURL(newConfigURL);
this.objManager.dbTablesCleanUp();
this.logger.write("RESTARTING SERVICE !!!", Logger.LOGLEVEL.INFO);
Environment.Exit(1);
}
else if (cmdObj.admin != null)
{
string subCommand = cmdObj.admin;
switch (subCommand)
{
case "schema":
if (this.objManager != null)
{
this.objManager.dbSchemaPush();
}
break;
case "dump_schema":
if (this.objManager != null)
{
this.objManager.dbSchemaDump();
}
break;
case "fullsync":
if (this.objManager != null)
{
this.objManager.dbDataPush();
}
break;
case "restart":
this.consumerBasicACK(deliveryTag);
restartService();
break;
case "reload":
this.consumerBasicACK(deliveryTag);
reloadService();
restartService();
break;
case "timezone":
this.get_UTC_timezone();
break;
case "ping":
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
var pingRespData = new PingResponseData();
string errorMessage;
pingRespData.type = "pong";
pingRespData.memory_usage = Math.Round((decimal)((this.ramCounter.NextValue() / 1024) / 1024), 2);
pingRespData.cpu_usage = Math.Round((decimal)this.cpuCounter.NextValue(), 2);
pingRespData.db_connection_status = Utils.try_connect_to_db(this.eConfig.cprDatabaseConnectionString, out errorMessage) ? "GOOD" : "DISCONNECTED";
pingRespData.uuid = cmdObj.uuid != null ? cmdObj.uuid : Guid.NewGuid().ToString();
this.publishMessage(JsonConvert.SerializeObject(pingRespData), "ecpr-config-response", 1);
});
break;
case "update":
string service_name = ConfigurationManager.AppSettings.Get("ecpr_service_name");
int command_code = 255;
if (service_name.Contains("stable"))
{
command_code = 128;
}
else if (service_name.Contains("demo"))
{
command_code = 129;
}
else if (service_name.Contains("sandbox"))
{
command_code = 130;
}
else if (service_name.Contains("beach"))
{
command_code = 131;
}
else if (service_name.Contains("testing"))
{
command_code = 132;
}
else if (service_name.Contains("unstable"))
{
command_code = 133;
}
Console.WriteLine(service_name);
Console.WriteLine(command_code);
try
{
ServiceController serviceController = new ServiceController("ECPRAutoUpdate");
serviceController.ExecuteCommand(command_code);
this.logger.write("Sent update command to ECPR Auto Update service.");
}
catch (Exception error)
{
this.logger.write(String.Format("Cannot run update ecpr because {0}", error.Message), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
break;
default:
logger.write(String.Format("Invalid admin command {0}", subCommand), Logger.LOGLEVEL.ERROR);
break;
}
}
else if (cmdObj.sync != null)
{
string tableName = cmdObj.sync;
this.objManager.dbDataPush(tableName, cmdObj.filter);
}
else if (cmdObj.schema != null)
{
string tableName = cmdObj.schema;
this.objManager.dbSchemaPush(tableName);
}
else if (cmdObj.dump != null)
{
string tableName = cmdObj.dump;
List <string> exclude_columns = null;
string filterCond = null;
if (tableName.Trim() == String.Empty)
{
logger.write(String.Format("Invalid dump command: {0}", cmdObj), Logger.LOGLEVEL.ERROR);
}
else
{
if (cmdObj.exclude_columns != null)
{
exclude_columns = cmdObj.exclude_columns.ToObject <List <string> >();
}
if (cmdObj.filter != null)
{
filterCond = cmdObj.filter;
}
this.objManager.dumpTable(new List <string> {
tableName
},
exclude_columns,
exportFormat(cmdObj), filterCond);
}
}
else if (cmdObj.dumps != null)
{
List <string> listTables = cmdObj.dumps.ToObject <List <string> >();
if (listTables == null || listTables.Count == 0)
{
logger.write(String.Format("Invalid dumps command: {0}", cmdObj), Logger.LOGLEVEL.ERROR);
}
else
{
this.objManager.dumpTable(listTables,
null,
exportFormat(cmdObj));
}
}
else if (cmdObj.count != null)
{
string tableName = cmdObj.count;
this.objManager.countTable(tableName, cmdObj.filter != null ? (string)cmdObj.filter : String.Empty);
}
else if (cmdObj.run_query != null)
{
string runQuery = cmdObj.run_query;
runQuery = runQuery.Trim();
// only select query is allowed
if (runQuery.StartsWith("select", StringComparison.OrdinalIgnoreCase))
{
string resultFile = String.Empty;
if (cmdObj.result_file != null)
{
resultFile = cmdObj.result_file;
resultFile = resultFile.Trim();
}
else
{
logger.write("result_file is required for run_query command.", Logger.LOGLEVEL.ERROR);
return;
}
string resultType = "csv";
if (cmdObj.result_type != null)
{
resultType = cmdObj.result_type;
resultType = resultType.Trim().ToLower();
}
if (resultType != "csv" && resultType != "json")
{
logger.write(String.Format("result_type = {0} is not supported for run_query command.", resultType), Logger.LOGLEVEL.ERROR);
return;
}
resultFile = String.Format("{0}_{1}.{2}", resultFile, DateTime.Now.ToString("MM-dd-yyyy_HH-mm-ss"), resultType);
this.objManager.runQueryToFile(runQuery, resultFile, resultType);
}
else
{
logger.write("Only select query is allowed in run_query command.", Logger.LOGLEVEL.ERROR);
}
}
else
{
logger.write(String.Format("Unknown or Invalid command {0}", cmdObj), Logger.LOGLEVEL.ERROR);
}
}