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