Revision acc45005
Added by Andreas Müller almost 6 years ago
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/validation/BerlinModelCommonNamesImportValidator.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 |
*/ |
... | ... | |
30 | 30 |
@Override |
31 | 31 |
public boolean validate(BerlinModelImportState state) { |
32 | 32 |
boolean result = true; |
33 |
result &= checkUnreferredNameUsedInSource(state.getConfig()); |
|
33 |
result &= checkNameUsedInSourceMinusOne(state.getConfig()); |
|
34 |
result &= checkUnreferredNameUsedInSource(state.getConfig()); |
|
34 | 35 |
result &= checkUnreferredLanguageRefFk(state.getConfig()); |
36 |
|
|
35 | 37 |
result &= checkTaxonIsAccepted(state.getConfig()); |
36 |
|
|
38 |
|
|
37 | 39 |
return result; |
38 | 40 |
} |
39 |
|
|
41 |
|
|
42 |
private boolean checkNameUsedInSourceMinusOne(BerlinModelImportConfigurator config){ |
|
43 |
try { |
|
44 |
boolean result = true; |
|
45 |
Source source = config.getSource(); |
|
46 |
String strQuery = "SELECT Count(*) as n " + |
|
47 |
" FROM emCommonName " + |
|
48 |
" WHERE (emCommonName.NameInSourceFk = - 1) "; |
|
49 |
|
|
50 |
if (StringUtils.isNotBlank(config.getCommonNameFilter())){ |
|
51 |
strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ; |
|
52 |
} |
|
53 |
|
|
54 |
ResultSet rs = source.getResultSet(strQuery); |
|
55 |
rs.next(); |
|
56 |
int count = rs.getInt("n"); |
|
57 |
if (count > 0){ |
|
58 |
System.out.println("========================================================"); |
|
59 |
System.out.println("There are " + count + " common names that have a name usedInSourceFk = -1."); |
|
60 |
System.out.println("========================================================"); |
|
61 |
} |
|
62 |
String sql = |
|
63 |
" SELECT DISTINCT emCommonName.CommonNameId, emCommonName.NameInSourceFk, emCommonName.CommonName, PTaxon.PTNameFk, PTaxon.PTRefFk," + |
|
64 |
" Name.FullNameCache, PTaxon.RIdentifier " + |
|
65 |
" FROM emCommonName INNER JOIN " + |
|
66 |
" PTaxon ON emCommonName.PTNameFk = PTaxon.PTNameFk AND emCommonName.PTRefFk = PTaxon.PTRefFk INNER JOIN " + |
|
67 |
" Name ON PTaxon.PTNameFk = Name.NameId " + |
|
68 |
" WHERE (emCommonName.NameInSourceFk = - 1) "; |
|
69 |
if (StringUtils.isNotBlank(config.getCommonNameFilter())){ |
|
70 |
sql += String.format(" AND (%s) ", config.getCommonNameFilter()) ; |
|
71 |
} |
|
72 |
|
|
73 |
rs = source.getResultSet(sql); |
|
74 |
int i = 0; |
|
75 |
while (rs.next()){ |
|
76 |
i++; |
|
77 |
int commonNameId = rs.getInt("CommonNameId"); |
|
78 |
String fullNameCache = rs.getString("FullNameCache"); |
|
79 |
String commonName = rs.getString("CommonName"); |
|
80 |
int rIdentifier = rs.getInt("RIdentifier"); |
|
81 |
int nameFk = rs.getInt("PTNameFk"); |
|
82 |
int refFk = rs.getInt("PTRefFk"); |
|
83 |
int nameInSourceFk = rs.getInt("NameInSourceFk"); |
|
84 |
|
|
85 |
System.out.println("CommonName: " + commonName + "\n CommonNameId: " + commonNameId + "\n Taxon Name:" + fullNameCache + "\n TaxonNameFk: " + nameFk + |
|
86 |
"\n TaxonRefFk: " + refFk + "\n TaxonId" + rIdentifier + "\n NameInSourceFk: " + nameInSourceFk + "\n"); |
|
87 |
} |
|
88 |
if (i > 0){ |
|
89 |
System.out.println(" "); |
|
90 |
} |
|
91 |
|
|
92 |
|
|
93 |
return result; |
|
94 |
} catch (SQLException e) { |
|
95 |
e.printStackTrace(); |
|
96 |
return false; |
|
97 |
} |
|
98 |
|
|
99 |
} |
|
100 |
|
|
40 | 101 |
private boolean checkUnreferredNameUsedInSource(BerlinModelImportConfigurator config){ |
41 | 102 |
try { |
42 | 103 |
boolean result = true; |
43 | 104 |
Source source = config.getSource(); |
44 | 105 |
String strQuery = "SELECT Count(*) as n " + |
45 | 106 |
" FROM emCommonName " + |
46 |
" WHERE (emCommonName.NameInSourceFk NOT IN " +
|
|
47 |
"(SELECT NameId FROM Name AS Name_1)) AND " +
|
|
107 |
" WHERE (emCommonName.NameInSourceFk NOT IN " + |
|
108 |
"(SELECT NameId FROM Name AS Name_1)) AND " + |
|
48 | 109 |
"(emCommonName.NameInSourceFk <> - 1) "; |
49 |
|
|
110 |
|
|
50 | 111 |
if (StringUtils.isNotBlank(config.getCommonNameFilter())){ |
51 |
strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ;
|
|
112 |
strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ; |
|
52 | 113 |
} |
53 |
|
|
114 |
|
|
54 | 115 |
ResultSet rs = source.getResultSet(strQuery); |
55 | 116 |
rs.next(); |
56 | 117 |
int count = rs.getInt("n"); |
57 | 118 |
if (count > 0){ |
58 | 119 |
System.out.println("========================================================"); |
59 | 120 |
System.out.println("There are " + count + " common names that have a name used in source which can not be found in the database."); |
60 |
|
|
121 |
|
|
61 | 122 |
System.out.println("========================================================"); |
62 | 123 |
} |
63 |
String sql =
|
|
64 |
" SELECT DISTINCT emCommonName.CommonNameId, emCommonName.NameInSourceFk, emCommonName.CommonName, PTaxon.PTNameFk, PTaxon.PTRefFk," +
|
|
124 |
String sql = |
|
125 |
" SELECT DISTINCT emCommonName.CommonNameId, emCommonName.NameInSourceFk, emCommonName.CommonName, PTaxon.PTNameFk, PTaxon.PTRefFk," + |
|
65 | 126 |
" Name.FullNameCache, PTaxon.RIdentifier " + |
66 | 127 |
" FROM emCommonName INNER JOIN " + |
67 | 128 |
" PTaxon ON emCommonName.PTNameFk = PTaxon.PTNameFk AND emCommonName.PTRefFk = PTaxon.PTRefFk INNER JOIN " + |
68 | 129 |
" Name ON PTaxon.PTNameFk = Name.NameId " + |
69 |
" WHERE (emCommonName.NameInSourceFk NOT IN " +
|
|
70 |
"(SELECT NameId FROM Name AS Name_1)) AND " +
|
|
130 |
" WHERE (emCommonName.NameInSourceFk NOT IN " + |
|
131 |
"(SELECT NameId FROM Name AS Name_1)) AND " + |
|
71 | 132 |
"(emCommonName.NameInSourceFk <> - 1)"; |
72 |
|
|
133 |
|
|
73 | 134 |
rs = source.getResultSet(sql); |
74 | 135 |
int i = 0; |
75 | 136 |
while (rs.next()){ |
... | ... | |
81 | 142 |
int nameFk = rs.getInt("PTNameFk"); |
82 | 143 |
int refFk = rs.getInt("PTRefFk"); |
83 | 144 |
int nameInSourceFk = rs.getInt("NameInSourceFk"); |
84 |
|
|
85 |
System.out.println("CommonName: " + commonName + "\n CommonNameId: " + commonNameId + "\n Taxon Name:" + fullNameCache + "\n TaxonNameFk: " + nameFk +
|
|
145 |
|
|
146 |
System.out.println("CommonName: " + commonName + "\n CommonNameId: " + commonNameId + "\n Taxon Name:" + fullNameCache + "\n TaxonNameFk: " + nameFk + |
|
86 | 147 |
"\n TaxonRefFk: " + refFk + "\n TaxonId" + rIdentifier + "\n NameInSourceFk: " + nameInSourceFk + "\n"); |
87 | 148 |
} |
88 | 149 |
if (i > 0){ |
89 | 150 |
System.out.println(" "); |
90 | 151 |
} |
91 |
|
|
92 |
|
|
152 |
|
|
153 |
|
|
93 | 154 |
return result; |
94 | 155 |
} catch (SQLException e) { |
95 | 156 |
e.printStackTrace(); |
... | ... | |
97 | 158 |
} |
98 | 159 |
|
99 | 160 |
} |
100 |
|
|
161 |
|
|
101 | 162 |
private boolean checkUnreferredLanguageRefFk(BerlinModelImportConfigurator config){ |
102 | 163 |
try { |
103 | 164 |
boolean result = true; |
104 | 165 |
Source source = config.getSource(); |
105 | 166 |
String strQueryArticlesWithoutJournal = "SELECT Count(*) as n " + |
106 | 167 |
" FROM emCommonName " + |
107 |
" WHERE (emCommonName.LanguageRefFk NOT IN " +
|
|
108 |
"(SELECT ReferenceId FROM emLanguageReference)) AND " +
|
|
168 |
" WHERE (emCommonName.LanguageRefFk NOT IN " + |
|
169 |
"(SELECT ReferenceId FROM emLanguageReference)) AND " + |
|
109 | 170 |
"(emCommonName.LanguageRefFk is NOT NULL)"; |
110 | 171 |
ResultSet rs = source.getResultSet(strQueryArticlesWithoutJournal); |
111 | 172 |
rs.next(); |
... | ... | |
119 | 180 |
if (count > 0){ |
120 | 181 |
System.out.println(" "); |
121 | 182 |
} |
122 |
|
|
123 |
|
|
183 |
|
|
184 |
|
|
124 | 185 |
return result; |
125 | 186 |
} catch (SQLException e) { |
126 | 187 |
e.printStackTrace(); |
... | ... | |
128 | 189 |
} |
129 | 190 |
|
130 | 191 |
} |
131 |
|
|
192 |
|
|
132 | 193 |
private static boolean checkTaxonIsAccepted(BerlinModelImportConfigurator config){ |
133 | 194 |
try { |
134 | 195 |
boolean result = true; |
135 | 196 |
Source source = config.getSource(); |
136 |
String strQuery = "SELECT cn.CommonNameId, cn.CommonName, pt.StatusFk, n.FullNameCache, s.Status, pt.PTRefFk, r.RefCache " +
|
|
197 |
String strQuery = "SELECT cn.CommonNameId, cn.CommonName, pt.StatusFk, n.FullNameCache, s.Status, pt.PTRefFk, r.RefCache " + |
|
137 | 198 |
" FROM emCommonName cn " + |
138 |
" INNER JOIN PTaxon pt ON cn.PTNameFk = pt.PTNameFk AND cn.PTRefFk = pt.PTRefFk " +
|
|
199 |
" INNER JOIN PTaxon pt ON cn.PTNameFk = pt.PTNameFk AND cn.PTRefFk = pt.PTRefFk " + |
|
139 | 200 |
" INNER JOIN Name n ON pt.PTNameFk = n.NameId " + |
140 | 201 |
" INNER JOIN Status s ON pt.StatusFk = s.StatusId " + |
141 |
" LEFT OUTER JOIN Reference r ON pt.PTRefFk = r.RefId " +
|
|
202 |
" LEFT OUTER JOIN Reference r ON pt.PTRefFk = r.RefId " + |
|
142 | 203 |
" WHERE (pt.StatusFk NOT IN ( 1, 5)) "; |
143 | 204 |
|
144 | 205 |
if (StringUtils.isNotBlank(config.getOccurrenceFilter())){ |
145 |
strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ;
|
|
206 |
strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ; |
|
146 | 207 |
} |
147 | 208 |
|
148 |
|
|
209 |
|
|
149 | 210 |
ResultSet resulSet = source.getResultSet(strQuery); |
150 | 211 |
boolean firstRow = true; |
151 | 212 |
while (resulSet.next()){ |
... | ... | |
160 | 221 |
String fullNameCache = resulSet.getString("FullNameCache"); |
161 | 222 |
String ptRefFk = resulSet.getString("PTRefFk"); |
162 | 223 |
String ptRef = resulSet.getString("RefCache"); |
163 |
|
|
164 |
System.out.println("CommonNameId: " + commonNameId + "\n CommonName: " + commonName +
|
|
165 |
"\n Status: " + status +
|
|
224 |
|
|
225 |
System.out.println("CommonNameId: " + commonNameId + "\n CommonName: " + commonName + |
|
226 |
"\n Status: " + status + |
|
166 | 227 |
"\n FullNameCache: " + fullNameCache + "\n ptRefFk: " + ptRefFk + |
167 | 228 |
"\n sec: " + ptRef ); |
168 |
|
|
229 |
|
|
169 | 230 |
result = firstRow = false; |
170 | 231 |
} |
171 |
|
|
232 |
|
|
172 | 233 |
return result; |
173 | 234 |
} catch (SQLException e) { |
174 | 235 |
e.printStackTrace(); |
175 | 236 |
return false; |
176 | 237 |
} |
177 | 238 |
} |
178 |
|
|
239 |
|
|
179 | 240 |
|
180 | 241 |
} |
Also available in: Unified diff
ref #7446 deduplicate common name language references in E+M