您是否将数据库静态数据放入源代码控制中?如何? [英] Do you put your database static data into source-control ? How?

问题描述

我使用的是 SQL-Server 2008 和 Visual Studio 数据库版.

I'm using SQL-Server 2008 with Visual Studio Database Edition.

通过这种设置，让您的架构保持同步非常容易.基本上，有一个比较架构"工具，可让我将两个数据库的架构和/或数据库架构与源控制的创建脚本文件夹同步.

With this setup, keeping your schema in sync is very easy. Basically, there's a 'compare schema' tool that allow me to sync the schema of two databases and/or a database schema with a source-controlled creation script folder.

然而，当涉及到数据时，情况就不太清楚了，数据可以分为三种:

However, the situation is less clear when it comes to data, which can be of three different kind :

• 代码中引用的静态数据.典型示例:我的用户可以更改他们的设置，并且他们的配置存储在服务器上.但是，每个设置都有一个系统范围的默认值，以防用户没有覆盖它.随着更多选项添加到程序中，包含这些默认设置的表会增长.这意味着，当签入新功能/选项时，通常还会在数据库中创建系统范围的默认设置.

• static data referenced in the code. typical example : my users can change their setting, and their configuration is stored on the server. However, there's a system-wide default value for each setting that is used in case the user didn't override it. The table containing those default settings grows as more options are added to the program. This means that when a new feature/option is checked in, the system-wide default setting is usually created in the database as well.

静态数据.例如.填充下拉列表的产品列表.该程序不依赖于列表中特定产品的存在来工作.例如，这可以是在部署程序的新unicode 版本"时应在生产中部署的 unicode 编码产品列表.

static data. eg. a product list populating a dropdown list. The program doesn't rely on the existence of a specific product in the list to work. This can be for example a list of unicode-encoded products that should be deployed in production when the new "unicode version" of the program is deployed.

其他数据，即其他所有数据(日志、用户帐户、用户数据等)

other data, ie everything else (logs, user accounts, user data, etc.)

对我来说很明显我的第三个项目不应该受源控制(当然，它应该定期备份)

It seems obvious to me that my third item shouldn't be source-controlled (of course, it should be backuped on a regular basis)

但是关于静态数据，我想知道该怎么做.

But regarding the static data, I'm wondering what to do.

• 我应该将插入脚本附加到创建脚本吗?或者使用单独的脚本?

• Should I append the insert scripts to the creation scripts? or maybe use separate scripts?

我(作为开发人员)如何警告进行部署的人员他们应该执行插入语句?

How do I (as a developer) warn the people doing the deployment that they should execute an insert statement ?

我应该区分我的两种数据吗?(第一个通常由开发人员创建，而第二个通常由非开发人员创建)

Should I differentiate my two kind of data? (the first one being usually created by a dev, while the second one is usually created by a non-dev)

你如何管理你的数据库静态数据?

How do you manage your DB static data ?

推荐答案

我已经解释了我在我的博客中使用的技术 版本控制和您的数据库.我使用数据库元数据(在本例中为 SQL Server 扩展属性)来存储部署的应用程序版本.我只有从版本升级到版本的脚本.在启动时，应用程序从数据库元数据中读取部署的版本(缺少元数据被解释为版本 0，即尚未部署任何内容).每个版本都有一个升级到下一个版本的应用程序功能.通常，此函数运行执行升级的内部资源 T-SQL 脚本，但也可以是其他内容，例如在数据库中部署 CLR 程序集.

I have explained the technique I used in my blog Version Control and Your Database. I use database metadata (in this case SQL Server extended properties) to store the deployed application version. I only have scripts that upgrade from version to version. At startup the application reads the deployed version from the database metadata (lack of metadata is interpreted as version 0, ie. nothing is yet deployed). For each version there is an application function that upgrades to the next version. Usually this function runs an internal resource T-SQL script that does the upgrade, but it can be something else, like deploying a CLR assembly in the database.

没有用于部署当前"数据库架构的脚本.新版本迭代所有中间版本，从版本 1 到当前版本.

There is no script to deploy the 'current' database schema. New installments iterate trough all intermediate versions, from version 1 to current version.

我喜欢这种技术的几个优点:

There are several advantages I enjoy by this technique:

• 测试新版本对我来说很容易.我有以前版本的备份，我应用了升级脚本，然后我可以恢复到以前的版本，更改脚本，再试一次，直到我对结果满意为止.
• 我的应用程序可以部署在任何以前的版本之上.不同的客户端有不同的部署版本.当他们升级时，我的应用程序支持从任何以前的版本升级.
• 全新安装和升级没有区别，它运行相同的代码，所以我需要维护和测试的代码路径更少.
• DML 和 DDL 更改之间没有区别(您的原始问题).他们都以相同的方式对待，因为脚本运行会从一个版本更改为下一个版本.当我需要像您描述的那样进行更改(更改默认值)时，我实际上会增加架构版本即使没有发生其他 DDL 更改.所以在 5.1 版本中默认是 'foo'，在 5.2 中默认是 'bar' 这是两个版本之间的唯一区别，而 'upgrade' 步骤只是一个 UPDATE 语句(后面是当然是通过版本元数据更改，即 sp_updateextendedproperty).
• 所有更改都在源代码管理中，属于应用程序源的一部分(主要是 T-SQL 脚本).
• 我可以轻松获得任何以前的架构版本，例如.重现客户投诉，只需运行升级序列并在我感兴趣的版本处停止即可.

• Is easy for me to test a new version. I have a backup of the previous version, I apply the upgrade script, then I can revert to the previous version, change the script, try again, until I'm happy with the result.
• My application can be deployed on top of any previous version. Various clients have various deployed version. When they upgrade, my application supports upgrade from any previous version.
• There is no difference between a fresh install and an upgrade, it runs the same code, so I have fewer code paths to maintain and test.
• There is no difference between DML and DDL changes (your original question). they all treated the same way, as script run to change from one version to next. When I need to make a change like you describe (change a default), I actually increase the schema version even if no other DDL change occurs. So at version 5.1 the default was 'foo', in 5.2 the default is 'bar' and that is the only difference between the two versions, and the 'upgrade' step is simply an UPDATE statement (followed of course by the version metadata change, ie. sp_updateextendedproperty).
• All changes are in source control, part of the application sources (T-SQL scripts mostly).
• I can easily get to any previous schema version, eg. to repro a customer complaint, simply by running the upgrade sequence and stopping at the version I'm interested in.

这种方法多次挽救了我的皮肤，我现在是一个真正的信徒.只有一个缺点:在源代码中没有明显的地方可以找到过程 foo 的当前形式是什么?".因为最新版本的 foo 可能已经升级了 2 或 3 个版本，并且此后没有更改，所以我需要查看 那个 版本的升级脚本.我通常只查看数据库并查看其中的内容，而不是搜索升级脚本.

This approach saved my skin a number of times and I'm a true believer now. There is only one disadvantage: there is no obvious place to look in source to find 'what is the current form of procedure foo?'. Because the latest version of foo might have been upgraded 2 or 3 versions ago and it wasn't changed since, I need to look at the upgrade script for that version. I usually resort to just looking into the database and see what's in there, rather than searching through the upgrade scripts.

最后一点:这实际上不是我的发明.这完全按照 SQL Server 本身如何升级数据库元数据 (mssqlsystemresource) 进行建模.

One final note: this is actually not my invention. This is modeled exactly after how SQL Server itself upgrades the database metadata (mssqlsystemresource).

这篇关于您是否将数据库静态数据放入源代码控制中?如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！