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