/// <summary>
/// 用于根据规则生成Sql表的名称, eg:相同前缀、命名规则等
/// </summary>
/// <param name="original">true表示设计时获取旧名称</param>
/// <param name="ctx">null表示运行时</param>
internal string GetSqlTableName(bool original, Design.IDesignContext ctx)
{
Debug.Assert(SqlStoreOptions != null);
#if FUTURE
return(Name); //暂直接返回名称
#else
if (!original && _sqlTableName_cached != null)
{
return(_sqlTableName_cached);
}
var name = original ? OriginalName : Name;
//TODO:根据规则生成，另注意默认存储使用默认规则
//if ((SqlStoreOptions.DataStoreModel.NameRules & DataStoreNameRules.AppPrefixForTable)
// == DataStoreNameRules.AppPrefixForTable)
//{
ApplicationModel app = ctx == null?Runtime.RuntimeContext.Current.GetApplicationModelAsync(AppId).Result
: ctx.GetApplicationModel(AppId);
if (original)
{
return($"{app.Name}.{name}");
}
_sqlTableName_cached = $"{app.Name}.{name}";
//}
//else
//{
// _sqlTableName_cached = name;
//}
return(_sqlTableName_cached);
#endif
}
public async Task DropTableAsync(EntityModel model, DbTransaction txn, Design.IDesignContext ctx)
{
Debug.Assert(txn != null);
var cmd = MakeDropTable(model, ctx);
cmd.Connection = txn.Connection;
await cmd.ExecuteNonQueryAsync();
}
public async Task AlterTableAsync(EntityModel model, DbTransaction txn, Design.IDesignContext ctx)
{
Debug.Assert(txn != null);
var cmds = MakeAlterTable(model, ctx);
foreach (var cmd in cmds)
{
cmd.Connection = txn.Connection;
await cmd.ExecuteNonQueryAsync();
}
}
protected internal abstract DbCommand MakeDropTable(EntityModel model, Design.IDesignContext ctx);
protected internal abstract IList <DbCommand> MakeAlterTable(EntityModel model, Design.IDesignContext ctx);
protected override IList <DbCommand> MakeAlterTable(EntityModel model, Design.IDesignContext ctx)
{
//TODO:***处理主键变更
var tableName = model.GetSqlTableName(false, ctx);
StringBuilder sb;
bool needCommand = false; //用于判断是否需要处理NpgsqlCommand
List <string> fks = new List <string>(); //引用外键列表
List <DbCommand> commands = new List <DbCommand>();
//List<DbCommand> funcCmds = new List<DbCommand>();
//先处理表名称有没有变更，后续全部使用新名称
if (model.IsNameChanged)
{
var oldTableName = model.GetSqlTableName(true, ctx);
var renameTableCmd = new NpgsqlCommand($"ALTER TABLE \"{oldTableName}\" RENAME TO \"{tableName}\"");
commands.Add(renameTableCmd);
}
//处理删除的成员
var deletedMembers = model.Members.Where(t => t.PersistentState == PersistentState.Deleted).ToArray();
if (deletedMembers != null && deletedMembers.Length > 0)
{
#region ----删除的成员----
sb = StringBuilderCache.Acquire();
foreach (var m in deletedMembers)
{
if (m.Type == EntityMemberType.DataField)
{
needCommand = true;
sb.AppendFormat("ALTER TABLE \"{0}\" DROP COLUMN \"{1}\";", tableName, ((DataFieldModel)m).SqlColOriginalName);
}
else if (m.Type == EntityMemberType.EntityRef)
{
EntityRefModel rm = (EntityRefModel)m;
if (!rm.IsAggregationRef)
{
var fkName = $"FK_{rm.Owner.Id}_{rm.MemberId}"; //TODO:特殊处理DbFirst导入表的外键约束名称
fks.Add($"ALTER TABLE \"{tableName}\" DROP CONSTRAINT \"{fkName}\";");
}
}
}
var cmdText = StringBuilderCache.GetStringAndRelease(sb);
if (needCommand)
{
//加入删除的外键SQL
for (int i = 0; i < fks.Count; i++)
{
sb.Insert(0, fks[i]);
sb.AppendLine();
}
commands.Add(new NpgsqlCommand(cmdText));
}
#endregion
}
//reset
needCommand = false;
fks.Clear();
//处理新增的成员
var addedMembers = model.Members.Where(t => t.PersistentState == PersistentState.Detached).ToArray();
if (addedMembers != null && addedMembers.Length > 0)
{
#region ----新增的成员----
sb = StringBuilderCache.Acquire();
foreach (var m in addedMembers)
{
if (m.Type == EntityMemberType.DataField)
{
needCommand = true;
sb.AppendFormat("ALTER TABLE \"{0}\" ADD COLUMN ", tableName);
BuildFieldDefine((DataFieldModel)m, sb, false);
sb.Append(";");
}
else if (m.Type == EntityMemberType.EntityRef)
{
var rm = (EntityRefModel)m;
if (!rm.IsAggregationRef) //只有非聚合引合创建外键
{
fks.Add(BuildForeignKey(rm, ctx, tableName));
//考虑CreateGetTreeNodeChildsDbFuncCommand
}
}
}
var cmdText = StringBuilderCache.GetStringAndRelease(sb);
if (needCommand)
{
//加入关系
sb.AppendLine();
for (int i = 0; i < fks.Count; i++)
{
sb.AppendLine(fks[i]);
}
commands.Add(new NpgsqlCommand(cmdText));
}
#endregion
}
//reset
needCommand = false;
fks.Clear();
//处理修改的成员
var changedMembers = model.Members.Where(t => t.PersistentState == PersistentState.Modified).ToArray();
if (changedMembers != null && changedMembers.Length > 0)
{
#region ----修改的成员----
foreach (var m in changedMembers)
{
if (m.Type == EntityMemberType.DataField)
{
DataFieldModel dfm = (DataFieldModel)m;
//先处理数据类型变更，变更类型或者变更AllowNull或者变更默认值
if (dfm.IsDataTypeChanged)
{
sb = StringBuilderCache.Acquire();
sb.AppendFormat("ALTER TABLE \"{0}\" ALTER COLUMN ", tableName);
string defaultValue = BuildFieldDefine(dfm, sb, true);
if (dfm.AllowNull)
{
sb.AppendFormat(",ALTER COLUMN \"{0}\" DROP NOT NULL", dfm.SqlColOriginalName);
}
else
{
if (dfm.DataType == EntityFieldType.Binary)
{
throw new Exception("Binary field must be allow null");
}
sb.AppendFormat(",ALTER COLUMN \"{0}\" SET NOT NULL,ALTER COLUMN \"{0}\" SET DEFAULT {1}",
dfm.SqlColOriginalName, defaultValue);
}
commands.Add(new NpgsqlCommand(StringBuilderCache.GetStringAndRelease(sb)));
}
//再处理重命名列
if (m.IsNameChanged)
{
var renameColCmd = new NpgsqlCommand($"ALTER TABLE \"{tableName}\" RENAME COLUMN \"{dfm.SqlColOriginalName}\" TO \"{dfm.SqlColName}\"");
commands.Add(renameColCmd);
}
}
//TODO:处理EntityRef更新与删除规则
//注意不再需要同旧实现一样变更EntityRef的外键约束名称 "ALTER TABLE \"XXX\" RENAME CONSTRAINT \"XXX\" TO \"XXX\""
//因为ModelFirst的外键名称为FK_{MemberId}；CodeFirst为导入的名称
}
#endregion
}
//处理索引变更
BuildIndexes(model, commands, tableName);
return(commands);
}
protected override DbCommand MakeDropTable(EntityModel model, Design.IDesignContext ctx)
{
var tableName = model.GetSqlTableName(true, ctx); //使用旧名称
return(new NpgsqlCommand($"DROP TABLE IF EXISTS \"{tableName}\""));
}
protected override IList <DbCommand> MakeCreateTable(EntityModel model, Design.IDesignContext ctx)
{
var tableName = model.GetSqlTableName(false, ctx);
//List<DbCommand> funcCmds = new List<DbCommand>();
List <string> fks = new List <string>(); //引用外键集合
var sb = StringBuilderCache.Acquire();
//Build Create Table
sb.Append($"CREATE TABLE \"{tableName}\" (");
foreach (var mm in model.Members)
{
if (mm.Type == EntityMemberType.DataField)
{
BuildFieldDefine((DataFieldModel)mm, sb, false);
sb.Append(',');
}
else if (mm.Type == EntityMemberType.EntityRef)
{
var rm = (EntityRefModel)mm;
if (!rm.IsAggregationRef) //只有非聚合引合创建外键
{
fks.Add(BuildForeignKey(rm, ctx, tableName));
//考虑旧实现CreateGetTreeNodeChildsDbFuncCommand
}
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append(");");
//Build PrimaryKey
if (model.SqlStoreOptions.HasPrimaryKeys)
{
//使用模型标识作为PK名称以避免重命名影响
sb.AppendLine();
sb.Append($"ALTER TABLE \"{tableName}\" ADD CONSTRAINT \"PK_{model.Id}\"");
sb.Append(" PRIMARY KEY (");
foreach (var pk in model.SqlStoreOptions.PrimaryKeys)
{
var mm = (DataFieldModel)model.GetMember(pk.MemberId, true);
sb.Append($"\"{mm.SqlColName}\",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(");");
}
//加入EntityRef引用外键
sb.AppendLine();
for (int i = 0; i < fks.Count; i++)
{
sb.AppendLine(fks[i]);
}
var res = new List <DbCommand>();
res.Add(new NpgsqlCommand(StringBuilderCache.GetStringAndRelease(sb)));
//Build Indexes
BuildIndexes(model, res, tableName);
return(res);
}
