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    
025    import java.sql.Connection;
026    import java.sql.PreparedStatement;
027    import java.sql.ResultSet;
028    
029    /**
030     * @author Igor Beslic
031     * @author Joshua Gok
032     */
033    public class VerifyDB2 extends VerifyProcess {
034    
035            protected void alterVarcharColumns() throws Exception {
036                    Connection con = null;
037                    PreparedStatement ps = null;
038                    ResultSet rs = null;
039    
040                    try {
041                            con = DataAccess.getUpgradeOptimizedConnection();
042    
043                            StringBundler sb = new StringBundler(4);
044    
045                            sb.append("select tbname, name, coltype, length from ");
046                            sb.append("sysibm.syscolumns where tbcreator = (select distinct ");
047                            sb.append("current schema from sysibm.sysschemata) AND coltype = ");
048                            sb.append("'VARCHAR' and length = 500");
049    
050                            ps = con.prepareStatement(sb.toString());
051    
052                            rs = ps.executeQuery();
053    
054                            while (rs.next()) {
055                                    String tableName = rs.getString(1);
056    
057                                    if (!isPortalTableName(tableName)) {
058                                            continue;
059                                    }
060    
061                                    String columnName = rs.getString(2);
062    
063                                    runSQL(
064                                            "alter table " + tableName + " alter column " + columnName +
065                                                    " set data type varchar(600)");
066                            }
067                    }
068                    finally {
069                            DataAccess.cleanUp(con, ps, rs);
070                    }
071            }
072    
073            protected void convertColumnToClob(String tableName, String columnName)
074                    throws Exception {
075    
076                    Connection con = null;
077                    PreparedStatement ps = null;
078                    ResultSet rs = null;
079    
080                    try {
081                            con = DataAccess.getUpgradeOptimizedConnection();
082    
083                            StringBundler sb = new StringBundler(5);
084    
085                            sb.append("select coltype from sysibm.syscolumns where tbname = '");
086                            sb.append(tableName);
087                            sb.append("' AND name = '");
088                            sb.append(columnName);
089                            sb.append(StringPool.APOSTROPHE);
090    
091                            ps = con.prepareStatement(sb.toString());
092    
093                            rs = ps.executeQuery();
094    
095                            if (!rs.next()) {
096                                    if (_log.isWarnEnabled()) {
097                                            sb = new StringBundler(4);
098    
099                                            sb.append("Unable to find column ");
100                                            sb.append(columnName);
101                                            sb.append(" in table ");
102                                            sb.append(tableName);
103    
104                                            _log.warn(sb.toString());
105                                    }
106    
107                                    return;
108                            }
109    
110                            String dataType = rs.getString(1);
111    
112                            if (dataType.equals("CLOB")) {
113                                    return;
114                            }
115    
116                            runSQL("alter table " + tableName + " add temp CLOB");
117    
118                            sb = new StringBundler(4);
119    
120                            sb.append("update ");
121                            sb.append(tableName);
122                            sb.append(" set temp = ");
123                            sb.append(columnName);
124    
125                            runSQL(sb.toString());
126    
127                            sb = new StringBundler(4);
128    
129                            sb.append("alter table ");
130                            sb.append(tableName);
131                            sb.append(" drop column ");
132                            sb.append(columnName);
133    
134                            runSQL(sb.toString());
135    
136                            sb = new StringBundler(4);
137    
138                            sb.append("alter table ");
139                            sb.append(tableName);
140                            sb.append(" rename column temp to ");
141                            sb.append(columnName);
142    
143                            runSQL(sb.toString());
144                    }
145                    finally {
146                            DataAccess.cleanUp(con, ps, rs);
147                    }
148            }
149    
150            @Override
151            protected void doVerify() throws Exception {
152                    DB db = DBFactoryUtil.getDB();
153    
154                    String dbType = db.getType();
155    
156                    if (!dbType.equals(DB.TYPE_DB2)) {
157                            return;
158                    }
159    
160                    alterVarcharColumns();
161    
162                    convertColumnToClob("EXPANDOVALUE", "DATA_");
163            }
164    
165            private static Log _log = LogFactoryUtil.getLog(VerifyDB2.class);
166    
167    }