001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
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.StringPool;
024    import com.liferay.portal.kernel.util.StringUtil;
025    
026    import java.sql.PreparedStatement;
027    import java.sql.ResultSet;
028    
029    import java.util.ArrayList;
030    import java.util.List;
031    
032    /**
033     * @author Douglas Wong
034     */
035    public class VerifySQLServer extends VerifyProcess {
036    
037            protected void convertColumnsToUnicode() {
038                    dropNonunicodeTableIndexes();
039    
040                    PreparedStatement ps = null;
041                    ResultSet rs = null;
042    
043                    try {
044                            StringBundler sb = new StringBundler(12);
045    
046                            sb.append("select sysobjects.name as table_name, syscolumns.name ");
047                            sb.append("AS column_name, systypes.name as data_type, ");
048                            sb.append("syscolumns.length, syscolumns.isnullable as ");
049                            sb.append("is_nullable FROM sysobjects inner join syscolumns on ");
050                            sb.append("sysobjects.id = syscolumns.id inner join systypes on ");
051                            sb.append("syscolumns.xtype = systypes.xtype where ");
052                            sb.append("(sysobjects.xtype = 'U') and (sysobjects.category != ");
053                            sb.append("2) and ");
054                            sb.append(_FILTER_NONUNICODE_DATA_TYPES);
055                            sb.append(" and ");
056                            sb.append(_FILTER_EXCLUDED_TABLES);
057                            sb.append(" order by sysobjects.name, syscolumns.colid");
058    
059                            String sql = sb.toString();
060    
061                            ps = connection.prepareStatement(sql);
062    
063                            rs = ps.executeQuery();
064    
065                            while (rs.next()) {
066                                    String tableName = rs.getString("table_name");
067    
068                                    if (!isPortalTableName(tableName)) {
069                                            continue;
070                                    }
071    
072                                    String columnName = rs.getString("column_name");
073                                    String dataType = rs.getString("data_type");
074                                    int length = rs.getInt("length");
075                                    boolean nullable = rs.getBoolean("is_nullable");
076    
077                                    if (dataType.equals("varchar")) {
078                                            convertVarcharColumn(
079                                                    tableName, columnName, length, nullable);
080                                    }
081                                    else if (dataType.equals("ntext") || dataType.equals("text")) {
082                                            convertTextColumn(tableName, columnName, nullable);
083                                    }
084                            }
085    
086                            for (String addPrimaryKeySQL : _addPrimaryKeySQLs) {
087                                    runSQL(addPrimaryKeySQL);
088                            }
089                    }
090                    catch (Exception e) {
091                            _log.error(e, e);
092                    }
093                    finally {
094                            DataAccess.cleanUp(null, ps, rs);
095                    }
096            }
097    
098            protected void convertTextColumn(
099                            String tableName, String columnName, boolean nullable)
100                    throws Exception {
101    
102                    if (_log.isInfoEnabled()) {
103                            _log.info(
104                                    "Updating " + tableName + "." + columnName +" to use " +
105                                            "nvarchar(max)");
106                    }
107    
108                    StringBundler sb = new StringBundler(4);
109    
110                    sb.append("alter table ");
111                    sb.append(tableName);
112                    sb.append(" add temp nvarchar(max)");
113    
114                    if (!nullable) {
115                            sb.append(" not null");
116                    }
117    
118                    runSQL(sb.toString());
119    
120                    runSQL("update " + tableName + " set temp = " + columnName);
121    
122                    runSQL("alter table " + tableName + " drop column " + columnName);
123    
124                    runSQL(
125                            "exec sp_rename \'" + tableName + ".temp\', \'" + columnName +
126                                    "\', \'column\'");
127            }
128    
129            protected void convertVarcharColumn(
130                            String tableName, String columnName, int length, boolean nullable)
131                    throws Exception {
132    
133                    if (_log.isInfoEnabled()) {
134                            _log.info(
135                                    "Updating " + tableName + "." + columnName +
136                                            " to use nvarchar");
137                    }
138    
139                    StringBundler sb = new StringBundler(8);
140    
141                    sb.append("alter table ");
142                    sb.append(tableName);
143                    sb.append(" alter column ");
144                    sb.append(columnName);
145                    sb.append(" nvarchar(");
146    
147                    if (length == -1) {
148                            sb.append("max");
149                    }
150                    else {
151                            sb.append(length);
152                    }
153    
154                    sb.append(StringPool.CLOSE_PARENTHESIS);
155    
156                    if (!nullable) {
157                            sb.append(" not null");
158                    }
159    
160                    runSQL(sb.toString());
161            }
162    
163            @Override
164            protected void doVerify() throws Exception {
165                    DB db = DBFactoryUtil.getDB();
166    
167                    String dbType = db.getType();
168    
169                    if (!dbType.equals(DB.TYPE_SQLSERVER)) {
170                            return;
171                    }
172    
173                    convertColumnsToUnicode();
174            }
175    
176            protected void dropNonunicodeTableIndexes() {
177                    PreparedStatement ps = null;
178                    ResultSet rs = null;
179    
180                    try {
181                            StringBundler sb = new StringBundler(15);
182    
183                            sb.append("select distinct sysobjects.name as table_name, ");
184                            sb.append("sysindexes.name as index_name FROM sysobjects inner ");
185                            sb.append("join sysindexes on sysobjects.id = sysindexes.id ");
186                            sb.append("inner join syscolumns on sysobjects.id = ");
187                            sb.append("syscolumns.id inner join sysindexkeys on ");
188                            sb.append("((sysobjects.id = sysindexkeys.id) and ");
189                            sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
190                            sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
191                            sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
192                            sb.append("(sysobjects.type = 'U') and (sysobjects.category != ");
193                            sb.append("2) and ");
194                            sb.append(_FILTER_NONUNICODE_DATA_TYPES);
195                            sb.append(" and ");
196                            sb.append(_FILTER_EXCLUDED_TABLES);
197                            sb.append(" order by sysobjects.name, sysindexes.name");
198    
199                            String sql = sb.toString();
200    
201                            ps = connection.prepareStatement(sql);
202    
203                            rs = ps.executeQuery();
204    
205                            while (rs.next()) {
206                                    String tableName = rs.getString("table_name");
207    
208                                    if (!isPortalTableName(tableName)) {
209                                            continue;
210                                    }
211    
212                                    String indexName = rs.getString("index_name");
213    
214                                    if (_log.isInfoEnabled()) {
215                                            _log.info("Dropping index " + tableName + "." + indexName);
216                                    }
217    
218                                    String indexNameUpperCase = StringUtil.toUpperCase(indexName);
219    
220                                    if (indexNameUpperCase.startsWith("PK")) {
221                                            String primaryKeyColumnNames = StringUtil.merge(
222                                                    getPrimaryKeyColumnNames(indexName));
223    
224                                            runSQL(
225                                                    "alter table " + tableName + " drop constraint " +
226                                                            indexName);
227    
228                                            _addPrimaryKeySQLs.add(
229                                                    "alter table " + tableName + " add primary key (" +
230                                                            primaryKeyColumnNames + ")");
231                                    }
232                                    else {
233                                            runSQL("drop index " + indexName + " on " + tableName);
234                                    }
235                            }
236                    }
237                    catch (Exception e) {
238                            _log.error(e, e);
239                    }
240                    finally {
241                            DataAccess.cleanUp(null, ps, rs);
242                    }
243            }
244    
245            protected List<String> getPrimaryKeyColumnNames(String indexName) {
246                    List<String> columnNames = new ArrayList<>();
247    
248                    PreparedStatement ps = null;
249                    ResultSet rs = null;
250    
251                    try {
252                            StringBundler sb = new StringBundler(10);
253    
254                            sb.append("select distinct syscolumns.name as column_name from ");
255                            sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
256                            sb.append("syscolumns.id inner join sysindexes on ");
257                            sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
258                            sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
259                            sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
260                            sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
261                            sb.append("sysindexes.name = '");
262                            sb.append(indexName);
263                            sb.append("'");
264    
265                            String sql = sb.toString();
266    
267                            ps = connection.prepareStatement(sql);
268    
269                            rs = ps.executeQuery();
270    
271                            while (rs.next()) {
272                                    String columnName = rs.getString("column_name");
273    
274                                    columnNames.add(columnName);
275                            }
276                    }
277                    catch (Exception e) {
278                            _log.error(e, e);
279                    }
280                    finally {
281                            DataAccess.cleanUp(null, ps, rs);
282                    }
283    
284                    return columnNames;
285            }
286    
287            private static final String _FILTER_EXCLUDED_TABLES =
288                    "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
289                            "'QUARTZ%')";
290    
291            private static final String _FILTER_NONUNICODE_DATA_TYPES =
292                    "((systypes.name = 'ntext') OR (systypes.name = 'text') OR " +
293                            "(systypes.name = 'varchar'))";
294    
295            private static final Log _log = LogFactoryUtil.getLog(
296                    VerifySQLServer.class);
297    
298            private final List<String> _addPrimaryKeySQLs = new ArrayList<>();
299    
300    }