Re: M:N-like table with a contents, updating the PK leads to problems
Re: M:N-like table with a contents, updating the PK leads to problems
- Subject: Re: M:N-like table with a contents, updating the PK leads to problems
- From: OCsite via Webobjects-dev <email@hidden>
- Date: Tue, 11 Oct 2022 13:33:28 +0200
markus,
thanks!
> On 11. 10. 2022, at 11:55, mailinglists <email@hidden> 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
>> <email@hidden <mailto:email@hidden>>
>> 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: <Connection: [department_id: X, user_id:
>> 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 (email@hidden
>> <mailto:email@hidden>)
>> Help/Unsubscribe/Update your Subscription:
>>
>>
>> This email sent to email@hidden
>
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden