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