// TODO: Create Command interface
public object executeStatement(string strSql)
{
object objReturn = null;
if (strSql.ToUpper().ContainsOutsideOfQuotes("GROUP BY"))
{
throw new NotImplementedException("GROUP BY clauses are not yet supported in MAX calls.");
}
int intParenIndex = strSql.IndexOf('(');
string maxlessSql = strSql.Substring(intParenIndex + 1);
string strFieldName = maxlessSql.Substring(0, maxlessSql.IndexOf(')')).ScrubValue();
maxlessSql = "SELECT " + strFieldName + " " + maxlessSql.Substring(maxlessSql.IndexOf(')') + 1).Trim();
SelectCommand selectCommand = new SelectCommand(_database);
object objTable = (DataTable)selectCommand.executeStatement(maxlessSql);
if (null != objTable)
{
DataTable table = (DataTable)objTable;
if (table.Rows.Count > 0)
{
table.DefaultView.Sort = strFieldName + " DESC";
table = table.DefaultView.ToTable();
objReturn = table.Rows[0][strFieldName];
}
}
return objReturn;
}
// TODO: Create Command interface
public object executeStatement(string strSql)
{
object objReturn = null;
if (strSql.ToUpper().ContainsOutsideOfQuotes("GROUP BY"))
{
throw new NotImplementedException("GROUP BY clauses are not yet supported in MAX calls.");
}
int intParenIndex = strSql.IndexOf('(');
string maxlessSql = strSql.Substring(intParenIndex + 1);
string strFieldName = maxlessSql.Substring(0, maxlessSql.IndexOf(')')).ScrubValue();
maxlessSql = "SELECT " + strFieldName + " " + maxlessSql.Substring(maxlessSql.IndexOf(')') + 1).Trim();
SelectCommand selectCommand = new SelectCommand(_database);
object objTable = (DataTable)selectCommand.executeStatement(maxlessSql);
if (null != objTable)
{
DataTable table = (DataTable)objTable;
if (table.Rows.Count > 0)
{
table.DefaultView.Sort = strFieldName + " DESC";
table = table.DefaultView.ToTable();
objReturn = table.Rows[0][strFieldName];
}
}
return(objReturn);
}
private DataTable _processInnerJoin(Queue<TableContext> qAllTables, DataTable dtReturn, string strJoinText,
string strParentTable, string strOrderBy, Queue<string> qInnerJoinFields)
{
SqlDbSharpLogger.LogMessage("Note that WHERE clauses are not yet applied to JOINed tables.", "SelectCommnd _processInnerJoin");
string strNewTable = null;
string strNewField = null;
string strOldField = null;
string strOldTable = null;
string strErrLoc = "init";
Queue<string> qColsToSelectInNewTable = new Queue<string>();
MainClass.logIt("join fields: " + string.Join("\n", qInnerJoinFields.ToArray()));
try
{
strJoinText = System.Text.RegularExpressions.Regex.Replace(strJoinText, @"\s\n+", " ");
string[] astrInnerJoins = strJoinText.ToLower().Split(new string[] { "inner join" }, StringSplitOptions.RemoveEmptyEntries);
Dictionary<string, DataTable> dictTables = new Dictionary<string, DataTable>();
dictTables.Add(strParentTable.ToLower(), dtReturn);
strErrLoc = "starting inner join array";
foreach (string strInnerJoin in astrInnerJoins)
{
// "from table1 inner join" <<< already removed
// "table2 on table1.field = table2.field2"
string[] astrTokens = strInnerJoin.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (!"=".Equals(astrTokens[3]))
{
throw new Exception("We're only supporting inner equi joins right now: " + strInnerJoin);
}
// Kludge alert -- must have table prefixes for now.
if (!astrTokens[2].Contains(".") || !astrTokens[4].Contains("."))
{
throw new Exception(string.Format(
"For now, joined fields must include table prefixes: {0} {1}",
astrTokens[2],
astrTokens[2])
);
}
strErrLoc = "determine old and new tables and fields";
string field1Parent = astrTokens[2].Substring(0, astrTokens[2].IndexOf("."));
string field2Parent = astrTokens[4].Substring(0, astrTokens[4].IndexOf("."));
string field1 = astrTokens[2].Substring(astrTokens[2].IndexOf(".") + 1);
string field2 = astrTokens[4].Substring(astrTokens[4].IndexOf(".") + 1);
if (dictTables.ContainsKey(field1Parent)) // TODO: Should probably check to see if they're both known and at least bork.
{
strNewTable = field2Parent;
strNewField = field2;
strOldTable = field1Parent;
strOldField = field1;
}
else
{
strNewTable = field1Parent;
strNewField = field1;
strOldTable = field2Parent;
strOldField = field2;
}
MainClass.logIt(string.Format(@"old table: {0}
old field: {1}
new table: {2}
new field: {3}",
strOldTable, strOldField, strNewTable, strNewField));
string strInClause = string.Empty;
TableContext tableOld = _database.getTableByName(strOldTable);
TableContext tableNew = _database.getTableByName(strNewTable);
qAllTables.Enqueue(tableNew); // we need this to figure out column parents later.
// Now that we know the new table to add, we need to get a list of columns
// to select from it.
// Sources for these fields could be...
// 1.) The joining field.
// 2.) ORDER BY fields for the entire statement
// 3.) conventional SELECT fields.
//
// To prevent column name collision, let's go ahead and prefix them
// all with the table name. A little unexpected, but a decent shortcut
// for now, I think.
strErrLoc = "beginning inner join select construction";
// 1.) Add the joining field.
qColsToSelectInNewTable.EnqueueIfNotContains(strNewField);
// 2.) ORDER BY fields that belong to this table.
if (!string.IsNullOrWhiteSpace(strOrderBy))
{
MainClass.logIt(strOrderBy);
strErrLoc = "constructing order by";
string[] astrOrderTokens = strOrderBy.StringToNonWhitespaceTokens2();
for (int i = 2; i < astrOrderTokens.Length; i++)
{
string strOrderField = astrOrderTokens[i].Trim(' ', ',');
string strOrderTable = strNewTable; // just to pretend. We'll skip it if the field doesn't exist here. Course this means we might dupe some non-table prefixed fields.
if (strOrderField.Contains("."))
{
strOrderTable = strOrderField.Substring(0, strOrderField.IndexOf("."));
strOrderField = strOrderField.Substring(strOrderField.IndexOf(".") + 1);
}
if (strNewTable.Equals(strOrderTable, StringComparison.CurrentCultureIgnoreCase)
&& !tableNew.containsColumn(strOrderField, false)
&& tableNew.containsColumn(strOrderField, true))
{
qColsToSelectInNewTable.EnqueueIfNotContains(strNewTable + "." + strOrderField);
}
}
}
// 3.) Conventional SELECT fields
strErrLoc = "Creating select clause";
if (qInnerJoinFields.Count > 0)
{
if (qInnerJoinFields.Any(fld => fld.Equals(strNewTable + ".*", StringComparison.CurrentCultureIgnoreCase) || fld.Equals("*")))
{
qColsToSelectInNewTable.EnqueueIfNotContains("*");
}
else
{
foreach (string strTableDotCol in qInnerJoinFields)
{
string[] astrTableDotCol = strTableDotCol.Split('.');
if (strNewTable.Equals(astrTableDotCol[0], StringComparison.CurrentCultureIgnoreCase))
{
MainClass.logIt("Adding field to join SELECT: " + astrTableDotCol[1].ScrubValue());
qColsToSelectInNewTable.EnqueueIfNotContains(astrTableDotCol[1].ScrubValue()); // again, offensive parsing is the rule.
}
}
}
}
// TODO: Consider grabbing every column up front, perhaps, and
// then cutting out those columns that we didn't select -- but
// do SELECT fields as a post-processing task, rather than this
// inline parsing. Also allows us to bork on fields that aren't
// in tables a little easier; right now you could include bogus
// joined fields without error.
dictTables[strOldTable].CaseSensitive = false; // TODO: If we keep this, do it in a smarter place.
// Right now, strOldField has the name that's in the DataTable from the previous select.
strErrLoc = @"Create WHERE IN clause for join ""inner"" SELECT";
// Now construct the `WHERE joinedField IN (X,Y,Z)` portion of the inner select
// we're about to fire off.
string strOperativeOldField = strOldField;
if (!dictTables[strOldTable].Columns.Contains(strOldField))
{
// Allow fuzzy names in join, if not the DataTable -- yet.
strOperativeOldField = tableOld.getRawColName(strOldField);
}
if (MainClass.bDebug)
{
Console.WriteLine("Looking for " + strOperativeOldField + " in the columns");
foreach (DataColumn column in dictTables[strOldTable].Columns)
{
Console.WriteLine(column.ColumnName);
}
}
foreach (DataRow row in dictTables[strOldTable].Rows)
{
strInClause += row[strOperativeOldField].ToString() + ",";
}
strErrLoc = "Completing SELECT construction";
strInClause = strInClause.Trim(',');
if (string.IsNullOrEmpty(strInClause))
{
dtReturn = new DataTable();
}
else
{
MainClass.logIt("Columns in inner JOIN select: " + string.Join(", ", qColsToSelectInNewTable.ToArray()));
string strInnerSelect = string.Format("SELECT {0} FROM {1} WHERE {2} IN ({3});",
string.Join(",", qColsToSelectInNewTable.ToArray()),
strNewTable,
strNewField,
strInClause
);
qColsToSelectInNewTable = new Queue<string>();
strErrLoc = strInnerSelect;
// TODO: Figure out the best time to handle the portion of the WHERE
// that impacts the tables mentioned in the join portion of the SQL.
// Note: I think now we treat it just like the ORDER BY. Not that
// complicated to pull out table-specific WHERE fields and send along
// with the reconsitituted "inner" SQL statement.
MainClass.logIt("Inner join: " + strInnerSelect + "\n\n", "select command _processInnerJoin");
SelectCommand selectCommand = new SelectCommand(_database);
object objReturn = selectCommand.executeStatement(strInnerSelect);
if (objReturn is DataTable)
{
DataTable dtInnerJoinResult = (DataTable)objReturn;
dtReturn = InfrastructureUtils.equijoinTables(
dtReturn,
dtInnerJoinResult,
strOperativeOldField,
strNewField
);
}
else
{
strErrLoc = "Datatable not returned: " + strInnerSelect;
throw new SyntaxException("Illegal inner select: " + strInnerSelect);
}
}
}
}
catch (Exception e)
{
if (e.GetType() == typeof(SyntaxException))
{
throw e;
}
else
{
throw new SyntaxException(string.Format(@"Uncaptured join syntax error -- {0}:
{1}
{2}", strErrLoc, strJoinText, e.ToString()));
}
}
return dtReturn;
}
public object executeCommand(string strSql)
{
object objReturn = null;
if (_logSql)
System.IO.File.AppendAllText (_database.strLogLoc, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ": "
+ strSql + System.Environment.NewLine);
strSql = strSql.RemoveNewlines(" ").BacktickQuotes(); // TODO: WHOA! Super kludge for single quote escapes. See "Grave accent" in idiosyncracies.
// Sorry, I got tired of forgetting this.
//if (!strSql.Trim().EndsWith(";"))
//{
// throw new Exception("Unterminated command.");
//}
// TODO: This is assuming a single command. Add splits by semi-colon.
strSql = strSql.TrimEnd(';');
string[] astrCmdTokens = strSql.StringToNonWhitespaceTokens2(); // TODO: We're almost always immediately doing this again in the executeStatements.
// TODO: Want to ISqlCommand this stuff -- we need to have execute
// methods that don't take strings but "command tokens".
switch (astrCmdTokens[0].ToLower()) {
case "insert":
_insertCommand = new InsertCommand(_database); // TODO: This is too much repeat instantiation. Rethink that.
objReturn = _insertCommand.executeInsert(strSql);
break;
case "select":
if (strSql.ToLower().StartsWith("select max("))
{
SelectMaxCommand selectMaxCmd = new SelectMaxCommand(_database);
objReturn = selectMaxCmd.executeStatement(strSql);
}
else
{
_selectCommand = new SelectCommand(_database);
objReturn = _selectCommand.executeStatement(strSql);
}
break;
case "delete":
_deleteCommand = new DeleteCommand(_database);
_deleteCommand.executeStatement(strSql);
objReturn = "DELETE executed."; // TODO: Add ret val of how many rows returned
break;
case "update":
_updateCommand = new UpdateCommand(_database);
_updateCommand.executeStatement(strSql);
objReturn = "UPDATE executed."; // TODO: Add ret val of how many rows returned
break;
case "create":
switch (astrCmdTokens[1].ToLower())
{
case "table":
_createTableCommand = new CreateTableCommand(_database);
objReturn = _createTableCommand.executeStatement(strSql);
break;
case "index":
CreateIndexCommand createIndexCommand = new CreateIndexCommand(_database);
objReturn = createIndexCommand.executeStatement(strSql);
break;
}
break;
case "drop":
DropTableCommand dropTableCommand = new DropTableCommand(_database);
dropTableCommand.executeStatement(strSql);
objReturn = @"Table dropped (or, if ""IF EXISTS"" was used, dropped iff found)."; // TODO: These are pretty sorry messages. Have the executeStatement return something more informative.
break;
default:
throw new SyntaxException("Syntax error: Unhandled command type.");
}
return objReturn;
}
private DataTable _processInnerJoin(Queue <TableContext> qAllTables, DataTable dtReturn, string strJoinText,
string strParentTable, string strOrderBy, Queue <string> qInnerJoinFields)
{
SqlDbSharpLogger.LogMessage("Note that WHERE clauses are not yet applied to JOINed tables.", "SelectCommnd _processInnerJoin");
string strNewTable = null;
string strNewField = null;
string strOldField = null;
string strOldTable = null;
string strErrLoc = "init";
Queue <string> qColsToSelectInNewTable = new Queue <string>();
MainClass.logIt("join fields: " + string.Join("\n", qInnerJoinFields.ToArray()));
try
{
strJoinText = System.Text.RegularExpressions.Regex.Replace(strJoinText, @"\s\n+", " ");
string[] astrInnerJoins = strJoinText.ToLower().Split(new string[] { "inner join" }, StringSplitOptions.RemoveEmptyEntries);
Dictionary <string, DataTable> dictTables = new Dictionary <string, DataTable>();
dictTables.Add(strParentTable.ToLower(), dtReturn);
strErrLoc = "starting inner join array";
foreach (string strInnerJoin in astrInnerJoins)
{
// "from table1 inner join" <<< already removed
// "table2 on table1.field = table2.field2"
string[] astrTokens = strInnerJoin.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (!"=".Equals(astrTokens[3]))
{
throw new Exception("We're only supporting inner equi joins right now: " + strInnerJoin);
}
// Kludge alert -- must have table prefixes for now.
if (!astrTokens[2].Contains(".") || !astrTokens[4].Contains("."))
{
throw new Exception(string.Format(
"For now, joined fields must include table prefixes: {0} {1}",
astrTokens[2],
astrTokens[2])
);
}
strErrLoc = "determine old and new tables and fields";
string field1Parent = astrTokens[2].Substring(0, astrTokens[2].IndexOf("."));
string field2Parent = astrTokens[4].Substring(0, astrTokens[4].IndexOf("."));
string field1 = astrTokens[2].Substring(astrTokens[2].IndexOf(".") + 1);
string field2 = astrTokens[4].Substring(astrTokens[4].IndexOf(".") + 1);
if (dictTables.ContainsKey(field1Parent)) // TODO: Should probably check to see if they're both known and at least bork.
{
strNewTable = field2Parent;
strNewField = field2;
strOldTable = field1Parent;
strOldField = field1;
}
else
{
strNewTable = field1Parent;
strNewField = field1;
strOldTable = field2Parent;
strOldField = field2;
}
MainClass.logIt(string.Format(@"old table: {0}
old field: {1}
new table: {2}
new field: {3}",
strOldTable, strOldField, strNewTable, strNewField));
string strInClause = string.Empty;
TableContext tableOld = _database.getTableByName(strOldTable);
TableContext tableNew = _database.getTableByName(strNewTable);
qAllTables.Enqueue(tableNew); // we need this to figure out column parents later.
// Now that we know the new table to add, we need to get a list of columns
// to select from it.
// Sources for these fields could be...
// 1.) The joining field.
// 2.) ORDER BY fields for the entire statement
// 3.) conventional SELECT fields.
//
// To prevent column name collision, let's go ahead and prefix them
// all with the table name. A little unexpected, but a decent shortcut
// for now, I think.
strErrLoc = "beginning inner join select construction";
// 1.) Add the joining field.
qColsToSelectInNewTable.EnqueueIfNotContains(strNewField);
// 2.) ORDER BY fields that belong to this table.
if (!string.IsNullOrWhiteSpace(strOrderBy))
{
MainClass.logIt(strOrderBy);
strErrLoc = "constructing order by";
string[] astrOrderTokens = strOrderBy.StringToNonWhitespaceTokens2();
for (int i = 2; i < astrOrderTokens.Length; i++)
{
string strOrderField = astrOrderTokens[i].Trim(' ', ',');
string strOrderTable = strNewTable; // just to pretend. We'll skip it if the field doesn't exist here. Course this means we might dupe some non-table prefixed fields.
if (strOrderField.Contains("."))
{
strOrderTable = strOrderField.Substring(0, strOrderField.IndexOf("."));
strOrderField = strOrderField.Substring(strOrderField.IndexOf(".") + 1);
}
if (strNewTable.Equals(strOrderTable, StringComparison.CurrentCultureIgnoreCase) &&
!tableNew.containsColumn(strOrderField, false) &&
tableNew.containsColumn(strOrderField, true))
{
qColsToSelectInNewTable.EnqueueIfNotContains(strNewTable + "." + strOrderField);
}
}
}
// 3.) Conventional SELECT fields
strErrLoc = "Creating select clause";
if (qInnerJoinFields.Count > 0)
{
if (qInnerJoinFields.Any(fld => fld.Equals(strNewTable + ".*", StringComparison.CurrentCultureIgnoreCase) || fld.Equals("*")))
{
qColsToSelectInNewTable.EnqueueIfNotContains("*");
}
else
{
foreach (string strTableDotCol in qInnerJoinFields)
{
string[] astrTableDotCol = strTableDotCol.Split('.');
if (strNewTable.Equals(astrTableDotCol[0], StringComparison.CurrentCultureIgnoreCase))
{
MainClass.logIt("Adding field to join SELECT: " + astrTableDotCol[1].ScrubValue());
qColsToSelectInNewTable.EnqueueIfNotContains(astrTableDotCol[1].ScrubValue()); // again, offensive parsing is the rule.
}
}
}
}
// TODO: Consider grabbing every column up front, perhaps, and
// then cutting out those columns that we didn't select -- but
// do SELECT fields as a post-processing task, rather than this
// inline parsing. Also allows us to bork on fields that aren't
// in tables a little easier; right now you could include bogus
// joined fields without error.
dictTables[strOldTable].CaseSensitive = false; // TODO: If we keep this, do it in a smarter place.
// Right now, strOldField has the name that's in the DataTable from the previous select.
strErrLoc = @"Create WHERE IN clause for join ""inner"" SELECT";
// Now construct the `WHERE joinedField IN (X,Y,Z)` portion of the inner select
// we're about to fire off.
string strOperativeOldField = strOldField;
if (!dictTables[strOldTable].Columns.Contains(strOldField))
{
// Allow fuzzy names in join, if not the DataTable -- yet.
strOperativeOldField = tableOld.getRawColName(strOldField);
}
if (MainClass.bDebug)
{
Console.WriteLine("Looking for " + strOperativeOldField + " in the columns");
foreach (DataColumn column in dictTables[strOldTable].Columns)
{
Console.WriteLine(column.ColumnName);
}
}
foreach (DataRow row in dictTables[strOldTable].Rows)
{
strInClause += row[strOperativeOldField].ToString() + ",";
}
strErrLoc = "Completing SELECT construction";
strInClause = strInClause.Trim(',');
if (string.IsNullOrEmpty(strInClause))
{
dtReturn = new DataTable();
}
else
{
MainClass.logIt("Columns in inner JOIN select: " + string.Join(", ", qColsToSelectInNewTable.ToArray()));
string strInnerSelect = string.Format("SELECT {0} FROM {1} WHERE {2} IN ({3});",
string.Join(",", qColsToSelectInNewTable.ToArray()),
strNewTable,
strNewField,
strInClause
);
qColsToSelectInNewTable = new Queue <string>();
strErrLoc = strInnerSelect;
// TODO: Figure out the best time to handle the portion of the WHERE
// that impacts the tables mentioned in the join portion of the SQL.
// Note: I think now we treat it just like the ORDER BY. Not that
// complicated to pull out table-specific WHERE fields and send along
// with the reconsitituted "inner" SQL statement.
MainClass.logIt("Inner join: " + strInnerSelect + "\n\n", "select command _processInnerJoin");
SelectCommand selectCommand = new SelectCommand(_database);
object objReturn = selectCommand.executeStatement(strInnerSelect);
if (objReturn is DataTable)
{
DataTable dtInnerJoinResult = (DataTable)objReturn;
dtReturn = InfrastructureUtils.equijoinTables(
dtReturn,
dtInnerJoinResult,
strOperativeOldField,
strNewField
);
}
else
{
strErrLoc = "Datatable not returned: " + strInnerSelect;
throw new SyntaxException("Illegal inner select: " + strInnerSelect);
}
}
}
}
catch (Exception e)
{
if (e.GetType() == typeof(SyntaxException))
{
throw e;
}
else
{
throw new SyntaxException(string.Format(@"Uncaptured join syntax error -- {0}:
{1}
{2}", strErrLoc, strJoinText, e.ToString()));
}
}
return(dtReturn);
}