Examples with DBQuery org.olat.core.commons.persistence.DBQuery used on opensource projects

Example 11 with DBQuery

use of org.olat.core.commons.persistence.DBQuery in project OpenOLAT by OpenOLAT.

the class BaseSecurityManager method createIdentitiesByPowerQuery.

private DBQuery createIdentitiesByPowerQuery(SearchIdentityParams params, boolean count) {
    boolean hasGroups = (params.getGroups() != null && params.getGroups().length > 0);
    boolean hasPermissionOnResources = (params.getPermissionOnResources() != null && params.getPermissionOnResources().length > 0);
    boolean hasAuthProviders = (params.getAuthProviders() != null && params.getAuthProviders().length > 0);
    // select identity and inner join with user to optimize query
    StringBuilder sb = new StringBuilder(5000);
    if (hasAuthProviders) {
        // It took my quite a while to make this work, so think twice before you change anything here!
        if (count) {
            sb.append("select count(distinct ident.key) from org.olat.basesecurity.AuthenticationImpl as auth  ").append(" right join auth.identity as ident").append(" inner join ident.user as user ");
        } else {
            sb.append("select distinct ident from org.olat.basesecurity.AuthenticationImpl as auth ").append(" right join auth.identity as ident").append(" inner join fetch ident.user as user ");
        }
    } else {
        if (count) {
            sb.append("select count(distinct ident.key) from org.olat.core.id.Identity as ident ").append(" inner join ident.user as user ");
        } else {
            sb.append("select distinct ident from org.olat.core.id.Identity as ident ").append(" inner join fetch ident.user as user ");
        }
    }
    if (hasGroups) {
        // join over security group memberships
        sb.append(" ,org.olat.basesecurity.SecurityGroupMembershipImpl as sgmsi ");
    }
    if (hasPermissionOnResources) {
        // join over policies
        sb.append(" ,org.olat.basesecurity.SecurityGroupMembershipImpl as policyGroupMembership ");
        sb.append(" ,org.olat.basesecurity.PolicyImpl as policy ");
        sb.append(" ,org.olat.resource.OLATResourceImpl as resource ");
    }
    String login = params.getLogin();
    Map<String, String> userproperties = params.getUserProperties();
    Date createdAfter = params.getCreatedAfter();
    Date createdBefore = params.getCreatedBefore();
    Integer status = params.getStatus();
    Collection<Long> identityKeys = params.getIdentityKeys();
    Boolean managed = params.getManaged();
    // complex where clause only when values are available
    if (login != null || (userproperties != null && !userproperties.isEmpty()) || (identityKeys != null && !identityKeys.isEmpty()) || createdAfter != null || createdBefore != null || hasAuthProviders || hasGroups || hasPermissionOnResources || status != null || managed != null) {
        sb.append(" where ");
        boolean needsAnd = false;
        boolean needsUserPropertiesJoin = false;
        // treat login and userProperties as one element in this query
        if (login != null && (userproperties != null && !userproperties.isEmpty())) {
            sb.append(" ( ");
        }
        // append query for login
        if (login != null) {
            login = makeFuzzyQueryString(login);
            if (login.contains("_") && dbVendor.equals("oracle")) {
                // oracle needs special ESCAPE sequence to search for escaped strings
                sb.append(" lower(ident.name) like :login ESCAPE '\\'");
            } else if (dbVendor.equals("mysql")) {
                sb.append(" ident.name like :login");
            } else {
                sb.append(" lower(ident.name) like :login");
            }
            // if user fields follow a join element is needed
            needsUserPropertiesJoin = true;
            // at least one user field used, after this and is required
            needsAnd = true;
        }
        // append queries for user fields
        if (userproperties != null && !userproperties.isEmpty()) {
            Map<String, String> emailProperties = new HashMap<String, String>();
            Map<String, String> otherProperties = new HashMap<String, String>();
            // split the user fields into two groups
            for (String key : userproperties.keySet()) {
                if (key.toLowerCase().contains("email")) {
                    emailProperties.put(key, userproperties.get(key));
                } else {
                    otherProperties.put(key, userproperties.get(key));
                }
            }
            // handle email fields special: search in all email fields
            if (!emailProperties.isEmpty()) {
                needsUserPropertiesJoin = checkIntersectionInUserProperties(sb, needsUserPropertiesJoin, params.isUserPropertiesAsIntersectionSearch());
                boolean moreThanOne = emailProperties.size() > 1;
                if (moreThanOne)
                    sb.append("(");
                boolean needsOr = false;
                for (String key : emailProperties.keySet()) {
                    if (needsOr)
                        sb.append(" or ");
                    if (dbVendor.equals("mysql")) {
                        sb.append(" user.").append(key).append(" like :").append(key).append("_value ");
                    } else {
                        sb.append(" lower(user.").append(key).append(") like :").append(key).append("_value ");
                    }
                    if (dbVendor.equals("oracle")) {
                        sb.append(" escape '\\'");
                    }
                    needsOr = true;
                }
                if (moreThanOne)
                    sb.append(")");
                // cleanup
                emailProperties.clear();
            }
            // add other fields
            for (String key : otherProperties.keySet()) {
                needsUserPropertiesJoin = checkIntersectionInUserProperties(sb, needsUserPropertiesJoin, params.isUserPropertiesAsIntersectionSearch());
                if (dbVendor.equals("mysql")) {
                    sb.append(" user.").append(key).append(" like :").append(key).append("_value ");
                } else {
                    sb.append(" lower(user.").append(key).append(") like :").append(key).append("_value ");
                }
                if (dbVendor.equals("oracle")) {
                    sb.append(" escape '\\'");
                }
                needsAnd = true;
            }
            // cleanup
            otherProperties.clear();
            // at least one user field used, after this and is required
            needsAnd = true;
        }
        // end of user fields and login part
        if (login != null && (userproperties != null && !userproperties.isEmpty())) {
            sb.append(" ) ");
        }
        // append query for identity primary keys
        if (identityKeys != null && !identityKeys.isEmpty()) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append("ident.key in (:identityKeys)");
        }
        if (managed != null) {
            needsAnd = checkAnd(sb, needsAnd);
            if (managed.booleanValue()) {
                sb.append("ident.externalId is not null");
            } else {
                sb.append("ident.externalId is null");
            }
        }
        // append query for named security groups
        if (hasGroups) {
            SecurityGroup[] groups = params.getGroups();
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" (");
            for (int i = 0; i < groups.length; i++) {
                sb.append(" sgmsi.securityGroup=:group_").append(i);
                if (i < (groups.length - 1))
                    sb.append(" or ");
            }
            sb.append(") ");
            sb.append(" and sgmsi.identity=ident ");
        }
        // append query for policies
        if (hasPermissionOnResources) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" (");
            PermissionOnResourceable[] permissionOnResources = params.getPermissionOnResources();
            for (int i = 0; i < permissionOnResources.length; i++) {
                sb.append(" (");
                sb.append(" policy.permission=:permission_").append(i);
                sb.append(" and policy.olatResource = resource ");
                sb.append(" and resource.resId = :resourceId_").append(i);
                sb.append(" and resource.resName = :resourceName_").append(i);
                sb.append(" ) ");
                if (i < (permissionOnResources.length - 1))
                    sb.append(" or ");
            }
            sb.append(") ");
            sb.append(" and policy.securityGroup=policyGroupMembership.securityGroup ");
            sb.append(" and policyGroupMembership.identity=ident ");
        }
        // append query for authentication providers
        if (hasAuthProviders) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" (");
            String[] authProviders = params.getAuthProviders();
            for (int i = 0; i < authProviders.length; i++) {
                // special case for null auth provider
                if (authProviders[i] == null) {
                    sb.append(" auth is null ");
                } else {
                    sb.append(" auth.provider=:authProvider_").append(i);
                }
                if (i < (authProviders.length - 1))
                    sb.append(" or ");
            }
            sb.append(") ");
        }
        // append query for creation date restrictions
        if (createdAfter != null) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" ident.creationDate >= :createdAfter ");
        }
        if (createdBefore != null) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" ident.creationDate <= :createdBefore ");
        }
        if (params.getUserLoginAfter() != null) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" ident.lastLogin >= :lastloginAfter ");
        }
        if (params.getUserLoginBefore() != null) {
            needsAnd = checkAnd(sb, needsAnd);
            sb.append(" ident.lastLogin <= :lastloginBefore ");
        }
        if (status != null) {
            if (status.equals(Identity.STATUS_VISIBLE_LIMIT)) {
                // search for all status smaller than visible limit
                needsAnd = checkAnd(sb, needsAnd);
                sb.append(" ident.status < :status ");
            } else {
                // search for certain status
                needsAnd = checkAnd(sb, needsAnd);
                sb.append(" ident.status = :status ");
            }
        }
    }
    // create query object now from string
    String query = sb.toString();
    DBQuery dbq = dbInstance.createQuery(query);
    // add user attributes
    if (login != null) {
        dbq.setString("login", login.toLowerCase());
    }
    if (identityKeys != null && !identityKeys.isEmpty()) {
        dbq.setParameterList("identityKeys", identityKeys);
    }
    // add user properties attributes
    if (userproperties != null && !userproperties.isEmpty()) {
        for (String key : userproperties.keySet()) {
            String value = userproperties.get(key);
            value = makeFuzzyQueryString(value);
            dbq.setString(key + "_value", value.toLowerCase());
        }
    }
    // add named security group names
    if (hasGroups) {
        SecurityGroup[] groups = params.getGroups();
        for (int i = 0; i < groups.length; i++) {
            // need to work with impls
            SecurityGroupImpl group = (SecurityGroupImpl) groups[i];
            dbq.setEntity("group_" + i, group);
        }
    }
    // add policies
    if (hasPermissionOnResources) {
        PermissionOnResourceable[] permissionOnResources = params.getPermissionOnResources();
        for (int i = 0; i < permissionOnResources.length; i++) {
            PermissionOnResourceable permissionOnResource = permissionOnResources[i];
            dbq.setString("permission_" + i, permissionOnResource.getPermission());
            Long id = permissionOnResource.getOlatResourceable().getResourceableId();
            dbq.setLong("resourceId_" + i, (id == null ? 0 : id.longValue()));
            dbq.setString("resourceName_" + i, permissionOnResource.getOlatResourceable().getResourceableTypeName());
        }
    }
    // add authentication providers
    if (hasAuthProviders) {
        String[] authProviders = params.getAuthProviders();
        for (int i = 0; i < authProviders.length; i++) {
            String authProvider = authProviders[i];
            if (authProvider != null) {
                dbq.setString("authProvider_" + i, authProvider);
            }
        // ignore null auth provider, already set to null in query
        }
    }
    // add date restrictions
    if (createdAfter != null) {
        dbq.setDate("createdAfter", createdAfter);
    }
    if (createdBefore != null) {
        dbq.setDate("createdBefore", createdBefore);
    }
    if (params.getUserLoginAfter() != null) {
        dbq.setDate("lastloginAfter", params.getUserLoginAfter());
    }
    if (params.getUserLoginBefore() != null) {
        dbq.setDate("lastloginBefore", params.getUserLoginBefore());
    }
    if (status != null) {
        dbq.setInteger("status", status);
    }
    // execute query
    return dbq;
}

Example 12 with DBQuery

use of org.olat.core.commons.persistence.DBQuery in project OpenOLAT by OpenOLAT.

the class EPArtefactManager method getArtefacts.

/**
 * Used by the indexer to retrieve all the artefacts
 * @param artefactIds List of ids to seek (optional)
 * @param firstResult First position
 * @param maxResults Max number of returned artefacts (0 or below for all)
 * @return
 */
@SuppressWarnings("unchecked")
protected List<AbstractArtefact> getArtefacts(Identity author, List<Long> artefactIds, int firstResult, int maxResults) {
    StringBuilder sb = new StringBuilder();
    sb.append("select artefact from ").append(AbstractArtefact.class.getName()).append(" artefact");
    boolean where = false;
    if (author != null) {
        where = true;
        sb.append(" where artefact.author=:author");
    }
    if (artefactIds != null && !artefactIds.isEmpty()) {
        if (where)
            sb.append(" and ");
        else
            sb.append(" where ");
        sb.append(" artefact.id in (:artefactIds)");
    }
    DBQuery query = dbInstance.createQuery(sb.toString());
    if (maxResults > 0) {
        query.setMaxResults(maxResults);
    }
    if (firstResult >= 0) {
        query.setFirstResult(firstResult);
    }
    if (author != null) {
        query.setEntity("author", author);
    }
    if (artefactIds != null && !artefactIds.isEmpty()) {
        query.setParameterList("artefactIds", artefactIds);
    }
    List<AbstractArtefact> artefacts = query.list();
    return artefacts;
}

Example 13 with DBQuery

use of org.olat.core.commons.persistence.DBQuery in project OpenOLAT by OpenOLAT.

the class EPArtefactManager method getArtefactPoolForUser.

protected List<AbstractArtefact> getArtefactPoolForUser(Identity ident) {
    long start = System.currentTimeMillis();
    StringBuilder sb = new StringBuilder();
    sb.append("select artefact from ").append(AbstractArtefact.class.getName()).append(" artefact").append(" where author=:author");
    DBQuery query = dbInstance.createQuery(sb.toString());
    query.setEntity("author", ident);
    @SuppressWarnings("unchecked") List<AbstractArtefact> artefacts = query.list();
    if (artefacts.isEmpty())
        return null;
    long duration = System.currentTimeMillis() - start;
    if (isLogDebugEnabled())
        logDebug("loading the full artefact pool took " + duration + "ms");
    return artefacts;
}

Example 14 with DBQuery

use of org.olat.core.commons.persistence.DBQuery in project OpenOLAT by OpenOLAT.

the class EPArtefactManager method loadArtefactByKey.

/**
 * Load the artefact by its primary key
 *
 * @param key The primary key
 * @return The artefact or null if nothing found
 */
protected AbstractArtefact loadArtefactByKey(Long key) {
    if (key == null)
        throw new NullPointerException();
    StringBuilder sb = new StringBuilder();
    sb.append("select artefact from ").append(AbstractArtefact.class.getName()).append(" artefact").append(" where artefact=:key");
    DBQuery query = dbInstance.createQuery(sb.toString());
    query.setLong("key", key);
    @SuppressWarnings("unchecked") List<AbstractArtefact> artefacts = query.list();
    // if not found, it is an empty list
    if (artefacts.isEmpty())
        return null;
    return artefacts.get(0);
}

Example 15 with DBQuery

use of org.olat.core.commons.persistence.DBQuery in project OpenOLAT by OpenOLAT.

the class EPStructureManager method getAllReferencesForArtefact.

protected List<PortfolioStructure> getAllReferencesForArtefact(AbstractArtefact artefact) {
    StringBuilder sb = new StringBuilder();
    sb.append("select link.structureElement from ").append(EPStructureToArtefactLink.class.getName()).append(" link").append(" where link.artefact=:artefactEl ");
    DBQuery query = dbInstance.createQuery(sb.toString());
    query.setEntity("artefactEl", artefact);
    @SuppressWarnings("unchecked") List<PortfolioStructure> pfList = query.list();
    return pfList;
}