protected override MyCatSchemaCollection GetRestrictions()
{
MyCatSchemaCollection dt = base.GetRestrictions();
object[][] restrictions = new object[][]
{
new object[] { "Procedure Parameters", "Database", "", 0 },
new object[] { "Procedure Parameters", "Schema", "", 1 },
new object[] { "Procedure Parameters", "Name", "", 2 },
new object[] { "Procedure Parameters", "Type", "", 3 },
new object[] { "Procedure Parameters", "Parameter", "", 4 },
new object[] { "Procedures", "Database", "", 0 },
new object[] { "Procedures", "Schema", "", 1 },
new object[] { "Procedures", "Name", "", 2 },
new object[] { "Procedures", "Type", "", 3 },
new object[] { "Views", "Database", "", 0 },
new object[] { "Views", "Schema", "", 1 },
new object[] { "Views", "Table", "", 2 },
new object[] { "ViewColumns", "Database", "", 0 },
new object[] { "ViewColumns", "Schema", "", 1 },
new object[] { "ViewColumns", "Table", "", 2 },
new object[] { "ViewColumns", "Column", "", 3 },
new object[] { "Triggers", "Database", "", 0 },
new object[] { "Triggers", "Schema", "", 1 },
new object[] { "Triggers", "Name", "", 2 },
new object[] { "Triggers", "EventObjectTable", "", 3 },
};
FillTable(dt, restrictions);
return(dt);
}
private MyCatSchemaCollection GetParametersFromIS(string[] restrictions, MyCatSchemaCollection routines)
{
MyCatSchemaCollection parms = null;
if (routines == null || routines.Rows.Count == 0)
{
if (restrictions == null)
{
parms = QueryCollection("parameters", "SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE 1=2");
}
else
{
parms = GetParametersForRoutineFromIS(restrictions);
}
}
else
{
foreach (MyCatSchemaRow routine in routines.Rows)
{
if (restrictions != null && restrictions.Length >= 3)
{
restrictions[2] = routine["ROUTINE_NAME"].ToString();
}
parms = GetParametersForRoutineFromIS(restrictions);
}
}
parms.Name = "Procedure Parameters";
return(parms);
}
/// <summary>
/// Return schema information about procedures and functions
/// Restrictions supported are:
/// schema, name, type
/// </summary>
/// <param name="restrictions"></param>
/// <returns></returns>
public override MyCatSchemaCollection GetProcedures(string[] restrictions)
{
try
{
if (connection.Settings.HasProcAccess)
{
return(base.GetProcedures(restrictions));
}
}
catch (MyCatException ex)
{
if (ex.Number == (int)MyCatErrorCode.TableAccessDenied)
{
connection.Settings.HasProcAccess = false;
}
else
{
throw;
}
}
string[] keys = new string[4];
keys[0] = "ROUTINE_CATALOG";
keys[1] = "ROUTINE_SCHEMA";
keys[2] = "ROUTINE_NAME";
keys[3] = "ROUTINE_TYPE";
MyCatSchemaCollection dt = Query("ROUTINES", null, keys, restrictions);
dt.Name = "Procedures";
return(dt);
}
private MyCatSchemaCollection GetTable(string sql)
{
MyCatSchemaCollection c = new MyCatSchemaCollection();
MyCatCommand cmd = new MyCatCommand(sql, connection);
MyCatDataReader 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())
{
MyCatSchemaRow row = c.AddRow();
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader.GetValue(i);
}
}
}
return(c);
}
protected virtual MyCatSchemaCollection 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 }
};
MyCatSchemaCollection dt = new MyCatSchemaCollection("MetaDataCollections");
dt.AddColumn("CollectionName", typeof(string));
dt.AddColumn("NumberOfRestrictions", typeof(int));
dt.AddColumn("NumberOfIdentifierParts", typeof(int));
FillTable(dt, collections);
return(dt);
}
private MyCatSchemaCollection GetParametersForRoutineFromIS(string[] restrictions)
{
string[] keys = new string[5];
keys[0] = "SPECIFIC_CATALOG";
keys[1] = "SPECIFIC_SCHEMA";
keys[2] = "SPECIFIC_NAME";
keys[3] = "ROUTINE_TYPE";
keys[4] = "PARAMETER_NAME";
StringBuilder sql = new StringBuilder(@"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS");
// now get our where clause and append it if there is one
string where = GetWhereClause(null, keys, restrictions);
if (!String.IsNullOrEmpty(where))
{
sql.AppendFormat(CultureInfo.InvariantCulture, " WHERE {0}", where);
}
MyCatSchemaCollection coll = QueryCollection("parameters", sql.ToString());
if ((coll.Rows.Count != 0) && ((string)coll.Rows[0]["routine_type"] == "FUNCTION"))
{
// update missing data for the first row (function return value).
// (using sames valus than GetParametersFromShowCreate).
coll.Rows[0]["parameter_mode"] = "IN";
coll.Rows[0]["parameter_name"] = "return_value"; // "FUNCTION";
}
return(coll);
}
protected override MyCatSchemaCollection GetSchemaInternal(string collection, string[] restrictions)
{
MyCatSchemaCollection dt = base.GetSchemaInternal(collection, restrictions);
if (dt != null)
{
return(dt);
}
switch (collection)
{
case "VIEWS":
return(GetViews(restrictions));
case "PROCEDURES":
return(GetProcedures(restrictions));
case "PROCEDURES WITH PARAMETERS":
return(GetProceduresWithParameters(restrictions));
case "PROCEDURE PARAMETERS":
return(GetProcedureParameters(restrictions, null));
case "TRIGGERS":
return(GetTriggers(restrictions));
case "VIEWCOLUMNS":
return(GetViewColumns(restrictions));
}
return(null);
}
public virtual MyCatSchemaCollection GetIndexes(string[] restrictions)
{
MyCatSchemaCollection dt = new MyCatSchemaCollection("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";
MyCatSchemaCollection tables = GetTables(tableRestrictions);
foreach (MyCatSchemaRow table in tables.Rows)
{
string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
MyCatHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]),
MyCatHelper.DoubleQuoteString((string)table["TABLE_NAME"]));
MyCatSchemaCollection indexes = QueryCollection("indexes", sql);
foreach (MyCatSchemaRow 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;
}
MyCatSchemaRow 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 static void ParseConstraint(MyCatSchemaCollection fkTable, MyCatSchemaRow table,
MyCatTokenizer tokenizer, bool includeColumns)
{
string name = tokenizer.NextToken();
MyCatSchemaRow 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);
}
}
public override MyCatSchemaCollection GetDatabases(string[] restrictions)
{
string[] keys = new string[1];
keys[0] = "SCHEMA_NAME";
MyCatSchemaCollection dt = Query("SCHEMATA", "", keys, restrictions);
dt.Columns[1].Name = "database_name";
dt.Name = "Databases";
return(dt);
}
private MyCatSchemaCollection GetViews(string[] restrictions)
{
string[] keys = new string[3];
keys[0] = "TABLE_CATALOG";
keys[1] = "TABLE_SCHEMA";
keys[2] = "TABLE_NAME";
MyCatSchemaCollection dt = Query("VIEWS", null, keys, restrictions);
dt.Name = "Views";
return(dt);
}
protected static void FillTable(MyCatSchemaCollection dt, object[][] data)
{
foreach (object[] dataItem in data)
{
MyCatSchemaRow row = dt.AddRow();
for (int i = 0; i < dataItem.Length; i++)
{
row[i] = dataItem[i];
}
}
}
/// <summary>
/// Returns schema information for the data source of this <see cref="DbConnection"/>
/// using the specified string for the schema name and the specified string array
/// for the restriction values.
/// </summary>
/// <param name="collectionName">Specifies the name of the schema to return.</param>
/// <param name="restrictionValues">Specifies a set of restriction values for the requested schema.</param>
/// <returns>A <see cref="DataTable"/> that contains schema information.</returns>
public override DataTable GetSchema(string collectionName, string[] restrictionValues)
{
if (collectionName == null)
{
collectionName = SchemaProvider.MetaCollection;
}
string[] restrictions = schemaProvider.CleanRestrictions(restrictionValues);
MyCatSchemaCollection c = schemaProvider.GetSchema(collectionName, restrictions);
return(c.AsDataTable());
}
private static void ProcessColumns(MyCatSchemaCollection fkTable, MyCatSchemaRow row, List <string> srcColumns, List <string> targetColumns)
{
for (int i = 0; i < srcColumns.Count; i++)
{
MyCatSchemaRow 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 MyCatSchemaCollection GetSchemaCollection(string collectionName, string[] restrictionValues)
{
if (collectionName == null)
{
collectionName = SchemaProvider.MetaCollection;
}
string[] restrictions = schemaProvider.CleanRestrictions(restrictionValues);
MyCatSchemaCollection c = schemaProvider.GetSchema(collectionName, restrictions);
return(c);
}
public override MyCatSchemaCollection GetTables(string[] restrictions)
{
string[] keys = new string[4];
keys[0] = "TABLE_CATALOG";
keys[1] = "TABLE_SCHEMA";
keys[2] = "TABLE_NAME";
keys[3] = "TABLE_TYPE";
MyCatSchemaCollection dt = Query("TABLES", "TABLE_TYPE != 'VIEW'", keys, restrictions);
dt.Name = "Tables";
return(dt);
}
private MyCatSchemaCollection GetTriggers(string[] restrictions)
{
string[] keys = new string[4];
keys[0] = "TRIGGER_CATALOG";
keys[1] = "TRIGGER_SCHEMA";
keys[2] = "EVENT_OBJECT_TABLE";
keys[3] = "TRIGGER_NAME";
MyCatSchemaCollection dt = Query("TRIGGERS", null, keys, restrictions);
dt.Name = "Triggers";
return(dt);
}
private MyCatSchemaCollection GetProceduresWithParameters(string[] restrictions)
{
MyCatSchemaCollection dt = GetProcedures(restrictions);
dt.AddColumn("ParameterList", typeof(string));
foreach (MyCatSchemaRow row in dt.Rows)
{
row["ParameterList"] = GetProcedureParameterLine(row);
}
return(dt);
}
private MyCatSchemaCollection GetDataSourceInformation()
{
#if NETSTANDARD1_3
throw new NotSupportedException();
#else
MyCatSchemaCollection dt = new MyCatSchemaCollection("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);
MyCatSchemaRow 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(MyCatSchemaCollection 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";
MyCatCommand cmd = new MyCatCommand(sql.ToString(), connection);
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MyCatSchemaRow 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);
}
}
}
public override MyCatSchemaCollection GetColumns(string[] restrictions)
{
string[] keys = new string[4];
keys[0] = "TABLE_CATALOG";
keys[1] = "TABLE_SCHEMA";
keys[2] = "TABLE_NAME";
keys[3] = "COLUMN_NAME";
MyCatSchemaCollection dt = Query("COLUMNS", null, keys, restrictions);
dt.RemoveColumn("CHARACTER_OCTET_LENGTH");
dt.Name = "Columns";
QuoteDefaultValues(dt);
return(dt);
}
internal void GetParametersFromShowCreate(MyCatSchemaCollection parametersTable,
string[] restrictions, MyCatSchemaCollection routines)
{
// this allows us to pass in a pre-populated routines table
// and avoid the querying for them again.
// we use this when calling a procedure or function
if (routines == null)
{
routines = GetSchema("procedures", restrictions);
}
MyCatCommand cmd = connection.CreateCommand();
foreach (MyCatSchemaRow routine in routines.Rows)
{
string showCreateSql = String.Format("SHOW CREATE {0} `{1}`.`{2}`",
routine["ROUTINE_TYPE"], routine["ROUTINE_SCHEMA"],
routine["ROUTINE_NAME"]);
cmd.CommandText = showCreateSql;
try
{
string nameToRestrict = null;
if (restrictions != null && restrictions.Length == 5 &&
restrictions[4] != null)
{
nameToRestrict = restrictions[4];
}
using (MyCatDataReader reader = cmd.ExecuteReader())
{
reader.Read();
string body = reader.GetString(2);
#if NETSTANDARD1_3
reader.Dispose();
#else
reader.Close();
#endif
ParseProcedureBody(parametersTable, body, routine, nameToRestrict);
}
}
#if NETSTANDARD1_3
catch (MyCatNullValueException snex)
#else
catch (System.Data.SqlTypes.SqlNullValueException snex)
#endif
{
throw new InvalidOperationException(
String.Format(Resources.UnableToRetrieveParameters, routine["ROUTINE_NAME"]), snex);
}
}
}
public virtual MyCatSchemaCollection GetTables(string[] restrictions)
{
MyCatSchemaCollection c = new MyCatSchemaCollection("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];
}
MyCatSchemaCollection databases = GetDatabases(dbRestriction);
if (restrictions != null)
{
Array.Copy(restrictions, dbRestriction,
Math.Min(dbRestriction.Length, restrictions.Length));
}
foreach (MyCatSchemaRow row in databases.Rows)
{
dbRestriction[1] = row["SCHEMA_NAME"].ToString();
FindTables(c, dbRestriction);
}
return(c);
}
private static MyCatSchemaCollection GetDataTypes()
{
MyCatSchemaCollection dt = new MyCatSchemaCollection("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
MyCatBit.SetDSInfo(dt);
MyCatBinary.SetDSInfo(dt);
MyCatDateTime.SetDSInfo(dt);
MyCatTimeSpan.SetDSInfo(dt);
MyCatString.SetDSInfo(dt);
MyCatDouble.SetDSInfo(dt);
MyCatSingle.SetDSInfo(dt);
MyCatByte.SetDSInfo(dt);
MyCatInt16.SetDSInfo(dt);
MyCatInt32.SetDSInfo(dt);
MyCatInt64.SetDSInfo(dt);
MyCatDecimal.SetDSInfo(dt);
MyCatUByte.SetDSInfo(dt);
MyCatUInt16.SetDSInfo(dt);
MyCatUInt32.SetDSInfo(dt);
MyCatUInt64.SetDSInfo(dt);
return(dt);
}
protected override MyCatSchemaCollection GetCollections()
{
MyCatSchemaCollection dt = base.GetCollections();
object[][] collections = new object[][]
{
new object[] { "Views", 2, 3 },
new object[] { "ViewColumns", 3, 4 },
new object[] { "Procedure Parameters", 5, 1 },
new object[] { "Procedures", 4, 3 },
new object[] { "Triggers", 2, 4 }
};
FillTable(dt, collections);
return(dt);
}
public virtual MyCatSchemaCollection GetUsers(string[] restrictions)
{
StringBuilder sb = new StringBuilder("SELECT Host, User FROM mysql.user");
if (restrictions != null && restrictions.Length > 0)
{
sb.AppendFormat(CultureInfo.InvariantCulture, " WHERE User LIKE '{0}'", restrictions[0]);
}
MyCatSchemaCollection c = QueryCollection("Users", sb.ToString());
c.Columns[0].Name = "HOST";
c.Columns[1].Name = "USERNAME";
return(c);
}
public virtual MyCatSchemaCollection GetSchema(string collection, String[] restrictions)
{
if (connection.State != ConnectionState.Open)
{
throw new MyCatException("GetSchema can only be called on an open connection.");
}
collection = StringUtility.ToUpperInvariant(collection);
MyCatSchemaCollection c = GetSchemaInternal(collection, restrictions);
if (c == null)
{
throw new ArgumentException("Invalid collection name");
}
return(c);
}
protected virtual MyCatSchemaCollection 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 }
};
MyCatSchemaCollection dt = new MyCatSchemaCollection("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 MyCatSchemaCollection GetViewColumns(string[] restrictions)
{
StringBuilder where = new StringBuilder();
StringBuilder sql = new StringBuilder(
"SELECT C.* FROM information_schema.columns C");
sql.Append(" JOIN information_schema.views V ");
sql.Append("ON C.table_schema=V.table_schema AND C.table_name=V.table_name ");
if (restrictions != null && restrictions.Length >= 2 &&
restrictions[1] != null)
{
where.AppendFormat(CultureInfo.InvariantCulture, "C.table_schema='{0}' ", restrictions[1]);
}
if (restrictions != null && restrictions.Length >= 3 &&
restrictions[2] != null)
{
if (where.Length > 0)
{
where.Append("AND ");
}
where.AppendFormat(CultureInfo.InvariantCulture, "C.table_name='{0}' ", restrictions[2]);
}
if (restrictions != null && restrictions.Length == 4 &&
restrictions[3] != null)
{
if (where.Length > 0)
{
where.Append("AND ");
}
where.AppendFormat(CultureInfo.InvariantCulture, "C.column_name='{0}' ", restrictions[3]);
}
if (where.Length > 0)
{
sql.AppendFormat(CultureInfo.InvariantCulture, " WHERE {0}", where);
}
MyCatSchemaCollection dt = GetTable(sql.ToString());
dt.Name = "ViewColumns";
dt.Columns[0].Name = "VIEW_CATALOG";
dt.Columns[1].Name = "VIEW_SCHEMA";
dt.Columns[2].Name = "VIEW_NAME";
QuoteDefaultValues(dt);
return(dt);
}
public virtual MyCatSchemaCollection 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]);
}
}
MyCatSchemaCollection dt = new MyCatSchemaCollection("User-defined Functions");
dt.AddColumn("NAME", typeof(string));
dt.AddColumn("RETURN_TYPE", typeof(int));
dt.AddColumn("LIBRARY_NAME", typeof(string));
MyCatCommand cmd = new MyCatCommand(sql, connection);
try
{
using (MyCatDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MyCatSchemaRow row = dt.AddRow();
row[0] = reader.GetString(0);
row[1] = reader.GetInt32(1);
row[2] = reader.GetString(2);
}
}
}
catch (MyCatException ex)
{
if (ex.Number != (int)MyCatErrorCode.TableAccessDenied)
{
throw;
}
throw new MyCatException(Resources.UnableToEnumerateUDF, ex);
}
return(dt);
}
