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