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