This is an automated email from the git hooks/post-receive script. New commit to branch feature/8429 in repository observe. See https://gitlab.nuiton.org/codelutin/observe.git commit 03198e7cd3129da4da1d69f208cc9d22a6d0821d Author: Tony CHEMIT <chemit@codelutin.com> Date: Mon Nov 21 09:24:01 2016 +0100 Amélioration de la migration des types de mensurations : on insère les référentiels manquants à la volée --- .../DataSourceMigrationForVersion_5_1.java | 118 ++++++++++++++++++--- 1 file changed, 101 insertions(+), 17 deletions(-) diff --git a/entities-migration/src/main/java/fr/ird/observe/entities/migration/DataSourceMigrationForVersion_5_1.java b/entities-migration/src/main/java/fr/ird/observe/entities/migration/DataSourceMigrationForVersion_5_1.java index 683af4d..f11f4b9 100644 --- a/entities-migration/src/main/java/fr/ird/observe/entities/migration/DataSourceMigrationForVersion_5_1.java +++ b/entities-migration/src/main/java/fr/ird/observe/entities/migration/DataSourceMigrationForVersion_5_1.java @@ -34,6 +34,9 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashSet; +import java.util.Iterator; import java.util.LinkedHashSet; import java.util.List; import java.util.Set; @@ -107,6 +110,84 @@ public class DataSourceMigrationForVersion_5_1 extends AbstractObserveMigrationC return set.getString(1); } }); + List<String> existingLengthMeasureTypes = topiaSqlSupport.findMultipleResult(new TopiaSqlQuery<String>() { + + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + // Attention à ce moment là ce référentiel est encore dans le schema longline + return connection.prepareStatement("SELECT DISTINCT(code) FROM observe_longline.sizemeasuretype "); + } + + @Override + public String prepareResult(ResultSet set) throws SQLException { + return set.getString(1); + } + }); + + Set<String> missingLengthMeasureTypeCodes = new HashSet<>(); + for (String lengthMeasureType : lengthMeasureTypes) { + String realCode = missingCodesMapping.get(lengthMeasureType); + if (realCode == null) { + realCode = lengthMeasureType; + } + missingLengthMeasureTypeCodes.add(realCode); + } + missingLengthMeasureTypeCodes.removeAll(existingLengthMeasureTypes); + + ImmutableMap.Builder<String, String> extraTopiaIdsByCodeBuilder = ImmutableMap.builder(); + + if (!missingLengthMeasureTypeCodes.isEmpty()) { + + // il manque des référentiels on va les ajouter + ImmutableMap<String, String> sqlMappingByCode = + ImmutableMap.<String, String>builder() + .put("FL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499465700#0.0902433863375336', 2, '2015-06-05', '2016-10-18 11:51:54.099140','FL', 1, null, 'Fork Length', 'Fork Length', 'Fork Length', null, null, null, null, null, false);") + .put("CFL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499465999#0.707568018231541', 3, '2015-06-05', '2016-10-18 11:51:54.099140', 'CFL', 1, null, 'Curved Fork Length', 'Curved Fork Length', 'Curved Fork Length', null, null, null, null, null, f [...] + .put("PAL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499466255#0.444246932631359', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'PAL', 1, null, 'Pectoral-Anal Length', 'Pectoral-Anal Length', 'Pectoral-Anal Length', null, null, null, null, n [...] + .put("TL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499466532#0.844473292818293', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'TL', 1, null, 'Total Length', 'Total Length', 'Total Length', null, null, null, null, null, false);") + .put("PDL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499466774#0.529249255312607', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'PDL', 1, null, 'Predorsal Length', 'Predorsal Length', 'Predorsal Length', null, null, null, null, null, false);") + .put("SL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499467059#0.563144549960271', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'SL', 1, null, 'Standard Length', 'Standard Length', 'Standard Length', null, null, null, null, null, false);") + .put("LJFL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499467326#0.537910396233201', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'LJFL', 1, null, 'Lower Jaw-Fork Length', 'Lower Jaw-Fork Length', 'Lower Jaw-Fork Length', null, null, null, nu [...] + .put("CLJFL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499467638#0.409153908025473', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'CLJFL', 1, null, 'Curved Lower Jaw-Fork Length', 'Curved Lower Jaw-Fork Length', 'Curved Lower Jaw-Fork Length [...] + .put("IDL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499467945#0.249704661779106', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'IDL', 1, null, 'Interdorsal Length', 'Interdorsal Length', 'Interdorsal Length', null, null, null, null, null, f [...] + .put("EFL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499468283#0.152317869942635', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'EFL', 1, null, 'Eye-Fork Length', 'Eye-Fork Length', 'Eye-Fork Length', null, null, null, null, null, false);") + .put("DW", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499468646#0.710986209334806', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'DW', 1, null, 'Disk Width', 'Disk Width', 'Disk Width', null, null, null, null, null, false);") + .put("DL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499469115#0.42996318358928', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'DL', 1, null, 'Disk Length', 'Disk Length', 'Disk Length', null, null, null, null, null, false);") + .put("TW", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499469572#0.969587777974084', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'TW', 1, null, 'Total Weight', 'Total Weight', 'Total Weight', null, null, null, null, null, false);") + .put("CTL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499470528#0.813851526239887', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'CTL', 1, null, 'Curved Total Length', 'Curved Total Length', 'Curved Total Length', null, null, null, null, null [...] + .put("DML", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499470887#0.950930784922093', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'DML', 1, null, 'Dorsal Mantle Length', 'Dorsal Mantle Length', 'Dorsal Mantle Length', null, null, null, null, n [...] + .put("CDML", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499471278#0.425988764036447', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'CDML', 1, null, 'Curved Dorsal Mantle Length', 'Curved Dorsal Mantle Length', 'Curved Dorsal Mantle Length', nu [...] + .put("PFL", "INSERT INTO observe_common.sizemeasuretype (topiaid, topiaversion, topiacreatedate, lastupdatedate, code, status, uri, label1, label2, label3, label4, label5, label6, label7, label8, needcomment) VALUES ('fr.ird.observe.entities.referentiel.SizeMeasureType#1433499472220#0.885759855154902', 2, '2015-06-05', '2016-10-18 11:51:54.099140', 'PFL', 1, null, 'Pectoral-Fork Length', 'Pectoral-Fork Length', 'Pectoral-Fork Length', null, null, null, null, n [...] + .build(); + + Iterator<String> iterator = missingLengthMeasureTypeCodes.iterator(); + while (iterator.hasNext()) { + String code = iterator.next(); + String sql = sqlMappingByCode.get(code); + if (sql != null) { + queries.add(sql); + iterator.remove(); + int i = sql.indexOf("'"); + String topiaId = sql.substring(i + 1); + int j = topiaId.indexOf("'"); + topiaId = topiaId.substring(0, j); + if (log.isInfoEnabled()) { + log.info("Add " + code + "::" + topiaId); + } + extraTopiaIdsByCodeBuilder.put(code, topiaId); + } + } + + if (!missingLengthMeasureTypeCodes.isEmpty()) { + + // migration impossible, il manque encore des référentiels + // ce cas ne devrait jamais arrivé + throw new IllegalStateException("Les types de mesure de taille pour les codes suivants :\n\t" + Joiner.on("\n\t").join(missingLengthMeasureTypeCodes) + " sont requis pour la migration mais n'ont pas été trouvés en base."); + } + + } + + ImmutableMap<String, String> extraTopiaIdsByCode = extraTopiaIdsByCodeBuilder.build(); Set<String> invalidCodes = new LinkedHashSet<>(); for (String lengthMeasureType : lengthMeasureTypes) { @@ -120,27 +201,13 @@ public class DataSourceMigrationForVersion_5_1 extends AbstractObserveMigrationC } String finalLengthMeasureType = lengthMeasureType; - String sizeMeasureTypeId = topiaSqlSupport.findSingleResult(new TopiaSqlQuery<String>() { - - @Override - public PreparedStatement prepareQuery(Connection connection) throws SQLException { - return connection.prepareStatement("SELECT topiaId FROM observe_longline.sizeMeasureType WHERE code = '" + finalLengthMeasureType + "'"); - } - - @Override - public String prepareResult(ResultSet set) throws SQLException { - return set.getString(1); - } - }); - - if (sizeMeasureTypeId == null && finalLengthMeasureType.equals("LF")) { - - // For test purposes... + String sizeMeasureTypeId = extraTopiaIdsByCode.get(lengthMeasureType); + if (sizeMeasureTypeId == null) { sizeMeasureTypeId = topiaSqlSupport.findSingleResult(new TopiaSqlQuery<String>() { @Override public PreparedStatement prepareQuery(Connection connection) throws SQLException { - return connection.prepareStatement("SELECT topiaId FROM observe_longline.sizeMeasureType WHERE code = 'TL'"); + return connection.prepareStatement("SELECT topiaId FROM observe_longline.sizeMeasureType WHERE code = '" + finalLengthMeasureType + "'"); } @Override @@ -149,6 +216,23 @@ public class DataSourceMigrationForVersion_5_1 extends AbstractObserveMigrationC } }); } + +// if (sizeMeasureTypeId == null && finalLengthMeasureType.equals("LF")) { +// +// // For test purposes... +// sizeMeasureTypeId = topiaSqlSupport.findSingleResult(new TopiaSqlQuery<String>() { +// +// @Override +// public PreparedStatement prepareQuery(Connection connection) throws SQLException { +// return connection.prepareStatement("SELECT topiaId FROM observe_longline.sizeMeasureType WHERE code = 'TL'"); +// } +// +// @Override +// public String prepareResult(ResultSet set) throws SQLException { +// return set.getString(1); +// } +// }); +// } if (sizeMeasureTypeId == null) { if (log.isWarnEnabled()) { -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.