public bool Bind(QueryBuilder qbl) { this._queryObject = qbl; this._dataReader = qbl.getDatabaseObject().getResult(this._queryObject); ATable srcTable = qbl.getDatabaseObject().GetTableCache().getCachedTable(((ATable)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject).name); /* // remove redundant fields from the field list. if (srcTable.getFieldList().Count != this._queryObject.getFieldList().Count) { object[] test = utils.arrayIntersect(this._queryObject.getFieldList().ToArray(), srcTable.getFieldList().ToArray()); this.setFieldList(new System.Collections.ArrayList(test)); } else this.setFieldList(srcTable.getFieldList()); * */ this.setFieldList(this._queryObject.getFieldList()); this.name = srcTable.name; this.BindData(qbl.getFieldList()); return false; }
public override string buildInsertStatement(QueryBuilder qbl) { string tblname = ((string)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject); ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname); if (tblObj == null) throw (new Exception("Invalid table specified. Check query spelling and try again.")); string wSql = "insert into [" + tblname + "] "; string fieldList = ""; string valueList = ""; ABSTRACTDATATYPES fieldType; // If our chosen table object does not have an autoincrementing primary key/id field // we can check for that here. // Build up field list and value list. foreach (AField field in qbl.getFieldList()) { AField refField = qbl.getDatabaseObject().getTableObject(field.owner).getFieldByName(field.name); if (tblObj.getFieldByName(field.name) == null) { this._dbObj.GetTableCache().rebuildAll(); throw(new Exception("Unable to build insert statement. An invalid field was included with the query.")); } fieldList += "["+field.name+"]"; if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) fieldList += ","; fieldType = refField.type; if (fieldType == ABSTRACTDATATYPES.ABool) { string boolRaw = Convert.ToString(field.value).ToLower(); bool relFieldValue = (boolRaw == "true" || boolRaw == "1" || boolRaw == "yes") ? true : false; if (relFieldValue) { valueList += "1"; } else { valueList += "0"; } } else if (fieldType == ABSTRACTDATATYPES.AForeignTable) { // This is tricky, we have to look up the value type from the // referencing table's file. } else if ((fieldType == ABSTRACTDATATYPES.ASmallInteger) || (fieldType == ABSTRACTDATATYPES.ALargeInteger )) { valueList += field.value; } else if ((fieldType == ABSTRACTDATATYPES.AString) || (fieldType == ABSTRACTDATATYPES.AChar) || (fieldType == ABSTRACTDATATYPES.ADateTime)) { // convert mysql/csql style quoting to mssql style quoting //field.value = Convert.ToString(field.value).Replace("''", @"\'"); // if the value is longer than the allowed nvarchar, then do a CONVERT/cast to text instead. valueList += "'" + field.value + "'"; } else if (fieldType == ABSTRACTDATATYPES.AData) { valueList += field.value; } else { // If we get here, then the field type is possibly unsupported. // try to figure out what type of formatting to use. string sValue = field.value != null?Convert.ToString(field.value):""; if (sValue == "") { valueList += "''"; } else { if (sValue.Substring(0, 1) == "'") { // convert mysql/csql style quoting to mssql style quoting //field.value = Convert.ToString(field.value).Replace("''", @"\'"); valueList += "'" + field.value + "'"; } else { valueList += field.value; } } } if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) valueList += ","; } wSql += "("; wSql += fieldList; wSql += ") VALUES ("; wSql += valueList; wSql += ");"; return wSql; }
public override string buildUpdateStatement(QueryBuilder qbl) { bool skipped = false; string tblname = ((object)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject).ToString(); ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname); string wSql = "update " + tblname + " set "; foreach(AField field in qbl.getFieldList()) { skipped = false; if (!field.hasModifier(ABSTRACTFIELDMODIFIERS.PrimaryKey)) // Skip the primary key as we cannot update it. { if (tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.AString) { wSql += "[" + field.name + "]" + "='" + field.value + "'"; } else if (tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ABool) { if (Convert.ToString(field.value) != "" && field.value != null) if(Convert.ToString(field.value).ToLower()=="true") wSql += "[" + field.name + "]" + "=1"; else wSql += "[" + field.name + "]" + "=0"; else skipped = true; } else if ((tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ALargeInteger)||(tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ASmallInteger)) if (Convert.ToString(field.value) != "" && field.value != null) wSql += "[" + field.name + "]" + "=" + field.value; else skipped = true; else if ((tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ADateTime)) if (Convert.ToString(field.value) != "" && field.value != null) wSql += "[" + field.name + "]" + "=CONVERT(datetime, '" + field.value+"')"; else skipped = true; } else skipped = true; if ((Convert.ToString(field.value) != "" && field.value != null)||(tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.AString)) if(!skipped) if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) wSql += ", "; else wSql += " "; else wSql += " "; } // sometimes the algorithm skips... fix it here wSql = wSql.Trim(); if (wSql[wSql.Length - 1] == ',') { wSql = wSql.Substring(0, wSql.Length - 1); } if (wSql[wSql.Length - 1] != ' ') wSql += " "; if(qbl.conditionalString != null) if(qbl.conditionalString.Length > 2) wSql += "where " + qbl.conditionalString; return wSql; }
public override string buildUpdateStatement(QueryBuilder qbl) { string tblname = ((string)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject); ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname); string wSql = "update " + tblname + " set "; foreach (AField field in qbl.getFieldList()) { if (tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.AString) { wSql += field.name + "='" + field.value + "'"; } else { wSql += field.name + "=" + field.value; } if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) wSql += ", "; else wSql += " "; } wSql += "where " + qbl.conditionalString; return wSql; }
public override string buildInsertStatement(QueryBuilder qbl) { string tblname = ((string)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject); ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname); string wSql = "insert into " + tblname + " "; string fieldList = ""; string valueList = ""; ABSTRACTDATATYPES fieldType; // If our chosen table object does not have an autoincrementing primary key/id field // we can check for that here. // Build up field list and value list. foreach (AField field in qbl.getFieldList()) { fieldList += field.name; if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) fieldList += ","; fieldType = tblObj.getFieldByName(field.name).type; if (fieldType == ABSTRACTDATATYPES.ABool) { if (((string)field.value == "true") || ((bool)field.value == true) || ((int)field.value == 1) || ((string)field.value == "1") || ((string)field.value == "yes")) { valueList += "1"; } } else if (fieldType == ABSTRACTDATATYPES.AForeignTable) { // This is tricky, we have to look up the value type from the // referencing table's file. } else if (fieldType == ABSTRACTDATATYPES.ASmallInteger) { valueList += field.value; } else if (fieldType == ABSTRACTDATATYPES.AString) { valueList += "'" + field.value + "'"; } if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) valueList += ","; } wSql += "("; wSql += fieldList; wSql += ") VALUES ("; wSql += valueList; wSql += ");"; return wSql; }
public override string buildInsertStatement(QueryBuilder qbl) { string tblname = (Convert.ToString(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)); ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname); string wSql = "insert into " + tblname + " "; string fieldList = ""; string valueList = ""; ABSTRACTDATATYPES fieldType; // If our chosen table object does not have an autoincrementing primary key/id field // we can check for that here. // Build up field list and value list. foreach (AField field in qbl.getFieldList()) { fieldList += field.name; if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) fieldList += ","; fieldType = tblObj.getFieldByName(field.name).type; if (fieldType == ABSTRACTDATATYPES.ABool) { if (((string)field.value == "true") || ((bool)field.value == true) || ((int)field.value == 1) || ((string)field.value == "1") || ((string)field.value == "yes")) { valueList += "1"; } } else if (fieldType == ABSTRACTDATATYPES.AForeignTable) { // This is tricky, we have to look up the value type from the // referencing table's file. } else if (fieldType == ABSTRACTDATATYPES.ASmallInteger) { // Deal with blank values and default val... if (field.defaultval == null) { if (field.hasModifier(ABSTRACTFIELDMODIFIERS.NotNull)) field.defaultval = 0; else field.defaultval = "NULL"; } if (field.value == null || Convert.ToString(field.value) == "") field.value = field.defaultval; valueList += field.value; } else if (fieldType == ABSTRACTDATATYPES.AString) { valueList += "'" + field.value + "'"; } else if (fieldType == ABSTRACTDATATYPES.ADateTime) { if (Convert.ToString(field.value).ToLower().Trim() == "now()") { valueList += field.value; } else { valueList += "'" + field.value + "'"; } } else { if (field.value == null || Convert.ToString(field.value)== "") field.value = field.defaultval; valueList += field.value; } if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) valueList += ","; } wSql += "("; wSql += fieldList; wSql += ") VALUES ("; wSql += valueList; wSql += ");"; return wSql; }
static void selectQueryHandler(ref QueryBuilder newQuery, string sqlStr) { string[] tokenList = new string[] { "select", "from", "inner join", "where", "group by", "order by", "limit" }; string tmp = ""; for(int i=0;i<=tokenList.Length-1;i++) { tmp = tokenList[i]; sqlStr = sqlStr.Replace(tmp, "[" + tmp + "]"); tokenList[i]="["+tmp+"]"; } System.Collections.ArrayList tokenised = new System.Collections.ArrayList(); newQuery.setType(ABSTRACTQUERYTYPES.SelectQuery); newQuery.QueryLimit.lStart = -1; newQuery.QueryLimit.lLimit = -1; // build the token list tokenised = SQLConsole.Data.utils.tokenizeList(sqlStr, tokenList); foreach (tokenItem tokenitem in tokenised) { string tokenString = tokenitem.tokenValue; string comString = tokenitem.tokenName; string paramString = tokenString.Substring(tokenString.IndexOf(tokenitem.tokenName) + tokenitem.tokenName.Length + 1).Trim(); if(paramString.Length > 10) if (paramString.Trim().ToLower().Substring(0, "distinct".Length) == "distinct") { newQuery.distinct = true; paramString = paramString.Trim().Substring("distinct ".Length); } //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.Trim(); 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 { if (realname == "*") { // deal with this seperately. newQuery.addField(new AField(realname, null)); } else { // Work out what source this field belongs to... string pureName = realname.Substring(realname.IndexOf(".") + 1); string[] sources = {}; for (int i = 0; i < tokenised.Count; i++) { if (((tokenItem)tokenised[i]).tokenName == "from") { string sourcesList = ((tokenItem)tokenised[i]).tokenValue; sourcesList = sourcesList.Substring(sourcesList.IndexOf(((tokenItem)tokenised[i]).tokenName) + ((tokenItem)tokenised[i]).tokenName.Length + 1); sourcesList.Trim(); string[] sources2 = sourcesList.Split(','); sources = (string[])utils.ConcatenateArrays(sources, sources2); } else if (((tokenItem)tokenised[i]).tokenName == "inner join") { string sourcesList = ((tokenItem)tokenised[i]).tokenValue; sourcesList = sourcesList.Substring(sourcesList.IndexOf(((tokenItem)tokenised[i]).tokenName) + ((tokenItem)tokenised[i]).tokenName.Length + 1); sourcesList = sourcesList.Substring(0, sourcesList.IndexOf(" on ")); sourcesList.Trim(); string[] sources2 = { sourcesList }; sources = (string[])utils.ConcatenateArrays(sources, sources2); } else if (((tokenItem)tokenised[i]).tokenName == "outer join") { string sourcesList = ((tokenItem)tokenised[i]).tokenValue; sourcesList = sourcesList.Substring(sourcesList.IndexOf(((tokenItem)tokenised[i]).tokenName) + ((tokenItem)tokenised[i]).tokenName.Length + 1); sourcesList = sourcesList.Substring(0, sourcesList.IndexOf(" on ")); sourcesList.Trim(); string[] sources2 = { sourcesList }; sources = (string[])utils.ConcatenateArrays(sources, sources2); } else if (((tokenItem)tokenised[i]).tokenName == "join") { string sourcesList = ((tokenItem)tokenised[i]).tokenValue; sourcesList = sourcesList.Substring(sourcesList.IndexOf(((tokenItem)tokenised[i]).tokenName) + ((tokenItem)tokenised[i]).tokenName.Length + 1); if(sourcesList.IndexOf(" on ") > 0) sourcesList = sourcesList.Substring(0, sourcesList.IndexOf(" on ")); sourcesList.Trim(); string[] sources2 = { sourcesList }; sources = (string[])utils.ConcatenateArrays(sources, sources2); } } AField newField = null; foreach (string source in sources) { ATable src = null; if (source.IndexOf(" ") > 0) src = newQuery.getDatabaseObject().getTableObject(source.Substring(0, source.IndexOf(" "))); else src = newQuery.getDatabaseObject().getTableObject(source); if (src == null) throw (new Exception("Bad table name exception")); if (src.getFieldByName(pureName) != null) { if (newField != null) throw (new Exception("Ambiguous field name: " + realname + ". Field could belong to more than one source table.")); newField = src.getFieldByName(pureName); if (source.IndexOf(" ") > 0) { newField.owner = source.Substring(source.IndexOf(" ")+1); } } } if (newField == null) throw (new Exception("Unable to determine source for field: " + realname)); newQuery.addField(newField); //newQuery.addField(new AField(realname, null, (ATable)((QueryBuilder.SOURCEBINDING)newQuery.getSourceList()[0]).sourceObject)); //newQuery.addField(new AField(realname,null,"")); } } 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 "distinct": newQuery.distinct = true; 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 "join": joinList = new System.Collections.ArrayList(); paramString = "join " + paramString; if (paramString.ToLower().IndexOf("join", "join".Length + 1) >= 2) { while (paramString.Length >= 0) { string tmp2 = paramString; if (paramString.ToLower().IndexOf("join", "join".Length + 1) >= 2) tmp2 = paramString.ToLower().Substring(0, paramString.ToLower().IndexOf("join", "join".Length + 2)).Trim(); else { joinList.Add(tmp2); break; } joinList.Add(tmp2); paramString = paramString.ToLower().Substring(paramString.ToLower().IndexOf("join", "join".Length + 2)).Trim(); } } else { joinList.Add(paramString); } foreach (string tmpstr in joinList) { paramString = tmpstr.Substring("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 */ string fname = null; string fOrderType = null; if (ff.IndexOf(' ') > 0) { ff.Trim(); fname = ff.Substring(0, ff.IndexOf(' ')); fOrderType = ff.Substring(ff.IndexOf(' ') + 1); } else fname = ff; if (newQuery.getField(fname) == null) { if (fname.IndexOf(".") > 0) { string tblName = ((ATable)newQuery.getSourceTableByAlias(fname.Substring(0, fname.IndexOf(".")))).name; AField fld = newQuery.getDatabaseObject().getTableObject(tblName).getFieldByName((fname.Substring(fname.IndexOf(".") + 1))); fld.owner = fname.Substring(0, fname.IndexOf(".")); newQuery.addField(fld); } else newQuery.addField(newQuery.getDatabaseObject().GetTableCache().getCachedTable(((ATable)((QueryBuilder.SOURCEBINDING)newQuery.getSourceList()[0]).sourceObject).name).getFieldByName(fname)); } if(fOrderType != null) { if (fOrderType.ToLower() == "asc") newQuery.getField(fname).OrderBy = ABSTRACTORDERTYPE.Ascending; else newQuery.getField(fname).OrderBy = ABSTRACTORDERTYPE.Descending; } else { //default to order by asc if (fname.IndexOf(".")>0) { fname = fname.Substring(fname.IndexOf(".")+1); } if (fname == newQuery.getField(fname).name) { newQuery.getField(fname).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; } } }