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 convertColumnToNvarcharMax(
103 String tableName, String columnName)
104 throws Exception {
105
106 Connection con = null;
107 PreparedStatement ps = null;
108 ResultSet rs = null;
109
110 try {
111 con = DataAccess.getUpgradeOptimizedConnection();
112
113 StringBundler sb = new StringBundler(7);
114
115 sb.append("select count(*) from INFORMATION_SCHEMA.COLUMNS ");
116 sb.append("where table_name = '");
117 sb.append(tableName);
118 sb.append("' and column_name = '");
119 sb.append(columnName);
120 sb.append("' and data_type = 'nvarchar' and ");
121 sb.append("character_maximum_length = '-1'");
122
123 ps = con.prepareStatement(sb.toString());
124
125 rs = ps.executeQuery();
126
127 if (!rs.next()) {
128 return;
129 }
130
131 int count = rs.getInt(1);
132
133 if (count > 0) {
134 return;
135 }
136
137 sb = new StringBundler(5);
138
139 sb.append("alter table ");
140 sb.append(tableName);
141 sb.append(" alter column ");
142 sb.append(columnName);
143 sb.append(" nvarchar(max) null");
144
145 runSQL(sb.toString());
146 }
147 catch (Exception e) {
148 _log.error(e, e);
149 }
150 finally {
151 DataAccess.cleanUp(con, ps, rs);
152 }
153 }
154
155 protected void convertTextColumn(
156 String tableName, String columnName, boolean nullable)
157 throws Exception {
158
159 if (_log.isInfoEnabled()) {
160 _log.info(
161 "Updating " + tableName + "." + columnName +" to use " +
162 "nvarchar(max)");
163 }
164
165 StringBundler sb = new StringBundler(4);
166
167 sb.append("alter table ");
168 sb.append(tableName);
169 sb.append(" add temp nvarchar(max)");
170
171 if (!nullable) {
172 sb.append(" not null");
173 }
174
175 runSQL(sb.toString());
176
177 runSQL("update " + tableName + " set temp = " + columnName);
178
179 runSQL("alter table " + tableName + " drop column " + columnName);
180
181 runSQL(
182 "exec sp_rename \'" + tableName + ".temp\', \'" + columnName +
183 "\', \'column\'");
184 }
185
186 protected void convertVarcharColumn(
187 String tableName, String columnName, int length, boolean nullable)
188 throws Exception {
189
190 if (_log.isInfoEnabled()) {
191 _log.info(
192 "Updating " + tableName + "." + columnName +
193 " to use nvarchar");
194 }
195
196 StringBundler sb = new StringBundler(8);
197
198 sb.append("alter table ");
199 sb.append(tableName);
200 sb.append(" alter column ");
201 sb.append(columnName);
202 sb.append(" nvarchar(");
203
204 if (length == -1) {
205 sb.append("max");
206 }
207 else {
208 sb.append(length);
209 }
210
211 sb.append(StringPool.CLOSE_PARENTHESIS);
212
213 if (!nullable) {
214 sb.append(" not null");
215 }
216
217 runSQL(sb.toString());
218 }
219
220 @Override
221 protected void doVerify() throws Exception {
222 DB db = DBFactoryUtil.getDB();
223
224 String dbType = db.getType();
225
226 if (!dbType.equals(DB.TYPE_SQLSERVER)) {
227 return;
228 }
229
230 convertColumnsToUnicode();
231
232 convertColumnToNvarcharMax("AssetEntry", "description");
233 convertColumnToNvarcharMax("AssetEntry", "summary");
234 convertColumnToNvarcharMax("ExpandoColumn", "defaultData");
235 convertColumnToNvarcharMax("ExpandoValue", "data_");
236 convertColumnToNvarcharMax("JournalArticle", "description");
237 }
238
239 protected void dropNonunicodeTableIndexes() {
240 Connection con = null;
241 PreparedStatement ps = null;
242 ResultSet rs = null;
243
244 try {
245 con = DataAccess.getUpgradeOptimizedConnection();
246
247 StringBundler sb = new StringBundler(15);
248
249 sb.append("select distinct sysobjects.name as table_name, ");
250 sb.append("sysindexes.name as index_name FROM sysobjects inner ");
251 sb.append("join sysindexes on sysobjects.id = sysindexes.id ");
252 sb.append("inner join syscolumns on sysobjects.id = ");
253 sb.append("syscolumns.id inner join sysindexkeys on ");
254 sb.append("((sysobjects.id = sysindexkeys.id) and ");
255 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
256 sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
257 sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
258 sb.append("(sysobjects.type = 'U') and (sysobjects.category != ");
259 sb.append("2) and ");
260 sb.append(_FILTER_NONUNICODE_DATA_TYPES);
261 sb.append(" and ");
262 sb.append(_FILTER_EXCLUDED_TABLES);
263 sb.append(" order by sysobjects.name, sysindexes.name");
264
265 String sql = sb.toString();
266
267 ps = con.prepareStatement(sql);
268
269 rs = ps.executeQuery();
270
271 while (rs.next()) {
272 String tableName = rs.getString("table_name");
273
274 if (!isPortalTableName(tableName)) {
275 continue;
276 }
277
278 String indexName = rs.getString("index_name");
279
280 if (_log.isInfoEnabled()) {
281 _log.info("Dropping index " + tableName + "." + indexName);
282 }
283
284 String indexNameUpperCase = StringUtil.toUpperCase(indexName);
285
286 if (indexNameUpperCase.startsWith("PK")) {
287 String primaryKeyColumnNames = StringUtil.merge(
288 getPrimaryKeyColumnNames(indexName));
289
290 runSQL(
291 "alter table " + tableName + " drop constraint " +
292 indexName);
293
294 _addPrimaryKeySQLs.add(
295 "alter table " + tableName + " add primary key (" +
296 primaryKeyColumnNames + ")");
297 }
298 else {
299 runSQL("drop index " + indexName + " on " + tableName);
300 }
301 }
302 }
303 catch (Exception e) {
304 _log.error(e, e);
305 }
306 finally {
307 DataAccess.cleanUp(con, ps, rs);
308 }
309 }
310
311 protected List<String> getPrimaryKeyColumnNames(String indexName) {
312 List<String> columnNames = new ArrayList<String>();
313
314 Connection con = null;
315 PreparedStatement ps = null;
316 ResultSet rs = null;
317
318 try {
319 con = DataAccess.getUpgradeOptimizedConnection();
320
321 StringBundler sb = new StringBundler(10);
322
323 sb.append("select distinct syscolumns.name as column_name from ");
324 sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
325 sb.append("syscolumns.id inner join sysindexes on ");
326 sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
327 sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
328 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
329 sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
330 sb.append("sysindexes.name = '");
331 sb.append(indexName);
332 sb.append("'");
333
334 String sql = sb.toString();
335
336 ps = con.prepareStatement(sql);
337
338 rs = ps.executeQuery();
339
340 while (rs.next()) {
341 String columnName = rs.getString("column_name");
342
343 columnNames.add(columnName);
344 }
345 }
346 catch (Exception e) {
347 _log.error(e, e);
348 }
349 finally {
350 DataAccess.cleanUp(con, ps, rs);
351 }
352
353 return columnNames;
354 }
355
356 private static final String _FILTER_EXCLUDED_TABLES =
357 "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
358 "'Cyrus%') and (sysobjects.name not like 'QUARTZ%')";
359
360 private static final String _FILTER_NONUNICODE_DATA_TYPES =
361 "((systypes.name = 'ntext') OR (systypes.name = 'text') OR " +
362 "(systypes.name = 'varchar'))";
363
364 private static Log _log = LogFactoryUtil.getLog(VerifySQLServer.class);
365
366 private List<String> _addPrimaryKeySQLs = new ArrayList<String>();
367
368 }