001    /**
002     * Copyright (c) 2000-present 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.v6_0_2;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.log.Log;
019    import com.liferay.portal.kernel.log.LogFactoryUtil;
020    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
021    import com.liferay.portal.util.PortalUtil;
022    import com.liferay.portlet.expando.model.ExpandoTableConstants;
023    
024    import java.sql.Connection;
025    import java.sql.PreparedStatement;
026    import java.sql.ResultSet;
027    
028    /**
029     * @author Jorge Ferrer
030     */
031    public class UpgradeExpando extends UpgradeProcess {
032    
033            protected void addRow(
034                            long rowId, long companyId, long tableId, long classPK)
035                    throws Exception {
036    
037                    Connection con = null;
038                    PreparedStatement ps = null;
039    
040                    try {
041                            con = DataAccess.getUpgradeOptimizedConnection();
042    
043                            ps = con.prepareStatement(
044                                    "insert into ExpandoRow (rowId_, companyId, tableId, " +
045                                            "classPK) values (?, ?, ?, ?)");
046    
047                            ps.setLong(1, rowId);
048                            ps.setLong(2, companyId);
049                            ps.setLong(3, tableId);
050                            ps.setLong(4, classPK);
051    
052                            ps.executeUpdate();
053                    }
054                    finally {
055                            DataAccess.cleanUp(con, ps);
056                    }
057            }
058    
059            protected void addValue(
060                            long valueId, long companyId, long tableId, long columnId,
061                            long rowId, long classNameId, long classPK, String data)
062                    throws Exception {
063    
064                    Connection con = null;
065                    PreparedStatement ps = null;
066    
067                    try {
068                            con = DataAccess.getUpgradeOptimizedConnection();
069    
070                            ps = con.prepareStatement(
071                                    "insert into ExpandoValue (valueId, companyId, tableId, " +
072                                            "columnId, rowId_, classNameId, classPK, data_) values " +
073                                                    "(?, ?, ?, ?, ?, ?, ?, ?)");
074    
075                            ps.setLong(1, valueId);
076                            ps.setLong(2, companyId);
077                            ps.setLong(3, tableId);
078                            ps.setLong(4, columnId);
079                            ps.setLong(5, rowId);
080                            ps.setLong(6, classNameId);
081                            ps.setLong(7, classPK);
082                            ps.setString(8, data);
083    
084                            ps.executeUpdate();
085                    }
086                    finally {
087                            DataAccess.cleanUp(con, ps);
088                    }
089            }
090    
091            @Override
092            protected void doUpgrade() throws Exception {
093                    updateTables(
094                            "com.liferay.portlet.journal.model.JournalArticle",
095                            "JournalArticle", "id_");
096    
097                    updateTables("com.liferay.wiki.model.WikiPage", "WikiPage", "pageId");
098            }
099    
100            protected boolean hasRow(long companyId, long tableId, long classPK)
101                    throws Exception {
102    
103                    Connection con = null;
104                    PreparedStatement ps = null;
105                    ResultSet rs = null;
106    
107                    try {
108                            con = DataAccess.getUpgradeOptimizedConnection();
109    
110                            ps = con.prepareStatement(
111                                    "select count(*) from ExpandoRow where companyId = ? and " +
112                                            "tableId = ? and classPK = ?");
113    
114                            ps.setLong(1, companyId);
115                            ps.setLong(2, tableId);
116                            ps.setLong(3, classPK);
117    
118                            rs = ps.executeQuery();
119    
120                            while (rs.next()) {
121                                    int count = rs.getInt(1);
122    
123                                    if (count > 0) {
124                                            return true;
125                                    }
126                            }
127    
128                            return false;
129                    }
130                    finally {
131                            DataAccess.cleanUp(con, ps, rs);
132                    }
133            }
134    
135            protected boolean hasValue(
136                            long companyId, long tableId, long columnId, long rowId)
137                    throws Exception {
138    
139                    Connection con = null;
140                    PreparedStatement ps = null;
141                    ResultSet rs = null;
142    
143                    try {
144                            con = DataAccess.getUpgradeOptimizedConnection();
145    
146                            ps = con.prepareStatement(
147                                    "select count(*) from ExpandoValue where companyId = ? and " +
148                                            "tableId = ? and columnId = ? and rowId_ = ?");
149    
150                            ps.setLong(1, companyId);
151                            ps.setLong(2, tableId);
152                            ps.setLong(3, columnId);
153                            ps.setLong(4, rowId);
154    
155                            rs = ps.executeQuery();
156    
157                            while (rs.next()) {
158                                    int count = rs.getInt(1);
159    
160                                    if (count > 0) {
161                                            return true;
162                                    }
163                            }
164    
165                            return false;
166                    }
167                    finally {
168                            DataAccess.cleanUp(con, ps, rs);
169                    }
170            }
171    
172            protected void updateRow(
173                            long companyId, long classPK, String tableName, long tableId,
174                            String columnName, long rowId)
175                    throws Exception {
176    
177                    Connection con = null;
178                    PreparedStatement ps = null;
179                    ResultSet rs = null;
180    
181                    try {
182                            con = DataAccess.getUpgradeOptimizedConnection();
183    
184                            ps = con.prepareStatement(
185                                    "select " + columnName + " from " + tableName + " where " +
186                                            "resourcePrimKey = ?");
187    
188                            ps.setLong(1, classPK);
189    
190                            rs = ps.executeQuery();
191    
192                            boolean delete = false;
193    
194                            while (rs.next()) {
195                                    long newClassPK = rs.getLong(columnName);
196    
197                                    delete = true;
198    
199                                    if (!hasRow(companyId, tableId, newClassPK)) {
200                                            long newRowId = increment();
201    
202                                            addRow(newRowId, companyId, tableId, newClassPK);
203    
204                                            updateValues(classPK, newClassPK, tableId, rowId, newRowId);
205                                    }
206                            }
207    
208                            if (delete) {
209                                    runSQL("delete from ExpandoRow where rowId_ = " + rowId);
210                                    runSQL("delete from ExpandoValue where rowId_ = " + rowId);
211                            }
212                    }
213                    finally {
214                            DataAccess.cleanUp(con, ps, rs);
215                    }
216            }
217    
218            protected void updateRows(String tableName, long tableId, String columnName)
219                    throws Exception {
220    
221                    Connection con = null;
222                    PreparedStatement ps = null;
223                    ResultSet rs = null;
224    
225                    try {
226                            con = DataAccess.getUpgradeOptimizedConnection();
227    
228                            ps = con.prepareStatement(
229                                    "select * from ExpandoRow where tableId = ?");
230    
231                            ps.setLong(1, tableId);
232    
233                            rs = ps.executeQuery();
234    
235                            while (rs.next()) {
236                                    long rowId = rs.getLong("rowId_");
237                                    long companyId = rs.getLong("companyId");
238                                    long classPK = rs.getLong("classPK");
239    
240                                    updateRow(
241                                            companyId, classPK, tableName, tableId, columnName, rowId);
242                            }
243                    }
244                    finally {
245                            DataAccess.cleanUp(con, ps, rs);
246                    }
247            }
248    
249            protected void updateTables(
250                            String className, String tableName, String columnName)
251                    throws Exception {
252    
253                    if (_log.isDebugEnabled()) {
254                            _log.debug("Upgrading " + tableName);
255                    }
256    
257                    long classNameId = PortalUtil.getClassNameId(className);
258    
259                    Connection con = null;
260                    PreparedStatement ps = null;
261                    ResultSet rs = null;
262    
263                    try {
264                            con = DataAccess.getUpgradeOptimizedConnection();
265    
266                            ps = con.prepareStatement(
267                                    "select * from ExpandoTable where classNameId = ? and " +
268                                            "name = ?");
269    
270                            ps.setLong(1, classNameId);
271                            ps.setString(2, ExpandoTableConstants.DEFAULT_TABLE_NAME);
272    
273                            rs = ps.executeQuery();
274    
275                            while (rs.next()) {
276                                    long tableId = rs.getLong("tableId");
277    
278                                    updateRows(tableName, tableId, columnName);
279                            }
280                    }
281                    finally {
282                            DataAccess.cleanUp(con, ps, rs);
283                    }
284            }
285    
286            protected void updateValues(
287                            long classPK, long newClassPK, long tableId, long rowId,
288                            long newRowId)
289                    throws Exception {
290    
291                    Connection con = null;
292                    PreparedStatement ps = null;
293                    ResultSet rs = null;
294    
295                    try {
296                            con = DataAccess.getUpgradeOptimizedConnection();
297    
298                            ps = con.prepareStatement(
299                                    "select * from ExpandoValue where tableId = ? and rowId_ = ? " +
300                                            "and classPK = ?");
301    
302                            ps.setLong(1, tableId);
303                            ps.setLong(2, rowId);
304                            ps.setLong(3, classPK);
305    
306                            rs = ps.executeQuery();
307    
308                            while (rs.next()) {
309                                    long companyId = rs.getLong("companyId");
310                                    long columnId = rs.getLong("columnId");
311                                    long classNameId = rs.getLong("classNameId");
312                                    String data = rs.getString("data_");
313    
314                                    if (!hasValue(companyId, tableId, columnId, newRowId)) {
315                                            long newValueId = increment();
316    
317                                            addValue(
318                                                    newValueId, companyId, tableId, columnId, newRowId,
319                                                    classNameId, newClassPK, data);
320                                    }
321                            }
322                    }
323                    finally {
324                            DataAccess.cleanUp(con, ps, rs);
325                    }
326            }
327    
328            private static final Log _log = LogFactoryUtil.getLog(UpgradeExpando.class);
329    
330    }