001
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
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 }