完整的数据库架构转换-如何测试重写的查询? [英] Complete db schema transformation - how to test rewritten queries?

问题描述

我们的数据库在所有方面的设计都很差(我们继承了它).我已经将架构重新设计为可用和可维护的东西.删除了一些表和列，移动了许多列，并重命名了大多数表和列.某些数据类型也已更改.

我已经从我们的Web应用程序中提取了所有查询，并且我们已经开始重写它们.我们认为，我们的DBA能够将旧数据迁移到新架构.为了确保我们需要通过将旧结果与新结果进行比较来测试每个查询.

我们如何测试这种大规模迁移?我需要能够指定参数，并将旧表/列映射到新表/列.对于数百个查询，这是一项艰巨的任务.我可以自己写点东西，但是我对时间的要求很高，因此最好使用现有工具.

谢谢！

解决方案

我必须这样做...而且很容易，因为我重写了整个应用程序;)

许多查询听起来像基本操作，例如select，insert，updates尚未在函数中抽象化-也许可以帮助在适应之前清理混乱.

现在进行测试:

您需要一个测试脚本，该脚本将 a)运行所有查询 b)存储所有选择的输出以进行比较

1. 备份测试数据库@状态0，清除常规查询日志

2. 使用所有删除，选择和更新来遍历您的应用程序

3. 复制该日志，粘贴每个选择，然后在其前面加上创建表temptable_xyz"(当然，选择SELECT到temptable_xyz中也要取决于可用语法).

4. 在两个数据库上运行，在迁移脚本后测试db @ state 0和测试db @ state 0

5. 比较

如果您可以确保在每个应用程序中都使用了每个功能，则应该这样做.

GL-就像使现有的东西变得更好；)

Our database is poorly designed all the way around (we inherited it). I've reworked the schema to something useable and maintainable. Quite a few tables and columns have been dropped, many columns have moved and most tables and columns have been renamed. Some datatypes have been changed also.

I've extracted all the queries from our webapps and we've started rewriting them. Our DBA is able to migrate the old data to the new schema, we think. To be sure we need to test each query by comparing the old results with the new.

How can we test such a wholesale migration? I need to be able to specify parameters, and map old tables/columns to new tables/columns. With hundreds of queries this is a daunting task. I could write something myself but I already have a lot of demands on my time so using an existing tool is preferable.

Thanks!

解决方案

I've had to do this ... and well it was easy because i rewrote the entire application ;)

Many queries sounds like basic operations such as select,insert,updates have not been abstracted in functions - maybe that can help clean up the mess before adapting.

Now for the testing:

You need a test script that will a) run all your queries b) store output of all selects for comparison

1. backup your test db @ state 0, clear the general query log

2. play around your application using all the deletes, selects and updates,

3. copy paste that log, take every single select and precede it with a "Create table temptable_xyz" (or of course SELECT into temptable_xyz .. depends on the available syntax)

4. run on both databases, test db @ state 0 and test db @ state 0 after migration script

5. compare

This should do it if you can make sure you used every feature in every app.

GL - nothing like making existing stuff better ;)

这篇关于完整的数据库架构转换-如何测试重写的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！