SQL 版本控制方法 [英] SQL version control methodology

问题描述

关于 SQL 的版本控制和网络上的大量资源有几个关于 SO 的问题，但我找不到完全涵盖我正在尝试做的事情.

There are several questions on SO about version control for SQL and lots of resources on the web, but I can't find something that quite covers what I'm trying to do.

首先，我在这里谈论的是一种方法.我熟悉那里的各种源代码控制应用程序，我熟悉 Red Gate 的 SQL 比较等工具，我知道如何编写一个应用程序来自动签入和签出我的源代码控制系统.如果有一种工具对提供全新的方法特别有用，或者具有有用且不常见的功能，那就太好了，但是对于上面提到的任务，我已经准备好了.

First off, I'm talking about a methodology here. I'm familiar with the various source control applications out there and I'm familiar with tools like Red Gate's SQL Compare, etc. and I know how to write an application to check things in and out of my source control system automatically. If there is a tool which would be particularly helpful in providing a whole new methodology or which have a useful and uncommon functionality then great, but for the tasks mentioned above I'm already set.

我试图满足的要求是:

• 数据库架构和查找表数据是版本化的
• 对较大表的数据修复的 DML 脚本已版本化
• 服务器可以从版本 N 升级到版本 N + X，其中 X 可能不总是 1
• 代码不会在版本控制系统中重复 - 例如，如果我向表中添加一列，我不想确保更改同时出现在创建脚本和更改脚本中
• 系统需要支持应用程序不同版本的多个客户端(试图在 1 或 2 个版本内将它们全部升级，但目前还没有)

一些组织在他们的版本控制中保留增量更改脚本，并且要从版本 N 到 N + 3，您必须运行 N->N+1 然后 N+1->N+2 然后 N+2- 的脚本>N+3.其中一些脚本可能是重复的(例如，添加了一个列，但随后又对其进行了更改以更改数据类型).我们试图避免这种重复，因为一些客户端数据库可能非常大，因此这些更改可能需要比必要更长的时间.

Some organizations keep incremental change scripts in their version control and to get from version N to N + 3 you would have to run scripts for N->N+1 then N+1->N+2 then N+2->N+3. Some of these scripts can be repetitive (for example, a column is added but then later it is altered to change the data type). We're trying to avoid that repetitiveness since some of the client DBs can be very large, so these changes might take longer than necessary.

一些组织只会在每个版本级别保留完整的数据库构建脚本，然后使用 SQL Compare 等工具将数据库升级到其中一个版本.这里的问题是混合 DML 脚本可能是个问题.想象一个场景，我添加一个列，使用 DML 脚本填充该列，然后在更高版本中更改该列名称.

Some organizations will simply keep a full database build script at each version level then use a tool like SQL Compare to bring a database up to one of those versions. The problem here is that intermixing DML scripts can be a problem. Imagine a scenario where I add a column, use a DML script to fill said column, then in a later version that column name is changed.

也许有一些混合解决方案?也许我只是要求太多?任何想法或建议将不胜感激.

Perhaps there is some hybrid solution? Maybe I'm just asking for too much? Any ideas or suggestions would be greatly appreciated though.

如果版主认为这更适合作为社区 wiki，请告诉我.

If the moderators think that this would be more appropriate as a community wiki, please let me know.

谢谢！

推荐答案

我为此苦苦挣扎了好几年，最近才采用了一种似乎效果很好的策略.我赖以生存的要点:

I struggled with this for several years before recently adopting a strategy that seems to work pretty well. Key points I live by:

• 数据库不需要独立于应用程序进行版本控制
• 所有数据库更新脚本都应该是幂等的

因此，我不再创建任何类型的版本表.我只是将更改添加到可在任何给定时间应用的编号序列 .sql 文件，而不会损坏数据库.如果它使事情变得更容易，我将为应用程序编写一个简单的安装程序屏幕，以允许管理员随时运行这些脚本.

As a result, I no longer create any kind of version tables. I simply add changes to a numbered sequence of .sql files that can be applied at any given time without corrupting the database. If it makes things easier, I'll write a simple installer screen for the app to allow administrators to run these scripts whenever they like.

当然，这种方法确实对数据库设计提出了一些要求:

Of course, this method does impose a few requirements on the database design:

• 所有架构更改都通过脚本完成 - 无需 GUI 工作.
• 必须格外小心，以确保所有键、约束等都已命名，以便以后的更新脚本可以在必要时引用它们.
• 所有更新脚本都应检查现有条件.

近期项目的示例:

001.sql:

if object_id(N'dbo.Registrations') is null 
begin
    create table dbo.Registrations
    (
        [Id]                    uniqueidentifier not null,
        [SourceA]               nvarchar(50)     null,
        [SourceB]               nvarchar(50)     null,
        [Title]                 nvarchar(50)     not null,
        [Occupation]            nvarchar(50)     not null,
        [EmailAddress]          nvarchar(100)    not null,
        [FirstName]             nvarchar(50)     not null,
        [LastName]              nvarchar(50)     not null,
        [ClinicName]            nvarchar(200)    not null,
        [ClinicAddress]         nvarchar(50)     not null,
        [ClinicCity]            nvarchar(50)     not null,
        [ClinicState]           nchar(2)         not null,
        [ClinicPostal]          nvarchar(10)     not null,
        [ClinicPhoneNumber]     nvarchar(10)     not null,
        [ClinicPhoneExtension]  nvarchar(10)     not null,
        [ClinicFaxNumber]       nvarchar(10)     not null,
        [NumberOfVets]          int              not null,  
        [IpAddress]             nvarchar(20)     not null,
        [MailOptIn]             bit              not null,
        [EmailOptIn]            bit              not null,
        [Created]               datetime         not null,
        [Modified]              datetime         not null,
        [Deleted]               datetime         null
    );
end

if not exists(select 1 from information_schema.table_constraints where constraint_name = 'pk_registrations')
    alter table dbo.Registrations add
        constraint pk_registrations primary key nonclustered (Id);

if not exists (select 1 from sysindexes where [name] = 'ix_registrations_created')
    create clustered index ix_registrations_created
        on dbo.Registrations(Created);

if not exists (select 1 from sysindexes where [name] = 'ix_registrations_email')
    create index ix_registrations_email
        on dbo.Registrations(EmailAddress);

if not exists (select 1 from sysindexes where [name] = 'ix_registrations_email')
    create index ix_registrations_name_and_clinic
        on dbo.Registrations (FirstName,
                              LastName,
                              ClinicName);

002.sql

/**********************************************************************
  The original schema allowed null for these columns, but we don't want
  that, so update existing nulls and change the columns to disallow 
  null values
 *********************************************************************/

update dbo.Registrations set SourceA = '' where SourceA is null;
update dbo.Registrations set SourceB = '' where SourceB is null;
alter table dbo.Registrations alter column SourceA nvarchar(50) not null;
alter table dbo.Registrations alter column SourceB nvarchar(50) not null;

/**********************************************************************
  The client wanted to modify the signup form to include a fax opt-in
 *********************************************************************/

if not exists 
(
    select 1 
      from information_schema.columns
     where table_schema = 'dbo'
       and table_name   = 'Registrations'
       and column_name  = 'FaxOptIn'
)
alter table dbo.Registrations 
    add FaxOptIn bit null 
        constraint df_registrations_faxoptin default 0;

003.sql、004.sql 等...

003.sql, 004.sql, etc...

在任何给定时间，我都可以在任何状态下针对数据库运行整个系列的脚本，并且知道事情将立即与当前版本的应用程序同步.因为一切都是脚本化的，所以构建一个简单的安装程序来执行此操作要容易得多，并且将架构更改添加到源代码管理完全没有问题.

At any given time I can run the entire series of scripts against the database in any state and know that things will be immediately brought up to speed with the current version of the app. Because everything is scripted, it's much easier to build a simple installer to do this, and it's adding the schema changes to source control is no problem at all.

这篇关于SQL 版本控制方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！