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.image.DLHook;
018    import com.liferay.portal.image.DatabaseHook;
019    import com.liferay.portal.image.FileSystemHook;
020    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
021    import com.liferay.portal.kernel.image.Hook;
022    import com.liferay.portal.kernel.log.Log;
023    import com.liferay.portal.kernel.log.LogFactoryUtil;
024    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
025    import com.liferay.portal.kernel.util.FileUtil;
026    import com.liferay.portal.kernel.util.MimeTypesUtil;
027    import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
028    import com.liferay.portal.kernel.util.StringBundler;
029    import com.liferay.portal.kernel.util.StringPool;
030    import com.liferay.portal.kernel.util.Validator;
031    import com.liferay.portal.model.Image;
032    import com.liferay.portal.service.ImageLocalServiceUtil;
033    import com.liferay.portal.util.PropsValues;
034    import com.liferay.portlet.documentlibrary.model.DLFileEntry;
035    import com.liferay.portlet.documentlibrary.model.DLFolder;
036    import com.liferay.portlet.documentlibrary.model.DLFolderConstants;
037    import com.liferay.portlet.documentlibrary.store.DLStoreUtil;
038    import com.liferay.portlet.documentlibrary.util.ImageProcessorUtil;
039    
040    import java.io.InputStream;
041    
042    import java.sql.Connection;
043    import java.sql.Date;
044    import java.sql.PreparedStatement;
045    import java.sql.ResultSet;
046    
047    import java.util.HashMap;
048    import java.util.Map;
049    
050    /**
051     * @author Sergio González
052     * @author Miguel Pastor
053     */
054    public class UpgradeImageGallery extends UpgradeProcess {
055    
056            public UpgradeImageGallery() throws Exception {
057                    ClassLoader classLoader = PortalClassLoaderUtil.getClassLoader();
058    
059                    _sourceHookClassName = FileSystemHook.class.getName();
060    
061                    if (Validator.isNotNull(PropsValues.IMAGE_HOOK_IMPL)) {
062                            _sourceHookClassName = PropsValues.IMAGE_HOOK_IMPL;
063                    }
064    
065                    _sourceHook = (Hook)classLoader.loadClass(
066                            _sourceHookClassName).newInstance();
067            }
068    
069            protected void addDLFileEntry(
070                            String uuid, long fileEntryId, long groupId, long companyId,
071                            long userId, String userName, long versionUserId,
072                            String versionUserName, Date createDate, Date modifiedDate,
073                            long repositoryId, long folderId, String name, String extension,
074                            String mimeType, String title, String description,
075                            String extraSettings, String version, long size, int readCount,
076                            long smallImageId, long largeImageId, long custom1ImageId,
077                            long custom2ImageId)
078                    throws Exception {
079    
080                    Connection con = null;
081                    PreparedStatement ps = null;
082    
083                    try {
084                            con = DataAccess.getConnection();
085    
086                            StringBundler sb = new StringBundler(9);
087    
088                            sb.append("insert into DLFileEntry (uuid_, fileEntryId, groupId, ");
089                            sb.append("companyId, userId, userName, versionUserId, ");
090                            sb.append("versionUserName, createDate, modifiedDate, ");
091                            sb.append("repositoryId, folderId, name, extension, mimeType, ");
092                            sb.append("title, description, extraSettings, version, size_, ");
093                            sb.append("readCount, smallImageId, largeImageId, ");
094                            sb.append("custom1ImageId, custom2ImageId) values (");
095                            sb.append("?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
096                            sb.append("?, ?, ?, ?, ?, ?, ?, ?)");
097    
098                            String sql = sb.toString();
099    
100                            ps = con.prepareStatement(sql);
101    
102                            ps.setString(1, uuid);
103                            ps.setLong(2, fileEntryId);
104                            ps.setLong(3, groupId);
105                            ps.setLong(4, companyId);
106                            ps.setLong(5, userId);
107                            ps.setString(6, userName);
108                            ps.setLong(7, versionUserId);
109                            ps.setString(8, versionUserName);
110                            ps.setDate(9, createDate);
111                            ps.setDate(10, modifiedDate);
112                            ps.setLong(11, repositoryId);
113                            ps.setLong(12, folderId);
114                            ps.setString(13, name);
115                            ps.setString(14, extension);
116                            ps.setString(15, mimeType);
117                            ps.setString(16, title);
118                            ps.setString(17, description);
119                            ps.setString(18, extraSettings);
120                            ps.setString(19, version);
121                            ps.setLong(20, size);
122                            ps.setInt(21, readCount);
123                            ps.setLong(22, smallImageId);
124                            ps.setLong(23, largeImageId);
125                            ps.setLong(24, custom1ImageId);
126                            ps.setLong(25, custom2ImageId);
127    
128                            ps.executeUpdate();
129                    }
130                    finally {
131                            DataAccess.cleanUp(con, ps);
132                    }
133            }
134    
135            protected void addDLFileVersion(
136                            long fileVersionId, long groupId, long companyId, long userId,
137                            String userName, Date createDate, long repositoryId, long folderId,
138                            long fileEntryId, String extension, String mimeType, String title,
139                            String description, String changeLog, String extraSettings,
140                            long fileEntryTypeId, String version, long size, int status,
141                            long statusByUserId, String statusByUserName, Date statusDate)
142                    throws Exception {
143    
144                    Connection con = null;
145                    PreparedStatement ps = null;
146    
147                    try {
148                            con = DataAccess.getConnection();
149    
150                            StringBundler sb = new StringBundler(9);
151    
152                            sb.append("insert into DLFileVersion (fileVersionId, groupId, ");
153                            sb.append("companyId, userId, userName, createDate, ");
154                            sb.append("modifiedDate, repositoryId, folderId, fileEntryId, ");
155                            sb.append("extension, mimeType, title, description, changeLog, ");
156                            sb.append("extraSettings, fileEntryTypeId, version, size_, ");
157                            sb.append("status, statusByUserId, statusByUserName, statusDate) ");
158                            sb.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
159                            sb.append("?, ?, ?, ?, ?, ?, ?, ?)");
160    
161                            String sql = sb.toString();
162    
163                            ps = con.prepareStatement(sql);
164    
165                            ps.setLong(1, fileVersionId);
166                            ps.setLong(2, groupId);
167                            ps.setLong(3, companyId);
168                            ps.setLong(4, userId);
169                            ps.setString(5, userName);
170                            ps.setDate(6, createDate);
171                            ps.setDate(7, statusDate);
172                            ps.setLong(8, repositoryId);
173                            ps.setLong(9, folderId);
174                            ps.setLong(10, fileEntryId);
175                            ps.setString(11, extension);
176                            ps.setString(12, mimeType);
177                            ps.setString(13, title);
178                            ps.setString(14, description);
179                            ps.setString(15, changeLog);
180                            ps.setString(16, extraSettings);
181                            ps.setLong(17, fileEntryTypeId);
182                            ps.setString(18, version);
183                            ps.setLong(19, size);
184                            ps.setInt(20, status);
185                            ps.setLong(21, statusByUserId);
186                            ps.setString(22, statusByUserName);
187                            ps.setDate(23, statusDate);
188    
189                            ps.executeUpdate();
190                    }
191                    finally {
192                            DataAccess.cleanUp(con, ps);
193                    }
194            }
195    
196            protected void addDLFolderEntry(
197                            String uuid, long folderId, long groupId, long companyId,
198                            long userId, String userName, Date createDate, Date modifiedDate,
199                            long repositoryId, long parentFolderId, String name,
200                            String description, Date lastPostDate)
201                    throws Exception {
202    
203                    Connection con = null;
204                    PreparedStatement ps = null;
205    
206                    try {
207                            con = DataAccess.getConnection();
208    
209                            StringBundler sb = new StringBundler(5);
210    
211                            sb.append("insert into DLFolder (uuid_, folderId, groupId, ");
212                            sb.append("companyId, userId, userName, createDate, ");
213                            sb.append("modifiedDate, repositoryId, mountPoint, ");
214                            sb.append("parentFolderId, name, description, lastPostDate) ");
215                            sb.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
216    
217                            String sql = sb.toString();
218    
219                            ps = con.prepareStatement(sql);
220    
221                            ps.setString(1, uuid);
222                            ps.setLong(2, folderId);
223                            ps.setLong(3, groupId);
224                            ps.setLong(4, companyId);
225                            ps.setLong(5, userId);
226                            ps.setString(6, userName);
227                            ps.setDate(7, createDate);
228                            ps.setDate(8, modifiedDate);
229                            ps.setLong(9, repositoryId);
230                            ps.setBoolean(10, false);
231                            ps.setLong(11, parentFolderId);
232                            ps.setString(12, name);
233                            ps.setString(13, description);
234                            ps.setDate(14, lastPostDate);
235    
236                            ps.executeUpdate();
237                    }
238                    finally {
239                            DataAccess.cleanUp(con, ps);
240                    }
241            }
242    
243            @Override
244            protected void doUpgrade() throws Exception {
245                    updateIGFolderEntries();
246                    updateIGImageEntries();
247                    updateIGFolderPermissions();
248                    updateIGImagePermissions();
249    
250                    migrateImageFiles();
251    
252                    UpgradeDocumentLibrary upgradeDocumentLibrary =
253                            new UpgradeDocumentLibrary();
254    
255                    upgradeDocumentLibrary.updateSyncs();
256            }
257    
258            protected Object[] getImage(long imageId) throws Exception {
259                    Connection con = null;
260                    PreparedStatement ps = null;
261                    ResultSet rs = null;
262    
263                    try {
264                            con = DataAccess.getConnection();
265    
266                            ps = con.prepareStatement(
267                                    "select type_, size_ from Image where imageId = " + imageId);
268    
269                            rs = ps.executeQuery();
270    
271                            if (rs.next()) {
272                                    String type = rs.getString("type_");
273                                    long size = rs.getLong("size_");
274    
275                                    return new Object[] {type, size};
276                            }
277    
278                            return null;
279                    }
280                    finally {
281                            DataAccess.cleanUp(con, ps, rs);
282                    }
283            }
284    
285            protected void migrateFile(
286                            long repositoryId, long companyId, String name, Image image)
287                    throws Exception {
288    
289                    InputStream is = _sourceHook.getImageAsStream(image);
290    
291                    byte[] bytes = FileUtil.getBytes(is);
292    
293                    if (name == null) {
294                            name = image.getImageId() + StringPool.PERIOD + image.getType();
295                    }
296    
297                    if (DLStoreUtil.hasFile(companyId, repositoryId, name)) {
298                            DLStoreUtil.deleteFile(companyId, repositoryId, name);
299                    }
300    
301                    DLStoreUtil.addFile(companyId, repositoryId, name, true, bytes);
302            }
303    
304            protected void migrateImage(long imageId) throws Exception {
305                    Connection con = null;
306                    PreparedStatement ps = null;
307                    ResultSet rs = null;
308    
309                    try {
310                            con = DataAccess.getConnection();
311    
312                            StringBundler sb = new StringBundler(8);
313    
314                            sb.append("select fileVersionId, fileEntry.fileEntryId ");
315                            sb.append("as fileEntryId, fileEntry.groupId as groupId, ");
316                            sb.append("fileEntry.companyId as companyId, fileEntry.folderId ");
317                            sb.append("as folderId, name, largeImageId, smallImageId, ");
318                            sb.append("custom1ImageId, custom2ImageId from ");
319                            sb.append("DLFileVersion fileVersion, DLFileEntry fileEntry ");
320                            sb.append("where fileEntry.fileEntryId = fileVersion.fileEntryId ");
321                            sb.append("and (largeImageId = ? or smallImageId = ? or ");
322                            sb.append("custom1ImageId = ? or custom2ImageId = ?)");
323    
324                            String sql = sb.toString();
325    
326                            ps = con.prepareStatement(sql);
327    
328                            ps.setLong(1, imageId);
329                            ps.setLong(2, imageId);
330                            ps.setLong(3, imageId);
331                            ps.setLong(4, imageId);
332    
333                            rs = ps.executeQuery();
334    
335                            if (rs.next()) {
336                                    long fileVersionId = rs.getLong("fileVersionId");
337                                    long fileEntryId = rs.getLong("fileEntryId");
338                                    long companyId = rs.getLong("companyId");
339                                    long groupId = rs.getLong("groupId");
340                                    long folderId = rs.getLong("folderId");
341                                    String name = rs.getString("name");
342                                    long largeImageId = rs.getLong("largeImageId");
343                                    long custom1ImageId = rs.getLong("custom1ImageId");
344                                    long custom2ImageId = rs.getLong("custom2ImageId");
345    
346                                    Image image = ImageLocalServiceUtil.getImage(imageId);
347    
348                                    if (largeImageId == imageId) {
349                                            long repositoryId = DLFolderConstants.getDataRepositoryId(
350                                                    groupId, folderId);
351    
352                                            try {
353                                                    migrateFile(repositoryId, companyId, name, image);
354                                            }
355                                            catch (Exception e) {
356                                                    if (_log.isWarnEnabled()) {
357                                                            _log.warn(
358                                                                    "Ignoring exception for image " + imageId, e);
359                                                    }
360    
361                                                    return;
362                                            }
363                                    }
364                                    else {
365                                            try {
366                                                    InputStream is = _sourceHook.getImageAsStream(image);
367    
368                                                    if (custom1ImageId != imageId) {
369                                                            custom1ImageId = 0;
370                                                    }
371    
372                                                    if (custom2ImageId != imageId) {
373                                                            custom2ImageId = 0;
374                                                    }
375    
376                                                    ImageProcessorUtil.storeThumbnail(
377                                                            companyId, groupId, fileEntryId, fileVersionId,
378                                                            custom1ImageId, custom2ImageId, is,
379                                                            image.getType());
380                                            }
381                                            catch (Exception e) {
382                                                    if (_log.isWarnEnabled()) {
383                                                            _log.warn(
384                                                                    "Ignoring exception for image " + imageId, e);
385                                                    }
386    
387                                                    return;
388                                            }
389                                    }
390    
391                                    _sourceHook.deleteImage(image);
392                            }
393                            else if (!_sourceHookClassName.equals(DLHook.class.getName())) {
394                                    Image image = ImageLocalServiceUtil.getImage(imageId);
395    
396                                    try {
397                                            migrateFile(0, 0, null, image);
398                                    }
399                                    catch (Exception e) {
400                                            if (_log.isWarnEnabled()) {
401                                                    _log.warn("Ignoring exception for image " + imageId, e);
402                                            }
403    
404                                            return;
405                                    }
406    
407                                    _sourceHook.deleteImage(image);
408                            }
409                    }
410                    finally {
411                            DataAccess.cleanUp(con, ps, rs);
412                    }
413            }
414    
415            protected void migrateImageFiles() throws Exception {
416                    Connection con = null;
417                    PreparedStatement ps = null;
418                    ResultSet rs = null;
419    
420                    try {
421                            con = DataAccess.getConnection();
422    
423                            ps = con.prepareStatement("select imageId from Image");
424    
425                            rs = ps.executeQuery();
426    
427                            while (rs.next()) {
428                                    long imageId = rs.getLong("imageId");
429    
430                                    migrateImage(imageId);
431                            }
432    
433                            StringBundler sb = new StringBundler(5);
434    
435                            sb.append("delete from Image where imageId in (select ");
436                            sb.append("smallImageId from DLFileEntry) or imageId in (select ");
437                            sb.append("largeImageId from DLFileEntry) or imageId in (select ");
438                            sb.append("custom1ImageId from DLFileEntry) or imageId in ");
439                            sb.append("(select custom2ImageId from DLFileEntry)");
440    
441                            runSQL(sb.toString());
442    
443                            if (_sourceHookClassName.equals(DatabaseHook.class.getName())) {
444                                    runSQL("update Image set text_ = ''");
445                            }
446                    }
447                    finally {
448                            DataAccess.cleanUp(con, ps, rs);
449                    }
450            }
451    
452            protected void updateIGFolderEntries() throws Exception {
453                    Connection con = null;
454                    PreparedStatement ps = null;
455                    ResultSet rs = null;
456    
457                    try {
458                            con = DataAccess.getConnection();
459    
460                            ps = con.prepareStatement(
461                                    "select * from IGFolder order by folderId asc");
462    
463                            rs = ps.executeQuery();
464    
465                            Map<Long, Long> folderIds = new HashMap<Long, Long>();
466    
467                            while (rs.next()) {
468                                    String uuid = rs.getString("uuid_");
469                                    long folderId = rs.getLong("folderId");
470                                    long groupId = rs.getLong("groupId");
471                                    long companyId = rs.getLong("companyId");
472                                    long userId = rs.getLong("userId");
473                                    String userName = rs.getString("userName");
474                                    Date createDate = rs.getDate("createDate");
475                                    Date modifiedDate = rs.getDate("modifiedDate");
476                                    long parentFolderId = rs.getLong("parentFolderId");
477                                    String name = rs.getString("name");
478                                    String description = rs.getString("description");
479    
480                                    if (folderIds.containsKey(parentFolderId)) {
481                                            parentFolderId = folderIds.get(parentFolderId);
482                                    }
483    
484                                    boolean update = updateIGImageFolderId(
485                                            groupId, name, parentFolderId, folderId, folderIds);
486    
487                                    if (!update) {
488                                            addDLFolderEntry(
489                                                    uuid, folderId, groupId, companyId, userId, userName,
490                                                    createDate, modifiedDate, groupId, parentFolderId, name,
491                                                    description, modifiedDate);
492                                    }
493                            }
494    
495                            runSQL("drop table IGFolder");
496                    }
497                    finally {
498                            DataAccess.cleanUp(con, ps, rs);
499                    }
500            }
501    
502            protected void updateIGFolderPermissions() throws Exception {
503                    if (PropsValues.PERMISSIONS_USER_CHECK_ALGORITHM != 6) {
504                            return;
505                    }
506    
507                    runSQL(
508                            "delete from ResourcePermission where " +
509                                    "name = 'com.liferay.portlet.imagegallery.model.IGFolder' " +
510                                            "and primKey = '0'");
511    
512                    Connection con = null;
513                    PreparedStatement ps = null;
514                    ResultSet rs = null;
515    
516                    try {
517                            con = DataAccess.getConnection();
518    
519                            StringBundler sb = new StringBundler(4);
520    
521                            sb.append("update ResourcePermission set name = '");
522                            sb.append(DLFolder.class.getName());
523                            sb.append("' where name = 'com.liferay.portlet.imagegallery.");
524                            sb.append("model.IGFolder'");
525    
526                            ps = con.prepareStatement(sb.toString());
527    
528                            ps.executeUpdate();
529                    }
530                    finally {
531                            DataAccess.cleanUp(con, ps, rs);
532                    }
533            }
534    
535            protected void updateIGImageEntries() throws Exception {
536                    Connection con = null;
537                    PreparedStatement ps = null;
538                    ResultSet rs = null;
539    
540                    try {
541                            con = DataAccess.getConnection();
542    
543                            ps = con.prepareStatement("select * from IGImage");
544    
545                            rs = ps.executeQuery();
546    
547                            while (rs.next()) {
548                                    String uuid = rs.getString("uuid_");
549                                    long imageId = rs.getLong("imageId");
550                                    long groupId = rs.getLong("groupId");
551                                    long companyId = rs.getLong("companyId");
552                                    long userId = rs.getLong("userId");
553                                    String userName = rs.getString("userName");
554                                    Date createDate = rs.getDate("createDate");
555                                    Date modifiedDate = rs.getDate("modifiedDate");
556                                    long folderId = rs.getLong("folderId");
557                                    String title = rs.getString("name");
558                                    String description = rs.getString("description");
559                                    long smallImageId = rs.getLong("smallImageId");
560                                    long largeImageId = rs.getLong("largeImageId");
561                                    long custom1ImageId = rs.getLong("custom1ImageId");
562                                    long custom2ImageId = rs.getLong("custom2ImageId");
563    
564                                    Object[] image = getImage(largeImageId);
565    
566                                    if (image == null) {
567                                            continue;
568                                    }
569    
570                                    String extension = (String)image[0];
571    
572                                    String mimeType = MimeTypesUtil.getContentType(
573                                            "A." + extension);
574    
575                                    String name = String.valueOf(
576                                            increment(DLFileEntry.class.getName()));
577    
578                                    long size = (Long)image[1];
579    
580                                    try {
581                                            addDLFileEntry(
582                                                    uuid, imageId, groupId, companyId, userId, userName,
583                                                    userId, userName, createDate, modifiedDate, groupId,
584                                                    folderId, name, extension, mimeType, title, description,
585                                                    StringPool.BLANK, "1.0", size, 0, smallImageId,
586                                                    largeImageId, custom1ImageId, custom2ImageId);
587                                    }
588                                    catch (Exception e) {
589                                            title = title.concat(StringPool.SPACE).concat(
590                                                    String.valueOf(imageId));
591    
592                                            addDLFileEntry(
593                                                    uuid, imageId, groupId, companyId, userId, userName,
594                                                    userId, userName, createDate, modifiedDate, groupId,
595                                                    folderId, name, extension, mimeType, title, description,
596                                                    StringPool.BLANK, "1.0", size, 0, smallImageId,
597                                                    largeImageId, custom1ImageId, custom2ImageId);
598                                    }
599    
600                                    addDLFileVersion(
601                                            increment(), groupId, companyId, userId, userName,
602                                            createDate, groupId, folderId, imageId, extension, mimeType,
603                                            title, description, StringPool.BLANK, StringPool.BLANK, 0,
604                                            "1.0", size, 0, userId, userName, modifiedDate);
605                            }
606    
607                            runSQL("drop table IGImage");
608                    }
609                    finally {
610                            DataAccess.cleanUp(con, ps, rs);
611                    }
612            }
613    
614            protected boolean updateIGImageFolderId(
615                            long groupId, String name, long parentFolderId, long folderId,
616                            Map<Long, Long> folderIds)
617                    throws Exception {
618    
619                    Connection con = null;
620                    PreparedStatement ps = null;
621                    ResultSet rs = null;
622    
623                    try {
624                            con = DataAccess.getConnection();
625    
626                            ps = con.prepareStatement(
627                                    "select folderId from DLFolder where groupId = " + groupId +
628                                            " and parentFolderId = " + parentFolderId +
629                                                    " and name = '" + name + "'");
630    
631                            rs = ps.executeQuery();
632    
633                            if (rs.next()) {
634                                    long newFolderId = rs.getLong("folderId");
635    
636                                    runSQL(
637                                            "update IGImage set folderId = " + newFolderId +
638                                                    " where folderId = " + folderId);
639    
640                                    folderIds.put(folderId, newFolderId);
641    
642                                    return true;
643                            }
644                    }
645                    finally {
646                            DataAccess.cleanUp(con, ps, rs);
647                    }
648    
649                    return false;
650            }
651    
652            protected void updateIGImagePermissions() throws Exception {
653                    if (PropsValues.PERMISSIONS_USER_CHECK_ALGORITHM != 6) {
654                            return;
655                    }
656    
657                    runSQL(
658                            "delete from ResourcePermission where name = '" +
659                                    _IG_IMAGE_CLASS_NAME + "' and primKey = '0'");
660    
661                    runSQL(
662                            "update ResourcePermission set name = '" +
663                                    DLFileEntry.class.getName() + "' where name = '" +
664                                            _IG_IMAGE_CLASS_NAME + "'");
665            }
666    
667            private static final String _IG_IMAGE_CLASS_NAME =
668                    "com.liferay.portlet.imagegallery.model.IGImage";
669    
670            private static Log _log = LogFactoryUtil.getLog(UpgradeImageGallery.class);
671    
672            private Hook _sourceHook;
673            private String _sourceHookClassName;
674    
675    }