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