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

Example 36 with DbConnection

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

the class DbDataSource method deleteAllMappingsInFolder.

public static Collection<DataSourceItem> deleteAllMappingsInFolder(DataSource ds, int folderId, boolean isBatch) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(ds);
    ArrayList<DataSourceItem> items = new ArrayList<DataSourceItem>();
    ZimbraLog.datasource.debug("Deleting all mappings for dataSource %s in folder %d", ds.getName(), folderId);
    mbox.lock.lock();
    try {
        DbConnection conn = null;
        PreparedStatement stmt = null;
        try {
            if (isBatch) {
                conn = mbox.getOperationConnection();
            } else {
                conn = DbPool.getConnection(mbox);
            }
            String dataSourceTable = getTableName(mbox);
            String IN_THIS_MAILBOX_AND = DebugConfig.disableMailboxGroups ? "" : dataSourceTable + ".mailbox_id = ? AND ";
            StringBuilder sb = new StringBuilder();
            sb.append("DELETE FROM ");
            sb.append(dataSourceTable);
            sb.append(" WHERE ");
            sb.append(IN_THIS_MAILBOX_AND);
            sb.append("  data_source_id = ? AND folder_id = ?");
            stmt = conn.prepareStatement(sb.toString());
            int pos = 1;
            pos = DbMailItem.setMailboxId(stmt, mbox, pos);
            stmt.setString(pos++, ds.getId());
            stmt.setInt(pos++, folderId);
            int numRows = stmt.executeUpdate();
            if (!isBatch) {
                conn.commit();
            }
            stmt.close();
            ZimbraLog.datasource.debug("Deleted %d mappings for %s", numRows, ds.getName());
        } catch (SQLException e) {
            throw ServiceException.FAILURE("Unable to delete mapping for dataSource " + ds.getName(), e);
        } finally {
            DbPool.closeStatement(stmt);
            if (!isBatch) {
                DbPool.quietClose(conn);
            }
        }
    } finally {
        mbox.lock.release();
    }
    return items;
}

Example 37 with DbConnection

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

the class DbDataSource method lookupPurgedConversationsByHash.

public static List<PurgedConversation> lookupPurgedConversationsByHash(DataSource ds, List<String> hashes) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(ds);
    DbConnection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs;
    Map<Integer, PurgedConversation> convMap = new HashMap<Integer, PurgedConversation>();
    try {
        conn = DbPool.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT pm.item_id, pm.parent_id, pm.remote_id, pm.remote_folder_id FROM");
        sb.append(" (SELECT item_id, parent_id, remote_id, remote_folder_id FROM ").append(getPurgedMessagesTableName(mbox));
        sb.append(" WHERE mailbox_id = ? AND data_source_id = ?)");
        sb.append(" pm ");
        sb.append(" INNER JOIN ");
        sb.append("(SELECT DISTINCT item_id FROM ").append(getPurgedConvsTableName(mbox));
        sb.append(" WHERE mailbox_id = ? AND data_source_id = ? AND ");
        sb.append(DbUtil.whereIn("hash", hashes.size()));
        sb.append(") pc ");
        sb.append("ON COALESCE(pm.parent_id, pm.item_id) = pc.item_id");
        stmt = conn.prepareStatement(sb.toString());
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, ds.getId());
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, ds.getId());
        for (String hash : hashes) {
            stmt.setString(pos++, hash);
        }
        rs = stmt.executeQuery();
        while (rs.next()) {
            Integer msgId = rs.getInt(1);
            Integer parentId = rs.getInt(2);
            String remoteId = rs.getString(3);
            String remoteFolderId = rs.getString(4);
            PurgedConversation conv = null;
            if (convMap.containsKey(parentId)) {
                conv = convMap.get(parentId);
            } else {
                conv = new PurgedConversation(ds, parentId);
                convMap.put(parentId, conv);
            }
            conv.addMessage(new PurgedMessage(msgId, parentId, remoteId, remoteFolderId));
        }
        rs.close();
        stmt.close();
        Collection<PurgedConversation> values = convMap.values();
        return new ArrayList<PurgedConversation>(values);
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to get purged conversations", e);
    } finally {
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}

Example 38 with DbConnection

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

the class DbDataSource method deletePurgeDataByDataSource.

private static void deletePurgeDataByDataSource(Mailbox mbox, String dataSourceId, String tableName) throws ServiceException {
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = DbPool.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM ").append(tableName);
        sb.append(" WHERE mailbox_id = ? AND data_source_id = ?");
        stmt = conn.prepareStatement(sb.toString());
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, dataSourceId);
        stmt.executeUpdate();
        stmt.close();
        conn.commit();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable delete purged conversations for data source", e);
    } finally {
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}

Example 39 with DbConnection

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

the class DbImapMessage method setUid.

public static void setUid(Mailbox mbox, int itemId, long uid) throws ServiceException {
    ZimbraLog.datasource.debug("Updating IMAP message tracker uid: mboxId=%d, localItemId=%d remoteUid=%x", mbox.getId(), itemId, uid);
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = DbPool.getConnection(mbox);
        stmt = conn.prepareStatement("UPDATE " + getTableName(mbox) + " SET uid = ?" + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "item_id = ?");
        int pos = 1;
        stmt.setLong(pos++, uid);
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, itemId);
        stmt.executeUpdate();
        conn.commit();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to update IMAP message data", e);
    } finally {
        if (stmt != null)
            DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}

Example 40 with DbConnection

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

the class DbImapMessage method deleteImapMessages.

/**
     * Deletes IMAP message tracker data.
     */
public static void deleteImapMessages(Mailbox mbox, int localFolderId) throws ServiceException {
    ZimbraLog.datasource.debug("Deleting all IMAP message trackers: mboxId=%d, localFolderId=%d", mbox.getId(), localFolderId);
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = DbPool.getConnection(mbox);
        stmt = conn.prepareStatement("DELETE FROM " + getTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "imap_folder_id = ?");
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, localFolderId);
        stmt.executeUpdate();
        conn.commit();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to delete IMAP message data", e);
    } finally {
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}