1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.apache.james.ai.classic;
21
22
23 import java.sql.Connection;
24 import java.sql.DatabaseMetaData;
25 import java.sql.PreparedStatement;
26 import java.sql.ResultSet;
27 import java.sql.SQLException;
28 import java.util.HashMap;
29 import java.util.Map;
30
31 import org.w3c.dom.Document;
32
33
34
35
36
37
38
39
40
41
42
43
44
45 abstract public class JDBCBayesianAnalyzer extends BayesianAnalyzer {
46
47
48 public final static String DATABASE_LOCK = "database lock";
49
50
51
52
53
54
55
56
57 abstract protected void delegatedLog(String errorString);
58
59
60
61
62 private final JDBCUtil theJDBCUtil = new JDBCUtil() {
63 protected void delegatedLog(String logString) {
64 this.delegatedLog(logString);
65 }
66 };
67
68
69 private SqlResources sqlQueries = new SqlResources();
70
71
72 private String sqlFileName;
73
74
75 private Map<String, String> sqlParameters = new HashMap<String, String>();
76
77
78 private static long lastDatabaseUpdateTime;
79
80
81
82
83
84
85 public String getSqlFileName() {
86
87 return this.sqlFileName;
88 }
89
90
91
92
93
94
95
96 public void setSqlFileName(String sqlFileName) {
97
98 this.sqlFileName = sqlFileName;
99 }
100
101
102
103
104
105
106 public Map<String, String> getSqlParameters() {
107
108 return this.sqlParameters;
109 }
110
111
112
113
114
115
116
117 public void setSqlParameters(Map<String, String> sqlParameters) {
118
119 this.sqlParameters = sqlParameters;
120 }
121
122
123
124
125
126
127 public static long getLastDatabaseUpdateTime() {
128
129 return lastDatabaseUpdateTime;
130 }
131
132
133
134
135 public static void touchLastDatabaseUpdateTime() {
136
137 lastDatabaseUpdateTime = System.currentTimeMillis();
138 }
139
140
141
142
143 public JDBCBayesianAnalyzer() {
144 }
145
146
147
148
149
150
151
152
153
154 public void loadHamNSpam(Connection conn) throws java.sql.SQLException {
155 PreparedStatement pstmt = null;
156 ResultSet rs = null;
157
158 try {
159 pstmt = conn.prepareStatement(sqlQueries.getSqlString("selectHamTokens", true));
160 rs = pstmt.executeQuery();
161
162 Map<String, Integer> ham = getHamTokenCounts();
163 while (rs.next()) {
164 String token = rs.getString(1);
165 int count = rs.getInt(2);
166
167 if (count > 1) {
168 ham.put(token, Integer.valueOf(count));
169 }
170 }
171
172 delegatedLog("Ham tokens count: " + ham.size());
173
174 rs.close();
175 pstmt.close();
176
177
178 pstmt = conn.prepareStatement(sqlQueries.getSqlString("selectSpamTokens", true));
179 rs = pstmt.executeQuery();
180
181 Map<String, Integer> spam = getSpamTokenCounts();
182 while (rs.next()) {
183 String token = rs.getString(1);
184 int count = rs.getInt(2);
185
186 if (count > 1) {
187 spam.put(token, new Integer(count));
188 }
189 }
190
191
192 delegatedLog("Spam tokens count: " + spam.size());
193
194 rs.close();
195 pstmt.close();
196
197
198 pstmt = conn.prepareStatement(sqlQueries.getSqlString("selectMessageCounts", true));
199 rs = pstmt.executeQuery();
200 if (rs.next()) {
201 setHamMessageCount(rs.getInt(1));
202 setSpamMessageCount(rs.getInt(2));
203 }
204
205 rs.close();
206 pstmt.close();
207
208 } finally {
209 if (rs != null) {
210 try {
211 rs.close();
212 } catch (java.sql.SQLException se) {
213 }
214
215 rs = null;
216 }
217
218 if (pstmt != null) {
219 try {
220 pstmt.close();
221 } catch (java.sql.SQLException se) {
222 }
223
224 pstmt = null;
225 }
226 }
227 }
228
229
230
231
232
233
234
235
236
237 public void updateHamTokens(Connection conn) throws java.sql.SQLException {
238 updateTokens(conn, getHamTokenCounts(), sqlQueries.getSqlString("insertHamToken", true), sqlQueries.getSqlString("updateHamToken", true));
239
240 setMessageCount(conn, sqlQueries.getSqlString("updateHamMessageCounts", true), getHamMessageCount());
241 }
242
243
244
245
246
247
248
249
250
251 public void updateSpamTokens(Connection conn) throws java.sql.SQLException {
252 updateTokens(conn, getSpamTokenCounts(), sqlQueries.getSqlString("insertSpamToken", true), sqlQueries.getSqlString("updateSpamToken", true));
253
254 setMessageCount(conn, sqlQueries.getSqlString("updateSpamMessageCounts", true), getSpamMessageCount());
255 }
256
257
258
259
260
261
262
263
264
265 public void resetData(Connection conn) throws SQLException {
266 deleteData(conn, sqlQueries.getSqlString("deleteHamTokens", true));
267 deleteData(conn, sqlQueries.getSqlString("deleteSpamTokens", true));
268 deleteData(conn, sqlQueries.getSqlString("deleteMessageCounts", true));
269 }
270
271 private void setMessageCount(Connection conn, String sqlStatement, int count) throws java.sql.SQLException {
272 PreparedStatement init = null;
273 PreparedStatement update = null;
274
275 try {
276
277 init = conn.prepareStatement(sqlQueries.getSqlString("initializeMessageCounts", true));
278 update = conn.prepareStatement(sqlStatement);
279
280 update.setInt(1, count);
281
282 if (update.executeUpdate() == 0) {
283 init.executeUpdate();
284 update.executeUpdate();
285 }
286
287 } finally {
288 if (init != null) {
289 try {
290 init.close();
291 } catch (java.sql.SQLException ignore) {
292 }
293 }
294 if (update != null) {
295 try {
296 update.close();
297 } catch (java.sql.SQLException ignore) {
298 }
299 }
300 }
301 }
302
303 private void updateTokens(Connection conn, Map<String, Integer> tokens, String insertSqlStatement, String updateSqlStatement) throws java.sql.SQLException {
304 PreparedStatement insert = null;
305 PreparedStatement update = null;
306
307 try {
308
309 insert = conn.prepareStatement(insertSqlStatement);
310
311
312 update = conn.prepareStatement(updateSqlStatement);
313
314 for (Map.Entry<String, Integer> entry : tokens.entrySet()) {
315 update.setInt(1, entry.getValue());
316 update.setString(2, entry.getKey());
317
318
319
320
321 if (update.executeUpdate() == 0) {
322 insert.setString(1, entry.getKey());
323 insert.setInt(2, entry.getValue());
324
325 insert.executeUpdate();
326 }
327 }
328 } finally {
329 if (insert != null) {
330 try {
331 insert.close();
332 } catch (java.sql.SQLException ignore) {
333 }
334
335 insert = null;
336 }
337
338 if (update != null) {
339 try {
340 update.close();
341 } catch (java.sql.SQLException ignore) {
342 }
343
344 update = null;
345 }
346 }
347 }
348
349
350
351
352
353
354
355
356
357
358
359
360 public void initSqlQueries(Connection conn, Document sqlConfiguration) throws Exception {
361 try {
362 if (conn.getAutoCommit()) {
363 conn.setAutoCommit(false);
364 }
365
366 sqlQueries.init(sqlConfiguration, JDBCBayesianAnalyzer.class.getName(), conn, getSqlParameters());
367
368 checkTables(conn);
369 } finally {
370 theJDBCUtil.closeJDBCConnection(conn);
371 }
372 }
373
374 private void checkTables(Connection conn) throws SQLException {
375
376
377
378
379
380 boolean dbUpdated = false;
381
382 dbUpdated = createTable(conn, "hamTableName", "createHamTable");
383
384 dbUpdated = createTable(conn, "spamTableName", "createSpamTable");
385
386 dbUpdated = createTable(conn, "messageCountsTableName", "createMessageCountsTable");
387
388
389 if (conn != null && dbUpdated && !conn.getAutoCommit()) {
390 conn.commit();
391 dbUpdated = false;
392 }
393
394 }
395
396 private boolean createTable(Connection conn, String tableNameSqlStringName, String createSqlStringName) throws SQLException {
397 String tableName = sqlQueries.getSqlString(tableNameSqlStringName, true);
398
399 DatabaseMetaData dbMetaData = conn.getMetaData();
400
401
402 if (theJDBCUtil.tableExists(dbMetaData, tableName)) {
403 return false;
404 }
405
406 PreparedStatement createStatement = null;
407
408 try {
409 createStatement = conn.prepareStatement(sqlQueries.getSqlString(createSqlStringName, true));
410 createStatement.execute();
411
412 StringBuffer logBuffer = null;
413 logBuffer = new StringBuffer(64).append("Created table '").append(tableName).append("' using sqlResources string '").append(createSqlStringName).append("'.");
414 delegatedLog(logBuffer.toString());
415
416 } finally {
417 theJDBCUtil.closeJDBCStatement(createStatement);
418 }
419
420 return true;
421 }
422
423 private void deleteData(Connection conn, String deleteSqlStatement) throws SQLException {
424 PreparedStatement delete = null;
425
426 try {
427
428 delete = conn.prepareStatement(deleteSqlStatement);
429 delete.executeUpdate();
430 } finally {
431 if (delete != null) {
432 try {
433 delete.close();
434 } catch (java.sql.SQLException ignore) {
435 }
436
437 delete = null;
438 }
439 }
440 }
441 }