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