001
014
015 package com.liferay.portal.verify;
016
017 import com.liferay.portal.kernel.dao.db.DB;
018 import com.liferay.portal.kernel.dao.db.DBFactoryUtil;
019 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
020 import com.liferay.portal.kernel.log.Log;
021 import com.liferay.portal.kernel.log.LogFactoryUtil;
022 import com.liferay.portal.kernel.util.ArrayUtil;
023 import com.liferay.portal.kernel.util.ReleaseInfo;
024 import com.liferay.portal.kernel.util.StringBundler;
025 import com.liferay.portal.kernel.util.StringPool;
026 import com.liferay.portal.kernel.util.StringUtil;
027
028 import java.sql.Connection;
029 import java.sql.PreparedStatement;
030 import java.sql.ResultSet;
031 import java.sql.SQLException;
032
033
037 public class VerifyOracle extends VerifyProcess {
038
039 protected void alterVarchar2Columns() throws Exception {
040 int buildNumber = getBuildNumber();
041
042 Connection con = null;
043 PreparedStatement ps = null;
044 ResultSet rs = null;
045
046 try {
047 con = DataAccess.getUpgradeOptimizedConnection();
048
049 ps = con.prepareStatement(
050 "select table_name, column_name, data_length from " +
051 "user_tab_columns where data_type = 'VARCHAR2' and " +
052 "char_used = 'B'");
053
054 rs = ps.executeQuery();
055
056 while (rs.next()) {
057 String tableName = rs.getString(1);
058
059 if (!isPortalTableName(tableName)) {
060 continue;
061 }
062
063 String columnName = rs.getString(2);
064 int dataLength = rs.getInt(3);
065
066 if (isBetweenBuildNumbers(
067 buildNumber, ReleaseInfo.RELEASE_5_2_9_BUILD_NUMBER,
068 ReleaseInfo.RELEASE_6_0_0_BUILD_NUMBER) ||
069 isBetweenBuildNumbers(
070 buildNumber, ReleaseInfo.RELEASE_6_0_5_BUILD_NUMBER,
071 ReleaseInfo.RELEASE_6_1_20_BUILD_NUMBER)) {
072
073
074
075 if (!ArrayUtil.contains(
076 _ORIGINAL_DATA_LENGTH_VALUES, dataLength)) {
077
078 dataLength = dataLength / 4;
079 }
080 }
081
082 try {
083 runSQL(
084 "alter table " + tableName + " modify " + columnName +
085 " varchar2(" + dataLength + " char)");
086 }
087 catch (SQLException sqle) {
088 if (sqle.getErrorCode() == 1441) {
089 if (_log.isWarnEnabled()) {
090 StringBundler sb = new StringBundler(6);
091
092 sb.append("Unable to alter length of column ");
093 sb.append(columnName);
094 sb.append(" for table ");
095 sb.append(tableName);
096 sb.append(" because it contains values that are ");
097 sb.append("larger than the new column length");
098
099 _log.warn(sb.toString());
100 }
101 }
102 else {
103 throw sqle;
104 }
105 }
106 }
107 }
108 finally {
109 DataAccess.cleanUp(con, ps, rs);
110 }
111 }
112
113 protected void convertColumnToClob(String tableName, String columnName)
114 throws Exception {
115
116 Connection con = null;
117 PreparedStatement ps = null;
118 ResultSet rs = null;
119
120 try {
121 con = DataAccess.getUpgradeOptimizedConnection();
122
123 StringBundler sb = new StringBundler(6);
124
125 sb.append("select data_type from user_tab_columns where ");
126 sb.append("table_name = '");
127 sb.append(StringUtil.toUpperCase(tableName));
128 sb.append("' and column_name = '");
129 sb.append(StringUtil.toUpperCase(columnName));
130 sb.append(StringPool.APOSTROPHE);
131
132 ps = con.prepareStatement(sb.toString());
133
134 rs = ps.executeQuery();
135
136 if (!rs.next()) {
137 if (_log.isWarnEnabled()) {
138 sb = new StringBundler(5);
139
140 sb.append("Column ");
141 sb.append(columnName);
142 sb.append(" in table ");
143 sb.append(tableName);
144 sb.append(" could not be found.");
145
146 _log.warn(sb.toString());
147 }
148
149 return;
150 }
151
152 String dataType = rs.getString(1);
153
154 if (dataType.equals("CLOB")) {
155 return;
156 }
157
158 runSQL("alter table " + tableName + " add temp CLOB");
159
160 sb = new StringBundler(4);
161
162 sb.append("update ");
163 sb.append(tableName);
164 sb.append(" set temp = ");
165 sb.append(columnName);
166
167 runSQL(sb.toString());
168
169 sb = new StringBundler(4);
170
171 sb.append("alter table ");
172 sb.append(tableName);
173 sb.append(" drop column ");
174 sb.append(columnName);
175
176 runSQL(sb.toString());
177
178 sb = new StringBundler(4);
179
180 sb.append("alter table ");
181 sb.append(tableName);
182 sb.append(" rename column temp to ");
183 sb.append(columnName);
184
185 runSQL(sb.toString());
186 }
187 finally {
188 DataAccess.cleanUp(con, ps, rs);
189 }
190 }
191
192 @Override
193 protected void doVerify() throws Exception {
194 DB db = DBFactoryUtil.getDB();
195
196 String dbType = db.getType();
197
198 if (!dbType.equals(DB.TYPE_ORACLE)) {
199 return;
200 }
201
202 alterVarchar2Columns();
203
204 convertColumnToClob("AssetEntry", "description");
205 convertColumnToClob("AssetEntry", "summary");
206 convertColumnToClob("ExpandoColumn", "defaultData");
207 convertColumnToClob("ExpandoValue", "data_");
208 convertColumnToClob("JournalArticle", "description");
209 convertColumnToClob("ShoppingCart", "itemIds");
210 convertColumnToClob("ShoppingOrder", "comments");
211 }
212
213 protected boolean isBetweenBuildNumbers(
214 int buildNumber, int startBuildNumber, int endBuildNumber) {
215
216 if ((buildNumber >= startBuildNumber) &&
217 (buildNumber < endBuildNumber)) {
218
219 return true;
220 }
221
222 return false;
223 }
224
225 private static final int[] _ORIGINAL_DATA_LENGTH_VALUES = {
226 75, 100, 150, 200, 255, 500, 1000, 1024, 2000, 4000
227 };
228
229 private static Log _log = LogFactoryUtil.getLog(VerifyOracle.class);
230
231 }