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 if (indexName.startsWith("PK")) {
205 String primaryKeyColumnNames = StringUtil.merge(
206 getPrimaryKeyColumnNames(indexName));
207
208 runSQL(
209 "alter table " + tableName + " drop constraint " +
210 indexName);
211
212 _addPrimaryKeySQLs.add(
213 "alter table " + tableName + " add primary key (" +
214 primaryKeyColumnNames + ")");
215 }
216 else {
217 runSQL("drop index " + indexName + " on " + tableName);
218 }
219 }
220 }
221 catch (Exception e) {
222 _log.error(e, e);
223 }
224 finally {
225 DataAccess.cleanUp(con, ps, rs);
226 }
227 }
228
229 protected List<String> getPrimaryKeyColumnNames(String indexName) {
230 List<String> columnNames = new ArrayList<String>();
231
232 Connection con = null;
233 PreparedStatement ps = null;
234 ResultSet rs = null;
235
236 try {
237 con = DataAccess.getConnection();
238
239 StringBundler sb = new StringBundler(10);
240
241 sb.append("select distinct syscolumns.name as column_name from ");
242 sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
243 sb.append("syscolumns.id inner join sysindexes on ");
244 sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
245 sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
246 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
247 sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
248 sb.append("sysindexes.name = '");
249 sb.append(indexName);
250 sb.append("'");
251
252 String sql = sb.toString();
253
254 ps = con.prepareStatement(sql);
255
256 rs = ps.executeQuery();
257
258 while (rs.next()) {
259 String columnName = rs.getString("column_name");
260
261 columnNames.add(columnName);
262 }
263 }
264 catch (Exception e) {
265 _log.error(e, e);
266 }
267 finally {
268 DataAccess.cleanUp(con, ps, rs);
269 }
270
271 return columnNames;
272 }
273
274 private static final String _FILTER_EXCLUDED_TABLES =
275 "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
276 "'Cyrus%') and (sysobjects.name not like 'QUARTZ%')";
277
278 private static final String _FILTER_NONUNICODE_DATA_TYPES =
279 "((systypes.name = 'varchar') OR (systypes.name = 'text'))";
280
281 private static Log _log = LogFactoryUtil.getLog(VerifySQLServer.class);
282
283 private List<String> _addPrimaryKeySQLs = new ArrayList<String>();
284
285 }