//校验当前数据表, 当与实际表
private bool ProofTestTable(TableMapping table)
{
//先尝试创建表
var create_sql = "CREATE TABLE IF NOT EXISTS \"" + table.TableName + "\"(\n";
create_sql += string.Join(",\n", (table.Columns.Select(p => Orm.SqlDecl(p))));
create_sql += ")";
var create_lst = Dict(create_sql);
ExecuteUpdate(create_sql);
//从数据库拉取表的信息
var command = CreateCommand("SELECT sql FROM sqlite_master WHERE type = \"table\" AND name = ? ;", table.TableName);
var exists_sql = command.ExecuteScalar <string>();
var exists_lst = Dict(exists_sql);
//比对表的差异性
bool rebuild = create_lst.Count < exists_lst.Count;
var add_cols = new List <string>();
for (int i = 0; i < create_lst.Count && !rebuild; i++)
{
var col1 = create_lst[i];
if (i < exists_lst.Count)
{
var col2 = exists_lst[i];
if (!col1.Item1.Equals(col2.Item1) || !col1.Item2.Equals(col2.Item2))
{
rebuild = true;
}
}
else
{
add_cols.Add(col1.Item2);
}
}
if (rebuild)
{
var same_cols = new List <string>();
for (int i = 0; i < create_lst.Count; i++)
{
for (int j = 0; j < exists_lst.Count; j++)
{
if (create_lst[i].Item1.Equals(exists_lst[j].Item1))
{
same_cols.Add(create_lst[i].Item1);
break;
}
}
}
//重建数据表(创建临时表->迁移相同字段的数据->删除临时表)
RunInTransaction(() =>
{
var table_name = table.TableName;
var table_temp = table_name + "_temp";
ExecuteUpdate("PRAGMA foreign_keys = off;");
ExecuteUpdate(string.Format("DROP TABLE IF EXISTS \"{0}\" ;", table_temp));
ExecuteUpdate(string.Format("CREATE TABLE \"{1}\" AS SELECT * FROM \"{0}\" ;", table_name, table_temp));
ExecuteUpdate(string.Format("DROP TABLE \"{0}\" ;", table_name));
ExecuteUpdate(create_sql);
if (same_cols.Count > 0)
{
var fields = string.Join(",", same_cols);
//ExecuteUpdate("INSERT INTO \"{0}\" ( {2} ) SELECT {2} FROM \"{0}_temp_table\";");
ExecuteUpdate(string.Format("INSERT INTO \"{0}\" ( {2} ) SELECT {2} FROM \"{1}\";", table_name, table_temp, fields));
}
ExecuteUpdate(string.Format("DROP TABLE \"{0}\" ;", table_temp));
ExecuteUpdate("PRAGMA foreign_keys = on;");
Debug.LogWarning(string.Format("column exception, rebuild sql: {0}\n{1}", table_name, create_sql));
});
}
else if (add_cols != null && add_cols.Count > 0)
{
//追加新的字段
RunInTransaction(() =>
{
var table_name = table.TableName;
foreach (var field in add_cols)
{
ExecuteUpdate(string.Format("ALTER TABLE \"{0}\" ADD COLUMN {1};", table_name, field));
Debug.LogWarning(string.Format("Alter table add column {0}:{1}", table_name, field));
}
});
}
return(true);
}
