// TODO: Create Command interface
public bool executeStatement(string strSql)
{
bool wasSuccessful = false;
// 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 updateParts = new CommandParts(_database, _table, strSql, CommandParts.COMMAND_TYPES.UPDATE);
if (MainClass.bDebug)
{
Console.WriteLine("SELECT: " + updateParts.strSelect);
Console.WriteLine("FROM: " + updateParts.strFrom);
if (!string.IsNullOrEmpty(updateParts.strInnerJoinKludge))
{
throw new Exception("Syntax error: INNER JOIN in an UPDATE statement is not supported: " + strSql);
}
Console.WriteLine("WHERE: " + updateParts.strWhere); // Note that WHEREs aren't applied to inner joined tables right now.
Console.WriteLine("ORDER BY: " + updateParts.strOrderBy);
}
_table = _database.getTableByName(updateParts.strTableName);
DataTable dtThrowAway = new DataTable();
WhereProcessor.ProcessRows(ref dtThrowAway, _table, updateParts);
return wasSuccessful;
}
public void removeExistingTable(TableContext tableToDrop)
{
string strTableName = tableToDrop.strTableName;
if (!_dictTables.Remove(strTableName))
{
throw new Exception("Unable to remove table: " + strTableName);
}
}
public string tableCheck()
{
string strReturn = "Tables in db" + System.Environment.NewLine
+ "============" + System.Environment.NewLine
+ System.Environment.NewLine;
foreach (KeyValuePair <string, TableContext> keyAndTable in _dictTables)
{
TableContext table = keyAndTable.Value;
strReturn += table.strTableName + System.Environment.NewLine;
}
return(strReturn);
}
// TODO: Create Command interface
public void executeStatement(string strSql)
{
DeleteParts deleteParts = new DeleteParts(_database, strSql);
if (MainClass.bDebug)
{
Console.WriteLine("DELETE: " + deleteParts.strDelete);
Console.WriteLine("FROM: " + deleteParts.strFrom);
Console.WriteLine("WHERE: " + deleteParts.strWhere);
}
_table = _database.getTableByName(deleteParts.strTableName);
List<Comparison> lstWhereConditions = _createWhereConditions(deleteParts.strWhere);
_deleteRows(lstWhereConditions);
}
/// <summary>
/// Returns null if table doesn't exist in DatabaseContext.
/// </summary>
public TableContext getTableByName(string strTableName)
{
TableContext table = null;
// Doing the weird foreach instead of ContainsKey so we can be case insensitive.
foreach (string key in _dictTables.Keys)
{
if (key.Equals(strTableName, this.caseSetting))
{
table = _dictTables[key];
break;
}
}
return(table);
}
public void executeStatement(string strSql)
{
bool bIgnoreMissingTable = false;
// NOTE: Forced case insensitivity.
strSql = strSql.ToLower().TrimEnd(';');
string[] astrCmdTokens = strSql.StringToNonWhitespaceTokens2();
bool bQuickTokenCheck = astrCmdTokens.Length >= 3
&& "drop" == astrCmdTokens[0].ToLower()
&& "table" == astrCmdTokens[1].ToLower();
if (!bQuickTokenCheck)
{
throw new Exception("Illegal drop command -- Syntax DROP TABLE TableName;");
}
else
{
string strTableName = astrCmdTokens[2];
if (astrCmdTokens.Length >= 5
&& astrCmdTokens[2].Equals("if", StringComparison.CurrentCultureIgnoreCase)
&& astrCmdTokens[3].Equals("exists", StringComparison.CurrentCultureIgnoreCase))
{
bIgnoreMissingTable = true;
strTableName = astrCmdTokens[4];
}
strTableName = strTableName.Trim('`');
_table = _database.getTableByName(strTableName);
if (null == _table)
{
if (!bIgnoreMissingTable)
{
throw new Exception("Table not found in database: " + strTableName);
}
}
else
{
File.Delete(_table.strTableFileLoc);
_database.removeExistingTable(_table);
}
}
}
public CommandParts(DatabaseContext database, TableContext table, string strSql, COMMAND_TYPES commandType)
{
_database = database;
_tableContext = table;
this.commandType = commandType;
this.strOriginal = strSql;
switch (commandType)
{
case COMMAND_TYPES.SELECT:
_parseSelectStatement(strSql);
_getColumnsToReturn();
break;
case COMMAND_TYPES.UPDATE:
_parseUpdateStatement(strSql);
break;
default:
throw new Exception("Unhandled statement type in CommandParts");
}
}
public static void ProcessRows(
ref DataTable dtWithCols,
TableContext table,
CommandParts commandParts
)
{
string strWhere = commandParts.strWhere;
List<Comparison> lstWhereConditions = _CreateWhereConditions(strWhere, table);
// TODO: Really need to design a legitimate table locking system.
int delayFactor = 1;
try
{
using (BinaryReader b = new BinaryReader(File.Open(table.strTableFileLoc, FileMode.Open)))
{
int intRowCount = table.intFileLength / table.intRowLength;
b.BaseStream.Seek(2 * table.intRowLength, SeekOrigin.Begin); // TODO: Code more defensively in case it's somehow not the right/minimum length
for (int i = 2; i < intRowCount; i++)
{
byte[] abytRow = b.ReadBytes(table.intRowLength);
bool bMatchingRow = true;
// Check and make sure this is an active row, and has
// the standard row lead byte, 0x11. If not, the row
// should not be read.
// I'm going to switch this to make it more defensive
// and a little easier to follow.
switch (abytRow[0])
{
case 0x88:
// DELETED
bMatchingRow = false;
break;
case 0x11:
// ACTIVE
// Find if the WHERE clause says to exclude this row.
foreach (Comparison comparison in lstWhereConditions)
{
// For now, we're (somewhat clumsily) processing INs as lots of small ORs.
// And no, we're not actually supporting the OR statement in a regular WHERE yet.
if (comparison is CompoundComparison)
{
bool bInKeeper = false;
// Could use a lot more indexed logic here, but that'll need to be
// an extension to this package to keep the logic simple.
// This is a painful, bullheaded Moore's comparison.
foreach (Comparison compInner in ((CompoundComparison)comparison).lstComparisons)
{
if (_ComparisonEngine(compInner, abytRow))
{
bInKeeper = true;
break;
}
}
bMatchingRow = bMatchingRow && bInKeeper;
}
else
{
bMatchingRow = bMatchingRow && _ComparisonEngine(comparison, abytRow);
}
}
break;
default:
throw new Exception("Unexpected row state in SELECT: " + abytRow[0]);
}
if (bMatchingRow)
{
switch (commandParts.commandType)
{
case CommandParts.COMMAND_TYPES.SELECT:
#region SELECT
Dictionary<string, string> dictFuzzyToColName = new Dictionary<string, string>(commandParts.dictFuzzyToColNameMappings); // resets with each row.
DataRow row = dtWithCols.NewRow();
foreach (Column mCol in commandParts.acolInSelect)
{
byte[] abytCol = new byte[mCol.intColLength];
Array.Copy(abytRow, mCol.intColStart, abytCol, 0, mCol.intColLength);
//Console.WriteLine(System.Text.Encoding.Default.GetString(abytCol));
// now translate/cast the value to the column in the row.
// OLD: row[OperativeName(mCol.strColName, dictColNameMapping)] = Router.routeMe(mCol).toNative(abytCol);
// foreach b/c we're supporting multiple calls to the same col in a SELECT now.
foreach (DataColumn dc in dtWithCols.Columns)
{
// See if we should use this column's (mCol's) value with this DataColumn.
if (dictFuzzyToColName.ContainsValue(mCol.strColName) || mCol.strColName.Equals(dc.ColumnName))
{
// If so, see if there's a fuzzy name mapped for this column.
string strColName = GetFuzzyNameIfExists(mCol.strColName, dictFuzzyToColName);
row[strColName] = Router.routeMe(mCol).toNative(abytCol);
// If we had a fuzzy name, remove from the dictionary so we don't dupe it.
if (dictFuzzyToColName.ContainsKey(strColName))
{
dictFuzzyToColName.Remove(strColName);
}
}
}
}
dtWithCols.Rows.Add(row);
#endregion SELECT
break;
case CommandParts.COMMAND_TYPES.UPDATE:
#region UPDATE
// kludge for fuzzy names:
// (This should be a one-way process, so I don't think having the logic
// in this cruddy a place is a huge problem that'll cause wasted
// resources; it's just having me rethink fuzzy names in general.)
Dictionary<string, string> dictLaunderedUpdateVals = new Dictionary<string,string>();
foreach (string key in commandParts.dictUpdateColVals.Keys)
{
dictLaunderedUpdateVals.Add(table.getRawColName(key), commandParts.dictUpdateColVals[key]);
}
foreach (Column mCol in table.getColumns())
{
Column colToPullValueFrom = null;
string strUpdateValueModifier = string.Empty;
if (dictLaunderedUpdateVals.ContainsKey(mCol.strColName))
{
// Column needs updating; take values from update
byte[] abytNewColVal = null; // Will hold "raw" value. Might not be the full column length.
// Check to see if we're updating using another column from the same row or a value.
// TODO: Performance here should be crappy. Create a mapping of col names & Cols for
// in-statement column value transfers. ie, "UPDATE table1 SET col1 = col2 WHERE col1 = 'update me';"
string valueAsString = dictLaunderedUpdateVals[mCol.strColName];
// Check for operators inside of update values.
// TODO: Handle strings with operators, but then that's what CONCAT is for.
// See PIPES_AS_CONCAT in MySQL for more fun. (Note that SQL Server does
// allow string concat via `+`.)
//
// TODO: Note that tabs in the phrase (though strange) should be legit.
// The current state of the code will choke on them, however.
//
// NOTE: I'm going to slowly refactor to ConstructValue as I add the operation
// functions to the serializers. So right now I've only got IntSerializer ready.
// (... but I want to check this in instead of stash).
COLUMN_TYPES[] validValueModiferTypes = new COLUMN_TYPES[] { COLUMN_TYPES.INT };
if (validValueModiferTypes.Contains(mCol.colType))
{
// New method that allows composition update clauses (eg, `col1 + 4 - col2`)
abytNewColVal = CompositeColumnValueModifier.ConstructValue(mCol, valueAsString, abytRow, table);
}
else
{
// Old method to update value (no composite clauses).
colToPullValueFrom = table.getColumnByName(valueAsString);
if (null != colToPullValueFrom)
{
if (mCol.intColLength < colToPullValueFrom.intColLength || !CompositeColumnValueModifier.ColsAreCompatible(mCol, colToPullValueFrom))
{
throw new Exception("UPDATE attempted to update with a value that was potentially too large or with columns of incompatible types.");
}
abytNewColVal = new byte[colToPullValueFrom.intColLength];
Array.Copy(abytRow, colToPullValueFrom.intColStart, abytNewColVal, 0, colToPullValueFrom.intColLength);
}
else
{
BaseSerializer serializer = Router.routeMe(mCol);
abytNewColVal = serializer.toByteArray(dictLaunderedUpdateVals[mCol.strColName]);
}
}
// double check that the serializer at least
// gave you a value that's the right length so
// that everything doesn't go to heck (moved where
// that was previously checked into the serializers)
if (abytNewColVal.Length != mCol.intColLength)
{
throw new Exception("Improperly lengthed field from serializer (UPDATE): " + mCol.strColName);
}
// keep in mind that column.intColLength should always match abytColValue.Length. While I'm
// testing, I'm going to put in this check, but at some point, you should be confident enough
// to consider removing this check.
if (abytNewColVal.Length != mCol.intColLength)
{
throw new Exception("Surprising value and column length mismatch");
}
Buffer.BlockCopy(abytNewColVal, 0, abytRow, mCol.intColStart, abytNewColVal.Length);
} // else don't touch what's in the row; it's not an updated colum
}
b.BaseStream.Seek(-1 * table.intRowLength, SeekOrigin.Current);
b.BaseStream.Write(abytRow, 0, abytRow.Length);
#endregion UPDATE
break;
case CommandParts.COMMAND_TYPES.DELETE:
byte[] abytErase = new byte[table.intRowLength]; // should be initialized to zeroes.
// at least to test, I'm going to write it all over with 0x88s.
for (int j = 0; j < table.intRowLength; j++) { abytErase[j] = 0x88; }
// move pointer back to the first byte of this row.
b.BaseStream.Seek(-1 * table.intRowLength, SeekOrigin.Current);
b.BaseStream.Write(abytErase, 0, abytErase.Length);
break;
default:
throw new Exception("Unhandled command type in WhereProcessor: " + commandParts.commandType);
}
}
} // eo for loop i < intRowCount
} // eo using statement for the binary reader.
}
catch (IOException)
{
delayFactor = delayFactor * 2;
if (delayFactor > (3 * 60 * 1000))
{
throw new Exception("Statement timeout: " + commandParts.strOriginal);
}
Thread.Sleep(delayFactor * 200);
//org.rufwork.mooresDb.SqlDbSharpLogger.LogMessage(table.strTableName + ".mdbf is locked. Waiting " + delayFactor + " millis to try again.", "WhereProcessor.ProcessRows");
}
// nothing to return -- dt was passed by ref.
}
private static List<Comparison> _CreateWhereConditions(string strWhere, TableContext table)
{
List<Comparison> lstReturn = new List<Comparison>();
if (!string.IsNullOrWhiteSpace(strWhere))
{
strWhere = strWhere.Substring(6);
string[] astrClauses = strWhere.SplitSeeingSingleQuotesAndBackticks("AND", false).ToArray();
// TODO: Handle NOTs, duh.
for (int i = 0; i < astrClauses.Length; i++)
{
Comparison comparison = null;
string strClause = astrClauses[i].Trim();
if (MainClass.bDebug) Console.WriteLine("Where clause #" + i + " " + strClause);
if (strClause.SplitSeeingSingleQuotesAndBackticks(" IN ", false).Count > 1)
{
CompoundComparison inClause = new CompoundComparison(GROUP_TYPE.OR);
if (MainClass.bDebug) Console.WriteLine("IN clause: " + strClause);
string strField = strClause.Substring(0, strClause.IndexOf(' '));
string strIn = strClause.Substring(strClause.IndexOf('(') + 1, strClause.LastIndexOf(')') - strClause.IndexOf('(') - 1);
string[] astrInVals = strIn.Split(',');
foreach (string strInVal in astrInVals)
{
string strFakeWhere = strField + " = " + strInVal;
inClause.lstComparisons.Add(WhereProcessor._CreateComparison(strFakeWhere, table));
}
lstReturn.Add(inClause);
}
else
{
comparison = WhereProcessor._CreateComparison(strClause, table);
if (null != comparison)
{
lstReturn.Add(comparison);
}
else
{
Console.WriteLine("Uncaptured WHERE clause type: " + strClause);
}
}
}
}
return lstReturn;
}
private static Comparison _CreateComparison(string strClause, TableContext table)
{
string strOperator = "=";
if (strClause.ContainsOutsideOfQuotes("<"))
{
strOperator = "<";
}
else if (strClause.ContainsOutsideOfQuotes(">"))
{
strOperator = ">";
}
else if (strClause.ContainsOutsideOfQuotes("LIKE", '\'', '`'))
{
strOperator = "LIKE";
}
else if (strClause.ContainsOutsideOfQuotes("like", '\'', '`'))
{
// kludge until I get case sensitivity into ContainsOutsideOfQuotes.
// Too bad, lIkE.
strOperator = "like";
}
else if (!strClause.ContainsOutsideOfQuotes("="))
{
throw new Exception("Illegal comparison type in SelectCommand: " + strClause);
}
string[] astrComparisonParts = strClause.SplitSeeingSingleQuotesAndBackticks(strOperator, false).Take(2).ToArray();
Column colToConstrain = table.getColumnByName(astrComparisonParts[0].Trim());
if (null == colToConstrain)
{
throw new ColumnNotFoundException("Column not found in SELECT statement: " + astrComparisonParts[0]);
}
BaseSerializer serializer = Router.routeMe(colToConstrain);
byte[] abytComparisonVal = serializer.toByteArray(astrComparisonParts[1].Trim());
return new Comparison(strOperator, colToConstrain, abytComparisonVal);
}
// TODO: For now, we're stogily assuming `val[whitespace][plus or minus][whitespace][value]` etc.
// TODO: Even though we're allowing multiple column names and values, this is still pretty naive,
// as we're not handling parentheses or order of operations at all.
public static byte[] ConstructValue(Column colOutput, string strClause, byte[] abytRowOfValues, TableContext table)
{
string strOrigClause = strClause;
strClause = "+ " + strClause;
string[] astrTokens = strClause.Split();
if (0 != astrTokens.Length % 2)
{
throw new Exception("Illegal update clause (value-operator count mismatch): " + strOrigClause);
}
Queue<CompositeColumnValueModifier> qModifiers = new Queue<CompositeColumnValueModifier>();
for (int i = 0; i < astrTokens.Length; i = i + 2)
{
qModifiers.Enqueue(
new CompositeColumnValueModifier(
astrTokens[i + 1].IsNumeric() ? astrTokens[i + 1] : string.Empty,
astrTokens[i + 1].IsNumeric() ? null : table.getColumnByName(astrTokens[i + 1]),
!astrTokens[i].Equals("-")
)
);
}
// I realize I could've done this in the loop where I construct
// the UpdateModifiers, but this feels a little cleaner.
byte[] abytResult = new byte[colOutput.intColLength];
BaseSerializer outputSerializer = Router.routeMe(colOutput);
foreach (CompositeColumnValueModifier modifier in qModifiers)
{
if (modifier.isValueNotColumn)
{
abytResult = outputSerializer.addRawToStringRepresentation(abytResult, modifier.strValue, !modifier.isAdditionModifierNotSubtraction);
}
else
{
if (colOutput.intColLength < modifier.column.intColLength || !ColsAreCompatible(colOutput, modifier.column))
{
throw new Exception("Value aggregation attempted to aggregate values that were potentially too large or with columns of incompatible types.");
}
byte[] abytValToAdd = new byte[modifier.column.intColLength];
Array.Copy(abytRowOfValues, modifier.column.intColStart, abytValToAdd, 0, modifier.column.intColLength);
abytResult = outputSerializer.addRawToRaw(abytResult, abytValToAdd, !modifier.isAdditionModifierNotSubtraction);
}
}
return abytResult;
}
/// <summary>
/// Processes a CREATE TABLE statement.
/// Throws any Exception on any failure.
/// </summary>
/// <param name="strSql"></param>
public void 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);
}
if (!strErr.Equals(""))
{
throw new Exception("Create table error" + System.Environment.NewLine
+ strErr);
}
}
public void removeExistingTable(TableContext tableToDrop)
{
string strTableName = tableToDrop.strTableName;
if (!_dictTables.Remove(strTableName))
{
throw new Exception("Unable to remove table: " + strTableName);
}
}
public void addNewTable(TableContext tableNew)
{
_dictTables.Add(tableNew.strTableName, tableNew);
}
/// <summary>
/// TODO: Create an InsertCommandException type that takes msg, table, and field name.
/// </summary>
/// <param name="astrCmdTokens">string[] of tokens from the SQL, split on whitespace</param>
/// <returns></returns>
public int executeInsert(string strSql)
{
int intNewlyInsertedRowId = -1;
byte[] abytFullRow = null;
string[] astrCmdTokens = strSql.SqlToTokens();
// TODO: Add a check for more than one row in the INSERT, which we don't support right now.
// Less than 6 means we can't pull off an insert
// INSERT INTO Table (Column) VALUES (value)
bool bQuickTokenCheck = !(astrCmdTokens.Length < 6 && astrCmdTokens[0].ToLower() != "insert" && astrCmdTokens[1].ToLower() != "into");
bool bTextCheck = strSql.ToLower().Contains("values"); // TODO: Keep track of a lowercase version of the string to eliminate all this ToLower stuff?
if (!bTextCheck) {
throw new Exception ("INSERT statement requires VALUES"); // TODO: again, make these all individual exception types.
} else if (!bQuickTokenCheck) {
throw new Exception("Illegal insert command -- Does not include INSERT or INTO at all or in the expected order or is too short.");
} else {
List<string> lstColumnNames = new List<string>();
List<string> lstStringRowValues = new List<string>();
Dictionary<string, byte[]> dictValsToWriteByColName = new Dictionary<string, byte[]>(); // just one row with insert right now.
string strTableName;
string strTemp;
strTableName = astrCmdTokens[2];
_table = _database.getTableByName(strTableName);
if (null == _table)
{
throw new Exception("Table not found in database: " + strTableName);
}
int i=3;
strTemp = astrCmdTokens[i].Trim();
while (!strTemp.Equals("values", StringComparison.CurrentCultureIgnoreCase) && i < astrCmdTokens.Length-1)
{
lstColumnNames.Add (strTemp);
strTemp = astrCmdTokens[++i].Trim();
}
if (strTemp.ToLower() != "values")
{
throw new Exception ("Illegal insert command 21");
}
else
{
// okay, odd place for an else, I know, since the Exception would kill the if block anyhow.
while (strTemp.IndexOf(";") != strTemp.Length-1 && i < astrCmdTokens.Length-1)
{
strTemp = astrCmdTokens[++i].Trim();
lstStringRowValues.Add(strTemp); // I don't think we care where the ")" appears, do we? Maybe I should split on parens first. But INSERT doesn't have something after the ), right?
}
}
// can't tell if I'd rather keep this all in the else or pretend like these are
// separate bits of logic.
if (lstStringRowValues.Count != lstColumnNames.Count) {
throw new Exception("Number of insert command columns and number of values are different; cannot insert row: " + Environment.NewLine
+ "Names: " + String.Join(", ", lstColumnNames) + Environment.NewLine
+ "Values: " + String.Join(", ", lstStringRowValues) + Environment.NewLine);
} else {
if (MainClass.bDebug)
{
for (int j = 0; j < lstStringRowValues.Count; j++)
{
Console.WriteLine(lstColumnNames[j] + " :: " + lstStringRowValues[j]);
}
}
for (int m=0; m<lstColumnNames.Count; m++) {
string strColName = lstColumnNames[m];
Column colFound = _table.getColumnByName(strColName);
if (null != colFound)
{
if (COLUMN_TYPES.AUTOINCREMENT == colFound.colType)
{
throw new Exception("Cannot insert a value into an autoincrement field: " + colFound.strColName);
}
byte[] abytVal = null; // "raw" value. Might not be the full column length.
BaseSerializer serializer = Router.routeMe(colFound);
abytVal = serializer.toByteArray(lstStringRowValues[m]);
// double check that the serializer at least
// gave you a value that's the right length so
// that everything doesn't go to heck (moved where
// that was previously checked into the serializers)
if (abytVal.Length != colFound.intColLength) {
throw new Exception("Improperly lengthed field from serializer: " + colFound.strColName + " :: " + lstColumnNames[m]);
}
dictValsToWriteByColName.Add(colFound.strColName, abytVal); // we'll put them in order with empty cols that weren't in the insert once we're done.
}
else // else the column name wasn't in this table. BORKED!
{
throw new Exception(strColName + " is not a valid column for " + strTableName + "; invalid INSERT.");
}
}
}
// once you have all the fields in the insert AND the table name, you have to reconcile
// against what columns have been left out to insert empty bytes for those.
// we'll cheat and do that by trolling through the _tblMgr.Columns and match up
// with those in the dictionary to create a row.
// note that we've already matched all the cols in the dictValsToWrite... with the
// colFound jive, above. We don't need to recheck that they're legit here.
Column[] allColumns = _table.getColumns();
abytFullRow = new byte[_table.intRowLength];
abytFullRow[0] = 0x11;
int intByteCounter = 1; // 1 b/c we inserted 0x11 in byte 0.
foreach (Column column in allColumns)
{
// So we already have a byte array, length matching the column's, full of 0x00
// (as that's a byte's default value in C#) in abytFullRow. That value only
// changes if we're got something to insert. We're "laying in" ranges of bytes
// like bricks into empty mortar when they exist.
if (dictValsToWriteByColName.ContainsKey(column.strColName)) {
byte[] abytColValue = dictValsToWriteByColName[column.strColName];
// keep in mind that column.intColLength should always match abytColValue.Length. While I'm
// testing, I'm going to put in this check, but at some point, you should be confident enough
// to consider removing this check.
if (abytColValue.Length != column.intColLength) {
throw new Exception("Surprising value and column length mismatch");
}
// Copy in value over our mortar of 0x00s.
Buffer.BlockCopy(abytColValue, 0, abytFullRow, intByteCounter, abytColValue.Length);
}
else if (COLUMN_TYPES.AUTOINCREMENT == column.colType)
{
if (column.intAutoIncrementCount >= 16777216)
{
throw new Exception("Autoincrement overflow. Congratulations. Column: " + column.strColName);
}
column.intAutoIncrementCount++;
intNewlyInsertedRowId = column.intAutoIncrementCount; // the return value for the function.
byte[] abytAutoIncrementValue = Utils.IntToByteArray(column.intAutoIncrementCount, 4); // NOTE: Changing from hard-coded 4 for AUTOINCREMENT length borks this
// This is the nasty bit. We need to increase the spot where we keep
// the greatest autoincrement value so that, in case we delete, we can
// still pick up where we left off. That is, because we increased the
// "column length", we have to serialize that change to the underlying file.
_table.updateAutoIncrementCount(column.strColName, column.intAutoIncrementCount); // Serialize the update to the autoincrement position.
Buffer.BlockCopy(abytAutoIncrementValue, 0, abytFullRow, intByteCounter, abytAutoIncrementValue.Length);
}
intByteCounter += column.intColLength; // keep track of how many bytes into the full row we've handled.
// insert the end of column 0x11 and increment the byte counter.
System.Buffer.BlockCopy(new byte[] { 0x11 }, 0, abytFullRow, intByteCounter++, 1);
// Note that we count it off whether we inserted something (the INSERT included this column or it was an AUTO_INCREMENT) or it didn't (we keep values all 0x00).
}
}
abytFullRow[abytFullRow.Length - 1] = 0x11; // insert final 0x11 to end the row
_table.writeRow(abytFullRow);
return intNewlyInsertedRowId;
}
public void addNewTable(TableContext tableNew)
{
_dictTables.Add(tableNew.strTableName, tableNew);
}
// 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;
}
private void _parseSelectStatement(string strSql)
{
int intTail = strSql.Length;
int intIndexOf = -1;
if (!strSql.ToLower().StartsWith("select") || !strSql.ToLower().Contains("from"))
{
throw new Exception("Invalid SELECT statement");
}
intIndexOf = strSql.IndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase);
if (-1 < intIndexOf)
{
this.strOrderBy = strSql.Substring(intIndexOf, intTail - intIndexOf);
this.strOrderBy = System.Text.RegularExpressions.Regex.Replace(this.strOrderBy, @"[\s\n]+", " "); // flatten whitespace to a single space.
intTail = intIndexOf;
}
intIndexOf = strSql.IndexOf("WHERE", StringComparison.CurrentCultureIgnoreCase);
if (-1 < intIndexOf)
{
this.strWhere = strSql.Substring(intIndexOf, intTail - intIndexOf);
intTail = intIndexOf;
}
intIndexOf = strSql.IndexOf("FROM", StringComparison.CurrentCultureIgnoreCase);
if (-1 < intIndexOf)
{
this.strFrom = strSql.Substring(intIndexOf, intTail - intIndexOf);
// Look for inner join.
// TODO: Another reserved word that we don't really want a table to be named: ("join").
this.strInnerJoinKludge = "";
if (this.strFrom.IndexOf(" join ", StringComparison.CurrentCultureIgnoreCase) > -1)
{
int intInnerJoin = this.strFrom.IndexOf("inner join", StringComparison.CurrentCultureIgnoreCase);
if (intInnerJoin > -1)
{
this.strInnerJoinKludge = this.strFrom.Substring(intInnerJoin);
this.strFrom = this.strFrom.Substring(0, intInnerJoin);
// Keep track of the join fields so we can intelligently select but
// not display them if they are/aren't in the SELECT.
// Let's start with the bullheaded way.
// The most natural place to find fields used to join "secondary
// tables" (any table after the first in the FROM list) would
// actually be in _processInnerJoin in SelectCommand, but this is
// already spaghettied enough. So let's dupe some logic and do it
// here.
// TODO: Consider deciphering lists of tables and fields in a
// refactored CommandParts and removing that from SelectCommand, etc.
string strMainTableName = this.strFrom.Substring(4).Trim();
string[] innerKludgeTokenized = this.strInnerJoinKludge.StringToNonWhitespaceTokens2();
Queue<string> qSecondaryTableNames = new Queue<string>();
for (int i=0; i<innerKludgeTokenized.Length; i++)
{
string toke = innerKludgeTokenized[i];
if (toke.ToUpper().StartsWith(strMainTableName.ToUpper()))
{
this.lstrJoinONLYFields.Add(toke.ReplaceCaseInsensitiveFind(strMainTableName + ".", ""));
}
else if (qSecondaryTableNames.Any(s => toke.ToUpper().StartsWith(s.ToUpper() + "."))) // TODO: this kinda suggests "." can't be in a table or column name either. Don't think we're checking that.
{
this.lstrJoinONLYFields.Add(toke);
}
// TODO: This makes JOIN a pretty hard keyword. I think that's safe, though.
// If you want a table named JOIN, it'll have to be in brackets, right?
if (toke.Equals("JOIN", StringComparison.CurrentCultureIgnoreCase))
{
qSecondaryTableNames.Enqueue(innerKludgeTokenized[i + 1]);
}
}
}
else
{
throw new SyntaxException("Statement includes `join` keyword. Currently, only inner joins are supported.");
}
}
this.strTableName = this.strFrom.Split()[1];
_tableContext = _database.getTableByName(this.strTableName);
if (null == _tableContext)
{
throw new Exception("Table does not exist: " + this.strTableName);
}
intTail = intIndexOf;
}
this.strSelect = strSql.Substring(0, intTail);
}
public void _parseStatement(string strSql)
{
int intTail = strSql.Length;
int intIndexOf = -1;
// TODO: Standardize on CurrentCultureIgnoreCase or ToLower.
if (!strSql.ToLower().StartsWith("delete") || !strSql.ToLower().Contains("from"))
{
throw new Exception("Invalid DELETE statement");
}
intIndexOf = strSql.IndexOf("WHERE", StringComparison.CurrentCultureIgnoreCase);
if (-1 < intIndexOf)
{
this.strWhere = strSql.Substring(intIndexOf, intTail - intIndexOf);
intTail = intIndexOf;
}
intIndexOf = strSql.IndexOf("FROM", StringComparison.CurrentCultureIgnoreCase);
if (-1 < intIndexOf)
{
this.strFrom = strSql.Substring(intIndexOf, intTail - intIndexOf);
this.strTableName = this.strFrom.Split()[1];
_tableContext = _database.getTableByName(this.strTableName);
if (null == _tableContext)
{
throw new Exception("Table does not exist: " + this.strTableName);
}
intTail = intIndexOf;
}
this.strDelete = strSql.Substring(0, intTail);
}
