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

[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

[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!

[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.

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

[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

[jtuchel]

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...