跟踪数据库模式更改的机制 [英] Mechanisms for tracking DB schema changes
问题描述
跟踪和/或自动化数据库模式更改的最佳方法是什么？我们的团队使用Subversion进行版本控制，我们已经能够以这种方式自动化我们的一些任务（推送到临时服务器，将经过测试的代码部署到生产服务器），但我们仍然手动进行数据库更新。我想找到或创建一个解决方案，使我们能够跨不同环境的服务器有效地工作，同时继续使用Subversion作为后端，代码和数据库更新推送到各种服务器。
许多流行的软件包包括自动更新脚本，用于检测数据库版本并应用必要的更改。这是最好的方式，即使在更大规模（跨多个项目，有时多个环境和语言）吗？如果是，是否有任何现有的代码，简化的过程，或者最好只是滚动我们自己的解决方案？有没有人实现类似的之前，并将它集成到Subversion post-commit钩子，或者这是一个坏主意？
虽然一个支持多平台的解决方案，肯定需要支持Linux / Apache / MySQL / PHP栈，因为我们的大部分工作是在这个平台上。
Rails世界中，存在迁移的概念，其中对数据库的更改是在Ruby中进行的脚本，而不是数据库特定的SQL风格。您的Ruby迁移代码最终会转换为特定于您当前数据库的DDL;这使得切换数据库平台非常容易。
对数据库所做的任何更改，都会写入一个新的迁移。迁移通常有两种方法：应用更改的上方法和撤消更改的下方法。单个命令使数据库更新，并且还可以用于将数据库带到特定版本的模式。在Rails中，迁移保存在项目目录中的自己的目录中，并像任何其他项目代码一样检查版本控制。
使用其他语言的开发人员已查看迁移并实施了自己的特定语言版本。我知道 Ruckusing ，PHP迁移系统是在Rails迁移后建模的;它可能是你要找的。
What are the best methods for tracking and/or automating DB schema changes? Our team uses Subversion for version control and we've been able to automate some of our tasks this way (pushing builds up to a staging server, deploying tested code to a production server) but we're still doing database updates manually. I would like to find or create a solution that allows us to work efficiently across servers with different environments while continuing to use Subversion as a backend through which code and DB updates are pushed around to various servers.
Many popular software packages include auto-update scripts which detect DB version and apply the necessary changes. Is this the best way to do this even on a larger scale (across multiple projects and sometimes multiple environments and languages)? If so, is there any existing code out there that simplifies the process or is it best just to roll our own solution? Has anyone implemented something similar before and integrated it into Subversion post-commit hooks, or is this a bad idea?
While a solution that supports multiple platforms would be preferable, we definitely need to support the Linux/Apache/MySQL/PHP stack as the majority of our work is on that platform.
In the Rails world, there's the concept of migrations, scripts in which changes to the database are made in Ruby rather than a database-specific flavour of SQL. Your Ruby migration code ends up being converted into the DDL specific to your current database; this makes switching database platforms very easy.
For every change you make to the database, you write a new migration. Migrations typically have two methods: an "up" method in which the changes are applied and a "down" method in which the changes are undone. A single command brings the database up to date, and can also be used to bring the database to a specific version of the schema. In Rails, migrations are kept in their own directory in the project directory and get checked into version control just like any other project code.
This Oracle guide to Rails migrations covers migrations quite well.
Developers using other languages have looked at migrations and have implemented their own language-specific versions. I know of Ruckusing, a PHP migrations system that is modelled after Rails' migrations; it might be what you're looking for.
这篇关于跟踪数据库模式更改的机制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
