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

Example 11 with DbConnection

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

the class DbMailItem method getUnreadMessages.

public static List<UnderlyingData> getUnreadMessages(MailItem relativeTo) throws ServiceException {
    if (relativeTo instanceof Tag) {
        return DbTag.getUnreadMessages((Tag) relativeTo);
    }
    Mailbox mbox = relativeTo.getMailbox();
    ArrayList<UnderlyingData> result = new ArrayList<UnderlyingData>();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        String relation;
        if (relativeTo instanceof VirtualConversation) {
            relation = "id = ?";
        } else if (relativeTo instanceof Conversation) {
            relation = "parent_id = ?";
        } else if (relativeTo instanceof Folder) {
            relation = "folder_id = ?";
        } else {
            relation = "id = ?";
        }
        stmt = conn.prepareStatement("SELECT " + DB_FIELDS + " FROM " + getMailItemTableName(relativeTo.getMailbox(), " mi") + " WHERE " + IN_THIS_MAILBOX_AND + "unread > 0 AND " + relation + " AND type NOT IN " + NON_SEARCHABLE_TYPES);
        if (relativeTo.getUnreadCount() > RESULTS_STREAMING_MIN_ROWS) {
            Db.getInstance().enableStreaming(stmt);
        }
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        if (relativeTo instanceof VirtualConversation) {
            stmt.setInt(pos++, ((VirtualConversation) relativeTo).getMessageId());
        } else {
            stmt.setInt(pos++, relativeTo.getId());
        }
        rs = stmt.executeQuery();
        while (rs.next()) {
            UnderlyingData data = constructItem(rs);
            if (Mailbox.isCachedType(MailItem.Type.of(data.type))) {
                throw ServiceException.INVALID_REQUEST("folders and tags must be retrieved from cache", null);
            }
            result.add(data);
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching unread messages for item " + relativeTo.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 12 with DbConnection

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

the class DbMailItem method delete.

public static void delete(Mailbox mbox, Collection<Integer> ids, boolean fromDumpster, boolean unsetDeletedFlag) throws ServiceException {
    // trim out any non-persisted items
    if (ids == null || ids.size() == 0) {
        return;
    }
    List<Integer> targets = new ArrayList<Integer>();
    for (int id : ids) {
        if (id > 0) {
            targets.add(id);
        }
    }
    if (targets.isEmpty()) {
        return;
    }
    DbConnection conn = mbox.getOperationConnection();
    for (int offset = 0; offset < targets.size(); offset += Db.getINClauseBatchSize()) {
        PreparedStatement stmt = null;
        try {
            int count = Math.min(Db.getINClauseBatchSize(), targets.size() - offset);
            if (!fromDumpster && mbox.dumpsterEnabled()) {
                copyToDumpster(conn, mbox, targets, offset, count, unsetDeletedFlag);
            }
            stmt = conn.prepareStatement("DELETE FROM " + getMailItemTableName(mbox, fromDumpster) + " WHERE " + IN_THIS_MAILBOX_AND + DbUtil.whereIn("id", count));
            int pos = 1;
            pos = setMailboxId(stmt, mbox, pos);
            for (int i = offset; i < offset + count; ++i) {
                stmt.setInt(pos++, targets.get(i));
            }
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw ServiceException.FAILURE("deleting " + ids.size() + " item(s): " + getIdListForLogging(ids), e);
        } finally {
            DbPool.closeStatement(stmt);
        }
    }
}

Example 13 with DbConnection

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

the class DbMailItem method getByHashes.

public static List<UnderlyingData> getByHashes(Mailbox mbox, List<String> hashes) throws ServiceException {
    if (ListUtil.isEmpty(hashes)) {
        return null;
    }
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement("SELECT " + DB_FIELDS + " FROM " + getMailItemTableName(mbox, "mi") + ", " + getConversationTableName(mbox, "oc") + " WHERE mi.id = oc.conv_id AND " + DbUtil.whereIn("oc.hash", hashes.size()) + (DebugConfig.disableMailboxGroups ? "" : " AND oc.mailbox_id = ? AND mi.mailbox_id = oc.mailbox_id"));
        int pos = 1;
        for (String hash : hashes) {
            stmt.setString(pos++, hash);
        }
        pos = setMailboxId(stmt, mbox, pos);
        rs = stmt.executeQuery();
        List<UnderlyingData> dlist = new ArrayList<UnderlyingData>(3);
        Set<Integer> convIds = Sets.newHashSetWithExpectedSize(3);
        while (rs.next()) {
            int id = rs.getInt(CI_ID);
            if (convIds.contains(id)) {
                continue;
            }
            UnderlyingData data = constructItem(rs);
            if (data.type == MailItem.Type.CONVERSATION.toByte()) {
                completeConversation(mbox, conn, data);
            }
            dlist.add(data);
            convIds.add(data.id);
        }
        return dlist.isEmpty() ? null : dlist;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching conversation for hash " + hashes, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 14 with DbConnection

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

the class DbMailItem method loadPop3Folder.

public static List<Pop3Message> loadPop3Folder(Set<Folder> folders, Date popSince) throws ServiceException {
    assert !folders.isEmpty() : folders;
    Mailbox mbox = Iterables.get(folders, 0).getMailbox();
    long popDate = popSince == null ? -1 : Math.max(popSince.getTime(), -1);
    List<Pop3Message> result = new ArrayList<Pop3Message>();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        String dateConstraint = popDate < 0 ? "" : " AND date > ?";
        stmt = conn.prepareStatement("SELECT mi.id, mi.size, mi.blob_digest FROM " + getMailItemTableName(mbox, " mi") + " WHERE " + IN_THIS_MAILBOX_AND + DbUtil.whereIn("folder_id", folders.size()) + " AND type = " + MailItem.Type.MESSAGE.toByte() + " AND " + Db.getInstance().bitAND("flags", String.valueOf(Flag.BITMASK_DELETED | Flag.BITMASK_POPPED)) + " = 0" + dateConstraint);
        if (getTotalFolderSize(folders) > RESULTS_STREAMING_MIN_ROWS) {
            //TODO: Because of POPPED flag, the folder size no longer represent the count.
            Db.getInstance().enableStreaming(stmt);
        }
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        for (Folder folder : folders) {
            stmt.setInt(pos++, folder.getId());
        }
        if (popDate >= 0) {
            stmt.setInt(pos++, (int) (popDate / 1000L));
        }
        rs = stmt.executeQuery();
        while (rs.next()) {
            result.add(new Pop3Message(rs.getInt(1), rs.getLong(2), rs.getString(3)));
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("loading POP3 folder data: " + folders, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 15 with DbConnection

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

the class DbMailItem method getByType.

public static List<UnderlyingData> getByType(Mailbox mbox, MailItem.Type type, SortBy sort) throws ServiceException {
    if (Mailbox.isCachedType(type)) {
        throw ServiceException.INVALID_REQUEST("folders and tags must be retrieved from cache", null);
    }
    ArrayList<UnderlyingData> result = new ArrayList<UnderlyingData>();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement("SELECT " + DB_FIELDS + " FROM " + getMailItemTableName(mbox, " mi") + " WHERE " + IN_THIS_MAILBOX_AND + typeIn(type) + DbSearch.orderBy(sort, false));
        if (type == MailItem.Type.MESSAGE) {
            Db.getInstance().enableStreaming(stmt);
        }
        setMailboxId(stmt, mbox, 1);
        rs = stmt.executeQuery();
        while (rs.next()) {
            result.add(constructItem(rs));
        }
        rs.close();
        rs = null;
        stmt.close();
        stmt = null;
        if (type == MailItem.Type.CONVERSATION) {
            completeConversations(mbox, conn, result);
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching items of type " + type, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}