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