public override MySqlSchemaCollection GetDatabases(string[] restrictions)
{
string[] keys = new string[1];
keys[0] = "SCHEMA_NAME";
MySqlSchemaCollection dt = Query("SCHEMATA", "", keys, restrictions);
dt.Columns[1].Name = "database_name";
dt.Name = "Databases";
return(dt);
}
private MySqlSchemaCollection GetViews(string[] restrictions)
{
string[] keys = new string[3];
keys[0] = "TABLE_CATALOG";
keys[1] = "TABLE_SCHEMA";
keys[2] = "TABLE_NAME";
MySqlSchemaCollection dt = Query("VIEWS", null, keys, restrictions);
dt.Name = "Views";
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);
}
private MySqlSchemaCollection 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";
MySqlSchemaCollection dt = Query("TRIGGERS", null, keys, restrictions);
dt.Name = "Triggers";
return(dt);
}
public override MySqlSchemaCollection GetTables(string[] restrictions)
{
string[] keys = new string[4];
keys[0] = "TABLE_CATALOG";
keys[1] = "TABLE_SCHEMA";
keys[2] = "TABLE_NAME";
keys[3] = "TABLE_TYPE";
MySqlSchemaCollection dt = Query("TABLES", "TABLE_TYPE != 'VIEW'", keys, restrictions);
dt.Name = "Tables";
return(dt);
}
/// <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);
MySqlSchemaCollection c = schemaProvider.GetSchema(collectionName, restrictions);
return(c.AsDataTable());
}
public MySqlSchemaCollection GetSchemaCollection(string collectionName, string[] restrictionValues)
{
if (collectionName == null)
{
collectionName = SchemaProvider.MetaCollection;
}
string[] restrictions = schemaProvider.CleanRestrictions(restrictionValues);
MySqlSchemaCollection c = schemaProvider.GetSchema(collectionName, restrictions);
return(c);
}
public override MySqlSchemaCollection GetColumns(string[] restrictions)
{
string[] keys = new string[4];
keys[0] = "TABLE_CATALOG";
keys[1] = "TABLE_SCHEMA";
keys[2] = "TABLE_NAME";
keys[3] = "COLUMN_NAME";
MySqlSchemaCollection dt = Query("COLUMNS", null, keys, restrictions);
dt.RemoveColumn("CHARACTER_OCTET_LENGTH");
dt.Name = "Columns";
QuoteDefaultValues(dt);
return(dt);
}
protected override MySqlSchemaCollection GetCollections()
{
MySqlSchemaCollection 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);
}
internal void GetParametersFromShowCreate(MySqlSchemaCollection parametersTable,
string[] restrictions, MySqlSchemaCollection 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);
}
MySqlCommand cmd = connection.CreateCommand();
foreach (MySqlSchemaRow 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 (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
string body = reader.GetString(2);
reader.Close();
ParseProcedureBody(parametersTable, body, routine, nameToRestrict);
}
}
#if RT
catch (MySqlNullValueException snex)
#else
catch (System.Data.SqlTypes.SqlNullValueException snex)
#endif
{
throw new InvalidOperationException(
String.Format(MySqlResources.UnableToRetrieveParameters, routine["ROUTINE_NAME"]), snex);
}
}
}
private MySqlSchemaCollection 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);
}
MySqlSchemaCollection 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);
}
private static ProcedureCacheEntry GetProcData(MySqlConnection connection, string spName)
{
string schema = String.Empty;
string name = spName;
int dotIndex = spName.IndexOf(".");
if (dotIndex != -1)
{
schema = spName.Substring(0, dotIndex);
name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1);
}
string[] restrictions = new string[4];
restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase();
restrictions[2] = name;
MySqlSchemaCollection proc = connection.GetSchemaCollection("procedures", restrictions);
if (proc.Rows.Count > 1)
{
throw new MySqlException(MySqlResources.ProcAndFuncSameName);
}
if (proc.Rows.Count == 0)
{
throw new MySqlException(String.Format(MySqlResources.InvalidProcName, name, schema));
}
ProcedureCacheEntry entry = new ProcedureCacheEntry();
entry.procedure = proc;
// we don't use GetSchema here because that would cause another
// query of procedures and we don't need that since we already
// know the procedure we care about.
ISSchemaProvider isp = new ISSchemaProvider(connection);
string[] rest = isp.CleanRestrictions(restrictions);
MySqlSchemaCollection parameters = isp.GetProcedureParameters(rest, proc);
entry.parameters = parameters;
return(entry);
}
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);
}
/// <summary>
/// Return schema information about parameters for procedures and functions
/// Restrictions supported are:
/// schema, name, type, parameter name
/// </summary>
public virtual MySqlSchemaCollection GetProcedureParameters(string[] restrictions,
MySqlSchemaCollection routines)
{
bool is55 = connection.driver.Version.isAtLeast(5, 5, 3);
try
{
// we want to avoid using IS if we can as it is painfully slow
MySqlSchemaCollection dt = CreateParametersTable();
GetParametersFromShowCreate(dt, restrictions, routines);
return(dt);
}
catch (Exception)
{
if (!is55)
{
throw;
}
// we get here by not having access and we are on 5.5 or later so just use IS
return(GetParametersFromIS(restrictions, routines));
}
}
public MySqlSchemaRow(MySqlSchemaCollection c)
{
Collection = c;
InitMetadata();
}
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);
}
}
