public Database Read()
{
Database database = new Database(this._dbSetting.Database);
var conn = this._db.Connection;
try
{
conn.Open();
this.LoadAllTables(database);
this.LoadAllColumns(database);
this.LoadAllConstraints(database);
}
catch (DbException)
{
database.Removed = true;
}
finally
{
conn.Close();
}
return database;
}
protected override void LoadAllColumns(Database database)
{
foreach (Table table in database.Tables)
{
using (var columnsReader = this.Db.QueryDataReader(@"
SELECT * FROM user_tab_columns WHERE TABLE_NAME = {0}
", table.Name))
{
while (columnsReader.Read())
{
string columnName = columnsReader["COLUMN_NAME"].ToString();
string sqlType = columnsReader["DATA_TYPE"].ToString().ToLower();
if (sqlType == "number")
{
var dataScale = columnsReader["DATA_SCALE"].ToString();
if (dataScale == "0") { sqlType = "integer"; }
}
DbType dbType = OracleDbTypeHelper.ConvertFromOracleTypeString(sqlType);
Column column = new Column(dbType, null, columnName, table);
column.IsRequired = columnsReader["NULLABLE"].ToString() == "N";
table.Columns.Add(column);
}
table.SortColumns();
}
}
}
internal DatabaseChanges(Database oldDatabase, DestinationDatabase newDatabase, IList<TableChanges> tableChanges)
{
this.OldDatabase = oldDatabase;
this.NewDatabase = newDatabase;
this.ChangeType = ChangeType.UnChanged;
if (!oldDatabase.Removed || !newDatabase.Removed)
{
if (oldDatabase.Removed)
{
this.ChangeType = ChangeType.Added;
return;
}
if (newDatabase.Removed)
{
this.ChangeType = ChangeType.Removed;
return;
}
if (tableChanges != null && tableChanges.Count > 0)
{
this.ChangeType = ChangeType.Modified;
this._allRecords = tableChanges;
}
}
}
public Table(string name, Database dataBase)
{
if (string.IsNullOrWhiteSpace(name)) throw new ArgumentNullException("name");
if (dataBase == null) throw new ArgumentNullException("dataBase");
this.Columns = new List<Column>();
this.Name = name;
this.DataBase = dataBase;
}
protected override void LoadAllTables(Database database)
{
using (var reader = this.Db.QueryDataReader(@"select * from user_tables"))
{
while (reader.Read())
{
string tableName = reader["TABLE_NAME"].ToString();
Table table = new Table(tableName, database);
database.Tables.Add(table);
}
}
}
/// <summary>
/// 计算出两个数据库元数据的所有表差别
/// </summary>
/// <param name="oldDatabase">旧数据库</param>
/// <param name="newDatabase">新数据库</param>
/// <returns></returns>
public static DatabaseChanges Distinguish(Database oldDatabase, DestinationDatabase newDatabase)
{
if (!oldDatabase.Removed) { oldDatabase.OrderByRelations(); }
if (!newDatabase.Removed) { newDatabase.OrderByRelations(); }
List<TableChanges> result = new List<TableChanges>();
if (!oldDatabase.Removed && !newDatabase.Removed)
{
//先找出所有被删除的表
foreach (var oldTable in oldDatabase.Tables.Reverse())
{
if (newDatabase.FindTable(oldTable.Name) == null && !newDatabase.IsIgnored(oldTable.Name))
{
result.Add(new TableChanges(oldTable, null, ChangeType.Removed));
}
}
foreach (var newTable in newDatabase.Tables)
{
if (!newDatabase.IsIgnored(newTable.Name))
{
var oldTable = oldDatabase.FindTable(newTable.Name);
//如果没有找到旧表，说明这个表是新加的。
if (oldTable == null)
{
result.Add(new TableChanges(null, newTable, ChangeType.Added));
}
else
{
//即不是新表，也不是旧表，计算两个表的区别
TableChanges record = Distinguish(oldTable, newTable);
//如果有区别，则记录下来
if (record != null) { result.Add(record); }
}
}
}
}
return new DatabaseChanges(oldDatabase, newDatabase, result);
}
/// <summary>
/// 加载每个表的所有列
/// </summary>
/// <param name="database"></param>
protected override void LoadAllColumns(Database database)
{
foreach (Table table in database.Tables)
{
using (var columnsReader = this.Db.QueryDataReader(@"
SELECT C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = {0}
", table.Name))
{
while (columnsReader.Read())
{
string columnName = columnsReader["COLUMN_NAME"].ToString();
string sqlType = columnsReader["DATA_TYPE"].ToString();
//不再读取 Length
//string length = null;
//var lengthObj = columnsReader["CHARACTER_MAXIMUM_LENGTH"];
//if (lengthObj != null && !DBNull.Value.Equals(lengthObj) && lengthObj.ToString() != "-1")
//{
// length = lengthObj.ToString();
//}
DbType dbType = SqlDbTypeHelper.ConvertFromSQLTypeString(sqlType);
Column column = new Column(dbType, null, columnName, table);
column.IsRequired = string.Compare(columnsReader["IS_NULLABLE"].ToString(), "no", true) == 0;
table.Columns.Add(column);
}
table.SortColumns();
}
}
this.LoadIsIdentity(database);
}
/// <summary>
/// 添加所有表
/// </summary>
/// <param name="database"></param>
protected override void LoadAllTables(Database database)
{
using (var reader = this.Db.QueryDataReader(@"select * from INFORMATION_SCHEMA.TABLES"))
{
while (reader.Read())
{
string tableName = reader["TABLE_NAME"].ToString();
string tableType = reader["TABLE_TYPE"].ToString().ToLower();
//SqlServer 中是 "BASE TABLE"，同时还会把一些系统表也查询出来，例如：sysdiagrams
//SQLCE 中是 "TABLE"
if (tableType.Contains("table") && !tableName.StartsWith("sys"))
{
//string schemaName = reader["SCHEMA_NAME"].ToString();
Table table = new Table(tableName, database);
database.Tables.Add(table);
}
}
}
}
private void LoadIsIdentity(Database database)
{
var identities = this.ReadAllIdentities();
foreach (var identity in identities)
{
var table = database.FindTable(identity.TableName);
if (table != null)
{
var column = table.FindColumn(identity.ColumnName);
if (column != null)
{
column.IsIdentity = true;
}
}
}
}
/// <summary> /// 加载每个表的所有列 /// </summary> /// <param name="database"></param> protected abstract void LoadAllColumns(Database database);
/// <summary>
/// 加载外键
/// </summary>
/// <param name="database"></param>
private void LoadAllConstraints(Database database)
{
List<Constraint> allConstrains = this.ReadAllConstrains();
foreach (var table in database.Tables)
{
foreach (var column in table.Columns)
{
this.DealColumnConstraints(column, allConstrains);
}
}
}
/// <summary> /// 添加所有表 /// </summary> /// <param name="database"></param> protected abstract void LoadAllTables(Database database);