public List <T> GetObjectList <T>(Schema s, params string[] types) where T : DatabaseObject
{
for (int i = 0; i < types.Length; i++)
{
types[i] = types[i].ToUpperInvariant();
}
RowResult result = GetRowResult("list_objects", new KeyValuePair <string, object>("schema", s.Name));
var rows = result.FetchAll();
List <T> docs = new List <T>();
foreach (var row in rows)
{
if (!types.Contains(row.GetString("type").ToUpperInvariant()))
{
continue;
}
List <object> parameters = new List <object>(new object[] { s, row.GetString("name") });
if (row["name"] is Byte[])
{
Byte[] byteArray = row["name"] as Byte[];
parameters[1] = Encoding.UTF8.GetString(byteArray, 0, byteArray.Length);
}
switch (row.GetString("type").ToUpperInvariant())
{
case "TABLE":
parameters.Add(false);
break;
case "VIEW":
parameters.Add(true);
break;
}
T t = (T)Activator.CreateInstance(typeof(T),
BindingFlags.NonPublic | BindingFlags.Instance,
null, parameters.ToArray(), null);
docs.Add(t);
}
return(docs);
}
/// <summary>
/// Sets the connection's charset default collation.
/// </summary>
/// <param name="session">The opened session.</param>
/// <param name="charset">The character set.</param>
private static void SetDefaultCollation(InternalSession session, string charset)
{
if (!session.GetServerVersion().isAtLeast(8, 0, 1))
{
return;
}
session.GetSqlRowResult("SHOW CHARSET WHERE Charset='" + charset + "'");
RowResult result = session.GetSqlRowResult("SHOW CHARSET WHERE Charset='" + charset + "'");
var row = result.FetchOne();
if (row != null)
{
var defaultCollation = row.GetString("Default collation");
session.ExecuteSqlNonQuery("SET collation_connection = '" + defaultCollation + "'");
}
else
{
session.ExecuteSqlNonQuery("SET collation_connection = 'utf8mb4_0900_ai_ci'");
}
}
public void RowResult_AssertValues_BothColumnValuesDoNotMatch_ShowFirstError(int rowNumber, int expectedUserId, int expectedDomainId)
{
DataTable table = CreateDefaultTable();
AddRow(table, 1001, 1002);
AddRow(table, 2001, 2002);
AddRow(table, 3001, 3002);
DataSetRow expectedData = new DataSetRow
{
{ "UserId", expectedUserId },
{ "DomainId", expectedDomainId }
};
RowResult result = new RowResult(new QueryResult(_testFramework, table), rowNumber);
var exception = Assert.Throws <Microsoft.VisualStudio.TestTools.UnitTesting.AssertFailedException>(() =>
{ result = result.AssertValues(expectedData); });
Assert.AreEqual($"Assert.AreEqual failed. Expected:<{ expectedUserId }>. Actual:<{table.Rows[rowNumber]["UserId"]}>. Column UserId in row {rowNumber} has an unexpected value", exception.Message);
}
public void Select()
{
InitTable();
Table table = GetTable();
var selectStmt = table.Select().Where("id = :id").Bind("id", 1);
RowResult row = ExecuteSelectStatement(selectStmt);
Assert.AreEqual(_allRows[0][1], row.FetchAll()[0]["name"].ToString());
Assert.False(selectStmt._isPrepared);
ValidatePreparedStatements(0, 0, null);
for (int i = 0; i < _allRows.Length; i++)
{
row = ExecuteSelectStatement(selectStmt.Bind("id", i + 1).Limit(1));
Assert.AreEqual(_allRows[i][1], row.FetchAll()[0]["name"].ToString());
Assert.True(selectStmt._isPrepared || !selectStmt.Session.SupportsPreparedStatements);
}
ValidatePreparedStatements(1, 3,
$"SELECT * FROM `{schemaName}`.`{_tableName}` WHERE (`id` = ?) LIMIT ?, ?");
}
public void TableDefaultCharset()
{
ExecuteSQL("CREATE TABLE test(b VARCHAR(255)) CHARSET greek");
ExecuteSQL("INSERT INTO test VALUES('Δ')");
RowResult r = ExecuteSelectStatement(GetSession().GetSchema(schemaName).GetTable("test").Select("b"));
var rows = r.FetchAll();
Assert.AreEqual(schemaName, r.Columns[0].SchemaName);
Assert.AreEqual("test", r.Columns[0].TableName);
Assert.AreEqual("test", r.Columns[0].TableLabel);
Assert.AreEqual("b", r.Columns[0].ColumnName);
Assert.AreEqual("b", r.Columns[0].ColumnLabel);
Assert.AreEqual(ColumnType.String, r.Columns[0].Type);
Assert.AreEqual(255u, r.Columns[0].Length);
Assert.AreEqual(0u, r.Columns[0].FractionalDigits);
Assert.False(r.Columns[0].IsNumberSigned);
Assert.AreEqual("utf8mb4", r.Columns[0].CharacterSetName);
Assert.AreEqual("utf8mb4_0900_ai_ci", r.Columns[0].CollationName);
Assert.False(r.Columns[0].IsPadded);
Assert.AreEqual("Δ", rows[0][0]);
}
public void SharedLockForbidsToModifyDocuments()
{
if (!session.InternalSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
session.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
using (var session2 = MySQLX.GetSession(ConnectionString))
{
session2.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
Table table = session.Schema.GetTable("test");
Table table2 = session2.GetSchema("test").GetTable("test");
session.SQL("START TRANSACTION").Execute();
RowResult rowResult = table.Select().Where("id = 1").LockShared().Execute();
Assert.Equal(1, rowResult.FetchAll().Count);
session2.SQL("START TRANSACTION").Execute();
// Reading the same row is allowed with LockShared().
rowResult = table2.Select().Where("id = 1").Execute();
Assert.Equal(1, rowResult.FetchAll().Count);
// Modify() is allowed for non-locked rows.
Result result = table2.Update().Where("id = 2").Set("age", 2).Execute();
Assert.Equal <ulong>(1, result.RecordsAffected);
// Session1 blocks, Modify() is not allowed for locked rows.
session2.SQL("SET SESSION innodb_lock_wait_timeout=1").Execute();
Exception ex = Assert.Throws <MySqlException>(() => table2.Update().Where("id = 1").Set("age", 2).Execute());
Assert.Equal("Lock wait timeout exceeded; try restarting transaction", ex.Message);
session.SQL("ROLLBACK").Execute();
// Modify() is allowed since row isn't locked anymore.
table2.Update().Where("id = 1").Set("age", 2).Execute();
session2.SQL("COMMIT").Execute();
}
}
public void SchemaDefaultCharset()
{
ExecuteSQL("CREATE TABLE test(b VARCHAR(255))");
ExecuteSQL("INSERT INTO test VALUES('CAR')");
var defaultValues = ExecuteSQLStatement(GetSession(true).SQL("SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME " +
$"FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{schemaName}'; ")).FetchAll();
RowResult r = ExecuteSelectStatement(GetSession().GetSchema(schemaName).GetTable("test").Select("b"));
var rows = r.FetchAll();
Assert.AreEqual(schemaName, r.Columns[0].SchemaName);
Assert.AreEqual("test", r.Columns[0].TableName);
Assert.AreEqual("test", r.Columns[0].TableLabel);
Assert.AreEqual("b", r.Columns[0].ColumnName);
Assert.AreEqual("b", r.Columns[0].ColumnLabel);
Assert.AreEqual(ColumnType.String, r.Columns[0].Type);
Assert.AreEqual(0u, r.Columns[0].FractionalDigits);
Assert.AreEqual(false, r.Columns[0].IsNumberSigned);
Assert.AreEqual(defaultValues[0][0], r.Columns[0].CharacterSetName);
Assert.AreEqual(defaultValues[0][1], r.Columns[0].CollationName);
Assert.AreEqual(false, r.Columns[0].IsPadded);
Assert.AreEqual("CAR", rows[0][0]);
using (var connection = new MySqlConnection(ConnectionStringRoot))
{
connection.Open();
if (connection.driver.Version.isAtLeast(8, 0, 1))
{
Assert.AreEqual(1020u, r.Columns[0].Length);
}
else
{
Assert.AreEqual(255u, r.Columns[0].Length);
}
}
}
public void ExclusiveLockAfterSharedLock()
{
if (!session.InternalSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
session.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
using (var session2 = MySQLX.GetSession(ConnectionString))
{
session2.SQL("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED").Execute();
Table table = session.Schema.GetTable("test");
session.SQL("CREATE UNIQUE INDEX myIndex ON test.test (id)").Execute();
Table table2 = session2.GetSchema("test").GetTable("test");
session.SQL("START TRANSACTION").Execute();
RowResult rowResult = table.Select().Where("id in (1, 3)").LockShared().Execute();
Assert.Equal(2, rowResult.FetchAll().Count);
session2.SQL("START TRANSACTION").Execute();
// Should return immediately since row isn't locked.
rowResult = table2.Select().Where("id = 2").LockExclusive().Execute();
// Should return immediately due to LockShared() allows reading by other sessions.
rowResult = table2.Select().Where("id = 2").LockShared().Execute();
Assert.Equal(1, rowResult.FetchAll().Count);
// Session2 blocks due to to LockExclusive() not allowing to read locked rows.
session2.SQL("SET SESSION innodb_lock_wait_timeout=1").Execute();
Exception ex = Assert.Throws <MySqlException>(() => table2.Select().Where("id = 1").LockExclusive().Execute());
Assert.Equal("Lock wait timeout exceeded; try restarting transaction", ex.Message);
// Session unlocks rows.
session.SQL("ROLLBACK").Execute();
rowResult = table2.Select().Where("id = 1").LockExclusive().Execute();
Assert.Equal(1, rowResult.FetchAll().Count);
session2.SQL("ROLLBACK").Execute();
}
}
public TestRowResult(RowResult row)
{
Key = row.GetInt32(0);
IsDeleted = row.IsDeleted;
}
public void LockExclusiveAndSharedWithWaitingOptions(LockContention lockOption, LockMode lockMode)
{
if (!session.XSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
CreateBooksTable();
string tableName = "books";
string schemaName = "test";
// first session locks the row
using (Session s1 = MySQLX.GetSession(ConnectionString))
{
var t1 = s1.GetSchema(schemaName).GetTable(tableName);
s1.StartTransaction();
RowResult r1 = ExecuteSelectStatement(t1.Select().Where("id = :id").Bind("id", 1).LockExclusive());
var rows1 = r1.FetchAll();
Assert.That(rows1, Has.One.Items);
Assert.AreEqual(1, rows1[0]["id"]);
// second session tries to read the locked row
using (Session s2 = MySQLX.GetSession(ConnectionString))
{
var t2 = s2.GetSchema(schemaName).GetTable(tableName);
ExecuteSQLStatement(s2.SQL("SET innodb_lock_wait_timeout = 1"));
s2.StartTransaction();
var stmt2 = t2.Select();
if (lockMode == LockMode.Exclusive)
{
stmt2.LockExclusive(lockOption);
}
else
{
stmt2.LockShared(lockOption);
}
switch (lockOption)
{
case LockContention.Default:
// error 1205 Lock wait timeout exceeded; try restarting transaction
Assert.AreEqual(1205u, Assert.Throws <MySqlException>(() => ExecuteSelectStatement(stmt2).FetchAll()).Code);
break;
case LockContention.NoWait:
// error 1205 Lock wait timeout exceeded; try restarting transaction
uint expectedError = 1205;
if (session.XSession.GetServerVersion().isAtLeast(8, 0, 5))
{
// error 3572 Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set
expectedError = 3572;
}
Assert.AreEqual(expectedError, Assert.Throws <MySqlException>(() => ExecuteSelectStatement(stmt2).FetchAll()).Code);
break;
case LockContention.SkipLocked:
if (!session.XSession.GetServerVersion().isAtLeast(8, 0, 5))
{
// error 1205 Lock wait timeout exceeded; try restarting transaction
Assert.AreEqual(1205u, Assert.Throws <MySqlException>(() => ExecuteSelectStatement(stmt2).FetchAll()).Code);
break;
}
var rows2 = ExecuteSelectStatement(stmt2).FetchAll();
Assert.That(rows2, Has.One.Items);
Assert.AreEqual(2, rows2[0]["id"]);
break;
default:
throw new NotImplementedException(lockOption.ToString());
}
}
// first session frees the lock
s1.Commit();
}
}
public void QueryResult_ValidateRow_RowDoesNotExist_Error(int rowNumber)
{
DataTable table = CreateDefaultTable();
AddRow(table, 1001, 1002);
AddRow(table, 2001, 2002);
AddRow(table, 3001, 3002);
QueryResult queryResult = new QueryResult(_testFramework, table);
var exception = Assert.Throws <Microsoft.VisualStudio.TestTools.UnitTesting.AssertFailedException>(() =>
{ RowResult result = queryResult.ValidateRow(rowNumber); });
Assert.AreEqual($"Assert.IsTrue failed. There is no row at position {rowNumber} (zero-based). There are 3 rows", exception.Message);
}
/// <summary>
/// Prints a <see cref="RowResult"/> in the output window showing extended information about the execution result (rows returned, execution time, etc.).
/// </summary>
/// <param name="statement">The executed statement.</param>
/// <param name="rowResult">A <see cref="RowResult"/> instance.</param>
private void PrintRowResult(string statement, RowResult rowResult)
{
string executionTime = rowResult.ExecutionTime;
var dictionariesList = rowResult.Data;
var count = dictionariesList != null ? dictionariesList.Count : 0;
switch (_executionModeOption)
{
case ExecutionModeOption.BatchMode:
if (dictionariesList != null && count > 0)
{
CreateResultPane(dictionariesList, _tabCounter);
_tabCounter++;
}
break;
case ExecutionModeOption.ConsoleMode:
if (dictionariesList == null)
{
break;
}
// Get columns names
var columns = rowResult.GetColumnNames();
// Create console table object for output format
var table = new ConsoleTable(columns.ToArray());
foreach (var rowData in dictionariesList)
{
object[] columnValues = rowData.Select(o => o.Value == null ? (object)"null" : o.Value.ToString()).ToArray();
var formattedColumnValues = new List <Object>();
//Format values
foreach (var value in rowData.Values)
{
if (value is Dictionary <string, object> )
{
formattedColumnValues.Add(Utils.GetFormattedValue(value));
continue;
}
formattedColumnValues.Add(value);
}
table.AddRow(formattedColumnValues.ToArray());
}
if (table.Rows.Count > 0)
{
BaseShellConsoleEditor.AddMessage(table.ToStringAlternative());
}
break;
}
var resultMessage = new StringBuilder();
// If no items are returned, it is a DDL statement (Drop, Create, etc.)
if (count == 0)
{
var sqlResult = rowResult as SqlResult;
if (sqlResult != null)
{
resultMessage.AppendFormat("Query OK, {0} row(s) affected, {1} warning(s)", sqlResult.AffectedRowCount, rowResult.WarningCount);
}
else
{
resultMessage.AppendFormat("Query OK, {0} warning(s)", rowResult.WarningCount);
}
}
else
{
resultMessage.AppendFormat("{0} row(s) in set.", count);
}
WriteToMySqlOutput(statement, resultMessage.ToString(), executionTime, MessageType.Information);
PrintWarnings(statement, rowResult, executionTime);
}
private void AddValueToRow()
{
RowResult.Add(ValueResult.ToString(0, ValueResult.Length - TrailingWhitespaceCount));
ValueResult = new StringBuilder();
TrailingWhitespaceCount = 0;
}
public object ExecuteQuery(IQuery query)
{
ExecutionResult result = new ExecutionResult();
SqlTransaction transaction = null;
bool hasErrors = false;
try
{
if (query.NeedTransaction)
{
transaction = sqlConnection.BeginTransaction();
}
SqlCommand command = new SqlCommand(query.QueryString, sqlConnection, transaction);
if (query.QueryParameters != null)
{
foreach (KeyValuePair <string, object> pair in query.QueryParameters)
{
command.Parameters.AddWithValue(pair.Key, pair.Value);
}
}
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
RowResult row = new RowResult();
for (int i = 0; i < dataReader.FieldCount; i++)
{
row.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
result.Rows.Add(row);
}
}
}
}
catch (Exception ex)
{
hasErrors = true;
throw ex;
}
finally
{
if (query.NeedTransaction)
{
if (hasErrors)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
}
return(result);
}
protected override bool Execute(string name, CefV8Value obj, CefV8Value[] arguments, out CefV8Value returnValue, out string exception)
{
exception = null;
returnValue = null;
string paramSampler = arguments[0].GetStringValue();
switch (name)
{
case "ChangeSelected":
CefProcessMessage cefMsg = CefProcessMessage.Create("ChangeSelected");
cefMsg.Arguments.SetSize(0);
cefMsg.Arguments.SetString(0, paramSampler);
CefV8Context.GetCurrentContext().GetBrowser().SendProcessMessage(CefProcessId.Browser, cefMsg);
break;
case "ShowYGDetail":
int overday = CommonDAO.GetInstance().GetCommonAppletConfigInt32("存样柜超期天数");
IList <RowResult> rowResult = new List <RowResult>();
IList <InfAutoCupBoard> cellList = CommonDAO.GetInstance().SelfDber.Entities <InfAutoCupBoard>(string.Format("where CupBoardCode like '%{0}%' and CupBoardType='{1}' and ParentPKID is not null", paramSampler, arguments[1].GetStringValue()));
if (cellList != null && cellList.Count > 0)
{
int maxRow = cellList.OrderByDescending(a => a.RowNumber).First().RowNumber;
int maxCell = cellList.OrderByDescending(a => a.CellNumber).First().CellNumber;
for (int i = 1; i <= maxRow; i++)
{
RowResult rowEntity = new RowResult();
rowEntity.RowName = String.Format("第 {0} 层", i);
rowEntity.CellList = new List <CellResult>();
for (int j = 1; j <= maxCell; j++)
{
CellResult cellEntity = new CellResult();
InfAutoCupBoard sampleCell = cellList.Where(a => a.RowNumber == i && a.CellNumber == j).FirstOrDefault();
if (sampleCell == null)
{
cellEntity.CellFlag = -1;
}
else
{
cellEntity.CellNumber = sampleCell.CupBoardDes.Replace("层", "").Replace("格", "").Remove(0, 2);
//柜门标识：-1没这一格，0空柜，1已存放，2超期样，3停用
if (sampleCell.IsValid == "0")
{
cellEntity.CellFlag = 3;
}
else if (sampleCell.State == 1 && sampleCell.SaveTime < DateTime.Now.AddDays(-overday))
{
cellEntity.CellFlag = 2;
}
else if (sampleCell.State == 1)
{
cellEntity.CellFlag = 1;
}
else
{
cellEntity.CellFlag = 0;
}
}
rowEntity.CellList.Add(cellEntity);
}
rowResult.Add(rowEntity);
}
}
returnValue = CefV8Value.CreateString(Newtonsoft.Json.JsonConvert.SerializeObject(rowResult));
break;
default:
returnValue = null;
break;
}
return(true);
}
public static Row FromResult(RowResult result)
{
return(new Row(result.GetString("a"),
result.GetString("b"),
result.GetString("c")));
}
private static void executeRowMultiplication(string path1, string path2, long rows_m1, long columns_m1, char separator, int rowIndex_m1)
{
RowResult rowResult = new RowResult();
string matrix1_row = File.ReadLines(path1).Skip(rowIndex_m1).Take(1).First();
var m1_rowElements = matrix1_row.Split(separator).Select(Int32.Parse).ToArray();
StringBuilder sbRowResult = new StringBuilder();
using (StreamReader sr2 = File.OpenText(path2))
{
for (int i_m2 = 0; i_m2 < rows_m1; i_m2++)
{
string matrix2_row = sr2.ReadLine();
var m2_rowElements = matrix2_row.Split(separator).Select(Int32.Parse).ToArray();
int mr_element = 0;
for (int j = 0; j < columns_m1; j++)
{
mr_element += m1_rowElements[j] * m2_rowElements[j];
}
sbRowResult.Append(mr_element);
sbRowResult.Append(separator);
}
}
sbRowResult.Length -= 1;
rowResult.row = sbRowResult.ToString();
rowResult.rowNumber = rowIndex_m1;
lock (syncLock) {
MatrixResult.Add(rowResult);
}
}
