public void SelectAsTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select `id` as `Identifier`, `id` from user where id = 1");
Assert.True(result.IsSimpleSelect);
Assert.Equal("Identifier", result.Columns[0]);
}
public void SelectFuncTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select FUNC(`id`) from user where id = 1");
Assert.True(result.IsSimpleSelect);
Assert.Equal("FUNC(`id`)", result.Columns[0]);
}
public void SelectCalcTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select `id` + 1, `id` from user where id = 1");
Assert.True(result.IsSimpleSelect);
Assert.Equal("`id` + 1", result.Columns[0]);
Assert.Equal("id", result.Columns[1]);
}
public void SelectAllTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select * from `user` where id = 1");
Assert.True(result.IsSimpleSelect);
Assert.Single(result.Columns);
Assert.Equal("*", result.Columns.First());
Assert.Equal("user", result.Table);
}
public void SelectSomeTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select `id`, username, password, `email` from user where id = 1");
Assert.True(result.IsSimpleSelect);
Assert.Equal(4, result.Columns.Count);
Assert.Equal("id", result.Columns[0]);
Assert.Equal("username", result.Columns[1]);
Assert.Equal("password", result.Columns[2]);
Assert.Equal("email", result.Columns[3]);
Assert.Equal("user", result.Table);
}
public void SelectGroupByTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select count(1), `group` from user group by `group`");
Assert.False(result.IsSimpleSelect);
}
public void EmptyCommandTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("");
Assert.False(result.IsSimpleSelect);
}
public void NonSelectCommandTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("show tables");
Assert.False(result.IsSimpleSelect);
}
public void SelectVariableTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select @test");
Assert.False(result.IsSimpleSelect);
}
public void SelectJoinTest()
{
var result = MySqlCommandSpliter.AnalyzeCommand("select `id`, username, password, `email` from user inner join `test` on `user`.id = `test`.`userid`");
Assert.False(result.IsSimpleSelect);
}
public IActionResult ExecuteResult([FromRoute] string id, [FromBody] ExecuteSqlRequest request)
{
var conn = ConnectionHelper.Connections[id];
var ret = new List <MySqlQueryResult>();
var splitedCommands = MySqlCommandSpliter.SplitCommand(request.Sql);
using (var command = new MySqlCommand())
{
command.Connection = conn;
command.CommandText = $"USE `{request.Database}`;";
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Response.StatusCode = 400;
return(Json(new DBError
{
Code = ex.Number,
Message = ex.Message
}));
}
foreach (var x in splitedCommands)
{
var res = new MySqlQueryResult();
res.Command = x;
var analyze = MySqlCommandSpliter.AnalyzeCommand(x);
res.Table = analyze.Table;
command.CommandText = x;
var begin = DateTime.Now;
IEnumerable <MySqlTableColumn> tableColumns = null;
try
{
tableColumns = MySqlCommandSpliter.GetTableColumns(res.Table, conn).ToList();
res.Readonly = string.IsNullOrWhiteSpace(analyze.Table) || !(analyze.IsSimpleSelect && MySqlCommandSpliter.IsContainedKeys(analyze.Columns, tableColumns));
}
catch (MySqlException)
{
res.Readonly = true;
}
try
{
using (var reader = command.ExecuteReader())
{
if (res.Readonly)
{
res.Columns = GenerateColumnsFromReader(reader).ToList();
res.ColumnTypes = null;
res.Nullable = null;
res.Keys = null;
res.RowsAffected = reader.RecordsAffected;
}
else
{
res.Columns = analyze.Columns;
if (res.Columns.Count() == 1 && res.Columns.First() == "*")
{
res.Columns = tableColumns.Select(x => x.Field);
}
else
{
res.Columns = analyze.Columns;
}
res.ColumnTypes = res.Columns.Select(x => tableColumns.SingleOrDefault(y => y.Field == x)?.Type);
res.Nullable = res.Columns.Select(x => tableColumns.SingleOrDefault(y => y.Field == x)?.Null);
res.Keys = tableColumns.Where(x => x.Key == "PRI").Select(x => x.Field);
res.RowsAffected = reader.RecordsAffected;
}
if (reader.HasRows)
{
res.Rows = new List <List <string> >();
while (reader.Read())
{
var row = new List <string>();
for (var i = 0; i < reader.FieldCount; ++i)
{
if (reader.IsDBNull(i))
{
row.Add(null);
}
else if (reader.GetFieldType(i) == typeof(byte[]))
{
row.Add(Convert.ToBase64String((byte[])reader[i]));
}
else
{
row.Add(reader[i].ToString());
}
}
res.Rows.Add(row);
}
}
}
}
catch (MySqlException ex)
{
Response.StatusCode = 400;
return(Json(new DBError
{
Code = ex.Number,
Message = ex.Message
}));
}
var end = DateTime.Now;
res.TimeSpan = Convert.ToInt64((end - begin).TotalMilliseconds);
ret.Add(res);
}
}
return(Json(ret));
}
