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