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    
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                    Connection con = null;
106                    PreparedStatement ps = null;
107    
108                    try {
109                            con = DataAccess.getUpgradeOptimizedConnection();
110    
111                            ps = con.prepareStatement(
112                                    "insert into UserGroupRole (userId, groupId, roleId) values " +
113                                            "(?, ?, ?)");
114    
115                            ps.setLong(1, userId);
116                            ps.setLong(2, groupId);
117                            ps.setLong(3, roleId);
118    
119                            ps.executeUpdate();
120                    }
121                    finally {
122                            DataAccess.cleanUp(con, ps);
123                    }
124            }
125    
126            protected void addUserRole(long userId, long roleId) throws Exception {
127                    Connection con = null;
128                    PreparedStatement ps = null;
129    
130                    try {
131                            con = DataAccess.getUpgradeOptimizedConnection();
132    
133                            ps = con.prepareStatement(
134                                    "insert into Users_Roles (userId, roleId) values (?, ?)");
135    
136                            ps.setLong(1, userId);
137                            ps.setLong(2, roleId);
138    
139                            ps.executeUpdate();
140                    }
141                    finally {
142                            DataAccess.cleanUp(con, ps);
143                    }
144            }
145    
146            protected void assignSingleApproverWorkflowRoles(
147                            long companyId, long roleId, long groupId)
148                    throws Exception {
149    
150                    Connection con = null;
151                    PreparedStatement ps = null;
152                    ResultSet rs = null;
153    
154                    try {
155                            con = DataAccess.getUpgradeOptimizedConnection();
156    
157                            ps = con.prepareStatement(
158                                    "select classNameId from Group_ where groupId = ?");
159    
160                            ps.setLong(1, groupId);
161    
162                            rs = ps.executeQuery();
163    
164                            long classNameId = 0;
165    
166                            if (rs.next()) {
167                                    classNameId = rs.getLong("classNameId");
168                            }
169    
170                            String className = PortalUtil.getClassName(classNameId);
171    
172                            long communityContentReviewerRoleId = getRoleId(
173                                    companyId, _ROLE_COMMUNITY_CONTENT_REVIEWER);
174                            long organizationContentReviewerRoleId = getRoleId(
175                                    companyId, _ROLE_ORGANIZATION_CONTENT_REVIEWER);
176                            long portalContentReviewerRoleId = getRoleId(
177                                    companyId, _ROLE_PORTAL_CONTENT_REVIEWER);
178    
179                            StringBundler sb = new StringBundler();
180    
181                            sb.append("(select User_.* from User_, Users_Roles where ");
182                            sb.append("User_.userId = Users_Roles.userId and ");
183                            sb.append("Users_Roles.roleId = ?) union all (select User_.* ");
184                            sb.append("from User_, UserGroupRole where User_.userId = ");
185                            sb.append("UserGroupRole.userId and UserGroupRole.roleId = ?)");
186    
187                            String sql = sb.toString();
188    
189                            ps = con.prepareStatement(sql);
190    
191                            ps.setLong(1, roleId);
192                            ps.setLong(2, roleId);
193    
194                            rs = ps.executeQuery();
195    
196                            while (rs.next()) {
197                                    long userId = rs.getLong("userId");
198    
199                                    if (className.equals(Company.class.getName())) {
200                                            addUserRole(userId, portalContentReviewerRoleId);
201                                    }
202                                    else if (className.equals(Group.class.getName())) {
203                                            addUserGroupRole(
204                                                    userId, groupId, communityContentReviewerRoleId);
205                                    }
206                                    else if (className.equals(Organization.class.getName())) {
207                                            addUserGroupRole(
208                                                    userId, groupId, organizationContentReviewerRoleId);
209                                    }
210                            }
211                    }
212                    finally {
213                            DataAccess.cleanUp(con, ps, rs);
214                    }
215            }
216    
217            @Override
218            protected void doUpgrade() throws Exception {
219                    addSingleApproverWorkflowRoles();
220    
221                    updatePermissions();
222            }
223    
224            protected long getRoleId(long companyId, String name) throws Exception {
225                    Connection con = null;
226                    PreparedStatement ps = null;
227                    ResultSet rs = null;
228    
229                    try {
230                            con = DataAccess.getUpgradeOptimizedConnection();
231    
232                            ps = con.prepareStatement(
233                                    "select roleId from Role_ where companyId = ? and name = ?");
234    
235                            ps.setLong(1, companyId);
236                            ps.setString(2, name);
237    
238                            rs = ps.executeQuery();
239    
240                            if (rs.next()) {
241                                    return rs.getLong("roleId");
242                            }
243    
244                            return 0;
245                    }
246                    finally {
247                            DataAccess.cleanUp(con, ps, rs);
248                    }
249            }
250    
251            protected void updatePermissions() throws Exception {
252                    Connection con = null;
253                    PreparedStatement ps = null;
254                    ResultSet rs = null;
255    
256                    try {
257                            con = DataAccess.getUpgradeOptimizedConnection();
258    
259                            StringBundler sb = new StringBundler();
260    
261                            sb.append("select ResourcePermission.companyId, ");
262                            sb.append("ResourcePermission.roleId, ResourcePermission.primKey ");
263                            sb.append("from ResourcePermission, ResourceAction where ");
264                            sb.append("ResourceAction.name = 'com.liferay.portlet.journal' ");
265                            sb.append("and ResourceAction.name = ResourcePermission.name and ");
266                            sb.append("ResourceAction.actionId = 'APPROVE_ARTICLE' and ");
267                            sb.append("ResourcePermission.scope = 4 and ");
268                            sb.append("ResourcePermission.actionIds >= ");
269                            sb.append("ResourceAction.bitwiseValue and ");
270                            sb.append("mod((ResourcePermission.actionIds / ");
271                            sb.append("ResourceAction.bitwiseValue), 2) = 1");
272    
273                            String sql = sb.toString();
274    
275                            sql = SQLTransformer.transform(sql);
276    
277                            ps = con.prepareStatement(sql);
278    
279                            rs = ps.executeQuery();
280    
281                            while (rs.next()) {
282                                    long companyId = rs.getLong("companyId");
283                                    long roleId = rs.getLong("roleId");
284                                    long groupId = GetterUtil.getLong(rs.getString("primKey"));
285    
286                                    assignSingleApproverWorkflowRoles(companyId, roleId, groupId);
287                            }
288                    }
289                    finally {
290                            DataAccess.cleanUp(con, ps, rs);
291                    }
292            }
293    
294            private static final String _ROLE_COMMUNITY_CONTENT_REVIEWER =
295                    "Community Content Reviewer";
296    
297            private static final String _ROLE_ORGANIZATION_CONTENT_REVIEWER =
298                    "Organization Content Reviewer";
299    
300            private static final String _ROLE_PORTAL_CONTENT_REVIEWER =
301                    "Portal Content Reviewer";
302    
303    }