chibenwa commented on code in PR #2444: URL: https://github.com/apache/james-project/pull/2444#discussion_r1794217329
########## upgrade-instructions.md: ########## @@ -34,6 +34,36 @@ Change list: - [Migrate RabbitMQ classic queues to version 2](#migrate-rabbitmq-classic-queues-to-version-2) - [JAMES-3946 White list removals](#james-3946-white-list-removals) - [JAMES-4052 Details in quota index](#james-4052-details-in-quota-index) + - [JAMES-1409 Change JPARecipientRewriteTable to store separate record per target address](#james-1409-change-jparecipientrewritetable-to-store-separate-record-per-target-address) + +### JAMES-1409 Change JPARecipientRewriteTable to store separate record per target address + +Date: 09/10/2024 + +JIRA: https://issues.apache.org/jira/browse/JAMES-1409 + +The JPARecipientRewriteTable was modified to store multiple mappings of a single source as separate database rows, +each with a single target address, instead of a single row with a long semicolon-delimited multi-value target address. +This solves both the limitation on the number of mappings (imposed by the column maximum length), and the broken query +by target address. + +For JPA users, the database schema must be changed as following: + +- Change Primary key from (DOMAIN_NAME, USER_NAME) to (DOMAIN_NAME, TARGET_ADDRESS, USER_NAME) (unfortunately, + JPA auto-generates it in alphabetical order). +- Add index USER_NAME_DOMAIN_NAME_TARGET_ADDRESS_INDEX (USER_NAME, DOMAIN_NAME, TARGET_ADDRESS). +- Add index TARGET_ADDRESS_INDEX (TARGET_ADDRESS). +- The columns themselves have not changed. + +In addition, the existing data must be migrated to the new structure: + +- Rows with a single address in the TARGET_ADDRESS column are unchanged. +- Rows that have multiple semicolon-delimited addresses in their TARGET_ADDRESS column need to be split into + individual rows, each with a single target address value. + Unfortunately, there is no standard way to do this in SQL. In some databases it may be possible to build + a recursive query that can do this. Otherwise, it can be done manually using database management tools, + or via a short external script, or via exporting the data, manual editing it, and re-importing the migrated + data. Review Comment: Or we could: - Ask the user to list all mappings through the CLI prior to the upgrade - Change the table name: a new table will be used, empty - Provide a new CLI command to import the mappings from the previously saved input That way user do not looses data. WDYT? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: notifications-unsubscr...@james.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: notifications-unsubscr...@james.apache.org For additional commands, e-mail: notifications-h...@james.apache.org
