internal static void SetDSInfo(MySqlSchemaCollection sc) { MySqlSchemaRow row = sc.AddRow(); row["TypeName"] = "JSON"; row["ProviderDbType"] = MySqlDbType.JSON; row["ColumnSize"] = 0; row["CreateFormat"] = "JSON"; row["CreateParameters"] = 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; }
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"] = "SMALLINT"; row["ProviderDbType"] = MySqlDbType.UInt16; row["ColumnSize"] = 0; row["CreateFormat"] = "SMALLINT UNSIGNED"; row["CreateParameters"] = null; row["DataType"] = "System.UInt16"; 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; }
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 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); } }
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); }
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]; } } }
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); }
/// <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 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); }
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); }
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); }
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); } }
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 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 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); } } }
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); }
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); #if NETSTANDARD1_3 reader.Dispose(); #else reader.Close(); #endif ParseProcedureBody(parametersTable, body, routine, nameToRestrict); } } #if NETSTANDARD1_3 catch (MySqlNullValueException snex) #else catch (System.Data.SqlTypes.SqlNullValueException snex) #endif { throw new InvalidOperationException( String.Format(Resources.UnableToRetrieveParameters, routine["ROUTINE_NAME"]), snex); } } }
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); }
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 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]); } MySqlSchemaCollection c = QueryCollection("Users", sb.ToString()); c.Columns[0].Name = "HOST"; c.Columns[1].Name = "USERNAME"; return(c); }
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); }
public virtual MySqlSchemaCollection GetSchema(string collection, String[] restrictions) { if (connection.State != ConnectionState.Open) { throw new MySqlException("GetSchema can only be called on an open connection."); } collection = StringUtility.ToUpperInvariant(collection); MySqlSchemaCollection c = GetSchemaInternal(collection, restrictions); if (c == null) { throw new ArgumentException("Invalid collection name"); } 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 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); }
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); }
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(Resources.ProcAndFuncSameName); } if (proc.Rows.Count == 0) { throw new MySqlException(String.Format(Resources.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); }
/// <summary> /// GetForeignKeysOnTable retrieves the foreign keys on the given table. /// Since MySQL supports foreign keys on versions prior to 5.0, we can't use /// information schema. MySQL also does not include any type of SHOW command /// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing /// the output. /// </summary> /// <param name="fkTable">The table to store the key info in.</param> /// <param name="tableToParse">The table to get the foeign key info for.</param> /// <param name="filterName">Only get foreign keys that match this name.</param> /// <param name="includeColumns">Should column information be included in the table.</param> private void GetForeignKeysOnTable(MySqlSchemaCollection fkTable, MySqlSchemaRow tableToParse, string filterName, bool includeColumns) { string sqlMode = GetSqlMode(); if (filterName != null) { filterName = StringUtility.ToLowerInvariant(filterName); } string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`", tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]); string lowerBody = null, body = null; MySqlCommand cmd = new MySqlCommand(sql, connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); body = reader.GetString(1); lowerBody = StringUtility.ToLowerInvariant(body); } MySqlTokenizer tokenizer = new MySqlTokenizer(lowerBody); tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1; while (true) { string token = tokenizer.NextToken(); // look for a starting contraint while (token != null && (token != "constraint" || tokenizer.Quoted)) { token = tokenizer.NextToken(); } if (token == null) { break; } ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns); } }
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); } } }
protected void QuoteDefaultValues(MySqlSchemaCollection schemaCollection) { if (schemaCollection == null) { return; } if (!schemaCollection.ContainsColumn("COLUMN_DEFAULT")) { return; } foreach (MySqlSchemaRow row in schemaCollection.Rows) { object defaultValue = row["COLUMN_DEFAULT"]; if (MetaData.IsTextType(row["DATA_TYPE"].ToString())) { row["COLUMN_DEFAULT"] = String.Format("{0}", defaultValue); } } }
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 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 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 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; }
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; }
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); } } }
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; }
/// <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); } }
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 void QuoteDefaultValues(MySqlSchemaCollection schemaCollection) { if (schemaCollection == null) return; if (!schemaCollection.ContainsColumn("COLUMN_DEFAULT")) return; foreach (MySqlSchemaRow row in schemaCollection.Rows) { object defaultValue = row["COLUMN_DEFAULT"]; if (MetaData.IsTextType(row["DATA_TYPE"].ToString())) row["COLUMN_DEFAULT"] = String.Format("{0}", defaultValue); } }
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; }
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); #if NETSTANDARD1_6 reader.Dispose(); #else reader.Close(); #endif ParseProcedureBody(parametersTable, body, routine, nameToRestrict); } } #if NETSTANDARD1_6 catch (MySqlNullValueException snex) #else catch (System.Data.SqlTypes.SqlNullValueException snex) #endif { throw new InvalidOperationException( String.Format(Resources.UnableToRetrieveParameters, routine["ROUTINE_NAME"]), snex); } } }
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; } }
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 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); }
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]; } }
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 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; }
/// <summary> /// GetForeignKeysOnTable retrieves the foreign keys on the given table. /// Since MySQL supports foreign keys on versions prior to 5.0, we can't use /// information schema. MySQL also does not include any type of SHOW command /// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing /// the output. /// </summary> /// <param name="fkTable">The table to store the key info in.</param> /// <param name="tableToParse">The table to get the foeign key info for.</param> /// <param name="filterName">Only get foreign keys that match this name.</param> /// <param name="includeColumns">Should column information be included in the table.</param> private void GetForeignKeysOnTable(MySqlSchemaCollection fkTable, MySqlSchemaRow tableToParse, string filterName, bool includeColumns) { string sqlMode = GetSqlMode(); if (filterName != null) filterName = StringUtility.ToLowerInvariant(filterName); string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`", tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]); string lowerBody = null, body = null; MySqlCommand cmd = new MySqlCommand(sql, connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); body = reader.GetString(1); lowerBody = StringUtility.ToLowerInvariant(body); } MySqlTokenizer tokenizer = new MySqlTokenizer(lowerBody); tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1; while (true) { string token = tokenizer.NextToken(); // look for a starting contraint while (token != null && (token != "constraint" || tokenizer.Quoted)) token = tokenizer.NextToken(); if (token == null) break; ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns); } }
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 GetIndexColumns(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("IndexColumns"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("COLUMN_NAME", typeof(string)); dt.AddColumn("ORDINAL_POSITION", typeof(int)); dt.AddColumn("SORT_ORDER", typeof(string)); int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) restrictions.CopyTo(tableRestrictions, 0); tableRestrictions[3] = "BASE TABLE"; MySqlSchemaCollection tables = GetTables(tableRestrictions); foreach (MySqlSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", table["TABLE_SCHEMA"], table["TABLE_NAME"]); MySqlCommand cmd = new MySqlCommand(sql, connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME")); string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME")); if (restrictions != null) { if (restrictions.Length >= 4 && restrictions[3] != null && key_name != restrictions[3]) continue; if (restrictions.Length >= 5 && restrictions[4] != null && col_name != restrictions[4]) continue; } MySqlSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = key_name; row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE")); row["COLUMN_NAME"] = col_name; row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX")); row["SORT_ORDER"] = reader.GetString("COLLATION"); } } } return dt; }
private 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 }
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 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[] { "MEDIUMINT", "INT" }; MySqlDbType[] dbtype = new MySqlDbType[] { MySqlDbType.UInt24, MySqlDbType.UInt32 }; // 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] + " UNSIGNED"; row["CreateParameters"] = null; row["DataType"] = "System.UInt32"; 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; } }
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; }