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