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