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.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.model.RoleConstants;
019    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
020    import com.liferay.portal.kernel.util.GetterUtil;
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.Connection;
026    import java.sql.PreparedStatement;
027    import java.sql.ResultSet;
028    
029    /**
030     * @author Raymond Augé
031     */
032    public class UpgradePermission extends UpgradeProcess {
033    
034            protected void addRole(
035                            long roleId, long companyId, long classNameId, long classPK,
036                            String name, int type)
037                    throws Exception {
038    
039                    Connection con = null;
040                    PreparedStatement ps = null;
041    
042                    try {
043                            con = DataAccess.getUpgradeOptimizedConnection();
044    
045                            ps = con.prepareStatement(
046                                    "insert into Role_ (roleId, companyId, classNameId, classPK, " +
047                                            "name, type_) values (?, ?, ?, ?, ?, ?)");
048    
049                            ps.setLong(1, roleId);
050                            ps.setLong(2, companyId);
051                            ps.setLong(3, classNameId);
052                            ps.setLong(4, classPK);
053                            ps.setString(5, name);
054                            ps.setInt(6, type);
055    
056                            ps.executeUpdate();
057                    }
058                    finally {
059                            DataAccess.cleanUp(con, ps);
060                    }
061            }
062    
063            protected void addSingleApproverWorkflowRoles() throws Exception {
064                    long[] companyIds = PortalInstances.getCompanyIdsBySQL();
065    
066                    for (long companyId : companyIds) {
067                            addSingleApproverWorkflowRoles(companyId);
068                    }
069            }
070    
071            protected void addSingleApproverWorkflowRoles(long companyId)
072                    throws Exception {
073    
074                    long classNameId = PortalUtil.getClassNameId(
075                            "com.liferay.portal.model.Role");
076                    long roleId = increment();
077    
078                    addRole(
079                            roleId, companyId, classNameId, roleId,
080                            _ROLE_COMMUNITY_CONTENT_REVIEWER, RoleConstants.TYPE_SITE);
081    
082                    classNameId = PortalUtil.getClassNameId(
083                            "com.liferay.portal.model.Organization");
084                    roleId = increment();
085    
086                    addRole(
087                            roleId, companyId, classNameId, roleId,
088                            _ROLE_ORGANIZATION_CONTENT_REVIEWER,
089                            RoleConstants.TYPE_ORGANIZATION);
090    
091                    classNameId = PortalUtil.getClassNameId(
092                            "com.liferay.portal.model.Company");
093                    roleId = increment();
094    
095                    addRole(
096                            roleId, companyId, classNameId, roleId,
097                            _ROLE_PORTAL_CONTENT_REVIEWER, RoleConstants.TYPE_REGULAR);
098            }
099    
100            protected void addUserGroupRole(long userId, long groupId, long roleId)
101                    throws Exception {
102    
103                    if (hasUserGroupRole(userId, groupId, roleId)) {
104                            return;
105                    }
106    
107                    Connection con = null;
108                    PreparedStatement ps = null;
109    
110                    try {
111                            con = DataAccess.getUpgradeOptimizedConnection();
112    
113                            ps = con.prepareStatement(
114                                    "insert into UserGroupRole (userId, groupId, roleId) values " +
115                                            "(?, ?, ?)");
116    
117                            ps.setLong(1, userId);
118                            ps.setLong(2, groupId);
119                            ps.setLong(3, roleId);
120    
121                            ps.executeUpdate();
122                    }
123                    finally {
124                            DataAccess.cleanUp(con, ps);
125                    }
126            }
127    
128            protected void addUserRole(long userId, long roleId) throws Exception {
129                    if (hasUserRole(userId, roleId)) {
130                            return;
131                    }
132    
133                    Connection con = null;
134                    PreparedStatement ps = null;
135    
136                    try {
137                            con = DataAccess.getUpgradeOptimizedConnection();
138    
139                            ps = con.prepareStatement(
140                                    "insert into Users_Roles (userId, roleId) values (?, ?)");
141    
142                            ps.setLong(1, userId);
143                            ps.setLong(2, roleId);
144    
145                            ps.executeUpdate();
146                    }
147                    finally {
148                            DataAccess.cleanUp(con, ps);
149                    }
150            }
151    
152            protected void assignSingleApproverWorkflowRoles(
153                            long companyId, long roleId, long groupId)
154                    throws Exception {
155    
156                    Connection con = null;
157                    PreparedStatement ps = null;
158                    ResultSet rs = null;
159    
160                    try {
161                            con = DataAccess.getUpgradeOptimizedConnection();
162    
163                            ps = con.prepareStatement(
164                                    "select classNameId from Group_ where groupId = ?");
165    
166                            ps.setLong(1, groupId);
167    
168                            rs = ps.executeQuery();
169    
170                            long classNameId = 0;
171    
172                            if (rs.next()) {
173                                    classNameId = rs.getLong("classNameId");
174                            }
175    
176                            String className = PortalUtil.getClassName(classNameId);
177    
178                            long communityContentReviewerRoleId = getRoleId(
179                                    companyId, _ROLE_COMMUNITY_CONTENT_REVIEWER);
180                            long organizationContentReviewerRoleId = getRoleId(
181                                    companyId, _ROLE_ORGANIZATION_CONTENT_REVIEWER);
182                            long portalContentReviewerRoleId = getRoleId(
183                                    companyId, _ROLE_PORTAL_CONTENT_REVIEWER);
184    
185                            StringBundler sb = new StringBundler(5);
186    
187                            sb.append("(select User_.* from User_, Users_Roles where ");
188                            sb.append("User_.userId = Users_Roles.userId and ");
189                            sb.append("Users_Roles.roleId = ?) union all (select User_.* ");
190                            sb.append("from User_, UserGroupRole where User_.userId = ");
191                            sb.append("UserGroupRole.userId and UserGroupRole.roleId = ?)");
192    
193                            ps = con.prepareStatement(sb.toString());
194    
195                            ps.setLong(1, roleId);
196                            ps.setLong(2, roleId);
197    
198                            rs = ps.executeQuery();
199    
200                            while (rs.next()) {
201                                    long userId = rs.getLong("userId");
202    
203                                    if (className.equals("com.liferay.portal.model.Company")) {
204                                            addUserRole(userId, portalContentReviewerRoleId);
205                                    }
206                                    else if (className.equals("com.liferay.portal.model.Group")) {
207                                            addUserGroupRole(
208                                                    userId, groupId, communityContentReviewerRoleId);
209                                    }
210                                    else if (className.equals(
211                                                            "com.liferay.portal.model.Organization")) {
212    
213                                            addUserGroupRole(
214                                                    userId, groupId, organizationContentReviewerRoleId);
215                                    }
216                            }
217                    }
218                    finally {
219                            DataAccess.cleanUp(con, ps, rs);
220                    }
221            }
222    
223            @Override
224            protected void doUpgrade() throws Exception {
225                    addSingleApproverWorkflowRoles();
226    
227                    updatePermissions();
228            }
229    
230            protected long getRoleId(long companyId, String name) throws Exception {
231                    Connection con = null;
232                    PreparedStatement ps = null;
233                    ResultSet rs = null;
234    
235                    try {
236                            con = DataAccess.getUpgradeOptimizedConnection();
237    
238                            ps = con.prepareStatement(
239                                    "select roleId from Role_ where companyId = ? and name = ?");
240    
241                            ps.setLong(1, companyId);
242                            ps.setString(2, name);
243    
244                            rs = ps.executeQuery();
245    
246                            if (rs.next()) {
247                                    return rs.getLong("roleId");
248                            }
249    
250                            return 0;
251                    }
252                    finally {
253                            DataAccess.cleanUp(con, ps, rs);
254                    }
255            }
256    
257            protected boolean hasUserGroupRole(long userId, long groupId, long roleId)
258                    throws Exception {
259    
260                    Connection con = null;
261                    PreparedStatement ps = null;
262                    ResultSet rs = null;
263    
264                    try {
265                            con = DataAccess.getUpgradeOptimizedConnection();
266    
267                            ps = con.prepareStatement(
268                                    "select count(*) from UserGroupRole where userId = ? and " +
269                                            "groupId = ? and roleId = ?");
270    
271                            ps.setLong(1, userId);
272                            ps.setLong(2, groupId);
273                            ps.setLong(3, roleId);
274    
275                            rs = ps.executeQuery();
276    
277                            if (rs.next()) {
278                                    int count = rs.getInt(1);
279    
280                                    if (count > 0) {
281                                            return true;
282                                    }
283                            }
284    
285                            return false;
286                    }
287                    finally {
288                            DataAccess.cleanUp(con, ps, rs);
289                    }
290            }
291    
292            protected boolean hasUserRole(long userId, long roleId) throws Exception {
293                    Connection con = null;
294                    PreparedStatement ps = null;
295                    ResultSet rs = null;
296    
297                    try {
298                            con = DataAccess.getUpgradeOptimizedConnection();
299    
300                            ps = con.prepareStatement(
301                                    "select count(*) from Users_Roles where userId = ? and " +
302                                            "roleId = ?");
303    
304                            ps.setLong(1, userId);
305                            ps.setLong(2, roleId);
306    
307                            rs = ps.executeQuery();
308    
309                            if (rs.next()) {
310                                    int count = rs.getInt(1);
311    
312                                    if (count > 0) {
313                                            return true;
314                                    }
315                            }
316    
317                            return false;
318                    }
319                    finally {
320                            DataAccess.cleanUp(con, ps, rs);
321                    }
322            }
323    
324            protected void updatePermissions() throws Exception {
325                    Connection con = null;
326                    PreparedStatement ps = null;
327                    ResultSet rs = null;
328    
329                    try {
330                            con = DataAccess.getUpgradeOptimizedConnection();
331    
332                            StringBundler sb = new StringBundler(11);
333    
334                            sb.append("select ResourcePermission.companyId, ");
335                            sb.append("ResourcePermission.roleId, ResourcePermission.primKey ");
336                            sb.append("from ResourcePermission, ResourceAction where ");
337                            sb.append("ResourceAction.name = 'com.liferay.portlet.journal' ");
338                            sb.append("and ResourceAction.name = ResourcePermission.name and ");
339                            sb.append("ResourceAction.actionId = 'APPROVE_ARTICLE' and ");
340                            sb.append("ResourcePermission.scope = 4 and ");
341                            sb.append("ResourcePermission.actionIds >= ");
342                            sb.append("ResourceAction.bitwiseValue and ");
343                            sb.append("mod((ResourcePermission.actionIds / ");
344                            sb.append("ResourceAction.bitwiseValue), 2) = 1");
345    
346                            ps = con.prepareStatement(sb.toString());
347    
348                            rs = ps.executeQuery();
349    
350                            while (rs.next()) {
351                                    long companyId = rs.getLong("companyId");
352                                    long roleId = rs.getLong("roleId");
353                                    long groupId = GetterUtil.getLong(rs.getString("primKey"));
354    
355                                    assignSingleApproverWorkflowRoles(companyId, roleId, groupId);
356                            }
357                    }
358                    finally {
359                            DataAccess.cleanUp(con, ps, rs);
360                    }
361            }
362    
363            private static final String _ROLE_COMMUNITY_CONTENT_REVIEWER =
364                    "Community Content Reviewer";
365    
366            private static final String _ROLE_ORGANIZATION_CONTENT_REVIEWER =
367                    "Organization Content Reviewer";
368    
369            private static final String _ROLE_PORTAL_CONTENT_REVIEWER =
370                    "Portal Content Reviewer";
371    
372    }