The above blog brought us to our current database version control system. Simply put, no DB changes are made without an update script and all update scripts are in our source control repository.
We only manage schema changes but you may also be able/willing to consider keeping dumps of your data available in version control as well; creating such files is a pretty trivial exercise using mysqldump.
Our solution differs from the solution presented in the blog in one key manner: it's not automated. We have to hand apply database updates, etc. Though this can be slightly time consuming, it postponed some of the effort a fully automated system would have required. One thing we did automate however, was the db version tracking in the software: this was pretty simple and it ensures that our software is aware of the database it's running against and will ONLY run if it knows the schema it's working with.
The hardest part of our solution was how to merge updates from our branches into our trunk. We spent some time to develop a workflow to address the possibility of two developers trying to merge branches with DB updates at the same time and how to handle it. We eventually settled on locking a file in version control (the file in question for us is actually a table mapping software version to db version which assists in our manual management strategy), much like you would a thread's critical section, and the developer who gets the lock goes about their update of the trunk. When completed, the other developer would be able to lock and it is their responsibility to make any changes necessary to their scripts to ensure that expected version collisions and other bad juju are avoided.
We keep all of our database scripts (data and schema/ddl) in version control. We also keep a central catalog of the changes. When a developer makes a change to a schema/DDL file or adds a script that changes the data in some way, those files are added to the catalog, along with the SVN commit number.
We have put together a small utility in-house that reads the catalog changes and builds a large update script based on the contents of the catalog by grabbing the contents from each revision in the catalog and applying them. The concept is pretty similar to the DBDeploy tool, which I believe originally came from Thoughtworks, so you may be able to utilize it. It will at least give you a good place to start, from which point you can customize a solution more directly suited to your needs.
If your database maps nicely to a set of data access objects, consider using 'migrations'. The idea is to store your data model as application code with steps for moving forward and backward through each database version.
To change versions, you run a simple script that steps through all of the up or down versions to get you to the version you want. The beauty of it is, you check your migrations into the same source repository as your app code - it's all in one place.
Maybe others can suggest other migration libraries.
发布评论
评论（3）
http://odetocode.com/Blogs/scott/archive/ 2008/01/30/11702.aspx
上面的博客将我们带到了我们当前的数据库版本控制系统。 简而言之，如果没有更新脚本，就不会进行任何数据库更改，并且所有更新脚本都位于我们的源代码控制存储库中。
我们只管理架构更改，但您也可以/愿意考虑在版本控制中保留数据转储可用； 使用 mysqldump 创建此类文件是一项非常简单的练习。
我们的解决方案与博客中提出的解决方案有一个关键的不同：它不是自动化的。 我们必须手动应用数据库更新等。虽然这可能会稍微耗时，但它推迟了全自动系统所需的一些工作。 然而，我们自动化做的一件事是软件中的数据库版本跟踪：这非常简单，它确保我们的软件知道它正在运行的数据库，并且只有在它知道它正在使用的模式时才会运行。
我们解决方案中最困难的部分是如何将分支的更新合并到主干中。 我们花了一些时间开发一个工作流程，以解决两个开发人员尝试同时合并分支与数据库更新的可能性以及如何处理它。 我们最终决定在版本控制中锁定文件（对我们来说，有问题的文件实际上是一个将软件版本映射到数据库版本的表，它有助于我们的手动管理策略），就像线程的关键部分一样，开发人员得到锁会更新主干。 完成后，其他开发人员将能够锁定，并且他们有责任对其脚本进行任何必要的更改，以确保避免预期的版本冲突和其他不良情况。
http://odetocode.com/Blogs/scott/archive/2008/01/30/11702.aspx
The above blog brought us to our current database version control system. Simply put, no DB changes are made without an update script and all update scripts are in our source control repository.
We only manage schema changes but you may also be able/willing to consider keeping dumps of your data available in version control as well; creating such files is a pretty trivial exercise using mysqldump.
Our solution differs from the solution presented in the blog in one key manner: it's not automated. We have to hand apply database updates, etc. Though this can be slightly time consuming, it postponed some of the effort a fully automated system would have required. One thing we did automate however, was the db version tracking in the software: this was pretty simple and it ensures that our software is aware of the database it's running against and will ONLY run if it knows the schema it's working with.
The hardest part of our solution was how to merge updates from our branches into our trunk. We spent some time to develop a workflow to address the possibility of two developers trying to merge branches with DB updates at the same time and how to handle it. We eventually settled on locking a file in version control (the file in question for us is actually a table mapping software version to db version which assists in our manual management strategy), much like you would a thread's critical section, and the developer who gets the lock goes about their update of the trunk. When completed, the other developer would be able to lock and it is their responsibility to make any changes necessary to their scripts to ensure that expected version collisions and other bad juju are avoided.
我们将所有数据库脚本（数据和模式/ddl）保留在版本控制中。 我们还保留了变更的中央目录。 当开发人员更改架构/DDL 文件或添加以某种方式更改数据的脚本时，这些文件将与 SVN 提交号一起添加到目录中。
我们内部组装了一个小型实用程序，用于读取目录更改并通过从目录中的每个修订中获取内容并应用它们来基于目录内容构建大型更新脚本。 这个概念与 DBDeploy 工具非常相似，我相信它最初来自 Thoughtworks，以便您可以使用它。 它至少会给您一个良好的起点，从这里您可以定制更直接适合您需求的解决方案。
祝你好运！
We keep all of our database scripts (data and schema/ddl) in version control. We also keep a central catalog of the changes. When a developer makes a change to a schema/DDL file or adds a script that changes the data in some way, those files are added to the catalog, along with the SVN commit number.
We have put together a small utility in-house that reads the catalog changes and builds a large update script based on the contents of the catalog by grabbing the contents from each revision in the catalog and applying them. The concept is pretty similar to the DBDeploy tool, which I believe originally came from Thoughtworks, so you may be able to utilize it. It will at least give you a good place to start, from which point you can customize a solution more directly suited to your needs.
Best of luck!
如果您的数据库很好地映射到一组数据访问对象，请考虑使用“迁移”。 这个想法是将数据模型存储为应用程序代码，并提供在每个数据库版本中向前和向后移动的步骤。
我相信 Rails 首先做到了。
Java 有至少一个项目。
这是.NET 迁移库。
要更改版本，您可以运行一个简单的脚本，该脚本会逐步执行所有向上或向下的版本，以达到您想要的版本。 它的美妙之处在于，您可以将迁移检查到与应用程序代码相同的源存储库中 - 一切都在一个地方。
也许其他人可以建议其他迁移库。
干杯。
编辑：另请参阅 https://stackoverflow.com/questions/313/net-migrations-engine 和.NET 数据库迁移工具综述（来自上面的帖子）。
If your database maps nicely to a set of data access objects, consider using 'migrations'. The idea is to store your data model as application code with steps for moving forward and backward through each database version.
I believe Rails did it first.
Java has at least one project.
And here's a .NET migration library.
To change versions, you run a simple script that steps through all of the up or down versions to get you to the version you want. The beauty of it is, you check your migrations into the same source repository as your app code - it's all in one place.
Maybe others can suggest other migration libraries.
Cheers.
Edit: See also https://stackoverflow.com/questions/313/net-migrations-engine and .NET database migration tool roundup (from above post).