在代码控制下的数据库脚本的最佳实践是什么 [英] What are the best practices for database scripts under code control

问题描述

我们目前正在审查如何将我们的数据库脚本（表，proc，函数，视图，数据修复）存储在subversion中，我想知道是否有什么共识是什么是最好的方法？

我们需要考虑的一些因素包括：

• 如何跟踪指定发行版的数据库状态


• 应该很容易


• 如果数据库中存在表，列出了针对它运行的脚本或数据库的版本等。

显然这是一个相当开放的问题，所以我很想知道人们的经验教给他们。

<经过几次迭代，我们采用的方法大致如下：

每个表一个文件，每个存储过程。

表的文件以CREATE命令开始，并且一系列的ALTER命令在模式发展时添加。这些命令中的每一个都在测试中是否已经存在表或列的括号内。这意味着每个脚本都可以在最新的数据库中运行，并且不会更改任何内容。这也意味着对于任何旧数据库，脚本将其更新为最新的模式。对于空数据库，CREATE脚本创建表，并且ALTER脚本都将被跳过。

我们还有一个程序（用Python编写），它扫描完整的脚本目录并将其汇编到一个大脚本中。它解析SQL足够推导表之间的依赖（基于外键引用），并适当地排序。结果是一个怪物SQL脚本，使数据库一次达到规格。脚本汇编程序还计算输入文件的MD5哈希值，并使用它来更新写入列表中最后一个脚本中的特殊表中的版本号。

除去事故，结果是，给定版本的源代码的数据库脚本创建了此代码旨在与之互操作的模式。这也意味着有一个（有点大）的SQL脚本给客户建立新的数据库或更新现有的数据库。 （在这种情况下，这是很重要的，因为会有很多数据库实例，每个客户有一个实例。）

We are currently reviewing how we store our database scripts (tables, procs, functions, views, data fixes) in subversion and I was wondering if there is any consensus as to what is the best approach?

Some of the factors we'd need to consider include:

• Should we checkin 'Create' scripts or checkin incremental changes with 'Alter' scripts
• How do we keep track of the state of the database for a given release
• It should be easy to build a database from scratch for any given release version
• Should a table exist in the database listing the scripts that have run against it, or the version of the database etc.

Obviously it's a pretty open ended question, so I'm keen to hear what people's experience has taught them.

解决方案

After a few iterations, the approach we took was roughly like this:

One file per table and per stored procedure. Also separate files for other things like setting up database users, populating look-up tables with their data.

The file for a table starts with the CREATE command and a succession of ALTER commands added as the schema evolves. Each of these commands is bracketed in tests for whether the table or column already exists. This means each script can be run in an up-to-date database and won't change anything. It also means that for any old database, the script updates it to the latest schema. And for an empty database the CREATE script creates the table and the ALTER scripts are all skipped.

We also have a program (written in Python) that scans the directory full of scripts and assembles them in to one big script. It parses the SQL just enough to deduce dependencies between tables (based on foreign-key references) and order them appropriately. The result is a monster SQL script that gets the database up to spec in one go. The script-assembling program also calculates the MD5 hash of the input files, and uses that to update a version number that is written in to a special table in the last script in the list.

Barring accidents, the result is that the database script for a give version of the source code creates the schema this code was designed to interoperate with. It also means that there is a single (somewhat large) SQL script to give to the customer to build new databases or update existing ones. (This was important in this case because there would be many instances of the database, one for each of their customers.)

这篇关于在代码控制下的数据库脚本的最佳实践是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！