1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
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  /**
36   * <a href="UpgradeAsset.java.html"><b><i>View Source</i></b></a>
37   *
38   * @author Jorge Ferrer
39   * @author Brian Wing Shun Chan
40   */
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         // Algorithm 1-5
542 
543         runSQL(
544             "update Resource_ set codeId = '" + newCodeId + "' where " +
545                 "codeId = '" + oldCodeId + "' and primKey = '" + categoryId +
546                     "';");
547 
548         // Algorithm 6
549 
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         // Algorithm 1-5
582 
583         runSQL(
584             "update ResourceCode set name = '" + newCodeName + "' where" +
585                 " name = '" + oldCodeName + "';");
586 
587         // Algorithm 6
588 
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 }