Beispiel #1
0
        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);
                }
            });
        }
Beispiel #2
0
        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");
        }
Beispiel #3
0
        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);
            });
        }
Beispiel #4
0
        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");
        }
Beispiel #5
0
        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);
                    }
                }
            });
        }