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