sequence table management

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

sequence table management

Rusty Wright-2
I'm using Hibernate annotations and Hibernate Tools to generate the ddl for my tables.  My primary keys are synthetic/surrogate; for example,

  @Entity
  @Table(name = "hosts")
  public class Host implements Serializable, Comparable<Host> {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "host_id")
    private Long id;

My database is Postgres and Hibernate is using a sequence table for the id primary keys.

I'm using dbunit as a maven task to load my database with the production data for integration and qa testing.  (Is this a bad idea?)

The problem is that dbunit isn't using the sequence table for the ids when it loads the data; they're all specified in the data.xml file.  So when starting with a fresh load the last entry in the hosts table has an id of, for example, 345, but the sequence table is at 2.

Then when my code has Hibernate save a new Host to the hosts table, it's using the sequence table and things blow up because there's a collision; there's already a host with an id of 2 or 3 in the hosts table.

So it I guess my options are I need to update the sequence table after I upload the data (unfortunately, it seems that manually is my only option), or to use something other than dbunit for the data load (possibly ddlutils, but it doesn't seem very alive).

Suggestions or ideas?

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

Re: sequence table management

Dave S-B
I always tend to make the test data IDs negative. That way, they won't
conflict with Hibernate generated IDs and it's also easy to identify
test-prepared data from anything else in the database.

Dave S-B
Lledr Solutions

Rusty Wright wrote:

> I'm using Hibernate annotations and Hibernate Tools to generate the ddl for my tables.  My primary keys are synthetic/surrogate; for example,
>
>   @Entity
>   @Table(name = "hosts")
>   public class Host implements Serializable, Comparable<Host> {
>     @Id
>     @GeneratedValue(strategy = GenerationType.AUTO)
>     @Column(name = "host_id")
>     private Long id;
>
> My database is Postgres and Hibernate is using a sequence table for the id primary keys.
>
> I'm using dbunit as a maven task to load my database with the production data for integration and qa testing.  (Is this a bad idea?)
>
> The problem is that dbunit isn't using the sequence table for the ids when it loads the data; they're all specified in the data.xml file.  So when starting with a fresh load the last entry in the hosts table has an id of, for example, 345, but the sequence table is at 2.
>
> Then when my code has Hibernate save a new Host to the hosts table, it's using the sequence table and things blow up because there's a collision; there's already a host with an id of 2 or 3 in the hosts table.
>
> So it I guess my options are I need to update the sequence table after I upload the data (unfortunately, it seems that manually is my only option), or to use something other than dbunit for the data load (possibly ddlutils, but it doesn't seem very alive).
>
> Suggestions or ideas?
>
> ------------------------------------------------------------------------------
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.375 / Virus Database: 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>
>  


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

Re: sequence table management

Rusty Wright-2
It's not just testing; any time a Host is saved it's going to use the next sequence number, which will be too low and one that's in use.


Dave S-B wrote:

> I always tend to make the test data IDs negative. That way, they won't
> conflict with Hibernate generated IDs and it's also easy to identify
> test-prepared data from anything else in the database.
>
> Dave S-B
> Lledr Solutions
>
> Rusty Wright wrote:
>> I'm using Hibernate annotations and Hibernate Tools to generate the ddl for my tables.  My primary keys are synthetic/surrogate; for example,
>>
>>   @Entity
>>   @Table(name = "hosts")
>>   public class Host implements Serializable, Comparable<Host> {
>>     @Id
>>     @GeneratedValue(strategy = GenerationType.AUTO)
>>     @Column(name = "host_id")
>>     private Long id;
>>
>> My database is Postgres and Hibernate is using a sequence table for the id primary keys.
>>
>> I'm using dbunit as a maven task to load my database with the production data for integration and qa testing.  (Is this a bad idea?)
>>
>> The problem is that dbunit isn't using the sequence table for the ids when it loads the data; they're all specified in the data.xml file.  So when starting with a fresh load the last entry in the hosts table has an id of, for example, 345, but the sequence table is at 2.
>>
>> Then when my code has Hibernate save a new Host to the hosts table, it's using the sequence table and things blow up because there's a collision; there's already a host with an id of 2 or 3 in the hosts table.
>>
>> So it I guess my options are I need to update the sequence table after I upload the data (unfortunately, it seems that manually is my only option), or to use something other than dbunit for the data load (possibly ddlutils, but it doesn't seem very alive).
>>
>> Suggestions or ideas?
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> dbunit-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.375 / Virus Database: 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>
>>  
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> 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: sequence table management

John Hurst-2
In reply to this post by Dave S-B
I guess you've thought of this, but to confirm that people do this, I just add JDBC calls to reset the sequences to desired values when running tests of this sort. I'm mostly using Oracle, but I think it would be the same.

Note: in Oracle this involves DDL, so it cannot be done in part of a transaction that can be rolled back. So it prevents use of e.g. the tearDown/transaction rollback pattern.

Regards

John Hurst

On Thu, Jun 25, 2009 at 7:08 AM, Dave S-B <[hidden email]> wrote:
I always tend to make the test data IDs negative. That way, they won't
conflict with Hibernate generated IDs and it's also easy to identify
test-prepared data from anything else in the database.

Dave S-B
Lledr Solutions

Rusty Wright wrote:
> I'm using Hibernate annotations and Hibernate Tools to generate the ddl for my tables.  My primary keys are synthetic/surrogate; for example,
>
>   @Entity
>   @Table(name = "hosts")
>   public class Host implements Serializable, Comparable<Host> {
>     @Id
>     @GeneratedValue(strategy = GenerationType.AUTO)
>     @Column(name = "host_id")
>     private Long id;
>
> My database is Postgres and Hibernate is using a sequence table for the id primary keys.
>
> I'm using dbunit as a maven task to load my database with the production data for integration and qa testing.  (Is this a bad idea?)
>
> The problem is that dbunit isn't using the sequence table for the ids when it loads the data; they're all specified in the data.xml file.  So when starting with a fresh load the last entry in the hosts table has an id of, for example, 345, but the sequence table is at 2.
>
> Then when my code has Hibernate save a new Host to the hosts table, it's using the sequence table and things blow up because there's a collision; there's already a host with an id of 2 or 3 in the hosts table.
>
> So it I guess my options are I need to update the sequence table after I upload the data (unfortunately, it seems that manually is my only option), or to use something other than dbunit for the data load (possibly ddlutils, but it doesn't seem very alive).
>
> Suggestions or ideas?
>
> ------------------------------------------------------------------------------
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.375 / Virus Database: 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>
>


------------------------------------------------------------------------------
_______________________________________________
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: sequence table management

Dave S-B
In reply to this post by Rusty Wright-2
Yes, but Hibernate always uses positive numbers, so any data saved using
Hibernate won't ever conflict with a negative primary key (which would
be all your DbUnit loaded data). So, you have IDs ranging from -1 down
to -500, say, and as soon as Hibernate inserts for the first time, it
will start at 1 and go up from there (it doesn't take into account
existing data when it starts a sequence). But perhaps I'm not getting
what you're doing here. You do have the option of updating the Hibernate
sequence table after a load, but there's still a chance that Hibernate
will cache the next available key and produce a duplicate.

There are standard ways of combining Hibernate and non-Hibernate inserts
which need unique primary keys, but I don't think there's a clean way of
using them with a dbUnit load.

Dave S-B

Rusty Wright wrote:

> It's not just testing; any time a Host is saved it's going to use the
> next sequence number, which will be too low and one that's in use.
>
>
> Dave S-B wrote:
>> I always tend to make the test data IDs negative. That way, they
>> won't conflict with Hibernate generated IDs and it's also easy to
>> identify test-prepared data from anything else in the database.
>>
>> Dave S-B
>> Lledr Solutions
>>
>> Rusty Wright wrote:
>>> I'm using Hibernate annotations and Hibernate Tools to generate the
>>> ddl for my tables.  My primary keys are synthetic/surrogate; for
>>> example,
>>>
>>>   @Entity
>>>   @Table(name = "hosts")
>>>   public class Host implements Serializable, Comparable<Host> {
>>>     @Id
>>>     @GeneratedValue(strategy = GenerationType.AUTO)
>>>     @Column(name = "host_id")
>>>     private Long id;
>>>
>>> My database is Postgres and Hibernate is using a sequence table for
>>> the id primary keys.
>>>
>>> I'm using dbunit as a maven task to load my database with the
>>> production data for integration and qa testing.  (Is this a bad idea?)
>>>
>>> The problem is that dbunit isn't using the sequence table for the
>>> ids when it loads the data; they're all specified in the data.xml
>>> file.  So when starting with a fresh load the last entry in the
>>> hosts table has an id of, for example, 345, but the sequence table
>>> is at 2.
>>>
>>> Then when my code has Hibernate save a new Host to the hosts table,
>>> it's using the sequence table and things blow up because there's a
>>> collision; there's already a host with an id of 2 or 3 in the hosts
>>> table.
>>>
>>> So it I guess my options are I need to update the sequence table
>>> after I upload the data (unfortunately, it seems that manually is my
>>> only option), or to use something other than dbunit for the data
>>> load (possibly ddlutils, but it doesn't seem very alive).
>>>
>>> Suggestions or ideas?
>>>
>>> ------------------------------------------------------------------------------
>>>
>>> _______________________________________________
>>> dbunit-user mailing list
>>> [hidden email]
>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>> ------------------------------------------------------------------------
>>>
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>
>>>  
>>
>>
>> ------------------------------------------------------------------------------
>>
>> _______________________________________________
>> dbunit-user mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.375 / Virus Database: 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>
>  


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

Re: sequence table management

Rusty Wright-2
But I'm using dbunit to load my production data, not for early testing
but for final testing, as it were, so I need real data.  (This is
largely static data which is identical to what will be in production.  
When the app is being used the tables getting entries added and removed
consist of foreign keys to these static tables.)

Is that a bad practice?

If so, what could/should I use instead?


Dave S-B wrote:

> Yes, but Hibernate always uses positive numbers, so any data saved
> using Hibernate won't ever conflict with a negative primary key (which
> would be all your DbUnit loaded data). So, you have IDs ranging from
> -1 down to -500, say, and as soon as Hibernate inserts for the first
> time, it will start at 1 and go up from there (it doesn't take into
> account existing data when it starts a sequence). But perhaps I'm not
> getting what you're doing here. You do have the option of updating the
> Hibernate sequence table after a load, but there's still a chance that
> Hibernate will cache the next available key and produce a duplicate.
>
> There are standard ways of combining Hibernate and non-Hibernate
> inserts which need unique primary keys, but I don't think there's a
> clean way of using them with a dbUnit load.
>
> Dave S-B
>
> Rusty Wright wrote:
>> It's not just testing; any time a Host is saved it's going to use the
>> next sequence number, which will be too low and one that's in use.
>>
>>
>> Dave S-B wrote:
>>> I always tend to make the test data IDs negative. That way, they
>>> won't conflict with Hibernate generated IDs and it's also easy to
>>> identify test-prepared data from anything else in the database.
>>>
>>> Dave S-B
>>> Lledr Solutions
>>>
>>> Rusty Wright wrote:
>>>> I'm using Hibernate annotations and Hibernate Tools to generate the
>>>> ddl for my tables.  My primary keys are synthetic/surrogate; for
>>>> example,
>>>>
>>>>   @Entity
>>>>   @Table(name = "hosts")
>>>>   public class Host implements Serializable, Comparable<Host> {
>>>>     @Id
>>>>     @GeneratedValue(strategy = GenerationType.AUTO)
>>>>     @Column(name = "host_id")
>>>>     private Long id;
>>>>
>>>> My database is Postgres and Hibernate is using a sequence table for
>>>> the id primary keys.
>>>>
>>>> I'm using dbunit as a maven task to load my database with the
>>>> production data for integration and qa testing.  (Is this a bad idea?)
>>>>
>>>> The problem is that dbunit isn't using the sequence table for the
>>>> ids when it loads the data; they're all specified in the data.xml
>>>> file.  So when starting with a fresh load the last entry in the
>>>> hosts table has an id of, for example, 345, but the sequence table
>>>> is at 2.
>>>>
>>>> Then when my code has Hibernate save a new Host to the hosts table,
>>>> it's using the sequence table and things blow up because there's a
>>>> collision; there's already a host with an id of 2 or 3 in the hosts
>>>> table.
>>>>
>>>> So it I guess my options are I need to update the sequence table
>>>> after I upload the data (unfortunately, it seems that manually is
>>>> my only option), or to use something other than dbunit for the data
>>>> load (possibly ddlutils, but it doesn't seem very alive).
>>>>
>>>> Suggestions or ideas?
>>>>
>>>> ------------------------------------------------------------------------------
>>>>
>>>> _______________________________________________
>>>> dbunit-user mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>>
>>>> No virus found in this incoming message.
>>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>>
>>>>  
>>>
>>>
>>> ------------------------------------------------------------------------------
>>>
>>> _______________________________________________
>>> dbunit-user mailing list
>>> [hidden email]
>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>
>>  
>

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

Re: sequence table management

Rusty Wright-2
In reply to this post by Dave S-B
Sorry about my previous message; I was eyeballing the wrong message when
I wrote that.

I see what you're saying.  It feels weird to me to use negative numbers,
for no specific reason, but it's a clever idea.

Hopefully, if the data were ever migrated to a different database where
the ids were unsigned, the negative number ids would get converted to
their unsigned counterparts where the high bit is turned on, so that
they'd become huge numbers (rather than just using their absolute
value).  Probably not a likely scenario; of course whenever I think
that, it eventually ends up happening to me.


Dave S-B wrote:

> Yes, but Hibernate always uses positive numbers, so any data saved
> using Hibernate won't ever conflict with a negative primary key (which
> would be all your DbUnit loaded data). So, you have IDs ranging from
> -1 down to -500, say, and as soon as Hibernate inserts for the first
> time, it will start at 1 and go up from there (it doesn't take into
> account existing data when it starts a sequence). But perhaps I'm not
> getting what you're doing here. You do have the option of updating the
> Hibernate sequence table after a load, but there's still a chance that
> Hibernate will cache the next available key and produce a duplicate.
>
> There are standard ways of combining Hibernate and non-Hibernate
> inserts which need unique primary keys, but I don't think there's a
> clean way of using them with a dbUnit load.
>
> Dave S-B
>
> Rusty Wright wrote:
>> It's not just testing; any time a Host is saved it's going to use the
>> next sequence number, which will be too low and one that's in use.
>>
>>
>> Dave S-B wrote:
>>> I always tend to make the test data IDs negative. That way, they
>>> won't conflict with Hibernate generated IDs and it's also easy to
>>> identify test-prepared data from anything else in the database.
>>>
>>> Dave S-B
>>> Lledr Solutions
>>>
>>> Rusty Wright wrote:
>>>> I'm using Hibernate annotations and Hibernate Tools to generate the
>>>> ddl for my tables.  My primary keys are synthetic/surrogate; for
>>>> example,
>>>>
>>>>   @Entity
>>>>   @Table(name = "hosts")
>>>>   public class Host implements Serializable, Comparable<Host> {
>>>>     @Id
>>>>     @GeneratedValue(strategy = GenerationType.AUTO)
>>>>     @Column(name = "host_id")
>>>>     private Long id;
>>>>
>>>> My database is Postgres and Hibernate is using a sequence table for
>>>> the id primary keys.
>>>>
>>>> I'm using dbunit as a maven task to load my database with the
>>>> production data for integration and qa testing.  (Is this a bad idea?)
>>>>
>>>> The problem is that dbunit isn't using the sequence table for the
>>>> ids when it loads the data; they're all specified in the data.xml
>>>> file.  So when starting with a fresh load the last entry in the
>>>> hosts table has an id of, for example, 345, but the sequence table
>>>> is at 2.
>>>>
>>>> Then when my code has Hibernate save a new Host to the hosts table,
>>>> it's using the sequence table and things blow up because there's a
>>>> collision; there's already a host with an id of 2 or 3 in the hosts
>>>> table.
>>>>
>>>> So it I guess my options are I need to update the sequence table
>>>> after I upload the data (unfortunately, it seems that manually is
>>>> my only option), or to use something other than dbunit for the data
>>>> load (possibly ddlutils, but it doesn't seem very alive).
>>>>
>>>> Suggestions or ideas?
>>>>
>>>> ------------------------------------------------------------------------------
>>>>
>>>> _______________________________________________
>>>> dbunit-user mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>>
>>>> No virus found in this incoming message.
>>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>>
>>>>  
>>>
>>>
>>> ------------------------------------------------------------------------------
>>>
>>> _______________________________________________
>>> dbunit-user mailing list
>>> [hidden email]
>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>
>>  
>

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

Re: sequence table management

Roberto Lo Giacco-4
Hi, I largely use PostgreSQL and Hibernate and my solution is to use the
negative numbers too, but I don't load production database with my test
datas.... you can append the maven-sql-plugin to the load phase and
execute an update to the the sequence through ddl if you prefer using
normal sequence numbers...

Your last option is to load the datas through hibernate itself avoiding
dbunit at all

My choice is to use the dump/load commands for production environment
for performance reasons: dbunit issues a statement for each row, while
dump/load performs much better... production environments usually have a
lot of datas... If your datas covers normalization tables only then I
think negative numbers or very huge numbers have a valuable side effect:
you'll now wich datas have been loaded on startup even in far future ;)

Rusty Wright wrote:

> Sorry about my previous message; I was eyeballing the wrong message when
> I wrote that.
>
> I see what you're saying.  It feels weird to me to use negative numbers,
> for no specific reason, but it's a clever idea.
>
> Hopefully, if the data were ever migrated to a different database where
> the ids were unsigned, the negative number ids would get converted to
> their unsigned counterparts where the high bit is turned on, so that
> they'd become huge numbers (rather than just using their absolute
> value).  Probably not a likely scenario; of course whenever I think
> that, it eventually ends up happening to me.
>
>
> Dave S-B wrote:
>  
>> Yes, but Hibernate always uses positive numbers, so any data saved
>> using Hibernate won't ever conflict with a negative primary key (which
>> would be all your DbUnit loaded data). So, you have IDs ranging from
>> -1 down to -500, say, and as soon as Hibernate inserts for the first
>> time, it will start at 1 and go up from there (it doesn't take into
>> account existing data when it starts a sequence). But perhaps I'm not
>> getting what you're doing here. You do have the option of updating the
>> Hibernate sequence table after a load, but there's still a chance that
>> Hibernate will cache the next available key and produce a duplicate.
>>
>> There are standard ways of combining Hibernate and non-Hibernate
>> inserts which need unique primary keys, but I don't think there's a
>> clean way of using them with a dbUnit load.
>>
>> Dave S-B
>>
>> Rusty Wright wrote:
>>    
>>> It's not just testing; any time a Host is saved it's going to use the
>>> next sequence number, which will be too low and one that's in use.
>>>
>>>
>>> Dave S-B wrote:
>>>      
>>>> I always tend to make the test data IDs negative. That way, they
>>>> won't conflict with Hibernate generated IDs and it's also easy to
>>>> identify test-prepared data from anything else in the database.
>>>>
>>>> Dave S-B
>>>> Lledr Solutions
>>>>
>>>> Rusty Wright wrote:
>>>>        
>>>>> I'm using Hibernate annotations and Hibernate Tools to generate the
>>>>> ddl for my tables.  My primary keys are synthetic/surrogate; for
>>>>> example,
>>>>>
>>>>>   @Entity
>>>>>   @Table(name = "hosts")
>>>>>   public class Host implements Serializable, Comparable<Host> {
>>>>>     @Id
>>>>>     @GeneratedValue(strategy = GenerationType.AUTO)
>>>>>     @Column(name = "host_id")
>>>>>     private Long id;
>>>>>
>>>>> My database is Postgres and Hibernate is using a sequence table for
>>>>> the id primary keys.
>>>>>
>>>>> I'm using dbunit as a maven task to load my database with the
>>>>> production data for integration and qa testing.  (Is this a bad idea?)
>>>>>
>>>>> The problem is that dbunit isn't using the sequence table for the
>>>>> ids when it loads the data; they're all specified in the data.xml
>>>>> file.  So when starting with a fresh load the last entry in the
>>>>> hosts table has an id of, for example, 345, but the sequence table
>>>>> is at 2.
>>>>>
>>>>> Then when my code has Hibernate save a new Host to the hosts table,
>>>>> it's using the sequence table and things blow up because there's a
>>>>> collision; there's already a host with an id of 2 or 3 in the hosts
>>>>> table.
>>>>>
>>>>> So it I guess my options are I need to update the sequence table
>>>>> after I upload the data (unfortunately, it seems that manually is
>>>>> my only option), or to use something other than dbunit for the data
>>>>> load (possibly ddlutils, but it doesn't seem very alive).
>>>>>
>>>>> Suggestions or ideas?
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>>
>>>>> _______________________________________________
>>>>> dbunit-user mailing list
>>>>> [hidden email]
>>>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>>>> ------------------------------------------------------------------------
>>>>>
>>>>>
>>>>>
>>>>> No virus found in this incoming message.
>>>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>>>
>>>>>  
>>>>>          
>>>> ------------------------------------------------------------------------------
>>>>
>>>> _______________________________________________
>>>> dbunit-user mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>>>        
>>> ------------------------------------------------------------------------
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>
>>>  
>>>      
>
> ------------------------------------------------------------------------------
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>  


--

  Roberto Lo Giacco

  Chief Technical Officer
  SmartLab s.r.l.
  via Spagna, snc - 87036 Rende (CS) - Italy

  Mobile: +39 392 8765657
  Email: [hidden email]
  Skype: longisland-75
  Web: http://www.smartlab.it 



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

Re: sequence table management

Rusty Wright-2
Ok, thanks.  And thanks for the pointer to the maven-sql-plugin.


Roberto Lo Giacco wrote:

> Hi, I largely use PostgreSQL and Hibernate and my solution is to use the
> negative numbers too, but I don't load production database with my test
> datas.... you can append the maven-sql-plugin to the load phase and
> execute an update to the the sequence through ddl if you prefer using
> normal sequence numbers...
>
> Your last option is to load the datas through hibernate itself avoiding
> dbunit at all
>
> My choice is to use the dump/load commands for production environment
> for performance reasons: dbunit issues a statement for each row, while
> dump/load performs much better... production environments usually have a
> lot of datas... If your datas covers normalization tables only then I
> think negative numbers or very huge numbers have a valuable side effect:
> you'll now wich datas have been loaded on startup even in far future ;)
>
> Rusty Wright wrote:
>> Sorry about my previous message; I was eyeballing the wrong message when
>> I wrote that.
>>
>> I see what you're saying.  It feels weird to me to use negative numbers,
>> for no specific reason, but it's a clever idea.
>>
>> Hopefully, if the data were ever migrated to a different database where
>> the ids were unsigned, the negative number ids would get converted to
>> their unsigned counterparts where the high bit is turned on, so that
>> they'd become huge numbers (rather than just using their absolute
>> value).  Probably not a likely scenario; of course whenever I think
>> that, it eventually ends up happening to me.
>>
>>
>> Dave S-B wrote:
>>  
>>> Yes, but Hibernate always uses positive numbers, so any data saved
>>> using Hibernate won't ever conflict with a negative primary key (which
>>> would be all your DbUnit loaded data). So, you have IDs ranging from
>>> -1 down to -500, say, and as soon as Hibernate inserts for the first
>>> time, it will start at 1 and go up from there (it doesn't take into
>>> account existing data when it starts a sequence). But perhaps I'm not
>>> getting what you're doing here. You do have the option of updating the
>>> Hibernate sequence table after a load, but there's still a chance that
>>> Hibernate will cache the next available key and produce a duplicate.
>>>
>>> There are standard ways of combining Hibernate and non-Hibernate
>>> inserts which need unique primary keys, but I don't think there's a
>>> clean way of using them with a dbUnit load.
>>>
>>> Dave S-B
>>>
>>> Rusty Wright wrote:
>>>    
>>>> It's not just testing; any time a Host is saved it's going to use the
>>>> next sequence number, which will be too low and one that's in use.
>>>>
>>>>
>>>> Dave S-B wrote:
>>>>      
>>>>> I always tend to make the test data IDs negative. That way, they
>>>>> won't conflict with Hibernate generated IDs and it's also easy to
>>>>> identify test-prepared data from anything else in the database.
>>>>>
>>>>> Dave S-B
>>>>> Lledr Solutions
>>>>>
>>>>> Rusty Wright wrote:
>>>>>        
>>>>>> I'm using Hibernate annotations and Hibernate Tools to generate the
>>>>>> ddl for my tables.  My primary keys are synthetic/surrogate; for
>>>>>> example,
>>>>>>
>>>>>>   @Entity
>>>>>>   @Table(name = "hosts")
>>>>>>   public class Host implements Serializable, Comparable<Host> {
>>>>>>     @Id
>>>>>>     @GeneratedValue(strategy = GenerationType.AUTO)
>>>>>>     @Column(name = "host_id")
>>>>>>     private Long id;
>>>>>>
>>>>>> My database is Postgres and Hibernate is using a sequence table for
>>>>>> the id primary keys.
>>>>>>
>>>>>> I'm using dbunit as a maven task to load my database with the
>>>>>> production data for integration and qa testing.  (Is this a bad idea?)
>>>>>>
>>>>>> The problem is that dbunit isn't using the sequence table for the
>>>>>> ids when it loads the data; they're all specified in the data.xml
>>>>>> file.  So when starting with a fresh load the last entry in the
>>>>>> hosts table has an id of, for example, 345, but the sequence table
>>>>>> is at 2.
>>>>>>
>>>>>> Then when my code has Hibernate save a new Host to the hosts table,
>>>>>> it's using the sequence table and things blow up because there's a
>>>>>> collision; there's already a host with an id of 2 or 3 in the hosts
>>>>>> table.
>>>>>>
>>>>>> So it I guess my options are I need to update the sequence table
>>>>>> after I upload the data (unfortunately, it seems that manually is
>>>>>> my only option), or to use something other than dbunit for the data
>>>>>> load (possibly ddlutils, but it doesn't seem very alive).
>>>>>>
>>>>>> Suggestions or ideas?
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>>
>>>>>> _______________________________________________
>>>>>> dbunit-user mailing list
>>>>>> [hidden email]
>>>>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>>>>> ------------------------------------------------------------------------
>>>>>>
>>>>>>
>>>>>>
>>>>>> No virus found in this incoming message.
>>>>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>>>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>>>>
>>>>>>  
>>>>>>          
>>>>> ------------------------------------------------------------------------------
>>>>>
>>>>> _______________________________________________
>>>>> dbunit-user mailing list
>>>>> [hidden email]
>>>>> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>>>>>        
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>> No virus found in this incoming message.
>>>> Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database:
>>>> 270.12.90/2200 - Release Date: 06/24/09 12:49:00
>>>>
>>>>  
>>>>      
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> 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