1
19
20 package com.liferay.portal.tools.sql;
21
22 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
23 import com.liferay.portal.kernel.util.FileUtil;
24 import com.liferay.portal.kernel.util.StringPool;
25 import com.liferay.portal.kernel.util.StringUtil;
26
27 import java.io.BufferedReader;
28 import java.io.File;
29 import java.io.IOException;
30 import java.io.StringReader;
31
32 import java.sql.CallableStatement;
33 import java.sql.Connection;
34 import java.sql.SQLException;
35
36 import java.util.HashSet;
37 import java.util.Set;
38
39 import javax.naming.NamingException;
40
41
50 public class DB2Util extends DBUtil {
51
52 public static DBUtil getInstance() {
53 return _instance;
54 }
55
56 public String buildSQL(String template) throws IOException {
57 template = convertTimestamp(template);
58 template = replaceTemplate(template, getTemplate());
59
60 template = reword(template);
61 template = removeLongInserts(template);
62 template = removeNull(template);
63 template = StringUtil.replace(template, "\\'", "''");
64
65 return template;
66 }
67
68 public boolean isSupportsAlterColumnType() {
69 return _SUPPORTS_ALTER_COLUMN_TYPE;
70 }
71
72 public void runSQL(String template)
73 throws IOException, NamingException, SQLException {
74
75 if (template.startsWith(ALTER_COLUMN_NAME) ||
76 template.startsWith(ALTER_COLUMN_TYPE)) {
77
78 String sql = buildSQL(template);
79
80 String[] alterSqls = StringUtil.split(sql, StringPool.SEMICOLON);
81
82 for (String alterSql : alterSqls) {
83 if (!alterSql.startsWith("-- ")){
84 runSQL(alterSql);
85 }
86 }
87 }
88 else {
89 super.runSQL(template);
90 }
91 }
92
93 public void runSQL(String[] templates)
94 throws IOException, NamingException, SQLException {
95
96 super.runSQL(templates);
97
98 _reorgTables(templates);
99 }
100
101 protected DB2Util() {
102 super(TYPE_DB2);
103 }
104
105 protected void buildCreateFile(String databaseName, boolean minimal)
106 throws IOException {
107
108 String minimalSuffix = getMinimalSuffix(minimal);
109
110 File file = new File(
111 "../sql/create" + minimalSuffix + "/create" + minimalSuffix +
112 "-db2.sql");
113
114 StringBuilder sb = new StringBuilder();
115
116 sb.append("drop database " + databaseName + ";\n");
117 sb.append("create database " + databaseName + ";\n");
118 sb.append("connect to " + databaseName + ";\n");
119 sb.append(
120 FileUtil.read("../sql/portal" + minimalSuffix + "/portal" +
121 minimalSuffix + "-db2.sql"));
122 sb.append("\n\n");
123 sb.append(FileUtil.read("../sql/indexes/indexes-db2.sql"));
124 sb.append("\n\n");
125 sb.append(FileUtil.read("../sql/sequences/sequences-db2.sql"));
126
127 FileUtil.write(file, sb.toString());
128 }
129
130 protected String getServerName() {
131 return "db2";
132 }
133
134 protected String[] getTemplate() {
135 return _DB2;
136 }
137
138 protected String reword(String data) throws IOException {
139 BufferedReader br = new BufferedReader(new StringReader(data));
140
141 StringBuilder sb = new StringBuilder();
142
143 String line = null;
144
145 while ((line = br.readLine()) != null) {
146 if (line.startsWith(ALTER_COLUMN_NAME)) {
147 String[] template = buildColumnNameTokens(line);
148
149 line = StringUtil.replace(
150 "alter table @table@ add column @new-column@ @type@;\n",
151 REWORD_TEMPLATE, template);
152
153 line = line + StringUtil.replace(
154 "update @table@ set @new-column@ = @old-column@;\n",
155 REWORD_TEMPLATE, template);
156
157 line = line + StringUtil.replace(
158 "alter table @table@ drop column @old-column@",
159 REWORD_TEMPLATE, template);
160 }
161 else if (line.startsWith(ALTER_COLUMN_TYPE)) {
162 line = "-- " + line;
163 }
164
165 sb.append(line);
166 sb.append("\n");
167 }
168
169 br.close();
170
171 return sb.toString();
172 }
173
174 private void _reorgTables(String[] templates) throws SQLException {
175 Set<String> tableNames = new HashSet<String>();
176
177 for (String template : templates) {
178 if (template.startsWith("alter table")) {
179 tableNames.add(template.split(" ")[2]);
180 }
181 }
182
183 if (tableNames.size() == 0) {
184 return;
185 }
186
187 Connection con = null;
188 CallableStatement callStmt = null;
189
190 try {
191 con = DataAccess.getConnection();
192
193 for (String tableName : tableNames) {
194 String sql = "call sysproc.admin_cmd(?)";
195
196 callStmt = con.prepareCall(sql);
197
198 String param = "reorg table " + tableName;
199
200 callStmt.setString(1, param);
201
202 callStmt.execute();
203 }
204 }
205 finally {
206 DataAccess.cleanUp(con, callStmt);
207 }
208 }
209
210 private static String[] _DB2 = {
211 "--", "1", "0",
212 "'1970-01-01-00.00.00.000000'", "current timestamp",
213 " blob(2000)", " smallint", " timestamp",
214 " double", " integer", " bigint",
215 " varchar(500)", " clob", " varchar",
216 " generated always as identity", "commit"
217 };
218
219 private static boolean _SUPPORTS_ALTER_COLUMN_TYPE;
220
221 private static DB2Util _instance = new DB2Util();
222
223 }