comparing dataset before/after tests with relations?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

comparing dataset before/after tests with relations?

Ronald
Hi,

I am looking into dbunit as a way for building up our tests, one major
problem i cannot see any solution to at the moment
is the handling of relations:

- we have many tables (around 30-40 i would guess), with many
relations going back and forth etc

- the constraints goes over the (incremental) ids for pk/fk.

- the ids are automatically generated by the database

The problem is that the ids can be different when i do two different
test runs (since we dont control how the database generate the ids).

When we after a test run export the database, we hence cant compare it
to a fixed dataset (since the ids might differ)

If we simply filter out the id:s from the exported dataset, we will
lose the relations, and does not know if the right objects are
corrected.

How do you guys handle this problem together with dbunit?

best regards,

RJ

------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

Ronald
On Wed, Apr 21, 2010 at 10:37 AM, Ronald Johnson <[hidden email]> wrote:
> If we simply filter out the id:s from the exported dataset, we will
> lose the relations, and does not know if the right objects are
> corrected.

should of course be "......and does not know if the right objects are connected"

RJ (slightly tired)

------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

John Hurst-2
I haven't worked with DbUnit using a database with auto generated keys. (I mostly use Oracle, sometime PostgreSQL and Firebird, all of which use sequences/generators.)

This is an interesting problem. I don't know a good solution.

If no one knows a way to deal with this, perhaps we need a feature where we can put some kind of key surrogates in the test data, to mark the IDs to be matched.

Regards

John Hurst
Wellington, New Zealand

On Wed, Apr 21, 2010 at 8:47 PM, Ronald Johnson <[hidden email]> wrote:
On Wed, Apr 21, 2010 at 10:37 AM, Ronald Johnson <[hidden email]> wrote:
> If we simply filter out the id:s from the exported dataset, we will
> lose the relations, and does not know if the right objects are
> corrected.

should of course be "......and does not know if the right objects are connected"

RJ (slightly tired)

------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game

------------------------------------------------------------------------------

_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

Zdenek Vrablik
In reply to this post by Ronald
Hi,

what about reseting these id generators to certain value before
running the test?

Than the ids should be everytime same.

At least oracle sequences may be reset by ALTER SEQUENCE.

What database do you use?

Regards,
Zdenek

On Wed, Apr 21, 2010 at 9:37 AM, Ronald Johnson <[hidden email]> wrote:

> Hi,
>
> I am looking into dbunit as a way for building up our tests, one major
> problem i cannot see any solution to at the moment
> is the handling of relations:
>
> - we have many tables (around 30-40 i would guess), with many
> relations going back and forth etc
>
> - the constraints goes over the (incremental) ids for pk/fk.
>
> - the ids are automatically generated by the database
>
> The problem is that the ids can be different when i do two different
> test runs (since we dont control how the database generate the ids).
>
> When we after a test run export the database, we hence cant compare it
> to a fixed dataset (since the ids might differ)
>
> If we simply filter out the id:s from the exported dataset, we will
> lose the relations, and does not know if the right objects are
> corrected.
>
> How do you guys handle this problem together with dbunit?
>
> best regards,
>
> RJ
>
> ------------------------------------------------------------------------------
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

George Dinwiddie
In reply to this post by Ronald
Ronald,

Ronald Johnson wrote:

> Hi,
>
> I am looking into dbunit as a way for building up our tests, one major
> problem i cannot see any solution to at the moment
> is the handling of relations:
>
> - we have many tables (around 30-40 i would guess), with many
> relations going back and forth etc
>
> - the constraints goes over the (incremental) ids for pk/fk.
>
> - the ids are automatically generated by the database

Sometimes you can subvert this for the tests.

> The problem is that the ids can be different when i do two different
> test runs (since we dont control how the database generate the ids).
>
> When we after a test run export the database, we hence cant compare it
> to a fixed dataset (since the ids might differ)
>
> If we simply filter out the id:s from the exported dataset, we will
> lose the relations, and does not know if the right objects are
> corrected.
>
> How do you guys handle this problem together with dbunit?

See
http://idiacomputing.com/moin/PrimaryKeyGeneration#Inserted_by_database_trigger

  - George

--
  ----------------------------------------------------------------------
   * George Dinwiddie *                      http://blog.gdinwiddie.com
   Software Development                    http://www.idiacomputing.com
   Consultant and Coach                    http://www.agilemaryland.org
  ----------------------------------------------------------------------


------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

John Hurst-2
In reply to this post by Zdenek Vrablik
The ALTER SEQUENCE approach is exactly what I use with Oracle.

Not sure if this works with autoincrement-type databases, such as MySQL, DB2(?) and SQL-Server.

JH

On Thu, Apr 22, 2010 at 7:41 AM, Zdeněk Vráblík <[hidden email]> wrote:
Hi,

what about reseting these id generators to certain value before
running the test?

Than the ids should be everytime same.

At least oracle sequences may be reset by ALTER SEQUENCE.

What database do you use?

Regards,
Zdenek

On Wed, Apr 21, 2010 at 9:37 AM, Ronald Johnson <[hidden email]> wrote:
> Hi,
>
> I am looking into dbunit as a way for building up our tests, one major
> problem i cannot see any solution to at the moment
> is the handling of relations:
>
> - we have many tables (around 30-40 i would guess), with many
> relations going back and forth etc
>
> - the constraints goes over the (incremental) ids for pk/fk.
>
> - the ids are automatically generated by the database
>
> The problem is that the ids can be different when i do two different
> test runs (since we dont control how the database generate the ids).
>
> When we after a test run export the database, we hence cant compare it
> to a fixed dataset (since the ids might differ)
>
> If we simply filter out the id:s from the exported dataset, we will
> lose the relations, and does not know if the right objects are
> corrected.
>
> How do you guys handle this problem together with dbunit?
>
> best regards,
>
> RJ
>
> ------------------------------------------------------------------------------
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game

------------------------------------------------------------------------------

_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

Zdenek Vrablik
I found this MySql documentation
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

There is this post:
In order to reset the auto_increment, in a situation where some of the
most recently added rows were deleted, use:

ALTER TABLE theTableInQuestion AUTO_INCREMENT=1234

and future insertions will be numbered from 1234 again (unless you
still had rows numbered greater than 1234, and then the future
insertions will start from the greatest number + 1 ).


Regards,
Zdenek

On Wed, Apr 21, 2010 at 9:11 PM, John Hurst <[hidden email]> wrote:

> The ALTER SEQUENCE approach is exactly what I use with Oracle.
>
> Not sure if this works with autoincrement-type databases, such as MySQL,
> DB2(?) and SQL-Server.
>
> JH
>
> On Thu, Apr 22, 2010 at 7:41 AM, Zdeněk Vráblík <[hidden email]> wrote:
>>
>> Hi,
>>
>> what about reseting these id generators to certain value before
>> running the test?
>>
>> Than the ids should be everytime same.
>>
>> At least oracle sequences may be reset by ALTER SEQUENCE.
>>
>> What database do you use?
>>
>> Regards,
>> Zdenek
>>
>> On Wed, Apr 21, 2010 at 9:37 AM, Ronald Johnson <[hidden email]>
>> wrote:
>> > Hi,
>> >
>> > I am looking into dbunit as a way for building up our tests, one major
>> > problem i cannot see any solution to at the moment
>> > is the handling of relations:
>> >
>> > - we have many tables (around 30-40 i would guess), with many
>> > relations going back and forth etc
>> >
>> > - the constraints goes over the (incremental) ids for pk/fk.
>> >
>> > - the ids are automatically generated by the database
>> >
>> > The problem is that the ids can be different when i do two different
>> > test runs (since we dont control how the database generate the ids).
>> >
>> > When we after a test run export the database, we hence cant compare it
>> > to a fixed dataset (since the ids might differ)
>> >
>> > If we simply filter out the id:s from the exported dataset, we will
>> > lose the relations, and does not know if the right objects are
>> > corrected.
>> >
>> > How do you guys handle this problem together with dbunit?
>> >
>> > best regards,
>> >
>> > RJ
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > _______________________________________________
>> > dbunit-user mailing list
>> > [hidden email]
>> > https://lists.sourceforge.net/lists/listinfo/dbunit-user
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> dbunit-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>
>
> --
> Life is interfering with my game
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>

------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: comparing dataset before/after tests with relations?

Jeff Jensen-2
In reply to this post by John Hurst-2

Mostly I use an in-mem db such as HSQLDB.  Each test starts with an empty database.

 

Another strategy I use is to rollback the transaction.  Spring Test is really helpful for this!

 

 

From: John Hurst [mailto:[hidden email]]
Sent: Wednesday, April 21, 2010 1:42 PM
To: [hidden email]
Subject: Re: [dbunit-user] comparing dataset before/after tests with relations?

 

I haven't worked with DbUnit using a database with auto generated keys. (I mostly use Oracle, sometime PostgreSQL and Firebird, all of which use sequences/generators.)

This is an interesting problem. I don't know a good solution.

If no one knows a way to deal with this, perhaps we need a feature where we can put some kind of key surrogates in the test data, to mark the IDs to be matched.

Regards

John Hurst
Wellington, New Zealand

On Wed, Apr 21, 2010 at 8:47 PM, Ronald Johnson <[hidden email]> wrote:

On Wed, Apr 21, 2010 at 10:37 AM, Ronald Johnson <[hidden email]> wrote:
> If we simply filter out the id:s from the exported dataset, we will
> lose the relations, and does not know if the right objects are
> corrected.

should of course be "......and does not know if the right objects are connected"

RJ (slightly tired)


------------------------------------------------------------------------------
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user




--
Life is interfering with my game


------------------------------------------------------------------------------

_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user