NF6.2 Copy Client
Feature: Copy Client
Goal: Technical
Developers: Carlos Ruiz
Sponsors: FH
Description
There are two use cases for this process:
- allows to move a whole client (tenant) from an external database to the current, it can be used even to move a tenant from Oracle to PostgreSQL
- allows to copy a template client in the current tenant into a new client
Usage
- Log in as System
- Open the process Copy Client
- Fill the parameters properly as explained below in detail
- If there are errors, analyze and solve
- Tip: if there are too many errors, you can find all of them in the Process Audit window, Log tab. This can be exported to CSV or Excel also for easier analysis.
Parameters
Copy Template Client: Flag to indicate if the copy is from an external database, or local from a template client.
Parameters for External Copy
JDBC URL: JDBC URL of the external database.
User Name: user used to connect to the JDBC URL. Optional, if empty use the same as target.
Password: password used to connect to the JDBC URL. Optional, if empty use the same as target.
Tables to Exclude: Comma separated list of tables to exclude. Temporary tables (name starting with T_) are always excluded.
Clients to Include: Comma separated list of AD_Client_ID to include, if empty then all non-System clients will be moved. You must usually exclude client 11 (GardenWorld).
Clients to Exclude: Comma separated list of AD_Client_ID to exclude. Just Include or Exclude can be used, not both at the same time.
Only Validate: To do just initial validations and not execute the process.
Preserve IDs: Comma separated list of tables that require to preserve IDs. You must take care that the IDs don't collide.
Parameters for Copy Template
Client Name: This is the name of the new client to be created.
Client Key: The search key for the new client to be created, this is used to replace some initial object names (explained below in process).
Clients to Include: This is the ID of the client to take as template - just one ID must be filled here (for example 11 for GardenWorld).
Only Validate: To do just initial validations and not execute the process.
Skip some validations: In order to make the process faster, you can enable this flag if you're sure the data is correct (for example if all validations passed in some previous check).
Copy Client from External DB
Validations
Before starting to copy the external data the process performs the following validations:
- General validations
- Verify connection to source database using the URL provided (and username/password also if necessary)
- Verify there are clients to move with the include/exclude filter provided
- Verify the client does not exist in current database
- Verify that attachments and archive are using the internal storage provider (Database) - migrating external storage provider is not implemented. This validation is just necessary if there are attachment/archive records in the client to be moved, and also if the attachment/archive table has not been excluded.
- Table validations: Each table in the dictionary of the source database (not excluded in the filter) is checked, these validations are made just for tables that have client data
- Table must be in target database - if the table doesn't exist in target database then the error is informed and the process cannot be executed
- Column validations: Each column of a table being moved is validated
- The column must exist in target database
- Source and target column must have the same reference - this validation is just performed for official references
- Source and target column must have the same length in dictionary
- Columns with types SingleSelectionGrid and MultipleSelectionGrid are not yet supported, they are informed if they contain data.
- Foreign key validations: Each column that is a foreign key is validated for
- If the column has a foreign key in a client different than system the error is informed - this can be considered a cross-client data corruption error and is considered a stopper for the process
- If the column has foreign keys in System client, then the UUIDs are verified to exist in the target database. If the system UUID doesn't exist this is informed and the process cannot be executed. To solve this error consider moving the required System data from source to target database using 2Pack.
After passing all validations it is possible to execute the process, but still there is a possibility that required null columns or other foreign key issues are present in the database, this will be informed in the processing if found.
Process
When all validations pass then the process can be executed if the flag "Only Validate" is disabled.
The process takes the records from every table to be moved, read it from the source database and insert the record in the target database.
The IDs are changed as necessary in the target database, but the UUIDs are preserved. The conversion map of the IDs is saved in the temporary table T_MoveClient.
When inserting record the process needs to find the corresponding foreign key conversion for the IDs. There can be some cases where orphan records are found at this stage (foreign keys not existing), this is informed and the process is stopped. Also if the table is flagged to Preserve IDs is selected, and the ID already exists in the target database this is informed and the process is stopped.
Note also that tables containing AD_Table_ID+Record_ID will migrate just the records of the included tables (this is, it will not migrate records for excluded tables).
There is a special treatment for the column AD_Preference.Value when AD_Preference.Attribute ends with _ID, in this case the Value is treated as a foreign key and the required conversion is saved as a String. However if your database has other ID fields being written as String (for example SingleSelectionGrid and MultipleSelectionGrid) these values are not being converted.
Copy Template Client within current DB
Validations
Before starting to copy the template client the process performs the following validations:
- General validations
- Verify there is just one client to move in the include filter provided
- Verify the client does not exist in current database (verified by Name and Key)
- Verify there is not W_Store record with the context as client key in lowercase
- Verify that attachments and archive are using the internal storage provider (Database) - migrating external storage provider is not implemented. This validation is just necessary if there are attachment/archive records in the client to be moved, and also if the attachment/archive table has not been excluded.
- Table validations: Each table in the dictionary of the source database (not excluded in the filter) is checked, these validations are made just for tables that have client data
- Column validations: Each column of a table being copied is validated
- Columns with types SingleSelectionGrid and MultipleSelectionGrid are not yet supported, they are informed if they contain data.
- Foreign key validations: Each column that is a foreign key is validated for
- If the column has a foreign key in a client different than system the error is informed - this can be considered a cross-client data corruption error and is considered a stopper for the process
After passing all validations it is possible to execute the process, but still there is a possibility that required null columns or other foreign key issues are present in the database, this will be informed in the processing if found.
Process
When all validations pass then the process can be executed if the flag "Only Validate" is disabled.
The process takes the records from every table to be copied, read it from the template client and insert the record in the target client.
The IDs are changed, and also the UUIDs are changed. The conversion map of the IDs is saved in the temporary table T_MoveClient. The conversion map of the UUIDs is saved in the table AD_Package_UUID_Map.
When inserting record the process needs to find the corresponding foreign key conversion for the IDs. There can be some cases where orphan records are found at this stage (foreign keys not existing), this is informed and the process is stopped.
Note also that tables containing AD_Table_ID+Record_ID will migrate just the records of the included tables (this is, it will not migrate records for excluded tables).
There is a special treatment for the column AD_Preference.Value when AD_Preference.Attribute ends with _ID, in this case the Value is treated as a foreign key and the required conversion is saved as a String. However if your database has other ID fields being written as String (for example SingleSelectionGrid and MultipleSelectionGrid) these values are not being converted.
The following columns replace the template client key with the target client key:
- AD_Org.Value
- AD_Org.Name
- AD_Role.Name
- AD_Tree.Name
- AD_User.Name
- AD_User.Description
- C_AcctProcessor.Name
- C_AcctSchema.Name
- C_BPartner.Value
- C_BPartner.Name
- C_Calendar.Name
- C_Element.Name
- M_CostType.Name
- R_RequestProcessor.Name
The column W_Store.WebContext is assigned with the Client Key in lowercase. Same for the column AD_User.Value.
The columns AD_User.Password and AD_User.Salt are cleared, so initially there are not passwords assigned for the users in the new tenant, it is a job of SuperUser to log in there and assign passwords (or do it in some custom post-process).
Technical Info: IDEMPIERE-3916
Known Issues
- Alex reported that copying a big tenant from another postgresql got error "java.net.SocketException: Connection timed out (Read failed)". Changed tcp_keepalives_idle in postgresql.conf to 300 sec and the process didn't hang anymore.
