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