001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
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    /**
046     * @author Jorge Ferrer
047     * @author Brian Wing Shun Chan
048     */
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    }