我应该如何将DDL更改从一种环境迁移到另一种环境? [英] How should I migrate DDL changes from one environment to the next?

问题描述

我使用SQL Developer的GUI进行DDL更改.问题是，我需要将那些相同的更改应用于测试环境.我想知道其他人如何处理这个问题.当前，我必须手动编写ALTER语句以使测试环境与开发环境保持一致，但这容易出错(两次执行相同的操作).在测试环境中没有重要数据的情况下，我通常会把所有东西丢掉，从dev中导出DDL脚本，然后在测试中从头开始运行它们.

I make DDL changes using SQL Developer's GUI. Problem is, I need to apply those same changes to the test environment. I'm wondering how others handle this issue. Currently I'm having to manually write ALTER statements to bring the test environment into alignment with the development environment, but this is prone to error (doing the same thing twice). In cases where there's no important data in the test environment I usually just blow everything away, export the DDL scripts from dev and run them from scratch in test.

我知道有一些触发器可以存储每个DDL更改，但这是一个高度共享的环境，如果可能，我想避免这种情况.

I know there are triggers that can store each DDL change, but this is a heavily shared environment and I would like to avoid that if possible.

也许我应该只手动编写DDL东西，而不要使用GUI?

Maybe I should just write the DDL stuff manually rather than using the GUI?

推荐答案

我已经看到了一种我不知道如何做的方法，最后我认为您只需要维护手册即可.脚本.

I've seen a I-don't-know-how-many ways tried to handle this, and in end I think you need to just maintain manual scripts.

现在，您不必一定要自己写.在MSSQL中，当您进行更改时，有一个用于生成脚本的小按钮，它将吐出一个SQL脚本来进行您所做的更改.我知道您在谈论Oracle，距我使用他们的GUI已有好几年了，但我只能想象他们具有相同的功能.

Now, you don't necessarily have to write then yourself. In MSSQL, as you're making a change, there is a little button to Generate Script, which will spit out a SQL script for the change you are making. I know you're talking about Oracle, and it's been a few years since I worked with their GUI, but I can only imagine that they have the same feature.

但是，您无法摆脱手动使用脚本的麻烦.关于现有数据，您将遇到很多问题，例如新列的默认值或如何处理重命名/删除/移动的列的数据.这只是随着时间的推移而无法摆脱的使用数据库模式的分析的一部分.如果您尝试使用完全自动化的解决方案来做到这一点，那么您的数据迟早会变得混乱.

However, you can't get away from working with scripts manually. You're going to have a lot of issues around pre-existing data, like default values for new columns or how to handle data for a renamed/deleted/moved column. This is just part of the analysis in working with a database schema over time that you can't get away from. If you try to do this with an completely automated solution, your data is going to get messed up sooner or later.

为了使您的生活更轻松，我建议的一件事是确保将架构更改与代码更改分开.不同之处在于，对表和列的模式更改必须准确地运行一次，并且永远不要再次运行，因此必须作为单独的更改脚本进行版本控制.但是，代码更改(例如存储的proc，函数甚至视图)可以(并且应该)反复运行，并且可以像其他任何代码文件一样进行版本控制.我看到的最好的方法是在VSS中拥有所有proc/函数/视图时，我们的构建过程将删除所有过程/函数/视图，并在每次更新期间重新创建它们.这与重建C#/Java/任何代码的想法相同，因为它确保所有内容始终都是最新的.

The one thing I would recommend, just to make your life a little easier, is make sure you separate schema changes from code changes. The difference is that schema changes to tables and columns must be run exactly once and never again, and therefore have to be versioned as individual change scripts. However, code changes, like stored procs, functions, and even views, can (and should) be run over and over, and can be versioned just like any other code file. The best approach to this I've seen was when we had all of the procs/functions/views in VSS, and our build process would drop all and and recreate them during every update. This is the same idea as doing a rebuild of your C#/Java/whatever code, because it make sure everything is always up to date.

这篇关于我应该如何将DDL更改从一种环境迁移到另一种环境?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！