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

Example 31 with DbConnection

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

the class DbDataSource method storePurgedConversationHash.

public static void storePurgedConversationHash(Mailbox mbox, String dataSourceId, Integer convId, String hash) throws ServiceException {
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = DbPool.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append(getPurgedConvsTableName(mbox));
        sb.append(" (mailbox_id, data_source_id, item_id, hash) VALUES (?, ?, ?, ?)");
        stmt = conn.prepareStatement(sb.toString());
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, dataSourceId);
        stmt.setInt(pos++, convId);
        stmt.setString(pos++, hash);
        stmt.executeUpdate();
        stmt.close();
        conn.commit();
    } catch (SQLException e) {
        if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) {
            ZimbraLog.datasource.warn("inserting duplicate hash");
        } else {
            throw ServiceException.FAILURE("Unable to insert purged conversation hash", e);
        }
    } finally {
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}

Example 32 with DbConnection

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

the class DbDataSource method deleteMappings.

public static void deleteMappings(DataSource ds, Collection<Integer> itemIds, boolean isBatch) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(ds);
    ZimbraLog.datasource.debug("Deleting %d mappings for dataSource %s", itemIds.size(), ds.getName());
    List<List<Integer>> splitIds = ListUtil.split(itemIds, Db.getINClauseBatchSize());
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        if (isBatch) {
            conn = mbox.getOperationConnection();
        } else {
            conn = DbPool.getConnection(mbox);
        }
        int numRows = 0;
        for (List<Integer> curIds : splitIds) {
            StringBuilder sb = new StringBuilder();
            sb.append("DELETE FROM ");
            sb.append(getTableName(mbox));
            sb.append(" WHERE ");
            sb.append(DbMailItem.IN_THIS_MAILBOX_AND);
            sb.append(" data_source_id = ? AND ");
            sb.append(DbUtil.whereIn("item_id", curIds.size()));
            stmt = conn.prepareStatement(sb.toString());
            int i = 1;
            i = DbMailItem.setMailboxId(stmt, mbox, i);
            stmt.setString(i++, ds.getId());
            for (int itemId : curIds) stmt.setInt(i++, itemId);
            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);
        }
    }
}

Example 33 with DbConnection

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

the class DbDataSource method removeFromPurgedMessageTable.

private static void removeFromPurgedMessageTable(Mailbox mbox, DataSource ds, PurgedMessage msg) throws ServiceException {
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = DbPool.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM ").append(getPurgedMessagesTableName(mbox));
        sb.append(" WHERE mailbox_id = ? AND data_source_id = ? AND item_id = ?");
        stmt = conn.prepareStatement(sb.toString());
        int pos = 1;
        pos = DbMailItem.setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, ds.getId());
        stmt.setInt(pos++, msg.getMsgId());
        stmt.executeUpdate();
        stmt.close();
        conn.commit();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable delete purged message", e);
    } finally {
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
}

Example 34 with DbConnection

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

the class DbDataSource method getAllMappings.

public static Collection<DataSourceItem> getAllMappings(DataSource ds) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(ds);
    ArrayList<DataSourceItem> items = new ArrayList<DataSourceItem>();
    ZimbraLog.datasource.debug("Get all mappings for %s", ds.getName());
    DbConnection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = DbPool.getConnection(mbox);
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT item_id, folder_id, remote_id, metadata FROM ");
        sb.append(getTableName(mbox));
        sb.append(" WHERE ");
        sb.append(DbMailItem.IN_THIS_MAILBOX_AND);
        sb.append("  data_source_id = ?");
        stmt = conn.prepareStatement(sb.toString());
        int i = 1;
        i = DbMailItem.setMailboxId(stmt, mbox, i);
        stmt.setString(i++, ds.getId());
        rs = stmt.executeQuery();
        while (rs.next()) {
            Metadata md = null;
            String buf = DbMailItem.decodeMetadata(rs.getString(4));
            if (buf != null)
                md = new Metadata(buf);
            items.add(new DataSourceItem(rs.getInt(2), rs.getInt(1), rs.getString(3), md));
        }
        rs.close();
        stmt.close();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to get mapping for dataSource " + ds.getName(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
    return items;
}

Example 35 with DbConnection

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

the class DbDataSource method getAllMappingsAndFlagsInFolder.

public static Collection<DataSourceItem> getAllMappingsAndFlagsInFolder(DataSource ds, int folderId) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(ds);
    ArrayList<DataSourceItem> items = new ArrayList<DataSourceItem>();
    ZimbraLog.datasource.debug("Get all mappings for %s in folder %d", ds.getName(), folderId);
    DbConnection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        String thisTable = getTableName(mbox);
        String IN_THIS_MAILBOX_AND = DebugConfig.disableMailboxGroups ? "" : thisTable + ".mailbox_id = ? AND ";
        String MBOX_JOIN = DebugConfig.disableMailboxGroups ? " " : thisTable + ".mailbox_id = mi.mailbox_id AND ";
        conn = DbPool.getConnection(mbox);
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT item_id, remote_id, ").append(thisTable).append(".metadata, mi.unread, mi.flags FROM ");
        sb.append(thisTable);
        sb.append("  LEFT OUTER JOIN " + DbMailItem.getMailItemTableName(mbox)).append(" mi ");
        sb.append("  ON ").append(MBOX_JOIN).append(thisTable).append(".item_id = mi.id ");
        sb.append(" WHERE ");
        sb.append(IN_THIS_MAILBOX_AND);
        sb.append("  data_source_id = ? AND ").append(thisTable).append(".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);
        rs = stmt.executeQuery();
        while (rs.next()) {
            Metadata md = null;
            String buf = DbMailItem.decodeMetadata(rs.getString(3));
            int unread = rs.getInt(4);
            int flags = rs.getInt(5);
            if (buf != null)
                md = new Metadata(buf);
            flags = unread > 0 ? (flags | Flag.BITMASK_UNREAD) : (flags & ~Flag.BITMASK_UNREAD);
            items.add(new DataSourceItem(folderId, rs.getInt(1), rs.getString(2), md, flags));
        }
        rs.close();
        stmt.close();
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to get mapping for dataSource " + ds.getName(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
        DbPool.quietClose(conn);
    }
    return items;
}