动态修补数据库 [英] Dynamic patching of databases

问题描述

请原谅我的长期问题。我对设计有一个想法，可以在上面提出一些意见。这样做是个好主意吗？我应该知道什么是深渊瀑布？还有其他类似的实现方法更好吗？

Please forgive my long question. I have an idea for a design that I could use some comments on. Is it a good idea to do this? And what are the pit falls I should be aware of? Are there other similar implementations that are better?

我的情况如下：

我正在重写Windows窗体应用程序连接到SQL 2008（以前是SQL 2005）服务器的过程。该应用程序是工程公司的专家系统，我们在其中存储有关构造的结构化数据。我们可以控制客户端软件的所有安装，没有外部客户或用户，它们都是公司内部的东西，并且可以信任他们不会对软件或数据库进行任何恶意操作。

My situation is as follows:
I am working on a rewrite of a windows forms application that connects to a SQL 2008 (earlier it was SQL 2005) server. The application is an "expert-system" for an engineering company where we store structured data about constructions. We have control of all installations of the client software, we have no external customers or users, they are all internal to the company, and they are all be trusted not to do anything malicious to the software or database.

当前的设计没有太多的表（大约10到20），但是其中一些表具有数百万个属于数百个构造的记录。到目前为止，系统性能尚可，但是随着我们不断突破设计极限，它开始下降。

The current design doesn't have too many tables (about 10 - 20) but some of them have millions of records that belong to several hundred constructions. The systems performance has been ok so far, but it is starting to degrade as we are pushing the limits of the design.

作为重写的一部分，我正在考虑将数据库分为一个主数据库和几个子数据库，每个子数据库描述一个构造。每个子数据库应具有相同的设计。这应该可以消除我们今天看到的性能问题，因为存储在每个数据库中的数据将少于总数据量的百分之一。

As part of the rewrite I am considering splitting the database into one master database and several "child" databases where each describes one construction. Each child database should be of identical design. This should eliminate the performance problems we are seeing today since the data stored in each database would be less than one percent of the total data amount.

我担心的是，我们现在不用维护一个数据库，而是获得数百个必须保持最新状态的数据库。随着公司需求变更（您知道情况如何），该系统在不断发展，并且我们尽力减少变更的数量。因此，我们将需要一个系统来跟踪对系统所做的所有数据库更改，以便将其应用于子数据库。更新客户端应用程序不会有问题，我们可以很好地控制这一方面。

My concern is that instead of maintaining one database we will now get hundreds of databases that must be kept up to date. The system is constantly evolving as the companys requirements change (you know how it is), and while we try to look forward to reduce the number of changes the changes will come. So we will need a system where we keep track of all database changes done to the system so they can be applied to the child databases. Updating the client application won't be a problem, we have good control of that aspect.

我正在考虑一个变更跟踪系统，在该系统中我们存储所有变更的数据库脚本在master数据库中的表中。然后，我们可以为每个更改提供一个版本号，并可以在每个子数据库中存储一个当前版本号。当客户端程序连接到子数据库时，我们可以对照主数据库的当前版本号检查数据库的版本号，如果有补丁程序的版本号大于子数据库的版本号，我们将运行它们并进行更新子数据库为最新版本。

I am thinking of a change tracing system where we store database scripts for all changes in a table in the master database. We can then give each change a version number and we can store a current version number in each child database. When the client program connects to a child database we can then check the version number of the database against the current version number of the master database and if there are patches with version numbers greater than the version number of the child database we run these and update the child database to the latest version.

我认为这应该很好。对系统的任何更改将首先进行测试和验证，然后再提交为数据库的新版本。然后，该更改将在用户第一次打开它时应用于数据库。我想我们将在应用更改时以独占模式打开数据库，但是只要更改不是太频繁，这都不是问题。

As I see it this should work well. Any changes to the system will first be tested and validated before committed as a new version of the database. The change will then be applied to the database the first time a user opens it. I suppose we would open the database in exclusive mode while applying the changes, but as long as the changes aren't too frequent this should not be a problem.

那又怎样？你认为？这样行吗？你们有做过类似的事情吗？

So what do you think? Will this work? Have any of you done something similar? Should we scrap the solution and go for the monolithic system instead?

推荐答案

我在这里也有类似的情况，尽管我使用的是MySQL。每个数据库都有一个版本表，该表包含版本（简单地是一个整数）和该版本中已更改内容的简短注释。我使用脚本来更新数据库。每个数据库更改都可以在一个功能中进行，有时一个更改是由多个功能进行的。函数在函数名称中包含版本号。该脚本在数据库中查找最高版本号，并且仅按顺序应用具有更高版本号的功能。

I have a similar situation here, though I use MySQL. Every database has a versions table that contains the version (simply an integer) and a short comment of what has changed in this version. I use a script to update the databases. Every database change can be in one function or sometimes one change is made by multiple functions. Functions contain the version number in the function name. The script looks up the highest version number in a database and applies only the functions that have a higher version number in order.

这使得更新数据库（只需添加新的更改功能），并允许我在必要时快速升级恢复的数据库（只需再次运行脚本）。

This makes it easy to update databases (just add new change functions) and allows me to quickly upgrade a recovered database if necessary (just run the script again).

即使在测试更改之前也可以进行防御性更改。如果您在桌子上做了一些大的改动并且想要安全地玩：

Even when testing the changes before this allows for defensive changes. If you make some heavy changes on a table and you want to play it safe:

def change103(...):
    "Create new table."
def change104(...):
    """Transfer data from old table to new table and make
       complicated changes in the process.
    """
def change105(...):
    "Drop old table"
def change106(...):
    "Rename new table to old table"

如果在change104（）中出了点问题（并引发异常），您可以简单地从新表中删除已转换的数据，并修复更改功能并再次运行脚本。

if in change104() is something going wrong (and throws an exception) you can simply delete the already converted data from the new table, fix your change function and run the script again.

但是我认为在客户端连接时动态更改数据库不是一个好主意。有时更改可能需要一些时间。并且访问数据库的软件应与数据库的架构匹配。您可以通过某种方式使它们保持同步。也许您可以分发新的软件版本，然后在客户端实际开始使用此新软件时要升级数据库。但是我还没有尝试过。

But I don't think that changing a database dynamically when a client connects is a good idea. Sometimes changes can take some time. And the software that accesses a database should match the schema of the database. You have somehow to keep them in sync. Maybe you could distribute a new software version and then you want to upgrade the database when a client is actually starting to use this new software. But I haven't tried that.

这篇关于动态修补数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！