Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: 3131da07 by Tony Chemit at 2022-09-30T17:57:42+02:00 Souci d'affichage (probablement d'index) dans les échantillons PS après migration V9 - Closes #2436 - - - - - 2 changed files: - core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DataSourceMigrationForVersion_9_0.java - core/persistence/resources/src/main/resources/db/migration/v9/9.0/03_fill-table-ps_observation_sample-common.sql Changes: ===================================== core/persistence/resources/src/main/java/fr/ird/observe/spi/migration/v9/DataSourceMigrationForVersion_9_0.java ===================================== @@ -23,6 +23,7 @@ package fr.ird.observe.spi.migration.v9; */ import com.google.auto.service.AutoService; +import fr.ird.observe.spi.context.DataDtoEntityContext; import fr.ird.observe.spi.migration.ByMajorMigrationVersionResource; import io.ultreia.java4all.util.Version; import io.ultreia.java4all.util.sql.SqlQuery; @@ -32,9 +33,13 @@ import org.apache.logging.log4j.Logger; import org.nuiton.topia.service.migration.resources.MigrationVersionResource; import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Timestamp; import java.util.List; import java.util.Map; import java.util.Set; +import java.util.TreeMap; /** * Created on 19/01/2021. @@ -124,6 +129,11 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe addNewTable(executor, withIds, "03", "table-ps_observation_catch", "table-ps_observation_sample"); + if (withIds) { + // See https://gitlab.com/ultreiaio/ird-observe/-/issues/2436 + migrateSample(executor); + } + executor.addScript("04_0", "adapt_table-ps_observation_activity"); executor.addScript("04_1", "adapt_table-ps_observation_set"); @@ -189,6 +199,307 @@ public class DataSourceMigrationForVersion_9_0 extends ByMajorMigrationVersionRe } } + static class Sample { + String topiaId; + long topiaVersion; + Timestamp topiaCreateDate; + Timestamp lastUpdateDate; + String homeId; + String comment; + String set; + + public Sample(ResultSet resultSet) throws SQLException { + this.topiaId = resultSet.getString(1); + this.topiaVersion = resultSet.getLong(2); + this.topiaCreateDate = resultSet.getTimestamp(3); + this.homeId = resultSet.getString(4); + this.comment = resultSet.getString(5); + this.set = resultSet.getString(6); + this.lastUpdateDate = resultSet.getTimestamp(7); + } + + public void addHomeId(String homeId) { + if (homeId == null) { + return; + } + if (this.homeId == null) { + this.homeId = homeId; + return; + } + this.homeId += " - " + homeId; + } + + public void addComment(String comment) { + if (comment == null) { + return; + } + if (this.comment == null) { + this.comment = comment; + return; + } + this.comment += " - " + comment; + } + + } + + + static class SampleMeasure { + String topiaId; + long topiaVersion; + Timestamp topiaCreateDate; + Timestamp lastUpdateDate; + String homeId; + Float length; + Boolean isLengthComputed; + String picturesReferences; + Float weight; + Boolean isWeightComputed; + int count; + int acquisitionMode; + String species; + String sample; + String sex; + String sizeMeasureType; + String weightMeasureType; + String tagNumber; + String speciesFate; + int sample_idx; + String lengthMeasureMethod; + String weightMeasureMethod; + + public SampleMeasure(ResultSet resultSet) throws SQLException { + this.topiaId = resultSet.getString(1); + this.topiaVersion = resultSet.getLong(2); + this.topiaCreateDate = resultSet.getTimestamp(3); + this.homeId = resultSet.getString(4); + this.length = resultSet.getFloat(5); + this.isLengthComputed = resultSet.getBoolean(6); + this.picturesReferences = resultSet.getString(7); + this.weight = resultSet.getFloat(8); + this.isWeightComputed = resultSet.getBoolean(9); + this.count = resultSet.getInt(10); + this.acquisitionMode = resultSet.getInt(11); + this.species = resultSet.getString(12); + this.sample = resultSet.getString(13); + this.sex = resultSet.getString(14); + this.lastUpdateDate = resultSet.getTimestamp(15); + this.sizeMeasureType = resultSet.getString(16); + this.weightMeasureType = resultSet.getString(17); + this.tagNumber = resultSet.getString(18); + this.speciesFate = resultSet.getString(19); + this.sample_idx = resultSet.getInt(20); + this.lengthMeasureMethod = resultSet.getString(21); + this.weightMeasureMethod = resultSet.getString(22); + } + + } + + private void migrateSample(MigrationVersionResourceExecutor executor) { + + Map<String, Sample> setToSampleMap = new TreeMap<>(); + Map<String, String> sampleMapping = new TreeMap<>(); + + addSample(executor, setToSampleMap, sampleMapping, "SELECT REPLACE(topiaId, '.NonTargetSample', '.Sample'), topiaVersion, topiaCreateDate, homeId, substr(trim(comment), 0, 1024), set, lastUpdateDate FROM ps_observation.nontargetsample order by set, topiaId"); + + addSample(executor, setToSampleMap, sampleMapping, "SELECT REPLACE(topiaId, '.TargetSample', '.Sample'), topiaVersion, topiaCreateDate, homeId, substr(trim(comment), 0, 1024), set, lastUpdateDate FROM ps_observation.targetsample order by set, topiaId"); + + for (Sample sample : setToSampleMap.values()) { + executor.writeSql(String.format("INSERT INTO ps_observation.Sample(topiaId, topiaVersion, topiaCreateDate, homeId, comment, set, lastUpdateDate) VALUES('%s', %s, '%s'::timestamp, %s, %s, '%s', '%s'::timestamp);", + sample.topiaId, + sample.topiaVersion, + sample.topiaCreateDate, + DataDtoEntityContext.escapeString(sample.homeId), + DataDtoEntityContext.escapeComment(sample.comment), + sample.set, + sample.lastUpdateDate + )); + } + + migrateSampleMeasure(executor, sampleMapping); + } + + private void migrateSampleMeasure(MigrationVersionResourceExecutor executor, Map<String, String> sampleMapping) { + // nonTargetSampleMeasure + addSampleMeasure(executor, sampleMapping, "SELECT " + + "REPLACE(topiaId, '.NonTargetLength', '.SampleMeasure'), " + + "topiaVersion + 1, " + + "topiaCreateDate, " + + "homeId, " + + "length, " + + "isLengthComputed, " + + "picturesReferences, " + + "weight, " + + "isWeightComputed, " + + "count, " + + "acquisitionMode, " + + "species, " + + "REPLACE(nonTargetSample, '.NonTargetSample', '.Sample'), " + + "sex, " + + "lastUpdateDate, " + + "sizeMeasureType, " + + "weightMeasureType, " + + "tagNumber, " + + "speciesFate, " + + "nonTargetSample_idx, " + + "lengthMeasureMethod, " + + "weightMeasureMethod " + + "FROM ps_observation.NonTargetLength"); + + // discardedTargetSampleMeasure + addSampleMeasure(executor, sampleMapping, "SELECT " + + "REPLACE(tl.topiaId, '.TargetLength', '.SampleMeasure'), " + + "tl.topiaVersion + 1, " + + "tl.topiaCreateDate, " + + "tl.homeId, " + + "tl.length, " + + "tl.isLengthComputed, " + + "NULL, " + + "tl.weight, " + + "tl.isWeightComputed, " + + "tl.count, " + + "tl.acquisitionMode, " + + "tl.species, " + + "REPLACE(tl.targetSample, '.TargetSample', '.Sample'), " + + "'fr.ird.referential.common.Sex#1239832686121#0.0', " + + "tl.lastUpdateDate, " + + "tl.sizeMeasureType, " + + "tl.weightMeasureType, " + + "tl.tagNumber, " + + "'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.6250731662108877', " + + "-tl.targetsample_idx, " + + "tl.lengthMeasureMethod, " + + "tl.weightMeasureMethod " + + "FROM ps_observation.TargetSample ts " + + "INNER JOIN ps_observation.TargetLength tl ON (tl.targetSample=ts.topiaId) " + + "WHERE ts.discarded"); + + // notDiscardedTargetSampleMeasure + addSampleMeasure(executor, sampleMapping, "SELECT " + + "REPLACE(tl.topiaId, '.TargetLength', '.SampleMeasure'), " + + "tl.topiaVersion + 1, " + + "tl.topiaCreateDate, " + + "tl.homeId, " + + "tl.length, " + + "tl.isLengthComputed, " + + "NULL, " + + "tl.weight, " + + "tl.isWeightComputed, " + + "tl.count, " + + "tl.acquisitionMode, " + + "tl.species, " + + "REPLACE(tl.targetSample, '.TargetSample', '.Sample'), " + + "'fr.ird.referential.common.Sex#1239832686121#0.0', " + + "tl.lastUpdateDate, " + + "tl.sizeMeasureType, " + + "tl.weightMeasureType, " + + "tl.tagNumber, " + + "'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5722739932065866', " + + "-tl.targetsample_idx, " + + "tl.lengthMeasureMethod, " + + "tl.weightMeasureMethod " + + "FROM ps_observation.TargetSample ts " + + "INNER JOIN ps_observation.TargetLength tl ON (tl.targetSample=ts.topiaId) " + + "WHERE NOT ts.discarded"); + } + + private void addSample(MigrationVersionResourceExecutor executor, Map<String, Sample> setToSampleMap, Map<String, String> sampleMapping, String query) { + List<Sample> targetSample = executor.findMultipleResult(SqlQuery.wrap(query, Sample::new)); + for (Sample sample : targetSample) { + Sample existingSample = setToSampleMap.get(sample.set); + if (existingSample == null) { + // new sample + setToSampleMap.put(sample.set, sample); + } else { + // add to sample mapping + sampleMapping.put(sample.topiaId, existingSample.topiaId); + // update homeId + existingSample.addHomeId(sample.homeId); + // update comment + existingSample.addComment(sample.comment); + } + } + } + + private void addSampleMeasure(MigrationVersionResourceExecutor executor, Map<String, String> sampleMapping, String query) { + List<SampleMeasure> sampleMeasureList = executor.findMultipleResult(SqlQuery.wrap(query, SampleMeasure::new)); + for (SampleMeasure sampleMeasure : sampleMeasureList) { + String sampleId = sampleMapping.get(sampleMeasure.sample); + if (sampleId == null) { + sampleId = sampleMeasure.sample; + } + executor.writeSql(String.format("INSERT INTO ps_observation.SampleMeasure(" + + " topiaId," + + " topiaVersion," + + " topiaCreateDate," + + " homeId," + + " length," + + " isLengthComputed," + + " picturesReferences," + + " weight," + + " isWeightComputed," + + " count," + + " acquisitionMode," + + " species," + + " sample," + + " sex," + + " lastUpdateDate," + + " sizeMeasureType," + + " weightMeasureType," + + " tagNumber," + + " speciesFate," + + " sample_idx," + + " lengthMeasureMethod," + + " weightMeasureMethod)" + + " VALUES(" + + "'%s', " + + "%s, " + + "'%s'::timestamp, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "'%s', " + + "%s, " + + "'%s'::timestamp, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s, " + + "%s " + + ");", + sampleMeasure.topiaId, + sampleMeasure.topiaVersion, + sampleMeasure.topiaCreateDate, + DataDtoEntityContext.escapeString(sampleMeasure.homeId == null ? null : sampleMeasure.homeId.replaceAll("'", "")), + sampleMeasure.length, + sampleMeasure.isLengthComputed, + DataDtoEntityContext.escapeString(sampleMeasure.picturesReferences == null ? null : sampleMeasure.picturesReferences.replaceAll("'", "")), + sampleMeasure.weight, + sampleMeasure.isWeightComputed, + sampleMeasure.count, + sampleMeasure.acquisitionMode, + DataDtoEntityContext.escapeString(sampleMeasure.species), + sampleId, + DataDtoEntityContext.escapeString(sampleMeasure.sex), + sampleMeasure.lastUpdateDate, + DataDtoEntityContext.escapeString(sampleMeasure.sizeMeasureType), + DataDtoEntityContext.escapeString(sampleMeasure.weightMeasureType), + DataDtoEntityContext.escapeString(sampleMeasure.tagNumber), + DataDtoEntityContext.escapeString(sampleMeasure.speciesFate), + sampleMeasure.sample_idx, + DataDtoEntityContext.escapeString(sampleMeasure.lengthMeasureMethod), + DataDtoEntityContext.escapeString(sampleMeasure.weightMeasureMethod) + )); + } + } + @Override public void generateFinalizeSqlScript(MigrationVersionResourceExecutor executor) { migrateIdx(executor, "ps_observation", "catch", "set"); ===================================== core/persistence/resources/src/main/resources/db/migration/v9/9.0/03_fill-table-ps_observation_sample-common.sql ===================================== @@ -20,14 +20,6 @@ -- #L% --- -INSERT INTO ps_observation.Sample(topiaId, topiaVersion, topiaCreateDate, homeId, comment, set, lastUpdateDate) SELECT REPLACE(topiaId, '.NonTargetSample', '.Sample'), topiaVersion, topiaCreateDate, homeId, substr(trim(comment), 0, 1024), set, lastUpdateDate FROM ps_observation.nontargetsample; -INSERT INTO ps_observation.Sample(topiaId, topiaVersion, topiaCreateDate, homeId, comment, set, lastUpdateDate) SELECT REPLACE(topiaId, '.TargetSample', '.Sample'), topiaVersion, topiaCreateDate, homeId, substr(trim(comment), 0, 1024), set, lastUpdateDate FROM ps_observation.TargetSample; - - -INSERT INTO ps_observation.SampleMeasure(topiaId, topiaVersion, topiaCreateDate, homeId, length, isLengthComputed, picturesReferences, weight, isWeightComputed, count, acquisitionMode, species, sample, sex, lastUpdateDate, sizeMeasureType,weightMeasureType, tagNumber, speciesFate, sample_idx, lengthMeasureMethod, weightMeasureMethod) SELECT REPLACE(topiaId, '.NonTargetLength', '.SampleMeasure'), topiaVersion + 1, topiaCreateDate, homeId, length, isLengthComputed, picturesReferences, weight, isWeightComputed, count, acquisitionMode, species, REPLACE(nonTargetSample, '.NonTargetSample', '.Sample'), sex, lastUpdateDate, sizeMeasureType, weightMeasureType, tagNumber, speciesFate, nonTargetSample_idx, lengthMeasureMethod, weightMeasureMethod FROM ps_observation.NonTargetLength; -INSERT INTO ps_observation.SampleMeasure(topiaId, topiaVersion, topiaCreateDate, homeId, length, isLengthComputed, weight, isWeightComputed, count, acquisitionMode, species, sample, sex, lastUpdateDate, sizeMeasureType, weightMeasureType,tagNumber, speciesFate, sample_idx, lengthMeasureMethod, weightMeasureMethod) SELECT REPLACE(tl.topiaId, '.TargetLength', '.SampleMeasure'), tl.topiaVersion + 1, tl.topiaCreateDate, tl.homeId, tl.length, tl.isLengthComputed, tl.weight, tl.isWeightComputed, tl.count, tl.acquisitionMode, tl.species, REPLACE(tl.targetSample, '.TargetSample', '.Sample'), 'fr.ird.referential.common.Sex#1239832686121#0.0', tl.lastUpdateDate, tl.sizeMeasureType, tl.weightMeasureType, tl.tagNumber, 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.6250731662108877', -tl.targetsample_idx, tl.lengthMeasureMethod, tl.weightMeasureMethod FROM ps_observation.TargetSample ts INNER JOIN ps_observation.TargetLength tl ON (tl.targetSample=ts.topiaId) WHERE ts.discarded; -INSERT INTO ps_observation.SampleMeasure(topiaId, topiaVersion, topiaCreateDate, homeId, length, isLengthComputed, weight, isWeightComputed, count, acquisitionMode, species, sample, sex, lastUpdateDate, sizeMeasureType, weightMeasureType,tagNumber, speciesFate, sample_idx, lengthMeasureMethod, weightMeasureMethod) SELECT REPLACE(tl.topiaId, '.TargetLength', '.SampleMeasure'), tl.topiaVersion + 1, tl.topiaCreateDate, tl.homeId, tl.length, tl.isLengthComputed, tl.weight, tl.isWeightComputed, tl.count, tl.acquisitionMode, tl.species, REPLACE(tl.targetSample, '.TargetSample', '.Sample'), 'fr.ird.referential.common.Sex#1239832686121#0.0', tl.lastUpdateDate, tl.sizeMeasureType, tl.weightMeasureType, tl.tagNumber, 'fr.ird.referential.ps.common.SpeciesFate#1239832683619#0.5722739932065866', -tl.targetsample_idx, tl.lengthMeasureMethod, tl.weightMeasureMethod FROM ps_observation.TargetSample ts INNER JOIN ps_observation.TargetLength tl ON (tl.targetSample=ts.topiaId) WHERE NOT ts.discarded; - UPDATE common.LastUpdateDate SET TYPE = REPLACE(TYPE, '.NonTargetSample', '.Sample') WHERE type = 'fr.ird.observe.entities.data.ps.observation.NonTargetSample'; DELETE from common.LastUpdateDate WHERE TYPE LIKE ('%.TargetSample'); UPDATE common.LastUpdateDate SET TYPE = REPLACE(TYPE, '.NonTargetLength', '.SampleMeasure') WHERE type = 'fr.ird.observe.entities.data.ps.observation.NonTargetLength'; View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/3131da07db0e270eff5c3fea60... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/3131da07db0e270eff5c3fea60... You're receiving this email because of your account on gitlab.com.