001: /* ================================================================
002: * Copyright (c) 2000-2002 CollabNet. All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are
006: * met:
007: *
008: * 1. Redistributions of source code must retain the above copyright
009: * notice, this list of conditions and the following disclaimer.
010: *
011: * 2. Redistributions in binary form must reproduce the above copyright
012: * notice, this list of conditions and the following disclaimer in the
013: * documentation and/or other materials provided with the distribution.
014: *
015: * 3. The end-user documentation included with the redistribution, if
016: * any, must include the following acknowlegement: "This product includes
017: * software developed by Collab.Net <http://www.Collab.Net/>."
018: * Alternately, this acknowlegement may appear in the software itself, if
019: * and wherever such third-party acknowlegements normally appear.
020: *
021: * 4. The hosted project names must not be used to endorse or promote
022: * products derived from this software without prior written
023: * permission. For written permission, please contact info@collab.net.
024: *
025: * 5. Products derived from this software may not use the "Tigris" or
026: * "Scarab" names nor may "Tigris" or "Scarab" appear in their names without
027: * prior written permission of Collab.Net.
028: *
029: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
030: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
031: * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
032: * IN NO EVENT SHALL COLLAB.NET OR ITS CONTRIBUTORS BE LIABLE FOR ANY
033: * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
034: * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
035: * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
036: * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
037: * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
038: * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
039: * ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
040: *
041: * ====================================================================
042: *
043: * This software consists of voluntary contributions made by many
044: * individuals on behalf of Collab.Net.
045: */
046:
047: package org.tigris.scarab.migration.b15b16;
048:
049: import java.sql.*;
050: import java.util.*;
051:
052: import org.apache.tools.ant.BuildException;
053: import org.tigris.scarab.migration.JDBCTask;
054:
055: /**
056: * The creation transaction was previously used to retrieve the
057: * created date and creator for an issue. This makes some queries
058: * complex and error prone as well as adding inefficiency to the query.
059: * Denormalizing a bit for easier and faster queries.
060: * If the columns have not previously been added to SCARAB_ISSUE, this
061: * script adds them and populates the columns with data from the
062: * SCARAB_TRANSACTION table.
063: *
064: * @author <a href="mailto:jon@collab.net">John McNally</a>
065: * @version $Id: DB_1_MoveIssueCreateInfo.java 9049 2004-04-03 22:30:24Z pledbrook $
066: */
067: public class DB_1_MoveIssueCreateInfo extends JDBCTask {
068: private static final String CREATE_ISSUE__PK = "1";
069: private static final String MOVE_ISSUE__PK = "3";
070:
071: public DB_1_MoveIssueCreateInfo() {
072: }
073:
074: public void execute() throws BuildException {
075: boolean proceed = false;
076: try {
077: // check whether SCARAB_ISSUE already has changes
078: Connection conn = null;
079: Statement stmt = null;
080: try {
081: setAutocommit(true);
082: conn = getConnection();
083: String sql = "SELECT CREATED_TRANS_ID FROM SCARAB_ISSUE";
084: stmt = conn.createStatement();
085: try {
086: stmt.executeQuery(sql);
087: } catch (SQLException e) {
088: proceed = true;
089: }
090: } finally {
091: close(stmt, conn);
092: }
093:
094: if (proceed) {
095: upgradeScarabIssue();
096: }
097: } catch (Exception e) {
098: throw new BuildException(e);
099: }
100:
101: addNewIndices();
102: }
103:
104: private void addNewIndices() {
105: Connection conn = null;
106: Statement stmt = null;
107: try {
108: setAutocommit(true);
109: conn = getConnection();
110: String sql = "CREATE INDEX IX_ATTACHMENT on SCARAB_ACTIVITY (ATTACHMENT_ID)";
111: try {
112: stmt = conn.createStatement();
113: stmt.execute(sql);
114: } catch (SQLException e) {
115: System.out
116: .println("index SCARAB_ACTIVITY.IX_ATTACHMENT was not created. verify that it already exists.");
117: }
118: close(stmt, null);
119:
120: sql = "CREATE INDEX IX_ISSUE_ATTACHTYPE on SCARAB_ATTACHMENT (ISSUE_ID, ATTACHMENT_TYPE_ID)";
121: try {
122: stmt = conn.createStatement();
123: stmt.execute(sql);
124: } catch (SQLException e) {
125: System.out
126: .println("index SCARAB_ATTACHMENT.IX_ISSUE_ATTACHTYPE was not created. verify that it already exists.");
127: }
128: close(stmt, null);
129:
130: sql = "CREATE INDEX IX_DEPEND on SCARAB_ACTIVITY (DEPEND_ID)";
131: try {
132: stmt = conn.createStatement();
133: stmt.execute(sql);
134: } catch (SQLException e) {
135: System.out
136: .println("index SCARAB_ACTIVITY.IX_DEPEND was not created. verify that it already exists.");
137: }
138: } finally {
139: close(stmt, conn);
140: }
141: }
142:
143: private static final String MYSQL = "mysql";
144: private static final String POSTGRESQL = "postgres";
145: private static final String ORACLE = "oracle";
146: private static final String[] supportedDBs = { MYSQL, POSTGRESQL,
147: ORACLE };
148: private Map intTypes = new HashMap(3);
149: private Map longTypes = new HashMap(3);
150: private Map dateTypes = new HashMap(3);
151:
152: {
153: intTypes.put(MYSQL, "INTEGER");
154: intTypes.put(POSTGRESQL, "integer");
155: intTypes.put(ORACLE, "NUMBER");
156: longTypes.put(MYSQL, "BIGINT");
157: longTypes.put(POSTGRESQL, "int8");
158: longTypes.put(ORACLE, "NUMBER (20, 0)");
159: dateTypes.put(MYSQL, "DATETIME");
160: dateTypes.put(POSTGRESQL, "timestamp");
161: dateTypes.put(ORACLE, "DATE");
162: }
163:
164: private String getCanonicalDBProductName(Connection conn)
165: throws SQLException, BuildException {
166: DatabaseMetaData dmd = conn.getMetaData();
167: String theVendor = dmd.getDatabaseProductName().toLowerCase();
168: String result = null;
169: for (int i = 0; i < supportedDBs.length && result == null; i++) {
170: if (theVendor.indexOf(supportedDBs[i]) >= 0) {
171: result = supportedDBs[i];
172: }
173: }
174: if (result == null) {
175: throw new BuildException("Unsupported database: "
176: + theVendor);
177: }
178:
179: return result;
180: }
181:
182: private void upgradeScarabIssue() throws SQLException {
183: Connection conn = null;
184: Statement stmt = null;
185: try {
186: // could try to do this as a transaction for db's that support
187: // it, but db's should be backed up prior to migration so taking
188: // the easy way out.
189: setAutocommit(true);
190: conn = getConnection();
191: String dbtype = getCanonicalDBProductName(conn);
192: String longType = (String) longTypes.get(dbtype);
193: //String intType = (String)intTypes.get(dbtype);
194: //String dateType = (String)dateTypes.get(dbtype);
195:
196: // add the transaction fk to SCARAB_ISSUE
197: String sql = "alter table SCARAB_ISSUE add "
198: + "CREATED_TRANS_ID " + longType + " NULL";
199: System.out.println("Adding creation info to SCARAB_ISSUE");
200: try {
201: stmt = conn.createStatement();
202: stmt.execute(sql);
203: } finally {
204: close(stmt, null);
205: }
206:
207: // add fk constraint
208: sql = "alter table SCARAB_ISSUE add FOREIGN KEY (CREATED_TRANS_ID)"
209: + " REFERENCES SCARAB_TRANSACTION(TRANSACTION_ID)";
210: try {
211: stmt = conn.createStatement();
212: stmt.execute(sql);
213: } finally {
214: close(stmt, null);
215: }
216:
217: // get the highest ISSUE_ID
218: long max = -1L;
219: sql = "select max(ISSUE_ID) from SCARAB_ISSUE";
220: try {
221: stmt = conn.createStatement();
222: ResultSet rs = stmt.executeQuery(sql);
223: rs.next();
224: max = rs.getLong(1);
225: } finally {
226: close(stmt, null);
227: }
228: System.out.println("Updating " + max
229: + " rows in SCARAB_ISSUE");
230: System.out.print("...");
231:
232: // get create info for 1000 issue records at a time
233: // there is still 1 update statement per issue
234: for (long i = 1; i < max; i += 1000L) {
235: for (Iterator idAndInfo = getCreateInfoFromInitialActivitySets(
236: i, i + 1000, conn).entrySet().iterator(); idAndInfo
237: .hasNext();) {
238: Map.Entry me = (Map.Entry) idAndInfo.next();
239: //String[] info = (String[])me.getValue();
240: //setCreatedInfo((String)me.getKey(), (String)info[0],
241: // (String)info[1], conn);
242: setCreatedInfo((String) me.getKey(), (String) me
243: .getValue(), conn);
244: }
245: }
246: System.out.print("\ndone.");
247: } finally {
248: close(stmt, conn);
249: }
250: }
251:
252: /**
253: * The initial activity set from issue creation.
254: *
255: * @return a <code>ActivitySet</code> value
256: * @exception Exception if an error occurs
257: */
258: private Map getCreateInfoFromInitialActivitySets(long startIssueId,
259: long endIssueId, Connection conn) throws SQLException {
260: Map result = new HashMap(1500);
261: Statement stmt = null;
262: String sql = "select "
263: +
264: //"a.ISSUE_ID, t.CREATED_DATE, t.CREATED_BY, t.TYPE_ID " +
265: "a.ISSUE_ID, t.TRANSACTION_ID, t.TYPE_ID "
266: + "FROM SCARAB_TRANSACTION t, SCARAB_ACTIVITY a "
267: + "WHERE a.ISSUE_ID >= " + startIssueId
268: + " and a.ISSUE_ID < " + endIssueId
269: + " and t.TYPE_ID IN (" + CREATE_ISSUE__PK + ","
270: + MOVE_ISSUE__PK
271: + ") and a.TRANSACTION_ID=t.TRANSACTION_ID "
272: + "ORDER BY t.TYPE_ID ASC";
273: System.out.print(".");
274: try {
275: stmt = conn.createStatement();
276: ResultSet rs = stmt.executeQuery(sql);
277: while (rs.next()) {
278: String id = rs.getString(1);
279: if (!result.containsKey(id)) {
280: //String[] info = {rs.getString(2), rs.getString(3)};
281: result.put(id, rs.getString(2));
282: }
283: }
284: } finally {
285: close(stmt, null);
286: }
287: return result;
288: }
289:
290: //private void setCreatedInfo(String issueId, String date, String userId,
291: private void setCreatedInfo(String issueId, String activitySetId,
292: Connection conn) throws SQLException {
293: Statement stmt = null;
294: //String sql = "update SCARAB_ISSUE set CREATED_DATE='" + date +
295: // "', CREATED_USER_ID=" + userId + " where ISSUE_ID=" + issueId;
296: String sql = "update SCARAB_ISSUE set CREATED_TRANS_ID="
297: + activitySetId + " where ISSUE_ID=" + issueId;
298: try {
299: stmt = conn.createStatement();
300: stmt.executeUpdate(sql);
301: } finally {
302: close(stmt, null);
303: }
304: }
305:
306: private void close(Statement stmt, Connection conn) {
307: if (stmt != null) {
308: try {
309: stmt.close();
310: } catch (SQLException ignore) {
311: }
312: }
313: if (conn != null) {
314: try {
315: conn.close();
316: } catch (SQLException ignore) {
317: }
318: }
319: }
320: }
|