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.v5_2_0;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.dao.jdbc.SmartResultSet;
019    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
020    import com.liferay.portal.kernel.util.StringPool;
021    import com.liferay.portlet.asset.NoSuchTagException;
022    
023    import java.sql.Connection;
024    import java.sql.PreparedStatement;
025    import java.sql.ResultSet;
026    import java.sql.Timestamp;
027    
028    import java.util.HashMap;
029    import java.util.Map;
030    
031    /**
032     * @author Jorge Ferrer
033     * @author Brian Wing Shun Chan
034     */
035    public class UpgradeTags extends UpgradeProcess {
036    
037            protected void addEntry(
038                            long entryId, long groupId, long companyId, long userId,
039                            String userName, Timestamp createDate, Timestamp modifiedDate,
040                            long parentEntryId, String name, long vocabularyId)
041                    throws Exception {
042    
043                    Connection con = null;
044                    PreparedStatement ps = null;
045    
046                    try {
047                            con = DataAccess.getConnection();
048    
049                            ps = con.prepareStatement(
050                                    "insert into TagsEntry (entryId, groupId, companyId, userId, " +
051                                            "userName, createDate, modifiedDate, parentEntryId, " +
052                                                    "name, vocabularyId) values (?, ?, ?, ?, ?, ?, ?, ?, " +
053                                                            "?, ?)");
054    
055                            ps.setLong(1, entryId);
056                            ps.setLong(2, groupId);
057                            ps.setLong(3, companyId);
058                            ps.setLong(4, userId);
059                            ps.setString(5, userName);
060                            ps.setTimestamp(6, createDate);
061                            ps.setTimestamp(7, modifiedDate);
062                            ps.setLong(8, parentEntryId);
063                            ps.setString(9, name);
064                            ps.setLong(10, vocabularyId);
065    
066                            ps.executeUpdate();
067                    }
068                    finally {
069                            DataAccess.cleanUp(con, ps);
070                    }
071            }
072    
073            protected void addProperty(
074                            long propertyId, long companyId, long userId, String userName,
075                            Timestamp createDate, Timestamp modifiedDate, long entryId,
076                            String key, String value)
077                    throws Exception {
078    
079                    Connection con = null;
080                    PreparedStatement ps = null;
081    
082                    try {
083                            con = DataAccess.getConnection();
084    
085                            ps = con.prepareStatement(
086                                    "insert into TagsProperty (propertyId, companyId, userId, " +
087                                            "userName, createDate, modifiedDate, entryId, key_, " +
088                                                    "value) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
089    
090                            ps.setLong(1, propertyId);
091                            ps.setLong(2, companyId);
092                            ps.setLong(3, userId);
093                            ps.setString(4, userName);
094                            ps.setTimestamp(5, createDate);
095                            ps.setTimestamp(6, modifiedDate);
096                            ps.setLong(7, entryId);
097                            ps.setString(8, key);
098                            ps.setString(9, value);
099    
100                            ps.executeUpdate();
101                    }
102                    finally {
103                            DataAccess.cleanUp(con, ps);
104                    }
105            }
106    
107            protected long copyEntry(long groupId, long entryId) throws Exception {
108                    String key = groupId + StringPool.UNDERLINE + entryId;
109    
110                    Long newEntryId = _entryIdsMap.get(key);
111    
112                    if (newEntryId != null) {
113                            return newEntryId.longValue();
114                    }
115    
116                    Connection con = null;
117                    PreparedStatement ps = null;
118                    ResultSet rs = null;
119    
120                    try {
121                            con = DataAccess.getConnection();
122    
123                            ps = con.prepareStatement(
124                                    "select * from TagsEntry where entryId = ?",
125                                    ResultSet.TYPE_SCROLL_INSENSITIVE,
126                                    ResultSet.CONCUR_READ_ONLY);
127    
128                            ps.setLong(1, entryId);
129    
130                            rs = ps.executeQuery();
131    
132                            while (rs.next()) {
133                                    long companyId = rs.getLong("companyId");
134                                    long userId = rs.getLong("userId");
135                                    String userName = rs.getString("userName");
136                                    Timestamp createDate = rs.getTimestamp("createDate");
137                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
138                                    long parentEntryId = rs.getLong("parentEntryId");
139                                    String name = rs.getString("name");
140                                    long vocabularyId = rs.getLong("vocabularyId");
141    
142                                    newEntryId = increment();
143    
144                                    addEntry(
145                                            newEntryId, groupId, companyId, userId, userName,
146                                            createDate, modifiedDate, parentEntryId, name,
147                                            vocabularyId);
148    
149                                    copyProperties(entryId, newEntryId);
150    
151                                    _entryIdsMap.put(key, newEntryId);
152    
153                                    return newEntryId;
154                            }
155                    }
156                    finally {
157                            DataAccess.cleanUp(con, ps, rs);
158                    }
159    
160                    throw new NoSuchTagException(
161                            "No AssetTag exists with the primary key " + entryId);
162            }
163    
164            public void copyProperties(long entryId, long newEntryId) throws Exception {
165                    Connection con = null;
166                    PreparedStatement ps = null;
167                    ResultSet rs = null;
168    
169                    try {
170                            con = DataAccess.getConnection();
171    
172                            ps = con.prepareStatement(
173                                    "select * from TagsProperty where entryId = ?",
174                                    ResultSet.TYPE_SCROLL_INSENSITIVE,
175                                    ResultSet.CONCUR_READ_ONLY);
176    
177                            ps.setLong(1, entryId);
178    
179                            rs = ps.executeQuery();
180    
181                            while (rs.next()) {
182                                    long companyId = rs.getLong("companyId");
183                                    long userId = rs.getLong("userId");
184                                    String userName = rs.getString("userName");
185                                    Timestamp createDate = rs.getTimestamp("createDate");
186                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
187                                    String key = rs.getString("key_");
188                                    String value = rs.getString("value");
189    
190                                    long newPropertyId = increment();
191    
192                                    addProperty(
193                                            newPropertyId, companyId, userId, userName, createDate,
194                                            modifiedDate, newEntryId, key, value);
195                            }
196                    }
197                    finally {
198                            DataAccess.cleanUp(con, ps, rs);
199                    }
200            }
201    
202            protected void deleteEntries() throws Exception {
203                    Connection con = null;
204                    PreparedStatement ps = null;
205                    ResultSet rs = null;
206    
207                    try {
208                            con = DataAccess.getConnection();
209    
210                            ps = con.prepareStatement(
211                                    "select entryId from TagsEntry where groupId = 0");
212    
213                            rs = ps.executeQuery();
214    
215                            while (rs.next()) {
216                                    long entryId = rs.getLong("entryId");
217    
218                                    runSQL(
219                                            "delete from TagsAssets_TagsEntries where entryId = " +
220                                                    entryId);
221    
222                                    runSQL("delete from TagsProperty where entryId = " + entryId);
223                            }
224    
225                            runSQL("delete from TagsEntry where groupId = 0");
226                    }
227                    finally {
228                            DataAccess.cleanUp(con, ps, rs);
229                    }
230            }
231    
232            protected void doUpgrade() throws Exception {
233                    updateGroupIds();
234                    updateAssets();
235            }
236    
237            protected void updateAssets() throws Exception {
238                    Connection con = null;
239                    PreparedStatement ps = null;
240                    ResultSet rs = null;
241    
242                    try {
243                            con = DataAccess.getConnection();
244    
245                            ps = con.prepareStatement(
246                                    "select resourcePrimKey from JournalArticle where approved " +
247                                            "= ?");
248    
249                            ps.setBoolean(1, false);
250    
251                            rs = ps.executeQuery();
252    
253                            while (rs.next()) {
254                                    long resourcePrimKey = rs.getLong("resourcePrimKey");
255    
256                                    runSQL(
257                                            "update TagsAsset set visible = FALSE where classPK = " +
258                                                    resourcePrimKey);
259                            }
260                    }
261                    finally {
262                            DataAccess.cleanUp(con, ps, rs);
263                    }
264            }
265    
266            protected void updateGroupIds() throws Exception {
267                    Connection con = null;
268                    PreparedStatement ps = null;
269                    ResultSet rs = null;
270    
271                    try {
272                            con = DataAccess.getConnection();
273    
274                            ps = con.prepareStatement(
275                                    "select TA.assetId, TA.groupId, TA_TE.entryId from " +
276                                            "TagsAssets_TagsEntries TA_TE inner join TagsAsset TA on " +
277                                                    "TA.assetId = TA_TE.assetId",
278                                    ResultSet.TYPE_SCROLL_INSENSITIVE,
279                                    ResultSet.CONCUR_READ_ONLY);
280    
281                            rs = ps.executeQuery();
282    
283                            SmartResultSet srs = new SmartResultSet(rs);
284    
285                            while (srs.next()) {
286                                    long assetId = srs.getLong("TA.assetId");
287                                    long groupId = srs.getLong("TA.groupId");
288                                    long entryId = srs.getLong("TA_TE.entryId");
289    
290                                    long newEntryId = copyEntry(groupId, entryId);
291    
292                                    runSQL(
293                                            "insert into TagsAssets_TagsEntries (assetId, entryId) " +
294                                                    "values (" + assetId + ", " + newEntryId + ")");
295                            }
296                    }
297                    finally {
298                            DataAccess.cleanUp(con, ps, rs);
299                    }
300    
301                    deleteEntries();
302            }
303    
304            private Map<String, Long> _entryIdsMap = new HashMap<String, Long>();
305    
306    }