当前位置：文江博客话题详情

Oracle database-administration

如何管理生产数据库的架构升级？

发布于 2024-07-04 14:31:47 字数 157 浏览 6 评论 0 原文

这似乎是一个被忽视的领域，确实需要一些洞察力。您的最佳实践是什么：

升级过程
在出现错误的情况下退出
同步代码和数据库更改
等部署机制之前进行测试
在修改表

...

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

征﹌骨岁月お 2024-07-11 14:31:48

我听说过有关 iBATIS 3 架构迁移系统的好消息：

用户指南：http://svn。 apache.org/repos/asf/ibatis/java/ibatis-3/trunk/doc/en/iBATIS-3-Migrations.pdf

回复收藏 0 原文

揽清风入怀 2024-07-11 14:31:48

这些都是很重要的话题，但这是我的更新建议。

您没有指定您的平台，但对于 NANT 构建环境，我使用塔伦蒂诺。对于准备提交的每个数据库更新，您可以创建一个更改脚本（使用 RedGate 或其他工具）。当您构建到生产环境时，Tarantino 会检查脚本是否已在数据库上运行（它会向数据库添加一个表以进行跟踪）。如果没有，则运行该脚本。它消除了管理数据库版本的所有手动工作（即：人为错误）。

回复收藏 0 原文

浪荡不羁 2024-07-11 14:31:48

一般来说，我的规则是：“应用程序应该管理它自己的模式。”

这意味着架构升级脚本是应用程序的任何升级包的一部分，并在应用程序启动时自动运行。如果出现错误，应用程序将无法启动，并且不会提交升级脚本事务。这样做的缺点是应用程序必须具有对模式的完全修改访问权限（这会惹恼 DBA）。

我使用 Hibernates SchemaUpdate 功能来管理表结构取得了巨大成功。让升级脚本仅处理实际的数据初始化和偶尔删除列（SchemaUpdate 不会这样做）。

关于测试，由于升级是应用程序的一部分，因此测试它们成为应用程序测试周期的一部分。

事后思考：考虑到这里其他帖子中的一些批评，请注意规则说“它是自己的”。它仅真正适用于应用程序拥有架构的情况，就像作为产品出售的软件的一般情况一样。如果您的软件与其他软件共享数据库，请使用其他方法。

回复收藏 0 原文

蓝天 2024-07-11 14:31:48

这是一个很好的问题。（这很有可能会结束一场规范化与非规范化数据库的辩论……我不会开始……好吧，现在提供一些意见。）

我已经做过的一些事情（将当我有更多时间或需要休息时添加更多）

客户端设计 - 这就是内联 sql 的 VB 方法（即使使用准备好的语句）给您带来麻烦的地方。您可能会花费很长时间才能找到这些陈述。如果你使用 Hibernate 之类的东西，并将尽可能多的 SQL 放入命名查询中，你就会有一个地方来存放大部分 sql（没有什么比尝试测试某些 IF 语句中的 sql 而你只是没有点击“触发器”更糟糕的了） IF 语句测试中的标准）。在使用 hibernate（或其他 orms）之前，当我直接在 JDBC 或 ODBC 中执行 SQL 时，我会将所有 sql 语句作为对象的公共字段（具有命名约定）或属性文件中（也具有命名约定）。值的约定为 PREP_STMT_xxxx，并在 a) 测试用例 b) 应用程序启动时使用反射或迭代值（某些 RDBMS 允许您在执行前使用准备好的语句进行预编译，因此在登录后启动时）会在启动时预编译 prep-stmts 以使应用程序进行自我测试，即使是在一个好的 RDBMS 上也只需几秒钟，而且它在一个项目上节省了 DBA 的很多时间。不会沟通（不同的团队，在不同的国家），并且模式似乎每晚都会无缘无故地改变，每天早上我们都会在启动时得到一份具体的位置列表，

如果您需要临时功能，将其放在一个命名良好的类中（即，命名约定再次有助于自动测试），该类充当您查询的某种工厂（即它构建查询）。无论如何，您都必须编写等效的代码，只需将其放在可以测试的地方即可。您甚至可以在同一个对象或单独的类中编写一些基本的测试方法。

如果可以的话，也尝试使用存储过程。如上所述，它们的测试有点困难。一些数据库也不会在编译时仅在运行时根据模式预先验证存储过程中的 sql。它通常涉及获取模式结构的副本（无数据），然后根据该副本创建所有存储过程（以防数据库团队进行更改未正确验证）。从而可以检查结构。但作为变更管理的一个点，存储过程非常棒。改变一切都明白了。特别是当数据库更改是业务流程更改的结果时。所有语言（java、vb 等）都会发生变化，

我通常还会设置一个我使用的表，称为 system_setting 等。在这个表中，我们保留一个 VERSION 标识符。这样客户端库就可以连接并验证它们是否对此版本的架构有效。根据架构的更改，如果客户端可能损坏您的架构，您不希望允许客户端连接（即，数据库中没有很多引用规则，但在客户端上）。这取决于您是否还将拥有多个客户端版本（这确实发生在非网络应用程序中，即它们运行错误的二进制文件）。您还可以拥有批处理工具等。我也做过的另一种方法是在某种属性文件中或在 system_info 表中定义一组操作版本的模式。该表在登录时加载，然后由每个“管理器”（我通常有某种客户端 API 来执行大多数数据库操作）使用来验证该操作是否是正确的版本。因此，大多数操作都可以成功，但您也可能因过时的方法而失败（抛出一些异常），并告诉您原因。

管理模式更改 -> 您是否更新表或向新表添加 1-1 关系？由于这个原因，我见过很多商店总是通过视图访问数据。这允许更改表名、列等。我曾尝试过将视图实际视为 COM 中的接口的想法。 IE。您为新功能/版本添加新视图。通常，您会遇到很多采用表格格式的报告（尤其是最终用户自定义报告）。这些视图允许您部署新的表格式，但支持现有的客户端应用程序（记住所有那些讨厌的临时报告）。

另外，需要编写更新和回滚脚本。再次测试，测试，测试...

------------ 好吧 - 这是一个有点随机的讨论时间 --------------

实际上有一个大型广告我们遇到同样问题的项目（即软件商店）。该架构是 2 层，他们使用的产品有点像 PHP，但是 PHP 之前的版本。一样。不同的名字。无论如何，我进入的是版本 2....

升级花费了很多钱。很多。 IE。赠送数周的免费现场咨询时间。

现在已经到了想要添加新功能或优化代码的地步。一些现有代码使用存储过程，因此我们有可以管理代码的共同点。但其他领域是 html 中嵌入的 sql 标记。这对于快速进入市场来说非常有用，但随着新功能的每次交互，测试和维护的成本至少增加了一倍。因此，当我们考虑取出 php 类型代码、放入数据层（这是 2001-2002 年，在任何 ORM 之前等）并添加许多新功能（客户反馈）时，我们考虑了如何设计升级的问题进入系统。这是一件大事，因为正确的升级需要花费很多钱。现在，人们在一定程度上讨论的大多数模式和所有其他内容都涉及正在运行的 OO 代码，但是您的数据必须 a) 集成到此逻辑，b) 的含义和结构，这一事实又如何呢？数据可能会随着时间的推移而变化，并且通常由于数据的工作方式，您最终会在客户组织中出现许多需要该数据的子流程/应用程序 -> 特别报告或任何复杂的自定义报告，以及为自定义数据源等完成的批处理作业。

考虑到这一点，我开始尝试一些有点脱离领域的东西。它还有一些假设。 a) 数据的读取次数多于写入次数。 b) 更新确实发生，但不是在银行级别，即。每秒说一两个。

这个想法是将 COM / Interface 视图应用于客户端如何通过一组 CONCRETE 表（随架构更改而变化）访问数据。您可以为每种类型的操作创建一个单独的视图 - 更新、删除、插入和读取。这个很重要。这些视图要么直接映射到表，要么允许您触发一个虚拟表来执行真正的更新或插入等。我真正想要的是某种可捕获的级别间接，它仍然可以被水晶报告等使用。注意- 对于插入、更新和删除，您还可以使用存储过程。并且该产品的每个版本都有一个版本。这样，您的 1.0 版本就有其模式版本，如果表发生更改，您仍然拥有 1.0 版本视图，但具有新的后端逻辑来根据需要映射到新表，但您还拥有支持的 2.0 版本视图新领域等。这实际上只是为了支持临时报告，如果您是业务人员而不是编码员，那么这可能就是您拥有该产品的全部意义。（你的产品可能很糟糕，但如果你有世界上最好的报告，你仍然可以获胜，反之亦然 - 你的产品可能是最好的功能，但如果它的报告最差，你很容易就会失败）。

好的，希望其中一些想法有所帮助。

That's a great question. ( There is a high chance this is going to end up a normalised versus denormalised database debate..which I am not going to start... okay now for some input.)

some off the top of my head things I have done (will add more when I have some more time or need a break)

client design - this is where the VB method of inline sql (even with prepared statements) gets you into trouble. You can spend AGES just finding those statements. If you use something like Hibernate and put as much SQL into named queries you have a single place for most of the sql (nothing worse than trying to test sql that is inside of some IF statement and you just don't hit the "trigger" criteria in your testing for that IF statement). Prior to using hibernate (or other orms') when I would do SQL directly in JDBC or ODBC I would put all the sql statements as either public fields of an object (with a naming convention) or in a property file (also with a naming convention for the values say PREP_STMT_xxxx. And use either reflection or iterate over the values at startup in a) test cases b) startup of the application (some rdbms allow you to pre-compile with prepared statements before execution, so on startup post login I would pre-compile the prep-stmts at startup to make the application self testing. Even for 100's of statements on a good rdbms thats only a few seconds. and only once. And it has saved my butt a lot. On one project the DBA's wouldn't communicate (a different team, in a different country) and the schema seemed to change NIGHTLY, for no reason. And each morning we got a list of exactly where it broke the application, on startup.

If you need adhoc functionality , put it in a well named class (ie. again a naming convention helps with auto mated testing) that acts as some sort of factory for you query (ie. it builds the query). You are going to have to write the equivalent code anyway right, just put in a place you can test it. You can even write some basic test methods on the same object or in a separate class.

If you can , also try to use stored procedures. They are a bit harder to test as above. Some db's also don't pre-validate the sql in stored procs against the schema at compile time only at run time. It usually involves say taking a copy of the schema structure (no data) and then creating all stored procs against this copy (in case the db team making the changes DIDn't validate correctly). Thus the structure can be checked. but as a point of change management stored procs are great. On change all get it. Especially when the db changes are a result of business process changes. And all languages (java, vb, etc get the change )

I usually also setup a table I use called system_setting etc. In this table we keep a VERSION identifier. This is so that client libraries can connection and validate if they are valid for this version of the schema. Depending on the changes to your schema, you don't want to allow clients to connect if they can corrupt your schema (ie. you don't have a lot of referential rules in the db, but on the client). It depends if you are also going to have multiple client versions (which does happen in NON - web apps, ie. they are running the wrong binary). You could also have batch tools etc. Another approach which I have also done is define a set of schema to operation versions in some sort of property file or again in a system_info table. This table is loaded on login, and then used by each "manager" (I usually have some sort of client side api to do most db stuff) to validate for that operation if it is the right version. Thus most operations can succeed, but you can also fail (throw some exception) on out of date methods and tells you WHY.

managing the change to schema -> do you update the table or add 1-1 relationships to new tables ? I have seen a lot of shops which always access data via a view for this reason. This allows table names to change , columns etc. I have played with the idea of actually treating views like interfaces in COM. ie. you add a new VIEW for new functionality / versions. Often, what gets you here is that you can have a lot of reports (especially end user custom reports) that assume table formats. The views allow you to deploy a new table format but support existing client apps (remember all those pesky adhoc reports).

Also, need to write update and rollback scripts. and again TEST, TEST, TEST...

------------ OKAY - THIS IS A BIT RANDOM DISCUSSION TIME --------------

Actually had a large commercial project (ie. software shop) where we had the same problem. The architecture was a 2 tier and they were using a product a bit like PHP but pre-php. Same thing. different name. anyway i came in in version 2....

It was costing A LOT OF MONEY to do upgrades. A lot. ie. give away weeks of free consulting time on site.

And it was getting to the point of wanting to either add new features or optimize the code. Some of the existing code used stored procedures , so we had common points where we could manage code. but other areas were this embedded sql markup in html. Which was great for getting to market quickly but with each interaction of new features the cost at least doubled to test and maintain. So when we were looking at pulling out the php type code out, putting in data layers (this was 2001-2002, pre any ORM's etc) and adding a lot of new features (customer feedback) looked at this issue of how to engineer UPGRADES into the system. Which is a big deal, as upgrades cost a lot of money to do correctly. Now, most patterns and all the other stuff people discuss with a degree of energy deals with OO code that is running, but what about the fact that your data has to a) integrate to this logic, b) the meaning and also the structure of the data can change over time, and often due to the way data works you end up with a lot of sub process / applications in your clients organisation that needs that data -> ad hoc reporting or any complex custom reporting, as well as batch jobs that have been done for custom data feeds etc.

With this in mind i started playing with something a bit left of field. It also has a few assumptions. a) data is heavily read more than write. b) updates do happen, but not at bank levels ie. one or 2 a second say.

The idea was to apply a COM / Interface view to how data was accessed by clients over a set of CONCRETE tables (which varied with schema changes). You could create a seperate view for each type operation - update, delete, insert and read. This is important. The views would either map directly to a table , or allow you to trigger of a dummy table that does the real updates or inserts etc. What i actually wanted was some sort of trappable level indirection that could still be used by crystal reports etc. NOTE - For inserts , update and deletes you could also use stored procs. And you had a version for each version of the product. That way your version 1.0 had its version of the schema, and if the tables changed, you would still have the version 1.0 VIEWS but with NEW backend logic to map to the new tables as needed, but you also had version 2.0 views that would support new fields etc. This was really just to support ad hoc reporting, which if your a BUSINESS person and not a coder is probably the whole point of why you have the product. (your product can be crap but if you have the best reporting in the world you can still win, the reverse is true - your product can be the best feature wise, but if its the worse on reporting you can very easily loose).

okay, hope some of those ideas help.

回复收藏 0 原文