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    
020    import java.sql.Connection;
021    import java.sql.PreparedStatement;
022    import java.sql.ResultSet;
023    
024    /**
025     * @author Brian Wing Shun Chan
026     */
027    public class UpgradeExpando extends UpgradeProcess {
028    
029            protected void doUpgrade() throws Exception {
030                    updateTables();
031            }
032    
033            protected void updateColumns(
034                            long scTableId, long snTableId, long wolTableId)
035                    throws Exception {
036    
037                    Connection con = null;
038                    PreparedStatement ps = null;
039                    ResultSet rs = null;
040    
041                    try {
042                            con = DataAccess.getConnection();
043    
044                            ps = con.prepareStatement(
045                                    "select * from ExpandoColumn where tableId = ?");
046    
047                            ps.setLong(1, wolTableId);
048    
049                            rs = ps.executeQuery();
050    
051                            long scColumnId = 0;
052                            long snColumnId = 0;
053    
054                            while (rs.next()) {
055                                    long wolColumnId = rs.getLong("columnId");
056                                    String name = rs.getString("name");
057    
058                                    long newTableId = 0;
059    
060                                    if (name.equals("aboutMe")) {
061                                            newTableId = snTableId;
062                                            snColumnId = wolColumnId;
063                                    }
064                                    else if (name.equals("jiraUserId")) {
065                                            newTableId = scTableId;
066                                            scColumnId = wolColumnId;
067                                    }
068    
069                                    runSQL(
070                                            "update ExpandoColumn set tableId = " + newTableId +
071                                                    " where tableId = " + wolTableId + " and name = '" +
072                                                            name + "'");
073                            }
074    
075                            updateRows(
076                                    scColumnId, scTableId, snColumnId, snTableId, wolTableId);
077                    }
078                    finally {
079                            DataAccess.cleanUp(con, ps, rs);
080                    }
081            }
082    
083            protected void updateRows(
084                            long scColumnId, long scTableId, long snColumnId, long snTableId,
085                            long wolTableId)
086                    throws Exception {
087    
088                    Connection con = null;
089                    PreparedStatement ps = null;
090                    ResultSet rs = null;
091    
092                    try {
093                            con = DataAccess.getConnection();
094    
095                            ps = con.prepareStatement(
096                                    "select * from ExpandoRow where tableId = ?");
097    
098                            ps.setLong(1, wolTableId);
099    
100                            rs = ps.executeQuery();
101    
102                            while (rs.next()) {
103                                    long wolRowId = rs.getLong("rowId_");
104                                    long companyId = rs.getLong("companyId");
105                                    long classPK = rs.getLong("classPK");
106    
107                                    long scRowId = increment();
108    
109                                    runSQL(
110                                            "insert into ExpandoRow (rowId_, companyId, tableId, " +
111                                                    "classPK) values (" + scRowId + ", " + companyId +
112                                                            ", " + scTableId + ", " + classPK + ")");
113    
114                                    long snRowId = increment();
115    
116                                    runSQL(
117                                            "insert into ExpandoRow (rowId_, companyId, tableId, " +
118                                                    "classPK) values (" + snRowId + ", " + companyId +
119                                                            ", " + snTableId + ", " + classPK + ")");
120    
121                                    runSQL("delete from ExpandoRow where tableId = " + wolTableId);
122    
123                                    updateValues(
124                                            scColumnId, scRowId, scTableId, snColumnId, snRowId,
125                                            snTableId, wolRowId, wolTableId);
126                            }
127                    }
128                    finally {
129                            DataAccess.cleanUp(con, ps, rs);
130                    }
131            }
132    
133            protected void updateTables() throws Exception {
134                    Connection con = null;
135                    PreparedStatement ps = null;
136                    ResultSet rs = null;
137    
138                    try {
139                            con = DataAccess.getConnection();
140    
141                            ps = con.prepareStatement(
142                                    "select * from ExpandoTable where name = ?");
143    
144                            ps.setString(1, "WOL");
145    
146                            rs = ps.executeQuery();
147    
148                            while (rs.next()) {
149                                    long wolTableId = rs.getLong("tableId");
150                                    long companyId = rs.getLong("companyId");
151                                    long classNameId = rs.getLong("classNameId");
152    
153                                    long scTableId = increment();
154    
155                                    runSQL(
156                                            "insert into ExpandoTable (tableId, companyId, " +
157                                                    "classNameId, name) values (" + scTableId + ", " +
158                                                            companyId + ", " + classNameId + ", 'SC')");
159    
160                                    long snTableId = increment();
161    
162                                    runSQL(
163                                            "insert into ExpandoTable (tableId, companyId, " +
164                                                    "classNameId, name) values (" + snTableId + ", " +
165                                                            companyId + ", " + classNameId + ", 'SN')");
166    
167                                    runSQL(
168                                            "delete from ExpandoTable where tableId = " + wolTableId);
169    
170                                    updateColumns(scTableId, snTableId, wolTableId);
171                            }
172                    }
173                    finally {
174                            DataAccess.cleanUp(con, ps, rs);
175                    }
176            }
177    
178            protected void updateValues(
179                            long scColumnId, long scRowId, long scTableId, long snColumnId,
180                            long snRowId, long snTableId, long wolRowId, long wolTableId)
181                    throws Exception {
182    
183                    Connection con = null;
184                    PreparedStatement ps = null;
185                    ResultSet rs = null;
186    
187                    try {
188                            con = DataAccess.getConnection();
189    
190                            ps = con.prepareStatement(
191                                    "select * from ExpandoValue where tableId = ? and rowId_ = ?");
192    
193                            ps.setLong(1, wolTableId);
194                            ps.setLong(2, wolRowId);
195    
196                            rs = ps.executeQuery();
197    
198                            while (rs.next()) {
199                                    long valueId = rs.getLong("valueId");
200                                    long columnId = rs.getLong("columnId");
201    
202                                    long newTableId = 0;
203                                    long newRowId = 0;
204    
205                                    if (columnId == scColumnId) {
206                                            newRowId = scRowId;
207                                            newTableId = scTableId;
208                                    }
209                                    else if (columnId == snColumnId) {
210                                            newRowId = snRowId;
211                                            newTableId = snTableId;
212                                    }
213    
214                                    runSQL(
215                                            "update ExpandoValue set tableId = " + newTableId +
216                                                    ", rowId_ = " + newRowId + " where " + "valueId = " +
217                                                            valueId);
218                            }
219                    }
220                    finally {
221                            DataAccess.cleanUp(con, ps, rs);
222                    }
223            }
224    
225    }