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