001
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
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 }