001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.upgrade.v6_1_0;
016    
017    import com.liferay.document.library.kernel.model.DLFileVersion;
018    import com.liferay.document.library.kernel.util.ImageProcessorUtil;
019    import com.liferay.portal.kernel.log.Log;
020    import com.liferay.portal.kernel.log.LogFactoryUtil;
021    import com.liferay.portal.kernel.repository.model.FileVersion;
022    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
023    import com.liferay.portal.kernel.util.CharPool;
024    import com.liferay.portal.kernel.util.GetterUtil;
025    import com.liferay.portal.kernel.util.LoggingTimer;
026    import com.liferay.portal.kernel.util.MimeTypesUtil;
027    import com.liferay.portal.kernel.util.SetUtil;
028    import com.liferay.portal.kernel.util.StringUtil;
029    import com.liferay.portal.repository.liferayrepository.model.LiferayFileVersion;
030    import com.liferay.portal.upgrade.v6_1_0.util.DLFileVersionTable;
031    import com.liferay.portal.util.PropsValues;
032    import com.liferay.portlet.documentlibrary.model.impl.DLFileVersionImpl;
033    
034    import java.sql.PreparedStatement;
035    import java.sql.ResultSet;
036    
037    import java.util.Set;
038    
039    /**
040     * @author Brian Wing Shun Chan
041     * @author Douglas Wong
042     * @author Alexander Chow
043     * @author Minhchau Dang
044     */
045    public class UpgradeDocumentLibrary extends UpgradeProcess {
046    
047            @Override
048            protected void doUpgrade() throws Exception {
049                    updateFileEntries();
050                    updateFileRanks();
051                    updateFileShortcuts();
052                    updateFileVersions();
053                    updateLocks();
054    
055                    if (PropsValues.DL_FILE_ENTRY_PREVIEW_AUTO_CREATE_ON_UPGRADE) {
056                            updateThumbnails();
057                    }
058    
059                    //UpdateSyncUtil.updateSyncs(connection);
060            }
061    
062            protected long getFileEntryId(long groupId, long folderId, String name)
063                    throws Exception {
064    
065                    try (PreparedStatement ps = connection.prepareStatement(
066                                    "select fileEntryId from DLFileEntry where groupId = ? and " +
067                                            "folderId = ? and name = ?")) {
068    
069                            ps.setLong(1, groupId);
070                            ps.setLong(2, folderId);
071                            ps.setString(3, name);
072    
073                            try (ResultSet rs = ps.executeQuery()) {
074                                    if (rs.next()) {
075                                            return rs.getLong("fileEntryId");
076                                    }
077    
078                                    return 0;
079                            }
080                    }
081            }
082    
083            protected long getGroupId(long folderId) throws Exception {
084                    long groupId = 0;
085    
086                    try (PreparedStatement ps = connection.prepareStatement(
087                                    "select groupId from DLFolder where folderId = ?")) {
088    
089                            ps.setLong(1, folderId);
090    
091                            try (ResultSet rs = ps.executeQuery()) {
092                                    if (rs.next()) {
093                                            groupId = rs.getLong("groupId");
094                                    }
095                            }
096                    }
097    
098                    return groupId;
099            }
100    
101            protected void updateFileEntries() throws Exception {
102                    try (LoggingTimer loggingTimer = new LoggingTimer();
103                            PreparedStatement ps = connection.prepareStatement(
104                                    "select fileEntryId, extension from DLFileEntry");
105                            ResultSet rs = ps.executeQuery()) {
106    
107                            while (rs.next()) {
108                                    long fileEntryId = rs.getLong("fileEntryId");
109                                    String extension = rs.getString("extension");
110    
111                                    String mimeType = MimeTypesUtil.getExtensionContentType(
112                                            extension);
113    
114                                    runSQL(
115                                            "update DLFileEntry set mimeType = '" + mimeType +
116                                                    "' where fileEntryId = " + fileEntryId);
117                            }
118                    }
119            }
120    
121            protected void updateFileRanks() throws Exception {
122                    try (LoggingTimer loggingTimer = new LoggingTimer();
123                            PreparedStatement ps = connection.prepareStatement(
124                                    "select groupId, fileRankId, folderId, name from DLFileRank");
125                            ResultSet rs = ps.executeQuery()) {
126    
127                            while (rs.next()) {
128                                    long groupId = rs.getLong("groupId");
129                                    long fileRankId = rs.getLong("fileRankId");
130                                    long folderId = rs.getLong("folderId");
131                                    String name = rs.getString("name");
132    
133                                    long fileEntryId = getFileEntryId(groupId, folderId, name);
134    
135                                    runSQL(
136                                            "update DLFileRank set fileEntryId = " + fileEntryId +
137                                                    " where fileRankId = " + fileRankId);
138                            }
139    
140                            runSQL("alter table DLFileRank drop column folderId");
141                            runSQL("alter table DLFileRank drop column name");
142                    }
143            }
144    
145            protected void updateFileShortcuts() throws Exception {
146                    try (LoggingTimer loggingTimer = new LoggingTimer();
147                            PreparedStatement ps = connection.prepareStatement(
148                                    "select fileShortcutId, toFolderId, toName from " +
149                                            "DLFileShortcut");
150                            ResultSet rs = ps.executeQuery()) {
151    
152                            while (rs.next()) {
153                                    long fileShortcutId = rs.getLong("fileShortcutId");
154                                    long toFolderId = rs.getLong("toFolderId");
155                                    String toName = rs.getString("toName");
156    
157                                    long groupId = getGroupId(toFolderId);
158    
159                                    long toFileEntryId = getFileEntryId(
160                                            groupId, toFolderId, toName);
161    
162                                    runSQL(
163                                            "update DLFileShortcut set toFileEntryId = " +
164                                                    toFileEntryId + " where fileShortcutId = " +
165                                                            fileShortcutId);
166                            }
167    
168                            runSQL("alter table DLFileShortcut drop column toFolderId");
169                            runSQL("alter table DLFileShortcut drop column toName");
170                    }
171            }
172    
173            protected void updateFileVersions() throws Exception {
174                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
175                            try (PreparedStatement ps = connection.prepareStatement(
176                                            "select groupId, fileVersionId, folderId, name, extension" +
177                                                    " from DLFileVersion");
178                                    ResultSet rs = ps.executeQuery()) {
179    
180                                    while (rs.next()) {
181                                            long groupId = rs.getLong("groupId");
182                                            long fileVersionId = rs.getLong("fileVersionId");
183                                            long folderId = rs.getLong("folderId");
184                                            String name = rs.getString("name");
185                                            String extension = rs.getString("extension");
186    
187                                            String mimeType = MimeTypesUtil.getExtensionContentType(
188                                                    extension);
189    
190                                            long fileEntryId = getFileEntryId(groupId, folderId, name);
191    
192                                            runSQL(
193                                                    "update DLFileVersion set fileEntryId = " +
194                                                            fileEntryId + ", mimeType = '" + mimeType +
195                                                                    "' where fileVersionId = " + fileVersionId);
196                                    }
197                            }
198    
199                            alter(
200                                    DLFileVersionTable.class,
201                                    new AlterColumnType("extraSettings", "TEXT null"),
202                                    new AlterColumnType("title", "VARCHAR(255) null"),
203                                    new AlterTableDropColumn("name"));
204                    }
205            }
206    
207            protected void updateLocks() throws Exception {
208                    try (LoggingTimer loggingTimer = new LoggingTimer();
209                            PreparedStatement ps = connection.prepareStatement(
210                                    "select lockId, key_ from Lock_ where className = ?")) {
211    
212                            ps.setString(
213                                    1, "com.liferay.portlet.documentlibrary.model.DLFileEntry");
214    
215                            try (ResultSet rs = ps.executeQuery()) {
216                                    while (rs.next()) {
217                                            long lockId = rs.getLong("lockId");
218                                            String key = rs.getString("key_");
219    
220                                            String[] keyArray = StringUtil.split(key, CharPool.POUND);
221    
222                                            if (keyArray.length != 3) {
223                                                    continue;
224                                            }
225    
226                                            long groupId = GetterUtil.getLong(keyArray[0]);
227                                            long folderId = GetterUtil.getLong(keyArray[1]);
228                                            String name = keyArray[2];
229    
230                                            long fileEntryId = getFileEntryId(groupId, folderId, name);
231    
232                                            if (fileEntryId > 0) {
233                                                    runSQL(
234                                                            "update Lock_ set key_ = '" + fileEntryId +
235                                                                    "' where lockId = " + lockId);
236                                            }
237                                    }
238                            }
239                    }
240            }
241    
242            protected void updateThumbnails() throws Exception {
243                    try (LoggingTimer loggingTimer = new LoggingTimer();
244                            PreparedStatement ps = connection.prepareStatement(
245                                    "select fileEntryId from DLFileEntry");
246                            ResultSet rs = ps.executeQuery()) {
247    
248                            while (rs.next()) {
249                                    long fileEntryId = rs.getLong("fileEntryId");
250    
251                                    updateThumbnails(fileEntryId);
252                            }
253                    }
254            }
255    
256            protected void updateThumbnails(long fileEntryId) throws Exception {
257                    try (PreparedStatement ps = connection.prepareStatement(
258                                    "select fileVersionId, userId, extension, mimeType, version " +
259                                            "from DLFileVersion where fileEntryId = " + fileEntryId +
260                                                    " order by version asc");
261                            ResultSet rs = ps.executeQuery()) {
262    
263                            while (rs.next()) {
264                                    long fileVersionId = rs.getLong("fileVersionId");
265                                    long userId = rs.getLong("userId");
266                                    String extension = rs.getString("extension");
267                                    String mimeType = rs.getString("mimeType");
268                                    String version = rs.getString("version");
269    
270                                    if (_imageMimeTypes.contains(mimeType)) {
271                                            DLFileVersion dlFileVersion = new DLFileVersionImpl();
272    
273                                            dlFileVersion.setFileVersionId(fileVersionId);
274                                            dlFileVersion.setUserId(userId);
275                                            dlFileVersion.setFileEntryId(fileEntryId);
276                                            dlFileVersion.setExtension(extension);
277                                            dlFileVersion.setMimeType(mimeType);
278                                            dlFileVersion.setVersion(version);
279    
280                                            FileVersion fileVersion = new LiferayFileVersion(
281                                                    dlFileVersion);
282    
283                                            try {
284                                                    ImageProcessorUtil.generateImages(null, fileVersion);
285                                            }
286                                            catch (Exception e) {
287                                                    if (_log.isWarnEnabled()) {
288                                                            _log.warn(
289                                                                    "Unable to generate thumbnails for " +
290                                                                            fileVersion.getFileVersionId(),
291                                                                    e);
292                                                    }
293                                            }
294                                    }
295                            }
296                    }
297            }
298    
299            private static final Log _log = LogFactoryUtil.getLog(
300                    UpgradeDocumentLibrary.class);
301    
302            private static final Set<String> _imageMimeTypes = SetUtil.fromArray(
303                    PropsValues.DL_FILE_ENTRY_PREVIEW_IMAGE_MIME_TYPES);
304    
305    }