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.counter.service.CounterLocalServiceUtil;
26  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
27  import com.liferay.portal.kernel.dao.jdbc.SmartResultSet;
28  import com.liferay.portal.kernel.upgrade.UpgradeProcess;
29  import com.liferay.portal.kernel.util.ArrayUtil;
30  import com.liferay.portal.kernel.util.StringPool;
31  import com.liferay.portal.kernel.util.Validator;
32  import com.liferay.portal.service.ServiceContext;
33  import com.liferay.portal.util.PropsValues;
34  import com.liferay.portlet.tags.NoSuchEntryException;
35  import com.liferay.portlet.tags.NoSuchVocabularyException;
36  import com.liferay.portlet.tags.model.TagsVocabulary;
37  import com.liferay.portlet.tags.service.TagsVocabularyLocalServiceUtil;
38  
39  import java.sql.Connection;
40  import java.sql.PreparedStatement;
41  import java.sql.ResultSet;
42  import java.sql.Timestamp;
43  
44  import java.util.HashMap;
45  import java.util.Map;
46  
47  /**
48   * <a href="UpgradeTags.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author Jorge Ferrer
51   * @author Brian Wing Shun Chan
52   */
53  public class UpgradeTags extends UpgradeProcess {
54  
55      protected long copyEntry(long groupId, long entryId) throws Exception {
56          String key = groupId + StringPool.UNDERLINE + entryId;
57  
58          Long newEntryId = _entryIdsMap.get(key);
59  
60          if (newEntryId != null) {
61              return newEntryId.longValue();
62          }
63  
64          Connection con = null;
65          PreparedStatement ps = null;
66          ResultSet rs = null;
67  
68          try {
69              con = DataAccess.getConnection();
70  
71              ps = con.prepareStatement(
72                  "select * from TagsEntry where entryId = ?");
73  
74              ps.setLong(1, entryId);
75  
76              rs = ps.executeQuery();
77  
78              while (rs.next()) {
79                  long companyId = rs.getLong("companyId");
80                  long userId = rs.getLong("userId");
81                  String userName = rs.getString("userName");
82                  Timestamp createDate = rs.getTimestamp("createDate");
83                  Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
84                  long parentEntryId = rs.getLong("parentEntryId");
85                  String name = rs.getString("name");
86                  long vocabularyId = rs.getLong("vocabularyId");
87  
88                  newEntryId = CounterLocalServiceUtil.increment();
89  
90                  ps = con.prepareStatement(
91                      "insert into TagsEntry (entryId, groupId, companyId, " +
92                          "userId, userName, createDate, modifiedDate, " +
93                              "parentEntryId, name, vocabularyId) values (?, " +
94                                  "?, ?, ?, ?, ?, ?, ?, ?, ?)");
95  
96                  ps.setLong(1, newEntryId);
97                  ps.setLong(2, groupId);
98                  ps.setLong(3, companyId);
99                  ps.setLong(4, userId);
100                 ps.setString(5, userName);
101                 ps.setTimestamp(6, createDate);
102                 ps.setTimestamp(7, modifiedDate);
103                 ps.setLong(8, parentEntryId);
104                 ps.setString(9, name);
105                 ps.setLong(10, vocabularyId);
106 
107                 ps.executeUpdate();
108 
109                 ps.close();
110 
111                 copyProperties(entryId, newEntryId);
112 
113                 _entryIdsMap.put(key, newEntryId);
114 
115                 return newEntryId;
116             }
117         }
118         finally {
119             DataAccess.cleanUp(con, ps, rs);
120         }
121 
122         throw new NoSuchEntryException(
123             "No TagsEntry exists with the primary key " + entryId);
124     }
125 
126     public void copyProperties(long entryId, long newEntryId) throws Exception {
127         Connection con = null;
128         PreparedStatement ps = null;
129         ResultSet rs = null;
130 
131         try {
132             con = DataAccess.getConnection();
133 
134             ps = con.prepareStatement(
135                 "select * from TagsProperty where entryId = ?");
136 
137             ps.setLong(1, entryId);
138 
139             rs = ps.executeQuery();
140 
141             while (rs.next()) {
142                 long companyId = rs.getLong("companyId");
143                 long userId = rs.getLong("userId");
144                 String userName = rs.getString("userName");
145                 Timestamp createDate = rs.getTimestamp("createDate");
146                 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
147                 String key = rs.getString("key_");
148                 String value = rs.getString("value");
149 
150                 long newPropertyId = CounterLocalServiceUtil.increment();
151 
152                 ps = con.prepareStatement(
153                     "insert into TagsProperty (propertyId, companyId, " +
154                         "userId, userName, createDate, modifiedDate, " +
155                             "entryId, key_, value) values (?, ?, ?, ?, ?, ?, " +
156                                 "?, ?, ?)");
157 
158                 ps.setLong(1, newPropertyId);
159                 ps.setLong(2, companyId);
160                 ps.setLong(3, userId);
161                 ps.setString(4, userName);
162                 ps.setTimestamp(5, createDate);
163                 ps.setTimestamp(6, modifiedDate);
164                 ps.setLong(7, newEntryId);
165                 ps.setString(8, key);
166                 ps.setString(9, value);
167 
168                 ps.executeUpdate();
169 
170                 ps.close();
171             }
172         }
173         finally {
174             DataAccess.cleanUp(con, ps, rs);
175         }
176     }
177 
178     protected void deleteEntries() throws Exception {
179         Connection con = null;
180         PreparedStatement ps = null;
181         ResultSet rs = null;
182 
183         try {
184             con = DataAccess.getConnection();
185 
186             ps = con.prepareStatement(
187                 "select entryId from TagsEntry where groupId = 0");
188 
189             rs = ps.executeQuery();
190 
191             while (rs.next()) {
192                 long entryId = rs.getLong("entryId");
193 
194                 ps = con.prepareStatement(
195                     "delete from TagsAssets_TagsEntries where entryId = ?");
196 
197                 ps.setLong(1, entryId);
198 
199                 ps.executeUpdate();
200 
201                 ps.close();
202 
203                 ps = con.prepareStatement(
204                     "delete from TagsProperty where entryId = ?");
205 
206                 ps.setLong(1, entryId);
207 
208                 ps.executeUpdate();
209 
210                 ps.close();
211             }
212 
213             ps = con.prepareStatement(
214                 "delete from TagsEntry where groupId = 0");
215 
216             ps.executeUpdate();
217 
218             ps.close();
219         }
220         finally {
221             DataAccess.cleanUp(con, ps, rs);
222         }
223     }
224 
225     protected void doUpgrade() throws Exception {
226         updateGroupIds();
227         updateCategories();
228         updateAssets();
229     }
230 
231     protected long getVocabularyId(
232             long userId, long groupId, String vocabularyName)
233         throws Exception {
234 
235         vocabularyName = vocabularyName.trim();
236 
237         if (Validator.isNull(vocabularyName) ||
238             ArrayUtil.contains(
239                 _DEFAULT_CATEGORY_PROPERTY_VALUES, vocabularyName)) {
240 
241             vocabularyName = PropsValues.TAGS_VOCABULARY_DEFAULT;
242         }
243 
244         String key = groupId + StringPool.UNDERLINE + vocabularyName;
245 
246         TagsVocabulary vocabulary = _vocabulariesMap.get(key);
247 
248         if (vocabulary == null) {
249             try {
250                 vocabulary = TagsVocabularyLocalServiceUtil.getGroupVocabulary(
251                     groupId, vocabularyName);
252             }
253             catch (NoSuchVocabularyException nsve) {
254                 ServiceContext serviceContext = new ServiceContext();
255 
256                 serviceContext.setAddCommunityPermissions(true);
257                 serviceContext.setAddGuestPermissions(true);
258                 serviceContext.setScopeGroupId(groupId);
259 
260                 vocabulary = TagsVocabularyLocalServiceUtil.addVocabulary(
261                     userId, vocabularyName, true, serviceContext);
262             }
263 
264             _vocabulariesMap.put(key, vocabulary);
265         }
266 
267         return vocabulary.getVocabularyId();
268     }
269 
270     protected void updateAssets() throws Exception {
271         Connection con = null;
272         PreparedStatement ps = null;
273         ResultSet rs = null;
274 
275         try {
276             con = DataAccess.getConnection();
277 
278             ps = con.prepareStatement(
279                 "select resourcePrimKey from JournalArticle where approved " +
280                     "= ?");
281 
282             ps.setBoolean(1, false);
283 
284             rs = ps.executeQuery();
285 
286             while (rs.next()) {
287                 long resourcePrimKey = rs.getLong("resourcePrimKey");
288 
289                 ps = con.prepareStatement(
290                     "update TagsAsset set visible = ? where classPK = ?");
291 
292                 ps.setBoolean(1, false);
293                 ps.setLong(2, resourcePrimKey);
294 
295                 ps.executeUpdate();
296 
297                 ps.close();
298             }
299         }
300         finally {
301             DataAccess.cleanUp(con, ps, rs);
302         }
303     }
304 
305     protected void updateCategories() throws Exception {
306         Connection con = null;
307         PreparedStatement ps = null;
308         ResultSet rs = null;
309 
310         try {
311             con = DataAccess.getConnection();
312 
313             ps = con.prepareStatement(
314                 "select TE.entryId, TE.groupId, TE.userId, TP.propertyId, " +
315                     "TP.value from TagsEntry TE, TagsProperty TP where " +
316                         "TE.entryId = TP.entryId and TE.vocabularyId <= 0 " +
317                             "and TP.key_ = 'category'");
318 
319             rs = ps.executeQuery();
320 
321             SmartResultSet srs = new SmartResultSet(rs);
322 
323             while (srs.next()) {
324                 long entryId = srs.getLong("TE.entryId");
325                 long groupId = srs.getLong("TE.groupId");
326                 long userId = srs.getLong("TE.userId");
327                 long propertyId = srs.getLong("TP.propertyId");
328                 String value = srs.getString("TP.value");
329 
330                 long vocabularyId = getVocabularyId(userId, groupId, value);
331 
332                 ps = con.prepareStatement(
333                     "update TagsEntry set vocabularyId = ? where entryId = ?");
334 
335                 ps.setLong(1, vocabularyId);
336                 ps.setLong(2, entryId);
337 
338                 ps.executeUpdate();
339 
340                 ps.close();
341 
342                 ps = con.prepareStatement(
343                     "delete from TagsProperty where propertyId = ?");
344 
345                 ps.setLong(1, propertyId);
346 
347                 ps.executeUpdate();
348 
349                 ps.close();
350             }
351         }
352         finally {
353             DataAccess.cleanUp(con, ps, rs);
354         }
355     }
356 
357     protected void updateGroupIds() throws Exception {
358         Connection con = null;
359         PreparedStatement ps = null;
360         ResultSet rs = null;
361 
362         try {
363             con = DataAccess.getConnection();
364 
365             ps = con.prepareStatement(
366                 "select TA.assetId, TA.groupId, TA_TE.entryId from " +
367                     "TagsAssets_TagsEntries TA_TE inner join TagsAsset TA on " +
368                         "TA.assetId = TA_TE.assetId");
369 
370             rs = ps.executeQuery();
371 
372             SmartResultSet srs = new SmartResultSet(rs);
373 
374             while (srs.next()) {
375                 long assetId = srs.getLong("TA.assetId");
376                 long groupId = srs.getLong("TA.groupId");
377                 long entryId = srs.getLong("TA_TE.entryId");
378 
379                 long newEntryId = copyEntry(groupId, entryId);
380 
381                 ps = con.prepareStatement(
382                     "insert into TagsAssets_TagsEntries (assetId, entryId) " +
383                         "values (?, ?)");
384 
385                 ps.setLong(1, assetId);
386                 ps.setLong(2, newEntryId);
387 
388                 ps.executeUpdate();
389 
390                 ps.close();
391             }
392         }
393         finally {
394             DataAccess.cleanUp(con, ps, rs);
395         }
396 
397         deleteEntries();
398     }
399 
400     private String[] _DEFAULT_CATEGORY_PROPERTY_VALUES = new String[] {
401         "undefined", "no category", "category"
402     };
403 
404     private Map<String, Long> _entryIdsMap = new HashMap<String, Long>();
405     private Map<String, TagsVocabulary> _vocabulariesMap =
406         new HashMap<String, TagsVocabulary>();
407 
408 }