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 when they differ by 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 for the instance microservice, which display the duplicate user accounts that will need to be removed for the migration to REGARDS 2.1.

note

For the name rs-instance database, you may use another name, but we advise this name.

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.

info

If several results are returned, you must decide which REGARDS accounts to delete.

You must be connected to the admin schema of the rs-instance database.
Execute an SQL query to delete the invalid accounts from the t_account table, you need to keep track of the removed accounts for a later step.

For the remaining valid accounts, you can update their associated projects by running an SQL query that inserts the account identifier and the project identifier (from the t_project table) into the ta_account_project table. This table defines the link between a project and an account in REGARDS.

Then, delete all project users linked to the removed accounts from each project schema.
This can be done by executing SQL queries on the t_project_user table within each project schema under the admin schema.

See info in rs-admin-instance microservice : access right

Dissemination info

In the version 2.1 of REGARDS, the database no long allows duplicated disseminations (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 disseminations. 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.