1
14
15 package com.liferay.portal.upgrade.v6_0_0;
16
17 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
18 import com.liferay.portal.kernel.upgrade.UpgradeProcess;
19 import com.liferay.portal.kernel.util.StringBundler;
20 import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
21 import com.liferay.portal.model.ResourceCode;
22 import com.liferay.portal.model.ResourceConstants;
23 import com.liferay.portal.service.ResourceCodeLocalServiceUtil;
24 import com.liferay.portal.service.ResourceLocalServiceUtil;
25 import com.liferay.portlet.asset.model.AssetCategory;
26 import com.liferay.portlet.asset.model.AssetEntry;
27 import com.liferay.portlet.asset.model.AssetTag;
28 import com.liferay.portlet.asset.model.AssetVocabulary;
29
30 import java.sql.Connection;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.Timestamp;
34
35
41 public class UpgradeAsset extends UpgradeProcess {
42
43 protected void addCategory(
44 long entryId, long groupId, long companyId, long userId,
45 String userName, Timestamp createDate, Timestamp modifiedDate,
46 long parentCategoryId, String name, long vocabularyId)
47 throws Exception {
48
49 Connection con = null;
50 PreparedStatement ps = null;
51
52 try {
53 con = DataAccess.getConnection();
54
55 StringBundler sb = new StringBundler(4);
56
57 sb.append("insert into AssetCategory (uuid_, categoryId, ");
58 sb.append("groupId, companyId, userId, userName, createDate, ");
59 sb.append("modifiedDate, parentCategoryId, name, vocabularyId) ");
60 sb.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
61
62 String sql = sb.toString();
63
64 ps = con.prepareStatement(sql);
65
66 ps.setString(1, PortalUUIDUtil.generate());
67 ps.setLong(2, entryId);
68 ps.setLong(3, groupId);
69 ps.setLong(4, companyId);
70 ps.setLong(5, userId);
71 ps.setString(6, userName);
72 ps.setTimestamp(7, createDate);
73 ps.setTimestamp(8, modifiedDate);
74 ps.setLong(9, parentCategoryId);
75 ps.setString(10, name);
76 ps.setLong(11, vocabularyId);
77
78 ps.executeUpdate();
79 }
80 finally {
81 DataAccess.cleanUp(con, ps);
82 }
83 }
84
85 protected void addEntry(
86 long assetId, long groupId, long companyId, long userId,
87 String userName, Timestamp createDate, Timestamp modifiedDate,
88 long classNameId, long classPK, boolean visible,
89 Timestamp startDate, Timestamp endDate, Timestamp publishDate,
90 Timestamp expirationDate, String mimeType, String title,
91 String description, String summary, String url, int height,
92 int width, double priority, int viewCount)
93 throws Exception {
94
95 Connection con = null;
96 PreparedStatement ps = null;
97
98 try {
99 con = DataAccess.getConnection();
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, long propertyId, long companyId,
148 long userId, String userName, Timestamp createDate,
149 Timestamp modifiedDate, long categoryId, String key, String value)
150 throws Exception {
151
152 Connection con = null;
153 PreparedStatement ps = null;
154
155 try {
156 con = DataAccess.getConnection();
157
158 StringBundler sb = new StringBundler(7);
159
160 sb.append("insert into ");
161 sb.append(tableName);
162 sb.append(" (");
163 sb.append(pkName);
164 sb.append(", companyId, userId, userName, createDate, ");
165 sb.append("modifiedDate, entryId, key_, value) values (?, ?, ?, ");
166 sb.append("?, ?, ?, ?, ?, ?)");
167
168 String sql = sb.toString();
169
170 ps = con.prepareStatement(sql);
171
172 ps.setLong(1, propertyId);
173 ps.setLong(2, companyId);
174 ps.setLong(3, userId);
175 ps.setString(4, userName);
176 ps.setTimestamp(5, createDate);
177 ps.setTimestamp(6, modifiedDate);
178 ps.setLong(7, categoryId);
179 ps.setString(8, key);
180 ps.setString(9, value);
181
182 ps.executeUpdate();
183 }
184 finally {
185 DataAccess.cleanUp(con, ps);
186 }
187 }
188
189 protected void addTag(
190 long entryId, long groupId, long companyId, long userId,
191 String userName, Timestamp createDate, Timestamp modifiedDate,
192 String name)
193 throws Exception {
194
195 Connection con = null;
196 PreparedStatement ps = null;
197
198 try {
199 con = DataAccess.getConnection();
200
201 StringBundler sb = new StringBundler(3);
202
203 sb.append("insert into AssetTag (tagId, groupId, companyId, ");
204 sb.append("userId, userName, createDate, modifiedDate, name) ");
205 sb.append("values (?, ?, ?, ?, ?, ?, ?, ?)");
206
207 String sql = sb.toString();
208
209 ps = con.prepareStatement(sql);
210
211 ps.setLong(1, entryId);
212 ps.setLong(2, groupId);
213 ps.setLong(3, companyId);
214 ps.setLong(4, userId);
215 ps.setString(5, userName);
216 ps.setTimestamp(6, createDate);
217 ps.setTimestamp(7, modifiedDate);
218 ps.setString(8, name);
219
220 ps.executeUpdate();
221 }
222 finally {
223 DataAccess.cleanUp(con, ps);
224 }
225 }
226
227 protected void addVocabulary(
228 long vocabularyId, long groupId, long companyId, long userId,
229 String userName, Timestamp createDate, Timestamp modifiedDate,
230 String name, String description)
231 throws Exception {
232
233 Connection con = null;
234 PreparedStatement ps = null;
235
236 try {
237 con = DataAccess.getConnection();
238
239 StringBundler sb = new StringBundler(4);
240
241 sb.append("insert into AssetVocabulary (uuid_, vocabularyId, ");
242 sb.append("groupId, companyId, userId, userName, createDate, ");
243 sb.append("modifiedDate, name, description) values (?, ?, ?, ?, ");
244 sb.append("?, ?, ?, ?, ?, ?)");
245
246 String sql = sb.toString();
247
248 ps = con.prepareStatement(sql);
249
250 ps.setString(1, PortalUUIDUtil.generate());
251 ps.setLong(2, vocabularyId);
252 ps.setLong(3, groupId);
253 ps.setLong(4, companyId);
254 ps.setLong(5, userId);
255 ps.setString(6, userName);
256 ps.setTimestamp(7, createDate);
257 ps.setTimestamp(8, modifiedDate);
258 ps.setString(9, name);
259 ps.setString(10, description);
260
261 ps.executeUpdate();
262 }
263 finally {
264 DataAccess.cleanUp(con, ps);
265 }
266 }
267
268 protected void copyAssociations(
269 long tagsEntryId, String tableName, String pkName)
270 throws Exception {
271
272 Connection con = null;
273 PreparedStatement ps = null;
274 ResultSet rs = null;
275
276 try {
277 con = DataAccess.getConnection();
278
279 ps = con.prepareStatement(
280 "select * from TagsAssets_TagsEntries where entryId = ?");
281
282 ps.setLong(1, tagsEntryId);
283
284 rs = ps.executeQuery();
285
286 while (rs.next()) {
287 long tagsAssetId = rs.getLong("assetId");
288
289 runSQL(
290 "insert into " + tableName + " (entryId, " + pkName +
291 ") values (" + tagsAssetId + ", " + tagsEntryId + ")");
292 }
293 }
294 finally {
295 DataAccess.cleanUp(con, ps, rs);
296 }
297 }
298
299 protected void copyEntriesToCategories(long vocabularyId) throws Exception {
300 Connection con = null;
301 PreparedStatement ps = null;
302 ResultSet rs = null;
303
304 try {
305 con = DataAccess.getConnection();
306
307 ps = con.prepareStatement(
308 "select * from TagsEntry where vocabularyId = ?");
309
310 ps.setLong(1, vocabularyId);
311
312 rs = ps.executeQuery();
313
314 while (rs.next()) {
315 long entryId = rs.getLong("entryId");
316 long groupId = rs.getLong("groupId");
317 long companyId = rs.getLong("companyId");
318 long userId = rs.getLong("userId");
319 String userName = rs.getString("userName");
320 Timestamp createDate = rs.getTimestamp("createDate");
321 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
322 long parentCategoryId = rs.getLong("parentEntryId");
323 String name = rs.getString("name");
324
325 addCategory(
326 entryId, groupId, companyId, userId, userName, createDate,
327 modifiedDate, parentCategoryId, name, vocabularyId);
328
329 copyAssociations(
330 entryId, "AssetEntries_AssetCategories", "categoryId");
331
332 copyProperties(
333 entryId, "AssetCategoryProperty", "categoryPropertyId");
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 throws Exception {
351
352 Connection con = null;
353 PreparedStatement ps = null;
354 ResultSet rs = null;
355
356 try {
357 con = DataAccess.getConnection();
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, propertyId, companyId, userId, userName,
378 createDate, modifiedDate, categoryId, key, value);
379 }
380 }
381 finally {
382 DataAccess.cleanUp(con, ps, rs);
383 }
384 }
385
386 protected void doUpgrade() throws Exception {
387 updateResourceCodes();
388 updateAssetEntries();
389 updateAssetCategories();
390 updateAssetTags();
391 }
392
393 protected void updateAssetCategories() throws Exception {
394 Connection con = null;
395 PreparedStatement ps = null;
396 ResultSet rs = null;
397
398 try {
399 con = DataAccess.getConnection();
400
401 ps = con.prepareStatement(
402 "select * from TagsVocabulary where folksonomy = ?");
403
404 ps.setBoolean(1, false);
405
406 rs = ps.executeQuery();
407
408 while (rs.next()) {
409 long vocabularyId = rs.getLong("vocabularyId");
410 long groupId = rs.getLong("groupId");
411 long companyId = rs.getLong("companyId");
412 long userId = rs.getLong("userId");
413 String userName = rs.getString("userName");
414 Timestamp createDate = rs.getTimestamp("createDate");
415 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
416 String name = rs.getString("name");
417 String description = rs.getString("description");
418
419 addVocabulary(
420 vocabularyId, groupId, companyId, userId, userName,
421 createDate, modifiedDate, name, description);
422
423 copyEntriesToCategories(vocabularyId);
424 }
425 }
426 finally {
427 DataAccess.cleanUp(con, ps, rs);
428 }
429 }
430
431 protected void updateAssetEntries() throws Exception {
432 Connection con = null;
433 PreparedStatement ps = null;
434 ResultSet rs = null;
435
436 try {
437 con = DataAccess.getConnection();
438
439 ps = con.prepareStatement("select * from TagsAsset");
440
441 rs = ps.executeQuery();
442
443 while (rs.next()) {
444 long assetId = rs.getLong("assetId");
445 long groupId = rs.getLong("groupId");
446 long companyId = rs.getLong("companyId");
447 long userId = rs.getLong("userId");
448 String userName = rs.getString("userName");
449 Timestamp createDate = rs.getTimestamp("createDate");
450 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
451 long classNameId = rs.getLong("classNameId");
452 long classPK = rs.getLong("classPK");
453 boolean visible = rs.getBoolean("visible");
454 Timestamp startDate = rs.getTimestamp("startDate");
455 Timestamp endDate = rs.getTimestamp("endDate");
456 Timestamp publishDate = rs.getTimestamp("publishDate");
457 Timestamp expirationDate = rs.getTimestamp("expirationDate");
458 String mimeType = rs.getString("mimeType");
459 String title = rs.getString("title");
460 String description = rs.getString("description");
461 String summary = rs.getString("summary");
462 String url = rs.getString("url");
463 int height = rs.getInt("height");
464 int width = rs.getInt("width");
465 double priority = rs.getDouble("priority");
466 int viewCount = rs.getInt("viewCount");
467
468 addEntry(
469 assetId, groupId, companyId, userId, userName, createDate,
470 modifiedDate, classNameId, classPK, visible, startDate,
471 endDate, publishDate, expirationDate, mimeType, title,
472 description, summary, url, height, width, priority,
473 viewCount);
474 }
475 }
476 finally {
477 DataAccess.cleanUp(con, ps, rs);
478 }
479 }
480
481 protected void updateAssetTags() throws Exception {
482 Connection con = null;
483 PreparedStatement ps = null;
484 ResultSet rs = null;
485
486 try {
487 con = DataAccess.getConnection();
488
489 ps = con.prepareStatement(
490 "select TE.* from TagsEntry TE inner join TagsVocabulary TV " +
491 "on TE.vocabularyId = TV.vocabularyId where " +
492 "TV.folksonomy = ?");
493
494 ps.setBoolean(1, true);
495
496 rs = ps.executeQuery();
497
498 while (rs.next()) {
499 long entryId = rs.getLong("entryId");
500 long groupId = rs.getLong("groupId");
501 long companyId = rs.getLong("companyId");
502 long userId = rs.getLong("userId");
503 String userName = rs.getString("userName");
504 Timestamp createDate = rs.getTimestamp("createDate");
505 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
506 String name = rs.getString("name");
507
508 addTag(
509 entryId, groupId, companyId, userId, userName, createDate,
510 modifiedDate, name);
511
512 copyAssociations(entryId, "AssetEntries_AssetTags", "tagId");
513
514 copyProperties(entryId, "AssetTagProperty", "tagPropertyId");
515 }
516 }
517 finally {
518 DataAccess.cleanUp(con, ps, rs);
519 }
520 }
521
522 protected void updateCategoryResource(long companyId, long categoryId)
523 throws Exception{
524
525 String oldName = "com.liferay.tags.model.TagsEntry";
526
527 ResourceCode oldResourceCode =
528 ResourceCodeLocalServiceUtil.getResourceCode(
529 companyId, oldName, ResourceConstants.SCOPE_INDIVIDUAL);
530
531 long oldCodeId = oldResourceCode.getCodeId();
532
533 String newName = AssetCategory.class.getName();
534
535 ResourceCode newResourceCode =
536 ResourceCodeLocalServiceUtil.getResourceCode(
537 companyId, newName, ResourceConstants.SCOPE_INDIVIDUAL);
538
539 long newCodeId = newResourceCode.getCodeId();
540
541
543 runSQL(
544 "update Resource_ set codeId = '" + newCodeId + "' where " +
545 "codeId = '" + oldCodeId + "' and primKey = '" + categoryId +
546 "';");
547
548
550 runSQL(
551 "update ResourcePermission set name = '" + newName + "' where " +
552 "companyId = '" + companyId + "' and name = '" + oldName +
553 "' and scope = '" + ResourceConstants.SCOPE_INDIVIDUAL +
554 "' and primKey = '" + categoryId + "';");
555 }
556
557 protected void updateResourceCodes() throws Exception {
558 updateResourceCodes(
559 "com.liferay.portlet.tags", "com.liferay.portlet.asset"
560 );
561
562 updateResourceCodes(
563 "com.liferay.portlet.tags.model.TagsEntry",
564 AssetTag.class.getName()
565 );
566
567 updateResourceCodes(
568 "com.liferay.portlet.tags.model.TagsAsset",
569 AssetEntry.class.getName()
570 );
571
572 updateResourceCodes(
573 "com.liferay.portlet.tags.model.TagsVocabulary",
574 AssetVocabulary.class.getName()
575 );
576 }
577
578 protected void updateResourceCodes(String oldCodeName, String newCodeName)
579 throws Exception {
580
581
583 runSQL(
584 "update ResourceCode set name = '" + newCodeName + "' where" +
585 " name = '" + oldCodeName + "';");
586
587
589 runSQL(
590 "update ResourceAction set name = '" + newCodeName + "' where" +
591 " name = '" + oldCodeName + "';");
592
593 runSQL(
594 "update ResourcePermission set name = '" + newCodeName + "' where" +
595 " name = '" + oldCodeName + "';");
596 }
597
598 }