Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: 24056b12 by Tony Chemit at 2024-06-25T17:12:09+02:00 Amélioration du code de mise à jour sql pour les associations lors de remplacement de référentiel - Closes #2899 - - - - - 2 changed files: - core/persistence/java/src/main/resources/META-INF/persistence/Observe/TopiaEntitySqlScriptModel.json - toolkit/templates/src/main/java/fr/ird/observe/toolkit/templates/entity/ReplaceReferentialScriptGenerator.java Changes: ===================================== core/persistence/java/src/main/resources/META-INF/persistence/Observe/TopiaEntitySqlScriptModel.json ===================================== @@ -1116,7 +1116,8 @@ "UPDATE ps_common.trip SET captain = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE captain = '%2$s';", "UPDATE ps_common.trip SET observationsDataEntryOperator = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE observationsDataEntryOperator = '%2$s';", "UPDATE ps_common.trip SET logbookDataEntryOperator = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE logbookDataEntryOperator = '%2$s';", - "UPDATE ps_logbook.sample_person SET person = '%3$s' WHERE person = '%2$s';" + "UPDATE ps_logbook.sample_person SET person = '%3$s' WHERE person = '%2$s' AND (SELECT COUNT(s.person) FROM ps_logbook.sample_person s WHERE s.person = '%3$s') = 0;", + "DELETE FROM ps_logbook.sample_person WHERE person = '%2$s';" ], "org.nuiton.topia.service.sql.script.DeleteReferentialScript": [ "DELETE FROM common.person WHERE topiaId = '%1$s';" @@ -1239,10 +1240,14 @@ "UPDATE ps_localmarket.batch SET species = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE species = '%2$s';", "UPDATE ps_localmarket.surveyPart SET species = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE species = '%2$s';", "UPDATE ps_localmarket.sampleSpecies SET species = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE species = '%2$s';", - "UPDATE ll_common.trip_species SET species = '%3$s' WHERE species = '%2$s';", - "UPDATE ll_observation.catch_predator SET species = '%3$s' WHERE species = '%2$s';", - "UPDATE ll_observation.tdr_species SET species = '%3$s' WHERE species = '%2$s';", - "UPDATE ll_logbook.catch_predator SET species = '%3$s' WHERE species = '%2$s';" + "UPDATE ll_common.trip_species SET species = '%3$s' WHERE species = '%2$s' AND (SELECT COUNT(s.species) FROM ll_common.trip_species s WHERE s.species = '%3$s') = 0;", + "DELETE FROM ll_common.trip_species WHERE species = '%2$s';", + "UPDATE ll_observation.catch_predator SET species = '%3$s' WHERE species = '%2$s' AND (SELECT COUNT(s.species) FROM ll_observation.catch_predator s WHERE s.species = '%3$s') = 0;", + "DELETE FROM ll_observation.catch_predator WHERE species = '%2$s';", + "UPDATE ll_observation.tdr_species SET species = '%3$s' WHERE species = '%2$s' AND (SELECT COUNT(s.species) FROM ll_observation.tdr_species s WHERE s.species = '%3$s') = 0;", + "DELETE FROM ll_observation.tdr_species WHERE species = '%2$s';", + "UPDATE ll_logbook.catch_predator SET species = '%3$s' WHERE species = '%2$s' AND (SELECT COUNT(s.species) FROM ll_logbook.catch_predator s WHERE s.species = '%3$s') = 0;", + "DELETE FROM ll_logbook.catch_predator WHERE species = '%2$s';" ], "org.nuiton.topia.service.sql.script.ReplaceReferentialInReferentialScript": [ "UPDATE ps_common.weightCategory SET species = '%3$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%1$s'::timestamp WHERE species = '%2$s';", @@ -1615,8 +1620,10 @@ "UPDATE common.lastUpdateDate SET lastUpdateDate = '%1$s'::timestamp WHERE type ='fr.ird.observe.entities.referential.ll.common.MitigationType';" ], "org.nuiton.topia.service.sql.script.ReplaceReferentialInDataScript": [ - "UPDATE ll_observation.set_mitigationType SET mitigationType = '%3$s' WHERE mitigationType = '%2$s';", - "UPDATE ll_logbook.set_mitigationType SET mitigationType = '%3$s' WHERE mitigationType = '%2$s';" + "UPDATE ll_observation.set_mitigationType SET mitigationType = '%3$s' WHERE mitigationType = '%2$s' AND (SELECT COUNT(s.mitigationType) FROM ll_observation.set_mitigationType s WHERE s.mitigationType = '%3$s') = 0;", + "DELETE FROM ll_observation.set_mitigationType WHERE mitigationType = '%2$s';", + "UPDATE ll_logbook.set_mitigationType SET mitigationType = '%3$s' WHERE mitigationType = '%2$s' AND (SELECT COUNT(s.mitigationType) FROM ll_logbook.set_mitigationType s WHERE s.mitigationType = '%3$s') = 0;", + "DELETE FROM ll_logbook.set_mitigationType WHERE mitigationType = '%2$s';" ], "org.nuiton.topia.service.sql.script.DeleteReferentialScript": [ "DELETE FROM ll_common.mitigationType WHERE topiaId = '%1$s';" @@ -2043,8 +2050,10 @@ "UPDATE common.lastUpdateDate SET lastUpdateDate = '%1$s'::timestamp WHERE type ='fr.ird.observe.entities.referential.ps.common.ObservedSystem';" ], "org.nuiton.topia.service.sql.script.ReplaceReferentialInDataScript": [ - "UPDATE ps_observation.activity_observedSystem SET observedSystem = '%3$s' WHERE observedSystem = '%2$s';", - "UPDATE ps_logbook.activity_observedSystem SET observedSystem = '%3$s' WHERE observedSystem = '%2$s';" + "UPDATE ps_observation.activity_observedSystem SET observedSystem = '%3$s' WHERE observedSystem = '%2$s' AND (SELECT COUNT(s.observedSystem) FROM ps_observation.activity_observedSystem s WHERE s.observedSystem = '%3$s') = 0;", + "DELETE FROM ps_observation.activity_observedSystem WHERE observedSystem = '%2$s';", + "UPDATE ps_logbook.activity_observedSystem SET observedSystem = '%3$s' WHERE observedSystem = '%2$s' AND (SELECT COUNT(s.observedSystem) FROM ps_logbook.activity_observedSystem s WHERE s.observedSystem = '%3$s') = 0;", + "DELETE FROM ps_logbook.activity_observedSystem WHERE observedSystem = '%2$s';" ], "org.nuiton.topia.service.sql.script.DeleteReferentialScript": [ "DELETE FROM ps_common.observedSystem WHERE topiaId = '%1$s';" ===================================== toolkit/templates/src/main/java/fr/ird/observe/toolkit/templates/entity/ReplaceReferentialScriptGenerator.java ===================================== @@ -22,6 +22,7 @@ package fr.ird.observe.toolkit.templates.entity; * #L% */ +import fr.ird.observe.spi.referential.SqlStatements; import org.nuiton.topia.service.sql.metadata.TopiaMetadataAssociation; import org.nuiton.topia.service.sql.metadata.TopiaMetadataComposition; import org.nuiton.topia.service.sql.metadata.TopiaMetadataEntity; @@ -55,11 +56,11 @@ public class ReplaceReferentialScriptGenerator { /** * To update a reference of a composition relation. */ - public static final String COMPOSITION_UPDATE_STATEMENT = "UPDATE %s.%s SET %s = '%s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%s'::timestamp WHERE %s = '%s';"; + public static final String COMPOSITION_UPDATE_STATEMENT = "UPDATE %1$s.%2$s SET %3$s = '%4$s', topiaVersion = topiaVersion + 1, lastUpdateDate = '%5$s'::timestamp WHERE %3$s = '%6$s';"; /** * To update a reference of an association relation. */ - private static final String ASSOCIATION_UPDATE_STATEMENT = "UPDATE %s.%s SET %s = '%s' WHERE %s = '%s';"; + private static final String ASSOCIATION_UPDATE_STATEMENT = "UPDATE %1$s.%2$s SET %3$s = '%4$s' WHERE %3$s = '%5$s' AND (SELECT COUNT(s.%3$s) FROM %1$s.%2$s s WHERE s.%3$s = '%4$s') = 0;"; /** * All reverse relation many-to-one. */ @@ -96,6 +97,8 @@ public class ReplaceReferentialScriptGenerator { } String sql = generateAssociationUpdateStatement(replacementStruct, sourceId, replacementId); builder.add(sql); + sql = generateAssociationDeleteStatement(replacementStruct, sourceId); + builder.add(sql); } return builder; } @@ -107,7 +110,6 @@ public class ReplaceReferentialScriptGenerator { composition.getTargetDbName(), replacementId, lastUpdateDate, - composition.getTargetDbName(), sourceId); } @@ -117,7 +119,14 @@ public class ReplaceReferentialScriptGenerator { association.getTableName(), association.getTargetDbName(), replacementId, - association.getTargetDbName(), sourceId); } + + private String generateAssociationDeleteStatement(TopiaMetadataAssociation association, String sourceId) { + return String.format(SqlStatements.ASSOCIATION_DELETE_STATEMENT, + association.getOwner().getDbSchemaName(), + association.getTableName(), + association.getTargetDbName(), + sourceId).trim(); + } } View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/24056b129bbe3bbb0b4e916d30... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/24056b129bbe3bbb0b4e916d30... You're receiving this email because of your account on gitlab.com.