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)); }