001    /**
002     * Copyright (c) 2000-2011 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.upgrade.util.UpgradeTable;
021    import com.liferay.portal.kernel.upgrade.util.UpgradeTableFactoryUtil;
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.ImageProcessor;
035    
036    import java.sql.Connection;
037    import java.sql.Date;
038    import java.sql.PreparedStatement;
039    import java.sql.ResultSet;
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, Date createDate, Date modifiedDate,
053                            long fileId, long repositoryId, long parentFolderId, String event,
054                            String type)
055                    throws Exception {
056    
057                    Connection con = null;
058                    PreparedStatement ps = null;
059    
060                    try {
061                            con = DataAccess.getConnection();
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.setDate(3, createDate);
071                            ps.setDate(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                    updateThumbnails();
093                    //updateSyncs();
094            }
095    
096            protected long getFileEntryId(long groupId, long folderId, String name)
097                    throws Exception {
098    
099                    Connection con = null;
100                    PreparedStatement ps = null;
101                    ResultSet rs = null;
102    
103                    try {
104                            con = DataAccess.getConnection();
105    
106                            ps = con.prepareStatement(
107                                    "select fileEntryId from DLFileEntry where groupId = ? and " +
108                                            "folderId = ? and name = ?");
109    
110                            ps.setLong(1, groupId);
111                            ps.setLong(2, folderId);
112                            ps.setString(3, name);
113    
114                            rs = ps.executeQuery();
115    
116                            if (rs.next()) {
117                                    return rs.getLong("fileEntryId");
118                            }
119    
120                            return 0;
121                    }
122                    finally {
123                            DataAccess.cleanUp(con, ps, rs);
124                    }
125            }
126    
127            protected long getGroupId(long folderId) throws Exception {
128                    Connection con = null;
129                    PreparedStatement ps = null;
130                    ResultSet rs = null;
131    
132                    long groupId = 0;
133    
134                    try {
135                            con = DataAccess.getConnection();
136    
137                            ps = con.prepareStatement(
138                                    "select groupId from DLFolder where folderId = ?");
139    
140                            ps.setLong(1, folderId);
141    
142                            rs = ps.executeQuery();
143    
144                            if (rs.next()) {
145                                    groupId = rs.getLong("groupId");
146                            }
147                    }
148                    finally {
149                            DataAccess.cleanUp(con, ps, rs);
150                    }
151    
152                    return groupId;
153            }
154    
155            protected void updateFileEntries() throws Exception {
156                    Connection con = null;
157                    PreparedStatement ps = null;
158                    ResultSet rs = null;
159    
160                    try {
161                            con = DataAccess.getConnection();
162    
163                            ps = con.prepareStatement(
164                                    "select fileEntryId, extension from DLFileEntry");
165    
166                            rs = ps.executeQuery();
167    
168                            while (rs.next()) {
169                                    long fileEntryId = rs.getLong("fileEntryId");
170                                    String extension = rs.getString("extension");
171    
172                                    String mimeType = MimeTypesUtil.getContentType(
173                                            "A." + extension);
174    
175                                    runSQL(
176                                            "update DLFileEntry set mimeType = '" + mimeType +
177                                                    "' where fileEntryId = " + fileEntryId);
178                            }
179                    }
180                    finally {
181                            DataAccess.cleanUp(con, ps, rs);
182                    }
183            }
184    
185            protected void updateFileRanks() throws Exception {
186                    Connection con = null;
187                    PreparedStatement ps = null;
188                    ResultSet rs = null;
189    
190                    try {
191                            con = DataAccess.getConnection();
192    
193                            ps = con.prepareStatement(
194                                    "select groupId, fileRankId, folderId, name from DLFileRank");
195    
196                            rs = ps.executeQuery();
197    
198                            while (rs.next()) {
199                                    long groupId = rs.getLong("groupId");
200                                    long fileRankId = rs.getLong("fileRankId");
201                                    long folderId = rs.getLong("folderId");
202                                    String name = rs.getString("name");
203    
204                                    long fileEntryId = getFileEntryId(groupId, folderId, name);
205    
206                                    runSQL(
207                                            "update DLFileRank set fileEntryId = " + fileEntryId +
208                                                    " where fileRankId = " + fileRankId);
209                            }
210                    }
211                    finally {
212                            DataAccess.cleanUp(con, ps, rs);
213                    }
214    
215                    runSQL("alter table DLFileRank drop column folderId");
216                    runSQL("alter table DLFileRank drop column name");
217            }
218    
219            protected void updateFileShortcuts() throws Exception {
220                    Connection con = null;
221                    PreparedStatement ps = null;
222                    ResultSet rs = null;
223    
224                    try {
225                            con = DataAccess.getConnection();
226    
227                            ps = con.prepareStatement(
228                                    "select fileShortcutId, toFolderId, toName from " +
229                                            "DLFileShortcut");
230    
231                            rs = ps.executeQuery();
232    
233                            while (rs.next()) {
234                                    long fileShortcutId = rs.getLong("fileShortcutId");
235                                    long toFolderId = rs.getLong("toFolderId");
236                                    String toName = rs.getString("toName");
237    
238                                    long groupId = getGroupId(toFolderId);
239    
240                                    long toFileEntryId = getFileEntryId(
241                                            groupId, toFolderId, toName);
242    
243                                    runSQL(
244                                            "update DLFileShortcut set toFileEntryId = " +
245                                                    toFileEntryId + " where fileShortcutId = " +
246                                                            fileShortcutId);
247                            }
248                    }
249                    finally {
250                            DataAccess.cleanUp(con, ps, rs);
251                    }
252    
253                    runSQL("alter table DLFileShortcut drop column toFolderId");
254                    runSQL("alter table DLFileShortcut drop column toName");
255            }
256    
257            protected void updateFileVersions() throws Exception {
258                    Connection con = null;
259                    PreparedStatement ps = null;
260                    ResultSet rs = null;
261    
262                    try {
263                            con = DataAccess.getConnection();
264    
265                            ps = con.prepareStatement(
266                                    "select groupId, fileVersionId, folderId, name, extension " +
267                                            "from DLFileVersion");
268    
269                            rs = ps.executeQuery();
270    
271                            while (rs.next()) {
272                                    long groupId = rs.getLong("groupId");
273                                    long fileVersionId = rs.getLong("fileVersionId");
274                                    long folderId = rs.getLong("folderId");
275                                    String name = rs.getString("name");
276                                    String extension = rs.getString("extension");
277    
278                                    String mimeType = MimeTypesUtil.getContentType(
279                                            "A." + extension);
280    
281                                    long fileEntryId = getFileEntryId(groupId, folderId, name);
282    
283                                    runSQL(
284                                            "update DLFileVersion set fileEntryId = " + fileEntryId +
285                                                    ", mimeType = '" + mimeType +
286                                                            "' where fileVersionId = " + fileVersionId);
287                            }
288                    }
289                    finally {
290                            DataAccess.cleanUp(con, ps, rs);
291                    }
292    
293                    try {
294                            runSQL("alter_column_type DLFileVersion extraSettings TEXT null");
295                            runSQL("alter_column_type DLFileVersion title VARCHAR(255) null");
296                            runSQL("alter table DLFileVersion drop column name");
297                    }
298                    catch (Exception e) {
299                            UpgradeTable upgradeTable = UpgradeTableFactoryUtil.getUpgradeTable(
300                                    DLFileVersionTable.TABLE_NAME,
301                                    DLFileVersionTable.TABLE_COLUMNS);
302    
303                            upgradeTable.setCreateSQL(DLFileVersionTable.TABLE_SQL_CREATE);
304                            upgradeTable.setIndexesSQL(
305                                    DLFileVersionTable.TABLE_SQL_ADD_INDEXES);
306    
307                            upgradeTable.updateTable();
308                    }
309            }
310    
311            protected void updateLocks() throws Exception {
312                    Connection con = null;
313                    PreparedStatement ps = null;
314                    ResultSet rs = null;
315    
316                    try {
317                            con = DataAccess.getConnection();
318    
319                            ps = con.prepareStatement(
320                                    "select lockId, key_ from Lock_ where className = ?");
321    
322                            ps.setString(1, DLFileEntry.class.getName());
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.getConnection();
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                                    Date createDate = rs.getDate("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.getConnection();
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.getConnection();
429    
430                            ps = con.prepareStatement(
431                                    "select fileVersionId, userId, extension, version from " +
432                                            "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 version = rs.getString("version");
442    
443                                    String mimeType = MimeTypesUtil.getContentType(
444                                            "A." + extension);
445    
446                                    DLFileVersion dlFileVersion = new DLFileVersionImpl();
447    
448                                    dlFileVersion.setFileVersionId(fileVersionId);
449                                    dlFileVersion.setUserId(userId);
450                                    dlFileVersion.setFileEntryId(fileEntryId);
451                                    dlFileVersion.setMimeType(mimeType);
452                                    dlFileVersion.setVersion(version);
453    
454                                    if (_imageMimeTypes.contains(mimeType)) {
455                                            FileVersion fileVersion = new LiferayFileVersion(
456                                                    dlFileVersion);
457    
458                                            ImageProcessor.generateImages(fileVersion);
459                                    }
460                            }
461                    }
462                    finally {
463                            DataAccess.cleanUp(con, ps, rs);
464                    }
465            }
466    
467            private static Set<String> _imageMimeTypes = SetUtil.fromArray(
468                    PropsValues.DL_FILE_ENTRY_PREVIEW_IMAGE_MIME_TYPES);
469    
470    }