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