Revision 2c11da48
Added by Andreas Müller over 4 years ago
| cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java | ||
|---|---|---|
| 49 | 49 |
// success &= testTaxa(); |
| 50 | 50 |
// success &= testTaxonRelations(); |
| 51 | 51 |
// success &= testCommonNames(); //source(s) discuss VLIZ, exact duplicates (except for sources), 3 language names |
| 52 |
// success &= testDistributions(); //lastAction test, sources (OccurrenceSource Tabelle), occurrenceStatus (compare with script), area spellings, 1 long note
|
|
| 53 |
// success &= testNotes(); //ecology notes test, notes on synonyms test, sources (NoteSource Tabelle)
|
|
| 52 |
// success &= testDistributions(); //>1000 duplicates in "dr", sources (OccurrenceSource table), area spellings, 1 long note
|
|
| 53 |
success &= testNotes(); //ecology notes test, link notes test, test trim, few duplicates, sources (NoteSource table)
|
|
| 54 | 54 |
// success &= testAdditionalTaxonSources(); //truncation of some references |
| 55 | 55 |
} catch (Exception e) {
|
| 56 | 56 |
e.printStackTrace(); |
| ... | ... | |
| 132 | 132 |
|
| 133 | 133 |
private boolean testNotesCount() {
|
| 134 | 134 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM notes ");
|
| 135 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Note ");
|
|
| 136 |
return equals("Notes count ", countSrc, countDest, String.valueOf(-1));
|
|
| 135 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Note "
|
|
| 136 |
+ " WHERE NOT (NoteCategoryFk = 4 AND LastAction IS NULL) AND NOT NoteCategoryFk IN (22,23,24) "); |
|
| 137 |
boolean result = equals("Notes count ", countSrc, countDest, String.valueOf(-1));
|
|
| 138 |
|
|
| 139 |
countSrc = source.getUniqueInteger("SELECT count(*) FROM tu "
|
|
| 140 |
+ " WHERE (tu_marine IS NOT NULL OR tu_brackish IS NOT NULL OR tu_fresh IS NOT NULL OR tu_terrestrial IS NOT NULL) " |
|
| 141 |
+ " AND tu.id <> 147415 "); |
|
| 142 |
countDest = destination.getUniqueInteger("SELECT count(*) FROM Note "
|
|
| 143 |
+ " WHERE (NoteCategoryFk = 4 AND LastAction IS NULL) "); |
|
| 144 |
result &= equals("Notes ecology count ", countSrc, countDest, String.valueOf(-1));
|
|
| 145 |
|
|
| 146 |
countSrc = source.getUniqueInteger("SELECT count(*) FROM links ");
|
|
| 147 |
countDest = destination.getUniqueInteger("SELECT count(*) FROM Note "
|
|
| 148 |
+ " WHERE NoteCategoryFk IN (22,23,24) "); |
|
| 149 |
result &= equals("Notes link count ", countSrc, countDest, String.valueOf(-1));
|
|
| 150 |
|
|
| 151 |
return result; |
|
| 137 | 152 |
} |
| 138 | 153 |
|
| 139 | 154 |
private boolean testDistributionCount() {
|
| ... | ... | |
| 300 | 315 |
} |
| 301 | 316 |
} |
| 302 | 317 |
if(!id.equals(srcId)){
|
| 303 |
logger.warn("SourceIDs are not equal: id1: " +id + ", id2: " + srcId);
|
|
| 318 |
logger.warn("Last Action SourceIDs are not equal: id: " +id + ", la-id: " + srcId);
|
|
| 304 | 319 |
} |
| 305 | 320 |
String destStr = destRs.getString("LastAction");
|
| 306 | 321 |
success &= equals(table + " SpeciesExpertName", srcRsLastAction.getString("ExpertName"), destRs.getString("SpeciesExpertName"), id); //mapping ExpertName => SpeciesExpertName according to SQL script
|
| ... | ... | |
| 368 | 383 |
return success; |
| 369 | 384 |
} |
| 370 | 385 |
|
| 371 |
|
|
| 372 | 386 |
private boolean testSingleAdditionalTaxonSources(int n) throws SQLException {
|
| 373 | 387 |
boolean success = true; |
| 374 | 388 |
ResultSet srcRs = source.getResultSet("SELECT CAST(tu.id as nvarchar(20)) tuId, MN.*, s.*, su.sourceuse_name "
|
| ... | ... | |
| 416 | 430 |
+ " LEFT JOIN NoteCategory cat ON cat.NoteCategoryId = no.NoteCategoryFk " |
| 417 | 431 |
+ " LEFT JOIN Language l ON l.LanguageId = no.LanguageFk " |
| 418 | 432 |
+ " WHERE t." + origErms |
| 433 |
+ " AND NOT (NoteCategoryFk = 4 AND no.LastAction IS NULL) AND NOT NoteCategoryFk IN (22,23,24) " |
|
| 419 | 434 |
+ " ORDER BY t.IdInSource, no.NoteCategoryCache, Note_1 "); |
| 420 | 435 |
int count = 0; |
| 421 | 436 |
ResultSet srcRsLastAction = source.getResultSet(""
|
| ... | ... | |
| 442 | 457 |
boolean success = equals("Note taxonID ", "tu_id: " + String.valueOf(srcRs.getInt("tuId")), destRs.getString("IdInSource"), id);
|
| 443 | 458 |
success &= equals("Note Note_1 ", srcRs.getString("note"), destRs.getString("Note_1"), id);
|
| 444 | 459 |
success &= isNull("Note_2", destRs);
|
| 445 |
success &= equals("Note category ", srcRs.getString("type"), destRs.getString("NoteCategoryCache"), id);
|
|
| 460 |
success &= equals("Note category cache", normalizeNoteCatCache(srcRs.getString("type")), destRs.getString("NoteCategoryCache"), id);
|
|
| 446 | 461 |
success &= equals("Note language ", srcRs.getString("LanName"), destRs.getString("Language"), id);
|
| 447 |
|
|
| 448 |
//TODO |
|
| 449 |
//SpeciesExpertGUID, SpeciesExpertName, LastAction, LastActionDate |
|
| 462 |
success &= isNull("Region", destRs);
|
|
| 463 |
success &= isNull("SpeciesExpertGUID", destRs);
|
|
| 464 |
//SpeciesExpertName, LastAction, LastActionDate handled in separate method |
|
| 465 |
//complete |
|
| 450 | 466 |
return success; |
| 451 | 467 |
} |
| 452 | 468 |
|
| 469 |
private String normalizeNoteCatCache(String string) {
|
|
| 470 |
return StringUtils.capitalize(string) |
|
| 471 |
.replace("Original Combination", "Original combination")
|
|
| 472 |
.replace("Taxonomic remark", "Taxonomic Remark");
|
|
| 473 |
} |
|
| 474 |
|
|
| 453 | 475 |
private boolean testSingleDistributions(int n) throws SQLException {
|
| 454 | 476 |
boolean success = true; |
| 455 | 477 |
ResultSet srcRs = source.getResultSet("SELECT CAST(ISNULL(tu.tu_accfinal, tu.id) as nvarchar(20)) tuId, gu.gazetteer_id, dr.*, gu.id guId, gu.gu_name "
|
| ... | ... | |
| 473 | 495 |
int count = 0; |
| 474 | 496 |
while (srcRs.next() && destRs.next()){
|
| 475 | 497 |
success &= testSingleDistribution(srcRs, destRs); |
| 498 |
//there are >1000 duplicates in dr, therefore this creates lots of warnings |
|
| 476 | 499 |
success &= testLastAction(srcRsLastAction, destRs, String.valueOf(srcRs.getInt("id")), "Distribution");
|
| 477 | 500 |
count++; |
| 478 | 501 |
} |
| ... | ... | |
| 486 | 509 |
success &= equals("Distribution gazetteer_id ", srcRs.getString("gazetteer_id"), destRs.getString("AreaERMSGazetteerId"), id);
|
| 487 | 510 |
success &= equals("Distribution area name ", srcRs.getString("gu_name"), destRs.getString("AreaName"), id);
|
| 488 | 511 |
success &= equals("Distribution area name cache", srcRs.getString("gu_name"), destRs.getString("AreaNameCache"), id);
|
| 489 |
//TODO OccurrenceStatusFk
|
|
| 490 |
//TODO OccurrenceStatusCache
|
|
| 512 |
success &= equals("Distribution OccurrenceStatusFk", 1, destRs.getInt("OccurrenceStatusFk"), id);
|
|
| 513 |
success &= equals("Distribution OccurrenceStatusCache", "Present", destRs.getString("OccurrenceStatusCache"), id);
|
|
| 491 | 514 |
//TODO see comments |
| 492 |
// success &= isNull("SourceFk", destRs); //sources should be moved to extra table only, check with script and PESI 2014 (=> has values for ERMS)
|
|
| 493 |
// success &= isNull("SourceNameCache", destRs); //sources should be moved to extra table, check with script and PESI 2014 (=> has values for ERMS)
|
|
| 515 |
success &= isNull("SourceFk", destRs); //sources should be moved to extra table only, check with script and PESI 2014 (=> has values for ERMS)
|
|
| 516 |
success &= isNull("SourceNameCache", destRs); //sources should be moved to extra table, check with script and PESI 2014 (=> has values for ERMS)
|
|
| 494 | 517 |
success &= equals("Distribution notes ", srcRs.getString("note"), destRs.getString("Notes"), id);
|
| 495 | 518 |
success &= isNull("SpeciesExpertGUID", destRs); //SpeciesExpertGUID does not exist in ERMS
|
| 496 | 519 |
//SpeciesExpertName,LastAction,LastActionDate handled in separate method |
Also available in: Unified diff
ref #8508 latest changes for erms pipeline validation