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 RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated)
{
var cmds = new List <InsertDelete>();
cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID));
if (archiveTable != null)
{
cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID));
}
var connStr = buildConnString();
var rowCounts = new RowCounts(0, 0);
using (var conn = new VerticaConnection(connStr)) {
conn.Open();
VerticaTransaction trans = conn.BeginTransaction();
foreach (var id in cmds)
{
id.delete.Connection = conn;
id.delete.Transaction = trans;
id.delete.CommandTimeout = Config.QueryTimeout;
logger.Log(id.delete.CommandText, LogLevel.Trace);
int deleted = id.delete.ExecuteNonQuery();
logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info);
id.insert.Connection = conn;
id.insert.Transaction = trans;
id.insert.CommandTimeout = Config.QueryTimeout;
logger.Log(id.insert.CommandText, LogLevel.Trace);
int inserted = id.insert.ExecuteNonQuery();
logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info);
rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted);
}
trans.Commit();
}
return(rowCounts);
}
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();
}
}
}
public void FromSameConnectionSequentially()
{
IEnumerable <Tuple <DataTable, Func <string, string> > > tuples = new List <Tuple <DataTable, Func <string, string> > >
{
new Tuple <DataTable, Func <string, string> >(new DataTable(), schemaName => String.Format("select * from {0}.{1};", schemaName, TestConfig.Table1)),
new Tuple <DataTable, Func <string, string> >(new DataTable(), schemaName => String.Format("select * from {0}.{1};", schemaName, TestConfig.Table2)),
};
using (var conn = new VerticaConnection(TestConfig.ConnectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
foreach (var tuple in tuples)
{
using (var command = conn.CreateCommand())
{
command.CommandText = tuple.Item2(TestConfig.SchemaName);
var verticaDataReader = command.ExecuteReader();
tuple.Item1.Load(verticaDataReader);
}
}
tx.Commit();
}
}
Assert.True(tuples.All(t => t.Item1.Rows.Count > 0), "Data should be loaded from all rows");
}
public VerticaClient(string host, string database, string user, string password)
{
VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder {
Host = host,
Database = database,
User = user,
Password = password
};
connection = new VerticaConnection(builder.ToString());
IsConnectionOpened();
}
public void SetDomainName(string domain)
{
DomainName = domain;
VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
builder.Database = "mydb";
builder.User = "******";
builder.Password = "******";
builder.Host = "10.58.44.163";
builder.Pooling = true;
client = new VerticaConnection(builder.ToString());
client.Open();
}
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;
}
public VerticaClient(string host, string database, string user, string password, BackgroundWorker bw = null)
{
VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder {
Host = host,
Database = database,
User = user,
Password = password
};
this.bw = bw;
connection = new VerticaConnection(builder.ToString());
IsConnectionOpened();
}
// NOTE: this may need work when Vertica is used as master agent
// currently this is not used by any workflow
public List <TColumn> GetFieldList(string dbName, string table, string schema)
{
var cols = new List <TColumn>();
using (var con = new VerticaConnection(buildConnString())) {
con.Open();
var t = con.GetSchema("Columns", new string[] { dbName, schema, table, null });
foreach (DataRow row in t.Rows)
{
cols.Add(new TColumn(row.Field <string>("COLUMN_NAME"), false, null, true));
}
}
return(cols);
}
public static VerticaConnection ConnectDb(DbCredentials pCredentials)
{
var vcsb = new VerticaConnectionStringBuilder
{
Database = pCredentials.DataBase,
User = pCredentials.User,
Password = pCredentials.Password,
Host = pCredentials.ServerIp
};
var vConnection = new VerticaConnection(vcsb.ConnectionString);
vConnection.Open();
return vConnection;
}
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 IList <long> BulkInsert(string fullFilePath, string tableName)
{
IList <long> rejectedRowNumbers = null;
using (var verticaConnection = new VerticaConnection(_connectionString))
{
try
{
verticaConnection.Open();
using (var verticaTransaction = verticaConnection.BeginTransaction())
{
try
{
FileStream inputFileStream = File.OpenRead(fullFilePath);
string copy = @"COPY extr_manual." + tableName + @" FROM STDIN RECORD TERMINATOR E'\r\n' DELIMITER E'|' ENFORCELENGTH NO COMMIT";
VerticaCopyStream stream = new VerticaCopyStream(verticaConnection, copy);
stream.Start();
stream.AddStream(inputFileStream, false);
stream.Execute();
long insertedRowsCount = stream.Finish();
rejectedRowNumbers = stream.Rejects;
if (rejectedRowNumbers.Count == 0)
{
verticaTransaction.Commit();
}
else
{
verticaTransaction.Rollback();
}
}
catch (Exception ex)
{
verticaTransaction.Rollback();
// TASK : BulkInsert Exception details may be logged for further analysis
throw;
}
}
}
finally
{
verticaConnection.Close();
}
}
return(rejectedRowNumbers);
}
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();
}
}
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();
}
}
/// <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);
}
public IEnumerable <TTable> GetTables(string dbName)
{
var tables = new List <TTable>();
using (var con = new VerticaConnection(buildConnString())) {
con.Open();
var t = con.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
foreach (DataRow row in t.Rows)
{
string tableName = row.Field <string>("TABLE_NAME");
// NOTE: interestingly, "TABLE_SCHEM" shall be used instead of "TABLE_SCHEMA"
// in case you think this is a typo
string schema = row.Field <string>("TABLE_SCHEM");
tables.Add(new TTable(tableName, schema));
}
}
return(tables);
}
/// <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]);
}
}
public void ResultLargerThanBufferWhenReadShouldFail()
{
var result = new DataTable();
using (var conn = new VerticaConnection(TestConfig.ConnectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
using (var command = conn.CreateCommand())
{
command.CommandText = String.Format("Select * from {0}.{1} limit 2000;",
TestConfig.SchemaName, TestConfig.LargeTable);
var verticaDataReader = command.ExecuteReader();
result.Load(verticaDataReader);
tx.Commit();
}
}
Assert.That(result.Rows.Count, Is.EqualTo(2000));
}
public static DataTable Get(int id)
{
var dataTable = new DataTable();
try
{
Console.WriteLine("[ID:{0}] using connection string :{1}", id, TestConfig.ConnectionString);
using (var conn = new VerticaConnection(TestConfig.ConnectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
using (var command = conn.CreateCommand())
{
Console.WriteLine("[ID:{0}] thread id is :{1}", id,
Thread.CurrentThread.ManagedThreadId);
command.Transaction = tx;
command.CommandText = String.Format("Select * from dual;");
var reader = command.ExecuteReader();
Console.WriteLine("[ID:{0}] Start reading ", id);
dataTable.Load(reader);
Console.WriteLine("[ID:{0}] Finished reading", id);
if (id % 2 == 0)
{
Console.WriteLine("[ID:{0}] going to sleep", id);
Thread.Sleep(1000 * 10);
Console.WriteLine("[ID:{0}] Waking Up", id);
}
Console.WriteLine("[ID:{0}] Finished executing", id);
tx.Commit();
}
}
return(dataTable);
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
throw;
}
}
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);
}
