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 static void DumpTableToFile(SqlConnection connection, string tableName, string fieldsName, string filterCond, string customerCodeEnv, int commandTimeout) { string fileName = String.Format("{0}.csv", tableName.ToLower()); string destinationFile = Path.Combine(Path.GetTempPath(), fileName); string whereCond = ""; if (filterCond != null) { whereCond = String.Format(" WHERE {0}", filterCond); } SqlTransaction trans; trans = connection.BeginTransaction(IsolationLevel.ReadUncommitted); using (var command = new SqlCommand("SELECT " + fieldsName + " FROM " + tableName + whereCond, connection)) { command.CommandTimeout = commandTimeout; command.Transaction = trans; TableDumper.readerToFile(command, destinationFile); } CompressFile(fileName, destinationFile); S3Uploader.upload(destinationFile + ".gz", String.Format("{0}/{1}.gz", customerCodeEnv, fileName), "ecpr"); }
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 static void runQueryToFile(SqlConnection connection, string runQuery, string fileName, string customerCodeEnv, int commandTimeout, string resultType) { string destinationFile = Path.Combine(Path.GetTempPath(), fileName); using (var command = new SqlCommand(runQuery, connection)) { command.CommandTimeout = commandTimeout; if (resultType == "csv") { TableDumper.readerToFile(command, destinationFile); } else { TableDumper.readerToFile_JSON(command, destinationFile); } } CompressFile(fileName, destinationFile); S3Uploader.upload(destinationFile + ".gz", String.Format("{0}/{1}.gz", customerCodeEnv, fileName), "ecpr"); }
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); } } }); }