protected void GetPrimaryKeys() { var primaryKeySql = MySqlSchemaQueries.FetchPrimaryKeys; using (var command = new MySqlCommand(primaryKeySql, _connection)) { command.Parameters.AddWithValue("@DatabaseName", DatabaseName); using (var reader = command.ExecuteReader()) { // read table names. while (reader.Read()) { // get the keys. var tableName = reader["TABLE_NAME"] as string; var columnName = reader["COLUMN_NAME"] as string; int ordinalPosition = 0; var val = reader["ORDINAL_POSITION"]; if (val is uint) { ordinalPosition = (int)(uint)val; } else if (val is long) { ordinalPosition = (int)(long)val; } // find the table. var table = MySqlTables.FirstOrDefault(t => t.Name == tableName); if (table == null) { continue; } // find the column var column = table.MySqlColumns.FirstOrDefault(t => t.Name == columnName); if (column == null) { throw new Exception($"could not find {columnName} inside table {tableName}"); } column.IsPrimaryKey = true; column.PrimaryKeyOrder = ordinalPosition; } } } }
public void LoadSchema() { // clear MySqlTables.Clear(); // connect & collect using (_connection = new MySqlConnection(ConnectionString)) { _connection.Open(); GetTables(); GetColumns(); GetPrimaryKeys(); GetForeignKeys(); GetIndexes(); GetSequences(); } }
protected void GetTables() { var tablesSql = MySqlSchemaQueries.FetchTables; // command. using (var command = new MySqlCommand(tablesSql, _connection)) { command.Parameters.AddWithValue("@DatabaseName", DatabaseName); command.Prepare(); using (var reader = command.ExecuteReader()) { // read table names. while (reader.Read()) { // create a new table. var t = new Table(); t.DatabaseSchema = this; t.Name = reader["TABLE_NAME"] as string; MySqlTables.Add(t); } } } }
protected void GetIndexes() { var indexSql = MySqlSchemaQueries.FetchIndexes; // command. using (var command = new MySqlCommand(indexSql, _connection)) { command.Parameters.AddWithValue("@DatabaseName", DatabaseName); using (var reader = command.ExecuteReader()) { var indexModels = new List <MySqlIndexModel>(); // read table names. while (reader.Read()) { var indexModel = new MySqlIndexModel(); indexModel.TableName = reader["TableName"] as string; indexModel.IndexName = reader["IndexName"] as string; indexModel.ColumnName = reader["ColumnName"] as string; indexModel.IsUniqueConstraint = (long)reader["IsUniqueConstraint"] != 0; byte ordinal = 0; var raw = reader["KeyOrdinal"]; if (raw is UInt32) { ordinal = (Byte)(UInt32)raw; } else if (raw is long) { ordinal = (Byte)(long)raw; } indexModel.KeyOrdinal = ordinal; indexModels.Add(indexModel); } var indexes = indexModels .GroupBy(t => new { t.TableName, t.IndexName, t.IsUniqueConstraint }) .Select(t => { var table = MySqlTables.FirstOrDefault(t2 => t2.Name == t.Key.TableName); if (table == null) { throw new Exception($"Cannot find table {t.Key.TableName}"); } var index = new Index(); index.MySqlTable = table; index.MySqlColumns = t.OrderBy(t2 => t2.KeyOrdinal).Select(t2 => { var c = table.MySqlColumns.FirstOrDefault(t3 => t3.Name == t2.ColumnName); if (c == null) { throw new Exception($"Cannot find column {t2.ColumnName} in {table}"); } return(c); ; }).ToList(); index.IsUnique = t.Key.IsUniqueConstraint; index.Name = t.Key.IndexName; return(index); }) .ToList(); // add indexes. indexes.ForEach(index2 => { index2.MySqlTable.MySqlIndexes.Add(index2); }); } } }
protected void GetColumns() { var columnsSql = MySqlSchemaQueries.FetchColumns; // command. using (var command = new MySqlCommand(columnsSql, _connection)) { command.Parameters.AddWithValue("@DatabaseName", DatabaseName); using (var reader = command.ExecuteReader()) { // table and schema name. string tableName; Table table = null; // read table names. while (reader.Read()) { tableName = reader["TABLE_NAME"] as string; // find table. table = MySqlTables.FirstOrDefault(t => t.Name == tableName); if (table == null) { continue; } // set dynamic stuff. var column = new Column(); column.MySqlTable = table; column.IsNullable = (reader["IS_NULLABLE"] as string) == "YES"; column.DataType = reader["DATA_TYPE"] as string; column.Name = reader["COLUMN_NAME"] as string; column.IsAutoIncrement = reader["IS_AUTO_INCREMENT"] is DBNull ? false : Convert.ToBoolean(reader["IS_AUTO_INCREMENT"]); column.RawColumnType = reader["COLUMN_TYPE"] as string; // default. if (false == (reader["COLUMN_DEFAULT"] is DBNull)) { column.DefaultValue = reader["COLUMN_DEFAULT"] as string; } // max char size. if (false == (reader["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) { column.CharacterMaximumLength = reader["CHARACTER_MAXIMUM_LENGTH"] as int?; } // numeric precision stuff. if (false == (reader["NUMERIC_PRECISION"] is DBNull)) { column.NumericPrecision = Convert.ToByte(reader["NUMERIC_PRECISION"]); } if (false == (reader["NUMERIC_SCALE"] is DBNull)) { column.NumericScale = Convert.ToByte(reader["NUMERIC_SCALE"]); } if (false == (reader["DATETIME_PRECISION"] is DBNull)) { column.DateTimePrecision = Convert.ToByte(reader["DATETIME_PRECISION"]); } // add the column table.MySqlColumns.Add(column); } } } }
protected void GetForeignKeys() { var foreignKeysSql = MySqlSchemaQueries.FetchForeignKeys; // command. using (var command = new MySqlCommand(foreignKeysSql, _connection)) { command.Parameters.AddWithValue("@DatabaseName", DatabaseName); using (var reader = command.ExecuteReader()) { // read table names. while (reader.Read()) { var name = reader["FKName"] as string; var deleteCascadeAction = reader["DeleteCascadeAction"] as string; var updateCascadeAction = reader["UpdateCascadeAction"] as string; // fk var fkTableName = reader["FKTable"] as string; var fkColumnName = reader["FKColumn"] as string; // pk var pkTableName = reader["PKTable"] as string; var pkColumnName = reader["PKColumn"] as string; // fk table var fkTableInfo = MySqlTables.FirstOrDefault(t => t.Name == fkTableName); var pkTableInfo = MySqlTables.FirstOrDefault(t => t.Name == pkTableName); // if not found just skip (prob means it was filtered somewhere in the drain) if (pkTableInfo == null || fkTableInfo == null) { continue; } // find the fk column. var fkColumn = fkTableInfo.MySqlColumns.FirstOrDefault(t => t.Name.Equals(fkColumnName, StringComparison.InvariantCultureIgnoreCase)); if (fkColumn == null) { throw new Exception($"Could not find {fkColumnName} in table {fkTableName}"); } // find the pk column var pkColumn = pkTableInfo.MySqlColumns.FirstOrDefault(t => t.Name.Equals(pkColumnName, StringComparison.InvariantCultureIgnoreCase)); if (pkColumn == null) { throw new Exception( $"Could not find {pkColumnName} in table {pkColumnName}"); } // set foreign key reference var fk = new ForeignKey(); fk.MySqlPrimaryKeyColumn = pkColumn; fk.MySqlForeignKeyColumn = fkColumn; fk.DeleteCascadeAction = deleteCascadeAction; fk.UpdateCascadeAction = updateCascadeAction; fk.Name = name; fkTableInfo.MySqlForeignKeys.Add(fk); } } } }