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 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 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 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 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();
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.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.ScriptsSplitString;
// 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);
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);
#region Create Table
string tableScript =
$@"
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.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 tableColumns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine(this.SetTableColumnComment(table, column, true));
}
#endregion
#region Primary Key
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
{
sb.AppendLine(this.AddPrimaryKey(primaryKeys.First()));
}
#endregion
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey)
{
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == tableName);
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex)
{
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
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)
{
var constraints = schemaInfo.TableConstraints.Where(item => item.Owner == table.Owner && item.TableName == tableName);
foreach (TableConstraint constraint in constraints)
{
sb.AppendLine(this.AddCheckConstraint(constraint));
}
}
#endregion
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);
}