protected virtual MySqlSchemaCollection GetCollections()
{
object[][] collections = new object[][]
{
new object[] { "MetaDataCollections", 0, 0 },
new object[] { "DataSourceInformation", 0, 0 },
new object[] { "DataTypes", 0, 0 },
new object[] { "Restrictions", 0, 0 },
new object[] { "ReservedWords", 0, 0 },
new object[] { "Databases", 1, 1 },
new object[] { "Tables", 4, 2 },
new object[] { "Columns", 4, 4 },
new object[] { "Users", 1, 1 },
new object[] { "Foreign Keys", 4, 3 },
new object[] { "IndexColumns", 5, 4 },
new object[] { "Indexes", 4, 3 },
new object[] { "Foreign Key Columns", 4, 3 },
new object[] { "UDF", 1, 1 }
};
MySqlSchemaCollection dt = new MySqlSchemaCollection("MetaDataCollections");
dt.AddColumn("CollectionName", typeof(string));
dt.AddColumn("NumberOfRestrictions", typeof(int));
dt.AddColumn("NumberOfIdentifierParts", typeof(int));
FillTable(dt, collections);
return(dt);
}
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;
}
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);
}
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);
}
protected virtual MySqlSchemaCollection GetRestrictions()
{
object[][] restrictions = new object[][]
{
new object[] { "Users", "Name", "", 0 },
new object[] { "Databases", "Name", "", 0 },
new object[] { "Tables", "Database", "", 0 },
new object[] { "Tables", "Schema", "", 1 },
new object[] { "Tables", "Table", "", 2 },
new object[] { "Tables", "TableType", "", 3 },
new object[] { "Columns", "Database", "", 0 },
new object[] { "Columns", "Schema", "", 1 },
new object[] { "Columns", "Table", "", 2 },
new object[] { "Columns", "Column", "", 3 },
new object[] { "Indexes", "Database", "", 0 },
new object[] { "Indexes", "Schema", "", 1 },
new object[] { "Indexes", "Table", "", 2 },
new object[] { "Indexes", "Name", "", 3 },
new object[] { "IndexColumns", "Database", "", 0 },
new object[] { "IndexColumns", "Schema", "", 1 },
new object[] { "IndexColumns", "Table", "", 2 },
new object[] { "IndexColumns", "ConstraintName", "", 3 },
new object[] { "IndexColumns", "Column", "", 4 },
new object[] { "Foreign Keys", "Database", "", 0 },
new object[] { "Foreign Keys", "Schema", "", 1 },
new object[] { "Foreign Keys", "Table", "", 2 },
new object[] { "Foreign Keys", "Constraint Name", "", 3 },
new object[] { "Foreign Key Columns", "Catalog", "", 0 },
new object[] { "Foreign Key Columns", "Schema", "", 1 },
new object[] { "Foreign Key Columns", "Table", "", 2 },
new object[] { "Foreign Key Columns", "Constraint Name", "", 3 },
new object[] { "UDF", "Name", "", 0 }
};
MySqlSchemaCollection dt = new MySqlSchemaCollection("Restrictions");
dt.AddColumn("CollectionName", typeof(string));
dt.AddColumn("RestrictionName", typeof(string));
dt.AddColumn("RestrictionDefault", typeof(string));
dt.AddColumn("RestrictionNumber", typeof(int));
FillTable(dt, restrictions);
return(dt);
}
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);
}
private MySqlSchemaCollection GetProceduresWithParameters(string[] restrictions)
{
MySqlSchemaCollection dt = GetProcedures(restrictions);
dt.AddColumn("ParameterList", typeof(string));
foreach (MySqlSchemaRow row in dt.Rows)
{
row["ParameterList"] = GetProcedureParameterLine(row);
}
return(dt);
}
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);
}
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);
}
public virtual MySqlSchemaCollection GetTables(string[] restrictions)
{
MySqlSchemaCollection c = new MySqlSchemaCollection("Tables");
c.AddColumn("TABLE_CATALOG", typeof(string));
c.AddColumn("TABLE_SCHEMA", typeof(string));
c.AddColumn("TABLE_NAME", typeof(string));
c.AddColumn("TABLE_TYPE", typeof(string));
c.AddColumn("ENGINE", typeof(string));
c.AddColumn("VERSION", typeof(ulong));
c.AddColumn("ROW_FORMAT", typeof(string));
c.AddColumn("TABLE_ROWS", typeof(ulong));
c.AddColumn("AVG_ROW_LENGTH", typeof(ulong));
c.AddColumn("DATA_LENGTH", typeof(ulong));
c.AddColumn("MAX_DATA_LENGTH", typeof(ulong));
c.AddColumn("INDEX_LENGTH", typeof(ulong));
c.AddColumn("DATA_FREE", typeof(ulong));
c.AddColumn("AUTO_INCREMENT", typeof(ulong));
c.AddColumn("CREATE_TIME", typeof(DateTime));
c.AddColumn("UPDATE_TIME", typeof(DateTime));
c.AddColumn("CHECK_TIME", typeof(DateTime));
c.AddColumn("TABLE_COLLATION", typeof(string));
c.AddColumn("CHECKSUM", typeof(ulong));
c.AddColumn("CREATE_OPTIONS", typeof(string));
c.AddColumn("TABLE_COMMENT", typeof(string));
// we have to new up a new restriction array here since
// GetDatabases takes the database in the first slot
string[] dbRestriction = new string[4];
if (restrictions != null && restrictions.Length >= 2)
dbRestriction[0] = restrictions[1];
MySqlSchemaCollection databases = GetDatabases(dbRestriction);
if (restrictions != null)
Array.Copy(restrictions, dbRestriction,
Math.Min(dbRestriction.Length, restrictions.Length));
foreach (MySqlSchemaRow row in databases.Rows)
{
dbRestriction[1] = row["SCHEMA_NAME"].ToString();
FindTables(c, dbRestriction);
}
return c;
}
public virtual MySqlSchemaCollection GetForeignKeyColumns(string[] restrictions)
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("Foreign Keys");
dt.AddColumn("CONSTRAINT_CATALOG", typeof(string));
dt.AddColumn("CONSTRAINT_SCHEMA", typeof(string));
dt.AddColumn("CONSTRAINT_NAME", typeof(string));
dt.AddColumn("TABLE_CATALOG", typeof(string));
dt.AddColumn("TABLE_SCHEMA", typeof(string));
dt.AddColumn("TABLE_NAME", typeof(string));
dt.AddColumn("COLUMN_NAME", typeof(string));
dt.AddColumn("ORDINAL_POSITION", typeof(int));
dt.AddColumn("REFERENCED_TABLE_CATALOG", typeof(string));
dt.AddColumn("REFERENCED_TABLE_SCHEMA", typeof(string));
dt.AddColumn("REFERENCED_TABLE_NAME", typeof(string));
dt.AddColumn("REFERENCED_COLUMN_NAME", typeof(string));
// first we use our restrictions to get a list of tables that should be
// consulted. We save the keyname restriction since GetTables doesn't
// understand that.
string keyName = null;
if (restrictions != null && restrictions.Length >= 4)
{
keyName = restrictions[3];
restrictions[3] = null;
}
MySqlSchemaCollection tables = GetTables(restrictions);
// now for each table retrieved, we call our helper function to
// parse it's foreign keys
foreach (MySqlSchemaRow table in tables.Rows)
{
GetForeignKeysOnTable(dt, table, keyName, true);
}
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;
}
internal MySqlSchemaCollection CreateParametersTable()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedure Parameters");
dt.AddColumn("SPECIFIC_CATALOG", typeof(string));
dt.AddColumn("SPECIFIC_SCHEMA", typeof(string));
dt.AddColumn("SPECIFIC_NAME", typeof(string));
dt.AddColumn("ORDINAL_POSITION", typeof(Int32));
dt.AddColumn("PARAMETER_MODE", typeof(string));
dt.AddColumn("PARAMETER_NAME", typeof(string));
dt.AddColumn("DATA_TYPE", typeof(string));
dt.AddColumn("CHARACTER_MAXIMUM_LENGTH", typeof(Int32));
dt.AddColumn("CHARACTER_OCTET_LENGTH", typeof(Int32));
dt.AddColumn("NUMERIC_PRECISION", typeof(byte));
dt.AddColumn("NUMERIC_SCALE", typeof(Int32));
dt.AddColumn("CHARACTER_SET_NAME", typeof(string));
dt.AddColumn("COLLATION_NAME", typeof(string));
dt.AddColumn("DTD_IDENTIFIER", typeof(string));
dt.AddColumn("ROUTINE_TYPE", typeof(string));
return(dt);
}
public virtual MySqlSchemaCollection GetColumns(string[] restrictions)
{
MySqlSchemaCollection c = new MySqlSchemaCollection("Columns");
c.AddColumn("TABLE_CATALOG", typeof(string));
c.AddColumn("TABLE_SCHEMA", typeof(string));
c.AddColumn("TABLE_NAME", typeof(string));
c.AddColumn("COLUMN_NAME", typeof(string));
c.AddColumn("ORDINAL_POSITION", typeof(ulong));
c.AddColumn("COLUMN_DEFAULT", typeof(string));
c.AddColumn("IS_NULLABLE", typeof(string));
c.AddColumn("DATA_TYPE", typeof(string));
c.AddColumn("CHARACTER_MAXIMUM_LENGTH", typeof(ulong));
c.AddColumn("CHARACTER_OCTET_LENGTH", typeof(ulong));
c.AddColumn("NUMERIC_PRECISION", typeof(ulong));
c.AddColumn("NUMERIC_SCALE", typeof(ulong));
c.AddColumn("CHARACTER_SET_NAME", typeof(string));
c.AddColumn("COLLATION_NAME", typeof(string));
c.AddColumn("COLUMN_TYPE", typeof(string));
c.AddColumn("COLUMN_KEY", typeof(string));
c.AddColumn("EXTRA", typeof(string));
c.AddColumn("PRIVILEGES", typeof(string));
c.AddColumn("COLUMN_COMMENT", typeof(string));
// we don't allow restricting on table type here
string columnName = null;
if (restrictions != null && restrictions.Length == 4)
{
columnName = restrictions[3];
restrictions[3] = null;
}
MySqlSchemaCollection tables = GetTables(restrictions);
foreach (MySqlSchemaRow row in tables.Rows)
LoadTableColumns(c, row["TABLE_SCHEMA"].ToString(),
row["TABLE_NAME"].ToString(), columnName);
QuoteDefaultValues(c);
return c;
}
private MySqlSchemaCollection GetDataSourceInformation()
{
#if NETSTANDARD1_3
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 static MySqlSchemaCollection GetReservedWords()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords");
#if !NETSTANDARD1_6
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_6
sr.Dispose();
str.Dispose();
#else
sr.Close();
str.Close();
#endif
return dt;
}
protected virtual MySqlSchemaCollection GetRestrictions()
{
object[][] restrictions = new object[][]
{
new object[] {"Users", "Name", "", 0},
new object[] {"Databases", "Name", "", 0},
new object[] {"Tables", "Database", "", 0},
new object[] {"Tables", "Schema", "", 1},
new object[] {"Tables", "Table", "", 2},
new object[] {"Tables", "TableType", "", 3},
new object[] {"Columns", "Database", "", 0},
new object[] {"Columns", "Schema", "", 1},
new object[] {"Columns", "Table", "", 2},
new object[] {"Columns", "Column", "", 3},
new object[] {"Indexes", "Database", "", 0},
new object[] {"Indexes", "Schema", "", 1},
new object[] {"Indexes", "Table", "", 2},
new object[] {"Indexes", "Name", "", 3},
new object[] {"IndexColumns", "Database", "", 0},
new object[] {"IndexColumns", "Schema", "", 1},
new object[] {"IndexColumns", "Table", "", 2},
new object[] {"IndexColumns", "ConstraintName", "", 3},
new object[] {"IndexColumns", "Column", "", 4},
new object[] {"Foreign Keys", "Database", "", 0},
new object[] {"Foreign Keys", "Schema", "", 1},
new object[] {"Foreign Keys", "Table", "", 2},
new object[] {"Foreign Keys", "Constraint Name", "", 3},
new object[] {"Foreign Key Columns", "Catalog", "", 0},
new object[] {"Foreign Key Columns", "Schema", "", 1},
new object[] {"Foreign Key Columns", "Table", "", 2},
new object[] {"Foreign Key Columns", "Constraint Name", "", 3},
new object[] {"UDF", "Name", "", 0}
};
MySqlSchemaCollection dt = new MySqlSchemaCollection("Restrictions");
dt.AddColumn("CollectionName", typeof(string));
dt.AddColumn("RestrictionName", typeof(string));
dt.AddColumn("RestrictionDefault", typeof(string));
dt.AddColumn("RestrictionNumber", typeof(int));
FillTable(dt, restrictions);
return dt;
}
private static MySqlSchemaCollection GetDataTypes()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("DataTypes");
dt.AddColumn("TypeName", typeof(string));
dt.AddColumn("ProviderDbType", typeof(int));
dt.AddColumn("ColumnSize", typeof(long));
dt.AddColumn("CreateFormat", typeof(string));
dt.AddColumn("CreateParameters", typeof(string));
dt.AddColumn("DataType", typeof(string));
dt.AddColumn("IsAutoincrementable", typeof(bool));
dt.AddColumn("IsBestMatch", typeof(bool));
dt.AddColumn("IsCaseSensitive", typeof(bool));
dt.AddColumn("IsFixedLength", typeof(bool));
dt.AddColumn("IsFixedPrecisionScale", typeof(bool));
dt.AddColumn("IsLong", typeof(bool));
dt.AddColumn("IsNullable", typeof(bool));
dt.AddColumn("IsSearchable", typeof(bool));
dt.AddColumn("IsSearchableWithLike", typeof(bool));
dt.AddColumn("IsUnsigned", typeof(bool));
dt.AddColumn("MaximumScale", typeof(short));
dt.AddColumn("MinimumScale", typeof(short));
dt.AddColumn("IsConcurrencyType", typeof(bool));
dt.AddColumn("IsLiteralSupported", typeof(bool));
dt.AddColumn("LiteralPrefix", typeof(string));
dt.AddColumn("LiteralSuffix", typeof(string));
dt.AddColumn("NativeDataType", typeof(string));
// have each one of the types contribute to the datatypes collection
MySqlBit.SetDSInfo(dt);
MySqlBinary.SetDSInfo(dt);
MySqlDateTime.SetDSInfo(dt);
MySqlTimeSpan.SetDSInfo(dt);
MySqlString.SetDSInfo(dt);
MySqlDouble.SetDSInfo(dt);
MySqlSingle.SetDSInfo(dt);
MySqlByte.SetDSInfo(dt);
MySqlInt16.SetDSInfo(dt);
MySqlInt32.SetDSInfo(dt);
MySqlInt64.SetDSInfo(dt);
MySqlDecimal.SetDSInfo(dt);
MySqlUByte.SetDSInfo(dt);
MySqlUInt16.SetDSInfo(dt);
MySqlUInt32.SetDSInfo(dt);
MySqlUInt64.SetDSInfo(dt);
return dt;
}
private MySqlSchemaCollection GetDataSourceInformation()
{
#if NETSTANDARD1_6
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
}
protected virtual MySqlSchemaCollection GetCollections()
{
object[][] collections = new object[][]
{
new object[] {"MetaDataCollections", 0, 0},
new object[] {"DataSourceInformation", 0, 0},
new object[] {"DataTypes", 0, 0},
new object[] {"Restrictions", 0, 0},
new object[] {"ReservedWords", 0, 0},
new object[] {"Databases", 1, 1},
new object[] {"Tables", 4, 2},
new object[] {"Columns", 4, 4},
new object[] {"Users", 1, 1},
new object[] {"Foreign Keys", 4, 3},
new object[] {"IndexColumns", 5, 4},
new object[] {"Indexes", 4, 3},
new object[] {"Foreign Key Columns", 4, 3},
new object[] {"UDF", 1, 1}
};
MySqlSchemaCollection dt = new MySqlSchemaCollection("MetaDataCollections");
dt.AddColumn("CollectionName", typeof(string));
dt.AddColumn("NumberOfRestrictions", typeof(int));
dt.AddColumn("NumberOfIdentifierParts", typeof(int));
FillTable(dt, collections);
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 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;
}
private static MySqlSchemaCollection GetDataTypes()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("DataTypes");
dt.AddColumn("TypeName", typeof(string));
dt.AddColumn("ProviderDbType", typeof(int));
dt.AddColumn("ColumnSize", typeof(long));
dt.AddColumn("CreateFormat", typeof(string));
dt.AddColumn("CreateParameters", typeof(string));
dt.AddColumn("DataType", typeof(string));
dt.AddColumn("IsAutoincrementable", typeof(bool));
dt.AddColumn("IsBestMatch", typeof(bool));
dt.AddColumn("IsCaseSensitive", typeof(bool));
dt.AddColumn("IsFixedLength", typeof(bool));
dt.AddColumn("IsFixedPrecisionScale", typeof(bool));
dt.AddColumn("IsLong", typeof(bool));
dt.AddColumn("IsNullable", typeof(bool));
dt.AddColumn("IsSearchable", typeof(bool));
dt.AddColumn("IsSearchableWithLike", typeof(bool));
dt.AddColumn("IsUnsigned", typeof(bool));
dt.AddColumn("MaximumScale", typeof(short));
dt.AddColumn("MinimumScale", typeof(short));
dt.AddColumn("IsConcurrencyType", typeof(bool));
dt.AddColumn("IsLiteralSupported", typeof(bool));
dt.AddColumn("LiteralPrefix", typeof(string));
dt.AddColumn("LiteralSuffix", typeof(string));
dt.AddColumn("NativeDataType", typeof(string));
// have each one of the types contribute to the datatypes collection
MySqlBit.SetDSInfo(dt);
MySqlBinary.SetDSInfo(dt);
MySqlDateTime.SetDSInfo(dt);
MySqlTimeSpan.SetDSInfo(dt);
MySqlString.SetDSInfo(dt);
MySqlDouble.SetDSInfo(dt);
MySqlSingle.SetDSInfo(dt);
MySqlByte.SetDSInfo(dt);
MySqlInt16.SetDSInfo(dt);
MySqlInt32.SetDSInfo(dt);
MySqlInt64.SetDSInfo(dt);
MySqlDecimal.SetDSInfo(dt);
MySqlUByte.SetDSInfo(dt);
MySqlUInt16.SetDSInfo(dt);
MySqlUInt32.SetDSInfo(dt);
MySqlUInt64.SetDSInfo(dt);
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;
}
public virtual MySqlSchemaCollection GetTables(string[] restrictions)
{
MySqlSchemaCollection c = new MySqlSchemaCollection("Tables");
c.AddColumn("TABLE_CATALOG", typeof(string));
c.AddColumn("TABLE_SCHEMA", typeof(string));
c.AddColumn("TABLE_NAME", typeof(string));
c.AddColumn("TABLE_TYPE", typeof(string));
c.AddColumn("ENGINE", typeof(string));
c.AddColumn("VERSION", typeof(ulong));
c.AddColumn("ROW_FORMAT", typeof(string));
c.AddColumn("TABLE_ROWS", typeof(ulong));
c.AddColumn("AVG_ROW_LENGTH", typeof(ulong));
c.AddColumn("DATA_LENGTH", typeof(ulong));
c.AddColumn("MAX_DATA_LENGTH", typeof(ulong));
c.AddColumn("INDEX_LENGTH", typeof(ulong));
c.AddColumn("DATA_FREE", typeof(ulong));
c.AddColumn("AUTO_INCREMENT", typeof(ulong));
c.AddColumn("CREATE_TIME", typeof(DateTime));
c.AddColumn("UPDATE_TIME", typeof(DateTime));
c.AddColumn("CHECK_TIME", typeof(DateTime));
c.AddColumn("TABLE_COLLATION", typeof(string));
c.AddColumn("CHECKSUM", typeof(ulong));
c.AddColumn("CREATE_OPTIONS", typeof(string));
c.AddColumn("TABLE_COMMENT", typeof(string));
// we have to new up a new restriction array here since
// GetDatabases takes the database in the first slot
string[] dbRestriction = new string[4];
if (restrictions != null && restrictions.Length >= 2)
{
dbRestriction[0] = restrictions[1];
}
MySqlSchemaCollection databases = GetDatabases(dbRestriction);
if (restrictions != null)
{
Array.Copy(restrictions, dbRestriction,
Math.Min(dbRestriction.Length, restrictions.Length));
}
foreach (MySqlSchemaRow row in databases.Rows)
{
dbRestriction[1] = row["SCHEMA_NAME"].ToString();
FindTables(c, dbRestriction);
}
return(c);
}
public virtual MySqlSchemaCollection GetColumns(string[] restrictions)
{
MySqlSchemaCollection c = new MySqlSchemaCollection("Columns");
c.AddColumn("TABLE_CATALOG", typeof(string));
c.AddColumn("TABLE_SCHEMA", typeof(string));
c.AddColumn("TABLE_NAME", typeof(string));
c.AddColumn("COLUMN_NAME", typeof(string));
c.AddColumn("ORDINAL_POSITION", typeof(ulong));
c.AddColumn("COLUMN_DEFAULT", typeof(string));
c.AddColumn("IS_NULLABLE", typeof(string));
c.AddColumn("DATA_TYPE", typeof(string));
c.AddColumn("CHARACTER_MAXIMUM_LENGTH", typeof(ulong));
c.AddColumn("CHARACTER_OCTET_LENGTH", typeof(ulong));
c.AddColumn("NUMERIC_PRECISION", typeof(ulong));
c.AddColumn("NUMERIC_SCALE", typeof(ulong));
c.AddColumn("CHARACTER_SET_NAME", typeof(string));
c.AddColumn("COLLATION_NAME", typeof(string));
c.AddColumn("COLUMN_TYPE", typeof(string));
c.AddColumn("COLUMN_KEY", typeof(string));
c.AddColumn("EXTRA", typeof(string));
c.AddColumn("PRIVILEGES", typeof(string));
c.AddColumn("COLUMN_COMMENT", typeof(string));
// we don't allow restricting on table type here
string columnName = null;
if (restrictions != null && restrictions.Length == 4)
{
columnName = restrictions[3];
restrictions[3] = null;
}
MySqlSchemaCollection tables = GetTables(restrictions);
foreach (MySqlSchemaRow row in tables.Rows)
{
LoadTableColumns(c, row["TABLE_SCHEMA"].ToString(),
row["TABLE_NAME"].ToString(), columnName);
}
QuoteDefaultValues(c);
return(c);
}
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);
}
internal MySqlSchemaCollection CreateParametersTable()
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedure Parameters");
dt.AddColumn("SPECIFIC_CATALOG", typeof(string));
dt.AddColumn("SPECIFIC_SCHEMA", typeof(string));
dt.AddColumn("SPECIFIC_NAME", typeof(string));
dt.AddColumn("ORDINAL_POSITION", typeof(Int32));
dt.AddColumn("PARAMETER_MODE", typeof(string));
dt.AddColumn("PARAMETER_NAME", typeof(string));
dt.AddColumn("DATA_TYPE", typeof(string));
dt.AddColumn("CHARACTER_MAXIMUM_LENGTH", typeof(Int32));
dt.AddColumn("CHARACTER_OCTET_LENGTH", typeof(Int32));
dt.AddColumn("NUMERIC_PRECISION", typeof(byte));
dt.AddColumn("NUMERIC_SCALE", typeof(Int32));
dt.AddColumn("CHARACTER_SET_NAME", typeof(string));
dt.AddColumn("COLLATION_NAME", typeof(string));
dt.AddColumn("DTD_IDENTIFIER", typeof(string));
dt.AddColumn("ROUTINE_TYPE", typeof(string));
return dt;
}
public virtual MySqlSchemaCollection GetForeignKeyColumns(string[] restrictions)
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("Foreign Keys");
dt.AddColumn("CONSTRAINT_CATALOG", typeof(string));
dt.AddColumn("CONSTRAINT_SCHEMA", typeof(string));
dt.AddColumn("CONSTRAINT_NAME", typeof(string));
dt.AddColumn("TABLE_CATALOG", typeof(string));
dt.AddColumn("TABLE_SCHEMA", typeof(string));
dt.AddColumn("TABLE_NAME", typeof(string));
dt.AddColumn("COLUMN_NAME", typeof(string));
dt.AddColumn("ORDINAL_POSITION", typeof(int));
dt.AddColumn("REFERENCED_TABLE_CATALOG", typeof(string));
dt.AddColumn("REFERENCED_TABLE_SCHEMA", typeof(string));
dt.AddColumn("REFERENCED_TABLE_NAME", typeof(string));
dt.AddColumn("REFERENCED_COLUMN_NAME", typeof(string));
// first we use our restrictions to get a list of tables that should be
// consulted. We save the keyname restriction since GetTables doesn't
// understand that.
string keyName = null;
if (restrictions != null && restrictions.Length >= 4)
{
keyName = restrictions[3];
restrictions[3] = null;
}
MySqlSchemaCollection tables = GetTables(restrictions);
// now for each table retrieved, we call our helper function to
// parse it's foreign keys
foreach (MySqlSchemaRow table in tables.Rows)
GetForeignKeysOnTable(dt, table, keyName, true);
return dt;
}