Hi Michael! > On 9 Jul 2020, at 14:20, Michael Gentry <blackn...@gmail.com> 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 <h...@karlmenn.is> 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
