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