Re: Migrating from exposed, compund PKs to single-column db generated keys

Hi Michael!

> On 9 Jul 2020, at 14:20, Michael Gentry <[email protected]> wrote:
> 
> Hi Hugi,
> 
> Are the legacy systems that are accessing this DB doing inserts?  If so,
> check if the DB has a way to do an automatic generated PK for that column's
> value if it is null upon insert.  If the inserts are only done from the
> Cayenne-based system, you'll be OK there.  Not sure if there'd be
> update/delete implications from the legacy system, but I suspect these may
> be OK.

Yeah, the legacy systems are doing updates/inserts. As you suggest, the new id-column is a serial column (in postgres; basically just an integer column backed by a sequence invoked on each insert) meaning PK-generation happens entirely on the DB side, so hopefully we're good there :).

Updates to FKs in the old system (and keeping the new FKs in sync) are a whole other can of worms, but unrelated to the Cayenne side of things so I didn't mention it here. But for anyone curious (or looking at this thread in the future), we're setting up a DB-trigger that logs all changes in the DB to a single "audit" table. A java process (Cayenne-driven, yay!) then looks at those changes, checks if the old FKs were touched and updates/aligns the corresponding new FKs. A bit horrid but I haven't been able to figure out a better way (the old applications are essentially a black box and can't be modified).

Cheers,
- hugi


> On Thu, Jul 9, 2020 at 9:17 AM Hugi Thordarson <[email protected]> wrote:
> 
>> Hi all,
>> 
>> I'm currently in the progress of migrating an old DB schema from using
>> exposed meaningful PKs to using db generated (serial) surrogate PKs.
>> However, some legacy systems still use the same DB, so my new PKs will
>> have to coexist alongside the older ones for a while.
>> 
>> My current plan:
>> - Each table gains a new column named "id" of the type serial, with a
>> unique constraint applied to it.
>> - The corresponding Cayenne DbEntity is changed to use the new "id" column
>> as the PK with a PK-generation strategy of "Database generated".
>> - Every table with relationships to this entity gets appropriate FKs that
>> reference the new "id" column instead of the old columns (and their
>> corresponding DbRelationships get changed as well)
>> 
>> Note that the tables will keep their old PK as the real, defined PK — the
>> "id" column is a plain addition.
>> 
>> So, my question is: Does anyone see a problem with this? What this
>> essentially means is that for a while, Cayenne will be using a "fake"
>> primary key to identify objects, i.e. not the table's actual defined PK but
>> just a regular unique integer column.
>> 
>> Initial testing seems to suggest that this works well, but I just wanted
>> to check if I could expect something horrid to happen down the line :).
>> 
>> Cheers,
>> - hugi