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