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