Wednesday, December 23, 2009

Start Apache as service in Linux (Fedora)

The Apache HTTP server is installed by default in all Linux distributions and it is configured to run as service. I usually prefer to install Apache from source instead of using the default one. After installing Apache I want to run that instance as service so that it can be restarted automatically whenever machine is rebooted. This can be achieved easily by modifying /etc/rc.d/init.d/httpd file, which is configured to run the Apache installed with OS.

Comment the following section, though it is not required but I prefer to keep all configuration at one place


if [ -f /etc/sysconfig/httpd ]; then
        . /etc/sysconfig/httpd
fi
Modify the following lines-
apachectl=/usr/sbin/apachectl
httpd=${HTTPD-/usr/sbin/httpd}
prog=httpd
pidfile=${PIDFILE-/var/run/httpd/httpd.pid}
to point towards your Apache installation
apachectl=/opt/apps/httpd-2.2.14/bin/apachectl
httpd=${HTTPD-/opt/apps/httpd-2.2.14/bin/httpd}
prog=httpd
pidfile=${PIDFILE-/opt/apps/httpd-2.2.14/logs/httpd.pid}
Now your custom Apache instance will be started by OS instead of the default one.

Thursday, December 10, 2009

Send XML data over web service

Occasionally I am being asked a common question, "How to send XML data over web service?". In Java (in fact in any programming language) XML is just a string (sequence of characters). So sending XML as request parameter or receiving it in response is just like handling any other string data.

For the following XML data from web service client and server-

XML from client
<name>vinod</name>

XML from server
<greeting>Hello! vinod</greeting>

This is the web service (JAX-WS) request and response-
REQUEST
<?xml version="1.0" ?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <ns2:xmlData xmlns:ns2="http://vinodsingh.com">
            <data>&lt;name&gt;vinod&lt;/name&gt;</data>
        </ns2:xmlData>
    </S:Body>
</S:Envelope>

RESPONSE
<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <ns2:xmlDataResponse xmlns:ns2="http://vinodsingh.com">
            <return>&lt;greeting>Hello! vinod&lt;/greeting></return>
        </ns2:xmlDataResponse>
    </S:Body>
</S:Envelope>

The above example clearly shows that JAX-WS runtime handles XML strings pretty well. The source code available here includes an example of sending bigger XML data.

Tuesday, December 01, 2009

JTA Transactions with Atomkios in Tomcat

Tomcat is the leading Java Servlet container. Many of our applications do not need full blown Java EE Application Server and a Servlet container like Tomcat is more than enough to run them even in production. Some of our applications need JTA transactions capabilities, which are not provided by Tomcat out of the box. There are very few open source JTA implementations. JOTM used to be most popular among them, unfortunately its development is dead for last several years though it seems to getting revived lately at its new home. Atomikos is another JTA implementation, which was open sourced (Transactions Essentials) when JOTM went into hibernation.

Integrating Atomikos with Tomcat is fairly easy, it can be done by following simple steps given below-

Create a configuration file for Atomikos at $TOMCAT_HOME/lib/transactions.properties location with contents like below-

com.atomikos.icatch.service=com.atomikos.icatch.standalone.UserTransactionServiceFactory
com.atomikos.icatch.automatic_resource_registration=true
com.atomikos.icatch.output_dir=../work
com.atomikos.icatch.log_base_dir=../work
com.atomikos.icatch.enable_logging=true
com.atomikos.icatch.console_log_level=INFO

Define a DataSource in $TOMCAT_HOME/conf/context.xml as shown below-

<Transaction factory="com.atomikos.icatch.jta.UserTransactionFactory" />

<Resource name="jdbc/pgDS"
                auth="Container"
                type="com.atomikos.jdbc.AtomikosDataSourceBean"
                factory="com.vinodsingh.atomikos.tomcat.DataSourceBeanFactory"
                uniqueResourceName="jdbc/pgDS"
                xaDataSourceClassName="org.postgresql.xa.PGXADataSource"
                xaProperties.serverName="localhost"
                xaProperties.portNumber="5432"
                xaProperties.databaseName="test"
                xaProperties.user="test"
                xaProperties.password="test"
                maxPoolSize="3"
                minPoolSize="1" />

Copy following files to $TOMCAT_HOME/lib directory-
  • JDBC driver of the Database being used.
  • JTA API (jta-1.1.jar).
  • Atomikos Essentials (transactions-essentials-all-3.5.9.jar), name may change according to the version being used.
  • Tomcat integration jars available at Atomikos' site or atomikos-tomcat.jar built from the source code attached with this post.
All these changes will make UserTransaction object available in Tomcat's JNDI tree and it can be used in same way as we do in any regular Java EE application server. An example of using UserTransaction is shown in following code snippet-
public String insertRecord(String value) {
    UserTransaction utx = null;
    try {
        utx = ServiceLocator.getTrasactionManager();
        utx.begin();
        // Do transactional work
        pgDAO.insertRecord(value);

        // commit the transaction
        utx.commit();
    } catch (Exception e) {
        try {
            if (utx != null)
                utx.rollback();

            String msg = "Oops! Transaction is rolled back due to an exception";
            log.error(msg, e);
            return msg;
        } catch (Exception e1) {
            log.error("Failed to rollback the transaction", e1);
            return "Failed to rollback the transaction" + e1.getMessage();
        }
    }
    return "Transaction succeded";
}
To see transactions in action compile the attached source code, deploy the generated jta.war on Tomcat and open http://localhost:8080/jta/ URL in your browser. Thats all it takes to integrate Atomikos JTA transactions in Tomcat.

Note:-
  1. Though Atomikos' documentation about Tomcat 6 integration talks about configuring AtomikosLifecycleListener in $TOMCAT_HOME/conf/server.xml as well. But Atomikos seems to work well inside Tomcat without that configuration.
  2. Here I have slightly modified the code for Tomcat integration from what is available at Atomikos website, more details about the same will follow in upcoming posts.

Source code for this post is available here.

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);
        }
    }

}