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 TranslateGroupOrderByFieldName(int index, List<int> tableDeclarations, Dictionary<int, string> fieldAliases, Dictionary<string, string> tableAliases, Dictionary<string, List<string>> fieldList,QueryTokenizer tokenizer) { QueryToken field = tokenizer.Tokens[index]; string tableName = ""; string fieldName = tokenizer.Tokens[index].Value; string alias = ""; if (field.Value.Contains(".")) { alias = field.Value.Substring(0, field.Value.IndexOf(".")); tableName = field.Value.Substring(0, field.Value.IndexOf(".")); fieldName = fieldName.Substring(tableName.Length + 1); } else { if (tableDeclarations.Count == 1) { tableName = _tokenizer.Tokens[tableDeclarations[0]].Value; } } if (tableAliases.ContainsKey(tableName)) tableName = tableAliases[tableName]; string ret = field.Value; Type t = LocateTableType(tableName); if (t != null) { sTable map = _pool.Mapping[t]; if (fieldName.Contains(".")) { while (fieldName.Contains(".")) { PropertyInfo pi = t.GetProperty(fieldName.Substring(0, fieldName.IndexOf(".")), Utility._BINDING_FLAGS_WITH_INHERITANCE); map = _pool.Mapping[(pi.PropertyType.IsArray ? pi.PropertyType.GetElementType() : pi.PropertyType)]; fieldName = fieldName.Substring(fieldName.IndexOf(".") + 1); t = (pi.PropertyType.IsArray ? pi.PropertyType.GetElementType() : pi.PropertyType); } if (fieldName != "*") { if (map.GetRelationForProperty(fieldName).HasValue && !map.IsEnumProperty(fieldName)) { ret = ""; foreach (string str in fieldList[field.Value]) { ret += field.Value.Replace(".", "_") + "." + str + ", "; } } else ret = TranslateParentFieldName(field.Value.Substring(0, field.Value.LastIndexOf(".")).Replace(".", "_"),fieldName,t,-1); } else { ret = field.Value.Substring(0, field.Value.LastIndexOf(".")).Replace(".", "_") + ".*"; } } else { if (fieldName == "*") { ret = "*"; } else { if (map.GetRelationForProperty(fieldName).HasValue && !map.IsEnumProperty(fieldName)) { ret = ""; foreach (string str in fieldList[field.Value]) { ret += field.Value.Replace(".", "_") + "." + str + ", "; } } else ret = TranslateParentFieldName(alias,fieldName,t,-1); } } } return ret; }
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; }