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_0;
016    
017    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
018    import com.liferay.portal.kernel.util.LoggingTimer;
019    
020    import java.sql.PreparedStatement;
021    import java.sql.ResultSet;
022    
023    /**
024     * @author Brian Wing Shun Chan
025     */
026    public class UpgradeExpando extends UpgradeProcess {
027    
028            @Override
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                    try (PreparedStatement ps = connection.prepareStatement(
038                                    "select * from ExpandoColumn where tableId = ?")) {
039    
040                            ps.setLong(1, wolTableId);
041    
042                            try (ResultSet rs = ps.executeQuery()) {
043                                    long scColumnId = 0;
044                                    long snColumnId = 0;
045    
046                                    while (rs.next()) {
047                                            long wolColumnId = rs.getLong("columnId");
048                                            String name = rs.getString("name");
049    
050                                            long newTableId = 0;
051    
052                                            if (name.equals("aboutMe")) {
053                                                    newTableId = snTableId;
054                                                    snColumnId = wolColumnId;
055                                            }
056                                            else if (name.equals("jiraUserId")) {
057                                                    newTableId = scTableId;
058                                                    scColumnId = wolColumnId;
059                                            }
060    
061                                            runSQL(
062                                                    "update ExpandoColumn set tableId = " + newTableId +
063                                                            " where tableId = " + wolTableId + " and name = '" +
064                                                                    name + "'");
065                                    }
066    
067                                    updateRows(
068                                            scColumnId, scTableId, snColumnId, snTableId, wolTableId);
069                            }
070                    }
071            }
072    
073            protected void updateRows(
074                            long scColumnId, long scTableId, long snColumnId, long snTableId,
075                            long wolTableId)
076                    throws Exception {
077    
078                    try (PreparedStatement ps = connection.prepareStatement(
079                                    "select * from ExpandoRow where tableId = ?")) {
080    
081                            ps.setLong(1, wolTableId);
082    
083                            try (ResultSet rs = ps.executeQuery()) {
084                                    while (rs.next()) {
085                                            long wolRowId = rs.getLong("rowId_");
086                                            long companyId = rs.getLong("companyId");
087                                            long classPK = rs.getLong("classPK");
088    
089                                            long scRowId = increment();
090    
091                                            runSQL(
092                                                    "insert into ExpandoRow (rowId_, companyId, tableId, " +
093                                                            "classPK) values (" + scRowId + ", " + companyId +
094                                                                    ", " + scTableId + ", " + classPK + ")");
095    
096                                            long snRowId = increment();
097    
098                                            runSQL(
099                                                    "insert into ExpandoRow (rowId_, companyId, tableId, " +
100                                                            "classPK) values (" + snRowId + ", " + companyId +
101                                                                    ", " + snTableId + ", " + classPK + ")");
102    
103                                            runSQL(
104                                                    "delete from ExpandoRow where tableId = " + wolTableId);
105    
106                                            updateValues(
107                                                    scColumnId, scRowId, scTableId, snColumnId, snRowId,
108                                                    snTableId, wolRowId, wolTableId);
109                                    }
110                            }
111                    }
112            }
113    
114            protected void updateTables() throws Exception {
115                    try (LoggingTimer loggingTimer = new LoggingTimer();
116                            PreparedStatement ps = connection.prepareStatement(
117                                    "select * from ExpandoTable where name = ?")) {
118    
119                            ps.setString(1, "WOL");
120    
121                            try (ResultSet rs = ps.executeQuery()) {
122                                    while (rs.next()) {
123                                            long wolTableId = rs.getLong("tableId");
124                                            long companyId = rs.getLong("companyId");
125                                            long classNameId = rs.getLong("classNameId");
126    
127                                            long scTableId = increment();
128    
129                                            runSQL(
130                                                    "insert into ExpandoTable (tableId, companyId, " +
131                                                            "classNameId, name) values (" + scTableId + ", " +
132                                                                    companyId + ", " + classNameId + ", 'SC')");
133    
134                                            long snTableId = increment();
135    
136                                            runSQL(
137                                                    "insert into ExpandoTable (tableId, companyId, " +
138                                                            "classNameId, name) values (" + snTableId + ", " +
139                                                                    companyId + ", " + classNameId + ", 'SN')");
140    
141                                            runSQL(
142                                                    "delete from ExpandoTable where tableId = " +
143                                                            wolTableId);
144    
145                                            updateColumns(scTableId, snTableId, wolTableId);
146                                    }
147                            }
148                    }
149            }
150    
151            protected void updateValues(
152                            long scColumnId, long scRowId, long scTableId, long snColumnId,
153                            long snRowId, long snTableId, long wolRowId, long wolTableId)
154                    throws Exception {
155    
156                    try (PreparedStatement ps = connection.prepareStatement(
157                                    "select * from ExpandoValue where tableId = ? and rowId_ =" +
158                                            " ?")) {
159    
160                            ps.setLong(1, wolTableId);
161                            ps.setLong(2, wolRowId);
162    
163                            try (ResultSet rs = ps.executeQuery()) {
164                                    while (rs.next()) {
165                                            long valueId = rs.getLong("valueId");
166                                            long columnId = rs.getLong("columnId");
167    
168                                            long newTableId = 0;
169                                            long newRowId = 0;
170    
171                                            if (columnId == scColumnId) {
172                                                    newRowId = scRowId;
173                                                    newTableId = scTableId;
174                                            }
175                                            else if (columnId == snColumnId) {
176                                                    newRowId = snRowId;
177                                                    newTableId = snTableId;
178                                            }
179    
180                                            runSQL(
181                                                    "update ExpandoValue set tableId = " + newTableId +
182                                                            ", rowId_ = " + newRowId + " where valueId = " +
183                                                                    valueId);
184                                    }
185                            }
186                    }
187            }
188    
189    }