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

Example 86 with DbConnection

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

the class DbTag method alterTag.

public static void alterTag(Tag tag, List<Integer> itemIds, boolean add) throws ServiceException {
    if (itemIds == null || itemIds.isEmpty()) {
        return;
    }
    Mailbox mbox = tag.getMailbox();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        boolean isFlag = tag instanceof Flag;
        boolean altersModseq = !isFlag || !((Flag) tag).isSystemFlag();
        String primaryUpdate, sanityCheckAnd;
        if (isFlag) {
            primaryUpdate = "flags = flags" + (add ? " + ?" : " - ?");
            sanityCheckAnd = Db.getInstance().bitAND("flags", "?") + (add ? " = 0" : " <> 0") + " AND ";
        } else {
            if (add) {
                primaryUpdate = "tag_names = CASE WHEN tag_names IS NULL THEN ? ELSE " + Db.getInstance().concat("tag_names", "?") + " END";
                sanityCheckAnd = "(tag_names IS NULL OR tag_names NOT LIKE ?) AND ";
            } else {
                primaryUpdate = "tag_names = CASE tag_names WHEN ? THEN NULL ELSE REPLACE(tag_names, ?, '\0') END";
                sanityCheckAnd = "";
            }
        }
        String updateChangeID = altersModseq ? ", mod_metadata = ?, change_date = ?" : "";
        for (int i = 0; i < itemIds.size(); i += Db.getINClauseBatchSize()) {
            int count = Math.min(Db.getINClauseBatchSize(), itemIds.size() - i);
            stmt = conn.prepareStatement("UPDATE " + DbMailItem.getMailItemTableName(mbox) + " SET " + primaryUpdate + updateChangeID + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + sanityCheckAnd + DbUtil.whereIn("id", count));
            int pos = 1;
            // primary update
            if (isFlag) {
                stmt.setLong(pos++, ((Flag) tag).toBitmask());
            } else {
                stmt.setString(pos++, delimitTagName(tag.getName()));
                stmt.setString(pos++, delimitTagName(tag.getName()).substring(add ? 1 : 0));
            }
            // change ID update
            if (altersModseq) {
                stmt.setInt(pos++, mbox.getOperationChangeID());
                stmt.setInt(pos++, mbox.getOperationTimestamp());
            }
            pos = DbMailItem.setMailboxId(stmt, mbox, pos);
            // sanity check
            if (isFlag) {
                stmt.setLong(pos++, ((Flag) tag).toBitmask());
            } else if (add) {
                stmt.setString(pos++, tagLIKEPattern(tag.getName()));
            }
            // item IDs
            for (int index = i; index < i + count; index++) {
                stmt.setInt(pos++, itemIds.get(index));
            }
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
            if (add) {
                addTaggedItemEntries(mbox, tag.getId(), itemIds.subList(i, i + count));
            } else {
                removeTaggedItemEntries(mbox, tag.getId(), itemIds.subList(i, i + count));
            }
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("updating tag data for " + itemIds.size() + " items: " + DbMailItem.getIdListForLogging(itemIds), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 87 with DbConnection

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

the class DbTag method removeTaggedItemEntries.

static void removeTaggedItemEntries(Mailbox mbox, int tagId, List<Integer> itemIds) throws ServiceException {
    if (tagId < 0 && !Mailbox.REIFIED_FLAGS.contains(tagId))
        return;
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("DELETE FROM " + getTaggedItemTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "tag_id = ? AND " + DbUtil.whereIn("item_id", itemIds.size()));
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, tagId);
        for (int itemId : itemIds) {
            stmt.setInt(pos++, itemId);
        }
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("removing TAGGED_ITEM entries for tag: " + tagId + ", items: " + DbMailItem.getIdListForLogging(itemIds), e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 88 with DbConnection

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

the class DbTag method getLeafNodes.

public static PendingDelete getLeafNodes(Mailbox mbox, Tag tag, int before, Integer maxItems) throws ServiceException {
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        String orderByLimit = "";
        if (maxItems != null && Db.supports(Db.Capability.LIMIT_CLAUSE)) {
            orderByLimit = " ORDER BY date " + Db.getInstance().limit(maxItems);
        }
        String mailboxesMatchAnd = DebugConfig.disableMailboxGroups ? "" : "mi.mailbox_id = ti.mailbox_id AND ";
        stmt = conn.prepareStatement("SELECT " + DbMailItem.LEAF_NODE_FIELDS + " FROM " + DbMailItem.getMailItemTableName(mbox, "mi") + " INNER JOIN " + getTaggedItemTableName(mbox, "ti") + " ON " + mailboxesMatchAnd + "ti.item_id = mi.id" + " WHERE " + inThisMailboxAnd("ti") + "ti.tag_id = ? AND date < ? AND type NOT IN " + DbMailItem.NON_SEARCHABLE_TYPES + orderByLimit);
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, tag.getId());
        stmt.setInt(pos++, before);
        PendingDelete info = DbMailItem.accumulateDeletionInfo(mbox, stmt);
        stmt = null;
        return info;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching list of items for purge", e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 89 with DbConnection

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

the class DbTag method deleteTagRow.

public static void deleteTagRow(Mailbox mbox, int tagId) throws ServiceException {
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        // delete the TAG row, which automatically cascades into TAGGED_ITEM
        stmt = conn.prepareStatement("DELETE FROM " + getTagTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "id = ?");
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, tagId);
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("deleting tag row for tagId " + tagId, e);
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 90 with DbConnection

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

the class DbPop3Message method getMatchingUids.

/**
     * Returns the set of persisted UID's that are also in the <code>uids</code>
     * collection.
     */
public static Set<String> getMatchingUids(Mailbox mbox, DataSource ds, Collection<String> uids) throws ServiceException {
    ZimbraLog.mailbox.debug("%s: looking for uids that match a set of size %d", ds, uids.size());
    List<List<String>> splitIds = ListUtil.split(uids, Db.getINClauseBatchSize());
    Set<String> matchingUids = new HashSet<String>();
    DbConnection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = DbPool.getConnection(mbox);
        for (List<String> curIds : splitIds) {
            stmt = conn.prepareStatement("SELECT uid FROM " + getTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "data_source_id = ?" + " AND " + DbUtil.whereIn("uid", curIds.size()));
            int pos = 1;
            pos = DbMailItem.setMailboxId(stmt, mbox, pos);
            stmt.setString(pos++, ds.getId());
            for (String uid : curIds) stmt.setString(pos++, uid);
            rs = stmt.executeQuery();
            while (rs.next()) matchingUids.add(rs.getString(1));
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to get UID's", e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
    ZimbraLog.mailbox.debug("Found %d matching UID's", matchingUids.size());
    return matchingUids;
}