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