001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.upgrade.v6_0_3;
016    
017    import com.liferay.portal.kernel.model.RoleConstants;
018    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
019    import com.liferay.portal.kernel.util.GetterUtil;
020    import com.liferay.portal.kernel.util.LoggingTimer;
021    import com.liferay.portal.kernel.util.PortalUtil;
022    import com.liferay.portal.kernel.util.StringBundler;
023    import com.liferay.portal.util.PortalInstances;
024    
025    import java.sql.PreparedStatement;
026    import java.sql.ResultSet;
027    
028    /**
029     * @author Raymond Aug??
030     */
031    public class UpgradePermission extends UpgradeProcess {
032    
033            protected void addRole(
034                            long roleId, long companyId, long classNameId, long classPK,
035                            String name, int type)
036                    throws Exception {
037    
038                    try (PreparedStatement ps = connection.prepareStatement(
039                                    "insert into Role_ (roleId, companyId, classNameId, classPK, " +
040                                            "name, type_) values (?, ?, ?, ?, ?, ?)")) {
041    
042                            ps.setLong(1, roleId);
043                            ps.setLong(2, companyId);
044                            ps.setLong(3, classNameId);
045                            ps.setLong(4, classPK);
046                            ps.setString(5, name);
047                            ps.setInt(6, type);
048    
049                            ps.executeUpdate();
050                    }
051            }
052    
053            protected void addSingleApproverWorkflowRoles() throws Exception {
054                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
055                            long[] companyIds = PortalInstances.getCompanyIdsBySQL();
056    
057                            for (long companyId : companyIds) {
058                                    addSingleApproverWorkflowRoles(companyId);
059                            }
060                    }
061            }
062    
063            protected void addSingleApproverWorkflowRoles(long companyId)
064                    throws Exception {
065    
066                    long classNameId = PortalUtil.getClassNameId(
067                            "com.liferay.portal.model.Role");
068                    long roleId = increment();
069    
070                    addRole(
071                            roleId, companyId, classNameId, roleId,
072                            _ROLE_COMMUNITY_CONTENT_REVIEWER, RoleConstants.TYPE_SITE);
073    
074                    classNameId = PortalUtil.getClassNameId(
075                            "com.liferay.portal.model.Organization");
076                    roleId = increment();
077    
078                    addRole(
079                            roleId, companyId, classNameId, roleId,
080                            _ROLE_ORGANIZATION_CONTENT_REVIEWER,
081                            RoleConstants.TYPE_ORGANIZATION);
082    
083                    classNameId = PortalUtil.getClassNameId(
084                            "com.liferay.portal.model.Company");
085                    roleId = increment();
086    
087                    addRole(
088                            roleId, companyId, classNameId, roleId,
089                            _ROLE_PORTAL_CONTENT_REVIEWER, RoleConstants.TYPE_REGULAR);
090            }
091    
092            protected void addUserGroupRole(long userId, long groupId, long roleId)
093                    throws Exception {
094    
095                    if (hasUserGroupRole(userId, groupId, roleId)) {
096                            return;
097                    }
098    
099                    try (PreparedStatement ps = connection.prepareStatement(
100                                    "insert into UserGroupRole (userId, groupId, roleId) values " +
101                                            "(?, ?, ?)")) {
102    
103                            ps.setLong(1, userId);
104                            ps.setLong(2, groupId);
105                            ps.setLong(3, roleId);
106    
107                            ps.executeUpdate();
108                    }
109            }
110    
111            protected void addUserRole(long userId, long roleId) throws Exception {
112                    if (hasUserRole(userId, roleId)) {
113                            return;
114                    }
115    
116                    try (PreparedStatement ps = connection.prepareStatement(
117                                    "insert into Users_Roles (userId, roleId) values (?, ?)")) {
118    
119                            ps.setLong(1, userId);
120                            ps.setLong(2, roleId);
121    
122                            ps.executeUpdate();
123                    }
124            }
125    
126            protected void assignSingleApproverWorkflowRoles(
127                            long companyId, long roleId, long groupId)
128                    throws Exception {
129    
130                    try (PreparedStatement ps1 = connection.prepareStatement(
131                                    "select classNameId from Group_ where groupId = ?")) {
132    
133                            ps1.setLong(1, groupId);
134    
135                            try (ResultSet rs1 = ps1.executeQuery()) {
136                                    long classNameId = 0;
137    
138                                    if (rs1.next()) {
139                                            classNameId = rs1.getLong("classNameId");
140                                    }
141    
142                                    String className = PortalUtil.getClassName(classNameId);
143    
144                                    long communityContentReviewerRoleId = getRoleId(
145                                            companyId, _ROLE_COMMUNITY_CONTENT_REVIEWER);
146                                    long organizationContentReviewerRoleId = getRoleId(
147                                            companyId, _ROLE_ORGANIZATION_CONTENT_REVIEWER);
148                                    long portalContentReviewerRoleId = getRoleId(
149                                            companyId, _ROLE_PORTAL_CONTENT_REVIEWER);
150    
151                                    StringBundler sb = new StringBundler(5);
152    
153                                    sb.append("(select User_.* from User_, Users_Roles where ");
154                                    sb.append("User_.userId = Users_Roles.userId and ");
155                                    sb.append("Users_Roles.roleId = ?) union all (select User_.* ");
156                                    sb.append("from User_, UserGroupRole where User_.userId = ");
157                                    sb.append("UserGroupRole.userId and UserGroupRole.roleId = ?)");
158    
159                                    try (PreparedStatement ps2 = connection.prepareStatement(
160                                                    sb.toString())) {
161    
162                                            ps2.setLong(1, roleId);
163                                            ps2.setLong(2, roleId);
164    
165                                            try (ResultSet rs2 = ps2.executeQuery()) {
166                                                    while (rs2.next()) {
167                                                            long userId = rs2.getLong("userId");
168    
169                                                            if (className.equals(
170                                                                            "com.liferay.portal.model.Company")) {
171    
172                                                                    addUserRole(
173                                                                            userId, portalContentReviewerRoleId);
174                                                            }
175                                                            else if (className.equals(
176                                                                                    "com.liferay.portal.model.Group")) {
177    
178                                                                    addUserGroupRole(
179                                                                            userId, groupId,
180                                                                            communityContentReviewerRoleId);
181                                                            }
182                                                            else if (className.equals(
183                                                                                    "com.liferay.portal.model." +
184                                                                                            "Organization")) {
185    
186                                                                    addUserGroupRole(
187                                                                            userId, groupId,
188                                                                            organizationContentReviewerRoleId);
189                                                            }
190                                                    }
191                                            }
192                                    }
193                            }
194                    }
195            }
196    
197            @Override
198            protected void doUpgrade() throws Exception {
199                    addSingleApproverWorkflowRoles();
200    
201                    updatePermissions();
202            }
203    
204            protected long getRoleId(long companyId, String name) throws Exception {
205                    try (PreparedStatement ps = connection.prepareStatement(
206                                    "select roleId from Role_ where companyId = ? and name = ?")) {
207    
208                            ps.setLong(1, companyId);
209                            ps.setString(2, name);
210    
211                            try (ResultSet rs = ps.executeQuery()) {
212                                    if (rs.next()) {
213                                            return rs.getLong("roleId");
214                                    }
215    
216                                    return 0;
217                            }
218                    }
219            }
220    
221            protected boolean hasUserGroupRole(long userId, long groupId, long roleId)
222                    throws Exception {
223    
224                    try (PreparedStatement ps = connection.prepareStatement(
225                                    "select count(*) from UserGroupRole where userId = ? and " +
226                                            "groupId = ? and roleId = ?")) {
227    
228                            ps.setLong(1, userId);
229                            ps.setLong(2, groupId);
230                            ps.setLong(3, roleId);
231    
232                            try (ResultSet rs = ps.executeQuery()) {
233                                    if (rs.next()) {
234                                            int count = rs.getInt(1);
235    
236                                            if (count > 0) {
237                                                    return true;
238                                            }
239                                    }
240    
241                                    return false;
242                            }
243                    }
244            }
245    
246            protected boolean hasUserRole(long userId, long roleId) throws Exception {
247                    try (PreparedStatement ps = connection.prepareStatement(
248                                    "select count(*) from Users_Roles where userId = ? and " +
249                                            "roleId = ?")) {
250    
251                            ps.setLong(1, userId);
252                            ps.setLong(2, roleId);
253    
254                            try (ResultSet rs = ps.executeQuery()) {
255                                    if (rs.next()) {
256                                            int count = rs.getInt(1);
257    
258                                            if (count > 0) {
259                                                    return true;
260                                            }
261                                    }
262    
263                                    return false;
264                            }
265                    }
266            }
267    
268            protected void updatePermissions() throws Exception {
269                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
270                            StringBundler sb = new StringBundler(11);
271    
272                            sb.append("select ResourcePermission.companyId, ");
273                            sb.append("ResourcePermission.roleId, ResourcePermission.primKey ");
274                            sb.append("from ResourcePermission, ResourceAction where ");
275                            sb.append("ResourceAction.name = 'com.liferay.portlet.journal' ");
276                            sb.append("and ResourceAction.name = ResourcePermission.name and ");
277                            sb.append("ResourceAction.actionId = 'APPROVE_ARTICLE' and ");
278                            sb.append("ResourcePermission.scope = 4 and ");
279                            sb.append("ResourcePermission.actionIds >= ");
280                            sb.append("ResourceAction.bitwiseValue and ");
281                            sb.append("mod((ResourcePermission.actionIds / ");
282                            sb.append("ResourceAction.bitwiseValue), 2) = 1");
283    
284                            try (PreparedStatement ps = connection.prepareStatement(
285                                            sb.toString());
286                                    ResultSet rs = ps.executeQuery()) {
287    
288                                    while (rs.next()) {
289                                            long companyId = rs.getLong("companyId");
290                                            long roleId = rs.getLong("roleId");
291                                            long groupId = GetterUtil.getLong(rs.getString("primKey"));
292    
293                                            assignSingleApproverWorkflowRoles(
294                                                    companyId, roleId, groupId);
295                                    }
296                            }
297                    }
298            }
299    
300            private static final String _ROLE_COMMUNITY_CONTENT_REVIEWER =
301                    "Community Content Reviewer";
302    
303            private static final String _ROLE_ORGANIZATION_CONTENT_REVIEWER =
304                    "Organization Content Reviewer";
305    
306            private static final String _ROLE_PORTAL_CONTENT_REVIEWER =
307                    "Portal Content Reviewer";
308    
309    }