예제 #1
0
        internal string GetCommandTextForBatching()
        {
            if (batchableCommandText == null)
            {
                // if the command starts with insert and is "simple" enough, then
                // we can use the multi-value form of insert
                if (String.Compare(CommandText.Substring(0, 6), "INSERT", true) == 0)
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection);
                    string sql_mode = cmd.ExecuteScalar().ToString().ToLower(CultureInfo.InvariantCulture);
                    SqlTokenizer tokenizer = new SqlTokenizer(CommandText);
                    tokenizer.AnsiQuotes = sql_mode.IndexOf("ansi_quotes") != -1;
                    tokenizer.BackslashEscapes = sql_mode.IndexOf("no_backslash_escapes") == -1;
                    string token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
                    while (token != null)
                    {
                        if (token.ToLower(CultureInfo.InvariantCulture) == "values" && 
                            !tokenizer.Quoted)
                        {
                            token = tokenizer.NextToken();
                            Debug.Assert(token == "(");
                            while (token != null && token != ")")
                            {
                                batchableCommandText += token;
                                token = tokenizer.NextToken();
                            }
                            if (token != null)
                                batchableCommandText += token;
                            token = tokenizer.NextToken();
                            if (token != null && (token == "," || 
                                token.ToLower(CultureInfo.InvariantCulture) == "on"))
                            {
                                batchableCommandText = null;
                                break;
                            }
                        }
                        token = tokenizer.NextToken();
                    }
                }
                if (batchableCommandText == null)
                    batchableCommandText = CommandText;
            }

            return batchableCommandText;
        }
예제 #2
0
        private void ParseProcedureBody(DataTable parametersTable, string body,
            DataRow row, string nameToRestrict)
        {
            ArrayList modes = new ArrayList(new string[3] { "IN", "OUT", "INOUT" });

            string sqlMode = row["SQL_MODE"].ToString();

            int pos = 1;
            SqlTokenizer tokenizer = new SqlTokenizer(body);
            tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
            tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;

            string token = tokenizer.NextToken();

            // this block will scan for the opening paren while also determining
            // if this routine is a function.  If so, then we need to add a
            // parameter row for the return parameter since it is ordinal position
            // 0 and should appear first.
            while (token != "(")
            {
                if (String.Compare(token, "FUNCTION", true) == 0 &&
                    nameToRestrict == null)
                {
                    parametersTable.Rows.Add(parametersTable.NewRow());
                    InitParameterRow(row, parametersTable.Rows[0]);
                }
                token = tokenizer.NextToken();
            }
            token = tokenizer.NextToken();  // now move to the next token past the (

            while (token != ")")
            {
                DataRow parmRow = parametersTable.NewRow();
                InitParameterRow(row, parmRow);
                parmRow["ORDINAL_POSITION"] = pos++;

                // handle mode and name for the parameter
                string mode = token.ToUpper(CultureInfo.InvariantCulture);
                if (!tokenizer.Quoted && modes.Contains(mode))
                {
                    parmRow["PARAMETER_MODE"] = mode;
                    token = tokenizer.NextToken();
                }
                parmRow["PARAMETER_NAME"] = token;

                // now parse data type
                token = ParseDataType(parmRow, tokenizer);
                if (token == ",")
                    token = tokenizer.NextToken();

                // now determine if we should include this row after all
                // we need to parse it before this check so we are correctly
                // positioned for the next parameter
                if (nameToRestrict == null ||
                    String.Compare(parmRow["PARAMETER_NAME"].ToString(), nameToRestrict, true) == 0)
                    parametersTable.Rows.Add(parmRow);
            }

            // now parse out the return parameter if there is one.
            token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
            if (String.Compare(token, "returns", true) == 0)
            {
                DataRow parameterRow = parametersTable.Rows[0];
                parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
                ParseDataType(parameterRow, tokenizer);
            }
        }
예제 #3
0
        /// <summary>
        ///  Parses out the elements of a procedure parameter data type.
        /// </summary>
        private string ParseDataType(DataRow row, SqlTokenizer tokenizer)
        {
            StringBuilder dtd = new StringBuilder(
                tokenizer.NextToken().ToUpper(CultureInfo.InvariantCulture));
            row["DATA_TYPE"] = dtd.ToString();
            string type = row["DATA_TYPE"].ToString();

            string token = tokenizer.NextToken();
            if (tokenizer.IsSize)
            {
                dtd.AppendFormat(CultureInfo.InvariantCulture, "({0})", token);
                if (type != "ENUM" && type != "SET")
                    ParseDataTypeSize(row, token);
                token = tokenizer.NextToken();
            }
            else
                dtd.Append(GetDataTypeDefaults(type, row));

            while (token != ")" && token != "," && String.Compare(token, "begin", true) != 0)
            {
                if (String.Compare(token, "CHARACTER", true) == 0 ||
                    String.Compare(token, "BINARY", true) == 0)
                { }  // we don't need to do anything with this
                else if (String.Compare(token, "SET", true) == 0 ||
                         String.Compare(token, "CHARSET", true) == 0)
                    row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
                else if (String.Compare(token, "ASCII", true) == 0)
                    row["CHARACTER_SET_NAME"] = "latin1";
                else if (String.Compare(token, "UNICODE", true) == 0)
                    row["CHARACTER_SET_NAME"] = "ucs2";
                else if (String.Compare(token, "COLLATE", true) == 0)
                    row["COLLATION_NAME"] = tokenizer.NextToken();
                else
                    dtd.AppendFormat(CultureInfo.InvariantCulture, " {0}", token);
                token = tokenizer.NextToken();
            }

            if (dtd.Length > 0)
                row["DTD_IDENTIFIER"] = dtd.ToString();

            // now default the collation if one wasn't given
            if (row["COLLATION_NAME"].ToString().Length == 0)
                row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(
                    row["CHARACTER_SET_NAME"].ToString(), connection);

            // now set the octet length
            if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                row["CHARACTER_OCTET_LENGTH"] =
                    CharSetMap.GetMaxLength(row["CHARACTER_SET_NAME"].ToString(), connection) *
                    (int)row["CHARACTER_MAXIMUM_LENGTH"];

            return token;
        }
예제 #4
0
        /// <summary>
        /// Attempts to parse a given token as a type attribute.
        /// </summary>
        /// <returns>True if the token was recognized as a type attribute,
        /// false otherwise.</returns>
        private static bool SetParameterAttribute(DataRow row, string token, bool isSize,
            SqlTokenizer tokenizer)
        {
            string lcDataType = row["DATA_TYPE"].ToString().ToLower(CultureInfo.InvariantCulture);
            
            if (isSize)
            {
                // if the data type if set or enum, then nothing to do.
                if (lcDataType == "enum" || lcDataType == "set") return true;

                string[] sizeParts = token.Split(new char[] { ',' });
                if (MetaData.IsNumericType(lcDataType))
                    row["NUMERIC_PRECISION"] = Int32.Parse(sizeParts[0]);
                else
                    row["CHARACTER_OCTET_LENGTH"] = Int32.Parse(sizeParts[0]);
                if (sizeParts.Length == 2)
                    row["NUMERIC_SCALE"] = Int32.Parse(sizeParts[1]);
                return true;
            }
            else
            {
                string lowerToken = token.ToLower(CultureInfo.InvariantCulture);
                switch (lowerToken)
                {
                    case "unsigned":
                    case "zerofill":
                        row["FLAGS"] = String.Format("{0} {1}", row["FLAGS"], token);
                        return true;
                    case "character":
                    case "charset":
                        if (lowerToken == "character")
                        {
                            string set = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
                            Debug.Assert(set == "set");
                        }
                        row["CHARACTER_SET"] = tokenizer.NextToken();
                        return true;
                    case "ascii":
                        row["CHARACTER_SET"] = "latin1";
                        return true;
                    case "unicode":
                        row["CHARACTER_SET"] = "ucs2";
                        return true;
                    case "binary":
                        return true;
                }
            }
            return false;
        }
예제 #5
0
        private string GetProcedureParameterLine(DataRow isRow)
        {
            string sql = "SHOW CREATE {0} `{1}`.`{2}`";
            sql = String.Format(sql, isRow["ROUTINE_TYPE"], isRow["ROUTINE_SCHEMA"],
                isRow["ROUTINE_NAME"]);
            MySqlCommand cmd = new MySqlCommand(sql, connection);
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                reader.Read();

                // if we are not the owner of this proc or have permissions
                // then we will get null for the body
                if (reader.IsDBNull(2)) return null;

                string sql_mode = reader.GetString(1);

                string body = reader.GetString(2);
                SqlTokenizer tokenizer = new SqlTokenizer(body);
                tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
                tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;

                string token = tokenizer.NextToken();
                while (token != "(")
                    token = tokenizer.NextToken();
                int start = tokenizer.Index + 1;
                token = tokenizer.NextToken();
                while (token != ")" || tokenizer.Quoted)
                {
                    token = tokenizer.NextToken();
                    // if we see another ( and we are not quoted then we
                    // are in a size element and we need to look for the closing paren
                    if (token == "(" && !tokenizer.Quoted)
                    {
                        while (token != ")" || tokenizer.Quoted)
                            token = tokenizer.NextToken();
                        token = tokenizer.NextToken();
                    }
                }
                return body.Substring(start, tokenizer.Index - start);
            }
        }
예제 #6
0
        private void ParseProcedureBody(DataTable parametersTable, string body,
            DataRow row, string nameToRestrict)
        {
            string sqlMode = row["SQL_MODE"].ToString();

            int pos = 1;
            SqlTokenizer tokenizer = new SqlTokenizer(body);
            tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
            tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
            string token = tokenizer.NextToken();

            // look for the opening paren
            while (token != "(")
                token = tokenizer.NextToken();

            while (token != ")")
            {
                token = tokenizer.NextToken();
                if (token == ")") break; /* handle the case where there are no parms */
                DataRow parmRow = parametersTable.NewRow();
                InitParameterRow(row, parmRow);
                parmRow["ORDINAL_POSITION"] = pos++;
                string parameterName = token;
                if (!tokenizer.Quoted)
                {
                    string mode = null;
                    string lowerToken = token.ToLower(CultureInfo.InvariantCulture);
                    if (lowerToken == "in")
                        mode = "IN";
                    else if (lowerToken == "inout")
                        mode = "INOUT";
                    else if (lowerToken == "out")
                        mode = "OUT";
                    if (mode != null)
                    {
                        parmRow["PARAMETER_MODE"] = mode;
                        parameterName = tokenizer.NextToken();
                    }
                }
                parmRow["PARAMETER_NAME"] = String.Format("{0}{1}",
                    connection.ParameterMarker, parameterName);
                token = ParseDataType(parmRow, tokenizer);
                if (nameToRestrict == null ||
                  parmRow["PARAMETER_NAME"].ToString().ToLower() ==
                nameToRestrict)
                    parametersTable.Rows.Add(parmRow);
            }

            // now parse out the return parameter if there is one.
            token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
            if (token == "returns")
            {
                DataRow parameterRow = parametersTable.NewRow();
                InitParameterRow(row, parameterRow);
                parameterRow["PARAMETER_NAME"] = String.Format("{0}RETURN_VALUE",
                    connection.ParameterMarker);
                parameterRow["IS_RESULT"] = "YES";
                ParseDataType(parameterRow, tokenizer);
                parametersTable.Rows.Add(parameterRow);
            }
        }
예제 #7
0
 /// <summary>
 ///  Parses out the elements of a procedure parameter data type.
 /// </summary>
 private static string ParseDataType(DataRow row, SqlTokenizer tokenizer)
 {
     row["DATA_TYPE"] = tokenizer.NextToken().ToUpper(CultureInfo.InvariantCulture);
     string token = tokenizer.NextToken();
     while (SetParameterAttribute(row, token, tokenizer.IsSize, tokenizer))
         token = tokenizer.NextToken();
     return token;
 }
예제 #8
0
        private List<ScriptStatement> BreakIntoStatements(bool ansiQuotes, bool noBackslashEscapes)
        {
            int startPos = 0;
            List<ScriptStatement> statements = new List<ScriptStatement>();
            List<int> lineNumbers = BreakScriptIntoLines();
            SqlTokenizer tokenizer = new SqlTokenizer(query);

            tokenizer.AnsiQuotes = ansiQuotes;
            tokenizer.BackslashEscapes = !noBackslashEscapes;

            string token = tokenizer.NextToken();
            while (token != null)
            {
                if (!tokenizer.Quoted &&
                    !tokenizer.IsSize)
                {
                    int delimiterPos = token.IndexOf(Delimiter);
                    if (delimiterPos != -1)
                    {
                        int endPos = tokenizer.Index - token.Length + delimiterPos;
                        if (tokenizer.Index == query.Length-1)
                            endPos++;
                        string currentQuery = query.Substring(startPos, endPos-startPos);
                        ScriptStatement statement = new ScriptStatement();
                        statement.text = currentQuery.Trim();
                        statement.line = FindLineNumber(startPos, lineNumbers);
                        statement.position = startPos - lineNumbers[statement.line];
                        statements.Add(statement);
                        startPos = endPos + delimiter.Length;
                    }
                }
                token = tokenizer.NextToken();
            }

            // now clean up the last statement
            if (tokenizer.Index > startPos)
            {
                string sqlLeftOver = query.Substring(startPos).Trim();
                if (!String.IsNullOrEmpty(sqlLeftOver))
                {
                    ScriptStatement statement = new ScriptStatement();
                    statement.text = sqlLeftOver;
                    statement.line = FindLineNumber(startPos, lineNumbers);
                    statement.position = startPos - lineNumbers[statement.line];
                    statements.Add(statement);
                }
            }
            return statements;
        }
예제 #9
0
 private ArrayList ParseColumns(SqlTokenizer tokenizer)
 {
     ArrayList sc = new ArrayList();
     string token = tokenizer.NextToken();
     while (token != ")")
     {
         if (token != ",")
             sc.Add(token);
         token = tokenizer.NextToken();
     }
     return sc;
 }
예제 #10
0
        private void ParseConstraint(DataTable fkTable, DataRow table, 
            SqlTokenizer tokenizer, bool includeColumns)
        {
            string name = tokenizer.NextToken();
            DataRow row = fkTable.NewRow();

            // make sure this constraint is a FK
            string token = tokenizer.NextToken();
            if (token != "foreign" || tokenizer.Quoted)
                return;
            tokenizer.NextToken(); // read off the 'KEY' symbol
            tokenizer.NextToken(); // read off the '(' symbol

            row["CONSTRAINT_CATALOG"] = table["TABLE_CATALOG"];
            row["CONSTRAINT_SCHEMA"] = table["TABLE_SCHEMA"];
            row["TABLE_CATALOG"] = table["TABLE_CATALOG"];
            row["TABLE_SCHEMA"] = table["TABLE_SCHEMA"];
            row["TABLE_NAME"] = table["TABLE_NAME"];
            row["REFERENCED_TABLE_CATALOG"] = null;
            row["CONSTRAINT_NAME"] = name;

            ArrayList srcColumns = includeColumns ? ParseColumns(tokenizer) : null;

            // now look for the references section
            while (token != "references" || tokenizer.Quoted)
                token = tokenizer.NextToken();
            string target1 = tokenizer.NextToken();
            string target2 = tokenizer.NextToken();
            if (target2.StartsWith("."))
            {
                row["REFERENCED_TABLE_SCHEMA"] = target1;
                row["REFERENCED_TABLE_NAME"] = target2.Substring(1);
                tokenizer.NextToken();  // read off the '('
            }
            else
            {
                row["REFERENCED_TABLE_SCHEMA"] = table["TABLE_SCHEMA"];
                row["REFERENCED_TABLE_NAME"] = target1;
            }

            // if we are supposed to include columns, read the target columns
            ArrayList targetColumns = includeColumns ? ParseColumns(tokenizer) : null;

            if (includeColumns)
                ProcessColumns(fkTable, row, srcColumns, targetColumns);
            else
                fkTable.Rows.Add(row);
        }
예제 #11
0
        /// <summary>
        /// GetForeignKeysOnTable retrieves the foreign keys on the given table.
        /// Since MySQL supports foreign keys on versions prior to 5.0, we can't  use
        /// information schema.  MySQL also does not include any type of SHOW command
        /// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing
        /// the output.
        /// </summary>
        /// <param name="fkTable">The table to store the key info in.</param>
        /// <param name="tableToParse">The table to get the foeign key info for.</param>
        /// <param name="filterName">Only get foreign keys that match this name.</param>
		/// <param name="includeColumns">Should column information be included in the table.</param>
        private void GetForeignKeysOnTable(DataTable fkTable, DataRow tableToParse,
                                           string filterName, bool includeColumns)
        {
            string sqlMode = GetSqlMode();

            if (filterName != null)
                filterName = filterName.ToLower(CultureInfo.InvariantCulture);

            string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`",
                                       tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]);
            string lowerBody = null, body = null;
            MySqlCommand cmd = new MySqlCommand(sql, connection);
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                reader.Read();
                body = reader.GetString(1);
                lowerBody = body.ToLower(CultureInfo.InvariantCulture);
            }

            SqlTokenizer tokenizer = new SqlTokenizer(lowerBody);
            tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
            tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
            
            while (true)
            {
                string token = tokenizer.NextToken();
                // look for a starting contraint
                while (token != null && (token != "constraint" || tokenizer.Quoted))
                    token = tokenizer.NextToken();
                if (token == null) break;

                ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns);
            }
        }
예제 #12
0
        /// <summary>
        /// THis code is not used yet but will likely be used in the future.
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private ArrayList TokenizeSql2(string sql) 
        {
            ArrayList sqlChunks = new ArrayList();
            StringBuilder currentChunk = new StringBuilder();
            bool batch = Connection.Settings.AllowBatch & Driver.SupportsBatch;

            int lastPos = 0;
            SqlTokenizer tokenizer = new SqlTokenizer(sql);
            string sql_mode = Connection.driver.Property("sql_mode");
            if (sql_mode != null)
            {
                sql_mode = sql_mode.ToString().ToLower();
                tokenizer.AnsiQuotes = sql_mode.IndexOf("ansi_quotes") != -1;
                tokenizer.BackslashEscapes = sql_mode.IndexOf("no_backslash_escapes") == -1;
            }

            string token = tokenizer.NextToken();
            while (token != null)
            {
                if (token == ";" && !batch)
                {
                    sqlChunks.Add(currentChunk.ToString());
                    currentChunk.Remove(0, currentChunk.Length);
                }

                else if (token.Length >= 2 &&
                    ((token[0] == '@' && token[1] != '@') ||
                    token[0] == '?'))
                {
                    sqlChunks.Add(currentChunk.ToString());
                    currentChunk.Remove(0, currentChunk.Length);
                }
                else
                {
                    currentChunk.Append(sql.Substring(lastPos, tokenizer.Index - lastPos+1));
                    lastPos = tokenizer.Index;
                }
                token = tokenizer.NextToken();
            }
            if (currentChunk.Length > 0)
                sqlChunks.Add(currentChunk.ToString());
            return sqlChunks;
        }