001 /*
002 * Copyright 2010 the original author or authors.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package com.hs.mail.imap.dao;
017
018 import java.sql.Connection;
019 import java.sql.PreparedStatement;
020 import java.sql.ResultSet;
021 import java.sql.SQLException;
022 import java.sql.Statement;
023 import java.sql.Timestamp;
024 import java.util.ArrayList;
025 import java.util.Date;
026 import java.util.HashMap;
027 import java.util.List;
028 import java.util.Map;
029
030 import javax.mail.Flags;
031
032 import org.apache.commons.collections.CollectionUtils;
033 import org.apache.commons.lang.ArrayUtils;
034 import org.apache.james.mime4j.parser.Field;
035 import org.springframework.jdbc.core.PreparedStatementCreator;
036 import org.springframework.jdbc.core.RowMapper;
037 import org.springframework.jdbc.support.GeneratedKeyHolder;
038 import org.springframework.jdbc.support.KeyHolder;
039 import org.springframework.jdbc.support.rowset.SqlRowSet;
040
041 import com.hs.mail.imap.message.FetchData;
042 import com.hs.mail.imap.message.MailMessage;
043 import com.hs.mail.imap.message.MessageHeader;
044 import com.hs.mail.imap.message.PhysMessage;
045
046 /**
047 *
048 * @author Won Chul Doh
049 * @since Mar 23, 2010
050 *
051 */
052 public class MySqlMessageDao extends AbstractDao implements MessageDao {
053
054 public List<Long> getMessageIDList(long mailboxID) {
055 String sql = "SELECT messageid FROM message WHERE mailboxid = ? ORDER BY messageid";
056 return (List<Long>) getJdbcTemplate().queryForList(sql,
057 new Object[] { new Long(mailboxID) }, Long.class);
058 }
059
060 public void addMessage(long mailboxID, MailMessage message) {
061 if (message.getPhysMessageID() == 0) {
062 addPhysicalMessage(message);
063 }
064 addMessage(message.getPhysMessageID(), mailboxID);
065 }
066
067 private long addPhysicalMessage(final MailMessage message) {
068 final String sql = "INSERT INTO physmessage (size, internaldate, subject, sentdate, fromaddr) VALUES(?, ?, ?, ?, ?)";
069 final MessageHeader header = message.getHeader();
070 KeyHolder keyHolder = new GeneratedKeyHolder();
071 getJdbcTemplate().update(new PreparedStatementCreator() {
072 public PreparedStatement createPreparedStatement(Connection con)
073 throws SQLException {
074 PreparedStatement pstmt = con.prepareStatement(sql,
075 Statement.RETURN_GENERATED_KEYS);
076 pstmt.setLong(1, message.getSize()); // size
077 pstmt.setTimestamp(2, new Timestamp(message.getInternalDate()
078 .getTime())); // internaldate
079 pstmt.setString(3, header.getSubject()); // subject
080 Date sent = header.getDate();
081 pstmt.setTimestamp(4, (sent != null) ? new Timestamp(sent
082 .getTime()) : null); // sentdate
083 pstmt.setString(5, header.getFrom().getDisplayString()); // fromaddr
084 return pstmt;
085 }
086 }, keyHolder);
087 long physmessageid = keyHolder.getKey().longValue();
088 message.setPhysMessageID(physmessageid);
089 addHeader(physmessageid, header);
090 return physmessageid;
091 }
092
093 private void addMessage(long physMessageID, long mailboxID) {
094 String sql = "INSERT INTO message (physmessageid, mailboxid) VALUES(?, ?)";
095 getJdbcTemplate().update(sql,
096 new Object[] { new Long(physMessageID), new Long(mailboxID) });
097 }
098
099 public void addMessage(long mailboxID, MailMessage message, Flags flags) {
100 if (message.getPhysMessageID() == 0) {
101 addPhysicalMessage(message);
102 }
103 addMessage(message.getPhysMessageID(), mailboxID, flags);
104 }
105
106 private void addMessage(long physMessageID, long mailboxID, Flags flags) {
107 String sql = "INSERT INTO message (physmessageid, mailboxid, seen, answered, deleted, flagged, draft) VALUES(?, ?, ?, ?, ?, ?, ?)";
108 getJdbcTemplate().update(
109 sql,
110 new Object[] { new Long(physMessageID), new Long(mailboxID),
111 FlagUtils.getParam(flags, Flags.Flag.SEEN),
112 FlagUtils.getParam(flags, Flags.Flag.ANSWERED),
113 FlagUtils.getParam(flags, Flags.Flag.DELETED),
114 FlagUtils.getParam(flags, Flags.Flag.FLAGGED),
115 FlagUtils.getParam(flags, Flags.Flag.DRAFT) });
116 }
117
118 public void copyMessage(long messageID, long mailboxID) {
119 // The flags and internal date of the message SHOULD be preserved, and
120 // the Recent flag SHOULD be set, in the copy.
121 String sql = "INSERT INTO message (mailboxid, physmessageid, seen, answered, deleted, flagged, draft) SELECT ?, physmessageid, seen, answered, deleted, flagged, draft FROM message WHERE messageid = ?";
122 getJdbcTemplate().update(sql,
123 new Object[] { new Long(mailboxID), new Long(messageID) });
124 }
125
126 public FetchData getMessageFetchData(long messageID) {
127 String sql = "SELECT * FROM message m, physmessage p WHERE m.messageid = ? AND m.physmessageid = p.id";
128 FetchData fd = (FetchData) queryForObject(sql, new Object[] { new Long(
129 messageID) }, fetchDataRowMapper);
130 if (fd != null) {
131 List<String> ufs = getUserFlags(messageID);
132 if (CollectionUtils.isNotEmpty(ufs)) {
133 Flags flags = fd.getFlags();
134 for (String uf : ufs) {
135 flags.add(uf);
136 }
137 }
138 }
139 return fd;
140 }
141
142 public void deleteMessage(long messageID) {
143 String sql = "DELETE m.*, k.* FROM message AS m LEFT JOIN keyword AS k ON (m.messageid = k.messageid) WHERE m.messageid = ?";
144 getJdbcTemplate().update(sql, new Object[] { new Long(messageID) });
145 }
146
147 public PhysMessage getDanglingMessageID(long messageID) {
148 String sql = "SELECT m.physmessageid, p.internaldate FROM message m, physmessage p WHERE m.physmessageid = (SELECT physmessageid FROM message WHERE messageid = ?) AND p.id=m.physmessageid GROUP BY m.physmessageid HAVING COUNT(m.physmessageid) = 1";
149 return (PhysMessage) queryForObject(sql,
150 new Object[] { new Long(messageID) }, new RowMapper() {
151 public Object mapRow(ResultSet rs, int rowNum)
152 throws SQLException {
153 PhysMessage pm = new PhysMessage();
154 pm.setPhysMessageID(rs.getLong("physmessageid"));
155 pm.setInternalDate(new Date(rs.getTimestamp("internaldate").getTime()));
156 return pm;
157 }
158 }
159 );
160 }
161
162 public void deletePhysicalMessage(long physMessageID) {
163 String[] sql = {
164 "DELETE FROM physmessage WHERE id = ?",
165 "DELETE FROM headervalue WHERE physmessageid = ?"
166 };
167 Object[] param = { new Long(physMessageID) };
168 getJdbcTemplate().update(sql[0], param);
169 getJdbcTemplate().update(sql[1], param);
170 }
171
172 public List<Long> resetRecent(long mailboxID) {
173 String sql = "SELECT messageid FROM message WHERE mailboxid = ? AND recent = 'Y'";
174 Object[] param = new Object[] { new Long(mailboxID) };
175 List<Long> result = getJdbcTemplate().queryForList(sql, param,
176 Long.class);
177 if (CollectionUtils.isNotEmpty(result)) {
178 sql = "UPDATE message SET recent = 'N' WHERE mailboxid = ? AND recent = 'Y'";
179 getJdbcTemplate().update(sql, param);
180 }
181 return result;
182 }
183
184 public void setFlags(long messageID, Flags flags, boolean replace,
185 boolean set) {
186 setSystemFlags(messageID, flags.getSystemFlags(), replace, set);
187 setUserFlags(messageID, flags.getUserFlags(), replace, set);
188 }
189
190 private int setSystemFlags(long messageID, Flags.Flag[] flags,
191 boolean replace, boolean set) {
192 if (ArrayUtils.isEmpty(flags)) {
193 return 0;
194 }
195 StringBuilder sql = new StringBuilder("UPDATE message SET ");
196 List params = new ArrayList();
197 sql.append(FlagUtils.buildParams(flags, replace, set, params));
198 sql.append(" WHERE messageid = ?");
199 params.add(new Long(messageID));
200 return getJdbcTemplate().update(sql.toString(), params.toArray());
201 }
202
203 private void setUserFlags(long messageID, String[] flags, boolean replace,
204 boolean set) {
205 if (replace) {
206 String sql = "DELETE FROM keyword WHERE messageid = ?";
207 getJdbcTemplate().update(sql, new Object[] { new Long(messageID) });
208 }
209 if (!ArrayUtils.isEmpty(flags)) {
210 String sql = (replace || set) ? "INSERT INTO keyword (messageid, keyword) VALUES(?, ?)"
211 : "DELETE FROM keyword WHERE messageid = ? AND keyword = ?";
212 for (int i = 0; i < flags.length; i++) {
213 getJdbcTemplate().update(sql,
214 new Object[] { new Long(messageID), flags[i] });
215 }
216 }
217 }
218
219 public Flags getFlags(long messageID) {
220 Flags flags = getSystemFlags(messageID);
221 if (flags == null) {
222 flags = new Flags();
223 }
224 List<String> ufs = getUserFlags(messageID);
225 if (CollectionUtils.isNotEmpty(ufs)) {
226 for (String uf : ufs) {
227 flags.add(uf);
228 }
229 }
230 return flags;
231 }
232
233 private Flags getSystemFlags(long messageID) {
234 String sql = "SELECT * FROM message WHERE messageid = ?";
235 return (Flags) queryForObject(sql,
236 new Object[] { new Long(messageID) }, new RowMapper() {
237 public Object mapRow(ResultSet rs, int rowNum)
238 throws SQLException {
239 return FlagUtils.getFlags(rs);
240 }
241 });
242 }
243
244 private List<String> getUserFlags(long messageID) {
245 String sql = "SELECT keyword FROM keyword WHERE messageid = ?";
246 return getJdbcTemplate().queryForList(sql,
247 new Object[] { new Long(messageID) }, String.class);
248 }
249
250 //-------------------------------------------------------------------------
251 // Methods dealing with message header
252 //-------------------------------------------------------------------------
253
254 public Map<String, String> getHeader(long physMessageID) {
255 String sql = "SELECT headername, headervalue FROM headername n, headervalue v WHERE v.physmessageid = ? AND v.headernameid = n.id";
256 SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql,
257 new Object[] { new Long(physMessageID) });
258 Map<String, String> results = new HashMap<String, String>();
259 while (rs.next()) {
260 results.put(rs.getString(1), rs.getString(2));
261 }
262 return results;
263 }
264
265 public Map<String, String> getHeader(long physMessageID, String[] fields) {
266 String sql = "SELECT headername, headervalue FROM headername n, headervalue v WHERE v.physmessageid = ? AND v.headernameid = n.id AND n.headername IN ";
267 Object[] param = new Object[fields.length + 1];
268 param[0] = new Long(physMessageID);
269 sql += duplicate(fields, param, 1);
270 Map<String, String> results = new HashMap<String, String>();
271 SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql, param);
272 while (rs.next()) {
273 results.put(rs.getString(1), rs.getString(2));
274 }
275 return results;
276 }
277
278 public void addHeader(long physMessageID, MessageHeader header) {
279 List<Field> fields = header.getHeader().getFields();
280 if (!CollectionUtils.isEmpty(fields)) {
281 for (Field field : fields) {
282 addField(physMessageID, field);
283 }
284 }
285 }
286
287 private void addField(long physMessageID, Field field) {
288 long id = getHeaderNameID(field.getName());
289 addHeaderValue(physMessageID, id, field.getBody());
290 }
291
292 public long getHeaderNameID(String headerName) {
293 String sql = "SELECT id FROM headername WHERE headername = ?";
294 long result = queryForLong(sql, new Object[] { headerName });
295 if (result == 0) {
296 result = addHeaderName(headerName);
297 }
298 return result;
299 }
300
301 private long addHeaderName(final String headerName) {
302 KeyHolder keyHolder = new GeneratedKeyHolder();
303 getJdbcTemplate().update(new PreparedStatementCreator() {
304 public PreparedStatement createPreparedStatement(Connection con)
305 throws SQLException {
306 String sql = "INSERT INTO headername (headername) VALUES(?)";
307 PreparedStatement pstmt = con.prepareStatement(sql,
308 Statement.RETURN_GENERATED_KEYS);
309 pstmt.setString(1, headerName);
310 return pstmt;
311 }
312 }, keyHolder);
313 return keyHolder.getKey().longValue();
314 }
315
316 private void addHeaderValue(long physMessageID, long headerNameID,
317 String headerValue) {
318 String sql = "INSERT INTO headervalue (physmessageid, headernameid, headervalue) VALUES(?, ?, ?)";
319 getJdbcTemplate().update(
320 sql,
321 new Object[] { new Long(physMessageID), new Long(headerNameID),
322 headerValue });
323 }
324
325 private static RowMapper fetchDataRowMapper = new RowMapper() {
326 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
327 FetchData fd = new FetchData();
328 fd.setMessageID(rs.getLong("messageid"));
329 fd.setPhysMessageID(rs.getLong("physmessageid"));
330 fd.setSize(rs.getLong("size"));
331 fd.setFlags(FlagUtils.getFlags(rs));
332 fd.setInternalDate(new Date(rs.getTimestamp("internaldate").getTime()));
333 return fd;
334 }
335 };
336
337 }