1 /*****************************************************************
2 * Licensed to the Apache Software Foundation (ASF) under one *
3 * or more contributor license agreements. See the NOTICE file *
4 * distributed with this work for additional information *
5 * regarding copyright ownership. The ASF licenses this file *
6 * to you under the Apache License, Version 2.0 (the *
7 * "License"); you may not use this file except in compliance *
8 * with the License. You may obtain a copy of the License at *
9 * *
10 * http://www.apache.org/licenses/LICENSE-2.0 *
11 * *
12 * Unless required by applicable law or agreed to in writing, *
13 * software distributed under the License is distributed on an *
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY *
15 * KIND, either express or implied. See the License for the *
16 * specific language governing permissions and limitations *
17 * under the License. *
18 ****************************************************************/
19
20 package org.apache.james.util;
21
22 import java.util.HashMap;
23 import java.util.Iterator;
24 import java.util.Map;
25
26 import java.io.File;
27
28 import java.sql.Connection;
29 import java.sql.PreparedStatement;
30 import java.sql.ResultSet;
31 import java.sql.SQLException;
32 import java.sql.DatabaseMetaData;
33
34 /***
35 * Manages the persistence of the spam bayesian analysis corpus using a JDBC database.
36 *
37 * <p>This class is abstract to allow implementations to
38 * take advantage of different logging capabilities/interfaces in
39 * different parts of the code.</p>
40
41 * @version CVS $Revision: $ $Date: $
42 * @since 2.3.0
43 */
44
45 abstract public class JDBCBayesianAnalyzer
46 extends BayesianAnalyzer {
47
48 /***
49 *Public object representing a lock on database activity.
50 */
51 public final static String DATABASE_LOCK = "database lock";
52
53 /***
54 * An abstract method which child classes override to handle logging of
55 * errors in their particular environments.
56 *
57 * @param errorString the error message generated
58 */
59 abstract protected void delegatedLog(String errorString);
60
61 /***
62 * The JDBCUtil helper class
63 */
64 private final JDBCUtil theJDBCUtil = new JDBCUtil() {
65 protected void delegatedLog(String logString) {
66 this.delegatedLog(logString);
67 }
68 };
69
70 /***
71 * Contains all of the sql strings for this component.
72 */
73 private SqlResources sqlQueries = new SqlResources();
74
75 /***
76 * Holds value of property sqlFileName.
77 */
78 private String sqlFileName;
79
80 private File sqlFile;
81
82 /***
83 * Holds value of property sqlParameters.
84 */
85 private Map sqlParameters = new HashMap();
86
87 /***
88 * Holds value of property lastDatabaseUpdateTime.
89 */
90 private static long lastDatabaseUpdateTime;
91
92 /***
93 * Getter for property sqlFileName.
94 * @return Value of property sqlFileName.
95 */
96 public String getSqlFileName() {
97
98 return this.sqlFileName;
99 }
100
101 /***
102 * Setter for property sqlFileName.
103 * @param sqlFileName New value of property sqlFileName.
104 */
105 public void setSqlFileName(String sqlFileName) {
106
107 this.sqlFileName = sqlFileName;
108 }
109
110 /***
111 * Getter for property sqlParameters.
112 * @return Value of property sqlParameters.
113 */
114 public Map getSqlParameters() {
115
116 return this.sqlParameters;
117 }
118
119 /***
120 * Setter for property sqlParameters.
121 * @param sqlParameters New value of property sqlParameters.
122 */
123 public void setSqlParameters(Map sqlParameters) {
124
125 this.sqlParameters = sqlParameters;
126 }
127
128 /***
129 * Getter for static lastDatabaseUpdateTime.
130 * @return Value of property lastDatabaseUpdateTime.
131 */
132 public static long getLastDatabaseUpdateTime() {
133
134 return lastDatabaseUpdateTime;
135 }
136
137 /***
138 * Sets static lastDatabaseUpdateTime to System.currentTimeMillis().
139 */
140 public static void touchLastDatabaseUpdateTime() {
141
142 lastDatabaseUpdateTime = System.currentTimeMillis();
143 }
144
145 /***
146 * Default constructor.
147 */
148 public JDBCBayesianAnalyzer() {
149 }
150
151 /***
152 * Loads the token frequencies from the database.
153 * @param conn The connection for accessing the database
154 * @throws SQLException If a database error occurs
155 */
156 public void loadHamNSpam(Connection conn)
157 throws java.sql.SQLException {
158 PreparedStatement pstmt = null;
159 ResultSet rs = null;
160
161 try {
162 pstmt = conn.prepareStatement(sqlQueries.getSqlString("selectHamTokens", true));
163 rs = pstmt.executeQuery();
164
165 Map ham = getHamTokenCounts();
166 while (rs.next()) {
167 String token = rs.getString(1);
168 int count = rs.getInt(2);
169
170 if (count > 1) {
171 ham.put(token, new Integer(count));
172 }
173 }
174
175 delegatedLog("Ham tokens count: " + ham.size());
176
177 rs.close();
178 pstmt.close();
179
180
181 pstmt = conn.prepareStatement(sqlQueries.getSqlString("selectSpamTokens", true));
182 rs = pstmt.executeQuery();
183
184 Map spam = getSpamTokenCounts();
185 while (rs.next()) {
186 String token = rs.getString(1);
187 int count = rs.getInt(2);
188
189 if (count > 1) {
190 spam.put(token, new Integer(count));
191 }
192 }
193
194
195 delegatedLog("Spam tokens count: " + spam.size());
196
197 rs.close();
198 pstmt.close();
199
200
201 pstmt = conn.prepareStatement(sqlQueries.getSqlString("selectMessageCounts", true));
202 rs = pstmt.executeQuery();
203 if (rs.next()) {
204 setHamMessageCount(rs.getInt(1));
205 setSpamMessageCount(rs.getInt(2));
206 }
207
208 rs.close();
209 pstmt.close();
210
211 } finally {
212 if (rs != null) {
213 try {
214 rs.close();
215 } catch (java.sql.SQLException se) {
216 }
217
218 rs = null;
219 }
220
221 if (pstmt != null) {
222 try {
223 pstmt.close();
224 } catch (java.sql.SQLException se) {
225 }
226
227 pstmt = null;
228 }
229 }
230 }
231
232 /***
233 * Updates the database with new "ham" token frequencies.
234 * @param conn The connection for accessing the database
235 * @throws SQLException If a database error occurs
236 */
237 public void updateHamTokens(Connection conn)
238 throws java.sql.SQLException {
239 updateTokens(conn, getHamTokenCounts(),
240 sqlQueries.getSqlString("insertHamToken", true),
241 sqlQueries.getSqlString("updateHamToken", true));
242
243 setMessageCount(conn, sqlQueries.getSqlString("updateHamMessageCounts", true), getHamMessageCount());
244 }
245
246 /***
247 * Updates the database with new "spam" token frequencies.
248 * @param conn The connection for accessing the database
249 * @throws SQLException If a database error occurs
250 */
251 public void updateSpamTokens(Connection conn)
252 throws java.sql.SQLException {
253 updateTokens(conn, getSpamTokenCounts(),
254 sqlQueries.getSqlString("insertSpamToken", true),
255 sqlQueries.getSqlString("updateSpamToken", true));
256
257 setMessageCount(conn, sqlQueries.getSqlString("updateSpamMessageCounts", true), getSpamMessageCount());
258 }
259
260 private void setMessageCount(Connection conn, String sqlStatement, int count)
261 throws java.sql.SQLException {
262 PreparedStatement init = null;
263 PreparedStatement update = null;
264
265 try {
266
267 init = conn.prepareStatement(sqlQueries.getSqlString("initializeMessageCounts", true));
268 update = conn.prepareStatement(sqlStatement);
269
270 update.setInt(1, count);
271
272 if (update.executeUpdate() == 0) {
273 init.executeUpdate();
274 update.executeUpdate();
275 }
276
277 } finally {
278 if (init != null) {
279 try {
280 init.close();
281 } catch (java.sql.SQLException ignore) {
282 }
283 }
284 if (update != null) {
285 try {
286 update.close();
287 } catch (java.sql.SQLException ignore) {
288 }
289 }
290 }
291 }
292
293 private void updateTokens(Connection conn, Map tokens, String insertSqlStatement, String updateSqlStatement)
294 throws java.sql.SQLException {
295 PreparedStatement insert = null;
296 PreparedStatement update = null;
297
298 try {
299
300 insert = conn.prepareStatement(insertSqlStatement);
301
302
303 update = conn.prepareStatement(updateSqlStatement);
304
305 Iterator i = tokens.keySet().iterator();
306 while (i.hasNext()) {
307 String key = (String) i.next();
308 int value = ((Integer) tokens.get(key)).intValue();
309
310 update.setInt(1, value);
311 update.setString(2, key);
312
313
314
315 if (update.executeUpdate() == 0) {
316 insert.setString(1, key);
317 insert.setInt(2, value);
318
319 insert.executeUpdate();
320 }
321 }
322 } finally {
323 if (insert != null) {
324 try {
325 insert.close();
326 } catch (java.sql.SQLException ignore) {
327 }
328
329 insert = null;
330 }
331
332 if (update != null) {
333 try {
334 update.close();
335 } catch (java.sql.SQLException ignore) {
336 }
337
338 update = null;
339 }
340 }
341 }
342
343 /***
344 * Initializes the sql query environment from the SqlResources file.
345 * Will look for conf/sqlResources.xml.
346 * @param conn The connection for accessing the database
347 * @param mailetContext The current mailet context,
348 * for finding the conf/sqlResources.xml file
349 * @throws Exception If any error occurs
350 */
351 public void initSqlQueries(Connection conn, org.apache.mailet.MailetContext mailetContext) throws Exception {
352 try {
353 if (conn.getAutoCommit()) {
354 conn.setAutoCommit(false);
355 }
356
357 this.sqlFile = new File((String) mailetContext.getAttribute("confDir"), "sqlResources.xml").getCanonicalFile();
358 sqlQueries.init(this.sqlFile, JDBCBayesianAnalyzer.class.getName() , conn, getSqlParameters());
359
360 checkTables(conn);
361 } finally {
362 theJDBCUtil.closeJDBCConnection(conn);
363 }
364 }
365
366 private void checkTables(Connection conn) throws SQLException {
367 DatabaseMetaData dbMetaData = conn.getMetaData();
368
369
370
371 boolean dbUpdated = false;
372
373 dbUpdated = createTable(conn, "hamTableName", "createHamTable");
374
375 dbUpdated = createTable(conn, "spamTableName", "createSpamTable");
376
377 dbUpdated = createTable(conn, "messageCountsTableName", "createMessageCountsTable");
378
379
380 if (conn != null && dbUpdated && !conn.getAutoCommit()) {
381 conn.commit();
382 dbUpdated = false;
383 }
384
385 }
386
387 private boolean createTable(Connection conn, String tableNameSqlStringName, String createSqlStringName) throws SQLException {
388 String tableName = sqlQueries.getSqlString(tableNameSqlStringName, true);
389
390 DatabaseMetaData dbMetaData = conn.getMetaData();
391
392
393 if (theJDBCUtil.tableExists(dbMetaData, tableName)) {
394 return false;
395 }
396
397 PreparedStatement createStatement = null;
398
399 try {
400 createStatement =
401 conn.prepareStatement(sqlQueries.getSqlString(createSqlStringName, true));
402 createStatement.execute();
403
404 StringBuffer logBuffer = null;
405 logBuffer =
406 new StringBuffer(64)
407 .append("Created table '")
408 .append(tableName)
409 .append("' using sqlResources string '")
410 .append(createSqlStringName)
411 .append("'.");
412 delegatedLog(logBuffer.toString());
413
414 } finally {
415 theJDBCUtil.closeJDBCStatement(createStatement);
416 }
417
418 return true;
419 }
420
421 }