Esempio n. 1
0
        // 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);
        }
Esempio n. 2
0
        /// <summary>
        /// Processes a CREATE TABLE statement.
        /// Throws any Exception on any failure.
        /// </summary>
        /// <param name="strSql"></param>
        public object executeStatement(string strSql)
        {
            string strErr = "";

            Match         createTableMatch = Regex.Match(strSql, @"^CREATE\s*TABLE\s*`?\w*`?\s*\(", RegexOptions.IgnoreCase);
            List <string> lstRawNames      = new List <string>();

            if (createTableMatch.Success)
            {
                string strTableName = Regex.Replace(createTableMatch.Groups[0].Value, @"\r\n?|\n", ""); // remove newlines with http://stackoverflow.com/a/8196219/1028230
                strTableName = strTableName.Substring(0, strTableName.ToLower().IndexOf("("));
                strTableName = strTableName.Substring(strTableName.ToLower().IndexOf("table") + 5).Trim().Trim('`');

                if (null != _database.getTableByName(strTableName))
                {
                    strErr += "Table " + strTableName + " already exists.\n";
                }
                else
                {
                    // Else this IS a legitimate location for a table file.  Begin.
                    // Store table loc in TableContext
                    _table = new TableContext();

                    // initialize rows with the 11 byte.
                    _lstByteDataTypeRow.Add(0x11);
                    _lstByteColNames.Add(0x11);

                    string strColInfo = strSql.Substring(strSql.IndexOf("(") + 1);       // get rid of everything up until the first open parens.
                    strColInfo = strColInfo.Substring(0, strColInfo.LastIndexOf(")"));   // peel off the last closed parens.

                    string[] astrSections = Regex.Split(strColInfo, ",");

                    for (int i = 0; i < astrSections.Length; i++)
                    {
                        COLUMN_TYPES?colType        = null; // This really should never be null after running through the code.  It'll throw an exception first.
                        string       strColName     = "";
                        int          intFieldLength = -1;

                        string strNextColumnInfo = astrSections[i].Trim();

                        // If we're defining a primary key, which we don't support (yet, if ever), skip the line.
                        // Else do the normal thing.
                        if (strNextColumnInfo.StartsWith("PRIMARY", StringComparison.CurrentCultureIgnoreCase))
                        {
                            SqlDbSharpLogger.LogMessage("Primary key creation is currently ignored: " + strNextColumnInfo, "CreateTable executeStatement");
                        }
                        else
                        {
                            string[] astrColInfo = strNextColumnInfo.StringToNonWhitespaceTokens2();

                            if (astrColInfo.Length < 2)
                            {
                                strErr += "Illegal column defintion; table not created: " + string.Join(":", astrColInfo) + "#\n";
                            }
                            else
                            {
                                //=====================
                                //======= DEBUG =======
                                //=====================
                                if (MainClass.bDebug)
                                {
                                    for (int j = 0; j < astrColInfo.Length; j++)
                                    {
                                        SqlDbSharpLogger.LogMessage(j + " :: " + astrColInfo[j], "Create table execute statement");
                                    }
                                }
                                //======================
                                //======================
                                if (3 <= astrColInfo.Length)
                                {
                                    int intLength;
                                    if (int.TryParse(astrColInfo[2], out intLength))
                                    {
                                        if (4369 == intLength)
                                        {
                                            throw new Exception("Idiosyncratically, column lengths of [exactly] 4369 are not allowed. " + astrColInfo[1]);
                                        }
                                        intFieldLength = intLength;
                                    }
                                    else
                                    {
                                        // We're going to step up from defaulting to a length of 20 in each case to
                                        // defining a default length for each column data type.
                                        intFieldLength = -1;
                                    }
                                }

                                // every column declaration has already been checked to ensure it has at least two entries (checked above)
                                // TODO: Check for statements expecting the default length but with modifiers, like
                                // `id` INT NOT NULL AUTO_INCREMENT DEFAULT NULL,
                                // This is handled with AUTO_INCREMENT because NOT NULL is thrown out as if it had the length.
                                // Which is to say, NOT NULL isn't passed along as part of the modifier string.
                                strColName = astrColInfo[0].Trim('`');
                                string strModifier = null;
                                if (astrColInfo.Length > 3)
                                {
                                    strModifier = string.Join(" ", astrColInfo, 3, astrColInfo.Length - 3);
                                }

                                colType = InfrastructureUtils.colTypeFromString(astrColInfo[1], intFieldLength, strModifier);

                                if (null == colType)
                                {
                                    strErr += "Illegal/Supported column type: " + astrColInfo[1] + "\n";
                                }
                                else
                                {
                                    COLUMN_TYPES colTypeCleaned = (COLUMN_TYPES)colType;    // got to be a better way to launder a nullable.
                                    if (intFieldLength < 0)
                                    {
                                        intFieldLength = _getDefaultLengthForType(colTypeCleaned);
                                    }
                                    string strRawName = strColName.Length > intFieldLength?strColName.Substring(0, intFieldLength) : strColName;

                                    IEnumerable <string> coveredNames = lstRawNames.Where(name => name.StartsWith(strRawName, StringComparison.CurrentCultureIgnoreCase) ||
                                                                                          strRawName.StartsWith(name, StringComparison.CurrentCultureIgnoreCase));
                                    if (coveredNames.Count() > 0)
                                    {
                                        throw new Exception(string.Format(@"Field names would ""cover"" each other: `{0}` and `{1}`", strRawName, coveredNames.First()));
                                    }
                                    lstRawNames.Add(strRawName);
                                    _createColumn(strColName, colTypeCleaned, intFieldLength);
                                }

                                if (!strErr.Equals(""))
                                {
                                    break;
                                }
                            }
                        } // end check for unsupported directives (like defining a primary key)
                    }     // eo table column creation for loop

                    if (MainClass.bDebug)
                    {
                        string strDebug = string.Empty;
                        for (int j = 0; j < _lstByteDataTypeRow.Count; j++)
                        {
                            strDebug += "0x" + _lstByteDataTypeRow[j].ToString("X2") + ", \n";
                        }
                        strDebug += "\n\n";

                        for (int j = 0; j < _lstByteColNames.Count; j++)
                        {
                            strDebug += "0x" + _lstByteColNames[j].ToString("X2") + ", \n";
                        }
                        strDebug += "\n";
                        strDebug += _table.strTableFileLoc + "\n";

                        SqlDbSharpLogger.LogMessage(strDebug, "Create table execute statement");
                    }

                    // TODO: Instead of writing bytes here, I should probably create a list of column objects,
                    // with name, length, and COLUMN_TYPE for each, then let the table manager worry about
                    // bytes and specific implmentations.
                    _table.writeMetadataRowsAndPrepareNewTable(_lstByteDataTypeRow, _lstByteColNames, strTableName, _database.strDbLoc);
                    _database.addNewTable(_table);
                } // eo table exists check.
            }     // eo createTableMatch.Success regex check
            else
            {
                strErr += "SYNTAX ERROR: Illegal Create Table Statement" + System.Environment.NewLine;
                // go ahead and throw specific error type.
                throw new SyntaxException(strErr);
            }

            strErr = strErr.Equals("") ? "Table created successfully." : "Create table error" + System.Environment.NewLine + strErr;
            return(strErr);
        }
Esempio n. 3
0
        private DataTable _processInnerJoin(Queue <TableContext> qAllTables, DataTable dtReturn, string strJoinText,
                                            string strParentTable, string strOrderBy, Queue <string> qInnerJoinFields)
        {
            SqlDbSharpLogger.LogMessage("Note that WHERE clauses are not yet applied to JOINed tables.", "SelectCommnd _processInnerJoin");

            string strNewTable = null;
            string strNewField = null;
            string strOldField = null;
            string strOldTable = null;

            string strErrLoc = "init";

            Queue <string> qColsToSelectInNewTable = new Queue <string>();

            MainClass.logIt("join fields: " + string.Join("\n", qInnerJoinFields.ToArray()));

            try
            {
                strJoinText = System.Text.RegularExpressions.Regex.Replace(strJoinText, @"\s\n+", " ");
                string[] astrInnerJoins = strJoinText.ToLower().Split(new string[] { "inner join" }, StringSplitOptions.RemoveEmptyEntries);
                Dictionary <string, DataTable> dictTables = new Dictionary <string, DataTable>();
                dictTables.Add(strParentTable.ToLower(), dtReturn);

                strErrLoc = "starting inner join array";
                foreach (string strInnerJoin in astrInnerJoins)
                {
                    // "from table1 inner join" <<< already removed
                    // "table2 on table1.field = table2.field2"
                    string[] astrTokens = strInnerJoin.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);

                    if (!"=".Equals(astrTokens[3]))
                    {
                        throw new Exception("We're only supporting inner equi joins right now: " + strInnerJoin);
                    }

                    // Kludge alert -- must have table prefixes for now.
                    if (!astrTokens[2].Contains(".") || !astrTokens[4].Contains("."))
                    {
                        throw new Exception(string.Format(
                                                "For now, joined fields must include table prefixes: {0} {1}",
                                                astrTokens[2],
                                                astrTokens[2])
                                            );
                    }

                    strErrLoc = "determine old and new tables and fields";
                    string field1Parent = astrTokens[2].Substring(0, astrTokens[2].IndexOf("."));
                    string field2Parent = astrTokens[4].Substring(0, astrTokens[4].IndexOf("."));
                    string field1       = astrTokens[2].Substring(astrTokens[2].IndexOf(".") + 1);
                    string field2       = astrTokens[4].Substring(astrTokens[4].IndexOf(".") + 1);

                    if (dictTables.ContainsKey(field1Parent))   // TODO: Should probably check to see if they're both known and at least bork.
                    {
                        strNewTable = field2Parent;
                        strNewField = field2;
                        strOldTable = field1Parent;
                        strOldField = field1;
                    }
                    else
                    {
                        strNewTable = field1Parent;
                        strNewField = field1;
                        strOldTable = field2Parent;
                        strOldField = field2;
                    }

                    MainClass.logIt(string.Format(@"old table: {0} 
old field: {1} 
new table: {2} 
new field: {3}",
                                                  strOldTable, strOldField, strNewTable, strNewField));

                    string strInClause = string.Empty;

                    TableContext tableOld = _database.getTableByName(strOldTable);
                    TableContext tableNew = _database.getTableByName(strNewTable);
                    qAllTables.Enqueue(tableNew);   // we need this to figure out column parents later.

                    // Now that we know the new table to add, we need to get a list of columns
                    // to select from it.
                    // Sources for these fields could be...
                    // 1.) The joining field.
                    // 2.) ORDER BY fields for the entire statement
                    // 3.) conventional SELECT fields.
                    //
                    // To prevent column name collision, let's go ahead and prefix them
                    // all with the table name.  A little unexpected, but a decent shortcut
                    // for now, I think.

                    strErrLoc = "beginning inner join select construction";

                    // 1.) Add the joining field.
                    qColsToSelectInNewTable.EnqueueIfNotContains(strNewField);

                    // 2.) ORDER BY fields that belong to this table.
                    if (!string.IsNullOrWhiteSpace(strOrderBy))
                    {
                        MainClass.logIt(strOrderBy);

                        strErrLoc = "constructing order by";
                        string[] astrOrderTokens = strOrderBy.StringToNonWhitespaceTokens2();
                        for (int i = 2; i < astrOrderTokens.Length; i++)
                        {
                            string strOrderField = astrOrderTokens[i].Trim(' ', ',');
                            string strOrderTable = strNewTable; // just to pretend. We'll skip it if the field doesn't exist here.  Course this means we might dupe some non-table prefixed fields.

                            if (strOrderField.Contains("."))
                            {
                                strOrderTable = strOrderField.Substring(0, strOrderField.IndexOf("."));
                                strOrderField = strOrderField.Substring(strOrderField.IndexOf(".") + 1);
                            }

                            if (strNewTable.Equals(strOrderTable, StringComparison.CurrentCultureIgnoreCase) &&
                                !tableNew.containsColumn(strOrderField, false) &&
                                tableNew.containsColumn(strOrderField, true))
                            {
                                qColsToSelectInNewTable.EnqueueIfNotContains(strNewTable + "." + strOrderField);
                            }
                        }
                    }

                    // 3.) Conventional SELECT fields
                    strErrLoc = "Creating select clause";
                    if (qInnerJoinFields.Count > 0)
                    {
                        if (qInnerJoinFields.Any(fld => fld.Equals(strNewTable + ".*", StringComparison.CurrentCultureIgnoreCase) || fld.Equals("*")))
                        {
                            qColsToSelectInNewTable.EnqueueIfNotContains("*");
                        }
                        else
                        {
                            foreach (string strTableDotCol in qInnerJoinFields)
                            {
                                string[] astrTableDotCol = strTableDotCol.Split('.');
                                if (strNewTable.Equals(astrTableDotCol[0], StringComparison.CurrentCultureIgnoreCase))
                                {
                                    MainClass.logIt("Adding field to join SELECT: " + astrTableDotCol[1].ScrubValue());
                                    qColsToSelectInNewTable.EnqueueIfNotContains(astrTableDotCol[1].ScrubValue()); // again, offensive parsing is the rule.
                                }
                            }
                        }
                    }

                    // TODO: Consider grabbing every column up front, perhaps, and
                    // then cutting out those columns that we didn't select -- but
                    // do SELECT fields as a post-processing task, rather than this
                    // inline parsing. Also allows us to bork on fields that aren't
                    // in tables a little easier; right now you could include bogus
                    // joined fields without error.

                    dictTables[strOldTable].CaseSensitive = false;  // TODO: If we keep this, do it in a smarter place.
                    // Right now, strOldField has the name that's in the DataTable from the previous select.

                    strErrLoc = @"Create WHERE IN clause for join ""inner"" SELECT";
                    // Now construct the `WHERE joinedField IN (X,Y,Z)` portion of the inner select
                    // we're about to fire off.
                    string strOperativeOldField = strOldField;
                    if (!dictTables[strOldTable].Columns.Contains(strOldField))
                    {
                        // Allow fuzzy names in join, if not the DataTable -- yet.
                        strOperativeOldField = tableOld.getRawColName(strOldField);
                    }

                    if (MainClass.bDebug)
                    {
                        Console.WriteLine("Looking for " + strOperativeOldField + " in the columns");
                        foreach (DataColumn column in dictTables[strOldTable].Columns)
                        {
                            Console.WriteLine(column.ColumnName);
                        }
                    }

                    foreach (DataRow row in dictTables[strOldTable].Rows)
                    {
                        strInClause += row[strOperativeOldField].ToString() + ",";
                    }

                    strErrLoc   = "Completing SELECT construction";
                    strInClause = strInClause.Trim(',');
                    if (string.IsNullOrEmpty(strInClause))
                    {
                        dtReturn = new DataTable();
                    }
                    else
                    {
                        MainClass.logIt("Columns in inner JOIN select: " + string.Join(", ", qColsToSelectInNewTable.ToArray()));
                        string strInnerSelect = string.Format("SELECT {0} FROM {1} WHERE {2} IN ({3});",
                                                              string.Join(",", qColsToSelectInNewTable.ToArray()),
                                                              strNewTable,
                                                              strNewField,
                                                              strInClause
                                                              );
                        qColsToSelectInNewTable = new Queue <string>();
                        strErrLoc = strInnerSelect;

                        // TODO: Figure out the best time to handle the portion of the WHERE
                        // that impacts the tables mentioned in the join portion of the SQL.
                        // Note: I think now we treat it just like the ORDER BY.  Not that
                        // complicated to pull out table-specific WHERE fields and send along
                        // with the reconsitituted "inner" SQL statement.

                        MainClass.logIt("Inner join: " + strInnerSelect + "\n\n", "select command _processInnerJoin");

                        SelectCommand selectCommand = new SelectCommand(_database);
                        object        objReturn     = selectCommand.executeStatement(strInnerSelect);

                        if (objReturn is DataTable)
                        {
                            DataTable dtInnerJoinResult = (DataTable)objReturn;
                            dtReturn = InfrastructureUtils.equijoinTables(
                                dtReturn,
                                dtInnerJoinResult,
                                strOperativeOldField,
                                strNewField
                                );
                        }
                        else
                        {
                            strErrLoc = "Datatable not returned: " + strInnerSelect;
                            throw new SyntaxException("Illegal inner select: " + strInnerSelect);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (e.GetType() == typeof(SyntaxException))
                {
                    throw e;
                }
                else
                {
                    throw new SyntaxException(string.Format(@"Uncaptured join syntax error -- {0}: 
{1} 
{2}", strErrLoc, strJoinText, e.ToString()));
                }
            }

            return(dtReturn);
        }