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