Examples with DbSearchConstraints com.zimbra.cs.index.DbSearchConstraints used on opensource projects

Example 1 with DbSearchConstraints

use of com.zimbra.cs.index.DbSearchConstraints in project zm-mailbox by Zimbra.

the class DbSearch method encodeConstraint.

private void encodeConstraint(DbSearchConstraints node, byte[] calTypes, boolean inCalTable, boolean joinTaggedItem) {
    if (node instanceof DbSearchConstraints.Intersection || node instanceof DbSearchConstraints.Union) {
        boolean first = true;
        boolean and = node instanceof DbSearchConstraints.Intersection;
        sql.append('(');
        for (DbSearchConstraints child : node.getChildren()) {
            if (!child.isEmpty()) {
                if (!first) {
                    sql.append(and ? " AND " : " OR ");
                }
                encodeConstraint(child, calTypes, inCalTable, joinTaggedItem);
                first = false;
            }
        }
        sql.append(") ");
        return;
    }
    // we're here, so we must be in a DbSearchConstraints leaf node
    if (node.isEmpty()) {
        return;
    }
    DbSearchConstraints.Leaf constraint = node.toLeaf();
    assert (node instanceof DbSearchConstraints.Leaf && constraint != null);
    // if there are no possible matches, short-circuit here...
    if (constraint.noResults) {
        sql.append(Db.supports(Db.Capability.BOOLEAN_DATATYPE) ? "FALSE" : "0=1");
        return;
    }
    sql.append('(');
    boolean needAnd = false;
    // special-case this one, since there can't be a leading AND here...
    if (ListUtil.isEmpty(constraint.types)) {
        if (!constraint.typesFactoredOut) {
            //don't include the negative types clause if types already encoded in
            sql.append("mi.type NOT IN ").append(DbMailItem.NON_SEARCHABLE_TYPES);
            needAnd = true;
        }
    } else {
        sql.append(DbUtil.whereIn("mi.type", constraint.types.size()));
        for (MailItem.Type type : constraint.types) {
            params.add(type.toByte());
        }
        needAnd = true;
    }
    needAnd = needAnd | encodeNoRecipients(constraint.excludeHasRecipients, needAnd);
    needAnd = needAnd | encodeType(constraint.excludeTypes, false, needAnd);
    needAnd = needAnd | encode("mi.type", inCalTable, calTypes, needAnd);
    needAnd = needAnd | encodeTag(constraint.tags, true, joinTaggedItem, needAnd);
    needAnd = needAnd | encodeTag(constraint.excludeTags, false, false, needAnd);
    needAnd = needAnd | encodeFolder(constraint.folders, true, needAnd);
    needAnd = needAnd | encodeFolder(constraint.excludeFolders, false, needAnd);
    if (constraint.convId > 0) {
        needAnd = needAnd | encode("mi.parent_id", true, constraint.convId, needAnd);
    } else {
        needAnd = needAnd | encode("mi.parent_id", false, constraint.prohibitedConvIds, needAnd);
    }
    needAnd = needAnd | encode("mi.id", true, constraint.itemIds, needAnd);
    needAnd = needAnd | encode("mi.id", false, constraint.prohibitedItemIds, needAnd);
    needAnd = needAnd | encode("mi.index_id", true, constraint.indexIds, needAnd);
    if (constraint.cursorRange != null) {
        needAnd = needAnd | encodeCursorRange(constraint.cursorRange, needAnd);
    }
    for (Map.Entry<DbSearchConstraints.RangeType, DbSearchConstraints.Range> entry : constraint.ranges.entries()) {
        switch(entry.getKey()) {
            case DATE:
                needAnd = needAnd | encodeDateRange("mi.date", (DbSearchConstraints.NumericRange) entry.getValue(), needAnd);
                break;
            case MDATE:
                needAnd = needAnd | encodeDateRange("mi.change_date", (DbSearchConstraints.NumericRange) entry.getValue(), needAnd);
                break;
            case MODSEQ:
                needAnd = needAnd | encodeLongRange("mi.mod_metadata", (DbSearchConstraints.NumericRange) entry.getValue(), 1L, needAnd);
                break;
            case SIZE:
                needAnd = needAnd | encodeLongRange("mi.size", (DbSearchConstraints.NumericRange) entry.getValue(), 0, needAnd);
                break;
            case CAL_START_DATE:
                if (inCalTable) {
                    needAnd = needAnd | encodeTimestampRange("ap.start_time", (DbSearchConstraints.NumericRange) entry.getValue(), 1L, needAnd);
                }
                break;
            case CAL_END_DATE:
                if (inCalTable) {
                    needAnd = needAnd | encodeTimestampRange("ap.end_time", (DbSearchConstraints.NumericRange) entry.getValue(), 1L, needAnd);
                }
                break;
            case SENDER:
                needAnd = needAnd | encodeStringRange("mi.sender", (DbSearchConstraints.StringRange) entry.getValue(), needAnd);
                break;
            case SUBJECT:
                needAnd = needAnd | encodeStringRange("mi.subject", (DbSearchConstraints.StringRange) entry.getValue(), needAnd);
                break;
            case CONV_COUNT:
            default:
                break;
        }
    }
    Boolean isSoloPart = node.toLeaf().getIsSoloPart();
    if (isSoloPart != null) {
        if (needAnd) {
            sql.append(" AND ");
        }
        needAnd = true;
        if (isSoloPart.booleanValue()) {
            sql.append("mi.parent_id is NULL ");
        } else {
            sql.append("mi.parent_id is NOT NULL ");
        }
    }
    if (constraint.hasIndexId != null) {
        if (needAnd) {
            sql.append(" AND ");
        }
        needAnd = true;
        if (constraint.hasIndexId.booleanValue()) {
            sql.append("mi.index_id is NOT NULL ");
        } else {
            sql.append("mi.index_id is NULL ");
        }
    }
    sql.append(')');
}

Example 2 with DbSearchConstraints

use of com.zimbra.cs.index.DbSearchConstraints in project zm-mailbox by Zimbra.

the class DbSearch method searchInternal.

private List<Result> searchInternal(DbConnection conn, DbSearchConstraints node, SortBy sort, int offset, int limit, FetchMode fetch, boolean searchDraftsSeparately) throws SQLException, ServiceException {
    //check if we need to run this as two queries: one with "mi.recipients is not NULL" and one in drafts with "mi.recipients is NULL"
    if (searchingInDrafts(node) && searchDraftsSeparately && sort != null && (sort.equals(SortBy.RCPT_ASC) || sort.equals(SortBy.RCPT_DESC))) {
        //clone the existing node containing the Drafts constraint
        DbSearchConstraints.Leaf draftsConstraint = findDraftsConstraint(node).clone();
        for (Folder folder : draftsConstraint.folders) {
            if (folder.getId() == Mailbox.ID_FOLDER_DRAFTS) {
                draftsConstraint.folders.clear();
                //constrain the node to only drafts
                draftsConstraint.folders.add(folder);
                break;
            }
        }
        draftsConstraint.excludeHasRecipients = true;
        DbSearchConstraints node1;
        DbSearchConstraints node2;
        if (sort.equals(SortBy.RCPT_ASC)) {
            node1 = node;
            node2 = draftsConstraint;
        } else {
            node1 = draftsConstraint;
            node2 = node;
        }
        List<Result> result = searchTwoConstraints(conn, node1, node2, sort, offset, limit, fetch);
        return result;
    }
    boolean hasValidLIMIT = offset >= 0 && limit >= 0;
    boolean hasMailItemOnlyConstraints = true;
    boolean hasAppointmentTableConstraints = hasAppointmentTableConstraints(node);
    if (hasAppointmentTableConstraints) {
        hasMailItemOnlyConstraints = hasMailItemOnlyConstraints(node);
    }
    boolean requiresUnion = hasMailItemOnlyConstraints && hasAppointmentTableConstraints;
    //HACK!HACK!HACK!
    //Bug: 68609
    //slow search "in:inbox is:unread or is:flagged"
    //its actually cheaper to do a D/B join between mail_item and tagged_item table when
    //the unread items are smaller in count. We can possibly do the same for user tags.
    boolean joinTaggedItem = false;
    if (node instanceof DbSearchConstraints.Leaf && !dumpster) {
        DbSearchConstraints.Leaf constraint = node.toLeaf();
        if (constraint.excludeTags.isEmpty() && !constraint.tags.isEmpty() && constraint.tags.size() == 1) {
            Tag tag = constraint.tags.iterator().next();
            if (tag.getId() == FlagInfo.UNREAD.toId() || tag.getId() > 0) {
                long count = 0;
                if (tag.getId() == FlagInfo.UNREAD.toId()) {
                    //let's make an estimate of # of unread items for this mailbox.
                    //It doesn't matter which folder(s) the user is trying to search because
                    //the performance solely depends on the # of unread items
                    count = countUnread(mailbox.getFolderById(null, Mailbox.ID_FOLDER_USER_ROOT));
                } else if (tag.getId() > 0) {
                    //user tag
                    count = tag.getSize();
                }
                if (count < LC.search_tagged_item_count_join_query_cutoff.intValue())
                    joinTaggedItem = true;
            }
        }
    }
    if (hasMailItemOnlyConstraints) {
        if (requiresUnion) {
            sql.append('(');
        }
        boolean maybeExcludeNoRecipients = true;
        if (node.toLeaf() != null) {
            maybeExcludeNoRecipients = !node.toLeaf().excludeHasRecipients;
        }
        // SELECT mi.id,... FROM mail_item AS mi [FORCE INDEX (...)] WHERE mi.mailboxid = ? AND
        encodeSelect(sort, fetch, false, joinTaggedItem, node, hasValidLIMIT, maybeExcludeNoRecipients);
        /*
             *( SUB-NODE AND/OR (SUB-NODE...) ) AND/OR ( SUB-NODE ) AND
             *    (
             *       one of: [type NOT IN (...)]  || [type = ?] || [type IN ( ...)]
             *       [ AND flags IN (...) ]
             *       ..etc
             *    )
             */
        encodeConstraint(node, hasAppointmentTableConstraints ? APPOINTMENT_TABLE_TYPES : null, false, joinTaggedItem);
        if (requiresUnion) {
            sql.append(orderBy(sort, true));
            // LIMIT ?, ?
            if (hasValidLIMIT && Db.supports(Db.Capability.LIMIT_CLAUSE)) {
                sql.append(' ').append(Db.getInstance().limit(offset, limit));
            }
        }
    }
    if (requiresUnion) {
        sql.append(" ) UNION ALL (");
    }
    if (hasAppointmentTableConstraints) {
        // SELECT...again...(this time with "appointment as ap")...WHERE...
        encodeSelect(sort, fetch, true, false, node, hasValidLIMIT);
        encodeConstraint(node, APPOINTMENT_TABLE_TYPES, true, false);
        if (requiresUnion) {
            sql.append(orderBy(sort, true));
            // LIMIT ?, ?
            if (hasValidLIMIT && Db.supports(Db.Capability.LIMIT_CLAUSE)) {
                sql.append(' ').append(Db.getInstance().limit(offset, limit));
            }
            if (requiresUnion) {
                sql.append(')');
            }
        }
    }
    // TODO FIXME: include COLLATION for sender/subject sort
    sql.append(orderBy(sort, true));
    // LIMIT ?, ?
    if (hasValidLIMIT && Db.supports(Db.Capability.LIMIT_CLAUSE)) {
        sql.append(' ').append(Db.getInstance().limit(offset, limit));
    }
    if (Db.supports(Db.Capability.SQL_PARAM_LIMIT)) {
        Db.getInstance().checkParamLimit(params.size());
    }
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        // Create the statement and bind all our parameters!
        stmt = conn.prepareStatement(sql.toString());
        setParameters(stmt);
        // Limit query if DB doesn't support LIMIT clause
        if (hasValidLIMIT && !Db.supports(Db.Capability.LIMIT_CLAUSE)) {
            stmt.setMaxRows(offset + limit + 1);
        }
        rs = stmt.executeQuery();
        List<Result> result = new ArrayList<Result>();
        while (rs.next()) {
            if (hasValidLIMIT && !Db.supports(Db.Capability.LIMIT_CLAUSE)) {
                if (offset-- > 0) {
                    continue;
                }
                if (limit-- <= 0) {
                    break;
                }
            }
            Object sortkey = getSortKey(rs, sort);
            switch(fetch) {
                case ID:
                    result.add(new IdResult(rs, sortkey));
                    break;
                case MAIL_ITEM:
                    result.add(new ItemDataResult(rs, sortkey, dumpster));
                    break;
                case IMAP_MSG:
                    result.add(new ImapResult(rs, sortkey));
                    break;
                case MODSEQ:
                    result.add(new ModSeqResult(rs, sortkey));
                    break;
                case MODCONTENT:
                    result.add(new ModContentResult(rs, sortkey));
                    break;
                case PARENT:
                    result.add(new ParentResult(rs, sortkey));
                    break;
                default:
                    assert false : fetch;
            }
        }
        return result;
    } finally {
        conn.closeQuietly(rs);
        conn.closeQuietly(stmt);
    }
}

Example 3 with DbSearchConstraints

use of com.zimbra.cs.index.DbSearchConstraints in project zm-mailbox by Zimbra.

the class DbSearch method search.

private List<Result> search(DbConnection conn, DbSearchConstraints node, SortBy sort, int offset, int limit, FetchMode fetch, boolean searchDraftsSeparately) throws ServiceException {
    if (!Db.supports(Db.Capability.AVOID_OR_IN_WHERE_CLAUSE) || !(node instanceof DbSearchConstraints.Union)) {
        try {
            node = node.optimize();
            return searchInternal(conn, node, sort, offset, limit, fetch, searchDraftsSeparately);
        } catch (SQLException e) {
            if (Db.errorMatches(e, Db.Error.TOO_MANY_SQL_PARAMS)) {
                // fall back to splitting OR clauses
                ZimbraLog.sqltrace.debug("Too many SQL params: %s", node, e);
            } else {
                throw ServiceException.FAILURE("Failed to search", e);
            }
        }
    }
    List<Result> result = new ArrayList<Result>();
    if (!(node instanceof DbSearchConstraints.Leaf)) {
        // run each toplevel ORed part as a separate SQL query, then merge the results in memory
        if (node instanceof DbSearchConstraints.Union) {
            for (DbSearchConstraints child : node.getChildren()) {
                result.addAll(new DbSearch(mailbox, dumpster).search(conn, child, sort, offset, limit, fetch));
            }
            Collections.sort(result, new ResultComparator(sort));
        } else if (node instanceof DbSearchConstraints.Intersection) {
            List<List<Result>> resultLists = new ArrayList<List<Result>>();
            for (DbSearchConstraints child : node.getChildren()) {
                resultLists.add(new DbSearch(mailbox, dumpster).search(conn, child, sort, offset, limit, fetch));
            }
            result = intersectSortedLists(result, resultLists);
        } else {
            throw ServiceException.FAILURE("Reached merge/intersect block with something other than OR/AND clause", null);
        }
    } else {
        //case 2 (leaf node), we could encounter a sql clause with too many folders involved, try splitting these folders
        //only deals with folders for now to avoid considering complicated situations (other constraints combined)
        DbSearchConstraints.Leaf leafNode = node.toLeaf();
        final int dbLimit = Db.getInstance().getParamLimit();
        //avoid edge cases
        int otherConstraintsCount = params.size() - leafNode.folders.size();
        final int softLimit = dbLimit - otherConstraintsCount - 10;
        if (leafNode.folders.size() > softLimit) {
            List<Folder> folderList = new ArrayList<Folder>(leafNode.folders);
            int end = leafNode.folders.size();
            int start = end - softLimit;
            leafNode.folders.clear();
            while (start > 0) {
                DbSearchConstraints.Leaf subsetNode = leafNode.clone();
                List<Folder> subList = folderList.subList(start, end);
                subsetNode.folders.addAll(subList);
                result.addAll(new DbSearch(mailbox, dumpster).search(conn, subsetNode, sort, offset, limit, fetch));
                end -= softLimit;
                start -= softLimit;
            }
            //0 to end
            DbSearchConstraints.Leaf subsetNode = leafNode.clone();
            List<Folder> subList = folderList.subList(0, end);
            subsetNode.folders.addAll(subList);
            result.addAll(new DbSearch(mailbox, dumpster).search(conn, subsetNode, sort, offset, limit, fetch));
            Collections.sort(result, new ResultComparator(sort));
        } else {
            throw ServiceException.FAILURE("splitting failed, too many constraints but not caused entirely by folders", null);
        }
    }
    return result;
}