1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.upgrade.v5_2_0;
24  
25  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.dao.jdbc.SmartResultSet;
27  import com.liferay.portal.kernel.upgrade.UpgradeProcess;
28  import com.liferay.portal.kernel.util.ArrayUtil;
29  import com.liferay.portal.kernel.util.StringPool;
30  import com.liferay.portal.kernel.util.Validator;
31  import com.liferay.portal.util.PropsValues;
32  import com.liferay.portlet.tags.NoSuchEntryException;
33  
34  import java.sql.Connection;
35  import java.sql.PreparedStatement;
36  import java.sql.ResultSet;
37  import java.sql.Timestamp;
38  
39  import java.util.HashMap;
40  import java.util.Map;
41  
42  /**
43   * <a href="UpgradeTags.java.html"><b><i>View Source</i></b></a>
44   *
45   * @author Jorge Ferrer
46   * @author Brian Wing Shun Chan
47   */
48  public class UpgradeTags extends UpgradeProcess {
49  
50      protected void addEntry(
51              long entryId, long groupId, long companyId, long userId,
52              String userName, Timestamp createDate, Timestamp modifiedDate,
53              long parentEntryId, String name, long vocabularyId)
54          throws Exception {
55  
56          Connection con = null;
57          PreparedStatement ps = null;
58  
59          try {
60              con = DataAccess.getConnection();
61  
62              ps = con.prepareStatement(
63                  "insert into TagsEntry (entryId, groupId, companyId, userId, " +
64                      "userName, createDate, modifiedDate, parentEntryId, " +
65                          "name, vocabularyId) values (?, ?, ?, ?, ?, ?, ?, ?, " +
66                              "?, ?)");
67  
68              ps.setLong(1, entryId);
69              ps.setLong(2, groupId);
70              ps.setLong(3, companyId);
71              ps.setLong(4, userId);
72              ps.setString(5, userName);
73              ps.setTimestamp(6, createDate);
74              ps.setTimestamp(7, modifiedDate);
75              ps.setLong(8, parentEntryId);
76              ps.setString(9, name);
77              ps.setLong(10, vocabularyId);
78  
79              ps.executeUpdate();
80          }
81          finally {
82              DataAccess.cleanUp(con, ps);
83          }
84      }
85  
86      protected void addProperty(
87              long propertyId, long companyId, long userId, String userName,
88              Timestamp createDate, Timestamp modifiedDate, long entryId,
89              String key, String value)
90          throws Exception {
91  
92          Connection con = null;
93          PreparedStatement ps = null;
94  
95          try {
96              con = DataAccess.getConnection();
97  
98              ps = con.prepareStatement(
99                  "insert into TagsProperty (propertyId, companyId, userId, " +
100                     "userName, createDate, modifiedDate, entryId, key_, " +
101                         "value) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
102 
103             ps.setLong(1, propertyId);
104             ps.setLong(2, companyId);
105             ps.setLong(3, userId);
106             ps.setString(4, userName);
107             ps.setTimestamp(5, createDate);
108             ps.setTimestamp(6, modifiedDate);
109             ps.setLong(7, entryId);
110             ps.setString(8, key);
111             ps.setString(9, value);
112 
113             ps.executeUpdate();
114         }
115         finally {
116             DataAccess.cleanUp(con, ps);
117         }
118     }
119 
120     protected long addVocabulary(
121             long groupId, long companyId, long userId, String userName,
122             String name)
123         throws Exception {
124 
125         long vocabularyId = increment();
126         Timestamp now = new Timestamp(System.currentTimeMillis());
127 
128         Connection con = null;
129         PreparedStatement ps = null;
130         ResultSet rs = null;
131 
132         try {
133             con = DataAccess.getConnection();
134 
135             StringBuilder sb = new StringBuilder();
136 
137             sb.append("insert into TagsVocabulary (vocabularyId, groupId, ");
138             sb.append("companyId, userId, userName, createDate, ");
139             sb.append("modifiedDate, name, description, folksonomy) values (");
140             sb.append("?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
141 
142             String sql = sb.toString();
143 
144             ps = con.prepareStatement(sql);
145 
146             ps.setLong(1, vocabularyId);
147             ps.setLong(2, groupId);
148             ps.setLong(3, companyId);
149             ps.setLong(4, userId);
150             ps.setString(5, userName);
151             ps.setTimestamp(6, now);
152             ps.setTimestamp(7, now);
153             ps.setString(8, name);
154             ps.setString(9, StringPool.BLANK);
155             ps.setBoolean(10, true);
156 
157             ps.executeUpdate();
158 
159         }
160         finally {
161             DataAccess.cleanUp(con, ps, rs);
162         }
163 
164         return vocabularyId;
165     }
166 
167     protected long copyEntry(long groupId, long entryId) throws Exception {
168         String key = groupId + StringPool.UNDERLINE + entryId;
169 
170         Long newEntryId = _entryIdsMap.get(key);
171 
172         if (newEntryId != null) {
173             return newEntryId.longValue();
174         }
175 
176         Connection con = null;
177         PreparedStatement ps = null;
178         ResultSet rs = null;
179 
180         try {
181             con = DataAccess.getConnection();
182 
183             ps = con.prepareStatement(
184                 "select * from TagsEntry where entryId = ?");
185 
186             ps.setLong(1, entryId);
187 
188             rs = ps.executeQuery();
189 
190             while (rs.next()) {
191                 long companyId = rs.getLong("companyId");
192                 long userId = rs.getLong("userId");
193                 String userName = rs.getString("userName");
194                 Timestamp createDate = rs.getTimestamp("createDate");
195                 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
196                 long parentEntryId = rs.getLong("parentEntryId");
197                 String name = rs.getString("name");
198                 long vocabularyId = rs.getLong("vocabularyId");
199 
200                 newEntryId = increment();
201 
202                 addEntry(
203                     newEntryId, groupId, companyId, userId, userName,
204                     createDate, modifiedDate, parentEntryId, name,
205                     vocabularyId);
206 
207                 copyProperties(entryId, newEntryId);
208 
209                 _entryIdsMap.put(key, newEntryId);
210 
211                 return newEntryId;
212             }
213         }
214         finally {
215             DataAccess.cleanUp(con, ps, rs);
216         }
217 
218         throw new NoSuchEntryException(
219             "No TagsEntry exists with the primary key " + entryId);
220     }
221 
222     protected void copyProperties(long entryId, long newEntryId)
223         throws Exception {
224 
225         Connection con = null;
226         PreparedStatement ps = null;
227         ResultSet rs = null;
228 
229         try {
230             con = DataAccess.getConnection();
231 
232             ps = con.prepareStatement(
233                 "select * from TagsProperty where entryId = ?");
234 
235             ps.setLong(1, entryId);
236 
237             rs = ps.executeQuery();
238 
239             while (rs.next()) {
240                 long companyId = rs.getLong("companyId");
241                 long userId = rs.getLong("userId");
242                 String userName = rs.getString("userName");
243                 Timestamp createDate = rs.getTimestamp("createDate");
244                 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
245                 String key = rs.getString("key_");
246                 String value = rs.getString("value");
247 
248                 long newPropertyId = increment();
249 
250                 addProperty(
251                     newPropertyId, companyId, userId, userName, createDate,
252                     modifiedDate, newEntryId, key, value);
253             }
254         }
255         finally {
256             DataAccess.cleanUp(con, ps, rs);
257         }
258     }
259 
260     protected void deleteEntries() throws Exception {
261         Connection con = null;
262         PreparedStatement ps = null;
263         ResultSet rs = null;
264 
265         try {
266             con = DataAccess.getConnection();
267 
268             ps = con.prepareStatement(
269                 "select entryId from TagsEntry where groupId = 0");
270 
271             rs = ps.executeQuery();
272 
273             while (rs.next()) {
274                 long entryId = rs.getLong("entryId");
275 
276                 ps = con.prepareStatement(
277                     "delete from TagsAssets_TagsEntries where entryId = ?");
278 
279                 ps.setLong(1, entryId);
280 
281                 ps.executeUpdate();
282 
283                 ps.close();
284 
285                 ps = con.prepareStatement(
286                     "delete from TagsProperty where entryId = ?");
287 
288                 ps.setLong(1, entryId);
289 
290                 ps.executeUpdate();
291 
292                 ps.close();
293             }
294 
295             ps = con.prepareStatement(
296                 "delete from TagsEntry where groupId = 0");
297 
298             ps.executeUpdate();
299 
300             ps.close();
301         }
302         finally {
303             DataAccess.cleanUp(con, ps, rs);
304         }
305     }
306 
307     protected void doUpgrade() throws Exception {
308         updateGroupIds();
309         updateCategories();
310         updateAssets();
311     }
312 
313     protected long getVocabularyId(
314             long groupId, long companyId, long userId, String userName,
315             String name)
316         throws Exception {
317 
318         name = name.trim();
319 
320         if (Validator.isNull(name) ||
321             ArrayUtil.contains(_DEFAULT_CATEGORY_PROPERTY_VALUES, name)) {
322 
323             name = PropsValues.TAGS_VOCABULARY_DEFAULT;
324         }
325 
326         String key = groupId + StringPool.UNDERLINE + name;
327 
328         Long vocabularyId = _vocabularyIdsMap.get(key);
329 
330         if (vocabularyId != null) {
331             return vocabularyId.longValue();
332         }
333 
334         Connection con = null;
335         PreparedStatement ps = null;
336         ResultSet rs = null;
337 
338         try {
339             con = DataAccess.getConnection();
340 
341             ps = con.prepareStatement(
342                 "select vocabularyId from TagsVocabulary where groupId = ? " +
343                     "and name = ?");
344 
345             ps.setLong(1, groupId);
346             ps.setString(2, name);
347 
348             rs = ps.executeQuery();
349 
350             if (rs.next()) {
351                 vocabularyId = rs.getLong("vocabularyId");
352             }
353             else {
354                 vocabularyId = addVocabulary(
355                     groupId, companyId, userId, userName, name);
356             }
357         }
358         finally {
359             DataAccess.cleanUp(con, ps, rs);
360         }
361 
362         _vocabularyIdsMap.put(key, vocabularyId);
363 
364         return vocabularyId.longValue();
365     }
366 
367     protected void updateAssets() throws Exception {
368         Connection con = null;
369         PreparedStatement ps = null;
370         ResultSet rs = null;
371 
372         try {
373             con = DataAccess.getConnection();
374 
375             ps = con.prepareStatement(
376                 "select resourcePrimKey from JournalArticle where approved " +
377                     "= ?");
378 
379             ps.setBoolean(1, false);
380 
381             rs = ps.executeQuery();
382 
383             while (rs.next()) {
384                 long resourcePrimKey = rs.getLong("resourcePrimKey");
385 
386                 runSQL(
387                     "update TagsAsset set visible = FALSE where classPK = " +
388                         resourcePrimKey);
389             }
390         }
391         finally {
392             DataAccess.cleanUp(con, ps, rs);
393         }
394     }
395 
396     protected void updateCategories() throws Exception {
397         Connection con = null;
398         PreparedStatement ps = null;
399         ResultSet rs = null;
400 
401         try {
402             con = DataAccess.getConnection();
403 
404             StringBuilder sb = new StringBuilder();
405 
406             sb.append("select TE.entryId, TE.groupId, TE.companyId, ");
407             sb.append("TE.userId, TE.userName, TP.propertyId, TP.value from ");
408             sb.append("TagsEntry TE, TagsProperty TP where TE.entryId = ");
409             sb.append("TP.entryId and TE.vocabularyId <= 0 and TP.key_ = ");
410             sb.append("'category'");
411 
412             String sql = sb.toString();
413 
414             ps = con.prepareStatement(sql);
415 
416             rs = ps.executeQuery();
417 
418             SmartResultSet srs = new SmartResultSet(rs);
419 
420             while (srs.next()) {
421                 long entryId = srs.getLong("TE.entryId");
422                 long groupId = srs.getLong("TE.groupId");
423                 long companyId = srs.getLong("TE.companyId");
424                 long userId = srs.getLong("TE.userId");
425                 String userName = srs.getString("TE.userName");
426                 long propertyId = srs.getLong("TP.propertyId");
427                 String value = srs.getString("TP.value");
428 
429                 long vocabularyId = getVocabularyId(
430                     groupId, companyId, userId, userName, value);
431 
432                 runSQL(
433                     "update TagsEntry set vocabularyId = " + vocabularyId +
434                         " where entryId = " + entryId);
435 
436                 runSQL(
437                     "delete from TagsProperty where propertyId = " +
438                         propertyId);
439             }
440         }
441         finally {
442             DataAccess.cleanUp(con, ps, rs);
443         }
444     }
445 
446     protected void updateGroupIds() throws Exception {
447         Connection con = null;
448         PreparedStatement ps = null;
449         ResultSet rs = null;
450 
451         try {
452             con = DataAccess.getConnection();
453 
454             ps = con.prepareStatement(
455                 "select TA.assetId, TA.groupId, TA_TE.entryId from " +
456                     "TagsAssets_TagsEntries TA_TE inner join TagsAsset TA on " +
457                         "TA.assetId = TA_TE.assetId");
458 
459             rs = ps.executeQuery();
460 
461             SmartResultSet srs = new SmartResultSet(rs);
462 
463             while (srs.next()) {
464                 long assetId = srs.getLong("TA.assetId");
465                 long groupId = srs.getLong("TA.groupId");
466                 long entryId = srs.getLong("TA_TE.entryId");
467 
468                 long newEntryId = copyEntry(groupId, entryId);
469 
470                 runSQL(
471                     "insert into TagsAssets_TagsEntries (assetId, entryId) " +
472                         "values (" + assetId + ", " + newEntryId + ")");
473             }
474         }
475         finally {
476             DataAccess.cleanUp(con, ps, rs);
477         }
478 
479         deleteEntries();
480     }
481 
482     private String[] _DEFAULT_CATEGORY_PROPERTY_VALUES = new String[] {
483         "undefined", "no category", "category"
484     };
485 
486     private Map<String, Long> _entryIdsMap = new HashMap<String, Long>();
487     private Map<String, Long> _vocabularyIdsMap = new HashMap<String, Long>();
488 
489 }