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