carl bondeson - 2012-08-31

I have rewritten the bean script for hsqldb to mysql for sql server. It didn't require much other than handling foreign keys and identity columns.

import java.sql.*;

/**
* refer documentation at: http://jtrac.info/doc/html/upgrading.html
*
* example usage:
*
* set CLASSPATH=bsh-2.0b4.jar;hsqldb-1.8.0.1.jar;mysql-connector-java-5.1.6-bin.jar
*
* java -Xmx512m -Dfile.encoding=UTF-8 -cp %CLASSPATH% bsh.Interpreter jtrac-hsqldb-to-mysql.bsh
*
* (this is for Windows, please make the necessary changes to the above for linux)
*
* IMPORTANT:
* a) ensure that database encoding is set correctly, one way to do this in MySQL:
*
*      create database jtrac charset utf8;
*
* b) and in the MySQL URL you can force the encoding if required, for example:
*     
*      jdbc:mysql://localhost/jtrac?useEncoding=true&characterEncoding=utf8
*
* edit the next 4 lines and provide database JDBC driver, url, username and password
* for the source and destination databases
*/       

Class.forName("org.hsqldb.jdbcDriver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection conn1 = DriverManager.getConnection("jdbc:hsqldb:file:C:\\.jtrac\\db\\jtrac", "sa", "");

Connection conn2 = DriverManager.getConnection("jdbc:sqlserver://dph-sql009;DatabaseName=jtrac;integratedSecurity=false;", "sa", "pa33w0rd");

DatabaseMetaData md1 = conn1.getMetaData();
ResultSet rs1 = md1.getTables(null, null, null, new String { "TABLE" });

List tableNames = new ArrayList();

/* add in specific order to guarantee foreign key constraint */
tableNames.add("config");
tableNames.add("metadata");
tableNames.add("users");
tableNames.add("spaces");
tableNames.add("user_space_roles");
tableNames.add("space_sequence");
tableNames.add("items");
tableNames.add("tags");
tableNames.add("item_tags");
tableNames.add("item_items");
tableNames.add("item_users");
tableNames.add("attachments");
tableNames.add("history");

Statement stmt1 = conn1.createStatement();       
Statement stmt2 = conn2.createStatement();

stmt2.executeUpdate("delete from user_space_roles");
stmt2.executeUpdate("delete from users");

Boolean ident=false;

for (String tableName : tableNames) {          
ident=false;
    rs1 = stmt1.executeQuery("select * from " + tableName);
    ResultSetMetaData rsmd1 = rs1.getMetaData();                      
    Map map = new HashMap();

    for (int i = 1; i <= rsmd1.getColumnCount(); i++) {
        String name = rsmd1.getColumnName(i).toUpperCase();
        map.put(name, i);
    }                       

    ResultSet rs2 = stmt2.executeQuery("select * from " + tableName.toLowerCase());
    ResultSetMetaData rsmd2 = rs2.getMetaData();

    String cols = "";
    String vals = "";

    for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
        String name = rsmd2.getColumnName(i);
        cols = cols + name;
        vals = vals + "?";
        if (i != rsmd1.getColumnCount()) {
            cols = cols + ", ";
            vals = vals + ", ";
        }

if (rsmd2.isAutoIncrement(i)) {
ident=true;

}
    }

String ins;

if (ident)
ins = "SET IDENTITY_INSERT " + tableName + " ON; " + "insert into " + tableName.toLowerCase() + " (" + cols + ") values (" + vals + ")" + "; SET IDENTITY_INSERT " + tableName + " OFF ;";             
else
ins = "insert into " + tableName.toLowerCase() + " (" + cols + ") values (" + vals + ")";

PreparedStatement ps2 = conn2.prepareStatement(ins);

    boolean isUsersTable = "USERS".equalsIgnoreCase(tableName);
    Map loginNames = isUsersTable ? new HashMap() : null;
   
    System.out.println("\nprocessing table: " + tableName);
   
    int count = 0;

    while (rs1.next()) {
        for (int i = 1; i <= rsmd2.getColumnCount(); i++) {                   
            String name = rsmd2.getColumnName(i).toUpperCase();
            int index = map.get(name);
            Object value = rs1.getObject(index);
            if(value != null && rsmd2.getColumnType(i) == Types.VARCHAR) {                       
                String s = (String) value;
                int precision = rsmd2.getPrecision(i);
                if(s.length() > precision) {
                    s = s.substring(0, precision -1);
                }
                if(isUsersTable && name.equalsIgnoreCase("LOGIN_NAME")) {
                    String temp = s.toUpperCase();
                    if(loginNames.containsKey(temp)) {
                        Integer userCount = loginNames.get(temp);
                        userCount++;
                        System.out.println("\nWARN, duplicate login_name " + s
                                + ", changing to " + s + userCount);
                        s = s + userCount;
                    } else {
                        loginNames.put(temp, 0);
                    }
                }                       

                ps2.setObject(i, s);
            } else {
                ps2.setObject(i, value);
            }
        }
        try {
            ps2.execute();
        } catch (Exception e) {
            System.out.println("\nWARN: skipping " + rs1.getObject(1) + ", " + e.getMessage());
        }       

        count++;       
        System.out.print("\r" + count);

    }

}

conn1.close();
conn2.close();

System.out.println("\n\n- DONE -");