Example #1
0
 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;
 }
Example #2
0
 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());
     }
   }
 }
Example #3
0
 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;
 }
Example #4
0
 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));
       }
     }
   }
 }
Example #5
0
 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;
 }
Example #6
0
 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");
     }
   }
 }