1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   *
12   *
13   */
14  
15  package com.liferay.portal.upgrade.v4_3_5;
16  
17  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
18  import com.liferay.portal.kernel.upgrade.UpgradeProcess;
19  import com.liferay.portal.model.GroupConstants;
20  import com.liferay.portal.model.Layout;
21  import com.liferay.portal.model.PortletConstants;
22  import com.liferay.portal.util.PortalInstances;
23  import com.liferay.portlet.blogs.model.BlogsEntry;
24  import com.liferay.portlet.bookmarks.model.BookmarksEntry;
25  import com.liferay.portlet.bookmarks.model.BookmarksFolder;
26  import com.liferay.portlet.calendar.model.CalEvent;
27  import com.liferay.portlet.documentlibrary.model.DLFileEntry;
28  import com.liferay.portlet.documentlibrary.model.DLFileShortcut;
29  import com.liferay.portlet.documentlibrary.model.DLFolder;
30  import com.liferay.portlet.imagegallery.model.IGFolder;
31  import com.liferay.portlet.imagegallery.model.IGImage;
32  import com.liferay.portlet.journal.model.JournalArticle;
33  import com.liferay.portlet.journal.model.JournalStructure;
34  import com.liferay.portlet.journal.model.JournalTemplate;
35  import com.liferay.portlet.messageboards.model.MBCategory;
36  import com.liferay.portlet.messageboards.model.MBMessage;
37  import com.liferay.portlet.polls.model.PollsQuestion;
38  import com.liferay.portlet.shopping.model.ShoppingCategory;
39  import com.liferay.portlet.shopping.model.ShoppingItem;
40  import com.liferay.portlet.softwarecatalog.model.SCFrameworkVersion;
41  import com.liferay.portlet.softwarecatalog.model.SCProductEntry;
42  import com.liferay.portlet.wiki.model.WikiNode;
43  import com.liferay.portlet.wiki.model.WikiPage;
44  
45  import java.sql.Connection;
46  import java.sql.PreparedStatement;
47  import java.sql.ResultSet;
48  
49  import java.util.ArrayList;
50  import java.util.List;
51  
52  /**
53   * <a href="UpgradePermission.java.html"><b><i>View Source</i></b></a>
54   *
55   * @author Brian Wing Shun Chan
56   */
57  public class UpgradePermission extends UpgradeProcess {
58  
59      protected void copyPermissions(long defaultUserId, long guestGroupId)
60          throws Exception {
61  
62          if ((defaultUserId == 0) || (guestGroupId == 0)) {
63              return;
64          }
65  
66          runSQL("delete from Users_Permissions where userId = " + defaultUserId);
67  
68          runSQL(
69              "insert into Users_Permissions (userId, permissionId) select " +
70                  defaultUserId + ", Groups_Permissions.permissionId from " +
71                      "Groups_Permissions where groupId = " + guestGroupId);
72  
73          for (long plid : getPlids(guestGroupId)) {
74              deletePortletPermissionIds(plid, guestGroupId);
75          }
76  
77          deletePermissionIds(
78              Layout.class.getName(), "Layout", "plid", guestGroupId);
79  
80          deletePermissionIds(
81              BlogsEntry.class.getName(), "BlogsEntry", "entryId", guestGroupId);
82  
83          deletePermissionIds(
84              BookmarksFolder.class.getName(), "BookmarksFolder", "folderId",
85              guestGroupId);
86          deletePermissionIds(
87              BookmarksEntry.class.getName(), "BookmarksEntry", "entryId",
88              "BookmarksFolder", "folderId", guestGroupId);
89  
90          deletePermissionIds(
91              CalEvent.class.getName(), "CalEvent", "eventId", guestGroupId);
92  
93          deletePermissionIds(
94              DLFolder.class.getName(), "DLFolder", "folderId", guestGroupId);
95          deletePermissionIds(
96              DLFileEntry.class.getName(), "DLFileEntry", "fileEntryId",
97              "DLFolder", "folderId", guestGroupId);
98          deletePermissionIds(
99              DLFileShortcut.class.getName(), "DLFileShortcut", "fileShortcutId",
100             "DLFolder", "folderId", guestGroupId);
101 
102         deletePermissionIds(
103             IGFolder.class.getName(), "IGFolder", "folderId", guestGroupId);
104         deletePermissionIds(
105             IGImage.class.getName(), "IGImage", "imageId", "IGFolder",
106             "folderId", guestGroupId);
107 
108         deletePermissionIds(
109             JournalArticle.class.getName(), "JournalArticle", "resourcePrimKey",
110             guestGroupId);
111         deletePermissionIds(
112             JournalStructure.class.getName(), "JournalStructure", "id_",
113             guestGroupId);
114         deletePermissionIds(
115             JournalTemplate.class.getName(), "JournalTemplate", "id_",
116             guestGroupId);
117 
118         deletePermissionIds(
119             MBCategory.class.getName(), "MBCategory", "categoryId",
120             guestGroupId);
121         deletePermissionIds(
122             MBMessage.class.getName(), "MBMessage", "messageId", "MBCategory",
123             "categoryId", guestGroupId);
124 
125         deletePermissionIds(
126             PollsQuestion.class.getName(), "PollsQuestion", "questionId",
127             guestGroupId);
128 
129         deletePermissionIds(
130             SCFrameworkVersion.class.getName(), "SCFrameworkVersion",
131             "frameworkVersionId", guestGroupId);
132         deletePermissionIds(
133             SCProductEntry.class.getName(), "SCProductEntry", "productEntryId",
134             guestGroupId);
135 
136         deletePermissionIds(
137             ShoppingCategory.class.getName(), "ShoppingCategory", "categoryId",
138             guestGroupId);
139         deletePermissionIds(
140             ShoppingItem.class.getName(), "ShoppingItem", "itemId",
141             "ShoppingCategory", "categoryId", guestGroupId);
142 
143         deletePermissionIds(
144             WikiNode.class.getName(), "WikiNode", "nodeId", guestGroupId);
145         deletePermissionIds(
146             WikiPage.class.getName(), "WikiPage", "resourcePrimKey", "WikiNode",
147             "nodeId", guestGroupId);
148     }
149 
150     protected void deletePermissionIds(
151             String className, String tableName, String tablePKCol,
152             long guestGroupId)
153         throws Exception {
154 
155         List<Long> permissionIds = getPermissionIds(
156             className, tableName, tablePKCol, guestGroupId);
157 
158         deletePermissionIds(permissionIds, guestGroupId);
159     }
160 
161     protected void deletePermissionIds(
162             String className, String tableName1, String tablePKCol1,
163             String tableName2, String tablePKCol2, long guestGroupId)
164         throws Exception {
165 
166         List<Long> permissionIds = getPermissionIds(
167             className, tableName1, tablePKCol1, tableName2, tablePKCol2,
168             guestGroupId);
169 
170         deletePermissionIds(permissionIds, guestGroupId);
171     }
172 
173     protected void deletePermissionIds(
174             List<Long> permissionIds, long guestGroupId)
175         throws Exception {
176 
177         for (long permissionId : permissionIds) {
178             runSQL(
179                 "delete from Groups_Permissions where groupId = " +
180                     guestGroupId + " and permissionId = " + permissionId);
181         }
182     }
183 
184     protected void deletePortletPermissionIds(long plid, long guestGroupId)
185         throws Exception {
186 
187         Connection con = null;
188         PreparedStatement ps = null;
189         ResultSet rs = null;
190 
191         try {
192             con = DataAccess.getConnection();
193 
194             ps = con.prepareStatement(
195                 "select primKey from Resource_ where primKey like ?");
196 
197             ps.setString(1, plid + PortletConstants.LAYOUT_SEPARATOR + "%");
198 
199             rs = ps.executeQuery();
200 
201             while (rs.next()) {
202                 String primKey = rs.getString("primKey");
203 
204                 List<Long> permissionIds = getPermissionIds(
205                     primKey, guestGroupId);
206 
207                 deletePermissionIds(permissionIds, guestGroupId);
208             }
209         }
210         finally {
211             DataAccess.cleanUp(con, ps, rs);
212         }
213     }
214 
215     protected void doUpgrade() throws Exception {
216         long[] companyIds = PortalInstances.getCompanyIdsBySQL();
217 
218         for (long companyId : companyIds) {
219             long defaultUserId = getDefaultUserId(companyId);
220             long guestGroupId = getGuestGroupId(companyId);
221 
222             copyPermissions(defaultUserId, guestGroupId);
223         }
224     }
225 
226     protected long getDefaultUserId(long companyId) throws Exception {
227         long userId = 0;
228 
229         Connection con = null;
230         PreparedStatement ps = null;
231         ResultSet rs = null;
232 
233         try {
234             con = DataAccess.getConnection();
235 
236             ps = con.prepareStatement(_GET_DEFAULT_USER_ID);
237 
238             ps.setLong(1, companyId);
239             ps.setBoolean(2, true);
240 
241             rs = ps.executeQuery();
242 
243             while (rs.next()) {
244                 userId = rs.getLong("userId");
245             }
246         }
247         finally {
248             DataAccess.cleanUp(con, ps, rs);
249         }
250 
251         return userId;
252     }
253 
254     protected long getGuestGroupId(long companyId) throws Exception {
255         long groupId = 0;
256 
257         Connection con = null;
258         PreparedStatement ps = null;
259         ResultSet rs = null;
260 
261         try {
262             con = DataAccess.getConnection();
263 
264             ps = con.prepareStatement(_GET_GUEST_GROUP_ID);
265 
266             ps.setLong(1, companyId);
267             ps.setString(2, GroupConstants.GUEST);
268 
269             rs = ps.executeQuery();
270 
271             while (rs.next()) {
272                 groupId = rs.getLong("groupId");
273             }
274         }
275         finally {
276             DataAccess.cleanUp(con, ps, rs);
277         }
278 
279         return groupId;
280     }
281 
282     protected List<Long> getPermissionIds(String primKey, long guestGroupId)
283         throws Exception {
284 
285         List<Long> permissionIds = new ArrayList<Long>();
286 
287         Connection con = null;
288         PreparedStatement ps = null;
289         ResultSet rs = null;
290 
291         try {
292             con = DataAccess.getConnection();
293 
294             ps = con.prepareStatement(_GET_PERMISSION_IDS_1);
295 
296             ps.setLong(1, guestGroupId);
297             ps.setString(2, primKey);
298 
299             rs = ps.executeQuery();
300 
301             while (rs.next()) {
302                 long permissionId = rs.getLong("permissionId");
303 
304                 permissionIds.add(permissionId);
305             }
306         }
307         finally {
308             DataAccess.cleanUp(con, ps, rs);
309         }
310 
311         return permissionIds;
312     }
313 
314     protected List<Long> getPermissionIds(
315             String className, String tableName, String tablePKCol,
316             long guestGroupId)
317         throws Exception {
318 
319         List<Long> permissionIds = new ArrayList<Long>();
320 
321         Connection con = null;
322         PreparedStatement ps = null;
323         ResultSet rs = null;
324 
325         try {
326             con = DataAccess.getConnection();
327 
328             ps = con.prepareStatement(
329                 "select " + tablePKCol + " from " + tableName + " " +
330                 "where groupId != " + guestGroupId);
331 
332             rs = ps.executeQuery();
333 
334             while (rs.next()) {
335                 String primKey = String.valueOf(rs.getLong(tablePKCol));
336 
337                 permissionIds.addAll(
338                     getPermissionIds(className, primKey, guestGroupId));
339             }
340         }
341         finally {
342             DataAccess.cleanUp(con, ps, rs);
343         }
344 
345         return permissionIds;
346     }
347 
348     protected List<Long> getPermissionIds(
349             String className, String tableName1, String tablePKCol1,
350             String tableName2, String tablePKCol2, long guestGroupId)
351         throws Exception {
352 
353         List<Long> permissionIds = new ArrayList<Long>();
354 
355         Connection con = null;
356         PreparedStatement ps = null;
357         ResultSet rs = null;
358 
359         try {
360             con = DataAccess.getConnection();
361 
362             ps = con.prepareStatement(
363                 "select " + tablePKCol1 + " from " + tableName1 + " " +
364                 "inner join " + tableName2 + " on " + tableName2 + "." +
365                     tablePKCol2 + " = " + tableName1 + "." + tablePKCol2 + " " +
366                 "where groupId != " + guestGroupId);
367 
368             rs = ps.executeQuery();
369 
370             while (rs.next()) {
371                 String primKey = String.valueOf(rs.getLong(tablePKCol1));
372 
373                 permissionIds.addAll(
374                     getPermissionIds(className, primKey, guestGroupId));
375             }
376         }
377         finally {
378             DataAccess.cleanUp(con, ps, rs);
379         }
380 
381         return permissionIds;
382     }
383 
384     protected List<Long> getPermissionIds(
385             String className, String primKey, long guestGroupId)
386         throws Exception {
387 
388         List<Long> permissionIds = new ArrayList<Long>();
389 
390         Connection con = null;
391         PreparedStatement ps = null;
392         ResultSet rs = null;
393 
394         try {
395             con = DataAccess.getConnection();
396 
397             ps = con.prepareStatement(_GET_PERMISSION_IDS_2);
398 
399             ps.setLong(1, guestGroupId);
400             ps.setString(2, primKey);
401             ps.setString(3, className);
402 
403             rs = ps.executeQuery();
404 
405             while (rs.next()) {
406                 long permissionId = rs.getLong("permissionId");
407 
408                 permissionIds.add(permissionId);
409             }
410         }
411         finally {
412             DataAccess.cleanUp(con, ps, rs);
413         }
414 
415         return permissionIds;
416     }
417 
418     protected List<Long> getPlids(long guestGroupId) throws Exception {
419         List<Long> plids = new ArrayList<Long>();
420 
421         Connection con = null;
422         PreparedStatement ps = null;
423         ResultSet rs = null;
424 
425         try {
426             con = DataAccess.getConnection();
427 
428             ps = con.prepareStatement(_GET_PLIDS);
429 
430             ps.setLong(1, guestGroupId);
431 
432             rs = ps.executeQuery();
433 
434             while (rs.next()) {
435                 long plid = rs.getLong("plid");
436 
437                 plids.add(plid);
438             }
439         }
440         finally {
441             DataAccess.cleanUp(con, ps, rs);
442         }
443 
444         return plids;
445     }
446 
447     private static final String _GET_DEFAULT_USER_ID =
448         "select userId from User_ where companyId = ? and defaultUser = ?";
449 
450     private static final String _GET_GUEST_GROUP_ID =
451         "select groupId from Group_ where companyId = ? and name = ?";
452 
453     private static final String _GET_PERMISSION_IDS_1 =
454         "select Groups_Permissions.permissionId from Groups_Permissions " +
455         "inner join Permission_ on Permission_.permissionId = " +
456             "Groups_Permissions.permissionId " +
457         "inner join Resource_ on Resource_.resourceId = " +
458             "Permission_.resourceId " +
459         "inner join ResourceCode on ResourceCode.codeId = Resource_.codeId " +
460         "where Groups_Permissions.groupId = ? and Resource_.primKey = ?";
461 
462     private static final String _GET_PERMISSION_IDS_2 =
463         "select Groups_Permissions.permissionId from Groups_Permissions " +
464         "inner join Permission_ on Permission_.permissionId = " +
465             "Groups_Permissions.permissionId " +
466         "inner join Resource_ on Resource_.resourceId = " +
467             "Permission_.resourceId " +
468         "inner join ResourceCode on ResourceCode.codeId = Resource_.codeId " +
469         "where Groups_Permissions.groupId = ? and Resource_.primKey = ? and " +
470             "ResourceCode.name = ?";
471 
472     private static final String _GET_PLIDS =
473         "select plid from Layout where Layout.groupId != ?";
474 
475 }