Sunday, October 04, 2009

Migrating from Bugzilla to Redmine

It has been a while since I started evaluating Redmine and later started using as well. Earlier we were using Bugzilla for issue tracking, which contains good amount of data (current and historical as well). This data can't be left behind just for the sake of using some new tool. I was looking at ways to migrate this data. Robert Heath's PHP script for migration was the best solution I found.

His scripts deletes all Redmine data and migrates everything from Bugzilla to Redmine, which I did not want and also we have a highly customized instance of Bugzilla, so using that script out of box was not possible. I wanted to pick and choose the data for migration instead of dumping everything in Redmine. The Bugzilla was having internal authentication for users while we wanted to have a mix of LDAP and internal authentication in Redmine.

To meet all these (and many more) requirements, big changes were required to Robert's script. Being a long time Java programmer, I hardly have any knowledge of PHP. Making changes to Robert's PHP script was difficult to me, so I rewrote the script again in Java. I used Robert's script as a reference to map Redmine and Bugzilla types and for SQL scripts. Without that it would have been quite difficult to write the script.

Though it took some effort to write the migration script but the effort spent was worth it. Now with Redmine we have a consolidated view of issues and source code. Migration has been seamless and users are much happier with Redmine. Now I am looking at possibilities about writing few plugins for Redmine to customize it for some innovative usages.

Update Feb 13, 2010: As I was being asked for the script frequently, here is the code I used for migration. This may not work against each and every Bugzilla installation, modify it according to your needs.

/**
 *
 * 1. All internal IDs are associated as Developer (4) to projects, while external as
 * Client (6).
 *
 */
public class Migrator {

    private Log log = LogFactory.getLog(this.getClass());
    String filePath = "D:\\bugzillaAttachments"; // where to store bugzilla attachments
    SimpleDateFormat sdf = new SimpleDateFormat("yyMMddhhmmss");
    private String projectList = "1, 2, 3, 4, 5";
    private static Map priority = new HashMap();
    private static Map status = new HashMap();
    private static Map tracker = new HashMap();
    // old values are key and new ones values
    private Map userMap = new HashMap();
    private Map existingUserMap = new HashMap();
    private Map projectMap = new HashMap();
    private Map categoryMap = new HashMap();
    private Map issueMap = new HashMap();
    // key > product_id + version name
    private Map versionMap = new HashMap();

    static {
        priority.put("P1", 5);
        priority.put("P2", 4);
        priority.put("P3", 3);
        priority.put("P4", 2);
        priority.put("P5", 1);

        status.put("UNCONFIRMED", 1);
        status.put("NEW", 1);
        status.put("ASSIGNED", 2);
        status.put("REOPENED", 7);
        status.put("RESOLVED", 3);
        status.put("VERIFIED", 5);
        status.put("CLOSED", 5);

        tracker.put("trivial", 1);
        tracker.put("minor", 1);
        tracker.put("normal", 1);
        tracker.put("major", 1);
        tracker.put("critical", 1);
        tracker.put("blocker", 1);
    }

    public Migrator() {
        Connection destCon = null;
        Connection srcCon = null;
        try {
            Class.forName("org.postgresql.Driver");
            destCon = DriverManager.getConnection("jdbc:postgresql://redmine_server:5432/redmine", "user",
                            "pwd");
            destCon.setAutoCommit(false);
            log.info("PostgreSQL/Redmine connection opened");

            Class.forName("com.mysql.jdbc.Driver");
            srcCon = DriverManager.getConnection("jdbc:mysql://bugzilla_server:3306/test", "user", "pwd");
            log.info("MySql/Bugzilla connection opened");

            loadExistingUsers(destCon);
            createUsers(destCon, srcCon); // create users
            createProjects(destCon, srcCon); // create projects
            destCon.commit();
        } catch (Exception e) {
            try {
                destCon.rollback();
                log.info("Rolled back changes");
            } catch (Exception e3) {
                log.error("Unable to rollback", e3);
            }
            log.error("", e);
        } finally {
            try {
                destCon.close();
                log.info("PostgreSQL/Redmine connection closed");
                srcCon.close();
                log.info("MySql/Bugzilla connection closed");
            } catch (Exception e2) {
                log.error("Error while closing connections", e2);
            }
        }
    }

    public static void main(String[] args) {
        new Migrator();
    }

    // load existing users and don't recreate them
    private void loadExistingUsers(Connection destCon) throws Exception {
        String SELECT = "SELECT mail, id FROM users";
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = destCon.createStatement();
            rs = stmt.executeQuery(SELECT);
            while (rs.next()) {
                existingUserMap.put(rs.getString(1), rs.getInt(2));
            }
        } finally {
            close(rs);
            close(stmt);
        }
    }

    private void createUsers(Connection destCon, Connection srcCon) throws Exception {
        String SELECT = "SELECT userid, login_name, realname, disabledtext FROM profiles "
                        + "WHERE userid IN (SELECT ugm.user_id FROM user_group_map ugm, group_control_map gcm "
                        + " WHERE ugm.group_id = gcm.group_id AND gcm.product_id IN (" + projectList + "))";
        String INSERT = "INSERT INTO users (login, mail, firstname, lastname, language, status, auth_source_id, created_on, updated_on) "
                        + "VALUES (?, ?, ?, ?, 'en', ?, ?, now(), now())";
        log.info("Creating uses . . .");
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet selectRs = null;
        ResultSet keysRs = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT, new String[] { "id" });
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            while (selectRs.next()) {
                int userId = selectRs.getInt(1);
                String loginName = selectRs.getString(2).trim();
                // don't recreate an existing user
                if (existingUserMap.containsKey(loginName)) {
                    userMap.put(userId, existingUserMap.get(loginName));
                    log.info("Bugzilla user [" + userId + ", " + loginName
                                    + "] > already exists as Redmine user [" + existingUserMap.get(loginName)
                                    + "]");
                    continue;
                }
                String realName = selectRs.getString(3).trim();
                String disabledText = selectRs.getString(4);

                // Keep non internal user ids with full email address, this will enable
                // future employees with same id
                String newLoginName;
                if (loginName.endsWith("@domain.com")) {
                    newLoginName = loginName.substring(0, loginName.indexOf("@"));
                    if (newLoginName.length() > 20) {
                        // Our LDAP (AD) has a limit of 20 chars
                        newLoginName = loginName.substring(0, 20);
                    }
                } else {
                    newLoginName = loginName;
                }

                String firstName;
                String lastName;
                if (realName.contains(" ")) {
                    firstName = realName.substring(0, realName.indexOf(' '));
                    lastName = realName.substring(realName.lastIndexOf(' ') + 1);
                } else {
                    // in few cases 'realName' is email address
                    if (realName.contains("@"))
                        realName = realName.substring(0, loginName.indexOf("@"));

                    firstName = realName.length() <= 30 ? realName : realName.substring(0, 30);
                    lastName = "";
                }
                // insert the user in Redmine
                insertStmt.setString(1, newLoginName);
                insertStmt.setString(2, loginName);
                insertStmt.setString(3, firstName);
                insertStmt.setString(4, lastName);
                // Wherever 'disabledtext' contains something mark his status as 3 (lock)
                if (disabledText != null && disabledText.trim().length() > 0)
                    insertStmt.setInt(5, 3);
                else
                    insertStmt.setInt(5, 1);
                // If user id is not @domain.com, mark their auth source as internal
                if (loginName.endsWith("@domain.com"))
                    insertStmt.setInt(6, 1);
                else
                    insertStmt.setNull(6, java.sql.Types.INTEGER);

                insertStmt.executeUpdate();
                // update new key in bugzilla
                keysRs = insertStmt.getGeneratedKeys();
                keysRs.next();
                int newId = keysRs.getInt(1);
                userMap.put(userId, newId); // put in map for later retrieval
                close(keysRs);

                log.info("Bugzilla user [" + userId + ", " + loginName + " ] > Redmine user [" + newId + ", "
                                + newLoginName + "]");
            }
            log.info(userMap.size() + " users created.");
        } finally {
            close(selectStmt);
            close(insertStmt);
            close(selectRs);
            close(keysRs);
        }
    }

    private void createProjects(Connection destCon, Connection srcCon) throws Exception {
        log.info("Creating project: ABC . . .");
        createProject(destCon, srcCon, "INSERT INTO projects (name, description, identifier, created_on, "
                        + "updated_on) VALUES ('ABC', 'ABC', 'abc', now(), now())", 77);
        // repaet it for more projects

        // Following projects already exist there, just migrate their issues
        log.info("Migrating project: XYZ . . . "
                        + "Redmine project id is 13 for old Bugzilla project id 91");
        createProjectArtifacts(destCon, srcCon, 91, 13);
        projectMap.put(91, 13);
    }

    private void createProject(Connection destCon, Connection srcCon, String insertSql, int bugzillaProjId)
                    throws Exception {
        Statement insertStmt = null;
        ResultSet rs = null;
        try {
            insertStmt = destCon.createStatement();
            insertStmt.executeUpdate(insertSql, new String[] { "id" });
            rs = insertStmt.getGeneratedKeys();
            rs.next();
            int redmineProjId = rs.getInt(1);
            // put in map for later retrieval
            projectMap.put(bugzillaProjId, redmineProjId);
            log.info("   Redmine project id is " + redmineProjId + " for old Bugzilla project id "
                            + bugzillaProjId);

            createProjectArtifacts(destCon, srcCon, bugzillaProjId, redmineProjId);
        } finally {
            close(rs);
            close(insertStmt);
        }
    }

    private void createProjectArtifacts(Connection destCon, Connection srcCon, int bugzillaProjId,
                    int redmineProjId) throws Exception {
        int[] trackers = { 1, 2, 3 };
        Statement insertStmt = null;
        try {
            insertStmt = destCon.createStatement();
            // enable issue tracker module
            insertStmt.executeUpdate("INSERT INTO enabled_modules (project_id, name) VALUES ("
                            + redmineProjId + ", 'issue_tracking')");
            log.info("   Enabled tracker module");

            // create trackers
            log.info("   Creating trackers");
            for (int tracker : trackers)
                insertStmt.executeUpdate("INSERT INTO projects_trackers (project_id, tracker_id) VALUES ("
                                + redmineProjId + ", " + tracker + ")");

            Thread.sleep(1000);
            log.info("   Creating versions");
            createVersions(destCon, srcCon, bugzillaProjId, redmineProjId);

            Thread.sleep(1000);
            log.info("   Assigining project members");
            assignProjectMembers(destCon, srcCon, bugzillaProjId, redmineProjId);

            Thread.sleep(1000);
            log.info("   Creating issue categories");
            createCategories(destCon, srcCon, bugzillaProjId, redmineProjId);

            Thread.sleep(1000);
            log.info("   Migrating issues");
            createIssues(destCon, srcCon, bugzillaProjId, redmineProjId);
        } finally {
            close(insertStmt);
        }
    }

    private void createVersions(Connection destCon, Connection srcCon, int bugzillaProjId, int redmineProjId)
                    throws Exception {
        String SELECT = "SELECT value FROM versions WHERE product_id = " + bugzillaProjId;
        String INSERT = "INSERT INTO versions (project_id, name, created_on, updated_on) VALUES (?, ?, now(), now())";
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet selectRs = null;
        ResultSet keysRs = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT, new String[] { "id" });
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            while (selectRs.next()) {
                String name = selectRs.getString(1);
                log.info("      Creating version: " + name);
                insertStmt.setInt(1, redmineProjId);
                insertStmt.setString(2, name);
                insertStmt.executeUpdate();
                keysRs = insertStmt.getGeneratedKeys();
                keysRs.next();
                int newId = keysRs.getInt(1);
                versionMap.put(bugzillaProjId + name, newId); // store for later retrieval
                keysRs.close();
            }
        } finally {
            close(selectStmt);
            close(insertStmt);
            close(selectRs);
        }
    }

    private void assignProjectMembers(Connection destCon, Connection srcCon, int bugzillaProjId,
                    int redmineProjId) throws Exception {
        String SELECT = "SELECT DISTINCT p.userid, pd.id, p.login_name "
                        + "FROM group_control_map gcm, user_group_map ugm, profiles p, products pd "
                        + "WHERE gcm.group_id = ugm.group_id AND ugm.user_id = p.userid AND gcm.product_id = pd.id "
                        + "AND pd.id = " + bugzillaProjId;
        String INSERT = "INSERT INTO members (user_id, project_id, role_id, created_on) VALUES (?, ?, ?, now())";
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet selectRs = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT);
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            int member = -1;
            while (selectRs.next()) {
                member = selectRs.getInt(1);
                String login_name = selectRs.getString(3);
                log
                                .info("      Assigning member: " + member + "(" + userMap.get(member) + "), "
                                                + login_name);
                insertStmt.setInt(1, userMap.get(member));
                insertStmt.setInt(2, redmineProjId);
                if (login_name.endsWith("@domain.com"))
                    insertStmt.setInt(3, 4); // mark everyone as developer
                else
                    insertStmt.setInt(3, 6); // client
                insertStmt.addBatch();
            }
            insertStmt.executeBatch();
        } finally {
            close(selectRs);
            close(selectStmt);
            close(insertStmt);
        }
    }

    private void createCategories(Connection destCon, Connection srcCon, int bugzillaProjId, int redmineProjId)
                    throws Exception {
        String SELECT = "SELECT id, product_id, name, initialowner FROM components WHERE product_id = "
                        + bugzillaProjId;
        String INSERT = "INSERT INTO issue_categories (project_id, name, assigned_to_id) VALUES (?, ?, ?)";
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet selectRs = null;
        ResultSet keysRs = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT, new String[] { "id" });
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            while (selectRs.next()) {
                insertStmt.setInt(1, redmineProjId);
                String name = selectRs.getString(3);
                name = name.length() <= 30 ? name : name.substring(0, 30);
                log.info("      Creating category: " + name);
                insertStmt.setString(2, name);
                insertStmt.setInt(3, userMap.get(selectRs.getInt(4)));
                insertStmt.executeUpdate();
                keysRs = insertStmt.getGeneratedKeys();
                keysRs.next();
                int newCatId = keysRs.getInt(1);
                close(keysRs);
                categoryMap.put(selectRs.getInt(1), newCatId);
            }
        } finally {
            close(selectRs);
            close(keysRs);
            close(insertStmt);
            close(selectStmt);
        }
    }

    private void createIssues(Connection destCon, Connection srcCon, int bugzillaProjId, int redmineProjId)
                    throws Exception {
        String SELECT = "SELECT bugs.bug_id, bugs.short_desc, longdescs.thetext, bugs.bug_file_loc AS url, bugs.assigned_to, "
                        + "bugs.reporter, bugs.creation_ts, longdescs.bug_when, bugs.priority, bugs.version, bugs.component_id, "
                        + "bugs.bug_status, bugs.resolution, bugs.bug_severity, longdescs.comment_id, longdescs.who, lastdiffed, "
                        + "bugs.cf_phases1, bugs.cf_work "
                        + "FROM bugs, longdescs WHERE bugs.bug_id = longdescs.bug_id AND bugs.product_id = "
                        + bugzillaProjId;
        String INSERT_ISSUE = "INSERT INTO issues (project_id, subject, description, assigned_to_id, author_id, created_on, "
                        + "updated_on, start_date, priority_id, fixed_version_id, category_id, tracker_id, status_id) "
                        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        String INSERT_JOURNAL = "INSERT INTO journals (journalized_id, journalized_type, user_id, notes, created_on) "
                        + " VALUES (?, ?, ?, ?, ?)";
        String INSERT_CUSTOM = "INSERT INTO custom_values (customized_type, customized_id, custom_field_id, value) "
                        + "VALUES ('Issue', ?, ?, ?)";
        Statement selectStmt = null;
        PreparedStatement insertIssueStmt = null;
        PreparedStatement insertJournalStmt = null;
        PreparedStatement insertCustomStmt = null;
        ResultSet selectRs = null;
        ResultSet keysRs = null;
        try {
            insertIssueStmt = destCon.prepareStatement(INSERT_ISSUE, new String[] { "id" });
            insertJournalStmt = destCon.prepareStatement(INSERT_JOURNAL);
            insertCustomStmt = destCon.prepareStatement(INSERT_CUSTOM);
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            int bugId = -1;
            int redmineBugId = -1;
            while (selectRs.next()) {
                int currentBugId = selectRs.getInt(1);
                // first record for the bug
                if (currentBugId != bugId) {
                    bugId = currentBugId;
                    log.info("    " + bugId + " >>>");
                    insertIssueStmt.setInt(1, redmineProjId);
                    insertIssueStmt.setString(2, selectRs.getString(2)); // subject
                    // description along with URL
                    insertIssueStmt.setString(3, selectRs.getString(3) + "\n\n" + selectRs.getString(4));
                    insertIssueStmt.setInt(4, userMap.get(selectRs.getInt(5))); // assigned_to
                    insertIssueStmt.setInt(5, userMap.get(selectRs.getInt(6)));// reporter
                    insertIssueStmt.setTimestamp(6, selectRs.getTimestamp(7)); // created_on
                    insertIssueStmt.setTimestamp(7, selectRs.getTimestamp(17)); // updated_on
                    insertIssueStmt.setDate(8, selectRs.getDate(7)); // start_date
                    insertIssueStmt.setInt(9, priority.get(selectRs.getString(9)));// priority
                    // fixed_version_id - do post processing to set 0 values to null/empty
                    insertIssueStmt.setInt(10, versionMap.get(bugzillaProjId + selectRs.getString(10)));
                    insertIssueStmt.setInt(11, categoryMap.get(selectRs.getInt(11))); // component/category
                    insertIssueStmt.setInt(12, tracker.get(selectRs.getString(14))); // tracker
                    insertIssueStmt.setInt(13, status.get(selectRs.getString(12))); // status
                    // (resolution)
                    insertIssueStmt.executeUpdate();
                    keysRs = insertIssueStmt.getGeneratedKeys();
                    keysRs.next();
                    redmineBugId = keysRs.getInt(1);
                    close(keysRs);
                    issueMap.put(bugId, redmineBugId);

                    // Custom values Severity(5), Defect Injection(3) and Defect Cause(4)
                    insertCustomStmt.setInt(1, redmineBugId);
                    insertCustomStmt.setInt(2, 5); // severity
                    insertCustomStmt.setString(3, selectRs.getString(14));
                    insertCustomStmt.addBatch();
                    insertCustomStmt.setInt(1, redmineBugId);
                    insertCustomStmt.setInt(2, 3); // injection
                    insertCustomStmt.setString(3, selectRs.getString(18));
                    insertCustomStmt.addBatch();
                    insertCustomStmt.setInt(1, redmineBugId);
                    insertCustomStmt.setInt(2, 4); // cause
                    insertCustomStmt.setString(3, selectRs.getString(19));
                    insertCustomStmt.addBatch();

                    // Map Bugzilla CC to Redmine Watchers
                    createWatchers(destCon, srcCon, bugId, redmineBugId);
                    // log.info("      Watchers (CC list) migrated");

                    // process attachments (11931 has attachments)
                    handleAttachments(destCon, srcCon, bugId, redmineBugId);
                    // log.info("      Attachments migrated");
                } else {
                    // Create journal entries (comments on issues)
                    insertJournalStmt.setInt(1, redmineBugId);
                    insertJournalStmt.setString(2, "Issue");
                    insertJournalStmt.setInt(3, userMap.get(selectRs.getInt(16)));
                    insertJournalStmt.setString(4, selectRs.getString(3));
                    insertJournalStmt.setTimestamp(5, selectRs.getTimestamp(8));
                    insertJournalStmt.addBatch();
                }
            }
            insertJournalStmt.executeBatch();
            log.info("      Created Journal entries");
            insertCustomStmt.executeBatch();
            log.info("      Created severity, cuase and injection");

            // Map Bugzilla Dependencies to Redmine Relations and Duplicates
            for (Integer issue : issueMap.keySet()) {
                updateIssueRelations(destCon, srcCon, issue);
                // log.info("      Updated related and duplicate issues");
            }
        } finally {
            close(selectRs);
            close(selectStmt);
            close(insertIssueStmt);
            close(insertJournalStmt);
            close(insertCustomStmt);
        }
    }

    private void createWatchers(Connection destCon, Connection srcCon, int bugzillaBugId, int redmineBugId)
                    throws Exception {
        String INSERT = "INSERT INTO watchers (watchable_id, user_id, watchable_type) VALUES (?, ?, 'Issue')";
        String SELECT = "SELECT bug_id, who FROM cc where bug_id = " + bugzillaBugId;
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet selectRs = null;
        String cc = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT);
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            int count = 0;
            while (selectRs.next()) {
                insertStmt.setInt(1, redmineBugId);
                int who = selectRs.getInt(2);
                if (count == 0)
                    cc = "      Adding as watcher: " + who + "(" + userMap.get(who) + ")";
                else
                    cc += ", " + who + "(" + userMap.get(who) + ")";
                insertStmt.setInt(2, userMap.get(who));
                insertStmt.addBatch();
                count++;
            }
            insertStmt.executeBatch();
        } finally {
            close(selectRs);
            close(selectStmt);
            close(insertStmt);
            if (cc != null && cc.length() > 0)
                log.info(cc);
        }
    }

    private void handleAttachments(Connection destCon, Connection srcCon, int bugzillaBugId, int redmineBugId)
                    throws Exception {
        String SELECT = "SELECT a.filename, a.creation_ts, ad.thedata, a.attach_id, a.bug_id, a.mimetype, a.submitter_id, a.description "
                        + "FROM attachments a, attach_data ad WHERE a.attach_id = ad.id and a.bug_id = "
                        + bugzillaBugId;
        String INSERT = "INSERT INTO attachments (container_id, container_type, filename, disk_filename, filesize, "
                        + "content_type, digest, downloads, author_id, created_on, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet selectRs = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT);
            selectStmt = srcCon.createStatement();
            selectRs = selectStmt.executeQuery(SELECT);
            while (selectRs.next()) {
                String fileName = selectRs.getString(1);
                Timestamp fileTs = selectRs.getTimestamp(2);
                // disk file name in redmine is yymmddhhmmss_filename.ext
                String diskFileName = sdf.format(fileTs) + "_" + fileName;
                log.info("      Saving attachment: " + fileName + "(" + diskFileName + ") for issue "
                                + bugzillaBugId + "(" + redmineBugId + ")");
                Blob blob = selectRs.getBlob(3);
                byte[] allBytesInBlob = blob.getBytes(1, (int) blob.length());
                File file = new File(filePath, diskFileName);
                file.createNewFile();
                FileOutputStream fos = new FileOutputStream(file);
                fos.write(allBytesInBlob);
                fos.flush();
                fos.close();

                insertStmt.setInt(1, redmineBugId);
                insertStmt.setString(2, "Issue");
                insertStmt.setString(3, fileName);
                insertStmt.setString(4, diskFileName);
                insertStmt.setInt(5, allBytesInBlob.length);
                insertStmt.setString(6, selectRs.getString(6)); // mime type
                insertStmt.setString(7, ""); // digest- leave it blank
                insertStmt.setInt(8, 0); // downloads
                insertStmt.setInt(9, userMap.get(selectRs.getInt(7))); // author
                insertStmt.setTimestamp(10, fileTs);
                insertStmt.setString(11, selectRs.getString(8));
                insertStmt.addBatch();
            }
            insertStmt.executeBatch();
        } finally {
            close(selectRs);
            close(selectStmt);
            close(insertStmt);
        }
    }

    private void updateIssueRelations(Connection destCon, Connection srcCon, int bugzillaBugId)
                    throws Exception {
        String SELECT_DUP = "SELECT dupe_of, dupe FROM duplicates WHERE dupe_of = " + bugzillaBugId;
        String SELECT_DEP = "SELECT blocked, dependson FROM dependencies WHERE blocked = " + bugzillaBugId;
        String INSERT = "INSERT INTO issue_relations (issue_from_id, issue_to_id, relation_type) VALUES (?, ?, ?)";
        Statement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet rs = null;
        try {
            insertStmt = destCon.prepareStatement(INSERT);
            selectStmt = srcCon.createStatement();
            rs = selectStmt.executeQuery(SELECT_DUP);
            while (rs.next()) {
                insertStmt.setInt(1, issueMap.get(rs.getInt(1)));
                insertStmt.setInt(2, issueMap.get(rs.getInt(2)));
                insertStmt.setString(3, "duplicates");
                insertStmt.addBatch();
            }
            close(rs);
            rs = selectStmt.executeQuery(SELECT_DEP);
            while (rs.next()) {
                insertStmt.setInt(1, rs.getInt(1));
                insertStmt.setInt(2, rs.getInt(2));
                insertStmt.setString(3, "blocks");
                insertStmt.addBatch();
            }
            insertStmt.executeBatch();
        } finally {
            close(rs);
            close(selectStmt);
            close(insertStmt);
        }
    }

    private void close(ResultSet rs) {
        if (rs == null)
            return;
        try {
            rs.close();
        } catch (Exception e) {
            log.error("Failed to close ResultSet", e);
        }
    }

    private void close(Statement stmt) {
        if (stmt == null)
            return;
        try {
            stmt.close();
        } catch (Exception e) {
            log.error("Failed to close PreparedStatement", e);
        }
    }

}

5 Comments: