public static IEnumerable <CriteriaSet> GenWhere(IEnumerable <Criteria> criteria)
{
CriteriaSet set = new CriteriaSet();
foreach (var c in criteria)
{
set.AddCriteria(c);
}
return(new[] { new CriteriaSet() });
}
/// <summary>
/// Given a SQL-like query, return a a Select object, ready for adding parameters and querying
/// </summary>
/// <param name="sql">SQL-like query</param>
/// <returns>Select object for adding parameters and executing</returns>
public static Select Parse(string sql)
{
string[] tokens = Utils.Tokenize(sql);
if (tokens.Length == 0 || (tokens.Length == 1 && string.IsNullOrWhiteSpace(tokens[0])))
{
throw new SqlException("No tokens", sql);
}
Select select = new Select();
SqlState state = SqlState.SELECT;
int idx = 0;
while (idx < tokens.Length)
{
string currentToken = tokens[idx];
if (state == SqlState.SELECT)
{
// Should start with SELECT
if (!currentToken.Equals("SELECT", StringComparison.OrdinalIgnoreCase))
{
throw new SqlException("No SELECT", sql);
}
// Slurp up the SELECT columns
select.select = new List <string>();
while (true)
{
++idx;
if (idx >= tokens.Length)
{
throw new SqlException("No SELECT columns", sql);
}
currentToken = tokens[idx];
bool lastColumn = !currentToken.EndsWith(",", StringComparison.Ordinal);
if (!lastColumn)
{
currentToken = currentToken.TrimEnd(',');
}
Utils.ValidateColumnName(currentToken, sql);
select.select.Add(currentToken);
if (lastColumn)
{
break;
}
}
++idx;
state = SqlState.FROM;
continue;
}
if (state == SqlState.FROM)
{
if (!currentToken.Equals("FROM", StringComparison.OrdinalIgnoreCase))
{
throw new SqlException("No FROM", sql);
}
++idx;
if (idx >= tokens.Length)
{
throw new SqlException("No FROM table", sql);
}
currentToken = tokens[idx];
Utils.ValidateTableName(currentToken, sql);
select.from = currentToken;
++idx;
state = SqlState.WHERE;
continue;
}
if (state == SqlState.WHERE)
{
if (!currentToken.Equals("WHERE", StringComparison.OrdinalIgnoreCase))
{
state = SqlState.ORDER;
continue;
}
// Gobble up WHERE criteria
CriteriaSet criteriaSet = new CriteriaSet();
select.where = new List <CriteriaSet> {
criteriaSet
};
++idx;
while ((idx + 3) <= tokens.Length)
{
var criteria =
new Criteria()
{
name = tokens[idx++],
op = tokens[idx++],
paramName = tokens[idx++]
};
Utils.ValidateColumnName(criteria.name, sql);
Utils.ValidateOperator(criteria.op, sql);
Utils.ValidateParameterName(criteria.paramName, sql);
criteriaSet.AddCriteria(criteria);
if
(
(idx + 3) <= tokens.Length
&&
tokens[idx].Equals("AND", StringComparison.OrdinalIgnoreCase)
)
{
++idx;
continue;
}
else
{
break;
}
}
if (criteriaSet.criteria.Count == 0)
{
throw new SqlException("No WHERE criteria", sql);
}
state = SqlState.ORDER;
continue;
}
if (state == SqlState.ORDER)
{
string nextToken = (idx + 1) < tokens.Length ? tokens[idx + 1] : "";
if
(
(idx + 3) > tokens.Length
||
!currentToken.Equals("ORDER", StringComparison.OrdinalIgnoreCase)
||
!nextToken.Equals("BY", StringComparison.OrdinalIgnoreCase)
)
{
state = SqlState.LIMIT;
continue;
}
idx += 2;
var orders = new List <Order>();
select.orderBy = orders;
while (idx < tokens.Length)
{
currentToken = tokens[idx];
bool currentEnds = idx == tokens.Length - 1 || currentToken.EndsWith(",", StringComparison.Ordinal);
nextToken = "ASC";
if (!currentEnds)
{
if ((idx + 1) < tokens.Length)
{
nextToken = tokens[++idx];
}
}
bool nextEnds = nextToken.EndsWith(",", StringComparison.Ordinal);
bool isLimit = nextToken.Equals("LIMIT", StringComparison.OrdinalIgnoreCase);
bool lastColumn = isLimit || !(currentEnds || nextEnds);
currentToken = currentToken.TrimEnd(',');
nextToken = nextToken.TrimEnd(',');
bool isDescending;
{
if (nextToken.Equals("ASC", StringComparison.OrdinalIgnoreCase))
{
isDescending = false;
}
else if (nextToken.Equals("DESC", StringComparison.OrdinalIgnoreCase))
{
isDescending = true;
}
else if (isLimit)
{
isDescending = false;
}
else
{
throw new SqlException("Invalid ORDER BY", sql);
}
}
Utils.ValidateColumnName(currentToken, sql);
var orderObj = new Order()
{
field = currentToken, descending = isDescending
};
orders.Add(orderObj);
if (!isLimit)
{
++idx;
}
if (lastColumn)
{
break;
}
}
state = SqlState.LIMIT;
continue;
}
if (state == SqlState.LIMIT)
{
if (currentToken.Equals("LIMIT", StringComparison.OrdinalIgnoreCase))
{
++idx;
if (idx >= tokens.Length)
{
throw new SqlException("No LIMIT value", sql);
}
currentToken = tokens[idx];
int limitVal;
if (!int.TryParse(currentToken, out limitVal))
{
throw new SqlException("Invalid LIMIT value", sql);
}
select.limit = limitVal;
++idx;
break;
}
else
{
throw new SqlException("Invalid final statement", sql);
}
}
throw new SqlException($"Invalid SQL parser state: {state}", sql);
}
if (idx < tokens.Length - 1)
{
throw new SqlException("Not all parsed", sql);
}
if (select.select.Count == 0)
{
throw new SqlException("No SELECT columns", sql);
}
if (string.IsNullOrWhiteSpace(select.from))
{
throw new SqlException("No FROM", sql);
}
return(select);
}