数据库版本/数据的更改控制不是架构？ [英] Database Version / Change Control for Data not Schema?

问题描述

在这里和周围阅读了几篇文章之后，我意识到开发团队中的数据库版本控制实际上非常重要。

After reading a few articles here and around, I have realised that database version control in a development team is actually of high importance.

到目前为止，我一直在使用一个简单的 dump整个数据库每次有一个更新，如果只有一个表被改变有时我们可以逃避只是倾销单表，然后重新导入。

Until now I have been using a simple dump whole database each time there is an update, if only 1 table was altered sometimes we can get away with just dumping the single table then reimporting. Not the best but it works quite well, for additive changes and we haven't had any hiccups yet.

现在，我保存了一个 .mwb （Mysql Workbench图）文件在我正在处理的项目的git存储库。
然后，我还使用 dbv 模式管理，以及git，每个分支根据项目命名，它的工作相当不错。这允许我对版本示意图更改恢复或回滚的能力。

Now, I save a .mwb (Mysql Workbench diagram) file in the git repository of the project I'm working on. Then I also use dbv for schema management, along with git, with each branch being named based on the project and it's working quite well. This allows me to version schematic changes with the ability to revert or rollback.

但是，表中包含的数据。如何维持这一点？也许我最好只是坚持旧的方法。我了解具有相同数据库结构但不同数据的项目，但很好，但对于需要版本化和管理的特定数据库数据的网站。

However, what about the data contained in the tables. How can this be maintained? Maybe I'm better off just sticking with the old method. I understand on projects with the same DB structure but different data that's fine but what about sites with specific database data that needs to be versioned and managed.

的已经部署的站点需要数据库更改，这是如何无缝。一些人建议使用update / alter脚本，并与默认值等工作正常。但是，如果我在一个网站平台上进行了更改，要求更改每个网站数据库，并保持数据完整，那该怎么办？

Also what about the base of already deployed sites that need database changes, how can this be seamless. Some have suggested the use of update/alter scripts and that works fine with default values and such. But what if I have made a change on a website platform that requires every websites database to be changed, and keep the data intact?

推荐答案

p>我主要从事业务应用开发和配置管理。您的问题代表了在这种环境中的挑战;当您升级实例Microsoft Word时，您不需要立即将所有文档从doc更改为docx。并且文档甚至有一个更简单的结构一个完整的关系数据库。

I've worked mostly in business application development and configuration management. Your question is representative for the challenges in such an environment; when you upgrade for instance Microsoft Word, you don't need to change all documents right away from doc to docx. And the documents even have a more simple structure a full relation database.

不适用于商业应用;用户跳过版本，对数据模型进行未经授权的更改，系统需要继续运行并提供正确的数字...

Not so for business applications; users skip releases, make unauthorized changes to the data model and the system needs to keep running and providing the correct numbers...

我们用于我们自己的应用程序

We use for our own applications (largest one is like 600 tables) a self-developed CASE tool which includes branching/merging, but the approach can also be done manually.

数据模型可以以结构化的方式写下来。例如作为表内容（要在带有元数据的表中加载的CSV），或者作为检测正在使用的版本的代码，并在缺失时添加列和表，包括不重要的迁移。

The data model can be written down in a structured way. For instance as table contents (CSV to be loaded in a table with meta data) or as code that detects the version in use and adds columns and tables when missing, including non-trivial migrations.

这甚至允许多个用户同时更改数据模型。

This even allows multiple users at the same time to change the data model.

检测（例如，我们使用名为verify_column而不是add_column的调用），这甚至允许独立于客户开始升级的版本号的平滑迁移。这种过程分析要更改的表，并在列缺失或 alter时发出正确的DDL，例如 alter table t1 add col1 number not null

When you use auto-detection (for instance, we use a call named "verify_column" instead of "add_column"), this even allows smooth migration independent of the release number the customer is starting the upgrade from. Such a procedure analyzes the table to be changed and issues the correct DDL such as alter table t1 add col1 number not null when a column is missing or alter table t1 modify col1 not null when the column was already present but nullable.

对于Oracle和SQL Server，我可以为您提供几个示例程序。在MySQL中，我将使用客户端语言编写代码，最好是独立于操作系统，以允许安装在Windows和Linux上运行。也许在你有这方面的经验时使用Apache Ant。

For Oracle and SQL Server I can provide you with a few sample procedures. In MySQL I would code this using a client side language, preferably OS independent to allow installations to run on Windows and Linux. Maybe using Apache Ant when you have experience with that.

：

• R：参考数据;应用程序站点在实际使用系统之前必须提供的数据。例如，总帐科目代码。引用数据在生存后很少改变，并且不会不断增大。


• T：交易数据;站点在应用程序使用期间注册，更改和删除的数据。例如，总分类帐条目。事务数据从0开始连续增长。当公司收入翻番时，交易数据也翻倍。


• S：种子数据;数据不是由用户在现场维护，而是由开发方提供和维护的。本质上这是将代码转换为数据。例如，'F'代表女性。


• 其余（理想情况下不需要，因为它们是技术性的，但有些系统需要临时表A或临时表B）。

• R: referential data; data the application site must provide before he actually use the system. For instance, general ledger account codes. The referential data seldomly changes after go live and does not continuously grow in size. The contents reflect the site's business model where the application is used.
• T: transaction data; data the site registers, changes and removes during use of the application. For instance, general ledger entries. The transaction data starts at 0 an grows continuously. When company doubles in revenues, transaction data also doubles.
• S: seeded data; data NOT maintained by the user at the site but provided and maintained by the developing party. Essentially this is code turned into data. For example, 'F' stands for 'Female'. Errors in seeded data can lead to system errors.
• O: the rest (ideally not needed, since they are technical, but some systems require a temporary table A or a scratch table B).

类别S（种子数据）的表的内容放在版本控制之下。我们通常在我们的案例工具中将这些元数据注册为元数据，然后命名为数据集，但也可以使用例如Microsoft Excel或甚至代码。

The contents of tables of category 'S' (seeded data) is placed under version control. We normally register these as metadata in our case tool, then named 'data sets', but you can also use for instance Microsoft Excel or even code.

Excel中，您将有一个种子数据行的列表。在A列中，您可以输入一个Excel函数，如 = B ..&|​​& C ..& | &

For example, in Excel you would have a list of rows of seeded data. In column A you might enter an Excel function like =B..&"|"&C..& "|" & ... which concatenates everything and makes it suitable for loading by a loader tool.

例如在代码中，你可能有一个调用：

For example in code, you might have a call like:

verifySeed('TABLE_A', 'CODE', 'VALUE')

Excel在版本控制下稍微有些困难，允许多个用户同时更改内容。使用代码的方法非常简单。

The Excel is a little bit hard to bring under version control allowing multiple users to change contents at the same time. The approach with code is very simple.

请记住添加功能以删除过时的种子数据。例如，通过明确列出过时的种子数据，或者自动删除表中存在的所有种子数据，但最后安装时未触及。

Please remember to also add features to remove obsoleted seeded data. For instance, by explicitly listing obsoleted seeded data or by automatically removing all seeded data present in the tables but not touched by the last installation.

这篇关于数据库版本/数据的更改控制不是架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！