Examples with PurgeableConv com.zimbra.cs.purge.DataSourcePurge.PurgeableConv used on opensource projects

Example 1 with PurgeableConv

use of com.zimbra.cs.purge.DataSourcePurge.PurgeableConv in project zm-mailbox by Zimbra.

the class DbDataSource method getOldestConversationsUpToSize.

public static List<PurgeableConv> getOldestConversationsUpToSize(List<DataSource> dataSources, long targetSize, long startDate) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(dataSources.get(0));
    DbConnection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    long totalSize = 0;
    try {
        conn = DbPool.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DISTINCT COALESCE(msgs.parent_id, msgs.msg_id) AS item_id," + " msgs.data_source_id AS data_source," + " COALESCE(convs.latest_date, msgs.mdate) AS newest_msg_date," + " COALESCE(convs.num_msgs, 1) AS num_msgs," + " COALESCE(convs.conv_size, msgs.msize) AS size");
        sb.append(" FROM ");
        sb.append("(SELECT di.data_source_id AS data_source_id, " + " mi.id AS msg_id," + " mi.parent_id AS parent_id," + " mi.size AS msize," + " mi.date AS mdate FROM ");
        sb.append(getTableName(mbox)).append(" di ");
        sb.append(" INNER JOIN ");
        sb.append(DbMailItem.getMailItemTableName(mbox)).append(" mi ");
        sb.append(" ON di.mailbox_id = mi.mailbox_id AND di.item_id = mi.id");
        sb.append(" WHERE di.mailbox_id = ? AND mi.type = ? AND ");
        sb.append(DbUtil.whereIn("di.data_source_id", dataSources.size()));
        sb.append(") AS msgs");
        sb.append(" LEFT JOIN ");
        sb.append("(SELECT parent_id, max(date) AS latest_date, count(date) AS num_msgs, sum(size) AS conv_size");
        sb.append(" FROM ").append(DbMailItem.getMailItemTableName(mbox));
        sb.append(" WHERE mailbox_id = ? AND type = ? GROUP BY parent_id) AS convs");
        sb.append(" ON msgs.parent_id = convs.parent_id");
        if (startDate > 0L) {
            sb.append(" WHERE COALESCE(convs.latest_date, msgs.mdate) > ?");
        }
        sb.append(" ORDER BY COALESCE(convs.latest_date, msgs.mdate) ASC");
        stmt = conn.prepareStatement(sb.toString());
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setByte(pos++, MailItem.Type.MESSAGE.toByte());
        for (DataSource ds : dataSources) {
            stmt.setString(pos++, ds.getId());
        }
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setByte(pos++, MailItem.Type.MESSAGE.toByte());
        if (startDate > 0L) {
            stmt.setLong(pos++, startDate);
        }
        rs = stmt.executeQuery();
        List<PurgeableConv> convs = new LinkedList<PurgeableConv>();
        while (rs.next() && totalSize < targetSize) {
            long convSize = rs.getLong("size");
            int itemId = rs.getInt("item_id");
            int numMsgs = rs.getInt("num_msgs");
            long convDate = rs.getLong("newest_msg_date");
            String dataSourceId = rs.getString("data_source");
            convs.add(new PurgeableConv(itemId, convSize, convDate, dataSourceId, numMsgs));
            totalSize += convSize;
        }
        rs.close();
        stmt.close();
        return convs;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to get oldest conversations for data sources", e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}