001
014
015 package com.liferay.portal.upgrade.v5_2_0;
016
017 import com.liferay.counter.service.CounterLocalServiceUtil;
018 import com.liferay.portal.NoSuchResourceException;
019 import com.liferay.portal.NoSuchRoleException;
020 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
021 import com.liferay.portal.kernel.dao.jdbc.SmartResultSet;
022 import com.liferay.portal.kernel.upgrade.UpgradeProcess;
023 import com.liferay.portal.kernel.util.ArrayUtil;
024 import com.liferay.portal.kernel.util.StringBundler;
025 import com.liferay.portal.kernel.util.StringPool;
026 import com.liferay.portal.kernel.util.Validator;
027 import com.liferay.portal.model.ResourceCode;
028 import com.liferay.portal.model.ResourceConstants;
029 import com.liferay.portal.model.Role;
030 import com.liferay.portal.model.RoleConstants;
031 import com.liferay.portal.service.ResourceCodeLocalServiceUtil;
032 import com.liferay.portal.service.ResourceLocalServiceUtil;
033 import com.liferay.portal.service.RoleLocalServiceUtil;
034 import com.liferay.portal.util.PropsValues;
035 import com.liferay.portlet.asset.NoSuchTagException;
036
037 import java.sql.Connection;
038 import java.sql.PreparedStatement;
039 import java.sql.ResultSet;
040 import java.sql.Timestamp;
041
042 import java.util.HashMap;
043 import java.util.Map;
044
045
049 public class UpgradeTags extends UpgradeProcess {
050
051 protected void addEntry(
052 long entryId, long groupId, long companyId, long userId,
053 String userName, Timestamp createDate, Timestamp modifiedDate,
054 long parentEntryId, String name, long vocabularyId)
055 throws Exception {
056
057 Connection con = null;
058 PreparedStatement ps = null;
059
060 try {
061 con = DataAccess.getConnection();
062
063 ps = con.prepareStatement(
064 "insert into TagsEntry (entryId, groupId, companyId, userId, " +
065 "userName, createDate, modifiedDate, parentEntryId, " +
066 "name, vocabularyId) values (?, ?, ?, ?, ?, ?, ?, ?, " +
067 "?, ?)");
068
069 ps.setLong(1, entryId);
070 ps.setLong(2, groupId);
071 ps.setLong(3, companyId);
072 ps.setLong(4, userId);
073 ps.setString(5, userName);
074 ps.setTimestamp(6, createDate);
075 ps.setTimestamp(7, modifiedDate);
076 ps.setLong(8, parentEntryId);
077 ps.setString(9, name);
078 ps.setLong(10, vocabularyId);
079
080 ps.executeUpdate();
081 }
082 finally {
083 DataAccess.cleanUp(con, ps);
084 }
085
086 addResources(
087 companyId, "com.liferay.portlet.tags.model.TagsEntry",
088 String.valueOf(entryId));
089 }
090
091 protected void addProperty(
092 long propertyId, long companyId, long userId, String userName,
093 Timestamp createDate, Timestamp modifiedDate, long entryId,
094 String key, String value)
095 throws Exception {
096
097 Connection con = null;
098 PreparedStatement ps = null;
099
100 try {
101 con = DataAccess.getConnection();
102
103 ps = con.prepareStatement(
104 "insert into TagsProperty (propertyId, companyId, userId, " +
105 "userName, createDate, modifiedDate, entryId, key_, " +
106 "value) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
107
108 ps.setLong(1, propertyId);
109 ps.setLong(2, companyId);
110 ps.setLong(3, userId);
111 ps.setString(4, userName);
112 ps.setTimestamp(5, createDate);
113 ps.setTimestamp(6, modifiedDate);
114 ps.setLong(7, entryId);
115 ps.setString(8, key);
116 ps.setString(9, value);
117
118 ps.executeUpdate();
119 }
120 finally {
121 DataAccess.cleanUp(con, ps);
122 }
123 }
124
125 protected void addResource(long resourceCodeId, String primKey)
126 throws Exception {
127
128 long resourceId = CounterLocalServiceUtil.increment(
129 "com.liferay.portal.model.Resource");
130
131 StringBundler sb = new StringBundler(8);
132
133 sb.append("insert into Resource_ (resourceId, codeId, primKey) ");
134 sb.append("values (");
135 sb.append(resourceId);
136 sb.append(", ");
137 sb.append(resourceCodeId);
138 sb.append(", '");
139 sb.append(primKey);
140 sb.append("')");
141
142 runSQL(sb.toString());
143 }
144
145 protected void addResourceCode(
146 long resourceCodeId, long companyId, String resourceName)
147 throws Exception {
148
149 StringBundler sb = new StringBundler(10);
150
151 sb.append("insert into ResourceCode (codeId, companyId, name, scope) ");
152 sb.append("values (");
153 sb.append(resourceCodeId);
154 sb.append(", ");
155 sb.append(companyId);
156 sb.append(", '");
157 sb.append(resourceName);
158 sb.append("', ");
159 sb.append(ResourceConstants.SCOPE_INDIVIDUAL);
160 sb.append(")");
161
162 runSQL(sb.toString());
163 }
164
165 protected void addResourcePermission(
166 long companyId, long roleId, String resourceName, String primKey)
167 throws Exception {
168
169 StringBundler sb = new StringBundler(15);
170
171 sb.append("insert into ResourcePermission (resourcePermissionId, ");
172 sb.append("companyId, name, scope, primKey, roleId, actionIds) ");
173 sb.append("values (");
174 sb.append(increment());
175 sb.append(", ");
176 sb.append(companyId);
177 sb.append(", '");
178 sb.append(resourceName);
179 sb.append("', ");
180 sb.append(ResourceConstants.SCOPE_INDIVIDUAL);
181 sb.append(", '");
182 sb.append(primKey);
183 sb.append("', ");
184 sb.append(roleId);
185 sb.append(", 0)");
186
187 runSQL(sb.toString());
188 }
189
190 protected void addResources(
191 long companyId, String resourceName, String primKey)
192 throws Exception {
193
194 if (PropsValues.PERMISSIONS_USER_CHECK_ALGORITHM == 5) {
195 ResourceCode resourceCode =
196 ResourceCodeLocalServiceUtil.getResourceCode(
197 companyId, resourceName,
198 ResourceConstants.SCOPE_INDIVIDUAL);
199
200 try {
201 ResourceLocalServiceUtil.getResource(
202 companyId, resourceName, ResourceConstants.SCOPE_INDIVIDUAL,
203 primKey);
204 }
205 catch (NoSuchResourceException nsre) {
206 addResource(resourceCode.getCodeId(), primKey);
207 }
208 }
209 else if (PropsValues.PERMISSIONS_USER_CHECK_ALGORITHM == 6) {
210 try {
211 Role role = RoleLocalServiceUtil.getRole(
212 companyId, RoleConstants.OWNER);
213
214 addResourcePermission(
215 companyId, role.getRoleId(), resourceName, primKey);
216 }
217 catch (NoSuchRoleException nsre) {
218 }
219 }
220 }
221
222 protected long addVocabulary(
223 long vocabularyId, long groupId, long companyId, long userId,
224 String userName, String name)
225 throws Exception {
226
227 Timestamp now = new Timestamp(System.currentTimeMillis());
228
229 Connection con = null;
230 PreparedStatement ps = null;
231 ResultSet rs = null;
232
233 try {
234 con = DataAccess.getConnection();
235
236 StringBuilder sb = new StringBuilder();
237
238 sb.append("insert into TagsVocabulary (vocabularyId, groupId, ");
239 sb.append("companyId, userId, userName, createDate, ");
240 sb.append("modifiedDate, name, description, folksonomy) values (");
241 sb.append("?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
242
243 String sql = sb.toString();
244
245 ps = con.prepareStatement(sql);
246
247 ps.setLong(1, vocabularyId);
248 ps.setLong(2, groupId);
249 ps.setLong(3, companyId);
250 ps.setLong(4, userId);
251 ps.setString(5, userName);
252 ps.setTimestamp(6, now);
253 ps.setTimestamp(7, now);
254 ps.setString(8, name);
255 ps.setString(9, StringPool.BLANK);
256 ps.setBoolean(10, true);
257
258 ps.executeUpdate();
259
260 }
261 finally {
262 DataAccess.cleanUp(con, ps, rs);
263 }
264
265 addResources(
266 companyId, "com.liferay.portlet.tags.model.TagsVocabulary",
267 String.valueOf(vocabularyId));
268
269 return vocabularyId;
270 }
271
272 protected long copyEntry(long groupId, long entryId) throws Exception {
273 String key = groupId + StringPool.UNDERLINE + entryId;
274
275 Long newEntryId = _entryIdsMap.get(key);
276
277 if (newEntryId != null) {
278 return newEntryId.longValue();
279 }
280
281 Connection con = null;
282 PreparedStatement ps = null;
283 ResultSet rs = null;
284
285 try {
286 con = DataAccess.getConnection();
287
288 ps = con.prepareStatement(
289 "select * from TagsEntry where entryId = ?",
290 ResultSet.TYPE_SCROLL_INSENSITIVE,
291 ResultSet.CONCUR_READ_ONLY);
292
293 ps.setLong(1, entryId);
294
295 rs = ps.executeQuery();
296
297 while (rs.next()) {
298 long companyId = rs.getLong("companyId");
299 long userId = rs.getLong("userId");
300 String userName = rs.getString("userName");
301 Timestamp createDate = rs.getTimestamp("createDate");
302 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
303 long parentEntryId = rs.getLong("parentEntryId");
304 String name = rs.getString("name");
305 long vocabularyId = rs.getLong("vocabularyId");
306
307 newEntryId = increment();
308
309 addEntry(
310 newEntryId, groupId, companyId, userId, userName,
311 createDate, modifiedDate, parentEntryId, name,
312 vocabularyId);
313
314 copyProperties(entryId, newEntryId);
315
316 _entryIdsMap.put(key, newEntryId);
317
318 return newEntryId;
319 }
320 }
321 finally {
322 DataAccess.cleanUp(con, ps, rs);
323 }
324
325 throw new NoSuchTagException(
326 "No AssetTag exists with the primary key " + entryId);
327 }
328
329 protected void copyProperties(long entryId, long newEntryId)
330 throws Exception {
331
332 Connection con = null;
333 PreparedStatement ps = null;
334 ResultSet rs = null;
335
336 try {
337 con = DataAccess.getConnection();
338
339 ps = con.prepareStatement(
340 "select * from TagsProperty where entryId = ?",
341 ResultSet.TYPE_SCROLL_INSENSITIVE,
342 ResultSet.CONCUR_READ_ONLY);
343
344 ps.setLong(1, entryId);
345
346 rs = ps.executeQuery();
347
348 while (rs.next()) {
349 long companyId = rs.getLong("companyId");
350 long userId = rs.getLong("userId");
351 String userName = rs.getString("userName");
352 Timestamp createDate = rs.getTimestamp("createDate");
353 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
354 String key = rs.getString("key_");
355 String value = rs.getString("value");
356
357 long newPropertyId = increment();
358
359 addProperty(
360 newPropertyId, companyId, userId, userName, createDate,
361 modifiedDate, newEntryId, key, value);
362 }
363 }
364 finally {
365 DataAccess.cleanUp(con, ps, rs);
366 }
367 }
368
369 protected void deleteEntries() throws Exception {
370 Connection con = null;
371 PreparedStatement ps = null;
372 ResultSet rs = null;
373
374 try {
375 con = DataAccess.getConnection();
376
377 ps = con.prepareStatement(
378 "select entryId from TagsEntry where groupId = 0");
379
380 rs = ps.executeQuery();
381
382 while (rs.next()) {
383 long entryId = rs.getLong("entryId");
384
385 runSQL(
386 "delete from TagsAssets_TagsEntries where entryId = " +
387 entryId);
388
389 runSQL("delete from TagsProperty where entryId = " + entryId);
390 }
391
392 runSQL("delete from TagsEntry where groupId = 0");
393 }
394 finally {
395 DataAccess.cleanUp(con, ps, rs);
396 }
397 }
398
399 @Override
400 protected void doUpgrade() throws Exception {
401 updateGroupIds();
402 updateCategories();
403 updateAssets();
404 }
405
406 protected long getVocabularyId(
407 long groupId, long companyId, long userId, String userName,
408 String name)
409 throws Exception {
410
411 name = name.trim();
412
413 if (Validator.isNull(name) ||
414 ArrayUtil.contains(_DEFAULT_CATEGORY_PROPERTY_VALUES, name)) {
415
416 name = _DEFAULT_TAGS_VOCABULARY;
417 }
418
419 String key = groupId + StringPool.UNDERLINE + name;
420
421 Long vocabularyId = _vocabularyIdsMap.get(key);
422
423 if (vocabularyId != null) {
424 return vocabularyId.longValue();
425 }
426
427 Connection con = null;
428 PreparedStatement ps = null;
429 ResultSet rs = null;
430
431 try {
432 con = DataAccess.getConnection();
433
434 ps = con.prepareStatement(
435 "select vocabularyId from TagsVocabulary where groupId = ? " +
436 "and name = ?");
437
438 ps.setLong(1, groupId);
439 ps.setString(2, name);
440
441 rs = ps.executeQuery();
442
443 if (rs.next()) {
444 vocabularyId = rs.getLong("vocabularyId");
445 }
446 else {
447 long newVocabularyId = increment();
448
449 vocabularyId = addVocabulary(
450 newVocabularyId, groupId, companyId, userId, userName,
451 name);
452 }
453 }
454 finally {
455 DataAccess.cleanUp(con, ps, rs);
456 }
457
458 _vocabularyIdsMap.put(key, vocabularyId);
459
460 return vocabularyId.longValue();
461 }
462
463 protected void updateAssets() throws Exception {
464 Connection con = null;
465 PreparedStatement ps = null;
466 ResultSet rs = null;
467
468 try {
469 con = DataAccess.getConnection();
470
471 ps = con.prepareStatement(
472 "select resourcePrimKey from JournalArticle where approved " +
473 "= ?");
474
475 ps.setBoolean(1, false);
476
477 rs = ps.executeQuery();
478
479 while (rs.next()) {
480 long resourcePrimKey = rs.getLong("resourcePrimKey");
481
482 runSQL(
483 "update TagsAsset set visible = FALSE where classPK = " +
484 resourcePrimKey);
485 }
486 }
487 finally {
488 DataAccess.cleanUp(con, ps, rs);
489 }
490 }
491
492 protected void updateCategories() throws Exception {
493 Connection con = null;
494 PreparedStatement ps = null;
495 ResultSet rs = null;
496
497 try {
498 con = DataAccess.getConnection();
499
500 StringBuilder sb = new StringBuilder();
501
502 sb.append("select TE.entryId, TE.groupId, TE.companyId, ");
503 sb.append("TE.userId, TE.userName, TP.propertyId, TP.value from ");
504 sb.append("TagsEntry TE, TagsProperty TP where TE.entryId = ");
505 sb.append("TP.entryId and TE.vocabularyId <= 0 and TP.key_ = ");
506 sb.append("'category'");
507
508 String sql = sb.toString();
509
510 ps = con.prepareStatement(sql);
511
512 rs = ps.executeQuery();
513
514 SmartResultSet srs = new SmartResultSet(rs);
515
516 while (srs.next()) {
517 long entryId = srs.getLong("TE.entryId");
518 long groupId = srs.getLong("TE.groupId");
519 long companyId = srs.getLong("TE.companyId");
520 long userId = srs.getLong("TE.userId");
521 String userName = srs.getString("TE.userName");
522 long propertyId = srs.getLong("TP.propertyId");
523 String value = srs.getString("TP.value");
524
525 long vocabularyId = getVocabularyId(
526 groupId, companyId, userId, userName, value);
527
528 runSQL(
529 "update TagsEntry set vocabularyId = " + vocabularyId +
530 " where entryId = " + entryId);
531
532 runSQL(
533 "delete from TagsProperty where propertyId = " +
534 propertyId);
535 }
536 }
537 finally {
538 DataAccess.cleanUp(con, ps, rs);
539 }
540 }
541
542 protected void updateGroupIds() throws Exception {
543 Connection con = null;
544 PreparedStatement ps = null;
545 ResultSet rs = null;
546
547 try {
548 con = DataAccess.getConnection();
549
550 ps = con.prepareStatement(
551 "select TA.assetId, TA.groupId, TA_TE.entryId from " +
552 "TagsAssets_TagsEntries TA_TE inner join TagsAsset TA on " +
553 "TA.assetId = TA_TE.assetId",
554 ResultSet.TYPE_SCROLL_INSENSITIVE,
555 ResultSet.CONCUR_READ_ONLY);
556
557 rs = ps.executeQuery();
558
559 SmartResultSet srs = new SmartResultSet(rs);
560
561 while (srs.next()) {
562 long assetId = srs.getLong("TA.assetId");
563 long groupId = srs.getLong("TA.groupId");
564 long entryId = srs.getLong("TA_TE.entryId");
565
566 long newEntryId = copyEntry(groupId, entryId);
567
568 runSQL(
569 "insert into TagsAssets_TagsEntries (assetId, entryId) " +
570 "values (" + assetId + ", " + newEntryId + ")");
571 }
572 }
573 finally {
574 DataAccess.cleanUp(con, ps, rs);
575 }
576
577 deleteEntries();
578 }
579
580 private static final String[] _DEFAULT_CATEGORY_PROPERTY_VALUES = {
581 "undefined", "no category", "category"
582 };
583
584 private static final String _DEFAULT_TAGS_VOCABULARY = "Default Tag Set";
585
586 private Map<String, Long> _entryIdsMap = new HashMap<String, Long>();
587 private Map<String, Long> _vocabularyIdsMap = new HashMap<String, Long>();
588
589 }