V2.0 to V2.1

Steps to migrate REGARDS from version 2.0 to 2.1

Database modifications

Email addresses

In version 2.1 of REGARDS, it is no longer allowed to have multiple users with identical email addresses except for the case. It is therefore necessary before the migration to check the uniqueness of the usernames by case-sensitive email address.

Here are some SQL queries to run on the admin schema of the rs-instance database to display the duplicate user accounts that will need to be removed for the migration to REGARDS 2.1.

SELECT LOWER(t_account.email), count(*) FROM "admin"."t_account" group by LOWER(t_account.email) HAVING COUNT(*) > 1;

SELECT * FROM "admin"."t_account" WHERE LOWER(email) IN (SELECT LOWER(t_account.email) FROM "admin"."t_account" group by LOWER(t_account.email) HAVING COUNT(*) > 1) ORDER BY email;

In order for the REGARDS application to be migrated and started, the previous queries must return no results.

Dissemination info

In the version 2.1 of REGARDS, the database does not allow anymore duplicated dissemination (same recipient and same requestId) for features (FEM products). It means that all existing duplicated dissemination must be removed. Here are some SQL queries to run on the fem schema of the project database to create a new table with the duplicated dissemination. Please note that you need to update the following script with realistic dates, depending on your fem.t_feature_dissemination size. Keep the time interval short, and repeat scripts to cover all features stored in database.

DROP TABLE IF EXISTS fem.TMP_DOUBLE_DISS_DETECTED; 
CREATE TABLE fem.TMP_DOUBLE_DISS_DETECTED AS
SELECT id, B.feature_id, B.label, request_date, ack_date, doublon_count
FROM fem.t_feature_dissemination_info B 
JOIN (
    SELECT label, feature_id, COUNT(*) AS doublon_count 
    FROM fem.t_feature_dissemination_info
    WHERE request_date BETWEEN '2010-03-01 00:00:00' AND '2030-04-01 00:00:00' 
    AND feature_id > 0 
    GROUP BY label, feature_id 
    HAVING COUNT(*) > 1
) AS sous_requet
ON sous_requet.feature_id = B.feature_id AND sous_requet.label = B.label;

DROP TABLE IF EXISTS fem.TMP_OLDEST_DISS_ACK; 
CREATE TABLE fem.TMP_OLDEST_DISS_ACK AS 
SELECT DISTINCT ON (label, feature_id) *
FROM fem.TMP_DOUBLE_DISS_DETECTED
WHERE ack_date is not null
ORDER BY label, feature_id, ack_date ASC;

DROP TABLE IF EXISTS fem.TMP_OLDEST_DISS_WITHOUT_ACK;
CREATE TABLE fem.TMP_OLDEST_DISS_WITHOUT_ACK AS  
SELECT DISTINCT ON (label, feature_id) *
FROM fem.tmp_double_diss_detected
WHERE ack_date is null
ORDER BY label, feature_id, request_date ASC;

DELETE FROM fem.tmp_oldest_diss_without_ack t
WHERE EXISTS (
    SELECT 1
    FROM fem.TMP_OLDEST_DISS_ACK a
    WHERE a.feature_id = t.feature_id
      AND a.label = t.label
);

SELECT count(*) FROM fem.TMP_DOUBLE_DISS_DETECTED;

This script shows how many duplicated entries have been detected.

Check now the table fem.TMP_DOUBLE_DISS_DETECTED. If there is at least one entry, you have to launch the following second script. This second script delete all duplicated disseminations, and update the dissemination_pending value to false.

DELETE FROM fem.t_feature_dissemination_info
WHERE id IN (SELECT id FROM fem.TMP_OLDEST_DISS_ACK);
DELETE FROM fem.t_feature_dissemination_info
WHERE id IN (SELECT id FROM fem.TMP_OLDEST_DISS_WITHOUT_ACK);
UPDATE fem.t_feature SET dissemination_pending = FALSE
WHERE id IN (SELECT feature_id FROM fem.TMP_OLDEST_DISS_ACK);
UPDATE fem.t_feature SET dissemination_pending = FALSE
WHERE id IN (SELECT feature_id FROM fem.TMP_OLDEST_DISS_WITHOUT_ACK);

This script does not return anything.

Once all FEM features have been treated, you can launch the third script:

DROP TABLE fem.TMP_DOUBLE_DISS_DETECTED;
DROP TABLE fem.TMP_OLDEST_DISS_ACK; 
DROP TABLE fem.TMP_OLDEST_DISS_WITHOUT_ACK;

This script does not return anything.

New downloader service

As of REGARDS version 2.1.0, file download functionality has been delegated to a dedicated microservice. It's no longer the storage microservice that's in charge, but the new downloader microservice.

To add the downloader microservice to your REGARDS deployment as of version 2.1.0, add it to your inventory as indicated in the installation manual.

The aim of this new microservice is to make downloading highly scalable.