Beispiel #1
0
        public virtual MySqlSchemaCollection GetUDF(string[] restrictions)
        {
            string sql = "SELECT name,ret,dl FROM mysql.func";

            if (restrictions != null)
            {
                if (restrictions.Length >= 1 && !String.IsNullOrEmpty(restrictions[0]))
                {
                    sql += String.Format(" WHERE name LIKE '{0}'", restrictions[0]);
                }
            }

            MySqlSchemaCollection dt = new MySqlSchemaCollection("User-defined Functions");

            dt.AddColumn("NAME", typeof(string));
            dt.AddColumn("RETURN_TYPE", typeof(int));
            dt.AddColumn("LIBRARY_NAME", typeof(string));

            MySqlCommand cmd = new MySqlCommand(sql, connection);

            try
            {
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        MySqlSchemaRow row = dt.AddRow();
                        row[0] = reader.GetString(0);
                        row[1] = reader.GetInt32(1);
                        row[2] = reader.GetString(2);
                    }
                }
            }
            catch (MySqlException ex)
            {
                if (ex.Number != (int)MySqlErrorCode.TableAccessDenied)
                {
                    throw;
                }
                throw new MySqlException(Resources.UnableToEnumerateUDF, ex);
            }

            return(dt);
        }
Beispiel #2
0
        private static string GetDataTypeDefaults(string type, MySqlSchemaRow row)
        {
            string format    = "({0},{1})";
            object precision = row["NUMERIC_PRECISION"];

            if (MetaData.IsNumericType(type) &&
                string.IsNullOrEmpty(( string )row["NUMERIC_PRECISION"]))
            {
                row["NUMERIC_PRECISION"] = 10;
                row["NUMERIC_SCALE"]     = 0;
                if (!MetaData.SupportScale(type))
                {
                    format = "({0})";
                }
                return(String.Format(format, row["NUMERIC_PRECISION"],
                                     row["NUMERIC_SCALE"]));
            }
            return(String.Empty);
        }
Beispiel #3
0
        private static ParameterDirection GetDirection(MySqlSchemaRow row)
        {
            string mode    = row["PARAMETER_MODE"].ToString();
            int    ordinal = Convert.ToInt32(row["ORDINAL_POSITION"]);

            if (0 == ordinal)
            {
                return(ParameterDirection.ReturnValue);
            }
            else if (mode == "IN")
            {
                return(ParameterDirection.Input);
            }
            else if (mode == "OUT")
            {
                return(ParameterDirection.Output);
            }
            return(ParameterDirection.InputOutput);
        }
Beispiel #4
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(MySqlSchemaCollection fkTable, MySqlSchemaRow tableToParse,
                                           string filterName, bool includeColumns)
        {
            string sqlMode = GetSqlMode();

            if (filterName != null)
            {
                filterName = StringUtility.ToLowerInvariant(filterName);
            }

            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 = StringUtility.ToLowerInvariant(body);
            }

            MySqlTokenizer tokenizer = new MySqlTokenizer(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);
            }
        }
Beispiel #5
0
        private static void ParseDataTypeSize(MySqlSchemaRow row, string size)
        {
            size = size.Trim('(', ')');
            string[] parts = size.Split(',');

            if (!MetaData.IsNumericType(row["DATA_TYPE"].ToString()))
            {
                row["CHARACTER_MAXIMUM_LENGTH"] = Int32.Parse(parts[0]);
                // will set octet length in a minute
            }
            else
            {
                row["NUMERIC_PRECISION"] = Int32.Parse(parts[0]);
                if (parts.Length == 2)
                {
                    row["NUMERIC_SCALE"] = Int32.Parse(parts[1]);
                }
            }
        }
Beispiel #6
0
        private void LoadTableColumns(MySqlSchemaCollection schemaCollection, string schema,
                                      string tableName, string columnRestriction)
        {
            string sql = String.Format("SHOW FULL COLUMNS FROM `{0}`.`{1}`",
                                       schema, tableName);
            MySqlCommand cmd = new MySqlCommand(sql, connection);

            int pos = 1;

            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string colName = reader.GetString(0);
                    if (columnRestriction != null && colName != columnRestriction)
                    {
                        continue;
                    }
                    MySqlSchemaRow row = schemaCollection.AddRow();
                    row["TABLE_CATALOG"]            = DBNull.Value;
                    row["TABLE_SCHEMA"]             = schema;
                    row["TABLE_NAME"]               = tableName;
                    row["COLUMN_NAME"]              = colName;
                    row["ORDINAL_POSITION"]         = pos++;
                    row["COLUMN_DEFAULT"]           = reader.GetValue(5);
                    row["IS_NULLABLE"]              = reader.GetString(3);
                    row["DATA_TYPE"]                = reader.GetString(1);
                    row["CHARACTER_MAXIMUM_LENGTH"] = DBNull.Value;
                    row["CHARACTER_OCTET_LENGTH"]   = DBNull.Value;
                    row["NUMERIC_PRECISION"]        = DBNull.Value;
                    row["NUMERIC_SCALE"]            = DBNull.Value;
                    row["CHARACTER_SET_NAME"]       = reader.GetValue(2);
                    row["COLLATION_NAME"]           = row["CHARACTER_SET_NAME"];
                    row["COLUMN_TYPE"]              = reader.GetString(1);
                    row["COLUMN_KEY"]               = reader.GetString(4);
                    row["EXTRA"]          = reader.GetString(6);
                    row["PRIVILEGES"]     = reader.GetString(7);
                    row["COLUMN_COMMENT"] = reader.GetString(8);
                    ParseColumnRow(row);
                }
            }
        }
Beispiel #7
0
        public virtual MySqlSchemaCollection GetDatabases(string[] restrictions)
        {
            Regex regex       = null;
            int   caseSetting = Int32.Parse(connection.driver.Property("lower_case_table_names"));

            string sql = "SHOW DATABASES";

            // if lower_case_table_names is zero, then case lookup should be sensitive
            // so we can use LIKE to do the matching.
            if (caseSetting == 0)
            {
                if (restrictions != null && restrictions.Length >= 1)
                {
                    sql = sql + " LIKE '" + restrictions[0] + "'";
                }
            }

            MySqlSchemaCollection c = QueryCollection("Databases", sql);

            if (caseSetting != 0 && restrictions != null && restrictions.Length >= 1 && restrictions[0] != null)
            {
                regex = new Regex(restrictions[0], RegexOptions.IgnoreCase);
            }

            MySqlSchemaCollection c2 = new MySqlSchemaCollection("Databases");

            c2.AddColumn("CATALOG_NAME", typeof(string));
            c2.AddColumn("SCHEMA_NAME", typeof(string));

            foreach (MySqlSchemaRow row in c.Rows)
            {
                if (regex != null && !regex.Match(row[0].ToString()).Success)
                {
                    continue;
                }
                MySqlSchemaRow newRow = c2.AddRow();
                newRow[1] = row[0];
            }
            return(c2);
        }
Beispiel #8
0
    public MySqlSchemaCollection(DataTable dt) : this()
    {
      // cache the original datatable to avoid the overhead of creating again whenever possible.
      _table = dt;
      int i = 0;
      foreach (DataColumn dc in dt.Columns)
      {
        columns.Add(new SchemaColumn() { Name = dc.ColumnName, Type = dc.DataType });
        Mapping.Add(dc.ColumnName, i++);
        LogicalMappings[columns.Count - 1] = columns.Count - 1;
      }

      foreach (DataRow dr in dt.Rows)
      {
        MySqlSchemaRow row = new MySqlSchemaRow(this);
        for (i = 0; i < columns.Count; i++)
        {
          row[i] = dr[i];
        }
        rows.Add(row);
      }
    }
Beispiel #9
0
        private static MySqlSchemaCollection GetReservedWords()
        {
            MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");

#if !NETSTANDARD1_3
            dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string));
            Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream(
                "Pomelo.Data.MySql.Properties.ReservedWords.txt");
#else
            dt.AddColumn("ReservedWord", typeof(string));
            Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("Pomelo.Data.MySql.Properties.ReservedWords.txt");
#endif
            StreamReader sr   = new StreamReader(str);
            string       line = sr.ReadLine();
            while (line != null)
            {
                string[] keywords = line.Split(new char[] { ' ' });
                foreach (string s in keywords)
                {
                    if (String.IsNullOrEmpty(s))
                    {
                        continue;
                    }
                    MySqlSchemaRow row = dt.AddRow();
                    row[0] = s;
                }
                line = sr.ReadLine();
            }
#if NETSTANDARD1_3
            sr.Dispose();
            str.Dispose();
#else
            sr.Close();
            str.Close();
#endif


            return(dt);
        }
Beispiel #10
0
        private static void ParseColumnRow(MySqlSchemaRow row)
        {
            // first parse the character set name
            string charset = row["CHARACTER_SET_NAME"].ToString();
            int    index   = charset.IndexOf('_');

            if (index != -1)
            {
                row["CHARACTER_SET_NAME"] = charset.Substring(0, index);
            }

            // now parse the data type
            string dataType = row["DATA_TYPE"].ToString();

            index = dataType.IndexOf('(');
            if (index == -1)
            {
                return;
            }
            row["DATA_TYPE"] = dataType.Substring(0, index);
            int    stop      = dataType.IndexOf(')', index);
            string dataLen   = dataType.Substring(index + 1, stop - (index + 1));
            string lowerType = row["DATA_TYPE"].ToString().ToLower();

            if (lowerType == "char" || lowerType == "varchar")
            {
                row["CHARACTER_MAXIMUM_LENGTH"] = dataLen;
            }
            else if (lowerType == "real" || lowerType == "decimal")
            {
                string[] lenparts = dataLen.Split(new char[] { ',' });
                row["NUMERIC_PRECISION"] = lenparts[0];
                if (lenparts.Length == 2)
                {
                    row["NUMERIC_SCALE"] = lenparts[1];
                }
            }
        }
Beispiel #11
0
        protected MySqlSchemaCollection QueryCollection(string name, string sql)
        {
            MySqlSchemaCollection c      = new MySqlSchemaCollection(name);
            MySqlCommand          cmd    = new MySqlCommand(sql, connection);
            MySqlDataReader       reader = cmd.ExecuteReader();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                c.AddColumn(reader.GetName(i), reader.GetFieldType(i));
            }

            using (reader)
            {
                while (reader.Read())
                {
                    MySqlSchemaRow row = c.AddRow();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[i] = reader.GetValue(i);
                    }
                }
            }
            return(c);
        }
Beispiel #12
0
 internal void CopyRow(MySqlSchemaRow row)
 {
   if (Collection.Columns.Count != row.Collection.Columns.Count)
     throw new InvalidOperationException("column count doesn't match");
   for (int i = 0; i < Collection.Columns.Count; i++)
     row[i] = this[i];
 }
    private string GetProcedureParameterLine(MySqlSchemaRow 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);
        MySqlTokenizer tokenizer = new MySqlTokenizer(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.StartIndex + 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.StartIndex - start);
      }
    }
Beispiel #14
0
 internal MySqlSchemaRow NewRow()
 {
   MySqlSchemaRow r = new MySqlSchemaRow(this);
   return r;
 }
 private static string GetDataTypeDefaults(string type, MySqlSchemaRow row)
 {
   string format = "({0},{1})";
   object precision = row["NUMERIC_PRECISION"];
   if (MetaData.IsNumericType(type) &&
       string.IsNullOrEmpty( ( string )row["NUMERIC_PRECISION"] ) )
   {
     row["NUMERIC_PRECISION"] = 10;
     row["NUMERIC_SCALE"] = 0;
     if (!MetaData.SupportScale(type))
       format = "({0})";
     return String.Format(format, row["NUMERIC_PRECISION"],
         row["NUMERIC_SCALE"]);
   }
   return String.Empty;
 }
Beispiel #16
0
        private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body,
                                        MySqlSchemaRow row, string nameToRestrict)
        {
            List <string> modes = new List <string>(new string[3] {
                "IN", "OUT", "INOUT"
            });

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

            int            pos       = 1;
            MySqlTokenizer tokenizer = new MySqlTokenizer(body);

            tokenizer.AnsiQuotes       = sqlMode.IndexOf("ANSI_QUOTES") != -1;
            tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
            tokenizer.ReturnComments   = false;
            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", StringComparison.OrdinalIgnoreCase) == 0 &&
                    nameToRestrict == null)
                {
                    parametersTable.AddRow();
                    InitParameterRow(row, parametersTable.Rows[0]);
                }
                token = tokenizer.NextToken();
            }
            token = tokenizer.NextToken(); // now move to the next token past the (

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

                // handle mode and name for the parameter
                string mode = StringUtility.ToUpperInvariant(token);
                if (!tokenizer.Quoted && modes.Contains(mode))
                {
                    parmRow["PARAMETER_MODE"] = mode;
                    token = tokenizer.NextToken();
                }
                if (tokenizer.Quoted)
                {
                    token = token.Substring(1, token.Length - 2);
                }
                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, StringComparison.OrdinalIgnoreCase) == 0)
                {
                    parametersTable.Rows.Add(parmRow);
                }
            }

            // now parse out the return parameter if there is one.
            token = StringUtility.ToUpperInvariant(tokenizer.NextToken());
            if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
            {
                MySqlSchemaRow parameterRow = parametersTable.Rows[0];
                parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
                ParseDataType(parameterRow, tokenizer);
            }
        }
 /// <summary>
 /// Initializes a new row for the procedure parameters table.
 /// </summary>
 private static void InitParameterRow(MySqlSchemaRow procedure, MySqlSchemaRow parameter)
 {
   parameter["SPECIFIC_CATALOG"] = null;
   parameter["SPECIFIC_SCHEMA"] = procedure["ROUTINE_SCHEMA"];
   parameter["SPECIFIC_NAME"] = procedure["ROUTINE_NAME"];
   parameter["PARAMETER_MODE"] = "IN";
   parameter["ORDINAL_POSITION"] = 0;
   parameter["ROUTINE_TYPE"] = procedure["ROUTINE_TYPE"];
 }
        private static ParameterDirection GetDirection(MySqlSchemaRow row)
        {
            string mode = row["PARAMETER_MODE"].ToString();
            int ordinal = Convert.ToInt32(row["ORDINAL_POSITION"]);

            if (0 == ordinal)
                return ParameterDirection.ReturnValue;
            else if (mode == "IN")
                return ParameterDirection.Input;
            else if (mode == "OUT")
                return ParameterDirection.Output;
            return ParameterDirection.InputOutput;
        }
        private static List<string> GetPossibleValues(MySqlSchemaRow row)
        {
            string[] types = new string[] { "ENUM", "SET" };
            string dtdIdentifier = row["DTD_IDENTIFIER"].ToString().Trim();

            int index = 0;
            for (; index < 2; index++)
                if (dtdIdentifier.StartsWith(types[index], StringComparison.OrdinalIgnoreCase))
                    break;
            if (index == 2) return null;
            dtdIdentifier = dtdIdentifier.Substring(types[index].Length).Trim();
            dtdIdentifier = dtdIdentifier.Trim('(', ')').Trim();

            List<string> values = new List<string>();
            MySqlTokenizer tokenzier = new MySqlTokenizer(dtdIdentifier);
            string token = tokenzier.NextToken();
            int start = tokenzier.StartIndex;
            while (true)
            {
                if (token == null || token == ",")
                {
                    int end = dtdIdentifier.Length - 1;
                    if (token == ",")
                        end = tokenzier.StartIndex;

                    string value = dtdIdentifier.Substring(start, end - start).Trim('\'', '\"').Trim();
                    values.Add(value);
                    start = tokenzier.StopIndex;
                }
                if (token == null) break;
                token = tokenzier.NextToken();
            }
            return values;
        }
    private static void ParseDataTypeSize(MySqlSchemaRow row, string size)
    {
      size = size.Trim('(', ')');
      string[] parts = size.Split(',');

      if (!MetaData.IsNumericType(row["DATA_TYPE"].ToString()))
      {
        row["CHARACTER_MAXIMUM_LENGTH"] = Int32.Parse(parts[0]);
        // will set octet length in a minute
      }
      else
      {
        row["NUMERIC_PRECISION"] = Int32.Parse(parts[0]);
        if (parts.Length == 2)
          row["NUMERIC_SCALE"] = Int32.Parse(parts[1]);
      }
    }
Beispiel #21
0
        public virtual MySqlSchemaCollection GetIndexColumns(string[] restrictions)
        {
            MySqlSchemaCollection dt = new MySqlSchemaCollection("IndexColumns");

            dt.AddColumn("INDEX_CATALOG", typeof(string));
            dt.AddColumn("INDEX_SCHEMA", typeof(string));
            dt.AddColumn("INDEX_NAME", typeof(string));
            dt.AddColumn("TABLE_NAME", typeof(string));
            dt.AddColumn("COLUMN_NAME", typeof(string));
            dt.AddColumn("ORDINAL_POSITION", typeof(int));
            dt.AddColumn("SORT_ORDER", typeof(string));

            int max = restrictions == null ? 4 : restrictions.Length;

            string[] tableRestrictions = new string[Math.Max(max, 4)];
            if (restrictions != null)
            {
                restrictions.CopyTo(tableRestrictions, 0);
            }
            tableRestrictions[3] = "BASE TABLE";
            MySqlSchemaCollection tables = GetTables(tableRestrictions);

            foreach (MySqlSchemaRow table in tables.Rows)
            {
                string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
                                           table["TABLE_SCHEMA"], table["TABLE_NAME"]);
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME"));
                        string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME"));

                        if (restrictions != null)
                        {
                            if (restrictions.Length >= 4 && restrictions[3] != null &&
                                key_name != restrictions[3])
                            {
                                continue;
                            }
                            if (restrictions.Length >= 5 && restrictions[4] != null &&
                                col_name != restrictions[4])
                            {
                                continue;
                            }
                        }
                        MySqlSchemaRow row = dt.AddRow();
                        row["INDEX_CATALOG"]    = null;
                        row["INDEX_SCHEMA"]     = table["TABLE_SCHEMA"];
                        row["INDEX_NAME"]       = key_name;
                        row["TABLE_NAME"]       = GetString(reader, reader.GetOrdinal("TABLE"));
                        row["COLUMN_NAME"]      = col_name;
                        row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX"));
                        row["SORT_ORDER"]       = reader.GetString("COLLATION");
                    }
                }
            }

            return(dt);
        }
    private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body,
        MySqlSchemaRow row, string nameToRestrict)
    {
      List<string> modes = new List<string>(new string[3] { "IN", "OUT", "INOUT" });

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

      int pos = 1;
      MySqlTokenizer tokenizer = new MySqlTokenizer(body);
      tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
      tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
      tokenizer.ReturnComments = false;
      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", StringComparison.OrdinalIgnoreCase) == 0 &&
            nameToRestrict == null)
        {
          parametersTable.AddRow();
          InitParameterRow(row, parametersTable.Rows[0]);
        }
        token = tokenizer.NextToken();
      }
      token = tokenizer.NextToken();  // now move to the next token past the (

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

        // handle mode and name for the parameter
        string mode = StringUtility.ToUpperInvariant(token);
        if (!tokenizer.Quoted && modes.Contains(mode))
        {
          parmRow["PARAMETER_MODE"] = mode;
          token = tokenizer.NextToken();
        }
        if (tokenizer.Quoted)
          token = token.Substring(1, token.Length - 2);
        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, StringComparison.OrdinalIgnoreCase) == 0)
          parametersTable.Rows.Add(parmRow);
      }

      // now parse out the return parameter if there is one.
      token = StringUtility.ToUpperInvariant(tokenizer.NextToken());
      if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
      {
        MySqlSchemaRow parameterRow = parametersTable.Rows[0];
        parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
        ParseDataType(parameterRow, tokenizer);
      }
    }
    private MySqlParameter GetAndFixParameter(string spName, MySqlSchemaRow param, bool realAsFloat, MySqlParameter returnParameter)
    {
      string mode = (string)param["PARAMETER_MODE"];
      string pName = (string)param["PARAMETER_NAME"];

      if (param["ORDINAL_POSITION"].Equals(0))
      {
        if (returnParameter == null)
          throw new InvalidOperationException(
              String.Format(Resources.RoutineRequiresReturnParameter, spName));
        pName = returnParameter.ParameterName;
      }

      // make sure the parameters given to us have an appropriate type set if it's not already
      MySqlParameter p = command.Parameters.GetParameterFlexible(pName, true);
      if (!p.TypeHasBeenSet)
      {
        string datatype = (string)param["DATA_TYPE"];
        bool unsigned = GetFlags(param["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1;
        p.MySqlDbType = MetaData.NameToType(datatype, unsigned, realAsFloat, Connection);
      }
      return p;
    }
    /// <summary>
    ///  Parses out the elements of a procedure parameter data type.
    /// </summary>
    private string ParseDataType(MySqlSchemaRow row, MySqlTokenizer tokenizer)
    {
      StringBuilder dtd = new StringBuilder(
          StringUtility.ToUpperInvariant(tokenizer.NextToken()));
      row["DATA_TYPE"] = dtd.ToString();
      string type = row["DATA_TYPE"].ToString();

      string token = tokenizer.NextToken();
      if (token == "(")
      {
        token = tokenizer.ReadParenthesis();
        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", StringComparison.OrdinalIgnoreCase) != 0 &&
             String.Compare(token, "return", StringComparison.OrdinalIgnoreCase) != 0)
      {
        if (String.Compare(token, "CHARACTER", StringComparison.OrdinalIgnoreCase) == 0 ||
            String.Compare(token, "BINARY", StringComparison.OrdinalIgnoreCase) == 0)
        { }  // we don't need to do anything with this
        else if (String.Compare(token, "SET", StringComparison.OrdinalIgnoreCase) == 0 ||
                 String.Compare(token, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0)
          row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
        else if (String.Compare(token, "ASCII", StringComparison.OrdinalIgnoreCase) == 0)
          row["CHARACTER_SET_NAME"] = "latin1";
        else if (String.Compare(token, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0)
          row["CHARACTER_SET_NAME"] = "ucs2";
        else if (String.Compare(token, "COLLATE", StringComparison.OrdinalIgnoreCase) == 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 ( string.IsNullOrEmpty( ( string )row["COLLATION_NAME"] ) &&
          !string.IsNullOrEmpty( ( string )row["CHARACTER_SET_NAME"] ))
        row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(
            row["CHARACTER_SET_NAME"].ToString(), connection);

      // now set the octet length
      if (row["CHARACTER_MAXIMUM_LENGTH"] != null)
      {
        if (row["CHARACTER_SET_NAME"] == null)
          row["CHARACTER_SET_NAME"] = "";
        row["CHARACTER_OCTET_LENGTH"] =
            CharSetMap.GetMaxLength(( string )row["CHARACTER_SET_NAME"], connection) *
            (int)row["CHARACTER_MAXIMUM_LENGTH"];
      }

      return token;
    }
Beispiel #25
0
        public virtual MySqlSchemaCollection GetProcedures(string[] restrictions)
        {
            MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedures");

            dt.AddColumn("SPECIFIC_NAME", typeof(string));
            dt.AddColumn("ROUTINE_CATALOG", typeof(string));
            dt.AddColumn("ROUTINE_SCHEMA", typeof(string));
            dt.AddColumn("ROUTINE_NAME", typeof(string));
            dt.AddColumn("ROUTINE_TYPE", typeof(string));
            dt.AddColumn("DTD_IDENTIFIER", typeof(string));
            dt.AddColumn("ROUTINE_BODY", typeof(string));
            dt.AddColumn("ROUTINE_DEFINITION", typeof(string));
            dt.AddColumn("EXTERNAL_NAME", typeof(string));
            dt.AddColumn("EXTERNAL_LANGUAGE", typeof(string));
            dt.AddColumn("PARAMETER_STYLE", typeof(string));
            dt.AddColumn("IS_DETERMINISTIC", typeof(string));
            dt.AddColumn("SQL_DATA_ACCESS", typeof(string));
            dt.AddColumn("SQL_PATH", typeof(string));
            dt.AddColumn("SECURITY_TYPE", typeof(string));
            dt.AddColumn("CREATED", typeof(DateTime));
            dt.AddColumn("LAST_ALTERED", typeof(DateTime));
            dt.AddColumn("SQL_MODE", typeof(string));
            dt.AddColumn("ROUTINE_COMMENT", typeof(string));
            dt.AddColumn("DEFINER", typeof(string));

            StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1");

            if (restrictions != null)
            {
                if (restrictions.Length >= 2 && restrictions[1] != null)
                {
                    sql.AppendFormat(CultureInfo.InvariantCulture,
                                     " AND db LIKE '{0}'", restrictions[1]);
                }
                if (restrictions.Length >= 3 && restrictions[2] != null)
                {
                    sql.AppendFormat(CultureInfo.InvariantCulture,
                                     " AND name LIKE '{0}'", restrictions[2]);
                }
                if (restrictions.Length >= 4 && restrictions[3] != null)
                {
                    sql.AppendFormat(CultureInfo.InvariantCulture,
                                     " AND type LIKE '{0}'", restrictions[3]);
                }
            }

            MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);

            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    MySqlSchemaRow row = dt.AddRow();
                    row["SPECIFIC_NAME"]   = reader.GetString("specific_name");
                    row["ROUTINE_CATALOG"] = DBNull.Value;
                    row["ROUTINE_SCHEMA"]  = reader.GetString("db");
                    row["ROUTINE_NAME"]    = reader.GetString("name");
                    string routineType = reader.GetString("type");
                    row["ROUTINE_TYPE"]   = routineType;
                    row["DTD_IDENTIFIER"] = StringUtility.ToLowerInvariant(routineType) == "function" ?
                                            (object)reader.GetString("returns") : DBNull.Value;
                    row["ROUTINE_BODY"]       = "SQL";
                    row["ROUTINE_DEFINITION"] = reader.GetString("body");
                    row["EXTERNAL_NAME"]      = DBNull.Value;
                    row["EXTERNAL_LANGUAGE"]  = DBNull.Value;
                    row["PARAMETER_STYLE"]    = "SQL";
                    row["IS_DETERMINISTIC"]   = reader.GetString("is_deterministic");
                    row["SQL_DATA_ACCESS"]    = reader.GetString("sql_data_access");
                    row["SQL_PATH"]           = DBNull.Value;
                    row["SECURITY_TYPE"]      = reader.GetString("security_type");
                    row["CREATED"]            = reader.GetDateTime("created");
                    row["LAST_ALTERED"]       = reader.GetDateTime("modified");
                    row["SQL_MODE"]           = reader.GetString("sql_mode");
                    row["ROUTINE_COMMENT"]    = reader.GetString("comment");
                    row["DEFINER"]            = reader.GetString("definer");
                }
            }

            return(dt);
        }
        private static void ParseColumnRow(MySqlSchemaRow row)
        {
            // first parse the character set name
            string charset = row["CHARACTER_SET_NAME"].ToString();
            int index = charset.IndexOf('_');
            if (index != -1)
                row["CHARACTER_SET_NAME"] = charset.Substring(0, index);

            // now parse the data type
            string dataType = row["DATA_TYPE"].ToString();
            index = dataType.IndexOf('(');
            if (index == -1)
                return;
            row["DATA_TYPE"] = dataType.Substring(0, index);
            int stop = dataType.IndexOf(')', index);
            string dataLen = dataType.Substring(index + 1, stop - (index + 1));
            string lowerType = row["DATA_TYPE"].ToString().ToLower();
            if (lowerType == "char" || lowerType == "varchar")
                row["CHARACTER_MAXIMUM_LENGTH"] = dataLen;
            else if (lowerType == "real" || lowerType == "decimal")
            {
                string[] lenparts = dataLen.Split(new char[] { ',' });
                row["NUMERIC_PRECISION"] = lenparts[0];
                if (lenparts.Length == 2)
                    row["NUMERIC_SCALE"] = lenparts[1];
            }
        }
Beispiel #27
0
        internal MySqlSchemaRow NewRow()
        {
            MySqlSchemaRow r = new MySqlSchemaRow(this);

            return(r);
        }
        /// <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(MySqlSchemaCollection fkTable, MySqlSchemaRow tableToParse,
                           string filterName, bool includeColumns)
        {
            string sqlMode = GetSqlMode();

            if (filterName != null)
                filterName = StringUtility.ToLowerInvariant(filterName);

            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 = StringUtility.ToLowerInvariant(body);
            }

            MySqlTokenizer tokenizer = new MySqlTokenizer(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);
            }
        }
Beispiel #29
0
        /// <summary>
        ///  Parses out the elements of a procedure parameter data type.
        /// </summary>
        private string ParseDataType(MySqlSchemaRow row, MySqlTokenizer tokenizer)
        {
            StringBuilder dtd = new StringBuilder(
                StringUtility.ToUpperInvariant(tokenizer.NextToken()));

            row["DATA_TYPE"] = dtd.ToString();
            string type = row["DATA_TYPE"].ToString();

            string token = tokenizer.NextToken();

            if (token == "(")
            {
                token = tokenizer.ReadParenthesis();
                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", StringComparison.OrdinalIgnoreCase) != 0 &&
                   String.Compare(token, "return", StringComparison.OrdinalIgnoreCase) != 0)
            {
                if (String.Compare(token, "CHARACTER", StringComparison.OrdinalIgnoreCase) == 0 ||
                    String.Compare(token, "BINARY", StringComparison.OrdinalIgnoreCase) == 0)
                {
                } // we don't need to do anything with this
                else if (String.Compare(token, "SET", StringComparison.OrdinalIgnoreCase) == 0 ||
                         String.Compare(token, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
                }
                else if (String.Compare(token, "ASCII", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    row["CHARACTER_SET_NAME"] = "latin1";
                }
                else if (String.Compare(token, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    row["CHARACTER_SET_NAME"] = "ucs2";
                }
                else if (String.Compare(token, "COLLATE", StringComparison.OrdinalIgnoreCase) == 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 (string.IsNullOrEmpty(( string )row["COLLATION_NAME"]) &&
                !string.IsNullOrEmpty(( string )row["CHARACTER_SET_NAME"]))
            {
                row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(
                    row["CHARACTER_SET_NAME"].ToString(), connection);
            }

            // now set the octet length
            if (row["CHARACTER_MAXIMUM_LENGTH"] != null)
            {
                if (row["CHARACTER_SET_NAME"] == null)
                {
                    row["CHARACTER_SET_NAME"] = "";
                }
                row["CHARACTER_OCTET_LENGTH"] =
                    CharSetMap.GetMaxLength(( string )row["CHARACTER_SET_NAME"], connection) *
                    (int)row["CHARACTER_MAXIMUM_LENGTH"];
            }

            return(token);
        }
        private static void ParseConstraint(MySqlSchemaCollection fkTable, MySqlSchemaRow table,
          MySqlTokenizer tokenizer, bool includeColumns)
        {
            string name = tokenizer.NextToken();
            MySqlSchemaRow row = fkTable.AddRow();

            // 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.Trim(new char[] { '\'', '`' });

            List<string> 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(".", StringComparison.Ordinal))
            {
                row["REFERENCED_TABLE_SCHEMA"] = target1;
                row["REFERENCED_TABLE_NAME"] = target2.Substring(1).Trim(new char[] { '\'', '`' });
                tokenizer.NextToken();  // read off the '('
            }
            else
            {
                row["REFERENCED_TABLE_SCHEMA"] = table["TABLE_SCHEMA"];
                row["REFERENCED_TABLE_NAME"] = target1.Substring(1).Trim(new char[] { '\'', '`' }); ;
            }

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

            if (includeColumns)
                ProcessColumns(fkTable, row, srcColumns, targetColumns);
            else
                fkTable.Rows.Add(row);
        }
 private static void ProcessColumns(MySqlSchemaCollection fkTable, MySqlSchemaRow row, List<string> srcColumns, List<string> targetColumns)
 {
     for (int i = 0; i < srcColumns.Count; i++)
     {
         MySqlSchemaRow newRow = fkTable.AddRow();
         row.CopyRow(newRow);
         newRow["COLUMN_NAME"] = srcColumns[i];
         newRow["ORDINAL_POSITION"] = i;
         newRow["REFERENCED_COLUMN_NAME"] = targetColumns[i];
         fkTable.Rows.Add(newRow);
     }
 }
Beispiel #32
0
 internal MySqlSchemaRow AddRow()
 {
   MySqlSchemaRow r = new MySqlSchemaRow(this);
   rows.Add(r);
   return r;
 }