从Oracle实例创建内存数据库结构 [英] Create an in-memory database structure from an Oracle instance

问题描述

我有一个应用程序，其中许多unit测试在执行期间使用与Oracle数据库的真实连接。

I have an application where many "unit" tests use a real connection to an Oracle database during their execution.

尽可能想象一下，这些测试需要花费太多时间来执行，因为他们需要初始化一些Spring上下文，并与Oracle实例进行通信。除此之外，我们必须管理复杂的机制，例如事务，以避免在测试执行后修改数据库（即使我们使用Spring中的有用类，如 AbstractAnnotationAwareTransactionalTests ）。

As you can imagine, these tests take too much time to be executed, as they need to initialize some Spring contexts, and communicate to the Oracle instance. In addition to that, we have to manage complex mechanisms, such as transactions, in order to avoid database modifications after the test execution (even if we use usefull classes from Spring like AbstractAnnotationAwareTransactionalTests).

所以我的想法是逐步用内存数据库替换这个Oracle测试实例。我将使用 hsqldb 或者更好 h2 。

So my idea is to progressively replace this Oracle test instance by an in-memory database. I will use hsqldb or maybe better h2.

我的问题是要知道这样做的最佳方法是什么。我主要关心的是构建内存数据库结构和插入引用数据。

My question is to know what is the best approach to do that. My main concern is related to the construction of the in-memory database structure and insertion of reference data.

当然，我可以从Oracle中提取数据库结构，使用一些工具如 SQL Developer 或 TOAD ，然后修改这些脚本以使它们适应 hsqldb 或 h2 语言。但我认为这不是更好的方法。

Of course, I can extract the database structure from Oracle, using some tools like SQL Developer or TOAD, and then modifying these scripts to adapt them to the hsqldb or h2 language. But I don't think that's the better approach.

事实上，我已经在另一个项目中使用 hsqldb ，但我手动编写了所有脚本来创建表。幸运的是，我只创建了几张桌子。我在此步骤中遇到的主要问题是将用于创建表的Oracle脚本转换为 hsqldb 语言。

In fact, I already did that on another project using hsqldb, but I have written manually all the scripts to create tables. Fortunately, I had only few tables to create. My main problem during this step was to "translate" the Oracle scripts used to create tables into the hsqldb language.

例如，使用以下sql命令在Oracle中创建的表：

For example, a table created in Oracle using the following sql command:

CREATE TABLE FOOBAR (
    SOME_ID NUMBER,
    SOME_DATE DATE, -- Add primary key constraint
    SOME_STATUS NUMBER,
    SOME_FLAG NUMBER(1) DEFAULT 0 NOT NULL);

需要翻译 hsqldb to：

CREATE TABLE FOOBAR (
    SOME_ID NUMERIC,
    SOME_DATE TIMESTAMP PRIMARY KEY,
    SOME_STATUS NUMERIC,
    SOME_FLAG INTEGER DEFAULT 0 NOT NULL);

在我目前的项目中，有太多的表要手动完成......

In my current project, there are too many tables to do that manually...

所以我的问题：

• 你可以给我什么建议？


• h2 或 hsqldb 提供一些从Oracle连接生成脚本的工具？

• What are the advices you can give me to achieve that?
• Does h2 or hsqldb provide some tools to generate their scripts from an Oracle connection?

技术信息

Java 1.6，Spring 2.5，Oracle 10.g，Maven 2

Java 1.6, Spring 2.5, Oracle 10.g, Maven 2

编辑

有关我的单元测试的一些信息：

Some information regarding my unit tests:

在我使用 hsqldb 的应用程序中，我进行了以下测试：
- 一些基本的单元测试，与DB无关。
- 对于DAO测试，我使用 hsqldb 来执行数据库操作，例如CRUD。
- 然后，在服务层，我使用 Mockito 来模拟我的DAO对象，以便专注于服务测试而不是整个应用程序（即服务） + dao + DB）。

In the application where I used hsqldb, I had the following tests: - Some "basic" unit tests, which have nothing to do with DB. - For DAO testing, I used hsqldb to execute database manipulations, such as CRUD. - Then, on the service layer, I used Mockito to mock my DAO objects, in order to focus on the service test and not the whole applications (i.e. service + dao + DB).

在我当前的应用程序中，我们遇到了最糟糕的情况：DAO层测试需要运行Oracle连接。服务层不使用（还）任何模拟对象来模拟DAO。所以服务测试也需要Oracle连接。

In my current application, we have the worst scenario: The DAO layer tests need an Oracle connection to be run. The services layer does not use (yet) any mock objects to simulate the DAO. So services tests also need an Oracle connection.

我知道模拟和内存数据库是两个分离点，我将解决他们尽快。但是，我的第一步是尝试通过内存数据库删除Oracle连接，然后我将使用我的 Mockito 知识来增强测试。

I am aware that mocks and in-memory database are two separates points, and I will address them as soon as possible. However, my first step is to try to remove the Oracle connection by an in-memory database, and then I will use my Mockito knowledges to enhance the tests.

请注意，我还想将单元测试与集成测试分开。后者需要访问Oracle数据库，才能执行真正的测试，但我主要关注的问题（这就是这个问题的目的）是几乎所有的单元测试都不是今天孤立运行的。

Note that I also want to separate unit tests from integration tests. The latter will need an access to the Oracle database, to execute "real" tests, but my main concern (and this is the purpose of this question) is that almost all of my unit tests are not run in isolation today.

推荐答案

使用内存/ Java数据库进行测试。这将确保测试比您在测试中抽象数据库时更接近现实世界。可能这样的测试也更容易编写和维护。另一方面，您可能希望在测试中抽象的是UI，因为UI测试通常难以自动化。

Use an in-memory / Java database for testing. This will ensure the tests are closer to the real world than if you try to 'abstract away' the database in your test. Probably such tests are also easier to write and maintain. On the other hand, what you probably do want to 'abstract away' in your tests is the UI, because UI testing is usually hard to automate.

Oracle语法你发布适用于H2数据库（我刚测试过），因此H2似乎比HSQLDB更好地支持Oracle语法。免责声明：我是H2的作者之一。如果某些东西不起作用，请将它发布在H2邮件列表上。

The Oracle syntax you posted works well with the H2 database (I just tested it), so it seems H2 supports the Oracle syntax better than HSQLDB. Disclaimer: I'm one of the authors of H2. If something doesn't work, please post it on the H2 mailing list.

你应该在你的版本控制系统中拥有数据库的DDL语句。您也可以使用这些脚本进行测试。可能您还需要支持多个模式版本 - 在这种情况下，您可以编写版本更新脚本（alter table ...）。使用Java数据库，您也可以测试它们。

You should anyway have the DDL statements for the database in your version control system. You can use those scripts for testing as well. Possibly you also need to support multiple schema versions - in that case you could write version update scripts (alter table...). With a Java database you can test those as well.

顺便说一句，在使用H2或HSQLDB时，您不一定需要使用内存模式。即使您持久保存数据，这两个数据库也都很快。而且它们易于安装（只是一个jar文件）并且需要的内存比Oracle少得多。

By the way, you don't necessarily need to use the in-memory mode when using H2 or HSQLDB. Both databases are fast even if you persist the data. And they are easy to install (just a jar file) and need much less memory than Oracle.

这篇关于从Oracle实例创建内存数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！