001
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
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 }