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.DBManagerUtil;
019 import com.liferay.portal.kernel.dao.db.DBType;
020 import com.liferay.portal.kernel.log.Log;
021 import com.liferay.portal.kernel.log.LogFactoryUtil;
022 import com.liferay.portal.kernel.util.LoggingTimer;
023 import com.liferay.portal.kernel.util.StringBundler;
024 import com.liferay.portal.kernel.util.StringPool;
025 import com.liferay.portal.kernel.util.StringUtil;
026
027 import java.sql.PreparedStatement;
028 import java.sql.ResultSet;
029
030 import java.util.ArrayList;
031 import java.util.List;
032
033
036 public class VerifySQLServer extends VerifyProcess {
037
038 protected void convertColumnsToUnicode() {
039 try (LoggingTimer loggingTimer = new LoggingTimer()) {
040 dropNonunicodeTableIndexes();
041
042 StringBundler sb = new StringBundler(12);
043
044 sb.append("select sysobjects.name as table_name, syscolumns.name ");
045 sb.append("AS column_name, systypes.name as data_type, ");
046 sb.append("syscolumns.length, syscolumns.isnullable as ");
047 sb.append("is_nullable FROM sysobjects inner join syscolumns on ");
048 sb.append("sysobjects.id = syscolumns.id inner join systypes on ");
049 sb.append("syscolumns.xtype = systypes.xtype where ");
050 sb.append("(sysobjects.xtype = 'U') and (sysobjects.category != ");
051 sb.append("2) and ");
052 sb.append(_FILTER_NONUNICODE_DATA_TYPES);
053 sb.append(" and ");
054 sb.append(_FILTER_EXCLUDED_TABLES);
055 sb.append(" order by sysobjects.name, syscolumns.colid");
056
057 String sql = sb.toString();
058
059 try (PreparedStatement ps = connection.prepareStatement(sql);
060 ResultSet rs = ps.executeQuery()) {
061
062 while (rs.next()) {
063 String tableName = rs.getString("table_name");
064
065 if (!isPortalTableName(tableName)) {
066 continue;
067 }
068
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("ntext") ||
079 dataType.equals("text")) {
080
081 convertTextColumn(tableName, columnName, nullable);
082 }
083 }
084
085 for (String addPrimaryKeySQL : _addPrimaryKeySQLs) {
086 runSQL(addPrimaryKeySQL);
087 }
088 }
089 catch (Exception e) {
090 _log.error(e, e);
091 }
092 }
093 }
094
095 protected void convertTextColumn(
096 String tableName, String columnName, boolean nullable)
097 throws Exception {
098
099 if (_log.isInfoEnabled()) {
100 _log.info(
101 "Updating " + tableName + "." + columnName +" to use " +
102 "nvarchar(max)");
103 }
104
105 StringBundler sb = new StringBundler(4);
106
107 sb.append("alter table ");
108 sb.append(tableName);
109 sb.append(" add temp nvarchar(max)");
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(StringPool.CLOSE_PARENTHESIS);
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 = DBManagerUtil.getDB();
163
164 if (db.getDBType() != DBType.SQLSERVER) {
165 return;
166 }
167
168 convertColumnsToUnicode();
169 }
170
171 protected void dropNonunicodeTableIndexes() {
172 StringBundler sb = new StringBundler(14);
173
174 sb.append("select distinct sysobjects.name as table_name, ");
175 sb.append("sysindexes.name as index_name FROM sysobjects inner join ");
176 sb.append("sysindexes on sysobjects.id = sysindexes.id inner join ");
177 sb.append("syscolumns on sysobjects.id = syscolumns.id inner join ");
178 sb.append("sysindexkeys on ((sysobjects.id = sysindexkeys.id) and ");
179 sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
180 sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
181 sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
182 sb.append("(sysobjects.type = 'U') and (sysobjects.category != 2) ");
183 sb.append("and ");
184 sb.append(_FILTER_NONUNICODE_DATA_TYPES);
185 sb.append(" and ");
186 sb.append(_FILTER_EXCLUDED_TABLES);
187 sb.append(" order by sysobjects.name, sysindexes.name");
188
189 String sql = sb.toString();
190
191 try (PreparedStatement ps = connection.prepareStatement(sql);
192 ResultSet rs = ps.executeQuery()) {
193
194 while (rs.next()) {
195 String tableName = rs.getString("table_name");
196
197 if (!isPortalTableName(tableName)) {
198 continue;
199 }
200
201 String indexName = rs.getString("index_name");
202
203 if (_log.isInfoEnabled()) {
204 _log.info("Dropping index " + tableName + "." + indexName);
205 }
206
207 String indexNameUpperCase = StringUtil.toUpperCase(indexName);
208
209 if (indexNameUpperCase.startsWith("PK")) {
210 String primaryKeyColumnNames = StringUtil.merge(
211 getPrimaryKeyColumnNames(indexName));
212
213 runSQL(
214 "alter table " + tableName + " drop constraint " +
215 indexName);
216
217 _addPrimaryKeySQLs.add(
218 "alter table " + tableName + " add primary key (" +
219 primaryKeyColumnNames + ")");
220 }
221 else {
222 runSQL("drop index " + indexName + " on " + tableName);
223 }
224 }
225 }
226 catch (Exception e) {
227 _log.error(e, e);
228 }
229 }
230
231 protected List<String> getPrimaryKeyColumnNames(String indexName) {
232 List<String> columnNames = new ArrayList<>();
233
234 StringBundler sb = new StringBundler(9);
235
236 sb.append("select distinct syscolumns.name as column_name from ");
237 sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
238 sb.append("syscolumns.id inner join sysindexes on sysobjects.id = ");
239 sb.append("sysindexes.id inner join sysindexkeys on ((sysobjects.id ");
240 sb.append("= sysindexkeys.id) and (syscolumns.colid = ");
241 sb.append("sysindexkeys.colid) and (sysindexes.indid = ");
242 sb.append("sysindexkeys.indid)) where sysindexes.name = '");
243 sb.append(indexName);
244 sb.append("'");
245
246 String sql = sb.toString();
247
248 try (PreparedStatement ps = connection.prepareStatement(sql);
249 ResultSet rs = ps.executeQuery()) {
250
251 while (rs.next()) {
252 String columnName = rs.getString("column_name");
253
254 columnNames.add(columnName);
255 }
256 }
257 catch (Exception e) {
258 _log.error(e, e);
259 }
260
261 return columnNames;
262 }
263
264 private static final String _FILTER_EXCLUDED_TABLES =
265 "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
266 "'QUARTZ%')";
267
268 private static final String _FILTER_NONUNICODE_DATA_TYPES =
269 "((systypes.name = 'ntext') OR (systypes.name = 'text') OR " +
270 "(systypes.name = 'varchar'))";
271
272 private static final Log _log = LogFactoryUtil.getLog(
273 VerifySQLServer.class);
274
275 private final List<String> _addPrimaryKeySQLs = new ArrayList<>();
276
277 }