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