001    /**
002     * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
013     */
014    
015    package com.liferay.portal.upgrade.v6_0_0;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
019    import com.liferay.portal.kernel.util.StringBundler;
020    import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
021    import com.liferay.portal.model.ResourceCode;
022    import com.liferay.portal.model.ResourceConstants;
023    import com.liferay.portal.service.ResourceCodeLocalServiceUtil;
024    import com.liferay.portal.service.ResourceLocalServiceUtil;
025    import com.liferay.portlet.asset.model.AssetCategory;
026    import com.liferay.portlet.asset.model.AssetEntry;
027    import com.liferay.portlet.asset.model.AssetTag;
028    import com.liferay.portlet.asset.model.AssetVocabulary;
029    
030    import java.sql.Connection;
031    import java.sql.PreparedStatement;
032    import java.sql.ResultSet;
033    import java.sql.Timestamp;
034    
035    /**
036     * @author Jorge Ferrer
037     * @author Brian Wing Shun Chan
038     */
039    public class UpgradeAsset extends UpgradeProcess {
040    
041            protected void addCategory(
042                            long entryId, long groupId, long companyId, long userId,
043                            String userName, Timestamp createDate, Timestamp modifiedDate,
044                            long parentCategoryId, String name, long vocabularyId)
045                    throws Exception {
046    
047                    Connection con = null;
048                    PreparedStatement ps = null;
049    
050                    try {
051                            con = DataAccess.getConnection();
052    
053                            StringBundler sb = new StringBundler(4);
054    
055                            sb.append("insert into AssetCategory (uuid_, categoryId, ");
056                            sb.append("groupId, companyId, userId, userName, createDate, ");
057                            sb.append("modifiedDate, parentCategoryId, name, vocabularyId) ");
058                            sb.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
059    
060                            String sql = sb.toString();
061    
062                            ps = con.prepareStatement(sql);
063    
064                            ps.setString(1, PortalUUIDUtil.generate());
065                            ps.setLong(2, entryId);
066                            ps.setLong(3, groupId);
067                            ps.setLong(4, companyId);
068                            ps.setLong(5, userId);
069                            ps.setString(6, userName);
070                            ps.setTimestamp(7, createDate);
071                            ps.setTimestamp(8, modifiedDate);
072                            ps.setLong(9, parentCategoryId);
073                            ps.setString(10, name);
074                            ps.setLong(11, vocabularyId);
075    
076                            ps.executeUpdate();
077                    }
078                    finally {
079                            DataAccess.cleanUp(con, ps);
080                    }
081            }
082    
083            protected void addEntry(
084                            long assetId, long groupId, long companyId, long userId,
085                            String userName, Timestamp createDate, Timestamp modifiedDate,
086                            long classNameId, long classPK, boolean visible,
087                            Timestamp startDate, Timestamp endDate, Timestamp publishDate,
088                            Timestamp expirationDate, String mimeType, String title,
089                            String description, String summary, String url, int height,
090                            int width, double priority, int viewCount)
091                    throws Exception {
092    
093                    Connection con = null;
094                    PreparedStatement ps = null;
095    
096                    try {
097                            con = DataAccess.getConnection();
098    
099                            StringBundler sb = new StringBundler(7);
100    
101                            sb.append("insert into AssetEntry (entryId, groupId, companyId, ");
102                            sb.append("userId, userName, createDate, modifiedDate, ");
103                            sb.append("classNameId, classPK, visible, startDate, endDate, ");
104                            sb.append("publishDate, expirationDate, mimeType, title, ");
105                            sb.append("description, summary, url, height, width, priority, ");
106                            sb.append("viewCount) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
107                            sb.append("?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
108    
109                            String sql = sb.toString();
110    
111                            ps = con.prepareStatement(sql);
112    
113                            ps.setLong(1, assetId);
114                            ps.setLong(2, groupId);
115                            ps.setLong(3, companyId);
116                            ps.setLong(4, userId);
117                            ps.setString(5, userName);
118                            ps.setTimestamp(6, createDate);
119                            ps.setTimestamp(7, modifiedDate);
120                            ps.setLong(8, classNameId);
121                            ps.setLong(9, classPK);
122                            ps.setBoolean(10, visible);
123                            ps.setTimestamp(11, startDate);
124                            ps.setTimestamp(12, endDate);
125                            ps.setTimestamp(13, publishDate);
126                            ps.setTimestamp(14, expirationDate);
127                            ps.setString(15, mimeType);
128                            ps.setString(16, title);
129                            ps.setString(17, description);
130                            ps.setString(18, summary);
131                            ps.setString(19, url);
132                            ps.setInt(20, height);
133                            ps.setInt(21, width);
134                            ps.setDouble(22, priority);
135                            ps.setInt(23, viewCount);
136    
137                            ps.executeUpdate();
138                    }
139                    finally {
140                            DataAccess.cleanUp(con, ps);
141                    }
142            }
143    
144            protected void addProperty(
145                            String tableName, String pkName, long propertyId, long companyId,
146                            long userId, String userName, Timestamp createDate,
147                            Timestamp modifiedDate, long categoryId, String key, String value)
148                    throws Exception {
149    
150                    Connection con = null;
151                    PreparedStatement ps = null;
152    
153                    try {
154                            con = DataAccess.getConnection();
155    
156                            StringBundler sb = new StringBundler(7);
157    
158                            sb.append("insert into ");
159                            sb.append(tableName);
160                            sb.append(" (");
161                            sb.append(pkName);
162                            sb.append(", companyId, userId, userName, createDate, ");
163                            sb.append("modifiedDate, tagId, key_, value) values (?, ?, ?, ");
164                            sb.append("?, ?, ?, ?, ?, ?)");
165    
166                            String sql = sb.toString();
167    
168                            ps = con.prepareStatement(sql);
169    
170                            ps.setLong(1, propertyId);
171                            ps.setLong(2, companyId);
172                            ps.setLong(3, userId);
173                            ps.setString(4, userName);
174                            ps.setTimestamp(5, createDate);
175                            ps.setTimestamp(6, modifiedDate);
176                            ps.setLong(7, categoryId);
177                            ps.setString(8, key);
178                            ps.setString(9, value);
179    
180                            ps.executeUpdate();
181                    }
182                    finally {
183                            DataAccess.cleanUp(con, ps);
184                    }
185            }
186    
187            protected void addTag(
188                            long entryId, long groupId, long companyId, long userId,
189                            String userName, Timestamp createDate, Timestamp modifiedDate,
190                            String name)
191                    throws Exception {
192    
193                    Connection con = null;
194                    PreparedStatement ps = null;
195    
196                    try {
197                            con = DataAccess.getConnection();
198    
199                            StringBundler sb = new StringBundler(3);
200    
201                            sb.append("insert into AssetTag (tagId, groupId, companyId, ");
202                            sb.append("userId, userName, createDate, modifiedDate, name) ");
203                            sb.append("values (?, ?, ?, ?, ?, ?, ?, ?)");
204    
205                            String sql = sb.toString();
206    
207                            ps = con.prepareStatement(sql);
208    
209                            ps.setLong(1, entryId);
210                            ps.setLong(2, groupId);
211                            ps.setLong(3, companyId);
212                            ps.setLong(4, userId);
213                            ps.setString(5, userName);
214                            ps.setTimestamp(6, createDate);
215                            ps.setTimestamp(7, modifiedDate);
216                            ps.setString(8, name);
217    
218                            ps.executeUpdate();
219                    }
220                    finally {
221                            DataAccess.cleanUp(con, ps);
222                    }
223            }
224    
225            protected void addVocabulary(
226                            long vocabularyId, long groupId, long companyId, long userId,
227                            String userName, Timestamp createDate, Timestamp modifiedDate,
228                            String name, String description)
229                    throws Exception {
230    
231                    Connection con = null;
232                    PreparedStatement ps = null;
233    
234                    try {
235                            con = DataAccess.getConnection();
236    
237                            StringBundler sb = new StringBundler(4);
238    
239                            sb.append("insert into AssetVocabulary (uuid_, vocabularyId, ");
240                            sb.append("groupId, companyId, userId, userName, createDate, ");
241                            sb.append("modifiedDate, name, description) values (?, ?, ?, ?, ");
242                            sb.append("?, ?, ?, ?, ?, ?)");
243    
244                            String sql = sb.toString();
245    
246                            ps = con.prepareStatement(sql);
247    
248                            ps.setString(1, PortalUUIDUtil.generate());
249                            ps.setLong(2, vocabularyId);
250                            ps.setLong(3, groupId);
251                            ps.setLong(4, companyId);
252                            ps.setLong(5, userId);
253                            ps.setString(6, userName);
254                            ps.setTimestamp(7, createDate);
255                            ps.setTimestamp(8, modifiedDate);
256                            ps.setString(9, name);
257                            ps.setString(10, description);
258    
259                            ps.executeUpdate();
260                    }
261                    finally {
262                            DataAccess.cleanUp(con, ps);
263                    }
264            }
265    
266            protected void copyAssociations(
267                            long tagsEntryId, String tableName, String pkName)
268                    throws Exception {
269    
270                    Connection con = null;
271                    PreparedStatement ps = null;
272                    ResultSet rs = null;
273    
274                    try {
275                            con = DataAccess.getConnection();
276    
277                            ps = con.prepareStatement(
278                                    "select * from TagsAssets_TagsEntries where entryId = ?");
279    
280                            ps.setLong(1, tagsEntryId);
281    
282                            rs = ps.executeQuery();
283    
284                            while (rs.next()) {
285                                    long tagsAssetId = rs.getLong("assetId");
286    
287                                    runSQL(
288                                            "insert into " + tableName + " (entryId, " + pkName +
289                                                    ") values (" + tagsAssetId + ", " + tagsEntryId + ")");
290                            }
291                    }
292                    finally {
293                            DataAccess.cleanUp(con, ps, rs);
294                    }
295            }
296    
297            protected void copyEntriesToCategories(long vocabularyId) throws Exception {
298                    Connection con = null;
299                    PreparedStatement ps = null;
300                    ResultSet rs = null;
301    
302                    try {
303                            con = DataAccess.getConnection();
304    
305                            ps = con.prepareStatement(
306                                    "select * from TagsEntry where vocabularyId = ?");
307    
308                            ps.setLong(1, vocabularyId);
309    
310                            rs = ps.executeQuery();
311    
312                            while (rs.next()) {
313                                    long entryId = rs.getLong("entryId");
314                                    long groupId = rs.getLong("groupId");
315                                    long companyId = rs.getLong("companyId");
316                                    long userId = rs.getLong("userId");
317                                    String userName = rs.getString("userName");
318                                    Timestamp createDate = rs.getTimestamp("createDate");
319                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
320                                    long parentCategoryId = rs.getLong("parentEntryId");
321                                    String name = rs.getString("name");
322    
323                                    addCategory(
324                                            entryId, groupId, companyId, userId, userName, createDate,
325                                            modifiedDate, parentCategoryId, name, vocabularyId);
326    
327                                    copyAssociations(
328                                            entryId, "AssetEntries_AssetCategories", "categoryId");
329    
330                                    copyProperties(
331                                            entryId, "AssetCategoryProperty", "categoryPropertyId");
332    
333                                    String resourceName = AssetCategory.class.getName();
334    
335                                    ResourceLocalServiceUtil.addModelResources(
336                                            companyId, groupId, 0, resourceName, null, null, null);
337    
338                                    updateCategoryResource(companyId, entryId);
339                            }
340                    }
341                    finally {
342                            DataAccess.cleanUp(con, ps, rs);
343                    }
344            }
345    
346            protected void copyProperties(
347                            long categoryId, String tableName, String pkName)
348                    throws Exception {
349    
350                    Connection con = null;
351                    PreparedStatement ps = null;
352                    ResultSet rs = null;
353    
354                    try {
355                            con = DataAccess.getConnection();
356    
357                            ps = con.prepareStatement(
358                                    "select * from TagsProperty where entryId = ?");
359    
360                            ps.setLong(1, categoryId);
361    
362                            rs = ps.executeQuery();
363    
364                            while (rs.next()) {
365                                    long propertyId = rs.getLong("propertyId");
366                                    long companyId = rs.getLong("companyId");
367                                    long userId = rs.getLong("userId");
368                                    String userName = rs.getString("userName");
369                                    Timestamp createDate = rs.getTimestamp("createDate");
370                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
371                                    String key = rs.getString("key_");
372                                    String value = rs.getString("value");
373    
374                                    addProperty(
375                                            tableName, pkName, propertyId, companyId, userId, userName,
376                                            createDate, modifiedDate, categoryId, key, value);
377                            }
378                    }
379                    finally {
380                            DataAccess.cleanUp(con, ps, rs);
381                    }
382            }
383    
384            protected void doUpgrade() throws Exception {
385                    updateResourceCodes();
386                    updateAssetEntries();
387                    updateAssetCategories();
388                    updateAssetTags();
389            }
390    
391            protected void updateAssetCategories() throws Exception {
392                    Connection con = null;
393                    PreparedStatement ps = null;
394                    ResultSet rs = null;
395    
396                    try {
397                            con = DataAccess.getConnection();
398    
399                            ps = con.prepareStatement(
400                                    "select * from TagsVocabulary where folksonomy = ?");
401    
402                            ps.setBoolean(1, false);
403    
404                            rs = ps.executeQuery();
405    
406                            while (rs.next()) {
407                                    long vocabularyId = rs.getLong("vocabularyId");
408                                    long groupId = rs.getLong("groupId");
409                                    long companyId = rs.getLong("companyId");
410                                    long userId = rs.getLong("userId");
411                                    String userName = rs.getString("userName");
412                                    Timestamp createDate = rs.getTimestamp("createDate");
413                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
414                                    String name = rs.getString("name");
415                                    String description = rs.getString("description");
416    
417                                    addVocabulary(
418                                            vocabularyId, groupId, companyId, userId, userName,
419                                            createDate, modifiedDate, name, description);
420    
421                                    copyEntriesToCategories(vocabularyId);
422                            }
423                    }
424                    finally {
425                            DataAccess.cleanUp(con, ps, rs);
426                    }
427            }
428    
429            protected void updateAssetEntries() throws Exception {
430                    Connection con = null;
431                    PreparedStatement ps = null;
432                    ResultSet rs = null;
433    
434                    try {
435                            con = DataAccess.getConnection();
436    
437                            ps = con.prepareStatement("select * from TagsAsset");
438    
439                            rs = ps.executeQuery();
440    
441                            while (rs.next()) {
442                                    long assetId = rs.getLong("assetId");
443                                    long groupId = rs.getLong("groupId");
444                                    long companyId = rs.getLong("companyId");
445                                    long userId = rs.getLong("userId");
446                                    String userName = rs.getString("userName");
447                                    Timestamp createDate = rs.getTimestamp("createDate");
448                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
449                                    long classNameId = rs.getLong("classNameId");
450                                    long classPK = rs.getLong("classPK");
451                                    boolean visible = rs.getBoolean("visible");
452                                    Timestamp startDate = rs.getTimestamp("startDate");
453                                    Timestamp endDate = rs.getTimestamp("endDate");
454                                    Timestamp publishDate = rs.getTimestamp("publishDate");
455                                    Timestamp expirationDate = rs.getTimestamp("expirationDate");
456                                    String mimeType = rs.getString("mimeType");
457                                    String title = rs.getString("title");
458                                    String description = rs.getString("description");
459                                    String summary = rs.getString("summary");
460                                    String url = rs.getString("url");
461                                    int height = rs.getInt("height");
462                                    int width = rs.getInt("width");
463                                    double priority = rs.getDouble("priority");
464                                    int viewCount = rs.getInt("viewCount");
465    
466                                    addEntry(
467                                            assetId, groupId, companyId, userId, userName, createDate,
468                                            modifiedDate, classNameId, classPK, visible, startDate,
469                                            endDate, publishDate, expirationDate, mimeType, title,
470                                            description, summary, url, height, width, priority,
471                                            viewCount);
472                            }
473                    }
474                    finally {
475                            DataAccess.cleanUp(con, ps, rs);
476                    }
477            }
478    
479            protected void updateAssetTags() throws Exception {
480                    Connection con = null;
481                    PreparedStatement ps = null;
482                    ResultSet rs = null;
483    
484                    try {
485                            con = DataAccess.getConnection();
486    
487                            ps = con.prepareStatement(
488                                    "select TE.* from TagsEntry TE inner join TagsVocabulary TV " +
489                                            "on TE.vocabularyId = TV.vocabularyId where " +
490                                                    "TV.folksonomy = ?");
491    
492                            ps.setBoolean(1, true);
493    
494                            rs = ps.executeQuery();
495    
496                            while (rs.next()) {
497                                    long entryId = rs.getLong("entryId");
498                                    long groupId = rs.getLong("groupId");
499                                    long companyId = rs.getLong("companyId");
500                                    long userId = rs.getLong("userId");
501                                    String userName = rs.getString("userName");
502                                    Timestamp createDate = rs.getTimestamp("createDate");
503                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
504                                    String name = rs.getString("name");
505    
506                                    addTag(
507                                            entryId, groupId, companyId, userId, userName, createDate,
508                                            modifiedDate, name);
509    
510                                    copyAssociations(entryId, "AssetEntries_AssetTags", "tagId");
511    
512                                    copyProperties(entryId, "AssetTagProperty", "tagPropertyId");
513                            }
514                    }
515                    finally {
516                            DataAccess.cleanUp(con, ps, rs);
517                    }
518            }
519    
520            protected void updateCategoryResource(long companyId, long categoryId)
521                    throws Exception{
522    
523                    String oldName = "com.liferay.tags.model.TagsEntry";
524    
525                    ResourceCode oldResourceCode =
526                            ResourceCodeLocalServiceUtil.getResourceCode(
527                                    companyId, oldName, ResourceConstants.SCOPE_INDIVIDUAL);
528    
529                    long oldCodeId = oldResourceCode.getCodeId();
530    
531                    String newName = AssetCategory.class.getName();
532    
533                    ResourceCode newResourceCode =
534                            ResourceCodeLocalServiceUtil.getResourceCode(
535                                    companyId, newName, ResourceConstants.SCOPE_INDIVIDUAL);
536    
537                    long newCodeId = newResourceCode.getCodeId();
538    
539                    // Algorithm 1-5
540    
541                    runSQL(
542                            "update Resource_ set codeId = '" + newCodeId + "' where " +
543                                    "codeId = '" + oldCodeId + "' and primKey = '" + categoryId +
544                                            "';");
545    
546                    // Algorithm 6
547    
548                    runSQL(
549                            "update ResourcePermission set name = '" + newName + "' where " +
550                                    "companyId = '" + companyId + "' and name = '" + oldName +
551                                            "' and scope = '" + ResourceConstants.SCOPE_INDIVIDUAL +
552                                                    "' and primKey = '" + categoryId + "';");
553            }
554    
555            protected void updateResourceCodes() throws Exception {
556                    updateResourceCodes(
557                            "com.liferay.portlet.tags", "com.liferay.portlet.asset"
558                    );
559    
560                    updateResourceCodes(
561                            "com.liferay.portlet.tags.model.TagsEntry",
562                            AssetTag.class.getName()
563                    );
564    
565                    updateResourceCodes(
566                            "com.liferay.portlet.tags.model.TagsAsset",
567                            AssetEntry.class.getName()
568                    );
569    
570                    updateResourceCodes(
571                            "com.liferay.portlet.tags.model.TagsVocabulary",
572                            AssetVocabulary.class.getName()
573                    );
574            }
575    
576            protected void updateResourceCodes(String oldCodeName, String newCodeName)
577                    throws Exception {
578    
579                    // Algorithm 1-5
580    
581                    runSQL(
582                            "update ResourceCode set name = '" + newCodeName + "' where" +
583                                    " name = '" + oldCodeName + "';");
584    
585                    // Algorithm 6
586    
587                    runSQL(
588                            "update ResourceAction set name = '" + newCodeName + "' where" +
589                                    " name = '" + oldCodeName + "';");
590    
591                    runSQL(
592                            "update ResourcePermission set name = '" + newCodeName + "' where" +
593                                    " name = '" + oldCodeName + "';");
594            }
595    
596    }