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 MetaDataGarudaPhoenixTableConstructor() { string expectedTable = "BIGTABLE"; using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); DataTable dt = c.GetTables(); foreach (DataRow row in dt.Rows) { var tbl = new Garuda.Data.MetaData.GarudaPhoenixTable(row); if (row["TABLE_NAME"].ToString() == expectedTable) { Assert.IsNotNull(tbl.Row); Assert.IsInstanceOfType(tbl.Row, typeof(DataRow)); Assert.IsNotNull(tbl.Name); Assert.AreEqual(expectedTable, tbl.Name); Assert.IsNotNull(tbl.FullName); Assert.AreEqual(expectedTable, tbl.FullName); Assert.IsNotNull(tbl.Schema); Assert.AreEqual(string.Empty, tbl.Schema); break; } } } }
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 MetaDataGarudaPhoenixTableGenerateUpsertStatement() { string expectedTable = "BIGTABLE"; using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); DataTable dt = c.GetTables(); foreach (DataRow row in dt.Rows) { if (row["TABLE_NAME"].ToString() == expectedTable) { var tbl = new Garuda.Data.MetaData.GarudaPhoenixTable(row); Task <string> tUpsert = tbl.GenerateUpsertStatementAsync(c); tUpsert.Wait(); Assert.IsNotNull(tUpsert.Result); Assert.IsTrue(tUpsert.Result.StartsWith("UPSERT INTO")); TestContext.WriteLine(tUpsert.Result); break; } } } }
public void BulkCopyTest2_Salted3Table() { Stopwatch sw = new Stopwatch(); using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); string tableName = CreateBulkCopyTableIfNotExists(c, false, 3); string nextValueForSequence = string.Format("NEXT VALUE FOR garuda.{0}", SequenceNameForTable(tableName)); PhoenixBulkCopy bc = new PhoenixBulkCopy(c); DataTable dt = ConvertCSVtoDataTable(System.Configuration.ConfigurationManager.AppSettings["BulkCopyCsvTestFile"]); // Query the table and measure performance sw.Start(); bc.DestinationTableName = tableName; bc.ColumnMappings.Add("ID", new PhoenixBulkCopyColumnMapping(nextValueForSequence)); bc.BatchSize = 100; bc.WriteToServer(dt); sw.Stop(); WriteBulkCopyPerf(dt.Rows.Count, sw.ElapsedMilliseconds, "BulkCopySalted3PerfFile"); // How many rows did we get back? this.TestContext.WriteLine("Bulk Copy Rows: {0}", dt.Rows.Count); this.TestContext.WriteLine("Bulk Copy Time: {0}ms", sw.ElapsedMilliseconds); // More than zero? Assert.IsTrue(dt.Rows.Count > 0); } }
public void MetaDataGarudaPhoenixTableGetIndexes() { string expectedTable = "BIGTABLE"; using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); DataTable dt = c.GetTables(); foreach (DataRow row in dt.Rows) { if (row["TABLE_NAME"].ToString() == expectedTable) { var tbl = new Garuda.Data.MetaData.GarudaPhoenixTable(row); DataTable dtCols = tbl.GetIndexes(c); Assert.IsNotNull(dtCols); Assert.IsNotNull(dtCols.Rows); Assert.IsTrue(dtCols.Rows.Count > 0); Assert.IsNotNull(dtCols.Columns); break; } } } }
private void RefreshTreeTables() { if (_connection.State != ConnectionState.Open) { _connection.Open(); } TreeNode root = _treeView.Nodes[0]; root.Nodes.Clear(); // Get list of tables and show in tree DataTable tables = _connection.GetTables(); foreach (DataRow row in tables.Rows) { GarudaPhoenixTable table = new GarudaPhoenixTable(row); TreeNode nSchema = GetSchemaTreeNode(table.Schema); TreeNode t = nSchema.Nodes.Add(table.FullName); t.Tag = table; t.ImageIndex = TreeImgNdx.Table; t.SelectedImageIndex = t.ImageIndex; t.ContextMenuStrip = _cmsTreeTableMenu; } root.Expand(); // Show tables in grid view for now. //UpdateDataGrid(tables); }
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 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 ConnectionTablesDataTable() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); DataTable tables = c.GetTables(); Assert.IsTrue(tables.Rows.Count > 0); } }
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 ConnectionConstructorBasicOpenDisposeTest() { using (PhoenixConnection c = new PhoenixConnection(this.ConnectionString())) { c.Open(); TestContext.WriteLine("ConnectionId: {0}", c.ConnectionId); Assert.AreEqual <ConnectionState>(ConnectionState.Open, c.State); Assert.IsNotNull(c.ConnectionId); Assert.IsNotNull(c.ConnectionString); } }
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); } } } }
private void _tspExecute_Click(object sender, EventArgs e) { Stopwatch sw = new Stopwatch(); try { // Clear the messages. _txtMessages.Clear(); // Reopen connection if needed. if (_connection.State != ConnectionState.Open) { _connection.Open(); } sw.Start(); _mainForm.UpdateBusyWaitState(true, Properties.Resources.StatusExecuting); using (PhoenixCommand cmd = new PhoenixCommand(_connection)) { cmd.CommandText = _rtbQuery.Text; using (IDataReader dr = cmd.ExecuteReader()) { DataTable dt = new DataTable(); if (0 < dr.FieldCount) { dt.Load(dr); } UpdateDataGrid(dt); _txtMessages.AppendText(string.Format("{0} record(s) affected", dr.RecordsAffected)); // How long did the command take? sw.Stop(); UpdateElapsedStatus(sw, cmd); } } } catch (Exception ex) { HandleException(ex); } finally { _mainForm.UpdateBusyWaitState(false, null); } }
public void CommandExplain() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (PhoenixCommand cmd = new PhoenixCommand(c)) { cmd.CommandText = "SELECT * FROM BIGTABLE WHERE ID < 1000"; DataTable dt = cmd.Explain(); Assert.IsNotNull(dt); } } }
public void CommandExecuteScalar() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (PhoenixCommand cmd = new PhoenixCommand(c)) { cmd.CommandText = "SELECT COUNT(*) FROM BIGTABLE WHERE ID < 1000"; object oVal = cmd.ExecuteScalar(); Assert.IsNotNull(oVal); Assert.IsInstanceOfType(oVal, typeof(long)); } } }
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)); } } }
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); }
public void ExecuteNonQueryUpsert1KBigTable() { int rowsToInsert = 1000; List <Func <object> > pFuncs = new List <Func <object> >(); pFuncs.Add(() => string.Format("N{0}", DateTime.Now.ToString("hmmss"))); pFuncs.Add(() => Guid.NewGuid().ToString()); pFuncs.Add(() => DateTime.Now); using (IDbConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); CreateBigTestTableIfNotExists(c, false); } PreparedCmdParameterTest(rowsToInsert, "UPSERT INTO bigtable (ID, AircraftIcaoNumber, LruFlightKey, MyTimestamp) VALUES (NEXT VALUE FOR garuda.bigtableSequence, :1, :2, :3)", pFuncs, false); }
/// <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 ExecuteQueryBigTable() { Stopwatch sw = new Stopwatch(); using (IDbConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); CreateBigTestTableIfNotExists(c, false); // Query the table and measure performance sw.Start(); long rows = QueryAllRows(c, "bigtable", 10); sw.Stop(); WriteQueryRowsPerf(rows, sw.ElapsedMilliseconds); // How many rows did we get back? this.TestContext.WriteLine("Queried Rows: {0}", rows); // More than zero? Assert.IsTrue(rows > 0); } }
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()); } } }