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.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    /**
034     * @author Douglas Wong
035     */
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    }