// TODO: Create Command interface
public DataTable executeStatement(string strSql)
{
DataTable dtReturn = new DataTable();
// TODO: Think how to track multiple tables/TableContexts
// Note that the constructor will set up the table named
// in the SELECT statement in _table.
CommandParts selectParts = new CommandParts(_database, _table, strSql, CommandParts.COMMAND_TYPES.SELECT);
if (MainClass.bDebug)
{
string strDebug = "SELECT: " + selectParts.strSelect + "\n";
strDebug += "FROM: " + selectParts.strFrom + "\n";
if (!string.IsNullOrEmpty(selectParts.strInnerJoinKludge))
{
strDebug += "INNER JOIN: " + selectParts.strInnerJoinKludge + "\n";
}
strDebug += "WHERE: " + selectParts.strWhere + "\n"; // Note that WHEREs aren't applied to inner joined tables right now.
strDebug += "ORDER BY: " + selectParts.strOrderBy + "\n";
SqlDbSharpLogger.LogMessage(strDebug, "SelectCommand executeStatement");
}
_table = _database.getTableByName(selectParts.strTableName);
Queue <TableContext> qAllTables = new Queue <TableContext>();
qAllTables.Enqueue(_table);
dtReturn = _initDataTable(selectParts);
WhereProcessor.ProcessRows(ref dtReturn, _table, selectParts);
//=====================================================================
// POST-PROCESS INNER JOINS
// (Joins are only in selects, so this isn't part of WhereProcessing.)
//
// To take account of joins, we basically need to create a SelectParts
// per inner join. So we need to create a WHERE from the table we
// just selected and then send those values down to a new _selectRows.
//=====================================================================
if (selectParts.strInnerJoinKludge.Length > 0)
{
if (selectParts.qInnerJoinFields.Count < 1)
{
selectParts.qInnerJoinFields.EnqueueIfNotContains("*"); // Kludge for "SELECT * FROM Table1 INNER JOIN..." or "SELECT test, * From...", etc
}
// TODO: Why aren't we just throwing in the whole selectParts again?
dtReturn = _processInnerJoin(qAllTables, dtReturn, selectParts.strInnerJoinKludge, selectParts.strTableName, selectParts.strOrderBy, selectParts.qInnerJoinFields);
// Now we need to make sure the order of the DataColumns reflects what we had
// in the original SQL. At least initially, column order wasn't guaranteed in
// _processInnerJoin, as it would add columns first for the "main" table and then
// for each "inner SELECT".
string strFromSelect = string.Join(", ", selectParts.qstrAllColumnNames.ToArray());
string strInTable = string.Join(", ", dtReturn.Columns.Cast <DataColumn>().Select(c => c.ColumnName).ToArray());
MainClass.logIt(string.Format(@"Select fields: {0}
Fields pushed into dtReturn: {1}", strFromSelect, strInTable));
try
{
string[] astrFromSelect = selectParts.qstrAllColumnNames.ToArray();
for (int i = 0; i < astrFromSelect.Length; i++)
{
dtReturn.Columns[astrFromSelect[i]].SetOrdinal(i);
}
// TODO: There are better ways to do this.
// TODO: Figure out if this handles all fuzzy name translations
// earlier in the SELECT process.
if (selectParts.lstrJoinONLYFields.Count() > 0)
{
foreach (string colName in selectParts.lstrJoinONLYFields)
{
dtReturn.Columns.Remove(colName);
}
}
}
catch (Exception e)
{
throw new SyntaxException("Problem reordering columns in inner join -- " + e.ToString());
}
}
//=====================================================================
// EO POST-PROCESS INNER JOINS
//=====================================================================
// strOrderBy has had all whitespace shortened to one space, so we can get away with the hardcoded 9.
if (null != selectParts.strOrderBy && selectParts.strOrderBy.Length > 9)
{
// ORDER BY needs to make sure it's not sorting on a fuzzy named column
// that may not have been explicitly selected in the SELECT.
string[] astrOrderByFields = selectParts.strOrderBy.Substring(9).Split(','); // Substring(9) to get rid of "ORDER BY " <<< But, ultimately, why not tokenize here too?
string strCleanedOrderBy = string.Empty;
foreach (string orderByClause in astrOrderByFields)
{
bool ascNotDesc = true;
string strOrderByClause = orderByClause.Trim();
string strField = orderByClause.Trim();
if (strField.Split().Length > 1)
{
strField = strOrderByClause.Substring(0, strOrderByClause.IndexOf(' ')).Trim();
string strAscDesc = strOrderByClause.Substring(strOrderByClause.IndexOf(' ')).Trim();
ascNotDesc = (-1 == strAscDesc.IndexOf("DESC", StringComparison.CurrentCultureIgnoreCase));
}
strOrderByClause += ","; // This is the default value if there's no fuzziness, and it needs the comma put back.
// TODO: Integrate fields prefixed by specific table names.
if (!dtReturn.Columns.Contains(strField))
{
// Check for fuzziness.
foreach (TableContext table in qAllTables)
{
if (!table.containsColumn(strField, false) && table.containsColumn(strField, true))
{
strOrderByClause = table.getRawColName(strField)
+ (ascNotDesc ? " ASC" : " DESC")
+ ",";
break;
}
}
}
strCleanedOrderBy += " " + strOrderByClause;
}
dtReturn.DefaultView.Sort = strCleanedOrderBy.Trim(',');
dtReturn = dtReturn.DefaultView.ToTable();
}
if (selectParts.dictRawNamesToASNames.Count > 0)
{
try
{
foreach (KeyValuePair <string, string> kvp in selectParts.dictRawNamesToASNames)
{
dtReturn.Columns[kvp.Key].ColumnName = kvp.Value;
}
}
catch (Exception e)
{
throw new SyntaxException("Illegal AS usage: " + e.ToString());
}
}
return(dtReturn);
}
/// <summary>
/// Processes a CREATE TABLE statement.
/// Throws any Exception on any failure.
/// </summary>
/// <param name="strSql"></param>
public object executeStatement(string strSql)
{
string strErr = "";
Match createTableMatch = Regex.Match(strSql, @"^CREATE\s*TABLE\s*`?\w*`?\s*\(", RegexOptions.IgnoreCase);
List <string> lstRawNames = new List <string>();
if (createTableMatch.Success)
{
string strTableName = Regex.Replace(createTableMatch.Groups[0].Value, @"\r\n?|\n", ""); // remove newlines with http://stackoverflow.com/a/8196219/1028230
strTableName = strTableName.Substring(0, strTableName.ToLower().IndexOf("("));
strTableName = strTableName.Substring(strTableName.ToLower().IndexOf("table") + 5).Trim().Trim('`');
if (null != _database.getTableByName(strTableName))
{
strErr += "Table " + strTableName + " already exists.\n";
}
else
{
// Else this IS a legitimate location for a table file. Begin.
// Store table loc in TableContext
_table = new TableContext();
// initialize rows with the 11 byte.
_lstByteDataTypeRow.Add(0x11);
_lstByteColNames.Add(0x11);
string strColInfo = strSql.Substring(strSql.IndexOf("(") + 1); // get rid of everything up until the first open parens.
strColInfo = strColInfo.Substring(0, strColInfo.LastIndexOf(")")); // peel off the last closed parens.
string[] astrSections = Regex.Split(strColInfo, ",");
for (int i = 0; i < astrSections.Length; i++)
{
COLUMN_TYPES?colType = null; // This really should never be null after running through the code. It'll throw an exception first.
string strColName = "";
int intFieldLength = -1;
string strNextColumnInfo = astrSections[i].Trim();
// If we're defining a primary key, which we don't support (yet, if ever), skip the line.
// Else do the normal thing.
if (strNextColumnInfo.StartsWith("PRIMARY", StringComparison.CurrentCultureIgnoreCase))
{
SqlDbSharpLogger.LogMessage("Primary key creation is currently ignored: " + strNextColumnInfo, "CreateTable executeStatement");
}
else
{
string[] astrColInfo = strNextColumnInfo.StringToNonWhitespaceTokens2();
if (astrColInfo.Length < 2)
{
strErr += "Illegal column defintion; table not created: " + string.Join(":", astrColInfo) + "#\n";
}
else
{
//=====================
//======= DEBUG =======
//=====================
if (MainClass.bDebug)
{
for (int j = 0; j < astrColInfo.Length; j++)
{
SqlDbSharpLogger.LogMessage(j + " :: " + astrColInfo[j], "Create table execute statement");
}
}
//======================
//======================
if (3 <= astrColInfo.Length)
{
int intLength;
if (int.TryParse(astrColInfo[2], out intLength))
{
if (4369 == intLength)
{
throw new Exception("Idiosyncratically, column lengths of [exactly] 4369 are not allowed. " + astrColInfo[1]);
}
intFieldLength = intLength;
}
else
{
// We're going to step up from defaulting to a length of 20 in each case to
// defining a default length for each column data type.
intFieldLength = -1;
}
}
// every column declaration has already been checked to ensure it has at least two entries (checked above)
// TODO: Check for statements expecting the default length but with modifiers, like
// `id` INT NOT NULL AUTO_INCREMENT DEFAULT NULL,
// This is handled with AUTO_INCREMENT because NOT NULL is thrown out as if it had the length.
// Which is to say, NOT NULL isn't passed along as part of the modifier string.
strColName = astrColInfo[0].Trim('`');
string strModifier = null;
if (astrColInfo.Length > 3)
{
strModifier = string.Join(" ", astrColInfo, 3, astrColInfo.Length - 3);
}
colType = InfrastructureUtils.colTypeFromString(astrColInfo[1], intFieldLength, strModifier);
if (null == colType)
{
strErr += "Illegal/Supported column type: " + astrColInfo[1] + "\n";
}
else
{
COLUMN_TYPES colTypeCleaned = (COLUMN_TYPES)colType; // got to be a better way to launder a nullable.
if (intFieldLength < 0)
{
intFieldLength = _getDefaultLengthForType(colTypeCleaned);
}
string strRawName = strColName.Length > intFieldLength?strColName.Substring(0, intFieldLength) : strColName;
IEnumerable <string> coveredNames = lstRawNames.Where(name => name.StartsWith(strRawName, StringComparison.CurrentCultureIgnoreCase) ||
strRawName.StartsWith(name, StringComparison.CurrentCultureIgnoreCase));
if (coveredNames.Count() > 0)
{
throw new Exception(string.Format(@"Field names would ""cover"" each other: `{0}` and `{1}`", strRawName, coveredNames.First()));
}
lstRawNames.Add(strRawName);
_createColumn(strColName, colTypeCleaned, intFieldLength);
}
if (!strErr.Equals(""))
{
break;
}
}
} // end check for unsupported directives (like defining a primary key)
} // eo table column creation for loop
if (MainClass.bDebug)
{
string strDebug = string.Empty;
for (int j = 0; j < _lstByteDataTypeRow.Count; j++)
{
strDebug += "0x" + _lstByteDataTypeRow[j].ToString("X2") + ", \n";
}
strDebug += "\n\n";
for (int j = 0; j < _lstByteColNames.Count; j++)
{
strDebug += "0x" + _lstByteColNames[j].ToString("X2") + ", \n";
}
strDebug += "\n";
strDebug += _table.strTableFileLoc + "\n";
SqlDbSharpLogger.LogMessage(strDebug, "Create table execute statement");
}
// TODO: Instead of writing bytes here, I should probably create a list of column objects,
// with name, length, and COLUMN_TYPE for each, then let the table manager worry about
// bytes and specific implmentations.
_table.writeMetadataRowsAndPrepareNewTable(_lstByteDataTypeRow, _lstByteColNames, strTableName, _database.strDbLoc);
_database.addNewTable(_table);
} // eo table exists check.
} // eo createTableMatch.Success regex check
else
{
strErr += "SYNTAX ERROR: Illegal Create Table Statement" + System.Environment.NewLine;
// go ahead and throw specific error type.
throw new SyntaxException(strErr);
}
strErr = strErr.Equals("") ? "Table created successfully." : "Create table error" + System.Environment.NewLine + strErr;
return(strErr);
}
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);
}
