Add default fallback foreign key to Copy Client (FHCA-6009)
Description
Environment
relates to
Activity
Carlos Ruiz April 4, 2025 at 7:45 PM
Documented at https://wiki.idempiere.org/en/HowTo:_Copy_or_Move_Tenant
Carlos Ruiz March 27, 2025 at 10:59 PM
When testing the solution described above, a failure was found with table AD_User_Roles when two missing foreign keys on AD_User were pointing to a single fallback local record.
Then a solution was needed to manage that case, allowing to map each foreign missing key to a different local fallback record.
Solution was to allow two forms of fallback record definition:
table.foreignUuid=idOrUuid
table=idOrUuid
The first is the detailed case, and the second is the general case, the second to be applied if the first is not found.
Also, to ease the discovery of the missing keys a summary of them is shown at the end of the log.
For example, in my tests I added the following in the source database:
two system users
one system unit of measure
one system schedule
those three records were not present in the target database.
When running the process, the following summary is shown at the end of the log:
Missing Key -> AD_Schedule.AD_Schedule_UU=d8a3e628-a604-498d-9c3b-92dc8e5338fb
Missing Key -> AD_User.AD_User_UU=c45ab851-984a-4052-b8fe-f74bcf1bfaf9
Missing Key -> AD_User.AD_User_UU=da5b4b2d-20a4-4f06-a0d1-88b2002c9dc2
Missing Key -> C_UOM.C_UOM_UU=83201a0c-dbf1-4c93-ae7f-3c89871a044bThen I created one UOM, one schedule and one user in the target database and tested with the following fallback keys definition:
AD_User=1000000, C_UOM=70bd761d-de32-4354-9158-7517057f0651, AD_Schedule=1000000The system threw an error because of duplicated key in AD_User_Roles
Then I created a second fallback user and tested with the following definition:
AD_User.c45ab851-984a-4052-b8fe-f74bcf1bfaf9=1000000, AD_User.da5b4b2d-20a4-4f06-a0d1-88b2002c9dc2=54f355eb-2b4c-4bec-ba76-2d5cd62e164c, C_UOM=70bd761d-de32-4354-9158-7517057f0651, AD_Schedule=1000000And the process worked perfect.
This fallback key means four things:
AD_User.c45ab851-984a-4052-b8fe-f74bcf1bfaf9=1000000 → this means the foreign key c45ab851-984a-4052-b8fe-f74bcf1bfaf9 will be mapped to the local fallback record with ID 1000000
AD_User.da5b4b2d-20a4-4f06-a0d1-88b2002c9dc2=54f355eb-2b4c-4bec-ba76-2d5cd62e164c → this means the foreign key da5b4b2d-20a4-4f06-a0d1-88b2002c9dc2 will be mapped to the local fallback record with UUID 54f355eb-2b4c-4bec-ba76-2d5cd62e164c
C_UOM=70bd761d-de32-4354-9158-7517057f0651 → this means any missing UOM in the source database will be mapped to the local C_UOM record with UUID 70bd761d-de32-4354-9158-7517057f0651
AD_Schedule=1000000 → this means any missing Schedule in the source database will be mapped to the local AD_Schedule record with ID 1000000
The Copy Client process tends to fail with foreign key problems when moving a tenant to another database.
The case is as follows:
in server A you have a system support user "John Doe", this system support user has access to tenant X and creates some records
in server B "John Doe" doesn't exist, so, when trying to move the tenant X from server A to B, all records created by "John Doe" fail because the CreatedBy cannot be resolved.
So, idea here is to implement another parameter that implements a way to define a fallback record for the case when a foreign key is not found.
The parameter would be named "FallbackRecordsWhenFKNotFound", type varchar 4000 and it would accept pairs of table=id (or uuid) separated by commas.
For example in the case above that CreatedBy fails for "John Doe" we could pass the parameter:
AD_User=100 to assign all failing CreatedBy to SuperUser
same for parameter AD_User=986cb4dc-7161-4d77-a7c7-3832d24b19de
NOTE that's a bad example, the recommended usage is maybe to create a user "Migration User from Tenant X" on server B and use that user as fallback.
Consider also if we can add a column to T_MoveClient (or another mechanism, like in the log) to preserve the Value of the source database, in such case T_MoveClient could contain the reference that in the source database it was "John Doe"