![(please configure the [header_logo] section in trac.ini)](http://www.molgenis.org/chrome/site/molgenis_logo2.png){kind=logo}
Changeset 2608
- Timestamp:
- 01/28/10 11:22:36 (2 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
molgenis/3.3/src/org/molgenis/model/JDBCModelExtractor.java
r2605 r2608 31 31 32 32 /** 33 * java.sql.Types 34 public static final int ARRAY 2003 35 public static final int BIGINT -5 36 public static final int BINARY -2 37 public static final int BIT -7 38 public static final int BLOB 2004 39 public static final int BOOLEAN 16 40 public static final int CHAR 1 41 public static final int CLOB 2005 42 public static final int DATALINK 70 43 public static final int DATE 91 44 public static final int DECIMAL 3 45 public static final int DISTINCT 2001 46 public static final int DOUBLE 8 47 public static final int FLOAT 6 48 public static final int INTEGER 4 49 public static final int JAVA_OBJECT 2000 50 public static final int LONGNVARCHAR -16 51 public static final int LONGVARBINARY -4 52 public static final int LONGVARCHAR -1 53 public static final int NCHAR -15 54 public static final int NCLOB 2011 55 public static final int NULL 0 56 public static final int NUMERIC 2 57 public static final int NVARCHAR -9 58 public static final int OTHER 1111 59 public static final int REAL 7 60 public static final int REF 2006 61 public static final int ROWID -8 62 public static final int SMALLINT 5 63 public static final int SQLXML 2009 64 public static final int STRUCT 2002 65 public static final int TIME 92 66 public static final int TIMESTAMP 93 67 public static final int TINYINT -6 68 public static final int VARBINARY -3 69 public static final int VARCHAR 12 33 * java.sql.Types public static final int ARRAY 2003 public static final int 34 * BIGINT -5 public static final int BINARY -2 public static final int BIT -7 35 * public static final int BLOB 2004 public static final int BOOLEAN 16 public 36 * static final int CHAR 1 public static final int CLOB 2005 public static final 37 * int DATALINK 70 public static final int DATE 91 public static final int 38 * DECIMAL 3 public static final int DISTINCT 2001 public static final int 39 * DOUBLE 8 public static final int FLOAT 6 public static final int INTEGER 4 40 * public static final int JAVA_OBJECT 2000 public static final int LONGNVARCHAR 41 * -16 public static final int LONGVARBINARY -4 public static final int 42 * LONGVARCHAR -1 public static final int NCHAR -15 public static final int 43 * NCLOB 2011 public static final int NULL 0 public static final int NUMERIC 2 44 * public static final int NVARCHAR -9 public static final int OTHER 1111 public 45 * static final int REAL 7 public static final int REF 2006 public static final 46 * int ROWID -8 public static final int SMALLINT 5 public static final int 47 * SQLXML 2009 public static final int STRUCT 2002 public static final int TIME 48 * 92 public static final int TIMESTAMP 93 public static final int TINYINT -6 49 * public static final int VARBINARY -3 public static final int VARCHAR 12 50 * 70 51 * @author Morris Swertz 71 * 52 * 72 53 */ 73 public class JDBCModelExtractor 74 { 54 public class JDBCModelExtractor { 75 55 static Logger logger = Logger.getLogger("JDBCModelExtractor"); 76 56 77 public static void main(String[] args) throws Exception 78 { 57 public static void main(String[] args) throws Exception { 79 58 Properties props = new Properties(); 80 59 props.load(new FileInputStream("molgenis.properties")); … … 82 61 } 83 62 84 public static String extract(MolgenisOptions options) 85 { 63 public static String extract(MolgenisOptions options) { 86 64 BasicDataSource data_src = new BasicDataSource(); 87 65 … … 95 73 } 96 74 97 public static String extract(Properties p) 98 { 75 public static String extract(Properties p) { 99 76 BasicDataSource data_src = new BasicDataSource(); 100 77 … … 109 86 } 110 87 111 private static String extract(BasicDataSource data_src) 112 { 88 private static String extract(BasicDataSource data_src) { 113 89 Model m = new Model(); 114 90 115 try 116 { 91 try { 117 92 // check conection 118 93 data_src.getConnection(); … … 138 113 m.setName(SCHEMA_NAME); 139 114 140 ResultSet rs = md.getTables(SCHEMA_NAME, null, null, new String[]141 { "TABLE" });115 ResultSet rs = md.getTables(SCHEMA_NAME, null, null, 116 new String[] { "TABLE" }); 142 117 143 118 Tuple tableInfo = new ResultSetTuple(rs); 144 while (rs.next()) 145 { 119 while (rs.next()) { 146 120 logger.debug("TABLE: " + tableInfo); 147 121 … … 151 125 152 126 // ADD THE COLUMNS 153 ResultSet rsCol = md.getColumns(SCHEMA_NAME, null, tableInfo .getString("TABLE_NAME"), null);154 while (rsCol.next())155 {127 ResultSet rsCol = md.getColumns(SCHEMA_NAME, null, tableInfo 128 .getString("TABLE_NAME"), null); 129 while (rsCol.next()) { 156 130 Tuple fieldInfo = new ResultSetTuple(rsCol); 157 131 logger.debug("COLUMN: " + fieldInfo); … … 159 133 Field f = new Field(); 160 134 f.setName(fieldInfo.getString("COLUMN_NAME")); 161 f.setType(Field.Type.getType(fieldInfo.getInt("DATA_TYPE"))); 135 f 136 .setType(Field.Type.getType(fieldInfo 137 .getInt("DATA_TYPE"))); 162 138 f.setDefaultValue(fieldInfo.getString("COLUMN_DEF")); 163 164 //accomodate mysql CURRENT_TIMESTAMP 165 if("CURRENT_TIMESTAMP".equals(f.getDefaultValue()) && 166 (f.getType().equals(Field.Type.DATETIME) || f.getType().equals(Field.Type.DATE))) 167 { 168 f.setDefaultValue(null); 169 f.setAuto(true); 170 } 171 172 if(fieldInfo.getString("REMARKS") != null && !"".equals(fieldInfo.getString("REMARKS").trim()) )f.setDescription(fieldInfo.getString("REMARKS")); 173 if (fieldInfo.getBool("NULLABLE")) f.setNillable(true); 139 140 if (md.getDatabaseProductName().toLowerCase().contains( 141 "mysql")) { 142 // accomodate mysql CURRENT_TIMESTAMP 143 if ("CURRENT_TIMESTAMP".equals(f.getDefaultValue()) 144 && (f.getType().equals(Field.Type.DATETIME) || f 145 .getType().equals(Field.Type.DATE))) { 146 f.setDefaultValue(null); 147 f.setAuto(true); 148 } 149 150 // accomodate mysql text/string fields + 151 // nillable="false" -> mysql ignore not null and so 152 // should we! 153 } 154 155 if (fieldInfo.getString("REMARKS") != null 156 && !"" 157 .equals(fieldInfo.getString("REMARKS") 158 .trim())) 159 f.setDescription(fieldInfo.getString("REMARKS")); 160 if (fieldInfo.getBool("NULLABLE")) 161 f.setNillable(true); 174 162 175 163 // auto increment? 176 if (f.getType().equals(Field.Type.INT)) 177 { 178 if (fieldInfo.getObject("IS_AUTOINCREMENT") != null) f.setAuto(fieldInfo 179 .getBool("IS_AUTOINCREMENT")); 180 } 181 182 if (f.getType().equals(Field.Type.STRING) || f.getType().equals(Field.Type.CHAR)) 183 { 184 if (fieldInfo.getInt("COLUMN_SIZE") > 255) f.setType(Field.Type.TEXT); 185 else 186 { 164 if (f.getType().equals(Field.Type.INT)) { 165 if (fieldInfo.getObject("IS_AUTOINCREMENT") != null) 166 f.setAuto(fieldInfo.getBool("IS_AUTOINCREMENT")); 167 } 168 169 if (f.getType().equals(Field.Type.STRING) 170 || f.getType().equals(Field.Type.CHAR)) { 171 if (fieldInfo.getInt("COLUMN_SIZE") > 255) 172 f.setType(Field.Type.TEXT); 173 else { 187 174 // f.setLength(fieldInfo.getInt("COLUMN_SIZE")); 188 175 f.setType(null); … … 190 177 } 191 178 192 ResultSet rsXref = md.getImportedKeys(SCHEMA_NAME, null, tableInfo.getString("TABLE_NAME"));193 while (rsXref.next())194 {179 ResultSet rsXref = md.getImportedKeys(SCHEMA_NAME, null, 180 tableInfo.getString("TABLE_NAME")); 181 while (rsXref.next()) { 195 182 Tuple xrefInfo = new ResultSetTuple(rsXref); 196 if (xrefInfo.getString("FKCOLUMN_NAME").equals( fieldInfo.getString("COLUMN_NAME")))197 {183 if (xrefInfo.getString("FKCOLUMN_NAME").equals( 184 fieldInfo.getString("COLUMN_NAME"))) { 198 185 f.setType(Field.Type.XREF_SINGLE); 199 //problem: PKTABLE_NAME is lowercase, need to be corrected later? 200 f.setXrefField(xrefInfo.getString("PKTABLE_NAME") + "." 201 + xrefInfo.getString("PKCOLUMN_NAME")); 186 // problem: PKTABLE_NAME is lowercase, need to be 187 // corrected later? 188 f 189 .setXrefField(xrefInfo 190 .getString("PKTABLE_NAME") 191 + "." 192 + xrefInfo 193 .getString("PKCOLUMN_NAME")); 202 194 } 203 195 } … … 210 202 // mysql workaround 211 203 Statement stmt = null; 212 try 213 { 204 try { 214 205 String sql = "select * from " + e.getName() + " where 1=0"; 215 206 stmt = conn.createStatement(); … … 217 208 ResultSet autoincRs = stmt.executeQuery(sql); 218 209 ResultSetMetaData rowMeta = autoincRs.getMetaData(); 219 for (int i = 1; i <= rowMeta.getColumnCount(); i++) 220 { 221 if (rowMeta.isAutoIncrement(i)) 222 { 210 for (int i = 1; i <= rowMeta.getColumnCount(); i++) { 211 if (rowMeta.isAutoIncrement(i)) { 223 212 e.getFields().get(i - 1).setAuto(true); 224 213 } 225 214 } 226 } 227 catch (Exception exc) 228 { 229 logger.error("didn't retrieve autoinc/sequence: " + exc.getMessage()); 215 } catch (Exception exc) { 216 logger.error("didn't retrieve autoinc/sequence: " 217 + exc.getMessage()); 230 218 // e.printStackTrace(); 231 } 232 finally 233 { 219 } finally { 234 220 stmt.close(); 235 221 } 236 222 237 223 // ADD UNIQUE CONTRAINTS 238 ResultSet rsIndex = md.getIndexInfo(SCHEMA_NAME, null, tableInfo.getString("TABLE_NAME"), true, false); 224 ResultSet rsIndex = md.getIndexInfo(SCHEMA_NAME, null, 225 tableInfo.getString("TABLE_NAME"), true, false); 239 226 // indexed list of uniques 240 227 Map<String, List<String>> uniques = new LinkedHashMap<String, List<String>>(); 241 while (rsIndex.next()) 242 { 228 while (rsIndex.next()) { 243 229 Tuple index = new ResultSetTuple(rsIndex); 244 230 logger.debug("UNIQUE: " + index); … … 249 235 // ORDINAL_POSITION='1' COLUMN_NAME='id' ASC_OR_DESC='A' 250 236 // CARDINALITY='0' PAGES='0' FILTER_CONDITION='null' 251 if (uniques.get(index.getString("INDEX_NAME")) == null) uniques.put(index.getString("INDEX_NAME"),252 new ArrayList<String>());253 uniques.get(index.getString("INDEX_NAME")).add(index.getString("COLUMN_NAME"));254 }255 for (List<String> index : uniques.values())256 {257 if (index.size() == 1)258 {237 if (uniques.get(index.getString("INDEX_NAME")) == null) 238 uniques.put(index.getString("INDEX_NAME"), 239 new ArrayList<String>()); 240 uniques.get(index.getString("INDEX_NAME")).add( 241 index.getString("COLUMN_NAME")); 242 } 243 for (List<String> index : uniques.values()) { 244 if (index.size() == 1) { 259 245 e.getField(index.get(0)).setUnique(true); 260 } 261 else 262 { 246 } else { 263 247 String fields = ""; 264 for (String field_name : index) 265 { 248 for (String field_name : index) { 266 249 fields += "," + field_name; 267 250 } … … 274 257 275 258 // FIND type="autoid" 276 for (Field f : e.getFields()) 277 { 278 if (f.getAuto() != null && f.getAuto() && f.getType().equals(Type.INT) && f.getUnique() != null 279 && f.getUnique()) 280 { 259 for (Field f : e.getFields()) { 260 if (f.getAuto() != null && f.getAuto() 261 && f.getType().equals(Type.INT) 262 && f.getUnique() != null && f.getUnique()) { 281 263 f.setType(Field.Type.AUTOID); 282 264 f.setAuto(null); … … 292 274 // then other fields having the same name are likely to be xref to 293 275 // the autoid 294 for (Entity e : m.getEntities()) 295 { 296 for (Field f : e.getFields()) 297 { 298 if (Field.Type.AUTOID.equals(f.getType())) 299 { 300 for (Entity otherE : m.getEntities()) 301 { 302 for (Field otherF : otherE.getFields()) 303 { 276 for (Entity e : m.getEntities()) { 277 for (Field f : e.getFields()) { 278 if (Field.Type.AUTOID.equals(f.getType())) { 279 for (Entity otherE : m.getEntities()) { 280 for (Field otherF : otherE.getFields()) { 304 281 // assume xref if 305 282 // name == name 306 283 // otherF.type == int 307 if (otherF.getName().equals(f.getName()) && otherF.getType().equals(Field.Type.INT)) 308 { 309 logger.debug("Guessed that " + otherE.getName() + "." + otherF.getName() 310 + " references " + e.getName() + "." + f.getName()); 284 if (otherF.getName().equals(f.getName()) 285 && otherF.getType().equals( 286 Field.Type.INT)) { 287 logger.debug("Guessed that " 288 + otherE.getName() + "." 289 + otherF.getName() + " references " 290 + e.getName() + "." + f.getName()); 311 291 otherF.setType(Field.Type.XREF_SINGLE); 312 292 // otherF.setXrefEntity(; 313 otherF.setXrefField(e.getName() + "." + f.getName()); 293 otherF.setXrefField(e.getName() + "." 294 + f.getName()); 314 295 } 315 296 } … … 321 302 322 303 // GUESS the xref labels 323 // guess the xreflabel as being the non-autoid field that is unique and not null 304 // guess the xreflabel as being the non-autoid field that is unique 305 // and not null 324 306 // rule: if there is another unique field in the referenced table 325 307 // then that probably is usable as label 326 for (Entity e : m.getEntities()) 327 {328 for (Field f : e.getFields())329 {330 if (Field.Type.XREF_SINGLE.equals(f.getType()))331 {332 String xrefEntityName = f.getXrefField().substring(0, f.getXrefField().indexOf("."));333 String xrefFieldName = f.getXrefField().substring(f.getXrefField().indexOf(".") + 1);334 //reset the xref entity to the uppercase version335 f.setXrefField(m.getEntity(xrefEntityName).getName()+"."+xrefFieldName);336 337 338 for (Field labelField : m.getEntity(xrefEntityName).getFields())339 {340 // find the other unique, nillable="false" field, ifany308 for (Entity e : m.getEntities()) { 309 for (Field f : e.getFields()) { 310 if (Field.Type.XREF_SINGLE.equals(f.getType())) { 311 String xrefEntityName = f.getXrefField().substring(0, 312 f.getXrefField().indexOf(".")); 313 String xrefFieldName = f.getXrefField().substring( 314 f.getXrefField().indexOf(".") + 1); 315 // reset the xref entity to the uppercase version 316 f.setXrefField(m.getEntity(xrefEntityName).getName() 317 + "." + xrefFieldName); 318 319 for (Field labelField : m.getEntity(xrefEntityName) 320 .getFields()) { 321 // find the other unique, nillable="false" field, if 322 // any 341 323 if (!labelField.getName().equals(xrefFieldName) 342 && Boolean.TRUE.equals(labelField.getUnique()) && Boolean.FALSE.equals(labelField.getNillable())) 343 { 344 logger.debug("guessed label " + e.getName() + "." + labelField.getName()); 324 && Boolean.TRUE.equals(labelField 325 .getUnique()) 326 && Boolean.FALSE.equals(labelField 327 .getNillable())) { 328 logger.debug("guessed label " + e.getName() 329 + "." + labelField.getName()); 345 330 f.setXrefLabel(labelField.getName()); 346 331 } … … 354 339 // probably inheriting... 355 340 // action: change to inheritance and remove the xref field 356 for (Entity e : m.getEntities()) 357 { 341 for (Entity e : m.getEntities()) { 358 342 List<Field> toBeRemoved = new ArrayList<Field>(); 359 for (Field f : e.getFields()) 360 {361 if (Field.Type.XREF_SINGLE.equals(f.getType()) && Boolean.TRUE.equals(f.getUnique()))362 {363 String entityName = f.getXrefField().substring(0,f.getXrefField().indexOf("."));343 for (Field f : e.getFields()) { 344 if (Field.Type.XREF_SINGLE.equals(f.getType()) 345 && Boolean.TRUE.equals(f.getUnique())) { 346 String entityName = f.getXrefField().substring(0, 347 f.getXrefField().indexOf(".")); 364 348 e.setExtends(entityName); 365 349 toBeRemoved.add(f); 366 350 } 367 351 } 368 for (Field f : toBeRemoved) 369 { 352 for (Field f : toBeRemoved) { 370 353 e.getFields().remove(f); 371 354 } … … 373 356 374 357 // TODO GUESS the type="mref" 375 // rule: any entity with two xref fields and optional autoid field should be a mref 358 // rule: any entity with two xref fields and optional autoid field 359 // should be a mref 376 360 List<Entity> toBeRemoved = new ArrayList<Entity>(); 377 for (Entity e : m.getEntities()) 378 { 379 380 if (e.getFields().size() <= 3) 381 { 361 for (Entity e : m.getEntities()) { 362 363 if (e.getFields().size() <= 3) { 382 364 int xrefs = 0; 383 365 String idField = null; 384 // the column refering to 'localEntity'366 // the column refering to 'localEntity' 385 367 String localIdField = null; 386 // the localEntiy368 // the localEntiy 387 369 String localEntity = null; 388 // the column referring to 'remoteEntity'370 // the column referring to 'remoteEntity' 389 371 String localEntityField = null; 390 // the column the localIdField is referning to372 // the column the localIdField is referning to 391 373 String remoteIdField = null; 392 // the column remoteEntity374 // the column remoteEntity 393 375 String remoteEntity = null; 394 // the column the remoteIdField is referring to376 // the column the remoteIdField is referring to 395 377 String remoteEntityField = null; 396 378 397 for (Field f : e.getFields()) 398 { 399 if (Field.Type.AUTOID.equals(f.getType())) 400 { 379 for (Field f : e.getFields()) { 380 if (Field.Type.AUTOID.equals(f.getType())) { 401 381 idField = f.getName(); 402 } 403 else if (Field.Type.XREF_SINGLE.equals(f.getType())) 404 { 382 } else if (Field.Type.XREF_SINGLE.equals(f.getType())) { 405 383 xrefs++; 406 if (xrefs == 1) 407 { 384 if (xrefs == 1) { 408 385 localIdField = f.getName(); 409 //localEntityField is just the idField of the localEntity 410 localEntity = f.getXrefField().substring(0, f.getXrefField().indexOf(".")); 411 localEntityField = f.getXrefField().substring(f.getXrefField().indexOf(".")+1); 412 } 413 else 414 { 386 // localEntityField is just the idField of the 387 // localEntity 388 localEntity = f.getXrefField().substring(0, 389 f.getXrefField().indexOf(".")); 390 localEntityField = f.getXrefField().substring( 391 f.getXrefField().indexOf(".") + 1); 392 } else { 415 393 remoteIdField = f.getName(); 416 //should be the id field of the remote entity 417 remoteEntity = f.getXrefField().substring(0, f.getXrefField().indexOf(".")); 418 remoteEntityField = f.getXrefField().substring(f.getXrefField().indexOf(".")+1); 394 // should be the id field of the remote entity 395 remoteEntity = f.getXrefField().substring(0, 396 f.getXrefField().indexOf(".")); 397 remoteEntityField = f.getXrefField().substring( 398 f.getXrefField().indexOf(".") + 1); 419 399 } 420 400 } … … 423 403 // if valid mref, drop this entity and add mref field to 424 404 // entity 425 if (xrefs == 2 && (e.getFields().size() == 2 || idField != null))426 {427 // add mref on 'local' end405 if (xrefs == 2 406 && (e.getFields().size() == 2 || idField != null)) { 407 // add mref on 'local' end 428 408 Entity localContainer = m.getEntity(localEntity); 429 409 Field localField = new Field(); 430 if (localContainer.getField(e.getName()) == null) 431 { 410 if (localContainer.getField(e.getName()) == null) { 432 411 localField.setName(e.getName()); 433 412 } 434 413 localField.setType(Field.Type.XREF_MULTIPLE); 435 localField.setXrefField(remoteEntity+"."+remoteEntityField); 414 localField.setXrefField(remoteEntity + "." 415 + remoteEntityField); 436 416 localField.setMrefName(e.getName()); 437 417 localField.setMrefLocalid(localIdField); 438 418 localField.setMrefRemoteid(remoteIdField); 439 419 localContainer.getFields().add(localField); 440 441 // add mref to remote end420 421 // add mref to remote end 442 422 Entity remoteContainer = m.getEntity(remoteEntity); 443 423 Field remoteField = new Field(); 444 if (remoteContainer.getField(e.getName()) == null) 445 { 424 if (remoteContainer.getField(e.getName()) == null) { 446 425 remoteField.setName(e.getName()); 447 426 } 448 427 remoteField.setType(Field.Type.XREF_MULTIPLE); 449 remoteField.setXrefField(localEntity+"."+localEntityField); 428 remoteField.setXrefField(localEntity + "." 429 + localEntityField); 450 430 remoteField.setMrefName(e.getName()); 451 // don't need to add local id as it is refering back431 // don't need to add local id as it is refering back 452 432 remoteField.setMrefLocalid(remoteIdField); 453 433 remoteField.setMrefRemoteid(localIdField); 454 remoteContainer.getFields().add(remoteField); 455 456 // remove the link table as separate entity434 remoteContainer.getFields().add(remoteField); 435 436 // remove the link table as separate entity 457 437 toBeRemoved.add(e); 458 438 logger.debug("guessed mref " + e.getName()); … … 465 445 System.out.println(toString(m)); 466 446 return toString(m); 467 } 468 catch (Exception e) 469 { 447 } catch (Exception e) { 470 448 logger.error(e); 471 449 e.printStackTrace(); … … 473 451 } 474 452 } 475 476 477 478 private static void logResultSet(ResultSet rs) throws SQLException 479 { 480 while (rs.next()) 481 { 453 454 private static void logResultSet(ResultSet rs) throws SQLException { 455 while (rs.next()) { 482 456 Tuple t = new ResultSetTuple(rs); // logger.debug(rs.get) 483 457 // logger.debug(t); … … 485 459 } 486 460 487 private static String toString(Model model) throws JAXBException 488 { 461 private static String toString(Model model) throws JAXBException { 489 462 // save to xml (FIXME: now print only) 490 463 ByteArrayOutputStream out = new ByteArrayOutputStream(); 491 JAXBContext jaxbContext = JAXBContext.newInstance("org.molgenis.model.jaxb"); 464 JAXBContext jaxbContext = JAXBContext 465 .newInstance("org.molgenis.model.jaxb"); 492 466 Marshaller marshaller = jaxbContext.createMarshaller(); 493 467 marshaller.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
Note: See TracChangeset
for help on using the changeset viewer.
