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; }