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.dao.jdbc.DataAccess;
021    import com.liferay.portal.kernel.log.Log;
022    import com.liferay.portal.kernel.log.LogFactoryUtil;
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                    dropNonunicodeTableIndexes();
040    
041                    PreparedStatement ps = null;
042                    ResultSet rs = null;
043    
044                    try {
045                            StringBundler sb = new StringBundler(12);
046    
047                            sb.append("select sysobjects.name as table_name, syscolumns.name ");
048                            sb.append("AS column_name, systypes.name as data_type, ");
049                            sb.append("syscolumns.length, syscolumns.isnullable as ");
050                            sb.append("is_nullable FROM sysobjects inner join syscolumns on ");
051                            sb.append("sysobjects.id = syscolumns.id inner join systypes on ");
052                            sb.append("syscolumns.xtype = systypes.xtype where ");
053                            sb.append("(sysobjects.xtype = 'U') and (sysobjects.category != ");
054                            sb.append("2) and ");
055                            sb.append(_FILTER_NONUNICODE_DATA_TYPES);
056                            sb.append(" and ");
057                            sb.append(_FILTER_EXCLUDED_TABLES);
058                            sb.append(" order by sysobjects.name, syscolumns.colid");
059    
060                            String sql = sb.toString();
061    
062                            ps = connection.prepareStatement(sql);
063    
064                            rs = ps.executeQuery();
065    
066                            while (rs.next()) {
067                                    String tableName = rs.getString("table_name");
068    
069                                    if (!isPortalTableName(tableName)) {
070                                            continue;
071                                    }
072    
073                                    String columnName = rs.getString("column_name");
074                                    String dataType = rs.getString("data_type");
075                                    int length = rs.getInt("length");
076                                    boolean nullable = rs.getBoolean("is_nullable");
077    
078                                    if (dataType.equals("varchar")) {
079                                            convertVarcharColumn(
080                                                    tableName, columnName, length, nullable);
081                                    }
082                                    else if (dataType.equals("ntext") || dataType.equals("text")) {
083                                            convertTextColumn(tableName, columnName, nullable);
084                                    }
085                            }
086    
087                            for (String addPrimaryKeySQL : _addPrimaryKeySQLs) {
088                                    runSQL(addPrimaryKeySQL);
089                            }
090                    }
091                    catch (Exception e) {
092                            _log.error(e, e);
093                    }
094                    finally {
095                            DataAccess.cleanUp(ps, rs);
096                    }
097            }
098    
099            protected void convertTextColumn(
100                            String tableName, String columnName, boolean nullable)
101                    throws Exception {
102    
103                    if (_log.isInfoEnabled()) {
104                            _log.info(
105                                    "Updating " + tableName + "." + columnName +" to use " +
106                                            "nvarchar(max)");
107                    }
108    
109                    StringBundler sb = new StringBundler(4);
110    
111                    sb.append("alter table ");
112                    sb.append(tableName);
113                    sb.append(" add temp nvarchar(max)");
114    
115                    if (!nullable) {
116                            sb.append(" not null");
117                    }
118    
119                    runSQL(sb.toString());
120    
121                    runSQL("update " + tableName + " set temp = " + columnName);
122    
123                    runSQL("alter table " + tableName + " drop column " + columnName);
124    
125                    runSQL(
126                            "exec sp_rename \'" + tableName + ".temp\', \'" + columnName +
127                                    "\', \'column\'");
128            }
129    
130            protected void convertVarcharColumn(
131                            String tableName, String columnName, int length, boolean nullable)
132                    throws Exception {
133    
134                    if (_log.isInfoEnabled()) {
135                            _log.info(
136                                    "Updating " + tableName + "." + columnName +
137                                            " to use nvarchar");
138                    }
139    
140                    StringBundler sb = new StringBundler(8);
141    
142                    sb.append("alter table ");
143                    sb.append(tableName);
144                    sb.append(" alter column ");
145                    sb.append(columnName);
146                    sb.append(" nvarchar(");
147    
148                    if (length == -1) {
149                            sb.append("max");
150                    }
151                    else {
152                            sb.append(length);
153                    }
154    
155                    sb.append(StringPool.CLOSE_PARENTHESIS);
156    
157                    if (!nullable) {
158                            sb.append(" not null");
159                    }
160    
161                    runSQL(sb.toString());
162            }
163    
164            @Override
165            protected void doVerify() throws Exception {
166                    DB db = DBManagerUtil.getDB();
167    
168                    if (db.getDBType() != DBType.SQLSERVER) {
169                            return;
170                    }
171    
172                    convertColumnsToUnicode();
173            }
174    
175            protected void dropNonunicodeTableIndexes() {
176                    PreparedStatement ps = null;
177                    ResultSet rs = null;
178    
179                    try {
180                            StringBundler sb = new StringBundler(15);
181    
182                            sb.append("select distinct sysobjects.name as table_name, ");
183                            sb.append("sysindexes.name as index_name FROM sysobjects inner ");
184                            sb.append("join sysindexes on sysobjects.id = sysindexes.id ");
185                            sb.append("inner join syscolumns on sysobjects.id = ");
186                            sb.append("syscolumns.id inner join sysindexkeys on ");
187                            sb.append("((sysobjects.id = sysindexkeys.id) and ");
188                            sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
189                            sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
190                            sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
191                            sb.append("(sysobjects.type = 'U') and (sysobjects.category != ");
192                            sb.append("2) and ");
193                            sb.append(_FILTER_NONUNICODE_DATA_TYPES);
194                            sb.append(" and ");
195                            sb.append(_FILTER_EXCLUDED_TABLES);
196                            sb.append(" order by sysobjects.name, sysindexes.name");
197    
198                            String sql = sb.toString();
199    
200                            ps = connection.prepareStatement(sql);
201    
202                            rs = ps.executeQuery();
203    
204                            while (rs.next()) {
205                                    String tableName = rs.getString("table_name");
206    
207                                    if (!isPortalTableName(tableName)) {
208                                            continue;
209                                    }
210    
211                                    String indexName = rs.getString("index_name");
212    
213                                    if (_log.isInfoEnabled()) {
214                                            _log.info("Dropping index " + tableName + "." + indexName);
215                                    }
216    
217                                    String indexNameUpperCase = StringUtil.toUpperCase(indexName);
218    
219                                    if (indexNameUpperCase.startsWith("PK")) {
220                                            String primaryKeyColumnNames = StringUtil.merge(
221                                                    getPrimaryKeyColumnNames(indexName));
222    
223                                            runSQL(
224                                                    "alter table " + tableName + " drop constraint " +
225                                                            indexName);
226    
227                                            _addPrimaryKeySQLs.add(
228                                                    "alter table " + tableName + " add primary key (" +
229                                                            primaryKeyColumnNames + ")");
230                                    }
231                                    else {
232                                            runSQL("drop index " + indexName + " on " + tableName);
233                                    }
234                            }
235                    }
236                    catch (Exception e) {
237                            _log.error(e, e);
238                    }
239                    finally {
240                            DataAccess.cleanUp(ps, rs);
241                    }
242            }
243    
244            protected List<String> getPrimaryKeyColumnNames(String indexName) {
245                    List<String> columnNames = new ArrayList<>();
246    
247                    PreparedStatement ps = null;
248                    ResultSet rs = null;
249    
250                    try {
251                            StringBundler sb = new StringBundler(10);
252    
253                            sb.append("select distinct syscolumns.name as column_name from ");
254                            sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
255                            sb.append("syscolumns.id inner join sysindexes on ");
256                            sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
257                            sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
258                            sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
259                            sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
260                            sb.append("sysindexes.name = '");
261                            sb.append(indexName);
262                            sb.append("'");
263    
264                            String sql = sb.toString();
265    
266                            ps = connection.prepareStatement(sql);
267    
268                            rs = ps.executeQuery();
269    
270                            while (rs.next()) {
271                                    String columnName = rs.getString("column_name");
272    
273                                    columnNames.add(columnName);
274                            }
275                    }
276                    catch (Exception e) {
277                            _log.error(e, e);
278                    }
279                    finally {
280                            DataAccess.cleanUp(ps, rs);
281                    }
282    
283                    return columnNames;
284            }
285    
286            private static final String _FILTER_EXCLUDED_TABLES =
287                    "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
288                            "'QUARTZ%')";
289    
290            private static final String _FILTER_NONUNICODE_DATA_TYPES =
291                    "((systypes.name = 'ntext') OR (systypes.name = 'text') OR " +
292                            "(systypes.name = 'varchar'))";
293    
294            private static final Log _log = LogFactoryUtil.getLog(
295                    VerifySQLServer.class);
296    
297            private final List<String> _addPrimaryKeySQLs = new ArrayList<>();
298    
299    }