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