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

Commits:

5 changed files:

Changes:

  • core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DataSourceMigrationForVersion_9_0.java
    ... ... @@ -254,6 +254,7 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe
    254 254 
                 executor.addScript("96", "add_referential_ps_common_ObservedSystem_finalize");
    255 255 
             }
    256 256 
         }
    257 
    +
    257 258 
         private void migrateCatches(MigrationVersionResourceExecutor executor) {
    258 259
    259 260 
             Function<String, String> commentFormat = executor.commentFormat();
    ... ... @@ -589,6 +590,14 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe
    589 590
    590 591 
         @Override
    591 592 
         public void generateFinalizeSqlScript(MigrationVersionResourceExecutor executor) {
    593 
    +        long stringFixedCount = new FixStringHelper(executor).execute();
    594 
    +        if (stringFixedCount > 0) {
    595 
    +            log.warn(String.format("Fix %s string rows(s).", stringFixedCount));
    596 
    +        }
    597 
    +        long commentFixedCount = new FixCommentHelper(executor).execute();
    598 
    +        if (commentFixedCount > 0) {
    599 
    +            log.warn(String.format("Fix %s comment rows(s).", commentFixedCount));
    600 
    +        }
    592 601 
             migrateIdx(executor, "ps_observation", "catch", "set");
    593 602 
             migrateIdx(executor, "ps_observation", "SampleMeasure", "sample");
    594 603 
         }

  • core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DiscardedTargetCatchRecord.java
    ... ... @@ -56,19 +56,19 @@ public class DiscardedTargetCatchRecord {
    56 56
    57 57 
         public static PreparedStatement prepareStatement(Connection connection) throws SQLException {
    58 58 
             return connection.prepareStatement("SELECT" +
    59 
    -                                     /* 01 */      " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
    60 
    -                                     /* 02 */      " tc.topiaVersion + 1," +
    61 
    -                                     /* 03 */      " tc.topiaCreateDate," +
    62 
    -                                     /* 04 */      " tc.homeId," +
    63 
    -                                     /* 05 */      " tc.catchWeight," +
    64 
    -                                     /* 06 */      " tc.weightCategory," +
    65 
    -                                     /* 07 */      " tc.comment," +
    66 
    -                                     /* 08 */      " tc.reasonForDiscard," +
    67 
    -                                     /* 09 */      " tc.set," +
    68 
    -                                     /* 10 */      " tc.lastUpdateDate," +
    69 
    -                                     /* 11 */      " tc.well," +
    70 
    -                                     /* 12 */      " -tc.set_idx," +
    71 
    -                                     /* 13 */      " tc.weightMeasureMethod" +
    59 
    +                                                   /* 01 */      " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
    60 
    +                                                   /* 02 */      " tc.topiaVersion + 1," +
    61 
    +                                                   /* 03 */      " tc.topiaCreateDate," +
    62 
    +                                                   /* 04 */      " tc.homeId," +
    63 
    +                                                   /* 05 */      " tc.catchWeight," +
    64 
    +                                                   /* 06 */      " tc.weightCategory," +
    65 
    +                                                   /* 07 */      " tc.comment," +
    66 
    +                                                   /* 08 */      " tc.reasonForDiscard," +
    67 
    +                                                   /* 09 */      " tc.set," +
    68 
    +                                                   /* 10 */      " tc.lastUpdateDate," +
    69 
    +                                                   /* 11 */      " tc.well," +
    70 
    +                                                   /* 12 */      " -tc.set_idx," +
    71 
    +                                                   /* 13 */      " tc.weightMeasureMethod" +
    72 72 
                                                        " FROM ps_observation.TargetCatch tc" +
    73 73 
                                                        " WHERE tc.discarded");
    74 74 
         }
    ... ... @@ -135,7 +135,7 @@ public class DiscardedTargetCatchRecord {
    135 135 
                     /*1*/                DataDtoEntityContext.escapeString(id),
    136 136 
                     /*2*/                topiaVersion,
    137 137 
                     /*3*/                DataDtoEntityContext.timestamp(topiaCreateDate),
    138 
    -                /*4*/                DataDtoEntityContext.escapeString(homeId),
    138 
    +                /*4*/                DataDtoEntityContext.escapeString(FixStringHelper.cleanString(homeId, true)),
    139 139 
                     /*5*/                catchWeight,
    140 140 
                     /*6*/                weightCategoryRecord.getMinWeight(),
    141 141 
                     /*7*/                weightCategoryRecord.getMaxWeight(),
    ... ... @@ -146,7 +146,7 @@ public class DiscardedTargetCatchRecord {
    146 146 
                     /*12*/               DataDtoEntityContext.escapeString(DataSourceMigrationForVersion_9_0.SPECIES_FATE_5),
    147 147 
                     /*13*/               DataDtoEntityContext.escapeString(setId),
    148 148 
                     /*14*/               DataDtoEntityContext.timestamp(lastUpdateDate),
    149 
    -                /*15*/               DataDtoEntityContext.escapeString(getWell()),
    149 
    +                /*15*/               DataDtoEntityContext.escapeString(FixStringHelper.cleanString(well, true)),
    150 150 
                     /*16*/               setIdx,
    151 151 
                     /*17*/               DataDtoEntityContext.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId),
    152 152 
                     /*18*/               DataDtoEntityContext.escapeString(DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_O)
    ... ... @@ -160,14 +160,4 @@ public class DiscardedTargetCatchRecord {
    160 160 
             return stringFormat.apply(comment + "\n" + weightCategoryRecord.toComment());
    161 161 
         }
    162 162
    163 
    -    public String getWell() {
    164 
    -        String result = well;
    165 
    -        if (result!=null) {
    166 
    -            result = well.trim();
    167 
    -            if (well.startsWith("'")) {
    168 
    -                result = result.substring(1);
    169 
    -            }
    170 
    -        }
    171 
    -        return result;
    172 
    -    }
    173 163 
     }

  • core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/FixCommentHelper.java
    1 
    +package fr.ird.observe.spi.migration.v9;
    2 
    +
    3 
    +/*-
    4 
    + * #%L
    5 
    + * ObServe Core :: Persistence :: Resources
    6 
    + * %%
    7 
    + * Copyright (C) 2008 - 2022 IRD, Ultreia.io
    8 
    + * %%
    9 
    + * This program is free software: you can redistribute it and/or modify
    10 
    + * it under the terms of the GNU General Public License as
    11 
    + * published by the Free Software Foundation, either version 3 of the
    12 
    + * License, or (at your option) any later version.
    13 
    + *
    14 
    + * This program is distributed in the hope that it will be useful,
    15 
    + * but WITHOUT ANY WARRANTY; without even the implied warranty of
    16 
    + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    17 
    + * GNU General Public License for more details.
    18 
    + *
    19 
    + * You should have received a copy of the GNU General Public
    20 
    + * License along with this program.  If not, see
    21 
    + * <http://www.gnu.org/licenses/gpl-3.0.html>.
    22 
    + * #L%
    23 
    + */
    24 
    +
    25 
    +import org.apache.logging.log4j.LogManager;
    26 
    +import org.apache.logging.log4j.Logger;
    27 
    +import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor;
    28 
    +
    29 
    +import java.sql.PreparedStatement;
    30 
    +import java.sql.ResultSet;
    31 
    +import java.util.Objects;
    32 
    +import java.util.concurrent.atomic.AtomicLong;
    33 
    +import java.util.function.Function;
    34 
    +
    35 
    +/**
    36 
    + * To auto-trim string comment fields.
    37 
    + * <p>
    38 
    + * <strong>Note:</strong> If fixed value is empty or blank then set {@code null} value.
    39 
    + * <p>
    40 
    + * Created on 11/11/2022.
    41 
    + *
    42 
    + * @author Tony Chemit - dev@tchemit.fr
    43 
    + * @since 9.0.17
    44 
    + */
    45 
    +public class FixCommentHelper {
    46 
    +
    47 
    +    private static final Logger log = LogManager.getLogger(FixCommentHelper.class);
    48 
    +
    49 
    +    private final MigrationVersionResourceExecutor executor;
    50 
    +    private final AtomicLong count = new AtomicLong();
    51 
    +    private final Function<String, String> commentFormat;
    52 
    +
    53 
    +    public FixCommentHelper(MigrationVersionResourceExecutor executor) {
    54 
    +        this.executor = Objects.requireNonNull(executor);
    55 
    +        commentFormat = executor.commentFormat();
    56 
    +    }
    57 
    +
    58 
    +    public long execute() {
    59 
    +
    60 
    +        fixField("common.Vessel", "comment");
    61 
    +
    62 
    +        fixField("ll_common.GearUseFeatures", "comment");
    63 
    +        fixField("ll_common.Program", "comment");
    64 
    +        fixField("ll_common.Trip", "generalComment");
    65 
    +        fixField("ll_common.Trip", "logbookComment");
    66 
    +        fixField("ll_common.Trip", "observationsComment");
    67 
    +
    68 
    +        fixField("ll_landing.Landing", "comment");
    69 
    +
    70 
    +        fixField("ll_logbook.Activity", "comment");
    71 
    +        fixField("ll_logbook.Catch", "comment");
    72 
    +        fixField("ll_logbook.Set", "comment");
    73 
    +        fixField("ll_logbook.Sample", "comment");
    74 
    +
    75 
    +        fixField("ll_observation.Activity", "comment");
    76 
    +        fixField("ll_observation.Branchline", "comment");
    77 
    +        fixField("ll_observation.Catch", "comment");
    78 
    +        fixField("ll_observation.Set", "comment");
    79 
    +
    80 
    +        fixField("ps_common.GearUseFeatures", "comment");
    81 
    +        fixField("ps_common.Program", "comment");
    82 
    +        fixField("ps_common.Trip", "generalComment");
    83 
    +        fixField("ps_common.Trip", "logbookComment");
    84 
    +        fixField("ps_common.Trip", "observationsComment");
    85 
    +
    86 
    +        fixField("ps_localmarket.Batch", "origin");
    87 
    +        fixField("ps_localmarket.Sample", "comment");
    88 
    +        fixField("ps_localmarket.SampleSpecies", "comment");
    89 
    +        fixField("ps_localmarket.Survey", "comment");
    90 
    +
    91 
    +        fixField("ps_logbook.Activity", "comment");
    92 
    +        fixField("ps_logbook.Catch", "comment");
    93 
    +        fixField("ps_logbook.FloatingObject", "comment");
    94 
    +        fixField("ps_logbook.Route", "comment");
    95 
    +        fixField("ps_logbook.Sample", "comment");
    96 
    +        fixField("ps_logbook.SampleSpecies", "comment");
    97 
    +        fixField("ps_logbook.TransmittingBuoy", "comment");
    98 
    +
    99 
    +        fixField("ps_observation.Activity", "comment");
    100 
    +        fixField("ps_observation.Catch", "comment");
    101 
    +        fixField("ps_observation.FloatingObject", "comment");
    102 
    +        fixField("ps_observation.NonTargetCatchRelease", "comment");
    103 
    +        fixField("ps_observation.Route", "comment");
    104 
    +        fixField("ps_observation.Set", "comment");
    105 
    +        fixField("ps_observation.TransmittingBuoy", "comment");
    106 
    +        return count.get();
    107 
    +    }
    108 
    +
    109 
    +    public void fixField(String gav, String field) {
    110 
    +
    111 
    +        executor.doSqlWork(connection -> {
    112 
    +            try (PreparedStatement statement = connection.prepareStatement(String.format("SELECT topiaId, %2$s FROM %1$s WHERE %2$s IS NOT NULL", gav, field))) {
    113 
    +                try (ResultSet resultSet = statement.executeQuery()) {
    114 
    +                    while (resultSet.next()) {
    115 
    +                        String fieldValue = resultSet.getString(2);
    116 
    +                        String fixFieldValue = fieldValue.trim();
    117 
    +                        if (!fieldValue.equals(fixFieldValue)) {
    118 
    +                            if (fixFieldValue.isEmpty()) {
    119 
    +                                fixFieldValue = null;
    120 
    +                            }
    121 
    +                            String id = resultSet.getString(1);
    122 
    +                            String finalFieldValue = commentFormat.apply(fixFieldValue);
    123 
    +                            log.warn(String.format("Fix comment field %s.%s[%s] (from '%s' to %s)", gav, field, id, fieldValue, finalFieldValue));
    124 
    +                            executor.writeSql(String.format("UPDATE %s SET %s = %s WHERE topiaId = '%s';", gav, field, finalFieldValue, id));
    125 
    +                            count.incrementAndGet();
    126 
    +                        }
    127 
    +                    }
    128 
    +                }
    129 
    +            }
    130 
    +        });
    131 
    +
    132 
    +    }
    133 
    +}

  • core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/FixStringHelper.java
    1 
    +package fr.ird.observe.spi.migration.v9;
    2 
    +
    3 
    +/*-
    4 
    + * #%L
    5 
    + * ObServe Core :: Persistence :: Resources
    6 
    + * %%
    7 
    + * Copyright (C) 2008 - 2022 IRD, Ultreia.io
    8 
    + * %%
    9 
    + * This program is free software: you can redistribute it and/or modify
    10 
    + * it under the terms of the GNU General Public License as
    11 
    + * published by the Free Software Foundation, either version 3 of the
    12 
    + * License, or (at your option) any later version.
    13 
    + *
    14 
    + * This program is distributed in the hope that it will be useful,
    15 
    + * but WITHOUT ANY WARRANTY; without even the implied warranty of
    16 
    + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    17 
    + * GNU General Public License for more details.
    18 
    + *
    19 
    + * You should have received a copy of the GNU General Public
    20 
    + * License along with this program.  If not, see
    21 
    + * <http://www.gnu.org/licenses/gpl-3.0.html>.
    22 
    + * #L%
    23 
    + */
    24 
    +
    25 
    +import fr.ird.observe.spi.context.DataDtoEntityContext;
    26 
    +import org.apache.logging.log4j.LogManager;
    27 
    +import org.apache.logging.log4j.Logger;
    28 
    +import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor;
    29 
    +
    30 
    +import java.sql.PreparedStatement;
    31 
    +import java.sql.ResultSet;
    32 
    +import java.util.Objects;
    33 
    +import java.util.concurrent.atomic.AtomicLong;
    34 
    +
    35 
    +/**
    36 
    + * To auto-trim string fields and remove any {@code '} characters inside it.
    37 
    + * <p>
    38 
    + * <strong>Note:</strong> If fixed value is empty or blank then set {@code null} value.
    39 
    + * <p>
    40 
    + * Created on 11/11/2022.
    41 
    + *
    42 
    + * @author Tony Chemit - dev@tchemit.fr
    43 
    + * @since 9.0.17
    44 
    + */
    45 
    +public class FixStringHelper {
    46 
    +
    47 
    +    private static final Logger log = LogManager.getLogger(FixStringHelper.class);
    48 
    +
    49 
    +    private final MigrationVersionResourceExecutor executor;
    50 
    +    private final AtomicLong count = new AtomicLong();
    51 
    +
    52 
    +    public FixStringHelper(MigrationVersionResourceExecutor executor) {
    53 
    +        this.executor = Objects.requireNonNull(executor);
    54 
    +    }
    55 
    +
    56 
    +    public long execute() {
    57 
    +
    58 
    +        fixI18nFields("common.Country", "code", "iso2Code", "iso3Code");
    59 
    +        fixI18nFields("common.DataQuality", "code");
    60 
    +        fixI18nFields("common.FpaZone", "code");
    61 
    +        fixI18nFields("common.Gear", "code");
    62 
    +        fixI18nFields("common.GearCharacteristic", "code");
    63 
    +        fixI18nFields("common.GearCharacteristicType", "code");
    64 
    +        fixI18nFields("common.Harbour", "code");
    65 
    +        fixFields("common.LengthLengthParameter", "coefficients", "inputOutputFormula", "outputInputFormula");
    66 
    +        fixI18nFields("common.LengthMeasureMethod", "code");
    67 
    +        fixFields("common.LengthWeightParameter", "coefficients", "lengthWeightFormula", "weightLengthFormula");
    68 
    +        fixI18nFields("common.Ocean", "code");
    69 
    +        fixI18nFields("common.Organism", "code");
    70 
    +        fixField("common.Person", "firstName", false);
    71 
    +        fixField("common.Person", "firstName", false);
    72 
    +        fixI18nFields("common.Sex", "code");
    73 
    +        fixFields("common.ShipOwner", "code", "label");
    74 
    +        fixI18nFields("common.SizeMeasureType", "code");
    75 
    +        fixI18nFields("common.Species", "scientificLabel");
    76 
    +        fixI18nFields("common.SpeciesGroup", "code");
    77 
    +        fixI18nFields("common.SpeciesGroupReleaseMode", "code");
    78 
    +        fixI18nFields("common.SpeciesList", "code");
    79 
    +        fixI18nFields("common.Vessel", "code");
    80 
    +        fixI18nFields("common.VesselType", "code");
    81 
    +        fixFields("common.VesselSizeCategory", "code", "capacityLabel", "gaugeLabel");
    82 
    +        fixI18nFields("common.WeightMeasureMethod", "code");
    83 
    +        fixI18nFields("common.WeightMeasureType", "code");
    84 
    +        fixI18nFields("common.Wind", "code");
    85 
    +
    86 
    +        fixI18nFields("ll_common.BaitSettingStatus", "code");
    87 
    +        fixI18nFields("ll_common.BaitType", "code");
    88 
    +        fixI18nFields("ll_common.CatchFate", "code");
    89 
    +        fixI18nFields("ll_common.HealthStatus", "code");
    90 
    +        fixI18nFields("ll_common.HookSize", "code");
    91 
    +        fixI18nFields("ll_common.HookType", "code");
    92 
    +        fixI18nFields("ll_common.LightsticksColor", "code");
    93 
    +        fixI18nFields("ll_common.LightsticksType", "code");
    94 
    +        fixI18nFields("ll_common.LineType", "code");
    95 
    +        fixI18nFields("ll_common.MitigationType", "code");
    96 
    +        fixI18nFields("ll_common.ObservationMethod", "code");
    97 
    +        fixI18nFields("ll_common.OnBoardProcessing", "code");
    98 
    +        fixI18nFields("ll_common.Program", "code");
    99 
    +        fixI18nFields("ll_common.SettingShape", "code");
    100 
    +        fixI18nFields("ll_common.TripType", "code");
    101 
    +        fixI18nFields("ll_common.VesselActivity", "code");
    102 
    +        fixI18nFields("ll_common.WeightDeterminationMethod", "code");
    103 
    +
    104 
    +        fixI18nFields("ll_landing.Company", "code");
    105 
    +        fixI18nFields("ll_landing.Conservation", "code");
    106 
    +        fixI18nFields("ll_landing.DataSource", "code");
    107 
    +
    108 
    +        fixI18nFields("ll_observation.BaitHaulingStatus", "code");
    109 
    +        fixI18nFields("ll_observation.EncounterType", "code");
    110 
    +        fixI18nFields("ll_observation.HookPosition", "code");
    111 
    +        fixI18nFields("ll_observation.ItemHorizontalPosition", "code");
    112 
    +        fixI18nFields("ll_observation.ItemVerticalPosition", "code");
    113 
    +        fixI18nFields("ll_observation.MaturityStatus", "code");
    114 
    +        fixFields("ll_observation.SensorBrand", "brandName", "code");
    115 
    +        fixI18nFields("ll_observation.SensorDataFormat", "code");
    116 
    +        fixI18nFields("ll_observation.SensorType", "code");
    117 
    +        fixI18nFields("ll_observation.StomachFullness", "code");
    118 
    +
    119 
    +        fixI18nFields("ps_common.AcquisitionStatus", "code");
    120 
    +        fixI18nFields("ps_common.ObjectMaterial", "code");
    121 
    +        fixI18nFields("ps_common.ObjectMaterialType", "code");
    122 
    +        fixI18nFields("ps_common.ObjectOperation", "code");
    123 
    +        fixI18nFields("ps_common.ObservedSystem", "code");
    124 
    +        fixI18nFields("ps_common.Program", "code");
    125 
    +        fixI18nFields("ps_common.ReasonForNoFishing", "code");
    126 
    +        fixI18nFields("ps_common.ReasonForNullSet", "code");
    127 
    +        fixI18nFields("ps_common.SampleType", "code");
    128 
    +        fixI18nFields("ps_common.SchoolType", "code");
    129 
    +        fixI18nFields("ps_common.SpeciesFate", "code");
    130 
    +        fixI18nFields("ps_common.TransmittingBuoyOperation", "code");
    131 
    +        fixI18nFields("ps_common.TransmittingBuoyOwnership", "code");
    132 
    +        fixI18nFields("ps_common.TransmittingBuoyType", "code");
    133 
    +        fixI18nFields("ps_common.VesselActivity", "code");
    134 
    +        fixI18nFields("ps_common.WeightCategory", "code");
    135 
    +
    136 
    +        fixI18nFields("ps_landing.Destination", "code");
    137 
    +        fixI18nFields("ps_landing.Fate", "code");
    138 
    +
    139 
    +        fixI18nFields("ps_localmarket.BatchComposition", "code");
    140 
    +        fixI18nFields("ps_localmarket.BatchWeightType", "code");
    141 
    +        fixI18nFields("ps_localmarket.Packaging", "code");
    142 
    +
    143 
    +        fixI18nFields("ps_logbook.InformationSource", "code");
    144 
    +        fixI18nFields("ps_logbook.SampleQuality", "code");
    145 
    +        fixI18nFields("ps_logbook.SetSuccessStatus", "code");
    146 
    +        fixI18nFields("ps_logbook.WellContentStatus", "code");
    147 
    +        fixI18nFields("ps_logbook.WellSamplingConformity", "code");
    148 
    +        fixI18nFields("ps_logbook.WellSamplingStatus", "code");
    149 
    +
    150 
    +        fixI18nFields("ps_observation.DetectionMode", "code");
    151 
    +        fixI18nFields("ps_observation.InformationSource", "code");
    152 
    +        fixI18nFields("ps_observation.NonTargetCatchReleaseConformity", "code");
    153 
    +        fixI18nFields("ps_observation.NonTargetCatchReleaseStatus", "code");
    154 
    +        fixI18nFields("ps_observation.NonTargetCatchReleasingTime", "code");
    155 
    +        fixI18nFields("ps_observation.ReasonForDiscard", "code");
    156 
    +        fixI18nFields("ps_observation.SpeciesStatus", "code");
    157 
    +        fixI18nFields("ps_observation.SurroundingActivity", "code");
    158 
    +
    159 
    +        fixFields("ll_common.GearUseFeaturesMeasurement", "measurementValue");
    160 
    +        fixFields("ll_common.Trip", "ersId", "homeId");
    161 
    +
    162 
    +        fixFields("ll_logbook.Catch", "photoReferences", "tagNumber");
    163 
    +        fixFields("ll_logbook.SamplePart", "tagNumber");
    164 
    +        fixFields("ll_logbook.Set", "homeId");
    165 
    +
    166 
    +        fixFields("ll_observation.Catch", "photoReferences", "tagNumber");
    167 
    +        fixFields("ll_observation.SensorUsed", "sensorSerialNo");
    168 
    +        fixFields("ll_observation.Set", "homeId");
    169 
    +        fixFields("ll_observation.Tdr", "serialNo");
    170 
    +
    171 
    +        fixFields("ps_common.GearUseFeaturesMeasurement", "measurementValue");
    172 
    +        fixFields("ps_common.Trip", "ersId", "formsUrl", "homeId", "reportsUrl");
    173 
    +
    174 
    +        fixFields("ps_localmarket.Sample", "number");
    175 
    +
    176 
    +        fixFields("ps_logbook.Catch", "well");
    177 
    +        fixFields("ps_logbook.FloatingObject", "supportVesselName");
    178 
    +        fixFields("ps_logbook.Sample", "well");
    179 
    +        fixFields("ps_logbook.TransmittingBuoy", "code");
    180 
    +        fixFields("ps_logbook.WellPlan", "well");
    181 
    +
    182 
    +        fixFields("ps_observation.Activity", "ersId");
    183 
    +        fixFields("ps_observation.FloatingObject", "supportVesselName");
    184 
    +
    185 
    +        fixFields("ps_observation.SampleMeasure", "picturesReferences", "tagNumber");
    186 
    +        fixFields("ps_observation.Set", "supportVesselName");
    187 
    +        fixFields("ps_observation.TransmittingBuoy", "code");
    188 
    +        return count.get();
    189 
    +    }
    190 
    +
    191 
    +    public void fixI18nFields(String gav, String... extraFields) {
    192 
    +        fixField(gav, "label1", false);
    193 
    +        fixField(gav, "label2", false);
    194 
    +        fixField(gav, "label3", false);
    195 
    +        fixFields(gav, extraFields);
    196 
    +    }
    197 
    +
    198 
    +    public void fixFields(String gav, String... extraFields) {
    199 
    +        for (String extraField : extraFields) {
    200 
    +            fixField(gav, extraField, false);
    201 
    +        }
    202 
    +    }
    203 
    +
    204 
    +    public void fixField(String gav, String field, boolean removeSimpleQuote) {
    205 
    +        executor.doSqlWork(connection -> {
    206 
    +            try (PreparedStatement statement = connection.prepareStatement(String.format("SELECT topiaId, %2$s FROM %1$s WHERE %2$s IS NOT NULL", gav, field))) {
    207 
    +                try (ResultSet resultSet = statement.executeQuery()) {
    208 
    +                    while (resultSet.next()) {
    209 
    +                        String fieldValue = resultSet.getString(2);
    210 
    +                        String fixFieldValue = cleanString(fieldValue, removeSimpleQuote);
    211 
    +                        if (gav.equals("ps_common.ObjectMaterial") && field.equals("code") && fixFieldValue == null) {
    212 
    +                            // special case when we need to keep blank value
    213 
    +                            fixFieldValue = "";
    214 
    +                        }
    215 
    +                        if (!fieldValue.equals(fixFieldValue)) {
    216 
    +                            String finalFieldValue = DataDtoEntityContext.escapeString(removeSimpleQuote ? fixFieldValue : fixFieldValue.replaceAll("'", "''"));
    217 
    +                            String id = resultSet.getString(1);
    218 
    +                            log.warn(String.format("Fix string field %s.%s[%s] (from '%s' to %s)", gav, field, id, fieldValue, finalFieldValue));
    219 
    +                            executor.writeSql(String.format("UPDATE %1$s SET %2$s = %3$s WHERE topiaId = '%4$s';", gav, field, finalFieldValue, id));
    220 
    +                            count.incrementAndGet();
    221 
    +                        }
    222 
    +                    }
    223 
    +                }
    224 
    +            }
    225 
    +        });
    226 
    +    }
    227 
    +
    228 
    +    protected static String cleanString(String field, boolean removeSimpleQuote) {
    229 
    +        String result = field;
    230 
    +        if (result != null) {
    231 
    +            result = result.trim();
    232 
    +            if (removeSimpleQuote) {
    233 
    +                result = result.replaceAll("'", "");
    234 
    +            }
    235 
    +            if (result.isEmpty()) {
    236 
    +                result = null;
    237 
    +            }
    238 
    +        }
    239 
    +        return result;
    240 
    +    }
    241 
    +}

  • core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/NotDiscardedTargetCatchRecord.java
    ... ... @@ -42,19 +42,19 @@ class NotDiscardedTargetCatchRecord {
    42 42
    43 43 
         public static PreparedStatement prepareStatement(Connection connection) throws SQLException {
    44 44 
             return connection.prepareStatement("SELECT" +
    45 
    -                                       /* 01 */    " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
    46 
    -                                       /* 02 */    " tc.topiaVersion + 1," +
    47 
    -                                       /* 03 */    " tc.topiaCreateDate," +
    48 
    -                                       /* 04 */    " tc.homeId," +
    49 
    -                                       /* 05 */    " tc.catchWeight," +
    50 
    -                                       /* 06 */    " tc.weightCategory," +
    51 
    -                                       /* 07 */    " tc.comment," +
    52 
    -                                       /* 08 */    " tc.set," +
    53 
    -                                       /* 09 */    " tc.lastUpdateDate," +
    54 
    -                                       /* 10 */    " tc.well," +
    55 
    -                                       /* 11 */    " -tc.set_idx," +
    56 
    -                                       /* 12 */    " tc.weightMeasureMethod," +
    57 
    -                                       /* 13 */    " s.targetcatchcompositionestimatedbyobserver " +
    45 
    +                                                   /* 01 */    " REPLACE(tc.topiaId, '.TargetCatch', '.Catch')," +
    46 
    +                                                   /* 02 */    " tc.topiaVersion + 1," +
    47 
    +                                                   /* 03 */    " tc.topiaCreateDate," +
    48 
    +                                                   /* 04 */    " tc.homeId," +
    49 
    +                                                   /* 05 */    " tc.catchWeight," +
    50 
    +                                                   /* 06 */    " tc.weightCategory," +
    51 
    +                                                   /* 07 */    " tc.comment," +
    52 
    +                                                   /* 08 */    " tc.set," +
    53 
    +                                                   /* 09 */    " tc.lastUpdateDate," +
    54 
    +                                                   /* 10 */    " tc.well," +
    55 
    +                                                   /* 11 */    " -tc.set_idx," +
    56 
    +                                                   /* 12 */    " tc.weightMeasureMethod," +
    57 
    +                                                   /* 13 */    " s.targetcatchcompositionestimatedbyobserver " +
    58 58 
                                                        " FROM ps_observation.TargetCatch tc INNER JOIN ps_observation.set s on s.topiaId = tc.set" +
    59 59 
                                                        " 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 )");
    60 60 
         }
    ... ... @@ -134,7 +134,7 @@ class NotDiscardedTargetCatchRecord {
    134 134 
                     /*1*/          DataDtoEntityContext.escapeString(id),
    135 135 
                     /*2*/          topiaVersion,
    136 136 
                     /*3*/          DataDtoEntityContext.timestamp(topiaCreateDate),
    137 
    -                /*4*/          DataDtoEntityContext.escapeString(homeId),
    137 
    +                /*4*/          DataDtoEntityContext.escapeString(FixStringHelper.cleanString(homeId, true)),
    138 138 
                     /*5*/          catchWeight,
    139 139 
                     /*6*/          weightCategoryRecord.getMinWeight(),
    140 140 
                     /*7*/          weightCategoryRecord.getMaxWeight(),
    ... ... @@ -145,7 +145,7 @@ class NotDiscardedTargetCatchRecord {
    145 145 
                     /*12*/         DataDtoEntityContext.escapeString("10".equals(weightCategoryRecord.getCode()) ? DataSourceMigrationForVersion_9_0.SPECIES_FATE_15 : DataSourceMigrationForVersion_9_0.SPECIES_FATE_6),
    146 146 
                     /*13*/         DataDtoEntityContext.escapeString(setId),
    147 147 
                     /*14*/         DataDtoEntityContext.timestamp(lastUpdateDate),
    148 
    -                /*15*/         DataDtoEntityContext.escapeString(getWell()),
    148 
    +                /*15*/         DataDtoEntityContext.escapeString(FixStringHelper.cleanString(well, true)),
    149 149 
                     /*16*/         setIdx,
    150 150 
                     /*17*/         DataDtoEntityContext.escapeString(weightMeasureMethodId == null ? "fr.ird.referential.common.WeightMeasureMethod#666#03" : weightCategoryId),
    151 151 
                     /*18*/         DataDtoEntityContext.escapeString(targetCatchCompositionEstimatedByObserver ? DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_P : DataSourceMigrationForVersion_9_0.INFORMATION_SOURCE_U)
    ... ... @@ -158,16 +158,4 @@ class NotDiscardedTargetCatchRecord {
    158 158 
             }
    159 159 
             return stringFormat.apply(comment + "\n" + weightCategoryRecord.toComment());
    160 160 
         }
    161 
    -
    162 
    -
    163 
    -    public String getWell() {
    164 
    -        String result = well;
    165 
    -        if (result!=null) {
    166 
    -            result = well.trim();
    167 
    -            if (well.startsWith("'")) {
    168 
    -                result = result.substring(1);
    169 
    -            }
    170 
    -        }
    171 
    -        return result;
    172 
    -    }
    173 161 
     }

    • —
    View it on GitLab.
    You're receiving this email because of your account on gitlab.com. Manage all notifications · Help