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

本文是小编为大家收集整理的关于代码控制下的数据库脚本的最佳实践是什么?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我们目前正在审查颠覆中如何存储数据库脚本(表,procs,函数,视图,数据修复),我想知道是否有关于最佳方法的共识?

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

  • 我们应该检查"创建"脚本或使用'Alter'脚本
  • 检查脚本的脚本更改
  • 我们如何跟踪给定版本的数据库状态
  • 对于任何给定的版本版本,从头开始构建数据库
  • 应该很容易
  • 应该在数据库中存在一个表,列出了已对其进行的脚本或数据库等版本等.

显然这是一个非常开放的问题,所以我很想听听人们的经历教给他们的东西.

推荐答案

经过几次迭代,我们采用的方法大致是这样的:

每个表和每个存储过程一个文件.还为其他内容分开文件,例如设置数据库用户,填充数据库用户.

表的文件从创建命令开始,随着架构的发展,添加了一系列Alter命令.这些命令中的每一个都在测试中括起来,以了解该表是否已经存在.这意味着每个脚本可以在最新数据库中运行,并且不会更改任何内容.这也意味着对于任何旧数据库,脚本都将其更新为最新的模式.对于一个空数据库,创建脚本创建表,并且Alter脚本都跳过了.

我们还有一个程序(用python编写),该程序扫描了装满脚本的目录,并将它们组装成一个大脚本.它可以解析SQL足以推断出表(基于外国钥匙引用)之间的依赖项并适当订购它们.结果是一个Monster SQL脚本,该脚本将数据库一口气达到规格.脚本组装程序还计算了输入文件的MD5哈希,并使用它来更新列表中最后一个脚本中的特殊表中写入的版本号.

禁止事故,结果是源代码的给出版本的数据库脚本创建了该代码旨在与之互操作的架构.这也意味着有一个(有点大)的SQL脚本可以给客户构建新数据库或更新现有数据库. (在这种情况下,这很重要,因为数据库将有很多实例,每个客户都有一个.)

其他推荐答案

此链接上有一篇有趣的文章: https://blog.codinghorror.com/get-horror.com/get-your-database -dound-vers-control/

它提倡一个基线"创建"脚本,然后检查" Alter"脚本并将版本表保存在数据库中.

其他推荐答案

您可以通过阅读如何使用 Ruby on Rails的迁移. 理解这一点的最佳方法可能是自己尝试一下,然后手动检查数据库.

回答您的每个因素:

  • 存储创建脚本.如果要结帐版本X.Y.Z,那么很高兴仅运行创建脚本即可立即设置数据库.您可以还可以添加ARTER脚本,从上一个版本转到下一个版本(例如,您提交版本3包含版本3创建脚本和版本2→3 Alter脚本).
  • 请参阅Rails迁移解决方案.基本上,他们将表版本编号保留在数据库中,因此您始终知道.
  • 使用创建脚本.
  • 使用版本号可能是最通用的解决方案 - 脚本名称和路径可能会随着时间而变化.

我的两分钱!

本文地址:https://www.itbaoku.cn/post/597404.html

问题描述

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.)

其他推荐答案

There is an interesting article at this link: https://blog.codinghorror.com/get-your-database-under-version-control/

It advocates a baseline 'create' script followed by checking in 'alter' scripts and keeping a version table in the database.

其他推荐答案

You could get some hints by reading how this is done with Ruby On Rails' migrations. The best way to understand this is probably to just try it out yourself, and then inspecting the database manually.

Answers to each of your factors:

  • Store CREATE scripts. If you want to checkout version x.y.z then it'd be nice to simply run your create script to setup the database immediately. You could add ALTER scripts as well to go from the previous version to the next (e.g., you commit version 3 which contains a version 3 CREATE script and a version 2 → 3 alter script).
  • See the Rails migration solution. Basically they keep the table version number in the database, so you always know.
  • Use CREATE scripts.
  • Using version numbers would probably be the most generic solution — script names and paths can change over time.

My two cents!