/// <summary>
/// creates a database visualizer, must be disposed to release resources
/// </summary>
/// <param name="db">database</param>
/// <param name="query">query string text</param>
/// <param name="validator">query validator</param>
/// <param name="options">visualize options</param>
public DbVisualizer(CsvDb db, string query, IQueryValidation validator, DbVisualize options = DbVisualize.Paged)
: this(db, DbQuery.Parse(query, validator), options)
{
}
/// <summary>
/// parse an sql query text
/// </summary>
/// <param name="query">sql query text</param>
/// <param name="validator">sql query validator against a database</param>
/// <returns></returns>
public static DbQuery Parse(string query, IQueryValidation validator)
{
if (validator == null)
{
throw new ArgumentException("No query database validator provided");
}
int top = -1;
ColumnsSelect columnSelect = null;
var tableCollection = new List <Table>();
SqlJoin join = null;
ExpressionOperator where = null;
TokenItem PreviousToken = default(TokenItem);
TokenItem CurrentToken = default(TokenItem);
var peekedToken = default(TokenItem);
var columnCollection = new List <Column>();
Table joinTable = null;
var queue = new Queue <TokenItem>(ParseTokens(query));
//#if DEBUG
// Console.WriteLine("Query tokens:");
// Console.WriteLine($" {String.Join($"{Environment.NewLine} ", queue)}{Environment.NewLine}");
//#endif
bool EndOfStream = queue.Count == 0;
IEnumerable <Table> AllTables()
{
foreach (var t in tableCollection)
{
yield return(t);
}
if (joinTable != null)
{
yield return(joinTable);
}
}
bool GetToken()
{
if (EndOfStream || (EndOfStream = !queue.TryDequeue(out TokenItem tk)))
{
return(false);
}
//save previous
PreviousToken = CurrentToken;
//get current
CurrentToken = tk;
return(true);
}
bool PeekToken()
{
if (!EndOfStream && queue.TryPeek(out peekedToken))
{
return(true);
}
return(false);
}
bool GetTokenIf(TokenType token)
{
if (!EndOfStream && queue.TryPeek(out peekedToken) && peekedToken.Token == token)
{
CurrentToken = queue.Dequeue();
return(true);
}
return(false);
};
bool GetTokenIfContains(List <TokenType> tokens)
{
if (!EndOfStream && queue.TryPeek(out peekedToken) && tokens.Contains(peekedToken.Token))
{
CurrentToken = queue.Dequeue();
return(true);
}
return(false);
}
Column ReadColumnName(bool readAS = true)
{
Column selectColumn = null;
//read operand [(i).](column)
if (GetTokenIf(TokenType.Identifier))
{
var tableAlias = CurrentToken.Value;
String columnName = null;
String columnAlias = null;
if (GetTokenIf(TokenType.Dot))
{
if (!GetToken())
{
throw new ArgumentException($"column name expected");
}
columnName = CurrentToken.Value;
}
else
{
columnName = tableAlias;
tableAlias = null;
}
//alias
if (readAS && GetTokenIf(TokenType.AS))
{
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"Column alias name expected after AS: {CurrentToken.Value}");
}
columnAlias = CurrentToken.Value;
}
selectColumn = new Column(columnName, tableAlias, columnAlias);
}
return(selectColumn);
}
ExpressionOperand ReadOperand(DbColumnType casting)
{
if (!PeekToken())
{
return(null);
}
switch (peekedToken.Token)
{
case TokenType.String:
GetToken();
return(new StringOperand(CurrentToken.Value));
case TokenType.Number:
GetToken();
return(new NumberOperand(CurrentToken.Value, casting));
case TokenType.Identifier:
//get column name
GetToken();
var columnName = CurrentToken.Value;
var position = CurrentToken.Position;
String columnIdentifier = null;
if (GetTokenIf(TokenType.Dot))
{
//tablealias.colummname
if (!GetToken())
{
throw new ArgumentException($"column name expected");
}
columnIdentifier = columnName;
columnName = CurrentToken.Value;
}
Column col = null;
//try to find column in known tables
if (columnIdentifier != null)
{
//find in table by its alias
var aliasTable = AllTables().FirstOrDefault(t => t.Alias == columnIdentifier);
if (aliasTable == null)
{
throw new ArgumentException($"cannot find column: {columnName}");
}
col = new Column(columnName, aliasTable.Alias);
col.Meta = validator.ColumnMetadata(aliasTable.Name, col.Name);
}
else
{
//find the only first in all known tables
var tables = AllTables().Where(t => validator.TableHasColumn(t.Name, columnName)).ToList();
if (tables.Count != 1)
{
throw new ArgumentException($"column: {columnName} could not be found in database or cannot resolve multiple tables");
}
//tableName = tables[0].Name;
col = new Column(columnName, validator.ColumnMetadata(tables[0].Name, columnName));
}
return(new ColumnOperand(col, casting));
default:
return(null);
}
}
ExpressionOperator ReadExpression()
{
//https://stackoverflow.com/questions/3422673/evaluating-a-math-expression-given-in-string-form
var stack = new Stack <ExpressionItem>();
var operStack = new Stack <TokenType>();
ExpressionItem BuildTree()
{
//get higher precedence operator from operator stack
var oper = operStack.Pop();
//get left and right expressions
var right = stack.Pop();
var left = stack.Pop();
//create new expression
var expr = ExpressionOperator.Create(left, oper, right);
//push onto expression stack
stack.Push(expr);
return(expr);
}
var end = false;
while (!end)
{
if (!(end = !PeekToken()))
{
var currOper = peekedToken.Token;
switch (currOper)
{
case TokenType.String:
case TokenType.Number:
case TokenType.Identifier:
//if on top of stack there's a casting oper, apply it
DbColumnType casting = DbColumnType.None;
if (stack.Count > 0 && stack.Peek().ExpressionType == ExpressionItemType.Casting)
{
casting = (stack.Pop() as CastingExpression).Type;
}
//read operand and apply casting if any
stack.Push(ReadOperand(casting));
break;
case TokenType.OpenPar:
//consume the open parenthesis (
GetToken();
//try ahead if it's a casting operator
var currToken = CurrentToken.Token;
//try to get ahead a casting type
if (PeekToken() && (casting = peekedToken.Token.ToCast()).IsCasting())
{
//consume the casting type
GetToken();
if (!GetTokenIf(TokenType.ClosePar))
{
throw new ArgumentException("close parenthesis expected after casting type");
}
//store new casting expression
stack.Push(new CastingExpression(casting));
}
else
{
operStack.Push(currToken);
}
break;
case TokenType.ClosePar: // )
GetToken();
while (operStack.Count > 0 && operStack.Peek() != TokenType.OpenPar)
{
BuildTree();
}
//discard close parenthesis ) operator
operStack.Pop();
break;
default:
//operator
// AND OR == <> > >= < <=
if (currOper == TokenType.AND || currOper == TokenType.OR ||
currOper == TokenType.Equal || currOper == TokenType.NotEqual ||
currOper == TokenType.Greater || currOper == TokenType.GreaterOrEqual ||
currOper == TokenType.Less || currOper == TokenType.LessOrEqual)
{
GetToken();
var thisOper = CurrentToken.Token;
//build tree of all operator on stack with higher precedence than current
while (operStack.Count > 0 &&
Precedence(operStack.Peek()) < Precedence(thisOper))
{
BuildTree();
}
//
operStack.Push(thisOper);
}
else
{
throw new ArgumentException($"operator expected, and we got: {currOper}");
}
break;
}
}
}
//resolve left operator
while (operStack.Count > 0)
{
BuildTree();
}
if (stack.Count == 0)
{
return(null);
}
var item = stack.Pop();
if (item.ExpressionType != ExpressionItemType.Operator)
{
throw new ArgumentException("operator expected on expression");
}
return(item as ExpressionOperator);
}
int Precedence(TokenType token)
{
switch (token)
{
//case TokenType.NOT: // ~ BITWISE NOT
// return 1;
case TokenType.Astherisk: // * / %(modulus)
return(2);
case TokenType.Plus: // + - sign
case TokenType.Minus: // + addition, concatenation -substraction
return(3); // bitwise & AND, | OR |
case TokenType.Equal: //comparison operators
case TokenType.NotEqual:
case TokenType.Greater:
case TokenType.GreaterOrEqual:
case TokenType.Less:
case TokenType.LessOrEqual:
return(4);
case TokenType.NOT:
return(5);
case TokenType.AND:
return(6);
case TokenType.BETWEEN: //ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
case TokenType.IN:
case TokenType.LIKE:
case TokenType.OR:
return(7);
case TokenType.Assign:
return(8);
case TokenType.OpenPar:
//highest
return(16);
default:
return(0);
}
}
#region SELECT
if (!GetTokenIf(TokenType.SELECT))
{
throw new ArgumentException("SELECT expected");
}
//see if it's a function
if (GetTokenIfContains(SelectFunctions))
{
var function = CurrentToken.Token;
if (!GetTokenIf(TokenType.OpenPar))
{
throw new ArgumentException($"expected ( after FUNCTION {CurrentToken.Token}");
}
// COUNT([(i).](column))
var functionColumn = ReadColumnName(readAS: false);
if (!GetTokenIf(TokenType.ClosePar))
{
throw new ArgumentException($"expected ) closing {CurrentToken.Token}");
}
//alias
String functionColumnAlias = null;
if (GetTokenIf(TokenType.AS))
{
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"");
}
functionColumnAlias = CurrentToken.Value;
}
columnSelect = new ColumnsSelect(function, functionColumn, functionColumnAlias);
}
else
{
//preceded by TOP if any
//TOP integer PERCENT
if (GetTokenIf(TokenType.TOP))
{
if (!GetTokenIf(TokenType.Number))
{
throw new ArgumentException($"Number expected after TOP");
}
//test for integer
if (!int.TryParse(CurrentToken.Value, out top) || top <= 0)
{
throw new ArgumentException($"TOP [positive integer greater than 0] expected: {CurrentToken.Value}");
}
//PERCENT
if (GetTokenIf(TokenType.PERCENT))
{
//save if for later
}
}
//read columns
//read column selector: comma separated or *
if (GetTokenIf(TokenType.Astherisk))
{
columnSelect = new ColumnsSelect(top);
}
else
{
//mut have at least one column identifier
if (PeekToken() && peekedToken.Token != TokenType.Identifier)
{
throw new ArgumentException("table column name(s) expected");
}
//read first
columnCollection.Add(ReadColumnName());
while (GetTokenIf(TokenType.Comma))
{
//next
columnCollection.Add(ReadColumnName());
}
columnSelect = new ColumnsSelect(top, columnCollection);
}
}
#endregion
#region FROM
if (!GetTokenIf(TokenType.FROM))
{
throw new ArgumentException("FROM expected");
}
do
{
//read identifier: table name
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException("table name identifier after FROM expected");
}
var tableName = CurrentToken.Value;
String tableAlias = null;
//var pos = CurrentToken.Position;
// [table name] AS [alias]
if (GetTokenIf(TokenType.AS))
{
//create it so WHERE can handle it
//tableIdentifier = new DbQueryTableIdentifier(table, CurrentToken.Value);
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"Table alias expected after AS");
}
tableAlias = CurrentToken.Value;
}
//tableIdentifier = new DbQueryTableIdentifier(table);
var table = new Table(tableName, tableAlias);
if (!validator.HasTable(table.Name))
{
throw new ArgumentException($"Invalid table name: {table.Name}");
}
tableCollection.Add(table);
}while (GetTokenIf(TokenType.Comma));
if (columnSelect.FullColumns)
{
//fill SELECT * with all columns in FROM if applies
foreach (var col in tableCollection
.SelectMany(
table => validator.ColumnsOf(table.Name),
(table, col) => new Column(col, validator.ColumnMetadata(table.Name, col))))
{
columnSelect.Add(col);
}
}
else
{
//check all SELECT columns
foreach (var col in columnSelect.Columns)
{
string tableName = null;
if (col.HasTableAlias)
{
//find it in table collection
var table = tableCollection.FirstOrDefault(t => t.HasAlias && col.TableAlias == t.Alias);
if (table == null)
{
throw new ArgumentException($"column alias undefined {col}");
}
tableName = table.Name;
if (!validator.TableHasColumn(tableName, col.Name))
{
throw new ArgumentException($"column: {col.Name} could not be found in table {tableName}");
}
}
else
{
//brute force search
var tables = tableCollection.Where(t => validator.TableHasColumn(t.Name, col.Name)).ToList();
if (tables.Count != 1)
{
throw new ArgumentException($"column: {col.Name} could not be found in database or cannot resolve multiple tables");
}
tableName = tables[0].Name;
}
//link column to table, and find its index
col.Meta = validator.ColumnMetadata(tableName, col.Name);
}
}
#endregion
#region JOIN
if (GetTokenIfContains(JoinStarts))
{
//FROM table must has identifier, should be only one here
TokenType JoinCommand = CurrentToken.Token;
ComparisonOperator JoinExpression = null;
if (CurrentToken.Token == TokenType.LEFT || CurrentToken.Token == TokenType.RIGHT ||
CurrentToken.Token == TokenType.FULL)
{
//LEFT OUTER JOIN
//RIGHT OUTER JOIN
//FULL OUTER JOIN
if (!GetTokenIf(TokenType.OUTER))
{
throw new ArgumentException($"OUTER expected after {CurrentToken.Token}");
}
}
else if (!(CurrentToken.Token == TokenType.INNER || CurrentToken.Token == TokenType.CROSS))
{
//INNER JOIN
//CROSS JOIN
throw new ArgumentException($"invalid JOIN keyword {CurrentToken.Token}");
}
if (!GetTokenIf(TokenType.JOIN))
{
throw new ArgumentException($"JOIN expected after {CurrentToken.Token}");
}
//read table name
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException("table name identifier after FROM expected");
}
//check -table name
var tableName = CurrentToken.Value;
String tableAlias = null;
// + identifier
if (GetTokenIf(TokenType.AS))
{
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"Table alias expected after AS");
}
tableAlias = CurrentToken.Value;
}
joinTable = new Table(tableName, tableAlias);
if (!validator.HasTable(joinTable.Name))
{
throw new ArgumentException($"Invalid table name: {joinTable.Name}");
}
//read ON
if (!GetTokenIf(TokenType.ON))
{
throw new ArgumentException($"ON expected in JOIN query");
}
//read expression
//left & right operand must be from different tables
//read left
var left = ReadOperand(DbColumnType.None);
//read operator
if (!GetToken() || !CurrentToken.Token.IsOperator())
{
throw new ArgumentException("operator of JOIN expression expected");
}
var oper = CurrentToken;
//read right
var right = ReadOperand(DbColumnType.None);
//operands must be of type column with different table identifiers
if (!left.IsColumn ||
!right.IsColumn ||
((ColumnOperand)left).Column.TableAlias == ((ColumnOperand)right).Column.TableAlias)
{
throw new ArgumentException("JOIN query expression table identifiers cannot be the same");
}
JoinExpression = new ComparisonOperator(
left,
oper.Token,
right
);
join = new SqlJoin(JoinCommand, joinTable, JoinExpression);
//validate JOIN column tables
}
#endregion
#region WHERE if any
if (GetTokenIf(TokenType.WHERE))
{
if ((where = ReadExpression()) == null)
{
throw new ArgumentException("WHERE expression(s) expected");
}
}
#endregion
if (GetToken() && CurrentToken.Token != TokenType.SemiColon)
{
throw new ArgumentException($"Unexpected: {CurrentToken.Value} @ {CurrentToken.Position}");
}
return(new DbQuery(columnSelect, tableCollection, join, new ColumnsWhere(where)));
}
