public void NewQuery(string NameSpace, string query)
{
_outputQuery = "";
_namespace = NameSpace;
if (query.StartsWith("(") && query.EndsWith(")"))
query = query.TrimStart('(').TrimEnd(')');
query = query.Trim();
_requiredTypes = new Queue<Type>();
_tokenizer = new QueryTokenizer(query);
_tokenizer.parse();
_tableFieldCounts = new Dictionary<int, int>();
_fieldNames = new Dictionary<int, string>();
_tableFields = new Dictionary<string, Type>();
_enumFields = new Dictionary<int, Type>();
_subQueryIndexes = new Dictionary<int, int>();
if (_conn != null)
{
try
{
_conn.Close();
}
catch (Exception e)
{
}
}
Translate();
_outputQuery = _outputQuery.Trim();
int index = 0;
while (_regTrueFalse.IsMatch(_outputQuery, index))
{
Match m = _regTrueFalse.Match(_outputQuery, index);
_outputQuery = _outputQuery.Substring(0, m.Groups[2].Index) + (m.Groups[2].Value.ToLower() == "true" ? _pool.TrueString : _pool.FalseString) + _outputQuery.Substring(m.Groups[2].Index + m.Groups[2].Length);
index = (m.Index + m.Length>_outputQuery.Length ? _outputQuery.Length-1 : m.Index+m.Length);
}
for (int x = 0; x < _fieldNames.Count; x++)
{
if (!_fieldNames.ContainsKey(x))
{
_fieldNames.Add(x, _fieldNames[x + 1]);
_fieldNames.Remove(x + 1);
}
_fieldNames[x] = _fieldNames[x].Trim("'\"".ToCharArray());
}
Logger.LogLine("Class Query: " + query + "\nTranslated To:" + _outputQuery);
}
private string TranslateSubQuery(int i,Dictionary<string,string> parentTableAliases, ref List<IDbDataParameter> parameters,ref Dictionary<int,string> translations)
{
List<int> tableDeclarations = new List<int>();
Dictionary<int, string> fieldAliases = new Dictionary<int, string>();
Dictionary<string, string> tableAliases = new Dictionary<string, string>();
List<int> fieldIndexes = new List<int>();
List<int> joinConditionIndexes = new List<int>();
Dictionary<int, int> conditionIndexes=new Dictionary<int, int>();
int x = 1;
int whereIndex=-1;
int fromIndex = -1;
while (
(x < _subQueryIndexes[i]) &&
(_tokenizer.Tokens[i + x].Value.ToUpper() != "FROM")
)
{
if (_subQueryIndexes.ContainsKey(i + x)){
x += _subQueryIndexes[i + x];
if ((x > _subQueryIndexes[i]) ||
(_tokenizer.Tokens[i + x].Value.ToUpper() == "FROM"))
break;
}
if (((_tokenizer.Tokens[i + x].Type == TokenType.KEY)||
(_tokenizer.Tokens[i + x].Type == TokenType.NUMBER)||
(_tokenizer.Tokens[i+x].Value.ToUpper()=="NULL")) &&
(
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "SELECT") ||
(_tokenizer.Tokens[i+x-1].Value.ToUpper()=="DISTINCT") ||
(_tokenizer.Tokens[i + x - 1].Value == ",") ||
(_tokenizer.Tokens[i + x - 1].Value == "(") ||
((_tokenizer.Tokens[i + x + 1].Value.ToUpper() == "AS") && (_tokenizer.Tokens[i + x + 1].Type == TokenType.KEYWORD)) ||
(_tokenizer.Tokens[i+x+1].Value.ToUpper()=="IS" && _tokenizer.Tokens[i+x+2].Value.ToUpper()=="NULL" && (
_tokenizer.Tokens[i+x-1].Value.ToUpper()=="AND"||
_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "OR"
))||
(_tokenizer.Tokens[i + x + 1].Type == TokenType.OPERATOR) ||
(_tokenizer.Tokens[i + x - 1].Type == TokenType.OPERATOR) ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "WHEN") ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "THEN") ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "ELSE")
))
{
fieldIndexes.Add(i + x);
if ((_tokenizer.Tokens[i + x + 1].Value.ToUpper() == "AS") && (_tokenizer.Tokens[i + x + 1].Type == TokenType.KEYWORD))
{
fieldAliases.Add(i + x, _tokenizer.Tokens[i + x + 2].Value);
}
}
x++;
}
if (_tokenizer.Tokens[i + x].Value.ToUpper() == "FROM")
{
fromIndex = i + x;
while (x < _subQueryIndexes[i])
{
if (_subQueryIndexes.ContainsKey(i + x))
x += _subQueryIndexes[i + x];
if (((_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "FROM") || (_tokenizer.Tokens[i + x - 1].Value == ",") || _tokenizer.Tokens[i + x - 1].Value.ToUpper() == "JOIN") && (_tokenizer.Tokens[i + x].Value != "("))
{
if (i + x + 1 < _tokenizer.Tokens.Count)
{
if ((_tokenizer.Tokens[i + x + 1].Value.ToUpper() != ",") && (_tokenizer.Tokens[i + x + 1].Value != "(") && (_tokenizer.Tokens[i + x + 1].Value.ToUpper() != "WHERE") && (_tokenizer.Tokens[i + x + 1].Value.ToUpper() != "GROUP") && (_tokenizer.Tokens[i + x + 1].Value.ToUpper() != "ORDER"))
{
if (_tokenizer.Tokens[i + x + 1].Value.ToUpper() == "AS")
{
if (!tableAliases.ContainsKey(_tokenizer.Tokens[i + x + 2].Value))
tableAliases.Add(_tokenizer.Tokens[i + x + 2].Value, _tokenizer.Tokens[i + x].Value);
}
else
{
if (!tableAliases.ContainsKey(_tokenizer.Tokens[i + x + 1].Value))
tableAliases.Add(_tokenizer.Tokens[i + x + 1].Value, _tokenizer.Tokens[i + x].Value);
}
}
}
tableDeclarations.Add(i + x);
}
else if (_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "ON")
{
while (x < _subQueryIndexes[i])
{
if (_subQueryIndexes.ContainsKey(i + x))
x += _subQueryIndexes[i + x];
if (_tokenizer.Tokens[i + x].Value.ToUpper() == "WHERE" || _tokenizer.Tokens[i + x].Value.ToUpper() == "UNION" || _tokenizer.Tokens[i + x].Value.ToUpper() == "ORDER" || _tokenizer.Tokens[i + x].Value.ToUpper() == "GROUP" || _tokenizer.Tokens[i + x].Value == "," || _tokenizer.Tokens[i + x].Value.ToUpper() == "INNER" || _tokenizer.Tokens[i + x].Value.ToUpper() == "OUTER" || _tokenizer.Tokens[i + x].Value.ToUpper() == "LEFT" || _tokenizer.Tokens[i + x].Value.ToUpper() == "RIGHT")
break;
else if (x + i + 1 == _tokenizer.Tokens.Count)
{
if (_tokenizer.Tokens[i + x - 1].Type == TokenType.OPERATOR)
joinConditionIndexes.Add(i + x);
break;
}
else if ((_tokenizer.Tokens[i + x].Type == TokenType.KEY) &&
(
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "ON") ||
(_tokenizer.Tokens[i + x - 1].Value == ",") ||
(_tokenizer.Tokens[i + x - 1].Value == "(") ||
(_tokenizer.Tokens[i + x + 1].Type == TokenType.OPERATOR) ||
(_tokenizer.Tokens[i + x - 1].Type == TokenType.OPERATOR) ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "WHEN") ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "THEN") ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "ELSE") ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "AND") ||
(_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "OR")
))
{
joinConditionIndexes.Add(i + x);
}
x++;
}
x--;
}
else if (x >= _subQueryIndexes[i])
break;
else if (_tokenizer.Tokens[i + x].Value.ToUpper() == "WHERE" || _tokenizer.Tokens[i + x].Value.ToUpper() == "ORDER" || _tokenizer.Tokens[i + x].Value.ToUpper() == "GROUP" || _tokenizer.Tokens[i + x].Value.ToUpper() == "UNION")
break;
x++;
}
}
if (x < _subQueryIndexes[i] && (_tokenizer.Tokens[i + x].Value.ToUpper() == "WHERE" || _tokenizer.Tokens[i + x].Value.ToUpper() == "ORDER" || _tokenizer.Tokens[i + x].Value.ToUpper() == "GROUP"))
{
whereIndex=x+i;
while (x < _subQueryIndexes[i])
{
if (_subQueryIndexes.ContainsKey(i + x))
x += _subQueryIndexes[i + x] + 2;
if ((_tokenizer.Tokens[i + x].Value.ToUpper() == "GROUP") ||
(_tokenizer.Tokens[i + x].Value.ToUpper() == "ORDER"))
break;
if ((_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "WHERE")
|| (_tokenizer.Tokens[i + x - 1].Value == "(")
|| (_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "OR")
|| (_tokenizer.Tokens[i + x - 1].Value.ToUpper() == "AND")
|| ((_tokenizer.Tokens[i+x-1].Value.ToUpper()=="NOT")
&&(
(_tokenizer.Tokens[i + x - 2].Value.ToUpper() == "WHERE")
|| (_tokenizer.Tokens[i + x - 2].Value.ToUpper() == "OR")
|| (_tokenizer.Tokens[i + x - 2].Value.ToUpper() == "AND")
)
)
){
if ((_tokenizer.Tokens[i + x].Value != "(") && (_tokenizer.Tokens[i+x].Value.ToUpper()!="NOT"))
{
int start = i + x;
x++;
while (x < _subQueryIndexes[i])
{
if (_subQueryIndexes.ContainsKey(i + x + 1))
x += _subQueryIndexes[i + x + 1];
if ((_tokenizer.Tokens[i + x].Value.ToUpper() == "OR")
|| (_tokenizer.Tokens[i + x].Value.ToUpper() == "AND")
|| (_tokenizer.Tokens[i + x].Value.ToUpper() == "GROUP")
|| (_tokenizer.Tokens[i + x].Value.ToUpper() == "ORDER"))
{
break;
}
x++;
}
conditionIndexes.Add(start, i + x);
}
}
if ((x < _subQueryIndexes[i])&&
(
(_tokenizer.Tokens[i + x].Value.ToUpper() == "GROUP") ||
(_tokenizer.Tokens[i + x].Value.ToUpper() == "ORDER")
))
break;
x++;
}
}
int endIndex = i + _subQueryIndexes[i];
foreach (int subIndex in Utility.SortDictionaryKeys(_subQueryIndexes.Keys))
{
if (subIndex > i && subIndex <= endIndex)
{
if (!translations.ContainsKey(subIndex))
{
if (parentTableAliases.Count != 0)
{
Dictionary<string, string> newList = new Dictionary<string, string>();
foreach (string str in parentTableAliases.Keys)
newList.Add(str, parentTableAliases[str]);
foreach (string str in tableAliases.Keys)
{
if (newList.ContainsKey(str))
newList.Remove(str);
newList.Add(str, tableAliases[str]);
}
string subquery = TranslateSubQuery(subIndex, newList, ref parameters, ref translations);
if (!translations.ContainsKey(subIndex))
translations.Add(subIndex, subquery);
}
else
{
string subquery = TranslateSubQuery(subIndex, tableAliases, ref parameters, ref translations);
if (!translations.ContainsKey(subIndex))
translations.Add(subIndex, subquery);
}
}
}
}
List<int> whereFieldIndexes = new List<int>();
foreach (int index in Utility.SortDictionaryKeys(conditionIndexes.Keys))
{
whereFieldIndexes.AddRange(ExtractFieldsFromCondition(index, conditionIndexes[index]));
}
Dictionary<string, List<string>> fieldList = new Dictionary<string, List<string>>();
string tables = CreateTableQuery(i,fromIndex,tableDeclarations, fieldIndexes,ref fieldList,whereFieldIndexes,joinConditionIndexes,tableAliases,parentTableAliases);
string fields = TranslateFields(i,fieldIndexes, tableDeclarations, fieldAliases, tableAliases,fieldList,parentTableAliases);
string wheres = TranslateWhereConditions(whereIndex,conditionIndexes,tableAliases,tableDeclarations,fieldList,parentTableAliases);
if ((whereFieldIndexes.Count == 0) && (whereIndex > 0) && (whereIndex < _subQueryIndexes[i]))
{
wheres = " ";
while (x < _subQueryIndexes[i])
{
wheres += _tokenizer.Tokens[x + i].Value + " ";
x++;
}
}
else if (x < _subQueryIndexes[i])
{
if (wheres == "")
wheres = " ";
while (x < _subQueryIndexes[i])
{
wheres += _tokenizer.Tokens[x + i].Value + " ";
x++;
}
}
string ending = "";
if (wheres.ToUpper().Contains(" GROUP BY ") || wheres.ToUpper().Contains(" ORDER BY "))
{
int endingStart = wheres.ToUpper().IndexOf(" GROUP BY ");
if ((endingStart==-1)||(wheres.ToUpper().IndexOf(" ORDER BY ")>endingStart))
endingStart = wheres.ToUpper().IndexOf(" ORDER BY ");
ending = wheres.Substring(endingStart);
wheres = wheres.Substring(0, endingStart);
QueryTokenizer qt = new QueryTokenizer(ending.Trim());
qt.parse();
if (qt.Tokens.Count > 0)
wheres += " " + qt.Tokens[0].Value+" ";
for (int y = 1; y < qt.Tokens.Count; y++)
{
if (qt.Tokens[y].Value.ToUpper() == "ASC" ||
qt.Tokens[y].Value.ToUpper() == "DESC" ||
qt.Tokens[y].Value.ToUpper() == "ASCENDING" ||
qt.Tokens[y].Value.ToUpper() == "DESCENDING"){
wheres += qt.Tokens[y].Value + " ";
}else if (
(qt.Tokens[y - 1].Value == ",") ||
(qt.Tokens[y - 1].Value == "(") ||
(qt.Tokens[y - 1].Value.ToUpper() == "BY") ||
((y+1<qt.Tokens.Count)&&(qt.Tokens[y + 1].Type == TokenType.OPERATOR)) ||
(qt.Tokens[y - 1].Type == TokenType.OPERATOR) ||
(qt.Tokens[y - 1].Value.ToUpper() == "WHEN") ||
(qt.Tokens[y - 1].Value.ToUpper() == "THEN") ||
(qt.Tokens[y - 1].Value.ToUpper() == "ELSE")
)
{
wheres+=TranslateGroupOrderByFieldName(y, tableDeclarations, fieldAliases, tableAliases, fieldList, qt)+" ";
}
else
wheres += qt.Tokens[y].Value+ " ";
}
}
return fields+" FROM " + tables+wheres;
}