import eu.etaxonomy.cdm.io.common.Source;
import eu.etaxonomy.cdm.io.pesi.out.PesiTransformer;
import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;
+import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
/**
* Tests the ERMS -> PESI pipeline by comparing the source DB with destination PESI DB.
// private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_EM2PESI();
private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_EM2PESI_2();
+ boolean doReferences = false;
+ boolean doTaxa = true;
+ boolean doTaxRels = false;
+ boolean doDistributions = false;
+ boolean doCommonNames = false;
+ boolean doNotes = false;
+ boolean doAdditionalTaxonSources = false;
+
private Source source = new Source(defaultSource);
private Source destination = defaultDestination;
try {
this.source = source;
this.destination = destination;
-// success &= testReferences();
- success &= testTaxa();
-// success &= testTaxonRelations();
-// success &= testDistributions();
-// success &= testNotes();
-// success &= testAdditionalTaxonSources();
+ success &= testReferences();
+ success &= testTaxa();
+ success &= testTaxonRelations();
+ success &= testDistributions();
+ success &= testCommonNames();
+ success &= testNotes();
+ success &= testAdditionalTaxonSources();
} catch (Exception e) {
e.printStackTrace();
success = false;
}
private boolean testAdditionalTaxonSources() throws SQLException {
+ if (!doAdditionalTaxonSources){
+ return true;
+ }
System.out.println("Start validate additional taxon sources");
boolean success = testAdditionalTaxonSourcesCount();
if (success){
}
private boolean testNotes() throws SQLException {
+ if (!doNotes){
+ return true;
+ }
System.out.println("Start validate notes");
boolean success = testNotesCount();
if (success){
}
private boolean testDistributions() throws SQLException {
+ if (!doDistributions){
+ return true;
+ }
System.out.println("Start validate distributions");
boolean success = testDistributionCount();
if (!success){
}
private boolean testCommonNames() throws SQLException {
+ if (!doCommonNames){
+ return true;
+ }
System.out.println("Start validate common names");
boolean success = testCommonNameCount();
if (success){
int countSynonyms;
int countIncludedIns;
- private boolean testTaxonRelations() {
+ private boolean testTaxonRelations() throws SQLException {
+ if (!doTaxRels){
+ return true;
+ }
System.out.println("Start validate taxon relations");
boolean success = testSynonymRelations();
success &= testIncludedInRelations();
}
}
- private boolean testSynonymRelations() {
+ private final String countSynonymRelation = "SELECT count(*) FROM TaxonBase syn LEFT JOIN TaxonBase acc ON syn.acceptedTaxon_id = acc.id WHERE syn.publish = 1 AND acc.publish = 1 ";
+ private boolean testSynonymRelations() throws SQLException {
int countSrc = source.getUniqueInteger(countSynonymRelation);
int countDest = destination.getUniqueInteger("SELECT count(*) FROM RelTaxon WHERE RelTaxonQualifierFk > 101");
boolean success = equals("Synonym count ", countSrc, countDest, String.valueOf(-1));
-// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason like 'currently placed%'
-// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason like 'currently held%'
-// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason like 'sy%' or tu_unacceptreason like '%jun%syn%'
-// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason = '(synonym)'
-// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason = 'reverted genus transfer'
-// update Match_RelStat set RelTaxon = 103 where tu_unacceptreason like 'misapplied%'
-// update Match_RelStat set RelTaxon = 104 where tu_unacceptreason like 'part% synonym%'
-// update Match_RelStat set RelTaxon = 106 where tu_unacceptreason = 'heterotypic synonym' or tu_unacceptreason = 'subjective synonym'
-// update Match_RelStat set RelTaxon = 107 where tu_unacceptreason like '%homot%syn%' or tu_unacceptreason = 'objective synonym' synyonym
-// update Match_RelStat set RelTaxon = 107 where tu_unacceptreason like '%bas[iy][no]%ny%'
if (success){
//TODO test single synonym relations
-// success &= testSingleTaxonRelations(source.getUniqueInteger(countSynonymRelation));
+ success &= testSingleSynonymRelations(source.getUniqueInteger(countSynonymRelation));
}
countSynonyms = (countSrc == countDest)? countSrc : -1;
return success;
}
+ private boolean testSingleSynonymRelations(int n) throws SQLException {
+ boolean success = true;
+ ResultSet srcRS = source.getResultSet(""
+ + " SELECT t.id tid, pt.id pid "
+ + " FROM TaxonNode tn "
+ + " INNER JOIN TaxonBase t ON tn.taxon_id = t.id "
+ + " LEFT JOIN TaxonNode ptn ON ptn.id = tn.parent_id "
+ + " LEFT JOIN TaxonBase pt ON ptn.taxon_id = pt.id "
+ + " WHERE t.publish = 1 && pt.publish = 1 "
+ + " ORDER BY CAST(tb.id as char(20)) ");
+
+ ResultSet destRS = destination.getResultSet("SELECT rel.*, t1.IdInSource t1Id, t2.IdInSource t2Id "
+ + " FROM RelTaxon rel "
+ + " LEFT JOIN Taxon t1 ON t1.TaxonId = rel.TaxonFk1 "
+ + " LEFT JOIN Taxon t2 ON t2.TaxonId = rel.TaxonFk2 "
+ + " WHERE t1."+origEuroMed+" AND t2." + origEuroMed + " AND RelTaxonQualifierFk > 101 "
+ + " ORDER BY t1.IdInSource");
+ int i = 0;
+ while (srcRS.next() && destRS.next()){
+ success &= testSingleSynonymRelation(srcRS, destRS);
+ i++;
+ }
+ success &= equals("Synonym relation count for single compare", n, i, String.valueOf(-1));
+ return success;
+ }
+
+ private boolean testSingleSynonymRelation(ResultSet srcRS, ResultSet destRS) throws SQLException {
+ String id = String.valueOf(srcRS.getInt("id"));
+ boolean success = equals("Taxon relation taxon1", "NameId: " + srcRS.getInt("id"), destRS.getString("t1Id"), id);
+ success &= equals("Taxon relation taxon2", "NameId: " + srcRS.getInt("tu_acctaxon"), destRS.getString("t2Id"), id);
+ success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_SYNONYM_OF, destRS.getInt("RelTaxonQualifierFk"), id);
+ success &= equals("Taxon relation qualifier cache", "is synonym of", destRS.getString("RelQualifierCache"), id);
+ //TODO enable after next import
+// success &= isNull("notes", destRS);
+ //complete if no further relations need to added
+ return success;
+ }
+
private boolean testNameRelations() {
//Name relations
- int countSrc = source.getUniqueInteger("SELECT count(*) FROM tu WHERE ("
- + " tu_unacceptreason like '%bas[iy][no]%ny%' OR tu_unacceptreason = 'original combination' "
- + " OR tu_unacceptreason = 'Subsequent combination' OR tu_unacceptreason like '%genus transfer%' "
- + " OR tu_unacceptreason = 'genus change' " //1
- + " OR tu_unacceptreason like '%homon%' " // 2
- + " OR tu_unacceptreason like '%spell%' OR tu_unacceptreason like 'lapsus %' " //16
-
+ int countSrc = source.getUniqueInteger("SELECT count(*) FROM NameRelationship WHERE ("
+ + " 1=1 "
+ ")");
int countDest = destination.getUniqueInteger("SELECT count(*) FROM RelTaxon WHERE RelTaxonQualifierFk <100 ");
boolean success = equals("Taxon name relation count ", countSrc, countDest, String.valueOf(-1));
if (success){
//TODO test single name relation
-// success &= testSingleTaxonRelations(source.getUniqueInteger(countSynonymRelation));
+// success &= testSingleNameRelations(source.getUniqueInteger(countSynonymRelation));
}
return success;
}
- private boolean testIncludedInRelations() {
+ private final String countParentRelation = "SELECT count(*) "
+ + " FROM TaxonNode tn "
+ + " INNER JOIN TaxonBase tb ON tn.taxon_id = tb.id "
+ + " LEFT JOIN TaxonNode ptn ON ptn.id = tn.parent_id "
+ + " LEFT JOIN TaxonBase pt ON ptn.taxon_id = pt.id "
+ + " WHERE tb.publish = 1 && pt.publish = 1 ";
+
+ private boolean testIncludedInRelations() throws SQLException {
int countSrc = source.getUniqueInteger(countParentRelation);
int countDest = destination.getUniqueInteger("SELECT count(*) FROM RelTaxon WHERE RelTaxonQualifierFk = 101 ");
boolean success = equals("Tax included in count ", countSrc, countDest, String.valueOf(-1));
if (success){
- //TODO test single includedIn relations
-// success &= testSingleTaxonRelations(source.getUniqueInteger(countSynonymRelation));
+ success &= testSingleTaxonRelations(source.getUniqueInteger(countParentRelation));
}
countIncludedIns = (countSrc == countDest)? countSrc : -1;
return success;
}
private boolean testTaxa() throws SQLException {
+ if (!doTaxa){
+ return true;
+ }
System.out.println("Start validate taxa");
boolean success = testTaxaCount();
- //FIXME
if (success){
success &= testSingleTaxa(source.getUniqueInteger(countTaxon));
}
return success;
}
+ String countReferencesStr = "SELECT count(*) FROM reference ";
private boolean testReferences() throws SQLException {
+ if (!doReferences){
+ return true;
+ }
System.out.println("Start validate references");
boolean success = testReferenceCount();
if (success){
- success &= testSingleReferences();
+ success &= testSingleReferences(source.getUniqueInteger(countReferencesStr));
}
return success;
}
private boolean testNotesCount() {
int countSrc = source.getUniqueInteger("SELECT count(*) FROM notes ");
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Note "
- + " WHERE NOT (NoteCategoryFk = 4 AND LastAction IS NULL) AND NOT NoteCategoryFk IN (22,23,24) ");
+ + " WHERE (1=1) ");
boolean result = equals("Notes count ", countSrc, countDest, String.valueOf(-1));
- countSrc = source.getUniqueInteger("SELECT count(*) FROM tu "
- + " WHERE (tu_marine IS NOT NULL OR tu_brackish IS NOT NULL OR tu_fresh IS NOT NULL OR tu_terrestrial IS NOT NULL) " );
- countDest = destination.getUniqueInteger("SELECT count(*) FROM Note "
- + " WHERE (NoteCategoryFk = 4 AND LastAction IS NULL) ");
- result &= equals("Notes ecology count ", countSrc, countDest, String.valueOf(-1));
-
- countSrc = source.getUniqueInteger("SELECT count(*) FROM links ");
- countDest = destination.getUniqueInteger("SELECT count(*) FROM Note "
- + " WHERE NoteCategoryFk IN (22,23,24) ");
- result &= equals("Notes link count ", countSrc, countDest, String.valueOf(-1));
-
return result;
}
return equals("CommonName count ", countSrc, countDest, String.valueOf(-1));
}
- private final String countSynonymRelation = "SELECT count(*) FROM tu syn LEFT JOIN tu acc ON syn.tu_acctaxon = acc.id WHERE (syn.id <> acc.id AND syn.tu_acctaxon IS NOT NULL AND syn.id <> acc.tu_parent) ";
- private final String countParentRelation = "SELECT count(*)-1 FROM tu syn LEFT JOIN tu acc ON syn.tu_acctaxon = acc.id WHERE (syn.id = acc.id OR syn.tu_acctaxon IS NULL OR syn.id = acc.tu_parent) ";
-
private final String countTaxon = "SELECT count(*) FROM TaxonBase tb WHERE tb.publish = 1 ";
private boolean testTaxaCount() {
int countSrc = source.getUniqueInteger(countTaxon);
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon t WHERE t.SourceFk IS NOT NULL OR t.AuthorString = 'auct.' ");
boolean result = equals("Taxon count ", countSrc, countDest, String.valueOf(-1));
-
-// //NomStatus
-// countSrc = source.getUniqueInteger("SELECT count(*) FROM tu WHERE ("
-// + " tu_unacceptreason like '%inval%' OR tu_unacceptreason like '%not val%' "
-// + " OR tu_unacceptreason like '%illeg%' OR tu_unacceptreason like '%nud%' "
-// + " OR tu_unacceptreason like '%rej.%' OR tu_unacceptreason like '%superfl%' "
-// + " OR tu_unacceptreason like '%Comb. nov%' OR tu_unacceptreason like '%New name%' "
-// + " OR tu_unacceptreason = 'new combination' "
-// + " OR tu_status IN (3,5,6,7,8) )");
-// countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon WHERE NameStatusFk IS NOT NULL ");
-// result = equals("Taxon name status count ", countSrc, countDest, String.valueOf(-1));
-
return result;
}
private boolean testSingleTaxa(int n) throws SQLException {
boolean success = true;
- ResultSet srcRS = source.getResultSet("SELECT CAST(tn.id as char(20)) tid, tb.uuid as GUID, tn.rank_id, rank.titleCache rank_name, "
+ ResultSet srcRS = source.getResultSet("SELECT CAST(tn.id as char(20)) tid, tb.uuid as GUID, pt.id parentId, "
+ + " tn.rank_id, rank.titleCache rank_name, "
+ " sec.titleCache secTitle,"
+ " tn.genusOrUninomial, tn.infraGenericEpithet, tn.specificEpithet, tn.infraSpecificEpithet, "
- + " tn.nameCache, tn.authorshipCache, tn.titleCache nameTitleCache, "
- + " tb.DTYPE taxStatus, nsType.id nsId, nsType.idInVocabulary nsTitle, "
+ + " tn.nameCache, tn.authorshipCache, tn.titleCache nameTitleCache, tn.fullTitleCache nameFullTitleCache, "
+ + " tb.DTYPE taxStatus, taxRelType.uuid taxRelTypeUuid, nsType.id nsId, nsType.idInVocabulary nsTitle, "
+ + " typeName_id, typeName.titleCache typeFullNameCache, "
+ " CASE WHEN tb.updated IS NOT NULL THEN tb.updated ELSE tb.created END as lastActionDate, "
+ " CASE WHEN tb.updated IS NOT NULL THEN 'changed' ELSE 'created' END as lastAction "
+ " FROM TaxonBase tb "
+ " LEFT JOIN TaxonName_NomenclaturalStatus nsMN ON tn.id = nsMN.TaxonName_id "
+ " LEFT JOIN NomenclaturalStatus ns ON ns.id = nsMN.status_id "
+ " LEFT JOIN DefinedTermBase nsType ON nsType.id = ns.type_id "
+ + " LEFT JOIN TaxonName_TypeDesignationBase typeMN ON typeMN.TaxonName_id = tn.id "
+ + " LEFT JOIN TypeDesignationBase td ON td.id = typeMN.typedesignations_id "
+ + " LEFT JOIN TaxonName typeName ON typeName.id = td.typeName_id "
+ + " LEFT JOIN TaxonNode n ON n.taxon_id = tb.id "
+ + " LEFT JOIN TaxonNode ptn ON n.parent_id = ptn.id "
+ + " LEFT JOIN TaxonBase pt ON pt.id = ptn.taxon_id AND pt.publish = 1 "
+ + " LEFT JOIN TaxonRelationship tr ON tr.relatedFrom_id = tb.id "
+ + " LEFT JOIN DefinedTermBase taxRelType ON taxRelType.id = tr.type_id"
+ " WHERE tb.publish = 1 "
+ " GROUP BY tid, GUID, tn.rank_id, rank.titleCache, secTitle,"
+ " tn.genusOrUninomial, tn.infraGenericEpithet, tn.specificEpithet, tn.infraSpecificEpithet, "
+ " tb.DTYPE, tb.updated, tb.created " //for duplicates caused by >1 name status
+ " ORDER BY tid, GUID, lastActionDate ");
ResultSet destRS = destination.getResultSet("SELECT t.*, "
- + " pt.GenusOrUninomial p_GenusOrUninomial, pt.InfraGenericEpithet p_InfraGenericEpithet, pt.SpecificEpithet p_SpecificEpithet, "
+ + " pt.IdInSource parentSourceId, " //not needed
+ " s.Name as sourceName, type.IdInSource typeSourceId, r.Rank "
+ " FROM Taxon t "
+ " LEFT JOIN Taxon pt ON pt.TaxonId = t.ParentTaxonFk "
return success;
}
-
private boolean testSingleTaxon(ResultSet srcRS, ResultSet destRS) throws SQLException {
String id = String.valueOf(srcRS.getInt("tid"));
//TODO decide, according to SQL it also contains the taxon UUID, but in PESI2014 backup I can't find this
success &= equals("Taxon kingdomFk", "3", destRS.getString("KingdomFk"), id);
//difficult to test success &= equals("Taxon rank fk", srcRS.getString("rank_id"), destRS.getString("RankFk"), id);
- success &= equals("Taxon rank cache", normalizeRank(srcRS.getString("rank_name"), srcRS, id), destRS.getString("Rank"), id);
+ success &= equals("Taxon rank cache", normalizeRank(srcRS.getString("rank_name")), destRS.getString("Rank"), id);
success &= equals("Taxon genusOrUninomial", srcRS.getString("genusOrUninomial"), destRS.getString("GenusOrUninomial"), id) ;
success &= equals("Taxon infraGenericEpithet", srcRS.getString("infraGenericEpithet"), destRS.getString("InfraGenericEpithet"), id) ;
success &= equals("Taxon specificEpithet", srcRS.getString("specificEpithet"), destRS.getString("SpecificEpithet"), id) ;
//TODO success &= equals("Taxon WebShowName", srcRS.getString("tu_displayname"), destRS.getString("WebShowName"), id);
//FIXME sensu+auct. autoren success &= equals("Taxon authority", srcRS.getString("authorshipCache"), destRS.getString("AuthorString"), id);
//FIXME sensu+auct. autoren success &= equals("Taxon FullName", srcRS.getString("nameTitleCache"), destRS.getString("FullName"), id);
-//TODO success &= isNull("NomRefString", destRS);
-//TODO success &= equals("Taxon DisplayName", srcDisplayName(srcRS), destRS.getString("DisplayName"), id); //in ERMS according to SQL script same as FullName, no nom.ref. information attached
-
-//TODO success &= equals("Taxon NameStatusFk", nullSafeInt(srcRS, "nsId"),nullSafeInt( destRS,"NameStatusFk"), id);
+ success &= equals("Taxon NomRefString", makeNomRefString(srcRS), destRS.getString("NomRefString"), id);
+ success &= equals("Taxon DisplayName", makeDisplayName(srcRS), destRS.getString("DisplayName"), id); //in ERMS according to SQL script same as FullName, no nom.ref. information attached
+//difficult to test success &= equals("Taxon NameStatusFk", nullSafeInt(srcRS, "nsId"),nullSafeInt( destRS,"NameStatusFk"), id);
success &= equals("Taxon NameStatusCache", srcRS.getString("nsTitle"), destRS.getString("NameStatusCache"), id);
-// success &= equals("Taxon TaxonStatusFk", mapTaxStatusFk(srcRS.getString("taxStatus")), nullSafeInt( destRS,"TaxonStatusFk"), id);
-// success &= equals("Taxon TaxonStatusCache", mapTaxStatus(srcRS.getString("taxStatus")), destRS.getString("TaxonStatusCache"), id);
+//reimport success &= equals("Taxon TaxonStatusFk", mapTaxStatusFk(srcRS.getString("taxStatus"), srcRS.getString("taxRelTypeUuid")), nullSafeInt( destRS,"TaxonStatusFk"), id);
+//reimport success &= equals("Taxon TaxonStatusCache", mapTaxStatus(srcRS.getString("taxStatus"), srcRS.getString("taxRelTypeUuid")), destRS.getString("TaxonStatusCache"), id);
-// //TODO ParentTaxonFk
-// Integer orgigTypeNameFk = nullSafeInt(srcRS, "tu_typetaxon");
-// success &= equals("Taxon TypeNameFk", orgigTypeNameFk == null? null : "tu_id: " + orgigTypeNameFk, destRS.getString("typeSourceId"), id);
-////TODO success &= equals("Taxon TypeFullNameCache", CdmUtils.concat(" ", srcRS.getString("typename"), srcRS.getString("typeauthor")), destRS.getString("TypeFullNameCache"), id);
- //quality status, according to SQL always constant, could be changed in future
+ success &= equals("Taxon ParentTaxonFk", nullSafeInt(srcRS, "parentId"), nullSafeInt(destRS, "ParentTaxonFk"), id);
+
+ Integer origTypeNameFk = nullSafeInt(srcRS, "typeName_id");
+ success &= equals("Taxon TypeNameFk", origTypeNameFk == null? null : "NameId: " + origTypeNameFk, destRS.getString("typeSourceId"), id);
+ success &= equals("Taxon TypeFullNameCache", srcRS.getString("typeFullNameCache"), destRS.getString("TypeFullNameCache"), id);
+ //according to SQL always constant, could be changed in future
success &= equals("Taxon QualityStatusFK", 2, nullSafeInt( destRS,"QualityStatusFk"), String.valueOf(id));
success &= equals("Taxon QualityStatusCache", "Added by Database Management Team", destRS.getString("QualityStatusCache"), id);
-// //TODO TreeIndex
- success &= isNull("FossilStatusFk", destRS);
- success &= isNull("FossilStatusCache", destRS);
+//TODO TreeIndex
+ success &= isNull("FossilStatusFk", destRS);
+ success &= isNull("FossilStatusCache", destRS);
success &= equals("Taxon GUID", srcRS.getString("GUID"), destRS.getString("GUID"), id);
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
success &= isNull("ExpertGUID", destRS); //according to SQL + PESI2014
-// success &= isNull("ExpertName", destRS);
-// success &= isNull("SpeciesExpertGUID", destRS);
-// success &= isNull("SpeciesExpertName", destRS); //only relevant after merge
+//reimport success &= equals("Taxon ExpertName", srcRS.getString("secTitle"), destRS.getString("ExpertName"), id);
+ success &= isNull("SpeciesExpertGUID", destRS);
+//reimport success &= equals("Taxon SpeciesExpertName", srcRS.getString("secTitle"), destRS.getString("SpeciesExpertName"), id);
//FIXME !! success &= equals("Taxon cache citation", srcRS.getString("secTitle"), destRS.getString("CacheCitation"), id);
success &= equals("Taxon Last Action", srcRS.getString("lastAction"), destRS.getString("LastAction"), id);
success &= equals("Taxon Last Action Date", srcRS.getTimestamp("lastActionDate"), destRS.getTimestamp("LastActionDate"), id);
return success;
}
- private String mapTaxStatus(String string) {
- if (string == null){
+ private String makeNomRefString(ResultSet srcRS) throws SQLException {
+ //there is no pure nomRefString field in CDM and also computing is only possible
+ //with cache strategy which requires a running CDM instance. So this is a workaround that maybe needs to be adapted
+ String result = null;
+ String fullTitle = srcRS.getString("nameFullTitleCache");
+ String nameTitleCache = srcRS.getString("nameTitleCache");
+ String nameStatus = CdmUtils.Nz(srcRS.getString("nsTitle"));
+ if (fullTitle != null && nameTitleCache != null){
+ result = fullTitle.substring(nameTitleCache.length())
+ .replaceAll("^, ", "")
+ .replaceAll("(, |^)"+nameStatus+"$", "");
+ }
+ return result;
+ }
+
+ private String mapTaxStatus(String dtype, String taxRelTypeUuidStr) {
+ Integer statusFk = mapTaxStatusFk(dtype, taxRelTypeUuidStr);
+ if (statusFk == null){
return null;
- }else if ("Synonym".equals(string)){
- return "synonym";
- }else if ("Taxon".equals(string)){
+ }else if (statusFk == PesiTransformer.T_STATUS_ACCEPTED){
return "accepted";
+ }else if (statusFk == PesiTransformer.T_STATUS_SYNONYM){
+ return "synonym";
+ }else if (statusFk == PesiTransformer.T_STATUS_PRO_PARTE_SYN){
+ return "pro parte synonym";
+ }else if (statusFk == PesiTransformer.T_STATUS_PARTIAL_SYN){
+ return "partial synonym";
}
return null;
}
- private Integer mapTaxStatusFk(String string) {
- if (string == null){
+ private Integer mapTaxStatusFk(String dtype, String taxRelTypeUuidStr) {
+ if (dtype == null){
return null;
- }else if ("Synonym".equals(string)){
+ }else if ("Synonym".equals(dtype)){
return PesiTransformer.T_STATUS_SYNONYM;
- }else if ("Taxon".equals(string)){
- return PesiTransformer.T_STATUS_ACCEPTED;
+ }else if ("Taxon".equals(dtype)){
+ UUID relTypeUuid = taxRelTypeUuidStr == null? null: UUID.fromString(taxRelTypeUuidStr);
+ if (TaxonRelationshipType.proParteUuids().contains(relTypeUuid)){
+ return PesiTransformer.T_STATUS_PRO_PARTE_SYN;
+ }else if (TaxonRelationshipType.partialUuids().contains(relTypeUuid)){
+ return PesiTransformer.T_STATUS_PARTIAL_SYN;
+ }else if (TaxonRelationshipType.misappliedNameUuids().contains(relTypeUuid)){
+ return PesiTransformer.T_STATUS_SYNONYM; //no explicit MAN status exists in PESI
+ }else{
+ return PesiTransformer.T_STATUS_ACCEPTED;
+ }
}
return null;
}
- private String normalizeRank(String rankStr, ResultSet srcRS, String id) throws SQLException {
+ private String normalizeRank(String rankStr) {
if (rankStr == null){return null;
}else if (rankStr.equals("Convar")){return "Convariety";
}else if (rankStr.equals("Unranked (infrageneric)")){return "Tax. infragen.";
}return rankStr;
}
- //see also ErmsTaxonImport.getExpectedTitleCache()
- private String srcFullName(ResultSet srcRs) throws SQLException {
- String result = null;
- String epi = srcRs.getString("tu_name");
- epi = " a" + epi;
- String display = srcRs.getString("tu_displayname");
- String sp = srcRs.getString("tu_sp");
- if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#2#")){ //homonym, animal
- result = srcRs.getString("tu_displayname").replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" ";
- }else{
- result = CdmUtils.concat(" ", srcRs.getString("tu_displayname"), srcRs.getString("tu_authority"));
- }
+ private String makeDisplayName(ResultSet srcRs) throws SQLException {
+ String nameCache = srcRs.getString("nameCache");
+ String nameTitle = srcRs.getString("nameTitleCache");
+ String taggedName = getTaggedNameTitle(nameCache, nameTitle);
+ String fullNameTitle = srcRs.getString("nameFullTitleCache");
+ String result = fullNameTitle
+ .replace(nameTitle, taggedName);
return result;
}
- private String srcDisplayName(ResultSet srcRs) throws SQLException {
- String result = null;
- String epi = srcRs.getString("tu_name");
- epi = " a" + epi;
- String display = "<i>"+srcRs.getString("tu_displayname")+"</i>";
- display = display.replace(" var. ", "</i> var. <i>").replace(" f. ", "</i> f. <i>");
- String sp = srcRs.getString("tu_sp");
- if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#2#")){ //homonym, animal
- result = display.replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" ";
- }else{
- result = CdmUtils.concat(" ", display, srcRs.getString("tu_authority"));
+ private String getTaggedNameTitle(String nameCache, String nameTitle) {
+ if (nameCache == null){
+ logger.warn("NameCache is null");
+ return nameTitle;
}
- return result;
- }
-
- String lastLastActionId = "-1";
- private boolean testLastAction(ResultSet srcRsLastAction, ResultSet destRs, String id, String table) throws SQLException {
+ String result = null;
try {
- boolean success = true;
- String srcId = null;
- while (srcRsLastAction.next()){
- srcId = String.valueOf(srcRsLastAction.getInt("id"));
- if (!lastLastActionId.equals(srcId)){
- lastLastActionId = srcId;
- break;
+ String[] nameCacheSplit = nameCache.split(" ");
+ String[] nameTitleSplit = nameTitle.split(" ");
+ result = "";
+ boolean currentIsName = false;
+ for (int i=0, j=0; j < nameTitleSplit.length; j++){
+ if (i < nameCacheSplit.length && nameCacheSplit[i].equals(nameTitleSplit[j])
+ && !isMarker(nameCacheSplit[i])){
+ if(!currentIsName){
+ result += " <i>" + nameCacheSplit[i];
+ currentIsName = true;
+ }else{
+ result += " " + nameCacheSplit[i];
+ }
+ if((j+1)==nameTitleSplit.length){
+ result += "</i>";
+ }
+ i++;
+ }else{
+ if(currentIsName){
+ result += "</i>";
+ currentIsName = false;
+ }
+ result += " " + nameTitleSplit[j];
+ if (i < nameCacheSplit.length && nameCacheSplit[i].equals(nameTitleSplit[j])
+ && isMarker(nameCacheSplit[i])){
+ i++;
+ }
}
}
- if(!id.equals(srcId)){
- logger.warn("Last Action SourceIDs are not equal: id: " +id + ", la-id: " + srcId);
- }
- String destStr = destRs.getString("LastAction");
- success &= equals(table + " SpeciesExpertName", srcRsLastAction.getString("ExpertName"), destRs.getString("SpeciesExpertName"), id); //mapping ExpertName => SpeciesExpertName according to SQL script
- success &= equals(table + " Last Action", srcRsLastAction.getString("action_name"), destStr == null? null : destStr, id);
- success &= equals(table + " Last Action Date", srcRsLastAction.getTimestamp("sessiondate"), destRs.getTimestamp("LastActionDate"), id);
-
- return success;
+ return result.trim();
} catch (Exception e) {
e.printStackTrace();
- throw e;
+ return result;
}
}
+ private boolean isMarker(String nameCacheSplit) {
+ return nameCacheSplit.endsWith(".") || nameCacheSplit.equals("[unranked]") ;
+ }
+
private boolean testSingleTaxonRelations(int n) throws SQLException {
boolean success = true;
ResultSet srcRS = source.getResultSet(""
- + " SELECT t.* "
- + " FROM tu t "
- + " WHERE tu_acctaxon <> id "
- + " ORDER BY CAST(t.id as char(20)) ");
+ + " SELECT t.name_id tid, pt.name_id pid "
+ + " FROM TaxonNode tn "
+ + " INNER JOIN TaxonBase t ON tn.taxon_id = t.id "
+ + " LEFT JOIN TaxonNode ptn ON ptn.id = tn.parent_id "
+ + " LEFT JOIN TaxonBase pt ON ptn.taxon_id = pt.id "
+ + " WHERE t.publish = 1 && pt.publish = 1 "
+ + " ORDER BY CAST(t.name_id as char(20)) ");
+
ResultSet destRS = destination.getResultSet("SELECT rel.*, t1.IdInSource t1Id, t2.IdInSource t2Id "
+ " FROM RelTaxon rel "
+ " LEFT JOIN Taxon t1 ON t1.TaxonId = rel.TaxonFk1 "
+ " LEFT JOIN Taxon t2 ON t2.TaxonId = rel.TaxonFk2 "
- + " WHERE t1."+origEuroMed+" AND t2." + origEuroMed
+ + " WHERE t1."+origEuroMed+" AND t2." + origEuroMed + " AND RelTaxonQualifierFk = 101 "
+ " ORDER BY t1.IdInSource");
int i = 0;
while (srcRS.next() && destRS.next()){
}
private boolean testSingleTaxonRelation(ResultSet srcRS, ResultSet destRS) throws SQLException {
- String id = String.valueOf(srcRS.getInt("id"));
- boolean success = equals("Taxon relation taxon1", "tu_id: " + srcRS.getInt("id"), destRS.getString("t1Id"), id);
- success &= equals("Taxon relation taxon2", "tu_id: " + srcRS.getInt("tu_acctaxon"), destRS.getString("t2Id"), id);
- success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_SYNONYM_OF, destRS.getInt("RelTaxonQualifierFk"), id);
- success &= equals("Taxon relation qualifier cache", "is synonym of", destRS.getString("RelQualifierCache"), id);
+ String id = String.valueOf(srcRS.getInt("tid"));
+ boolean success = equals("Taxon relation taxon1", "NameId: " + srcRS.getInt("tid"), destRS.getString("t1Id"), id);
+ success &= equals("Taxon relation taxon2", "NameId: " + srcRS.getInt("pid"), destRS.getString("t2Id"), id);
+ success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_TAXONOMICALLY_INCLUDED_IN, destRS.getInt("RelTaxonQualifierFk"), id);
+ success &= equals("Taxon relation qualifier cache", "is taxonomically included in", destRS.getString("RelQualifierCache"), id);
//TODO enable after next import
-// success &= isNull("notes", destRS);
+ success &= isNull("notes", destRS);
//complete if no further relations need to added
return success;
}
+ " AND NOT (NoteCategoryFk = 4 AND no.LastAction IS NULL) AND NOT NoteCategoryFk IN (22,23,24) "
+ " ORDER BY t.IdInSource, no.NoteCategoryCache, Note_1 ");
int count = 0;
- ResultSet srcRsLastAction = source.getResultSet(""
- + " SELECT no.id, s.sessiondate, a.action_name, s.ExpertName "
- + " FROM notes no "
- + " INNER JOIN tu ON tu.id = no.tu_id "
- + " LEFT JOIN languages l ON l.LanID = no.lan_id"
- + " LEFT JOIN notes_sessions MN ON no.id = MN.note_id "
- + " LEFT JOIN actions a ON a.id = MN.action_id "
- + " LEFT JOIN sessions s ON s.id = MN.session_id "
- + " ORDER BY CAST(tu.id as char(20)), no.type, no.noteSortable, s.sessiondate DESC, a.id DESC ");
-
while (srcRs.next() && destRs.next()){
success &= testSingleNote(srcRs, destRs);
- success &= testLastAction(srcRsLastAction, destRs, String.valueOf(srcRs.getInt("id")), "Note");
count++;
}
success &= equals("Notes count differs", n, count, "-1");
+ " FROM CommonName cn INNER JOIN Taxon t ON t.TaxonId = cn.TaxonFk LEFT JOIN Language l ON l.LanguageId = cn.LanguageFk "
+ " WHERE t." + origEuroMed
+ " ORDER BY t.IdInSource, ISNULL("+preferredISO639+", "+alternativeISO639+"), cn.CommonName, cn.LastActionDate "); //sorting also lastActionDate results in a minimum of exact duplicate problems
- ResultSet srcRsLastAction = source.getResultSet(""
- + " SELECT v.id, s.sessiondate, a.action_name, s.ExpertName "
- + " FROM vernaculars v "
- + " INNER JOIN tu ON tu.id = v.tu_id "
- + " LEFT JOIN languages l ON l.LanID = v.lan_id"
- + " LEFT JOIN vernaculars_sessions MN ON v.id = MN.vernacular_id "
- + " LEFT JOIN actions a ON a.id = MN.action_id "
- + " LEFT JOIN sessions s ON s.id = MN.session_id "
- + " ORDER BY CAST(tu.id as char(20)), ISNULL([639_3],[639_2]), v.vername, v.id, s.sessiondate DESC, a.id DESC ");
int count = 0;
while (srcRs.next() && destRs.next()){
success &= testSingleCommonName(srcRs, destRs);
- success &= testLastAction(srcRsLastAction, destRs, String.valueOf(srcRs.getInt("id")), "CommonName");
count++;
}
success &= equals("Common name count differs", n, count, "-1");
return result;
}
- private boolean testSingleReferences() throws SQLException {
+ private boolean testSingleReferences(int count) throws SQLException {
boolean success = true;
- ResultSet srcRS = source.getResultSet("SELECT r.* FROM Reference r ORDER BY r.id ");
+ ResultSet srcRS = source.getResultSet("SELECT r.*, a.titleCache author "
+ + " FROM Reference r LEFT OUTER JOIN AgentBase a ON r.authorship_id = a.id "
+ + " ORDER BY r.id ");
ResultSet destRS = destination.getResultSet("SELECT s.* FROM Source s "
+ " WHERE s." + origEuroMed
+ " ORDER BY s.RefIdInSource "); // +1 for the source reference "erms" but this has no OriginalDB
+ int i = 0;
while (srcRS.next() && destRS.next()){
success &= testSingleReference(srcRS, destRS);
+ i++;
}
+ success &= equals("References count differs", count, i, "-1");
return success;
}
success &= equals("Reference name ", srcRS.getString("titleCache"), destRS.getString("Name"), id);
success &= equals("Reference abstract ", srcRS.getString("referenceAbstract"), destRS.getString("Abstract"), id);
success &= equals("Reference title ", srcRS.getString("title"), destRS.getString("Title"), id);
-// success &= equals("Reference author string ", srcRS.getString("source_author"), destRS.getString("AuthorString"), id);
-// success &= equals("Reference year ", normalizeYear(srcRS.getString("source_year")), destRS.getString("RefYear"), id);
- success &= equals("Reference NomRefCache ", srcRS.getString("abbrevTitleCache"), destRS.getString("NomRefCache"), id);
- //TODO DOI
-// success &= equals("Reference link ", srcRS.getString("source_link"), destRS.getString("Link"), id);
+ success &= equals("Reference author string ", srcRS.getString("author"), destRS.getString("AuthorString"), id);
+ //TODO
+ success &= equals("Reference year ", normalizeYear(srcRS), destRS.getString("RefYear"), id);
+ //FIXME
+// success &= equals("Reference NomRefCache ", srcRS.getString("abbrevTitleCache"), destRS.getString("NomRefCache"), id);
+ success &= equals("Reference DOI ", srcRS.getString("doi"), destRS.getString("Doi"), id);
+ success &= equals("Reference link ", srcRS.getString("uri"), destRS.getString("Link"), id);
+ //TODO Notes
// success &= equals("Reference note ", srcRS.getString("source_note"), destRS.getString("Notes"), id);
- //TODO see above
//complete
return success;
}
private Integer convertSourceTypeFk(String sourceType) {
if (sourceType == null){
return null;
- }else if ("d".equals(sourceType)){
- return 4;
- }else if ("e".equals(sourceType)){
- return 5;
- }else if ("p".equals(sourceType)){
- return 11;
+ }else if ("DB".equals(sourceType)){
+ return PesiTransformer.REF_DATABASE;
+ }else if ("JOU".equals(sourceType)){
+ return PesiTransformer.REF_JOURNAL;
+ }else if ("BK".equals(sourceType)){
+ return PesiTransformer.REF_BOOK;
+ }else if ("GEN".equals(sourceType)){
+ return PesiTransformer.REF_UNRESOLVED;
+ }else if ("SER".equals(sourceType)){
+// TODO correct?
+ return PesiTransformer.REF_UNRESOLVED;
}else if ("i".equals(sourceType)){
return 12;
}
private String convertSourceTypeCache(String sourceType) {
if (sourceType == null){
return null;
- }else if ("d".equals(sourceType)){
+ }else if ("DB".equals(sourceType)){
return "database";
- }else if ("e".equals(sourceType)){
- return "informal reference";
- }else if ("p".equals(sourceType)){
- return "publication";
+ }else if ("JOU".equals(sourceType)){
+ return "journal";
+ }else if ("BK".equals(sourceType)){
+ return "book";
+ }else if ("SER".equals(sourceType)){
+ return "published";
+ }else if ("BK".equals(sourceType)){
+ return "book";
+ }else if ("GEN".equals(sourceType)){
+ return "unresolved";
}else if ("i".equals(sourceType)){
//TODO
return "i";
}
private boolean testReferenceCount() {
- int countSrc = source.getUniqueInteger("SELECT count(*) FROM reference ");
+ int countSrc = source.getUniqueInteger(countReferencesStr);
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Source s WHERE s."+ origEuroMed); // +1 for the source reference "erms" but this has no OriginalDB
boolean success = equals("Reference count ", countSrc, countDest, "-1");
return success;
}
- private String normalizeYear(String yearStr) {
- if (StringUtils.isBlank(yearStr)){
- return yearStr;
+ private String normalizeYear(ResultSet rs) throws SQLException {
+ String freetext = rs.getString("datePublished_freetext");
+ if(StringUtils.isNotBlank(freetext)){
+ return freetext;
}
- yearStr = yearStr.trim();
- if (yearStr.matches("\\d{4}-\\d{2}")){
- yearStr = yearStr.substring(0, 5)+yearStr.substring(0, 2)+yearStr.substring(5);
+ String start = rs.getString("datePublished_start");
+ String end = rs.getString("datePublished_end");
+ if (start != null){
+ start = start.substring(0,4);
}
- return yearStr;
+ if (end != null){
+ end = end.substring(0,4);
+ }
+ String result = start == null? null: start + (end==null? "": "-"+ end);
+ return result;
}
private boolean isNull(String attrName, ResultSet destRS) throws SQLException {
BGBM BDI Projects / cdmlib-apps.git / commitdiff