public static IEnumerable <string> GetColumnNames(this SqlGroup node)
{
var segment = node.SkipWhile(n => !n.TextEquals("select"))
.Skip(1).TakeWhile(n => !n.TextEquals("from")).ToArray();
if (!segment.Any())
{
yield break;
}
int start = 0;
string name;
for (var i = 1; i < segment.Length; i++)
{
if (segment[i].TextEquals(","))
{
name = ColumnName(segment, start, i);
if (!string.IsNullOrWhiteSpace(name))
{
yield return(name);
}
start = i + 1;
}
}
name = ColumnName(segment, start, segment.Length);
if (!string.IsNullOrWhiteSpace(name))
{
yield return(name);
}
}
public SqlDeclares(SqlGroup group)
{
var declares = group.OfType<SqlGroup>().Where(g => g.FirstOrDefault().TextEquals("declare"));
foreach (var declare in declares)
{
ParseDeclare(declare);
}
}
public SqlDeclares(SqlGroup group)
{
var declares = group.OfType <SqlGroup>().Where(g => g.FirstOrDefault().TextEquals("declare"));
foreach (var declare in declares)
{
ParseDeclare(declare);
}
}
public static int EditGroup(string email, SqlGroup userGroup)
{
//INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
var sql = $"UPDATE user SET mygroup = '{userGroup.ToString()}' WHERE email = '{email}'";
using (var cmd = new MySqlCommand(sql, HyMySqlHelper.MySqlConnection))
{
var num = cmd.ExecuteNonQuery();
return(num);
}
}
private void ParseDeclare(SqlGroup group)
{
var segment = group.ToArray();
var start = 1;
for (var i = 1; i < segment.Length; i++)
{
if (segment[i].TextEquals(","))
{
DeclareSegment(segment.Skip(start).Take(i - start));
start = i + 1;
}
}
DeclareSegment(segment.Skip(start).Take(segment.Length - start));
}
private void ProcessGroup(SqlTableInfo info, SqlGroup group)
{
if (group == null)
{
return;
}
var cols = group.GetColumnNames().ToList();
var toRemove = cols.Where(c => c.EndsWith("*")).ToArray();
if (toRemove.Any(c => c == "*"))
{
var ctx = new SqlContext(group);
cols.AddRange(ctx.Tables.Where(t => t.Columns != null).SelectMany(t => t.Columns));
info.AdditionalColumns = ctx.Tables.Where(t => t.Columns == null && t.Name != null)
.Concat(ctx.Tables.Where(t => t.AdditionalColumns != null).SelectMany(t => t.AdditionalColumns)).ToArray();
}
else if (cols.Any(c => c.EndsWith("*")))
{
var ctx = new SqlContext(group);
var additional = new List <SqlTableInfo>();
SqlTableInfo colInfo;
foreach (var col in toRemove.Select(c => c.TrimEnd('.', '*')))
{
if (ctx.TryByName(col, out colInfo))
{
if (colInfo.Columns == null)
{
additional.Add(colInfo);
}
else
{
cols.AddRange(colInfo.Columns);
}
}
}
info.AdditionalColumns = additional;
}
foreach (var item in toRemove)
{
cols.Remove(item);
}
if (cols.Any())
{
info.Columns = cols;
}
return;
}
private void ParseDeclare(SqlGroup group)
{
var segment = group.ToArray();
var start = 1;
for (var i = 1; i < segment.Length; i++)
{
if (segment[i].TextEquals(","))
{
DeclareSegment(segment.Skip(start).Take(i - start));
start = i + 1;
}
}
DeclareSegment(segment.Skip(start).Take(segment.Length - start));
}
public static int editGroup(string userid, SqlGroup userGroup)
{
int a = 0;
using (var db = new DataContext())
{
UserEntity userEntity = db.User
.Where(b => b.Userid.Equals(userid)).FirstOrDefault();
if (userid != null)
{
userEntity.UserGroup = userGroup.ToString();
}
a = db.SaveChanges();
}
return(a);
}
public SqlContext(SqlGroup node)
{
var parentGroup = node.Parent as SqlGroup;
while (!node.Any(n => SqlTokenizer.KeywordPrecedesTable(n as SqlLiteral)) && parentGroup != null)
{
node = parentGroup;
parentGroup = node.Parent as SqlGroup;
}
var i = 0;
SqlLiteral literal;
SqlName name;
SqlGroup group;
while (i < node.Count)
{
literal = node[i] as SqlLiteral;
if (i == 0 && node[i].TextEquals("with"))
{
i++;
while (i < node.Count && node[i] is SqlLiteral && !node[i].TextEquals("select"))
{
var info = new SqlTableInfo();
info.Alias = ((SqlLiteral)node[i]).Text;
_definitions.Add(info.Alias);
i++;
if (i >= node.Count)
{
return;
}
if (node[i].TextEquals("as"))
{
i++;
}
if (i >= node.Count)
{
return;
}
ProcessGroup(info, node[i] as SqlGroup);
_tables.Add(info);
if (node[i].TextEquals(","))
{
i++;
}
}
}
else if (SqlTokenizer.KeywordPrecedesTable(literal) && (i + 1) < node.Count)
{
i++;
name = node[i] as SqlName;
group = node[i] as SqlGroup;
if (name != null)
{
if (!_tables.Any(t => string.Equals(t.Alias, name.ToString(), StringComparison.OrdinalIgnoreCase)))
{
_tables.Add(new SqlTableInfo()
{
Name = name,
Alias = name.Alias
});
}
}
else if (group != null &&
group.Count > 5 &&
group[0].TextEquals("(") &&
group[1].TextEquals("select"))
{
var info = new SqlTableInfo();
ProcessGroup(info, group);
if ((i + 1) < node.Count && node[i + 1].TextEquals("as"))
{
i++;
}
if ((i + 1) < node.Count && node[i + 1].Type == SqlType.Identifier)
{
i++;
info.Alias = ((SqlLiteral)node[i]).Text;
}
_tables.Add(info);
}
else if (group != null &&
group.Count > 2 &&
group[0] is SqlName &&
group[1].TextEquals("("))
{
var info = new SqlTableInfo()
{
Name = group[0] as SqlName
};
if ((i + 1) < node.Count && node[i + 1].TextEquals("as"))
{
i++;
}
if ((i + 1) < node.Count && node[i + 1].Type == SqlType.Identifier)
{
i++;
info.Alias = ((SqlLiteral)node[i]).Text;
}
_tables.Add(info);
}
}
i++;
}
}
public static SqlGroup Parse(IEnumerable <SqlNode> tokens)
{
var groups = new Stack <SqlGroup>();
groups.Push(new SqlGroup());
SqlLiteral literal;
foreach (var node in tokens)
{
literal = node as SqlLiteral;
if (groups.Peek().Any() && literal != null)
{
if (literal.Text == "(")
{
var newGroup = new SqlGroup();
newGroup.Type = SqlType.QueryExpr;
var last = groups.Peek().Last();
// Move the name of the function being called to the new group
if (last.Type == SqlType.Identifier)
{
groups.Peek().Remove(last);
newGroup.Add(last);
}
groups.Push(newGroup);
}
else if (
literal.Text.Equals("case", StringComparison.OrdinalIgnoreCase) ||
literal.Text.Equals("begin", StringComparison.OrdinalIgnoreCase)
)
{
groups.Push(new SqlGroup()
{
Type = SqlType.QueryExpr
});
}
//else if (literal.Text == ",")
//{
// var lastGroup = groups.Peek();
// var idx = lastGroup.Count - 1;
// while (idx >= 0 && !lastGroup[idx].TextEquals("(")
// && !lastGroup[idx].TextEquals(",") && lastGroup[idx].Type != SqlType.Keyword)
// idx--;
// var newGroup = new SqlGroup();
//}
}
groups.Peek().Add(node);
if (literal != null)
{
if (literal.Text == ")" ||
literal.Text.Equals("end", StringComparison.OrdinalIgnoreCase))
{
var child = groups.Pop();
if (!groups.Any())
{
groups.Push(new SqlGroup());
}
groups.Peek().Add(child);
}
else if (literal.Text == ";")
{
var child = groups.Pop();
// Deal with unclosed expressions
while (child.Type == SqlType.QueryExpr && groups.Any())
{
groups.Peek().Add(child);
child = groups.Pop();
}
if (!groups.Any())
{
groups.Push(new SqlGroup());
groups.Peek().Add(child);
}
child = new SqlGroup();
groups.Peek().Add(child);
groups.Push(child);
}
}
}
var result = groups.Pop();
if (result.Count < 1 && groups.Any())
{
result = groups.Pop();
}
if (result.Count == 1 && result[0] is SqlGroup)
{
result = result[0] as SqlGroup;
}
else if (result.Any() && result.Last() is SqlGroup && !((SqlGroup)result.Last()).Any())
{
result.Remove(result.Last());
}
while (groups.Any())
{
var newResult = groups.Pop();
newResult.Add(result);
result = newResult;
}
return(result);
}
public SqlContext(SqlGroup node)
{
var parentGroup = node.Parent as SqlGroup;
while (!node.Any(n => SqlTokenizer.KeywordPrecedesTable(n as SqlLiteral)) && parentGroup != null)
{
node = parentGroup;
parentGroup = node.Parent as SqlGroup;
}
var i = 0;
SqlLiteral literal;
SqlName name;
SqlGroup group;
while (i < node.Count)
{
literal = node[i] as SqlLiteral;
if (i == 0 && node[i].TextEquals("with"))
{
i++;
while (i < node.Count && node[i] is SqlLiteral && !node[i].TextEquals("select"))
{
var info = new SqlTableInfo();
info.Alias = ((SqlLiteral)node[i]).Text;
_definitions.Add(info.Alias);
i++;
if (i >= node.Count) return;
if (node[i].TextEquals("as")) i++;
if (i >= node.Count) return;
ProcessGroup(info, node[i] as SqlGroup);
_tables.Add(info);
if (node[i].TextEquals(",")) i++;
}
}
else if (SqlTokenizer.KeywordPrecedesTable(literal) && (i+1) < node.Count)
{
i++;
name = node[i] as SqlName;
group = node[i] as SqlGroup;
if (name != null)
{
if (!_tables.Any(t => string.Equals(t.Alias, name.ToString(), StringComparison.OrdinalIgnoreCase )))
_tables.Add(new SqlTableInfo()
{
Name = name,
Alias = name.Alias
});
}
else if (group != null
&& group.Count > 5
&& group[0].TextEquals("(")
&& group[1].TextEquals("select"))
{
var info = new SqlTableInfo();
ProcessGroup(info, group);
if ((i + 1) < node.Count && node[i + 1].TextEquals("as"))
i++;
if ((i + 1) < node.Count && node[i + 1].Type == SqlType.Identifier)
{
i++;
info.Alias = ((SqlLiteral)node[i]).Text;
}
_tables.Add(info);
}
else if (group != null
&& group.Count > 2
&& group[0] is SqlName
&& group[1].TextEquals("("))
{
var info = new SqlTableInfo() { Name = group[0] as SqlName };
if ((i + 1) < node.Count && node[i + 1].TextEquals("as"))
i++;
if ((i + 1) < node.Count && node[i + 1].Type == SqlType.Identifier)
{
i++;
info.Alias = ((SqlLiteral)node[i]).Text;
}
_tables.Add(info);
}
}
i++;
}
}
private void ProcessGroup(SqlTableInfo info, SqlGroup group)
{
if (group == null) return;
var cols = group.GetColumnNames().ToList();
var toRemove = cols.Where(c => c.EndsWith("*")).ToArray();
if (toRemove.Any(c => c == "*"))
{
var ctx = new SqlContext(group);
cols.AddRange(ctx.Tables.Where(t => t.Columns != null).SelectMany(t => t.Columns));
info.AdditionalColumns = ctx.Tables.Where(t => t.Columns == null && t.Name != null)
.Concat(ctx.Tables.Where(t => t.AdditionalColumns != null).SelectMany(t => t.AdditionalColumns)).ToArray();
}
else if (cols.Any(c => c.EndsWith("*")))
{
var ctx = new SqlContext(group);
var additional = new List<SqlTableInfo>();
SqlTableInfo colInfo;
foreach (var col in toRemove.Select(c => c.TrimEnd('.', '*')))
{
if (ctx.TryByName(col, out colInfo))
{
if (colInfo.Columns == null)
{
additional.Add(colInfo);
}
else
{
cols.AddRange(colInfo.Columns);
}
}
}
info.AdditionalColumns = additional;
}
foreach (var item in toRemove)
{
cols.Remove(item);
}
if (cols.Any())
info.Columns = cols;
return;
}
