001    /**
002     * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
013     */
014    
015    package com.liferay.portal.upgrade.v4_3_5;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
019    import com.liferay.portal.model.GroupConstants;
020    import com.liferay.portal.model.Layout;
021    import com.liferay.portal.model.PortletConstants;
022    import com.liferay.portal.util.PortalInstances;
023    import com.liferay.portlet.blogs.model.BlogsEntry;
024    import com.liferay.portlet.bookmarks.model.BookmarksEntry;
025    import com.liferay.portlet.bookmarks.model.BookmarksFolder;
026    import com.liferay.portlet.calendar.model.CalEvent;
027    import com.liferay.portlet.documentlibrary.model.DLFileEntry;
028    import com.liferay.portlet.documentlibrary.model.DLFileShortcut;
029    import com.liferay.portlet.documentlibrary.model.DLFolder;
030    import com.liferay.portlet.imagegallery.model.IGFolder;
031    import com.liferay.portlet.imagegallery.model.IGImage;
032    import com.liferay.portlet.journal.model.JournalArticle;
033    import com.liferay.portlet.journal.model.JournalStructure;
034    import com.liferay.portlet.journal.model.JournalTemplate;
035    import com.liferay.portlet.messageboards.model.MBCategory;
036    import com.liferay.portlet.messageboards.model.MBMessage;
037    import com.liferay.portlet.polls.model.PollsQuestion;
038    import com.liferay.portlet.shopping.model.ShoppingCategory;
039    import com.liferay.portlet.shopping.model.ShoppingItem;
040    import com.liferay.portlet.softwarecatalog.model.SCFrameworkVersion;
041    import com.liferay.portlet.softwarecatalog.model.SCProductEntry;
042    import com.liferay.portlet.wiki.model.WikiNode;
043    import com.liferay.portlet.wiki.model.WikiPage;
044    
045    import java.sql.Connection;
046    import java.sql.PreparedStatement;
047    import java.sql.ResultSet;
048    
049    import java.util.ArrayList;
050    import java.util.List;
051    
052    /**
053     * @author Brian Wing Shun Chan
054     */
055    public class UpgradePermission extends UpgradeProcess {
056    
057            protected void copyPermissions(long defaultUserId, long guestGroupId)
058                    throws Exception {
059    
060                    if ((defaultUserId == 0) || (guestGroupId == 0)) {
061                            return;
062                    }
063    
064                    runSQL("delete from Users_Permissions where userId = " + defaultUserId);
065    
066                    runSQL(
067                            "insert into Users_Permissions (userId, permissionId) select " +
068                                    defaultUserId + ", Groups_Permissions.permissionId from " +
069                                            "Groups_Permissions where groupId = " + guestGroupId);
070    
071                    for (long plid : getPlids(guestGroupId)) {
072                            deletePortletPermissionIds(plid, guestGroupId);
073                    }
074    
075                    deletePermissionIds(
076                            Layout.class.getName(), "Layout", "plid", guestGroupId);
077    
078                    deletePermissionIds(
079                            BlogsEntry.class.getName(), "BlogsEntry", "entryId", guestGroupId);
080    
081                    deletePermissionIds(
082                            BookmarksFolder.class.getName(), "BookmarksFolder", "folderId",
083                            guestGroupId);
084                    deletePermissionIds(
085                            BookmarksEntry.class.getName(), "BookmarksEntry", "entryId",
086                            "BookmarksFolder", "folderId", guestGroupId);
087    
088                    deletePermissionIds(
089                            CalEvent.class.getName(), "CalEvent", "eventId", guestGroupId);
090    
091                    deletePermissionIds(
092                            DLFolder.class.getName(), "DLFolder", "folderId", guestGroupId);
093                    deletePermissionIds(
094                            DLFileEntry.class.getName(), "DLFileEntry", "fileEntryId",
095                            "DLFolder", "folderId", guestGroupId);
096                    deletePermissionIds(
097                            DLFileShortcut.class.getName(), "DLFileShortcut", "fileShortcutId",
098                            "DLFolder", "folderId", guestGroupId);
099    
100                    deletePermissionIds(
101                            IGFolder.class.getName(), "IGFolder", "folderId", guestGroupId);
102                    deletePermissionIds(
103                            IGImage.class.getName(), "IGImage", "imageId", "IGFolder",
104                            "folderId", guestGroupId);
105    
106                    deletePermissionIds(
107                            JournalArticle.class.getName(), "JournalArticle", "resourcePrimKey",
108                            guestGroupId);
109                    deletePermissionIds(
110                            JournalStructure.class.getName(), "JournalStructure", "id_",
111                            guestGroupId);
112                    deletePermissionIds(
113                            JournalTemplate.class.getName(), "JournalTemplate", "id_",
114                            guestGroupId);
115    
116                    deletePermissionIds(
117                            MBCategory.class.getName(), "MBCategory", "categoryId",
118                            guestGroupId);
119                    deletePermissionIds(
120                            MBMessage.class.getName(), "MBMessage", "messageId", "MBCategory",
121                            "categoryId", guestGroupId);
122    
123                    deletePermissionIds(
124                            PollsQuestion.class.getName(), "PollsQuestion", "questionId",
125                            guestGroupId);
126    
127                    deletePermissionIds(
128                            SCFrameworkVersion.class.getName(), "SCFrameworkVersion",
129                            "frameworkVersionId", guestGroupId);
130                    deletePermissionIds(
131                            SCProductEntry.class.getName(), "SCProductEntry", "productEntryId",
132                            guestGroupId);
133    
134                    deletePermissionIds(
135                            ShoppingCategory.class.getName(), "ShoppingCategory", "categoryId",
136                            guestGroupId);
137                    deletePermissionIds(
138                            ShoppingItem.class.getName(), "ShoppingItem", "itemId",
139                            "ShoppingCategory", "categoryId", guestGroupId);
140    
141                    deletePermissionIds(
142                            WikiNode.class.getName(), "WikiNode", "nodeId", guestGroupId);
143                    deletePermissionIds(
144                            WikiPage.class.getName(), "WikiPage", "resourcePrimKey", "WikiNode",
145                            "nodeId", guestGroupId);
146            }
147    
148            protected void deletePermissionIds(
149                            String className, String tableName, String tablePKCol,
150                            long guestGroupId)
151                    throws Exception {
152    
153                    List<Long> permissionIds = getPermissionIds(
154                            className, tableName, tablePKCol, guestGroupId);
155    
156                    deletePermissionIds(permissionIds, guestGroupId);
157            }
158    
159            protected void deletePermissionIds(
160                            String className, String tableName1, String tablePKCol1,
161                            String tableName2, String tablePKCol2, long guestGroupId)
162                    throws Exception {
163    
164                    List<Long> permissionIds = getPermissionIds(
165                            className, tableName1, tablePKCol1, tableName2, tablePKCol2,
166                            guestGroupId);
167    
168                    deletePermissionIds(permissionIds, guestGroupId);
169            }
170    
171            protected void deletePermissionIds(
172                            List<Long> permissionIds, long guestGroupId)
173                    throws Exception {
174    
175                    for (long permissionId : permissionIds) {
176                            runSQL(
177                                    "delete from Groups_Permissions where groupId = " +
178                                            guestGroupId + " and permissionId = " + permissionId);
179                    }
180            }
181    
182            protected void deletePortletPermissionIds(long plid, long guestGroupId)
183                    throws Exception {
184    
185                    Connection con = null;
186                    PreparedStatement ps = null;
187                    ResultSet rs = null;
188    
189                    try {
190                            con = DataAccess.getConnection();
191    
192                            ps = con.prepareStatement(
193                                    "select primKey from Resource_ where primKey like ?");
194    
195                            ps.setString(1, plid + PortletConstants.LAYOUT_SEPARATOR + "%");
196    
197                            rs = ps.executeQuery();
198    
199                            while (rs.next()) {
200                                    String primKey = rs.getString("primKey");
201    
202                                    List<Long> permissionIds = getPermissionIds(
203                                            primKey, guestGroupId);
204    
205                                    deletePermissionIds(permissionIds, guestGroupId);
206                            }
207                    }
208                    finally {
209                            DataAccess.cleanUp(con, ps, rs);
210                    }
211            }
212    
213            protected void doUpgrade() throws Exception {
214                    long[] companyIds = PortalInstances.getCompanyIdsBySQL();
215    
216                    for (long companyId : companyIds) {
217                            long defaultUserId = getDefaultUserId(companyId);
218                            long guestGroupId = getGuestGroupId(companyId);
219    
220                            copyPermissions(defaultUserId, guestGroupId);
221                    }
222            }
223    
224            protected long getDefaultUserId(long companyId) throws Exception {
225                    long userId = 0;
226    
227                    Connection con = null;
228                    PreparedStatement ps = null;
229                    ResultSet rs = null;
230    
231                    try {
232                            con = DataAccess.getConnection();
233    
234                            ps = con.prepareStatement(_GET_DEFAULT_USER_ID);
235    
236                            ps.setLong(1, companyId);
237                            ps.setBoolean(2, true);
238    
239                            rs = ps.executeQuery();
240    
241                            while (rs.next()) {
242                                    userId = rs.getLong("userId");
243                            }
244                    }
245                    finally {
246                            DataAccess.cleanUp(con, ps, rs);
247                    }
248    
249                    return userId;
250            }
251    
252            protected long getGuestGroupId(long companyId) throws Exception {
253                    long groupId = 0;
254    
255                    Connection con = null;
256                    PreparedStatement ps = null;
257                    ResultSet rs = null;
258    
259                    try {
260                            con = DataAccess.getConnection();
261    
262                            ps = con.prepareStatement(_GET_GUEST_GROUP_ID);
263    
264                            ps.setLong(1, companyId);
265                            ps.setString(2, GroupConstants.GUEST);
266    
267                            rs = ps.executeQuery();
268    
269                            while (rs.next()) {
270                                    groupId = rs.getLong("groupId");
271                            }
272                    }
273                    finally {
274                            DataAccess.cleanUp(con, ps, rs);
275                    }
276    
277                    return groupId;
278            }
279    
280            protected List<Long> getPermissionIds(String primKey, long guestGroupId)
281                    throws Exception {
282    
283                    List<Long> permissionIds = new ArrayList<Long>();
284    
285                    Connection con = null;
286                    PreparedStatement ps = null;
287                    ResultSet rs = null;
288    
289                    try {
290                            con = DataAccess.getConnection();
291    
292                            ps = con.prepareStatement(_GET_PERMISSION_IDS_1);
293    
294                            ps.setLong(1, guestGroupId);
295                            ps.setString(2, primKey);
296    
297                            rs = ps.executeQuery();
298    
299                            while (rs.next()) {
300                                    long permissionId = rs.getLong("permissionId");
301    
302                                    permissionIds.add(permissionId);
303                            }
304                    }
305                    finally {
306                            DataAccess.cleanUp(con, ps, rs);
307                    }
308    
309                    return permissionIds;
310            }
311    
312            protected List<Long> getPermissionIds(
313                            String className, String tableName, String tablePKCol,
314                            long guestGroupId)
315                    throws Exception {
316    
317                    List<Long> permissionIds = new ArrayList<Long>();
318    
319                    Connection con = null;
320                    PreparedStatement ps = null;
321                    ResultSet rs = null;
322    
323                    try {
324                            con = DataAccess.getConnection();
325    
326                            ps = con.prepareStatement(
327                                    "select " + tablePKCol + " from " + tableName + " " +
328                                    "where groupId != " + guestGroupId);
329    
330                            rs = ps.executeQuery();
331    
332                            while (rs.next()) {
333                                    String primKey = String.valueOf(rs.getLong(tablePKCol));
334    
335                                    permissionIds.addAll(
336                                            getPermissionIds(className, primKey, guestGroupId));
337                            }
338                    }
339                    finally {
340                            DataAccess.cleanUp(con, ps, rs);
341                    }
342    
343                    return permissionIds;
344            }
345    
346            protected List<Long> getPermissionIds(
347                            String className, String tableName1, String tablePKCol1,
348                            String tableName2, String tablePKCol2, long guestGroupId)
349                    throws Exception {
350    
351                    List<Long> permissionIds = new ArrayList<Long>();
352    
353                    Connection con = null;
354                    PreparedStatement ps = null;
355                    ResultSet rs = null;
356    
357                    try {
358                            con = DataAccess.getConnection();
359    
360                            ps = con.prepareStatement(
361                                    "select " + tablePKCol1 + " from " + tableName1 + " " +
362                                    "inner join " + tableName2 + " on " + tableName2 + "." +
363                                            tablePKCol2 + " = " + tableName1 + "." + tablePKCol2 + " " +
364                                    "where groupId != " + guestGroupId);
365    
366                            rs = ps.executeQuery();
367    
368                            while (rs.next()) {
369                                    String primKey = String.valueOf(rs.getLong(tablePKCol1));
370    
371                                    permissionIds.addAll(
372                                            getPermissionIds(className, primKey, guestGroupId));
373                            }
374                    }
375                    finally {
376                            DataAccess.cleanUp(con, ps, rs);
377                    }
378    
379                    return permissionIds;
380            }
381    
382            protected List<Long> getPermissionIds(
383                            String className, String primKey, long guestGroupId)
384                    throws Exception {
385    
386                    List<Long> permissionIds = new ArrayList<Long>();
387    
388                    Connection con = null;
389                    PreparedStatement ps = null;
390                    ResultSet rs = null;
391    
392                    try {
393                            con = DataAccess.getConnection();
394    
395                            ps = con.prepareStatement(_GET_PERMISSION_IDS_2);
396    
397                            ps.setLong(1, guestGroupId);
398                            ps.setString(2, primKey);
399                            ps.setString(3, className);
400    
401                            rs = ps.executeQuery();
402    
403                            while (rs.next()) {
404                                    long permissionId = rs.getLong("permissionId");
405    
406                                    permissionIds.add(permissionId);
407                            }
408                    }
409                    finally {
410                            DataAccess.cleanUp(con, ps, rs);
411                    }
412    
413                    return permissionIds;
414            }
415    
416            protected List<Long> getPlids(long guestGroupId) throws Exception {
417                    List<Long> plids = new ArrayList<Long>();
418    
419                    Connection con = null;
420                    PreparedStatement ps = null;
421                    ResultSet rs = null;
422    
423                    try {
424                            con = DataAccess.getConnection();
425    
426                            ps = con.prepareStatement(_GET_PLIDS);
427    
428                            ps.setLong(1, guestGroupId);
429    
430                            rs = ps.executeQuery();
431    
432                            while (rs.next()) {
433                                    long plid = rs.getLong("plid");
434    
435                                    plids.add(plid);
436                            }
437                    }
438                    finally {
439                            DataAccess.cleanUp(con, ps, rs);
440                    }
441    
442                    return plids;
443            }
444    
445            private static final String _GET_DEFAULT_USER_ID =
446                    "select userId from User_ where companyId = ? and defaultUser = ?";
447    
448            private static final String _GET_GUEST_GROUP_ID =
449                    "select groupId from Group_ where companyId = ? and name = ?";
450    
451            private static final String _GET_PERMISSION_IDS_1 =
452                    "select Groups_Permissions.permissionId from Groups_Permissions " +
453                    "inner join Permission_ on Permission_.permissionId = " +
454                            "Groups_Permissions.permissionId " +
455                    "inner join Resource_ on Resource_.resourceId = " +
456                            "Permission_.resourceId " +
457                    "inner join ResourceCode on ResourceCode.codeId = Resource_.codeId " +
458                    "where Groups_Permissions.groupId = ? and Resource_.primKey = ?";
459    
460            private static final String _GET_PERMISSION_IDS_2 =
461                    "select Groups_Permissions.permissionId from Groups_Permissions " +
462                    "inner join Permission_ on Permission_.permissionId = " +
463                            "Groups_Permissions.permissionId " +
464                    "inner join Resource_ on Resource_.resourceId = " +
465                            "Permission_.resourceId " +
466                    "inner join ResourceCode on ResourceCode.codeId = Resource_.codeId " +
467                    "where Groups_Permissions.groupId = ? and Resource_.primKey = ? and " +
468                            "ResourceCode.name = ?";
469    
470            private static final String _GET_PLIDS =
471                    "select plid from Layout where Layout.groupId != ?";
472    
473    }