php 跟踪数据库架构更改的机制

Mechanisms for tracking DB schema changes

提问by pix0r

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.

跟踪和/或自动化数据库架构更改的最佳方法是什么？我们的团队使用 Subversion 进行版本控制，我们已经能够以这种方式自动化我们的一些任务（将构建推送到临时服务器，将测试代码部署到生产服务器），但我们仍然手动进行数据库更新。我想找到或创建一个解决方案，使我们能够在具有不同环境的服务器之间高效工作，同时继续使用 Subversion 作为后端，通过该后端将代码和数据库更新推送到各种服务器。

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?

许多流行的软件包包括自动更新脚本，可检测数据库版本并应用必要的更改。即使在更大的范围内（跨多个项目，有时也跨多个环境和语言），这是最好的方法吗？如果是这样，是否有任何现有代码可以简化流程，还是最好仅推出我们自己的解决方案？有没有人之前实现过类似的东西并将其集成到 Subversion 提交后挂钩中，或者这是一个坏主意？

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.

虽然支持多个平台的解决方案更可取，但我们肯定需要支持 Linux/Apache/MySQL/PHP 堆栈，因为我们的大部分工作都在该平台上进行。

采纳答案by Joey deVilla

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.

在 Rails 世界中，有迁移的概念，在脚本中对数据库的更改是在 Ruby 中进行的，而不是特定于数据库的 SQL 风格。您的 Ruby 迁移代码最终会被转换为特定于您当前数据库的 DDL；这使得切换数据库平台非常容易。

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.

对于您对数据库所做的每一次更改，您都会编写一个新的迁移。迁移通常有两种方法：应用更改的“向上”方法和撤消更改的“向下”方法。单个命令使数据库更新，也可用于将数据库设置为特定版本的模式。在 Rails 中，迁移保存在项目目录中自己的目录中，并像任何其他项目代码一样被签入版本控制。

This Oracle guide to Rails migrationscovers migrations quite well.

这个 Oracle Rails 迁移指南很好地涵盖了迁移。

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.

使用其他语言的开发人员已经研究了迁移并实现了他们自己的特定于语言的版本。我知道Ruckusing，这是一个以 Rails 迁移为模型的 PHP 迁移系统；它可能是你正在寻找的。

回答by rix0rrr

We use something similar to bcwoord to keep our database schemata synchronized across 5 different installations (production, staging and a few development installations), and backed up in version control, and it works pretty well. I'll elaborate a bit:

我们使用类似于 bcwoord 的东西来保持我们的数据库模式在 5 个不同的安装（生产、暂存和一些开发安装）之间保持同步，并在版本控制中进行备份，它运行得非常好。我再详细说明一下：

---

---

To synchronize the database structure, we have a single script, update.php, and a number of files numbered 1.sql, 2.sql, 3.sql, etc. The script uses one extra table to store the current version number of the database. The N.sql files are crafted by hand, to go from version (N-1) to version N of the database.

为了同步数据库结构，我们有一个单独的脚本 update.php 和一些编号为 1.sql、2.sql、3.sql 等的文件。该脚本使用一个额外的表来存储当前版本号数据库。N.sql 文件是手工制作的，从数据库的版本 (N-1) 到版本 N。

They can be used to add tables, add columns, migrate data from an old to a new column format then drop the column, insert "master" data rows such as user types, etc. Basically, it can do anything, and with proper data migration scripts you'll never lose data.

它们可用于添加表、添加列、将数据从旧的列格式迁移到新的列格式然后删除列、插入“主”数据行（例如用户类型等）。基本上，它可以做任何事情，并且具有适当的数据迁移脚本，您永远不会丢失数据。

The update script works like this:

更新脚本的工作方式如下：

• Connect to the database.
• Make a backup of the current database (because stuff willgo wrong) [mysqldump].
• Create bookkeeping table (called _meta) if it doesn't exist.
• Read current VERSION from _meta table. Assume 0 if not found.
• For all .sql files numbered higher than VERSION, execute them in order
• If one of the files produced an error: roll back to the backup
• Otherwise, update the version in the bookkeeping table to the highest .sql file executed.

• 连接到数据库。
• 备份当前数据库（因为东西会出错）[mysqldump]。
• 如果不存在，则创建簿记表（称为 _meta）。
• 从 _meta 表中读取当前版本。如果未找到，则假定为 0。
• 对于所有编号高于 VERSION 的 .sql 文件，按顺序执行它们
• 如果其中一个文件产生错误：回滚到备份
• 否则，将簿记表中的版本更新为执行的最高 .sql 文件。

Everything goes into source control, and every installation has a script to update to the latest version with a single script execution (calling update.php with the proper database password etc.). We SVN update staging and production environments via a script that automatically calls the database update script, so a code update comes with the necessary database updates.

一切都进入源代码控制，每个安装都有一个脚本，可以通过单个脚本执行更新到最新版本（使用正确的数据库密码调用 update.php 等）。我们通过自动调用数据库更新脚本的脚本来更新 SVN 登台和生产环境，因此代码更新伴随着必要的数据库更新。

We can also use the same script to recreate the entire database from scratch; we just drop and recreate the database, then run the script which will completely repopulate the database. We can also use the script to populate an empty database for automated testing.

我们也可以使用相同的脚本从头开始重新创建整个数据库；我们只需删除并重新创建数据库，然后运行将完全重新填充数据库的脚本。我们还可以使用脚本来填充一个空数据库以进行自动化测试。

---

---

It took only a few hours to set up this system, it's conceptually simple and everyone gets the version numbering scheme, and it has been invaluable in having the ability to move forward and evolving the database design, without having to communicate or manually execute the modifications on all databases.

搭建这个系统只用了几个小时，概念上很简单，每个人都掌握了版本编号方案，在无需沟通或手动执行修改的情况下，能够推进和进化数据库设计的能力是非常宝贵的在所有数据库上。

Beware when pasting queries from phpMyAdmin though!Those generated queries usually include the database name, which you definitely don't want since it will break your scripts! Something like CREATE TABLE mydb.newtable(...) will fail if the database on the system is not called mydb. We created a pre-comment SVN hook that will disallow .sql files containing the mydbstring, which is a sure sign that someone copy/pasted from phpMyAdmin without proper checking.

不过，从 phpMyAdmin 粘贴查询时要小心！那些生成的查询通常包含数据库名称，您绝对不想要它，因为它会破坏您的脚本！类似于 CREATE TABLE 的东西mydb。newtable(...) 如果系统上的数据库未调用 mydb，则会失败。我们创建了一个预注释 SVN 钩子，它将禁止包含mydb字符串的.sql 文件，这是一个明确的迹象，表明有人在没有适当检查的情况下从 phpMyAdmin 复制/粘贴。

回答by Brandon Wood

My team scripts out all database changes, and commits those scripts to SVN, along with each release of the application. This allows for incremental changes of the database, without losing any data.

我的团队编写了所有数据库更改的脚本，并将这些脚本与应用程序的每个版本一起提交到 SVN。这允许对数据库进行增量更改，而不会丢失任何数据。

To go from one release to the next, you just need to run the set of change scripts, and your database is up-to-date, and you've still got all your data. It may not be the easiest method, but it definitely is effective.

要从一个版本转到下一个版本，您只需要运行一组更改脚本，并且您的数据库是最新的，并且您仍然拥有所有数据。这可能不是最简单的方法，但绝对有效。

回答by Brandon Wood

The issue here is really making it easy for developers to script their own local changes into source control to share with the team. I've faced this problem for many years, and was inspired by the functionality of Visual Studio for Database professionals. If you want an open-source tool with the same features, try this: http://dbsourcetools.codeplex.com/Have fun, - Nathan.

这里的问题确实让开发人员可以轻松地将他们自己的本地更改脚本编写到源代码管理中以与团队共享。我多年来一直面临这个问题，并且受到了 Visual Studio for Database 专业人员功能的启发。如果你想要一个具有相同功能的开源工具，试试这个：http: //dbsourcetools.codeplex.com/玩得开心， - Nathan。

回答by Christophe Fondacci

If you are still looking for solutions : we are proposing a tool called neXtep designer. It is a database development environment with which you can put your whole database under version control. You work on a version controlled repository where every change can be tracked.

如果您仍在寻找解决方案：我们提出了一种名为 neXtep 设计器的工具。它是一个数据库开发环境，您可以使用它来将整个数据库置于版本控制之下。您在一个版本控制的存储库上工作，其中可以跟踪每个更改。

When you need to release an update, you can commit your components and the product will automatically generate the SQL upgrade script from the previous version. Of course, you can generate this SQL from any 2 versions.

当您需要发布更新时，您可以提交您的组件，产品会自动从以前的版本生成 SQL 升级脚本。当然，您可以从任何 2 个版本生成此 SQL。

Then you have many options : you can take those scripts and put them in your SVN with your app code so that it'll be deployed by your existing mechanism. Another option is to use the delivery mechanism of neXtep : scripts are exported in something called a "delivery package" (SQL scripts + XML descriptor), and an installer can understand this package and deploy it to a target server while ensuring strcutural consistency, dependency check, registering installed version, etc.

然后你有很多选择：你可以把这些脚本和你的应用程序代码一起放在你的 SVN 中，这样它就会被你现有的机制部署。另一种选择是使用 neXtep 的交付机制：脚本以所谓的“交付包”（SQL 脚本 + XML 描述符）的形式导出，安装程序可以理解此包并将其部署到目标服务器，同时确保结构的一致性、依赖性检查、注册已安装的版本等。

The product is GPL and is based on Eclipse so it runs on Linux, Mac and windows. It also support Oracle, Mysql and Postgresql at the moment (DB2 support is on the way). Have a look at the wiki where you will find more detailed information : http://www.nextep-softwares.com/wiki

该产品是 GPL 并基于 Eclipse，因此它可以在 Linux、Mac 和 Windows 上运行。目前它还支持 Oracle、Mysql 和 Postgresql（DB2 支持正在开发中）。查看 wiki，您会在其中找到更多详细信息：http: //www.nextep-softwares.com/wiki

回答by Sam McAfee

Scott Ambler produces a great series of articles (and co-authored a book) on database refactoring, with the idea that you should essentially apply TDD principles and practices to maintaining your schema. You set up a series of structure and seed data unit tests for the database. Then, before you change anything, you modify/write tests to reflect that change.

Scott Ambler 撰写了大量关于数据库重构的文章（并合着了一本书），其想法是您应该基本上应用 TDD 原则和实践来维护您的模式。您为数据库设置了一系列结构和种子数据单元测试。然后，在您更改任何内容之前，您可以修改/编写测试以反映该更改。

We have been doing this for a while now and it seems to work. We wrote code to generate basic column name and datatype checks in a unit testing suite. We can rerun those tests anytime to verify that the database in the SVN checkout matches the live db the application is actually running.

我们已经这样做了一段时间，它似乎有效。我们编写了代码来在单元测试套件中生成基本的列名和数据类型检查。我们可以随时重新运行这些测试，以验证 SVN 结帐中的数据库是否与应用程序实际运行的实时数据库匹配。

As it turns out, developers also sometimes tweak their sandbox database and neglect to update the schema file in SVN. The code then depends on a db change that hasn't been checked in. That sort of bug can be maddeningly hard to pin down, but the test suite will pick it up right away. This is particularly nice if you have it built into a larger Continuous Integration plan.

事实证明，开发人员有时也会调整他们的沙箱数据库而忽略更新 SVN 中的架构文件。然后，代码取决于尚未签入的数据库更改。这种错误可能非常难以确定，但测试套件会立即发现它。如果您将其内置到更大的持续集成计划中，这将特别好。

回答by deadprogrammer

Dump your schema into a file and add it to source control. Then a simple diff will show you what changed.

将您的架构转储到文件中并将其添加到源代码管理中。然后一个简单的差异将向您展示发生了什么变化。

回答by Rob

K. Scott Allen has a decent article or two on schema versioning, which uses the incremental update scripts/migrations concept referenced in other answers here; see http://odetocode.com/Blogs/scott/archive/2008/01/31/11710.aspx.

K. Scott Allen 有一两篇关于模式版本控制的不错的文章，它使用了此处其他答案中引用的增量更新脚本/迁移概念；参见http://odetocode.com/Blogs/scott/archive/2008/01/31/11710.aspx。

回答by Fabio Gomes

We use a very simple but yet effective solution.

我们使用一个非常简单但有效的解决方案。

For new installs, we have a metadata.sql file in the repository which holds all the DB schema, then in the build process we use this file to generate the database.

对于新安装，我们在存储库中有一个 metadata.sql 文件，其中包含所有数据库架构，然后在构建过程中我们使用此文件生成数据库。

For updates, we add the updates in the software hardcoded. We keep it hardcoded because we don't like solving problems before it really IS a problem, and this kind of thing didn't prove to be a problem so far.

对于更新，我们在硬编码的软件中添加更新。我们对其进行了硬编码，因为我们不喜欢在问题真正成为问题之前解决问题，而这种事情到目前为止还没有被证明是问题。

So in our software we have something like this:

所以在我们的软件中，我们有这样的东西：

RegisterUpgrade(1, 'ALTER TABLE XX ADD XY CHAR(1) NOT NULL;');

RegisterUpgrade(1, 'ALTER TABLE XX ADD XY CHAR(1) NOT NULL;');

This code will check if the database is in version 1 (which is stored in a table created automatically), if it is outdated, then the command is executed.

此代码将检查数据库是否为版本 1（存储在自动创建的表中），如果已过时，则执行命令。

To update the metadata.sql in the repository, we run this upgrades locally and then extract the full database metadata.

为了更新存储库中的 metadata.sql，我们在本地运行此升级，然后提取完整的数据库元数据。

The only thing that happens every so often, is to forget commiting the metadata.sql, but this isn't a major problem because its easy to test on the build process and also the only thing that could happen is to make a new install with an outdated database and upgraded it on the first use.

唯一经常发生的事情是忘记提交 metadata.sql，但这不是主要问题，因为它很容易在构建过程中进行测试，而且唯一可能发生的事情是使用以下命令进行新安装一个过时的数据库并在第一次使用时对其进行了升级。

Also we don't support downgrades, but it is by design, if something breaks on an update, we restored the previous version and fix the update before trying again.

此外，我们不支持降级，但这是设计使然，如果更新出现问题，我们会恢复以前的版本并修复更新，然后再试一次。

回答by tbreffni

I've used the following database project structure in Visual Studio for several projects and it's worked pretty well:

我在 Visual Studio 中为多个项目使用了以下数据库项目结构，并且效果很好：

Database

数据库

Change Scripts

0.PreDeploy.sql

1.SchemaChanges.sql

2.DataChanges.sql

3.Permissions.sql

Create Scripts

Sprocs

Functions

Views

更改脚本

0.预部署.sql

1.SchemaChanges.sql

2.DataChanges.sql

3.权限.sql

创建脚本

程序

职能

观看次数

Our build system then updates the database from one version to the next by executing the scripts in the following order:

然后，我们的构建系统通过按以下顺序执行脚本将数据库从一个版本更新到下一个版本：

1.PreDeploy.sql

2.SchemaChanges.sql

Contents of Create Scripts folder

2.DataChanges.sql

3.Permissions.sql

1.预部署.sql

2.SchemaChanges.sql

创建脚本文件夹的内容

2.DataChanges.sql

3.权限.sql

Each developer checks in their changes for a particular bug/feature by appending their code onto the end of each file. Once a major version is complete and branched in source control, the contents of the .sql files in the Change Scripts folder are deleted.

每个开发人员通过将他们的代码附加到每个文件的末尾来检查他们对特定错误/功能的更改。一旦主要版本完成并在源代码管理中分支，Change Scripts 文件夹中的 .sql 文件内容将被删除。