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

Example 66 with DbConnection

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

the class ZimbraQueryTest method mdate.

@Test
public void mdate() throws Exception {
    Mailbox mbox = MailboxManager.getInstance().getMailboxByAccountId(MockProvisioning.DEFAULT_ACCOUNT_ID);
    DbConnection conn = DbPool.getConnection();
    DbUtil.executeUpdate(conn, "INSERT INTO mboxgroup1.mail_item " + "(mailbox_id, id, folder_id, type, flags, date, change_date, size, tags, mod_metadata, mod_content) " + "VALUES(?, ?, ?, ?, 0, ?, ?, 0, 0, 0, 0)", mbox.getId(), 101, Mailbox.ID_FOLDER_INBOX, MailItem.Type.MESSAGE.toByte(), 100, 1000);
    DbUtil.executeUpdate(conn, "INSERT INTO mboxgroup1.mail_item " + "(mailbox_id, id, folder_id, type, flags, date, change_date, size, tags, mod_metadata, mod_content) " + "VALUES(?, ?, ?, ?, 0, ?, ?, 0, 0, 0, 0)", mbox.getId(), 102, Mailbox.ID_FOLDER_INBOX, MailItem.Type.MESSAGE.toByte(), 200, 2000);
    DbUtil.executeUpdate(conn, "INSERT INTO mboxgroup1.mail_item " + "(mailbox_id, id, folder_id, type, flags, date, change_date, size, tags, mod_metadata, mod_content) " + "VALUES(?, ?, ?, ?, 0, ?, ?, 0, 0, 0, 0)", mbox.getId(), 103, Mailbox.ID_FOLDER_INBOX, MailItem.Type.MESSAGE.toByte(), 300, 3000);
    DbUtil.executeUpdate(conn, "INSERT INTO mboxgroup1.mail_item " + "(mailbox_id, id, folder_id, type, flags, date, change_date, size, tags, mod_metadata, mod_content) " + "VALUES(?, ?, ?, ?, 0, ?, ?, 0, 0, 0, 0)", mbox.getId(), 104, Mailbox.ID_FOLDER_INBOX, MailItem.Type.MESSAGE.toByte(), 400, 4000);
    DbUtil.executeUpdate(conn, "INSERT INTO mboxgroup1.mail_item " + "(mailbox_id, id, folder_id, type, flags, date, change_date, size, tags, mod_metadata, mod_content) " + "VALUES(?, ?, ?, ?, 0, ?, ?, 0, 0, 0, 0)", mbox.getId(), 105, Mailbox.ID_FOLDER_INBOX, MailItem.Type.MESSAGE.toByte(), 500, 5000);
    conn.commit();
    conn.closeQuietly();
    SearchParams params = new SearchParams();
    params.setQueryString("mdate:>3000000");
    params.setSortBy(SortBy.DATE_ASC);
    params.setTypes(EnumSet.of(MailItem.Type.MESSAGE));
    params.setFetchMode(SearchParams.Fetch.IDS);
    ZimbraQuery query = new ZimbraQuery(new OperationContext(mbox), SoapProtocol.Soap12, mbox, params);
    Assert.assertEquals("ZQ: Q(DATE:MDATE,197001010050-196912312359)", query.toString());
    ZimbraQueryResults result = query.execute();
    Assert.assertEquals(104, result.getNext().getItemId());
    Assert.assertEquals(105, result.getNext().getItemId());
    Assert.assertEquals(null, result.getNext());
    Closeables.closeQuietly(result);
}

Example 67 with DbConnection

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

the class DesktopMailboxTest method countInboxMessages.

private int countInboxMessages(Mailbox mbox) throws ServiceException, SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    DbConnection conn = DbPool.getConnection(mbox);
    try {
        stmt = conn.prepareStatement("SELECT COUNT(*) FROM mboxgroup1.mail_item WHERE mailbox_id = ? and folder_id = ?");
        stmt.setInt(1, mbox.getId());
        stmt.setInt(2, Mailbox.ID_FOLDER_INBOX);
        rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    } finally {
        DbPool.closeResults(rs);
        DbPool.quietCloseStatement(stmt);
        DbPool.quietClose(conn);
    }
}

Example 68 with DbConnection

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

the class ContactTest method tooLongSender.

@Test
public void tooLongSender() throws Exception {
    Mailbox mbox = MailboxManager.getInstance().getMailboxByAccountId(MockProvisioning.DEFAULT_ACCOUNT_ID);
    Map<String, Object> fields = new HashMap<String, Object>();
    fields.put(ContactConstants.A_firstName, Strings.repeat("F", 129));
    Contact contact = mbox.createContact(null, new ParsedContact(fields), Mailbox.ID_FOLDER_CONTACTS, null);
    DbConnection conn = DbPool.getConnection(mbox);
    Assert.assertEquals(Strings.repeat("F", 128), DbUtil.executeQuery(conn, "SELECT sender FROM mboxgroup1.mail_item WHERE mailbox_id = ? AND id = ?", mbox.getId(), contact.getId()).getString(1));
    fields.put(ContactConstants.A_firstName, null);
    fields.put(ContactConstants.A_lastName, Strings.repeat("L", 129));
    mbox.modifyContact(null, contact.getId(), new ParsedContact(fields));
    Assert.assertEquals(Strings.repeat("L", 128), DbUtil.executeQuery(conn, "SELECT sender FROM mboxgroup1.mail_item WHERE mailbox_id = ? AND id = ?", mbox.getId(), contact.getId()).getString(1));
    conn.closeQuietly();
}

Example 69 with DbConnection

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

the class TestUtil method updateMailItemChangeDateAndFlag.

public static void updateMailItemChangeDateAndFlag(Mailbox mbox, int itemId, long changeDate, int flagValue) throws ServiceException {
    DbConnection conn = DbPool.getConnection(mbox);
    ;
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(DbMailItem.getMailItemTableName(mbox)).append(" SET change_date = ").append(changeDate);
        if (flagValue > 0) {
            sql.append(", flags = ").append(flagValue);
        }
        sql.append(" WHERE id = ").append(itemId);
        DbUtil.executeUpdate(conn, sql.toString());
    } finally {
        conn.commit();
        conn.closeQuietly();
    }
}

Example 70 with DbConnection

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

the class DbPendingAclPush method queue.

public static void queue(Mailbox mbox, int itemId) throws ServiceException {
    if (!supported)
        return;
    if (mbox == null)
        return;
    ZimbraLog.mailbox.debug("Queuing for ACL push - mailbox %s item %s", mbox.getId(), itemId);
    DbConnection conn = mbox.getOperationConnection();
    PreparedStatement stmt = null;
    boolean supportsReplace = Db.supports(Db.Capability.REPLACE_INTO);
    try {
        stmt = conn.prepareStatement("SELECT mailbox_id, item_id, date FROM " + TABLE_PENDING_ACL_PUSH + " WHERE mailbox_id = ? AND item_id = ? ");
        stmt.setInt(1, mbox.getId());
        stmt.setInt(2, itemId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            stmt = conn.prepareStatement("UPDATE " + TABLE_PENDING_ACL_PUSH + " SET date = ? WHERE mailbox_id = ? AND item_id = ?");
            stmt.setLong(1, System.currentTimeMillis());
            stmt.setInt(2, mbox.getId());
            stmt.setInt(3, itemId);
            stmt.executeUpdate();
        } else {
            String command = supportsReplace ? "REPLACE" : "INSERT";
            stmt = conn.prepareStatement(command + " INTO " + TABLE_PENDING_ACL_PUSH + " (mailbox_id, item_id, date) VALUES (?, ?, ?)");
            stmt.setInt(1, mbox.getId());
            stmt.setInt(2, itemId);
            stmt.setLong(3, System.currentTimeMillis());
            stmt.executeUpdate();
        }
    } catch (SQLException e) {
        throw ServiceException.FAILURE("Unable to queue for ACL push - mailbox " + mbox.getId() + " item " + itemId, e);
    } finally {
        conn.closeQuietly(stmt);
    }
    // take care of this.
    if (!supportsReplace) {
        try {
            Thread.sleep(2);
        } catch (InterruptedException ignored) {
        }
    }
}