V4 - Self referencing table with composite key

Posts   
 
    
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 10-Apr-2013 11:33:17   

Hi Frans,

First of all congratulations on the new release!!

While upgrading one of my projects I encountered an issue during validation which was not a problem in V3.

For this (simplified) table:

CREATE TABLE [OrderItemsLink]
    (
      [OrderId] [int] NOT NULL ,
      [ItemId] [bigint] NOT NULL ,
      [ParentId] [bigint] NULL ,
      CONSTRAINT [PK_db33b5c4e9c844f3c0987f9e1df] PRIMARY KEY CLUSTERED
        ( [OrderId] ASC, [ItemId] ASC )
    )
GO

ALTER TABLE [OrderItemsLink]  WITH CHECK ADD  CONSTRAINT [FK_cc237dc4f1b8b98277dc5bb91f3] FOREIGN KEY([OrderId], [ParentId])
REFERENCES [OrderItemsLink] ([OrderId], [ItemId])
GO

the designer reports this error:

The relationship 'OrderItemsLink.ParentLink - OrderItemsLink.Contents (m:1)' has a foreign key field 'OrderItemsLink.OrderId (FK)' which points to itself, which isn't supported.

In V3 this was not reported, so perhaps the second part of the key is ignored in V4?

Regards, André

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 11-Apr-2013 07:27:46   

Hi André,

Yes, I reproduced the error message on v4. And yes, it worked before on v3.5. We will look into this (whether this is or not a new breaking change).

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Apr-2013 11:26:42   

I do recall it was a restriction in earlier v3.5 but was lifted in later builds, but have to check. If that's the case we accidentally forgot to port it over to v4. In any case, we'll lift the restriction simple_smile

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 11-Apr-2013 11:34:41   

Great!!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Apr-2013 12:19:18   

It was a restriction which was lifted on March 12th, 2012 in v3.1 and backported to v3.5, but apparently not to v4.0. We'll backport the changes to v4 as well.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 11-Apr-2013 12:32:09   

Great!

I found no other issues so far, so when the fix is there I can start testing the performance gains.

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Apr-2013 12:39:08   

The test in the was added in v4. The restriction I mentioned above was in the FK / relational model data which was lifted in v3.1, and that code is still present in v4.

The test added was added to find relationships which fully point to themselves, this was added during v4 beta. Your example shows the test is too strict, it should only raise an error if all fields point to themselves. We'll correct this mistake.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 11-Apr-2013 12:49:00   

Thanks for the explanation!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Apr-2013 12:54:28   

Attached is a debug build of the applicationcore dll, which fixes the issue. please copy this into the installation folder as administrator and test whether this solves your problem. The final build will be out later today.

Attachments
Filename File size Added on Approval
SD.LLBLGen.Pro.ApplicationCore.zip 362,085 11-Apr-2013 12:54.36 Approved
Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 11-Apr-2013 13:08:07   

Thanks for the quick fix. I will test it this evening.

twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 12-Apr-2013 00:57:54   

It is fixed.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 12-Apr-2013 07:42:53   

twaindev wrote:

It is fixed.

Good sunglasses Thanks for the feedback

David Elizondo | LLBLGen Support Team