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