Revision 0575453b
Added by Andreas Müller almost 6 years ago
| app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/validation/BerlinModelTaxonRelationImportValidator.java | ||
|---|---|---|
| 1 | 1 |
/** |
| 2 | 2 |
* Copyright (C) 2007 EDIT |
| 3 |
* European Distributed Institute of Taxonomy
|
|
| 3 |
* European Distributed Institute of Taxonomy |
|
| 4 | 4 |
* http://www.e-taxonomy.eu |
| 5 |
*
|
|
| 5 |
* |
|
| 6 | 6 |
* The contents of this file are subject to the Mozilla Public License Version 1.1 |
| 7 | 7 |
* See LICENSE.TXT at the top of this package for the full license terms. |
| 8 | 8 |
*/ |
| ... | ... | |
| 38 | 38 |
result &= checkTaxaWithNoRelations(state); |
| 39 | 39 |
return result; |
| 40 | 40 |
} |
| 41 |
|
|
| 42 |
|
|
| 41 |
|
|
| 42 |
|
|
| 43 | 43 |
private boolean checkInActivatedStatus(BerlinModelImportState state){
|
| 44 | 44 |
try {
|
| 45 | 45 |
boolean result = true; |
| 46 | 46 |
BerlinModelImportConfigurator config = state.getConfig(); |
| 47 | 47 |
Source source = state.getConfig().getSource(); |
| 48 |
String strSQL =
|
|
| 48 |
String strSQL = |
|
| 49 | 49 |
" SELECT RelPTaxon.RelPTaxonId, RelPTaxon.RelQualifierFk, FromName.FullNameCache AS FromName, RelPTaxon.PTNameFk1 AS FromNameID, " + |
| 50 |
" Status.Status AS FromStatus, ToName.FullNameCache AS ToName, RelPTaxon.PTNameFk2 AS ToNameId, ToStatus.Status AS ToStatus, FromTaxon.DoubtfulFlag AS doubtfulFrom, ToTaxon.DoubtfulFlag AS doubtfulTo" +
|
|
| 51 |
" FROM PTaxon AS FromTaxon " +
|
|
| 52 |
" INNER JOIN RelPTaxon ON FromTaxon.PTNameFk = RelPTaxon.PTNameFk1 AND FromTaxon.PTRefFk = RelPTaxon.PTRefFk1 " +
|
|
| 53 |
" INNER JOIN PTaxon AS ToTaxon ON RelPTaxon.PTNameFk2 = ToTaxon.PTNameFk AND RelPTaxon.PTRefFk2 = ToTaxon.PTRefFk " +
|
|
| 54 |
" INNER JOIN Name AS ToName ON ToTaxon.PTNameFk = ToName.NameId " +
|
|
| 55 |
" INNER JOIN Name AS FromName ON FromTaxon.PTNameFk = FromName.NameId " +
|
|
| 56 |
" INNER JOIN Status ON FromTaxon.StatusFk = Status.StatusId AND FromTaxon.StatusFk = Status.StatusId " +
|
|
| 50 |
" Status.Status AS FromStatus, ToName.FullNameCache AS ToName, RelPTaxon.PTNameFk2 AS ToNameId, ToStatus.Status AS ToStatus, FromTaxon.DoubtfulFlag AS doubtfulFrom, ToTaxon.DoubtfulFlag AS doubtfulTo" + |
|
| 51 |
" FROM PTaxon AS FromTaxon " + |
|
| 52 |
" INNER JOIN RelPTaxon ON FromTaxon.PTNameFk = RelPTaxon.PTNameFk1 AND FromTaxon.PTRefFk = RelPTaxon.PTRefFk1 " + |
|
| 53 |
" INNER JOIN PTaxon AS ToTaxon ON RelPTaxon.PTNameFk2 = ToTaxon.PTNameFk AND RelPTaxon.PTRefFk2 = ToTaxon.PTRefFk " + |
|
| 54 |
" INNER JOIN Name AS ToName ON ToTaxon.PTNameFk = ToName.NameId " + |
|
| 55 |
" INNER JOIN Name AS FromName ON FromTaxon.PTNameFk = FromName.NameId " + |
|
| 56 |
" INNER JOIN Status ON FromTaxon.StatusFk = Status.StatusId AND FromTaxon.StatusFk = Status.StatusId " + |
|
| 57 | 57 |
" INNER JOIN Status AS ToStatus ON ToTaxon.StatusFk = ToStatus.StatusId AND ToTaxon.StatusFk = ToStatus.StatusId " + |
| 58 | 58 |
" WHERE (RelPTaxon.RelQualifierFk = - 99) "; |
| 59 |
|
|
| 59 |
|
|
| 60 | 60 |
if (StringUtils.isNotBlank(config.getRelTaxaIdQuery())){
|
| 61 | 61 |
strSQL += String.format(" AND (RelPTaxon.RelPTaxonId IN " +
|
| 62 |
" ( %s ) )" , config.getRelTaxaIdQuery()) ;
|
|
| 62 |
" ( %s ) )" , config.getRelTaxaIdQuery()) ; |
|
| 63 | 63 |
} |
| 64 |
|
|
| 64 |
|
|
| 65 | 65 |
// System.out.println(strSQL); |
| 66 | 66 |
ResultSet rs = source.getResultSet(strSQL); |
| 67 | 67 |
boolean firstRow = true; |
| ... | ... | |
| 73 | 73 |
System.out.println("There are TaxonRelationships with status 'inactivated'(-99)!");
|
| 74 | 74 |
System.out.println("========================================================");
|
| 75 | 75 |
} |
| 76 |
|
|
| 76 |
|
|
| 77 | 77 |
int relPTaxonId = rs.getInt("RelPTaxonId");
|
| 78 | 78 |
String fromName = rs.getString("FromName");
|
| 79 | 79 |
int fromNameID = rs.getInt("FromNameID");
|
| 80 | 80 |
String fromStatus = rs.getString("FromStatus");
|
| 81 |
|
|
| 81 |
|
|
| 82 | 82 |
String toName = rs.getString("ToName");
|
| 83 | 83 |
int toNameId = rs.getInt("ToNameId");
|
| 84 | 84 |
String toStatus = rs.getString("ToStatus");
|
| 85 | 85 |
String doubtfulFrom = String.valueOf(rs.getObject("doubtfulFrom"));
|
| 86 | 86 |
String doubtfulTo = String.valueOf(rs.getObject("doubtfulTo"));
|
| 87 |
|
|
| 88 |
|
|
| 89 |
System.out.println("RelPTaxonId:" + relPTaxonId +
|
|
| 90 |
"\n FromName: " + fromName + "\n FromNameID: " + fromNameID + "\n FromStatus: " + fromStatus + "\n FromDoubtful: " + doubtfulFrom +
|
|
| 87 |
|
|
| 88 |
|
|
| 89 |
System.out.println("RelPTaxonId:" + relPTaxonId +
|
|
| 90 |
"\n FromName: " + fromName + "\n FromNameID: " + fromNameID + "\n FromStatus: " + fromStatus + "\n FromDoubtful: " + doubtfulFrom + |
|
| 91 | 91 |
"\n ToName: " + toName + "\n ToNameId: " + toNameId + "\n ToStatus: " + toStatus + "\n ToDoubtful: " + doubtfulTo ); |
| 92 | 92 |
result = firstRow = false; |
| 93 | 93 |
} |
| 94 | 94 |
if (i > 0){
|
| 95 | 95 |
System.out.println(" ");
|
| 96 | 96 |
} |
| 97 |
|
|
| 97 |
|
|
| 98 | 98 |
return result; |
| 99 | 99 |
} catch (SQLException e) {
|
| 100 | 100 |
e.printStackTrace(); |
| 101 | 101 |
return false; |
| 102 | 102 |
} |
| 103 | 103 |
} |
| 104 |
|
|
| 104 |
|
|
| 105 | 105 |
/** |
| 106 | 106 |
* @param state |
| 107 | 107 |
* @return |
| ... | ... | |
| 111 | 111 |
try {
|
| 112 | 112 |
BerlinModelImportConfigurator config = state.getConfig(); |
| 113 | 113 |
Source source = config.getSource(); |
| 114 |
String strQuery =
|
|
| 115 |
"SELECT count(*) AS n FROM RelPTaxon " +
|
|
| 114 |
String strQuery = |
|
| 115 |
"SELECT count(*) AS n FROM RelPTaxon " + |
|
| 116 | 116 |
" WHERE (Notes IS NOT NULL) AND (RTRIM(LTRIM(Notes)) <> '') "; |
| 117 |
|
|
| 117 |
|
|
| 118 | 118 |
if (StringUtils.isNotBlank(config.getRelTaxaIdQuery())){
|
| 119 | 119 |
strQuery += String.format(" AND (RelPTaxon.RelPTaxonId IN " +
|
| 120 |
" ( %s ) ) " , config.getRelTaxaIdQuery()) ;
|
|
| 120 |
" ( %s ) ) " , config.getRelTaxaIdQuery()) ; |
|
| 121 | 121 |
} |
| 122 |
|
|
| 122 |
|
|
| 123 | 123 |
ResultSet rs = source.getResultSet(strQuery); |
| 124 | 124 |
rs.next(); |
| 125 | 125 |
int n; |
| ... | ... | |
| 129 | 129 |
System.out.println("There are " + n + " RelPTaxa with a note. Notes for RelPTaxa are not imported!");
|
| 130 | 130 |
System.out.println("========================================================");
|
| 131 | 131 |
success = false; |
| 132 |
|
|
| 132 |
|
|
| 133 | 133 |
} |
| 134 | 134 |
} catch (SQLException e) {
|
| 135 | 135 |
e.printStackTrace(); |
| 136 | 136 |
} |
| 137 | 137 |
return success; |
| 138 | 138 |
} |
| 139 |
|
|
| 139 |
|
|
| 140 | 140 |
/** |
| 141 | 141 |
* @param state |
| 142 | 142 |
* @return |
| ... | ... | |
| 145 | 145 |
boolean success = true; |
| 146 | 146 |
try {
|
| 147 | 147 |
BerlinModelImportConfigurator config = state.getConfig(); |
| 148 |
|
|
| 148 |
|
|
| 149 | 149 |
Source source = config.getSource(); |
| 150 |
String strQuery =
|
|
| 150 |
String strQuery = |
|
| 151 | 151 |
"SELECT RelPTaxon.RelPTaxonId, RelPTQualifier, PTaxon.RIdentifier, Name.FullNameCache fromName, PTaxon.PTRefFk, Name.NameId as fromNameId, AcceptedName.FullNameCache acceptedName" + |
| 152 | 152 |
" FROM RelPTaxon INNER JOIN PTaxon ON RelPTaxon.PTNameFk1 = PTaxon.PTNameFk AND RelPTaxon.PTRefFk1 = PTaxon.PTRefFk " + |
| 153 | 153 |
" INNER JOIN RelPTQualifier ON RelPTaxon.RelQualifierFk = RelPTQualifier.RelPTQualifierId " + |
| 154 | 154 |
" LEFT OUTER JOIN Name ON PTaxon.PTNameFk = Name.NameId " + |
| 155 | 155 |
" LEFT OUTER JOIN Name AS AcceptedName ON RelPTaxon.PTNameFk2 = AcceptedName.NameId " + |
| 156 | 156 |
" WHERE (PTaxon.StatusFk = 1) AND (RelPTaxon.RelQualifierFk IN (2, 4, 5, 6, 7)) "; |
| 157 |
|
|
| 157 |
|
|
| 158 | 158 |
if (StringUtils.isNotBlank(config.getRelTaxaIdQuery())){
|
| 159 | 159 |
strQuery += String.format(" AND (RelPTaxon.RelPTaxonId IN " +
|
| 160 |
" ( %s ) )" , config.getRelTaxaIdQuery()) ;
|
|
| 160 |
" ( %s ) )" , config.getRelTaxaIdQuery()) ; |
|
| 161 | 161 |
} |
| 162 |
|
|
| 162 |
|
|
| 163 | 163 |
ResultSet rs = source.getResultSet(strQuery); |
| 164 | 164 |
boolean firstRow = true; |
| 165 | 165 |
while (rs.next()){
|
| ... | ... | |
| 176 | 176 |
int fromRefFk = rs.getInt("PTRefFk");
|
| 177 | 177 |
int fromNameId = rs.getInt("fromNameId");
|
| 178 | 178 |
String toName = rs.getString("acceptedName");
|
| 179 |
|
|
| 180 |
System.out.println("RelPTaxonId:" + relPTaxonId +
|
|
| 181 |
"\n TaxonRIdentifier: " + fromIdentifier + "\n name: " + fromName + "\n nameId: " + fromNameId + "\n RefFk: " + fromRefFk + "\n RelType: " + relType
|
|
| 179 |
|
|
| 180 |
System.out.println("RelPTaxonId:" + relPTaxonId +
|
|
| 181 |
"\n TaxonRIdentifier: " + fromIdentifier + "\n name: " + fromName + "\n nameId: " + fromNameId + "\n RefFk: " + fromRefFk + "\n RelType: " + relType |
|
| 182 | 182 |
+ "\n acceptedName: " + toName //+ "\n ToNameId: " + toNameId + "\n ToStatus: " + toStatus + "\n ToDoubtful: " + doubtfulTo ) |
| 183 | 183 |
); |
| 184 | 184 |
success = (firstRow = false); |
| ... | ... | |
| 188 | 188 |
} |
| 189 | 189 |
return success; |
| 190 | 190 |
} |
| 191 |
|
|
| 192 |
|
|
| 191 |
|
|
| 192 |
|
|
| 193 | 193 |
/** |
| 194 | 194 |
* @param state |
| 195 | 195 |
* @return |
| ... | ... | |
| 198 | 198 |
boolean success = true; |
| 199 | 199 |
try {
|
| 200 | 200 |
BerlinModelImportConfigurator config = state.getConfig(); |
| 201 |
|
|
| 201 |
|
|
| 202 | 202 |
Source source = config.getSource(); |
| 203 |
String strQuery =
|
|
| 203 |
String strQuery = |
|
| 204 | 204 |
"SELECT RelPTaxon.RelPTaxonId, RelPTQualifier, PTaxon.RIdentifier,PTaxon.StatusFk as fromStatus, Name.FullNameCache fromName, PTaxon.PTRefFk, Name.NameId as fromNameId, AcceptedName.FullNameCache acceptedName" + |
| 205 | 205 |
" FROM RelPTaxon INNER JOIN PTaxon ON RelPTaxon.PTNameFk1 = PTaxon.PTNameFk AND RelPTaxon.PTRefFk1 = PTaxon.PTRefFk " + |
| 206 | 206 |
" INNER JOIN RelPTQualifier ON RelPTaxon.RelQualifierFk = RelPTQualifier.RelPTQualifierId " + |
| 207 | 207 |
" LEFT OUTER JOIN Name ON PTaxon.PTNameFk = Name.NameId " + |
| 208 | 208 |
" LEFT OUTER JOIN Name AS AcceptedName ON RelPTaxon.PTNameFk2 = AcceptedName.NameId " + |
| 209 | 209 |
" WHERE (PTaxon.StatusFk IN (2,3,4)) AND (RelPTaxon.RelQualifierFk NOT IN (2, 4, 5, 6, 7, 101, 102, 103, 104, -99)) "; |
| 210 |
|
|
| 210 |
|
|
| 211 | 211 |
if (StringUtils.isNotBlank(config.getRelTaxaIdQuery())){
|
| 212 | 212 |
strQuery += String.format(" AND (RelPTaxon.RelPTaxonId IN " +
|
| 213 |
" ( %s ) )" , config.getRelTaxaIdQuery()) ;
|
|
| 213 |
" ( %s ) )" , config.getRelTaxaIdQuery()) ; |
|
| 214 | 214 |
} |
| 215 |
|
|
| 215 |
|
|
| 216 | 216 |
ResultSet rs = source.getResultSet(strQuery); |
| 217 | 217 |
boolean firstRow = true; |
| 218 | 218 |
while (rs.next()){
|
| ... | ... | |
| 231 | 231 |
int fromRefFk = rs.getInt("PTRefFk");
|
| 232 | 232 |
int fromNameId = rs.getInt("fromNameId");
|
| 233 | 233 |
String toName = rs.getString("acceptedName");
|
| 234 |
|
|
| 235 |
System.out.println("RelPTaxonId:" + relPTaxonId +
|
|
| 236 |
"\n TaxonRIdentifier: " + fromIdentifier + "\n name: " + fromName + "\n nameId: " + fromNameId + "\n RefFk: " + fromRefFk + "\n RelType: " + relType
|
|
| 234 |
|
|
| 235 |
System.out.println("RelPTaxonId:" + relPTaxonId +
|
|
| 236 |
"\n TaxonRIdentifier: " + fromIdentifier + "\n name: " + fromName + "\n nameId: " + fromNameId + "\n RefFk: " + fromRefFk + "\n RelType: " + relType |
|
| 237 | 237 |
+ "\n toName: " + toName //+ "\n ToNameId: " + toNameId + "\n ToStatus: " + toStatus + "\n ToDoubtful: " + doubtfulTo ) |
| 238 | 238 |
); |
| 239 | 239 |
success = (firstRow = false); |
| ... | ... | |
| 243 | 243 |
} |
| 244 | 244 |
return success; |
| 245 | 245 |
} |
| 246 |
|
|
| 246 |
|
|
| 247 | 247 |
/** |
| 248 | 248 |
* @param state |
| 249 | 249 |
* @return |
| ... | ... | |
| 252 | 252 |
boolean success = true; |
| 253 | 253 |
try {
|
| 254 | 254 |
BerlinModelImportConfigurator config = state.getConfig(); |
| 255 |
|
|
| 255 |
|
|
| 256 | 256 |
Source source = config.getSource(); |
| 257 |
String strQuery = |
|
| 258 |
" SELECT pt.PTRefFk AS secRefFk, dbo.Reference.RefCache AS secRef, dbo.Name.FullNameCache, Name.NameId, Status.Status " + |
|
| 257 |
String strSelect = " SELECT pt.PTRefFk AS secRefFk, r.RefCache AS secRef, n.FullNameCache, n.NameId, st.Status "; |
|
| 258 |
String strCount = " SELECT count(*) as n "; |
|
| 259 |
String strQueryBase = |
|
| 259 | 260 |
" FROM PTaxon AS pt LEFT OUTER JOIN " + |
| 260 |
" Status ON pt.StatusFk = Status.StatusId LEFT OUTER JOIN " + |
|
| 261 |
" Reference ON pt.PTRefFk = dbo.Reference.RefId LEFT OUTER JOIN " + |
|
| 262 |
" Name ON pt.PTNameFk = dbo.Name.NameId LEFT OUTER JOIN " + |
|
| 263 |
" RelPTaxon ON pt.PTNameFk = dbo.RelPTaxon.PTNameFk2 AND pt.PTRefFk = dbo.RelPTaxon.PTRefFk2 LEFT OUTER JOIN " + |
|
| 264 |
" RelPTaxon AS RelPTaxon_1 ON pt.PTNameFk = RelPTaxon_1.PTNameFk1 AND pt.PTRefFk = RelPTaxon_1.PTRefFk1 " + |
|
| 265 |
" WHERE (RelPTaxon_1.RelQualifierFk IS NULL) AND (dbo.RelPTaxon.RelQualifierFk IS NULL) AND pt.statusFK <> 6 " + |
|
| 266 |
" ORDER BY Reference.RefCache, pt.PTRefFk, Name.FullNameCache, statusFK"; |
|
| 261 |
" Status st ON pt.StatusFk = st.StatusId LEFT OUTER JOIN " + |
|
| 262 |
" Reference r ON pt.PTRefFk = r.RefId LEFT OUTER JOIN " + |
|
| 263 |
" Name n ON pt.PTNameFk = n.NameId LEFT OUTER JOIN " + |
|
| 264 |
" RelPTaxon rel1 ON pt.PTNameFk = rel1.PTNameFk2 AND pt.PTRefFk = rel1.PTRefFk2 LEFT OUTER JOIN " + |
|
| 265 |
" RelPTaxon AS rel2 ON pt.PTNameFk = rel2.PTNameFk1 AND pt.PTRefFk = rel2.PTRefFk1 " + |
|
| 266 |
" WHERE (rel2.RelQualifierFk IS NULL) AND (rel1.RelQualifierFk IS NULL) AND pt.statusFK <> 6 "; |
|
| 267 |
String strOrderBy = " ORDER BY r.RefCache, pt.PTRefFk, n.FullNameCache, pt.statusFK "; |
|
| 268 |
if (state.getConfig().isEuroMed()){
|
|
| 269 |
strQueryBase += " AND pt.RIdentifier IN (SELECT RIdentifier FROM v_cdm_exp_taxaAll) AND (pt.IsExcludedMarker = 0 OR pt.IsExcludedMarker IS NULL) " ; |
|
| 270 |
} |
|
| 267 | 271 |
|
| 268 | 272 |
//project filter |
| 269 | 273 |
// if (StringUtils.isNotBlank(config.getRelTaxaIdQuery())){
|
| 270 | 274 |
// strQuery += String.format(" AND (RelPTaxon.RelPTaxonId IN " +
|
| 271 |
// " ( %s ) )" , config.getRelTaxaIdQuery()) ;
|
|
| 275 |
// " ( %s ) )" , config.getRelTaxaIdQuery()) ; |
|
| 272 | 276 |
// } |
| 273 |
|
|
| 274 |
ResultSet rs = source.getResultSet(strQuery); |
|
| 275 |
boolean firstRow = true; |
|
| 277 |
|
|
| 278 |
ResultSet rs = source.getResultSet(strCount + strQueryBase); |
|
| 279 |
rs.next(); |
|
| 280 |
int n = rs.getInt("n");
|
|
| 281 |
if (n > 0){
|
|
| 282 |
System.out.println("=======================================================================");
|
|
| 283 |
System.out.println("There are "+n+" taxa without any taxon relationship and not being orphaned!");
|
|
| 284 |
System.out.println("=======================================================================");
|
|
| 285 |
} |
|
| 286 |
|
|
| 287 |
rs = source.getResultSet(strSelect + strQueryBase + strOrderBy); |
|
| 276 | 288 |
while (rs.next()){
|
| 277 |
if (firstRow){
|
|
| 278 |
System.out.println("=====================================================================");
|
|
| 279 |
System.out.println("There are taxa without any taxon relationship and not being orphaned!");
|
|
| 280 |
System.out.println("=====================================================================");
|
|
| 281 |
} |
|
| 282 | 289 |
|
| 283 |
int secRefFk = rs.getInt("secRefFk");
|
|
| 290 |
int secRefFk = rs.getInt("secRefFk");
|
|
| 284 | 291 |
String secRef = rs.getString("secRef");
|
| 285 | 292 |
String nameCache = rs.getString("FullNameCache");
|
| 286 | 293 |
int nameId = rs.getInt("NameId");
|
| 287 | 294 |
String status = rs.getString("Status");
|
| 288 |
|
|
| 289 |
System.out.println("SecRef:" + secRefFk +
|
|
| 290 |
"\n secRef: " + secRef + "\n name: " + nameCache + "\n nameId: " + nameId
|
|
| 291 |
+ "\n status: " + status
|
|
| 295 |
|
|
| 296 |
System.out.println("SecRef:" + secRefFk +
|
|
| 297 |
"\n secRef: " + secRef + "\n name: " + nameCache + "\n nameId: " + nameId |
|
| 298 |
+ "\n status: " + status |
|
| 292 | 299 |
); |
| 293 |
success = (firstRow = false); |
|
| 294 | 300 |
} |
| 301 |
success = (n == 0); |
|
| 295 | 302 |
} catch (SQLException e) {
|
| 296 | 303 |
e.printStackTrace(); |
| 304 |
success = false; |
|
| 297 | 305 |
} |
| 298 | 306 |
return success; |
| 299 | 307 |
} |
| 300 |
|
|
| 308 |
|
|
| 301 | 309 |
|
| 302 | 310 |
|
| 303 | 311 |
} |
Also available in: Unified diff
improve BM orphaned taxon validation