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