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