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