001    /**
002     * Copyright (c) 2000-2011 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            @Override
096            protected void doUpgrade() throws Exception {
097                    updateTables(
098                            JournalArticle.class.getName(),
099                            JournalArticleImpl.TABLE_NAME, "id_");
100    
101                    updateTables(
102                            WikiPage.class.getName(), WikiPageImpl.TABLE_NAME, "pageId");
103            }
104    
105            protected boolean hasRow(long companyId, long tableId, long classPK)
106                    throws Exception{
107    
108                    Connection con = null;
109                    PreparedStatement ps = null;
110                    ResultSet rs = null;
111    
112                    try {
113                            con = DataAccess.getConnection();
114    
115                            ps = con.prepareStatement(
116                                    "select count(*) from ExpandoRow where companyId = ? and " +
117                                            "tableId = ?  and classPK = ?");
118    
119                            ps.setLong(1, companyId);
120                            ps.setLong(2, tableId);
121                            ps.setLong(3, classPK);
122    
123                            rs = ps.executeQuery();
124    
125                            while (rs.next()) {
126                                    long count = rs.getLong(1);
127    
128                                    if (count > 0) {
129                                            return true;
130                                    }
131                            }
132    
133                            return false;
134                    }
135                    finally {
136                            DataAccess.cleanUp(con, ps, rs);
137                    }
138            }
139    
140            protected boolean hasValue(
141                            long companyId, long tableId, long columnId, long rowId)
142                    throws Exception{
143    
144                    Connection con = null;
145                    PreparedStatement ps = null;
146                    ResultSet rs = null;
147    
148                    try {
149                            con = DataAccess.getConnection();
150    
151                            ps = con.prepareStatement(
152                                    "select count(*) from ExpandoValue where companyId = ? and " +
153                                            "tableId = ? and columnId = ? and rowId_ = ?");
154    
155                            ps.setLong(1, companyId);
156                            ps.setLong(2, tableId);
157                            ps.setLong(3, columnId);
158                            ps.setLong(4, rowId);
159    
160                            rs = ps.executeQuery();
161    
162                            while (rs.next()) {
163                                    long count = rs.getLong(1);
164    
165                                    if (count > 0) {
166                                            return true;
167                                    }
168                            }
169    
170                            return false;
171                    }
172                    finally {
173                            DataAccess.cleanUp(con, ps, rs);
174                    }
175            }
176    
177            protected void updateRow(
178                            long companyId, long classPK, String tableName, long tableId,
179                            String columnName, long rowId)
180                    throws Exception {
181    
182                    Connection con = null;
183                    PreparedStatement ps = null;
184                    ResultSet rs = null;
185    
186                    try {
187                            con = DataAccess.getConnection();
188    
189                            ps = con.prepareStatement(
190                                    "select " + columnName + " from " + tableName + " where " +
191                                            "resourcePrimKey = ?");
192    
193                            ps.setLong(1, classPK);
194    
195                            rs = ps.executeQuery();
196    
197                            boolean delete = false;
198    
199                            while (rs.next()) {
200                                    long newClassPK = rs.getLong(columnName);
201    
202                                    delete = true;
203    
204                                    if (!hasRow(companyId, tableId, newClassPK)) {
205                                            long newRowId = increment();
206    
207                                            addRow(newRowId, companyId, tableId, newClassPK);
208    
209                                            updateValues(classPK, newClassPK, tableId, rowId, newRowId);
210                                    }
211                            }
212    
213                            if (delete) {
214                                    runSQL("delete from ExpandoRow where rowId_ = " + rowId);
215                                    runSQL("delete from ExpandoValue where rowId_ = " + rowId);
216                            }
217                    }
218                    finally {
219                            DataAccess.cleanUp(con, ps, rs);
220                    }
221            }
222    
223            protected void updateRows(
224                            String tableName, long tableId, String columnName)
225                    throws Exception {
226    
227                    Connection con = null;
228                    PreparedStatement ps = null;
229                    ResultSet rs = null;
230    
231                    try {
232                            con = DataAccess.getConnection();
233    
234                            ps = con.prepareStatement(
235                                    "select * from ExpandoRow where tableId = ?");
236    
237                            ps.setLong(1, tableId);
238    
239                            rs = ps.executeQuery();
240    
241                            while (rs.next()) {
242                                    long rowId = rs.getLong("rowId_");
243                                    long companyId = rs.getLong("companyId");
244                                    long classPK = rs.getLong("classPK");
245    
246                                    updateRow(
247                                            companyId, classPK, tableName, tableId, columnName, rowId);
248                            }
249                    }
250                    finally {
251                            DataAccess.cleanUp(con, ps, rs);
252                    }
253            }
254    
255            protected void updateTables(
256                            String className, String tableName, String columnName)
257                    throws Exception {
258    
259                    if (_log.isDebugEnabled()) {
260                            _log.debug("Upgrading " + tableName);
261                    }
262    
263                    long classNameId = PortalUtil.getClassNameId(className);
264    
265                    Connection con = null;
266                    PreparedStatement ps = null;
267                    ResultSet rs = null;
268    
269                    try {
270                            con = DataAccess.getConnection();
271    
272                            ps = con.prepareStatement(
273                                    "select * from ExpandoTable where classNameId = ? and " +
274                                            "name = ?");
275    
276                            ps.setLong(1, classNameId);
277                            ps.setString(2, ExpandoTableConstants.DEFAULT_TABLE_NAME);
278    
279                            rs = ps.executeQuery();
280    
281                            while (rs.next()) {
282                                    long tableId = rs.getLong("tableId");
283    
284                                    updateRows(tableName, tableId, columnName);
285                            }
286                    }
287                    finally {
288                            DataAccess.cleanUp(con, ps, rs);
289                    }
290            }
291    
292            protected void updateValues(
293                            long classPK, long newClassPK, long tableId, long rowId,
294                            long newRowId)
295                    throws Exception {
296    
297                    Connection con = null;
298                    PreparedStatement ps = null;
299                    ResultSet rs = null;
300    
301                    try {
302                            con = DataAccess.getConnection();
303    
304                            ps = con.prepareStatement(
305                                    "select * from ExpandoValue where tableId = ? and rowId_ = ? " +
306                                            "and classPK = ?");
307    
308                            ps.setLong(1, tableId);
309                            ps.setLong(2, rowId);
310                            ps.setLong(3, classPK);
311    
312                            rs = ps.executeQuery();
313    
314                            while (rs.next()) {
315                                    long companyId = rs.getLong("companyId");
316                                    long columnId = rs.getLong("columnId");
317                                    long classNameId = rs.getLong("classNameId");
318                                    String data = rs.getString("data_");
319    
320                                    if (!hasValue(companyId, tableId, columnId, newRowId)) {
321                                            long newValueId = increment();
322    
323                                            addValue(
324                                                    newValueId, companyId, tableId, columnId, newRowId,
325                                                    classNameId, newClassPK, data);
326                                    }
327                            }
328                    }
329                    finally {
330                            DataAccess.cleanUp(con, ps, rs);
331                    }
332            }
333    
334            private static Log _log = LogFactoryUtil.getLog(UpgradeExpando.class);
335    
336    }