DbUnit: how to assert generated IDs

Question

I need an idea/tip how to use DbUnit to assert IDs, generated by a database (e.g. MySQL's auto increment column). I have very simple case, which yet, at the moment, I find problematic:

2 tables: main and related. main.id column is an auto-increment. Related table has a foreign key to it: related.main_id -> main.id. In my test case my application does insert multiple entries into both tables, so the dataset looks similar to this:

<dataset>
    <main id="???" comment="ABC" />
    <main id="???" comment="DEF" />

    <related id="..." main_id="???" comment="#1 related to ABC" />
    <related id="..." main_id="???" comment="#2 related to ABC" />
    <related id="..." main_id="???" comment="#3 related to DEF" />
    <related id="..." main_id="???" comment="#4 related to DEF" />
</dataset>

As the order, how the inserts will be performed is unclear - I cannot simply clear/truncate the table before the test and use predefined IDs in advance (e.g. "ABC" entry will come at first so it gets ID 1 and "DEF" as 2nd - gets 2). If I write test such way - this will be wrong - with a bit of luck sometimes it may work and in other cases not.

Is there a clean way how test such cases? As I still want to assert that entries were created and linked properly in DB, not only that they exists (if I would simply ignore the auto-increment columns).

@Najzero this is THE problem in my opinion - as it is unclear in which order the inserts will come, I cannot override identities in my dataset as they simply will not match, doesn't matter what I would use... Of you mean something else? — Laimoncijus, Nov 20 '12 at 08:47
@jonathan.cone: not directly - we ended up in writing query, joining these 2 related tables (selecting just fields like `comment` and skipping `id`, `main_id`) and creating a dataset from this SQL. Then such dataset can be compared to some other statically defined dataset to see that entries in DB are matching properly. After all you're not interested in what particular IDs those are, you just want to make sure that entries in DB are properly "linked". — Laimoncijus, Mar 12 '14 at 11:37
@Laimoncijus That's the conclusion I came to as well, I appreciate your response! — jonathan.cone, Mar 13 '14 at 02:22

score 2 · Accepted Answer · answered Mar 16 '14 at 10:57

Based on the comments of the question, I am answering my own question, so this may help others, looking for similar solution.

After all we did skip asserting generated IDs, as they were not really interesting for us. What we actually did want to check is that the entries between main and related tables are "properly linked". To achieve this, in our unit test we did created the the dataset using query, joining both tables:

SELECT main.comment, related.comment AS related_comment 
FROM main, related 
WHERE main.id = related.main_id

Then we assert, that dataset produced by this query matches statically defined dataset:

<dataset>
     <result comment="ABC" related_comment="#1 related to ABC" />
     <result comment="ABC" related_comment="#2 related to ABC" />
     <result comment="DEF" related_comment="#3 related to DEF" />
     <result comment="DEF" related_comment="#4 related to DEF" />
</dataset>

When the datasets are matching, we can assume, that entries were "linked properly".

score 0 · Answer 2 · answered Nov 20 '12 at 06:45

0

Maybe you let dbunit sort your table main by id and table related by id automatically. Since the absolute number of rows are known in advantage this should solve your problem.

DBUnit allows sorting with org.dbunit.dataset.SortedTable.SortedTable which needs a table an a list of colums which should be sorted. JavaDoc of SortedTable

answered Nov 20 '12 at 06:45

EhmKah a.k.a. Michael Krauße

2,837
5
34
53

If I just sort the values - I would need to skip the ID fields out of my expected dataset (because they are unknown in advance). In this case I assert everything except main-related association were created properly, so test would not be 100% correct... – Laimoncijus Nov 20 '12 at 08:50
The difference to 100% depends on how "unambiguously" your data is without the id column. Maybe your rows would be already unambiguously without ID, because ID is only a technical number. – EhmKah a.k.a. Michael Krauße Nov 20 '12 at 09:37

DbUnit: how to assert generated IDs

2 Answers2