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