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

Example 21 with DbConnection

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

the class DbMailItem method getByParent.

public static List<UnderlyingData> getByParent(MailItem parent, SortBy sort, int limit, boolean fromDumpster) throws ServiceException {
    Mailbox mbox = parent.getMailbox();
    List<UnderlyingData> result = new ArrayList<UnderlyingData>();
    StringBuilder sql = new StringBuilder("SELECT ").append(DB_FIELDS).append(" FROM ").append(getMailItemTableName(parent.getMailbox(), " mi", fromDumpster)).append(" WHERE ").append(IN_THIS_MAILBOX_AND).append("parent_id = ? ").append(DbSearch.orderBy(sort, false));
    if (limit > 0) {
        sql.append(" LIMIT ?");
    }
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement(sql.toString());
        if (parent.getSize() > RESULTS_STREAMING_MIN_ROWS) {
            Db.getInstance().enableStreaming(stmt);
        }
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setInt(pos++, parent.getId());
        if (limit > 0) {
            stmt.setInt(pos++, limit);
        }
        rs = stmt.executeQuery();
        while (rs.next()) {
            UnderlyingData data = constructItem(rs, fromDumpster);
            if (Mailbox.isCachedType(MailItem.Type.of(data.type))) {
                throw ServiceException.INVALID_REQUEST("folders and tags must be retrieved from cache", null);
            }
            result.add(data);
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("fetching children of item " + parent.getId(), e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 22 with DbConnection

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

the class DbMailItem method getIMAPDeletedItems.

/**
     * Finds mail items with deleted flag (\Deleted)
     *
     * @param mbox Object of Mailbox
     * @param cutOff Cut off time
     * @throws ServiceException
     */
public static List<Integer> getIMAPDeletedItems(Mailbox mbox, long cutOff, int batchSize) throws ServiceException {
    List<Integer> imapDeletedItems = new ArrayList<Integer>();
    DbConnection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = DbPool.getConnection(mbox);
        stmt = conn.prepareStatement("SELECT id FROM " + getMailItemTableName(mbox) + " WHERE " + IN_THIS_MAILBOX_AND + " change_date < ? AND " + Db.getInstance().bitAND("flags", String.valueOf(Flag.BITMASK_DELETED)) + " = " + String.valueOf(Flag.BITMASK_DELETED) + " limit " + batchSize);
        setMailboxId(stmt, mbox, 1);
        stmt.setLong(2, cutOff);
        rs = stmt.executeQuery();
        while (rs.next()) {
            imapDeletedItems.add(rs.getInt("id"));
        }
    } catch (SQLException sqle) {
        throw ServiceException.FAILURE("error while fetching imap deleted items", sqle);
    } finally {
        conn.closeQuietly(rs);
        conn.closeQuietly(stmt);
        DbPool.quietClose(conn);
    }
    return imapDeletedItems;
}

Example 23 with DbConnection

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

the class DbMailItem method getDumpsterItems.

public static List<Integer> getDumpsterItems(Mailbox mbox, int lastSync, int folderId, int maxTrack) throws ServiceException {
    List<Integer> result = new ArrayList<>();
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.prepareStatement("SELECT id, prev_folders, folder_id, mod_metadata FROM " + getMailItemTableName(mbox, true) + " WHERE " + IN_THIS_MAILBOX_AND + "mod_metadata > ? " + " ORDER BY id");
        Db.getInstance().enableStreaming(stmt);
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setLong(pos++, lastSync);
        rs = stmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt(1);
            String prevFolders = rs.getString(2);
            int curFolderId = rs.getInt(3);
            int modseq = rs.getInt(4);
            if (lastSync < modseq && curFolderId == folderId) {
                result.add(id);
                continue;
            }
            if (StringUtil.isNullOrEmpty(prevFolders)) {
                continue;
            }
            //e.g. "101:2;110:5"
            String[] mappings = prevFolders.split(";");
            Map<Integer, Integer> modseq2folders = new HashMap<>();
            for (String info : mappings) {
                String[] meta = info.split(":");
                modseq2folders.put(Integer.parseInt(meta[0]), Integer.parseInt(meta[1]));
            }
            if (!modseq2folders.containsValue(folderId)) {
                continue;
            }
            int index = 0;
            while (index < mappings.length && index < maxTrack) {
                String md2id = mappings[index++];
                String[] pair = md2id.split(":");
                int md = Integer.parseInt(pair[0]);
                if (lastSync < md) {
                    if (folderId == Integer.parseInt(pair[1])) {
                        result.add(id);
                        break;
                    }
                }
            }
        }
        return result;
    } catch (SQLException e) {
        throw ServiceException.FAILURE("reading mail_item_dumpster since modseq: " + lastSync, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

Example 24 with DbConnection

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

the class DbMailItem method updateInCalendarItemTable.

public static void updateInCalendarItemTable(CalendarItem calItem) throws ServiceException {
    Mailbox mbox = calItem.getMailbox();
    long start = calItem.getStartTime();
    long end = calItem.getEndTime();
    Timestamp startTs = new Timestamp(start);
    Timestamp endTs = getEnd(start, end);
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    try {
        String command = Db.supports(Db.Capability.REPLACE_INTO) ? "REPLACE" : "INSERT";
        String mailbox_id = DebugConfig.disableMailboxGroups ? "" : "mailbox_id, ";
        stmt = conn.prepareStatement(command + " INTO " + getCalendarItemTableName(mbox) + " (" + mailbox_id + "uid, item_id, start_time, end_time)" + " VALUES (" + MAILBOX_ID_VALUE + "?, ?, ?, ?)");
        int pos = 1;
        pos = setMailboxId(stmt, mbox, pos);
        stmt.setString(pos++, calItem.getUid());
        stmt.setInt(pos++, calItem.getId());
        stmt.setTimestamp(pos++, startTs);
        stmt.setTimestamp(pos++, endTs);
        stmt.executeUpdate();
    } catch (SQLException e) {
        if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) {
            try {
                DbPool.closeStatement(stmt);
                stmt = conn.prepareStatement("UPDATE " + getCalendarItemTableName(mbox) + " SET item_id = ?, start_time = ?, end_time = ? WHERE " + IN_THIS_MAILBOX_AND + "uid = ?");
                int pos = 1;
                stmt.setInt(pos++, calItem.getId());
                stmt.setTimestamp(pos++, startTs);
                stmt.setTimestamp(pos++, endTs);
                pos = setMailboxId(stmt, mbox, pos);
                stmt.setString(pos++, calItem.getUid());
                stmt.executeUpdate();
            } catch (SQLException nested) {
                throw ServiceException.FAILURE("updating data in calendar item table " + calItem.getUid(), nested);
            }
        } else {
            throw ServiceException.FAILURE("writing invite to calendar item table " + calItem.getUid(), e);
        }
    } finally {
        DbPool.closeStatement(stmt);
    }
}

Example 25 with DbConnection

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

the class DbDataSource method updateMapping.

public static void updateMapping(DataSource ds, DataSourceItem item, final boolean isBatch) throws ServiceException {
    Mailbox mbox = DataSourceManager.getInstance().getMailbox(ds);
    ZimbraLog.datasource.debug("Updating mapping for dataSource %s: itemId(%d), remoteId(%s)", ds.getName(), item.itemId, item.remoteId);
    DbConnection conn = null;
    PreparedStatement stmt = null;
    try {
        if (isBatch) {
            conn = mbox.getOperationConnection();
        } else {
            conn = DbPool.getConnection(mbox);
        }
        if (!Db.supports(Db.Capability.ON_DUPLICATE_KEY) && !hasMapping(ds, item.itemId)) {
            // if we need to update due to unique remoteId then itemid
            // isn't going to be the same
            StringBuilder sb = new StringBuilder();
            sb.append("UPDATE ");
            sb.append(getTableName(mbox));
            sb.append(" SET folder_id = ?, item_id = ?, metadata = ? WHERE ");
            sb.append(DbMailItem.IN_THIS_MAILBOX_AND);
            sb.append(" remote_id = ?");
            stmt = conn.prepareStatement(sb.toString());
            int i = 1;
            stmt.setInt(i++, item.folderId);
            stmt.setInt(i++, item.itemId);
            stmt.setString(i++, DbMailItem.checkMetadataLength((item.md == null) ? null : item.md.toString()));
            i = DbMailItem.setMailboxId(stmt, mbox, i);
            stmt.setString(i++, item.remoteId);
        } else {
            StringBuilder sb = new StringBuilder();
            sb.append("UPDATE ");
            sb.append(getTableName(mbox));
            sb.append(" SET folder_id = ?, remote_id = ?, metadata = ? WHERE ");
            sb.append(DbMailItem.IN_THIS_MAILBOX_AND);
            sb.append(" item_id = ?");
            stmt = conn.prepareStatement(sb.toString());
            int i = 1;
            stmt.setInt(i++, item.folderId);
            stmt.setString(i++, item.remoteId);
            stmt.setString(i++, DbMailItem.checkMetadataLength((item.md == null) ? null : item.md.toString()));
            i = DbMailItem.setMailboxId(stmt, mbox, i);
            stmt.setInt(i++, item.itemId);
        }
        stmt.executeUpdate();
        if (!isBatch) {
            conn.commit();
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to update mapping for dataSource " + ds.getName(), e);
    } finally {
        DbPool.closeStatement(stmt);
        if (!isBatch) {
            DbPool.quietClose(conn);
        }
    }
}