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