Пример #1
0
        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)));
            }
        }
Пример #2
0
        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));
            }
        }