Revision 506f2414
Added by Andreas Müller over 4 years ago
cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/euromed/PesiEuroMedValidator.java | ||
---|---|---|
25 | 25 |
import eu.etaxonomy.cdm.io.common.Source; |
26 | 26 |
import eu.etaxonomy.cdm.io.pesi.out.PesiTransformer; |
27 | 27 |
import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm; |
28 |
import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType; |
|
28 | 29 |
|
29 | 30 |
/** |
30 | 31 |
* Tests the ERMS -> PESI pipeline by comparing the source DB with destination PESI DB. |
... | ... | |
40 | 41 |
// private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_EM2PESI(); |
41 | 42 |
private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_EM2PESI_2(); |
42 | 43 |
|
44 |
boolean doReferences = false; |
|
45 |
boolean doTaxa = true; |
|
46 |
boolean doTaxRels = false; |
|
47 |
boolean doDistributions = false; |
|
48 |
boolean doCommonNames = false; |
|
49 |
boolean doNotes = false; |
|
50 |
boolean doAdditionalTaxonSources = false; |
|
51 |
|
|
43 | 52 |
private Source source = new Source(defaultSource); |
44 | 53 |
private Source destination = defaultDestination; |
45 | 54 |
|
... | ... | |
51 | 60 |
try { |
52 | 61 |
this.source = source; |
53 | 62 |
this.destination = destination; |
54 |
// success &= testReferences(); |
|
55 |
success &= testTaxa(); |
|
56 |
// success &= testTaxonRelations(); |
|
57 |
// success &= testDistributions(); |
|
58 |
// success &= testNotes(); |
|
59 |
// success &= testAdditionalTaxonSources(); |
|
63 |
success &= testReferences(); |
|
64 |
success &= testTaxa(); |
|
65 |
success &= testTaxonRelations(); |
|
66 |
success &= testDistributions(); |
|
67 |
success &= testCommonNames(); |
|
68 |
success &= testNotes(); |
|
69 |
success &= testAdditionalTaxonSources(); |
|
60 | 70 |
} catch (Exception e) { |
61 | 71 |
e.printStackTrace(); |
62 | 72 |
success = false; |
... | ... | |
66 | 76 |
} |
67 | 77 |
|
68 | 78 |
private boolean testAdditionalTaxonSources() throws SQLException { |
79 |
if (!doAdditionalTaxonSources){ |
|
80 |
return true; |
|
81 |
} |
|
69 | 82 |
System.out.println("Start validate additional taxon sources"); |
70 | 83 |
boolean success = testAdditionalTaxonSourcesCount(); |
71 | 84 |
if (success){ |
... | ... | |
75 | 88 |
} |
76 | 89 |
|
77 | 90 |
private boolean testNotes() throws SQLException { |
91 |
if (!doNotes){ |
|
92 |
return true; |
|
93 |
} |
|
78 | 94 |
System.out.println("Start validate notes"); |
79 | 95 |
boolean success = testNotesCount(); |
80 | 96 |
if (success){ |
... | ... | |
84 | 100 |
} |
85 | 101 |
|
86 | 102 |
private boolean testDistributions() throws SQLException { |
103 |
if (!doDistributions){ |
|
104 |
return true; |
|
105 |
} |
|
87 | 106 |
System.out.println("Start validate distributions"); |
88 | 107 |
boolean success = testDistributionCount(); |
89 | 108 |
if (!success){ |
... | ... | |
93 | 112 |
} |
94 | 113 |
|
95 | 114 |
private boolean testCommonNames() throws SQLException { |
115 |
if (!doCommonNames){ |
|
116 |
return true; |
|
117 |
} |
|
96 | 118 |
System.out.println("Start validate common names"); |
97 | 119 |
boolean success = testCommonNameCount(); |
98 | 120 |
if (success){ |
... | ... | |
103 | 125 |
|
104 | 126 |
int countSynonyms; |
105 | 127 |
int countIncludedIns; |
106 |
private boolean testTaxonRelations() { |
|
128 |
private boolean testTaxonRelations() throws SQLException { |
|
129 |
if (!doTaxRels){ |
|
130 |
return true; |
|
131 |
} |
|
107 | 132 |
System.out.println("Start validate taxon relations"); |
108 | 133 |
boolean success = testSynonymRelations(); |
109 | 134 |
success &= testIncludedInRelations(); |
... | ... | |
125 | 150 |
} |
126 | 151 |
} |
127 | 152 |
|
128 |
private boolean testSynonymRelations() { |
|
153 |
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 "; |
|
154 |
private boolean testSynonymRelations() throws SQLException { |
|
129 | 155 |
|
130 | 156 |
int countSrc = source.getUniqueInteger(countSynonymRelation); |
131 | 157 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM RelTaxon WHERE RelTaxonQualifierFk > 101"); |
132 | 158 |
boolean success = equals("Synonym count ", countSrc, countDest, String.valueOf(-1)); |
133 |
// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason like 'currently placed%' |
|
134 |
// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason like 'currently held%' |
|
135 |
// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason like 'sy%' or tu_unacceptreason like '%jun%syn%' |
|
136 |
// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason = '(synonym)' |
|
137 |
// update Match_RelStat set RelTaxon = 102 where tu_unacceptreason = 'reverted genus transfer' |
|
138 |
// update Match_RelStat set RelTaxon = 103 where tu_unacceptreason like 'misapplied%' |
|
139 |
// update Match_RelStat set RelTaxon = 104 where tu_unacceptreason like 'part% synonym%' |
|
140 |
// update Match_RelStat set RelTaxon = 106 where tu_unacceptreason = 'heterotypic synonym' or tu_unacceptreason = 'subjective synonym' |
|
141 |
// update Match_RelStat set RelTaxon = 107 where tu_unacceptreason like '%homot%syn%' or tu_unacceptreason = 'objective synonym' synyonym |
|
142 |
// update Match_RelStat set RelTaxon = 107 where tu_unacceptreason like '%bas[iy][no]%ny%' |
|
143 | 159 |
if (success){ |
144 | 160 |
//TODO test single synonym relations |
145 |
// success &= testSingleTaxonRelations(source.getUniqueInteger(countSynonymRelation));
|
|
161 |
success &= testSingleSynonymRelations(source.getUniqueInteger(countSynonymRelation));
|
|
146 | 162 |
} |
147 | 163 |
countSynonyms = (countSrc == countDest)? countSrc : -1; |
148 | 164 |
return success; |
149 | 165 |
} |
150 | 166 |
|
167 |
private boolean testSingleSynonymRelations(int n) throws SQLException { |
|
168 |
boolean success = true; |
|
169 |
ResultSet srcRS = source.getResultSet("" |
|
170 |
+ " SELECT t.id tid, pt.id pid " |
|
171 |
+ " FROM TaxonNode tn " |
|
172 |
+ " INNER JOIN TaxonBase t ON tn.taxon_id = t.id " |
|
173 |
+ " LEFT JOIN TaxonNode ptn ON ptn.id = tn.parent_id " |
|
174 |
+ " LEFT JOIN TaxonBase pt ON ptn.taxon_id = pt.id " |
|
175 |
+ " WHERE t.publish = 1 && pt.publish = 1 " |
|
176 |
+ " ORDER BY CAST(tb.id as char(20)) "); |
|
177 |
|
|
178 |
ResultSet destRS = destination.getResultSet("SELECT rel.*, t1.IdInSource t1Id, t2.IdInSource t2Id " |
|
179 |
+ " FROM RelTaxon rel " |
|
180 |
+ " LEFT JOIN Taxon t1 ON t1.TaxonId = rel.TaxonFk1 " |
|
181 |
+ " LEFT JOIN Taxon t2 ON t2.TaxonId = rel.TaxonFk2 " |
|
182 |
+ " WHERE t1."+origEuroMed+" AND t2." + origEuroMed + " AND RelTaxonQualifierFk > 101 " |
|
183 |
+ " ORDER BY t1.IdInSource"); |
|
184 |
int i = 0; |
|
185 |
while (srcRS.next() && destRS.next()){ |
|
186 |
success &= testSingleSynonymRelation(srcRS, destRS); |
|
187 |
i++; |
|
188 |
} |
|
189 |
success &= equals("Synonym relation count for single compare", n, i, String.valueOf(-1)); |
|
190 |
return success; |
|
191 |
} |
|
192 |
|
|
193 |
private boolean testSingleSynonymRelation(ResultSet srcRS, ResultSet destRS) throws SQLException { |
|
194 |
String id = String.valueOf(srcRS.getInt("id")); |
|
195 |
boolean success = equals("Taxon relation taxon1", "NameId: " + srcRS.getInt("id"), destRS.getString("t1Id"), id); |
|
196 |
success &= equals("Taxon relation taxon2", "NameId: " + srcRS.getInt("tu_acctaxon"), destRS.getString("t2Id"), id); |
|
197 |
success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_SYNONYM_OF, destRS.getInt("RelTaxonQualifierFk"), id); |
|
198 |
success &= equals("Taxon relation qualifier cache", "is synonym of", destRS.getString("RelQualifierCache"), id); |
|
199 |
//TODO enable after next import |
|
200 |
// success &= isNull("notes", destRS); |
|
201 |
//complete if no further relations need to added |
|
202 |
return success; |
|
203 |
} |
|
204 |
|
|
151 | 205 |
private boolean testNameRelations() { |
152 | 206 |
//Name relations |
153 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM tu WHERE (" |
|
154 |
+ " tu_unacceptreason like '%bas[iy][no]%ny%' OR tu_unacceptreason = 'original combination' " |
|
155 |
+ " OR tu_unacceptreason = 'Subsequent combination' OR tu_unacceptreason like '%genus transfer%' " |
|
156 |
+ " OR tu_unacceptreason = 'genus change' " //1 |
|
157 |
+ " OR tu_unacceptreason like '%homon%' " // 2 |
|
158 |
+ " OR tu_unacceptreason like '%spell%' OR tu_unacceptreason like 'lapsus %' " //16 |
|
159 |
|
|
207 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM NameRelationship WHERE (" |
|
208 |
+ " 1=1 " |
|
160 | 209 |
+ ")"); |
161 | 210 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM RelTaxon WHERE RelTaxonQualifierFk <100 "); |
162 | 211 |
boolean success = equals("Taxon name relation count ", countSrc, countDest, String.valueOf(-1)); |
163 | 212 |
if (success){ |
164 | 213 |
//TODO test single name relation |
165 |
// success &= testSingleTaxonRelations(source.getUniqueInteger(countSynonymRelation));
|
|
214 |
// success &= testSingleNameRelations(source.getUniqueInteger(countSynonymRelation));
|
|
166 | 215 |
} |
167 | 216 |
return success; |
168 | 217 |
} |
169 | 218 |
|
170 |
private boolean testIncludedInRelations() { |
|
219 |
private final String countParentRelation = "SELECT count(*) " |
|
220 |
+ " FROM TaxonNode tn " |
|
221 |
+ " INNER JOIN TaxonBase tb ON tn.taxon_id = tb.id " |
|
222 |
+ " LEFT JOIN TaxonNode ptn ON ptn.id = tn.parent_id " |
|
223 |
+ " LEFT JOIN TaxonBase pt ON ptn.taxon_id = pt.id " |
|
224 |
+ " WHERE tb.publish = 1 && pt.publish = 1 "; |
|
225 |
|
|
226 |
private boolean testIncludedInRelations() throws SQLException { |
|
171 | 227 |
int countSrc = source.getUniqueInteger(countParentRelation); |
172 | 228 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM RelTaxon WHERE RelTaxonQualifierFk = 101 "); |
173 | 229 |
boolean success = equals("Tax included in count ", countSrc, countDest, String.valueOf(-1)); |
174 | 230 |
if (success){ |
175 |
//TODO test single includedIn relations |
|
176 |
// success &= testSingleTaxonRelations(source.getUniqueInteger(countSynonymRelation)); |
|
231 |
success &= testSingleTaxonRelations(source.getUniqueInteger(countParentRelation)); |
|
177 | 232 |
} |
178 | 233 |
countIncludedIns = (countSrc == countDest)? countSrc : -1; |
179 | 234 |
return success; |
180 | 235 |
} |
181 | 236 |
|
182 | 237 |
private boolean testTaxa() throws SQLException { |
238 |
if (!doTaxa){ |
|
239 |
return true; |
|
240 |
} |
|
183 | 241 |
System.out.println("Start validate taxa"); |
184 | 242 |
boolean success = testTaxaCount(); |
185 |
//FIXME |
|
186 | 243 |
if (success){ |
187 | 244 |
success &= testSingleTaxa(source.getUniqueInteger(countTaxon)); |
188 | 245 |
} |
189 | 246 |
return success; |
190 | 247 |
} |
191 | 248 |
|
249 |
String countReferencesStr = "SELECT count(*) FROM reference "; |
|
192 | 250 |
private boolean testReferences() throws SQLException { |
251 |
if (!doReferences){ |
|
252 |
return true; |
|
253 |
} |
|
193 | 254 |
System.out.println("Start validate references"); |
194 | 255 |
boolean success = testReferenceCount(); |
195 | 256 |
if (success){ |
196 |
success &= testSingleReferences(); |
|
257 |
success &= testSingleReferences(source.getUniqueInteger(countReferencesStr));
|
|
197 | 258 |
} |
198 | 259 |
return success; |
199 | 260 |
} |
... | ... | |
208 | 269 |
private boolean testNotesCount() { |
209 | 270 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM notes "); |
210 | 271 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Note " |
211 |
+ " WHERE NOT (NoteCategoryFk = 4 AND LastAction IS NULL) AND NOT NoteCategoryFk IN (22,23,24) ");
|
|
272 |
+ " WHERE (1=1) ");
|
|
212 | 273 |
boolean result = equals("Notes count ", countSrc, countDest, String.valueOf(-1)); |
213 | 274 |
|
214 |
countSrc = source.getUniqueInteger("SELECT count(*) FROM tu " |
|
215 |
+ " WHERE (tu_marine IS NOT NULL OR tu_brackish IS NOT NULL OR tu_fresh IS NOT NULL OR tu_terrestrial IS NOT NULL) " ); |
|
216 |
countDest = destination.getUniqueInteger("SELECT count(*) FROM Note " |
|
217 |
+ " WHERE (NoteCategoryFk = 4 AND LastAction IS NULL) "); |
|
218 |
result &= equals("Notes ecology count ", countSrc, countDest, String.valueOf(-1)); |
|
219 |
|
|
220 |
countSrc = source.getUniqueInteger("SELECT count(*) FROM links "); |
|
221 |
countDest = destination.getUniqueInteger("SELECT count(*) FROM Note " |
|
222 |
+ " WHERE NoteCategoryFk IN (22,23,24) "); |
|
223 |
result &= equals("Notes link count ", countSrc, countDest, String.valueOf(-1)); |
|
224 |
|
|
225 | 275 |
return result; |
226 | 276 |
} |
227 | 277 |
|
... | ... | |
248 | 298 |
return equals("CommonName count ", countSrc, countDest, String.valueOf(-1)); |
249 | 299 |
} |
250 | 300 |
|
251 |
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) "; |
|
252 |
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) "; |
|
253 |
|
|
254 | 301 |
private final String countTaxon = "SELECT count(*) FROM TaxonBase tb WHERE tb.publish = 1 "; |
255 | 302 |
private boolean testTaxaCount() { |
256 | 303 |
int countSrc = source.getUniqueInteger(countTaxon); |
257 | 304 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon t WHERE t.SourceFk IS NOT NULL OR t.AuthorString = 'auct.' "); |
258 | 305 |
boolean result = equals("Taxon count ", countSrc, countDest, String.valueOf(-1)); |
259 |
|
|
260 |
// //NomStatus |
|
261 |
// countSrc = source.getUniqueInteger("SELECT count(*) FROM tu WHERE (" |
|
262 |
// + " tu_unacceptreason like '%inval%' OR tu_unacceptreason like '%not val%' " |
|
263 |
// + " OR tu_unacceptreason like '%illeg%' OR tu_unacceptreason like '%nud%' " |
|
264 |
// + " OR tu_unacceptreason like '%rej.%' OR tu_unacceptreason like '%superfl%' " |
|
265 |
// + " OR tu_unacceptreason like '%Comb. nov%' OR tu_unacceptreason like '%New name%' " |
|
266 |
// + " OR tu_unacceptreason = 'new combination' " |
|
267 |
// + " OR tu_status IN (3,5,6,7,8) )"); |
|
268 |
// countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon WHERE NameStatusFk IS NOT NULL "); |
|
269 |
// result = equals("Taxon name status count ", countSrc, countDest, String.valueOf(-1)); |
|
270 |
|
|
271 | 306 |
return result; |
272 | 307 |
} |
273 | 308 |
|
274 | 309 |
private boolean testSingleTaxa(int n) throws SQLException { |
275 | 310 |
boolean success = true; |
276 |
ResultSet srcRS = source.getResultSet("SELECT CAST(tn.id as char(20)) tid, tb.uuid as GUID, tn.rank_id, rank.titleCache rank_name, " |
|
311 |
ResultSet srcRS = source.getResultSet("SELECT CAST(tn.id as char(20)) tid, tb.uuid as GUID, pt.id parentId, " |
|
312 |
+ " tn.rank_id, rank.titleCache rank_name, " |
|
277 | 313 |
+ " sec.titleCache secTitle," |
278 | 314 |
+ " tn.genusOrUninomial, tn.infraGenericEpithet, tn.specificEpithet, tn.infraSpecificEpithet, " |
279 |
+ " tn.nameCache, tn.authorshipCache, tn.titleCache nameTitleCache, " |
|
280 |
+ " tb.DTYPE taxStatus, nsType.id nsId, nsType.idInVocabulary nsTitle, " |
|
315 |
+ " tn.nameCache, tn.authorshipCache, tn.titleCache nameTitleCache, tn.fullTitleCache nameFullTitleCache, " |
|
316 |
+ " tb.DTYPE taxStatus, taxRelType.uuid taxRelTypeUuid, nsType.id nsId, nsType.idInVocabulary nsTitle, " |
|
317 |
+ " typeName_id, typeName.titleCache typeFullNameCache, " |
|
281 | 318 |
+ " CASE WHEN tb.updated IS NOT NULL THEN tb.updated ELSE tb.created END as lastActionDate, " |
282 | 319 |
+ " CASE WHEN tb.updated IS NOT NULL THEN 'changed' ELSE 'created' END as lastAction " |
283 | 320 |
+ " FROM TaxonBase tb " |
... | ... | |
287 | 324 |
+ " LEFT JOIN TaxonName_NomenclaturalStatus nsMN ON tn.id = nsMN.TaxonName_id " |
288 | 325 |
+ " LEFT JOIN NomenclaturalStatus ns ON ns.id = nsMN.status_id " |
289 | 326 |
+ " LEFT JOIN DefinedTermBase nsType ON nsType.id = ns.type_id " |
327 |
+ " LEFT JOIN TaxonName_TypeDesignationBase typeMN ON typeMN.TaxonName_id = tn.id " |
|
328 |
+ " LEFT JOIN TypeDesignationBase td ON td.id = typeMN.typedesignations_id " |
|
329 |
+ " LEFT JOIN TaxonName typeName ON typeName.id = td.typeName_id " |
|
330 |
+ " LEFT JOIN TaxonNode n ON n.taxon_id = tb.id " |
|
331 |
+ " LEFT JOIN TaxonNode ptn ON n.parent_id = ptn.id " |
|
332 |
+ " LEFT JOIN TaxonBase pt ON pt.id = ptn.taxon_id AND pt.publish = 1 " |
|
333 |
+ " LEFT JOIN TaxonRelationship tr ON tr.relatedFrom_id = tb.id " |
|
334 |
+ " LEFT JOIN DefinedTermBase taxRelType ON taxRelType.id = tr.type_id" |
|
290 | 335 |
+ " WHERE tb.publish = 1 " |
291 | 336 |
+ " GROUP BY tid, GUID, tn.rank_id, rank.titleCache, secTitle," |
292 | 337 |
+ " tn.genusOrUninomial, tn.infraGenericEpithet, tn.specificEpithet, tn.infraSpecificEpithet, " |
... | ... | |
294 | 339 |
+ " tb.DTYPE, tb.updated, tb.created " //for duplicates caused by >1 name status |
295 | 340 |
+ " ORDER BY tid, GUID, lastActionDate "); |
296 | 341 |
ResultSet destRS = destination.getResultSet("SELECT t.*, " |
297 |
+ " pt.GenusOrUninomial p_GenusOrUninomial, pt.InfraGenericEpithet p_InfraGenericEpithet, pt.SpecificEpithet p_SpecificEpithet, "
|
|
342 |
+ " pt.IdInSource parentSourceId, " //not needed
|
|
298 | 343 |
+ " s.Name as sourceName, type.IdInSource typeSourceId, r.Rank " |
299 | 344 |
+ " FROM Taxon t " |
300 | 345 |
+ " LEFT JOIN Taxon pt ON pt.TaxonId = t.ParentTaxonFk " |
... | ... | |
313 | 358 |
return success; |
314 | 359 |
} |
315 | 360 |
|
316 |
|
|
317 | 361 |
private boolean testSingleTaxon(ResultSet srcRS, ResultSet destRS) throws SQLException { |
318 | 362 |
String id = String.valueOf(srcRS.getInt("tid")); |
319 | 363 |
//TODO decide, according to SQL it also contains the taxon UUID, but in PESI2014 backup I can't find this |
... | ... | |
322 | 366 |
|
323 | 367 |
success &= equals("Taxon kingdomFk", "3", destRS.getString("KingdomFk"), id); |
324 | 368 |
//difficult to test success &= equals("Taxon rank fk", srcRS.getString("rank_id"), destRS.getString("RankFk"), id); |
325 |
success &= equals("Taxon rank cache", normalizeRank(srcRS.getString("rank_name"), srcRS, id), destRS.getString("Rank"), id);
|
|
369 |
success &= equals("Taxon rank cache", normalizeRank(srcRS.getString("rank_name")), destRS.getString("Rank"), id); |
|
326 | 370 |
success &= equals("Taxon genusOrUninomial", srcRS.getString("genusOrUninomial"), destRS.getString("GenusOrUninomial"), id) ; |
327 | 371 |
success &= equals("Taxon infraGenericEpithet", srcRS.getString("infraGenericEpithet"), destRS.getString("InfraGenericEpithet"), id) ; |
328 | 372 |
success &= equals("Taxon specificEpithet", srcRS.getString("specificEpithet"), destRS.getString("SpecificEpithet"), id) ; |
... | ... | |
332 | 376 |
//TODO success &= equals("Taxon WebShowName", srcRS.getString("tu_displayname"), destRS.getString("WebShowName"), id); |
333 | 377 |
//FIXME sensu+auct. autoren success &= equals("Taxon authority", srcRS.getString("authorshipCache"), destRS.getString("AuthorString"), id); |
334 | 378 |
//FIXME sensu+auct. autoren success &= equals("Taxon FullName", srcRS.getString("nameTitleCache"), destRS.getString("FullName"), id); |
335 |
//TODO success &= isNull("NomRefString", destRS); |
|
336 |
//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 |
|
337 |
|
|
338 |
//TODO success &= equals("Taxon NameStatusFk", nullSafeInt(srcRS, "nsId"),nullSafeInt( destRS,"NameStatusFk"), id); |
|
379 |
success &= equals("Taxon NomRefString", makeNomRefString(srcRS), destRS.getString("NomRefString"), id); |
|
380 |
success &= equals("Taxon DisplayName", makeDisplayName(srcRS), destRS.getString("DisplayName"), id); //in ERMS according to SQL script same as FullName, no nom.ref. information attached |
|
381 |
//difficult to test success &= equals("Taxon NameStatusFk", nullSafeInt(srcRS, "nsId"),nullSafeInt( destRS,"NameStatusFk"), id); |
|
339 | 382 |
success &= equals("Taxon NameStatusCache", srcRS.getString("nsTitle"), destRS.getString("NameStatusCache"), id); |
340 | 383 |
|
341 |
// success &= equals("Taxon TaxonStatusFk", mapTaxStatusFk(srcRS.getString("taxStatus")), nullSafeInt( destRS,"TaxonStatusFk"), id);
|
|
342 |
// success &= equals("Taxon TaxonStatusCache", mapTaxStatus(srcRS.getString("taxStatus")), destRS.getString("TaxonStatusCache"), id);
|
|
384 |
//reimport success &= equals("Taxon TaxonStatusFk", mapTaxStatusFk(srcRS.getString("taxStatus"), srcRS.getString("taxRelTypeUuid")), nullSafeInt( destRS,"TaxonStatusFk"), id);
|
|
385 |
//reimport success &= equals("Taxon TaxonStatusCache", mapTaxStatus(srcRS.getString("taxStatus"), srcRS.getString("taxRelTypeUuid")), destRS.getString("TaxonStatusCache"), id);
|
|
343 | 386 |
|
344 |
// //TODO ParentTaxonFk |
|
345 |
// Integer orgigTypeNameFk = nullSafeInt(srcRS, "tu_typetaxon"); |
|
346 |
// success &= equals("Taxon TypeNameFk", orgigTypeNameFk == null? null : "tu_id: " + orgigTypeNameFk, destRS.getString("typeSourceId"), id); |
|
347 |
////TODO success &= equals("Taxon TypeFullNameCache", CdmUtils.concat(" ", srcRS.getString("typename"), srcRS.getString("typeauthor")), destRS.getString("TypeFullNameCache"), id); |
|
348 |
//quality status, according to SQL always constant, could be changed in future |
|
387 |
success &= equals("Taxon ParentTaxonFk", nullSafeInt(srcRS, "parentId"), nullSafeInt(destRS, "ParentTaxonFk"), id); |
|
388 |
|
|
389 |
Integer origTypeNameFk = nullSafeInt(srcRS, "typeName_id"); |
|
390 |
success &= equals("Taxon TypeNameFk", origTypeNameFk == null? null : "NameId: " + origTypeNameFk, destRS.getString("typeSourceId"), id); |
|
391 |
success &= equals("Taxon TypeFullNameCache", srcRS.getString("typeFullNameCache"), destRS.getString("TypeFullNameCache"), id); |
|
392 |
//according to SQL always constant, could be changed in future |
|
349 | 393 |
success &= equals("Taxon QualityStatusFK", 2, nullSafeInt( destRS,"QualityStatusFk"), String.valueOf(id)); |
350 | 394 |
success &= equals("Taxon QualityStatusCache", "Added by Database Management Team", destRS.getString("QualityStatusCache"), id); |
351 |
// //TODO TreeIndex
|
|
352 |
success &= isNull("FossilStatusFk", destRS);
|
|
353 |
success &= isNull("FossilStatusCache", destRS);
|
|
395 |
//TODO TreeIndex |
|
396 |
success &= isNull("FossilStatusFk", destRS); |
|
397 |
success &= isNull("FossilStatusCache", destRS); |
|
354 | 398 |
success &= equals("Taxon GUID", srcRS.getString("GUID"), destRS.getString("GUID"), id); |
355 | 399 |
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 |
356 | 400 |
success &= isNull("ExpertGUID", destRS); //according to SQL + PESI2014 |
357 |
// success &= isNull("ExpertName", destRS);
|
|
358 |
// success &= isNull("SpeciesExpertGUID", destRS);
|
|
359 |
// success &= isNull("SpeciesExpertName", destRS); //only relevant after merge
|
|
401 |
//reimport success &= equals("Taxon ExpertName", srcRS.getString("secTitle"), destRS.getString("ExpertName"), id);
|
|
402 |
success &= isNull("SpeciesExpertGUID", destRS); |
|
403 |
//reimport success &= equals("Taxon SpeciesExpertName", srcRS.getString("secTitle"), destRS.getString("SpeciesExpertName"), id);
|
|
360 | 404 |
//FIXME !! success &= equals("Taxon cache citation", srcRS.getString("secTitle"), destRS.getString("CacheCitation"), id); |
361 | 405 |
success &= equals("Taxon Last Action", srcRS.getString("lastAction"), destRS.getString("LastAction"), id); |
362 | 406 |
success &= equals("Taxon Last Action Date", srcRS.getTimestamp("lastActionDate"), destRS.getTimestamp("LastActionDate"), id); |
... | ... | |
366 | 410 |
return success; |
367 | 411 |
} |
368 | 412 |
|
369 |
private String mapTaxStatus(String string) { |
|
370 |
if (string == null){ |
|
413 |
private String makeNomRefString(ResultSet srcRS) throws SQLException { |
|
414 |
//there is no pure nomRefString field in CDM and also computing is only possible |
|
415 |
//with cache strategy which requires a running CDM instance. So this is a workaround that maybe needs to be adapted |
|
416 |
String result = null; |
|
417 |
String fullTitle = srcRS.getString("nameFullTitleCache"); |
|
418 |
String nameTitleCache = srcRS.getString("nameTitleCache"); |
|
419 |
String nameStatus = CdmUtils.Nz(srcRS.getString("nsTitle")); |
|
420 |
if (fullTitle != null && nameTitleCache != null){ |
|
421 |
result = fullTitle.substring(nameTitleCache.length()) |
|
422 |
.replaceAll("^, ", "") |
|
423 |
.replaceAll("(, |^)"+nameStatus+"$", ""); |
|
424 |
} |
|
425 |
return result; |
|
426 |
} |
|
427 |
|
|
428 |
private String mapTaxStatus(String dtype, String taxRelTypeUuidStr) { |
|
429 |
Integer statusFk = mapTaxStatusFk(dtype, taxRelTypeUuidStr); |
|
430 |
if (statusFk == null){ |
|
371 | 431 |
return null; |
372 |
}else if ("Synonym".equals(string)){ |
|
373 |
return "synonym"; |
|
374 |
}else if ("Taxon".equals(string)){ |
|
432 |
}else if (statusFk == PesiTransformer.T_STATUS_ACCEPTED){ |
|
375 | 433 |
return "accepted"; |
434 |
}else if (statusFk == PesiTransformer.T_STATUS_SYNONYM){ |
|
435 |
return "synonym"; |
|
436 |
}else if (statusFk == PesiTransformer.T_STATUS_PRO_PARTE_SYN){ |
|
437 |
return "pro parte synonym"; |
|
438 |
}else if (statusFk == PesiTransformer.T_STATUS_PARTIAL_SYN){ |
|
439 |
return "partial synonym"; |
|
376 | 440 |
} |
377 | 441 |
return null; |
378 | 442 |
} |
379 | 443 |
|
380 |
private Integer mapTaxStatusFk(String string) {
|
|
381 |
if (string == null){
|
|
444 |
private Integer mapTaxStatusFk(String dtype, String taxRelTypeUuidStr) {
|
|
445 |
if (dtype == null){
|
|
382 | 446 |
return null; |
383 |
}else if ("Synonym".equals(string)){
|
|
447 |
}else if ("Synonym".equals(dtype)){
|
|
384 | 448 |
return PesiTransformer.T_STATUS_SYNONYM; |
385 |
}else if ("Taxon".equals(string)){ |
|
386 |
return PesiTransformer.T_STATUS_ACCEPTED; |
|
449 |
}else if ("Taxon".equals(dtype)){ |
|
450 |
UUID relTypeUuid = taxRelTypeUuidStr == null? null: UUID.fromString(taxRelTypeUuidStr); |
|
451 |
if (TaxonRelationshipType.proParteUuids().contains(relTypeUuid)){ |
|
452 |
return PesiTransformer.T_STATUS_PRO_PARTE_SYN; |
|
453 |
}else if (TaxonRelationshipType.partialUuids().contains(relTypeUuid)){ |
|
454 |
return PesiTransformer.T_STATUS_PARTIAL_SYN; |
|
455 |
}else if (TaxonRelationshipType.misappliedNameUuids().contains(relTypeUuid)){ |
|
456 |
return PesiTransformer.T_STATUS_SYNONYM; //no explicit MAN status exists in PESI |
|
457 |
}else{ |
|
458 |
return PesiTransformer.T_STATUS_ACCEPTED; |
|
459 |
} |
|
387 | 460 |
} |
388 | 461 |
return null; |
389 | 462 |
} |
390 | 463 |
|
391 |
private String normalizeRank(String rankStr, ResultSet srcRS, String id) throws SQLException {
|
|
464 |
private String normalizeRank(String rankStr) {
|
|
392 | 465 |
if (rankStr == null){return null; |
393 | 466 |
}else if (rankStr.equals("Convar")){return "Convariety"; |
394 | 467 |
}else if (rankStr.equals("Unranked (infrageneric)")){return "Tax. infragen."; |
... | ... | |
399 | 472 |
}return rankStr; |
400 | 473 |
} |
401 | 474 |
|
402 |
//see also ErmsTaxonImport.getExpectedTitleCache() |
|
403 |
private String srcFullName(ResultSet srcRs) throws SQLException { |
|
404 |
String result = null; |
|
405 |
String epi = srcRs.getString("tu_name"); |
|
406 |
epi = " a" + epi; |
|
407 |
String display = srcRs.getString("tu_displayname"); |
|
408 |
String sp = srcRs.getString("tu_sp"); |
|
409 |
if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#2#")){ //homonym, animal |
|
410 |
result = srcRs.getString("tu_displayname").replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" "; |
|
411 |
}else{ |
|
412 |
result = CdmUtils.concat(" ", srcRs.getString("tu_displayname"), srcRs.getString("tu_authority")); |
|
413 |
} |
|
475 |
private String makeDisplayName(ResultSet srcRs) throws SQLException { |
|
476 |
String nameCache = srcRs.getString("nameCache"); |
|
477 |
String nameTitle = srcRs.getString("nameTitleCache"); |
|
478 |
String taggedName = getTaggedNameTitle(nameCache, nameTitle); |
|
479 |
String fullNameTitle = srcRs.getString("nameFullTitleCache"); |
|
480 |
String result = fullNameTitle |
|
481 |
.replace(nameTitle, taggedName); |
|
414 | 482 |
return result; |
415 | 483 |
} |
416 | 484 |
|
417 |
private String srcDisplayName(ResultSet srcRs) throws SQLException { |
|
418 |
String result = null; |
|
419 |
String epi = srcRs.getString("tu_name"); |
|
420 |
epi = " a" + epi; |
|
421 |
String display = "<i>"+srcRs.getString("tu_displayname")+"</i>"; |
|
422 |
display = display.replace(" var. ", "</i> var. <i>").replace(" f. ", "</i> f. <i>"); |
|
423 |
String sp = srcRs.getString("tu_sp"); |
|
424 |
if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#2#")){ //homonym, animal |
|
425 |
result = display.replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" "; |
|
426 |
}else{ |
|
427 |
result = CdmUtils.concat(" ", display, srcRs.getString("tu_authority")); |
|
485 |
private String getTaggedNameTitle(String nameCache, String nameTitle) { |
|
486 |
if (nameCache == null){ |
|
487 |
logger.warn("NameCache is null"); |
|
488 |
return nameTitle; |
|
428 | 489 |
} |
429 |
return result; |
|
430 |
} |
|
431 |
|
|
432 |
String lastLastActionId = "-1"; |
|
433 |
private boolean testLastAction(ResultSet srcRsLastAction, ResultSet destRs, String id, String table) throws SQLException { |
|
490 |
String result = null; |
|
434 | 491 |
try { |
435 |
boolean success = true; |
|
436 |
String srcId = null; |
|
437 |
while (srcRsLastAction.next()){ |
|
438 |
srcId = String.valueOf(srcRsLastAction.getInt("id")); |
|
439 |
if (!lastLastActionId.equals(srcId)){ |
|
440 |
lastLastActionId = srcId; |
|
441 |
break; |
|
492 |
String[] nameCacheSplit = nameCache.split(" "); |
|
493 |
String[] nameTitleSplit = nameTitle.split(" "); |
|
494 |
result = ""; |
|
495 |
boolean currentIsName = false; |
|
496 |
for (int i=0, j=0; j < nameTitleSplit.length; j++){ |
|
497 |
if (i < nameCacheSplit.length && nameCacheSplit[i].equals(nameTitleSplit[j]) |
|
498 |
&& !isMarker(nameCacheSplit[i])){ |
|
499 |
if(!currentIsName){ |
|
500 |
result += " <i>" + nameCacheSplit[i]; |
|
501 |
currentIsName = true; |
|
502 |
}else{ |
|
503 |
result += " " + nameCacheSplit[i]; |
|
504 |
} |
|
505 |
if((j+1)==nameTitleSplit.length){ |
|
506 |
result += "</i>"; |
|
507 |
} |
|
508 |
i++; |
|
509 |
}else{ |
|
510 |
if(currentIsName){ |
|
511 |
result += "</i>"; |
|
512 |
currentIsName = false; |
|
513 |
} |
|
514 |
result += " " + nameTitleSplit[j]; |
|
515 |
if (i < nameCacheSplit.length && nameCacheSplit[i].equals(nameTitleSplit[j]) |
|
516 |
&& isMarker(nameCacheSplit[i])){ |
|
517 |
i++; |
|
518 |
} |
|
442 | 519 |
} |
443 | 520 |
} |
444 |
if(!id.equals(srcId)){ |
|
445 |
logger.warn("Last Action SourceIDs are not equal: id: " +id + ", la-id: " + srcId); |
|
446 |
} |
|
447 |
String destStr = destRs.getString("LastAction"); |
|
448 |
success &= equals(table + " SpeciesExpertName", srcRsLastAction.getString("ExpertName"), destRs.getString("SpeciesExpertName"), id); //mapping ExpertName => SpeciesExpertName according to SQL script |
|
449 |
success &= equals(table + " Last Action", srcRsLastAction.getString("action_name"), destStr == null? null : destStr, id); |
|
450 |
success &= equals(table + " Last Action Date", srcRsLastAction.getTimestamp("sessiondate"), destRs.getTimestamp("LastActionDate"), id); |
|
451 |
|
|
452 |
return success; |
|
521 |
return result.trim(); |
|
453 | 522 |
} catch (Exception e) { |
454 | 523 |
e.printStackTrace(); |
455 |
throw e;
|
|
524 |
return result;
|
|
456 | 525 |
} |
457 | 526 |
} |
458 | 527 |
|
528 |
private boolean isMarker(String nameCacheSplit) { |
|
529 |
return nameCacheSplit.endsWith(".") || nameCacheSplit.equals("[unranked]") ; |
|
530 |
} |
|
531 |
|
|
459 | 532 |
private boolean testSingleTaxonRelations(int n) throws SQLException { |
460 | 533 |
boolean success = true; |
461 | 534 |
ResultSet srcRS = source.getResultSet("" |
462 |
+ " SELECT t.* " |
|
463 |
+ " FROM tu t " |
|
464 |
+ " WHERE tu_acctaxon <> id " |
|
465 |
+ " ORDER BY CAST(t.id as char(20)) "); |
|
535 |
+ " SELECT t.name_id tid, pt.name_id pid " |
|
536 |
+ " FROM TaxonNode tn " |
|
537 |
+ " INNER JOIN TaxonBase t ON tn.taxon_id = t.id " |
|
538 |
+ " LEFT JOIN TaxonNode ptn ON ptn.id = tn.parent_id " |
|
539 |
+ " LEFT JOIN TaxonBase pt ON ptn.taxon_id = pt.id " |
|
540 |
+ " WHERE t.publish = 1 && pt.publish = 1 " |
|
541 |
+ " ORDER BY CAST(t.name_id as char(20)) "); |
|
542 |
|
|
466 | 543 |
ResultSet destRS = destination.getResultSet("SELECT rel.*, t1.IdInSource t1Id, t2.IdInSource t2Id " |
467 | 544 |
+ " FROM RelTaxon rel " |
468 | 545 |
+ " LEFT JOIN Taxon t1 ON t1.TaxonId = rel.TaxonFk1 " |
469 | 546 |
+ " LEFT JOIN Taxon t2 ON t2.TaxonId = rel.TaxonFk2 " |
470 |
+ " WHERE t1."+origEuroMed+" AND t2." + origEuroMed |
|
547 |
+ " WHERE t1."+origEuroMed+" AND t2." + origEuroMed + " AND RelTaxonQualifierFk = 101 "
|
|
471 | 548 |
+ " ORDER BY t1.IdInSource"); |
472 | 549 |
int i = 0; |
473 | 550 |
while (srcRS.next() && destRS.next()){ |
... | ... | |
479 | 556 |
} |
480 | 557 |
|
481 | 558 |
private boolean testSingleTaxonRelation(ResultSet srcRS, ResultSet destRS) throws SQLException { |
482 |
String id = String.valueOf(srcRS.getInt("id")); |
|
483 |
boolean success = equals("Taxon relation taxon1", "tu_id: " + srcRS.getInt("id"), destRS.getString("t1Id"), id);
|
|
484 |
success &= equals("Taxon relation taxon2", "tu_id: " + srcRS.getInt("tu_acctaxon"), destRS.getString("t2Id"), id);
|
|
485 |
success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_SYNONYM_OF, destRS.getInt("RelTaxonQualifierFk"), id);
|
|
486 |
success &= equals("Taxon relation qualifier cache", "is synonym of", destRS.getString("RelQualifierCache"), id);
|
|
559 |
String id = String.valueOf(srcRS.getInt("tid"));
|
|
560 |
boolean success = equals("Taxon relation taxon1", "NameId: " + srcRS.getInt("tid"), destRS.getString("t1Id"), id);
|
|
561 |
success &= equals("Taxon relation taxon2", "NameId: " + srcRS.getInt("pid"), destRS.getString("t2Id"), id);
|
|
562 |
success &= equals("Taxon relation qualifier fk", PesiTransformer.IS_TAXONOMICALLY_INCLUDED_IN, destRS.getInt("RelTaxonQualifierFk"), id);
|
|
563 |
success &= equals("Taxon relation qualifier cache", "is taxonomically included in", destRS.getString("RelQualifierCache"), id);
|
|
487 | 564 |
//TODO enable after next import |
488 |
// success &= isNull("notes", destRS);
|
|
565 |
success &= isNull("notes", destRS); |
|
489 | 566 |
//complete if no further relations need to added |
490 | 567 |
return success; |
491 | 568 |
} |
... | ... | |
539 | 616 |
+ " AND NOT (NoteCategoryFk = 4 AND no.LastAction IS NULL) AND NOT NoteCategoryFk IN (22,23,24) " |
540 | 617 |
+ " ORDER BY t.IdInSource, no.NoteCategoryCache, Note_1 "); |
541 | 618 |
int count = 0; |
542 |
ResultSet srcRsLastAction = source.getResultSet("" |
|
543 |
+ " SELECT no.id, s.sessiondate, a.action_name, s.ExpertName " |
|
544 |
+ " FROM notes no " |
|
545 |
+ " INNER JOIN tu ON tu.id = no.tu_id " |
|
546 |
+ " LEFT JOIN languages l ON l.LanID = no.lan_id" |
|
547 |
+ " LEFT JOIN notes_sessions MN ON no.id = MN.note_id " |
|
548 |
+ " LEFT JOIN actions a ON a.id = MN.action_id " |
|
549 |
+ " LEFT JOIN sessions s ON s.id = MN.session_id " |
|
550 |
+ " ORDER BY CAST(tu.id as char(20)), no.type, no.noteSortable, s.sessiondate DESC, a.id DESC "); |
|
551 |
|
|
552 | 619 |
while (srcRs.next() && destRs.next()){ |
553 | 620 |
success &= testSingleNote(srcRs, destRs); |
554 |
success &= testLastAction(srcRsLastAction, destRs, String.valueOf(srcRs.getInt("id")), "Note"); |
|
555 | 621 |
count++; |
556 | 622 |
} |
557 | 623 |
success &= equals("Notes count differs", n, count, "-1"); |
... | ... | |
686 | 752 |
+ " FROM CommonName cn INNER JOIN Taxon t ON t.TaxonId = cn.TaxonFk LEFT JOIN Language l ON l.LanguageId = cn.LanguageFk " |
687 | 753 |
+ " WHERE t." + origEuroMed |
688 | 754 |
+ " ORDER BY t.IdInSource, ISNULL("+preferredISO639+", "+alternativeISO639+"), cn.CommonName, cn.LastActionDate "); //sorting also lastActionDate results in a minimum of exact duplicate problems |
689 |
ResultSet srcRsLastAction = source.getResultSet("" |
|
690 |
+ " SELECT v.id, s.sessiondate, a.action_name, s.ExpertName " |
|
691 |
+ " FROM vernaculars v " |
|
692 |
+ " INNER JOIN tu ON tu.id = v.tu_id " |
|
693 |
+ " LEFT JOIN languages l ON l.LanID = v.lan_id" |
|
694 |
+ " LEFT JOIN vernaculars_sessions MN ON v.id = MN.vernacular_id " |
|
695 |
+ " LEFT JOIN actions a ON a.id = MN.action_id " |
|
696 |
+ " LEFT JOIN sessions s ON s.id = MN.session_id " |
|
697 |
+ " ORDER BY CAST(tu.id as char(20)), ISNULL([639_3],[639_2]), v.vername, v.id, s.sessiondate DESC, a.id DESC "); |
|
698 | 755 |
int count = 0; |
699 | 756 |
while (srcRs.next() && destRs.next()){ |
700 | 757 |
success &= testSingleCommonName(srcRs, destRs); |
701 |
success &= testLastAction(srcRsLastAction, destRs, String.valueOf(srcRs.getInt("id")), "CommonName"); |
|
702 | 758 |
count++; |
703 | 759 |
} |
704 | 760 |
success &= equals("Common name count differs", n, count, "-1"); |
... | ... | |
748 | 804 |
return result; |
749 | 805 |
} |
750 | 806 |
|
751 |
private boolean testSingleReferences() throws SQLException { |
|
807 |
private boolean testSingleReferences(int count) throws SQLException {
|
|
752 | 808 |
boolean success = true; |
753 |
ResultSet srcRS = source.getResultSet("SELECT r.* FROM Reference r ORDER BY r.id "); |
|
809 |
ResultSet srcRS = source.getResultSet("SELECT r.*, a.titleCache author " |
|
810 |
+ " FROM Reference r LEFT OUTER JOIN AgentBase a ON r.authorship_id = a.id " |
|
811 |
+ " ORDER BY r.id "); |
|
754 | 812 |
ResultSet destRS = destination.getResultSet("SELECT s.* FROM Source s " |
755 | 813 |
+ " WHERE s." + origEuroMed |
756 | 814 |
+ " ORDER BY s.RefIdInSource "); // +1 for the source reference "erms" but this has no OriginalDB |
815 |
int i = 0; |
|
757 | 816 |
while (srcRS.next() && destRS.next()){ |
758 | 817 |
success &= testSingleReference(srcRS, destRS); |
818 |
i++; |
|
759 | 819 |
} |
820 |
success &= equals("References count differs", count, i, "-1"); |
|
760 | 821 |
return success; |
761 | 822 |
} |
762 | 823 |
|
... | ... | |
769 | 830 |
success &= equals("Reference name ", srcRS.getString("titleCache"), destRS.getString("Name"), id); |
770 | 831 |
success &= equals("Reference abstract ", srcRS.getString("referenceAbstract"), destRS.getString("Abstract"), id); |
771 | 832 |
success &= equals("Reference title ", srcRS.getString("title"), destRS.getString("Title"), id); |
772 |
// success &= equals("Reference author string ", srcRS.getString("source_author"), destRS.getString("AuthorString"), id); |
|
773 |
// success &= equals("Reference year ", normalizeYear(srcRS.getString("source_year")), destRS.getString("RefYear"), id); |
|
774 |
success &= equals("Reference NomRefCache ", srcRS.getString("abbrevTitleCache"), destRS.getString("NomRefCache"), id); |
|
775 |
//TODO DOI |
|
776 |
// success &= equals("Reference link ", srcRS.getString("source_link"), destRS.getString("Link"), id); |
|
833 |
success &= equals("Reference author string ", srcRS.getString("author"), destRS.getString("AuthorString"), id); |
|
834 |
//TODO |
|
835 |
success &= equals("Reference year ", normalizeYear(srcRS), destRS.getString("RefYear"), id); |
|
836 |
//FIXME |
|
837 |
// success &= equals("Reference NomRefCache ", srcRS.getString("abbrevTitleCache"), destRS.getString("NomRefCache"), id); |
|
838 |
success &= equals("Reference DOI ", srcRS.getString("doi"), destRS.getString("Doi"), id); |
|
839 |
success &= equals("Reference link ", srcRS.getString("uri"), destRS.getString("Link"), id); |
|
840 |
//TODO Notes |
|
777 | 841 |
// success &= equals("Reference note ", srcRS.getString("source_note"), destRS.getString("Notes"), id); |
778 |
//TODO see above |
|
779 | 842 |
//complete |
780 | 843 |
return success; |
781 | 844 |
} |
... | ... | |
783 | 846 |
private Integer convertSourceTypeFk(String sourceType) { |
784 | 847 |
if (sourceType == null){ |
785 | 848 |
return null; |
786 |
}else if ("d".equals(sourceType)){ |
|
787 |
return 4; |
|
788 |
}else if ("e".equals(sourceType)){ |
|
789 |
return 5; |
|
790 |
}else if ("p".equals(sourceType)){ |
|
791 |
return 11; |
|
849 |
}else if ("DB".equals(sourceType)){ |
|
850 |
return PesiTransformer.REF_DATABASE; |
|
851 |
}else if ("JOU".equals(sourceType)){ |
|
852 |
return PesiTransformer.REF_JOURNAL; |
|
853 |
}else if ("BK".equals(sourceType)){ |
|
854 |
return PesiTransformer.REF_BOOK; |
|
855 |
}else if ("GEN".equals(sourceType)){ |
|
856 |
return PesiTransformer.REF_UNRESOLVED; |
|
857 |
}else if ("SER".equals(sourceType)){ |
|
858 |
// TODO correct? |
|
859 |
return PesiTransformer.REF_UNRESOLVED; |
|
792 | 860 |
}else if ("i".equals(sourceType)){ |
793 | 861 |
return 12; |
794 | 862 |
} |
... | ... | |
797 | 865 |
private String convertSourceTypeCache(String sourceType) { |
798 | 866 |
if (sourceType == null){ |
799 | 867 |
return null; |
800 |
}else if ("d".equals(sourceType)){
|
|
868 |
}else if ("DB".equals(sourceType)){
|
|
801 | 869 |
return "database"; |
802 |
}else if ("e".equals(sourceType)){ |
|
803 |
return "informal reference"; |
|
804 |
}else if ("p".equals(sourceType)){ |
|
805 |
return "publication"; |
|
870 |
}else if ("JOU".equals(sourceType)){ |
|
871 |
return "journal"; |
|
872 |
}else if ("BK".equals(sourceType)){ |
|
873 |
return "book"; |
|
874 |
}else if ("SER".equals(sourceType)){ |
|
875 |
return "published"; |
|
876 |
}else if ("BK".equals(sourceType)){ |
|
877 |
return "book"; |
|
878 |
}else if ("GEN".equals(sourceType)){ |
|
879 |
return "unresolved"; |
|
806 | 880 |
}else if ("i".equals(sourceType)){ |
807 | 881 |
//TODO |
808 | 882 |
return "i"; |
... | ... | |
811 | 885 |
} |
812 | 886 |
|
813 | 887 |
private boolean testReferenceCount() { |
814 |
int countSrc = source.getUniqueInteger("SELECT count(*) FROM reference ");
|
|
888 |
int countSrc = source.getUniqueInteger(countReferencesStr);
|
|
815 | 889 |
int countDest = destination.getUniqueInteger("SELECT count(*) FROM Source s WHERE s."+ origEuroMed); // +1 for the source reference "erms" but this has no OriginalDB |
816 | 890 |
boolean success = equals("Reference count ", countSrc, countDest, "-1"); |
817 | 891 |
return success; |
818 | 892 |
} |
819 | 893 |
|
820 |
private String normalizeYear(String yearStr) { |
|
821 |
if (StringUtils.isBlank(yearStr)){ |
|
822 |
return yearStr; |
|
894 |
private String normalizeYear(ResultSet rs) throws SQLException { |
|
895 |
String freetext = rs.getString("datePublished_freetext"); |
|
896 |
if(StringUtils.isNotBlank(freetext)){ |
|
897 |
return freetext; |
|
823 | 898 |
} |
824 |
yearStr = yearStr.trim(); |
|
825 |
if (yearStr.matches("\\d{4}-\\d{2}")){ |
|
826 |
yearStr = yearStr.substring(0, 5)+yearStr.substring(0, 2)+yearStr.substring(5); |
|
899 |
String start = rs.getString("datePublished_start"); |
|
900 |
String end = rs.getString("datePublished_end"); |
|
901 |
if (start != null){ |
|
902 |
start = start.substring(0,4); |
|
827 | 903 |
} |
828 |
return yearStr; |
|
904 |
if (end != null){ |
|
905 |
end = end.substring(0,4); |
|
906 |
} |
|
907 |
String result = start == null? null: start + (end==null? "": "-"+ end); |
|
908 |
return result; |
|
829 | 909 |
} |
830 | 910 |
|
831 | 911 |
private boolean isNull(String attrName, ResultSet destRS) throws SQLException { |
Also available in: Unified diff
ref #8577 cleanup E+M PESI validation