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