1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.upgrade.v4_3_5;
24  
25  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.log.Log;
27  import com.liferay.portal.kernel.log.LogFactoryUtil;
28  import com.liferay.portal.model.GroupConstants;
29  import com.liferay.portal.model.Layout;
30  import com.liferay.portal.model.PortletConstants;
31  import com.liferay.portal.upgrade.UpgradeException;
32  import com.liferay.portal.upgrade.UpgradeProcess;
33  import com.liferay.portlet.blogs.model.BlogsEntry;
34  import com.liferay.portlet.bookmarks.model.BookmarksEntry;
35  import com.liferay.portlet.bookmarks.model.BookmarksFolder;
36  import com.liferay.portlet.calendar.model.CalEvent;
37  import com.liferay.portlet.documentlibrary.model.DLFileEntry;
38  import com.liferay.portlet.documentlibrary.model.DLFileShortcut;
39  import com.liferay.portlet.documentlibrary.model.DLFolder;
40  import com.liferay.portlet.imagegallery.model.IGFolder;
41  import com.liferay.portlet.imagegallery.model.IGImage;
42  import com.liferay.portlet.journal.model.JournalArticle;
43  import com.liferay.portlet.journal.model.JournalStructure;
44  import com.liferay.portlet.journal.model.JournalTemplate;
45  import com.liferay.portlet.messageboards.model.MBCategory;
46  import com.liferay.portlet.messageboards.model.MBMessage;
47  import com.liferay.portlet.polls.model.PollsQuestion;
48  import com.liferay.portlet.shopping.model.ShoppingCategory;
49  import com.liferay.portlet.shopping.model.ShoppingItem;
50  import com.liferay.portlet.softwarecatalog.model.SCFrameworkVersion;
51  import com.liferay.portlet.softwarecatalog.model.SCProductEntry;
52  import com.liferay.portlet.wiki.model.WikiNode;
53  import com.liferay.portlet.wiki.model.WikiPage;
54  
55  import java.sql.Connection;
56  import java.sql.PreparedStatement;
57  import java.sql.ResultSet;
58  
59  import java.util.ArrayList;
60  import java.util.List;
61  
62  /**
63   * <a href="UpgradePermission.java.html"><b><i>View Source</i></b></a>
64   *
65   * @author Brian Wing Shun Chan
66   *
67   */
68  public class UpgradePermission extends UpgradeProcess {
69  
70      public void upgrade() throws UpgradeException {
71          _log.info("Upgrading");
72  
73          try {
74              doUpgrade();
75          }
76          catch (Exception e) {
77              throw new UpgradeException(e);
78          }
79      }
80  
81      protected void copyPermissions(long defaultUserId, long guestGroupId)
82          throws Exception {
83  
84          if ((defaultUserId == 0) || (guestGroupId == 0)) {
85              return;
86          }
87  
88          runSQL("delete from Users_Permissions where userId = " + defaultUserId);
89  
90          runSQL(
91              "insert into Users_Permissions (userId, permissionId) select " +
92                  defaultUserId + ", Groups_Permissions.permissionId from " +
93                      "Groups_Permissions where groupId = " + guestGroupId);
94  
95          deletePortletPermissionIds(guestGroupId);
96  
97          deletePermissionIds(
98              Layout.class.getName(), "Layout", "plid", guestGroupId);
99  
100         deletePermissionIds(
101             BlogsEntry.class.getName(), "BlogsEntry", "entryId", guestGroupId);
102 
103         deletePermissionIds(
104             BookmarksFolder.class.getName(), "BookmarksFolder", "folderId",
105             guestGroupId);
106         deletePermissionIds(
107             BookmarksEntry.class.getName(), "BookmarksEntry", "entryId",
108             "BookmarksFolder", "folderId", guestGroupId);
109 
110         deletePermissionIds(
111             CalEvent.class.getName(), "CalEvent", "eventId", guestGroupId);
112 
113         deletePermissionIds(
114             DLFolder.class.getName(), "DLFolder", "folderId", guestGroupId);
115         deletePermissionIds(
116             DLFileEntry.class.getName(), "DLFileEntry", "fileEntryId",
117             "DLFolder", "folderId", guestGroupId);
118         deletePermissionIds(
119             DLFileShortcut.class.getName(), "DLFileShortcut", "fileShortcutId",
120             "DLFolder", "folderId", guestGroupId);
121 
122         deletePermissionIds(
123             IGFolder.class.getName(), "IGFolder", "folderId", guestGroupId);
124         deletePermissionIds(
125             IGImage.class.getName(), "IGImage", "imageId", "IGFolder",
126             "folderId", guestGroupId);
127 
128         deletePermissionIds(
129             JournalArticle.class.getName(), "JournalArticle", "resourcePrimKey",
130             guestGroupId);
131         deletePermissionIds(
132             JournalStructure.class.getName(), "JournalStructure", "id_",
133             guestGroupId);
134         deletePermissionIds(
135             JournalTemplate.class.getName(), "JournalTemplate", "id_",
136             guestGroupId);
137 
138         deletePermissionIds(
139             MBCategory.class.getName(), "MBCategory", "categoryId",
140             guestGroupId);
141         deletePermissionIds(
142             MBMessage.class.getName(), "MBMessage", "messageId", "MBCategory",
143             "categoryId", guestGroupId);
144 
145         deletePermissionIds(
146             PollsQuestion.class.getName(), "PollsQuestion", "questionId",
147             guestGroupId);
148 
149         deletePermissionIds(
150             SCFrameworkVersion.class.getName(), "SCFrameworkVersion",
151             "frameworkVersionId", guestGroupId);
152         deletePermissionIds(
153             SCProductEntry.class.getName(), "SCProductEntry", "productEntryId",
154             guestGroupId);
155 
156         deletePermissionIds(
157             ShoppingCategory.class.getName(), "ShoppingCategory", "categoryId",
158             guestGroupId);
159         deletePermissionIds(
160             ShoppingItem.class.getName(), "ShoppingItem", "itemId",
161             "ShoppingCategory", "categoryId", guestGroupId);
162 
163         deletePermissionIds(
164             WikiNode.class.getName(), "WikiNode", "nodeId", guestGroupId);
165         deletePermissionIds(
166             WikiPage.class.getName(), "WikiPage", "resourcePrimKey", "WikiNode",
167             "nodeId", guestGroupId);
168     }
169 
170     protected void deletePermissionIds(
171             String className, String tableName, String tablePKCol,
172             long guestGroupId)
173         throws Exception {
174 
175         List<Long> permissionIds = getPermissionIds(
176             className, tableName, tablePKCol, guestGroupId);
177 
178         deletePermissionIds(permissionIds, guestGroupId);
179     }
180 
181     protected void deletePermissionIds(
182             String className, String tableName1, String tablePKCol1,
183             String tableName2, String tablePKCol2, long guestGroupId)
184         throws Exception {
185 
186         List<Long> permissionIds = getPermissionIds(
187             className, tableName1, tablePKCol1, tableName2, tablePKCol2,
188             guestGroupId);
189 
190         deletePermissionIds(permissionIds, guestGroupId);
191     }
192 
193     protected void deletePermissionIds(
194             List<Long> permissionIds, long guestGroupId)
195         throws Exception {
196 
197         for (long permissionId : permissionIds) {
198             runSQL(
199                 "delete from Groups_Permissions where groupId = " +
200                     guestGroupId + " and permissionId = " + permissionId);
201         }
202     }
203 
204     protected void deletePortletPermissionIds(long guestGroupId)
205         throws Exception {
206 
207         Connection con = null;
208         PreparedStatement ps = null;
209         ResultSet rs = null;
210 
211         try {
212             con = DataAccess.getConnection();
213 
214             for (long plid : getPlids(guestGroupId)) {
215                 ps = con.prepareStatement(
216                     "select primKey from Resource_ where primKey like ?");
217 
218                 ps.setString(1, plid + PortletConstants.LAYOUT_SEPARATOR + "%");
219 
220                 rs = ps.executeQuery();
221 
222                 while (rs.next()) {
223                     String primKey = rs.getString("primKey");
224 
225                     List<Long> permissionIds = getPermissionIds(
226                         primKey, guestGroupId);
227 
228                     deletePermissionIds(permissionIds, guestGroupId);
229                 }
230 
231                 ps.close();
232             }
233         }
234         finally {
235             DataAccess.cleanUp(con, ps, rs);
236         }
237     }
238 
239     protected void doUpgrade() throws Exception {
240         Connection con = null;
241         PreparedStatement ps = null;
242         ResultSet rs = null;
243 
244         try {
245             con = DataAccess.getConnection();
246 
247             ps = con.prepareStatement(_GET_COMPANY_IDS);
248 
249             rs = ps.executeQuery();
250 
251             while (rs.next()) {
252                 long companyId = rs.getLong("companyId");
253 
254                 long defaultUserId = getDefaultUserId(companyId);
255                 long guestGroupId = getGuestGroupId(companyId);
256 
257                 copyPermissions(defaultUserId, guestGroupId);
258             }
259         }
260         finally {
261             DataAccess.cleanUp(con, ps, rs);
262         }
263     }
264 
265     protected long getDefaultUserId(long companyId) throws Exception {
266         long userId = 0;
267 
268         Connection con = null;
269         PreparedStatement ps = null;
270         ResultSet rs = null;
271 
272         try {
273             con = DataAccess.getConnection();
274 
275             ps = con.prepareStatement(_GET_DEFAULT_USER_ID);
276 
277             ps.setLong(1, companyId);
278             ps.setBoolean(2, true);
279 
280             rs = ps.executeQuery();
281 
282             while (rs.next()) {
283                 userId = rs.getLong("userId");
284             }
285         }
286         finally {
287             DataAccess.cleanUp(con, ps, rs);
288         }
289 
290         return userId;
291     }
292 
293     protected long getGuestGroupId(long companyId) throws Exception {
294         long groupId = 0;
295 
296         Connection con = null;
297         PreparedStatement ps = null;
298         ResultSet rs = null;
299 
300         try {
301             con = DataAccess.getConnection();
302 
303             ps = con.prepareStatement(_GET_GUEST_GROUP_ID);
304 
305             ps.setLong(1, companyId);
306             ps.setString(2, GroupConstants.GUEST);
307 
308             rs = ps.executeQuery();
309 
310             while (rs.next()) {
311                 groupId = rs.getLong("groupId");
312             }
313         }
314         finally {
315             DataAccess.cleanUp(con, ps, rs);
316         }
317 
318         return groupId;
319     }
320 
321     protected List<Long> getPermissionIds(String primKey, long guestGroupId)
322         throws Exception {
323 
324         List<Long> permissionIds = new ArrayList<Long>();
325 
326         Connection con = null;
327         PreparedStatement ps = null;
328         ResultSet rs = null;
329 
330         try {
331             con = DataAccess.getConnection();
332 
333             ps = con.prepareStatement(_GET_PERMISSION_IDS_1);
334 
335             ps.setLong(1, guestGroupId);
336             ps.setString(2, primKey);
337 
338             rs = ps.executeQuery();
339 
340             while (rs.next()) {
341                 long permissionId = rs.getLong("permissionId");
342 
343                 permissionIds.add(permissionId);
344             }
345         }
346         finally {
347             DataAccess.cleanUp(con, ps, rs);
348         }
349 
350         return permissionIds;
351     }
352 
353     protected List<Long> getPermissionIds(
354             String className, String tableName, String tablePKCol,
355             long guestGroupId)
356         throws Exception {
357 
358         List<Long> permissionIds = new ArrayList<Long>();
359 
360         Connection con = null;
361         PreparedStatement ps = null;
362         ResultSet rs = null;
363 
364         try {
365             con = DataAccess.getConnection();
366 
367             ps = con.prepareStatement(
368                 "select " + tablePKCol + " from " + tableName + " " +
369                 "where groupId != " + guestGroupId);
370 
371             rs = ps.executeQuery();
372 
373             while (rs.next()) {
374                 String primKey = String.valueOf(rs.getLong(tablePKCol));
375 
376                 permissionIds.addAll(
377                     getPermissionIds(className, primKey, guestGroupId));
378             }
379         }
380         finally {
381             DataAccess.cleanUp(con, ps, rs);
382         }
383 
384         return permissionIds;
385     }
386 
387     protected List<Long> getPermissionIds(
388             String className, String tableName1, String tablePKCol1,
389             String tableName2, String tablePKCol2, long guestGroupId)
390         throws Exception {
391 
392         List<Long> permissionIds = new ArrayList<Long>();
393 
394         Connection con = null;
395         PreparedStatement ps = null;
396         ResultSet rs = null;
397 
398         try {
399             con = DataAccess.getConnection();
400 
401             ps = con.prepareStatement(
402                 "select " + tablePKCol1 + " from " + tableName1 + " " +
403                 "inner join " + tableName2 + " on " + tableName2 + "." +
404                     tablePKCol2 + " = " + tableName1 + "." + tablePKCol2 + " " +
405                 "where groupId != " + guestGroupId);
406 
407             rs = ps.executeQuery();
408 
409             while (rs.next()) {
410                 String primKey = String.valueOf(rs.getLong(tablePKCol1));
411 
412                 permissionIds.addAll(
413                     getPermissionIds(className, primKey, guestGroupId));
414             }
415         }
416         finally {
417             DataAccess.cleanUp(con, ps, rs);
418         }
419 
420         return permissionIds;
421     }
422 
423     protected List<Long> getPermissionIds(
424             String className, String primKey, long guestGroupId)
425         throws Exception {
426 
427         List<Long> permissionIds = new ArrayList<Long>();
428 
429         Connection con = null;
430         PreparedStatement ps = null;
431         ResultSet rs = null;
432 
433         try {
434             con = DataAccess.getConnection();
435 
436             ps = con.prepareStatement(_GET_PERMISSION_IDS_2);
437 
438             ps.setLong(1, guestGroupId);
439             ps.setString(2, primKey);
440             ps.setString(3, className);
441 
442             rs = ps.executeQuery();
443 
444             while (rs.next()) {
445                 long permissionId = rs.getLong("permissionId");
446 
447                 permissionIds.add(permissionId);
448             }
449         }
450         finally {
451             DataAccess.cleanUp(con, ps, rs);
452         }
453 
454         return permissionIds;
455     }
456 
457     protected List<Long> getPlids(long guestGroupId) throws Exception {
458         List<Long> plids = new ArrayList<Long>();
459 
460         Connection con = null;
461         PreparedStatement ps = null;
462         ResultSet rs = null;
463 
464         try {
465             con = DataAccess.getConnection();
466 
467             ps = con.prepareStatement(_GET_PLIDS);
468 
469             ps.setLong(1, guestGroupId);
470 
471             rs = ps.executeQuery();
472 
473             while (rs.next()) {
474                 long plid = rs.getLong("plid");
475 
476                 plids.add(plid);
477             }
478         }
479         finally {
480             DataAccess.cleanUp(con, ps, rs);
481         }
482 
483         return plids;
484     }
485 
486     private static final String _GET_COMPANY_IDS =
487         "select companyId from Company";
488 
489     private static final String _GET_DEFAULT_USER_ID =
490         "select userId from User_ where companyId = ? and defaultUser = ?";
491 
492     private static final String _GET_GUEST_GROUP_ID =
493         "select groupId from Group_ where companyId = ? and name = ?";
494 
495     private static final String _GET_PERMISSION_IDS_1 =
496         "select Groups_Permissions.permissionId from Groups_Permissions " +
497         "inner join Permission_ on Permission_.permissionId = " +
498             "Groups_Permissions.permissionId " +
499         "inner join Resource_ on Resource_.resourceId = " +
500             "Permission_.resourceId " +
501         "inner join ResourceCode on ResourceCode.codeId = Resource_.codeId " +
502         "where Groups_Permissions.groupId = ? and Resource_.primKey = ?";
503 
504     private static final String _GET_PERMISSION_IDS_2 =
505         "select Groups_Permissions.permissionId from Groups_Permissions " +
506         "inner join Permission_ on Permission_.permissionId = " +
507             "Groups_Permissions.permissionId " +
508         "inner join Resource_ on Resource_.resourceId = " +
509             "Permission_.resourceId " +
510         "inner join ResourceCode on ResourceCode.codeId = Resource_.codeId " +
511         "where Groups_Permissions.groupId = ? and Resource_.primKey = ? and " +
512             "ResourceCode.name = ?";
513 
514     private static final String _GET_PLIDS =
515         "select plid from Layout where Layout.groupId != ?";
516 
517     private static Log _log = LogFactoryUtil.getLog(UpgradePermission.class);
518 
519 }