001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
006 *
007 * This library is free software; you can redistribute it and/or
008 * modify it under the terms of the GNU Lesser General Public
009 * License as published by the Free Software Foundation; either
010 * version 2.1 of the License, or (at your option) any later version.
011 *
012 * This library is distributed in the hope that it will be useful,
013 * but WITHOUT ANY WARRANTY; without even the implied warranty of
014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015 * Lesser General Public License for more details.
016 *
017 * For further information about Alkacon Software, please see the
018 * company website: http://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: http://www.opencms.org
022 *
023 * You should have received a copy of the GNU Lesser General Public
024 * License along with this library; if not, write to the Free Software
025 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
026 */
027
028package org.opencms.db.generic;
029
030import org.opencms.db.CmsCompositeQueryFragment;
031import org.opencms.db.CmsDbUtil;
032import org.opencms.db.CmsPagingQuery;
033import org.opencms.db.CmsSelectQuery;
034import org.opencms.db.CmsSelectQuery.TableAlias;
035import org.opencms.db.CmsSimpleQueryFragment;
036import org.opencms.db.CmsSqlBooleanClause;
037import org.opencms.db.CmsStatementBuilder;
038import org.opencms.db.I_CmsQueryFragment;
039import org.opencms.file.CmsGroup;
040import org.opencms.file.CmsUserSearchParameters;
041import org.opencms.file.CmsUserSearchParameters.SearchKey;
042import org.opencms.file.CmsUserSearchParameters.SortKey;
043import org.opencms.i18n.CmsEncoder;
044import org.opencms.security.CmsOrganizationalUnit;
045import org.opencms.security.I_CmsPrincipal;
046import org.opencms.util.CmsPair;
047import org.opencms.util.CmsStringUtil;
048import org.opencms.util.CmsUUID;
049
050import java.util.Collection;
051import java.util.List;
052
053import com.google.common.base.Joiner;
054
055/**
056 * Default implementation of the user query builder.<p>
057 *
058 * @since 8.0.0
059 */
060public class CmsUserQueryBuilder {
061
062    /**
063     * Creates a query for searching users.<p>
064     *
065     * @param searchParams the user search criteria
066     * @param countOnly if true, the query will only count the total number of results instead of returning them
067     *
068     * @return a pair consisting of the query string and its parameters
069     */
070    public CmsPair<String, List<Object>> createUserQuery(CmsUserSearchParameters searchParams, boolean countOnly) {
071
072        CmsSelectQuery select = new CmsSelectQuery();
073        TableAlias users = select.addTable(tabUsers(), "usr");
074        if (countOnly) {
075            select.addColumn("COUNT(" + users.column(colId()) + ")");
076        } else {
077            String[] columns = new String[] {
078                colId(),
079                colName(),
080                colPassword(),
081                colFirstName(),
082                colLastName(),
083                colEmail(),
084                colLastLogin(),
085                colFlags(),
086                colOu(),
087                colDateCreated()};
088            for (String columnName : columns) {
089                select.addColumn(users.column(columnName));
090            }
091        }
092        CmsOrganizationalUnit orgUnit = searchParams.getOrganizationalUnit();
093        boolean recursive = searchParams.recursiveOrgUnits();
094
095        if (orgUnit != null) {
096            addOrgUnitCondition(select, users, orgUnit, recursive);
097        }
098        if (searchParams.isFilterCore()) {
099            select.addCondition(createCoreCondition(users));
100        }
101        addEmailCondition(select, users, searchParams.getEmail());
102        addAllowedOuCondition(select, users, searchParams.getAllowedOus());
103        addFlagCondition(select, users, searchParams.getFlags(), searchParams.keepCoreUsers());
104        if (orgUnit != null) {
105            addWebuserCondition(select, orgUnit, users);
106        }
107        addSearchFilterCondition(select, users, searchParams);
108        addGroupCondition(select, users, searchParams);
109        if (countOnly) {
110            CmsStatementBuilder builder = new CmsStatementBuilder();
111            select.visit(builder);
112            return CmsPair.create(builder.getQuery(), builder.getParameters());
113        } else {
114            addSorting(select, users, searchParams);
115            return makePaged(select, searchParams);
116        }
117    }
118
119    /**
120     * Adds OU conditions to an SQL query.<p>
121     *
122     * @param select the query
123     * @param users the user table alias
124     * @param allowedOus the allowed ous
125     */
126    protected void addAllowedOuCondition(
127        CmsSelectQuery select,
128        TableAlias users,
129        List<CmsOrganizationalUnit> allowedOus) {
130
131        if ((allowedOus != null) && !allowedOus.isEmpty()) {
132            CmsCompositeQueryFragment ouCondition = new CmsCompositeQueryFragment();
133            ouCondition.setPrefix("(");
134            ouCondition.setSuffix(")");
135            ouCondition.setSeparator(" OR ");
136            for (CmsOrganizationalUnit ou : allowedOus) {
137                String ouName = CmsStringUtil.joinPaths("/", ou.getName());
138                ouCondition.add(new CmsSimpleQueryFragment(users.column(colOu()) + " = ? ", ouName));
139            }
140            select.addCondition(ouCondition);
141        }
142    }
143
144    /**
145     * Adds an equality test for the email address.
146     *
147     * If the email address is null, no test is added.
148     *
149     * @param select the select statement to add the test to
150     * @param users the user table alias
151     * @param email the email address to compare the column to
152     */
153    protected void addEmailCondition(CmsSelectQuery select, TableAlias users, String email) {
154
155        if (email != null) {
156            CmsSimpleQueryFragment condition = new CmsSimpleQueryFragment(users.column(colEmail()) + " = ? ", email);
157            select.addCondition(condition);
158        }
159    }
160
161    /**
162     * Adds flag checking conditions to an SQL query.<p>
163     *
164     * @param select the query
165     * @param users the user table alias
166     * @param flags the flags
167     * @param allowCore set to true if core users should not be filtered out
168     */
169    protected void addFlagCondition(CmsSelectQuery select, TableAlias users, int flags, boolean allowCore) {
170
171        if (flags != 0) {
172            I_CmsQueryFragment condition = createFlagCondition(users, flags);
173            if (allowCore) {
174                I_CmsQueryFragment coreCondition = createCoreCondition(users);
175                select.addCondition(CmsSqlBooleanClause.makeOr(condition, coreCondition));
176            } else {
177                select.addCondition(condition);
178            }
179        }
180    }
181
182    /**
183     * Adds group conditions to an SQL query.<p>
184     *
185     * @param select the query
186     * @param users the user table alias
187     * @param searchParams the search parameters
188     */
189    protected void addGroupCondition(CmsSelectQuery select, TableAlias users, CmsUserSearchParameters searchParams) {
190
191        CmsGroup group = searchParams.getGroup();
192        if (group != null) {
193            CmsUUID groupId = group.getId();
194            TableAlias groupUsers = select.addTable(tabGroupUsers(), "groupusrs");
195            select.addCondition(
196                new CmsSimpleQueryFragment(groupUsers.column(colGroupUserGroupId()) + " = ? ", groupId.toString()));
197            select.addCondition(
198                new CmsSimpleQueryFragment(groupUsers.column(colGroupUserUserId()) + " = " + users.column(colId())));
199            if (searchParams.isFilterByGroupOu()) {
200                select.addCondition(new CmsSimpleQueryFragment(users.column(colOu()) + " = ? ", group.getOuFqn()));
201            }
202        }
203        CmsGroup notGroup = searchParams.getNotGroup();
204        if (notGroup != null) {
205            CmsSimpleQueryFragment notGroupCondition = new CmsSimpleQueryFragment(
206                "NOT EXISTS (SELECT "
207                    + getGroupUserSubqueryColumns()
208                    + " FROM "
209                    + tabGroupUsers()
210                    + " GU WHERE GU."
211                    + colGroupUserUserId()
212                    + " = "
213                    + users.column(colId())
214                    + " AND GU."
215                    + colGroupUserGroupId()
216                    + " = ?)",
217                notGroup.getId().toString());
218            select.addCondition(notGroupCondition);
219        }
220
221        Collection<CmsGroup> anyGroups = searchParams.getAnyGroups();
222        if ((anyGroups != null) && !anyGroups.isEmpty()) {
223            CmsCompositeQueryFragment groupClause = new CmsCompositeQueryFragment();
224            groupClause.setSeparator(" OR ");
225            groupClause.setPrefix("(");
226            groupClause.setSuffix(")");
227            for (CmsGroup grp : anyGroups) {
228                groupClause.add(
229                    new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString()));
230            }
231            CmsCompositeQueryFragment existsClause = new CmsCompositeQueryFragment();
232            existsClause.add(
233                new CmsSimpleQueryFragment(
234                    "EXISTS (SELECT "
235                        + getGroupUserSubqueryColumns()
236                        + " FROM "
237                        + tabGroupUsers()
238                        + " GU WHERE GU."
239                        + colGroupUserUserId()
240                        + " = "
241                        + users.column(colId())
242                        + " AND "));
243            existsClause.add(groupClause);
244            existsClause.add(new CmsSimpleQueryFragment(" ) "));
245            select.addCondition(existsClause);
246        }
247        Collection<CmsGroup> notAnyGroups = searchParams.getNotAnyGroups();
248        if ((notAnyGroups != null) && (!notAnyGroups.isEmpty())) {
249            CmsCompositeQueryFragment groupClause = new CmsCompositeQueryFragment();
250            groupClause.setPrefix("(");
251            groupClause.setSuffix(")");
252            groupClause.setSeparator(" OR ");
253            for (CmsGroup grp : notAnyGroups) {
254                groupClause.add(
255                    new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString()));
256            }
257            CmsCompositeQueryFragment notExistsClause = new CmsCompositeQueryFragment();
258            notExistsClause.add(
259                new CmsSimpleQueryFragment(
260                    "NOT EXISTS (SELECT "
261                        + getGroupUserSubqueryColumns()
262                        + " FROM "
263                        + tabGroupUsers()
264                        + " GU WHERE GU."
265                        + colGroupUserUserId()
266                        + " = "
267                        + users.column(colId())
268                        + " AND "));
269            notExistsClause.add(groupClause);
270            notExistsClause.add(new CmsSimpleQueryFragment(" ) "));
271            select.addCondition(notExistsClause);
272        }
273    }
274
275    /**
276     * Adds a check for an OU to an SQL query.<p>
277     *
278     * @param select the query
279     * @param users the user table alias
280     * @param orgUnit the organizational unit
281     * @param recursive if true, checks for sub-OUs too
282     */
283    protected void addOrgUnitCondition(
284        CmsSelectQuery select,
285        TableAlias users,
286        CmsOrganizationalUnit orgUnit,
287        boolean recursive) {
288
289        String ouName = orgUnit.getName();
290        String pattern = CmsOrganizationalUnit.SEPARATOR + ouName;
291        if (recursive) {
292            pattern += "%";
293        }
294        select.addCondition(CmsDbUtil.columnLike(users.column(colOu()), pattern));
295    }
296
297    /**
298     * Adds a search condition to a query.<p>
299     *
300     * @param select the query
301     * @param users the user table alias
302     * @param searchParams the search criteria
303     */
304    protected void addSearchFilterCondition(
305        CmsSelectQuery select,
306        TableAlias users,
307        CmsUserSearchParameters searchParams) {
308
309        String searchFilter = searchParams.getSearchFilter();
310        if (!CmsStringUtil.isEmptyOrWhitespaceOnly(searchFilter)) {
311            boolean caseInsensitive = !searchParams.isCaseSensitive();
312            if (caseInsensitive) {
313                searchFilter = searchFilter.toLowerCase();
314            }
315            CmsCompositeQueryFragment searchCondition = new CmsCompositeQueryFragment();
316            searchCondition.setSeparator(" OR ");
317            searchCondition.setPrefix("(");
318            searchCondition.setSuffix(")");
319            //use coalesce in case any of the name columns are null
320            String patternExprTemplate = generateConcat(
321                "COALESCE(%1$s, '')",
322                "' '",
323                "COALESCE(%2$s, '')",
324                "' '",
325                "COALESCE(%3$s, '')");
326            patternExprTemplate = wrapLower(patternExprTemplate, caseInsensitive);
327
328            String patternExpr = String.format(
329                patternExprTemplate,
330                users.column(colName()),
331                users.column(colFirstName()),
332                users.column(colLastName()));
333            String like = " LIKE ? ESCAPE '!' ";
334            String matchExpr = patternExpr + like;
335            searchFilter = "%" + CmsEncoder.escapeSqlLikePattern(searchFilter, '!') + '%';
336            searchCondition.add(new CmsSimpleQueryFragment(matchExpr, searchFilter));
337            for (SearchKey key : searchParams.getSearchKeys()) {
338                switch (key) {
339                    case email:
340                        searchCondition.add(
341                            new CmsSimpleQueryFragment(
342                                wrapLower(users.column(colEmail()), caseInsensitive) + like,
343                                searchFilter));
344                        break;
345                    case orgUnit:
346                        searchCondition.add(
347                            new CmsSimpleQueryFragment(
348                                wrapLower(users.column(colOu()), caseInsensitive) + like,
349                                searchFilter));
350                        break;
351                    default:
352                        break;
353                }
354            }
355            select.addCondition(searchCondition);
356        }
357    }
358
359    /**
360     * Adds a sort order to an SQL query.<p>
361     *
362     * @param select the query
363     * @param users the user table alias
364     * @param searchParams the user search criteria
365     */
366    protected void addSorting(CmsSelectQuery select, TableAlias users, CmsUserSearchParameters searchParams) {
367
368        boolean ascending = searchParams.isAscending();
369        String ordering = getSortExpression(users, searchParams);
370        if (ascending) {
371            ordering += " ASC";
372        } else {
373            ordering += " DESC";
374        }
375
376        select.setOrdering(ordering);
377    }
378
379    /**
380     * Adds a check for the web user condition to an SQL query.<p>
381     *
382     * @param select the query
383     * @param orgUnit the organizational unit
384     * @param users the user table alias
385     */
386    protected void addWebuserCondition(CmsSelectQuery select, CmsOrganizationalUnit orgUnit, TableAlias users) {
387
388        String webuserConditionTemplate;
389        if (orgUnit.hasFlagWebuser()) {
390            webuserConditionTemplate = "( %1$s >= 32768 AND %1$s < 65536 )";
391        } else {
392            webuserConditionTemplate = "( %1$s < 32768 OR %1$s >= 65536 )";
393        }
394        String webuserCondition = String.format(webuserConditionTemplate, users.column(colFlags()));
395        select.addCondition(webuserCondition);
396    }
397
398    /**
399     * Column name accessor.<p>
400     *
401     * @return the name of the column
402     */
403    protected String colDateCreated() {
404
405        return "USER_DATECREATED";
406    }
407
408    /**
409     * Column name accessor.<p>
410     *
411     * @return the name of the column
412     */
413    protected String colEmail() {
414
415        return "USER_EMAIL";
416    }
417
418    /**
419     * Column name accessor.<p>
420     *
421     * @return the name of the column
422     */
423    protected String colFirstName() {
424
425        return "USER_FIRSTNAME";
426    }
427
428    /**
429     * Column name accessor.<p>
430     *
431     * @return the name of the column
432     */
433    protected String colFlags() {
434
435        return "USER_FLAGS";
436    }
437
438    /**
439     * Column name accessor.<p>
440     *
441     * @return the name of the column
442     */
443    protected String colGroupUserGroupId() {
444
445        return "GROUP_ID";
446    }
447
448    /**
449     * Column name accessor.<p>
450     *
451     * @return the name of the column
452     */
453    protected String colGroupUserUserId() {
454
455        return "USER_ID";
456    }
457
458    /**
459     * Column name accessor.<p>
460     *
461     * @return the name of the column
462     */
463    protected String colId() {
464
465        return "USER_ID";
466    }
467
468    /**
469     * Column name accessor.<p>
470     *
471     * @return the name of the column
472     */
473    protected String colLastLogin() {
474
475        return "USER_LASTLOGIN";
476    }
477
478    /**
479     * Column name accessor.<p>
480     *
481     * @return the name of the column
482     */
483    protected String colLastName() {
484
485        return "USER_LASTNAME";
486    }
487
488    /**
489     * Column name accessor.<p>
490     *
491     * @return the name of the column
492     */
493    protected String colName() {
494
495        return "USER_NAME";
496    }
497
498    /**
499     * Column name accessor.<p>
500     *
501     * @return the name of the column
502     */
503    protected String colOu() {
504
505        return "USER_OU";
506    }
507
508    /**
509     * Column name accessor.<p>
510     *
511     * @return the name of the column
512     */
513    protected String colPassword() {
514
515        return "USER_PASSWORD";
516    }
517
518    /**
519     * Creates a core user check condition.<p>
520     *
521     * @param users the user table alias
522     *
523     * @return the resulting SQL expression
524     */
525    protected I_CmsQueryFragment createCoreCondition(TableAlias users) {
526
527        return new CmsSimpleQueryFragment(users.column(colFlags()) + " <= " + I_CmsPrincipal.FLAG_CORE_LIMIT);
528    }
529
530    /**
531     * Creates an SQL flag check condition.<p>
532     *
533     * @param users the user table alias
534     * @param flags the flags to check
535     *
536     * @return the resulting SQL expression
537     */
538    protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) {
539
540        return new CmsSimpleQueryFragment(
541            users.column(colFlags()) + " & ? = ? ",
542            Integer.valueOf(flags),
543            Integer.valueOf(flags));
544    }
545
546    /**
547     * Generates an SQL expression for concatenating several other SQL expressions.<p>
548     *
549     * @param expressions the expressions to concatenate
550     *
551     * @return the concat expression
552     */
553    protected String generateConcat(String... expressions) {
554
555        return "CONCAT(" + Joiner.on(", ").join(expressions) + ")";
556    }
557
558    /**
559     * Generates an SQL expression for trimming whitespace from the beginning and end of a string.<p>
560     *
561     * @param expression the expression to wrap
562     *
563     * @return the expression for trimming the given expression
564     */
565    protected String generateTrim(String expression) {
566
567        return "TRIM(" + expression + ")";
568    }
569
570    /**
571     * Returns the columns that should be returned by  user subqueries.<p>
572     *
573     * @return the columns that should be returned by user subqueries
574     */
575    protected String getGroupUserSubqueryColumns() {
576
577        return "*";
578    }
579
580    /**
581     * Returns the expression used for sorting the results.<p>
582     *
583     * @param users the user table alias
584     * @param searchParams the search parameters
585     *
586     * @return the sorting expressiong
587     */
588    protected String getSortExpression(TableAlias users, CmsUserSearchParameters searchParams) {
589
590        SortKey sortKey = searchParams.getSortKey();
591        String ordering = users.column(colId());
592        if (sortKey != null) {
593            switch (sortKey) {
594                case email:
595                    ordering = users.column(colEmail());
596                    break;
597                case loginName:
598                    ordering = users.column(colName());
599                    break;
600                case fullName:
601                    ordering = getUserFullNameExpression(users);
602                    break;
603                case lastLogin:
604                    ordering = users.column(colLastLogin());
605                    break;
606                case orgUnit:
607                    ordering = users.column(colOu());
608                    break;
609                case activated:
610                    ordering = getUserActivatedExpression(users);
611                    break;
612                case flagStatus:
613                    ordering = getUserFlagExpression(users, searchParams.getSortFlags());
614                    break;
615                default:
616                    break;
617
618            }
619        }
620        return ordering;
621    }
622
623    /**
624     * Returns an expression for checking whether a user is activated.<p>
625     *
626     * @param users the user table alias
627     *
628     * @return the expression for checking whether the user is activated
629     */
630    protected String getUserActivatedExpression(TableAlias users) {
631
632        return "MOD(" + users.column(colFlags()) + ", 2)";
633    }
634
635    /**
636     * Returns a bitwise AND expression with a fixed second operand.<p>
637     *
638     * @param users the user table alias
639     * @param flags the user flags
640     * @return the resulting SQL expression
641     */
642    protected String getUserFlagExpression(TableAlias users, int flags) {
643
644        return users.column(colFlags()) + " & " + flags;
645
646    }
647
648    /**
649     * Returns the SQL expression for generating the user's full name in the format
650     * 'firstname lastname (loginname)'.<p>
651     *
652     * @param users the user table alias
653     *
654     * @return the expression for generating the user's full name
655     */
656    protected String getUserFullNameExpression(TableAlias users) {
657
658        //use coalesce in case any of the name columns are null
659        String template = generateTrim(
660            generateConcat("COALESCE(%1$s, '')", "' '", "COALESCE(%2$s, '')", "' ('", "%3$s", "')'"));
661        return String.format(
662            template,
663            users.column(colFirstName()),
664            users.column(colLastName()),
665            users.column(colName()));
666    }
667
668    /**
669     * Creates a query which uses paging from another query.<p>
670     *
671     * @param select the base query
672     * @param params the query parameters
673     *
674     * @return the paged version of the query
675     */
676    protected CmsPair<String, List<Object>> makePaged(CmsSelectQuery select, CmsUserSearchParameters params) {
677
678        CmsPagingQuery paging = new CmsPagingQuery(select);
679        paging.setUseWindowFunctions(useWindowFunctionsForPaging());
680        int page = params.getPage();
681        int pageSize = params.getPageSize();
682        paging.setNameSubquery(shouldNameSubqueries());
683        paging.setPaging(pageSize, page);
684        CmsStatementBuilder builder = new CmsStatementBuilder();
685        paging.visit(builder);
686        return CmsPair.create(builder.getQuery(), builder.getParameters());
687    }
688
689    /**
690     * Should return true if subqueries in a FROM clause should be named.<p>
691     *
692     * @return true if subqueries in a FROM clause should be named
693     */
694    protected boolean shouldNameSubqueries() {
695
696        return false;
697    }
698
699    /**
700     * Table name accessor.<p>
701     *
702     * @return the name of a table
703     */
704    protected String tabGroups() {
705
706        return "CMS_GROUPS";
707    }
708
709    /**
710     * Table name accessor.<p>
711     *
712     * @return the name of a table
713     */
714    protected String tabGroupUsers() {
715
716        return "CMS_GROUPUSERS";
717    }
718
719    /**
720     * Table name accessor.<p>
721     *
722     * @return the name of a table
723     */
724    protected String tabUsers() {
725
726        return "CMS_USERS";
727    }
728
729    /**
730     * Returns true if window functions should be used for paging.<p>
731     *
732     * @return true if window functions should be used for paging
733     */
734    protected boolean useWindowFunctionsForPaging() {
735
736        return false;
737    }
738
739    /**
740     * Wraps an SQL expression in a "LOWER" call conditionally.<p>
741     *
742     * @param expr the expression to wrap
743     * @param caseInsensitive if false, no wrapping should occur
744     *
745     * @return the resulting expression
746     */
747    protected String wrapLower(String expr, boolean caseInsensitive) {
748
749        return caseInsensitive ? "LOWER(" + expr + ")" : expr;
750    }
751
752}