public void DataReaderGetSchemaTable()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "SELECT * FROM BIGTABLE";
using (IDataReader dr = cmd.ExecuteReader())
{
DataTable dt = dr.GetSchemaTable();
Assert.IsTrue(dt.Columns.Count > 0);
DataColumn dcColName = dt.Columns["ColumnName"];
Assert.IsNotNull(dcColName, "ColumnName");
DataColumn dcColSize = dt.Columns["ColumnSize"];
Assert.IsNotNull(dcColSize, "ColumnSize");
Assert.AreEqual(dcColSize.DataType, typeof(int));
DataColumn dcColOrdinal = dt.Columns["ColumnOrdinal"];
Assert.IsNotNull(dcColOrdinal, "ColumnOrdinal");
Assert.AreEqual(dcColOrdinal.DataType, typeof(int));
DataColumn dcNullable = dt.Columns["AllowDBNull"];
Assert.IsNotNull(dcNullable, "AllowDBNull");
Assert.AreEqual(dcNullable.DataType, typeof(bool));
}
}
}
}
public void TransactionReuseCommitTest()
{
int toInsert = 10;
using (IDbConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
ReCreateTestTableIfNotExists(c);
using (IDbTransaction tx = c.BeginTransaction())
{
for (int i = 0; i < toInsert; i++)
{
using (IDbCommand cmd = c.CreateCommand())
{
cmd.Transaction = tx;
cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'NINTX1', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)");
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
Assert.AreEqual(toInsert, QueryAllRows(c));
}
}
public void DataReaderItemStringIndexer()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "SELECT * FROM BIGTABLE";
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
string name = dr.GetName(i);
object o = dr[name];
object o2 = dr.GetValue(i);
Assert.AreEqual(o2, o, "Mismatch on field {0}", name);
}
}
}
}
}
}
public void CommandExecuteNonQueryElapsedGreaterThanZero()
{
using (IDbConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
ReCreateTestTableIfNotExists(c);
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'NINTX1', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)");
cmd.ExecuteNonQuery();
// Confirm PhoenixCommand.Elapsed is working a bit.
PhoenixCommand phCmd = cmd as PhoenixCommand;
Assert.IsNotNull(phCmd);
Assert.IsNotNull(phCmd.Elapsed);
Assert.AreNotEqual(0, phCmd.Elapsed.TotalMilliseconds, nameof(phCmd.Elapsed.TotalMilliseconds));
this.TestContext.WriteLine("PhoenixCommand.Elapsed: {0}", phCmd.Elapsed);
}
Assert.AreEqual(1, QueryAllRows(c));
}
}
public void CommandCreateDisposeTest()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
// Do nothing, just displose
}
}
}
public void DataReaderRecordsAffectedOne()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "UPSERT INTO BIGTABLE (ID, MYTIMESTAMP) VALUES (502, NOW())";
using (IDataReader dr = cmd.ExecuteReader())
{
Assert.AreEqual(1, dr.RecordsAffected);
}
}
}
}
public void DataReaderHasRowsFalse()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "UPSERT INTO BIGTABLE (ID, MYTIMESTAMP) VALUES (502, NOW())";
using (PhoenixDataReader dr = cmd.ExecuteReader() as PhoenixDataReader)
{
Assert.AreEqual(false, dr.HasRows);
}
}
}
}
public DataTable GetColumns(PhoenixConnection c)
{
if (null == c)
{
throw new ArgumentNullException(nameof(c));
}
DataTable columns = null;
StringBuilder sbSql = new StringBuilder(SqlColumnMetaData);
if (c.State != ConnectionState.Open)
{
c.Open();
}
using (IDbCommand cmd = c.CreateCommand())
{
// Parameters for table name, and schema if not null.
cmd.Parameters.Add(new PhoenixParameter(this.Name));
if (DBNull.Value == Row["TABLE_SCHEM"])
{
sbSql.Append(SqlTableSchemaNullCriteria);
}
else
{
sbSql.Append(SqlTableSchemaCriteria);
cmd.Parameters.Add(new PhoenixParameter(Row["TABLE_SCHEM"]));
}
cmd.CommandText = sbSql.ToString();
cmd.Prepare();
using (IDataReader dr = cmd.ExecuteReader())
{
columns = new DataTable(string.Format("{0} Columns", this.Name));
columns.BeginLoadData();
columns.Load(dr);
columns.EndLoadData();
}
}
return(columns);
}
private void PreparedCmdParameterTest(int rowsToInsert, string sql, List <Func <object> > pFunc, bool assertTotalRows = true)
{
using (IDbConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
ReCreateTestTableIfNotExists(c);
using (IDbTransaction tx = c.BeginTransaction())
{
using (IDbCommand cmd = c.CreateCommand())
{
cmd.Transaction = tx;
cmd.CommandText = sql;
cmd.Prepare();
for (int i = 0; i < rowsToInsert; i++)
{
// Parameters loop
foreach (var pf in pFunc)
{
// Create a parameter used in the query
var p = cmd.CreateParameter();
p.Value = pf();
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
tx.Commit();
}
if (assertTotalRows)
{
Assert.AreEqual(rowsToInsert, QueryAllRows(c));
}
}
}
/// <summary>
/// Gets a DataTable containing the columns of the table which comprise the key columns of this index.
/// This requies an additional trip to the Phoenix Query Server using the specified connection.
/// </summary>
/// <returns>The DataTable containing the index meta data.</returns>
public DataTable GetKeyColumns(PhoenixConnection c)
{
if (null == c)
{
throw new ArgumentNullException(nameof(c));
}
DataTable dt = null;
if (c.State != ConnectionState.Open)
{
c.Open();
}
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = SqlKeyColumnMetaData;
cmd.Parameters.Add(new PhoenixParameter(this.Name));
//if (DBNull.Value == Row["TABLE_SCHEM"])
//{
// cmd.CommandText += SqlTableSchemaNullCriteria;
//}
//else
//{
// cmd.CommandText += SqlTableSchemaCriteria;
// cmd.Parameters.Add(new PhoenixParameter(Row["TABLE_SCHEM"]));
//}
cmd.Prepare();
using (IDataReader dr = cmd.ExecuteReader())
{
dt = new DataTable(string.Format("{0} Key Columns", this.Name));
dt.BeginLoadData();
dt.Load(dr);
dt.EndLoadData();
}
}
return(dt);
}
public void DataTableLoadFromPhoenixDataReader()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "SELECT * FROM BIGTABLE";
using (IDataReader dr = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(dr);
Assert.IsTrue(dt.Rows.Count > 0);
Assert.IsTrue(dt.Columns.Count > 0);
}
}
}
}
public void DataReaderGetFieldType()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "SELECT * FROM BIGTABLE";
using (IDataReader dr = cmd.ExecuteReader())
{
dr.Read();
for (int i = 0; i < dr.FieldCount; i++)
{
Type t = dr.GetFieldType(i);
Assert.IsNotNull(t, "GetFieldType returned null!");
}
}
}
}
}
public void CommandPrepareTest()
{
int toInsert = 10;
using (IDbConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
ReCreateTestTableIfNotExists(c);
using (IDbTransaction tx = c.BeginTransaction())
{
using (IDbCommand cmd = c.CreateCommand())
{
cmd.Transaction = tx;
cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, :1, 12, 14, 87, 45, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)");
cmd.Prepare();
for (int i = 0; i < toInsert; i++)
{
// Create a parameter used in the query
var p1 = cmd.CreateParameter();
p1.Value = string.Format("N{0}", DateTime.Now.ToString("hmmss"));
cmd.Parameters.Add(p1);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
tx.Commit();
}
Assert.AreEqual(toInsert, QueryAllRows(c));
}
}
public void DataReaderGetValues()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
ReCreateTestTableIfNotExists(c, "DataReaderGetValuesTest",
"CREATE TABLE IF NOT EXISTS DataReaderGetValuesTest (ID BIGINT PRIMARY KEY, FirstCol varchar(16), SecondCol varchar(64))",
true, true);
List <Func <object> > pFuncs = new List <Func <object> >();
pFuncs.Add(() => "First");
pFuncs.Add(() => "Second");
PreparedCmdParameterTest(2,
"UPSERT INTO DataReaderGetValuesTest (ID, FirstCol, SecondCol) VALUES (NEXT VALUE FOR garuda.DataReaderGetValuesTestSequence, :1, :2)",
pFuncs, false);
using (IDbCommand cmd = c.CreateCommand())
{
cmd.CommandText = "SELECT * FROM DataReaderGetValuesTest";
using (IDataReader dr = cmd.ExecuteReader())
{
object[] values = new object[dr.FieldCount];
while (dr.Read())
{
dr.GetValues(values);
Assert.AreEqual("First", values[1]);
Assert.AreEqual("Second", values[2]);
}
}
}
}
}
static void Main(string[] args)
{
GarudaUtilCmdLineArgs cmdLine = new GarudaUtilCmdLineArgs(args);
try
{
if (args.Length == 0)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new MainForm());
}
else
{
// Command Line mode
using (IDbConnection phConn = new PhoenixConnection())
{
phConn.ConnectionString = cmdLine.ConnectionString;
phConn.Open();
//(phConn as PhoenixConnection).SystemTables();
using (IDbCommand cmd = phConn.CreateCommand())
{
cmd.CommandText = "DROP TABLE IF EXISTS GARUDATEST";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS GARUDATEST (ID BIGINT PRIMARY KEY, AircraftIcaoNumber varchar(16), MyInt INTEGER, MyUint UNSIGNED_INT, MyUlong UNSIGNED_LONG, MyTingInt TINYINT, MyTime TIME, MyDate DATE, MyTimestamp TIMESTAMP, MyUnsignedTime UNSIGNED_TIME, MyFloat FLOAT, MyBinary BINARY(16), MyArray INTEGER[2] )";
cmd.ExecuteNonQuery();
bool bCreateSequence = true;
cmd.CommandText = "SELECT sequence_schema, sequence_name, start_with, increment_by, cache_size FROM SYSTEM.\"SEQUENCE\""; // WHERE sequence_schema = 'garuda' AND sequence_name='testsequence'
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetString(1).Equals("testsequence", StringComparison.InvariantCultureIgnoreCase))
{
bCreateSequence = false;
break;
}
}
}
if (bCreateSequence)
{
cmd.CommandText = "CREATE SEQUENCE garuda.testsequence";
cmd.ExecuteNonQuery();
}
// Insert a bunch of data...
using (IDbTransaction tx = phConn.BeginTransaction())
{
cmd.Transaction = tx;
cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'NINTX1', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)");
cmd.ExecuteNonQuery();
tx.Rollback();
}
// Insert a bunch of data...
int recordsToInsert = 10;
for (int i = 0; i < recordsToInsert; i++)
{
cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'N{0}', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)", DateTime.Now.ToString("hmmss"));
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT * FROM GARUDATEST";
using (IDataReader reader = cmd.ExecuteReader())
{
int iRecords = 0;
while (reader.Read())
{
iRecords++;
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(string.Format("{0}: {1} ({2})", reader.GetName(i), reader.GetValue(i), reader.GetDataTypeName(i)));
}
}
if (iRecords != recordsToInsert)
{
MessageBox.Show(string.Format("Expected {0}, got {1} records.", recordsToInsert, iRecords), "Warning");
}
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex);
if (cmdLine.ShowException)
{
MessageBox.Show(ex.ToString(), ex.GetType().ToString());
}
}
}
private async void _tsmiTableScriptCreate_Click(object sender, EventArgs e)
{
try
{
// Get location of context menu. This cooresponds to the point underwhich
// is the node we care about.
GarudaPhoenixTable table = GetTableFromTreeHitTest();
if (null != table)
{
DataTable columns = await table.GetColumnsAsync(_connection, true);
using (IDbCommand cmd = _connection.CreateCommand())
{
cmd.CommandText = string.Format("SELECT * FROM {0} LIMIT 0", table.FullName);
using (IDataReader dr = cmd.ExecuteReader())
{
DataTable schemaTable = dr.GetSchemaTable();
StringBuilder sbCreate = new StringBuilder();
sbCreate.AppendFormat("CREATE TABLE {0} (", table.FullName);
sbCreate.AppendLine();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow col = schemaTable.Rows[i];
string dataType = dr.GetDataTypeName(i);
string colName = col["ColumnName"].ToString();
bool isPK = table.IsColumnPrimaryKey(columns, colName);
if (i > 0)
{
sbCreate.AppendLine(",");
}
// Column name and data type, with size for varchars
sbCreate.AppendFormat("\t{0} {1}", colName, dataType);
if ("VARCHAR" == dataType)
{
sbCreate.AppendFormat("({0})", col["ColumnSize"]);
}
// Nullable?
if (!Convert.ToBoolean(col["AllowDBNull"]))
{
sbCreate.AppendFormat(" NOT");
}
sbCreate.AppendFormat(" NULL");
// Primary key?
if (isPK)
{
sbCreate.Append(" PRIMARY KEY");
}
}
sbCreate.AppendLine();
sbCreate.AppendFormat(")");
// Open a new query view tab and set the text.
QueryView qv = NewQueryViewTab(null, null);
qv.Text = sbCreate.ToString();
}
}
}
}
catch (Exception ex)
{
HandleException(ex);
}
}
