private static MySqlSchemaCollection GetReservedWords() { MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords"); #if !RT dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string)); Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream( "MySql.Data.MySqlClient.Properties.ReservedWords.txt"); #else dt.AddColumn("ReservedWord", typeof(string)); Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("MySql.Data.MySqlClient.Properties.ReservedWords.txt"); #endif StreamReader sr = new StreamReader(str); string line = sr.ReadLine(); while (line != null) { string[] keywords = line.Split(new char[] { ' ' }); foreach (string s in keywords) { if (String.IsNullOrEmpty(s)) { continue; } MySqlSchemaRow row = dt.AddRow(); row[0] = s; } line = sr.ReadLine(); } sr.Dispose(); str.Close(); return(dt); }
public virtual MySqlSchemaCollection GetDatabases(string[] restrictions) { Regex regex = null; int num = int.Parse(this.connection.driver.Property("lower_case_table_names")); string sql = "SHOW DATABASES"; if (((num == 0) && (restrictions != null)) && (restrictions.Length >= 1)) { sql = sql + " LIKE '" + restrictions[0] + "'"; } MySqlSchemaCollection schemas = this.QueryCollection("Databases", sql); if (((num != 0) && (restrictions != null)) && ((restrictions.Length >= 1) && (restrictions[0] != null))) { regex = new Regex(restrictions[0], RegexOptions.IgnoreCase); } MySqlSchemaCollection schemas2 = new MySqlSchemaCollection("Databases"); schemas2.AddColumn("CATALOG_NAME", typeof(string)); schemas2.AddColumn("SCHEMA_NAME", typeof(string)); foreach (MySqlSchemaRow row in schemas.Rows) { if ((regex == null) || regex.Match(row[0].ToString()).Success) { schemas2.AddRow()[1] = row[0]; } } return(schemas2); }
protected virtual MySqlSchemaCollection GetCollections() { object[][] collections = { 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 static MySqlSchemaCollection GetReservedWords() { MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords"); #if !NETSTANDARD1_3 dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string)); Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream( "MySql.Data.Properties.ReservedWords.txt"); #else dt.AddColumn("ReservedWord", typeof(string)); Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("MySql.Data.Properties.ReservedWords.txt"); #endif StreamReader sr = new StreamReader(str); string line = sr.ReadLine(); while (line != null) { MySqlSchemaRow row = dt.AddRow(); row[0] = line; line = sr.ReadLine(); } sr.Dispose(); return(dt); }
protected virtual MySqlSchemaCollection GetRestrictions() { object[][] data = 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, data); return(dt); }
public virtual MySqlSchemaCollection GetIndexes(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("Indexes"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("UNIQUE", typeof(bool)); dt.AddColumn("PRIMARY", typeof(bool)); dt.AddColumn("TYPE", typeof(string)); dt.AddColumn("COMMENT", typeof(string)); // Get the list of tables first int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) { restrictions.CopyTo(tableRestrictions, 0); } tableRestrictions[3] = "BASE TABLE"; MySqlSchemaCollection tables = GetTables(tableRestrictions); foreach (MySqlSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", MySqlHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]), MySqlHelper.DoubleQuoteString((string)table["TABLE_NAME"])); MySqlSchemaCollection indexes = QueryCollection("indexes", sql); foreach (MySqlSchemaRow index in indexes.Rows) { long seq_index = (long)index["SEQ_IN_INDEX"]; if (seq_index != 1) { continue; } if (restrictions != null && restrictions.Length == 4 && restrictions[3] != null && !index["KEY_NAME"].Equals(restrictions[3])) { continue; } MySqlSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = index["KEY_NAME"]; row["TABLE_NAME"] = index["TABLE"]; row["UNIQUE"] = (long)index["NON_UNIQUE"] == 0; row["PRIMARY"] = index["KEY_NAME"].Equals("PRIMARY"); row["TYPE"] = index["INDEX_TYPE"]; row["COMMENT"] = index["COMMENT"]; } } return(dt); }
private MySqlSchemaCollection GetTable(string sql) { MySqlSchemaCollection c = new MySqlSchemaCollection(); MySqlCommand cmd = new MySqlCommand(sql, connection); MySqlDataReader reader = cmd.ExecuteReader(); // add columns for (int i = 0; i < reader.FieldCount; i++) { c.AddColumn(reader.GetName(i), reader.GetFieldType(i)); } using (reader) { while (reader.Read()) { MySqlSchemaRow row = c.AddRow(); for (int i = 0; i < reader.FieldCount; i++) { row[i] = reader.GetValue(i); } } } return(c); }
public virtual MySqlSchemaCollection GetUDF(string[] restrictions) { string sql = "SELECT name,ret,dl FROM mysql.func"; if (restrictions != null) { if (restrictions.Length >= 1 && !String.IsNullOrEmpty(restrictions[0])) { sql += String.Format(" WHERE name LIKE '{0}'", restrictions[0]); } } MySqlSchemaCollection dt = new MySqlSchemaCollection("User-defined Functions"); dt.AddColumn("NAME", typeof(string)); dt.AddColumn("RETURN_TYPE", typeof(int)); dt.AddColumn("LIBRARY_NAME", typeof(string)); MySqlCommand cmd = new MySqlCommand(sql, connection); try { using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MySqlSchemaRow row = dt.AddRow(); row[0] = reader.GetString(0); row[1] = reader.GetInt32(1); row[2] = reader.GetString(2); } } } catch (MySqlException ex) { if (ex.Number != (int)MySqlErrorCode.TableAccessDenied) { throw; } throw new MySqlException(Resources.UnableToEnumerateUDF, ex); } return(dt); }
private MySqlSchemaCollection GetProceduresWithParameters(string[] restrictions) { MySqlSchemaCollection dt = GetProcedures(restrictions); dt.AddColumn("ParameterList", typeof(string)); foreach (MySqlSchemaRow row in dt.Rows) { row["ParameterList"] = GetProcedureParameterLine(row); } return dt; }
private MySqlSchemaCollection GetProceduresWithParameters(string[] restrictions) { MySqlSchemaCollection procedures = this.GetProcedures(restrictions); procedures.AddColumn("ParameterList", typeof(string)); foreach (MySqlSchemaRow current in procedures.Rows) { current["ParameterList"] = this.GetProcedureParameterLine(current); } return(procedures); }
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 GetUDF(string[] restrictions) { string cmdText = "SELECT name,ret,dl FROM mysql.func"; if (((restrictions != null) && (restrictions.Length >= 1)) && !string.IsNullOrEmpty(restrictions[0])) { cmdText = cmdText + string.Format(" WHERE name LIKE '{0}'", restrictions[0]); } MySqlSchemaCollection schemas = new MySqlSchemaCollection("User-defined Functions"); schemas.AddColumn("NAME", typeof(string)); schemas.AddColumn("RETURN_TYPE", typeof(int)); schemas.AddColumn("LIBRARY_NAME", typeof(string)); MySqlCommand command = new MySqlCommand(cmdText, this.connection); try { using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { MySqlSchemaRow row = schemas.AddRow(); row[0] = reader.GetString(0); row[1] = reader.GetInt32(1); row[2] = reader.GetString(2); } } } catch (MySqlException exception) { if (exception.Number != 0x476) { throw; } throw new MySqlException(Resources.UnableToEnumerateUDF, exception); } return(schemas); }
public virtual MySqlSchemaCollection GetIndexes(string[] restrictions) { MySqlSchemaCollection schemas = new MySqlSchemaCollection("Indexes"); schemas.AddColumn("INDEX_CATALOG", typeof(string)); schemas.AddColumn("INDEX_SCHEMA", typeof(string)); schemas.AddColumn("INDEX_NAME", typeof(string)); schemas.AddColumn("TABLE_NAME", typeof(string)); schemas.AddColumn("UNIQUE", typeof(bool)); schemas.AddColumn("PRIMARY", typeof(bool)); schemas.AddColumn("TYPE", typeof(string)); schemas.AddColumn("COMMENT", typeof(string)); int num = (restrictions == null) ? 4 : restrictions.Length; string[] array = new string[Math.Max(num, 4)]; if (restrictions != null) { restrictions.CopyTo(array, 0); } array[3] = "BASE TABLE"; foreach (MySqlSchemaRow row in this.GetTables(array).Rows) { string sql = string.Format("SHOW INDEX FROM `{0}`.`{1}`", MySqlHelper.DoubleQuoteString((string)row["TABLE_SCHEMA"]), MySqlHelper.DoubleQuoteString((string)row["TABLE_NAME"])); foreach (MySqlSchemaRow row2 in this.QueryCollection("indexes", sql).Rows) { long num2 = (long)row2["SEQ_IN_INDEX"]; if ((num2 == 1L) && (((restrictions == null) || (restrictions.Length != 4)) || ((restrictions[3] == null) || row2["KEY_NAME"].Equals(restrictions[3])))) { MySqlSchemaRow row3 = schemas.AddRow(); row3["INDEX_CATALOG"] = null; row3["INDEX_SCHEMA"] = row["TABLE_SCHEMA"]; row3["INDEX_NAME"] = row2["KEY_NAME"]; row3["TABLE_NAME"] = row2["TABLE"]; row3["UNIQUE"] = ((long)row2["NON_UNIQUE"]) == 0L; row3["PRIMARY"] = row2["KEY_NAME"].Equals("PRIMARY"); row3["TYPE"] = row2["INDEX_TYPE"]; row3["COMMENT"] = row2["COMMENT"]; } } } return(schemas); }
public virtual MySqlSchemaCollection GetIndexColumns(string[] restrictions) { MySqlSchemaCollection schemas = new MySqlSchemaCollection("IndexColumns"); schemas.AddColumn("INDEX_CATALOG", typeof(string)); schemas.AddColumn("INDEX_SCHEMA", typeof(string)); schemas.AddColumn("INDEX_NAME", typeof(string)); schemas.AddColumn("TABLE_NAME", typeof(string)); schemas.AddColumn("COLUMN_NAME", typeof(string)); schemas.AddColumn("ORDINAL_POSITION", typeof(int)); schemas.AddColumn("SORT_ORDER", typeof(string)); int num = (restrictions == null) ? 4 : restrictions.Length; string[] array = new string[Math.Max(num, 4)]; if (restrictions != null) { restrictions.CopyTo(array, 0); } array[3] = "BASE TABLE"; foreach (MySqlSchemaRow row in this.GetTables(array).Rows) { MySqlCommand command = new MySqlCommand(string.Format("SHOW INDEX FROM `{0}`.`{1}`", row["TABLE_SCHEMA"], row["TABLE_NAME"]), this.connection); using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string str2 = GetString(reader, reader.GetOrdinal("KEY_NAME")); string str3 = GetString(reader, reader.GetOrdinal("COLUMN_NAME")); if ((restrictions == null) || ((((restrictions.Length < 4) || (restrictions[3] == null)) || (str2 == restrictions[3])) && (((restrictions.Length < 5) || (restrictions[4] == null)) || (str3 == restrictions[4])))) { MySqlSchemaRow row2 = schemas.AddRow(); row2["INDEX_CATALOG"] = null; row2["INDEX_SCHEMA"] = row["TABLE_SCHEMA"]; row2["INDEX_NAME"] = str2; row2["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE")); row2["COLUMN_NAME"] = str3; row2["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX")); row2["SORT_ORDER"] = reader.GetString("COLLATION"); } } } } return(schemas); }
protected MySqlSchemaCollection QueryCollection(string name, string sql) { MySqlSchemaCollection schemas = new MySqlSchemaCollection(name); MySqlDataReader reader = new MySqlCommand(sql, this.connection).ExecuteReader(); for (int i = 0; i < reader.FieldCount; i++) { schemas.AddColumn(reader.GetName(i), reader.GetFieldType(i)); } using (reader) { while (reader.Read()) { MySqlSchemaRow row = schemas.AddRow(); for (int j = 0; j < reader.FieldCount; j++) { row[j] = reader.GetValue(j); } } } return(schemas); }
private static MySqlSchemaCollection GetReservedWords() { MySqlSchemaCollection schemas = new MySqlSchemaCollection("ReservedWords"); schemas.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string)); Stream manifestResourceStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MySql.Data.MySqlClient.Properties.ReservedWords.txt"); StreamReader reader = new StreamReader(manifestResourceStream); for (string str = reader.ReadLine(); str != null; str = reader.ReadLine()) { foreach (string str2 in str.Split(new char[] { ' ' })) { if (!string.IsNullOrEmpty(str2)) { schemas.AddRow()[0] = str2; } } } reader.Dispose(); manifestResourceStream.Close(); return(schemas); }
private MySqlSchemaCollection GetTable(string sql) { MySqlSchemaCollection mySqlSchemaCollection = new MySqlSchemaCollection(); MySqlDataReader mySqlDataReader = new MySqlCommand(sql, this.connection).ExecuteReader(); for (int i = 0; i < mySqlDataReader.FieldCount; i++) { mySqlSchemaCollection.AddColumn(mySqlDataReader.GetName(i), mySqlDataReader.GetFieldType(i)); } using (mySqlDataReader) { while (mySqlDataReader.Read()) { MySqlSchemaRow mySqlSchemaRow = mySqlSchemaCollection.AddRow(); for (int j = 0; j < mySqlDataReader.FieldCount; j++) { mySqlSchemaRow[j] = mySqlDataReader.GetValue(j); } } } return(mySqlSchemaCollection); }
public virtual MySqlSchemaCollection GetForeignKeys(string[] restrictions) { MySqlSchemaCollection fkTable = new MySqlSchemaCollection("Foreign Keys"); fkTable.AddColumn("CONSTRAINT_CATALOG", typeof(string)); fkTable.AddColumn("CONSTRAINT_SCHEMA", typeof(string)); fkTable.AddColumn("CONSTRAINT_NAME", typeof(string)); fkTable.AddColumn("TABLE_CATALOG", typeof(string)); fkTable.AddColumn("TABLE_SCHEMA", typeof(string)); fkTable.AddColumn("TABLE_NAME", typeof(string)); fkTable.AddColumn("MATCH_OPTION", typeof(string)); fkTable.AddColumn("UPDATE_RULE", typeof(string)); fkTable.AddColumn("DELETE_RULE", typeof(string)); fkTable.AddColumn("REFERENCED_TABLE_CATALOG", typeof(string)); fkTable.AddColumn("REFERENCED_TABLE_SCHEMA", typeof(string)); fkTable.AddColumn("REFERENCED_TABLE_NAME", typeof(string)); string filterName = null; if ((restrictions != null) && (restrictions.Length >= 4)) { filterName = restrictions[3]; restrictions[3] = null; } foreach (MySqlSchemaRow row in this.GetTables(restrictions).Rows) { this.GetForeignKeysOnTable(fkTable, row, filterName, false); } return(fkTable); }
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 GetForeignKeys(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("MATCH_OPTION", typeof(string)); dt.AddColumn("UPDATE_RULE", typeof(string)); dt.AddColumn("DELETE_RULE", typeof(string)); dt.AddColumn("REFERENCED_TABLE_CATALOG", typeof(string)); dt.AddColumn("REFERENCED_TABLE_SCHEMA", typeof(string)); dt.AddColumn("REFERENCED_TABLE_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, false); return dt; }
public virtual MySqlSchemaCollection GetProcedures(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedures"); dt.AddColumn("SPECIFIC_NAME", typeof(string)); dt.AddColumn("ROUTINE_CATALOG", typeof(string)); dt.AddColumn("ROUTINE_SCHEMA", typeof(string)); dt.AddColumn("ROUTINE_NAME", typeof(string)); dt.AddColumn("ROUTINE_TYPE", typeof(string)); dt.AddColumn("DTD_IDENTIFIER", typeof(string)); dt.AddColumn("ROUTINE_BODY", typeof(string)); dt.AddColumn("ROUTINE_DEFINITION", typeof(string)); dt.AddColumn("EXTERNAL_NAME", typeof(string)); dt.AddColumn("EXTERNAL_LANGUAGE", typeof(string)); dt.AddColumn("PARAMETER_STYLE", typeof(string)); dt.AddColumn("IS_DETERMINISTIC", typeof(string)); dt.AddColumn("SQL_DATA_ACCESS", typeof(string)); dt.AddColumn("SQL_PATH", typeof(string)); dt.AddColumn("SECURITY_TYPE", typeof(string)); dt.AddColumn("CREATED", typeof(DateTime)); dt.AddColumn("LAST_ALTERED", typeof(DateTime)); dt.AddColumn("SQL_MODE", typeof(string)); dt.AddColumn("ROUTINE_COMMENT", typeof(string)); dt.AddColumn("DEFINER", typeof(string)); StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1"); if (restrictions != null) { if (restrictions.Length >= 2 && restrictions[1] != null) sql.AppendFormat(CultureInfo.InvariantCulture, " AND db LIKE '{0}'", restrictions[1]); if (restrictions.Length >= 3 && restrictions[2] != null) sql.AppendFormat(CultureInfo.InvariantCulture, " AND name LIKE '{0}'", restrictions[2]); if (restrictions.Length >= 4 && restrictions[3] != null) sql.AppendFormat(CultureInfo.InvariantCulture, " AND type LIKE '{0}'", restrictions[3]); } MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MySqlSchemaRow row = dt.AddRow(); row["SPECIFIC_NAME"] = reader.GetString("specific_name"); row["ROUTINE_CATALOG"] = DBNull.Value; row["ROUTINE_SCHEMA"] = reader.GetString("db"); row["ROUTINE_NAME"] = reader.GetString("name"); string routineType = reader.GetString("type"); row["ROUTINE_TYPE"] = routineType; row["DTD_IDENTIFIER"] = StringUtility.ToLowerInvariant(routineType) == "function" ? (object)reader.GetString("returns") : DBNull.Value; row["ROUTINE_BODY"] = "SQL"; row["ROUTINE_DEFINITION"] = reader.GetString("body"); row["EXTERNAL_NAME"] = DBNull.Value; row["EXTERNAL_LANGUAGE"] = DBNull.Value; row["PARAMETER_STYLE"] = "SQL"; row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic"); row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access"); row["SQL_PATH"] = DBNull.Value; row["SECURITY_TYPE"] = reader.GetString("security_type"); row["CREATED"] = reader.GetDateTime("created"); row["LAST_ALTERED"] = reader.GetDateTime("modified"); row["SQL_MODE"] = reader.GetString("sql_mode"); row["ROUTINE_COMMENT"] = reader.GetString("comment"); row["DEFINER"] = reader.GetString("definer"); } } return dt; }
public virtual MySqlSchemaCollection GetIndexColumns(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("IndexColumns"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("COLUMN_NAME", typeof(string)); dt.AddColumn("ORDINAL_POSITION", typeof(int)); dt.AddColumn("SORT_ORDER", typeof(string)); int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) { restrictions.CopyTo(tableRestrictions, 0); } tableRestrictions[3] = "BASE TABLE"; MySqlSchemaCollection tables = GetTables(tableRestrictions); foreach (MySqlSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", table["TABLE_SCHEMA"], table["TABLE_NAME"]); MySqlCommand cmd = new MySqlCommand(sql, connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME")); string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME")); if (restrictions != null) { if (restrictions.Length >= 4 && restrictions[3] != null && key_name != restrictions[3]) { continue; } if (restrictions.Length >= 5 && restrictions[4] != null && col_name != restrictions[4]) { continue; } } MySqlSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = key_name; row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE")); row["COLUMN_NAME"] = col_name; row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX")); row["SORT_ORDER"] = reader.GetString("COLLATION"); } } } return(dt); }
internal MySqlSchemaCollection CreateParametersTable() { MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedure Parameters"); dt.AddColumn("SPECIFIC_CATALOG", typeof(string)); dt.AddColumn("SPECIFIC_SCHEMA", typeof(string)); dt.AddColumn("SPECIFIC_NAME", typeof(string)); dt.AddColumn("ORDINAL_POSITION", typeof(Int32)); dt.AddColumn("PARAMETER_MODE", typeof(string)); dt.AddColumn("PARAMETER_NAME", typeof(string)); dt.AddColumn("DATA_TYPE", typeof(string)); dt.AddColumn("CHARACTER_MAXIMUM_LENGTH", typeof(Int32)); dt.AddColumn("CHARACTER_OCTET_LENGTH", typeof(Int32)); dt.AddColumn("NUMERIC_PRECISION", typeof(byte)); dt.AddColumn("NUMERIC_SCALE", typeof(Int32)); dt.AddColumn("CHARACTER_SET_NAME", typeof(string)); dt.AddColumn("COLLATION_NAME", typeof(string)); dt.AddColumn("DTD_IDENTIFIER", typeof(string)); dt.AddColumn("ROUTINE_TYPE", typeof(string)); return dt; }
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 schema = new MySqlSchemaCollection("Tables"); schema.AddColumn("TABLE_CATALOG", typeof(string)); schema.AddColumn("TABLE_SCHEMA", typeof(string)); schema.AddColumn("TABLE_NAME", typeof(string)); schema.AddColumn("TABLE_TYPE", typeof(string)); schema.AddColumn("ENGINE", typeof(string)); schema.AddColumn("VERSION", typeof(ulong)); schema.AddColumn("ROW_FORMAT", typeof(string)); schema.AddColumn("TABLE_ROWS", typeof(ulong)); schema.AddColumn("AVG_ROW_LENGTH", typeof(ulong)); schema.AddColumn("DATA_LENGTH", typeof(ulong)); schema.AddColumn("MAX_DATA_LENGTH", typeof(ulong)); schema.AddColumn("INDEX_LENGTH", typeof(ulong)); schema.AddColumn("DATA_FREE", typeof(ulong)); schema.AddColumn("AUTO_INCREMENT", typeof(ulong)); schema.AddColumn("CREATE_TIME", typeof(DateTime)); schema.AddColumn("UPDATE_TIME", typeof(DateTime)); schema.AddColumn("CHECK_TIME", typeof(DateTime)); schema.AddColumn("TABLE_COLLATION", typeof(string)); schema.AddColumn("CHECKSUM", typeof(ulong)); schema.AddColumn("CREATE_OPTIONS", typeof(string)); schema.AddColumn("TABLE_COMMENT", typeof(string)); string[] strArray = new string[4]; if ((restrictions != null) && (restrictions.Length >= 2)) { strArray[0] = restrictions[1]; } MySqlSchemaCollection databases = this.GetDatabases(strArray); if (restrictions != null) { Array.Copy(restrictions, strArray, Math.Min(strArray.Length, restrictions.Length)); } foreach (MySqlSchemaRow row in databases.Rows) { strArray[1] = row["SCHEMA_NAME"].ToString(); this.FindTables(schema, strArray); } return(schema); }
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); }
private MySqlSchemaCollection GetDataSourceInformation() { MySqlSchemaCollection schemas = new MySqlSchemaCollection("DataSourceInformation"); schemas.AddColumn("CompositeIdentifierSeparatorPattern", typeof(string)); schemas.AddColumn("DataSourceProductName", typeof(string)); schemas.AddColumn("DataSourceProductVersion", typeof(string)); schemas.AddColumn("DataSourceProductVersionNormalized", typeof(string)); schemas.AddColumn("GroupByBehavior", typeof(GroupByBehavior)); schemas.AddColumn("IdentifierPattern", typeof(string)); schemas.AddColumn("IdentifierCase", typeof(IdentifierCase)); schemas.AddColumn("OrderByColumnsInSelect", typeof(bool)); schemas.AddColumn("ParameterMarkerFormat", typeof(string)); schemas.AddColumn("ParameterMarkerPattern", typeof(string)); schemas.AddColumn("ParameterNameMaxLength", typeof(int)); schemas.AddColumn("ParameterNamePattern", typeof(string)); schemas.AddColumn("QuotedIdentifierPattern", typeof(string)); schemas.AddColumn("QuotedIdentifierCase", typeof(IdentifierCase)); schemas.AddColumn("StatementSeparatorPattern", typeof(string)); schemas.AddColumn("StringLiteralPattern", typeof(string)); schemas.AddColumn("SupportedJoinOperators", typeof(SupportedJoinOperators)); DBVersion version = this.connection.driver.Version; string str = string.Format("{0:0}.{1:0}.{2:0}", version.Major, version.Minor, version.Build); MySqlSchemaRow item = schemas.AddRow(); item["CompositeIdentifierSeparatorPattern"] = @"\."; item["DataSourceProductName"] = "MySQL"; item["DataSourceProductVersion"] = this.connection.ServerVersion; item["DataSourceProductVersionNormalized"] = str; item["GroupByBehavior"] = GroupByBehavior.Unrelated; item["IdentifierPattern"] = "(^\\`\\p{Lo}\\p{Lu}\\p{Ll}_@#][\\p{Lo}\\p{Lu}\\p{Ll}\\p{Nd}@$#_]*$)|(^\\`[^\\`\\0]|\\`\\`+\\`$)|(^\\\" + [^\\\"\\0]|\\\"\\\"+\\\"$)"; item["IdentifierCase"] = IdentifierCase.Insensitive; item["OrderByColumnsInSelect"] = false; item["ParameterMarkerFormat"] = "{0}"; item["ParameterMarkerPattern"] = "(@[A-Za-z0-9_$#]*)"; item["ParameterNameMaxLength"] = 0x80; item["ParameterNamePattern"] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)"; item["QuotedIdentifierPattern"] = @"(([^\`]|\`\`)*)"; item["QuotedIdentifierCase"] = IdentifierCase.Sensitive; item["StatementSeparatorPattern"] = ";"; item["StringLiteralPattern"] = "'(([^']|'')*)'"; item["SupportedJoinOperators"] = 15; schemas.Rows.Add(item); return(schemas); }
protected virtual MySqlSchemaCollection GetCollections() { object[][] collections = new object[][] { new object[] {"MetaDataCollections", 0, 0}, new object[] {"DataSourceInformation", 0, 0}, new object[] {"DataTypes", 0, 0}, new object[] {"Restrictions", 0, 0}, new object[] {"ReservedWords", 0, 0}, new object[] {"Databases", 1, 1}, new object[] {"Tables", 4, 2}, new object[] {"Columns", 4, 4}, new object[] {"Users", 1, 1}, new object[] {"Foreign Keys", 4, 3}, new object[] {"IndexColumns", 5, 4}, new object[] {"Indexes", 4, 3}, new object[] {"Foreign Key Columns", 4, 3}, new object[] {"UDF", 1, 1} }; MySqlSchemaCollection dt = new MySqlSchemaCollection("MetaDataCollections"); dt.AddColumn("CollectionName", typeof(string)); dt.AddColumn("NumberOfRestrictions", typeof(int)); dt.AddColumn("NumberOfIdentifierParts", typeof(int)); FillTable(dt, collections); return dt; }
public virtual MySqlSchemaCollection GetUDF(string[] restrictions) { string sql = "SELECT name,ret,dl FROM mysql.func"; if (restrictions != null) { if (restrictions.Length >= 1 && !String.IsNullOrEmpty(restrictions[0])) sql += String.Format(" WHERE name LIKE '{0}'", restrictions[0]); } MySqlSchemaCollection dt = new MySqlSchemaCollection("User-defined Functions"); dt.AddColumn("NAME", typeof(string)); dt.AddColumn("RETURN_TYPE", typeof(int)); dt.AddColumn("LIBRARY_NAME", typeof(string)); MySqlCommand cmd = new MySqlCommand(sql, connection); try { using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MySqlSchemaRow row = dt.AddRow(); row[0] = reader.GetString(0); row[1] = reader.GetInt32(1); row[2] = reader.GetString(2); } } } catch (MySqlException ex) { if (ex.Number != (int)MySqlErrorCode.TableAccessDenied) throw; throw new MySqlException(Resources.UnableToEnumerateUDF, ex); } return dt; }
public virtual MySqlSchemaCollection 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 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); }
private static MySqlSchemaCollection GetReservedWords() { MySqlSchemaCollection dt = new MySqlSchemaCollection("ReservedWords"); #if !RT dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string)); Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream( "MySql.Data.MySqlClient.Properties.ReservedWords.txt"); #else dt.AddColumn("ReservedWord", typeof(string)); Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("MySql.Data.MySqlClient.Properties.ReservedWords.txt"); #endif StreamReader sr = new StreamReader(str); string line = sr.ReadLine(); while (line != null) { string[] keywords = line.Split(new char[] { ' ' }); foreach (string s in keywords) { if (String.IsNullOrEmpty(s)) continue; MySqlSchemaRow row = dt.AddRow(); row[0] = s; } line = sr.ReadLine(); } #if !CF sr.Dispose(); #else sr.Close(); #endif str.Close(); return dt; }
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)); c.AddColumn("GENERATION_EXPRESSION", typeof(string)); // we don't allow restricting on table type here string columnName = null; if (restrictions != null && restrictions.Length == 4) { columnName = restrictions[3]; restrictions[3] = null; } MySqlSchemaCollection tables = GetTables(restrictions); foreach (MySqlSchemaRow row in tables.Rows) { LoadTableColumns(c, row["TABLE_SCHEMA"].ToString(), row["TABLE_NAME"].ToString(), columnName); } QuoteDefaultValues(c); return(c); }
private MySqlSchemaCollection GetDataSourceInformation() { #if CF || RT throw new NotSupportedException(); #else MySqlSchemaCollection dt = new MySqlSchemaCollection("DataSourceInformation"); dt.AddColumn("CompositeIdentifierSeparatorPattern", typeof(string)); dt.AddColumn("DataSourceProductName", typeof(string)); dt.AddColumn("DataSourceProductVersion", typeof(string)); dt.AddColumn("DataSourceProductVersionNormalized", typeof(string)); dt.AddColumn("GroupByBehavior", typeof(GroupByBehavior)); dt.AddColumn("IdentifierPattern", typeof(string)); dt.AddColumn("IdentifierCase", typeof(IdentifierCase)); dt.AddColumn("OrderByColumnsInSelect", typeof(bool)); dt.AddColumn("ParameterMarkerFormat", typeof(string)); dt.AddColumn("ParameterMarkerPattern", typeof(string)); dt.AddColumn("ParameterNameMaxLength", typeof(int)); dt.AddColumn("ParameterNamePattern", typeof(string)); dt.AddColumn("QuotedIdentifierPattern", typeof(string)); dt.AddColumn("QuotedIdentifierCase", typeof(IdentifierCase)); dt.AddColumn("StatementSeparatorPattern", typeof(string)); dt.AddColumn("StringLiteralPattern", typeof(string)); dt.AddColumn("SupportedJoinOperators", typeof(SupportedJoinOperators)); DBVersion v = connection.driver.Version; string ver = String.Format("{0:0}.{1:0}.{2:0}", v.Major, v.Minor, v.Build); MySqlSchemaRow row = dt.AddRow(); row["CompositeIdentifierSeparatorPattern"] = "\\."; row["DataSourceProductName"] = "MySQL"; row["DataSourceProductVersion"] = connection.ServerVersion; row["DataSourceProductVersionNormalized"] = ver; row["GroupByBehavior"] = GroupByBehavior.Unrelated; row["IdentifierPattern"] = @"(^\`\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\`[^\`\0]|\`\`+\`$)|(^\"" + [^\""\0]|\""\""+\""$)"; row["IdentifierCase"] = IdentifierCase.Insensitive; row["OrderByColumnsInSelect"] = false; row["ParameterMarkerFormat"] = "{0}"; row["ParameterMarkerPattern"] = "(@[A-Za-z0-9_$#]*)"; row["ParameterNameMaxLength"] = 128; row["ParameterNamePattern"] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)"; row["QuotedIdentifierPattern"] = @"(([^\`]|\`\`)*)"; row["QuotedIdentifierCase"] = IdentifierCase.Sensitive; row["StatementSeparatorPattern"] = ";"; row["StringLiteralPattern"] = "'(([^']|'')*)'"; row["SupportedJoinOperators"] = 15; dt.Rows.Add(row); return dt; #endif }
public virtual MySqlSchemaCollection GetForeignKeyColumns(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("Foreign Keys"); dt.AddColumn("CONSTRAINT_CATALOG", typeof(string)); dt.AddColumn("CONSTRAINT_SCHEMA", typeof(string)); dt.AddColumn("CONSTRAINT_NAME", typeof(string)); dt.AddColumn("TABLE_CATALOG", typeof(string)); dt.AddColumn("TABLE_SCHEMA", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("COLUMN_NAME", typeof(string)); dt.AddColumn("ORDINAL_POSITION", typeof(int)); dt.AddColumn("REFERENCED_TABLE_CATALOG", typeof(string)); dt.AddColumn("REFERENCED_TABLE_SCHEMA", typeof(string)); dt.AddColumn("REFERENCED_TABLE_NAME", typeof(string)); dt.AddColumn("REFERENCED_COLUMN_NAME", typeof(string)); // first we use our restrictions to get a list of tables that should be // consulted. We save the keyname restriction since GetTables doesn't // understand that. string keyName = null; if (restrictions != null && restrictions.Length >= 4) { keyName = restrictions[3]; restrictions[3] = null; } MySqlSchemaCollection tables = GetTables(restrictions); // now for each table retrieved, we call our helper function to // parse it's foreign keys foreach (MySqlSchemaRow table in tables.Rows) { GetForeignKeysOnTable(dt, table, keyName, true); } return(dt); }
public virtual MySqlSchemaCollection GetIndexColumns(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("IndexColumns"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("COLUMN_NAME", typeof(string)); dt.AddColumn("ORDINAL_POSITION", typeof(int)); dt.AddColumn("SORT_ORDER", typeof(string)); int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) restrictions.CopyTo(tableRestrictions, 0); tableRestrictions[3] = "BASE TABLE"; MySqlSchemaCollection tables = GetTables(tableRestrictions); foreach (MySqlSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", table["TABLE_SCHEMA"], table["TABLE_NAME"]); MySqlCommand cmd = new MySqlCommand(sql, connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME")); string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME")); if (restrictions != null) { if (restrictions.Length >= 4 && restrictions[3] != null && key_name != restrictions[3]) continue; if (restrictions.Length >= 5 && restrictions[4] != null && col_name != restrictions[4]) continue; } MySqlSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = key_name; row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE")); row["COLUMN_NAME"] = col_name; row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX")); row["SORT_ORDER"] = reader.GetString("COLLATION"); } } } return dt; }
public virtual MySqlSchemaCollection GetProcedures(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedures"); dt.AddColumn("SPECIFIC_NAME", typeof(string)); dt.AddColumn("ROUTINE_CATALOG", typeof(string)); dt.AddColumn("ROUTINE_SCHEMA", typeof(string)); dt.AddColumn("ROUTINE_NAME", typeof(string)); dt.AddColumn("ROUTINE_TYPE", typeof(string)); dt.AddColumn("DTD_IDENTIFIER", typeof(string)); dt.AddColumn("ROUTINE_BODY", typeof(string)); dt.AddColumn("ROUTINE_DEFINITION", typeof(string)); dt.AddColumn("EXTERNAL_NAME", typeof(string)); dt.AddColumn("EXTERNAL_LANGUAGE", typeof(string)); dt.AddColumn("PARAMETER_STYLE", typeof(string)); dt.AddColumn("IS_DETERMINISTIC", typeof(string)); dt.AddColumn("SQL_DATA_ACCESS", typeof(string)); dt.AddColumn("SQL_PATH", typeof(string)); dt.AddColumn("SECURITY_TYPE", typeof(string)); dt.AddColumn("CREATED", typeof(DateTime)); dt.AddColumn("LAST_ALTERED", typeof(DateTime)); dt.AddColumn("SQL_MODE", typeof(string)); dt.AddColumn("ROUTINE_COMMENT", typeof(string)); dt.AddColumn("DEFINER", typeof(string)); StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1"); if (restrictions != null) { if (restrictions.Length >= 2 && restrictions[1] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND db LIKE '{0}'", restrictions[1]); } if (restrictions.Length >= 3 && restrictions[2] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND name LIKE '{0}'", restrictions[2]); } if (restrictions.Length >= 4 && restrictions[3] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND type LIKE '{0}'", restrictions[3]); } } MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MySqlSchemaRow row = dt.AddRow(); row["SPECIFIC_NAME"] = reader.GetString("specific_name"); row["ROUTINE_CATALOG"] = DBNull.Value; row["ROUTINE_SCHEMA"] = reader.GetString("db"); row["ROUTINE_NAME"] = reader.GetString("name"); string routineType = reader.GetString("type"); row["ROUTINE_TYPE"] = routineType; row["DTD_IDENTIFIER"] = StringUtility.ToLowerInvariant(routineType) == "function" ? (object)reader.GetString("returns") : DBNull.Value; row["ROUTINE_BODY"] = "SQL"; row["ROUTINE_DEFINITION"] = reader.GetString("body"); row["EXTERNAL_NAME"] = DBNull.Value; row["EXTERNAL_LANGUAGE"] = DBNull.Value; row["PARAMETER_STYLE"] = "SQL"; row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic"); row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access"); row["SQL_PATH"] = DBNull.Value; row["SECURITY_TYPE"] = reader.GetString("security_type"); row["CREATED"] = reader.GetDateTime("created"); row["LAST_ALTERED"] = reader.GetDateTime("modified"); row["SQL_MODE"] = reader.GetString("sql_mode"); row["ROUTINE_COMMENT"] = reader.GetString("comment"); row["DEFINER"] = reader.GetString("definer"); } } return(dt); }
public virtual MySqlSchemaCollection GetIndexes(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("Indexes"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("UNIQUE", typeof(bool)); dt.AddColumn("PRIMARY", typeof(bool)); dt.AddColumn("TYPE", typeof(string)); dt.AddColumn("COMMENT", typeof(string)); // Get the list of tables first int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) restrictions.CopyTo(tableRestrictions, 0); tableRestrictions[3] = "BASE TABLE"; MySqlSchemaCollection tables = GetTables(tableRestrictions); foreach (MySqlSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", MySqlHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]), MySqlHelper.DoubleQuoteString((string)table["TABLE_NAME"])); MySqlSchemaCollection indexes = QueryCollection("indexes", sql); foreach (MySqlSchemaRow index in indexes.Rows) { long seq_index = (long)index["SEQ_IN_INDEX"]; if (seq_index != 1) continue; if (restrictions != null && restrictions.Length == 4 && restrictions[3] != null && !index["KEY_NAME"].Equals(restrictions[3])) continue; MySqlSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = index["KEY_NAME"]; row["TABLE_NAME"] = index["TABLE"]; row["UNIQUE"] = (long)index["NON_UNIQUE"] == 0; row["PRIMARY"] = index["KEY_NAME"].Equals("PRIMARY"); row["TYPE"] = index["INDEX_TYPE"]; row["COMMENT"] = index["COMMENT"]; } } return dt; }
private MySqlSchemaCollection GetDataSourceInformation() { 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 = $"{v.Major:0}.{v.Minor:0}.{v.Build:0}"; 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); }
private static MySqlSchemaCollection GetDataTypes() { MySqlSchemaCollection dt = new MySqlSchemaCollection("DataTypes"); dt.AddColumn("TypeName", typeof(string)); dt.AddColumn("ProviderDbType", typeof(int)); dt.AddColumn("ColumnSize", typeof(long)); dt.AddColumn("CreateFormat", typeof(string)); dt.AddColumn("CreateParameters", typeof(string)); dt.AddColumn("DataType", typeof(string)); dt.AddColumn("IsAutoincrementable", typeof(bool)); dt.AddColumn("IsBestMatch", typeof(bool)); dt.AddColumn("IsCaseSensitive", typeof(bool)); dt.AddColumn("IsFixedLength", typeof(bool)); dt.AddColumn("IsFixedPrecisionScale", typeof(bool)); dt.AddColumn("IsLong", typeof(bool)); dt.AddColumn("IsNullable", typeof(bool)); dt.AddColumn("IsSearchable", typeof(bool)); dt.AddColumn("IsSearchableWithLike", typeof(bool)); dt.AddColumn("IsUnsigned", typeof(bool)); dt.AddColumn("MaximumScale", typeof(short)); dt.AddColumn("MinimumScale", typeof(short)); dt.AddColumn("IsConcurrencyType", typeof(bool)); dt.AddColumn("IsLiteralSupported", typeof(bool)); dt.AddColumn("LiteralPrefix", typeof(string)); dt.AddColumn("LiteralSuffix", typeof(string)); dt.AddColumn("NativeDataType", typeof(string)); // have each one of the types contribute to the datatypes collection MySqlBit.SetDSInfo(dt); MySqlBinary.SetDSInfo(dt); MySqlDateTime.SetDSInfo(dt); MySqlTimeSpan.SetDSInfo(dt); MySqlString.SetDSInfo(dt); MySqlDouble.SetDSInfo(dt); MySqlSingle.SetDSInfo(dt); MySqlByte.SetDSInfo(dt); MySqlInt16.SetDSInfo(dt); MySqlInt32.SetDSInfo(dt); MySqlInt64.SetDSInfo(dt); MySqlDecimal.SetDSInfo(dt); MySqlUByte.SetDSInfo(dt); MySqlUInt16.SetDSInfo(dt); MySqlUInt32.SetDSInfo(dt); MySqlUInt64.SetDSInfo(dt); return dt; }
protected 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 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; }
protected MySqlSchemaCollection QueryCollection(string name, string sql) { MySqlSchemaCollection c = new MySqlSchemaCollection(name); MySqlCommand cmd = new MySqlCommand(sql, connection); MySqlDataReader reader = cmd.ExecuteReader(); for (int i = 0; i < reader.FieldCount; i++) c.AddColumn(reader.GetName(i), reader.GetFieldType(i)); using (reader) { while (reader.Read()) { MySqlSchemaRow row = c.AddRow(); for (int i = 0; i < reader.FieldCount; i++) row[i] = reader.GetValue(i); } } return c; }
public virtual MySqlSchemaCollection GetColumns(string[] restrictions) { MySqlSchemaCollection schemaCollection = new MySqlSchemaCollection("Columns"); schemaCollection.AddColumn("TABLE_CATALOG", typeof(string)); schemaCollection.AddColumn("TABLE_SCHEMA", typeof(string)); schemaCollection.AddColumn("TABLE_NAME", typeof(string)); schemaCollection.AddColumn("COLUMN_NAME", typeof(string)); schemaCollection.AddColumn("ORDINAL_POSITION", typeof(ulong)); schemaCollection.AddColumn("COLUMN_DEFAULT", typeof(string)); schemaCollection.AddColumn("IS_NULLABLE", typeof(string)); schemaCollection.AddColumn("DATA_TYPE", typeof(string)); schemaCollection.AddColumn("CHARACTER_MAXIMUM_LENGTH", typeof(ulong)); schemaCollection.AddColumn("CHARACTER_OCTET_LENGTH", typeof(ulong)); schemaCollection.AddColumn("NUMERIC_PRECISION", typeof(ulong)); schemaCollection.AddColumn("NUMERIC_SCALE", typeof(ulong)); schemaCollection.AddColumn("CHARACTER_SET_NAME", typeof(string)); schemaCollection.AddColumn("COLLATION_NAME", typeof(string)); schemaCollection.AddColumn("COLUMN_TYPE", typeof(string)); schemaCollection.AddColumn("COLUMN_KEY", typeof(string)); schemaCollection.AddColumn("EXTRA", typeof(string)); schemaCollection.AddColumn("PRIVILEGES", typeof(string)); schemaCollection.AddColumn("COLUMN_COMMENT", typeof(string)); string columnRestriction = null; if ((restrictions != null) && (restrictions.Length == 4)) { columnRestriction = restrictions[3]; restrictions[3] = null; } foreach (MySqlSchemaRow row in this.GetTables(restrictions).Rows) { this.LoadTableColumns(schemaCollection, row["TABLE_SCHEMA"].ToString(), row["TABLE_NAME"].ToString(), columnRestriction); } this.QuoteDefaultValues(schemaCollection); return(schemaCollection); }