Revision 9ec5ede2
Added by Andreas Müller over 4 years ago
| cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java | ||
|---|---|---|
| 31 | 31 |
private static final Logger logger = Logger.getLogger(PesiErmsValidator.class); |
| 32 | 32 |
|
| 33 | 33 |
private static final Source defaultSource = PesiSources.PESI2019_ERMS(); |
| 34 |
private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_ERMS2PESI_2();
|
|
| 34 |
private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_ERMS2PESI(); |
|
| 35 | 35 |
|
| 36 | 36 |
private Source source = defaultSource; |
| 37 | 37 |
private Source destination = defaultDestination; |
| ... | ... | |
| 44 | 44 |
success &= testReferences(); |
| 45 | 45 |
success &= testTaxa(); |
| 46 | 46 |
success &= testTaxonRelations(); |
| 47 |
success &= testCommonNames(); |
|
| 47 | 48 |
} catch (Exception e) {
|
| 48 | 49 |
e.printStackTrace(); |
| 49 | 50 |
success = false; |
| ... | ... | |
| 52 | 53 |
System.out.println("end validation " + (success? "":"NOT ") + "successful.");
|
| 53 | 54 |
} |
| 54 | 55 |
|
| 56 |
private boolean testCommonNames() throws SQLException {
|
|
| 57 |
boolean success = testCommonNameCount(); |
|
| 58 |
if (success){
|
|
| 59 |
success &= testSingleCommonNames(source.getUniqueInteger("SELECT count(*) FROM vernaculars "));
|
|
| 60 |
} |
|
| 61 |
return success; |
|
| 62 |
} |
|
| 63 |
|
|
| 55 | 64 |
private boolean testTaxonRelations() {
|
| 56 | 65 |
boolean success = true; |
| 57 | 66 |
return success; |
| ... | ... | |
| 73 | 82 |
return success; |
| 74 | 83 |
} |
| 75 | 84 |
|
| 85 |
private boolean testCommonNameCount() {
|
|
| 86 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM vernaculars ");
|
|
| 87 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM CommonName ");
|
|
| 88 |
return equals("CommonName count ", countSrc, countDest, String.valueOf(-1));
|
|
| 89 |
} |
|
| 90 |
|
|
| 76 | 91 |
private boolean testTaxaCount() {
|
| 77 | 92 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM tu ");
|
| 78 | 93 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon ");
|
| 79 |
return equals("Taxon count ", countSrc, countDest, -1);
|
|
| 94 |
return equals("Taxon count ", countSrc, countDest, String.valueOf(-1));
|
|
| 80 | 95 |
} |
| 81 | 96 |
|
| 82 | 97 |
private boolean testSingleTaxa(int n) throws SQLException {
|
| ... | ... | |
| 95 | 110 |
ResultSet destRS = destination.getResultSet("SELECT t.*, type.IdInSource typeSourceId "
|
| 96 | 111 |
+ " FROM Taxon t " |
| 97 | 112 |
+ " LEFT JOIN Taxon type ON type.TaxonId = t.TypeNameFk" |
| 98 |
+ " WHERE t.OriginalDB = 'erms' ORDER BY t.IdInSource"); |
|
| 113 |
+ " WHERE t.OriginalDB = 'erms' " |
|
| 114 |
+ " ORDER BY t.IdInSource"); |
|
| 99 | 115 |
ResultSet srcRsLastAction = source.getResultSet(""
|
| 100 | 116 |
+ " SELECT t.id, s.sessiondate, a.action_name " |
| 101 | 117 |
+ " FROM tu t " |
| ... | ... | |
| 106 | 122 |
int i = 0; |
| 107 | 123 |
while (srcRS.next() && destRS.next()){
|
| 108 | 124 |
success &= testSingleTaxon(srcRS, destRS); |
| 109 |
success &= testTaxonLastAction(srcRsLastAction, destRS, srcRS.getInt("id"));
|
|
| 125 |
success &= testTaxonLastAction(srcRsLastAction, destRS, String.valueOf(srcRS.getInt("id")));
|
|
| 110 | 126 |
i++; |
| 111 | 127 |
} |
| 112 |
success &= equals("Taxon count for single compare", n, i, -1);
|
|
| 128 |
success &= equals("Taxon count for single compare", n, i, String.valueOf(-1));
|
|
| 113 | 129 |
return success; |
| 114 | 130 |
} |
| 115 | 131 |
|
| 116 | 132 |
|
| 117 | 133 |
private boolean testSingleTaxon(ResultSet srcRS, ResultSet destRS) throws SQLException {
|
| 118 |
int id = srcRS.getInt("id");
|
|
| 134 |
String id = String.valueOf(srcRS.getInt("id"));
|
|
| 119 | 135 |
boolean success = equals("Taxon ID", "tu_id: " + srcRS.getInt("id"), destRS.getString("IdInSource"), id);
|
| 120 | 136 |
//TODO SourceFk |
| 121 | 137 |
// success &= compareKingdom("Taxon kingdom", srcRS, destRS, id);
|
| ... | ... | |
| 137 | 153 |
Integer orgigTypeNameFk = nullSafeInt(srcRS, "tu_typetaxon"); |
| 138 | 154 |
success &= equals("Taxon TypeNameFk", orgigTypeNameFk == null? null : "tu_id: " + orgigTypeNameFk, destRS.getString("typeSourceId"), id);
|
| 139 | 155 |
//TODO success &= equals("Taxon TypeFullNameCache", CdmUtils.concat(" ", srcRS.getString("typename"), srcRS.getString("typeauthor")), destRS.getString("TypeFullNameCache"), id);
|
| 140 |
success &= equals("Taxon QualityStatusFK", nullSafeInt(srcRS, "tu_qualitystatus"),nullSafeInt( destRS,"QualityStatusFk"), id);
|
|
| 156 |
success &= equals("Taxon QualityStatusFK", nullSafeInt(srcRS, "tu_qualitystatus"),nullSafeInt( destRS,"QualityStatusFk"), String.valueOf(id));
|
|
| 141 | 157 |
success &= equals("Taxon QualityStatusCache", srcRS.getString("qualitystatus_name"), destRS.getString("QualityStatusCache"), id);
|
| 142 | 158 |
//TODO TreeIndex |
| 143 |
success &= equals("Taxon FossilStatusFk", nullSafeInt(srcRS, "tu_fossil"),nullSafeInt( destRS,"FossilStatusFk"), id);
|
|
| 159 |
success &= equals("Taxon FossilStatusFk", nullSafeInt(srcRS, "tu_fossil"),nullSafeInt( destRS,"FossilStatusFk"), String.valueOf(id));
|
|
| 144 | 160 |
success &= equals("Taxon FossilStatusCache", srcRS.getString("fossil_name"), destRS.getString("FossilStatusCache"), id);
|
| 145 | 161 |
success &= equals("Taxon GUID", srcRS.getString("GUID"), destRS.getString("GUID"), id);
|
| 146 | 162 |
//in 2014 GUID and DerivedFromGuid was always same for ERMS |
| 147 | 163 |
success &= equals("Taxon DerivedFromGuid", srcRS.getString("GUID"), destRS.getString("DerivedFromGuid"), id);
|
| 148 |
//TODO DerivedFromGuid |
|
| 149 | 164 |
//TODO ExpertGUID |
| 150 | 165 |
//TODO ExpertName |
| 151 | 166 |
//TODO SpeciesExpertGUID |
| 152 | 167 |
//TODO SpeciesExpertName |
| 153 | 168 |
//TODO CacheCitation |
| 154 | 169 |
//LastAction(Date) handled in separate method |
| 155 |
//TODO GUID2
|
|
| 156 |
//TODO DerivedFromGuid2
|
|
| 170 |
success &= isNull("GUID2", destRS); //only relevant after merge
|
|
| 171 |
success &= isNull("DerivedFromGuid2", destRS); //only relevant after merge
|
|
| 157 | 172 |
return success; |
| 158 | 173 |
} |
| 159 | 174 |
|
| 160 |
|
|
| 161 |
|
|
| 162 |
/** |
|
| 163 |
* @param srcRS |
|
| 164 |
* @return |
|
| 165 |
* @throws SQLException |
|
| 166 |
*/ |
|
| 167 | 175 |
private String srcFullName(ResultSet srcRs) throws SQLException {
|
| 168 | 176 |
String result = null; |
| 169 | 177 |
String epi = srcRs.getString("tu_name");
|
| 170 | 178 |
epi = " a" + epi; |
| 171 | 179 |
String display = srcRs.getString("tu_displayname");
|
| 172 | 180 |
String sp = srcRs.getString("tu_sp");
|
| 173 |
if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#1#")){ //homonym, animal
|
|
| 181 |
if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#2#")){ //homonym, animal
|
|
| 174 | 182 |
result = srcRs.getString("tu_displayname").replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" ";
|
| 175 | 183 |
}else{
|
| 176 | 184 |
result = CdmUtils.concat(" ", srcRs.getString("tu_displayname"), srcRs.getString("tu_authority"));
|
| ... | ... | |
| 178 | 186 |
return result; |
| 179 | 187 |
} |
| 180 | 188 |
|
| 181 |
private boolean testTaxonLastAction(ResultSet srcRs, ResultSet destRs, int id) throws SQLException {
|
|
| 189 |
private boolean testTaxonLastAction(ResultSet srcRs, ResultSet destRs, String id) throws SQLException {
|
|
| 182 | 190 |
boolean success = true; |
| 183 | 191 |
while (srcRs.next()){
|
| 184 | 192 |
int srcId = srcRs.getInt("id");
|
| 185 |
if (id == srcId){
|
|
| 193 |
if (id.equals(String.valueOf(srcId))){
|
|
| 186 | 194 |
break; |
| 187 | 195 |
} |
| 188 | 196 |
} |
| ... | ... | |
| 192 | 200 |
return success; |
| 193 | 201 |
} |
| 194 | 202 |
|
| 195 |
private boolean compareKingdom(String messageStart, ResultSet srcRS, ResultSet destRS, int id) throws SQLException {
|
|
| 203 |
private boolean compareKingdom(String messageStart, ResultSet srcRS, ResultSet destRS, String id) throws SQLException {
|
|
| 196 | 204 |
String strSrc = srcRS.getString("acc_sp");
|
| 197 | 205 |
if (strSrc == null){
|
| 198 | 206 |
strSrc = srcRS.getString("tu_sp");
|
| ... | ... | |
| 212 | 220 |
} |
| 213 | 221 |
} |
| 214 | 222 |
|
| 223 |
private boolean testSingleCommonNames(int n) throws SQLException {
|
|
| 224 |
boolean success = true; |
|
| 225 |
ResultSet srcRs = source.getResultSet("SELECT v.*, l.LanName, tu.id tuId "
|
|
| 226 |
+ " FROM vernaculars v LEFT JOIN tu ON v.tu_id = tu.id LEFT JOIN languages l ON l.LanID = v.lan_id " |
|
| 227 |
+ " ORDER BY CAST(tu.id as nvarchar(20)), tu.id "); |
|
| 228 |
ResultSet destRs = destination.getResultSet("SELECT cn.*, t.IdInSource, l.ISO639_2 "
|
|
| 229 |
+ " FROM CommonName cn INNER JOIN Taxon t ON t.TaxonId = cn.TaxonFk INNER JOIN Language l ON l.LanguageId = cn.LanguageFk " |
|
| 230 |
+ " WHERE t.OriginalDB = 'erms' " |
|
| 231 |
+ " ORDER BY t.IdInSource, l.ISO639_2"); |
|
| 232 |
int count = 0; |
|
| 233 |
while (srcRs.next() && destRs.next()){
|
|
| 234 |
success &= testSingleCommonName(srcRs, destRs); |
|
| 235 |
count++; |
|
| 236 |
} |
|
| 237 |
success &= equals("Common name count differs", n, count, "-1");
|
|
| 238 |
return success; |
|
| 239 |
} |
|
| 240 |
|
|
| 241 |
private boolean testSingleCommonName(ResultSet srcRs, ResultSet destRs) throws SQLException {
|
|
| 242 |
String id = String.valueOf(srcRs.getInt("tuId") + "-" + srcRs.getString("lan_id"));
|
|
| 243 |
boolean success = equals("Common name taxonID ", "tu_id: " + String.valueOf(srcRs.getInt("tuId")), destRs.getString("IdInSource"), id);
|
|
| 244 |
success &= equals("Common name languageID ", srcRs.getString("lan_id"), destRs.getString("ISO639_2"), id);
|
|
| 245 |
success &= equals("CommonName name ", srcRs.getString("vername"), destRs.getString("CommonName"), id);
|
|
| 246 |
//TODO success = equals("CommonName language code ", srcRs.getString("lan_id"), destRs.getString("LanguageFk"), id);
|
|
| 247 |
success = equals("CommonName LanguageCache ", srcRs.getString("LanName"), destRs.getString("LanguageCache"), id);
|
|
| 248 |
success &= isNull("Region", destRs); //region does not seem to exist in ERMS
|
|
| 249 |
|
|
| 250 |
//TODO |
|
| 251 |
return success; |
|
| 252 |
} |
|
| 253 |
|
|
| 215 | 254 |
private boolean testSingleReferences() throws SQLException {
|
| 216 | 255 |
boolean success = true; |
| 217 | 256 |
ResultSet srcRS = source.getResultSet("SELECT s.* FROM sources s ORDER BY s.id ");
|
| ... | ... | |
| 224 | 263 |
} |
| 225 | 264 |
|
| 226 | 265 |
private boolean testSingleReference(ResultSet srcRS, ResultSet destRS) throws SQLException {
|
| 227 |
int id = srcRS.getInt("id");
|
|
| 266 |
String id = String.valueOf(srcRS.getInt("id"));
|
|
| 228 | 267 |
boolean success = equals("Reference ID ", srcRS.getInt("id"), destRS.getInt("RefIdInSource"), id);
|
| 229 | 268 |
success &= equals("Reference IMIS_id ", srcRS.getString("imis_id"), destRS.getString("IMIS_Id"), id);
|
| 230 | 269 |
//TODO success &= equals("Reference SourceCategoryFk ", srcRS.getString("source_type"), destRS.getInt("SourceCategoryFk"), id);
|
| ... | ... | |
| 232 | 271 |
success &= equals("Reference name ", srcRS.getString("source_name"), destRS.getString("Name"), id);
|
| 233 | 272 |
success &= equals("Reference abstract ", srcRS.getString("source_abstract"), destRS.getString("Abstract"), id);
|
| 234 | 273 |
success &= equals("Reference title ", srcRS.getString("source_title"), destRS.getString("Title"), id);
|
| 235 |
//TODO success &= equals("Reference author string ", srcRS.getString("source_author"), destRS.getString("AuthorString"), id);
|
|
| 274 |
success &= equals("Reference author string ", srcRS.getString("source_author"), destRS.getString("AuthorString"), id);
|
|
| 236 | 275 |
success &= equals("Reference year ", normalizeYear(srcRS.getString("source_year")), destRS.getString("RefYear"), id);
|
| 237 | 276 |
success &= isNull("NomRefCache", destRS); //for ERMS no other value was found in 2014 value
|
| 238 | 277 |
success &= equals("Reference link ", srcRS.getString("source_link"), destRS.getString("Link"), id);
|
| ... | ... | |
| 245 | 284 |
private boolean testReferenceCount() {
|
| 246 | 285 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM sources ");
|
| 247 | 286 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Source s WHERE s.OriginalDB = 'erms'"); // +1 for the source reference "erms" but this has no OriginalDB
|
| 248 |
boolean success = equals("Reference count ", countSrc, countDest, -1);
|
|
| 287 |
boolean success = equals("Reference count ", countSrc, countDest, "-1");
|
|
| 249 | 288 |
return success; |
| 250 | 289 |
} |
| 251 | 290 |
|
| ... | ... | |
| 272 | 311 |
} |
| 273 | 312 |
} |
| 274 | 313 |
|
| 275 |
private boolean equals(String messageStart, Timestamp srcDate, Timestamp destDate, int id) {
|
|
| 314 |
private boolean equals(String messageStart, Timestamp srcDate, Timestamp destDate, String id) {
|
|
| 276 | 315 |
if (!CdmUtils.nullSafeEqual(srcDate, destDate)){
|
| 277 | 316 |
String message = id + ": " + messageStart + " must be equal, but was not.\n Source: "+ srcDate + "; Destination: " + destDate; |
| 278 | 317 |
logger.warn(message); |
| ... | ... | |
| 283 | 322 |
} |
| 284 | 323 |
} |
| 285 | 324 |
|
| 286 |
private boolean equals(String messageStart, Integer nSrc, Integer nDest, int id) {
|
|
| 287 |
String strId = id == -1? "": (id+ ": ");
|
|
| 325 |
private boolean equals(String messageStart, Integer nSrc, Integer nDest, String id) {
|
|
| 326 |
String strId = id.equals("-1")? "": (id+ ": ");
|
|
| 288 | 327 |
if (!CdmUtils.nullSafeEqual(nSrc,nDest)){
|
| 289 | 328 |
String message = strId+ messageStart + " must be equal, but was not.\n Source: "+ nSrc + "; Destination: " + nDest; |
| 290 | 329 |
logger.warn(message); |
| ... | ... | |
| 295 | 334 |
} |
| 296 | 335 |
} |
| 297 | 336 |
|
| 298 |
private boolean equals(String messageStart, String strSrc, String strDest, int id) {
|
|
| 337 |
private boolean equals(String messageStart, String strSrc, String strDest, String id) {
|
|
| 299 | 338 |
if (StringUtils.isBlank(strSrc)){
|
| 300 | 339 |
strSrc = null; |
| 301 | 340 |
}else{
|
Also available in: Unified diff
ref #8508 latest changes to ERMS pipeline validator