Revision 3a180cbe
Added by Andreas Müller over 4 years ago
| cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java | ||
|---|---|---|
| 10 | 10 |
|
| 11 | 11 |
import java.sql.ResultSet; |
| 12 | 12 |
import java.sql.SQLException; |
| 13 |
import java.sql.Timestamp; |
|
| 13 | 14 |
|
| 15 |
import org.apache.commons.lang.StringUtils; |
|
| 14 | 16 |
import org.apache.log4j.Logger; |
| 15 | 17 |
|
| 16 | 18 |
import eu.etaxonomy.cdm.app.pesi.PesiDestinations; |
| ... | ... | |
| 29 | 31 |
private static final Logger logger = Logger.getLogger(PesiErmsValidator.class); |
| 30 | 32 |
|
| 31 | 33 |
private static final Source defaultSource = PesiSources.PESI2019_ERMS(); |
| 32 |
private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_ERMS2PESI(); |
|
| 34 |
private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_ERMS2PESI_2();
|
|
| 33 | 35 |
|
| 34 | 36 |
private Source source = defaultSource; |
| 35 | 37 |
private Source destination = defaultDestination; |
| 36 | 38 |
|
| 37 | 39 |
public void invoke(Source source, Source destination){
|
| 38 | 40 |
boolean success = true; |
| 39 |
this.source = source; |
|
| 40 |
this.destination = destination; |
|
| 41 |
success &= testReferences(); |
|
| 42 |
success &= testTaxa(); |
|
| 43 |
success &= testTaxonRelations(); |
|
| 41 |
try {
|
|
| 42 |
this.source = source; |
|
| 43 |
this.destination = destination; |
|
| 44 |
success &= testReferences(); |
|
| 45 |
success &= testTaxa(); |
|
| 46 |
success &= testTaxonRelations(); |
|
| 47 |
} catch (Exception e) {
|
|
| 48 |
e.printStackTrace(); |
|
| 49 |
success = false; |
|
| 50 |
} |
|
| 44 | 51 |
//TBC |
| 45 | 52 |
System.out.println("end validation " + (success? "":"NOT ") + "successful.");
|
| 46 | 53 |
} |
| ... | ... | |
| 50 | 57 |
return success; |
| 51 | 58 |
} |
| 52 | 59 |
|
| 53 |
private boolean testTaxa() {
|
|
| 54 |
try {
|
|
| 60 |
private boolean testTaxa() throws SQLException {
|
|
| 55 | 61 |
boolean success = testTaxaCount(); |
| 56 | 62 |
if (success){
|
| 57 |
success &= testSingleTaxa(); |
|
| 63 |
success &= testSingleTaxa(source.getUniqueInteger("SELECT count(*) FROM tu "));
|
|
| 58 | 64 |
} |
| 59 | 65 |
return success; |
| 60 |
} catch (Exception e) {
|
|
| 61 |
e.printStackTrace(); |
|
| 62 |
return false; |
|
| 63 |
} |
|
| 64 | 66 |
} |
| 65 | 67 |
|
| 66 |
private boolean testTaxaCount() {
|
|
| 67 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM tu ");
|
|
| 68 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon ");
|
|
| 69 |
return equals("Taxon count ", countSrc, countDest);
|
|
| 70 |
} |
|
| 71 |
|
|
| 72 |
private boolean testReferences() {
|
|
| 73 |
try {
|
|
| 74 |
boolean success = testReferenceCount(); |
|
| 75 |
if (success){
|
|
| 76 |
success &= testSingleReferences(); |
|
| 77 |
} |
|
| 78 |
return success; |
|
| 79 |
} catch (SQLException e) {
|
|
| 80 |
e.printStackTrace(); |
|
| 81 |
return false; |
|
| 68 |
private boolean testReferences() throws SQLException {
|
|
| 69 |
boolean success = testReferenceCount(); |
|
| 70 |
if (success){
|
|
| 71 |
success &= testSingleReferences(); |
|
| 82 | 72 |
} |
| 73 |
return success; |
|
| 83 | 74 |
} |
| 84 | 75 |
|
| 85 |
private boolean testSingleTaxa() throws SQLException {
|
|
| 76 |
private boolean testTaxaCount() {
|
|
| 77 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM tu ");
|
|
| 78 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon ");
|
|
| 79 |
return equals("Taxon count ", countSrc, countDest, -1);
|
|
| 80 |
} |
|
| 81 |
|
|
| 82 |
private boolean testSingleTaxa(int n) throws SQLException {
|
|
| 86 | 83 |
boolean success = true; |
| 87 |
ResultSet srcRS = source.getResultSet("SELECT t.* FROM tu t ORDER BY CAST(t.id as nvarchar(20)) ");
|
|
| 88 |
ResultSet destRS = destination.getResultSet("SELECT t.* FROM Taxon t "
|
|
| 84 |
ResultSet srcRS = source.getResultSet(""
|
|
| 85 |
+ " SELECT t.*, acc.tu_sp as acc_sp, st.status_name, " |
|
| 86 |
+ " type.tu_displayname typename, type.tu_authority typeauthor, " |
|
| 87 |
+ " fo.fossil_name, qs.qualitystatus_name " |
|
| 88 |
+ " FROM tu t " |
|
| 89 |
+ " LEFT OUTER JOIN tu acc ON acc.id = t.tu_acctaxon " |
|
| 90 |
+ " LEFT OUTER JOIN status st ON st.status_id = t.tu_status " |
|
| 91 |
+ " LEFT OUTER JOIN tu type ON type.id = t.tu_typetaxon " |
|
| 92 |
+ " LEFT OUTER JOIN fossil fo ON t.tu_fossil = fo.fossil_id " |
|
| 93 |
+ " LEFT OUTER JOIN qualitystatus qs ON t.tu_qualitystatus = qs.id " |
|
| 94 |
+ " ORDER BY CAST(t.id as nvarchar(20)) "); |
|
| 95 |
ResultSet destRS = destination.getResultSet("SELECT t.*, type.IdInSource typeSourceId "
|
|
| 96 |
+ " FROM Taxon t " |
|
| 97 |
+ " LEFT JOIN Taxon type ON type.TaxonId = t.TypeNameFk" |
|
| 89 | 98 |
+ " WHERE t.OriginalDB = 'erms' ORDER BY t.IdInSource"); |
| 99 |
ResultSet srcRsLastAction = source.getResultSet(""
|
|
| 100 |
+ " SELECT t.id, s.sessiondate, a.action_name " |
|
| 101 |
+ " FROM tu t " |
|
| 102 |
+ " LEFT OUTER JOIN tu_sessions MN ON t.id = MN.tu_id " |
|
| 103 |
+ " LEFT JOIN actions a ON a.id = MN.action_id " |
|
| 104 |
+ " LEFT JOIN sessions s ON s.id = MN.session_id " |
|
| 105 |
+ " ORDER BY CAST(t.id as nvarchar(20)), s.sessiondate DESC, a.id DESC "); |
|
| 106 |
int i = 0; |
|
| 90 | 107 |
while (srcRS.next() && destRS.next()){
|
| 91 | 108 |
success &= testSingleTaxon(srcRS, destRS); |
| 109 |
success &= testTaxonLastAction(srcRsLastAction, destRS, srcRS.getInt("id"));
|
|
| 110 |
i++; |
|
| 92 | 111 |
} |
| 112 |
success &= equals("Taxon count for single compare", n, i, -1);
|
|
| 93 | 113 |
return success; |
| 94 | 114 |
} |
| 95 | 115 |
|
| 116 |
|
|
| 96 | 117 |
private boolean testSingleTaxon(ResultSet srcRS, ResultSet destRS) throws SQLException {
|
| 97 |
//id, IdInSource |
|
| 98 | 118 |
int id = srcRS.getInt("id");
|
| 99 | 119 |
boolean success = equals("Taxon ID", "tu_id: " + srcRS.getInt("id"), destRS.getString("IdInSource"), id);
|
| 120 |
//TODO SourceFk |
|
| 121 |
// success &= compareKingdom("Taxon kingdom", srcRS, destRS, id);
|
|
| 122 |
//TODO RankFk, RankCache |
|
| 123 |
//TODO GenusOrUninomial, InfraGenericEpithet, SpecificEpithet, InfraSpecificEpithet |
|
| 124 |
// success &= equals("Taxon websearchname", srcRS.getString("tu_displayname"), destRS.getString("WebSearchName"), id);
|
|
| 125 |
// success &= equals("Taxon WebShowName", srcRS.getString("tu_displayname"), destRS.getString("WebShowName"), id);
|
|
| 100 | 126 |
success &= equals("Taxon authority", srcRS.getString("tu_authority"), destRS.getString("AuthorString"), id);
|
| 127 |
success &= equals("Taxon FullName", srcFullName(srcRS), destRS.getString("FullName"), id);
|
|
| 128 |
//TODO NomRefString |
|
| 129 |
//TODO DisplayName |
|
| 130 |
//TODO success &= equals("Taxon NameStatusFk", toNameStatus(nullSafeInt(srcRS, "tu_status")),nullSafeInt( destRS,"NameStatusFk"), id);
|
|
| 131 |
//TODO success &= equals("Taxon NameStatusCache", srcRS.getString("status_name"), destRS.getString("NameStatusCache"), id);
|
|
| 132 |
|
|
| 133 |
//TODO success &= equals("Taxon TaxonStatusFk", nullSafeInt(srcRS, "tu_status"),nullSafeInt( destRS,"TaxonStatusFk"), id);
|
|
| 134 |
//TODO success &= equals("Taxon TaxonStatusCache", srcRS.getString("status_name"), destRS.getString("TaxonStatusCache"), id);
|
|
| 135 |
|
|
| 136 |
//TODO ParentTaxonFk |
|
| 137 |
Integer orgigTypeNameFk = nullSafeInt(srcRS, "tu_typetaxon"); |
|
| 138 |
success &= equals("Taxon TypeNameFk", orgigTypeNameFk == null? null : "tu_id: " + orgigTypeNameFk, destRS.getString("typeSourceId"), id);
|
|
| 139 |
//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);
|
|
| 141 |
success &= equals("Taxon QualityStatusCache", srcRS.getString("qualitystatus_name"), destRS.getString("QualityStatusCache"), id);
|
|
| 142 |
//TODO TreeIndex |
|
| 143 |
success &= equals("Taxon FossilStatusFk", nullSafeInt(srcRS, "tu_fossil"),nullSafeInt( destRS,"FossilStatusFk"), id);
|
|
| 144 |
success &= equals("Taxon FossilStatusCache", srcRS.getString("fossil_name"), destRS.getString("FossilStatusCache"), id);
|
|
| 101 | 145 |
success &= equals("Taxon GUID", srcRS.getString("GUID"), destRS.getString("GUID"), id);
|
| 102 |
success &= compareKingdom("Taxon kingdom", srcRS.getString("tu_sp"), nullSafeInt(destRS, "KingdomFk"), id);
|
|
| 146 |
//in 2014 GUID and DerivedFromGuid was always same for ERMS |
|
| 147 |
success &= equals("Taxon DerivedFromGuid", srcRS.getString("GUID"), destRS.getString("DerivedFromGuid"), id);
|
|
| 148 |
//TODO DerivedFromGuid |
|
| 149 |
//TODO ExpertGUID |
|
| 150 |
//TODO ExpertName |
|
| 151 |
//TODO SpeciesExpertGUID |
|
| 152 |
//TODO SpeciesExpertName |
|
| 153 |
//TODO CacheCitation |
|
| 154 |
//LastAction(Date) handled in separate method |
|
| 155 |
//TODO GUID2 |
|
| 156 |
//TODO DerivedFromGuid2 |
|
| 157 |
return success; |
|
| 158 |
} |
|
| 159 |
|
|
| 103 | 160 |
|
| 104 |
// success &= equals("Taxon websearchname", srcRS.getString("tu_displayname"), destRS.getString("WebSearchName"), id);
|
|
| 105 | 161 |
|
| 106 |
//TODO TBC |
|
| 162 |
/** |
|
| 163 |
* @param srcRS |
|
| 164 |
* @return |
|
| 165 |
* @throws SQLException |
|
| 166 |
*/ |
|
| 167 |
private String srcFullName(ResultSet srcRs) throws SQLException {
|
|
| 168 |
String result = null; |
|
| 169 |
String epi = srcRs.getString("tu_name");
|
|
| 170 |
epi = " a" + epi; |
|
| 171 |
String display = srcRs.getString("tu_displayname");
|
|
| 172 |
String sp = srcRs.getString("tu_sp");
|
|
| 173 |
if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#1#")){ //homonym, animal
|
|
| 174 |
result = srcRs.getString("tu_displayname").replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" ";
|
|
| 175 |
}else{
|
|
| 176 |
result = CdmUtils.concat(" ", srcRs.getString("tu_displayname"), srcRs.getString("tu_authority"));
|
|
| 177 |
} |
|
| 178 |
return result; |
|
| 179 |
} |
|
| 180 |
|
|
| 181 |
private boolean testTaxonLastAction(ResultSet srcRs, ResultSet destRs, int id) throws SQLException {
|
|
| 182 |
boolean success = true; |
|
| 183 |
while (srcRs.next()){
|
|
| 184 |
int srcId = srcRs.getInt("id");
|
|
| 185 |
if (id == srcId){
|
|
| 186 |
break; |
|
| 187 |
} |
|
| 188 |
} |
|
| 189 |
String destStr = destRs.getString("LastAction");
|
|
| 190 |
success &= equals("Taxon Last Action", srcRs.getString("action_name"), destStr == null? null : destStr, id);
|
|
| 191 |
success &= equals("Taxon Last Action Date", srcRs.getTimestamp("sessiondate"), destRs.getTimestamp("LastActionDate"), id);
|
|
| 107 | 192 |
return success; |
| 108 | 193 |
} |
| 109 | 194 |
|
| 110 |
private boolean compareKingdom(String messageStart, String strSrc, Integer strDest, int id) {
|
|
| 111 |
if (strDest == null){
|
|
| 195 |
private boolean compareKingdom(String messageStart, ResultSet srcRS, ResultSet destRS, int id) throws SQLException {
|
|
| 196 |
String strSrc = srcRS.getString("acc_sp");
|
|
| 197 |
if (strSrc == null){
|
|
| 198 |
strSrc = srcRS.getString("tu_sp");
|
|
| 199 |
} |
|
| 200 |
Integer intDest = nullSafeInt(destRS, "KingdomFk"); |
|
| 201 |
if (intDest == null){
|
|
| 112 | 202 |
logger.warn(id +": " + messageStart + " must never be null for destination. Biota needs to be 0, all the rest needs to have >0 int value."); |
| 113 | 203 |
return false; |
| 114 | 204 |
}else if (strSrc == null){
|
| ... | ... | |
| 118 | 208 |
}else{
|
| 119 | 209 |
strSrc = strSrc.substring(1); |
| 120 | 210 |
String strSrcKingdom = strSrc.substring(0, strSrc.indexOf("#"));
|
| 121 |
return equals(messageStart, strSrcKingdom, String.valueOf(strDest), id);
|
|
| 211 |
return equals(messageStart, strSrcKingdom, String.valueOf(intDest), id);
|
|
| 122 | 212 |
} |
| 123 | 213 |
} |
| 124 | 214 |
|
| ... | ... | |
| 128 | 218 |
ResultSet destRS = destination.getResultSet("SELECT s.* FROM Source s "
|
| 129 | 219 |
+ " WHERE s.OriginalDB = 'erms' ORDER BY s.RefIdInSource"); // +1 for the source reference "erms" but this has no OriginalDB |
| 130 | 220 |
while (srcRS.next() && destRS.next()){
|
| 131 |
// success &= testSingleReference(srcRS, destRS);
|
|
| 221 |
success &= testSingleReference(srcRS, destRS); |
|
| 132 | 222 |
} |
| 133 | 223 |
return success; |
| 134 | 224 |
} |
| 135 | 225 |
|
| 136 | 226 |
private boolean testSingleReference(ResultSet srcRS, ResultSet destRS) throws SQLException {
|
| 137 |
//id, RefIdInSource |
|
| 138 | 227 |
int id = srcRS.getInt("id");
|
| 139 |
boolean success = equals("Reference ID ", srcRS.getInt("id"), destRS.getInt("RefIdInSource"));
|
|
| 228 |
boolean success = equals("Reference ID ", srcRS.getInt("id"), destRS.getInt("RefIdInSource"), id);
|
|
| 229 |
success &= equals("Reference IMIS_id ", srcRS.getString("imis_id"), destRS.getString("IMIS_Id"), id);
|
|
| 230 |
//TODO success &= equals("Reference SourceCategoryFk ", srcRS.getString("source_type"), destRS.getInt("SourceCategoryFk"), id);
|
|
| 231 |
//TODO success &= equals("Reference SourceCategoryCache ", srcRS.getString("source_type"), destRS.getString("SourceCategoryCache"), id);
|
|
| 140 | 232 |
success &= equals("Reference name ", srcRS.getString("source_name"), destRS.getString("Name"), id);
|
| 141 |
success &= equals("Reference note ", srcRS.getString("source_note"), destRS.getString("Notes"), id);
|
|
| 233 |
success &= equals("Reference abstract ", srcRS.getString("source_abstract"), destRS.getString("Abstract"), id);
|
|
| 234 |
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);
|
|
| 236 |
success &= equals("Reference year ", normalizeYear(srcRS.getString("source_year")), destRS.getString("RefYear"), id);
|
|
| 237 |
success &= isNull("NomRefCache", destRS); //for ERMS no other value was found in 2014 value
|
|
| 142 | 238 |
success &= equals("Reference link ", srcRS.getString("source_link"), destRS.getString("Link"), id);
|
| 143 |
success &= equals("Reference year ", srcRS.getString("source_year"), destRS.getString("RefYear"), id);
|
|
| 144 |
//TODO TBC
|
|
| 239 |
success &= equals("Reference note ", srcRS.getString("source_note"), destRS.getString("Notes"), id);
|
|
| 240 |
//complete
|
|
| 145 | 241 |
return success; |
| 146 | 242 |
} |
| 147 | 243 |
|
| 244 |
|
|
| 148 | 245 |
private boolean testReferenceCount() {
|
| 149 | 246 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM sources ");
|
| 150 | 247 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Source s WHERE s.OriginalDB = 'erms'"); // +1 for the source reference "erms" but this has no OriginalDB
|
| 151 |
boolean success = equals("Reference count ", countSrc, countDest);
|
|
| 248 |
boolean success = equals("Reference count ", countSrc, countDest, -1);
|
|
| 152 | 249 |
return success; |
| 153 | 250 |
} |
| 154 | 251 |
|
| 155 |
private boolean equals(String messageStart, int nSrc, int nDest) {
|
|
| 156 |
if (nSrc != nDest){
|
|
| 157 |
String message = messageStart + " must be equal, but was not.\n Source: "+ nSrc + "; Destination: " + nDest; |
|
| 252 |
private String normalizeYear(String yearStr) {
|
|
| 253 |
if (StringUtils.isBlank(yearStr)){
|
|
| 254 |
return yearStr; |
|
| 255 |
} |
|
| 256 |
yearStr = yearStr.trim(); |
|
| 257 |
if (yearStr.matches("\\d{4}-\\d{2}")){
|
|
| 258 |
yearStr = yearStr.substring(0, 5)+yearStr.substring(0, 2)+yearStr.substring(5); |
|
| 259 |
} |
|
| 260 |
return yearStr; |
|
| 261 |
} |
|
| 262 |
|
|
| 263 |
private boolean isNull(String attrName, ResultSet destRS) throws SQLException {
|
|
| 264 |
Object value = destRS.getObject(attrName); |
|
| 265 |
if (value != null){
|
|
| 266 |
String message = attrName + " was expected to be null but was: " + value.toString(); |
|
| 267 |
logger.warn(message); |
|
| 268 |
return false; |
|
| 269 |
}else{
|
|
| 270 |
logger.info(attrName + " was null as expected"); |
|
| 271 |
return true; |
|
| 272 |
} |
|
| 273 |
} |
|
| 274 |
|
|
| 275 |
private boolean equals(String messageStart, Timestamp srcDate, Timestamp destDate, int id) {
|
|
| 276 |
if (!CdmUtils.nullSafeEqual(srcDate, destDate)){
|
|
| 277 |
String message = id + ": " + messageStart + " must be equal, but was not.\n Source: "+ srcDate + "; Destination: " + destDate; |
|
| 278 |
logger.warn(message); |
|
| 279 |
return false; |
|
| 280 |
}else{
|
|
| 281 |
logger.info(messageStart + " were equal: " + srcDate); |
|
| 282 |
return true; |
|
| 283 |
} |
|
| 284 |
} |
|
| 285 |
|
|
| 286 |
private boolean equals(String messageStart, Integer nSrc, Integer nDest, int id) {
|
|
| 287 |
String strId = id == -1? "": (id+ ": "); |
|
| 288 |
if (!CdmUtils.nullSafeEqual(nSrc,nDest)){
|
|
| 289 |
String message = strId+ messageStart + " must be equal, but was not.\n Source: "+ nSrc + "; Destination: " + nDest; |
|
| 158 | 290 |
logger.warn(message); |
| 159 | 291 |
return false; |
| 160 | 292 |
}else{
|
| 161 |
logger.info(messageStart + " were equal: " + nSrc); |
|
| 293 |
logger.info(strId + messageStart + " were equal: " + nSrc);
|
|
| 162 | 294 |
return true; |
| 163 | 295 |
} |
| 164 | 296 |
} |
| 165 | 297 |
|
| 166 | 298 |
private boolean equals(String messageStart, String strSrc, String strDest, int id) {
|
| 299 |
if (StringUtils.isBlank(strSrc)){
|
|
| 300 |
strSrc = null; |
|
| 301 |
}else{
|
|
| 302 |
strSrc = strSrc.trim(); |
|
| 303 |
} |
|
| 304 |
//we do not trim strDest here because this should be done during import already. If not it should be shown here |
|
| 167 | 305 |
if (!CdmUtils.nullSafeEqual(strSrc, strDest)){
|
| 168 | 306 |
int index = diffIndex(strSrc, strDest); |
| 169 | 307 |
String message = id+ ": " + messageStart + " must be equal, but was not at "+index+".\n Source: "+ strSrc + "\n Destination: " + strDest; |
| ... | ... | |
| 174 | 312 |
return true; |
| 175 | 313 |
} |
| 176 | 314 |
} |
| 177 |
/** |
|
| 178 |
* @param strSrc |
|
| 179 |
* @param strDest |
|
| 180 |
* @return |
|
| 181 |
*/ |
|
| 315 |
|
|
| 182 | 316 |
private int diffIndex(String strSrc, String strDest) {
|
| 183 | 317 |
if (strSrc == null || strDest == null){
|
| 184 | 318 |
return 0; |
Also available in: Unified diff
ref #8508 improve ERMS validation