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