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