public bool ExecuteUpdateQuery(string query, Dictionary <string, object> parameters)
{
bool updated = false;
if (!IsConnectionOpened())
{
return(updated);
}
try {
VerticaCommand update = new VerticaCommand(query, connection);
if (parameters.Count > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
update.Parameters.Add(new VerticaParameter(parameter.Key, parameter.Value));
}
}
updated = update.ExecuteNonQuery() > 0 ? true : false;
} catch (Exception e) {
string subject = "Ошибка выполнения запроса к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
SystemMail.SendMail(subject, body, Properties.Settings.Default.MailCopy);
Logging.ToLog(subject + " " + body);
connection.Close();
}
return(updated);
}
/// <summary>
/// Build the apply command
/// </summary>
/// <param name="table">Table to apply changes to</param>
/// <param name="schema">Vertica schema to apply changes to</param>
/// <param name="CTDBName">CT database name, which is actually Vertica CT schema name</param>
/// <param name="CTID">Change tracking ID</param>
/// <returns>InsertDelete object representing the apply command</returns>
private InsertDelete BuildApplyCommand(TableConf table, string schema, string CTDBName, long CTID)
{
// NOTE: Vertica does not like the first alias P in the following command:
// DELETE FROM a.b P WHERE EXISTS (SELECT 1 FROM c.d CT WHERE P.id = CT.id)
// instead, the first alias has to be removed, thus:
// DELETE FROM a.b WHERE EXISTS (SELECT 1 FROM c.d CT WHERE a.b.id = CT.id)
// and in the case of multi-column primary key:
// DELETE FROM a.b WHERE EXISTS (SELECT 1 FROM c.d CT WHERE a.b.id1 = CT.id1 AND a.b.id2 = CT.id2)
string verticaTableName = string.Format("{0}.{1}", schema, table.Name);
string delete = string.Format(
@"DELETE FROM {0} WHERE EXISTS (SELECT 1 FROM {1}.{2} CT WHERE {3});",
verticaTableName,
CTDBName,
table.ToCTName(CTID),
table.getNoAliasPkList(verticaTableName));
// since Vertica does not have the reserved words issue
// we are using table.SimpleColumnList
string insert = string.Format(
@"INSERT INTO {0} ({1})
SELECT {1} FROM {2}.{3} CT
WHERE NOT EXISTS (SELECT 1 FROM {0} P WHERE {4}) AND CT.sys_change_operation IN ( 'I', 'U' );",
verticaTableName,
table.SimpleColumnList,
CTDBName,
table.ToCTName(CTID),
table.PkList);
var deleteCmd = new VerticaCommand(delete);
var insertCmd = new VerticaCommand(insert);
return(new InsertDelete(insertCmd, deleteCmd));
}
public void ReadStaticC(int v, TimeSpan ts)
{
VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
builder.Database = "mydb";
builder.User = "******";
builder.Password = "******";
builder.Host = "10.58.44.163";
VerticaConnection con = new VerticaConnection(builder.ToString());
con.Open();
StringBuilder c = new StringBuilder();
string content = PrepareReadMultiple(v, ts, c);
VerticaCommand com = new VerticaCommand(content);
com.Connection = con;
while (ReadTest.Running)
{
VerticaDataReader r = com.ExecuteReader();
if (!r.HasRows)
{
System.Console.WriteLine("Error: Vertica ReadMultiple received empty");
}
r.Close();
System.Threading.Thread.Sleep(100);
}
}
public void TruncateWmsPerformanceTable(string tableName)
{
var verticaQuery = "DELETE FROM extr_manual." + tableName;
using (var verticaConnection = new VerticaConnection(_connectionString))
{
try
{
verticaConnection.Open();
using (var verticaTransaction = verticaConnection.BeginTransaction())
{
try
{
using (var verticaCommand = new VerticaCommand(verticaQuery, verticaConnection))
{
verticaCommand.ExecuteNonQuery();
verticaTransaction.Commit();
}
}
catch (Exception)
{
verticaTransaction.Rollback();
// TASK : TruncateWmsPerformanceTable Exception details may be logged for further analysis
throw;
}
}
}
finally
{
verticaConnection.Close();
}
}
}
/// <summary>
/// Check if the datatype of the column [column] in [dbName].[table] in Vertica DB
/// matches the given data type
/// We treat dbName as the schema in Vertica
/// </summary>
/// <param name="dbName">Database name</param>
/// <param name="schema">Schema name</param>
/// <param name="table">Table name</param>
/// <param name="column">Column name</param>
/// <param name="dataType">Data type to match</param>
/// <returns>Boolean representing whether the column exists</returns>
private bool ColumnDatatypeMatches(string dbName, string schema, string table, string column, string dataType)
{
// NOTE: for our scenario (MSSQL relay, Vertica slave)
// MSSQL database name becomes Vertica schema name
// and MSSQL schema name is ignored
// NOTE: not sure why this same method is public in NetezzaDataUtils class
// where the method is not used anywhere outside the class. We are keeping
// this method private here, as is the case in MSSQLDataUtils and MySQLDataUtils
string sql = string.Format(
@"SELECT data_type FROM v_catalog.columns
WHERE UPPER(table_schema) = UPPER('{0}')
AND UPPER(table_name) = UPPER('{1}')
AND UPPER(column_name) = UPPER('{2}')",
dbName,
table,
column);
var cmd = new VerticaCommand(sql);
try {
string dataTypeString = SqlQueryToScalar <string>(cmd);
return(dataTypeString.Equals(dataType, StringComparison.OrdinalIgnoreCase));
} catch (Exception e) {
logger.Log("Exception while matching data type for column [" + dbName + "].[" + table + "].[" + column + "]. " + e.Message, LogLevel.Debug);
return(false);
}
}
private void mCheckInsert()
{
if (checkcom != null)
{
return;
}
checkcom = new VerticaCommand("select value from mydata where variable_id = 'testinstance_" + max_var + "' AND timestamp = '" + ts_max.ToString("yyyy-MM-dd hh:mm:ss.ffffff", dtfi) + "'");
checkcom.Connection = client;
}
/// <summary>
/// Parse a SQL query, substituting parameters for their values.
/// </summary>
/// <param name="cmd">VerticaCommand to parse</param>
/// <returns>The parsed query</returns>
private string ParseCommand(VerticaCommand cmd)
{
string query = cmd.CommandText;
foreach (VerticaParameter p in cmd.Parameters)
{
query = query.Replace(p.ParameterName, "'" + p.Value.ToString() + "'");
}
return(query);
}
public bool DropTableIfExists(string dbName, string table, string schema)
{
// here we are still calling the public method defined by the interface
if (CheckTableExists(dbName, table, schema))
{
// for Vertica, the source database name becomes the schema name
string drop = string.Format("DROP TABLE {0}.{1};", dbName, table);
var cmd = new VerticaCommand(drop);
return(SqlNonQuery(cmd) > 0);
}
return(false);
}
/// <summary>
/// Writes data from the given data file to the destination Vertica database
/// </summary>
/// <param name="fileName">Name of data file to copy from</param>
/// <param name="dbName">Database name</param>
/// <param name="table">Table name to write to</param>
/// <param name="timeout">Timeout</param>
public void BulkCopy(string fileName, string dbName, string table, int timeout)
{
// reference: http://goo.gl/8R6UXJ
string copyStatement = string.Format(
"COPY {0}.{1} FROM '{2}' DELIMITER '|' NULL '' ENCLOSED BY '' RECORD TERMINATOR E'\r\n' ABORT ON ERROR DIRECT STREAM NAME 'Tesla' NO COMMIT",
dbName,
table,
fileName);
VerticaCommand cmd = new VerticaCommand(copyStatement);
SqlNonQueryWithTransaction(cmd);
}
public static IEnumerable<SampleTable> SelectSampleTable(VerticaConnection pConnection)
{
var vResult = new List<SampleTable>();
using (var dt = new DataTable())
using (var comm = new VerticaCommand {
CommandText = Queries.SelectSampleTable,
Connection = pConnection})
{
dt.Load(comm.ExecuteReader());
vResult.AddRange(Enumerable.Select(dt.AsEnumerable(), dataRow => new SampleTable(dataRow)));
}
return vResult;
}
/// <summary>
/// Runs a sql query and returns first column and row from results as specified type
/// </summary>
/// <param name="cmd">VerticaCommand to run</param>
/// <param name="timeout">Query timeout</param>
/// <returns>The value in the first column and row, as the specified type</returns>
private T SqlQueryToScalar <T>(VerticaCommand cmd, int?timeout = null)
{
DataTable result = SqlQuery(cmd, timeout);
// return result in first column and first row as specified type
T toRet;
try {
toRet = (T)result.Rows[0][0];
} catch (InvalidCastException) {
throw new Exception("Unable to cast value " + result.Rows[0][0].ToString() + " to type " + typeof(T) +
" when running query: " + ParseCommand(cmd));
}
return(toRet);
}
public ActionResult <IEnumerable <string> > Get()
{
using (var db = new VerticaConnection("Host=localhost:37878;Database=docker;User=dbadmin;Password=1234;")) {
db.Open();
using (var comm = new VerticaCommand {
CommandText = "Select * from TestTable;",
Connection = db
}) {
var dt = new DataTable();
dt.Load(comm.ExecuteReader());
}
db.Close();
}
return(new string[] { "value1", "value2" });
}
public void PrepareReadMultipleLatest(int v, TimeSpan ts)
{
StringBuilder con = new StringBuilder();
con.Append("select * from mydata where variable_id in (");
for (int i = 1; i <= v; i++)
{
con.Append("'testinstance_" + i + "'"); if (i != v)
{
con.Append(",");
}
}
con.Append(") and timestamp >= now() - interval '" + ts.TotalSeconds + "'");
readmulcom = new VerticaCommand(con.ToString());
readmulcom.Connection = client;
}
public static int UpdateSampleTable(SampleTable pData, VerticaConnection pConnection)
{
using (var comm = new VerticaCommand())
{
comm.Connection = pConnection;
comm.CommandText = Queries.UpdateSampleTable;
comm.Parameters.AddRange(new[]
{
new VerticaParameter("@pID", VerticaType.Numeric, pData.Id),
new VerticaParameter("@pText", VerticaType.VarChar, pData.Text)
}
);
return comm.ExecuteNonQuery();
}
}
protected bool CheckTableExists(string schema, string table)
{
VerticaCommand cmd = new VerticaCommand();
// NOTE: Vertica is data case-sensitive, and command case-insensitive
// which means:
// for data, "SELECT ... WHERE col = 'A'" is not the same as "SELECT ... WHERE col = 'a'",
// for command, "CREATE TABLE t ..." will not work if a table [T] already exists
cmd.CommandText = "SELECT table_schema, table_name FROM v_catalog.tables " +
"WHERE UPPER(table_schema) = UPPER(@TABLE_SCHEMA) " +
"AND UPPER(table_name) = UPPER(@TABLE_NAME)";
cmd.Parameters.Add(new VerticaParameter("TABLE_SCHEMA", VerticaType.VarChar, schema));
cmd.Parameters.Add(new VerticaParameter("TABLE_NAME", VerticaType.VarChar, table));
var res = SqlQuery(cmd);
return(res.Rows.Count > 0);
}
public static int DeleteSampleTableById(SampleTable pData, VerticaConnection pConnection)
{
using (var comm = new VerticaCommand())
{
comm.Connection = pConnection;
comm.CommandText = Queries.DeleteSampleTableById;
comm.Parameters.AddRange(new[]
{
new VerticaParameter("@pID", VerticaType.Numeric, pData.Id)
}
);
return comm.ExecuteNonQuery();
}
}
/// <summary>
/// Runs a query that does not return results (i.e. a write operation)
/// </summary>
/// <param name="cmd">VerticaCommand to run</param>
/// <param name="timeout">Timeout (higher than selects since some writes can be large)</param>
/// <returns>The number of rows affected</returns>
internal int SqlNonQuery(VerticaCommand cmd, int?timeout = null)
{
int commandTimeout = timeout ?? Config.QueryTimeout;
//build connection string based on server/db info passed in
string connStr = buildConnString();
int numrows;
//using block to avoid resource leaks
using (VerticaConnection conn = new VerticaConnection(connStr)) {
//open database connection
conn.Open();
cmd.Connection = conn;
cmd.CommandTimeout = commandTimeout;
LogCommand(cmd);
numrows = cmd.ExecuteNonQuery();
}
return(numrows);
}
// TASK : exception handling for method GetLastUpdateDateOfTable
private DateTime GetLastUpdateDateOfTable(string tableName, VerticaConnection verticaConnection)
{
DateTime result = DateTime.MinValue;
string verticaTableLastUpdateQuery =
string.Format("SELECT MAX(insert_date) AS LastUpdateDate FROM extr_manual.{0}", tableName);
using (var verticaTableLastUpdateCommand = new VerticaCommand(verticaTableLastUpdateQuery, verticaConnection))
{
var verticaTableLastUpdateReader = verticaTableLastUpdateCommand.ExecuteReader();
while (verticaTableLastUpdateReader.Read())
{
DateTime.TryParse(verticaTableLastUpdateReader[0].ToString(), out result);
}
}
return(result);
}
// The refresh command configured will execute if Config.RefreshViews has a configuration for the dbName tableName pair
private void RefreshViews(string dbName, string tableName)
{
var refresh = Config.RefreshViews.Where(r => r.Db.ToLower() == dbName.ToLower() && r.TableName.ToLower() == tableName.ToLower()).FirstOrDefault();
if (refresh == null)
{
logger.Log("No refresh view config is available for [" + dbName + "].[" + tableName + "]. Abort refreshing views.", LogLevel.Debug);
return;
}
string sql = refresh.Command;
var cmd = new VerticaCommand(sql);
try {
SqlNonQuery(cmd);
} catch (Exception) {
throw new Exception("Please check any pending schema changes to be applied on Vertica before refreshing the view::" + dbName + ".." + refresh.ViewName);
}
}
public void RenameColumn(TableConf t, string dbName, string columnName, string newColumnName, string historyDB)
{
// rename the column if it exists
if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName))
{
string sql = string.Format(
@"ALTER TABLE {0}.{1} RENAME COLUMN {2} TO {3};",
dbName,
t.Name,
columnName,
newColumnName);
var cmd = new VerticaCommand(sql);
SqlNonQuery(cmd);
if (ShallRefreshViews(t, dbName, newColumnName, action: "RenameColumn", columnShallExist: true))
{
RefreshViews(dbName, t.Name);
}
}
}
public void CopyTableDefinition(string sourceDB, string sourceTableName, string schema, string destDB, string destTableName, string originalTableName = null)
{
var cols = GetColumns(sourceDB, sourceTableName, schema, originalTableName ?? sourceTableName);
// get the table config object
var table = Config.TableByName(originalTableName);
List <TableConf> tableConfs = new List <TableConf>()
{
table
};
Dictionary <TableConf, IList <TColumn> > allColumnsByTable = sourceDataUtils.GetAllFields(sourceDB, tableConfs.ToDictionary(t => t, t => t.Name));
string pkList = string.Join(",", cols.Where(c => c.isPk).Select(c => c.name));
if (String.IsNullOrEmpty(pkList))
{
string err = "Primary Key information is required for Vertica, but absent";
logger.Log(err, LogLevel.Error);
throw new Exception("Copy table definition error: " + err);
}
string vCreate = string.Format(
@"CREATE TABLE {0}.{1}
(
{2} NOT NULL,
PRIMARY KEY ({3})
)
ORDER BY {3}
SEGMENTED BY HASH({3})
ALL NODES
KSAFE {4};",
destDB, // for Vertica, the "database" becomes the "schema"
destTableName,
string.Join(",", cols),
pkList,
Config.VerticaKsafe);
logger.Log(vCreate, LogLevel.Trace);
destDataUtils.DropTableIfExists(destDB, destTableName, schema);
var cmd = new VerticaCommand(vCreate);
destDataUtils.SqlNonQuery(cmd);
}
public void DropColumn(TableConf t, string dbName, string columnName, string historyDB)
{
// NOTE: Reserved word should not be a problem for Vertica
// in case we found it is some point in the future, enable mapping
// columnName = MapReservedWord(columnName);
if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName))
{
string sql = string.Format(
@"ALTER TABLE {0}.{1} DROP COLUMN {2} RESTRICT;",
dbName,
t.Name,
columnName);
var cmd = new VerticaCommand(sql);
SqlNonQuery(cmd);
if (ShallRefreshViews(t, dbName, columnName, action: "DropColumn", columnShallExist: false))
{
RefreshViews(dbName, t.Name);
}
}
}
public void PrepareWriteData(int v)
{
if (v > variables)
{
checkcom = null;
}
variables = v;
coms.Clear();
System.Random r = new Random();
StringBuilder con = new StringBuilder();
con.Append("insert /*+ AUTO */ into mydata\n\t");
VerticaCommand command;
for (int i = 1; i <= variables; i++)
{
if (i >= 1000 && (i % 1000 == 0))
{
command = client.CreateCommand();
command.CommandText = con.ToString();
coms.Add(command);
con.Clear();
con.Append("insert /*+ AUTO */ into mydata\n\t");
}
DateTime ts = DateTime.UtcNow;
int upVal = r.Next(1, 1000000);
if (i == max_var)
{
ts_max = ts;
}
con.Append(" select 'testinstance_" + i + "','OK', TIMESTAMP '" + ts.ToString("yyyy-MM-dd hh:mm:ss.ffffff", dtfi) + "', " + upVal.ToString());
if (i != variables && ((i + 1) % 1000 != 0))
{
con.Append(" UNION ");
}
}
command = client.CreateCommand();
command.CommandText = con.ToString();
command.Prepare();
coms.Add(command);
}
/// <summary>
/// Runs a sql query and returns results as requested type
/// </summary>
/// <param name="cmd">VerticaCommand to run</param>
/// <param name="timeout">Query timeout</param>
/// <returns>DataTable object representing the result</returns>
internal DataTable SqlQuery(VerticaCommand cmd, int?timeout = null)
{
int commandTimeout = timeout ?? Config.QueryTimeout;
string connStr = buildConnString();
using (VerticaConnection conn = new VerticaConnection(connStr)) {
conn.Open();
cmd.Connection = conn;
cmd.CommandTimeout = commandTimeout;
LogCommand(cmd);
DataSet ds = new DataSet();
VerticaDataAdapter da = new VerticaDataAdapter(cmd);
//this is where the query is run
da.Fill(ds);
//return the result, which is the first DataTable in the DataSet
return(ds.Tables[0]);
}
}
/// <summary>
/// Check if a column [column] exists in [dbName].[table] in Vertica DB
/// We treat dbName as the schema in Vertica
/// </summary>
/// <param name="dbName">Database name</param>
/// <param name="schema">Schema name</param>
/// <param name="table">Table name</param>
/// <param name="column">Column name</param>
/// <returns>Boolean representing whether the column exists</returns>
private bool CheckColumnExists(string dbName, string schema, string table, string column)
{
// NOTE: for our scenario (MSSQL relay, Vertica slave)
// MSSQL database name becomes Vertica schema name
// and MSSQL schema name is ignored
// NOTE: not sure why this same method is public in NetezzaDataUtils class
// where the method is not used anywhere outside the class. We are keeping
// this method private here, as is the case in MSSQLDataUtils and MySQLDataUtils
string sql = string.Format(
@"SELECT 1 FROM v_catalog.columns
WHERE UPPER(table_schema) = UPPER('{0}')
AND UPPER(table_name) = UPPER('{1}')
AND UPPER(column_name) = UPPER('{2}')",
dbName,
table,
column);
var cmd = new VerticaCommand(sql);
var res = SqlQuery(cmd);
return(res.Rows.Count > 0);
}
public void AddColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB)
{
// NOTE: Reserved word should not be a problem for Vertica
// in case we found it is some point in the future, enable mapping
// columnName = MapReservedWord(columnName);
if (!CheckColumnExists(dbName, t.SchemaName, t.Name, columnName))
{
string destDataType = MapColumnTypeName(Config.RelayType, dataType, t.getColumnModifier(columnName));
string sql = string.Format(
@"ALTER TABLE {0}.{1} ADD {2} {3};",
dbName,
t.Name,
columnName,
destDataType);
var cmd = new VerticaCommand(sql);
SqlNonQuery(cmd);
if (ShallRefreshViews(t, dbName, columnName, action: "AddColumn", columnShallExist: true))
{
RefreshViews(dbName, t.Name);
}
}
}
public DataTable GetDataTable(string query, Dictionary <string, object> parameters = null)
{
DataTable dataTable = new DataTable();
if (!IsConnectionOpened())
{
return(dataTable);
}
try {
using (VerticaCommand command = new VerticaCommand(query, connection)) {
if (parameters != null && parameters.Count > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
command.Parameters.Add(new VerticaParameter(parameter.Key, parameter.Value));
}
}
using (VerticaDataAdapter fbDataAdapter = new VerticaDataAdapter(command))
fbDataAdapter.Fill(dataTable);
}
} catch (Exception e) {
string subject = "Ошибка выполнения запроса к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
SystemMail.SendMail(subject, body, Properties.Settings.Default.MailCopy);
Logging.ToLog(subject + " " + body);
connection.Close();
if (bw != null)
{
bw.ReportProgress(0, subject + " " + body);
}
}
return(dataTable);
}
public void ModifyColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB)
{
// modify the column if it exists
if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName))
{
string destDataType = MapColumnTypeName(Config.RelayType, dataType, t.getColumnModifier(columnName));
if (!ColumnDatatypeMatches(dbName, t.SchemaName, t.Name, columnName, destDataType))
{
// do not modify if the destination column already has the right data type
string sql = string.Format(
@"ALTER TABLE {0}.{1} ALTER COLUMN {2} SET DATA TYPE {3};",
dbName,
t.Name,
columnName,
destDataType);
var cmd = new VerticaCommand(sql);
SqlNonQuery(cmd);
if (ShallRefreshViews(t, dbName, columnName, action: "ModifyColumn", columnShallExist: true, dataType: destDataType))
{
RefreshViews(dbName, t.Name);
}
}
}
}
public InsertDelete(VerticaCommand insert, VerticaCommand delete)
{
this.insert = insert;
this.delete = delete;
}
public IEnumerable <WmsPerformanceTableDto> GetWmsPerformanceTableInfos(
IEnumerable <string> ignoredColumnTypes = null)
{
// TASK : Remove dummy item requirement.
var queryResult = new[] { new { TableName = string.Empty, ColumnName = string.Empty, DataType = string.Empty, IsNullable = true, LastUpdateDate = DateTime.MinValue } }.ToList();
var queryLastDateResult = new[] { new { TableName = string.Empty, LastUpdateDate = DateTime.MinValue } }.ToList();
const string verticaQuery = "SELECT table_name, column_name, data_type, is_nullable FROM columns WHERE table_schema = 'extr_manual' ORDER BY ordinal_position";
using (var verticaConnection = new VerticaConnection(_connectionString))
{
try
{
// verticaConnection Open failures may be handled with catch block and may be logged for further analysis
// TASK : catch and handle verticaConnection Open failures
verticaConnection.Open();
using (var verticaCommand = new VerticaCommand(verticaQuery, verticaConnection))
{
var reader = verticaCommand.ExecuteReader();
while (reader.Read())
{
// verticaQuery modifications may break the code below.
var columnType = reader[2].ToString();
// These filtering below may be done with appending into verticaQuery
// TASK : filter column type with appending into verticaQuery. Ref : Efficiency
//Skip the ignored column types.
if (ignoredColumnTypes != null && ignoredColumnTypes.Contains(columnType))
{
continue;
}
// At first tableName finding, add its LastUpdateDate to queryLastDateResult.
// These operation is differs from the methods responsibility (GetWmsPerformanceTableInfos). LastUpdateDate calculation per table can be done in grouping section below. Also after closing current verticaConnection..
// TASK : Refactor LastUpdateDate calculation by taking these functionality into another class or change the execution place to grouping section. Ref : Single Responsibility
if (!queryLastDateResult.Any(x => x.TableName == reader[0].ToString()))
{
queryLastDateResult.Add(new
{
TableName = reader[0].ToString(),
LastUpdateDate = GetLastUpdateDateOfTable(reader[0].ToString(), verticaConnection)
});
}
queryResult.Add(new
{
TableName = reader[0].ToString(),
ColumnName = reader[1].ToString(),
DataType = columnType,
IsNullable = Convert.ToBoolean(reader[3]),
LastUpdateDate = queryLastDateResult.SingleOrDefault(x => x.TableName == reader[0].ToString()).LastUpdateDate
});
}
}
}
finally
{
verticaConnection.Close();
}
}
//Remove the initial dummy item.
queryResult.RemoveAt(0);
// TASK : Preparing the result type may be handled in the reader while loop, for efficiency
//Prepare the result by using anonymous query result type.
var tables = queryResult.GroupBy(e => e.TableName).Select(e => new WmsPerformanceTableDto
{
TableName = e.Key,
LastUpdateDate = e.FirstOrDefault(c => c.TableName == e.Key).LastUpdateDate,
Columns = e.Select(c => new WmsPerformanceColumnDto
{
ColumnName = c.ColumnName,
DataType = c.DataType,
IsNullable = c.IsNullable,
}).ToList()
}).ToList();
return(tables);
}
/// <summary>
/// Log a command
/// </summary>
/// <param name="cmd">VerticaCommand to log</param>
private void LogCommand(VerticaCommand cmd)
{
logger.Log("Executing query: " + ParseCommand(cmd), LogLevel.Debug);
}
public bool ExecuteUpdateQuery(
string query,
bool isTreatmentsDetails,
string fileInfo)
{
bool updatedCorrected = true;
if (!IsConnectionOpened())
{
return(updatedCorrected);
}
string now = DateTime.Now.ToString("yyyyMMddHHmmss");
using (VerticaTransaction transaction = connection.BeginTransaction()) {
using (VerticaCommand update = new VerticaCommand(query, connection)) {
if (isTreatmentsDetails)
{
DataTable dataTable = Program.FileContentTreatmentsDetails;
if (dataTable == null)
{
return(false);
}
for (int i = 0; i < dataTable.Rows.Count; i++)
{
try {
update.Parameters.Clear();
bool skip = false;
foreach (Program.Header header in Program.headers)
{
if (Debugger.IsAttached)
{
if (header.DbField == "ordtid" && string.IsNullOrEmpty(dataTable.Rows[i][header.DbField].ToString()))
{
skip = true;
break;
}
}
update.Parameters.Add(new VerticaParameter(header.DbField, dataTable.Rows[i][header.DbField]));
}
if (skip)
{
continue;
}
update.Parameters.Add(new VerticaParameter("@etl_pipeline_id", "CleanedTreatmentsDetailsImport" + "_" + now));
update.Parameters.Add(new VerticaParameter("@file_info", fileInfo));
update.Parameters.Add(new VerticaParameter("@loadingUserName", Environment.UserName + "@" + Environment.MachineName));
if (dataTable.Columns.Contains("average_discount"))
{
update.Parameters.Add(new VerticaParameter("@average_discount", dataTable.Rows[i]["average_discount"]));
}
else
{
update.Parameters.Add(new VerticaParameter("@average_discount", null));
}
if (dataTable.Columns.Contains("amount_total_with_average_discount"))
{
update.Parameters.Add(new VerticaParameter("@amount_total_with_average_discount", dataTable.Rows[i]["amount_total_with_average_discount"]));
}
else
{
update.Parameters.Add(new VerticaParameter("@amount_total_with_average_discount", null));
}
if (update.ExecuteNonQuery() == 0)
{
updatedCorrected = false;
}
} catch (Exception e) {
string subject = "Ошибка выполнения запроса к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
SystemMail.SendMail(subject, body, Properties.Settings.Default.MailCopy);
Logging.ToLog(subject + " " + body);
if (bw != null)
{
bw.ReportProgress(0, subject + " " + body);
}
Logging.ToLog("---Исходные данные:");
foreach (Program.Header header in Program.headers)
{
Logging.ToLog(header.DbField + " | " + (dataTable.Rows[i][header.DbField] == null ? "null" : dataTable.Rows[i][header.DbField].ToString()));
}
transaction.Rollback();
connection.Close();
return(false);
}
}
}
else
{
foreach (ItemProfitAndLoss item in Program.FileContentProfitAndLoss)
{
try {
update.Parameters.Clear();
update.Parameters.Add(new VerticaParameter("@object_name", item.ObjectName));
update.Parameters.Add(new VerticaParameter("@period_year", item.PeriodYear));
update.Parameters.Add(new VerticaParameter("@period_type", item.PeriodType));
update.Parameters.Add(new VerticaParameter("@group_name_level_1", item.GroupNameLevel1));
update.Parameters.Add(new VerticaParameter("@group_name_level_2", item.GroupNameLevel2));
update.Parameters.Add(new VerticaParameter("@group_name_level_3", item.GroupNameLevel3));
update.Parameters.Add(new VerticaParameter("@value", item.Value));
update.Parameters.Add(new VerticaParameter("@group_sorting_order", item.GroupSortingOrder));
update.Parameters.Add(new VerticaParameter("@object_sorting_order", item.ObjectSrotingOrder));
update.Parameters.Add(new VerticaParameter("@quarter", item.Quarter));
update.Parameters.Add(new VerticaParameter("@has_data", item.HasData));
update.Parameters.Add(new VerticaParameter("@etl_pipeline_id", "CleanedTreatmentsDetailsImport" + "_" + now));
update.Parameters.Add(new VerticaParameter("@file_info", fileInfo));
update.Parameters.Add(new VerticaParameter("@loadingUserName", Environment.UserName + "@" + Environment.MachineName));
if (update.ExecuteNonQuery() == 0)
{
updatedCorrected = false;
}
} catch (Exception e) {
string subject = "Ошибка выполнения запроса к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
if (e.InnerException != null)
{
body += Environment.NewLine + e.InnerException.Message + Environment.NewLine + e.InnerException.StackTrace;
}
SystemMail.SendMail(subject, body, Properties.Settings.Default.MailCopy);
Logging.ToLog(subject + " " + body);
if (bw != null)
{
bw.ReportProgress(0, subject + " " + body);
}
Logging.ToLog("---Исходные данные:");
Logging.ToLog(item.ToString());
transaction.Rollback();
connection.Close();
return(false);
}
}
}
}
transaction.Commit();
}
return(updatedCorrected);
}
public void CopyTableDefinition(string sourceDB, string sourceTableName, string schema, string destDB, string destTableName, string originalTableName = null) {
var cols = GetColumns(sourceDB, sourceTableName, schema, originalTableName ?? sourceTableName);
// get the table config object
var table = Config.TableByName(originalTableName);
List<TableConf> tableConfs = new List<TableConf>() { table };
Dictionary<TableConf, IList<TColumn>> allColumnsByTable = sourceDataUtils.GetAllFields(sourceDB, tableConfs.ToDictionary(t => t, t => t.Name));
string pkList = string.Join(",", cols.Where(c => c.isPk).Select(c => c.name));
if (String.IsNullOrEmpty(pkList)) {
string err = "Primary Key information is required for Vertica, but absent";
logger.Log(err, LogLevel.Error);
throw new Exception("Copy table definition error: " + err);
}
string vCreate = string.Format(
@"CREATE TABLE {0}.{1}
(
{2} NOT NULL,
PRIMARY KEY ({3})
)
ORDER BY {3}
SEGMENTED BY HASH({3})
ALL NODES
KSAFE {4};",
destDB, // for Vertica, the "database" becomes the "schema"
destTableName,
string.Join(",", cols),
pkList,
Config.VerticaKsafe);
logger.Log(vCreate, LogLevel.Trace);
destDataUtils.DropTableIfExists(destDB, destTableName, schema);
var cmd = new VerticaCommand(vCreate);
destDataUtils.SqlNonQuery(cmd);
}
public void PrepareReadOne(TimeSpan ts)
{
readonecom = new VerticaCommand("select * from mydata where variable_id = 'testinstance_1' and timestamp >= timestamp '" + Now + "' - interval '" + ts.TotalSeconds + "'");
readonecom.Connection = client;
}
