Re: M:N-like table with a contents, updating the PK leads to problems

[OCsite via Webobjects-dev]

markus,

thanks!

> On 11. 10. 2022, at 11:55, mailinglists  wrote:
> Thou shall never ever change a primary key!

Yup, I suspected that it would be the culprit.

> Replace the compound PK with a “real” ID and be done. This is not such a big 
> thing to do. I've done this several times. You need a bit of SQL to “fix” 
> your database, but that is no rocket science.

I must admit SQL is not my force. I've tried in Frontbase Manager to
- add a new INT column c_uid to the table
- remove the primary key flag from both department_id and user_id
- add the flag to the new column
and I keep getting errors.

> This m:n join table is not a mere technical requirement anymore but now 
> represents business logic so it really really should have its own dedicated 
> primary key.

Well yes; on the other hand, when this happened, there was absolutely no 
intention to ever change the user (or department) relationship. Thus it was 
much easier to keep the PK as-was.

> At least that’s how I would do it.

I wonder whether it wouldn't be easier as Ramsey suggested to delete the object 
and insert a new one, which would be a copy of the old one but for the user_id 
value.

Actually, given that, I wonder whether it might be possible to
(a) just change the user_id value of the existing object;
(b) and somehow make EOF to think nothing was updated; instead that the old one 
was deleted and new one inserted.

For, far as the database contents goes, there would be no difference 
altogether. The difference is purely WO-side, in the way the changes are merged 
into snapshots and other ECs.

That would be, I suppose, far easiest approach — if, that is, (b) can be done 
reliably. Would that be possible somehow?

Thanks and all the best,
OC

>> On 11 Oct 2022, at 00:16, OCsite via Webobjects-dev 
>> mailto:webobjects-dev@lists.apple.com>> 
>> wrote:
>> 
>> Hi there,
>> 
>> I've just bumped into a new problem. There's a table which, many years ago, 
>> was created as an invisible M:N intermediate table. Later, we needed to add 
>> some information to the relationship, so now we have a table, say, 
>> Connection, which has
>> - a number of normal attributes
>> - a compound PK (department_id, user_id) which contains two FKs into two 
>> other tables, say, User and Market (the remaining of the original M:N 
>> intermediate)
>> - two :1 relationships to those two tables (user and market).
>> 
>> Both User and Market tables model :N relationships connections (owning, 
>> PK-propagating), which long long ago replaced the original flattened M:N 
>> ones. Worked like a charm for years.
>> 
>> Now though, I've got a new requirement: I need to be able to change the user 
>> of a given Connection.
>> 
>> I've found that 
>> aConnection.addObjectToBothSidesOfRelationshipWithKey(newUser,'user') seems 
>> to work sort of properly — looks like all the relationships are properly 
>> updated and the key in the Connection table is changed in the database all 
>> right.
>> 
>> The catch is, sometimes (by far not always), a short time after the change, 
>> I start getting
>> 
>> No Connection found with globalID: > Y] >
>> 
>> with the original pre-change values of X and Y.
>> 
>> I can't be quite sure, but I think probably there's sometimes a :N 
>> User.connections snapshot which contains the  globalID of the original 
>> object. Since the user relationship change of its target actually changes 
>> the very PK of the object, the EOF synchronisation does not match the 
>> updated object (with a different PK => different globalID) with the original 
>> one and does not update the snapshot. Then, someone touches the 
>> relationship, gets the snapshot, EOF creates a fault with the original 
>> values, and when the fault fires, oops, there's nothing like that in the 
>> database.
>> 
>> Does anybody see how to fix the problem?
>> 
>> In principle I guess I could go programmatically through all the :N 
>> snapshots and try to find the old globalIDs and replace them by the new 
>> ones; but it would be sorta non-trivial and definitely dangerous...
>> 
>> Thanks,
>> OC
>> 
>> 
>> 
>> ___
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list  (Webobjects-dev@lists.apple.com 
>> )
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/mailinglists%40kataputt.com
>>  
>> 
>> 
>> This email sent to mailingli...@kataputt.com
> 
> 

 ___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list  (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Re: M:N-like table with a contents, updating the PK leads to problems

[mailinglists via Webobjects-dev]

Thou shall never ever change a primary key!

Replace the compound PK with a “real” ID and be done. This is not such a big 
thing to do. I've done this several times. You need a bit of SQL to “fix” your 
database, but that is no rocket science.

This m:n join table is not a mere technical requirement anymore but now 
represents business logic so it really really should have its own dedicated 
primary key.

At least that’s how I would do it.

Good luck
---markus---

> On 11 Oct 2022, at 00:16, OCsite via Webobjects-dev 
>  wrote:
> 
> Hi there,
> 
> I've just bumped into a new problem. There's a table which, many years ago, 
> was created as an invisible M:N intermediate table. Later, we needed to add 
> some information to the relationship, so now we have a table, say, 
> Connection, which has
> - a number of normal attributes
> - a compound PK (department_id, user_id) which contains two FKs into two 
> other tables, say, User and Market (the remaining of the original M:N 
> intermediate)
> - two :1 relationships to those two tables (user and market).
> 
> Both User and Market tables model :N relationships connections (owning, 
> PK-propagating), which long long ago replaced the original flattened M:N 
> ones. Worked like a charm for years.
> 
> Now though, I've got a new requirement: I need to be able to change the user 
> of a given Connection.
> 
> I've found that 
> aConnection.addObjectToBothSidesOfRelationshipWithKey(newUser,'user') seems 
> to work sort of properly — looks like all the relationships are properly 
> updated and the key in the Connection table is changed in the database all 
> right.
> 
> The catch is, sometimes (by far not always), a short time after the change, I 
> start getting
> 
> No Connection found with globalID:  Y] >
> 
> with the original pre-change values of X and Y.
> 
> I can't be quite sure, but I think probably there's sometimes a :N 
> User.connections snapshot which contains the  globalID of the original 
> object. Since the user relationship change of its target actually changes the 
> very PK of the object, the EOF synchronisation does not match the updated 
> object (with a different PK => different globalID) with the original one and 
> does not update the snapshot. Then, someone touches the relationship, gets 
> the snapshot, EOF creates a fault with the original values, and when the 
> fault fires, oops, there's nothing like that in the database.
> 
> Does anybody see how to fix the problem?
> 
> In principle I guess I could go programmatically through all the :N snapshots 
> and try to find the old globalIDs and replace them by the new ones; but it 
> would be sorta non-trivial and definitely dangerous...
> 
> Thanks,
> OC
> 
> 
> 
> ___
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list  (Webobjects-dev@lists.apple.com)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/mailinglists%40kataputt.com
> 
> This email sent to mailingli...@kataputt.com


 ___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list  (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com

This email sent to arch...@mail-archive.com