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

Example 46 with DbConnection

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

the class DbMailItem method listByFolder.

public static List<Integer> listByFolder(Folder folder, MailItem.Type type, boolean descending) throws ServiceException {
    Mailbox mbox = folder.getMailbox();
    boolean allTypes = type == MailItem.Type.UNKNOWN;
    List<Integer> result = new ArrayList<Integer>();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        String typeConstraint = allTypes ? "" : "type = ? AND ";
        stmt = conn.prepareStatement("SELECT id FROM " + getMailItemTableName(folder) + " WHERE " + IN_THIS_MAILBOX_AND + typeConstraint + "folder_id = ?" + " ORDER BY date" + (descending ? " DESC" : ""));
        if (type == MailItem.Type.MESSAGE && folder.getSize() > RESULTS_STREAMING_MIN_ROWS) {
            Db.getInstance().enableStreaming(stmt);
        }
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        if (!allTypes) {
            stmt.setByte(pos++, type.toByte());
        }
        stmt.setInt(pos++, folder.getId());
        rs = stmt.executeQuery();
        while (rs.next()) {
            result.add(rs.getInt(1));
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching item list for folder " + folder.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 47 with DbConnection

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

the class DbMailItem method accumulateLeafRevisions.

static void accumulateLeafRevisions(PendingDelete info, Mailbox mbox, List<Integer> versioned) throws ServiceException {
    if (versioned == null || versioned.size() == 0) {
        return;
    }
    boolean dumpsterEnabled = mbox.dumpsterEnabled();
    boolean useDumpsterForSpam = mbox.useDumpsterForSpam();
    DbConnection conn = mbox.getOperationConnection();
    StoreManager sm = StoreManager.getInstance();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement("SELECT mi.id, mi.folder_id, rev.size, rev.mod_content, rev.locator, rev.blob_digest " + " FROM " + getMailItemTableName(mbox, "mi") + ", " + getRevisionTableName(mbox, "rev") + " WHERE mi.id = rev.item_id AND " + DbUtil.whereIn("mi.id", versioned.size()) + (DebugConfig.disableMailboxGroups ? "" : " AND mi.mailbox_id = ? AND mi.mailbox_id = rev.mailbox_id"));
        int pos = 1;
        for (int vid : versioned) {
            stmt.setInt(pos++, vid);
        }
        pos = setMailboxId(stmt, mbox, pos);
        rs = stmt.executeQuery();
        while (rs.next()) {
            Integer folderId = rs.getInt(2);
            LocationCount count = info.folderCounts.get(folderId);
            if (count == null) {
                info.folderCounts.put(folderId, new LocationCount(0, 0, rs.getLong(3)));
            } else {
                count.increment(0, 0, rs.getLong(3));
            }
            int fid = folderId != null ? folderId.intValue() : -1;
            if (!dumpsterEnabled || fid == Mailbox.ID_FOLDER_DRAFTS || (fid == Mailbox.ID_FOLDER_SPAM && !useDumpsterForSpam)) {
                String blobDigest = rs.getString(6);
                if (blobDigest != null) {
                    info.blobDigests.add(blobDigest);
                    try {
                        MailboxBlob mblob = sm.getMailboxBlob(mbox, rs.getInt(1), rs.getInt(4), rs.getString(5), false);
                        if (mblob == null) {
                            ZimbraLog.mailbox.error("missing blob for id: %d, change: %s", rs.getInt(1), rs.getInt(4));
                        } else {
                            info.blobs.add(mblob);
                        }
                    } catch (Exception e1) {
                    }
                }
            }
            long revisionSize = rs.getLong(3);
            info.size += revisionSize;
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("getting version deletion info for items: " + versioned, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 48 with DbConnection

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

the class DbMailItem method getDigestsForItems.

public static Map<String, Integer> getDigestsForItems(Folder folder, MailItem.Type type) throws ServiceException {
    if (Mailbox.isCachedType(type)) {
        throw ServiceException.INVALID_REQUEST("folders and tags must be retrieved from cache", null);
    }
    Map<String, Integer> digestMap = Maps.newHashMap();
    if (null == folder) {
        return digestMap;
    }
    Mailbox mbox = folder.getMailbox();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement(String.format("SELECT mi.id, mi.blob_digest FROM %s WHERE %s folder_id = ? AND %s", getMailItemTableName(mbox, "mi"), IN_THIS_MAILBOX_AND, typeIn(type)));
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, folder.getId());
        rs = stmt.executeQuery();
        while (rs.next()) {
            Integer id = rs.getInt(1);
            String digest = rs.getString(2);
            digestMap.put(digest, id);
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE(String.format("Getting digests for items from folder id=%s name='%s'", folder.getId(), folder.getName()), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
    return digestMap;
}

Example 49 with DbConnection

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

the class DbMailItem method assignUuids.

/**
     * Populate the uuid column in mail_item and mail_item_dumpster tables with unique UUID values.
     * Only the rows for item types that support UUID will be updated.  This method is not atomic,
     * as the updates are committed in batches.  It is safe to call this method repeatedly until
     * it succeeds.
     * @param mbox
     * @param reassignExisting if false, only update rows that have uuid=null; if true, clear uuid from
     *                      all rows first, then populate with new values
     */
public static void assignUuids(Mailbox mbox, boolean reassignExisting) throws ServiceException {
    // uuid is needed for Octopus types only: folder, mountpoint, document, comment, link
    // (also including search folder and deprecated wiki because they are sub-types of folder and document)
    String types = "type IN (1, 2, 8, 13, 14, 17, 18)";
    // mail_item and mail_item_dumpster tables
    String[] tables;
    if (Db.supports(Db.Capability.DUMPSTER_TABLES)) {
        tables = new String[] { DbMailItem.getMailItemTableName(mbox, false), DbMailItem.getMailItemTableName(mbox, true) };
    } else {
        tables = new String[] { DbMailItem.getMailItemTableName(mbox, false) };
    }
    DbConnection conn = null;
    try {
        conn = DbPool.getConnection(mbox);
        if (reassignExisting) {
            for (String table : tables) {
                PreparedStatement clear = null;
                try {
                    clear = conn.prepareStatement("UPDATE " + table + " SET uuid = NULL" + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + " uuid IS NOT NULL");
                    DbMailItem.setMailboxId(clear, mbox, 1);
                    clear.execute();
                    conn.commit();
                } catch (SQLException e) {
                    throw ServiceException.FAILURE("error while clearing existing uuid values", e);
                } finally {
                    conn.closeQuietly(clear);
                }
            }
        }
        for (String table : tables) {
            boolean done = false;
            int batchSize = 1000;
            while (!done) {
                // Fetch a batch of items that don't have UUID.
                PreparedStatement stmt = null;
                ResultSet rs = null;
                List<Integer> ids = new ArrayList<Integer>(batchSize);
                try {
                    stmt = conn.prepareStatement("SELECT id FROM " + table + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + types + " AND uuid IS NULL LIMIT ?");
                    int pos = DbMailItem.setMailboxId(stmt, mbox, 1);
                    stmt.setInt(pos, batchSize);
                    rs = stmt.executeQuery();
                    while (rs.next()) {
                        ids.add(rs.getInt(1));
                    }
                } catch (SQLException e) {
                    throw ServiceException.FAILURE("error while fetching rows with missing uuid", e);
                } finally {
                    conn.closeQuietly(rs);
                    conn.closeQuietly(stmt);
                }
                // Set UUID one item at a time.
                for (int id : ids) {
                    PreparedStatement update = null;
                    try {
                        update = conn.prepareStatement("UPDATE " + table + " SET uuid = ?" + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + " id = ?");
                        int pos = 1;
                        update.setString(pos++, UUIDUtil.generateUUID());
                        pos = DbMailItem.setMailboxId(update, mbox, pos);
                        update.setInt(pos, id);
                        update.execute();
                    } catch (SQLException e) {
                        throw ServiceException.FAILURE("error while setting uuid for item " + id, e);
                    } finally {
                        conn.closeQuietly(update);
                    }
                }
                // Commit the batch.  We don't need overall atomicity for this upgrade.
                conn.commit();
                done = ids.isEmpty();
            }
        }
    } catch (ServiceException e) {
        DbPool.quietRollback(conn);
        throw e;
    } finally {
        DbPool.quietClose(conn);
    }
}

Example 50 with DbConnection

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

the class DbMailItem method getCalendarItems.

public static List<UnderlyingData> getCalendarItems(Mailbox mbox, List<String> uids) throws ServiceException {
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    List<UnderlyingData> result = new ArrayList<UnderlyingData>();
    try {
        for (int i = 0; i < uids.size(); i += Db.getINClauseBatchSize()) {
            int count = Math.min(Db.getINClauseBatchSize(), uids.size() - i);
            stmt = conn.prepareStatement("SELECT " + DB_FIELDS + " FROM " + getCalendarItemTableName(mbox, "ci") + ", " + getMailItemTableName(mbox, "mi") + " WHERE mi.id = ci.item_id AND mi.type IN " + CALENDAR_TYPES + (DebugConfig.disableMailboxGroups ? "" : " AND ci.mailbox_id = ? AND mi.mailbox_id = ci.mailbox_id") + " AND " + DbUtil.whereIn("ci.uid", count));
            int pos = 1;
            pos = setMailboxId(stmt, mbox, pos);
            for (int index = i; index < i + count; index++) {
                stmt.setString(pos++, uids.get(index));
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                result.add(constructItem(rs));
            }
            stmt.close();
            stmt = null;
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching calendar items for mailbox " + mbox.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}