天天看点
返回

版本化SQL Server数据库

本文翻译自：Versioning SQL Server database

I want to get my databases under version control.

我想让我的数据库受版本控制。

Does anyone have any advice or recommended articles to get me started?

有没有人有任何建议或推荐的文章让我开始？

I'll always want to have at least some data in there (as alumb mentions: user types and administrators).

我总是希望在那里至少有一些数据（如alumb提到的：用户类型和管理员）。

I'll also often want a large collection of generated test data for performance measurements.

我还经常需要大量生成的测试数据来进行性能测量。

#1楼

参考：https://stackoom.com/question/2n/版本化SQL-Server数据库

#2楼

Every database should be under source-code control.

每个数据库都应该受源代码控制。

What is lacking is a tool to automatically script all database objects - and "configuration data" - to file, which then can be added to any source control system.

缺少的是一个自动编写所有数据库对象 - 和“配置数据” - 到文件的工具，然后可以将其添加到任何源控制系统。

If you are using SQL Server, then my solution is here : http://dbsourcetools.codeplex.com/ .

如果您使用的是SQL Server，那么我的解决方案就在这里： http ： //dbsourcetools.codeplex.com/ 。

Have fun.

玩得开心。

- Nathan.

- 内森

#3楼

In my experience the solution is twofold:

根据我的经验，解决方案有两个方面：
  1. You need to handle changes to the development database that are done by multiple developers during development. 您需要处理开发期间由多个开发人员完成的开发数据库更改。
  2. You need to handle database upgrades in customers sites. 您需要在客户站点中处理数据库升级。

In order to handle #1 you'll need a strong database diff/merge tool.

为了处理＃1，您需要一个强大的数据库差异/合并工具。

The best tool should be able to perform automatic merge as much as possible while allowing you to resolve unhandled conflicts manually.

最好的工具应该能够尽可能多地执行自动合并，同时允许您手动解决未处理的冲突。

The perfect tool should handle merge operations by using a 3-way merge algorithm that brings into account the changes that were made in the THEIRS database and the MINE database, relative to the BASE database.

完美的工具应该通过使用3向合并算法来处理合并操作，该算法考虑了相对于BASE数据库在THEIRS数据库和MINE数据库中所做的更改。

I wrote a commercial tool that provides manual merge support for SQLite databases and I'm currently adding support for 3-way merge algorithm for SQLite.

我编写了一个商业工具，为SQLite数据库提供手动合并支持，我目前正在为SQLite添加对3向合并算法的支持。

Check it out at http://www.sqlitecompare.com

请访问http://www.sqlitecompare.com查看

In order to handle #2 you will need an upgrade framework in place.

为了处理＃2，您需要一个升级框架。

The basic idea is to develop an automatic upgrade framework that knows how to upgrade from an existing SQL schema to the newer SQL schema and can build an upgrade path for every existing DB installation.

基本思想是开发一个自动升级框架，该框架知道如何从现有SQL模式升级到较新的SQL模式，并可以为每个现有数据库安装构建升级路径。

Check out my article on the subject in http://www.codeproject.com/KB/database/sqlite_upgrade.aspx to get a general idea of what I'm talking about.

在http://www.codeproject.com/KB/database/sqlite_upgrade.aspx上查看我关于这个主题的文章，以大致了解我在说什么。

Good Luck

祝好运

Liron Levi

Liron Levi

#4楼

Because our app has to work across multiple RDBMSs, we store our schema definition in version control using the database-neutral Torque format (XML).

因为我们的应用程序必须跨多个RDBMS工作，所以我们使用数据库中立的Torque格式（XML）将我们的模式定义存储在版本控制中。

We also version-control the reference data for our database in XML format as follows (where "Relationship" is one of the reference tables):

我们还以XML格式对数据库的参考数据进行版本控制，如下所示（其中“Relationship”是参考表之一）： 
<Relationship RelationshipID="1" InternalName="Manager"/>
  <Relationship RelationshipID="2" InternalName="Delegate"/>
  etc.

We then use home-grown tools to generate the schema upgrade and reference data upgrade scripts that are required to go from version X of the database to version X + 1.

然后，我们使用自行开发的工具生成从数据库的X版本到版本X + 1所需的架构升级和参考数据升级脚本。

#5楼

It is a good approach to save database scripts into version control with change scripts so that you can upgrade any one database you have.

使用更改脚本将数据库脚本保存到版本控制是一种很好的方法，这样您就可以升级任何一个数据库。

Also you might want to save schemas for different versions so that you can create a full database without having to apply all the change scripts.

此外，您可能希望为不同版本保存模式，以便您可以创建完整数据库而无需应用所有更改脚本。

Handling the scripts should be automated so that you don't have to do manual work.

处理脚本应该是自动化的，这样您就不必进行手动操作。

I think its important to have a separate database for every developer and not use a shared database.

我认为为每个开发人员建立一个单独的数据库并且不使用共享数据库很重要。

That way the developers can create test cases and development phases independently from other developers.

这样，开发人员可以独立于其他开发人员创建测试用例和开发阶段。

The automating tool should have means for handling database metadata, which tells what databases are in what state of development and which tables contain version controllable data and so on.

自动化工具应该具有处理数据库元数据的方法，该方法告诉哪些数据库处于什么开发状态以及哪些表包含版本可控数据等等。

#6楼

To make the dump to a source code control system that little bit faster, you can see which objects have changed since last time by using the version information in sysobjects.

要使转储到更快一点的源代码控制系统，您可以使用sysobjects中的版本信息查看自上次以来哪些对象已更改。

Setup: Create a table in each database you want to check incrementally to hold the version information from the last time you checked it (empty on the first run).

设置：在每个要逐步检查的数据库中创建一个表，以保存上次检查时的版本信息（第一次运行时为空）。

Clear this table if you want to re-scan your whole data structure.

如果要重新扫描整个数据结构，请清除此表。 
IF ISNULL(OBJECT_ID('last_run_sysversions'), 0) <> 0 DROP TABLE last_run_sysversions
CREATE TABLE last_run_sysversions (
    name varchar(128), 
    id int, base_schema_ver int,
    schema_ver int,
    type char(2)
)

Normal running mode: You can take the results from this sql, and generate sql scripts for just the ones you're interested in, and put them into a source control of your choice.

正常运行模式：您可以从此sql获取结果，并为您感兴趣的那些生成sql脚本，并将它们放入您选择的源代码控制中。 
IF ISNULL(OBJECT_ID('tempdb.dbo.#tmp'), 0) <> 0 DROP TABLE #tmp
CREATE TABLE #tmp (
    name varchar(128), 
    id int, base_schema_ver int,
    schema_ver int,
    type char(2)
)

SET NOCOUNT ON

-- Insert the values from the end of the last run into #tmp
INSERT #tmp (name, id, base_schema_ver, schema_ver, type) 
SELECT name, id, base_schema_ver, schema_ver, type FROM last_run_sysversions

DELETE last_run_sysversions
INSERT last_run_sysversions (name, id, base_schema_ver, schema_ver, type)
SELECT name, id, base_schema_ver, schema_ver, type FROM sysobjects

-- This next bit lists all differences to scripts.
SET NOCOUNT OFF

--Renamed.
SELECT 'renamed' AS ChangeType, t.name, o.name AS extra_info, 1 AS Priority
FROM sysobjects o INNER JOIN #tmp t ON o.id = t.id
WHERE o.name <> t.name /*COLLATE*/
AND o.type IN ('TR', 'P' ,'U' ,'V')
UNION 

--Changed (using alter)
SELECT 'changed' AS ChangeType, o.name /*COLLATE*/, 
       'altered' AS extra_info, 2 AS Priority
FROM sysobjects o INNER JOIN #tmp t ON o.id = t.id 
WHERE (
   o.base_schema_ver <> t.base_schema_ver
OR o.schema_ver      <> t.schema_ver
)
AND  o.type IN ('TR', 'P' ,'U' ,'V')
AND  o.name NOT IN ( SELECT oi.name 
         FROM sysobjects oi INNER JOIN #tmp ti ON oi.id = ti.id
         WHERE oi.name <> ti.name /*COLLATE*/
         AND oi.type IN ('TR', 'P' ,'U' ,'V')) 
UNION

--Changed (actually dropped and recreated [but not renamed])
SELECT 'changed' AS ChangeType, t.name, 'dropped' AS extra_info, 2 AS Priority
FROM #tmp t
WHERE    t.name IN ( SELECT ti.name /*COLLATE*/ FROM #tmp ti
         WHERE NOT EXISTS (SELECT * FROM sysobjects oi
                           WHERE oi.id = ti.id))
AND  t.name IN ( SELECT oi.name /*COLLATE*/ FROM sysobjects oi
         WHERE NOT EXISTS (SELECT * FROM #tmp ti
                           WHERE oi.id = ti.id)
         AND   oi.type  IN ('TR', 'P' ,'U' ,'V'))
UNION

--Deleted
SELECT 'deleted' AS ChangeType, t.name, '' AS extra_info, 0 AS Priority
FROM #tmp t
WHERE NOT EXISTS (SELECT * FROM sysobjects o
                  WHERE o.id = t.id)
AND t.name NOT IN (  SELECT oi.name /*COLLATE*/ FROM sysobjects oi
         WHERE NOT EXISTS (SELECT * FROM #tmp ti
                           WHERE oi.id = ti.id)
         AND   oi.type  IN ('TR', 'P' ,'U' ,'V'))
UNION

--Added
SELECT 'added' AS ChangeType, o.name /*COLLATE*/, '' AS extra_info, 4 AS Priority
FROM sysobjects o
WHERE NOT EXISTS (SELECT * FROM #tmp t
                  WHERE o.id = t.id)
AND      o.type  IN ('TR', 'P' ,'U' ,'V')
AND  o.name NOT IN ( SELECT ti.name /*COLLATE*/ FROM #tmp ti
         WHERE NOT EXISTS (SELECT * FROM sysobjects oi
                           WHERE oi.id = ti.id))
ORDER BY Priority ASC

Note: If you use a non-standard collation in any of your databases, you will need to replace

with your database collation.

注意：如果在任何数据库中使用非标准排序规则，则需要将 替换为数据库排序规则。

ie

COLLATE Latin1_General_CI_AI

 即

COLLATE Latin1_General_CI_AI
SQL-Server database svn version-control
上一篇: 大数据行业步入快车道？忽视了它最终要被大浪淘
下一篇: 深度学习中的规范化1. 作用2. 做法3. 原理与使用参考

继续阅读