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

Example 41 with DBQuery

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

the class TaggingManagerImpl method getTagsAsString.

@Override
public List<String> getTagsAsString(Identity identity, OLATResourceable ores, String subPath, String businessPath) {
    if (ores.getResourceableId() == null || ores.getResourceableTypeName() == null) {
        // this ores seems not yet to be persisted, therefore has no key and as a result no tags!
        return null;
    }
    StringBuilder sb = new StringBuilder();
    sb.append("select tag.tag from ").append(TagImpl.class.getName()).append(" tag where tag.resId=:resId and tag.resName=:resName");
    if (subPath != null) {
        sb.append(" and tag.subPath=:subPath");
    }
    if (identity != null) {
        sb.append(" and tag.author=:author");
    }
    if (businessPath != null) {
        sb.append(" and tag.businessPath=:businessPath");
    }
    sb.append(" group by tag.tag");
    DBQuery query = dbInstance.createQuery(sb.toString());
    query.setLong("resId", ores.getResourceableId());
    query.setString("resName", ores.getResourceableTypeName());
    if (subPath != null) {
        query.setString("subPath", subPath);
    }
    if (identity != null) {
        query.setEntity("author", identity);
    }
    if (businessPath != null) {
        query.setString("businessPath", businessPath);
    }
    @SuppressWarnings("unchecked") List<String> tags = query.list();
    return tags;
}

Example 42 with DBQuery

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

the class TaggingManagerImpl method getUserTagsAsString.

@Override
public List<String> getUserTagsAsString(Identity identity) {
    if (identity == null)
        return Collections.emptyList();
    StringBuilder sb = new StringBuilder();
    sb.append("select tag.tag from ").append(TagImpl.class.getName()).append(" tag where tag.author=:author").append(" group by tag.tag").append(" order by count(tag.key) DESC");
    DBQuery query = dbInstance.createQuery(sb.toString());
    query.setEntity("author", identity);
    @SuppressWarnings("unchecked") List<String> tags = query.list();
    return tags;
}

Example 43 with DBQuery

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

the class TaggingManagerImpl method getUserTagsWithFrequency.

@Override
public List<Map<String, Integer>> getUserTagsWithFrequency(Identity identity) {
    if (identity == null)
        return null;
    StringBuilder sb = new StringBuilder();
    sb.append("select new map ( tag.tag as tag, count(*) as nr ) from ").append(TagImpl.class.getName()).append(" tag where tag.author=:author and tag.tag=tag.tag ").append("Group by tag.tag order by count(*) DESC, tag.tag ASC");
    DBQuery query = dbInstance.createQuery(sb.toString());
    query.setEntity("author", identity);
    @SuppressWarnings("unchecked") List<Map<String, Integer>> tags = query.list();
    return tags;
}

Example 44 with DBQuery

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

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 45 with DBQuery

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

the class BaseSecurityManager method countIdentitiesByPowerSearch.

@Override
public long countIdentitiesByPowerSearch(String login, Map<String, String> userproperties, boolean userPropertiesAsIntersectionSearch, SecurityGroup[] groups, PermissionOnResourceable[] permissionOnResources, String[] authProviders, Date createdAfter, Date createdBefore, Date userLoginAfter, Date userLoginBefore, Integer status) {
    DBQuery dbq = createIdentitiesByPowerQuery(new SearchIdentityParams(login, userproperties, userPropertiesAsIntersectionSearch, groups, permissionOnResources, authProviders, createdAfter, createdBefore, userLoginAfter, userLoginBefore, status), true);
    Number count = (Number) dbq.uniqueResult();
    return count.longValue();
}