Reuse an existing Database Sequence (DB2Sequence) for a new table - possible?

Hi,

this time I have a really tricky one.

I need to use an existing DB2Sequence of an existing table for the primary key of a newly added table.

When Glorp creates the table (in #createTables:)  it always assumes there is a strict 1:1 relationship between tables and sequences, so it tries to create a sequence for the new table. Creating the same sequence twice, however, is not accepted by the database ;-)

Background: 

I have a class that has existed for a while. Now I want to add a new class that is the existing class's sibling (they both subclass a common superclass). The new class has loads of  additional attributes, so there is no way of using a FilteredTypeResolver (both classes in one table).

So I wanted to use a HorizontalTypeResolver. This, however, requires the two mapped classes to share a sequence, so that the ids of the two tables don't overlap.

And this is where I got lost: I cannot find a way to tell a field to be a "platform sequence" which re-uses the native db sequence of another table.

Here's what I tried:

tableForMYTABLE: aTable

(aTable createFieldNamed: 'id' type: (self sequenceTypeNamed: 'ExistingTable_id_seq'))

bePrimaryKey.

This leads to a "duplicate name" sql exception when I create the table.

So I am lost.

Is there some other way to have two tables share a sequence that is auto-generated by the database? It seems all of Glorp's Sequence classes that sound as if they could help me are not finished, there are lots of subclassResponsibilities that are not implemented on these subclasses.

has anybody done this before?

Joachim

Eventually, it could be beneficial for Glorp to allow use of one sequence for all tables, as sequence values are retrieved from the database prior to insertion, and using one would reduce the number of queries required significantly.

On 10/15/2014 11:09 AM, jtuchel wrote:

Nevermind, I found it!

My example works perfectly, all that needs to be done is to not use any of the provided createTable:* methods that also create the sequence for the table. If I only create the table and its foreign key constraints, but not the sequence for the table, all looks very good.

So far I have only tested inserting new objects and the fetch of a new id for the new table from the existing sequence works as expected.

And I tested a readManyOf: the common superclass which now fetches instances of both its subclasses

The next step will be to test whether 1:n relationships to the superclass work, but I see no reason why this should not work, because the readManyOf: of the superclass works.

So I learned something new today about Glorp. And even if it cost me a few hours to find out, it shows me once more how much flexibility is hidden in it. It is an OR mapper with all its consequences, but it's a really good one (except for its lack of documentation).

Joachim

Am Mittwoch, 15. Oktober 2014 16:05:32 UTC+2 schrieb jtuchel:

Hi,

this time I have a really tricky one.

I need to use an existing DB2Sequence of an existing table for the primary key of a newly added table.

When Glorp creates the table (in #createTables:)  it always assumes there is a strict 1:1 relationship between tables and sequences, so it tries to create a sequence for the new table. Creating the same sequence twice, however, is not accepted by the database ;-)

Background: 

I have a class that has existed for a while. Now I want to add a new class that is the existing class's sibling (they both subclass a common superclass). The new class has loads of  additional attributes, so there is no way of using a FilteredTypeResolver (both classes in one table).

So I wanted to use a HorizontalTypeResolver. This, however, requires the two mapped classes to share a sequence, so that the ids of the two tables don't overlap.

And this is where I got lost: I cannot find a way to tell a field to be a "platform sequence" which re-uses the native db sequence of another table.

Here's what I tried:

tableForMYTABLE: aTable

(aTable createFieldNamed: 'id' type: (self sequenceTypeNamed: 'ExistingTable_id_seq'))

bePrimaryKey.

This leads to a "duplicate name" sql exception when I create the table.

So I am lost.

Is there some other way to have two tables share a sequence that is auto-generated by the database? It seems all of Glorp's Sequence classes that sound as if they could help me are not finished, there are lots of subclassResponsibilities that are not implemented on these subclasses.

has anybody done this before?

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="ug3rIKvZ7cgJ" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="ug3rIKvZ7cgJ" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

I was bitten by that too. And ended up doing the same as you, create sequence's DDL statements separately.

These days my object/data model is more stable, I do the DDL by hand.

If there is a better way to deal with this, particularly for schema migrations, I'll be pleased to know about it.

Regards!

El miércoles, 15 de octubre de 2014 14:09:42 UTC-3, jtuchel escribió:

Nevermind, I found it!

My example works perfectly, all that needs to be done is to not use any of the provided createTable:* methods that also create the sequence for the table. If I only create the table and its foreign key constraints, but not the sequence for the table, all looks very good.

So far I have only tested inserting new objects and the fetch of a new id for the new table from the existing sequence works as expected.

And I tested a readManyOf: the common superclass which now fetches instances of both its subclasses

The next step will be to test whether 1:n relationships to the superclass work, but I see no reason why this should not work, because the readManyOf: of the superclass works.

So I learned something new today about Glorp. And even if it cost me a few hours to find out, it shows me once more how much flexibility is hidden in it. It is an OR mapper with all its consequences, but it's a really good one (except for its lack of documentation).

Joachim

Am Mittwoch, 15. Oktober 2014 16:05:32 UTC+2 schrieb jtuchel:

Hi,

this time I have a really tricky one.

I need to use an existing DB2Sequence of an existing table for the primary key of a newly added table.

When Glorp creates the table (in #createTables:)  it always assumes there is a strict 1:1 relationship between tables and sequences, so it tries to create a sequence for the new table. Creating the same sequence twice, however, is not accepted by the database ;-)

Background: 

I have a class that has existed for a while. Now I want to add a new class that is the existing class's sibling (they both subclass a common superclass). The new class has loads of  additional attributes, so there is no way of using a FilteredTypeResolver (both classes in one table).

So I wanted to use a HorizontalTypeResolver. This, however, requires the two mapped classes to share a sequence, so that the ids of the two tables don't overlap.

And this is where I got lost: I cannot find a way to tell a field to be a "platform sequence" which re-uses the native db sequence of another table.

Here's what I tried:

tableForMYTABLE: aTable

(aTable createFieldNamed: 'id' type: (self sequenceTypeNamed: 'ExistingTable_id_seq'))

bePrimaryKey.

This leads to a "duplicate name" sql exception when I create the table.

So I am lost.

Is there some other way to have two tables share a sequence that is auto-generated by the database? It seems all of Glorp's Sequence classes that sound as if they could help me are not finished, there are lots of subclassResponsibilities that are not implemented on these subclasses.

has anybody done this before?

Joachim