You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Matthew Broadhead <ma...@nbmlaw.co.uk.INVALID> on 2019/01/04 13:40:21 UTC
deleting openjpa_sequence_table
i am about to merge a few databases. each database has an
openjpa_sequence_table. do i just drop these tables?
Re: deleting openjpa_sequence_table
Posted by Matthew Broadhead <ma...@nbmlaw.co.uk.INVALID>.
hi Mark,
there are no tables conflicting between the databases. i want to
simplify by moving tables from 3 databases into 1. so merging the data
is not a problem.
that is a good hint about openjpa.Sequence. at the moment it seems to
just use one sequence for all the tables. can i add that to my
persistence.xml at this stage? if i delete the openjpa_sequence_table
will it recalculate the highest index for each table?
On 05/01/2019 22:16, Mark Struberg wrote:
> Hi Matthew!
>
> As Craig already explained they contain the hightest value for a specified index.
> Of course only if no Database Sequence got used!
> The reason to use this over a Sequence is to have a database independent counter which is fully portable.
>
> There are also multiple 'modes' for the sequence to operate!
> I personally prefer the counter by table.
>
> In your persistence.xml you might probably have something like the following in your <properties> section:
>
> <property name="openjpa.Sequence" value="class-table(Table=SEQUENCES, Increment=20, InitialValue=10000)"/>
>
> openjpa.Sequence determines the strategy to use plus the details. 'class-table' will create 1 row per @Entity basically.
>
> You can read more over here:
> https://openjpa.apache.org/builds/3.0.0/apache-openjpa/docs/ref_guide_sequence.html
>
>
> So in hindsight dealing with the openjpa_sequence values might be your least problematic issue.
> The more problematic one might be that you have to re-assign new primary keys to your migrated data.
>
> Consider you have an @Entity Customer. Then both or your databases to merge might have a Customer with the id=1. And 2 customers with id=2, etc.
> That means merging them together will create a unique key violation.
>
> One strategy is to leave one db as is and update the other one.
> If you e.g. have 18572 customers in database_A. Then you could update all your customers in database_B to be 10000+id. Means your customer with id=1 in database_B will get id=100001, etc. And of course you will have to update all the 1:n referencing tables as well. So e.g. update all Address entries to have a customerid=customerid+100000;
> You might need to drop/disable all foreign-key indexes before you start doing this update and then activate them again after all is done.
> And of course this can only be done while there is no moving target. That means you have to stop operation on your db. If this is not possible then you might want to do this with 'filtered tx-logs'. But that is WAY more effort...
>
> After that you can merge both without a conflict.
>
> Do others have a better idea?
>
> LieGrue,
> strub
>
>
>
>> Am 05.01.2019 um 01:04 schrieb Craig Russell <ap...@gmail.com>:
>>
>> It might be best to inspect the tables and merge them when you merge the databases. IIRC they hold the highest keys that have been used and simply dropping them might introduce errors.
>>
>> Craig
>>
>>> On Jan 4, 2019, at 5:40 AM, Matthew Broadhead <ma...@nbmlaw.co.uk.INVALID> wrote:
>>>
>>> i am about to merge a few databases. each database has an openjpa_sequence_table. do i just drop these tables?
>> Craig L Russell
>> clr@apache.org
>>
Re: deleting openjpa_sequence_table
Posted by Mark Struberg <st...@yahoo.de.INVALID>.
Hi Matthew!
As Craig already explained they contain the hightest value for a specified index.
Of course only if no Database Sequence got used!
The reason to use this over a Sequence is to have a database independent counter which is fully portable.
There are also multiple 'modes' for the sequence to operate!
I personally prefer the counter by table.
In your persistence.xml you might probably have something like the following in your <properties> section:
<property name="openjpa.Sequence" value="class-table(Table=SEQUENCES, Increment=20, InitialValue=10000)"/>
openjpa.Sequence determines the strategy to use plus the details. 'class-table' will create 1 row per @Entity basically.
You can read more over here:
https://openjpa.apache.org/builds/3.0.0/apache-openjpa/docs/ref_guide_sequence.html
So in hindsight dealing with the openjpa_sequence values might be your least problematic issue.
The more problematic one might be that you have to re-assign new primary keys to your migrated data.
Consider you have an @Entity Customer. Then both or your databases to merge might have a Customer with the id=1. And 2 customers with id=2, etc.
That means merging them together will create a unique key violation.
One strategy is to leave one db as is and update the other one.
If you e.g. have 18572 customers in database_A. Then you could update all your customers in database_B to be 10000+id. Means your customer with id=1 in database_B will get id=100001, etc. And of course you will have to update all the 1:n referencing tables as well. So e.g. update all Address entries to have a customerid=customerid+100000;
You might need to drop/disable all foreign-key indexes before you start doing this update and then activate them again after all is done.
And of course this can only be done while there is no moving target. That means you have to stop operation on your db. If this is not possible then you might want to do this with 'filtered tx-logs'. But that is WAY more effort...
After that you can merge both without a conflict.
Do others have a better idea?
LieGrue,
strub
> Am 05.01.2019 um 01:04 schrieb Craig Russell <ap...@gmail.com>:
>
> It might be best to inspect the tables and merge them when you merge the databases. IIRC they hold the highest keys that have been used and simply dropping them might introduce errors.
>
> Craig
>
>> On Jan 4, 2019, at 5:40 AM, Matthew Broadhead <ma...@nbmlaw.co.uk.INVALID> wrote:
>>
>> i am about to merge a few databases. each database has an openjpa_sequence_table. do i just drop these tables?
>
> Craig L Russell
> clr@apache.org
>
Re: deleting openjpa_sequence_table
Posted by Craig Russell <ap...@gmail.com>.
It might be best to inspect the tables and merge them when you merge the databases. IIRC they hold the highest keys that have been used and simply dropping them might introduce errors.
Craig
> On Jan 4, 2019, at 5:40 AM, Matthew Broadhead <ma...@nbmlaw.co.uk.INVALID> wrote:
>
> i am about to merge a few databases. each database has an openjpa_sequence_table. do i just drop these tables?
Craig L Russell
clr@apache.org