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