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