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.util.Date;
024 import java.util.List;
025
026 import org.apache.commons.lang.StringUtils;
027 import org.springframework.jdbc.core.PreparedStatementCreator;
028 import org.springframework.jdbc.core.RowMapper;
029 import org.springframework.jdbc.support.GeneratedKeyHolder;
030 import org.springframework.jdbc.support.KeyHolder;
031
032 import com.hs.mail.imap.ImapConstants;
033 import com.hs.mail.imap.mailbox.Mailbox;
034 import com.hs.mail.imap.message.PhysMessage;
035
036 /**
037 *
038 * @author Won Chul Doh
039 * @since Mar 23, 2010
040 *
041 */
042 public class MySqlMailboxDao extends AbstractDao implements MailboxDao {
043
044 public Mailbox getMailbox(long ownerID, String mailboxName) {
045 String sql = "SELECT * FROM mailbox USE INDEX (fk_mailbox_user) WHERE ownerid = ? AND name = ?";
046 return (Mailbox) queryForObject(sql, new Object[] { new Long(ownerID),
047 mailboxName }, mailboxRowMapper);
048 }
049
050 public boolean mailboxExists(long ownerID, String mailboxName) {
051 String sql = "SELECT COUNT(1) FROM mailbox USE INDEX (fk_mailbox_user) WHERE ownerid = ? AND name = ?";
052 return queryForInt(sql, new Object[] { new Long(ownerID), mailboxName }) > 0;
053 }
054
055 public Mailbox createMailbox(long ownerID, String mailboxName) {
056 Mailbox result = doCreateMailbox(ownerID, mailboxName);
057 forceCreate(ownerID, Mailbox.getParent(mailboxName));
058 return result;
059 }
060
061 public void renameMailbox(Mailbox source, String dest) {
062 long ownerid = source.getOwnerID();
063
064 // If the server¡¯s hierarchy separator character appears in the
065 // name, the server SHOULD create any superior hierarchical names
066 // that are needed.
067 forceCreate(ownerid, Mailbox.getParent(dest));
068
069 // If INBOX is renamed, then all messages in INBOX MUST be moved to
070 // a new mailbox with the given name, leaving INBOX empty.
071 String base = source.getName();
072 if (ImapConstants.INBOX_NAME.equals(base)) {
073 doCreateMailbox(ownerid, ImapConstants.INBOX_NAME);
074 }
075 doRenameMailbox(source.rename(base, dest));
076
077 // If source name has inferior hierarchical names, then the inferior
078 // hierarchical names MUST also be renamed.
079 List<Mailbox> children = getChildren(ownerid, base);
080 for (Mailbox child : children) {
081 doRenameMailbox(child.rename(base, dest));
082 }
083 }
084
085 private void forceCreate(long ownerID, String mailboxName) {
086 while (!"".equals(mailboxName) && !mailboxExists(ownerID, mailboxName)) {
087 doCreateMailbox(ownerID, mailboxName);
088 mailboxName = Mailbox.getParent(mailboxName);
089 }
090 }
091
092 private Mailbox doCreateMailbox(final long ownerID, final String mailboxName) {
093 final String sql = "INSERT INTO mailbox (name, ownerid, nextuid, uidvalidity) VALUES(?, ?, ?, ?)";
094 final long uidValidity = System.currentTimeMillis();
095 KeyHolder keyHolder = new GeneratedKeyHolder();
096 getJdbcTemplate().update(new PreparedStatementCreator() {
097 public PreparedStatement createPreparedStatement(Connection con)
098 throws SQLException {
099 PreparedStatement pstmt = con.prepareStatement(sql,
100 Statement.RETURN_GENERATED_KEYS);
101 pstmt.setString(1, mailboxName);
102 pstmt.setLong(2, ownerID);
103 pstmt.setLong(3, 1);
104 pstmt.setLong(4, uidValidity);
105 return pstmt;
106 }
107 }, keyHolder);
108
109 Mailbox mailbox = new Mailbox();
110 mailbox.setMailboxID(keyHolder.getKey().longValue());
111 mailbox.setOwnerID(ownerID);
112 mailbox.setName(mailboxName);
113 mailbox.setNextUID(1);
114 mailbox.setUidValidity(uidValidity);
115
116 return mailbox;
117 }
118
119 public List<Mailbox> getChildren(long userID, long ownerID,
120 String mailboxName, boolean subscribed) {
121 if (subscribed)
122 return getSubscriptions(userID, ownerID, mailboxName);
123 else
124 return getChildren(ownerID, mailboxName);
125 }
126
127 private List<Mailbox> getChildren(long ownerID, String mailboxName) {
128 if (StringUtils.isEmpty(mailboxName)) {
129 String sql = "SELECT * FROM mailbox USE INDEX (fk_mailbox_user) WHERE ownerid = ? ORDER BY name";
130 return getJdbcTemplate().query(sql,
131 new Object[] { new Long(ownerID) }, mailboxRowMapper);
132 } else {
133 String sql = "SELECT * FROM mailbox USE INDEX (fk_mailbox_user) WHERE ownerid = ? AND name LIKE ? ORDER BY name";
134 return getJdbcTemplate().query(
135 sql,
136 new Object[] {
137 new Long(ownerID),
138 new StringBuilder(escape(mailboxName))
139 .append(Mailbox.folderSeparator).append('%')
140 .toString() }, mailboxRowMapper);
141 }
142 }
143
144 private List<Mailbox> getSubscriptions(long userID, long ownerID,
145 String mailboxName) {
146 if (StringUtils.isEmpty(mailboxName)) {
147 String sql = "SELECT b.* FROM mailbox b, subscription s WHERE s.userid = ? AND b.ownerid = ? AND b.name = s.name ORDER BY b.name";
148 return getJdbcTemplate().query(sql,
149 new Object[] { new Long(userID), new Long(ownerID) },
150 mailboxRowMapper);
151 } else {
152 String sql = "SELECT b.* FROM mailbox b, subscription s WHERE s.userid = ? AND b.ownerid = ? AND b.name LIKE ? AND b.name = s.name ORDER BY b.name";
153 return getJdbcTemplate().query(
154 sql,
155 new Object[] {
156 new Long(userID),
157 new Long(ownerID),
158 new StringBuilder(escape(mailboxName))
159 .append(Mailbox.folderSeparator).append('%')
160 .toString() }, mailboxRowMapper);
161 }
162 }
163
164 public int getChildCount(long ownerID, String mailboxName) {
165 if (StringUtils.isEmpty(mailboxName)) {
166 String sql = "SELECT COUNT(1) FROM mailbox USE INDEX (fk_mailbox_user) WHERE ownerid = ?";
167 Object[] params = { new Long(ownerID) };
168 return queryForInt(sql, params);
169 } else {
170 String sql = "SELECT COUNT(1) FROM mailbox USE INDEX (fk_mailbox_user) WHERE ownerid = ? AND name LIKE ?";
171 Object[] params = {
172 new Long(ownerID),
173 new StringBuilder(escape(mailboxName)).append(
174 Mailbox.folderSeparator).append('%').toString() };
175 return queryForInt(sql, params);
176 }
177 }
178
179 public List<Long> getMailboxIDList(String mailboxName) {
180 String sql = "SELECT mailboxid FROM mailbox WHERE name = ?";
181 return (List<Long>) getJdbcTemplate().queryForList(sql,
182 new Object[] { mailboxName }, Long.class);
183 }
184
185 public boolean isSubscribed(long userID, String mailboxName) {
186 String sql = "SELECT COUNT(1) FROM subscription WHERE userid = ? AND name = ?";
187 int count = getJdbcTemplate().queryForInt(sql,
188 new Object[] { new Long(userID), mailboxName });
189 return (count > 0);
190 }
191
192 public void addSubscription(long userID, long mailboxID, String mailboxName) {
193 if (!isSubscribed(userID, mailboxName)) {
194 String sql = "INSERT INTO subscription (userid, mailboxid, name) VALUES(?, ?, ?)";
195 getJdbcTemplate().update(
196 sql,
197 new Object[] { new Long(userID), new Long(mailboxID),
198 mailboxName });
199 } else {
200 // already subscribed to the mailbox, verified after attempt to
201 // subscribe
202 }
203 }
204
205 public void deleteSubscription(long userID, String mailboxName) {
206 String sql = "DELETE FROM subscription WHERE userid = ? AND name = ?";
207 getJdbcTemplate().update(sql,
208 new Object[] { new Long(userID), mailboxName });
209 }
210
211 private int doRenameMailbox(Mailbox mailbox) {
212 String sql = "UPDATE mailbox SET name = ? WHERE mailboxid = ?";
213 return getJdbcTemplate().update(
214 sql,
215 new Object[] { mailbox.getName(),
216 new Long(mailbox.getMailboxID()) });
217 }
218
219 public List<Long> getDeletedMessageIDList(long mailboxID) {
220 String sql = "SELECT messageid FROM message WHERE mailboxid = ? AND deleted = 'Y'";
221 return (List<Long>) getJdbcTemplate().queryForList(sql,
222 new Object[] { new Long(mailboxID) }, Long.class);
223 }
224
225 public void deleteMailboxes(long ownerID) {
226 String sql = "DELETE FROM mailbox WHERE ownerid = ?";
227 getJdbcTemplate().update(sql, new Object[] { new Long(ownerID) });
228 }
229
230 public void deleteMailbox(long ownerID, long mailboxID) {
231 String sql = "DELETE FROM mailbox WHERE mailboxid = ?";
232 getJdbcTemplate().update(sql, new Object[] { new Long(mailboxID) });
233 }
234
235 public void forbidSelectMailbox(long ownerID, long mailboxID) {
236 String sql = "UPDATE mailbox SET noselect = 'Y' WHERE mailboxid = ?";
237 getJdbcTemplate().update(sql, new Object[] { new Long(mailboxID) });
238 }
239
240 public List<PhysMessage> getDanglingMessageIDList(long ownerID) {
241 String sql = "SELECT m.physmessageid, p.internaldate FROM mailbox b, message m, physmessage p WHERE b.ownerid = ? AND m.physmessageid = p.id AND m.mailboxid = b.mailboxid GROUP BY m.physmessageid HAVING COUNT(m.physmessageid) = 1";
242 return (List<PhysMessage>) getJdbcTemplate().query(sql,
243 new Object[] { new Long(ownerID) }, new RowMapper() {
244 public Object mapRow(ResultSet rs, int rowNum)
245 throws SQLException {
246 PhysMessage pm = new PhysMessage();
247 pm.setPhysMessageID(rs.getLong("physmessageid"));
248 pm.setInternalDate(new Date(rs.getTimestamp("internaldate").getTime()));
249 return pm;
250 }
251
252 });
253 }
254
255 public List<PhysMessage> getDanglingMessageIDList(long ownerID,
256 long mailboxID) {
257 String sql = "SELECT m.physmessageid, p.internaldate FROM message m, message n, physmessage p WHERE m.mailboxid = ? AND m.physmessageid = n.physmessageid AND m.physmessageid = p.id GROUP BY n.physmessageid HAVING COUNT(n.physmessageid) = 1";
258 return (List<PhysMessage>) getJdbcTemplate().query(sql,
259 new Object[] { new Long(mailboxID) }, new RowMapper() {
260 public Object mapRow(ResultSet rs, int rowNum)
261 throws SQLException {
262 PhysMessage pm = new PhysMessage();
263 pm.setPhysMessageID(rs.getLong("physmessageid"));
264 pm.setInternalDate(new Date(rs.getTimestamp("internaldate").getTime()));
265 return pm;
266 }
267
268 });
269 }
270
271 public void deleteMessages(long ownerID) {
272 String sql = "DELETE m.*, k.* FROM message AS m LEFT JOIN keyword AS k ON (m.messageid = k.messageid) WHERE mailboxid IN (SELECT mailboxid FROM mailbox WHERE ownerid = ?)";
273 getJdbcTemplate().update(sql, new Object[] { new Long(ownerID) });
274 }
275
276 public void deleteMessages(long ownerID, long mailboxID) {
277 String sql = "DELETE m.*, k.* FROM message AS m LEFT JOIN keyword AS k ON (m.messageid = k.messageid) WHERE m.mailboxid = ?";
278 getJdbcTemplate().update(sql, new Object[] { new Long(mailboxID) });
279 }
280
281 public int getMessageCount(long mailboxID) {
282 String sql = "SELECT COUNT(1) FROM message WHERE mailboxid = ?";
283 return queryForInt(sql, new Object[] { new Long(mailboxID) });
284 }
285
286 public int getRecentMessageCount(long mailboxID) {
287 String sql = "SELECT COUNT(1) FROM message WHERE mailboxid = ? AND recent = 'Y'";
288 return queryForInt(sql, new Object[] { new Long(mailboxID) });
289 }
290
291 public int getUnseenMessageCount(long mailboxID) {
292 String sql = "SELECT COUNT(1) FROM message WHERE mailboxid = ? AND seen = 'N'";
293 return getJdbcTemplate().queryForInt(sql,
294 new Object[] { new Long(mailboxID) });
295 }
296
297 public long getFirstUnseenMessageID(long mailboxID) {
298 String sql = "SELECT messageid FROM message WHERE mailboxid = ? AND seen = 'N' ORDER BY messageid LIMIT 1";
299 return queryForLong(sql, new Object[] { new Long(mailboxID) });
300 }
301
302 public List<Long> getGarbageMailboxList() {
303 String sql = "SELECT m.mailboxid FROM mailbox AS m LEFT JOIN user AS u ON m.ownerid = u.id WHERE u.id IS NULL";
304 return getJdbcTemplate().queryForList(sql, Long.class);
305 }
306
307 private static RowMapper mailboxRowMapper = new RowMapper() {
308 public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
309 Mailbox mailbox = new Mailbox();
310 mailbox.setMailboxID(rs.getLong("mailboxid"));
311 mailbox.setOwnerID(rs.getLong("ownerid"));
312 mailbox.setName(rs.getString("name"));
313 mailbox.setNoInferiors("Y".equals(rs.getString("noinferiors")));
314 mailbox.setNoSelect("Y".equals(rs.getString("noselect")));
315 mailbox.setReadOnly("Y".equals(rs.getString("readonly")));
316 mailbox.setNextUID(rs.getLong("nextuid"));
317 mailbox.setUidValidity(rs.getLong("uidvalidity"));
318 return mailbox;
319 }
320 };
321
322 }