Create an in-memory database structure from an Oracle instance
Solution 1
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.
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.
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.
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.
Solution 2
Latest HSQLDB 2.0.1 supports ORACLE syntax for DUAL, ROWNUM, NEXTVAL and CURRVAL via a syntax compatibility flag, sql.syntax_ora=true. In the same manner, concatenation of a string with a NULL string and restrictions on NULL in UNIQUE constraints are handled with other flags. Most ORACLE functions such as TO_CHAR, TO_DATE, NVL etc. are already built in.
At the moment, to use simple ORACLE types such as NUMBER, you can use a type definition:
CREATE TYPE NUMBER AS NUMERIC
The next snapshot will allow NUMBER(N) and other aspects of ORACLE type compatibility when the flag is set.
Download from http://hsqldb.org/support/
[Update:] The snapshot issued on Oct 4 translates most Oracle specific types to ANSI SQL types. HSQLDB 2.0 also supports the ANSI SQL INTERVAL type and date / timestamp arithmetic the same way as Oracle.
Solution 3
What are your unit tests for? If they test the proper working of DDLs and stored procedures then you should write the tests "closer" to Oracle: either without Java code or without Spring and other nice web interfaces at all focusing on the db.
If you want to test the application logic implemented in Java and Spring then you may use mock objects/database connection to make your tests independent of the database.
If you want to test the working as a whole (what is against the modular development and testing principle) then you may virtualize your database and test on that instance without having the risk of doing some nasty irreversible modifications.
Solution 4
As long as your tests clean up after themselves (as you already seem to know how to set up), there's nothing wrong with running tests against a real database instance. In fact it's the approach I usually prefer, because you'll be testing something as close to production as possible.
The incompatibilities seem small, but really end up biting back not so long afterwards. In a good case, you may get away with some nasty sql translation / extensive mockery. In bad cases, parts of the system will be just impossible to test, which I think is an unacceptable risk for business-critical systems.
Romain Linsolas
As a software engineer and specialist in Java / J2EE application development, I am particularly interested in designing and developing high performance JEE applications, and Rich Internet Applications. My specialties: IT Consulting in Java / J2EE development (Spring, Hibernate, JSF, Struts) Web development (XHTML, Javascript, CSS, Ajax) Agile methodologies (Scrum, eXtreme Programming) Unit Testing and Quality, Continuous Integration and Performance
Updated on July 13, 2020Comments
-
Romain Linsolas almost 4 years
I have an application where many "unit" tests use a real connection to an Oracle database during their execution.
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
).So my idea is to progressively replace this Oracle test instance by an in-memory database. I will use
hsqldb
or maybe betterh2
.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.
Of course, I can extract the database structure from Oracle, using some tools like
SQL Developer
orTOAD
, and then modifying these scripts to adapt them to thehsqldb
orh2
language. But I don't think that's the better approach.
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 thehsqldb
language.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);
needed to be "translated" for
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...
So my questions:
- What are the advices you can give me to achieve that?
- Does
h2
orhsqldb
provide some tools to generate their scripts from an Oracle connection?
Technical information
Java 1.6, Spring 2.5, Oracle 10.g, Maven 2
Edit
Some information regarding my unit tests:
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 usedhsqldb
to execute database manipulations, such as CRUD. - Then, on the service layer, I usedMockito
to mock my DAO objects, in order to focus on the service test and not the whole applications (i.e. service + dao + DB).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.
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.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.