/// <summary>
/// 加载指定数据库中的每个表的所有列
/// </summary>
/// <param name="database">需要加载列的数据库对象</param>
protected override void LoadAllColumns(Database database)
{
foreach (Table table in database.Tables)
{
using (var columnsReader = this.Db.QueryDataReader(@"SHOW FULL COLUMNS FROM `" + table.Name + "`;"))
{
while (columnsReader.Read())
{
string columnName = columnsReader["Field"].ToString();
string sqlType = columnsReader["Type"].ToString();
DbType dbType = MySqlDbTypeHelper.ConvertFromMySqlTypeString(sqlType);
Column column = new Column(columnName, dbType, null, table);
column.IsRequired = string.Compare(columnsReader["Null"].ToString(), "Yes", true) != 0;
table.Columns.Add(column);
}
table.SortColumns();
}
}
}
/// <summary>
/// 加载指定数据库中的每个表的所有列
/// </summary>
/// <param name="database">需要加载列的数据库对象</param>
protected override void LoadAllColumns(Database database)
{
//用一句 Sql 将所有的表的所有字段都一次性查询出来。
//不再使用 @"SHOW FULL COLUMNS FROM `" + table.Name + "`;"
var sql = new StringBuilder(
@"SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN (");
sql.Append(string.Join(",", database.Tables.Select(t => "'" + t.Name + "'")));
sql.Append(") AND TABLE_SCHEMA = '").Append(database.Name).Append(@"'
ORDER BY TABLE_NAME");
using (var columnsReader = this.Db.QueryDataReader(sql.ToString()))
{
Table currentTable = null;//当前正在处理的表。（放在循环外，有缓存的作用。）
while (columnsReader.Read())
{
//找到该列所对应的表。
var tableName = columnsReader["TABLE_NAME"].ToString();
if (currentTable == null || !currentTable.Name.EqualsIgnoreCase(tableName))
{
currentTable = database.FindTable(tableName);
}
string columnName = columnsReader["COLUMN_NAME"].ToString();
string sqlType = columnsReader["DATA_TYPE"].ToString();
DbType dbType = MySqlDbTypeHelper.ConvertFromMySqlTypeString(sqlType);
Column column = new Column(columnName, dbType, null, currentTable);
column.IsRequired = "NO".EqualsIgnoreCase(columnsReader["IS_NULLABLE"].ToString());
column.IsIdentity = columnsReader["EXTRA"].ToString().ToLower().Contains("auto_increment");
currentTable.Columns.Add(column);
}
}
}
/// <summary>
/// 生成更新备注信息
/// </summary>
/// <param name="op">更新备注的实体对象</param>
protected override void Generate(UpdateComment op)
{
if (string.IsNullOrEmpty(op.ColumnName))
{
this.AddRun(new SqlMigrationRun
{
Sql = string.Format(@"ALTER TABLE `{0}` COMMENT '{1}';", this.Prepare(op.TableName), op.Comment)
});
}
else
{
//MySql 不支持外键修改备注，所以过滤掉外键修改备注
if (string.Compare(op.ColumnName, "id", true) != 0 && string.Compare(op.TableName, "BlogUser") != 0)
{
this.AddRun(new SqlMigrationRun
{
Sql = string.Format(@"ALTER TABLE `{0}` MODIFY COLUMN `{1}` {2} COMMENT '{3}';", this.Prepare(op.TableName), this.Prepare(op.ColumnName), MySqlDbTypeHelper.ConvertToMySqlTypeString(op.ColumnDataType), op.Comment)
});
}
}
}
/// <summary>
/// 把Clr的数据类型转型为MySql的数据类型
/// </summary>
/// <param name="dataType">数据类型</param>
/// <param name="length">数据长度</param>
/// <returns>返回MySql的数据类型</returns>
protected override string ConvertToTypeString(DbType dataType, string length)
{
return(MySqlDbTypeHelper.ConvertToMySqlTypeString(dataType, length));
}
