Examples with DbConnection com.zimbra.cs.db.DbPool.DbConnection used on opensource projects

Example 41 with DbConnection

use of com.zimbra.cs.db.DbPool.DbConnection in project zm-mailbox by Zimbra.

the class DbMailItem method getItemsChangedSinceDate.

public static Pair<List<Map<String, String>>, TypedIdList> getItemsChangedSinceDate(Mailbox mbox, MailItem.Type type, int changeDate, Set<Integer> visible) throws ServiceException {
    int lastDeleteSync = -1;
    if (Mailbox.isCachedType(type)) {
        throw ServiceException.INVALID_REQUEST("folders and tags must be retrieved from cache", null);
    }
    List<Map<String, String>> idList = new ArrayList<Map<String, String>>();
    TypedIdList missed = new TypedIdList();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        String typeConstraint = type == MailItem.Type.UNKNOWN ? "type NOT IN " + NON_SYNCABLE_TYPES : typeIn(type);
        String dateConstraint = changeDate > 0 ? "change_date > ? AND " : "";
        stmt = conn.prepareStatement("SELECT id, type, parent_id, folder_id, prev_folders, date, mod_metadata, change_date, uuid" + " FROM " + getMailItemTableName(mbox) + " WHERE " + IN_THIS_MAILBOX_AND + dateConstraint + typeConstraint + " ORDER BY mod_metadata, id");
        if (type == MailItem.Type.MESSAGE) {
            Db.getInstance().enableStreaming(stmt);
        }
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        if (changeDate > 0) {
            stmt.setInt(pos++, changeDate);
        }
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, String> resultData = new HashMap<String, String>();
                resultData.put("id", Integer.toString(rs.getInt(1)));
                resultData.put("type", Integer.toString(rs.getInt(2)));
                resultData.put("parent_id", Integer.toString(rs.getInt(3)));
                resultData.put("folder_id", Integer.toString(rs.getInt(4)));
                resultData.put("prev_folders", rs.getString(5));
                long temp = rs.getInt(6) * 1000L;
                resultData.put("date", Long.toString(temp));
                resultData.put("mod_metadata", Integer.toString(rs.getInt(7)));
                temp = rs.getInt(8) * 1000L;
                resultData.put("change_date", Long.toString(temp));
                if (visible == null || visible.contains(rs.getInt(3))) {
                    idList.add(resultData);
                } else {
                    int modSeq = rs.getInt(7);
                    if (modSeq > lastDeleteSync) {
                        missed.add(MailItem.Type.of(rs.getByte(2)), rs.getInt(1), rs.getString(9), modSeq, rs.getString(5));
                    }
                }
            }
        } finally {
            DbPool.closeResults(rs);
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Getting items modified since " + changeDate, e);
    } finally {
        DbPool.closeStatement(stmt);
    }
    return new Pair<List<Map<String, String>>, TypedIdList>(idList, missed);
}

Example 42 with DbConnection

use of com.zimbra.cs.db.DbPool.DbConnection in project zm-mailbox by Zimbra.

the class DbMailItem method getCalendarItemMetadata.

/**
     * @param start start time of range, in milliseconds. {@code -1} means to leave the start time unconstrained.
     * @param end end time of range, in milliseconds. {@code -1} means to leave the end time unconstrained.
     */
public static List<CalendarItem.CalendarMetadata> getCalendarItemMetadata(Folder folder, long start, long end) throws ServiceException {
    Mailbox mbox = folder.getMailbox();
    ArrayList<CalendarItem.CalendarMetadata> result = new ArrayList<CalendarItem.CalendarMetadata>();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        // Note - Negative times are valid.  However, treat -1 as meaning "unconstrained"
        // Want appointments that end after the start time
        String startConstraint = (start != -1) ? " AND ci.end_time > ?" : "";
        // Want appointments that start before the end time
        String endConstraint = (end != -1) ? " AND ci.start_time < ?" : "";
        String folderConstraint = " AND mi.folder_id = ?";
        stmt = conn.prepareStatement("SELECT mi.mailbox_id, mi.id, ci.uid, mi.mod_metadata, mi.mod_content, ci.start_time, ci.end_time" + " FROM " + getMailItemTableName(mbox, "mi") + ", " + getCalendarItemTableName(mbox, "ci") + " WHERE mi.mailbox_id = ci.mailbox_id AND mi.id = ci.item_id" + (DebugConfig.disableMailboxGroups ? "" : " AND mi.mailbox_id = ? ") + startConstraint + endConstraint + folderConstraint);
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        if (!startConstraint.isEmpty()) {
            stmt.setTimestamp(pos++, new Timestamp(start));
        }
        if (!endConstraint.isEmpty()) {
            stmt.setTimestamp(pos++, new Timestamp(end));
        }
        stmt.setInt(pos++, folder.getId());
        rs = stmt.executeQuery();
        while (rs.next()) {
            result.add(new CalendarItem.CalendarMetadata(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getTimestamp(6).getTime(), rs.getTimestamp(7).getTime()));
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching CalendarItem Metadata for mbox " + mbox.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
    return result;
}

Example 43 with DbConnection

use of com.zimbra.cs.db.DbPool.DbConnection in project zm-mailbox by Zimbra.

the class DbMailItem method changeType.

public static void changeType(MailItem item, byte type) throws ServiceException {
    Mailbox mbox = item.getMailbox();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(item) + " SET type = ? WHERE " + IN_THIS_MAILBOX_AND + "id = ?");
        int pos = 1;
        stmt.setInt(pos++, type);
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, item.getId());
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("writing new type for item " + item.getId(), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 44 with DbConnection

use of com.zimbra.cs.db.DbPool.DbConnection in project zm-mailbox by Zimbra.

the class DbMailItem method visitAllBlobDigests.

public static void visitAllBlobDigests(Mailbox mbox, Callback<String> callback) throws ServiceException {
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("SELECT blob_digest FROM " + getMailItemTableName(mbox) + " WHERE " + IN_THIS_MAILBOX_AND + "blob_digest IS NOT NULL");
        visitAllBlobDigests(stmt, mbox, callback);
        stmt = conn.prepareStatement("SELECT blob_digest FROM " + getMailItemTableName(mbox, true) + " WHERE " + IN_THIS_MAILBOX_AND + "blob_digest IS NOT NULL");
        visitAllBlobDigests(stmt, mbox, callback);
        stmt = conn.prepareStatement("SELECT blob_digest FROM " + getRevisionTableName(mbox) + " WHERE " + IN_THIS_MAILBOX_AND + "blob_digest IS NOT NULL");
        visitAllBlobDigests(stmt, mbox, callback);
        stmt = conn.prepareStatement("SELECT blob_digest FROM " + getRevisionTableName(mbox, true) + " WHERE " + IN_THIS_MAILBOX_AND + "blob_digest IS NOT NULL");
        visitAllBlobDigests(stmt, mbox, callback);
    } catch (SQLException e) {
        throw ServiceException.FAILURE("visiting blob digests list for mailbox " + mbox.getId(), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 45 with DbConnection

use of com.zimbra.cs.db.DbPool.DbConnection in project zm-mailbox by Zimbra.

the class DbMailItem method alterUnread.

public static void alterUnread(Mailbox mbox, List<Integer> itemIDs, boolean unread) throws ServiceException {
    if (itemIDs == null || itemIDs.isEmpty()) {
        return;
    }
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        for (int i = 0; i < itemIDs.size(); i += Db.getINClauseBatchSize()) {
            int count = Math.min(Db.getINClauseBatchSize(), itemIDs.size() - i);
            stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(mbox) + " SET unread = ?, mod_metadata = ?, change_date = ?" + " WHERE " + IN_THIS_MAILBOX_AND + "unread = ?" + "  AND " + DbUtil.whereIn("id", count) + "  AND " + typeIn(MailItem.Type.MESSAGE));
            int pos = 1;
            stmt.setInt(pos++, unread ? 1 : 0);
            stmt.setInt(pos++, mbox.getOperationChangeID());
            stmt.setInt(pos++, mbox.getOperationTimestamp());
            pos = setMailboxId(stmt, mbox, pos);
            stmt.setInt(pos++, unread ? 0 : 1);
            for (int index = i; index < i + count; index++) {
                stmt.setInt(pos++, itemIDs.get(index));
            }
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
            if (unread) {
                DbTag.addTaggedItemEntries(mbox, Flag.ID_UNREAD, itemIDs.subList(i, i + count));
            } else {
                DbTag.removeTaggedItemEntries(mbox, Flag.ID_UNREAD, itemIDs.subList(i, i + count));
            }
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("updating unread state for " + itemIDs.size() + " items: " + getIdListForLogging(itemIDs), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}