View Javadoc

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                 // to reduce memory, use the token only if the count is > 1
170                 if (count > 1) {
171                     ham.put(token, new Integer(count));
172                 }
173             }
174             //Verbose.
175             delegatedLog("Ham tokens count: " + ham.size());
176             
177             rs.close();
178             pstmt.close();
179                         
180             //Get the spam tokens/counts.
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                 // to reduce memory, use the token only if the count is > 1
189                 if (count > 1) {
190                     spam.put(token, new Integer(count));
191                 }
192             }
193             
194             //Verbose.
195             delegatedLog("Spam tokens count: " + spam.size());
196             
197             rs.close();
198             pstmt.close();
199                         
200             //Get the ham/spam message counts.
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             //set the ham/spam message counts.
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             //Used to insert new token entries.
300             insert = conn.prepareStatement(insertSqlStatement);
301             
302             //Used to update existing token entries.
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                 //If the update affected 0 (zero) rows, then the token hasn't been
314                 //encountered before, and we need to add it to the corpus.
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         // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
369         // Try UPPER, lower, and MixedCase, to see if the table is there.
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         //Commit our changes if necessary.
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         // Try UPPER, lower, and MixedCase, to see if the table is there.
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 }