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