Revision 34743ac7
Added by Andreas Müller over 4 years ago
| cdm-pesi/src/main/java/eu/etaxonomy/cdm/app/pesi/validate/PesiEuroMedValidator.java | ||
|---|---|---|
| 19 | 19 |
|
| 20 | 20 |
import eu.etaxonomy.cdm.app.common.CdmDestinations; |
| 21 | 21 |
import eu.etaxonomy.cdm.app.common.PesiDestinations; |
| 22 |
import eu.etaxonomy.cdm.app.pesi.EuroMedSourceActivator; |
|
| 22 | 23 |
import eu.etaxonomy.cdm.common.CdmUtils; |
| 23 | 24 |
import eu.etaxonomy.cdm.common.UTF8; |
| 24 | 25 |
import eu.etaxonomy.cdm.database.ICdmDataSource; |
| ... | ... | |
| 34 | 35 |
* @author a.mueller |
| 35 | 36 |
* @since 08.10.2019 |
| 36 | 37 |
*/ |
| 37 |
public class PesiEuroMedValidator {
|
|
| 38 |
public class PesiEuroMedValidator extends PesiValidatorBase {
|
|
| 38 | 39 |
|
| 39 | 40 |
private static final Logger logger = Logger.getLogger(PesiEuroMedValidator.class); |
| 40 | 41 |
|
| ... | ... | |
| 79 | 80 |
|
| 80 | 81 |
private boolean testAdditionalTaxonSources() throws SQLException {
|
| 81 | 82 |
if (!doAdditionalTaxonSources){
|
| 83 |
System.out.println("Ignore validate additional taxon sources");
|
|
| 82 | 84 |
return true; |
| 83 | 85 |
} |
| 84 | 86 |
System.out.println("Start validate additional taxon sources");
|
| ... | ... | |
| 91 | 93 |
|
| 92 | 94 |
private boolean testNotes() throws SQLException {
|
| 93 | 95 |
if (!doNotes){
|
| 96 |
System.out.println("Ignore validate notes");
|
|
| 94 | 97 |
return true; |
| 95 | 98 |
} |
| 96 | 99 |
System.out.println("Start validate notes");
|
| ... | ... | |
| 103 | 106 |
|
| 104 | 107 |
private boolean testDistributions() throws SQLException {
|
| 105 | 108 |
if (!doDistributions){
|
| 109 |
System.out.println("Ignore validate distributions");
|
|
| 106 | 110 |
return true; |
| 107 | 111 |
} |
| 108 | 112 |
System.out.println("Start validate distributions");
|
| ... | ... | |
| 115 | 119 |
|
| 116 | 120 |
private boolean testCommonNames() throws SQLException {
|
| 117 | 121 |
if (!doCommonNames){
|
| 122 |
System.out.println("Ignore validate common names");
|
|
| 118 | 123 |
return true; |
| 119 | 124 |
} |
| 120 | 125 |
System.out.println("Start validate common names");
|
| ... | ... | |
| 129 | 134 |
int countIncludedIns; |
| 130 | 135 |
private boolean testTaxonRelations() throws SQLException {
|
| 131 | 136 |
if (!doTaxRels){
|
| 137 |
System.out.println("Ignore validate taxon relations");
|
|
| 132 | 138 |
return true; |
| 133 | 139 |
} |
| 134 | 140 |
System.out.println("Start validate taxon relations");
|
| ... | ... | |
| 238 | 244 |
|
| 239 | 245 |
private boolean testTaxa() throws SQLException {
|
| 240 | 246 |
if (!doTaxa){
|
| 247 |
System.out.println("Ignore validate taxa");
|
|
| 241 | 248 |
return true; |
| 242 | 249 |
} |
| 243 | 250 |
System.out.println("Start validate taxa");
|
| ... | ... | |
| 251 | 258 |
String countReferencesStr = "SELECT count(*) FROM reference "; |
| 252 | 259 |
private boolean testReferences() throws SQLException {
|
| 253 | 260 |
if (!doReferences){
|
| 261 |
System.out.println("Ignore validate references");
|
|
| 254 | 262 |
return true; |
| 255 | 263 |
} |
| 256 | 264 |
System.out.println("Start validate references");
|
| ... | ... | |
| 307 | 315 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon t WHERE "+ destTaxonFilter);
|
| 308 | 316 |
boolean result = equals("Taxon count ", countSrc, countDest, String.valueOf(-1));
|
| 309 | 317 |
return result; |
| 310 |
}
|
|
| 318 |
} |
|
| 311 | 319 |
|
| 312 | 320 |
private boolean testSingleTaxa(int n) throws SQLException {
|
| 313 | 321 |
boolean success = true; |
| ... | ... | |
| 345 | 353 |
+ " tb.DTYPE, tb.updated, tb.created " //for duplicates caused by >1 name status |
| 346 | 354 |
+ " ORDER BY tid, GUID, lastActionDate "); |
| 347 | 355 |
ResultSet destRS = destination.getResultSet("SELECT t.*, "
|
| 348 |
+ " pt.IdInSource parentSourceId, " //not needed |
|
| 356 |
+ " pt.treeIndex pTreeIndex, pt.IdInSource parentSourceId, " //not needed
|
|
| 349 | 357 |
+ " s.Name as sourceName, type.IdInSource typeSourceId, r.Rank " |
| 350 | 358 |
+ " FROM Taxon t " |
| 351 | 359 |
+ " LEFT JOIN Taxon pt ON pt.TaxonId = t.ParentTaxonFk " |
| ... | ... | |
| 368 | 376 |
String id = String.valueOf(srcRS.getInt("tid"));
|
| 369 | 377 |
//TODO decide, according to SQL it also contains the taxon UUID, but in PESI2014 backup I can't find this |
| 370 | 378 |
boolean success = equals("Taxon ID", "NameId: " + srcRS.getInt("tid"), destRS.getString("IdInSource"), id);
|
| 371 |
success &= equals("Taxon source", srcRS.getString("secTitle"), destRS.getString("sourceName"), id);
|
|
| 379 |
success &= equals("Taxon source", makeSource(srcRS), destRS.getString("sourceName"), id);
|
|
| 372 | 380 |
|
| 373 | 381 |
success &= equals("Taxon kingdomFk", "3", destRS.getString("KingdomFk"), id);
|
| 374 | 382 |
//difficult to test success &= equals("Taxon rank fk", srcRS.getString("rank_id"), destRS.getString("RankFk"), id);
|
| ... | ... | |
| 399 | 407 |
//according to SQL always constant, could be changed in future |
| 400 | 408 |
success &= equals("Taxon QualityStatusFK", 2, nullSafeInt( destRS,"QualityStatusFk"), String.valueOf(id));
|
| 401 | 409 |
success &= equals("Taxon QualityStatusCache", "Added by Database Management Team", destRS.getString("QualityStatusCache"), id);
|
| 402 |
//TODO TreeIndex
|
|
| 403 |
success &= isNull("FossilStatusFk", destRS);
|
|
| 404 |
success &= isNull("FossilStatusCache", destRS);
|
|
| 410 |
success &= testTreeIndex(destRS, "TreeIndex", "pTreeIndex", id);
|
|
| 411 |
success &= isNull("FossilStatusFk", destRS, id);
|
|
| 412 |
success &= isNull("FossilStatusCache", destRS, id);
|
|
| 405 | 413 |
success &= equals("Taxon GUID", srcRS.getString("GUID"), destRS.getString("GUID"), id);
|
| 406 | 414 |
success &= equals("Taxon DerivedFromGuid", srcRS.getString("GUID"), destRS.getString("DerivedFromGuid"), id); //according to SQL script GUID and DerivedFromGuid are always the same, according to 2014DB this is even true for all databases
|
| 407 |
success &= isNull("ExpertGUID", destRS); //according to SQL + PESI2014
|
|
| 415 |
success &= isNull("ExpertGUID", destRS, id); //according to SQL + PESI2014
|
|
| 408 | 416 |
//FIXME success &= equals("Taxon ExpertName", srcRS.getString("secTitle"), destRS.getString("ExpertName"), id);
|
| 409 | 417 |
//FIXME success &= isNull("SpeciesExpertGUID", destRS);
|
| 410 |
success &= equals("Taxon SpeciesExpertName", srcRS.getString("secTitle"), destRS.getString("SpeciesExpertName"), id);
|
|
| 418 |
//FIXME success &= equals("Taxon SpeciesExpertName", srcRS.getString("secTitle"), destRS.getString("SpeciesExpertName"), id);
|
|
| 411 | 419 |
//FIXME !! success &= equals("Taxon cache citation", srcRS.getString("secTitle"), destRS.getString("CacheCitation"), id);
|
| 412 | 420 |
success &= equals("Taxon Last Action", srcRS.getString("lastAction"), destRS.getString("LastAction"), id);
|
| 413 | 421 |
success &= equals("Taxon Last Action Date", srcRS.getTimestamp("lastActionDate"), destRS.getTimestamp("LastActionDate"), id);
|
| 414 | 422 |
|
| 415 |
success &= isNull("GUID2", destRS); //only relevant after merge
|
|
| 416 |
success &= isNull("DerivedFromGuid2", destRS); //only relevant after merge
|
|
| 423 |
success &= isNull("GUID2", destRS, id); //only relevant after merge
|
|
| 424 |
success &= isNull("DerivedFromGuid2", destRS, id); //only relevant after merge
|
|
| 417 | 425 |
return success; |
| 418 | 426 |
} |
| 419 | 427 |
|
| 428 |
private String makeSource(ResultSet srcRs) throws SQLException {
|
|
| 429 |
String secStr = srcRs.getString("secTitle");
|
|
| 430 |
if (secStr == null){
|
|
| 431 |
return EuroMedSourceActivator.sourceReferenceTitle; |
|
| 432 |
}else{
|
|
| 433 |
return secStr; |
|
| 434 |
} |
|
| 435 |
} |
|
| 436 |
|
|
| 420 | 437 |
private String makeAuthorship(ResultSet srcRs) throws SQLException {
|
| 421 | 438 |
boolean isMisapplied = isMisapplied(srcRs); |
| 422 | 439 |
if (isMisapplied){
|
| ... | ... | |
| 451 | 468 |
.replaceAll("^, ", "")
|
| 452 | 469 |
.replaceAll("(, |^)"+nameStatus+"$", "")
|
| 453 | 470 |
.replaceAll("\\[as \".*\"\\]", "")
|
| 471 |
.replaceAll(", nom\\. cons\\., nom\\. altern\\.$", "") //single case with 2 nom. status
|
|
| 454 | 472 |
.trim(); |
| 455 | 473 |
} |
| 456 | 474 |
return result; |
| ... | ... | |
| 640 | 658 |
success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_TAXONOMICALLY_INCLUDED_IN, destRS.getInt("RelTaxonQualifierFk"), id);
|
| 641 | 659 |
success &= equals("Taxon relation qualifier cache", "is taxonomically included in", destRS.getString("RelQualifierCache"), id);
|
| 642 | 660 |
//TODO enable after next import |
| 643 |
success &= isNull("notes", destRS);
|
|
| 661 |
success &= isNull("notes", destRS, id);
|
|
| 644 | 662 |
//complete if no further relations need to added |
| 645 | 663 |
return success; |
| 646 | 664 |
} |
| ... | ... | |
| 706 | 724 |
String id = String.valueOf(srcRs.getInt("tuId") + "-" + srcRs.getString("type"));
|
| 707 | 725 |
boolean success = equals("Note taxonID ", "tu_id: " + String.valueOf(srcRs.getInt("tuId")), destRs.getString("IdInSource"), id);
|
| 708 | 726 |
success &= equals("Note Note_1 ", srcRs.getString("note"), destRs.getString("Note_1"), id);
|
| 709 |
success &= isNull("Note_2", destRs);
|
|
| 727 |
success &= isNull("Note_2", destRs, id);
|
|
| 710 | 728 |
success &= equals("Note category cache", normalizeNoteCatCache(srcRs.getString("type")), destRs.getString("NoteCategoryCache"), id);
|
| 711 | 729 |
success &= equals("Note language ", srcRs.getString("LanName"), destRs.getString("Language"), id);
|
| 712 |
success &= isNull("Region", destRs);
|
|
| 713 |
success &= isNull("SpeciesExpertGUID", destRs);
|
|
| 730 |
success &= isNull("Region", destRs, id);
|
|
| 731 |
success &= isNull("SpeciesExpertGUID", destRs, id);
|
|
| 714 | 732 |
//SpeciesExpertName, LastAction, LastActionDate handled in separate method |
| 715 | 733 |
//complete |
| 716 | 734 |
return success; |
| ... | ... | |
| 756 | 774 |
success &= equals("Distribution area name cache", normalizeDistrArea(srcRs.getString("area")), destRs.getString("AreaNameCache"), id);
|
| 757 | 775 |
success &= equals("Distribution OccurrenceStatusFk", mapStatus(srcRs.getString("statusUuid")), destRs.getInt("OccurrenceStatusFk"), id);
|
| 758 | 776 |
//TODO success &= equals("Distribution OccurrenceStatusCache", "Present", destRs.getString("OccurrenceStatusCache"), id);
|
| 759 |
success &= isNull("SourceFk", destRs); //sources should be moved to extra table only, according to script there were values, but in PESI 2014 values existed only in OccurrenceSource table (for all only E+M records)
|
|
| 760 |
success &= isNull("SourceCache", destRs); //sources should be moved to extra table, see above
|
|
| 777 |
success &= isNull("SourceFk", destRs, id); //sources should be moved to extra table only, according to script there were values, but in PESI 2014 values existed only in OccurrenceSource table (for all only E+M records)
|
|
| 778 |
success &= isNull("SourceCache", destRs, id); //sources should be moved to extra table, see above
|
|
| 761 | 779 |
//TODO success &= equals("Distribution notes ", srcRs.getString("note"), destRs.getString("Notes"), id);
|
| 762 |
success &= isNull("SpeciesExpertGUID", destRs); //SpeciesExpertGUID does not exist in EM and according to script
|
|
| 763 |
success &= isNull("SpeciesExpertName", destRs); //SpeciesExpertName does not exist in EM and according to script
|
|
| 780 |
success &= isNull("SpeciesExpertGUID", destRs, id); //SpeciesExpertGUID does not exist in EM and according to script
|
|
| 781 |
success &= isNull("SpeciesExpertName", destRs, id); //SpeciesExpertName does not exist in EM and according to script
|
|
| 764 | 782 |
success &= equals("Distribution Last Action", srcRs.getString("lastAction"), destRs.getString("LastAction"), id);
|
| 765 | 783 |
success &= equals("Distribution Last Action Date", srcRs.getTimestamp("lastActionDate"), destRs.getTimestamp("LastActionDate"), id);
|
| 766 | 784 |
return success; |
| ... | ... | |
| 850 | 868 |
success &= equals("Common name languageFk ", srcRs.getString("iso"), getLanguageIso(destRs), id);
|
| 851 | 869 |
success = equals("CommonName LanguageCache ", normalizeLang(srcRs.getString("LanName")), destRs.getString("LanguageCache"), id);
|
| 852 | 870 |
//TODO needed? success = equals("CommonName language code ", srcRs.getString("lan_id"), destRs.getString("LanguageFk"), id);
|
| 853 |
success &= isNull("Region", destRs); //region does not seem to exist in ERMS
|
|
| 871 |
success &= isNull("Region", destRs, id); //region does not seem to exist in ERMS
|
|
| 854 | 872 |
//TODO see comments |
| 855 | 873 |
// success &= isNull("SourceFk", destRs); //sources should be moved to extra table, check with PESI 2014
|
| 856 | 874 |
// success &= isNull("SourceNameCache", destRs); //sources should be moved to extra table, check with PESI 2014
|
| 857 |
success &= isNull("SpeciesExpertGUID", destRs); //SpeciesExpertGUID does not exist in ERMS
|
|
| 875 |
success &= isNull("SpeciesExpertGUID", destRs, id); //SpeciesExpertGUID does not exist in ERMS
|
|
| 858 | 876 |
//SpeciesExpertName,LastAction,LastActionDate handled in separate method |
| 859 | 877 |
//complete |
| 860 | 878 |
return success; |
| ... | ... | |
| 902 | 920 |
private boolean testSingleReference(ResultSet srcRS, ResultSet destRS) throws SQLException {
|
| 903 | 921 |
String id = String.valueOf(srcRS.getInt("id"));
|
| 904 | 922 |
boolean success = equals("Reference ID ", srcRS.getInt("id"), destRS.getInt("RefIdInSource"), id);
|
| 905 |
success &= isNull("IMIS_Id", destRS); //for E+M no IMIS id exists
|
|
| 923 |
success &= isNull("IMIS_Id", destRS, id); //for E+M no IMIS id exists
|
|
| 906 | 924 |
success &= equals("Reference SourceCategoryFk ", convertSourceTypeFk(srcRS.getString("refType")), destRS.getInt("SourceCategoryFk"), id);
|
| 907 | 925 |
success &= equals("Reference SourceCategoryCache ", convertSourceTypeCache(srcRS.getString("refType")), destRS.getString("SourceCategoryCache"), id);
|
| 908 | 926 |
success &= equals("Reference name ", srcRS.getString("titleCache"), destRS.getString("Name"), id);
|
| ... | ... | |
| 935 | 953 |
}else if ("SER".equals(sourceType)){
|
| 936 | 954 |
// TODO correct? |
| 937 | 955 |
return PesiTransformer.REF_UNRESOLVED; |
| 938 |
}else if ("i".equals(sourceType)){
|
|
| 939 |
return 12; |
|
| 940 | 956 |
} |
| 941 | 957 |
return null; |
| 942 | 958 |
} |
| ... | ... | |
| 955 | 971 |
return "book"; |
| 956 | 972 |
}else if ("GEN".equals(sourceType)){
|
| 957 | 973 |
return "unresolved"; |
| 958 |
}else if ("i".equals(sourceType)){
|
|
| 959 |
//TODO |
|
| 960 |
return "i"; |
|
| 961 | 974 |
} |
| 962 | 975 |
return null; |
| 963 | 976 |
} |
| ... | ... | |
| 986 | 999 |
return result; |
| 987 | 1000 |
} |
| 988 | 1001 |
|
| 989 |
private boolean isNull(String attrName, ResultSet destRS) throws SQLException {
|
|
| 990 |
Object value = destRS.getObject(attrName); |
|
| 991 |
if (value != null){
|
|
| 992 |
String message = attrName + " was expected to be null but was: " + value.toString(); |
|
| 993 |
logger.warn(message); |
|
| 994 |
return false; |
|
| 995 |
}else{
|
|
| 996 |
logger.info(attrName + " was null as expected"); |
|
| 997 |
return true; |
|
| 998 |
} |
|
| 999 |
} |
|
| 1000 |
|
|
| 1001 | 1002 |
private boolean equals(String messageStart, Timestamp srcDate, Timestamp destDate, String id) {
|
| 1002 | 1003 |
if (!CdmUtils.nullSafeEqual(srcDate, destDate)){
|
| 1003 | 1004 |
LocalDate date1 = srcDate.toLocalDateTime().toLocalDate(); |
| ... | ... | |
| 1028 | 1029 |
} |
| 1029 | 1030 |
} |
| 1030 | 1031 |
|
| 1031 |
private boolean equals(String messageStart, String strSrc, String strDest, String id) {
|
|
| 1032 |
if (StringUtils.isBlank(strSrc)){
|
|
| 1033 |
strSrc = null; |
|
| 1034 |
}else{
|
|
| 1035 |
strSrc = strSrc.trim(); |
|
| 1036 |
} |
|
| 1037 |
//we do not trim strDest here because this should be done during import already. If not it should be shown here |
|
| 1038 |
if (!CdmUtils.nullSafeEqual(strSrc, strDest)){
|
|
| 1039 |
int index = CdmUtils.diffIndex(strSrc, strDest); |
|
| 1040 |
String message = id+ ": " + messageStart + " must be equal, but was not at "+index+".\n Source: "+ strSrc + "\n Destination: " + strDest; |
|
| 1041 |
logger.warn(message); |
|
| 1042 |
return false; |
|
| 1043 |
}else{
|
|
| 1044 |
logger.info(id+ ": " + messageStart + " were equal: " + strSrc); |
|
| 1045 |
return true; |
|
| 1046 |
} |
|
| 1047 |
} |
|
| 1048 |
|
|
| 1049 |
protected Integer nullSafeInt(ResultSet rs, String columnName) throws SQLException {
|
|
| 1050 |
Object intObject = rs.getObject(columnName); |
|
| 1051 |
if (intObject == null){
|
|
| 1052 |
return null; |
|
| 1053 |
}else{
|
|
| 1054 |
return Integer.valueOf(intObject.toString()); |
|
| 1055 |
} |
|
| 1056 |
} |
|
| 1057 |
|
|
| 1058 |
private boolean isNotBlank(String str) {
|
|
| 1059 |
return StringUtils.isNotBlank(str); |
|
| 1060 |
} |
|
| 1061 |
|
|
| 1062 | 1032 |
//** ************* MAIN ********************************************/ |
| 1063 | 1033 |
|
| 1064 |
|
|
| 1065 |
|
|
| 1066 | 1034 |
public static void main(String[] args){
|
| 1067 | 1035 |
PesiEuroMedValidator validator = new PesiEuroMedValidator(); |
| 1068 | 1036 |
validator.invoke(new Source(defaultSource), defaultDestination); |
Also available in: Unified diff
ref #8508, ref #8577 treeindex test for E+M validation and common base class for E+M and Erms validation