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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

jtuchel
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Esteban A. Maringolo
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

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Tom Robinson
In reply to this post by jtuchel
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

jtuchel
Tom,

I think TOPLink supported this. At least one of our customers uses this model with TOPLink on VAST: One Sequence for all tables/classes.

What you describe is just the other extreme edge case of the same problem: The current implementation of DescriptorSystem implements the other one: 1 sequence per table). Although the rest of the framework can handle "shared" sequences without any problems (at least as far as I can tell right now).

So what would be nice is if DescriptorSystem could be changed to support another concept of the relationship between tables and sequences. I guess this is not without problems with regards to existing DescriptorSystem subclasses...

Joachim


Am Donnerstag, 16. Oktober 2014 23:29:18 UTC+2 schrieb Tom Robinson:
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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

jtuchel
In reply to this post by Esteban A. Maringolo
Oh, good to hear I am not the only one. 
Glorp is a bit complex, so you always tend to wonder if it's just your own stupidity. So at least I am not alone here ;-)

Am Donnerstag, 16. Oktober 2014 21:43:08 UTC+2 schrieb Esteban A. Maringolo:
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.

I am also doing schema changes "by hand", meaning I implement a method that runs at startup time of my Seaside Server and creates/alters tables as needed. 
 
If there is a better way to deal with this, particularly for schema migrations, I'll be pleased to know about it.

I just saw that in the Glorp Documentation that ships with VisualWorks, there is a chapter on migrations from one DescriptorSystem to another. And there is some code in Glorp that determines new tables, tables to alter and such. I decided to take a closer look when I have time, but stay with my way of doing it for now.

Now that I have this problem with reusing Sequences, it is very unlikely that I have another chance anyways. But I am happy with this, so far. I'd have to introduce some mechanics to determine the DescriptorSystem subclass to migrate from/to and which one to use at runtime and so one, which also means I'd have to do a major redesign of my startup and configuration routines...


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

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.