/// <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); }