1
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
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 }