[Observe-commits] [Git][ultreiaio/ird-observe][develop-9.0.x] 5 commits: Persistence migration - Remove more ghost rows (coming from empty lines in a Table form)

22 Nov 2022


      Tony CHEMIT pushed to branch develop-9.0.x at ultreiaio / ird-observe


Commits:
78cf1718 by Tony Chemit at 2022-11-22T12:23:19+01:00
Persistence migration - Remove more ghost rows (coming from empty lines in a Table form)

- - - - -
3fb10c3f by Tony Chemit at 2022-11-22T19:38:50+01:00
Persistence sql - add DataDtoEntityContext.toTimeStamp method (should be moved to SqlHelper and replace the timestamp method to make generated sql easier to read)

- - - - -
0ce88e90 by Tony Chemit at 2022-11-22T19:55:24+01:00
Persistence migration - Review WeightCategoryRecord loading and make migration safe on it (we now refuse a migration if there is some missing required categories)

- - - - -
24377f0f by Tony Chemit at 2022-11-22T19:55:24+01:00
Persistence migration - Improve migration v9.0 code around catches and sample (in executor nothing is done, juste delegate to clever methods on records, this makes the executor code easier to read)

- - - - -
aec0ae22 by Tony Chemit at 2022-11-22T21:21:23+01:00
Merge branch 'feature/issue_2537' into develop-9.0.x
Closes #2537

- - - - -


8 changed files:

- core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DataSourceMigrationForVersion_9_0.java
- − core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DiscardedTargetCatchRecord.java
- core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/SampleMeasureRecord.java
- core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/SampleRecord.java
- core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/NotDiscardedTargetCatchRecord.java → core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/TargetCatchRecord.java
- core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/WeightCategoryRecord.java
- core/persistence/resources/src/main/resources/db/migration/v8/8.1/99_fix_not_null_constraints-common.sql
- toolkit/persistence/src/main/java/fr/ird/observe/spi/context/DataDtoEntityContext.java


Changes:

=====================================
core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DataSourceMigrationForVersion_9_0.java
=====================================
@@ -32,14 +32,10 @@ import org.apache.logging.log4j.Logger;
 import org.nuiton.topia.service.migration.resources.MigrationVersionResource;
 import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor;
 
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
 import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
-import java.util.TreeMap;
-import java.util.function.Function;
 import java.util.stream.Stream;
 
 /**
@@ -57,6 +53,7 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe
     public static final String INFORMATION_SOURCE_U = "fr.ird.referential.ps.observation.InformationSource#1464000000000#03";
     public static final String SPECIES_FATE_5 = "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.6250731662108877";
     public static final String SPECIES_FATE_6 = "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5722739932065866";
+    public static final String DEFAULT_WEIGHT_MEASURE_METHOD = "fr.ird.referential.common.WeightMeasureMethod#666#03";
     public static final Set<String> SMALL_SPECIES_TO_SPECIES_FATE_6 =
             Set.of("fr.ird.referential.common.Species#1239832685476#0.5618871286604711", //ALB
                    "fr.ird.referential.common.Species#1239832685475#0.13349466123905152", //BET
@@ -158,14 +155,14 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe
             executor.addScript("03_1", "migrate-table-ps_observation_nonTargetCatch");
 
             // migrate TargetCatch
-            migrateCatches(executor);
+            TargetCatchRecord.processTargetCatches(executor);
 
             // To fix well
             executor.addScript("03_2", "migrate-table-ps_observation_catch_well");
 
             // See https://gitlab.com/ultreiaio/ird-observe/-/issues/2436
             // See https://gitlab.com/ultreiaio/ird-observe/-/issues/2539
-            migrateSample(executor);
+            SampleRecord.processSample(executor);
         }
 
         executor.addScript("04_0", "adapt_table-ps_observation_activity");
@@ -258,60 +255,6 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe
         }
     }
 
-    private void migrateCatches(MigrationVersionResourceExecutor executor) {
-
-        Function<String, String> commentFormat = executor.commentFormat();
-        Map<String, WeightCategoryRecord> weightCategoryRecordById = WeightCategoryRecord.loadWeightCategoriesMap(executor);
-
-        executor.doSqlWork(connection -> {
-            // process discarded values
-            try (PreparedStatement statement = DiscardedTargetCatchRecord.prepareStatement(connection)) {
-                try (ResultSet resultSet = statement.executeQuery()) {
-                    while (resultSet.next()) {
-                        DiscardedTargetCatchRecord record = new DiscardedTargetCatchRecord(resultSet);
-                        executor.writeSql(record.toCatchSql(weightCategoryRecordById, commentFormat));
-                    }
-                }
-            }
-            // process not discarded values
-            try (PreparedStatement statement = NotDiscardedTargetCatchRecord.prepareStatement(connection)) {
-                try (ResultSet resultSet = statement.executeQuery()) {
-                    while (resultSet.next()) {
-                        NotDiscardedTargetCatchRecord record = new NotDiscardedTargetCatchRecord(resultSet);
-                        executor.writeSql(record.toCatchSql(weightCategoryRecordById, commentFormat));
-                    }
-                }
-            }
-        });
-    }
-
-
-    private void migrateSample(MigrationVersionResourceExecutor executor) {
-
-        Map<String, SampleRecord> setToSampleMap = new TreeMap<>();
-        Map<String, String> sampleMapping = new TreeMap<>();
-
-        executor.doSqlWork(connection -> {
-            SampleRecord.addNonTargetSample(connection, setToSampleMap, sampleMapping);
-            SampleRecord.addTargetSample(connection, setToSampleMap, sampleMapping);
-        });
-
-        Function<String, String> stringFormat = executor.commentFormat();
-        for (SampleRecord sample : setToSampleMap.values()) {
-            executor.writeSql(sample.toSampleSql(stringFormat));
-        }
-
-        migrateSampleMeasure(executor, sampleMapping);
-    }
-
-    private void migrateSampleMeasure(MigrationVersionResourceExecutor executor, Map<String, String> sampleMapping) {
-        executor.doSqlWork(connection -> {
-            SampleMeasureRecord.addNonTargetSampleMeasure(connection, executor, sampleMapping);
-            SampleMeasureRecord.addDiscardedTargetSampleMeasure(connection, executor, sampleMapping);
-            SampleMeasureRecord.addNotDiscardedTargetSampleMeasure(connection, executor, sampleMapping);
-        });
-    }
-
     @Override
     public void generateFinalizeSqlScript(MigrationVersionResourceExecutor executor) {
         long stringFixedCount = new FixStringHelper(executor).execute();


=====================================
core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DiscardedTargetCatchRecord.java deleted
=====================================
@@ -1,165 +0,0 @@
-package fr.ird.observe.spi.migration.v9;
-
-/*-
- * #%L
- * ObServe Core :: Persistence :: Resources
- * %%
- * Copyright (C) 2008 - 2022 IRD, Ultreia.io
- * %%
- * This program is free software: you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as
- * published by the Free Software Foundation, either version 3 of the
- * License, or (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public
- * License along with this program.  If not, see
- * <http://www.gnu.org/licenses/gpl-3.0.html>.
- * #L%
- */
-
-import fr.ird.observe.dto.StringCleaner;
-import fr.ird.observe.persistence.SqlHelper;
-import fr.ird.observe.spi.context.DataDtoEntityContext;
-
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Timestamp;
-import java.util.Map;
-import java.util.function.Function;
-
-/**
- * Created on 06/11/2022.
- *
- * @author Tony Chemit - dev@tchemit.fr
- * @since 9.0.17
- */
-public class DiscardedTargetCatchRecord {
-
-    private final String id;
-    private final long topiaVersion;
-    private final Timestamp topiaCreateDate;
-    private final String homeId;
-    private final Float catchWeight;
-    private final String weightCategoryId;
-    private final String comment;
-    private final String reasonForDiscardId;
-    private final String setId;
-    private final Timestamp lastUpdateDate;
-    private final String well;
-    private final long setIdx;
-    private final String weightMeasureMethodId;
-
-    public static PreparedStatement prepareStatement(Connection connection) throws SQLException {
-        return connection.prepareStatement("SELECT" +
-                                                   /* 01 */      " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
-                                                   /* 02 */      " tc.topiaVersion + 1," +
-                                                   /* 03 */      " tc.topiaCreateDate," +
-                                                   /* 04 */      " tc.homeId," +
-                                                   /* 05 */      " tc.catchWeight," +
-                                                   /* 06 */      " tc.weightCategory," +
-                                                   /* 07 */      " tc.comment," +
-                                                   /* 08 */      " tc.reasonForDiscard," +
-                                                   /* 09 */      " tc.set," +
-                                                   /* 10 */      " tc.lastUpdateDate," +
-                                                   /* 11 */      " tc.well," +
-                                                   /* 12 */      " -tc.set_idx," +
-                                                   /* 13 */      " tc.weightMeasureMethod" +
-                                                   " FROM ps_observation.TargetCatch tc" +
-                                                   " WHERE tc.discarded");
-    }
-
-    public DiscardedTargetCatchRecord(ResultSet resultSet) throws SQLException {
-        this.id = resultSet.getString(1);
-        this.topiaVersion = resultSet.getLong(2);
-        this.topiaCreateDate = resultSet.getTimestamp(3);
-        this.homeId = resultSet.getString(4);
-        this.catchWeight = resultSet.getFloat(5);
-        this.weightCategoryId = resultSet.getString(6);
-        this.comment = resultSet.getString(7);
-        this.reasonForDiscardId = resultSet.getString(8);
-        this.setId = resultSet.getString(9);
-        this.lastUpdateDate = resultSet.getTimestamp(10);
-        this.well = resultSet.getString(11);
-        this.setIdx = resultSet.getLong(12);
-        this.weightMeasureMethodId = resultSet.getString(13);
-    }
-
-    public String toCatchSql(Map<String, WeightCategoryRecord> weightCategoryRecordById, Function<String, String> commentFormat) {
-        WeightCategoryRecord weightCategoryRecord = weightCategoryRecordById.get(weightCategoryId);
-        if (weightCategoryRecord == null) {
-            throw new IllegalStateException(String.format("Could not find weightCategory with id: %s", weightCategoryId));
-        }
-        return String.format("INSERT INTO ps_observation.Catch(" +
-                                     /*1*/                       "topiaId, " +
-                                     /*2*/                       "topiaVersion, " +
-                                     /*3*/                       "topiaCreateDate, " +
-                                     /*4*/                       "homeId, " +
-                                     /*5*/                       "catchWeight, " +
-                                     /*6*/                       "minWeight, " +
-                                     /*7*/                       "maxWeight, " +
-                                     /*8*/                       "meanWeight, " +
-                                     /*9*/                       "comment, " +
-                                     /*10*/                      "reasonForDiscard, " +
-                                     /*11*/                      "species, " +
-                                     /*12*/                      "speciesFate, " +
-                                     /*13*/                      "set, " +
-                                     /*14*/                      "lastUpdateDate, " +
-                                     /*15*/                      "well, " +
-                                     /*16*/                      "set_idx, " +
-                                     /*17*/                      "weightMeasureMethod, " +
-                                     /*18*/                      "informationSource) VALUES ( " +
-                                     "%1$s, " +
-                                     "%2$s, " +
-                                     "'%3$s'::timestamp, " +
-                                     "%4$s, " +
-                                     "%5$s, " +
-                                     "%6$s, " +
-                                     "%7$s, " +
-                                     "%8$s, " +
-                                     "%9$s, " +
-                                     "%10$s, " +
-                                     "%11$s, " +
-                                     "%12$s, " +
-                                     "%13$s, " +
-                                     "'%14$s'::timestamp, " +
-                                     "%15$s, " +
-                                     "%16$s, " +
-                                     "%17$s, " +
-                                     "%18$s" +
-                                     ");",
-                /*1*/                SqlHelper.escapeString(id),
-                /*2*/                topiaVersion,
-                /*3*/                DataDtoEntityContext.timestamp(topiaCreateDate),
-                /*4*/                SqlHelper.escapeString(StringCleaner.ALL.apply(homeId)),
-                /*5*/                catchWeight,
-                /*6*/                weightCategoryRecord.getMinWeight(),
-                /*7*/                weightCategoryRecord.getMaxWeight(),
-                /*8*/                null,
-                /*9*/                getComment(weightCategoryRecord, commentFormat),
-                /*10*/               SqlHelper.escapeString(reasonForDiscardId),
-                /*11*/               SqlHelper.escapeString(weightCategoryRecord.getSpeciesId()),
-                /*12*/               SqlHelper.escapeString(DataSourceMigrationForVersion_9_0.SPECIES_FATE_5),
-                /*13*/               SqlHelper.escapeString(setId),
-                /*14*/               DataDtoEntityContext.timestamp(lastUpdateDate),
-                /*15*/               SqlHelper.escapeString(StringCleaner.ALL.apply(well)),
-                /*16*/               setIdx,
-                /*17*/               SqlHelper.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId),
-                /*18*/               SqlHelper.escapeString(DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_O)
-        );
-    }
-
-    public String getComment(WeightCategoryRecord weightCategoryRecord, Function<String, String> stringFormat) {
-        if (comment == null) {
-            return stringFormat.apply(weightCategoryRecord.toComment());
-        }
-        return stringFormat.apply(comment + "\n" + weightCategoryRecord.toComment());
-    }
-
-}


=====================================
core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/SampleMeasureRecord.java
=====================================
@@ -22,7 +22,9 @@ package fr.ird.observe.spi.migration.v9;
  * #L%
  */
 
+import fr.ird.observe.dto.StringCleaner;
 import fr.ird.observe.persistence.SqlHelper;
+import fr.ird.observe.spi.context.DataDtoEntityContext;
 import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor;
 
 import java.sql.Connection;
@@ -31,7 +33,7 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Timestamp;
 import java.util.Map;
-import java.util.function.Function;
+import java.util.function.BiFunction;
 
 /**
  * Created on 20/11/2022.
@@ -129,17 +131,16 @@ class SampleMeasureRecord {
                          SampleMeasureRecord::toNotDiscardedTargetSpeciesFate);
     }
 
-    static void addSampleMeasure(Connection connection, MigrationVersionResourceExecutor executor, Map<String, String> sampleMapping, String query, Function<SampleMeasureRecord, String> speciesFateSupplier) throws SQLException {
-
+    static void addSampleMeasure(Connection connection,
+                                 MigrationVersionResourceExecutor executor,
+                                 Map<String, String> sampleMapping,
+                                 String query,
+                                 BiFunction<SampleMeasureRecord, String, String> speciesFateSupplier) throws SQLException {
         try (PreparedStatement statement = connection.prepareStatement(query)) {
             try (ResultSet resultSet = statement.executeQuery()) {
                 while (resultSet.next()) {
-                    SampleMeasureRecord sampleMeasure = new SampleMeasureRecord(resultSet);
-                    String sampleId = sampleMapping.get(sampleMeasure.sample);
-                    if (sampleId == null) {
-                        sampleId = sampleMeasure.sample;
-                    }
-                    executor.writeSql(sampleMeasure.toSampleMeasureSql(sampleId, speciesFateSupplier));
+                    SampleMeasureRecord sampleMeasure = new SampleMeasureRecord(resultSet, sampleMapping, speciesFateSupplier);
+                    executor.writeSql(sampleMeasure.toSql());
                 }
             }
         }
@@ -158,7 +159,7 @@ class SampleMeasureRecord {
     int count;
     int acquisitionMode;
     String species;
-    String sample;
+    String sampleId;
     String sex;
     String sizeMeasureType;
     String weightMeasureType;
@@ -168,35 +169,9 @@ class SampleMeasureRecord {
     String lengthMeasureMethod;
     String weightMeasureMethod;
 
-
-    private String toNonTargetSpeciesFate() {
-        if (speciesFate == null) {
-            return null;
-        }
-        if (DataSourceMigrationForVersion_9_0.SPECIES_FATE_6.equals(speciesFate)) {
-            // like for non target catches always change from speciesFate 6 to 15
-            speciesFate = DataSourceMigrationForVersion_9_0.SPECIES_FATE_15;
-        }
-        return speciesFate;
-    }
-
-    private String toDiscardedTargetSpeciesFate() {
-        // Discarded target species use always speciesFate 5 (and we have it from the sql query)
-        return speciesFate;
-
-    }
-
-    private String toNotDiscardedTargetSpeciesFate() {
-        // by default we use in sql query speciesFate = 6
-        // for small species, they are always with speciesFate = 15
-
-        if (!DataSourceMigrationForVersion_9_0.SMALL_SPECIES_TO_SPECIES_FATE_6.contains(species)) {
-            speciesFate = DataSourceMigrationForVersion_9_0.SPECIES_FATE_15;
-        }
-        return speciesFate;
-    }
-
-    public SampleMeasureRecord(ResultSet resultSet) throws SQLException {
+    public SampleMeasureRecord(ResultSet resultSet,
+                               Map<String, String> sampleMapping,
+                               BiFunction<SampleMeasureRecord, String, String> speciesFateSupplier) throws SQLException {
         this.topiaId = resultSet.getString(1);
         this.topiaVersion = resultSet.getLong(2);
         this.topiaCreateDate = resultSet.getTimestamp(3);
@@ -209,13 +184,16 @@ class SampleMeasureRecord {
         this.count = resultSet.getInt(10);
         this.acquisitionMode = resultSet.getInt(11);
         this.species = resultSet.getString(12);
-        this.sample = resultSet.getString(13);
+        String originalSampleId = resultSet.getString(13);
+        String replaceSampleId = sampleMapping.get(originalSampleId);
+        this.sampleId = replaceSampleId == null ? originalSampleId : replaceSampleId;
         this.sex = resultSet.getString(14);
         this.lastUpdateDate = resultSet.getTimestamp(15);
         this.sizeMeasureType = resultSet.getString(16);
         this.weightMeasureType = resultSet.getString(17);
         this.tagNumber = resultSet.getString(18);
-        this.speciesFate = resultSet.getString(19);
+        String originalSpeciesFate = resultSet.getString(19);
+        this.speciesFate = speciesFateSupplier.apply(this, originalSpeciesFate);
         this.sample_idx = resultSet.getInt(20);
         this.lengthMeasureMethod = resultSet.getString(21);
         this.weightMeasureMethod = resultSet.getString(22);
@@ -225,7 +203,7 @@ class SampleMeasureRecord {
         }
     }
 
-    String toSampleMeasureSql(String sampleId, Function<SampleMeasureRecord, String> speciesFateSupplier) {
+    String toSql() {
         return String.format("INSERT INTO ps_observation.SampleMeasure(" +
                                      " topiaId," +
                                      " topiaVersion," +
@@ -250,9 +228,7 @@ class SampleMeasureRecord {
                                      " lengthMeasureMethod," +
                                      " weightMeasureMethod)" +
                                      " VALUES(" +
-                                     "'%s', " +
                                      "%s, " +
-                                     "'%s'::timestamp, " +
                                      "%s, " +
                                      "%s, " +
                                      "%s, " +
@@ -262,9 +238,11 @@ class SampleMeasureRecord {
                                      "%s, " +
                                      "%s, " +
                                      "%s, " +
-                                     "'%s', " +
                                      "%s, " +
-                                     "'%s'::timestamp, " +
+                                     "%s, " +
+                                     "%s, " +
+                                     "%s, " +
+                                     "%s, " +
                                      "%s, " +
                                      "%s, " +
                                      "%s, " +
@@ -273,29 +251,53 @@ class SampleMeasureRecord {
                                      "%s, " +
                                      "%s " +
                                      ");",
-                             topiaId,
+                             SqlHelper.escapeString(topiaId),
                              topiaVersion,
-                             topiaCreateDate,
-                             SqlHelper.escapeString(homeId == null ? null : homeId.replaceAll("'", "")),
+                             DataDtoEntityContext.toTimeStamp(topiaCreateDate),
+                             SqlHelper.escapeString(StringCleaner.ALL.apply(homeId)),
                              length,
                              isLengthComputed,
-                             SqlHelper.escapeString(picturesReferences == null ? null : picturesReferences.replaceAll("'", "")),
+                             SqlHelper.escapeString(StringCleaner.ALL.apply(picturesReferences)),
                              weight,
                              isWeightComputed,
                              count,
                              acquisitionMode,
                              SqlHelper.escapeString(species),
-                             sampleId,
+                             SqlHelper.escapeString(sampleId),
                              SqlHelper.escapeString(sex),
-                             lastUpdateDate,
+                             DataDtoEntityContext.toTimeStamp(lastUpdateDate),
                              SqlHelper.escapeString(sizeMeasureType),
                              SqlHelper.escapeString(weightMeasureType),
                              SqlHelper.escapeString(tagNumber),
-                             SqlHelper.escapeString(speciesFateSupplier.apply(this)),
+                             SqlHelper.escapeString(speciesFate),
                              sample_idx,
                              SqlHelper.escapeString(lengthMeasureMethod),
-                             SqlHelper.escapeString(weightMeasureMethod)
-        );
+                             SqlHelper.escapeString(weightMeasureMethod));
+    }
+
+    private String toNonTargetSpeciesFate(String speciesFate) {
+        if (speciesFate == null) {
+            // keep null value from NonTargetCatch
+            return null;
+        }
+        if (DataSourceMigrationForVersion_9_0.SPECIES_FATE_6.equals(speciesFate)) {
+            // like for non target catches always change from speciesFate 6 to 15
+            speciesFate = DataSourceMigrationForVersion_9_0.SPECIES_FATE_15;
+        }
+        return speciesFate;
+    }
+
+    private String toDiscardedTargetSpeciesFate(String speciesFate) {
+        // Discarded target species use always speciesFate 5 (and we have it from the sql query)
+        return speciesFate;
     }
 
+    private String toNotDiscardedTargetSpeciesFate(String speciesFate) {
+        // by default we use in sql query speciesFate = 6
+        if (!DataSourceMigrationForVersion_9_0.SMALL_SPECIES_TO_SPECIES_FATE_6.contains(species)) {
+            // for small species, they are always with speciesFate = 15
+            speciesFate = DataSourceMigrationForVersion_9_0.SPECIES_FATE_15;
+        }
+        return speciesFate;
+    }
 }


=====================================
core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/SampleRecord.java
=====================================
@@ -24,6 +24,8 @@ package fr.ird.observe.spi.migration.v9;
 
 import fr.ird.observe.dto.StringCleaner;
 import fr.ird.observe.persistence.SqlHelper;
+import fr.ird.observe.spi.context.DataDtoEntityContext;
+import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -31,6 +33,7 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Timestamp;
 import java.util.Map;
+import java.util.TreeMap;
 import java.util.function.Function;
 
 /**
@@ -44,11 +47,26 @@ class SampleRecord {
     final long topiaVersion;
     final Timestamp topiaCreateDate;
     final Timestamp lastUpdateDate;
-    final boolean nonTarget;
     String homeId;
     String comment;
     String set;
 
+    static void processSample(MigrationVersionResourceExecutor executor) {
+        executor.doSqlWork(connection -> {
+            Function<String, String> commentFormat = executor.commentFormat();
+            Map<String, String> sampleMapping = new TreeMap<>();
+            Map<String, SampleRecord> setToSampleMap = new TreeMap<>();
+            SampleRecord.addNonTargetSample(connection, setToSampleMap, sampleMapping);
+            SampleRecord.addTargetSample(connection, setToSampleMap, sampleMapping);
+            for (SampleRecord sample : setToSampleMap.values()) {
+                executor.writeSql(sample.toSql(commentFormat));
+            }
+            SampleMeasureRecord.addNonTargetSampleMeasure(connection, executor, sampleMapping);
+            SampleMeasureRecord.addDiscardedTargetSampleMeasure(connection, executor, sampleMapping);
+            SampleMeasureRecord.addNotDiscardedTargetSampleMeasure(connection, executor, sampleMapping);
+        });
+    }
+
     static void addNonTargetSample(Connection connection, Map<String, SampleRecord> setToSampleMap, Map<String, String> sampleMapping) throws SQLException {
 
         try (PreparedStatement statement = connection.prepareStatement(
@@ -62,7 +80,7 @@ class SampleRecord {
                         "lastUpdateDate " +
                         "FROM ps_observation.nontargetsample " +
                         "ORDER BY set, topiaId")) {
-            addSample(statement, setToSampleMap, sampleMapping, true);
+            addSample(statement, setToSampleMap, sampleMapping);
         }
     }
 
@@ -79,14 +97,14 @@ class SampleRecord {
                         "lastUpdateDate " +
                         "FROM ps_observation.targetsample " +
                         "ORDER BY set, topiaId")) {
-            addSample(statement, setToSampleMap, sampleMapping, false);
+            addSample(statement, setToSampleMap, sampleMapping);
         }
     }
 
-    static void addSample(PreparedStatement statement, Map<String, SampleRecord> setToSampleMap, Map<String, String> sampleMapping, boolean nonTarget) throws SQLException {
+    static void addSample(PreparedStatement statement, Map<String, SampleRecord> setToSampleMap, Map<String, String> sampleMapping) throws SQLException {
         try (ResultSet resultSet = statement.executeQuery()) {
             while (resultSet.next()) {
-                SampleRecord sample = new SampleRecord(resultSet, nonTarget);
+                SampleRecord sample = new SampleRecord(resultSet);
                 SampleRecord existingSample = setToSampleMap.get(sample.set);
                 if (existingSample == null) {
                     // new sample
@@ -103,7 +121,7 @@ class SampleRecord {
         }
     }
 
-    public SampleRecord(ResultSet resultSet, boolean nonTarget) throws SQLException {
+    SampleRecord(ResultSet resultSet) throws SQLException {
         this.topiaId = resultSet.getString(1);
         this.topiaVersion = resultSet.getLong(2);
         this.topiaCreateDate = resultSet.getTimestamp(3);
@@ -111,18 +129,17 @@ class SampleRecord {
         this.comment = resultSet.getString(5);
         this.set = resultSet.getString(6);
         this.lastUpdateDate = resultSet.getTimestamp(7);
-        this.nonTarget = nonTarget;
     }
 
-    public String toSampleSql(Function<String, String> commentFormat) {
-        return String.format("INSERT INTO ps_observation.Sample(topiaId, topiaVersion, topiaCreateDate, homeId, comment, set, lastUpdateDate) VALUES('%s', %s, '%s'::timestamp, %s, %s, '%s', '%s'::timestamp);",
-                             topiaId,
+    public String toSql(Function<String, String> commentFormat) {
+        return String.format("INSERT INTO ps_observation.Sample(topiaId, topiaVersion, topiaCreateDate, homeId, comment, set, lastUpdateDate) VALUES(%s, %s, %s, %s, %s, %s, %s);",
+                             SqlHelper.escapeString(topiaId),
                              topiaVersion,
-                             topiaCreateDate,
+                             DataDtoEntityContext.toTimeStamp(topiaCreateDate),
                              SqlHelper.escapeString(StringCleaner.ALL.apply(homeId)),
                              commentFormat.apply(comment),
-                             set,
-                             lastUpdateDate);
+                             SqlHelper.escapeString(set),
+                             DataDtoEntityContext.toTimeStamp(lastUpdateDate));
     }
 
     public void addHomeId(String homeId) {


=====================================
core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/NotDiscardedTargetCatchRecord.java → core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/TargetCatchRecord.java
=====================================
@@ -25,6 +25,7 @@ package fr.ird.observe.spi.migration.v9;
 import fr.ird.observe.dto.StringCleaner;
 import fr.ird.observe.persistence.SqlHelper;
 import fr.ird.observe.spi.context.DataDtoEntityContext;
+import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -32,6 +33,8 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Timestamp;
 import java.util.Map;
+import java.util.Set;
+import java.util.TreeSet;
 import java.util.function.Function;
 
 /**
@@ -40,25 +43,92 @@ import java.util.function.Function;
  * @author Tony Chemit - dev@tchemit.fr
  * @since 9.0.17
  */
-class NotDiscardedTargetCatchRecord {
+public class TargetCatchRecord {
 
-    public static PreparedStatement prepareStatement(Connection connection) throws SQLException {
-        return connection.prepareStatement("SELECT" +
-                                                   /* 01 */    " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
-                                                   /* 02 */    " tc.topiaVersion + 1," +
-                                                   /* 03 */    " tc.topiaCreateDate," +
-                                                   /* 04 */    " tc.homeId," +
-                                                   /* 05 */    " tc.catchWeight," +
-                                                   /* 06 */    " tc.weightCategory," +
-                                                   /* 07 */    " tc.comment," +
-                                                   /* 08 */    " tc.set," +
-                                                   /* 09 */    " tc.lastUpdateDate," +
-                                                   /* 10 */    " tc.well," +
-                                                   /* 11 */    " -tc.set_idx," +
-                                                   /* 12 */    " tc.weightMeasureMethod," +
-                                                   /* 13 */    " s.targetcatchcompositionestimatedbyobserver " +
-                                                   " FROM ps_observation.TargetCatch tc INNER JOIN ps_observation.set s on s.topiaId = tc.set" +
-                                                   " WHERE NOT tc.discarded AND NOT ( weightCategory IS NULL AND catchweight IS NULL AND well IS NULL AND broughtondeck IS NULL AND reasonfordiscard IS NULL )");
+    static final String DISCARDED_SQL = "SELECT" +
+            /* 01 */    " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
+            /* 02 */    " tc.topiaVersion + 1," +
+            /* 03 */    " tc.topiaCreateDate," +
+            /* 04 */    " tc.homeId," +
+            /* 05 */    " tc.catchWeight," +
+            /* 06 */    " tc.weightCategory," +
+            /* 07 */    " tc.comment," +
+            /* 08 */    " tc.set," +
+            /* 09 */    " tc.lastUpdateDate," +
+            /* 10 */    " tc.well," +
+            /* 11 */    " -tc.set_idx," +
+            /* 12 */    " tc.weightMeasureMethod," +
+            /* 13 */    " tc.reasonForDiscard" +
+            " FROM ps_observation.TargetCatch tc" +
+            " WHERE tc.discarded AND NOT ( tc.weightCategory IS NULL AND tc.catchWeight IS NULL AND tc.well IS NULL AND tc.broughtOnDeck IS NULL AND tc.reasonForDiscard IS NULL )";
+
+    static final String NOT_DISCARDED_SQL = "SELECT" +
+            /* 01 */    " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
+            /* 02 */    " tc.topiaVersion + 1," +
+            /* 03 */    " tc.topiaCreateDate," +
+            /* 04 */    " tc.homeId," +
+            /* 05 */    " tc.catchWeight," +
+            /* 06 */    " tc.weightCategory," +
+            /* 07 */    " tc.comment," +
+            /* 08 */    " tc.set," +
+            /* 09 */    " tc.lastUpdateDate," +
+            /* 10 */    " tc.well," +
+            /* 11 */    " -tc.set_idx," +
+            /* 12 */    " tc.weightMeasureMethod," +
+            /* 13 */    " s.targetCatchCompositionEstimatedByObserver " +
+            " FROM ps_observation.TargetCatch tc INNER JOIN ps_observation.set s on s.topiaId = tc.set" +
+            " WHERE NOT tc.discarded AND NOT ( tc.weightCategory IS NULL AND tc.catchWeight IS NULL AND tc.well IS NULL AND tc.broughtOnDeck IS NULL AND tc.reasonForDiscard IS NULL )";
+
+    public static void processTargetCatches(MigrationVersionResourceExecutor executor) {
+        // Load weight category records indexed by their id
+        Map<String, WeightCategoryRecord> weightCategoryRecordById = WeightCategoryRecord.loadWeightCategoriesMap(executor);
+        // To track target catches with no weight category
+        Set<String> catchesWithNullWeightCategory = new TreeSet<>();
+        executor.doSqlWork(connection -> {
+            // process discarded values
+            processTargetCatches(connection, executor, weightCategoryRecordById, catchesWithNullWeightCategory, true);
+            // process not discarded values
+            processTargetCatches(connection, executor, weightCategoryRecordById, catchesWithNullWeightCategory, false);
+        });
+        // can't perform migration, we found some target catches with no weight category
+        if (!catchesWithNullWeightCategory.isEmpty()) {
+            StringBuilder error = new StringBuilder();
+            error.append(String.format("\nFound %d target catches with null category:", catchesWithNullWeightCategory.size()));
+            for (String id : catchesWithNullWeightCategory) {
+                error.append("\n\t").append(id);
+            }
+            throw new IllegalStateException(error.toString());
+        }
+    }
+
+    static void processTargetCatches(Connection connection,
+                                     MigrationVersionResourceExecutor executor,
+                                     Map<String, WeightCategoryRecord> weightCategoryRecordById,
+                                     Set<String> catchesWithNullWeightCategory,
+                                     boolean discarded) throws SQLException {
+        Function<String, String> commentFormat = executor.commentFormat();
+        // to skip writing sql to executor as soon as a record was found with no weight category
+        // since this case will stop migration
+        boolean skipWriteSql = !catchesWithNullWeightCategory.isEmpty();
+        try (PreparedStatement statement = connection.prepareStatement(discarded ? DISCARDED_SQL : NOT_DISCARDED_SQL)) {
+            try (ResultSet resultSet = statement.executeQuery()) {
+                while (resultSet.next()) {
+                    TargetCatchRecord record = new TargetCatchRecord(resultSet, weightCategoryRecordById, discarded);
+                    if (record.isWeightCategoryNull()) {
+                        // can't process a such record,
+                        // just keep id of target catch for migration failure exception message
+                        catchesWithNullWeightCategory.add(record.getId());
+                        // no more need to add sql to executor
+                        skipWriteSql = true;
+                        continue;
+                    }
+                    if (skipWriteSql) {
+                        continue;
+                    }
+                    executor.writeSql(record.toSql(commentFormat));
+                }
+            }
+        }
     }
 
     private final String id;
@@ -66,36 +136,45 @@ class NotDiscardedTargetCatchRecord {
     private final Timestamp topiaCreateDate;
     private final String homeId;
     private final Float catchWeight;
-    private final String weightCategoryId;
+    private final WeightCategoryRecord weightCategory;
     private final String comment;
+    private final String reasonForDiscardId;
     private final String setId;
     private final Timestamp lastUpdateDate;
     private final String well;
     private final long setIdx;
     private final String weightMeasureMethodId;
-    private final boolean targetCatchCompositionEstimatedByObserver;
+    private final String speciesFateId;
+    private final String informationSourceId;
 
-    public NotDiscardedTargetCatchRecord(ResultSet resultSet) throws SQLException {
+    public TargetCatchRecord(ResultSet resultSet, Map<String, WeightCategoryRecord> weightCategoryRecordById, boolean discarded) throws SQLException {
         this.id = resultSet.getString(1);
         this.topiaVersion = resultSet.getLong(2);
         this.topiaCreateDate = resultSet.getTimestamp(3);
         this.homeId = resultSet.getString(4);
         this.catchWeight = resultSet.getFloat(5);
-        this.weightCategoryId = resultSet.getString(6);
+        String weightCategoryId = resultSet.getString(6);
+        this.weightCategory = weightCategoryId == null ? null : weightCategoryRecordById.get(weightCategoryId);
         this.comment = resultSet.getString(7);
         this.setId = resultSet.getString(8);
         this.lastUpdateDate = resultSet.getTimestamp(9);
         this.well = resultSet.getString(10);
         this.setIdx = resultSet.getLong(11);
-        this.weightMeasureMethodId = resultSet.getString(12);
-        this.targetCatchCompositionEstimatedByObserver = resultSet.getBoolean(13);
+        String weightMeasureMethodId = resultSet.getString(12);
+        this.weightMeasureMethodId = weightMeasureMethodId == null ? DataSourceMigrationForVersion_9_0.DEFAULT_WEIGHT_MEASURE_METHOD : weightMeasureMethodId;
+        if (discarded) {
+            this.reasonForDiscardId = resultSet.getString(13);
+            this.speciesFateId = DataSourceMigrationForVersion_9_0.SPECIES_FATE_5;
+            this.informationSourceId = DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_O;
+        } else {
+            this.reasonForDiscardId = null;
+            this.speciesFateId = weightCategory != null && "10".equals(weightCategory.getCode()) ? DataSourceMigrationForVersion_9_0.SPECIES_FATE_15 : DataSourceMigrationForVersion_9_0.SPECIES_FATE_6;
+            boolean targetCatchCompositionEstimatedByObserver = resultSet.getBoolean(13);
+            this.informationSourceId = targetCatchCompositionEstimatedByObserver ? DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_P : DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_U;
+        }
     }
 
-    public String toCatchSql(Map<String, WeightCategoryRecord> weightCategoryRecordById, Function<String, String> commentFormat) {
-        WeightCategoryRecord weightCategoryRecord = weightCategoryRecordById.get(weightCategoryId);
-        if (weightCategoryRecord == null) {
-            throw new IllegalStateException(String.format("Could not find weightCategory with id: %s", weightCategoryId));
-        }
+    public String toSql(Function<String, String> commentFormat) {
         return String.format("INSERT INTO ps_observation.Catch(" +
                                      /*1*/                  "topiaId, " +
                                      /*2*/                  "topiaVersion, " +
@@ -117,7 +196,7 @@ class NotDiscardedTargetCatchRecord {
                                      /*18*/                 "informationSource) VALUES ( " +
                                      "%1$s, " +
                                      "%2$s, " +
-                                     "'%3$s'::timestamp, " +
+                                     "%3$s, " +
                                      "%4$s, " +
                                      "%5$s, " +
                                      "%6$s, " +
@@ -128,29 +207,29 @@ class NotDiscardedTargetCatchRecord {
                                      "%11$s, " +
                                      "%12$s, " +
                                      "%13$s, " +
-                                     "'%14$s'::timestamp, " +
+                                     "%14$s, " +
                                      "%15$s, " +
                                      "%16$s, " +
                                      "%17$s, " +
                                      "%18$s);",
                 /*1*/          SqlHelper.escapeString(id),
                 /*2*/          topiaVersion,
-                /*3*/          DataDtoEntityContext.timestamp(topiaCreateDate),
+                /*3*/          DataDtoEntityContext.toTimeStamp(topiaCreateDate),
                 /*4*/          SqlHelper.escapeString(StringCleaner.ALL.apply(homeId)),
                 /*5*/          catchWeight,
-                /*6*/          weightCategoryRecord.getMinWeight(),
-                /*7*/          weightCategoryRecord.getMaxWeight(),
+                /*6*/          weightCategory.getMinWeight(),
+                /*7*/          weightCategory.getMaxWeight(),
                 /*8*/          null,
-                /*9*/          getComment(weightCategoryRecord, commentFormat),
-                /*10*/         null,
-                /*11*/         SqlHelper.escapeString(weightCategoryRecord.getSpeciesId()),
-                /*12*/         SqlHelper.escapeString("10".equals(weightCategoryRecord.getCode()) ? DataSourceMigrationForVersion_9_0.SPECIES_FATE_15 : DataSourceMigrationForVersion_9_0.SPECIES_FATE_6),
+                /*9*/          getComment(weightCategory, commentFormat),
+                /*10*/         SqlHelper.escapeString(reasonForDiscardId),
+                /*11*/         SqlHelper.escapeString(weightCategory.getSpeciesId()),
+                /*12*/         SqlHelper.escapeString(speciesFateId),
                 /*13*/         SqlHelper.escapeString(setId),
-                /*14*/         DataDtoEntityContext.timestamp(lastUpdateDate),
+                /*14*/         DataDtoEntityContext.toTimeStamp(lastUpdateDate),
                 /*15*/         SqlHelper.escapeString(StringCleaner.ALL.apply(well)),
                 /*16*/         setIdx,
-                /*17*/         SqlHelper.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId),
-                /*18*/         SqlHelper.escapeString(targetCatchCompositionEstimatedByObserver ? DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_P : DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_U)
+                /*17*/         SqlHelper.escapeString(weightMeasureMethodId),
+                /*18*/         SqlHelper.escapeString(informationSourceId)
         );
     }
 
@@ -160,4 +239,12 @@ class NotDiscardedTargetCatchRecord {
         }
         return stringFormat.apply(comment + "\n" + weightCategoryRecord.toComment());
     }
+
+    public String getId() {
+        return id;
+    }
+
+    public boolean isWeightCategoryNull() {
+        return weightCategory == null;
+    }
 }


=====================================
core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/WeightCategoryRecord.java
=====================================
@@ -30,6 +30,8 @@ import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExec
 
 import java.util.Map;
 import java.util.Set;
+import java.util.TreeMap;
+import java.util.TreeSet;
 
 /**
  * Created on 06/11/2022.
@@ -43,162 +45,208 @@ public class WeightCategoryRecord {
     private final String code;
     private final String speciesId;
     private final String label1;
+    private final String speciesFaoCode;
+    private final String speciesLabel1;
     private Float minWeight;
     private Float maxWeight;
 
     public static Map<String, WeightCategoryRecord> loadWeightCategoriesMap(MigrationVersionResourceExecutor executor) {
-        Set<WeightCategoryRecord> weightCategories = executor.findMultipleResultAstSet(SqlQuery.wrap("SELECT topiaId, code, species, label1 FROM ps_observation.WeightCategory", resultSet -> new WeightCategoryRecord(
-                resultSet.getString(1),
-                resultSet.getString(2),
-                resultSet.getString(3),
-                resultSet.getString(4))));
-        Map<String, WeightCategoryRecord> map = Maps.uniqueIndex(weightCategories, WeightCategoryRecord::getId);
+        Set<WeightCategoryRecord> weightCategories = executor.findMultipleResultAstSet(SqlQuery.wrap(
+                "SELECT wc.topiaId, wc.code, wc.species, wc.label1, s.faoCode, s.label1" +
+                        " FROM ps_observation.WeightCategory wc" +
+                        " INNER JOIN common.species s on wc.species = s.topiaId", resultSet -> new WeightCategoryRecord(
+                        resultSet.getString(1),
+                        resultSet.getString(2),
+                        resultSet.getString(3),
+                        resultSet.getString(4),
+                        resultSet.getString(5),
+                        resultSet.getString(6)
+                )));
+        // all weight categories found in database
+        // while building safe map, this map will be reduced to weight categories that are not in the safe list
+        Map<String, WeightCategoryRecord> toFixMap = new TreeMap<>(Maps.uniqueIndex(weightCategories, WeightCategoryRecord::getId));
+        // safe weight categories we can use in migration (we need to get all of them, otherwise let's throw an error)
+        Map<String, WeightCategoryRecord> safeMap = new TreeMap<>();
+        // missing weight categories (from the safe list)
+        Set<String> missingIds = new TreeSet<>();
 
         // load weight categories extra fields (minWeight-maxWeight)
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685804#0.034641865339321565", 0f, 3f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685810#0.9054833697032716", 3f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685818#0.5146901232402814", 11f, 30f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685824#0.30572839692068965", 3f, 30f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685830#0.3505424853460628", 31f, 50f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685837#0.9977145363359975", 11f, 50f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265900731495#0.02540873055336068", 50f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685843#0.5227248075951917", 10f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685849#0.25030425638120324", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336721513613#0.1603994688043372", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1389775941412#0.15834335814453693", 30f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1389776343228#0.7378693597245063", 30f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1424937125831#0.9928551215821171", 0f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685747#0.8903018444376307", 0f, 3f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685754#0.10338259022397256", 3f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685760#0.5190060347627616", 11f, 30f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685767#0.17985459818605176", 3f, 30f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685773#0.36018870624305044", 31f, 50f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685779#0.6459697632929102", 11f, 50f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685785#0.017557737970281928", 50f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685791#0.96882088900269", 10f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685798#0.8205778575049493", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336721546508#0.9537814356455492", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1389776259896#0.07638719997162424", 30f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1424937065301#0.2847458239357389", 0f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1447659295872#0.5394619880622312", 0f, 3.5f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1447659325662#0.768248108810874", 3.5f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336722270972#0.37652846873357493", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.6003570416302645", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336722322572#0.3938987624410807", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685856#0.37681463866680454", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685862#0.3577261083569382", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685869#0.1577912646543379", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685876#0.571764466269097", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685882#0.6060344598115799", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685888#0.04548287749163349", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685894#0.7596812507048669", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685901#0.9401665182005969", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685907#0.703864325385401", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336740964786#0.9038903638109549", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771681957#0.9874591590552072", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771701326#0.4010839902789137", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771715715#0.7853847204136084", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771729301#0.4884568851061948", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771742931#0.7051564476067321", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771756464#0.45050535009150927", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771767261#0.6841254148640502", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602771778122#0.16162153365230103", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602770129319#0.19516930145898193", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1602770254324#0.846496629553337", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685913#0.6908151762213386", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685919#0.32661937164161003", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685926#0.26263537384431435", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685932#0.05603149764956994", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685938#0.1289482411270122", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685944#0.6386905101076812", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685950#0.5552574879416816", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685956#0.8040582347597208", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685962#0.5585432399656218", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741018010#0.03331113748771242", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922010990#0.3754603442395006", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922245707#0.40050172552023944", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922280084#0.6622140480197926", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922599555#0.7704492147942078", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922687314#0.6979902619400133", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922752777#0.47792415661458676", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922804724#0.4624943876905273", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303922867136#0.4287768516967707", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.2988865794350679", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741045619#0.26215173370303935", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925079863#0.4512897233754356", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925079863#0.5772066302124889", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925079863#0.5076973842921164", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925079864#0.15355738476896108", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925590606#0.2425119417078675", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925590606#0.8917988680730413", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925716959#0.9994063870345968", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303925716959#0.07903343358670578", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.6346372733268454", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741085150#0.8502977815118109", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.6536295925217674", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741106738#0.8017167831584734", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685968#0.8743676757879854", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685975#0.9269185705341096", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685981#0.20973946035734992", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685987#0.8124900788304172", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685993#0.7110149379733451", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685999#0.35760983661297885", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832686006#0.27918502949209356", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832686012#0.3678254910247588", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685610#0.04119062512809801", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741128034#0.08367392559943787", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265900836849#0.18015633272140852", 0f, 1.8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265900836849#0.9036635253340434", 1.8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685673#0.3163596616269717", 1.8f, 4f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685679#0.019733673419385367", 1.8f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685685#0.7315998797178913", 4f, 6f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685692#0.5940916998227946", 4f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685722#0.9714869893887224", 6f, 8f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685729#0.0015801959709714763", 8f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685741#0.9084571608340902", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741168338#0.14273986795446758", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1447659383213#0.4400294468298005", 0f, 3.5f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1447659418223#0.8121977439460182", 3.5f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1555434122029#0.33046872986365916", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1555434233562#0.6221497586001838", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1563970176195#0.042949635286452015", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1563970198102#0.5406480296062075", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685499#0.45137386216494235", 0f, 3f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1239832685505#0.6893474228813191", 3f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265900972942#0.14809205088390143", 11f, 30f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265900972963#0.6915765595336695", 3f, 30f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265901072470#0.48282507736865", 31f, 50f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265901072470#0.049555168356280266", 11f, 50f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265901072470#0.3502049636413197", 50f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265901225547#0.7474093737982056", 10f, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1265901225548#0.3886087214402537", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741209227#0.14815475082140694", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1424936892080#0.3071247026973247", 0f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1447659237402#0.5659786750175115", 0f, 3.5f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1447658978491#0.015566209814640963", 3.5f, 10f);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741235125#0.9596181329735903", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1303924814809#0.6964774216650054", null, null);
-        addWeightCategoryMinMaxWeight(map, "fr.ird.referential.ps.observation.WeightCategory#1336741257450#0.021228233536910834", null, null);
-        return map;
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685804#0.034641865339321565", 0f, 3f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685810#0.9054833697032716", 3f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685818#0.5146901232402814", 11f, 30f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685824#0.30572839692068965", 3f, 30f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685830#0.3505424853460628", 31f, 50f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685837#0.9977145363359975", 11f, 50f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265900731495#0.02540873055336068", 50f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685843#0.5227248075951917", 10f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685849#0.25030425638120324", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336721513613#0.1603994688043372", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1389775941412#0.15834335814453693", 30f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1389776343228#0.7378693597245063", 30f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1424937125831#0.9928551215821171", 0f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685747#0.8903018444376307", 0f, 3f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685754#0.10338259022397256", 3f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685760#0.5190060347627616", 11f, 30f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685767#0.17985459818605176", 3f, 30f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685773#0.36018870624305044", 31f, 50f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685779#0.6459697632929102", 11f, 50f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685785#0.017557737970281928", 50f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685791#0.96882088900269", 10f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685798#0.8205778575049493", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336721546508#0.9537814356455492", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1389776259896#0.07638719997162424", 30f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1424937065301#0.2847458239357389", 0f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1447659295872#0.5394619880622312", 0f, 3.5f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1447659325662#0.768248108810874", 3.5f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336722270972#0.37652846873357493", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.6003570416302645", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336722322572#0.3938987624410807", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685856#0.37681463866680454", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685862#0.3577261083569382", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685869#0.1577912646543379", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685876#0.571764466269097", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685882#0.6060344598115799", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685888#0.04548287749163349", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685894#0.7596812507048669", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685901#0.9401665182005969", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685907#0.703864325385401", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336740964786#0.9038903638109549", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771681957#0.9874591590552072", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771701326#0.4010839902789137", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771715715#0.7853847204136084", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771729301#0.4884568851061948", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771742931#0.7051564476067321", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771756464#0.45050535009150927", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771767261#0.6841254148640502", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602771778122#0.16162153365230103", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602770129319#0.19516930145898193", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1602770254324#0.846496629553337", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685913#0.6908151762213386", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685919#0.32661937164161003", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685926#0.26263537384431435", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685932#0.05603149764956994", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685938#0.1289482411270122", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685944#0.6386905101076812", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685950#0.5552574879416816", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685956#0.8040582347597208", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685962#0.5585432399656218", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741018010#0.03331113748771242", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922010990#0.3754603442395006", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922245707#0.40050172552023944", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922280084#0.6622140480197926", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922599555#0.7704492147942078", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922687314#0.6979902619400133", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922752777#0.47792415661458676", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922804724#0.4624943876905273", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303922867136#0.4287768516967707", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.2988865794350679", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741045619#0.26215173370303935", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925079863#0.4512897233754356", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925079863#0.5772066302124889", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925079863#0.5076973842921164", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925079864#0.15355738476896108", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925590606#0.2425119417078675", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925590606#0.8917988680730413", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925716959#0.9994063870345968", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303925716959#0.07903343358670578", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.6346372733268454", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741085150#0.8502977815118109", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1285247232470#0.6536295925217674", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741106738#0.8017167831584734", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685968#0.8743676757879854", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685975#0.9269185705341096", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685981#0.20973946035734992", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685987#0.8124900788304172", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685993#0.7110149379733451", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685999#0.35760983661297885", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832686006#0.27918502949209356", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832686012#0.3678254910247588", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685610#0.04119062512809801", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741128034#0.08367392559943787", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265900836849#0.18015633272140852", 0f, 1.8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265900836849#0.9036635253340434", 1.8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685673#0.3163596616269717", 1.8f, 4f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685679#0.019733673419385367", 1.8f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685685#0.7315998797178913", 4f, 6f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685692#0.5940916998227946", 4f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685722#0.9714869893887224", 6f, 8f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685729#0.0015801959709714763", 8f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685741#0.9084571608340902", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741168338#0.14273986795446758", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1447659383213#0.4400294468298005", 0f, 3.5f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1447659418223#0.8121977439460182", 3.5f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1555434122029#0.33046872986365916", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1555434233562#0.6221497586001838", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1563970176195#0.042949635286452015", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1563970198102#0.5406480296062075", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685499#0.45137386216494235", 0f, 3f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1239832685505#0.6893474228813191", 3f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265900972942#0.14809205088390143", 11f, 30f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265900972963#0.6915765595336695", 3f, 30f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265901072470#0.48282507736865", 31f, 50f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265901072470#0.049555168356280266", 11f, 50f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265901072470#0.3502049636413197", 50f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265901225547#0.7474093737982056", 10f, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1265901225548#0.3886087214402537", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741209227#0.14815475082140694", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1424936892080#0.3071247026973247", 0f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1447659237402#0.5659786750175115", 0f, 3.5f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1447658978491#0.015566209814640963", 3.5f, 10f);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741235125#0.9596181329735903", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1303924814809#0.6964774216650054", null, null);
+        addWeightCategoryMinMaxWeight(toFixMap, safeMap, missingIds, "fr.ird.referential.ps.observation.WeightCategory#1336741257450#0.021228233536910834", null, null);
+        if (!missingIds.isEmpty()) {
+            StringBuilder error = new StringBuilder();
+            error.append(String.format("\nDetects %d official weight categories not found in current database:\n\t", missingIds.size()));
+            error.append(String.join("\n\t", missingIds));
+            log.warn(error);
+        }
+        if (toFixMap.isEmpty()) {
+            // everything is ok, we can use the safe map
+            return safeMap;
+        }
+        StringBuilder error = new StringBuilder(String.format("\nDetects %d weight categories in current database, that should be replaced by official one:", toFixMap.size()));
+        for (WeightCategoryRecord value : toFixMap.values()) {
+            error.append("\n").append(value.toString());
+        }
+        throw new IllegalStateException(error.toString());
     }
 
     private static final Logger log = LogManager.getLogger(WeightCategoryRecord.class);
 
-    private static void addWeightCategoryMinMaxWeight(Map<String, WeightCategoryRecord> map, String id, Float minWeight, Float maxWeight) {
-        WeightCategoryRecord weightCategoryRecord = map.get(id);
+    private static void addWeightCategoryMinMaxWeight(Map<String, WeightCategoryRecord> existingMap,
+                                                      Map<String, WeightCategoryRecord> safeMap,
+                                                      Set<String> missingIds,
+                                                      String id,
+                                                      Float minWeight,
+                                                      Float maxWeight) {
+        WeightCategoryRecord weightCategoryRecord = existingMap.get(id);
         if (weightCategoryRecord == null) {
-            log.error(String.format("Could not find in database weightCategory: %s", id));
+            missingIds.add(id);
             return;
         }
+        // remove it from existing map (since this weight category is ok)
+        existingMap.remove(id);
+        // add it to safe map
+        safeMap.put(id, weightCategoryRecord);
+        // add min weight
         weightCategoryRecord.setMinWeight(minWeight);
+        // add max weight
         weightCategoryRecord.setMaxWeight(maxWeight);
     }
 
-    WeightCategoryRecord(String id, String code, String speciesId, String label1) {
+    WeightCategoryRecord(String id,
+                         String code,
+                         String speciesId,
+                         String label1,
+                         String speciesFaoCode,
+                         String speciesLabel1) {
         this.id = id;
         this.code = code;
         this.speciesId = speciesId;
         this.label1 = label1;
+        this.speciesFaoCode = speciesFaoCode;
+        this.speciesLabel1 = speciesLabel1;
     }
 
     public void setMinWeight(Float minWeight) {
@@ -237,6 +285,11 @@ public class WeightCategoryRecord {
         return String.format(COMMENT_PATTERN, getCode(), getLabel1());
     }
 
+    @Override
+    public String toString() {
+        return String.format("{id='%s', code='%s', label1='%s', speciesId='%s', speciesFaoCode='%s', speciesLabel1='%s'}", id, code, label1, speciesId, speciesFaoCode, speciesLabel1);
+    }
+
     public static final String COMMENT_PATTERN = "<weightcategory>\n" +
             "   <code>%1$s</code>\n" +
             "   <label1>%2$s</label1>\n" +


=====================================
core/persistence/resources/src/main/resources/db/migration/v8/8.1/99_fix_not_null_constraints-common.sql
=====================================
@@ -44,6 +44,11 @@ DELETE FROM ps_observation.ObjectObservedSpecies WHERE species IS NULL AND count
 DELETE FROM ps_observation.ObjectSchoolEstimate WHERE species IS NULL AND totalWeight IS NULL;
 DELETE FROM ll_observation.BranchlinesComposition WHERE topType IS NULL AND tracelineType IS NULL AND length IS NULL;
 DELETE FROM ll_observation.SizeMeasure WHERE size IS NULL;
+DELETE FROM ps_observation.nonTargetCatch WHERE species IS NULL AND catchweight IS NULL AND meanWeight IS NULL AND totalCount IS NULL AND reasonfordiscard IS NULL AND well IS NULL;
+DELETE FROM ps_common.GearUseFeatures WHERE gear IS NULL AND number IS NULL AND usedintrip IS NULL;
+DELETE FROM ps_common.GearUseFeaturesMeasurement WHERE measurementvalue IS NULL;
+DELETE FROM ll_common.GearUseFeatures WHERE gear IS NULL AND number IS NULL AND usedintrip IS NULL;
+DELETE FROM ll_common.GearUseFeaturesMeasurement WHERE measurementvalue IS NULL;
 
 UPDATE ps_observation.NonTargetLength SET lastUpdateDate = ${CURRENT_TIMESTAMP}, topiaVersion = topiaVersion + 1, species = 'fr.ird.referential.common.Species#1433499266610#0.696541526820511' WHERE species IS NULL;
 UPDATE ps_observation.NonTargetLength SET lastUpdateDate = ${CURRENT_TIMESTAMP}, topiaVersion = topiaVersion + 1, count = 1 WHERE count IS NULL AND species IS NOT NULL;


=====================================
toolkit/persistence/src/main/java/fr/ird/observe/spi/context/DataDtoEntityContext.java
=====================================
@@ -78,6 +78,10 @@ public abstract class DataDtoEntityContext<
         return String.format("%1$tF %1$tT", date);
     }
 
+    public static String toTimeStamp(Date time) {
+        return time == null ? null : SqlHelper.escapeString(DataDtoEntityContext.timestamp(time)) + "::timestamp";
+    }
+
     public static String toId(Entity entity) {
         return entity == null ? "NULL" : SqlHelper.escapeString(entity.getTopiaId());
     }



View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/compare/8bde4b6814c870048356a488b...

-- 
View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/compare/8bde4b6814c870048356a488b...
You're receiving this email because of your account on gitlab.com.

    

[Observe-commits] [Git][ultreiaio/ird-observe][develop-9.0.x] 5 commits: Persistence migration - Remove more ghost rows (coming from empty lines in a Table form)

Tony CHEMIT (＠tchemit)