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