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.