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