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
026 import java.sql.Connection;
027 import java.sql.PreparedStatement;
028 import java.sql.ResultSet;
029 import java.sql.SQLException;
030
031
035 public class VerifyOracle extends VerifyProcess {
036
037 protected void alterVarchar2Columns() throws Exception {
038 int buildNumber = getBuildNumber();
039
040 Connection con = null;
041 PreparedStatement ps = null;
042 ResultSet rs = null;
043
044 try {
045 con = DataAccess.getUpgradeOptimizedConnection();
046
047 ps = con.prepareStatement(
048 "select table_name, column_name, data_length from " +
049 "user_tab_columns where data_type = 'VARCHAR2' and " +
050 "char_used = 'B'");
051
052 rs = ps.executeQuery();
053
054 while (rs.next()) {
055 String tableName = rs.getString(1);
056
057 if (!isPortalTableName(tableName)) {
058 continue;
059 }
060
061 String columnName = rs.getString(2);
062 int dataLength = rs.getInt(3);
063
064 if (isBetweenBuildNumbers(
065 buildNumber, ReleaseInfo.RELEASE_5_2_9_BUILD_NUMBER,
066 ReleaseInfo.RELEASE_6_0_0_BUILD_NUMBER) ||
067 isBetweenBuildNumbers(
068 buildNumber, ReleaseInfo.RELEASE_6_0_5_BUILD_NUMBER,
069 ReleaseInfo.RELEASE_6_1_20_BUILD_NUMBER)) {
070
071
072
073 if (!ArrayUtil.contains(
074 _ORIGINAL_DATA_LENGTH_VALUES, dataLength)) {
075
076 dataLength = dataLength / 4;
077 }
078 }
079
080 try {
081 runSQL(
082 "alter table " + tableName + " modify " + columnName +
083 " varchar2(" + dataLength + " char)");
084 }
085 catch (SQLException sqle) {
086 if (sqle.getErrorCode() == 1441) {
087 if (_log.isWarnEnabled()) {
088 StringBundler sb = new StringBundler(6);
089
090 sb.append("Unable to alter length of column ");
091 sb.append(columnName);
092 sb.append(" for table ");
093 sb.append(tableName);
094 sb.append(" because it contains values that are ");
095 sb.append("larger than the new column length");
096
097 _log.warn(sb.toString());
098 }
099 }
100 else {
101 throw sqle;
102 }
103 }
104 }
105 }
106 finally {
107 DataAccess.cleanUp(con, ps, rs);
108 }
109 }
110
111 protected void convertColumnToClob(String tableName, String columnName)
112 throws Exception {
113
114 Connection con = null;
115 PreparedStatement ps = null;
116 ResultSet rs = null;
117
118 try {
119 con = DataAccess.getUpgradeOptimizedConnection();
120
121 StringBundler sb = new StringBundler(6);
122
123 sb.append("select count(*) from user_tab_columns ");
124 sb.append("where table_name = '");
125 sb.append(tableName.toUpperCase());
126 sb.append("' and column_name = '");
127 sb.append(columnName.toUpperCase());
128 sb.append("' and data_type = 'CLOB'");
129
130 ps = con.prepareStatement(sb.toString());
131
132 rs = ps.executeQuery();
133
134 if (!rs.next()) {
135 return;
136 }
137
138 int count = rs.getInt(1);
139
140 if (count > 0) {
141 return;
142 }
143
144 runSQL("alter table " + tableName + " add temp CLOB");
145 runSQL("update " + tableName + " set temp = " + columnName);
146 runSQL("alter table " + tableName + " drop column " + columnName);
147 runSQL(
148 "alter table " + tableName + " rename column temp to " +
149 columnName);
150 }
151 finally {
152 DataAccess.cleanUp(con, ps, rs);
153 }
154 }
155
156 @Override
157 protected void doVerify() throws Exception {
158 DB db = DBFactoryUtil.getDB();
159
160 String dbType = db.getType();
161
162 if (!dbType.equals(DB.TYPE_ORACLE)) {
163 return;
164 }
165
166 alterVarchar2Columns();
167
168 convertColumnToClob("Layout", "css");
169 convertColumnToClob("LayoutRevision", "css");
170 }
171
172 protected boolean isBetweenBuildNumbers(
173 int buildNumber, int startBuildNumber, int endBuildNumber) {
174
175 if ((buildNumber >= startBuildNumber) &&
176 (buildNumber < endBuildNumber)) {
177
178 return true;
179 }
180
181 return false;
182 }
183
184 private static final int[] _ORIGINAL_DATA_LENGTH_VALUES = {
185 75, 100, 150, 200, 255, 500, 1000, 1024, 2000, 4000
186 };
187
188 private static Log _log = LogFactoryUtil.getLog(VerifyOracle.class);
189
190 }