001    /**
002     * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
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_COMMUNITY);
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            protected void doUpgrade() throws Exception {
244                    addSingleApproverWorkflowRoles();
245    
246                    if (PropsValues.PERMISSIONS_USER_CHECK_ALGORITHM == 5) {
247                            updatePermissions_5();
248                    }
249                    else if (PropsValues.PERMISSIONS_USER_CHECK_ALGORITHM == 6) {
250                            updatePermissions_6();
251                    }
252            }
253    
254            protected long getRoleId(long companyId, String name) throws Exception {
255                    Connection con = null;
256                    PreparedStatement ps = null;
257                    ResultSet rs = null;
258    
259                    try {
260                            con = DataAccess.getConnection();
261    
262                            ps = con.prepareStatement(
263                                    "select roleId from Role_ where companyId = ? and name = ?");
264    
265                            ps.setLong(1, companyId);
266                            ps.setString(2, name);
267    
268                            rs = ps.executeQuery();
269    
270                            if (rs.next()) {
271                                    return rs.getLong("roleId");
272                            }
273    
274                            return 0;
275                    }
276                    finally {
277                            DataAccess.cleanUp(con, ps, rs);
278                    }
279            }
280    
281            protected void updatePermissions_5() throws Exception {
282                    Connection con = null;
283                    PreparedStatement ps = null;
284                    ResultSet rs = null;
285    
286                    try {
287                            con = DataAccess.getConnection();
288    
289                            StringBundler sb = new StringBundler();
290    
291                            sb.append("select ResourceCode.companyId, ");
292                            sb.append("Roles_Permissions.roleId, Resource_.primKey from ");
293                            sb.append("Resource_, ResourceCode, Permission_, ");
294                            sb.append("Roles_Permissions where Resource_.codeId = ");
295                            sb.append("ResourceCode.codeId and ResourceCode.name = ");
296                            sb.append("'com.liferay.portlet.journal' and ");
297                            sb.append("ResourceCode.scope = 4 and Resource_.resourceId = ");
298                            sb.append("Permission_.resourceId and Permission_.actionId = ");
299                            sb.append("'APPROVE_ARTICLE' and Permission_.permissionId = ");
300                            sb.append("Roles_Permissions.permissionId");
301    
302                            String sql = sb.toString();
303    
304                            ps = con.prepareStatement(sql);
305    
306                            rs = ps.executeQuery();
307    
308                            while (rs.next()) {
309                                    long companyId = rs.getLong("companyId");
310                                    long roleId = rs.getLong("roleId");
311                                    long groupId = GetterUtil.getLong(rs.getString("primKey"));
312    
313                                    assignSingleApproverWorkflowRoles(companyId, roleId, groupId);
314                            }
315                    }
316                    finally {
317                            DataAccess.cleanUp(con, ps, rs);
318                    }
319    
320                    deletePermissions_5();
321            }
322    
323            protected void updatePermissions_6() throws Exception {
324                    Connection con = null;
325                    PreparedStatement ps = null;
326                    ResultSet rs = null;
327    
328                    try {
329                            con = DataAccess.getConnection();
330    
331                            StringBundler sb = new StringBundler();
332    
333                            sb.append("select ResourcePermission.companyId, ");
334                            sb.append("ResourcePermission.roleId, ResourcePermission.primKey ");
335                            sb.append("from ResourcePermission, ResourceAction where ");
336                            sb.append("ResourceAction.name = 'com.liferay.portlet.journal' ");
337                            sb.append("and ResourceAction.name = ResourcePermission.name and ");
338                            sb.append("ResourceAction.actionId = 'APPROVE_ARTICLE' and ");
339                            sb.append("ResourcePermission.scope = 4 and ");
340                            sb.append("ResourcePermission.actionIds >= ");
341                            sb.append("ResourceAction.bitwiseValue and ");
342                            sb.append("ResourcePermission.actionIds / ");
343                            sb.append("mod(ResourceAction.bitwiseValue, 2) = 1");
344    
345                            String sql = sb.toString();
346    
347                            sql = SQLTransformer.transform(sql);
348    
349                            ps = con.prepareStatement(sql);
350    
351                            rs = ps.executeQuery();
352    
353                            while (rs.next()) {
354                                    long companyId = rs.getLong("companyId");
355                                    long roleId = rs.getLong("roleId");
356                                    long groupId = GetterUtil.getLong(rs.getString("primKey"));
357    
358                                    assignSingleApproverWorkflowRoles(companyId, roleId, groupId);
359                            }
360                    }
361                    finally {
362                            DataAccess.cleanUp(con, ps, rs);
363                    }
364            }
365    
366            private static final String _ROLE_COMMUNITY_CONTENT_REVIEWER =
367                    "Community Content Reviewer";
368    
369            private static final String _ROLE_ORGANIZATION_CONTENT_REVIEWER =
370                    "Organization Content Reviewer";
371    
372            private static final String _ROLE_PORTAL_CONTENT_REVIEWER =
373                    "Portal Content Reviewer";
374    
375    }