001    /**
002     * Copyright (c) 2000-2013 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.v6_1_0;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.log.Log;
019    import com.liferay.portal.kernel.log.LogFactoryUtil;
020    import com.liferay.portal.kernel.repository.model.FileVersion;
021    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
022    import com.liferay.portal.kernel.util.CharPool;
023    import com.liferay.portal.kernel.util.GetterUtil;
024    import com.liferay.portal.kernel.util.MimeTypesUtil;
025    import com.liferay.portal.kernel.util.SetUtil;
026    import com.liferay.portal.kernel.util.StringBundler;
027    import com.liferay.portal.kernel.util.StringUtil;
028    import com.liferay.portal.repository.liferayrepository.model.LiferayFileVersion;
029    import com.liferay.portal.upgrade.v6_1_0.util.DLFileVersionTable;
030    import com.liferay.portal.util.PropsValues;
031    import com.liferay.portlet.documentlibrary.model.DLFileEntry;
032    import com.liferay.portlet.documentlibrary.model.DLFileVersion;
033    import com.liferay.portlet.documentlibrary.model.impl.DLFileVersionImpl;
034    import com.liferay.portlet.documentlibrary.util.ImageProcessorUtil;
035    
036    import java.sql.Connection;
037    import java.sql.PreparedStatement;
038    import java.sql.ResultSet;
039    import java.sql.SQLException;
040    import java.sql.Timestamp;
041    
042    import java.util.Set;
043    
044    /**
045     * @author Brian Wing Shun Chan
046     * @author Douglas Wong
047     * @author Alexander Chow
048     * @author Minhchau Dang
049     */
050    public class UpgradeDocumentLibrary extends UpgradeProcess {
051    
052            protected void addSync(
053                            long syncId, long companyId, Timestamp createDate,
054                            Timestamp modifiedDate, long fileId, long repositoryId,
055                            long parentFolderId, String event, String type)
056                    throws Exception {
057    
058                    Connection con = null;
059                    PreparedStatement ps = null;
060    
061                    try {
062                            con = DataAccess.getUpgradeOptimizedConnection();
063    
064                            ps = con.prepareStatement(
065                                    "insert into DLSync (syncId, companyId, createDate, " +
066                                            "modifiedDate, fileId, repositoryId, parentFolderId, " +
067                                                    "event, type_) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
068    
069                            ps.setLong(1, syncId);
070                            ps.setLong(2, companyId);
071                            ps.setTimestamp(3, createDate);
072                            ps.setTimestamp(4, createDate);
073                            ps.setLong(5, fileId);
074                            ps.setLong(6, repositoryId);
075                            ps.setLong(7, parentFolderId);
076                            ps.setString(8, event);
077                            ps.setString(9, type);
078    
079                            ps.executeUpdate();
080                    }
081                    finally {
082                            DataAccess.cleanUp(con, ps);
083                    }
084            }
085    
086            @Override
087            protected void doUpgrade() throws Exception {
088                    updateFileEntries();
089                    updateFileRanks();
090                    updateFileShortcuts();
091                    updateFileVersions();
092                    updateLocks();
093    
094                    if (PropsValues.DL_FILE_ENTRY_PREVIEW_AUTO_CREATE_ON_UPGRADE) {
095                            updateThumbnails();
096                    }
097    
098                    //updateSyncs();
099            }
100    
101            protected long getFileEntryId(long groupId, long folderId, String name)
102                    throws Exception {
103    
104                    Connection con = null;
105                    PreparedStatement ps = null;
106                    ResultSet rs = null;
107    
108                    try {
109                            con = DataAccess.getUpgradeOptimizedConnection();
110    
111                            ps = con.prepareStatement(
112                                    "select fileEntryId from DLFileEntry where groupId = ? and " +
113                                            "folderId = ? and name = ?");
114    
115                            ps.setLong(1, groupId);
116                            ps.setLong(2, folderId);
117                            ps.setString(3, name);
118    
119                            rs = ps.executeQuery();
120    
121                            if (rs.next()) {
122                                    return rs.getLong("fileEntryId");
123                            }
124    
125                            return 0;
126                    }
127                    finally {
128                            DataAccess.cleanUp(con, ps, rs);
129                    }
130            }
131    
132            protected long getGroupId(long folderId) throws Exception {
133                    Connection con = null;
134                    PreparedStatement ps = null;
135                    ResultSet rs = null;
136    
137                    long groupId = 0;
138    
139                    try {
140                            con = DataAccess.getUpgradeOptimizedConnection();
141    
142                            ps = con.prepareStatement(
143                                    "select groupId from DLFolder where folderId = ?");
144    
145                            ps.setLong(1, folderId);
146    
147                            rs = ps.executeQuery();
148    
149                            if (rs.next()) {
150                                    groupId = rs.getLong("groupId");
151                            }
152                    }
153                    finally {
154                            DataAccess.cleanUp(con, ps, rs);
155                    }
156    
157                    return groupId;
158            }
159    
160            protected void updateFileEntries() throws Exception {
161                    Connection con = null;
162                    PreparedStatement ps = null;
163                    ResultSet rs = null;
164    
165                    try {
166                            con = DataAccess.getUpgradeOptimizedConnection();
167    
168                            ps = con.prepareStatement(
169                                    "select fileEntryId, extension from DLFileEntry");
170    
171                            rs = ps.executeQuery();
172    
173                            while (rs.next()) {
174                                    long fileEntryId = rs.getLong("fileEntryId");
175                                    String extension = rs.getString("extension");
176    
177                                    String mimeType = MimeTypesUtil.getExtensionContentType(
178                                            extension);
179    
180                                    runSQL(
181                                            "update DLFileEntry set mimeType = '" + mimeType +
182                                                    "' where fileEntryId = " + fileEntryId);
183                            }
184                    }
185                    finally {
186                            DataAccess.cleanUp(con, ps, rs);
187                    }
188            }
189    
190            protected void updateFileRanks() throws Exception {
191                    Connection con = null;
192                    PreparedStatement ps = null;
193                    ResultSet rs = null;
194    
195                    try {
196                            con = DataAccess.getUpgradeOptimizedConnection();
197    
198                            ps = con.prepareStatement(
199                                    "select groupId, fileRankId, folderId, name from DLFileRank");
200    
201                            rs = ps.executeQuery();
202    
203                            while (rs.next()) {
204                                    long groupId = rs.getLong("groupId");
205                                    long fileRankId = rs.getLong("fileRankId");
206                                    long folderId = rs.getLong("folderId");
207                                    String name = rs.getString("name");
208    
209                                    long fileEntryId = getFileEntryId(groupId, folderId, name);
210    
211                                    if (fileEntryId != 0) {
212                                            runSQL(
213                                                    "update DLFileRank set fileEntryId = " + fileEntryId +
214                                                            " where fileRankId = " + fileRankId);
215                                    }
216                                    else {
217                                            runSQL(
218                                                    "delete from DLFileRank where fileRankId = " +
219                                                            fileRankId);
220                                    }
221                            }
222                    }
223                    finally {
224                            DataAccess.cleanUp(con, ps, rs);
225                    }
226    
227                    runSQL("alter table DLFileRank drop column folderId");
228                    runSQL("alter table DLFileRank drop column name");
229            }
230    
231            protected void updateFileShortcuts() throws Exception {
232                    Connection con = null;
233                    PreparedStatement ps = null;
234                    ResultSet rs = null;
235    
236                    try {
237                            con = DataAccess.getUpgradeOptimizedConnection();
238    
239                            ps = con.prepareStatement(
240                                    "select fileShortcutId, toFolderId, toName from " +
241                                            "DLFileShortcut");
242    
243                            rs = ps.executeQuery();
244    
245                            while (rs.next()) {
246                                    long fileShortcutId = rs.getLong("fileShortcutId");
247                                    long toFolderId = rs.getLong("toFolderId");
248                                    String toName = rs.getString("toName");
249    
250                                    long groupId = getGroupId(toFolderId);
251    
252                                    long toFileEntryId = getFileEntryId(
253                                            groupId, toFolderId, toName);
254    
255                                    runSQL(
256                                            "update DLFileShortcut set toFileEntryId = " +
257                                                    toFileEntryId + " where fileShortcutId = " +
258                                                            fileShortcutId);
259                            }
260                    }
261                    finally {
262                            DataAccess.cleanUp(con, ps, rs);
263                    }
264    
265                    runSQL("alter table DLFileShortcut drop column toFolderId");
266                    runSQL("alter table DLFileShortcut drop column toName");
267            }
268    
269            protected void updateFileVersions() throws Exception {
270                    Connection con = null;
271                    PreparedStatement ps = null;
272                    ResultSet rs = null;
273    
274                    try {
275                            con = DataAccess.getUpgradeOptimizedConnection();
276    
277                            ps = con.prepareStatement(
278                                    "select groupId, fileVersionId, folderId, name, extension " +
279                                            "from DLFileVersion");
280    
281                            rs = ps.executeQuery();
282    
283                            while (rs.next()) {
284                                    long groupId = rs.getLong("groupId");
285                                    long fileVersionId = rs.getLong("fileVersionId");
286                                    long folderId = rs.getLong("folderId");
287                                    String name = rs.getString("name");
288                                    String extension = rs.getString("extension");
289    
290                                    String mimeType = MimeTypesUtil.getExtensionContentType(
291                                            extension);
292    
293                                    long fileEntryId = getFileEntryId(groupId, folderId, name);
294    
295                                    runSQL(
296                                            "update DLFileVersion set fileEntryId = " + fileEntryId +
297                                                    ", mimeType = '" + mimeType +
298                                                            "' where fileVersionId = " + fileVersionId);
299                            }
300                    }
301                    finally {
302                            DataAccess.cleanUp(con, ps, rs);
303                    }
304    
305                    try {
306                            runSQL("alter_column_type DLFileVersion extraSettings TEXT null");
307                            runSQL("alter_column_type DLFileVersion title VARCHAR(255) null");
308                            runSQL("alter table DLFileVersion drop column name");
309                    }
310                    catch (SQLException sqle) {
311                            upgradeTable(
312                                    DLFileVersionTable.TABLE_NAME, DLFileVersionTable.TABLE_COLUMNS,
313                                    DLFileVersionTable.TABLE_SQL_CREATE,
314                                    DLFileVersionTable.TABLE_SQL_ADD_INDEXES);
315                    }
316            }
317    
318            protected void updateLocks() throws Exception {
319                    Connection con = null;
320                    PreparedStatement ps = null;
321                    ResultSet rs = null;
322    
323                    try {
324                            con = DataAccess.getUpgradeOptimizedConnection();
325    
326                            ps = con.prepareStatement(
327                                    "select lockId, key_ from Lock_ where className = ?");
328    
329                            ps.setString(1, DLFileEntry.class.getName());
330    
331                            rs = ps.executeQuery();
332    
333                            while (rs.next()) {
334                                    long lockId = rs.getLong("lockId");
335                                    String key = rs.getString("key_");
336    
337                                    String[] keyArray = StringUtil.split(key, CharPool.POUND);
338    
339                                    if (keyArray.length != 3) {
340                                            continue;
341                                    }
342    
343                                    long groupId = GetterUtil.getLong(keyArray[0]);
344                                    long folderId = GetterUtil.getLong(keyArray[1]);
345                                    String name = keyArray[2];
346    
347                                    long fileEntryId = getFileEntryId(groupId, folderId, name);
348    
349                                    if (fileEntryId > 0) {
350                                            runSQL(
351                                                    "update Lock_ set key_ = '" + fileEntryId +
352                                                            "' where lockId = " + lockId);
353                                    }
354                            }
355                    }
356                    finally {
357                            DataAccess.cleanUp(con, ps, rs);
358                    }
359            }
360    
361            protected void updateSyncs() throws Exception {
362                    Connection con = null;
363                    PreparedStatement ps = null;
364                    ResultSet rs = null;
365    
366                    try {
367                            con = DataAccess.getUpgradeOptimizedConnection();
368    
369                            StringBundler sb = new StringBundler(10);
370    
371                            sb.append("select DLFileEntry.fileEntryId as fileId, ");
372                            sb.append("DLFileEntry.groupId as groupId, DLFileEntry.companyId");
373                            sb.append(" as companyId, DLFileEntry.createDate as createDate, ");
374                            sb.append("DLFileEntry.folderId as parentFolderId, 'file' as ");
375                            sb.append("type from DLFileEntry union all select ");
376                            sb.append("DLFolder.folderId as fileId, DLFolder.groupId as ");
377                            sb.append("groupId, DLFolder.companyId as companyId, ");
378                            sb.append("DLFolder.createDate as createDate, ");
379                            sb.append("DLFolder.parentFolderId as parentFolderId, 'folder' ");
380                            sb.append("as type from DLFolder");
381    
382                            String sql = sb.toString();
383    
384                            ps = con.prepareStatement(sql);
385    
386                            rs = ps.executeQuery();
387    
388                            while (rs.next()) {
389                                    long fileId = rs.getLong("fileId");
390                                    long groupId = rs.getLong("groupId");
391                                    long companyId = rs.getLong("companyId");
392                                    Timestamp createDate = rs.getTimestamp("createDate");
393                                    long parentFolderId = rs.getLong("parentFolderId");
394                                    String type = rs.getString("type");
395    
396                                    addSync(
397                                            increment(), companyId, createDate, createDate, fileId,
398                                            groupId, parentFolderId, "add", type);
399                            }
400                    }
401                    finally {
402                            DataAccess.cleanUp(con, ps, rs);
403                    }
404            }
405    
406            protected void updateThumbnails() throws Exception {
407                    Connection con = null;
408                    PreparedStatement ps = null;
409                    ResultSet rs = null;
410    
411                    try {
412                            con = DataAccess.getUpgradeOptimizedConnection();
413    
414                            ps = con.prepareStatement("select fileEntryId from DLFileEntry");
415    
416                            rs = ps.executeQuery();
417    
418                            while (rs.next()) {
419                                    long fileEntryId = rs.getLong("fileEntryId");
420    
421                                    updateThumbnails(fileEntryId);
422                            }
423                    }
424                    finally {
425                            DataAccess.cleanUp(con, ps, rs);
426                    }
427            }
428    
429            protected void updateThumbnails(long fileEntryId) throws Exception {
430                    Connection con = null;
431                    PreparedStatement ps = null;
432                    ResultSet rs = null;
433    
434                    try {
435                            con = DataAccess.getUpgradeOptimizedConnection();
436    
437                            ps = con.prepareStatement(
438                                    "select fileVersionId, userId, extension, mimeType, version " +
439                                            "from DLFileVersion where fileEntryId = " + fileEntryId +
440                                                    " order by version asc");
441    
442                            rs = ps.executeQuery();
443    
444                            while (rs.next()) {
445                                    long fileVersionId = rs.getLong("fileVersionId");
446                                    long userId = rs.getLong("userId");
447                                    String extension = rs.getString("extension");
448                                    String mimeType = rs.getString("mimeType");
449                                    String version = rs.getString("version");
450    
451                                    if (_imageMimeTypes.contains(mimeType)) {
452                                            DLFileVersion dlFileVersion = new DLFileVersionImpl();
453    
454                                            dlFileVersion.setFileVersionId(fileVersionId);
455                                            dlFileVersion.setUserId(userId);
456                                            dlFileVersion.setFileEntryId(fileEntryId);
457                                            dlFileVersion.setExtension(extension);
458                                            dlFileVersion.setMimeType(mimeType);
459                                            dlFileVersion.setVersion(version);
460    
461                                            FileVersion fileVersion = new LiferayFileVersion(
462                                                    dlFileVersion);
463    
464                                            try {
465                                                    ImageProcessorUtil.generateImages(null, fileVersion);
466                                            }
467                                            catch (Exception e) {
468                                                    if (_log.isWarnEnabled()) {
469                                                            _log.warn(
470                                                                    "Unable to generate thumbnails for " +
471                                                                            fileVersion.getFileVersionId(),
472                                                                    e);
473                                                    }
474                                            }
475                                    }
476                            }
477                    }
478                    finally {
479                            DataAccess.cleanUp(con, ps, rs);
480                    }
481            }
482    
483            private static Log _log = LogFactoryUtil.getLog(
484                    UpgradeDocumentLibrary.class);
485    
486            private static Set<String> _imageMimeTypes = SetUtil.fromArray(
487                    PropsValues.DL_FILE_ENTRY_PREVIEW_IMAGE_MIME_TYPES);
488    
489    }