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