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