private string GetDatabaseObjectScripts(DatabaseObject dbObj, bool isSource)
{
if (dbObj == null)
{
return(string.Empty);
}
DbScriptGenerator scriptGenerator = isSource ? this.sourceScriptGenerator : this.targetScriptGenerator;
if (dbObj is Table table)
{
SchemaInfo schemaInfo = isSource ? this.sourceSchemaInfo : this.targetSchemaInfo;
IEnumerable <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.Owner == table.Owner && item.TableName == table.Name).OrderBy(item => item.Name);
TablePrimaryKey tablePrimaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.Owner == table.Owner && item.TableName == table.Name);
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.Owner == table.Owner && item.TableName == table.Name);
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.Owner == table.Owner && item.TableName == table.Name).OrderBy(item => item.Name);
IEnumerable <TableConstraint> constraints = schemaInfo.TableConstraints.Where(item => item.Owner == table.Owner && item.TableName == table.Name);
return(scriptGenerator.AddTable(table, columns, tablePrimaryKey, foreignKeys, indexes, constraints).ToString());
}
else
{
return(scriptGenerator.Add(dbObj).Content);
}
}
public static List <TablePrimaryKey> GetTablePrimaryKeys(List <TablePrimaryKeyItem> primaryKeyItems)
{
List <TablePrimaryKey> primaryKeys = new List <TablePrimaryKey>();
var groups = primaryKeyItems.GroupBy(item => new { item.Owner, item.TableName, item.Name, item.Clustered, item.Comment });
foreach (var group in groups)
{
TablePrimaryKey primaryKey = new TablePrimaryKey()
{
Owner = group.Key.Owner,
TableName = group.Key.TableName,
Name = group.Key.Name,
Clustered = group.Key.Clustered,
Comment = group.Key.Comment
};
primaryKey.Columns.AddRange(group.Select(item => new IndexColumn()
{
ColumnName = item.ColumnName, IsDesc = item.IsDesc
}));
primaryKeys.Add(primaryKey);
}
return(primaryKeys);
}
public override Script AddPrimaryKey(TablePrimaryKey primaryKey)
{
string script =
$@"ALTER TABLE {this.GetQuotedFullTableName(primaryKey)} ADD CONSTRAINT
{this.GetQuotedString(primaryKey.Name)} PRIMARY KEY {(primaryKey.Clustered ? "CLUSTERED" : "NONCLUSTERED")}
(
{string.Join(",", primaryKey.Columns.Select(item => $"{this.GetQuotedString(item.ColumnName)} {(item.IsDesc ? "DESC" : "")}"))}
) WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]";
return(new CreateDbObjectScript <TablePrimaryKey>(script));
}
public override Script AddPrimaryKey(TablePrimaryKey primaryKey)
{
string columnNames = string.Join(",", primaryKey.Columns.Select(item => this.GetQuotedString(item.ColumnName)));
string sql = $"ALTER TABLE {this.GetQuotedString(primaryKey.TableName)} ADD CONSTRAINT { this.GetQuotedString(this.GetRestrictedLengthName(primaryKey.Name))} PRIMARY KEY ({columnNames})";
if (!string.IsNullOrEmpty(primaryKey.Comment))
{
sql += $" COMMENT '{this.TransferSingleQuotationString(primaryKey.Comment)}'";
}
return(new CreateDbObjectScript <TablePrimaryKey>(sql + this.scriptsDelimiter));
}
public override Script AddPrimaryKey(TablePrimaryKey primaryKey)
{
string sql =
$@"
ALTER TABLE {this.GetQuotedFullTableName(primaryKey)} ADD CONSTRAINT {this.GetQuotedString(primaryKey.Name)} PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKey.Columns.Select(item => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)
USING INDEX
TABLESPACE
{this.dbInterpreter.ConnectionInfo.Database}{this.scriptsDelimiter}";
return(new Script(sql));
}
public override ScriptBuilder GenerateSchemaScripts(SchemaInfo schemaInfo)
{
ScriptBuilder sb = new ScriptBuilder();
#region Function
sb.AppendRange(this.GenerateScriptDbObjectScripts <Function>(schemaInfo.Functions));
#endregion
#region Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, table);
IEnumerable <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.TableName == table.Name).OrderBy(item => item.Order);
TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.TableName == table.Name);
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == table.Name);
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.TableName == table.Name).OrderBy(item => item.Order);
ScriptBuilder sbTable = this.AddTable(table, columns, primaryKey, foreignKeys, indexes, null);
sb.AppendRange(sbTable.Scripts);
this.FeedbackInfo(OperationState.End, table);
}
#endregion
#region View
sb.AppendRange(this.GenerateScriptDbObjectScripts <View>(schemaInfo.Views));
#endregion
#region Trigger
sb.AppendRange(this.GenerateScriptDbObjectScripts <TableTrigger>(schemaInfo.TableTriggers));
#endregion
#region Procedure
sb.AppendRange(this.GenerateScriptDbObjectScripts <Procedure>(schemaInfo.Procedures));
#endregion
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb);
}
private void Menu_add_Click(object sender, EventArgs e)
{
EditRole editRole = new EditRole("新增角色", "", "");
if (editRole.ShowDialog() == DialogResult.OK)
{
this.roleEntity.ID = TablePrimaryKey.InsertRolePID();
this.roleEntity.UserRole = editRole.roleName;
this.roleEntity.Remark = editRole.roleRemark;
this.roleEntity.UpdateDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
var cCount = roleManager.Insert(this.roleEntity);
if (cCount > 0)
{
UserOperateRecord.UpdateOperateRecord($"新增角色-{roleEntity.UserRole}");
QueryRoleInfo();
}
}
}
public List<Script> GetPrimaryKeyAlterScripts(TablePrimaryKey oldPrimaryKey, TablePrimaryKey newPrimaryKey, bool onlyCompareColumns)
{
List<Script> scripts = new List<Script>();
bool primaryKeyChanged = !SchemaInfoHelper.IsPrimaryKeyEquals(oldPrimaryKey, newPrimaryKey, onlyCompareColumns, true);
Action alterPrimaryKey = () =>
{
if (oldPrimaryKey != null)
{
scripts.Add(this.scriptGenerator.DropPrimaryKey(oldPrimaryKey));
}
scripts.Add(this.scriptGenerator.AddPrimaryKey(newPrimaryKey));
if (!string.IsNullOrEmpty(newPrimaryKey.Comment))
{
this.SetTableChildComment(scripts, this.scriptGenerator, newPrimaryKey, true);
}
};
if (primaryKeyChanged)
{
alterPrimaryKey();
}
else if (!ValueHelper.IsStringEquals(oldPrimaryKey?.Comment, newPrimaryKey?.Comment))
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
this.SetTableChildComment(scripts, this.scriptGenerator, newPrimaryKey, string.IsNullOrEmpty(oldPrimaryKey?.Comment));
}
else
{
alterPrimaryKey();
}
}
return scripts;
}
public static bool IsPrimaryKeyEquals(TablePrimaryKey primaryKey1, TablePrimaryKey primaryKey2, bool onlyComapreColums = false, bool excludeComment = true)
{
if (primaryKey1 == null && primaryKey2 == null)
{
return(true);
}
else if ((primaryKey1 != null && primaryKey2 == null) || (primaryKey1 == null && primaryKey2 != null))
{
return(false);
}
else
{
if (IsIndexColumnsEquals(primaryKey1.Columns, primaryKey2.Columns))
{
if (onlyComapreColums)
{
return(true);
}
else
{
if (primaryKey1.Name == primaryKey2.Name && primaryKey1.Clustered == primaryKey2.Clustered &&
(excludeComment || (!excludeComment && ValueHelper.IsStringEquals(primaryKey1.Comment, primaryKey2.Comment)))
)
{
return(true);
}
else
{
return(false);
}
}
}
else
{
return(false);
}
}
}
private void Btn_ok_Click(object sender, EventArgs e)
{
var curRole = this.cob_roleList.Text.Trim();
if (curRole == "")
{
MessageBox.Show("请选择要授权的角色！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//update diclist
FuncLimit funcLimit = new FuncLimit();
funcLimit.UserRole = curRole;
OperatLimit operatLimit = new OperatLimit();
operatLimit.UserRole = curRole;
foreach (var item in this.checkListFuncLimit.Items)
{
switch (item.Text)
{
case FUN_CABLE_LIB_MANAGE_NAME:
funcLimit.CableLib = ConvertState2Dec(item.CheckState);
break;
case FUN_CIRCUIT_TEST_NAME:
funcLimit.ShortCircuitTest = ConvertState2Dec(item.CheckState);
break;
case FUN_CONDUCT_TEST_NAME:
funcLimit.ConductTest = ConvertState2Dec(item.CheckState);
break;
//case FUN_CONNECTOR_LIB_MANAGE_NAME:
// funcLimit.ConnectorLib = ConvertState2Dec(item.CheckState);
// break;
case FUN_DEVICE_CALIBRATE:
funcLimit.DeviceCalibration = ConvertState2Dec(item.CheckState);
break;
case FUN_DEVICE_DEBUG:
funcLimit.DeviceDebug = ConvertState2Dec(item.CheckState);
break;
case FUN_DEVICE_SELF_CHECK:
funcLimit.DeviceSelfCheck = ConvertState2Dec(item.CheckState);
break;
case FUN_ENVIRONMENT_PARAMS_SET:
funcLimit.EnvironmentalParameters = ConvertState2Dec(item.CheckState);
break;
case FUN_EXCUTE_REPORT_NAME:
funcLimit.ExcuteReport = ConvertState2Dec(item.CheckState);
break;
//case FUN_FAULT_QUERY:
// funcLimit.DeviceFaultQuery = ConvertState2Dec(item.CheckState);
// break;
case FUN_INSULATE_TEST_NAME:
funcLimit.InsulateTest = ConvertState2Dec(item.CheckState);
break;
case FUN_INTERFACE_LIB_MANAGE_NAME:
funcLimit.InterfaceLib = ConvertState2Dec(item.CheckState);
break;
case FUN_NEW_PROJECT:
funcLimit.NewProject = ConvertState2Dec(item.CheckState);
break;
case FUN_ONE_KEY_TEST_NAME:
funcLimit.OneKeyTest = ConvertState2Dec(item.CheckState);
break;
//case FUN_OPERAT_RECORD:
// funcLimit.OperatorRecord = ConvertState2Dec(item.CheckState);
// break;
case FUN_PRINT_REPORT:
funcLimit.PrintReport = ConvertState2Dec(item.CheckState);
break;
case FUN_PROBE:
funcLimit.Probe = ConvertState2Dec(item.CheckState);
break;
case FUN_PROJECT_MANAGE:
funcLimit.ProjectManage = ConvertState2Dec(item.CheckState);
break;
case FUN_REPORT_PATH_SET:
funcLimit.ReportConfigPath = ConvertState2Dec(item.CheckState);
break;
case FUN_RESISTAN_COMPENSAT_MANAGE:
funcLimit.ResistanceCompensationManage = ConvertState2Dec(item.CheckState);
break;
case FUN_SAVE_DATA:
funcLimit.SaveTestData = ConvertState2Dec(item.CheckState);
break;
case FUN_SELF_STUDY_NAME:
funcLimit.SelfStudy = ConvertState2Dec(item.CheckState);
break;
case FUN_START_RESISTAN_COMPENSAT:
funcLimit.StartResistanceCompensation = ConvertState2Dec(item.CheckState);
break;
//case FUN_SWITCH_STAND_LIB_MANAGE_NAME:
// funcLimit.SwitchStandLib = ConvertState2Dec(item.CheckState);
// break;
//case FUN_SWITCH_WEAR_LIB_MANAGE_NAME:
// funcLimit.SwitchWearLib = ConvertState2Dec(item.CheckState);
// break;
case FUN_TEST_DATA:
funcLimit.HistoryTestData = ConvertState2Dec(item.CheckState);
break;
}
}
foreach (var item in this.checkListOperatLimit.Items)
{
switch (item.Text)
{
case OPERATE_ADD_CABLE_LIB:
operatLimit.CableLib_add = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_ADD_CONNECTOR:
// operatLimit.ConnectorLib_add = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_ADD_INTER_INFO:
operatLimit.InterfaceLib_add = ConvertState2Dec(item.CheckState);
break;
case OPERATE_ADD_PROJECT:
operatLimit.Project_add = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_ADD_SWITCH_STAND:
// operatLimit.SwitchStandLib_add = ConvertState2Dec(item.CheckState);
// break;
//case OPERATE_ADD_SWITCH_WEAR:
// operatLimit.SwitchWearLib_add = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_DEL_CABLE_LIB:
operatLimit.CableLib_del = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_DEL_CONNECTOR:
// operatLimit.ConnectorLib_del = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_DEL_INTER_INFO:
operatLimit.InterfaceLib_del = ConvertState2Dec(item.CheckState);
break;
case OPERATE_DEL_OPERATE_RECORD:
operatLimit.OperatorRecord_del = ConvertState2Dec(item.CheckState);
break;
case OPERATE_DEL_PROJECT:
operatLimit.Project_del = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_DEL_SWITCH_STAND:
// operatLimit.SwitchStandLib_del = ConvertState2Dec(item.CheckState);
// break;
//case OPERATE_DEL_SWITCH_WEAR:
// operatLimit.SwitchWearLib_del = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_DEL_TEST_DATA:
operatLimit.HistoryTestData_del = ConvertState2Dec(item.CheckState);
break;
case OPERATE_EDIT_CABLE_LIB:
operatLimit.CableLib_edit = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_EDIT_CONNECTOR:
// operatLimit.ConnectorLib_edit = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_EDIT_INTER_INFO:
operatLimit.InterfaceLib_edit = ConvertState2Dec(item.CheckState);
break;
case OPERATE_EDIT_PROJECT:
operatLimit.Project_edit = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_EDIT_SWITCH_STAND:
// operatLimit.SwitchStandLib_edit = ConvertState2Dec(item.CheckState);
// break;
//case OPERATE_EDIT_SWITCH_WEAR:
// operatLimit.SwitchWearLib_edit = ConvertState2Dec(item.CheckState);
// break;
//case OPERATE_EXPORT_CONNECTOR:
// operatLimit.ConnectorLib_export = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_EXPORT_INTER_INFO:
operatLimit.InterfaceLib_export = ConvertState2Dec(item.CheckState);
break;
case OPERATE_EXPORT_OPERATE_RECORD:
operatLimit.OperatorRecord_export = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_EXPORT_SWITCH_STAND:
// operatLimit.SwitchStandLib_export = ConvertState2Dec(item.CheckState);
// break;
//case OPERATE_EXPORT_SWITCH_WEAR:
// operatLimit.SwitchWearLib_export = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_EXPORT_TEST_DATA:
operatLimit.HistoryTestData_export = ConvertState2Dec(item.CheckState);
break;
case OPERATE_EXPORT_CABLE_LIB:
operatLimit.CableLib_export = ConvertState2Dec(item.CheckState);
break;
case OPERATE_QUERY_CABLE_LIB:
operatLimit.CableLib_query = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_QUERY_CONNECTOR_LIB:
// operatLimit.ConnectorLib_query = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_QUERY_INTER_INFO:
operatLimit.InterfaceLib_query = ConvertState2Dec(item.CheckState);
break;
case OPERATE_QUERY_OPERATE_RECORD:
operatLimit.OperatorRecord_query = ConvertState2Dec(item.CheckState);
break;
case OPERATE_QUERY_PROJECT:
operatLimit.Project_query = ConvertState2Dec(item.CheckState);
break;
//case OPERATE_QUERY_SWITCH_STAND:
// operatLimit.SwitchStandLib_query = ConvertState2Dec(item.CheckState);
// break;
//case OPERATE_QUERY_SWITCH_WEAR:
// operatLimit.SwitchWearLib_query = ConvertState2Dec(item.CheckState);
// break;
case OPERATE_QUERY_TEST_DATA:
operatLimit.HistoryTestData_query = ConvertState2Dec(item.CheckState);
break;
}
}
if (IsExistRoleFuncLimit(curRole))
{
funcLimit.ID = GetFuncIDByRole(curRole);
this.funLimitManager.Update(funcLimit);
}
else
{
funcLimit.ID = TablePrimaryKey.InsertFuncLimitPID();
this.funLimitManager.Insert(funcLimit);
}
if (IsExistRoleOperatLimit(curRole))
{
operatLimit.ID = GetOperatIDByRole(curRole);
var ups = this.operatLimitManager.Update(operatLimit);
if (ups > 0)
{
MessageBox.Show("更新成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
operatLimit.ID = TablePrimaryKey.InsertOperateLimitPID();
var ins = this.operatLimitManager.Insert(operatLimit);
if (ins > 0)
{
MessageBox.Show("更新成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
this.Close();
}
public async Task <List <Script> > GenerateTableChangedScripts(SchemaInfo schemaInfo, DbDifference difference, string targetDbOwner)
{
List <Script> scripts = new List <Script>();
DbDifferenceType diffType = difference.DifferenceType;
Table sourceTable = difference.Source as Table;
Table targetTable = difference.Target as Table;
if (diffType == DbDifferenceType.Added)
{
List <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.Owner == sourceTable.Owner && item.TableName == sourceTable.Name).OrderBy(item => item.Order).ToList();
TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.Owner == sourceTable.Owner && item.TableName == sourceTable.Name);
List <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.Owner == sourceTable.Owner && item.TableName == sourceTable.Name).ToList();
List <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.Owner == sourceTable.Owner && item.TableName == sourceTable.Name).OrderBy(item => item.Order).ToList();
List <TableConstraint> constraints = schemaInfo.TableConstraints.Where(item => item.Owner == sourceTable.Owner && item.TableName == sourceTable.Name).ToList();
this.ChangeOwner(columns, targetDbOwner);
primaryKey = this.CloneDbObject(primaryKey, targetDbOwner);
this.ChangeOwner(foreignKeys, targetDbOwner);
this.ChangeOwner(indexes, targetDbOwner);
this.ChangeOwner(constraints, targetDbOwner);
scripts.AddRange(this.targetScriptGenerator.AddTable(sourceTable, columns, primaryKey, foreignKeys, indexes, constraints).Scripts);
}
else if (diffType == DbDifferenceType.Deleted)
{
scripts.Add(this.targetScriptGenerator.DropTable(targetTable));
}
else if (diffType == DbDifferenceType.Modified)
{
if (!ValueHelper.IsStringEquals(sourceTable.Comment, targetTable.Comment))
{
scripts.Add(targetScriptGenerator.SetTableComment(sourceTable, string.IsNullOrEmpty(targetTable.Comment)));
}
foreach (DbDifference subDiff in difference.SubDifferences)
{
DbDifferenceType subDiffType = subDiff.DifferenceType;
if (subDiffType == DbDifferenceType.None)
{
continue;
}
DatabaseObjectType subDbObjectType = subDiff.DatabaseObjectType;
switch (subDbObjectType)
{
case DatabaseObjectType.TableColumn:
case DatabaseObjectType.TablePrimaryKey:
case DatabaseObjectType.TableForeignKey:
case DatabaseObjectType.TableIndex:
case DatabaseObjectType.TableConstraint:
scripts.AddRange(await this.GenerateTableChildChangedScripts(subDiff));
break;
case DatabaseObjectType.TableTrigger:
scripts.AddRange(this.GenereateScriptDbObjectChangedScripts(subDiff, targetDbOwner));
break;
}
}
}
return(scripts);
}
public override ScriptBuilder GenerateSchemaScripts(SchemaInfo schemaInfo)
{
ScriptBuilder sb = new ScriptBuilder();
#region User Defined Type
List <string> userTypeNames = new List <string>();
foreach (UserDefinedType userDefinedType in schemaInfo.UserDefinedTypes)
{
this.FeedbackInfo(OperationState.Begin, userDefinedType);
string userTypeName = userDefinedType.Name;
if (userTypeNames.Contains(userTypeName))
{
userTypeName += "_" + userDefinedType.AttrName;
}
TableColumn column = new TableColumn()
{
DataType = userDefinedType.Type, MaxLength = userDefinedType.MaxLength, Precision = userDefinedType.Precision, Scale = userDefinedType.Scale
};
string dataLength = this.dbInterpreter.GetColumnDataLength(column);
string script = $@"CREATE TYPE {this.GetQuotedString(userDefinedType.Owner)}.{this.GetQuotedString(userTypeName)} FROM {this.GetQuotedString(userDefinedType.Type)}{(dataLength == "" ? "" : "(" + dataLength + ")")} {(userDefinedType.IsRequired ? "NOT NULL" : "NULL")};";
sb.AppendLine(new CreateDbObjectScript <UserDefinedType>(script));
sb.AppendLine(new SpliterScript(this.scriptsDelimiter));
userTypeNames.Add(userDefinedType.Name);
this.FeedbackInfo(OperationState.End, userDefinedType);
}
#endregion
#region Function
sb.AppendRange(this.GenerateScriptDbObjectScripts <Function>(schemaInfo.Functions));
#endregion
#region Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, table);
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
IEnumerable <TableColumn> tableColumns = schemaInfo.TableColumns.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order);
bool hasBigDataType = tableColumns.Any(item => this.IsBigDataType(item));
#region Create Table
string existsClause = $"IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name='{(table.Name)}')";
string tableScript =
$@"
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
{(this.dbInterpreter.NotCreateIfExists ? existsClause : "")}
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.dbInterpreter.ParseColumn(table, item)))}
) ON [PRIMARY]{(hasBigDataType ? " TEXTIMAGE_ON [PRIMARY]" : "")}" + ";";
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
#region Comment
if (!string.IsNullOrEmpty(table.Comment))
{
sb.AppendLine(this.SetTableComment(table));
}
foreach (TableColumn column in tableColumns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine(this.SetTableColumnComment(table, column, true));
}
#endregion
#region Default Value
if (this.option.TableScriptsGenerateOption.GenerateDefaultValue)
{
IEnumerable <TableColumn> defaultValueColumns = schemaInfo.TableColumns.Where(item => item.Owner == table.Owner && item.TableName == tableName && !string.IsNullOrEmpty(item.DefaultValue));
foreach (TableColumn column in defaultValueColumns)
{
sb.AppendLine(this.AddDefaultValueConstraint(column));
}
}
#endregion
TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.Owner == table.Owner && item.TableName == tableName);
#region Primary Key
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKey != null)
{
sb.AppendLine(this.AddPrimaryKey(primaryKey));
if (!string.IsNullOrEmpty(primaryKey.Comment))
{
sb.AppendLine(this.SetTableChildComment(primaryKey, true));
}
}
#endregion
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey)
{
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.Owner == table.Owner && item.TableName == tableName);
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
if (!string.IsNullOrEmpty(foreignKey.Comment))
{
sb.AppendLine(this.SetTableChildComment(foreignKey, true));
}
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex)
{
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order);
foreach (TableIndex index in indexes)
{
sb.AppendLine(this.AddIndex(index));
if (!string.IsNullOrEmpty(index.Comment))
{
sb.AppendLine(this.SetTableChildComment(index, true));
}
}
}
#endregion
#region Constraint
if (this.option.TableScriptsGenerateOption.GenerateConstraint)
{
var constraints = schemaInfo.TableConstraints.Where(item => item.Owner == table.Owner && item.TableName == tableName);
foreach (TableConstraint constraint in constraints)
{
sb.AppendLine(this.AddCheckConstraint(constraint));
if (!string.IsNullOrEmpty(constraint.Comment))
{
sb.AppendLine(this.SetTableChildComment(constraint, true));
}
}
}
#endregion
sb.Append(new SpliterScript(this.scriptsDelimiter));
this.FeedbackInfo(OperationState.End, table);
}
#endregion
#region View
sb.AppendRange(this.GenerateScriptDbObjectScripts <View>(schemaInfo.Views));
#endregion
#region Trigger
sb.AppendRange(this.GenerateScriptDbObjectScripts <TableTrigger>(schemaInfo.TableTriggers));
#endregion
#region Procedure
sb.AppendRange(this.GenerateScriptDbObjectScripts <Procedure>(schemaInfo.Procedures));
#endregion
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString().Trim(), GenerateScriptMode.Schema, true);
}
return(sb);
}
public async Task <ContentSaveResult> GenerateChangeScripts(SchemaDesignerInfo schemaDesignerInfo, bool isNew)
{
string validateMsg;
Table table = null;
try
{
bool isValid = this.ValidateModel(schemaDesignerInfo, out validateMsg);
if (!isValid)
{
return(this.GetFaultSaveResult(validateMsg));
}
TableDesignerGenerateScriptsData scriptsData = new TableDesignerGenerateScriptsData();
SchemaInfo schemaInfo = this.GetSchemaInfo(schemaDesignerInfo);
table = schemaInfo.Tables.First();
scriptsData.Table = table;
DbScriptGenerator scriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.dbInterpreter);
List <Script> scripts = new List <Script>();
if (isNew)
{
ScriptBuilder scriptBuilder = scriptGenerator.GenerateSchemaScripts(schemaInfo);
scripts = scriptBuilder.Scripts;
}
else
{
#region Alter Table
scripts = new List <Script>();
TableDesignerInfo tableDesignerInfo = schemaDesignerInfo.TableDesignerInfo;
SchemaInfoFilter filter = new SchemaInfoFilter()
{
Strict = true
};
filter.TableNames = new string[] { tableDesignerInfo.OldName };
filter.DatabaseObjectType = DatabaseObjectType.Table
| DatabaseObjectType.TableColumn
| DatabaseObjectType.TablePrimaryKey
| DatabaseObjectType.TableForeignKey
| DatabaseObjectType.TableIndex
| DatabaseObjectType.TableConstraint;
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
this.dbInterpreter.Option.IncludePrimaryKeyWhenGetTableIndex = true;
}
SchemaInfo oldSchemaInfo = await this.dbInterpreter.GetSchemaInfoAsync(filter);
Table oldTable = oldSchemaInfo.Tables.FirstOrDefault();
if (oldTable == null)
{
return(this.GetFaultSaveResult($"Table \"{tableDesignerInfo.OldName}\" is not existed"));
}
if (tableDesignerInfo.OldName != tableDesignerInfo.Name)
{
scripts.Add(scriptGenerator.RenameTable(new Table()
{
Owner = tableDesignerInfo.Owner, Name = tableDesignerInfo.OldName
}, tableDesignerInfo.Name));
}
if (!this.IsStringEquals(tableDesignerInfo.Comment, oldTable.Comment))
{
oldTable.Comment = tableDesignerInfo.Comment;
scripts.Add(scriptGenerator.SetTableComment(oldTable, string.IsNullOrEmpty(oldTable.Comment)));
}
#region Columns
List <TableColumnDesingerInfo> columnDesingerInfos = schemaDesignerInfo.TableColumnDesingerInfos;
List <TableColumn> oldColumns = oldSchemaInfo.TableColumns;
List <TableDefaultValueConstraint> defaultValueConstraints = null;
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
defaultValueConstraints = await(this.dbInterpreter as SqlServerInterpreter).GetTableDefautValueConstraintsAsync(filter);
}
foreach (TableColumnDesingerInfo columnDesignerInfo in columnDesingerInfos)
{
TableColumn oldColumn = oldColumns.FirstOrDefault(item => item.Name.ToLower() == columnDesignerInfo.Name.ToLower());
TableColumn newColumn = schemaInfo.TableColumns.FirstOrDefault(item => item.Name == columnDesignerInfo.Name);
if (oldColumn == null)
{
scripts.Add(scriptGenerator.AddTableColumn(table, newColumn));
}
else
{
if (!this.IsValueEqualsIgnoreCase(columnDesignerInfo.OldName, columnDesignerInfo.Name))
{
scripts.Add(scriptGenerator.RenameTableColumn(table, oldColumn, newColumn.Name));
}
bool isDefaultValueEquals = this.IsStringEquals(ValueHelper.GetTrimedDefaultValue(oldColumn.DefaultValue), newColumn.DefaultValue);
if (!SchemaInfoHelper.IsTableColumnEquals(this.dbInterpreter.DatabaseType, oldColumn, newColumn) ||
!isDefaultValueEquals)
{
if (!isDefaultValueEquals)
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
SqlServerScriptGenerator sqlServerScriptGenerator = scriptGenerator as SqlServerScriptGenerator;
TableDefaultValueConstraint defaultValueConstraint = defaultValueConstraints?.FirstOrDefault(item => item.Owner == oldTable.Owner && item.TableName == oldTable.Name && item.ColumnName == oldColumn.Name);
if (defaultValueConstraint != null)
{
scripts.Add(sqlServerScriptGenerator.DropDefaultValueConstraint(defaultValueConstraint));
}
scripts.Add(sqlServerScriptGenerator.AddDefaultValueConstraint(newColumn));
}
}
Script alterColumnScript = scriptGenerator.AlterTableColumn(table, newColumn);
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
if (!oldColumn.IsNullable && !newColumn.IsNullable)
{
alterColumnScript.Content = Regex.Replace(alterColumnScript.Content, "NOT NULL", "", RegexOptions.IgnoreCase);
}
else if (oldColumn.IsNullable && newColumn.IsNullable)
{
alterColumnScript.Content = Regex.Replace(alterColumnScript.Content, "NULL", "", RegexOptions.IgnoreCase);
}
}
scripts.Add(alterColumnScript);
}
else if (!this.IsStringEquals(columnDesignerInfo.Comment, oldColumn.Comment))
{
scripts.Add(scriptGenerator.SetTableColumnComment(table, newColumn, string.IsNullOrEmpty(oldColumn.Comment)));
}
}
}
foreach (TableColumn oldColumn in oldColumns)
{
if (!columnDesingerInfos.Any(item => item.Name == oldColumn.Name))
{
scripts.Add(scriptGenerator.DropTableColumn(oldColumn));
}
}
#endregion
#region Primary Key
TablePrimaryKey oldPrimaryKey = oldSchemaInfo.TablePrimaryKeys.FirstOrDefault();
TablePrimaryKey newPrimaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault();
bool primaryKeyChanged = !SchemaInfoHelper.IsPrimaryKeyEquals(oldPrimaryKey, newPrimaryKey, schemaDesignerInfo.IgnoreTableIndex, true);
Action alterPrimaryKey = () =>
{
if (oldPrimaryKey != null)
{
scripts.Add(scriptGenerator.DropPrimaryKey(oldPrimaryKey));
}
scripts.Add(scriptGenerator.AddPrimaryKey(newPrimaryKey));
if (!string.IsNullOrEmpty(newPrimaryKey.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newPrimaryKey, true);
}
};
if (primaryKeyChanged)
{
alterPrimaryKey();
}
else if (!ValueHelper.IsStringEquals(oldPrimaryKey?.Comment, newPrimaryKey?.Comment))
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
this.SetTableChildComment(scripts, scriptGenerator, newPrimaryKey, string.IsNullOrEmpty(oldPrimaryKey?.Comment));
}
else
{
alterPrimaryKey();
}
}
#endregion
#region Index
if (!schemaDesignerInfo.IgnoreTableIndex)
{
IEnumerable <TableIndex> oldIndexes = oldSchemaInfo.TableIndexes.Where(item => !item.IsPrimary);
IEnumerable <TableIndexDesignerInfo> indexDesignerInfos = schemaDesignerInfo.TableIndexDesingerInfos.Where(item => !item.IsPrimary);
foreach (TableIndexDesignerInfo indexDesignerInfo in indexDesignerInfos)
{
TableIndex newIndex = schemaInfo.TableIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.Name);
TableIndex oldIndex = oldIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldName, indexDesignerInfo.Name) &&
this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldType, indexDesignerInfo.Type)
)
{
if (oldIndex != null && this.IsStringEquals(oldIndex.Comment, newIndex.Comment) && SchemaInfoHelper.IsIndexColumnsEquals(oldIndex.Columns, newIndex.Columns))
{
continue;
}
}
if (oldIndex != null)
{
scripts.Add(scriptGenerator.DropIndex(oldIndex));
}
scripts.Add(scriptGenerator.AddIndex(newIndex));
if (!string.IsNullOrEmpty(newIndex.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newIndex, true);
}
}
foreach (TableIndex oldIndex in oldIndexes)
{
if (!indexDesignerInfos.Any(item => item.Name == oldIndex.Name))
{
scripts.Add(scriptGenerator.DropIndex(oldIndex));
}
}
}
#endregion
#region Foreign Key
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
List <TableForeignKey> oldForeignKeys = oldSchemaInfo.TableForeignKeys;
IEnumerable <TableForeignKeyDesignerInfo> foreignKeyDesignerInfos = schemaDesignerInfo.TableForeignKeyDesignerInfos;
foreach (TableForeignKeyDesignerInfo foreignKeyDesignerInfo in foreignKeyDesignerInfos)
{
TableForeignKey newForeignKey = schemaInfo.TableForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.Name);
TableForeignKey oldForeignKey = oldForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(foreignKeyDesignerInfo.OldName, foreignKeyDesignerInfo.Name) &&
foreignKeyDesignerInfo.UpdateCascade == oldForeignKey.UpdateCascade &&
foreignKeyDesignerInfo.DeleteCascade == oldForeignKey.DeleteCascade)
{
if (oldForeignKey != null && this.IsStringEquals(oldForeignKey.Comment, newForeignKey.Comment) &&
SchemaInfoHelper.IsForeignKeyColumnsEquals(oldForeignKey.Columns, newForeignKey.Columns))
{
continue;
}
}
if (oldForeignKey != null)
{
scripts.Add(scriptGenerator.DropForeignKey(oldForeignKey));
}
scripts.Add(scriptGenerator.AddForeignKey(newForeignKey));
if (!string.IsNullOrEmpty(newForeignKey.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newForeignKey, true);
}
}
foreach (TableForeignKey oldForeignKey in oldForeignKeys)
{
if (!foreignKeyDesignerInfos.Any(item => item.Name == oldForeignKey.Name))
{
scripts.Add(scriptGenerator.DropForeignKey(oldForeignKey));
}
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
List <TableConstraint> oldConstraints = oldSchemaInfo.TableConstraints;
IEnumerable <TableConstraintDesignerInfo> constraintDesignerInfos = schemaDesignerInfo.TableConstraintDesignerInfos;
foreach (TableConstraintDesignerInfo constraintDesignerInfo in constraintDesignerInfos)
{
TableConstraint newConstraint = schemaInfo.TableConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.Name);
TableConstraint oldConstraint = oldConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(constraintDesignerInfo.OldName, constraintDesignerInfo.Name))
{
if (oldConstraint != null && this.IsStringEquals(oldConstraint.Comment, newConstraint.Comment))
{
continue;
}
}
if (oldConstraint != null)
{
scripts.Add(scriptGenerator.DropCheckConstraint(oldConstraint));
}
scripts.Add(scriptGenerator.AddCheckConstraint(newConstraint));
if (!string.IsNullOrEmpty(newConstraint.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newConstraint, true);
}
}
foreach (TableConstraint oldConstraint in oldConstraints)
{
if (!constraintDesignerInfos.Any(item => item.Name == oldConstraint.Name))
{
scripts.Add(scriptGenerator.DropCheckConstraint(oldConstraint));
}
}
}
#endregion
#endregion
}
scriptsData.Scripts.AddRange(scripts);
return(new ContentSaveResult()
{
IsOK = true, ResultData = scriptsData
});
}
catch (Exception ex)
{
return(this.GetFaultSaveResult(ExceptionHelper.GetExceptionDetails(ex)));
}
}
public override Script DropPrimaryKey(TablePrimaryKey primaryKey)
{
return(new DropDbObjectScript <TablePrimaryKey>(this.GetDropConstraintSql(primaryKey)));
}
public async Task<ContentSaveResult> GenerateChangeScripts(SchemaDesignerInfo schemaDesignerInfo, bool isNew)
{
string validateMsg;
Table table = null;
try
{
bool isValid = this.ValidateModel(schemaDesignerInfo, out validateMsg);
if (!isValid)
{
return this.GetFaultSaveResult(validateMsg);
}
TableDesignerGenerateScriptsData scriptsData = new TableDesignerGenerateScriptsData();
SchemaInfo schemaInfo = this.GetSchemaInfo(schemaDesignerInfo);
table = schemaInfo.Tables.First();
scriptsData.Table = table;
List<Script> scripts = new List<Script>();
if (isNew)
{
ScriptBuilder scriptBuilder = this.scriptGenerator.GenerateSchemaScripts(schemaInfo);
scripts.AddRange(scriptBuilder.Scripts);
}
else
{
#region Alter Table
TableDesignerInfo tableDesignerInfo = schemaDesignerInfo.TableDesignerInfo;
SchemaInfoFilter filter = new SchemaInfoFilter() { Strict = true };
filter.TableNames = new string[] { tableDesignerInfo.OldName };
filter.DatabaseObjectType = DatabaseObjectType.Table
| DatabaseObjectType.TableColumn
| DatabaseObjectType.TablePrimaryKey
| DatabaseObjectType.TableForeignKey
| DatabaseObjectType.TableIndex
| DatabaseObjectType.TableConstraint;
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
this.dbInterpreter.Option.IncludePrimaryKeyWhenGetTableIndex = true;
}
SchemaInfo oldSchemaInfo = await this.dbInterpreter.GetSchemaInfoAsync(filter);
Table oldTable = oldSchemaInfo.Tables.FirstOrDefault();
if (oldTable == null)
{
return this.GetFaultSaveResult($"Table \"{tableDesignerInfo.OldName}\" is not existed");
}
if (tableDesignerInfo.OldName != tableDesignerInfo.Name)
{
scripts.Add(this.scriptGenerator.RenameTable(new Table() { Owner = tableDesignerInfo.Owner, Name = tableDesignerInfo.OldName }, tableDesignerInfo.Name));
}
if (!this.IsStringEquals(tableDesignerInfo.Comment, oldTable.Comment))
{
oldTable.Comment = tableDesignerInfo.Comment;
scripts.Add(this.scriptGenerator.SetTableComment(oldTable, string.IsNullOrEmpty(oldTable.Comment)));
}
#region Columns
List<TableColumnDesingerInfo> columnDesingerInfos = schemaDesignerInfo.TableColumnDesingerInfos;
List<TableColumn> oldColumns = oldSchemaInfo.TableColumns;
List<TableDefaultValueConstraint> defaultValueConstraints = await this.GetTableDefaultConstraints(filter);
foreach (TableColumnDesingerInfo columnDesignerInfo in columnDesingerInfos)
{
TableColumn oldColumn = oldColumns.FirstOrDefault(item => item.Name.ToLower() == columnDesignerInfo.Name.ToLower());
TableColumn newColumn = schemaInfo.TableColumns.FirstOrDefault(item => item.Name == columnDesignerInfo.Name);
if (oldColumn == null)
{
scripts.Add(this.scriptGenerator.AddTableColumn(table, newColumn));
}
else
{
if (!this.IsValueEqualsIgnoreCase(columnDesignerInfo.OldName, columnDesignerInfo.Name))
{
scripts.Add(this.scriptGenerator.RenameTableColumn(table, oldColumn, newColumn.Name));
}
scripts.AddRange(this.GetColumnAlterScripts(oldTable, table, oldColumn, newColumn, defaultValueConstraints));
}
}
foreach (TableColumn oldColumn in oldColumns)
{
if (!columnDesingerInfos.Any(item => item.Name == oldColumn.Name))
{
scripts.Add(this.scriptGenerator.DropTableColumn(oldColumn));
}
}
#endregion
#region Primary Key
TablePrimaryKey oldPrimaryKey = oldSchemaInfo.TablePrimaryKeys.FirstOrDefault();
TablePrimaryKey newPrimaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault();
scripts.AddRange(this.GetPrimaryKeyAlterScripts(oldPrimaryKey, newPrimaryKey, schemaDesignerInfo.IgnoreTableIndex));
#endregion
#region Index
if (!schemaDesignerInfo.IgnoreTableIndex)
{
IEnumerable<TableIndex> oldIndexes = oldSchemaInfo.TableIndexes.Where(item => !item.IsPrimary);
IEnumerable<TableIndexDesignerInfo> indexDesignerInfos = schemaDesignerInfo.TableIndexDesingerInfos.Where(item => !item.IsPrimary);
foreach (TableIndexDesignerInfo indexDesignerInfo in indexDesignerInfos)
{
TableIndex newIndex = schemaInfo.TableIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.Name);
TableIndex oldIndex = oldIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldName, indexDesignerInfo.Name)
&& this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldType, indexDesignerInfo.Type)
)
{
if (oldIndex != null && this.IsStringEquals(oldIndex.Comment, newIndex.Comment) && SchemaInfoHelper.IsIndexColumnsEquals(oldIndex.Columns, newIndex.Columns))
{
continue;
}
}
scripts.AddRange(this.GetIndexAlterScripts(oldIndex, newIndex));
}
foreach (TableIndex oldIndex in oldIndexes)
{
if (!indexDesignerInfos.Any(item => item.Name == oldIndex.Name))
{
scripts.Add(this.scriptGenerator.DropIndex(oldIndex));
}
}
}
#endregion
#region Foreign Key
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
List<TableForeignKey> oldForeignKeys = oldSchemaInfo.TableForeignKeys;
IEnumerable<TableForeignKeyDesignerInfo> foreignKeyDesignerInfos = schemaDesignerInfo.TableForeignKeyDesignerInfos;
foreach (TableForeignKeyDesignerInfo foreignKeyDesignerInfo in foreignKeyDesignerInfos)
{
TableForeignKey newForeignKey = schemaInfo.TableForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.Name);
TableForeignKey oldForeignKey = oldForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(foreignKeyDesignerInfo.OldName, foreignKeyDesignerInfo.Name) &&
foreignKeyDesignerInfo.UpdateCascade == oldForeignKey.UpdateCascade &&
foreignKeyDesignerInfo.DeleteCascade == oldForeignKey.DeleteCascade)
{
if (oldForeignKey != null && this.IsStringEquals(oldForeignKey.Comment, newForeignKey.Comment)
&& SchemaInfoHelper.IsForeignKeyColumnsEquals(oldForeignKey.Columns, newForeignKey.Columns))
{
continue;
}
}
scripts.AddRange(this.GetForeignKeyAlterScripts(oldForeignKey, newForeignKey));
}
foreach (TableForeignKey oldForeignKey in oldForeignKeys)
{
if (!foreignKeyDesignerInfos.Any(item => item.Name == oldForeignKey.Name))
{
scripts.Add(this.scriptGenerator.DropForeignKey(oldForeignKey));
}
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
List<TableConstraint> oldConstraints = oldSchemaInfo.TableConstraints;
IEnumerable<TableConstraintDesignerInfo> constraintDesignerInfos = schemaDesignerInfo.TableConstraintDesignerInfos;
foreach (TableConstraintDesignerInfo constraintDesignerInfo in constraintDesignerInfos)
{
TableConstraint newConstraint = schemaInfo.TableConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.Name);
TableConstraint oldConstraint = oldConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(constraintDesignerInfo.OldName, constraintDesignerInfo.Name))
{
if (oldConstraint != null && this.IsStringEquals(oldConstraint.Comment, newConstraint.Comment))
{
continue;
}
}
scripts.AddRange(this.GetConstraintAlterScripts(oldConstraint, newConstraint));
}
foreach (TableConstraint oldConstraint in oldConstraints)
{
if (!constraintDesignerInfos.Any(item => item.Name == oldConstraint.Name))
{
scripts.Add(this.scriptGenerator.DropCheckConstraint(oldConstraint));
}
}
}
#endregion
#endregion
}
scriptsData.Scripts.AddRange(scripts);
return new ContentSaveResult() { IsOK = true, ResultData = scriptsData };
}
catch (Exception ex)
{
return this.GetFaultSaveResult(ExceptionHelper.GetExceptionDetails(ex));
}
}
public override ScriptBuilder AddTable(Table table, IEnumerable <TableColumn> columns,
TablePrimaryKey primaryKey,
IEnumerable <TableForeignKey> foreignKeys,
IEnumerable <TableIndex> indexes,
IEnumerable <TableConstraint> constraints)
{
ScriptBuilder sb = new ScriptBuilder();
MySqlInterpreter mySqlInterpreter = this.dbInterpreter as MySqlInterpreter;
string dbCharSet = mySqlInterpreter.DbCharset;
string notCreateIfExistsClause = mySqlInterpreter.NotCreateIfExistsClause;
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
this.RestrictColumnLength(columns, primaryKey?.Columns);
this.RestrictColumnLength(columns, foreignKeys.SelectMany(item => item.Columns));
this.RestrictColumnLength(columns, indexes.SelectMany(item => item.Columns));
string primaryKeyColumns = "";
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKey != null)
{
primaryKeyColumns =
$@"
,PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKey.Columns.Select(item => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)";
}
#region Table
string tableScript =
$@"
CREATE TABLE {notCreateIfExistsClause} {quotedTableName}(
{string.Join("," + Environment.NewLine, columns.Select(item => this.dbInterpreter.ParseColumn(table, item)))}{primaryKeyColumns}
){(!string.IsNullOrEmpty(table.Comment) ? ($"comment='{this.dbInterpreter.ReplaceSplitChar(ValueHelper.TransferSingleQuotation(table.Comment))}'") : "")}
DEFAULT CHARSET={dbCharSet}" + this.scriptsDelimiter;
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
//#region Primary Key
//if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
//{
// TablePrimaryKey primaryKey = primaryKeys.FirstOrDefault();
// if (primaryKey != null)
// {
// sb.AppendLine(this.AddPrimaryKey(primaryKey));
// }
//}
//#endregion
List <string> foreignKeysLines = new List <string>();
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey)
{
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex)
{
foreach (TableIndex index in indexes)
{
sb.AppendLine(this.AddIndex(index));
}
}
#endregion
sb.AppendLine();
return(sb);
}
public override ScriptBuilder GenerateSchemaScripts(SchemaInfo schemaInfo)
{
ScriptBuilder sb = new ScriptBuilder();
#region User Defined Type
List <string> userTypeNames = new List <string>();
foreach (UserDefinedType userDefinedType in schemaInfo.UserDefinedTypes)
{
this.FeedbackInfo(OperationState.Begin, userDefinedType);
string userTypeName = userDefinedType.Name;
if (userTypeNames.Contains(userTypeName))
{
userTypeName += "_" + userDefinedType.AttrName;
}
userDefinedType.Name = userTypeName;
sb.AppendLine(this.AddUserDefinedType(userDefinedType));
sb.AppendLine(new SpliterScript(this.scriptsDelimiter));
userTypeNames.Add(userDefinedType.Name);
this.FeedbackInfo(OperationState.End, userDefinedType);
}
#endregion
#region Function
sb.AppendRange(this.GenerateScriptDbObjectScripts <Function>(schemaInfo.Functions));
#endregion
#region Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, table);
IEnumerable <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.Owner == table.Owner && item.TableName == table.Name).OrderBy(item => item.Order);
TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.Owner == table.Owner && item.TableName == table.Name);
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.Owner == table.Owner && item.TableName == table.Name);
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.Owner == table.Owner && item.TableName == table.Name).OrderBy(item => item.Order);
IEnumerable <TableConstraint> constraints = schemaInfo.TableConstraints.Where(item => item.Owner == table.Owner && item.TableName == table.Name);
ScriptBuilder sbTable = this.AddTable(table, columns, primaryKey, foreignKeys, indexes, constraints);
sb.AppendRange(sbTable.Scripts);
this.FeedbackInfo(OperationState.End, table);
}
#endregion
#region View
sb.AppendRange(this.GenerateScriptDbObjectScripts <View>(schemaInfo.Views));
#endregion
#region Trigger
sb.AppendRange(this.GenerateScriptDbObjectScripts <TableTrigger>(schemaInfo.TableTriggers));
#endregion
#region Procedure
sb.AppendRange(this.GenerateScriptDbObjectScripts <Procedure>(schemaInfo.Procedures));
#endregion
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString().Trim(), GenerateScriptMode.Schema, true);
}
return(sb);
}
public override ScriptBuilder GenerateSchemaScripts(SchemaInfo schemaInfo)
{
ScriptBuilder sb = new ScriptBuilder();
MySqlInterpreter mySqlInterpreter = this.dbInterpreter as MySqlInterpreter;
string dbCharSet = mySqlInterpreter.DbCharset;
string notCreateIfExistsClause = mySqlInterpreter.NotCreateIfExistsClause;
#region Function
sb.AppendRange(this.GenerateScriptDbObjectScripts <Function>(schemaInfo.Functions));
#endregion
#region Create Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, table);
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
IEnumerable <TableColumn> tableColumns = schemaInfo.TableColumns.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
IEnumerable <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.TableName == tableName);
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == tableName);
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
this.RestrictColumnLength(tableColumns, primaryKeys.SelectMany(item => item.Columns));
this.RestrictColumnLength(tableColumns, foreignKeys.SelectMany(item => item.Columns));
this.RestrictColumnLength(tableColumns, indexes.SelectMany(item => item.Columns));
string primaryKeyColumns = "";
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
{
TablePrimaryKey primaryKey = primaryKeys.FirstOrDefault();
primaryKeyColumns =
$@"
,PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKey.Columns.Select(item => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)";
}
#region Table
string tableScript =
$@"
CREATE TABLE {notCreateIfExistsClause} {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.dbInterpreter.ParseColumn(table, item)))}{primaryKeyColumns}
){(!string.IsNullOrEmpty(table.Comment) ? ($"comment='{this.dbInterpreter.ReplaceSplitChar(ValueHelper.TransferSingleQuotation(table.Comment))}'") : "")}
DEFAULT CHARSET={dbCharSet}" + this.scriptsDelimiter;
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
//#region Primary Key
//if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
//{
// TablePrimaryKey primaryKey = primaryKeys.FirstOrDefault();
// if (primaryKey != null)
// {
// sb.AppendLine(this.AddPrimaryKey(primaryKey));
// }
//}
//#endregion
List <string> foreignKeysLines = new List <string>();
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey)
{
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex)
{
foreach (TableIndex index in indexes)
{
sb.AppendLine(this.AddIndex(index));
}
}
#endregion
sb.AppendLine();
this.FeedbackInfo(OperationState.End, table);
}
#endregion
#region View
sb.AppendRange(this.GenerateScriptDbObjectScripts <View>(schemaInfo.Views));
#endregion
#region Trigger
sb.AppendRange(this.GenerateScriptDbObjectScripts <TableTrigger>(schemaInfo.TableTriggers));
#endregion
#region Procedure
sb.AppendRange(this.GenerateScriptDbObjectScripts <Procedure>(schemaInfo.Procedures));
#endregion
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb);
}
public override ScriptBuilder AddTable(Table table, IEnumerable <TableColumn> columns,
TablePrimaryKey primaryKey,
IEnumerable <TableForeignKey> foreignKeys,
IEnumerable <TableIndex> indexes,
IEnumerable <TableConstraint> constraints)
{
ScriptBuilder sb = new ScriptBuilder();
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
bool hasBigDataType = columns.Any(item => this.IsBigDataType(item));
#region Create Table
string existsClause = $"IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name='{(table.Name)}')";
string tableScript =
$@"
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
{(this.dbInterpreter.NotCreateIfExists ? existsClause : "")}
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, columns.Select(item => this.dbInterpreter.ParseColumn(table, item)))}
) ON [PRIMARY]{(hasBigDataType ? " TEXTIMAGE_ON [PRIMARY]" : "")}" + ";";
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
#region Comment
if (!string.IsNullOrEmpty(table.Comment))
{
sb.AppendLine(this.SetTableComment(table));
}
foreach (TableColumn column in columns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine(this.SetTableColumnComment(table, column, true));
}
#endregion
#region Default Value
if (this.option.TableScriptsGenerateOption.GenerateDefaultValue)
{
IEnumerable <TableColumn> defaultValueColumns = columns.Where(item => item.Owner == table.Owner && item.TableName == tableName && !string.IsNullOrEmpty(item.DefaultValue));
foreach (TableColumn column in defaultValueColumns)
{
sb.AppendLine(this.AddDefaultValueConstraint(column));
}
}
#endregion
#region Primary Key
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKey != null)
{
sb.AppendLine(this.AddPrimaryKey(primaryKey));
if (!string.IsNullOrEmpty(primaryKey.Comment))
{
sb.AppendLine(this.SetTableChildComment(primaryKey, true));
}
}
#endregion
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey && foreignKeys != null)
{
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
if (!string.IsNullOrEmpty(foreignKey.Comment))
{
sb.AppendLine(this.SetTableChildComment(foreignKey, true));
}
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex && indexes != null)
{
foreach (TableIndex index in indexes)
{
sb.AppendLine(this.AddIndex(index));
if (!string.IsNullOrEmpty(index.Comment))
{
sb.AppendLine(this.SetTableChildComment(index, true));
}
}
}
#endregion
#region Constraint
if (this.option.TableScriptsGenerateOption.GenerateConstraint && constraints != null)
{
foreach (TableConstraint constraint in constraints)
{
sb.AppendLine(this.AddCheckConstraint(constraint));
if (!string.IsNullOrEmpty(constraint.Comment))
{
sb.AppendLine(this.SetTableChildComment(constraint, true));
}
}
}
#endregion
sb.Append(new SpliterScript(this.scriptsDelimiter));
return(sb);
}
public override Script DropPrimaryKey(TablePrimaryKey primaryKey)
{
return(new DropDbObjectScript <TablePrimaryKey>($"ALTER TABLE {this.GetQuotedString(primaryKey.TableName)} DROP PRIMARY KEY"));
}
public abstract Script DropPrimaryKey(TablePrimaryKey primaryKey);
public override ScriptBuilder GenerateSchemaScripts(SchemaInfo schemaInfo)
{
ScriptBuilder sb = new ScriptBuilder();
string dbOwner = this.GetDbOwner();
//#region User Defined Type
//List<string> userTypeNames = schemaInfo.UserDefinedTypes.Select(item => item.Name).Distinct().ToList();
//foreach (string userTypeName in userTypeNames)
//{
// IEnumerable<UserDefinedType> userTypes = schemaInfo.UserDefinedTypes.Where(item => item.Name == userTypeName);
// this.FeedbackInfo(OperationState.Begin, userTypes.First());
// string dataTypes = string.Join(",", userTypes.Select(item => $"{item.AttrName} {this.dbInterpreter.ParseDataType(new TableColumn() { MaxLength = item.MaxLength, DataType = item.Type, Precision = item.Precision, Scale = item.Scale })}"));
// string script = $"CREATE TYPE {this.GetQuotedString(userTypeName)} AS OBJECT ({dataTypes})" + this.dbInterpreter.ScriptsDelimiter;
// sb.AppendLine(new CreateDbObjectScript<UserDefinedType>(script));
// this.FeedbackInfo(OperationState.End, userTypes.First());
//}
//#endregion
#region Function
sb.AppendRange(this.GenerateScriptDbObjectScripts <Function>(schemaInfo.Functions));
#endregion
#region Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, table);
IEnumerable <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.TableName == table.Name).OrderBy(item => item.Order);
TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.TableName == table.Name);
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == table.Name);
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.TableName == table.Name).OrderBy(item => item.Order);
IEnumerable <TableConstraint> constraints = schemaInfo.TableConstraints.Where(item => item.Owner == table.Owner && item.TableName == table.Name);
ScriptBuilder sbTable = this.AddTable(table, columns, primaryKey, foreignKeys, indexes, constraints);
sb.AppendRange(sbTable.Scripts);
this.FeedbackInfo(OperationState.End, table);
}
#endregion
#region View
sb.AppendRange(this.GenerateScriptDbObjectScripts <View>(schemaInfo.Views));
#endregion
#region Trigger
sb.AppendRange(this.GenerateScriptDbObjectScripts <TableTrigger>(schemaInfo.TableTriggers));
#endregion
#region Procedure
sb.AppendRange(this.GenerateScriptDbObjectScripts <Procedure>(schemaInfo.Procedures));
#endregion
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb);
}
public abstract Script AddPrimaryKey(TablePrimaryKey primaryKey);
public override ScriptBuilder AddTable(Table table, IEnumerable <TableColumn> columns,
TablePrimaryKey primaryKey,
IEnumerable <TableForeignKey> foreignKeys,
IEnumerable <TableIndex> indexes,
IEnumerable <TableConstraint> constraints)
{
ScriptBuilder sb = new ScriptBuilder();
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
#region Create Table
string tableScript =
$@"
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, columns.Select(item => this.dbInterpreter.ParseColumn(table, item))).TrimEnd(',')}
)
TABLESPACE
{this.dbInterpreter.ConnectionInfo.Database}" + this.scriptsDelimiter;
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
sb.AppendLine();
#region Comment
if (!string.IsNullOrEmpty(table.Comment))
{
sb.AppendLine(this.SetTableComment(table));
}
foreach (TableColumn column in columns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine(this.SetTableColumnComment(table, column, true));
}
#endregion
#region Primary Key
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKey != null)
{
sb.AppendLine(this.AddPrimaryKey(primaryKey));
}
#endregion
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey && foreignKeys != null)
{
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex && indexes != null)
{
List <string> indexColumns = new List <string>();
foreach (TableIndex index in indexes)
{
string columnNames = string.Join(",", index.Columns.OrderBy(item => item.ColumnName).Select(item => item.ColumnName));
//Avoid duplicated indexes for one index.
if (indexColumns.Contains(columnNames))
{
continue;
}
sb.AppendLine(this.AddIndex(index));
if (!indexColumns.Contains(columnNames))
{
indexColumns.Add(columnNames);
}
}
}
#endregion
#region Constraint
if (this.option.TableScriptsGenerateOption.GenerateConstraint && constraints != null)
{
foreach (TableConstraint constraint in constraints)
{
sb.AppendLine(this.AddCheckConstraint(constraint));
}
}
#endregion
return(sb);
}
public abstract ScriptBuilder AddTable(Table table, IEnumerable <TableColumn> columns,
TablePrimaryKey primaryKey,
IEnumerable <TableForeignKey> foreignKeys,
IEnumerable <TableIndex> indexes,
IEnumerable <TableConstraint> constraints);
public SchemaInfo GetSchemaInfo(SchemaDesignerInfo schemaDesignerInfo)
{
SchemaInfo schemaInfo = new SchemaInfo();
Table table = new Table();
ObjectHelper.CopyProperties(schemaDesignerInfo.TableDesignerInfo, table);
schemaInfo.Tables.Add(table);
#region Columns
TablePrimaryKey primaryKey = null;
foreach (TableColumnDesingerInfo column in schemaDesignerInfo.TableColumnDesingerInfos)
{
TableColumn tableColumn = new TableColumn();
ObjectHelper.CopyProperties(column, tableColumn);
if (!tableColumn.IsUserDefined)
{
ColumnManager.SetColumnLength(this.dbInterpreter.DatabaseType, tableColumn, column.Length);
}
if (column.IsPrimary)
{
if (primaryKey == null)
{
primaryKey = new TablePrimaryKey()
{
Owner = table.Owner, TableName = table.Name, Name = IndexManager.GetPrimaryKeyDefaultName(table)
};
}
IndexColumn indexColumn = new IndexColumn()
{
ColumnName = column.Name, IsDesc = false, Order = primaryKey.Columns.Count + 1
};
if (!schemaDesignerInfo.IgnoreTableIndex)
{
TableIndexDesignerInfo indexDesignerInfo = schemaDesignerInfo.TableIndexDesingerInfos
.FirstOrDefault(item => item.Type == IndexType.Primary.ToString() && item.Columns.Any(t => t.ColumnName == column.Name));
if (indexDesignerInfo != null)
{
primaryKey.Name = indexDesignerInfo.Name;
primaryKey.Comment = indexDesignerInfo.Comment;
IndexColumn columnInfo = indexDesignerInfo.Columns.FirstOrDefault(item => item.ColumnName == column.Name);
if (columnInfo != null)
{
indexColumn.IsDesc = columnInfo.IsDesc;
}
if (indexDesignerInfo.ExtraPropertyInfo != null)
{
primaryKey.Clustered = indexDesignerInfo.ExtraPropertyInfo.Clustered;
}
}
}
primaryKey.Columns.Add(indexColumn);
}
TableColumnExtraPropertyInfo extralProperty = column.ExtraPropertyInfo;
if (column.IsIdentity)
{
if (extralProperty != null)
{
table.IdentitySeed = extralProperty.Seed;
table.IdentityIncrement = extralProperty.Increment;
}
else
{
table.IdentitySeed = 1;
table.IdentityIncrement = 1;
}
}
if (extralProperty?.Expression != null)
{
tableColumn.ComputeExp = extralProperty.Expression;
}
schemaInfo.TableColumns.Add(tableColumn);
}
if (primaryKey != null)
{
schemaInfo.TablePrimaryKeys.Add(primaryKey);
}
#endregion
#region Indexes
if (!schemaDesignerInfo.IgnoreTableIndex)
{
foreach (TableIndexDesignerInfo indexDesignerInfo in schemaDesignerInfo.TableIndexDesingerInfos)
{
if (!indexDesignerInfo.IsPrimary)
{
TableIndex index = new TableIndex()
{
Owner = indexDesignerInfo.Owner, TableName = indexDesignerInfo.TableName
};
index.Name = indexDesignerInfo.Name;
index.IsUnique = indexDesignerInfo.Type == IndexType.Unique.ToString();
index.Clustered = indexDesignerInfo.Clustered;
index.Comment = indexDesignerInfo.Comment;
index.Columns.AddRange(indexDesignerInfo.Columns);
int order = 1;
index.Columns.ForEach(item => { item.Order = order++; });
schemaInfo.TableIndexes.Add(index);
}
}
}
#endregion
#region Foreign Keys
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
foreach (TableForeignKeyDesignerInfo keyDesignerInfo in schemaDesignerInfo.TableForeignKeyDesignerInfos)
{
TableForeignKey foreignKey = new TableForeignKey()
{
Owner = keyDesignerInfo.Owner, TableName = keyDesignerInfo.TableName
};
foreignKey.Name = keyDesignerInfo.Name;
foreignKey.ReferencedTableName = keyDesignerInfo.ReferencedTableName;
foreignKey.UpdateCascade = keyDesignerInfo.UpdateCascade;
foreignKey.DeleteCascade = keyDesignerInfo.DeleteCascade;
foreignKey.Comment = keyDesignerInfo.Comment;
foreignKey.Columns.AddRange(keyDesignerInfo.Columns);
int order = 1;
foreignKey.Columns.ForEach(item => { item.Order = order++; });
schemaInfo.TableForeignKeys.Add(foreignKey);
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
foreach (TableConstraintDesignerInfo constraintDesignerInfo in schemaDesignerInfo.TableConstraintDesignerInfos)
{
TableConstraint constraint = new TableConstraint()
{
Owner = constraintDesignerInfo.Owner, TableName = constraintDesignerInfo.TableName
};
constraint.Name = constraintDesignerInfo.Name;
constraint.Definition = constraintDesignerInfo.Definition;
constraint.Comment = constraintDesignerInfo.Comment;
schemaInfo.TableConstraints.Add(constraint);
}
}
#endregion
return(schemaInfo);
}
public virtual async Task <string> GenerateDataScriptsAsync(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.ClearScriptFile(GenerateScriptMode.Data);
}
int i = 0;
int pickupIndex = -1;
if (schemaInfo.PickupTable != null)
{
foreach (Table table in schemaInfo.Tables)
{
if (table.Owner == schemaInfo.PickupTable.Owner && table.Name == schemaInfo.PickupTable.Name)
{
pickupIndex = i;
break;
}
i++;
}
}
i = 0;
using (DbConnection connection = this.dbInterpreter.CreateConnection())
{
int tableCount = schemaInfo.Tables.Count - (pickupIndex == -1 ? 0 : pickupIndex);
int count = 0;
foreach (Table table in schemaInfo.Tables)
{
if (this.dbInterpreter.CancelRequested)
{
break;
}
if (i < pickupIndex)
{
i++;
continue;
}
count++;
string strTableCount = $"({count}/{tableCount})";
string tableName = table.Name;
List <TableColumn> columns = schemaInfo.TableColumns.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order).ToList();
bool isSelfReference = TableReferenceHelper.IsSelfReference(tableName, schemaInfo.TableForeignKeys);
TablePrimaryKey primaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault(item => item.Owner == table.Owner && item.TableName == tableName);
string primaryKeyColumns = primaryKey == null ? "" : string.Join(",", primaryKey.Columns.OrderBy(item => item.Order).Select(item => this.GetQuotedString(item.ColumnName)));
long total = await this.dbInterpreter.GetTableRecordCountAsync(connection, table);
if (this.option.DataGenerateThreshold.HasValue && total > this.option.DataGenerateThreshold.Value)
{
this.FeedbackInfo($"Record count of table \"{table.Name}\" exceeds {this.option.DataGenerateThreshold.Value},ignore it.");
continue;
}
int pageSize = this.dbInterpreter.DataBatchSize;
this.FeedbackInfo($"{strTableCount}Table \"{table.Name}\":record count is {total}.");
Dictionary <long, List <Dictionary <string, object> > > dictPagedData;
if (isSelfReference)
{
string parentColumnName = schemaInfo.TableForeignKeys.FirstOrDefault(item =>
item.Owner == table.Owner &&
item.TableName == tableName &&
item.ReferencedTableName == tableName)?.Columns?.FirstOrDefault()?.ColumnName;
string strWhere = $" WHERE { this.GetQuotedString(parentColumnName)} IS NULL";
dictPagedData = await this.GetSortedPageData(connection, table, primaryKeyColumns, parentColumnName, columns, strWhere);
}
else
{
dictPagedData = await this.dbInterpreter.GetPagedDataListAsync(connection, table, columns, primaryKeyColumns, total, pageSize);
}
this.FeedbackInfo($"{strTableCount}Table \"{table.Name}\":data read finished.");
if (count > 1)
{
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToString))
{
sb.AppendLine();
}
else if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(Environment.NewLine, GenerateScriptMode.Data);
}
}
i++;
if (this.option.BulkCopy && this.dbInterpreter.SupportBulkCopy && !this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
continue;
}
else
{
this.AppendDataScripts(sb, table, columns, dictPagedData);
}
}
}
var dataScripts = string.Empty;
try
{
dataScripts = sb.ToString();
}
catch (OutOfMemoryException ex)
{
this.FeedbackError("Exception occurs:" + ex.Message);
}
finally
{
sb.Clear();
}
return(dataScripts);
}
