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.StringBundler;
023 import com.liferay.portal.kernel.util.StringPool;
024 import com.liferay.portal.kernel.util.StringUtil;
025
026 import java.sql.Connection;
027 import java.sql.PreparedStatement;
028 import java.sql.ResultSet;
029
030 import java.util.ArrayList;
031 import java.util.List;
032
033
036 public class VerifySQLServer extends VerifyProcess {
037
038 protected void convertColumnsToUnicode() {
039 dropNonunicodeTableIndexes();
040
041 Connection con = null;
042 PreparedStatement ps = null;
043 ResultSet rs = null;
044
045 try {
046 con = DataAccess.getUpgradeOptimizedConnection();
047
048 StringBundler sb = new StringBundler(12);
049
050 sb.append("select sysobjects.name as table_name, syscolumns.name ");
051 sb.append("AS column_name, systypes.name as data_type, ");
052 sb.append("syscolumns.length, syscolumns.isnullable as ");
053 sb.append("is_nullable FROM sysobjects inner join syscolumns on ");
054 sb.append("sysobjects.id = syscolumns.id inner join systypes on ");
055 sb.append("syscolumns.xtype = systypes.xtype where ");
056 sb.append("(sysobjects.xtype = 'U') and (sysobjects.category != ");
057 sb.append("2) and ");
058 sb.append(_FILTER_NONUNICODE_DATA_TYPES);
059 sb.append(" and ");
060 sb.append(_FILTER_EXCLUDED_TABLES);
061 sb.append(" order by sysobjects.name, syscolumns.colid");
062
063 String sql = sb.toString();
064
065 ps = con.prepareStatement(sql);
066
067 rs = ps.executeQuery();
068
069 while (rs.next()) {
070 String tableName = rs.getString("table_name");
071
072 if (!isPortalTableName(tableName)) {
073 continue;
074 }
075
076 String columnName = rs.getString("column_name");
077 String dataType = rs.getString("data_type");
078 int length = rs.getInt("length");
079 boolean nullable = rs.getBoolean("is_nullable");
080
081 if (dataType.equals("varchar")) {
082 convertVarcharColumn(
083 tableName, columnName, length, nullable);
084 }
085 else if (dataType.equals("ntext") || dataType.equals("text")) {
086 convertTextColumn(tableName, columnName, nullable);
087 }
088 }
089
090 for (String addPrimaryKeySQL : _addPrimaryKeySQLs) {
091 runSQL(addPrimaryKeySQL);
092 }
093 }
094 catch (Exception e) {
095 _log.error(e, e);
096 }
097 finally {
098 DataAccess.cleanUp(con, ps, rs);
099 }
100 }
101
102 protected void convertTextColumn(
103 String tableName, String columnName, boolean nullable)
104 throws Exception {
105
106 if (_log.isInfoEnabled()) {
107 _log.info(
108 "Updating " + tableName + "." + columnName +" to use " +
109 "nvarchar(max)");
110 }
111
112 StringBundler sb = new StringBundler(4);
113
114 sb.append("alter table ");
115 sb.append(tableName);
116 sb.append(" add temp nvarchar(max)");
117
118 if (!nullable) {
119 sb.append(" not null");
120 }
121
122 runSQL(sb.toString());
123
124 runSQL("update " + tableName + " set temp = " + columnName);
125
126 runSQL("alter table " + tableName + " drop column " + columnName);
127
128 runSQL(
129 "exec sp_rename \'" + tableName + ".temp\', \'" + columnName +
130 "\', \'column\'");
131 }
132
133 protected void convertVarcharColumn(
134 String tableName, String columnName, int length, boolean nullable)
135 throws Exception {
136
137 if (_log.isInfoEnabled()) {
138 _log.info(
139 "Updating " + tableName + "." + columnName +
140 " to use nvarchar");
141 }
142
143 StringBundler sb = new StringBundler(8);
144
145 sb.append("alter table ");
146 sb.append(tableName);
147 sb.append(" alter column ");
148 sb.append(columnName);
149 sb.append(" nvarchar(");
150
151 if (length == -1) {
152 sb.append("max");
153 }
154 else {
155 sb.append(length);
156 }
157
158 sb.append(StringPool.CLOSE_PARENTHESIS);
159
160 if (!nullable) {
161 sb.append(" not null");
162 }
163
164 runSQL(sb.toString());
165 }
166
167 @Override
168 protected void doVerify() throws Exception {
169 DB db = DBFactoryUtil.getDB();
170
171 String dbType = db.getType();
172
173 if (!dbType.equals(DB.TYPE_SQLSERVER)) {
174 return;
175 }
176
177 convertColumnsToUnicode();
178 }
179
180 protected void dropNonunicodeTableIndexes() {
181 Connection con = null;
182 PreparedStatement ps = null;
183 ResultSet rs = null;
184
185 try {
186 con = DataAccess.getUpgradeOptimizedConnection();
187
188 StringBundler sb = new StringBundler(15);
189
190 sb.append("select distinct sysobjects.name as table_name, ");
191 sb.append("sysindexes.name as index_name FROM sysobjects inner ");
192 sb.append("join sysindexes on sysobjects.id = sysindexes.id ");
193 sb.append("inner join syscolumns on sysobjects.id = ");
194 sb.append("syscolumns.id inner join sysindexkeys on ");
195 sb.append("((sysobjects.id = sysindexkeys.id) and ");
196 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
197 sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
198 sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
199 sb.append("(sysobjects.type = 'U') and (sysobjects.category != ");
200 sb.append("2) and ");
201 sb.append(_FILTER_NONUNICODE_DATA_TYPES);
202 sb.append(" and ");
203 sb.append(_FILTER_EXCLUDED_TABLES);
204 sb.append(" order by sysobjects.name, sysindexes.name");
205
206 String sql = sb.toString();
207
208 ps = con.prepareStatement(sql);
209
210 rs = ps.executeQuery();
211
212 while (rs.next()) {
213 String tableName = rs.getString("table_name");
214
215 if (!isPortalTableName(tableName)) {
216 continue;
217 }
218
219 String indexName = rs.getString("index_name");
220
221 if (_log.isInfoEnabled()) {
222 _log.info("Dropping index " + tableName + "." + indexName);
223 }
224
225 String indexNameUpperCase = StringUtil.toUpperCase(indexName);
226
227 if (indexNameUpperCase.startsWith("PK")) {
228 String primaryKeyColumnNames = StringUtil.merge(
229 getPrimaryKeyColumnNames(indexName));
230
231 runSQL(
232 "alter table " + tableName + " drop constraint " +
233 indexName);
234
235 _addPrimaryKeySQLs.add(
236 "alter table " + tableName + " add primary key (" +
237 primaryKeyColumnNames + ")");
238 }
239 else {
240 runSQL("drop index " + indexName + " on " + tableName);
241 }
242 }
243 }
244 catch (Exception e) {
245 _log.error(e, e);
246 }
247 finally {
248 DataAccess.cleanUp(con, ps, rs);
249 }
250 }
251
252 protected List<String> getPrimaryKeyColumnNames(String indexName) {
253 List<String> columnNames = new ArrayList<String>();
254
255 Connection con = null;
256 PreparedStatement ps = null;
257 ResultSet rs = null;
258
259 try {
260 con = DataAccess.getUpgradeOptimizedConnection();
261
262 StringBundler sb = new StringBundler(10);
263
264 sb.append("select distinct syscolumns.name as column_name from ");
265 sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
266 sb.append("syscolumns.id inner join sysindexes on ");
267 sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
268 sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
269 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
270 sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
271 sb.append("sysindexes.name = '");
272 sb.append(indexName);
273 sb.append("'");
274
275 String sql = sb.toString();
276
277 ps = con.prepareStatement(sql);
278
279 rs = ps.executeQuery();
280
281 while (rs.next()) {
282 String columnName = rs.getString("column_name");
283
284 columnNames.add(columnName);
285 }
286 }
287 catch (Exception e) {
288 _log.error(e, e);
289 }
290 finally {
291 DataAccess.cleanUp(con, ps, rs);
292 }
293
294 return columnNames;
295 }
296
297 private static final String _FILTER_EXCLUDED_TABLES =
298 "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
299 "'Cyrus%') and (sysobjects.name not like 'QUARTZ%')";
300
301 private static final String _FILTER_NONUNICODE_DATA_TYPES =
302 "((systypes.name = 'varchar') OR (systypes.name = 'text'))";
303
304 private static Log _log = LogFactoryUtil.getLog(VerifySQLServer.class);
305
306 private List<String> _addPrimaryKeySQLs = new ArrayList<String>();
307
308 }