public static List<Map<String, Object>> read(ResultSet rs, DBTable t) throws SQLException {
List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
List<DBColumn> pkc = t.getPrimaryKey().getColumns();
while (rs.next()) {
PrimaryKey key = new PrimaryKey();
for (DBColumn c : pkc) {
key.addKeyValue(rs.getObject(c.getColumnName()));
}
Map<String, Object> fieldValues = new HashMap<String, Object>();
for (DBColumn c : t.getColumns()) {
fieldValues.put(c.getColumnName(), rs.getObject(c.getColumnName()));
}
data.add(fieldValues);
}
return data;
}
public static void compareSchema(DBSchema schema1, DBSchema schema2) {
for (DBTable t2 : schema2.getTables()) {
DBTable t1 = schema1.getTable(t2.getTableName());
if (t1 == null) {
SQL sql = new SQLCreateTable(t2);
System.out.println(sql.getDDL());
} else {
compareTable(t1, t2);
}
}
for (DBTable t1 : schema1.getTables()) {
if (schema2.getTable(t1.getTableName()) == null) {
SQL sql = new SQLDropTable(t1);
System.out.println(sql.getDDL());
}
}
}
public static List<Map<String, Object>> read(DBTable t, Connection conn) throws SQLException {
String sql = "select * from " + t.getQualifiedName() + " ORDER BY ";
DBPrimaryKey pk = t.getPrimaryKey();
for (DBColumn c : pk.getColumns()) {
if (sql.endsWith(" ORDER BY ")) {
sql += c.getColumnName();
} else {
sql += ", " + c.getColumnName();
}
}
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
List<Map<String, Object>> data = read(rs, t);
rs.close();
ps.close();
return data;
}
public static void compareData(DBSchema schema1, DBSchema schema2, Connection c1, Connection c2)
throws SQLException {
for (DBTable t2 : schema2.getTables()) {
List<Map<String, Object>> data2 = read(t2, c2);
DBTable t1 = schema1.getTable(t2.getTableName());
if (t1 == null) {
if (!data2.isEmpty()) {
System.out.println(insertEntireTable(t2, data2));
}
} else {
List<Map<String, Object>> data1 = read(t1, c1);
String s = compareTableData(t2, data1, data2);
if (!StringUtils.isEmpty(s)) {
System.out.println(compareTableData(t2, data1, data2));
}
}
}
}
public static Map<PrimaryKey, Map<String, Object>> convertData(
List<Map<String, Object>> data, DBTable t) {
Map<PrimaryKey, Map<String, Object>> result = new HashMap<PrimaryKey, Map<String, Object>>();
for (Map<String, Object> map : data) {
PrimaryKey key = new PrimaryKey();
for (DBColumn c : t.getPrimaryKey().getColumns()) {
key.addKeyValue(map.get(c.getColumnName()));
}
result.put(key, map);
}
return result;
}
public static void compareTable(DBTable t1, DBTable t2) {
// compare columns
for (DBColumn c2 : t2.getColumns()) {
DBColumn c1 = t1.getColumn(c2.getColumnName());
if (c1 == null) {
SQL sql = SQLAlterTable.createSQLAddColumn(c2);
System.out.println(sql.getDDL());
} else if (!c1.getDataTypeDDL().equals(c2.getDataTypeDDL())) {
SQL sql = SQLAlterTable.createSQLAlterColumn(c2);
System.out.println(sql.getDDL());
}
}
for (DBColumn c1 : t1.getColumns()) {
if (t2.getColumn(c1.getColumnName()) == null) {
SQL sql = SQLAlterTable.createSQLDropColumn(c1);
System.out.println(sql.getDDL());
}
}
// compare primary key
DBPrimaryKey pk1 = t1.getPrimaryKey();
DBPrimaryKey pk2 = t2.getPrimaryKey();
if (pk2.getPrimaryKeyDDL().equals(pk1.getPrimaryKeyDDL())) {
if (!pk1.getKeyName().equals(pk2.getKeyName())) {
// TODO different key names, same key columns
System.out.println("-- TODO different primary key names, same primary key columns");
}
} else {
if (pk1.getKeyName().equals(pk2.getKeyName())) {
// TODO same key name, different key columns
System.out.println("-- TODO same primary key name, different primary key columns");
} else {
// TODO different in both key name and key columns
System.out.println("-- TODO different in both primary key name and primary key columns");
}
}
// compare indexes
for (DBIndex idx2 : t2.getIndexes()) {
DBIndex idx1 = t1.getIndex(idx2.getIndexName());
if (idx1 == null) {
// TODO create new index
System.out.println("-- TODO create new index");
SQL sql = new SQLCreateIndex(idx2);
System.out.println(sql.getDDL());
} else {
if (!idx1.getIndexDDL().equals(idx2.getIndexDDL())) {
// TODO change existing index
System.out.println("-- TODO change existing index");
}
}
}
for (DBIndex idx1 : t1.getIndexes()) {
if (t2.getIndex(idx1.getIndexName()) == null) {
// TODO drop existing index
System.out.println("-- TODO drop existing index");
}
}
// compare foreign keys
for (DBForeignKey fk2 : t2.getForeignKeys()) {
DBForeignKey fx1 = t1.getForeignKey(fk2.getKeyName());
if (fx1 == null) {
// TODO create new foreign key
System.out.println("-- TODO create new foreign key");
} else {
if (!fk2.getForeignKeyDDL().equals(fx1.getForeignKeyDDL())) {
// TODO change existing foreign key
System.out.println("-- TODO change existing foreign key");
}
}
}
for (DBForeignKey fk1 : t1.getForeignKeys()) {
if (t2.getForeignKey(fk1.getKeyName()) == null) {
// TODO drop existing foreign key
System.out.println("-- TODO drop existing foreign key");
}
}
}