|
1
46
47 package org.tigris.scarab.migration.b15b16;
48
49 import java.sql.*;
50 import java.util.*;
51
52 import org.apache.tools.ant.BuildException;
53 import org.tigris.scarab.migration.JDBCTask;
54
55
67 public class DB_1_MoveIssueCreateInfo extends JDBCTask
68 {
69 private static final String  CREATE_ISSUE__PK = "1";
70 private static final String MOVE_ISSUE__PK = "3";
71
72 public DB_1_MoveIssueCreateInfo()
73 {
74 }
75
76 public void execute()
77 throws BuildException
78 {
79 boolean proceed = false;
80 try
81 {
82 Connection conn = null;
84 Statement stmt = null;
85 try
86 {
87 setAutocommit(true);
88 conn = getConnection();
89 String sql = "SELECT CREATED_TRANS_ID FROM SCARAB_ISSUE";
90 stmt = conn.createStatement();
91 try
92 {
93 stmt.executeQuery(sql);
94 }
95 catch (SQLException e)
96 {
97 proceed = true;
98 }
99 }
100 finally
101 {
102 close(stmt, conn);
103 }
104
105 if (proceed)
106 {
107 upgradeScarabIssue();
108 }
109 }
110 catch (Exception e)
111 {
112 throw new BuildException(e);
113 }
114
115 addNewIndices();
116 }
117
118 private void addNewIndices()
119 {
120 Connection conn = null;
121 Statement stmt = null;
122 try
123 {
124 setAutocommit(true);
125 conn = getConnection();
126 String sql = "CREATE INDEX IX_ATTACHMENT on SCARAB_ACTIVITY (ATTACHMENT_ID)";
127 try
128 {
129 stmt = conn.createStatement();
130 stmt.execute(sql);
131 }
132 catch (SQLException e)
133 {
134 System.out.println("index SCARAB_ACTIVITY.IX_ATTACHMENT was not created. verify that it already exists.");
135 }
136 close(stmt, null);
137
138 sql = "CREATE INDEX IX_ISSUE_ATTACHTYPE on SCARAB_ATTACHMENT (ISSUE_ID, ATTACHMENT_TYPE_ID)";
139 try
140 {
141 stmt = conn.createStatement();
142 stmt.execute(sql);
143 }
144 catch (SQLException e)
145 {
146 System.out.println("index SCARAB_ATTACHMENT.IX_ISSUE_ATTACHTYPE was not created. verify that it already exists.");
147 }
148 close(stmt, null);
149
150 sql = "CREATE INDEX IX_DEPEND on SCARAB_ACTIVITY (DEPEND_ID)";
151 try
152 {
153 stmt = conn.createStatement();
154 stmt.execute(sql);
155 }
156 catch (SQLException e)
157 {
158 System.out.println("index SCARAB_ACTIVITY.IX_DEPEND was not created. verify that it already exists.");
159 }
160 }
161 finally
162 {
163 close(stmt, conn);
164 }
165 }
166
167
168 private static final String MYSQL = "mysql";
169 private static final String POSTGRESQL = "postgres";
170 private static final String ORACLE = "oracle";
171 private static final String [] supportedDBs =
172 {MYSQL, POSTGRESQL, ORACLE};
173 private Map intTypes = new HashMap(3);
174 private Map longTypes = new HashMap(3);
175 private Map dateTypes = new HashMap(3);
176
177 {
178 intTypes.put(MYSQL, "INTEGER");
179 intTypes.put(POSTGRESQL, "integer");
180 intTypes.put(ORACLE, "NUMBER");
181 longTypes.put(MYSQL, "BIGINT");
182 longTypes.put(POSTGRESQL, "int8");
183 longTypes.put(ORACLE, "NUMBER (20, 0)");
184 dateTypes.put(MYSQL, "DATETIME");
185 dateTypes.put(POSTGRESQL, "timestamp");
186 dateTypes.put(ORACLE, "DATE");
187 }
188
189 private String getCanonicalDBProductName(Connection conn)
190 throws SQLException, BuildException
191 {
192 DatabaseMetaData dmd = conn.getMetaData();
193 String theVendor = dmd.getDatabaseProductName().toLowerCase();
194 String result = null;
195 for (int i=0; i<supportedDBs.length && result == null; i++)
196 {
197 if (theVendor.indexOf(supportedDBs[i]) >= 0)
198 {
199 result = supportedDBs[i];
200 }
201 }
202 if (result == null)
203 {
204 throw new BuildException("Unsupported database: " + theVendor);
205 }
206
207 return result;
208 }
209
210 private void upgradeScarabIssue()
211 throws SQLException
212 {
213 Connection conn = null;
214 Statement stmt = null;
215 try
216 {
217 setAutocommit(true);
221 conn = getConnection();
222 String dbtype = getCanonicalDBProductName(conn);
223 String longType = (String )longTypes.get(dbtype);
224
227 String sql = "alter table SCARAB_ISSUE add " +
229 "CREATED_TRANS_ID " + longType + " NULL";
230 System.out.println(
231 "Adding creation info to SCARAB_ISSUE");
232 try
233 {
234 stmt = conn.createStatement();
235 stmt.execute(sql);
236 }
237 finally
238 {
239 close(stmt, null);
240 }
241
242 sql ="alter table SCARAB_ISSUE add FOREIGN KEY (CREATED_TRANS_ID)"
244 + " REFERENCES SCARAB_TRANSACTION(TRANSACTION_ID)";
245 try
246 {
247 stmt = conn.createStatement();
248 stmt.execute(sql);
249 }
250 finally
251 {
252 close(stmt, null);
253 }
254
255 long max = -1L;
257 sql = "select max(ISSUE_ID) from SCARAB_ISSUE";
258 try
259 {
260 stmt = conn.createStatement();
261 ResultSet rs = stmt.executeQuery(sql);
262 rs.next();
263 max = rs.getLong(1);
264 }
265 finally
266 {
267 close(stmt, null);
268 }
269 System.out.println("Updating " + max + " rows in SCARAB_ISSUE");
270 System.out.print("...");
271
272 for (long i=1; i<max; i+=1000L)
275 {
276 for (Iterator idAndInfo =
277 getCreateInfoFromInitialActivitySets(i, i+1000, conn)
278 .entrySet().iterator(); idAndInfo.hasNext();)
279 {
280 Map.Entry me = (Map.Entry)idAndInfo.next();
281 setCreatedInfo((String )me.getKey(), (String )me.getValue(),
285 conn);
286 }
287 }
288 System.out.print("\ndone.");
289 }
290 finally
291 {
292 close(stmt, conn);
293 }
294 }
295
296
302 private Map getCreateInfoFromInitialActivitySets(long startIssueId,
303 long endIssueId,
304 Connection conn)
305 throws SQLException
306 {
307 Map result = new HashMap(1500);
308 Statement stmt = null;
309 String sql = "select " +
310 "a.ISSUE_ID, t.TRANSACTION_ID, t.TYPE_ID " +
312 "FROM SCARAB_TRANSACTION t, SCARAB_ACTIVITY a " +
313 "WHERE a.ISSUE_ID >= " + startIssueId +
314 " and a.ISSUE_ID < " + endIssueId +
315 " and t.TYPE_ID IN (" + CREATE_ISSUE__PK + "," + MOVE_ISSUE__PK +
316 ") and a.TRANSACTION_ID=t.TRANSACTION_ID " +
317 "ORDER BY t.TYPE_ID ASC";
318 System.out.print(".");
319 try
320 {
321 stmt = conn.createStatement();
322 ResultSet rs = stmt.executeQuery(sql);
323 while (rs.next())
324 {
325 String id = rs.getString(1);
326 if (!result.containsKey(id))
327 {
328 result.put(id, rs.getString(2));
330 }
331 }
332 }
333 finally
334 {
335 close(stmt, null);
336 }
337 return result;
338 }
339
340 private void setCreatedInfo(String issueId, String activitySetId,
342 Connection conn)
343 throws SQLException
344 {
345 Statement stmt = null;
346 String sql = "update SCARAB_ISSUE set CREATED_TRANS_ID=" +
349 activitySetId + " where ISSUE_ID=" + issueId;
350 try
351 {
352 stmt = conn.createStatement();
353 stmt.executeUpdate(sql);
354 }
355 finally
356 {
357 close(stmt, null);
358 }
359 }
360
361 private void close(Statement stmt, Connection conn)
362 {
363 if (stmt != null)
364 {
365 try
366 {
367 stmt.close();
368 }
369 catch (SQLException ignore) {}
370 }
371 if (conn != null)
372 {
373 try
374 {
375 conn.close();
376 }
377 catch (SQLException ignore) {}
378 }
379 }
380 }
381 |
Popular Tags |
Java API By Example, From Geeks To Geeks.