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
*/
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 -");