All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.opencms.db.generic.CmsUserQueryBuilder Maven / Gradle / Ivy

Go to download

OpenCms is an enterprise-ready, easy to use website content management system based on Java and XML technology. Offering a complete set of features, OpenCms helps content managers worldwide to create and maintain beautiful websites fast and efficiently.

There is a newer version: 18.0
Show newest version
/*
 * This library is part of OpenCms -
 * the Open Source Content Management System
 *
 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * For further information about Alkacon Software, please see the
 * company website: http://www.alkacon.com
 *
 * For further information about OpenCms, please see the
 * project website: http://www.opencms.org
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package org.opencms.db.generic;

import org.opencms.db.CmsCompositeQueryFragment;
import org.opencms.db.CmsDbUtil;
import org.opencms.db.CmsPagingQuery;
import org.opencms.db.CmsSelectQuery;
import org.opencms.db.CmsSelectQuery.TableAlias;
import org.opencms.db.CmsSimpleQueryFragment;
import org.opencms.db.CmsSqlBooleanClause;
import org.opencms.db.CmsStatementBuilder;
import org.opencms.db.I_CmsQueryFragment;
import org.opencms.file.CmsGroup;
import org.opencms.file.CmsUserSearchParameters;
import org.opencms.file.CmsUserSearchParameters.SearchKey;
import org.opencms.file.CmsUserSearchParameters.SortKey;
import org.opencms.i18n.CmsEncoder;
import org.opencms.security.CmsOrganizationalUnit;
import org.opencms.security.I_CmsPrincipal;
import org.opencms.util.CmsPair;
import org.opencms.util.CmsStringUtil;
import org.opencms.util.CmsUUID;

import java.util.Collection;
import java.util.List;

import com.google.common.base.Joiner;

/**
 * Default implementation of the user query builder.

* * @since 8.0.0 */ public class CmsUserQueryBuilder { /** * Creates a query for searching users.

* * @param searchParams the user search criteria * @param countOnly if true, the query will only count the total number of results instead of returning them * * @return a pair consisting of the query string and its parameters */ public CmsPair> createUserQuery(CmsUserSearchParameters searchParams, boolean countOnly) { CmsSelectQuery select = new CmsSelectQuery(); TableAlias users = select.addTable(tabUsers(), "usr"); if (countOnly) { select.addColumn("COUNT(" + users.column(colId()) + ")"); } else { String[] columns = new String[] { colId(), colName(), colPassword(), colFirstName(), colLastName(), colEmail(), colLastLogin(), colFlags(), colOu(), colDateCreated()}; for (String columnName : columns) { select.addColumn(users.column(columnName)); } } CmsOrganizationalUnit orgUnit = searchParams.getOrganizationalUnit(); boolean recursive = searchParams.recursiveOrgUnits(); if (orgUnit != null) { addOrgUnitCondition(select, users, orgUnit, recursive); } if (searchParams.isFilterCore()) { select.addCondition(createCoreCondition(users)); } addAllowedOuCondition(select, users, searchParams.getAllowedOus()); addFlagCondition(select, users, searchParams.getFlags(), searchParams.keepCoreUsers()); if (orgUnit != null) { addWebuserCondition(select, orgUnit, users); } addSearchFilterCondition(select, users, searchParams); addGroupCondition(select, users, searchParams); if (countOnly) { CmsStatementBuilder builder = new CmsStatementBuilder(); select.visit(builder); return CmsPair.create(builder.getQuery(), builder.getParameters()); } else { addSorting(select, users, searchParams); return makePaged(select, searchParams); } } /** * Adds OU conditions to an SQL query.

* * @param select the query * @param users the user table alias * @param allowedOus the allowed ous */ protected void addAllowedOuCondition( CmsSelectQuery select, TableAlias users, List allowedOus) { if ((allowedOus != null) && !allowedOus.isEmpty()) { CmsCompositeQueryFragment ouCondition = new CmsCompositeQueryFragment(); ouCondition.setPrefix("("); ouCondition.setSuffix(")"); ouCondition.setSeparator(" OR "); for (CmsOrganizationalUnit ou : allowedOus) { String ouName = CmsStringUtil.joinPaths("/", ou.getName()); ouCondition.add(new CmsSimpleQueryFragment(users.column(colOu()) + " = ? ", ouName)); } select.addCondition(ouCondition); } } /** * Adds flag checking conditions to an SQL query.

* * @param select the query * @param users the user table alias * @param flags the flags * @param allowCore set to true if core users should not be filtered out */ protected void addFlagCondition(CmsSelectQuery select, TableAlias users, int flags, boolean allowCore) { if (flags != 0) { I_CmsQueryFragment condition = createFlagCondition(users, flags); if (allowCore) { I_CmsQueryFragment coreCondition = createCoreCondition(users); select.addCondition(CmsSqlBooleanClause.makeOr(condition, coreCondition)); } else { select.addCondition(condition); } } } /** * Adds group conditions to an SQL query.

* * @param select the query * @param users the user table alias * @param searchParams the search parameters */ protected void addGroupCondition(CmsSelectQuery select, TableAlias users, CmsUserSearchParameters searchParams) { CmsGroup group = searchParams.getGroup(); if (group != null) { CmsUUID groupId = group.getId(); TableAlias groupUsers = select.addTable(tabGroupUsers(), "groupusrs"); select.addCondition( new CmsSimpleQueryFragment(groupUsers.column(colGroupUserGroupId()) + " = ? ", groupId.toString())); select.addCondition( new CmsSimpleQueryFragment(groupUsers.column(colGroupUserUserId()) + " = " + users.column(colId()))); if (searchParams.isFilterByGroupOu()) { select.addCondition(new CmsSimpleQueryFragment(users.column(colOu()) + " = ? ", group.getOuFqn())); } } CmsGroup notGroup = searchParams.getNotGroup(); if (notGroup != null) { CmsSimpleQueryFragment notGroupCondition = new CmsSimpleQueryFragment( "NOT EXISTS (SELECT " + getGroupUserSubqueryColumns() + " FROM " + tabGroupUsers() + " GU WHERE GU." + colGroupUserUserId() + " = " + users.column(colId()) + " AND GU." + colGroupUserGroupId() + " = ?)", notGroup.getId().toString()); select.addCondition(notGroupCondition); } Collection anyGroups = searchParams.getAnyGroups(); if ((anyGroups != null) && !anyGroups.isEmpty()) { CmsCompositeQueryFragment groupClause = new CmsCompositeQueryFragment(); groupClause.setSeparator(" OR "); for (CmsGroup grp : anyGroups) { groupClause.add( new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString())); } CmsCompositeQueryFragment existsClause = new CmsCompositeQueryFragment(); existsClause.add( new CmsSimpleQueryFragment( "EXISTS (SELECT " + getGroupUserSubqueryColumns() + " FROM " + tabGroupUsers() + " GU WHERE GU." + colGroupUserUserId() + " = " + users.column(colId()) + " AND ")); existsClause.add(groupClause); existsClause.add(new CmsSimpleQueryFragment(" ) ")); select.addCondition(existsClause); } Collection notAnyGroups = searchParams.getNotAnyGroups(); if ((notAnyGroups != null) && (!notAnyGroups.isEmpty())) { CmsCompositeQueryFragment groupClause = new CmsCompositeQueryFragment(); groupClause.setPrefix("("); groupClause.setSuffix(")"); groupClause.setSeparator(" OR "); for (CmsGroup grp : notAnyGroups) { groupClause.add( new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString())); } CmsCompositeQueryFragment notExistsClause = new CmsCompositeQueryFragment(); notExistsClause.add( new CmsSimpleQueryFragment( "NOT EXISTS (SELECT " + getGroupUserSubqueryColumns() + " FROM " + tabGroupUsers() + " GU WHERE GU." + colGroupUserUserId() + " = " + users.column(colId()) + " AND ")); notExistsClause.add(groupClause); notExistsClause.add(new CmsSimpleQueryFragment(" ) ")); select.addCondition(notExistsClause); } } /** * Adds a check for an OU to an SQL query.

* * @param select the query * @param users the user table alias * @param orgUnit the organizational unit * @param recursive if true, checks for sub-OUs too */ protected void addOrgUnitCondition( CmsSelectQuery select, TableAlias users, CmsOrganizationalUnit orgUnit, boolean recursive) { String ouName = orgUnit.getName(); String pattern = CmsOrganizationalUnit.SEPARATOR + ouName; if (recursive) { pattern += "%"; } select.addCondition(CmsDbUtil.columnLike(users.column(colOu()), pattern)); } /** * Adds a search condition to a query.

* * @param select the query * @param users the user table alias * @param searchParams the search criteria */ protected void addSearchFilterCondition( CmsSelectQuery select, TableAlias users, CmsUserSearchParameters searchParams) { String searchFilter = searchParams.getSearchFilter(); if (!CmsStringUtil.isEmptyOrWhitespaceOnly(searchFilter)) { boolean caseInsensitive = !searchParams.isCaseSensitive(); if (caseInsensitive) { searchFilter = searchFilter.toLowerCase(); } CmsCompositeQueryFragment searchCondition = new CmsCompositeQueryFragment(); searchCondition.setSeparator(" OR "); searchCondition.setPrefix("("); searchCondition.setSuffix(")"); //use coalesce in case any of the name columns are null String patternExprTemplate = generateConcat( "COALESCE(%1$s, '')", "' '", "COALESCE(%2$s, '')", "' '", "COALESCE(%3$s, '')"); patternExprTemplate = wrapLower(patternExprTemplate, caseInsensitive); String patternExpr = String.format( patternExprTemplate, users.column(colName()), users.column(colFirstName()), users.column(colLastName())); String like = " LIKE ? ESCAPE '!' "; String matchExpr = patternExpr + like; searchFilter = "%" + CmsEncoder.escapeSqlLikePattern(searchFilter, '!') + '%'; searchCondition.add(new CmsSimpleQueryFragment(matchExpr, searchFilter)); for (SearchKey key : searchParams.getSearchKeys()) { switch (key) { case email: searchCondition.add( new CmsSimpleQueryFragment( wrapLower(users.column(colEmail()), caseInsensitive) + like, searchFilter)); break; case orgUnit: searchCondition.add(new CmsSimpleQueryFragment( wrapLower(users.column(colOu()), caseInsensitive) + like, searchFilter)); break; default: break; } } select.addCondition(searchCondition); } } /** * Adds a sort order to an SQL query.

* * @param select the query * @param users the user table alias * @param searchParams the user search criteria */ protected void addSorting(CmsSelectQuery select, TableAlias users, CmsUserSearchParameters searchParams) { boolean ascending = searchParams.isAscending(); String ordering = getSortExpression(users, searchParams); if (ascending) { ordering += " ASC"; } else { ordering += " DESC"; } select.setOrdering(ordering); } /** * Adds a check for the web user condition to an SQL query.

* * @param select the query * @param orgUnit the organizational unit * @param users the user table alias */ protected void addWebuserCondition(CmsSelectQuery select, CmsOrganizationalUnit orgUnit, TableAlias users) { String webuserConditionTemplate; if (orgUnit.hasFlagWebuser()) { webuserConditionTemplate = "( %1$s >= 32768 AND %1$s < 65536 )"; } else { webuserConditionTemplate = "( %1$s < 32768 OR %1$s >= 65536 )"; } String webuserCondition = String.format(webuserConditionTemplate, users.column(colFlags())); select.addCondition(webuserCondition); } /** * Column name accessor.

* * @return the name of the column */ protected String colDateCreated() { return "USER_DATECREATED"; } /** * Column name accessor.

* * @return the name of the column */ protected String colEmail() { return "USER_EMAIL"; } /** * Column name accessor.

* * @return the name of the column */ protected String colFirstName() { return "USER_FIRSTNAME"; } /** * Column name accessor.

* * @return the name of the column */ protected String colFlags() { return "USER_FLAGS"; } /** * Column name accessor.

* * @return the name of the column */ protected String colGroupUserGroupId() { return "GROUP_ID"; } /** * Column name accessor.

* * @return the name of the column */ protected String colGroupUserUserId() { return "USER_ID"; } /** * Column name accessor.

* * @return the name of the column */ protected String colId() { return "USER_ID"; } /** * Column name accessor.

* * @return the name of the column */ protected String colLastLogin() { return "USER_LASTLOGIN"; } /** * Column name accessor.

* * @return the name of the column */ protected String colLastName() { return "USER_LASTNAME"; } /** * Column name accessor.

* * @return the name of the column */ protected String colName() { return "USER_NAME"; } /** * Column name accessor.

* * @return the name of the column */ protected String colOu() { return "USER_OU"; } /** * Column name accessor.

* * @return the name of the column */ protected String colPassword() { return "USER_PASSWORD"; } /** * Creates a core user check condition.

* * @param users the user table alias * * @return the resulting SQL expression */ protected I_CmsQueryFragment createCoreCondition(TableAlias users) { return new CmsSimpleQueryFragment(users.column(colFlags()) + " <= " + I_CmsPrincipal.FLAG_CORE_LIMIT); } /** * Creates an SQL flag check condition.

* * @param users the user table alias * @param flags the flags to check * * @return the resulting SQL expression */ protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) { return new CmsSimpleQueryFragment( users.column(colFlags()) + " & ? = ? ", new Integer(flags), new Integer(flags)); } /** * Generates an SQL expression for concatenating several other SQL expressions.

* * @param expressions the expressions to concatenate * * @return the concat expression */ protected String generateConcat(String... expressions) { return "CONCAT(" + Joiner.on(", ").join(expressions) + ")"; } /** * Generates an SQL expression for trimming whitespace from the beginning and end of a string.

* * @param expression the expression to wrap * * @return the expression for trimming the given expression */ protected String generateTrim(String expression) { return "TRIM(" + expression + ")"; } /** * Returns the columns that should be returned by user subqueries.

* * @return the columns that should be returned by user subqueries */ protected String getGroupUserSubqueryColumns() { return "*"; } /** * Returns the expression used for sorting the results.

* * @param users the user table alias * @param searchParams the search parameters * * @return the sorting expressiong */ protected String getSortExpression(TableAlias users, CmsUserSearchParameters searchParams) { SortKey sortKey = searchParams.getSortKey(); String ordering = users.column(colId()); if (sortKey != null) { switch (sortKey) { case email: ordering = users.column(colEmail()); break; case loginName: ordering = users.column(colName()); break; case fullName: ordering = getUserFullNameExpression(users); break; case lastLogin: ordering = users.column(colLastLogin()); break; case orgUnit: ordering = users.column(colOu()); break; case activated: ordering = getUserActivatedExpression(users); break; case flagStatus: ordering = getUserFlagExpression(users, searchParams.getSortFlags()); break; default: break; } } return ordering; } /** * Returns an expression for checking whether a user is activated.

* * @param users the user table alias * * @return the expression for checking whether the user is activated */ protected String getUserActivatedExpression(TableAlias users) { return "MOD(" + users.column(colFlags()) + ", 2)"; } /** * Returns a bitwise AND expression with a fixed second operand.

* * @param users the user table alias * @param flags the user flags * @return the resulting SQL expression */ protected String getUserFlagExpression(TableAlias users, int flags) { return users.column(colFlags()) + " & " + flags; } /** * Returns the SQL expression for generating the user's full name in the format * 'firstname lastname (loginname)'.

* * @param users the user table alias * * @return the expression for generating the user's full name */ protected String getUserFullNameExpression(TableAlias users) { //use coalesce in case any of the name columns are null String template = generateTrim( generateConcat("COALESCE(%1$s, '')", "' '", "COALESCE(%2$s, '')", "' ('", "%3$s", "')'")); return String.format( template, users.column(colFirstName()), users.column(colLastName()), users.column(colName())); } /** * Creates a query which uses paging from another query.

* * @param select the base query * @param params the query parameters * * @return the paged version of the query */ protected CmsPair> makePaged(CmsSelectQuery select, CmsUserSearchParameters params) { CmsPagingQuery paging = new CmsPagingQuery(select); paging.setUseWindowFunctions(useWindowFunctionsForPaging()); int page = params.getPage(); int pageSize = params.getPageSize(); paging.setNameSubquery(shouldNameSubqueries()); paging.setPaging(pageSize, page); CmsStatementBuilder builder = new CmsStatementBuilder(); paging.visit(builder); return CmsPair.create(builder.getQuery(), builder.getParameters()); } /** * Should return true if subqueries in a FROM clause should be named.

* * @return true if subqueries in a FROM clause should be named */ protected boolean shouldNameSubqueries() { return false; } /** * Table name accessor.

* * @return the name of a table */ protected String tabGroups() { return "CMS_GROUPS"; } /** * Table name accessor.

* * @return the name of a table */ protected String tabGroupUsers() { return "CMS_GROUPUSERS"; } /** * Table name accessor.

* * @return the name of a table */ protected String tabUsers() { return "CMS_USERS"; } /** * Returns true if window functions should be used for paging.

* * @return true if window functions should be used for paging */ protected boolean useWindowFunctionsForPaging() { return false; } /** * Wraps an SQL expression in a "LOWER" call conditionally.

* * @param expr the expression to wrap * @param caseInsensitive if false, no wrapping should occur * * @return the resulting expression */ protected String wrapLower(String expr, boolean caseInsensitive) { return caseInsensitive ? "LOWER(" + expr + ")" : expr; } }





© 2015 - 2024 Weber Informatics LLC | Privacy Policy