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