001    /**
002     * Copyright (c) 2000-2013 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
013     */
014    
015    package com.liferay.portal.upgrade.v6_0_0;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
019    import com.liferay.portal.kernel.util.StringBundler;
020    import com.liferay.portal.kernel.util.StringPool;
021    import com.liferay.portal.kernel.util.StringUtil;
022    import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
023    import com.liferay.portal.model.ResourceConstants;
024    import com.liferay.portal.model.UserConstants;
025    import com.liferay.portal.security.auth.FullNameGenerator;
026    import com.liferay.portal.security.auth.FullNameGeneratorFactory;
027    import com.liferay.portlet.asset.model.AssetCategory;
028    import com.liferay.portlet.asset.model.AssetEntry;
029    import com.liferay.portlet.asset.model.AssetTag;
030    import com.liferay.portlet.asset.model.AssetVocabulary;
031    
032    import java.sql.Connection;
033    import java.sql.PreparedStatement;
034    import java.sql.ResultSet;
035    import java.sql.Timestamp;
036    
037    /**
038     * @author Jorge Ferrer
039     * @author Brian Wing Shun Chan
040     */
041    public class UpgradeAsset extends UpgradeProcess {
042    
043            protected void addCategory(
044                            long entryId, long groupId, long companyId, long userId,
045                            String userName, Timestamp createDate, Timestamp modifiedDate,
046                            long parentCategoryId, String name, long vocabularyId)
047                    throws Exception {
048    
049                    Connection con = null;
050                    PreparedStatement ps = null;
051    
052                    try {
053                            con = DataAccess.getUpgradeOptimizedConnection();
054    
055                            StringBundler sb = new StringBundler(4);
056    
057                            sb.append("insert into AssetCategory (uuid_, categoryId, ");
058                            sb.append("groupId, companyId, userId, userName, createDate, ");
059                            sb.append("modifiedDate, parentCategoryId, name, vocabularyId) ");
060                            sb.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
061    
062                            String sql = sb.toString();
063    
064                            ps = con.prepareStatement(sql);
065    
066                            ps.setString(1, PortalUUIDUtil.generate());
067                            ps.setLong(2, entryId);
068                            ps.setLong(3, groupId);
069                            ps.setLong(4, companyId);
070                            ps.setLong(5, userId);
071                            ps.setString(6, userName);
072                            ps.setTimestamp(7, createDate);
073                            ps.setTimestamp(8, modifiedDate);
074                            ps.setLong(9, parentCategoryId);
075                            ps.setString(10, name);
076                            ps.setLong(11, vocabularyId);
077    
078                            ps.executeUpdate();
079                    }
080                    finally {
081                            DataAccess.cleanUp(con, ps);
082                    }
083            }
084    
085            protected void addEntry(
086                            long assetId, long groupId, long companyId, long userId,
087                            String userName, Timestamp createDate, Timestamp modifiedDate,
088                            long classNameId, long classPK, boolean visible,
089                            Timestamp startDate, Timestamp endDate, Timestamp publishDate,
090                            Timestamp expirationDate, String mimeType, String title,
091                            String description, String summary, String url, int height,
092                            int width, double priority, int viewCount)
093                    throws Exception {
094    
095                    Connection con = null;
096                    PreparedStatement ps = null;
097    
098                    try {
099                            con = DataAccess.getUpgradeOptimizedConnection();
100    
101                            StringBundler sb = new StringBundler(7);
102    
103                            sb.append("insert into AssetEntry (entryId, groupId, companyId, ");
104                            sb.append("userId, userName, createDate, modifiedDate, ");
105                            sb.append("classNameId, classPK, visible, startDate, endDate, ");
106                            sb.append("publishDate, expirationDate, mimeType, title, ");
107                            sb.append("description, summary, url, height, width, priority, ");
108                            sb.append("viewCount) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
109                            sb.append("?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
110    
111                            String sql = sb.toString();
112    
113                            ps = con.prepareStatement(sql);
114    
115                            ps.setLong(1, assetId);
116                            ps.setLong(2, groupId);
117                            ps.setLong(3, companyId);
118                            ps.setLong(4, userId);
119                            ps.setString(5, userName);
120                            ps.setTimestamp(6, createDate);
121                            ps.setTimestamp(7, modifiedDate);
122                            ps.setLong(8, classNameId);
123                            ps.setLong(9, classPK);
124                            ps.setBoolean(10, visible);
125                            ps.setTimestamp(11, startDate);
126                            ps.setTimestamp(12, endDate);
127                            ps.setTimestamp(13, publishDate);
128                            ps.setTimestamp(14, expirationDate);
129                            ps.setString(15, mimeType);
130                            ps.setString(16, title);
131                            ps.setString(17, description);
132                            ps.setString(18, summary);
133                            ps.setString(19, url);
134                            ps.setInt(20, height);
135                            ps.setInt(21, width);
136                            ps.setDouble(22, priority);
137                            ps.setInt(23, viewCount);
138    
139                            ps.executeUpdate();
140                    }
141                    finally {
142                            DataAccess.cleanUp(con, ps);
143                    }
144            }
145    
146            protected void addProperty(
147                            String tableName, String pkName, String assocationPKName,
148                            long propertyId, long companyId, long userId, String userName,
149                            Timestamp createDate, Timestamp modifiedDate, long categoryId,
150                            String key, String value)
151                    throws Exception {
152    
153                    Connection con = null;
154                    PreparedStatement ps = null;
155    
156                    try {
157                            con = DataAccess.getUpgradeOptimizedConnection();
158    
159                            StringBundler sb = new StringBundler(7);
160    
161                            sb.append("insert into ");
162                            sb.append(tableName);
163                            sb.append(" (");
164                            sb.append(pkName);
165                            sb.append(", companyId, userId, userName, createDate, ");
166                            sb.append("modifiedDate, ");
167                            sb.append(assocationPKName);
168                            sb.append(", key_, value) values (?, ?, ?, ");
169                            sb.append("?, ?, ?, ?, ?, ?)");
170    
171                            String sql = sb.toString();
172    
173                            ps = con.prepareStatement(sql);
174    
175                            ps.setLong(1, propertyId);
176                            ps.setLong(2, companyId);
177                            ps.setLong(3, userId);
178                            ps.setString(4, userName);
179                            ps.setTimestamp(5, createDate);
180                            ps.setTimestamp(6, modifiedDate);
181                            ps.setLong(7, categoryId);
182                            ps.setString(8, key);
183                            ps.setString(9, value);
184    
185                            ps.executeUpdate();
186                    }
187                    finally {
188                            DataAccess.cleanUp(con, ps);
189                    }
190            }
191    
192            protected void addTag(
193                            long entryId, long groupId, long companyId, long userId,
194                            String userName, Timestamp createDate, Timestamp modifiedDate,
195                            String name)
196                    throws Exception {
197    
198                    Connection con = null;
199                    PreparedStatement ps = null;
200    
201                    try {
202                            con = DataAccess.getUpgradeOptimizedConnection();
203    
204                            StringBundler sb = new StringBundler(3);
205    
206                            sb.append("insert into AssetTag (tagId, groupId, companyId, ");
207                            sb.append("userId, userName, createDate, modifiedDate, name) ");
208                            sb.append("values (?, ?, ?, ?, ?, ?, ?, ?)");
209    
210                            String sql = sb.toString();
211    
212                            ps = con.prepareStatement(sql);
213    
214                            ps.setLong(1, entryId);
215                            ps.setLong(2, groupId);
216                            ps.setLong(3, companyId);
217                            ps.setLong(4, userId);
218                            ps.setString(5, userName);
219                            ps.setTimestamp(6, createDate);
220                            ps.setTimestamp(7, modifiedDate);
221                            ps.setString(8, name);
222    
223                            ps.executeUpdate();
224                    }
225                    finally {
226                            DataAccess.cleanUp(con, ps);
227                    }
228            }
229    
230            protected void addVocabulary(
231                            long vocabularyId, long groupId, long companyId, long userId,
232                            String userName, Timestamp createDate, Timestamp modifiedDate,
233                            String name, String description)
234                    throws Exception {
235    
236                    Connection con = null;
237                    PreparedStatement ps = null;
238    
239                    try {
240                            con = DataAccess.getUpgradeOptimizedConnection();
241    
242                            StringBundler sb = new StringBundler(4);
243    
244                            sb.append("insert into AssetVocabulary (uuid_, vocabularyId, ");
245                            sb.append("groupId, companyId, userId, userName, createDate, ");
246                            sb.append("modifiedDate, name, description) values (?, ?, ?, ?, ");
247                            sb.append("?, ?, ?, ?, ?, ?)");
248    
249                            String sql = sb.toString();
250    
251                            ps = con.prepareStatement(sql);
252    
253                            ps.setString(1, PortalUUIDUtil.generate());
254                            ps.setLong(2, vocabularyId);
255                            ps.setLong(3, groupId);
256                            ps.setLong(4, companyId);
257                            ps.setLong(5, userId);
258                            ps.setString(6, userName);
259                            ps.setTimestamp(7, createDate);
260                            ps.setTimestamp(8, modifiedDate);
261                            ps.setString(9, name);
262                            ps.setString(10, description);
263    
264                            ps.executeUpdate();
265                    }
266                    finally {
267                            DataAccess.cleanUp(con, ps);
268                    }
269            }
270    
271            protected void copyAssociations(
272                            long tagsEntryId, String tableName, String pkName)
273                    throws Exception {
274    
275                    Connection con = null;
276                    PreparedStatement ps = null;
277                    ResultSet rs = null;
278    
279                    try {
280                            con = DataAccess.getUpgradeOptimizedConnection();
281    
282                            ps = con.prepareStatement(
283                                    "select * from TagsAssets_TagsEntries where entryId = ?");
284    
285                            ps.setLong(1, tagsEntryId);
286    
287                            rs = ps.executeQuery();
288    
289                            while (rs.next()) {
290                                    long tagsAssetId = rs.getLong("assetId");
291    
292                                    runSQL(
293                                            "insert into " + tableName + " (entryId, " + pkName +
294                                                    ") values (" + tagsAssetId + ", " + tagsEntryId + ")");
295                            }
296                    }
297                    finally {
298                            DataAccess.cleanUp(con, ps, rs);
299                    }
300            }
301    
302            protected void copyEntriesToCategories(long vocabularyId) throws Exception {
303                    Connection con = null;
304                    PreparedStatement ps = null;
305                    ResultSet rs = null;
306    
307                    try {
308                            con = DataAccess.getUpgradeOptimizedConnection();
309    
310                            ps = con.prepareStatement(
311                                    "select * from TagsEntry where vocabularyId = ?");
312    
313                            ps.setLong(1, vocabularyId);
314    
315                            rs = ps.executeQuery();
316    
317                            while (rs.next()) {
318                                    long entryId = rs.getLong("entryId");
319                                    long groupId = rs.getLong("groupId");
320                                    long companyId = rs.getLong("companyId");
321                                    long userId = rs.getLong("userId");
322                                    String userName = rs.getString("userName");
323                                    Timestamp createDate = rs.getTimestamp("createDate");
324                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
325                                    long parentCategoryId = rs.getLong("parentEntryId");
326                                    String name = rs.getString("name");
327    
328                                    addCategory(
329                                            entryId, groupId, companyId, userId, userName, createDate,
330                                            modifiedDate, parentCategoryId, name, vocabularyId);
331    
332                                    copyAssociations(
333                                            entryId, "AssetEntries_AssetCategories", "categoryId");
334    
335                                    copyProperties(
336                                            entryId, "AssetCategoryProperty", "categoryPropertyId",
337                                            "categoryId");
338    
339                                    updateCategoryResource(companyId, entryId);
340                            }
341                    }
342                    finally {
343                            DataAccess.cleanUp(con, ps, rs);
344                    }
345            }
346    
347            protected void copyProperties(
348                            long categoryId, String tableName, String pkName,
349                            String assocationPKName)
350                    throws Exception {
351    
352                    Connection con = null;
353                    PreparedStatement ps = null;
354                    ResultSet rs = null;
355    
356                    try {
357                            con = DataAccess.getUpgradeOptimizedConnection();
358    
359                            ps = con.prepareStatement(
360                                    "select * from TagsProperty where entryId = ?");
361    
362                            ps.setLong(1, categoryId);
363    
364                            rs = ps.executeQuery();
365    
366                            while (rs.next()) {
367                                    long propertyId = rs.getLong("propertyId");
368                                    long companyId = rs.getLong("companyId");
369                                    long userId = rs.getLong("userId");
370                                    String userName = rs.getString("userName");
371                                    Timestamp createDate = rs.getTimestamp("createDate");
372                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
373                                    String key = rs.getString("key_");
374                                    String value = rs.getString("value");
375    
376                                    addProperty(
377                                            tableName, pkName, assocationPKName, propertyId, companyId,
378                                            userId, userName, createDate, modifiedDate, categoryId, key,
379                                            value);
380                            }
381                    }
382                    finally {
383                            DataAccess.cleanUp(con, ps, rs);
384                    }
385            }
386    
387            @Override
388            protected void doUpgrade() throws Exception {
389                    updateAssetEntries();
390                    updateAssetCategories();
391                    updateAssetTags();
392                    updateResources();
393            }
394    
395            protected String generateShortenedFullName(long userId, String userName)
396                    throws Exception {
397    
398                    Connection con = null;
399                    PreparedStatement ps = null;
400                    ResultSet rs = null;
401    
402                    try {
403                            con = DataAccess.getUpgradeOptimizedConnection();
404    
405                            ps = con.prepareStatement(
406                                    "select firstName, middleName, lastName from User_ where " +
407                                            "userId = ?");
408    
409                            ps.setLong(1, userId);
410    
411                            rs = ps.executeQuery();
412    
413                            if (!rs.next()) {
414                                    return StringUtil.shorten(
415                                            userName, UserConstants.FULL_NAME_MAX_LENGTH,
416                                            StringPool.BLANK);
417                            }
418    
419                            String firstName = rs.getString("firstName");
420                            String middleName = rs.getString("middleName");
421                            String lastName = rs.getString("lastName");
422    
423                            if (firstName == null) {
424                                    firstName = StringPool.BLANK;
425                            }
426    
427                            if (middleName == null) {
428                                    middleName = StringPool.BLANK;
429                            }
430    
431                            if (lastName == null) {
432                                    lastName = StringPool.BLANK;
433                            }
434    
435                            FullNameGenerator fullNameGenerator =
436                                    FullNameGeneratorFactory.getInstance();
437    
438                            return fullNameGenerator.getFullName(
439                                    firstName, middleName, lastName);
440                    }
441                    finally {
442                            DataAccess.cleanUp(con, ps, rs);
443                    }
444            }
445    
446            protected void updateAssetCategories() throws Exception {
447                    Connection con = null;
448                    PreparedStatement ps = null;
449                    ResultSet rs = null;
450    
451                    try {
452                            con = DataAccess.getUpgradeOptimizedConnection();
453    
454                            ps = con.prepareStatement(
455                                    "select * from TagsVocabulary where folksonomy = ?");
456    
457                            ps.setBoolean(1, false);
458    
459                            rs = ps.executeQuery();
460    
461                            while (rs.next()) {
462                                    long vocabularyId = rs.getLong("vocabularyId");
463                                    long groupId = rs.getLong("groupId");
464                                    long companyId = rs.getLong("companyId");
465                                    long userId = rs.getLong("userId");
466                                    String userName = rs.getString("userName");
467                                    Timestamp createDate = rs.getTimestamp("createDate");
468                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
469                                    String name = rs.getString("name");
470                                    String description = rs.getString("description");
471    
472                                    addVocabulary(
473                                            vocabularyId, groupId, companyId, userId, userName,
474                                            createDate, modifiedDate, name, description);
475    
476                                    copyEntriesToCategories(vocabularyId);
477                            }
478                    }
479                    finally {
480                            DataAccess.cleanUp(con, ps, rs);
481                    }
482            }
483    
484            protected void updateAssetEntries() throws Exception {
485                    Connection con = null;
486                    PreparedStatement ps = null;
487                    ResultSet rs = null;
488    
489                    try {
490                            con = DataAccess.getUpgradeOptimizedConnection();
491    
492                            ps = con.prepareStatement("select * from TagsAsset");
493    
494                            rs = ps.executeQuery();
495    
496                            while (rs.next()) {
497                                    long assetId = rs.getLong("assetId");
498                                    long groupId = rs.getLong("groupId");
499                                    long companyId = rs.getLong("companyId");
500                                    long userId = rs.getLong("userId");
501                                    String userName = rs.getString("userName");
502                                    Timestamp createDate = rs.getTimestamp("createDate");
503                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
504                                    long classNameId = rs.getLong("classNameId");
505                                    long classPK = rs.getLong("classPK");
506                                    boolean visible = rs.getBoolean("visible");
507                                    Timestamp startDate = rs.getTimestamp("startDate");
508                                    Timestamp endDate = rs.getTimestamp("endDate");
509                                    Timestamp publishDate = rs.getTimestamp("publishDate");
510                                    Timestamp expirationDate = rs.getTimestamp("expirationDate");
511                                    String mimeType = rs.getString("mimeType");
512                                    String title = rs.getString("title");
513                                    String description = rs.getString("description");
514                                    String summary = rs.getString("summary");
515                                    String url = rs.getString("url");
516                                    int height = rs.getInt("height");
517                                    int width = rs.getInt("width");
518                                    double priority = rs.getDouble("priority");
519                                    int viewCount = rs.getInt("viewCount");
520    
521                                    if (userName.length() > UserConstants.FULL_NAME_MAX_LENGTH) {
522                                            userName = generateShortenedFullName(userId, userName);
523                                    }
524    
525                                    addEntry(
526                                            assetId, groupId, companyId, userId, userName, createDate,
527                                            modifiedDate, classNameId, classPK, visible, startDate,
528                                            endDate, publishDate, expirationDate, mimeType, title,
529                                            description, summary, url, height, width, priority,
530                                            viewCount);
531                            }
532                    }
533                    finally {
534                            DataAccess.cleanUp(con, ps, rs);
535                    }
536            }
537    
538            protected void updateAssetTags() throws Exception {
539                    Connection con = null;
540                    PreparedStatement ps = null;
541                    ResultSet rs = null;
542    
543                    try {
544                            con = DataAccess.getUpgradeOptimizedConnection();
545    
546                            ps = con.prepareStatement(
547                                    "select TE.* from TagsEntry TE inner join TagsVocabulary TV " +
548                                            "on TE.vocabularyId = TV.vocabularyId where " +
549                                                    "TV.folksonomy = ?");
550    
551                            ps.setBoolean(1, true);
552    
553                            rs = ps.executeQuery();
554    
555                            while (rs.next()) {
556                                    long entryId = rs.getLong("entryId");
557                                    long groupId = rs.getLong("groupId");
558                                    long companyId = rs.getLong("companyId");
559                                    long userId = rs.getLong("userId");
560                                    String userName = rs.getString("userName");
561                                    Timestamp createDate = rs.getTimestamp("createDate");
562                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
563                                    String name = rs.getString("name");
564    
565                                    addTag(
566                                            entryId, groupId, companyId, userId, userName, createDate,
567                                            modifiedDate, name);
568    
569                                    copyAssociations(entryId, "AssetEntries_AssetTags", "tagId");
570    
571                                    copyProperties(
572                                            entryId, "AssetTagProperty", "tagPropertyId", "tagId");
573                            }
574                    }
575                    finally {
576                            DataAccess.cleanUp(con, ps, rs);
577                    }
578    
579                    updateAssetTagsCount();
580            }
581    
582            protected void updateAssetTagsCount() throws Exception {
583                    StringBundler sb = new StringBundler(5);
584    
585                    sb.append("update AssetTag set assetCount = (select count(*) from ");
586                    sb.append("AssetEntry inner join AssetEntries_AssetTags on ");
587                    sb.append("AssetEntry.entryId = AssetEntries_AssetTags.entryId ");
588                    sb.append("where AssetEntry.visible = TRUE and AssetTag.tagId = ");
589                    sb.append("AssetEntries_AssetTags.tagId)");
590    
591                    runSQL(sb.toString());
592            }
593    
594            protected void updateCategoryResource(long companyId, long categoryId)
595                    throws Exception {
596    
597                    String oldName = "com.liferay.portlet.tags.model.TagsEntry";
598                    String newName = AssetCategory.class.getName();
599    
600                    runSQL(
601                            "update ResourcePermission set name = '" + newName + "' where " +
602                                    "companyId = " + companyId + " and name = '" + oldName +
603                                            "' and scope = " + ResourceConstants.SCOPE_INDIVIDUAL +
604                                                    " and primKey = '" + categoryId + "';");
605            }
606    
607            protected void updateResources() throws Exception {
608                    updateResources(
609                            "com.liferay.portlet.tags", "com.liferay.portlet.asset"
610                    );
611    
612                    updateResources(
613                            "com.liferay.portlet.tags.model.TagsEntry", AssetTag.class.getName()
614                    );
615    
616                    updateResources(
617                            "com.liferay.portlet.tags.model.TagsAsset",
618                            AssetEntry.class.getName()
619                    );
620    
621                    updateResources(
622                            "com.liferay.portlet.tags.model.TagsVocabulary",
623                            AssetVocabulary.class.getName()
624                    );
625            }
626    
627            protected void updateResources(String oldCodeName, String newCodeName)
628                    throws Exception {
629    
630                    runSQL(
631                            "update ResourceAction set name = '" + newCodeName + "' where" +
632                                    " name = '" + oldCodeName + "';");
633    
634                    runSQL(
635                            "update ResourcePermission set name = '" + newCodeName + "' where" +
636                                    " name = '" + oldCodeName + "';");
637            }
638    
639    }