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(
080 tableName, columnName, length, 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, int length, 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 sb.append(length);
144 sb.append(")");
145
146 if (!nullable) {
147 sb.append(" not null");
148 }
149
150 runSQL(sb.toString());
151 }
152
153 @Override
154 protected void doVerify() throws Exception {
155 DB db = DBFactoryUtil.getDB();
156
157 String dbType = db.getType();
158
159 if (!dbType.equals(DB.TYPE_SQLSERVER)) {
160 return;
161 }
162
163 convertColumnsToUnicode();
164 }
165
166 protected void dropNonunicodeTableIndexes() {
167 Connection con = null;
168 PreparedStatement ps = null;
169 ResultSet rs = null;
170
171 try {
172 con = DataAccess.getConnection();
173
174 StringBundler sb = new StringBundler(12);
175
176 sb.append("select distinct sysobjects.name as table_name, ");
177 sb.append("sysindexes.name as index_name FROM sysobjects inner ");
178 sb.append("join sysindexes on sysobjects.id = sysindexes.id ");
179 sb.append("inner join syscolumns on sysobjects.id = ");
180 sb.append("syscolumns.id inner join sysindexkeys on ");
181 sb.append("((sysobjects.id = sysindexkeys.id) and ");
182 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
183 sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
184 sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
185 sb.append("sysobjects.type = 'U' and ");
186 sb.append(_FILTER_NONUNICODE_DATA_TYPES);
187 sb.append(" and ");
188 sb.append(_FILTER_EXCLUDED_TABLES);
189 sb.append(" order by sysobjects.name, sysindexes.name");
190
191 String sql = sb.toString();
192
193 ps = con.prepareStatement(sql);
194
195 rs = ps.executeQuery();
196
197 while (rs.next()) {
198 String tableName = rs.getString("table_name");
199 String indexName = rs.getString("index_name");
200
201 if (_log.isInfoEnabled()) {
202 _log.info("Dropping index " + tableName + "." + indexName);
203 }
204
205 if (indexName.startsWith("PK")) {
206 String primaryKeyColumnNames = StringUtil.merge(
207 getPrimaryKeyColumnNames(indexName));
208
209 runSQL(
210 "alter table " + tableName + " drop constraint " +
211 indexName);
212
213 _addPrimaryKeySQLs.add(
214 "alter table " + tableName + " add primary key (" +
215 primaryKeyColumnNames + ")");
216 }
217 else {
218 runSQL("drop index " + indexName + " on " + tableName);
219 }
220 }
221 }
222 catch (Exception e) {
223 _log.error(e, e);
224 }
225 finally {
226 DataAccess.cleanUp(con, ps, rs);
227 }
228 }
229
230 protected List<String> getPrimaryKeyColumnNames(String indexName) {
231 List<String> columnNames = new ArrayList<String>();
232
233 Connection con = null;
234 PreparedStatement ps = null;
235 ResultSet rs = null;
236
237 try {
238 con = DataAccess.getConnection();
239
240 StringBundler sb = new StringBundler(10);
241
242 sb.append("select distinct syscolumns.name as column_name from ");
243 sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
244 sb.append("syscolumns.id inner join sysindexes on ");
245 sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
246 sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
247 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
248 sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
249 sb.append("sysindexes.name = '");
250 sb.append(indexName);
251 sb.append("'");
252
253 String sql = sb.toString();
254
255 ps = con.prepareStatement(sql);
256
257 rs = ps.executeQuery();
258
259 while (rs.next()) {
260 String columnName = rs.getString("column_name");
261
262 columnNames.add(columnName);
263 }
264 }
265 catch (Exception e) {
266 _log.error(e, e);
267 }
268 finally {
269 DataAccess.cleanUp(con, ps, rs);
270 }
271
272 return columnNames;
273 }
274
275 private static final String _FILTER_EXCLUDED_TABLES =
276 "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
277 "'Cyrus%') and (sysobjects.name not like 'QUARTZ%')";
278
279 private static final String _FILTER_NONUNICODE_DATA_TYPES =
280 "((systypes.name = 'varchar') OR (systypes.name = 'text'))";
281
282 private static Log _log = LogFactoryUtil.getLog(VerifySQLServer.class);
283
284 private List<String> _addPrimaryKeySQLs = new ArrayList<String>();
285
286 }