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.
}
// 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;
}
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);
}
/// <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;
}
