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