Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: 7ea06d73 by Tony Chemit at 2022-11-12T14:43:10+01:00 [UI REFERENTIELS] Envisager un auto-trim droite et gauche sur les champs alphanumériques - See #1544 Nettoyage par migration de tous les champs texte - Closes #2529 - - - - - 8f5e68fb by Tony Chemit at 2022-11-12T16:10:50+01:00 Test fixtures - [UI REFERENTIELS] Envisager un auto-trim droite et gauche sur les champs alphanumériques - See #1544 - - - - - c7278943 by Tony Chemit at 2022-11-12T16:10:50+01:00 Test fixtures - Renommage speciesFate - - - - - 247c8c3d by Tony Chemit at 2022-11-12T16:10:50+01:00 SetCatch - MEttre minWeight et maxWeight sur la meme ligne du formulaire - - - - - 20 changed files: - client/datasource/editor/ps/src/main/i18n/getters/jaxx.getter - client/datasource/editor/ps/src/main/java/fr/ird/observe/client/datasource/editor/ps/data/observation/SetCatchUI.jaxx - core/api/services/src/main/i18n/getters/labels.getter - core/persistence/java/src/main/java/fr/ird/observe/entities/data/ps/common/TripSpi.java - core/persistence/resources/src/main/java/fr/ird/observe/persistence/avdth/data/DataWriter.java - 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/FixCommentHelper.java - + core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/FixStringHelper.java - core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/NotDiscardedTargetCatchRecord.java - core/services/i18n/src/main/i18n/translations/services_en_GB.properties - core/services/i18n/src/main/i18n/translations/services_es_ES.properties - core/services/i18n/src/main/i18n/translations/services_fr_FR.properties - core/services/test/src/main/resources/fixtures/fr/ird/observe/services/service/ValidateService-dataResult.json - core/services/test/src/main/resources/fixtures/fr/ird/observe/services/service/ValidateService-referentialResult.json - model/src/main/models/Observe/dto/class/i18nLabels.properties - model/src/main/models/Observe/dto/class/i18nOverrideLabels.properties - + toolkit/api/src/main/java/fr/ird/observe/dto/StringCleaner.java - toolkit/api/src/main/java/fr/ird/observe/persistence/SqlHelper.java - toolkit/persistence/src/main/java/fr/ird/observe/spi/context/DataDtoEntityContext.java Changes: ===================================== client/datasource/editor/ps/src/main/i18n/getters/jaxx.getter ===================================== @@ -190,6 +190,7 @@ observe.data.ps.observation.Catch.catchWeight observe.data.ps.observation.Catch.maxWeight observe.data.ps.observation.Catch.meanLength observe.data.ps.observation.Catch.meanWeight +observe.data.ps.observation.Catch.minMaxWeight observe.data.ps.observation.Catch.minWeight observe.data.ps.observation.Catch.reasonForDiscard observe.data.ps.observation.Catch.totalCount ===================================== client/datasource/editor/ps/src/main/java/fr/ird/observe/client/datasource/editor/ps/data/observation/SetCatchUI.jaxx ===================================== @@ -125,10 +125,13 @@ </row> <row> <cell> - <JLabel id='minWeightLabel'/> + <JLabel id='minMaxWeightLabel'/> </cell> <cell weightx='1'> - <NumberEditor id='minWeight' styleClass="float2"/> + <JPanel layout="{new GridLayout()}"> + <NumberEditor id='minWeight' styleClass="float2"/> + <NumberEditor id='maxWeight' styleClass="float2"/> + </JPanel> </cell> </row> <row> @@ -139,15 +142,6 @@ <NumberEditor id='meanWeight' styleClass="float2"/> </cell> </row> - <row> - <cell> - <JLabel id='maxWeightLabel'/> - </cell> - <cell weightx='1'> - <NumberEditor id='maxWeight' styleClass="float2"/> - </cell> - </row> - <!-- taille moyenne --> <row> <cell> ===================================== core/api/services/src/main/i18n/getters/labels.getter ===================================== @@ -1075,6 +1075,7 @@ observe.data.ps.observation.Catch.meanWeight observe.data.ps.observation.Catch.meanWeight.short observe.data.ps.observation.Catch.meanWeightComputed.computed.tip observe.data.ps.observation.Catch.meanWeightComputed.observed.tip +observe.data.ps.observation.Catch.minMaxWeight observe.data.ps.observation.Catch.minWeight observe.data.ps.observation.Catch.minWeight.short observe.data.ps.observation.Catch.reasonForDiscard ===================================== core/persistence/java/src/main/java/fr/ird/observe/entities/data/ps/common/TripSpi.java ===================================== @@ -45,6 +45,7 @@ import fr.ird.observe.entities.referential.ps.common.AcquisitionStatus; import fr.ird.observe.entities.referential.ps.common.ObservedSystem; import fr.ird.observe.entities.referential.ps.common.Program; import fr.ird.observe.entities.referential.ps.logbook.WellContentStatus; +import fr.ird.observe.persistence.SqlHelper; import fr.ird.observe.spi.service.ServiceContext; import io.ultreia.java4all.util.Dates; import io.ultreia.java4all.util.sql.SqlScriptWriter; @@ -330,8 +331,8 @@ public class TripSpi extends GeneratedTripSpi { toId(observationsDataQuality), toId(observer), commentFormat.apply(observationsComment), - escapeString(formsUrl), - escapeString(reportsUrl), + SqlHelper.escapeString(formsUrl), + SqlHelper.escapeString(reportsUrl), newId); } ===================================== core/persistence/resources/src/main/java/fr/ird/observe/persistence/avdth/data/DataWriter.java ===================================== @@ -77,7 +77,7 @@ public abstract class DataWriter<E extends DataEntity, R extends DataReader<E>> } public static String escapeString(String comment) { - return DataDtoEntityContext.escapeString(comment); + return SqlHelper.escapeString(comment); } public static String roundFloat2(Float comment) { ===================================== core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DataSourceMigrationForVersion_9_0.java ===================================== @@ -23,7 +23,8 @@ package fr.ird.observe.spi.migration.v9; */ import com.google.auto.service.AutoService; -import fr.ird.observe.spi.context.DataDtoEntityContext; +import fr.ird.observe.dto.StringCleaner; +import fr.ird.observe.persistence.SqlHelper; import fr.ird.observe.spi.migration.ByMajorMigrationVersionResource; import io.ultreia.java4all.util.Version; import io.ultreia.java4all.util.sql.SqlQuery; @@ -254,6 +255,7 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe executor.addScript("96", "add_referential_ps_common_ObservedSystem_finalize"); } } + private void migrateCatches(MigrationVersionResourceExecutor executor) { Function<String, String> commentFormat = executor.commentFormat(); @@ -395,7 +397,7 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe sample.topiaId, sample.topiaVersion, sample.topiaCreateDate, - DataDtoEntityContext.escapeString(sample.homeId), + SqlHelper.escapeString(StringCleaner.ALL.apply(sample.homeId)), stringFormat.apply(sample.comment), sample.set, sample.lastUpdateDate @@ -564,31 +566,39 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe sampleMeasure.topiaId, sampleMeasure.topiaVersion, sampleMeasure.topiaCreateDate, - DataDtoEntityContext.escapeString(sampleMeasure.homeId == null ? null : sampleMeasure.homeId.replaceAll("'", "")), + SqlHelper.escapeString(sampleMeasure.homeId == null ? null : sampleMeasure.homeId.replaceAll("'", "")), sampleMeasure.length, sampleMeasure.isLengthComputed, - DataDtoEntityContext.escapeString(sampleMeasure.picturesReferences == null ? null : sampleMeasure.picturesReferences.replaceAll("'", "")), + SqlHelper.escapeString(sampleMeasure.picturesReferences == null ? null : sampleMeasure.picturesReferences.replaceAll("'", "")), sampleMeasure.weight, sampleMeasure.isWeightComputed, sampleMeasure.count, sampleMeasure.acquisitionMode, - DataDtoEntityContext.escapeString(sampleMeasure.species), + SqlHelper.escapeString(sampleMeasure.species), sampleId, - DataDtoEntityContext.escapeString(sampleMeasure.sex), + SqlHelper.escapeString(sampleMeasure.sex), sampleMeasure.lastUpdateDate, - DataDtoEntityContext.escapeString(sampleMeasure.sizeMeasureType), - DataDtoEntityContext.escapeString(sampleMeasure.weightMeasureType), - DataDtoEntityContext.escapeString(sampleMeasure.tagNumber), - DataDtoEntityContext.escapeString(sampleMeasure.speciesFate), + SqlHelper.escapeString(sampleMeasure.sizeMeasureType), + SqlHelper.escapeString(sampleMeasure.weightMeasureType), + SqlHelper.escapeString(sampleMeasure.tagNumber), + SqlHelper.escapeString(sampleMeasure.speciesFate), sampleMeasure.sample_idx, - DataDtoEntityContext.escapeString(sampleMeasure.lengthMeasureMethod), - DataDtoEntityContext.escapeString(sampleMeasure.weightMeasureMethod) + SqlHelper.escapeString(sampleMeasure.lengthMeasureMethod), + SqlHelper.escapeString(sampleMeasure.weightMeasureMethod) )); } } @Override public void generateFinalizeSqlScript(MigrationVersionResourceExecutor executor) { + long stringFixedCount = new FixStringHelper(executor).execute(); + if (stringFixedCount > 0) { + log.warn(String.format("Fix %s string rows(s).", stringFixedCount)); + } + long commentFixedCount = new FixCommentHelper(executor).execute(); + if (commentFixedCount > 0) { + log.warn(String.format("Fix %s comment rows(s).", commentFixedCount)); + } migrateIdx(executor, "ps_observation", "catch", "set"); migrateIdx(executor, "ps_observation", "SampleMeasure", "sample"); } ===================================== core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DiscardedTargetCatchRecord.java ===================================== @@ -22,6 +22,8 @@ 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 java.sql.Connection; @@ -56,19 +58,19 @@ public class DiscardedTargetCatchRecord { 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" + + /* 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"); } @@ -132,24 +134,24 @@ public class DiscardedTargetCatchRecord { "%17$s, " + "%18$s" + ");", - /*1*/ DataDtoEntityContext.escapeString(id), + /*1*/ SqlHelper.escapeString(id), /*2*/ topiaVersion, /*3*/ DataDtoEntityContext.timestamp(topiaCreateDate), - /*4*/ DataDtoEntityContext.escapeString(homeId), + /*4*/ SqlHelper.escapeString(StringCleaner.ALL.apply(homeId)), /*5*/ catchWeight, /*6*/ weightCategoryRecord.getMinWeight(), /*7*/ weightCategoryRecord.getMaxWeight(), /*8*/ null, /*9*/ getComment(weightCategoryRecord, commentFormat), - /*10*/ DataDtoEntityContext.escapeString(reasonForDiscardId), - /*11*/ DataDtoEntityContext.escapeString(weightCategoryRecord.getSpeciesId()), - /*12*/ DataDtoEntityContext.escapeString(DataSourceMigrationForVersion_9_0.SPECIES_FATE_5), - /*13*/ DataDtoEntityContext.escapeString(setId), + /*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*/ DataDtoEntityContext.escapeString(getWell()), + /*15*/ SqlHelper.escapeString(StringCleaner.ALL.apply(well)), /*16*/ setIdx, - /*17*/ DataDtoEntityContext.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId), - /*18*/ DataDtoEntityContext.escapeString(DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_O) + /*17*/ SqlHelper.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId), + /*18*/ SqlHelper.escapeString(DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_O) ); } @@ -160,14 +162,4 @@ public class DiscardedTargetCatchRecord { return stringFormat.apply(comment + "\n" + weightCategoryRecord.toComment()); } - public String getWell() { - String result = well; - if (result!=null) { - result = well.trim(); - if (well.startsWith("'")) { - result = result.substring(1); - } - } - return result; - } } ===================================== core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/FixCommentHelper.java ===================================== @@ -0,0 +1,133 @@ +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 org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; +import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.util.Objects; +import java.util.concurrent.atomic.AtomicLong; +import java.util.function.Function; + +/** + * To auto-trim string comment fields. + * <p> + * <strong>Note:</strong> If fixed value is empty or blank then set {@code null} value. + * <p> + * Created on 11/11/2022. + * + * @author Tony Chemit - dev@tchemit.fr + * @since 9.0.17 + */ +public class FixCommentHelper { + + private static final Logger log = LogManager.getLogger(FixCommentHelper.class); + + private final MigrationVersionResourceExecutor executor; + private final AtomicLong count = new AtomicLong(); + private final Function<String, String> commentFormat; + + public FixCommentHelper(MigrationVersionResourceExecutor executor) { + this.executor = Objects.requireNonNull(executor); + commentFormat = executor.commentFormat(); + } + + public long execute() { + + fixField("common.Vessel", "comment"); + + fixField("ll_common.GearUseFeatures", "comment"); + fixField("ll_common.Program", "comment"); + fixField("ll_common.Trip", "generalComment"); + fixField("ll_common.Trip", "logbookComment"); + fixField("ll_common.Trip", "observationsComment"); + + fixField("ll_landing.Landing", "comment"); + + fixField("ll_logbook.Activity", "comment"); + fixField("ll_logbook.Catch", "comment"); + fixField("ll_logbook.Set", "comment"); + fixField("ll_logbook.Sample", "comment"); + + fixField("ll_observation.Activity", "comment"); + fixField("ll_observation.Branchline", "comment"); + fixField("ll_observation.Catch", "comment"); + fixField("ll_observation.Set", "comment"); + + fixField("ps_common.GearUseFeatures", "comment"); + fixField("ps_common.Program", "comment"); + fixField("ps_common.Trip", "generalComment"); + fixField("ps_common.Trip", "logbookComment"); + fixField("ps_common.Trip", "observationsComment"); + + fixField("ps_localmarket.Batch", "origin"); + fixField("ps_localmarket.Sample", "comment"); + fixField("ps_localmarket.SampleSpecies", "comment"); + fixField("ps_localmarket.Survey", "comment"); + + fixField("ps_logbook.Activity", "comment"); + fixField("ps_logbook.Catch", "comment"); + fixField("ps_logbook.FloatingObject", "comment"); + fixField("ps_logbook.Route", "comment"); + fixField("ps_logbook.Sample", "comment"); + fixField("ps_logbook.SampleSpecies", "comment"); + fixField("ps_logbook.TransmittingBuoy", "comment"); + + fixField("ps_observation.Activity", "comment"); + fixField("ps_observation.Catch", "comment"); + fixField("ps_observation.FloatingObject", "comment"); + fixField("ps_observation.NonTargetCatchRelease", "comment"); + fixField("ps_observation.Route", "comment"); + fixField("ps_observation.Set", "comment"); + fixField("ps_observation.TransmittingBuoy", "comment"); + return count.get(); + } + + public void fixField(String gav, String field) { + + executor.doSqlWork(connection -> { + try (PreparedStatement statement = connection.prepareStatement(String.format("SELECT topiaId, %2$s FROM %1$s WHERE %2$s IS NOT NULL", gav, field))) { + try (ResultSet resultSet = statement.executeQuery()) { + while (resultSet.next()) { + String fieldValue = resultSet.getString(2); + String fixFieldValue = fieldValue.trim(); + if (!fieldValue.equals(fixFieldValue)) { + if (fixFieldValue.isEmpty()) { + fixFieldValue = null; + } + String id = resultSet.getString(1); + String finalFieldValue = commentFormat.apply(fixFieldValue); + log.info(String.format("Fix comment field %s.%s[%s] from\n[%s]\nto\n[%s]", gav, field, id, fieldValue, fixFieldValue)); + executor.writeSql(String.format("UPDATE %s SET %s = %s WHERE topiaId = '%s';", gav, field, finalFieldValue, id)); + count.incrementAndGet(); + } + } + } + } + }); + + } +} ===================================== core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/FixStringHelper.java ===================================== @@ -0,0 +1,264 @@ +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 org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; +import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Collections; +import java.util.Map; +import java.util.Objects; +import java.util.Set; +import java.util.TreeMap; +import java.util.TreeSet; +import java.util.concurrent.atomic.AtomicLong; + +/** + * To auto-trim string fields and remove any {@code '} characters inside it. + * <p> + * <strong>Note:</strong> If fixed value is empty or blank then set {@code null} value. + * <p> + * Created on 11/11/2022. + * + * @author Tony Chemit - dev@tchemit.fr + * @since 9.0.17 + */ +public class FixStringHelper { + + private static final Logger log = LogManager.getLogger(FixStringHelper.class); + + private final MigrationVersionResourceExecutor executor; + private final AtomicLong count = new AtomicLong(); + + public FixStringHelper(MigrationVersionResourceExecutor executor) { + this.executor = Objects.requireNonNull(executor); + } + + static class FixStringEntityModel { + + private final String gav; + private final Map<String, StringCleaner> fieldsActions; + private final Set<String> fieldsDeleteIfNull; + + static FixStringEntityModel on(String gav, StringCleaner cleaner, String firstField, String... fields) { + return new FixStringEntityModel(gav).add(cleaner, firstField, fields); + } + + static FixStringEntityModel onI18n(String gav, StringCleaner cleaner, String firstField, String... fields) { + return on(gav, StringCleaner.TRIM, "label1", "label2", "label3").add(cleaner, firstField, fields); + } + + private FixStringEntityModel(String gav) { + this.gav = gav; + this.fieldsActions = new TreeMap<>(); + this.fieldsDeleteIfNull = new TreeSet<>(); + } + + FixStringEntityModel add(StringCleaner cleaner, String firstField, String... fields) { + fieldsActions.put(firstField, cleaner); + for (String field : fields) { + fieldsActions.put(field, cleaner); + } + return this; + } + + FixStringEntityModel deleteIfNull(String firstField, String... fields) { + fieldsDeleteIfNull.add(firstField); + Collections.addAll(fieldsDeleteIfNull, fields); + return this; + } + + } + + public long execute() { + executor.doSqlWork(connection -> { + execute(connection, FixStringEntityModel.onI18n("common.Country", StringCleaner.ALL, "code", "iso2Code", "iso3Code")); + execute(connection, FixStringEntityModel.onI18n("common.DataQuality", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.FpaZone", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.Gear", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.GearCharacteristic", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.GearCharacteristicType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.Harbour", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.on("common.LengthLengthParameter", StringCleaner.ALL, "coefficients", "inputOutputFormula", "outputInputFormula")); + execute(connection, FixStringEntityModel.onI18n("common.LengthMeasureMethod", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.on("common.LengthWeightParameter", StringCleaner.ALL, "coefficients", "lengthWeightFormula", "weightLengthFormula")); + execute(connection, FixStringEntityModel.onI18n("common.Ocean", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.Organism", StringCleaner.ALL, "code")); + // not removing single quotes + execute(connection, FixStringEntityModel.on("common.Person", StringCleaner.TRIM, "firstName", "lastName")); + execute(connection, FixStringEntityModel.onI18n("common.Sex", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.on("common.ShipOwner", StringCleaner.ALL, "code", "label")); + execute(connection, FixStringEntityModel.onI18n("common.SizeMeasureType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.Species", StringCleaner.ALL, "scientificLabel")); + execute(connection, FixStringEntityModel.onI18n("common.SpeciesGroup", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.SpeciesGroupReleaseMode", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.SpeciesList", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.Vessel", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.VesselType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.on("common.VesselSizeCategory", StringCleaner.ALL, "code", "capacityLabel", "gaugeLabel")); + execute(connection, FixStringEntityModel.onI18n("common.WeightMeasureMethod", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.WeightMeasureType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("common.Wind", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ll_common.BaitSettingStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.BaitType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.CatchFate", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.HealthStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.HookSize", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.HookType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.LightsticksColor", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.LightsticksType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.LineType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.MitigationType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.ObservationMethod", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.OnBoardProcessing", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.Program", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.SettingShape", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.TripType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.VesselActivity", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_common.WeightDeterminationMethod", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ll_landing.Company", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_landing.Conservation", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_landing.DataSource", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ll_observation.BaitHaulingStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.EncounterType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.HookPosition", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.ItemHorizontalPosition", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.ItemVerticalPosition", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.MaturityStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.on("ll_observation.SensorBrand", StringCleaner.ALL, "brandName", "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.SensorDataFormat", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.SensorType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ll_observation.StomachFullness", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ps_common.AcquisitionStatus", StringCleaner.ALL, "code")); + // not set to null if empty (special value FOB has an empty code) + execute(connection, FixStringEntityModel.onI18n("ps_common.ObjectMaterial", StringCleaner.REMOVE_SINGLE_QUOTE_AND_TRIM, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.ObjectMaterialType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.ObjectOperation", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.ObservedSystem", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.Program", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.ReasonForNoFishing", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.ReasonForNullSet", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.SampleType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.SchoolType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.SpeciesFate", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.TransmittingBuoyOperation", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.TransmittingBuoyOwnership", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.TransmittingBuoyType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.VesselActivity", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_common.WeightCategory", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ps_landing.Destination", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_landing.Fate", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ps_localmarket.BatchComposition", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_localmarket.BatchWeightType", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_localmarket.Packaging", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ps_logbook.InformationSource", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_logbook.SampleQuality", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_logbook.SetSuccessStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_logbook.WellContentStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_logbook.WellSamplingConformity", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_logbook.WellSamplingStatus", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.onI18n("ps_observation.DetectionMode", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.InformationSource", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.NonTargetCatchReleaseConformity", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.NonTargetCatchReleaseStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.NonTargetCatchReleasingTime", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.ReasonForDiscard", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.SpeciesStatus", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.onI18n("ps_observation.SurroundingActivity", StringCleaner.ALL, "code")); + + execute(connection, FixStringEntityModel.on("ll_common.GearUseFeaturesMeasurement", StringCleaner.ALL, "measurementValue").deleteIfNull("measurementValue")); + execute(connection, FixStringEntityModel.on("ll_common.Trip", StringCleaner.ALL, "ersId", "homeId")); + + execute(connection, FixStringEntityModel.on("ll_logbook.Catch", StringCleaner.ALL, "photoReferences", "tagNumber")); + execute(connection, FixStringEntityModel.on("ll_logbook.SamplePart", StringCleaner.ALL, "tagNumber")); + execute(connection, FixStringEntityModel.on("ll_logbook.Set", StringCleaner.ALL, "homeId")); + + execute(connection, FixStringEntityModel.on("ll_observation.Catch", StringCleaner.ALL, "photoReferences", "tagNumber")); + execute(connection, FixStringEntityModel.on("ll_observation.SensorUsed", StringCleaner.ALL, "sensorSerialNo")); + execute(connection, FixStringEntityModel.on("ll_observation.Set", StringCleaner.ALL, "homeId")); + execute(connection, FixStringEntityModel.on("ll_observation.Tdr", StringCleaner.ALL, "serialNo")); + + execute(connection, FixStringEntityModel.on("ps_common.GearUseFeaturesMeasurement", StringCleaner.ALL, "measurementValue").deleteIfNull("measurementValue")); + execute(connection, FixStringEntityModel.on("ps_common.Trip", StringCleaner.ALL, "ersId", "formsUrl", "homeId", "reportsUrl")); + + execute(connection, FixStringEntityModel.on("ps_localmarket.Sample", StringCleaner.ALL, "number")); + + execute(connection, FixStringEntityModel.on("ps_logbook.Catch", StringCleaner.ALL, "well")); + execute(connection, FixStringEntityModel.on("ps_logbook.FloatingObject", StringCleaner.ALL, "supportVesselName")); + execute(connection, FixStringEntityModel.on("ps_logbook.Sample", StringCleaner.ALL, "well")); + execute(connection, FixStringEntityModel.on("ps_logbook.TransmittingBuoy", StringCleaner.ALL, "code")); + execute(connection, FixStringEntityModel.on("ps_logbook.WellPlan", StringCleaner.ALL, "well")); + + execute(connection, FixStringEntityModel.on("ps_observation.Activity", StringCleaner.ALL, "ersId")); + execute(connection, FixStringEntityModel.on("ps_observation.FloatingObject", StringCleaner.ALL, "supportVesselName")); + execute(connection, FixStringEntityModel.on("ps_observation.SampleMeasure", StringCleaner.ALL, "picturesReferences", "tagNumber")); + execute(connection, FixStringEntityModel.on("ps_observation.Set", StringCleaner.ALL, "supportVesselName")); + execute(connection, FixStringEntityModel.on("ps_observation.TransmittingBuoy", StringCleaner.ALL, "code")); + }); + return count.get(); + } + + void execute(Connection connection, FixStringEntityModel model) throws SQLException { + for (Map.Entry<String, StringCleaner> entry : model.fieldsActions.entrySet()) { + execute(connection, model.gav, entry.getKey(), entry.getValue(), model.fieldsDeleteIfNull.contains(entry.getKey())); + } + } + + void execute(Connection connection, String gav, String field, StringCleaner cleaner, boolean deleteIfNull) throws SQLException { + try (PreparedStatement statement = connection.prepareStatement(String.format("SELECT topiaId, %2$s FROM %1$s WHERE %2$s IS NOT NULL", gav, field))) { + try (ResultSet resultSet = statement.executeQuery()) { + while (resultSet.next()) { + String fieldValue = resultSet.getString(2); + String fixFieldValue = cleaner.apply(fieldValue); + if (!fieldValue.equals(fixFieldValue)) { + String id = resultSet.getString(1); + if (deleteIfNull && fixFieldValue == null) { + log.warn(String.format("Delete row since field is empty %s.%s[%s] (was '%s')", gav, field, id, fieldValue)); + executor.writeSql(String.format("DELETE FROM %1$s WHERE topiaId = '%2$s';", gav, id)); + } else { + log.info(String.format("Fix string field %s.%s[%s] from\n[%s]\n to\n[%s]", gav, field, id, fieldValue, fixFieldValue)); + String finalFieldValue = SqlHelper.escapeString(fixFieldValue); + executor.writeSql(String.format("UPDATE %1$s SET %2$s = %3$s WHERE topiaId = '%4$s';", gav, field, finalFieldValue, id)); + } + count.incrementAndGet(); + } + } + } + } + } +} ===================================== core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/NotDiscardedTargetCatchRecord.java ===================================== @@ -22,6 +22,8 @@ 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 java.sql.Connection; @@ -42,19 +44,19 @@ class NotDiscardedTargetCatchRecord { 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 " + + /* 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 )"); } @@ -131,24 +133,24 @@ class NotDiscardedTargetCatchRecord { "%16$s, " + "%17$s, " + "%18$s);", - /*1*/ DataDtoEntityContext.escapeString(id), + /*1*/ SqlHelper.escapeString(id), /*2*/ topiaVersion, /*3*/ DataDtoEntityContext.timestamp(topiaCreateDate), - /*4*/ DataDtoEntityContext.escapeString(homeId), + /*4*/ SqlHelper.escapeString(StringCleaner.ALL.apply(homeId)), /*5*/ catchWeight, /*6*/ weightCategoryRecord.getMinWeight(), /*7*/ weightCategoryRecord.getMaxWeight(), /*8*/ null, /*9*/ getComment(weightCategoryRecord, commentFormat), /*10*/ null, - /*11*/ DataDtoEntityContext.escapeString(weightCategoryRecord.getSpeciesId()), - /*12*/ DataDtoEntityContext.escapeString("10".equals(weightCategoryRecord.getCode()) ? DataSourceMigrationForVersion_9_0.SPECIES_FATE_15 : DataSourceMigrationForVersion_9_0.SPECIES_FATE_6), - /*13*/ DataDtoEntityContext.escapeString(setId), + /*11*/ SqlHelper.escapeString(weightCategoryRecord.getSpeciesId()), + /*12*/ SqlHelper.escapeString("10".equals(weightCategoryRecord.getCode()) ? DataSourceMigrationForVersion_9_0.SPECIES_FATE_15 : DataSourceMigrationForVersion_9_0.SPECIES_FATE_6), + /*13*/ SqlHelper.escapeString(setId), /*14*/ DataDtoEntityContext.timestamp(lastUpdateDate), - /*15*/ DataDtoEntityContext.escapeString(getWell()), + /*15*/ SqlHelper.escapeString(StringCleaner.ALL.apply(well)), /*16*/ setIdx, - /*17*/ DataDtoEntityContext.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId), - /*18*/ DataDtoEntityContext.escapeString(targetCatchCompositionEstimatedByObserver ? DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_P : DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_U) + /*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) ); } @@ -158,16 +160,4 @@ class NotDiscardedTargetCatchRecord { } return stringFormat.apply(comment + "\n" + weightCategoryRecord.toComment()); } - - - public String getWell() { - String result = well; - if (result!=null) { - result = well.trim(); - if (well.startsWith("'")) { - result = result.substring(1); - } - } - return result; - } } ===================================== core/services/i18n/src/main/i18n/translations/services_en_GB.properties ===================================== @@ -1287,6 +1287,7 @@ observe.data.ps.observation.Catch.meanWeight.short=Mean weight observe.data.ps.observation.Catch.meanWeight.validation.notFilled=Weight or mean size must be filled. observe.data.ps.observation.Catch.meanWeightComputed.computed.tip=Mean weight was computed (%s) observe.data.ps.observation.Catch.meanWeightComputed.observed.tip=Mean weight was observed +observe.data.ps.observation.Catch.minMaxWeight=Lower / upper weight limit (in kg) observe.data.ps.observation.Catch.minWeight=Lower weight limit (in kg) observe.data.ps.observation.Catch.minWeight.short=Min weight (in kg) observe.data.ps.observation.Catch.reasonForDiscard=Reason for discard ===================================== core/services/i18n/src/main/i18n/translations/services_es_ES.properties ===================================== @@ -1287,6 +1287,7 @@ observe.data.ps.observation.Catch.meanWeight.short=Peso medio observe.data.ps.observation.Catch.meanWeight.validation.notFilled=Debe seleccionar uno de los dos valores (peso medio o talla media). observe.data.ps.observation.Catch.meanWeightComputed.computed.tip=El peso medio se calculó observe.data.ps.observation.Catch.meanWeightComputed.observed.tip=El peso medio se observó +observe.data.ps.observation.Catch.minMaxWeight=Lower / upper weight limit (in kg) TODO observe.data.ps.observation.Catch.minWeight=Lower weight limit (in kg) TODO observe.data.ps.observation.Catch.minWeight.short=Max weight (in kg) observe.data.ps.observation.Catch.reasonForDiscard=Razón del descarte ===================================== core/services/i18n/src/main/i18n/translations/services_fr_FR.properties ===================================== @@ -1287,6 +1287,7 @@ observe.data.ps.observation.Catch.meanWeight.short=Poids moy observe.data.ps.observation.Catch.meanWeight.validation.notFilled=Une des deux valeurs (poids moyen ou taille moyenne) devrait être renseignée. observe.data.ps.observation.Catch.meanWeightComputed.computed.tip=Le poids moyen a été calculé (%s) observe.data.ps.observation.Catch.meanWeightComputed.observed.tip=Le poids moyen a été observé +observe.data.ps.observation.Catch.minMaxWeight=Borne de poids inférieure / supérieure (en kg) observe.data.ps.observation.Catch.minWeight=Borne de poids inférieure (en kg) observe.data.ps.observation.Catch.minWeight.short=Poids min (en kg) observe.data.ps.observation.Catch.reasonForDiscard=Raison rejet ===================================== core/services/test/src/main/resources/fixtures/fr/ird/observe/services/service/ValidateService-dataResult.json ===================================== @@ -43828,7 +43828,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.009Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 1.0, "well": "2", "id": "fr.ird.data.ps.observation.Catch#1554060786914#0.3899957341641852", @@ -43874,7 +43874,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.004Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 1.0, "well": "2", "id": "fr.ird.data.ps.observation.Catch#1554060786914#0.455212337619455", @@ -43920,7 +43920,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.010Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 16.0, "well": "1", "id": "fr.ird.data.ps.observation.Catch#1554228692388#0.40195828823649804", @@ -43966,7 +43966,7 @@ "topiaVersion": 15, "topiaCreateDate": "2009-04-15T00:00:00.001Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 1.0, "well": "1", "id": "fr.ird.data.ps.observation.Catch#1554228692388#0.5827043409961202", @@ -44012,7 +44012,7 @@ "topiaVersion": 15, "topiaCreateDate": "2009-04-15T00:00:00.001Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 4.0, "well": "1", "id": "fr.ird.data.ps.observation.Catch#1554228964667#0.3873882731438876", @@ -44058,7 +44058,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.010Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 27.0, "well": "1", "id": "fr.ird.data.ps.observation.Catch#1554228964667#0.6898347092973715", @@ -44104,7 +44104,7 @@ "topiaVersion": 15, "topiaCreateDate": "2009-04-15T00:00:00.001Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 1.0, "well": "4", "id": "fr.ird.data.ps.observation.Catch#1554228964683#0.5525193499129678", @@ -44150,7 +44150,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.010Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 39.0, "well": "4", "id": "fr.ird.data.ps.observation.Catch#1554228964683#0.6111969202426193", @@ -44196,7 +44196,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.010Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 15.0, "well": "4", "id": "fr.ird.data.ps.observation.Catch#1554229020077#0.06929838028483837", @@ -44242,7 +44242,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.010Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 25.0, "well": "2", "id": "fr.ird.data.ps.observation.Catch#1554229020077#0.764944972601716", @@ -44288,7 +44288,7 @@ "topiaVersion": 15, "topiaCreateDate": "2009-04-15T00:00:00.001Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 1.0, "well": "3", "id": "fr.ird.data.ps.observation.Catch#1554659569269#0.5089206009917716", @@ -44334,7 +44334,7 @@ "topiaVersion": 16, "topiaCreateDate": "2009-04-15T00:00:00.010Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 53.0, "well": "3", "id": "fr.ird.data.ps.observation.Catch#1554659569269#0.9595940690443661", @@ -44470,7 +44470,7 @@ "topiaVersion": 19, "topiaCreateDate": "2009-04-15T00:00:00.005Z" }, - "speciesFateLabel": "Conservé (en cuve, poisson séché ou salé)", + "speciesFateLabel": "Conservé à destination de la conserverie", "catchWeight": 12.0, "well": "3T", "id": "fr.ird.data.ps.observation.Catch#1612860304046#0.051529228859696796", ===================================== core/services/test/src/main/resources/fixtures/fr/ird/observe/services/service/ValidateService-referentialResult.json ===================================== @@ -56940,13 +56940,13 @@ }, { "fieldName": "uri", - "scope": "WARNING", - "message": "Le champ n\u0027est pas renseigné." + "scope": "ERROR", + "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." }, { "fieldName": "uri", - "scope": "ERROR", - "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." + "scope": "WARNING", + "message": "Le champ n\u0027est pas renseigné." }, { "fieldName": "wormsId", @@ -62791,13 +62791,13 @@ }, { "fieldName": "uri", - "scope": "WARNING", - "message": "Le champ n\u0027est pas renseigné." + "scope": "ERROR", + "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." }, { "fieldName": "uri", - "scope": "ERROR", - "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." + "scope": "WARNING", + "message": "Le champ n\u0027est pas renseigné." }, { "fieldName": "wormsId", @@ -63241,13 +63241,13 @@ "messages": [ { "fieldName": "uri", - "scope": "WARNING", - "message": "Le champ n\u0027est pas renseigné." + "scope": "ERROR", + "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." }, { "fieldName": "uri", - "scope": "ERROR", - "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." + "scope": "WARNING", + "message": "Le champ n\u0027est pas renseigné." }, { "fieldName": "wormsId", @@ -68238,12 +68238,12 @@ { "fieldName": "species", "scope": "ERROR", - "message": "Le référentiel sélectionné «SPY - Sphyrnidae - Famille Sphyrnidae» (à la position 24) est désactivé." + "message": "Le référentiel sélectionné «LKY - Lepidochelys kempii - Tortue de Kemp» (à la position 120) est désactivé." }, { "fieldName": "species", "scope": "ERROR", - "message": "Le référentiel sélectionné «LKY - Lepidochelys kempii - Tortue de Kemp» (à la position 120) est désactivé." + "message": "Le référentiel sélectionné «SPY - Sphyrnidae - Famille Sphyrnidae» (à la position 24) est désactivé." }, { "fieldName": "species", @@ -68504,7 +68504,7 @@ { "fieldName": "species", "scope": "ERROR", - "message": "Le référentiel sélectionné «SPY - Sphyrnidae - Famille Sphyrnidae» (à la position 24) est désactivé." + "message": "Le référentiel sélectionné «LKY - Lepidochelys kempii - Tortue de Kemp» (à la position 120) est désactivé." }, { "fieldName": "species", @@ -68514,7 +68514,7 @@ { "fieldName": "species", "scope": "ERROR", - "message": "Le référentiel sélectionné «LKY - Lepidochelys kempii - Tortue de Kemp» (à la position 120) est désactivé." + "message": "Le référentiel sélectionné «SPY - Sphyrnidae - Famille Sphyrnidae» (à la position 24) est désactivé." }, { "fieldName": "species", @@ -142383,13 +142383,13 @@ }, { "fieldName": "uri", - "scope": "WARNING", - "message": "Le champ n\u0027est pas renseigné." + "scope": "ERROR", + "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." }, { "fieldName": "uri", - "scope": "ERROR", - "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." + "scope": "WARNING", + "message": "Le champ n\u0027est pas renseigné." }, { "fieldName": "wellRegex", @@ -157800,13 +157800,13 @@ }, { "fieldName": "uri", - "scope": "WARNING", - "message": "Le champ n\u0027est pas renseigné." + "scope": "ERROR", + "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." }, { "fieldName": "uri", - "scope": "ERROR", - "message": "Le champ (s\u0027il est renseigné) ne doit pas être consituté que d\u0027espaces." + "scope": "WARNING", + "message": "Le champ n\u0027est pas renseigné." }, { "fieldName": "vesselSizeCategory", @@ -196593,6 +196593,7 @@ "code": "1", "label": "Echappe du filet (pour requin-baleine et cétacés)", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683618#0.06155887805368032", @@ -196626,6 +196627,7 @@ "code": "3", "label": "Sortie mort du filet (pour requin-baleine et cétacés)", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.11883784875534997", @@ -196659,6 +196661,7 @@ "code": "4", "label": "Rejeté vivant", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5308862132841506", @@ -196682,6 +196685,7 @@ "code": "6", "label": "Conservé à destination de la conserverie", "discard": false, + "weightRangeAllowed": true, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5722739932065866", @@ -196710,6 +196714,7 @@ "code": "5", "label": "Rejeté mort", "discard": true, + "weightRangeAllowed": true, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.6250731662108877", @@ -196733,6 +196738,7 @@ "code": "2", "label": "Sortie vivant du filet (pour requin-baleine et cétacés)", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.9931091059863436", @@ -196765,6 +196771,7 @@ "content": { "code": "7", "label": "Partiellement conservé (ex: ailerons de requin, poisson séché)", + "weightRangeAllowed": false, "needComment": false, "enabled": false, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683620#0.46609703818634485", @@ -196803,6 +196810,7 @@ "code": "8", "label": "Utilisé en cuisine du bord", "discard": false, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683621#0.6728026426066158", @@ -196835,6 +196843,7 @@ "content": { "code": "9", "label": "Autres (à préciser dans les notes)", + "weightRangeAllowed": false, "needComment": true, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1239832683621#0.9099804284263154", @@ -196868,6 +196877,7 @@ "code": "15", "label": "Conservé pour le marché local ou poisson séché/salé à bord", "discard": false, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1464000000000#15", @@ -196895,6 +196905,7 @@ "content": { "code": "10", "label": "Ailerons seulements", + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1467372855729#0.568287924081734", @@ -196918,6 +196929,7 @@ "code": "11", "label": "Rejeté, statut non observé", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1501492537510#0.9210847837998154", @@ -196941,6 +196953,7 @@ "code": "12", "label": "Conservé pour raisons scientifiques", "discard": false, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1501492831539#0.9377232562184147", @@ -196964,6 +196977,7 @@ "code": "13", "label": "Rejeté suffocant", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1544448835551#0.620629930572886", @@ -196987,6 +197001,7 @@ "code": "14", "label": "Rejeté suffocant blessé", "discard": true, + "weightRangeAllowed": false, "needComment": false, "enabled": true, "id": "fr.ird.referential.ps.common.SpeciesFate#1544448977865#0.24265421995390768", ===================================== model/src/main/models/Observe/dto/class/i18nLabels.properties ===================================== @@ -84,7 +84,7 @@ data.ps.logbook.Well=well,wellVessel,wellFactory,wellSamplingConformity,wellSamp data.ps.logbook.WellActivity=activity,wellActivitySpecies data.ps.logbook.WellActivitySpecies=species,weight,weightCategory,count,setSpeciesNumber data.ps.observation.Activity=date,coordinate,observedSystem.available,observedSystem.selected,comment,comment2,currentFpaZone,dataQuality,detectionMode,ersId,floatingObjectEmpty,latitude,longitude,nextFpaZone,nonTargetCatchSpecies,observedSystem,observedSystemDistance,previousFpaZone,quadrant,reasonForNoFishing,seaSurfaceTemperature,surroundingActivity,time,vesselActivity,vesselSpeed,wind,generalTab,error.no.activity.6,measurementsTab,observedSystemTab -data.ps.observation.Catch=catchWeight,comment,informationSource,lengthMeasureMethod,meanLength,minWeight,maxWeight,meanWeight,reasonForDiscard,species,speciesFate,totalCount,weightMeasureMethod,well,catchWeightComputed.computed.tip,catchWeightComputed.observed.tip,meanLengthComputed.computed.tip,meanLengthComputed.observed.tip,meanWeightComputed.computed.tip,meanWeightComputed.observed.tip,totalCountComputed.computed.tip,totalCountComputed.observed.tip +data.ps.observation.Catch=catchWeight,comment,informationSource,lengthMeasureMethod,meanLength,minWeight,maxWeight,meanWeight,minMaxWeight,reasonForDiscard,species,speciesFate,totalCount,weightMeasureMethod,well,catchWeightComputed.computed.tip,catchWeightComputed.observed.tip,meanLengthComputed.computed.tip,meanLengthComputed.observed.tip,meanWeightComputed.computed.tip,meanWeightComputed.observed.tip,totalCountComputed.computed.tip,totalCountComputed.observed.tip data.ps.observation.FloatingObject=materialsValid,objectOperation,supportVesselName,country,vessel,computedBiodegradable,computedNonEntangling,computedSimplifiedObjectType,computedValues,generalTab,buoysTab,materialsTab,notComputed,type.short data.ps.observation.NonTargetCatchRelease=comment,conformity,count,length,lengthMeasureMethod,releasingTime,sex,status,species,speciesGroupReleaseMode,message.cantAdd data.ps.observation.ObjectObservedSpecies=count,species,speciesStatus ===================================== model/src/main/models/Observe/dto/class/i18nOverrideLabels.properties ===================================== @@ -23,7 +23,7 @@ data.ll.common.Trip=species data.ll.landing.Landing=vessel data.ps.localmarket.Sample=well data.ps.logbook.Catch=weightMeasureMethod.validation.required -data.ps.observation.Catch=minWeight,maxWeight,weightMeasureMethod +data.ps.observation.Catch=minWeight,maxWeight,minMaxWeight,weightMeasureMethod data.ps.observation.SchoolEstimate=species data.ps.observation.Set=startTime,haulingStartTimeStamp,haulingEndTimeStamp,endTimeStamp referential.common.Species=codeAndHomeId ===================================== toolkit/api/src/main/java/fr/ird/observe/dto/StringCleaner.java ===================================== @@ -0,0 +1,82 @@ +package fr.ird.observe.dto; + +/*- + * #%L + * ObServe Toolkit :: API + * %% + * 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 java.util.function.Function; + +/** + * Object to clean a string related to a database field. + * <p> + * Created on 12/11/2022. + * + * @author Tony Chemit - dev@tchemit.fr + * @since 9.017 + */ +public class StringCleaner implements Function<String, String> { + + public static final StringCleaner ALL = new StringCleaner(true, true, true); + public static final StringCleaner REMOVE_SINGLE_QUOTE_AND_TRIM = new StringCleaner(true, true, false); + public static final StringCleaner TRIM_AND_REPLACE_EMPTY_BY_NULL = new StringCleaner(false, true, true); + public static final StringCleaner TRIM = new StringCleaner(false, true, false); + + /** + * To remove any single quote in given string. + */ + private final boolean removeSingleQuote; + /** + * To perform a trim on given string. + */ + private final boolean trim; + /** + * To replace the given string value by null if it is empty. + */ + private final boolean replaceEmptyByNull; + + public StringCleaner(boolean removeSingleQuote, boolean trim, boolean replaceEmptyByNull) { + this.removeSingleQuote = removeSingleQuote; + this.trim = trim; + this.replaceEmptyByNull = replaceEmptyByNull; + } + + public static String removeSingleQuote(String string) { + return string.replaceAll("'", ""); + } + + @Override + public String apply(String string) { + if (string == null) { + return null; + } + String result = string; + if (removeSingleQuote) { + result = removeSingleQuote(result); + } + if (trim) { + result = result.trim(); + } + if (replaceEmptyByNull && result.isEmpty()) { + result = null; + } + return result; + } +} ===================================== toolkit/api/src/main/java/fr/ird/observe/persistence/SqlHelper.java ===================================== @@ -64,7 +64,21 @@ public class SqlHelper { if (trim.length() > maxLength) { trim = trim.substring(0, maxLength - 1); } - return "'" + trim.replaceAll("'", "''") + "'"; + return "'" + escapeSingleQuote(trim) + "'"; } + public static String escapeSingleQuote(String string) { + return string.replaceAll("'", "''"); + } + + public static String escapeString(String string) { + if (string == null) { + return "NULL"; + } + String trim = string.trim(); + if (trim.isEmpty()) { + return "NULL"; + } + return "'" + escapeSingleQuote(trim) + "'"; + } } ===================================== toolkit/persistence/src/main/java/fr/ird/observe/spi/context/DataDtoEntityContext.java ===================================== @@ -35,6 +35,7 @@ import fr.ird.observe.dto.referential.ReferentialLocale; import fr.ird.observe.entities.Entity; import fr.ird.observe.entities.data.DataEntity; import fr.ird.observe.entities.data.DataFileAware; +import fr.ird.observe.persistence.SqlHelper; import fr.ird.observe.persistence.request.DeleteRequest; import fr.ird.observe.spi.service.ServiceContext; import fr.ird.observe.spi.usage.UsageHelper; @@ -78,14 +79,7 @@ public abstract class DataDtoEntityContext< } public static String toId(Entity entity) { - return entity == null ? "NULL" : escapeString(entity.getTopiaId()); - } - - public static String escapeString(String string) { - if (string == null) { - return "NULL"; - } - return "'" + string.trim() + "'"; + return entity == null ? "NULL" : SqlHelper.escapeString(entity.getTopiaId()); } public static Blob byteArrayToBlob(byte[] bytes) { View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/compare/ad0dd0ec7e1a4b1c31e84a565... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/compare/ad0dd0ec7e1a4b1c31e84a565... You're receiving this email because of your account on gitlab.com.
participants (1)
-
Tony CHEMIT (＠tchemit)