static void selectQueryHandler(ref QueryBuilder newQuery, string sqlStr) { string[] tokenList = new string[] { "select", "from", "inner join", "where", "group by", "order by", "limit", "nullterminator" }; string tmp = ""; System.Collections.ArrayList tokenised = new System.Collections.ArrayList(); System.Collections.Hashtable tokenized = new System.Collections.Hashtable(); newQuery.setType(ABSTRACTQUERYTYPES.SelectQuery); newQuery.QueryLimit.lStart = -1; newQuery.QueryLimit.lLimit = -1; for(int i=0;i<tokenList.Length;i++) { if(sqlStr.ToLower().IndexOf(tokenList[i]) >= 0) { // locate next match int nextmatch = -1; for (int x = i+1; x < tokenList.Length; x++) { if (sqlStr.ToLower().IndexOf(tokenList[x]) >= 0) { nextmatch = x; break; } } if (nextmatch == -1) { tmp = sqlStr.Substring(sqlStr.ToLower().IndexOf(tokenList[i])); } else { if (sqlStr.ToLower().IndexOf(tokenList[nextmatch]) >= 0) tmp = sqlStr.Substring(sqlStr.ToLower().IndexOf(tokenList[i]), (sqlStr.ToLower().IndexOf(tokenList[nextmatch])-sqlStr.ToLower().IndexOf(tokenList[i]))); } // perform a quick check on the last token to see if it has another // embedded instance of the current token... if so perform chopping // and add each instance into the tokenized list. /* if (tmp.ToLower().IndexOf(tokenList[i], tokenList[i].Length + 1) >= 2) { while (tmp.Length >= 0) { string tmp2 = null; if (tmp.ToLower().IndexOf(tokenList[i], tokenList[i].Length + 1) >= 2) tmp2 = tmp.ToLower().Substring(0, tmp.IndexOf(tokenList[i], tokenList[i].Length + 2)).Trim(); else { tokenized.Add(tokenList[i], tmp); break; } tokenized.Add(tokenList[i], tmp2); tmp = tmp.ToLower().Substring(tmp.IndexOf(tokenList[i], tokenList[i].Length + 2)).Trim(); } } else { * */ tokenized.Add(tokenList[i], tmp.Trim()); // } //tokenised.Add(tmp); // tokenized.Add(tokenList[i], tmp.Trim()); } } foreach (string tokenkey in tokenized.Keys) { string tokenString = (string)tokenized[tokenkey]; string comString = tokenkey; string paramString = tokenString.Substring(tokenString.IndexOf(tokenkey)+tokenkey.Length+1).Trim(); //string comString = tokenString.Substring(0, tokenString.IndexOf(" ")).Trim(); //string paramString = tokenString.Substring(tokenString.IndexOf(" ") + 1).Trim(); string tableAlias = ""; string tablename = ""; string[] fieldList; switch (comString) { case "select": string[] fieldnames = paramString.Split(','); string realname = ""; string agg; ABSTRACTAGGREGATE aggMode = ABSTRACTAGGREGATE.None; foreach (string field in fieldnames) { realname = field; if (field.IndexOf(")") > 0) { realname = field.Substring(field.IndexOf("(") + 1, field.IndexOf(")") - field.IndexOf("(") - 1); realname = realname.Trim(); agg = field.Substring(0, field.IndexOf("(") - 1); switch (agg) { case "count": aggMode = ABSTRACTAGGREGATE.Count; break; case "max": aggMode = ABSTRACTAGGREGATE.Max; break; case "min": aggMode = ABSTRACTAGGREGATE.Min; break; } } if (realname.IndexOf(".") > 0) newQuery.addField(new AField(realname.Substring(realname.IndexOf(".") + 1), null, realname.Substring(0, realname.IndexOf(".")))); else newQuery.addField(new AField(realname, null, (ATable)((QueryBuilder.SOURCEBINDING)newQuery.getSourceList()[0]).sourceObject)); if (realname.IndexOf(".") > 0) newQuery.getField(realname.Substring(realname.IndexOf(".") + 1), realname.Substring(0, realname.IndexOf("."))).function = aggMode; else newQuery.getField(realname).function = aggMode; } break; case "from": if (paramString.IndexOf(' ') != -1) { tableAlias = paramString.Substring(paramString.IndexOf(' ') + 1); tablename = paramString.Substring(0, paramString.IndexOf(' ')); } else tablename = paramString.Trim(); newQuery.addSource(new ATable(tablename, tableAlias)); break; case "inner join": System.Collections.ArrayList joinList = new System.Collections.ArrayList(); paramString = "inner join " + paramString; if (paramString.ToLower().IndexOf("inner join", "inner join".Length + 1) >= 2) { while (paramString.Length >= 0) { string tmp2 = paramString; if (paramString.ToLower().IndexOf("inner join", "inner join".Length + 1) >= 2) tmp2 = paramString.ToLower().Substring(0, paramString.ToLower().IndexOf("inner join", "inner join".Length + 2)).Trim(); else { joinList.Add(tmp2); break; } joinList.Add(tmp2); paramString = paramString.ToLower().Substring(paramString.ToLower().IndexOf("inner join", "inner join".Length + 2)).Trim(); } } else { joinList.Add(paramString); } foreach (string tmpstr in joinList) { paramString = tmpstr.Substring("inner join".Length+1); // inner join anothertable on sjlieglij=sliejgil string lTmp = paramString.Substring(0, paramString.IndexOf(" on ")); if (lTmp.IndexOf(' ') != -1) { tableAlias = lTmp.Substring(lTmp.IndexOf(' ')).Trim(); tablename = lTmp.Substring(0, lTmp.IndexOf(' ')).Trim(); } else tablename = lTmp.Trim(); lTmp = paramString.Substring(paramString.IndexOf(" on") + " on".Length + 1); string srcTable = ""; string dstTable = ""; string srcField = ""; string dstField = ""; srcTable = lTmp.Substring(0, lTmp.IndexOf('=')); dstTable = lTmp.Substring(lTmp.IndexOf('=') + 1); if (srcTable.IndexOf('.') > 0) { // Assume specifed table. srcField = srcTable.Substring(srcTable.IndexOf(".") + 1); srcTable = srcTable.Substring(0, srcTable.IndexOf(".")); } if (dstTable.IndexOf('.') > 0) { // Assume specifed table. dstField = dstTable.Substring(dstTable.IndexOf(".") + 1); dstTable = dstTable.Substring(0, dstTable.IndexOf(".")); } newQuery.addSource(new ATable(tablename, tableAlias), ABSTRACTSOURCEBINDTYPES.INNERJOIN, ((QueryBuilder.SOURCEBINDING)newQuery.getSourceList()[newQuery.getSourceList().Count - 1]).sourceObject, srcField, dstField); } //newQuery.addSource(new ATable(tablename, tableAlias), ABSTRACTSOURCEBINDTYPES.INNERJOIN, null, lTmp.Substring(0, lTmp.IndexOf('=')), lTmp.Substring(lTmp.IndexOf('=')+1)); break; case "where": // where b.id = sjilegj, b.Name = fjff // where b.id = silegjlsg and newQuery.conditionalString = paramString; break; case "group by": fieldList = paramString.Split(','); foreach(string ff in fieldList) { newQuery.getField(ff).GroupBy = true; } break; case "order by": fieldList = paramString.Split(','); foreach (string ff in fieldList) { /* patch... bugfix: orderby where the field is not in the select field list * ie: select * from [table] order by field */ if (newQuery.getField(ff) == null) { newQuery.addField(newQuery.getDatabaseObject().GetTableCache().getCachedTable(((ATable)((QueryBuilder.SOURCEBINDING)newQuery.getSourceList()[0]).sourceObject).name).getFieldByName(ff)); } if(ff.IndexOf(' ') > 0) { ff.Trim(); string fname = ff.Substring(0, ff.IndexOf(' ')); string fOrderType = ff.Substring(ff.IndexOf(' ') + 1); if (fOrderType.ToLower() == "asc") newQuery.getField(fname).OrderBy = ABSTRACTORDERTYPE.Ascending; else newQuery.getField(fname).OrderBy = ABSTRACTORDERTYPE.Descending; } else { //default to order by asc if (ff == newQuery.getField(ff).name) { newQuery.getField(ff).OrderBy = ABSTRACTORDERTYPE.Ascending; } } } break; case "limit": newQuery.QueryLimit.lStart = 0; if (paramString.IndexOf(',') > 0) { // format limit 0,5 // format limit offset, count newQuery.QueryLimit.lStart = Convert.ToInt32(paramString.Substring(0, paramString.IndexOf(','))); newQuery.QueryLimit.lLimit = Convert.ToInt32(paramString.Substring(paramString.IndexOf(',') + 1)); } else newQuery.QueryLimit.lLimit = Convert.ToInt32(paramString); break; } } }