Re: [cinjug-users] DbUnit and Hibernate

["Edward Sumerfield" <esumerfd@xxxxxxxxx>]

That's interesting, I didn't know that.

So from an editability perspective we can compare XML, Excel and now SQL. None if which ends up being very good.

XlsDataSet.write(dataSet, file);

I can confirm that Excel sucks completely. While it might sound convenient, each table is a separate sheet and there is no way to find a sheet by name. Same as columns. With massive numbers of columns in a table it is painstaking to find the right one.

XmlDataSet.write(dataSet, file);

XML is better because you can at least find the table and column names in a text editor but there are some downsides. This XML format encodes tables as tags and columns as attributes and is the easiest to edit.

However, it doesn't give you manageable foreign key errors at load time so is next to impossible to get right after the export.

FlatXmlDataSet.write(dataSet, file);

The flat XML format uses a "table" tag with name attribute followed by  a list of "column" tags that contain the column names. This is followed by lists of value tags that are assumed to be in the same order as the column tags. Anyway, for tables with lots of columns this makes it really hard to find the value to edit.

This format does, however, give us the good foreign key errors.

    <dataset>
        <table name="customer">
            <column>A</column>
            <column>B</column>
            <column>C</column>
            <row>
                <value>something</value>
                <value>something else</value>
                <null/>
           </row>
       </table>
    </dataset>

SQL.

This format is going to require that we get the data into hsqldb before it can be saved to the file. This means we need an intermediate format that can be edited.

To the testing issue of load, save and replace between tests, I am not sure. Yes, the ant integration would work fine but I can't see how I could integrate it into an eclipse test. We could take the intermediate solution of using a junit base class to do the creation and the setup to do the replacement, so at least the db is only being loaded once per test class.

On 8/8/06, James Carman <james@xxxxxxxxxxxxxxxxxxxx> wrote:

Well, I don't know if it's that bad of an idea to just check in the file.  It's not really binary.  Have you ever looked at a HSQLDB file?  Here's one I just created.  First, here, the "script" file:

 

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE MEMORY TABLE TEST(ID INTEGER,NAME VARCHAR(255))

CREATE USER SA PASSWORD ""

GRANT DBA TO SA

SET WRITE_DELAY 20

SET SCHEMA PUBLIC

INSERT INTO TEST VALUES(0,'Name0')

INSERT INTO TEST VALUES(1,'Name1')

INSERT INTO TEST VALUES(2,'Name2')

INSERT INTO TEST VALUES(3,'Name3')

INSERT INTO TEST VALUES(4,'Name4')

INSERT INTO TEST VALUES(5,'Name5')

INSERT INTO TEST VALUES(6,'Name6')

INSERT INTO TEST VALUES(7,'Name7')

INSERT INTO TEST VALUES(8,'Name8')

INSERT INTO TEST VALUES(9,'Name9')

INSERT INTO TEST VALUES(10,'Name10')

 

And, here's the properties file:

 

#HSQL Database Engine

#Tue Aug 08 19:07:42 EDT 2006

hsqldb.script_format=0

runtime.gc_interval=0

sql.enforce_strict_size=false

hsqldb.cache_size_scale=8

readonly=false

hsqldb.nio_data_file=true

hsqldb.cache_scale=14

version=1.8.0

hsqldb.default_table_type=memory

hsqldb.cache_file_scale=1

hsqldb.log_size=200

modified=no

hsqldb.cache_version=1.7.0

hsqldb.original_version=1.8.0

hsqldb.compatible_version=1.8.0

 

That looks pretty editable to me.  In fact, it might be just as easy to edit that to create the rows you want in your tables.  I don't know. 

 

On the other hand, that file can be generated, as you say, by Java code, which you would obviously check into your source repository.  I might create a little "loader" program that uses Hibernate to load the test data into the database and then just start copying it around.  How would you make sure that it's loaded before your tests are run?  In Ant, you might make your test target dependent upon an init-test-db target or something.  In Maven2, you'd have to put in a hook before your tests run to execute your main class or something, but that's pretty easy, since you can use Ant in M2.  It may be better off to just check in your loader.  Sorry for the stream-of-consciousness.  :-)

 

 

---

From: Edward Sumerfield [mailto:esumerfd@xxxxxxxxx]
Sent: Tuesday, August 08, 2006 5:44 PM
To: users@xxxxxxxxxx
Subject: [Norton AntiSpam] Re: [cinjug-users] DbUnit and Hibernate

 

Yes, that sounded really good, though I didn't like his suggestion of checking in the binary file. I was thinking of trying what you suggested, one load and then the file swap on retest. My data is always checked in in an editable format.

On 8/8/06, James Carman < james@xxxxxxxxxxxxxxxxxxxx> wrote:

There was an even better way to use HSQLDB that was mentioned this weekend that I want to try out also.  Basically, you keep an HSQLDB database around as a snapshot of what you want your database to look like.  You can load this anyway you want.  Then, before running your tests, you just copy it, since it's just a file!  I think this might be the fastest way to do it, but haven't tried it yet.  Anyone tried that yet?

 

---

From: Rob Keefer [mailto:rbkeefer@xxxxxxxxx]
Sent: Tuesday, August 08, 2006 2:41 PM
To: users@xxxxxxxxxx

Subject: Re: [cinjug-users] DbUnit and Hibernate

 

-->

I just want to reinforce Ed's comment a bit...

 

Unit testing of DAOs is difficult because you'd like to have reproducible tests that affect multiple tables, so after each test you need to get the tables back into a known state. What hsqldb buys you is the ability to do this easily.

 

On a project a few years back we had a crap-load of DAO tests that took 16 hours to run. We had them organized into three categories Select oriented query tests (so data wasn't altered at all), CRUD oriented tests (each test was able to reset the data to a known state after it completed), and then the transaction oriented tests. We tried a number of schemes to update the database between transaction oriented tests, but none worked very well.

 

So, even with some of the drawbacks this export to hsqldb method has, it is easily the best method we have found to do these kinds of tests, and we have the scars to show we've tried plenty of other methods.

 

- Rob

 

----- Original Message ----
From: Edward Sumerfield <esumerfd@xxxxxxxxx>
To: "users@xxxxxxxxxx" < users@xxxxxxxxxx>
Sent: Tuesday, August 8, 2006 1:24:18 PM
Subject: Re: [cinjug-users] DbUnit and Hibernate

DBUnit rocks. It gives you data exporting and importing to a few different formats. I use Excel and XML format. I export data from an Oracle database and import into hsqldb between each test. Very fast and fairly simple to use.

The toughest problem is managing load dependencies. Each exported table and row must not break the foreign keys.

Oracle allows us to generate a list of tables in foreign key dependent order. It doesn't solve the problem of self-referencing tables and the row sequence though.

Additionally, since you are likley not exporting the entire database you may miss some required dependencies so there is usually some data patching that is required after the export to ensure that the data can be re-loaded correctly.

Make sure that you allow the export to include tables from a sub-dependent tree. I have never been able to fix the dependencies for an entire schema.

On 8/8/06, twcrone@xxxxxxxxxxxxx <twcrone@xxxxxxxxxxxxx> wrote:

In the past I have not built very good tests for my Hibernate DAO's.  Typically write a main method that tests the CRUD operations but never put it somewhere that others can easily use it or it can easily be run as a test suite.  I have heard of something called DBUNIT that I plan to look into.  Any other suggestions?

BTW...NFJS conference this weekend was GREAT!!!

- Todd

 

--
Ed

 

--
Ed

-- 
Ed