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

Example 56 with DbConnection

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

the class DbMailItem method getBlobDigest.

/**
     * Returns the blob digest for the item with the given id, or <tt>null</tt>
     * if either the id doesn't exist in the table or there is no associated blob.
     */
public static String getBlobDigest(Mailbox mbox, int itemId) throws ServiceException {
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement("SELECT blob_digest " + " FROM " + getMailItemTableName(mbox) + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?");
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, itemId);
        rs = stmt.executeQuery();
        return rs.next() ? rs.getString(1) : null;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("unable to get blob digest for id " + itemId, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 57 with DbConnection

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

the class DbMailItem method markDeletionTargets.

/**
     * Updates all conversations affected by a folder deletion.  For all conversations
     * that have messages in the given folder, updates their message count and nulls out
     * metadata so that the sender list is recalculated the next time the conversation
     * is instantiated.
     *
     * @param folder the folder that is being deleted
     * @return the ids of any conversation that were purged as a result of this operation
     */
public static List<Integer> markDeletionTargets(Folder folder, Set<Integer> candidates) throws ServiceException {
    Mailbox mbox = folder.getMailbox();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        if (Db.supports(Db.Capability.MULTITABLE_UPDATE)) {
            stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(folder) + ", " + "(SELECT parent_id pid, COUNT(*) count FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + "folder_id = ? AND parent_id IS NOT NULL GROUP BY parent_id) AS x" + " SET size = size - count, metadata = NULL, mod_metadata = ?, change_date = ?" + " WHERE " + IN_THIS_MAILBOX_AND + "id = pid AND type = " + MailItem.Type.CONVERSATION.toByte());
            int pos = 1;
            pos = setMailboxId(stmt, mbox, pos);
            stmt.setInt(pos++, folder.getId());
            stmt.setInt(pos++, mbox.getOperationChangeID());
            stmt.setInt(pos++, mbox.getOperationTimestamp());
            pos = setMailboxId(stmt, mbox, pos);
            stmt.executeUpdate();
            stmt.close();
        } else {
            stmt = conn.prepareStatement("SELECT parent_id, COUNT(*) FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + "folder_id = ? AND parent_id IS NOT NULL" + " GROUP BY parent_id");
            int pos = 1;
            pos = setMailboxId(stmt, mbox, pos);
            stmt.setInt(pos++, folder.getId());
            rs = stmt.executeQuery();
            Map<Integer, List<Integer>> counts = new HashMap<Integer, List<Integer>>();
            while (rs.next()) {
                int convId = rs.getInt(1), count = rs.getInt(2);
                List<Integer> targets = counts.get(count);
                if (targets == null) {
                    counts.put(count, targets = new ArrayList<Integer>());
                }
                targets.add(convId);
            }
            rs.close();
            stmt.close();
            for (Map.Entry<Integer, List<Integer>> update : counts.entrySet()) {
                List<Integer> convIDs = update.getValue();
                for (int i = 0; i < convIDs.size(); i += Db.getINClauseBatchSize()) {
                    int count = Math.min(Db.getINClauseBatchSize(), convIDs.size() - i);
                    stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(folder) + " SET size = size - ?, metadata = NULL, mod_metadata = ?, change_date = ?" + " WHERE " + IN_THIS_MAILBOX_AND + DbUtil.whereIn("id", count) + "  AND type = " + MailItem.Type.CONVERSATION.toByte());
                    pos = 1;
                    stmt.setInt(pos++, update.getKey());
                    stmt.setInt(pos++, mbox.getOperationChangeID());
                    stmt.setInt(pos++, mbox.getOperationTimestamp());
                    pos = setMailboxId(stmt, mbox, pos);
                    for (int index = i; index < i + count; index++) {
                        stmt.setInt(pos++, convIDs.get(index));
                    }
                    stmt.executeUpdate();
                    stmt.close();
                }
            }
        }
        return getPurgedConversations(mbox, candidates);
    } catch (SQLException e) {
        throw ServiceException.FAILURE("marking deletions for conversations crossing folder " + folder.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 58 with DbConnection

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

the class DbMailItem method copy.

public static String copy(MailItem item, int id, String uuid, Folder folder, int indexId, int parentId, String locator, String metadata, boolean fromDumpster) throws ServiceException {
    Mailbox mbox = item.getMailbox();
    String prevFolders = null;
    if (id <= 0 || folder == null || parentId == 0) {
        throw ServiceException.FAILURE("invalid data for DB item copy", null);
    }
    checkNamingConstraint(mbox, folder.getId(), item.getName(), id);
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        String srcTable = getMailItemTableName(mbox, fromDumpster);
        String destTable = getMailItemTableName(mbox);
        String mailbox_id = DebugConfig.disableMailboxGroups ? "" : "mailbox_id, ";
        stmt = conn.prepareStatement("INSERT INTO " + destTable + "(" + mailbox_id + " id, type, parent_id, folder_id, prev_folders, index_id, imap_id, date, size, locator, blob_digest," + " unread, flags, tag_names, sender, subject, name, metadata, mod_metadata, change_date, mod_content, uuid) " + "SELECT " + MAILBOX_ID_VALUE + " ?, type, ?, ?, ?, ?, ?, date, size, ?, blob_digest, unread," + " flags, tag_names, sender, subject, name, ?, ?, ?, ?, ? FROM " + srcTable + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?");
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        // ID
        stmt.setInt(pos++, id);
        if (parentId <= 0) {
            // PARENT_ID null for messages in virtual convs
            stmt.setNull(pos++, Types.INTEGER);
        } else {
            //   or, PARENT_ID specified by caller
            stmt.setInt(pos++, parentId);
        }
        // FOLDER_ID
        stmt.setInt(pos++, folder.getId());
        int modseq = mbox.getOperationChangeID();
        prevFolders = findPrevFolders(item, modseq);
        stmt.setString(pos++, prevFolders);
        if (indexId == MailItem.IndexStatus.NO.id()) {
            stmt.setNull(pos++, Types.INTEGER);
        } else {
            stmt.setInt(pos++, indexId);
        }
        // IMAP_ID is initially the same as ID
        stmt.setInt(pos++, id);
        stmt.setString(pos++, locator);
        // METADATA
        stmt.setString(pos++, checkMetadataLength(metadata));
        // MOD_METADATA
        stmt.setInt(pos++, modseq);
        // CHANGE_DATE
        stmt.setInt(pos++, mbox.getOperationTimestamp());
        // MOD_CONTENT
        stmt.setInt(pos++, mbox.getOperationChangeID());
        // UUID
        stmt.setString(pos++, uuid);
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, item.getId());
        int num = stmt.executeUpdate();
        if (num != 1) {
            throw ServiceException.FAILURE("failed to create object", null);
        }
        DbTag.storeTagReferences(mbox, id, item.getType(), item.getInternalFlagBitmask(), item.isUnread());
        DbTag.storeTagReferences(mbox, id, item.getType(), item.getTags());
    } catch (SQLException e) {
        // catch item_id uniqueness constraint violation and return failure
        if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) {
            throw MailServiceException.ALREADY_EXISTS(id, e);
        } else {
            throw ServiceException.FAILURE("copying " + item.getType() + ": " + item.getId(), e);
        }
    } finally {
        DbPool.closeStatement(stmt);
    }
    return prevFolders;
}

Example 59 with DbConnection

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

the class DbMailbox method clearMailboxContactCount.

public static void clearMailboxContactCount(Mailbox mbox) throws ServiceException {
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("UPDATE " + qualifyZimbraTableName(mbox, TABLE_MAILBOX) + " SET contact_count = NULL WHERE id = ?");
        stmt.setInt(1, mbox.getId());
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("clearing contact count for mailbox " + mbox.getId(), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 60 with DbConnection

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

the class DbMailItem method saveSubject.

// need to kill the Note class sooner rather than later
public static void saveSubject(Note note) throws ServiceException {
    Mailbox mbox = note.getMailbox();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("UPDATE " + getMailItemTableName(note) + " SET date = ?, size = ?, subject = ?, mod_metadata = ?, change_date = ?, mod_content = ?" + " WHERE " + IN_THIS_MAILBOX_AND + "id = ?");
        int pos = 1;
        stmt.setInt(pos++, (int) (note.getDate() / 1000));
        stmt.setLong(pos++, note.getSize());
        stmt.setString(pos++, note.getSubject());
        stmt.setInt(pos++, mbox.getOperationChangeID());
        stmt.setInt(pos++, mbox.getOperationTimestamp());
        stmt.setInt(pos++, mbox.getOperationChangeID());
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, note.getId());
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("writing subject for mailbox " + note.getMailboxId() + ", note " + note.getId(), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}