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