database 您是否对数据库项目使用源代码管理？

Do you use source control for your database items?

提问by Brian MacKay

I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to rely on your last line of defense in this way.

我觉得我的商店有一个漏洞，因为我们没有一个可靠的流程来对我们的数据库架构更改进行版本控制。我们做了很多备份，所以我们或多或少都被覆盖了，但以这种方式依赖你的最后一道防线是不好的做法。

Surprisingly, this seems to be a common thread. Many shops I have spoken to ignore this issue because their databases don't change often, and they basically just try to be meticulous.

令人惊讶的是，这似乎是一个共同点。我采访过的许多商店都忽略了这个问题，因为他们的数据库不会经常更改，而且他们基本上只是尽量做到一丝不苟。

However, I know how that story goes. It's only a matter of time before things line up just wrong and something goes missing.

不过，我知道这个故事是怎么回事。事情出现错误并丢失某些东西只是时间问题。

Are there any best practices for this? What are some strategies that have worked for you?

是否有任何最佳做法？有哪些策略对你有用？

采纳答案by Gulzar Nazim

Must read Get your database under version control. Check the series of posts by K. Scott Allen.

必须阅读在版本控制下获取您的数据库。查看 K. Scott Allen 的系列帖子。

When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.

说到版本控制，数据库往往是二等甚至三等公民。从我所看到的情况来看，一百万年后永远不会想到编写没有版本控制的代码的团队——而且确实如此——可能会以某种方式完全忘记围绕他们的应用程序所依赖的关键数据库进行版本控制的需求。我不知道当您的数据库与其他代码的源代码控制级别不同时，您如何称自己为软件工程师并保持板着脸。不要让这发生在你身上。让您的数据库处于版本控制之下。

回答by blowdart

The databases themselves? No

数据库本身？不

The scripts that create them, including static data inserts, stored procedures and the like; of course. They're text files, they are included in the project and are checked in and out like everything else.

创建它们的脚本，包括静态数据插入、存储过程等；当然。它们是文本文件，它们包含在项目中，并像其他所有内容一样被签入和签出。

Of course in an ideal world your database management tool would do this; but you just have to be disciplined about it.

当然，在理想情况下，您的数据库管理工具会这样做；但你只需要遵守纪律。

回答by Matt Rogish

I absolutely love Rails ActiveRecord migrations. It abstracts the DML to ruby script which can then be easily version'd in your source repository.

我非常喜欢 Rails ActiveRecord 迁移。它将 DML 抽象为 ruby​​ 脚本，然后可以轻松地在您的源存储库中对其进行版本控制。

However, with a bit of work, you could do the same thing. Any DDL changes (ALTER TABLE, etc.) can be stored in text files. Keep a numbering system (or a date stamp) for the file names, and apply them in sequence.

但是，通过一些工作，您可以做同样的事情。任何 DDL 更改（ALTER TABLE 等）都可以存储在文本文件中。为文件名保留编号系统（或日期戳），并按顺序应用它们。

Rails also has a 'version' table in the DB that keeps track of the last applied migration. You can do the same easily.

Rails 在数据库中还有一个“版本”表，用于跟踪上次应用的迁移。您可以轻松地做到这一点。

回答by killdash10

Check out LiquiBasefor managing database changes using source control.

查看LiquiBase以使用源代码控制管理数据库更改。

回答by Paul Tomblin

You should never just log in and start entering "ALTER TABLE" commands to change a production database. The project I'm on has database on every customer site, and so every change to the database is made in two places, a dump file that is used to create a new database on a new customer site, and an update file that is run on every update which checks your current database version number against the highest number in the file, and updates your database in place. So for instance, the last couple of updates:

您永远不应该只是登录并开始输入“ALTER TABLE”命令来更改生产数据库。我所在的项目在每个客户站点上都有数据库，因此对数据库的每次更改都在两个地方进行，一个是用于在新客户站点上创建新数据库的转储文件，另一个是运行的更新文件在每次更新时，它会根据文件中的最高数字检查您当前的数据库版本号，并就地更新您的数据库。例如，最近的几个更新：

if [ $VERSION \< '8.0.108' ] ; then
  psql -U cosuser $dbName << EOF8.0.108
    BEGIN TRANSACTION;
    --
    -- Remove foreign key that shouldn't have been there.
    -- PCR:35665
    --
    ALTER TABLE     migratorjobitems
    DROP CONSTRAINT migratorjobitems_destcmaid_fkey;
    -- 
    -- Increment the version
    UPDATE          sys_info
    SET             value = '8.0.108'
    WHERE           key = 'DB VERSION';
    END TRANSACTION;
EOF8.0.108
fi

if [ $VERSION \< '8.0.109' ] ; then
  psql -U cosuser $dbName << EOF8.0.109
    BEGIN TRANSACTION;
    --
    -- I missed a couple of cases when I changed the legacy playlist
    -- from reporting showplaylistidnum to playlistidnum
    --
    ALTER TABLE     featureidrequestkdcs
    DROP CONSTRAINT featureidrequestkdcs_cosfeatureid_fkey;
    ALTER TABLE     featureidrequestkdcs
    ADD CONSTRAINT  featureidrequestkdcs_cosfeatureid_fkey
    FOREIGN KEY     (cosfeatureid)
    REFERENCES      playlist(playlistidnum)
    ON DELETE       CASCADE;
    --
    ALTER TABLE     ticket_system_ids
    DROP CONSTRAINT ticket_system_ids_showplaylistidnum_fkey;
    ALTER TABLE     ticket_system_ids
    RENAME          showplaylistidnum
    TO              playlistidnum;
    ALTER TABLE     ticket_system_ids
    ADD CONSTRAINT  ticket_system_ids_playlistidnum_fkey
    FOREIGN KEY     (playlistidnum)
    REFERENCES      playlist(playlistidnum)
    ON DELETE       CASCADE;
    -- 
    -- Increment the version
    UPDATE          sys_info
    SET             value = '8.0.109'
    WHERE           key = 'DB VERSION';
    END TRANSACTION;
EOF8.0.109
fi

I'm sure there is a better way to do this, but it's worked for me so far.

我确信有更好的方法可以做到这一点，但到目前为止它对我有用。

回答by Stu Thompson

Yes. Code is code. My rule of thumb is that I need to be able to build and deploy the application from scratch, without looking at a development or production machine.

是的。代码就是代码。我的经验法则是，我需要能够从头开始构建和部署应用程序，而无需查看开发或生产机器。

回答by Sara Chipps

The best practice I have seen is creating a build script to scrap and rebuild your database on a staging server. Each iteration was given a folder for database changes, all changes were scripted with "Drop... Create" 's . This way you can rollback to an earlier version at any time by pointing the build to folder you want to version to.

我见过的最佳实践是创建一个构建脚本来在登台服务器上废弃和重建您的数据库。每次迭代都有一个用于数据库更改的文件夹，所有更改都使用“Drop... Create”的脚本编写。通过这种方式，您可以随时通过将构建指向要版本化的文件夹来回滚到早期版本。

I believe this was done with NaNt/CruiseControl.

我相信这是通过 NaNt/CruiseControl 完成的。

回答by Pete

YES, I think it is important to version your database. Not the data, but the schema for certain.

是的，我认为对数据库进行版本控制很重要。不是数据，而是模式。

In Ruby On Rails, this is handled by the framework with "migrations". Any time you alter the db, you make a script that applies the changes and check it into source control.

在 Ruby On Rails 中，这由具有“迁移”的框架处理。每当您更改数据库时，您都会创建一个应用更改并将其签入源代码管理的脚本。

My shop liked that idea so much that we added the functionality to our Java-based build using shell scriptsand Ant. We integrated the process into our deployment routine. It would be fairly easy to write scripts to do the same thing in other frameworks that don't support DB versioning out-of-the-box.

我的商店非常喜欢这个想法，以至于我们使用 shell 脚本和 Ant将功能添加到基于 Java 的构建中。我们将该过程集成到我们的部署例程中。在不支持开箱即用的数据库版本控制的其他框架中编写脚本来执行相同的操作是相当容易的。

回答by Tom A

The new Database projects in Visual Studio provide source control and change scripts.

Visual Studio 中的新数据库项目提供源代码控制和更改脚本。

They have a nice tool that compares databases and can generate a script that converts the schema of one into the other, or updates the data in one to match the other.

他们有一个很好的工具来比较数据库，并可以生成一个脚本，将一个模式转换成另一个，或者更新一个中的数据以匹配另一个。

The db schema is "shredded" to create many, many small .sql files, one per DDL command that describes the DB.

db 模式被“粉碎”以创建许多很多小的 .sql 文件，每个 DDL 命令描述数据库一个。

+tom

+汤姆

---

---

Additional info 2008-11-30

补充资料 2008-11-30

I have been using it as a developer for the past year and really like it. It makes it easy to compare my dev work to production and generate a script to use for the release. I don't know if it is missing features that DBAs need for "enterprise-type" projects.

过去一年我一直在使用它作为开发人员并且非常喜欢它。它可以轻松地将我的开发工作与生产进行比较，并生成用于发布的脚本。我不知道它是否缺少 DBA 需要的“企业类型”项目的功能。

Because the schema is "shredded" into sql files the source control works fine.

因为模式被“粉碎”成 sql 文件，所以源代码控制工作正常。

One gotcha is that you need to have a different mindset when you use a db project. The tool has a "db project" in VS, which is just the sql, plus an automatically generated local database which has the schema and some other admin data -- but none of your application data, plus your local dev db that you use for app data dev work. You rarely are aware of the automatically generated db, but you have to know its there so you can leave it alone :). This special db is clearly recognizable because it has a Guid in its name,

一个问题是，当您使用 db 项目时，您需要有不同的心态。该工具在 VS 中有一个“db 项目”，它只是 sql，加上一个自动生成的本地数据库，其中包含架构和其他一些管理数据——但没有你的应用程序数据，加上你使用的本地开发数据库应用数据开发工作。您很少知道自动生成的数据库，但您必须知道它在那里，以便您可以不理会它:)。这个特殊的 db 很容易辨认，因为它的名字中有一个 Guid，

The VS DB Project does a nice job of integrating db changes that other team members have made into your local project/associated db. but you need to take the extra step to compare the project schema with your local dev db schema and apply the mods. It makes sense, but it seems awkward at first.

VS DB 项目在将其他团队成员所做的数据库更改集成到您的本地项目/关联数据库方面做得很好。但是您需要采取额外的步骤将项目架构与本地开发数据库架构进行比较并应用 mods。这是有道理的，但乍一看似乎很尴尬。

DB Projects are a very powerful tool. They not only generate scripts but can apply them immediately. Be sure not to destroy your production db with it. ;)

DB Projects 是一个非常强大的工具。它们不仅生成脚本，而且可以立即应用它们。确保不要用它破坏您的生产数据库。;)

I really like the VS DB projects and I expect to use this tool for all my db projects going forward.

我真的很喜欢 VS DB 项目，我希望在我未来的所有 db 项目中使用这个工具。

+tom

+汤姆

回答by AliceF

Requiring the development teams to use an SQL database source control management system isn't the magic bullet which will prevent issues from happening. On its own, database source control introduces additional overhead as the developers are required to save the changes they've made to an object in a separate SQL script, open the source control system client, check in the SQL script file using the client and then apply the changes to the live database.

要求开发团队使用 SQL 数据库源控制管理系统并不是防止问题发生的灵丹妙药。就其本身而言，数据库源代码控制引入了额外的开销，因为开发人员需要将他们对对象所做的更改保存在单独的 SQL 脚本中，打开源代码控制系统客户端，使用客户端检入 SQL 脚本文件，然后将更改应用到实时数据库。

I can suggest using the SSMS add-in called ApexSQL Source Control. It allows developers to easily map database objects with the source control system via the wizard directly from SSMS. The add-in includes support for TFS, Git, Subversion and other SC systems. It also includes support for source controlling Static data.

我可以建议使用名为ApexSQL Source Control的 SSMS 插件。它允许开发人员通过向导直接从 SSMS 轻松地将数据库对象与源控制系统映射。该插件包括对 TFS、Git、Subversion 和其他 SC 系统的支持。它还包括对源控制静态数据的支持。

After downloading and installing ApexSQL Source Control, simply right-click the database you want to version control and navigate to ApexSQL Source Control sub-menu in SSMS. Click the Link database to source control option, select the source control system and the development model. After that you'll need to provide the log-in information and the repository string for the source control system you've chosen.

下载并安装 ApexSQL Source Control 后，只需右键单击要进行版本控制的数据库并导航到 SSMS 中的 ApexSQL Source Control 子菜单。单击 Link database to source control 选项，选择源控制系统和开发模型。之后，您需要为您选择的源代码控制系统提供登录信息和存储库字符串。

You can read this article for more information: http://solutioncenter.apexsql.com/sql-source-control-reduce-database-development-time/

您可以阅读这篇文章了解更多信息：http: //solutioncenter.apexsql.com/sql-source-control-reduce-database-development-time/