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);
}
public virtual MySqlSchemaCollection GetIndexColumns(string[] restrictions)
{
MySqlSchemaCollection schemas = new MySqlSchemaCollection("IndexColumns");
schemas.AddColumn("INDEX_CATALOG", typeof(string));
schemas.AddColumn("INDEX_SCHEMA", typeof(string));
schemas.AddColumn("INDEX_NAME", typeof(string));
schemas.AddColumn("TABLE_NAME", typeof(string));
schemas.AddColumn("COLUMN_NAME", typeof(string));
schemas.AddColumn("ORDINAL_POSITION", typeof(int));
schemas.AddColumn("SORT_ORDER", typeof(string));
int num = (restrictions == null) ? 4 : restrictions.Length;
string[] array = new string[Math.Max(num, 4)];
if (restrictions != null)
{
restrictions.CopyTo(array, 0);
}
array[3] = "BASE TABLE";
foreach (MySqlSchemaRow row in this.GetTables(array).Rows)
{
MySqlCommand command = new MySqlCommand(string.Format("SHOW INDEX FROM `{0}`.`{1}`", row["TABLE_SCHEMA"], row["TABLE_NAME"]), this.connection);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string str2 = GetString(reader, reader.GetOrdinal("KEY_NAME"));
string str3 = GetString(reader, reader.GetOrdinal("COLUMN_NAME"));
if ((restrictions == null) || ((((restrictions.Length < 4) || (restrictions[3] == null)) || (str2 == restrictions[3])) && (((restrictions.Length < 5) || (restrictions[4] == null)) || (str3 == restrictions[4]))))
{
MySqlSchemaRow row2 = schemas.AddRow();
row2["INDEX_CATALOG"] = null;
row2["INDEX_SCHEMA"] = row["TABLE_SCHEMA"];
row2["INDEX_NAME"] = str2;
row2["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE"));
row2["COLUMN_NAME"] = str3;
row2["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX"));
row2["SORT_ORDER"] = reader.GetString("COLLATION");
}
}
}
}
return(schemas);
}
private void FindTables(MySqlSchemaCollection schema, string[] restrictions)
{
StringBuilder builder = new StringBuilder();
StringBuilder builder2 = new StringBuilder();
builder.AppendFormat(CultureInfo.InvariantCulture, "SHOW TABLE STATUS FROM `{0}`", new object[] { restrictions[1] });
if (((restrictions != null) && (restrictions.Length >= 3)) && (restrictions[2] != null))
{
builder2.AppendFormat(CultureInfo.InvariantCulture, " LIKE '{0}'", new object[] { restrictions[2] });
}
builder.Append(builder2.ToString());
string str = (restrictions[1].ToLower() == "information_schema") ? "SYSTEM VIEW" : "BASE TABLE";
MySqlCommand command = new MySqlCommand(builder.ToString(), this.connection);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MySqlSchemaRow row = schema.AddRow();
row["TABLE_CATALOG"] = null;
row["TABLE_SCHEMA"] = restrictions[1];
row["TABLE_NAME"] = reader.GetString(0);
row["TABLE_TYPE"] = str;
row["ENGINE"] = GetString(reader, 1);
row["VERSION"] = reader.GetValue(2);
row["ROW_FORMAT"] = GetString(reader, 3);
row["TABLE_ROWS"] = reader.GetValue(4);
row["AVG_ROW_LENGTH"] = reader.GetValue(5);
row["DATA_LENGTH"] = reader.GetValue(6);
row["MAX_DATA_LENGTH"] = reader.GetValue(7);
row["INDEX_LENGTH"] = reader.GetValue(8);
row["DATA_FREE"] = reader.GetValue(9);
row["AUTO_INCREMENT"] = reader.GetValue(10);
row["CREATE_TIME"] = reader.GetValue(11);
row["UPDATE_TIME"] = reader.GetValue(12);
row["CHECK_TIME"] = reader.GetValue(13);
row["TABLE_COLLATION"] = GetString(reader, 14);
row["CHECKSUM"] = reader.GetValue(15);
row["CREATE_OPTIONS"] = GetString(reader, 0x10);
row["TABLE_COMMENT"] = GetString(reader, 0x11);
}
}
}
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);
row["GENERATION_EXPRESION"] = reader.GetString(6).Contains("VIRTUAL") ? reader.GetString(9) : string.Empty;
ParseColumnRow(row);
}
}
}
public virtual MySqlSchemaCollection GetIndexes(string[] restrictions)
{
MySqlSchemaCollection schemas = new MySqlSchemaCollection("Indexes");
schemas.AddColumn("INDEX_CATALOG", typeof(string));
schemas.AddColumn("INDEX_SCHEMA", typeof(string));
schemas.AddColumn("INDEX_NAME", typeof(string));
schemas.AddColumn("TABLE_NAME", typeof(string));
schemas.AddColumn("UNIQUE", typeof(bool));
schemas.AddColumn("PRIMARY", typeof(bool));
schemas.AddColumn("TYPE", typeof(string));
schemas.AddColumn("COMMENT", typeof(string));
int num = (restrictions == null) ? 4 : restrictions.Length;
string[] array = new string[Math.Max(num, 4)];
if (restrictions != null)
{
restrictions.CopyTo(array, 0);
}
array[3] = "BASE TABLE";
foreach (MySqlSchemaRow row in this.GetTables(array).Rows)
{
string sql = string.Format("SHOW INDEX FROM `{0}`.`{1}`", MySqlHelper.DoubleQuoteString((string)row["TABLE_SCHEMA"]), MySqlHelper.DoubleQuoteString((string)row["TABLE_NAME"]));
foreach (MySqlSchemaRow row2 in this.QueryCollection("indexes", sql).Rows)
{
long num2 = (long)row2["SEQ_IN_INDEX"];
if ((num2 == 1L) && (((restrictions == null) || (restrictions.Length != 4)) || ((restrictions[3] == null) || row2["KEY_NAME"].Equals(restrictions[3]))))
{
MySqlSchemaRow row3 = schemas.AddRow();
row3["INDEX_CATALOG"] = null;
row3["INDEX_SCHEMA"] = row["TABLE_SCHEMA"];
row3["INDEX_NAME"] = row2["KEY_NAME"];
row3["TABLE_NAME"] = row2["TABLE"];
row3["UNIQUE"] = ((long)row2["NON_UNIQUE"]) == 0L;
row3["PRIMARY"] = row2["KEY_NAME"].Equals("PRIMARY");
row3["TYPE"] = row2["INDEX_TYPE"];
row3["COMMENT"] = row2["COMMENT"];
}
}
}
return(schemas);
}
private static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");
dt.AddColumn("ReservedWord", typeof(string));
Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("MySql.Data.Properties.ReservedWords.txt");
StreamReader sr = new StreamReader(str);
string line = sr.ReadLine();
while (line != null)
{
MySqlSchemaRow row = dt.AddRow();
row[0] = line;
line = sr.ReadLine();
}
sr.Dispose();
return(dt);
}
private static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");
#if !RT && !DNXCORE50
dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string));
Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream(
"MySql.Data.MySqlClient.Properties.ReservedWords.txt");
#else
dt.AddColumn("ReservedWord", typeof(string));
Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("MySql.Data.MySqlClient.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 !CF
sr.Dispose();
#else
sr.Close();
#endif
#if !DNXCORE50
str.Close();
#else
str.Dispose();
#endif
return(dt);
}
private MySqlSchemaCollection GetTable(string sql)
{
MySqlSchemaCollection c = new MySqlSchemaCollection();
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
// add columns
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;
}
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);
}
protected MySqlSchemaCollection QueryCollection(string name, string sql)
{
MySqlSchemaCollection schemas = new MySqlSchemaCollection(name);
MySqlDataReader reader = new MySqlCommand(sql, this.connection).ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
schemas.AddColumn(reader.GetName(i), reader.GetFieldType(i));
}
using (reader)
{
while (reader.Read())
{
MySqlSchemaRow row = schemas.AddRow();
for (int j = 0; j < reader.FieldCount; j++)
{
row[j] = reader.GetValue(j);
}
}
}
return(schemas);
}
private MySqlSchemaCollection GetTable(string sql)
{
MySqlSchemaCollection mySqlSchemaCollection = new MySqlSchemaCollection();
MySqlDataReader mySqlDataReader = new MySqlCommand(sql, this.connection).ExecuteReader();
for (int i = 0; i < mySqlDataReader.FieldCount; i++)
{
mySqlSchemaCollection.AddColumn(mySqlDataReader.GetName(i), mySqlDataReader.GetFieldType(i));
}
using (mySqlDataReader)
{
while (mySqlDataReader.Read())
{
MySqlSchemaRow mySqlSchemaRow = mySqlSchemaCollection.AddRow();
for (int j = 0; j < mySqlDataReader.FieldCount; j++)
{
mySqlSchemaRow[j] = mySqlDataReader.GetValue(j);
}
}
}
return(mySqlSchemaCollection);
}
private static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection schemas = new MySqlSchemaCollection("ReservedWords");
schemas.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string));
Stream manifestResourceStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MySql.Data.MySqlClient.Properties.ReservedWords.txt");
StreamReader reader = new StreamReader(manifestResourceStream);
for (string str = reader.ReadLine(); str != null; str = reader.ReadLine())
{
foreach (string str2 in str.Split(new char[] { ' ' }))
{
if (!string.IsNullOrEmpty(str2))
{
schemas.AddRow()[0] = str2;
}
}
}
reader.Dispose();
manifestResourceStream.Close();
return(schemas);
}
public virtual MySqlSchemaCollection GetUDF(string[] restrictions)
{
string cmdText = "SELECT name,ret,dl FROM mysql.func";
if (((restrictions != null) && (restrictions.Length >= 1)) && !string.IsNullOrEmpty(restrictions[0]))
{
cmdText = cmdText + string.Format(" WHERE name LIKE '{0}'", restrictions[0]);
}
MySqlSchemaCollection schemas = new MySqlSchemaCollection("User-defined Functions");
schemas.AddColumn("NAME", typeof(string));
schemas.AddColumn("RETURN_TYPE", typeof(int));
schemas.AddColumn("LIBRARY_NAME", typeof(string));
MySqlCommand command = new MySqlCommand(cmdText, this.connection);
try
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MySqlSchemaRow row = schemas.AddRow();
row[0] = reader.GetString(0);
row[1] = reader.GetInt32(1);
row[2] = reader.GetString(2);
}
}
}
catch (MySqlException exception)
{
if (exception.Number != 0x476)
{
throw;
}
throw new MySqlException(Resources.UnableToEnumerateUDF, exception);
}
return(schemas);
}
private void LoadTableColumns(MySqlSchemaCollection schemaCollection, string schema, string tableName, string columnRestriction)
{
MySqlCommand command = new MySqlCommand(string.Format("SHOW FULL COLUMNS FROM `{0}`.`{1}`", schema, tableName), this.connection);
int num = 1;
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string str2 = reader.GetString(0);
if ((columnRestriction == null) || (str2 == columnRestriction))
{
MySqlSchemaRow row = schemaCollection.AddRow();
row["TABLE_CATALOG"] = DBNull.Value;
row["TABLE_SCHEMA"] = schema;
row["TABLE_NAME"] = tableName;
row["COLUMN_NAME"] = str2;
row["ORDINAL_POSITION"] = num++;
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);
}
}
}
}
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);
}
private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body, MySqlSchemaRow row, string nameToRestrict)
{
List <string> list = new List <string>(new string[]
{
"IN",
"OUT",
"INOUT"
});
string text = row["SQL_MODE"].ToString();
int num = 1;
MySqlTokenizer mySqlTokenizer = new MySqlTokenizer(body);
mySqlTokenizer.AnsiQuotes = (text.IndexOf("ANSI_QUOTES") != -1);
mySqlTokenizer.BackslashEscapes = (text.IndexOf("NO_BACKSLASH_ESCAPES") == -1);
mySqlTokenizer.ReturnComments = false;
string text2 = mySqlTokenizer.NextToken();
while (text2 != "(")
{
if (string.Compare(text2, "FUNCTION", StringComparison.OrdinalIgnoreCase) == 0 && nameToRestrict == null)
{
parametersTable.AddRow();
ISSchemaProvider.InitParameterRow(row, parametersTable.Rows[0]);
}
text2 = mySqlTokenizer.NextToken();
}
text2 = mySqlTokenizer.NextToken();
while (text2 != ")")
{
MySqlSchemaRow mySqlSchemaRow = parametersTable.NewRow();
ISSchemaProvider.InitParameterRow(row, mySqlSchemaRow);
mySqlSchemaRow["ORDINAL_POSITION"] = num++;
string text3 = StringUtility.ToUpperInvariant(text2);
if (!mySqlTokenizer.Quoted && list.Contains(text3))
{
mySqlSchemaRow["PARAMETER_MODE"] = text3;
text2 = mySqlTokenizer.NextToken();
}
if (mySqlTokenizer.Quoted)
{
text2 = text2.Substring(1, text2.Length - 2);
}
mySqlSchemaRow["PARAMETER_NAME"] = text2;
text2 = this.ParseDataType(mySqlSchemaRow, mySqlTokenizer);
if (text2 == ",")
{
text2 = mySqlTokenizer.NextToken();
}
if (nameToRestrict == null || string.Compare(mySqlSchemaRow["PARAMETER_NAME"].ToString(), nameToRestrict, StringComparison.OrdinalIgnoreCase) == 0)
{
parametersTable.Rows.Add(mySqlSchemaRow);
}
}
text2 = StringUtility.ToUpperInvariant(mySqlTokenizer.NextToken());
if (string.Compare(text2, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
{
MySqlSchemaRow mySqlSchemaRow2 = parametersTable.Rows[0];
mySqlSchemaRow2["PARAMETER_NAME"] = "RETURN_VALUE";
this.ParseDataType(mySqlSchemaRow2, mySqlTokenizer);
}
}
public static void SetDSInfo(MySqlSchemaCollection sc)
{
string[] types = new string[] { "BLOB", "TINYBLOB", "MEDIUMBLOB", "LONGBLOB", "BINARY", "VARBINARY" };
MySqlDbType[] dbtype = new MySqlDbType[] { MySqlDbType.Blob,
MySqlDbType.TinyBlob, MySqlDbType.MediumBlob, MySqlDbType.LongBlob, MySqlDbType.Binary, MySqlDbType.VarBinary };
long[] sizes = new long[] { 65535L, 255L, 16777215L, 4294967295L, 255L, 65535L };
string[] format = new string[] { null, null, null, null, "binary({0})", "varbinary({0})" };
string[] parms = new string[] { null, null, null, null, "length", "length" };
// we use name indexing because this method will only be called
// when GetSchema is called for the DataSourceInformation
// collection and then it wil be cached.
for (int x = 0; x < types.Length; x++)
{
MySqlSchemaRow row = sc.AddRow();
row["TypeName"] = types[x];
row["ProviderDbType"] = dbtype[x];
row["ColumnSize"] = sizes[x];
row["CreateFormat"] = format[x];
row["CreateParameters"] = parms[x];
row["DataType"] = "System.Byte[]";
row["IsAutoincrementable"] = false;
row["IsBestMatch"] = true;
row["IsCaseSensitive"] = false;
row["IsFixedLength"] = x < 4 ? false : true;
row["IsFixedPrecisionScale"] = false;
row["IsLong"] = sizes[x] > 255;
row["IsNullable"] = true;
row["IsSearchable"] = false;
row["IsSearchableWithLike"] = false;
row["IsUnsigned"] = DBNull.Value;
row["MaximumScale"] = DBNull.Value;
row["MinimumScale"] = DBNull.Value;
row["IsConcurrencyType"] = DBNull.Value;
row["IsLiteralSupported"] = false;
row["LiteralPrefix"] = "0x";
row["LiteralSuffix"] = DBNull.Value;
row["NativeDataType"] = DBNull.Value;
}
}
private static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");
string[] lines = @"ACCESSIBLE ADD ALL
ALTER ANALYZE AND
AS ASC ASENSITIVE
BEFORE BETWEEN BIGINT
BINARY BLOB BOTH
BY CALL CASCADE
CASE CHANGE CHAR
CHARACTER CHECK COLLATE
COLUMN CONDITION CONNECTION
CONSTRAINT CONTINUE CONTRIBUTORS
CONVERT CREATE CROSS
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP
CURRENT_USER CURSOR DATABASE
DATABASES DAY_HOUR DAY_MICROSECOND
DAY_MINUTE DAY_SECOND DEC
DECIMAL DECLARE DEFAULT
DELAYED DELETE DESC
DESCRIBE DETERMINISTIC DISTINCT
DISTINCTROW DIV DOUBLE
DROP DUAL EACH
ELSE ELSEIF ENCLOSED
ESCAPED EXISTS EXIT
EXPLAIN FALSE FETCH
FLOAT FLOAT4 FLOAT8
FOR FORCE FOREIGN
FROM FULLTEXT GRANT
GROUP HAVING HIGH_PRIORITY
HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND
IF IGNORE IN
INDEX INFILE INNER
INOUT INSENSITIVE INSERT
INT INT1 INT2
INT3 INT4 INT8
INTEGER INTERVAL INTO
IS ITERATE JOIN
KEY KEYS KILL
LEADING LEAVE LEFT
LIKE LIMIT LINEAR
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOOP
LOW_PRIORITY MATCH MEDIUMBLOB
MEDIUMINT MEDIUMTEXT MIDDLEINT
MINUTE_MICROSECOND MINUTE_SECOND MOD
MODIFIES NATURAL NOT
NO_WRITE_TO_BINLOG NULL NUMERIC
ON OPTIMIZE OPTION
OPTIONALLY OR ORDER
OUT OUTER OUTFILE
PRECISION PRIMARY PROCEDURE
PURGE RANGE READ
READS READ_ONLY READ_WRITE
REAL REFERENCES REGEXP
RELEASE RENAME REPEAT
REPLACE REQUIRE RESTRICT
RETURN REVOKE RIGHT
RLIKE SCHEMA SCHEMAS
SECOND_MICROSECOND SELECT SENSITIVE
SEPARATOR SET SHOW
SMALLINT SPATIAL SPECIFIC
SQL SQLEXCEPTION SQLSTATE
SQLWARNING SQL_BIG_RESULT SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT SSL STARTING
STRAIGHT_JOIN TABLE TERMINATED
THEN TINYBLOB TINYINT
TINYTEXT TO TRAILING
TRIGGER TRUE UNDO
UNION UNIQUE UNLOCK
UNSIGNED UPDATE UPGRADE
USAGE USE USING
UTC_DATE UTC_TIME UTC_TIMESTAMP
VALUES VARBINARY VARCHAR
VARCHARACTER VARYING WHEN
WHERE WHILE WITH
WRITE X509 XOR
YEAR_MONTH ZEROFILL
".Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var line in lines)
{
string[] keywords = line.Split(new char[] { ' ' });
foreach (string s in keywords)
{
if (String.IsNullOrEmpty(s))
{
continue;
}
MySqlSchemaRow row = dt.AddRow();
row[0] = s;
}
}
return(dt);
}
private MySqlSchemaCollection GetDataSourceInformation()
{
#if CF || RT
throw new NotSupportedException();
#else
MySqlSchemaCollection dt = new MySqlSchemaCollection("DataSourceInformation");
dt.AddColumn("CompositeIdentifierSeparatorPattern", typeof(string));
dt.AddColumn("DataSourceProductName", typeof(string));
dt.AddColumn("DataSourceProductVersion", typeof(string));
dt.AddColumn("DataSourceProductVersionNormalized", typeof(string));
dt.AddColumn("GroupByBehavior", typeof(GroupByBehavior));
dt.AddColumn("IdentifierPattern", typeof(string));
dt.AddColumn("IdentifierCase", typeof(IdentifierCase));
dt.AddColumn("OrderByColumnsInSelect", typeof(bool));
dt.AddColumn("ParameterMarkerFormat", typeof(string));
dt.AddColumn("ParameterMarkerPattern", typeof(string));
dt.AddColumn("ParameterNameMaxLength", typeof(int));
dt.AddColumn("ParameterNamePattern", typeof(string));
dt.AddColumn("QuotedIdentifierPattern", typeof(string));
dt.AddColumn("QuotedIdentifierCase", typeof(IdentifierCase));
dt.AddColumn("StatementSeparatorPattern", typeof(string));
dt.AddColumn("StringLiteralPattern", typeof(string));
dt.AddColumn("SupportedJoinOperators", typeof(SupportedJoinOperators));
DBVersion v = connection.driver.Version;
string ver = String.Format("{0:0}.{1:0}.{2:0}",
v.Major, v.Minor, v.Build);
MySqlSchemaRow row = dt.AddRow();
row["CompositeIdentifierSeparatorPattern"] = "\\.";
row["DataSourceProductName"] = "MySQL";
row["DataSourceProductVersion"] = connection.ServerVersion;
row["DataSourceProductVersionNormalized"] = ver;
row["GroupByBehavior"] = GroupByBehavior.Unrelated;
row["IdentifierPattern"] =
@"(^\`\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\`[^\`\0]|\`\`+\`$)|(^\"" + [^\""\0]|\""\""+\""$)";
row["IdentifierCase"] = IdentifierCase.Insensitive;
row["OrderByColumnsInSelect"] = false;
row["ParameterMarkerFormat"] = "{0}";
row["ParameterMarkerPattern"] = "(@[A-Za-z0-9_$#]*)";
row["ParameterNameMaxLength"] = 128;
row["ParameterNamePattern"] =
@"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
row["QuotedIdentifierPattern"] = @"(([^\`]|\`\`)*)";
row["QuotedIdentifierCase"] = IdentifierCase.Sensitive;
row["StatementSeparatorPattern"] = ";";
row["StringLiteralPattern"] = "'(([^']|'')*)'";
row["SupportedJoinOperators"] = 15;
dt.Rows.Add(row);
return dt;
#endif
}
private void FindTables(MySqlSchemaCollection schema, string[] restrictions)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture,
"SHOW TABLE STATUS FROM `{0}`", restrictions[1]);
if (restrictions != null && restrictions.Length >= 3 &&
restrictions[2] != null)
where.AppendFormat(CultureInfo.InvariantCulture,
" LIKE '{0}'", restrictions[2]);
sql.Append(where.ToString());
string table_type = restrictions[1].ToLower() == "information_schema"
?
"SYSTEM VIEW"
: "BASE TABLE";
MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MySqlSchemaRow row = schema.AddRow();
row["TABLE_CATALOG"] = null;
row["TABLE_SCHEMA"] = restrictions[1];
row["TABLE_NAME"] = reader.GetString(0);
row["TABLE_TYPE"] = table_type;
row["ENGINE"] = GetString(reader, 1);
row["VERSION"] = reader.GetValue(2);
row["ROW_FORMAT"] = GetString(reader, 3);
row["TABLE_ROWS"] = reader.GetValue(4);
row["AVG_ROW_LENGTH"] = reader.GetValue(5);
row["DATA_LENGTH"] = reader.GetValue(6);
row["MAX_DATA_LENGTH"] = reader.GetValue(7);
row["INDEX_LENGTH"] = reader.GetValue(8);
row["DATA_FREE"] = reader.GetValue(9);
row["AUTO_INCREMENT"] = reader.GetValue(10);
row["CREATE_TIME"] = reader.GetValue(11);
row["UPDATE_TIME"] = reader.GetValue(12);
row["CHECK_TIME"] = reader.GetValue(13);
row["TABLE_COLLATION"] = GetString(reader, 14);
row["CHECKSUM"] = reader.GetValue(15);
row["CREATE_OPTIONS"] = GetString(reader, 16);
row["TABLE_COMMENT"] = GetString(reader, 17);
}
}
}
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 = token.ToUpper();
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 = tokenizer.NextToken().ToUpper();
if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0)
{
MySqlSchemaRow parameterRow = parametersTable.Rows[0];
parameterRow["PARAMETER_NAME"] = "RETURN_VALUE";
ParseDataType(parameterRow, tokenizer);
}
}
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;
}
internal static void SetDSInfo(MySqlSchemaCollection sc)
{
// we use name indexing because this method will only be called
// when GetSchema is called for the DataSourceInformation
// collection and then it wil be cached.
MySqlSchemaRow row = sc.AddRow();
row["TypeName"] = "TINY INT";
row["ProviderDbType"] = MySqlDbType.UByte;
row["ColumnSize"] = 0;
row["CreateFormat"] = "TINYINT UNSIGNED";
row["CreateParameters"] = null;
row["DataType"] = "System.Byte";
row["IsAutoincrementable"] = true;
row["IsBestMatch"] = true;
row["IsCaseSensitive"] = false;
row["IsFixedLength"] = true;
row["IsFixedPrecisionScale"] = true;
row["IsLong"] = false;
row["IsNullable"] = true;
row["IsSearchable"] = true;
row["IsSearchableWithLike"] = false;
row["IsUnsigned"] = true;
row["MaximumScale"] = 0;
row["MinimumScale"] = 0;
row["IsConcurrencyType"] = DBNull.Value;
row["IsLiteralSupported"] = false;
row["LiteralPrefix"] = null;
row["LiteralSuffix"] = null;
row["NativeDataType"] = null;
}
private static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");
string[] lines = @"ACCESSIBLE ADD ALL
ALTER ANALYZE AND
AS ASC ASENSITIVE
BEFORE BETWEEN BIGINT
BINARY BLOB BOTH
BY CALL CASCADE
CASE CHANGE CHAR
CHARACTER CHECK COLLATE
COLUMN CONDITION CONNECTION
CONSTRAINT CONTINUE CONTRIBUTORS
CONVERT CREATE CROSS
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP
CURRENT_USER CURSOR DATABASE
DATABASES DAY_HOUR DAY_MICROSECOND
DAY_MINUTE DAY_SECOND DEC
DECIMAL DECLARE DEFAULT
DELAYED DELETE DESC
DESCRIBE DETERMINISTIC DISTINCT
DISTINCTROW DIV DOUBLE
DROP DUAL EACH
ELSE ELSEIF ENCLOSED
ESCAPED EXISTS EXIT
EXPLAIN FALSE FETCH
FLOAT FLOAT4 FLOAT8
FOR FORCE FOREIGN
FROM FULLTEXT GRANT
GROUP HAVING HIGH_PRIORITY
HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND
IF IGNORE IN
INDEX INFILE INNER
INOUT INSENSITIVE INSERT
INT INT1 INT2
INT3 INT4 INT8
INTEGER INTERVAL INTO
IS ITERATE JOIN
KEY KEYS KILL
LEADING LEAVE LEFT
LIKE LIMIT LINEAR
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOOP
LOW_PRIORITY MATCH MEDIUMBLOB
MEDIUMINT MEDIUMTEXT MIDDLEINT
MINUTE_MICROSECOND MINUTE_SECOND MOD
MODIFIES NATURAL NOT
NO_WRITE_TO_BINLOG NULL NUMERIC
ON OPTIMIZE OPTION
OPTIONALLY OR ORDER
OUT OUTER OUTFILE
PRECISION PRIMARY PROCEDURE
PURGE RANGE READ
READS READ_ONLY READ_WRITE
REAL REFERENCES REGEXP
RELEASE RENAME REPEAT
REPLACE REQUIRE RESTRICT
RETURN REVOKE RIGHT
RLIKE SCHEMA SCHEMAS
SECOND_MICROSECOND SELECT SENSITIVE
SEPARATOR SET SHOW
SMALLINT SPATIAL SPECIFIC
SQL SQLEXCEPTION SQLSTATE
SQLWARNING SQL_BIG_RESULT SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT SSL STARTING
STRAIGHT_JOIN TABLE TERMINATED
THEN TINYBLOB TINYINT
TINYTEXT TO TRAILING
TRIGGER TRUE UNDO
UNION UNIQUE UNLOCK
UNSIGNED UPDATE UPGRADE
USAGE USE USING
UTC_DATE UTC_TIME UTC_TIMESTAMP
VALUES VARBINARY VARCHAR
VARCHARACTER VARYING WHEN
WHERE WHILE WITH
WRITE X509 XOR
YEAR_MONTH ZEROFILL
".Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var line in lines)
{
string[] keywords = line.Split(new char[] { ' ' });
foreach (string s in keywords)
{
if (String.IsNullOrEmpty(s)) continue;
MySqlSchemaRow row = dt.AddRow();
row[0] = s;
}
}
return dt;
}
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);
}
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;
}
public virtual MySqlSchemaCollection GetIndexes(string[] restrictions)
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("Indexes");
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("UNIQUE", typeof(bool));
dt.AddColumn("PRIMARY", typeof(bool));
dt.AddColumn("TYPE", typeof(string));
dt.AddColumn("COMMENT", typeof(string));
// Get the list of tables first
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}`",
MySqlHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]),
MySqlHelper.DoubleQuoteString((string)table["TABLE_NAME"]));
MySqlSchemaCollection indexes = QueryCollection("indexes", sql);
foreach (MySqlSchemaRow index in indexes.Rows)
{
long seq_index = (long)index["SEQ_IN_INDEX"];
if (seq_index != 1) continue;
if (restrictions != null && restrictions.Length == 4 &&
restrictions[3] != null &&
!index["KEY_NAME"].Equals(restrictions[3])) continue;
MySqlSchemaRow row = dt.AddRow();
row["INDEX_CATALOG"] = null;
row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"];
row["INDEX_NAME"] = index["KEY_NAME"];
row["TABLE_NAME"] = index["TABLE"];
row["UNIQUE"] = (long)index["NON_UNIQUE"] == 0;
row["PRIMARY"] = index["KEY_NAME"].Equals("PRIMARY");
row["TYPE"] = index["INDEX_TYPE"];
row["COMMENT"] = index["COMMENT"];
}
}
return dt;
}
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;
}
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);
}
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;
}
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);
}
}
}
internal static void SetDSInfo(MySqlSchemaCollection sc)
{
string[] types = new string[] { "DATE", "DATETIME", "TIMESTAMP" };
MySqlDbType[] dbtype = new MySqlDbType[] { MySqlDbType.Date,
MySqlDbType.DateTime, MySqlDbType.Timestamp };
// we use name indexing because this method will only be called
// when GetSchema is called for the DataSourceInformation
// collection and then it wil be cached.
for (int x = 0; x < types.Length; x++)
{
MySqlSchemaRow row = sc.AddRow();
row["TypeName"] = types[x];
row["ProviderDbType"] = dbtype[x];
row["ColumnSize"] = 0;
row["CreateFormat"] = types[x];
row["CreateParameters"] = null;
row["DataType"] = "System.DateTime";
row["IsAutoincrementable"] = false;
row["IsBestMatch"] = true;
row["IsCaseSensitive"] = false;
row["IsFixedLength"] = true;
row["IsFixedPrecisionScale"] = true;
row["IsLong"] = false;
row["IsNullable"] = true;
row["IsSearchable"] = true;
row["IsSearchableWithLike"] = false;
row["IsUnsigned"] = false;
row["MaximumScale"] = 0;
row["MinimumScale"] = 0;
row["IsConcurrencyType"] = DBNull.Value;
row["IsLiteralSupported"] = false;
row["LiteralPrefix"] = null;
row["LiteralSuffix"] = null;
row["NativeDataType"] = null;
}
}
private static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");
#if !RT
dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string));
Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream(
"MySql.Data.MySqlClient.Properties.ReservedWords.txt");
#else
dt.AddColumn("ReservedWord", typeof(string));
Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("MySql.Data.MySqlClient.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 !CF
sr.Dispose();
#else
sr.Close();
#endif
str.Close();
return dt;
}
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);
}
}
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;
}
internal static void SetDSInfo(MySqlSchemaCollection sc)
{
string[] types = new string[] { "CHAR", "NCHAR", "VARCHAR", "NVARCHAR", "SET",
"ENUM", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT" };
MySqlDbType[] dbtype = new MySqlDbType[] { MySqlDbType.String, MySqlDbType.String,
MySqlDbType.VarChar, MySqlDbType.VarChar, MySqlDbType.Set, MySqlDbType.Enum,
MySqlDbType.TinyText, MySqlDbType.Text, MySqlDbType.MediumText,
MySqlDbType.LongText };
// we use name indexing because this method will only be called
// when GetSchema is called for the DataSourceInformation
// collection and then it wil be cached.
for (int x = 0; x < types.Length; x++)
{
MySqlSchemaRow row = sc.AddRow();
row["TypeName"] = types[x];
row["ProviderDbType"] = dbtype[x];
row["ColumnSize"] = 0;
row["CreateFormat"] = x < 4 ? types[x] + "({0})" : types[x];
row["CreateParameters"] = x < 4 ? "size" : null;
row["DataType"] = "System.String";
row["IsAutoincrementable"] = false;
row["IsBestMatch"] = true;
row["IsCaseSensitive"] = false;
row["IsFixedLength"] = false;
row["IsFixedPrecisionScale"] = true;
row["IsLong"] = false;
row["IsNullable"] = true;
row["IsSearchable"] = true;
row["IsSearchableWithLike"] = true;
row["IsUnsigned"] = false;
row["MaximumScale"] = 0;
row["MinimumScale"] = 0;
row["IsConcurrencyType"] = DBNull.Value;
row["IsLiteralSupported"] = false;
row["LiteralPrefix"] = null;
row["LiteralSuffix"] = null;
row["NativeDataType"] = null;
}
}
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);
}
}
protected static void FillTable(MySqlSchemaCollection dt, object[][] data)
{
foreach (object[] dataItem in data)
{
MySqlSchemaRow row = dt.AddRow();
for (int i = 0; i < dataItem.Length; i++)
row[i] = dataItem[i];
}
}
