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