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