001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
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                                    String columnName = rs.getString("column_name");
071                                    String dataType = rs.getString("data_type");
072                                    int length = rs.getInt("length");
073                                    boolean nullable = rs.getBoolean("is_nullable");
074    
075                                    if (dataType.equals("varchar")) {
076                                            convertVarcharColumn(
077                                                    tableName, columnName, length, nullable);
078                                    }
079                                    else if (dataType.equals("text")) {
080                                            convertTextColumn(tableName, columnName, nullable);
081                                    }
082                            }
083    
084                            for (String addPrimaryKeySQL : _addPrimaryKeySQLs) {
085                                    runSQL(addPrimaryKeySQL);
086                            }
087                    }
088                    catch (Exception e) {
089                            _log.error(e, e);
090                    }
091                    finally {
092                            DataAccess.cleanUp(con, ps, rs);
093                    }
094            }
095    
096            protected void convertTextColumn(
097                            String tableName, String columnName, boolean nullable)
098                    throws Exception {
099    
100                    if (_log.isInfoEnabled()) {
101                            _log.info(
102                                    "Updating " + tableName + "." + columnName + " to use ntext");
103                    }
104    
105                    StringBundler sb = new StringBundler(4);
106    
107                    sb.append("alter table ");
108                    sb.append(tableName);
109                    sb.append(" add temp ntext");
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(")");
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 = DBFactoryUtil.getDB();
163    
164                    String dbType = db.getType();
165    
166                    if (!dbType.equals(DB.TYPE_SQLSERVER)) {
167                            return;
168                    }
169    
170                    convertColumnsToUnicode();
171            }
172    
173            protected void dropNonunicodeTableIndexes() {
174                    Connection con = null;
175                    PreparedStatement ps = null;
176                    ResultSet rs = null;
177    
178                    try {
179                            con = DataAccess.getUpgradeOptimizedConnection();
180    
181                            StringBundler sb = new StringBundler(15);
182    
183                            sb.append("select distinct sysobjects.name as table_name, ");
184                            sb.append("sysindexes.name as index_name FROM sysobjects inner ");
185                            sb.append("join sysindexes on sysobjects.id = sysindexes.id ");
186                            sb.append("inner join syscolumns on sysobjects.id = ");
187                            sb.append("syscolumns.id inner join sysindexkeys on ");
188                            sb.append("((sysobjects.id = sysindexkeys.id) and ");
189                            sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
190                            sb.append("(sysindexes.indid = sysindexkeys.indid)) inner join ");
191                            sb.append("systypes on syscolumns.xtype = systypes.xtype where ");
192                            sb.append("(sysobjects.type = 'U') and (sysobjects.category != ");
193                            sb.append("2) and ");
194                            sb.append(_FILTER_NONUNICODE_DATA_TYPES);
195                            sb.append(" and ");
196                            sb.append(_FILTER_EXCLUDED_TABLES);
197                            sb.append(" order by sysobjects.name, sysindexes.name");
198    
199                            String sql = sb.toString();
200    
201                            ps = con.prepareStatement(sql);
202    
203                            rs = ps.executeQuery();
204    
205                            while (rs.next()) {
206                                    String tableName = rs.getString("table_name");
207                                    String indexName = rs.getString("index_name");
208    
209                                    if (_log.isInfoEnabled()) {
210                                            _log.info("Dropping index " + tableName + "." + indexName);
211                                    }
212    
213                                    String indexNameUpperCase = indexName.toUpperCase();
214    
215                                    if (indexNameUpperCase.startsWith("PK")) {
216                                            String primaryKeyColumnNames = StringUtil.merge(
217                                                    getPrimaryKeyColumnNames(indexName));
218    
219                                            runSQL(
220                                                    "alter table " + tableName + " drop constraint " +
221                                                            indexName);
222    
223                                            _addPrimaryKeySQLs.add(
224                                                    "alter table " + tableName + " add primary key (" +
225                                                            primaryKeyColumnNames + ")");
226                                    }
227                                    else {
228                                            runSQL("drop index " + indexName + " on " + tableName);
229                                    }
230                            }
231                    }
232                    catch (Exception e) {
233                            _log.error(e, e);
234                    }
235                    finally {
236                            DataAccess.cleanUp(con, ps, rs);
237                    }
238            }
239    
240            protected List<String> getPrimaryKeyColumnNames(String indexName) {
241                    List<String> columnNames = new ArrayList<String>();
242    
243                    Connection con = null;
244                    PreparedStatement ps = null;
245                    ResultSet rs = null;
246    
247                    try {
248                            con = DataAccess.getUpgradeOptimizedConnection();
249    
250                            StringBundler sb = new StringBundler(10);
251    
252                            sb.append("select distinct syscolumns.name as column_name from ");
253                            sb.append("sysobjects inner join syscolumns on sysobjects.id = ");
254                            sb.append("syscolumns.id inner join sysindexes on ");
255                            sb.append("sysobjects.id = sysindexes.id inner join sysindexkeys ");
256                            sb.append("on ((sysobjects.id = sysindexkeys.id) and ");
257                            sb.append("(syscolumns.colid = sysindexkeys.colid) and ");
258                            sb.append("(sysindexes.indid = sysindexkeys.indid)) where ");
259                            sb.append("sysindexes.name = '");
260                            sb.append(indexName);
261                            sb.append("'");
262    
263                            String sql = sb.toString();
264    
265                            ps = con.prepareStatement(sql);
266    
267                            rs = ps.executeQuery();
268    
269                            while (rs.next()) {
270                                    String columnName = rs.getString("column_name");
271    
272                                    columnNames.add(columnName);
273                            }
274                    }
275                    catch (Exception e) {
276                            _log.error(e, e);
277                    }
278                    finally {
279                            DataAccess.cleanUp(con, ps, rs);
280                    }
281    
282                    return columnNames;
283            }
284    
285            private static final String _FILTER_EXCLUDED_TABLES =
286                    "(sysobjects.name not like 'Counter') and (sysobjects.name not like " +
287                            "'Cyrus%') and (sysobjects.name not like 'QUARTZ%')";
288    
289            private static final String _FILTER_NONUNICODE_DATA_TYPES =
290                    "((systypes.name = 'varchar') OR (systypes.name = 'text'))";
291    
292            private static Log _log = LogFactoryUtil.getLog(VerifySQLServer.class);
293    
294            private List<String> _addPrimaryKeySQLs = new ArrayList<String>();
295    
296    }