public Collection<EntityMetaData> loadOMX(RepositoryCollection omx) {
// extract attribute metadata
Map<String, AttributeMetaData> attributes = new LinkedHashMap<String, AttributeMetaData>();
for (Entity e : omx.getRepositoryByEntityName("observablefeature")) {
logger.debug("found observablefeature: " + e);
DefaultAttributeMetaData att = new DefaultAttributeMetaData(e.getString("name"));
if (e.get("dataType") != null)
att.setDataType(MolgenisFieldTypes.getType(e.getString("dataType")));
attributes.put(e.getString("identifier"), att);
if (e.get("description") != null) att.setDescription(e.getString("description"));
// TODO unit! if(e.get("unit") != null)
if ("xref".equals(e.get("dataType")) || "mref".equals(e.get("dataType"))) {
// TODO: cannot solve!!!
att.setRefEntity(omxEntities.get("Characteristic"));
}
if ("categorical".equals(e.get("dataType"))) {
att.setRefEntity(omxEntities.get("Category"));
}
}
// TODO: fix categorical!
// extract protocols as entities(abstract=true)
Map<String, EntityMetaData> entities = new LinkedHashMap<String, EntityMetaData>();
for (Entity e : omx.getRepositoryByEntityName("protocol")) {
// skip all null entities
if (hasValues(e)) {
logger.debug("found protocol: " + e);
DefaultEntityMetaData ent = new DefaultEntityMetaData(e.getString("identifier")); // alas
ent.setLabel(e.getString("name"));
ent.setAbstract(true);
// add attributes
if (e.get("features_identifier") != null)
for (String attIdentifier : e.getList("features_identifier")) {
if (attributes.get(attIdentifier) == null)
throw new RuntimeException("attribute '" + attIdentifier + "' unknown");
ent.addAttributeMetaData(attributes.get(attIdentifier));
}
entities.put(e.getString("identifier"), ent);
}
}
for (Entity e : omx.getRepositoryByEntityName("protocol")) {
// add subprotocols as compound
if (e.get("subprotocols_identifier") != null) {
for (String subProtocol : e.getList("subprotocols_identifier")) {
DefaultAttributeMetaData att = new DefaultAttributeMetaData(subProtocol);
att.setDataType(MolgenisFieldTypes.COMPOUND);
att.setRefEntity(entities.get(subProtocol));
((DefaultEntityMetaData) entities.get(e.get("identifier"))).addAttributeMetaData(att);
}
}
}
// create dataset as entities
for (Entity e : omx.getRepositoryByEntityName("dataset")) {
logger.debug("found dataset: " + e);
DefaultEntityMetaData ent = new DefaultEntityMetaData(e.getString("identifier"));
ent.setLabel(e.getString("name"));
// dataset 'extends' protocol
ent.setExtends(entities.get(e.getString("protocolused_identifier")));
entities.put(e.getString("identifier"), ent);
}
return entities.values();
}
public Collection<EntityMetaData> extractMetaData(DataSource ds) throws ClassNotFoundException {
Map<String, DefaultEntityMetaData> result = new LinkedHashMap<String, DefaultEntityMetaData>();
Connection conn = null;
try {
conn = ds.getConnection();
DatabaseMetaData md = conn.getMetaData();
ResultSet tableInfo = md.getTables(null, null, "%", new String[] {"TABLE"});
// first add all entities
while (tableInfo.next()) {
DefaultEntityMetaData entity = new DefaultEntityMetaData(tableInfo.getString("TABLE_NAME"));
result.put(entity.getName(), entity);
}
// again, then add columns
tableInfo = md.getTables(null, null, "%", new String[] {"TABLE"});
while (tableInfo.next()) {
DefaultEntityMetaData entity = result.get(tableInfo.getString("TABLE_NAME"));
// ADD THE COLUMNS
ResultSet fieldInfo = md.getColumns(null, null, tableInfo.getString("TABLE_NAME"), null);
while (fieldInfo.next()) {
DefaultAttributeMetaData f =
new DefaultAttributeMetaData(fieldInfo.getString("COLUMN_NAME"));
// FIXME refactor out
f.setDataType(
MolgenisFieldTypes.getType(
org.molgenis.model.jaxb.Field.Type.getType(fieldInfo.getInt("DATA_TYPE"))
.toString()));
f.setDefaultValue(fieldInfo.getString("COLUMN_DEF"));
entity.addAttributeMetaData(f);
if (md.getDatabaseProductName().toLowerCase().contains("mysql")) {
// accomodate mysql CURRENT_TIMESTAMP
if ("CURRENT_TIMESTAMP".equals(f.getDefaultValue())
&& (f.getDataType().equals(MolgenisFieldTypes.DATETIME)
|| f.getDataType().equals(MolgenisFieldTypes.DATE))) {
f.setDefaultValue(null);
f.setAuto(true);
}
}
// accomodate mysql text/string fields +
// nillable="false" -> mysql ignore not null and so
// should we!
if (fieldInfo.getString("REMARKS") != null
&& !"".equals(fieldInfo.getString("REMARKS").trim())) {
f.setDescription(fieldInfo.getString("REMARKS"));
}
if (fieldInfo.getBoolean("NULLABLE")) {
f.setNillable(true);
}
// auto increment?
if (f.getDataType().equals(MolgenisFieldTypes.INT)
&& fieldInfo.getObject("IS_AUTOINCREMENT") != null) {
f.setAuto(fieldInfo.getBoolean("IS_AUTOINCREMENT"));
}
if (f.getDataType().equals(MolgenisFieldTypes.STRING)) {
if (fieldInfo.getInt("COLUMN_SIZE") > 255) {
f.setDataType(MolgenisFieldTypes.TEXT);
// f.setLength(fieldInfo.getInt("COLUMN_SIZE"));
} else {
// if (fieldInfo.getInt("COLUMN_SIZE") != 255)
// {f.setLength(fieldInfo.getInt("COLUMN_SIZE"));}
f.setDataType(null); // defaults to string
}
}
// xrefs
ResultSet xrefInfo = md.getImportedKeys(null, null, tableInfo.getString("TABLE_NAME"));
while (xrefInfo.next()) {
if (xrefInfo.getString("FKCOLUMN_NAME").equals(fieldInfo.getString("COLUMN_NAME"))) {
f.setDataType(MolgenisFieldTypes.XREF);
// problem: PKTABLE_NAME is lowercase, need to be
// corrected later?
f.setRefEntity(result.get(xrefInfo.getString("PKTABLE_NAME")));
// + "."
// + xrefInfo.getString("PKCOLUMN_NAME"));
}
}
}
// // GET AUTO INCREMENT
//
// // mysql workaround
// Statement stmt = null;
// try
// {
// String sql = "select * from " + e.getName() + " where 1=0";
// stmt = conn.createStatement();
//
// ResultSet autoincRs = stmt.executeQuery(sql);
// ResultSetMetaData rowMeta = autoincRs.getMetaData();
// for (int i = 1; i <= rowMeta.getColumnCount(); i++)
// {
// if (rowMeta.isAutoIncrement(i))
// {
// e.getFields().get(i - 1).setAuto(true);
// }
// }
// }
// catch (Exception exc)
// {
// logger.error("didn't retrieve autoinc/sequence: " + exc.getMessage());
// // e.printStackTrace();
// }
// finally
// {
// stmt.close();
// }
//
// ADD UNIQUE CONTRAINTS
ResultSet rsIndex =
md.getIndexInfo(null, null, tableInfo.getString("TABLE_NAME"), true, false);
// indexed list of uniques
Map<String, List<String>> uniques = new LinkedHashMap<String, List<String>>();
while (rsIndex.next()) {
logger.debug("UNIQUE: " + rsIndex);
// TABLE_CAT='molgenistest' TABLE_SCHEM='null'
// TABLE_NAME='boolentity' NON_UNIQUE='false'
// INDEX_QUALIFIER='' INDEX_NAME='PRIMARY' TYPE='3'
// ORDINAL_POSITION='1' COLUMN_NAME='id' ASC_OR_DESC='A'
// CARDINALITY='0' PAGES='0' FILTER_CONDITION='null'
if (uniques.get(rsIndex.getString("INDEX_NAME")) == null)
uniques.put(rsIndex.getString("INDEX_NAME"), new ArrayList<String>());
uniques.get(rsIndex.getString("INDEX_NAME")).add(rsIndex.getString("COLUMN_NAME"));
}
for (List<String> index : uniques.values()) {
if (index.size() == 1) {
((DefaultAttributeMetaData) entity.getAttribute(index.get(0))).setUnique(true);
} else {
// TODO: composite keys!
// StringBuilder fieldsBuilder = new StringBuilder();
// for (String field_name : index)
// {
// fieldsBuilder.append(',').append(field_name);
// }
// Unique u = new Unique();
// u.setFields(fieldsBuilder.substring(1));
// e.getUniques().add(u);
}
}
//
// // FIND type="autoid"
// for (Field f : e.getFields())
// {
// if (f.getAuto() != null && f.getAuto() && f.getType().equals(Type.INT) && f.getUnique()
// != null
// && f.getUnique())
// {
// f.setType(Field.Type.AUTOID);
// f.setAuto(null);
// f.setUnique(null);
// }
// }
// }
//
// // GUESS type="xref"
// // normally they should be defined as foreign key but sometimes
// // designers leave this out
// // rule: if the field name is the same and one is autoid,
// // then other fields having the same name are likely to be xref to
// // the autoid
// for (Entity e : m.getEntities())
// {
// for (Field f : e.getFields())
// {
// if (Field.Type.AUTOID.equals(f.getType()))
// {
// for (Entity otherE : m.getEntities())
// {
// for (Field otherF : otherE.getFields())
// {
// // assume xref if
// // name == name
// // otherF.type == int
// if (otherF.getName().equals(f.getName()) && otherF.getType().equals(Field.Type.INT))
// {
// logger.debug("Guessed that " + otherE.getName() + "." + otherF.getName()
// + " references " + e.getName() + "." + f.getName());
// otherF.setType(Field.Type.XREF_SINGLE);
// // otherF.setXrefEntity(;
// otherF.setXrefField(e.getName() + "." + f.getName());
// }
// }
// }
// }
//
// }
// }
//
// // GUESS the xref labels
// // guess the xreflabel as being the non-autoid field that is unique
// // and not null
// // rule: if there is another unique field in the referenced table
// // then that probably is usable as label
// for (Entity e : m.getEntities())
// {
// for (Field f : e.getFields())
// {
// if (Field.Type.XREF_SINGLE.equals(f.getType()))
// {
// String xrefEntityName = f.getXrefField().substring(0, f.getXrefField().indexOf("."));
// String xrefFieldName = f.getXrefField().substring(f.getXrefField().indexOf(".") + 1);
// // reset the xref entity to the uppercase version
// f.setXrefField(m.getEntity(xrefEntityName).getName() + "." + xrefFieldName);
//
// for (Field labelField : m.getEntity(xrefEntityName).getFields())
// {
// // find the other unique, nillable="false" field, if
// // any
// if (!labelField.getName().equals(xrefFieldName)
// && Boolean.TRUE.equals(labelField.getUnique())
// && Boolean.FALSE.equals(labelField.getNillable()))
// {
// logger.debug("guessed label " + e.getName() + "." + labelField.getName());
// f.setXrefLabel(labelField.getName());
// }
// }
// }
// }
// }
// GUESS the inheritance relationship
// rule: if there is a foreign key that is unique itself it is
// probably inheriting...
// action: change to inheritance and remove the xref field
for (DefaultEntityMetaData e : result.values()) {
List<AttributeMetaData> toBeRemoved = new ArrayList<AttributeMetaData>();
for (AttributeMetaData f : e.getAttributes()) {
if (MolgenisFieldTypes.XREF.equals(f.getDataType())
&& Boolean.TRUE.equals(f.isUnique())) {
e.setExtends(f.getRefEntity());
toBeRemoved.add(f);
}
}
// for (DefaultEntityMetaData f : toBeRemoved)
// {
// //e.removeAttributeMetaData(f);
// }
}
//
// // TODO GUESS the type="mref"
// // rule: any entity that is not a subclass and that has maximum two
// // xref fields and autoid field
// // should be a mref
// List<Entity> toBeRemoved = new ArrayList<Entity>();
// for (Entity e : m.getEntities())
// if ("".equals(e.getExtends()))
// {
//
// if (e.getFields().size() <= 3)
// {
// int xrefs = 0;
// String idField = null;
// // the column refering to 'localEntity'
// String localIdField = null;
// // the localEntiy
// String localEntity = null;
// // the column referring to 'remoteEntity'
// String localEntityField = null;
// // the column the localIdField is referning to
// String remoteIdField = null;
// // the column remoteEntity
// String remoteEntity = null;
// // the column the remoteIdField is referring to
// String remoteEntityField = null;
//
// for (Field f : e.getFields())
// {
// if (Field.Type.AUTOID.equals(f.getType()))
// {
// idField = f.getName();
// }
// else if (Field.Type.XREF_SINGLE.equals(f.getType()))
// {
// xrefs++;
// if (xrefs == 1)
// {
// localIdField = f.getName();
// // localEntityField is just the idField of
// // the
// // localEntity
// localEntity = f.getXrefField().substring(0, f.getXrefField().indexOf("."));
// localEntityField = f.getXrefField()
// .substring(f.getXrefField().indexOf(".") + 1);
// }
// else
// {
// remoteIdField = f.getName();
// // should be the id field of the remote
// // entity
// remoteEntity = f.getXrefField().substring(0, f.getXrefField().indexOf("."));
// remoteEntityField = f.getXrefField().substring(
// f.getXrefField().indexOf(".") + 1);
// }
// }
// }
//
// // if valid mref, drop this entity and add mref fields
// // to
// // the other entities.
// if (xrefs == 2 && (e.getFields().size() == 2 || idField != null))
// {
// // add mref on 'local' end
// Entity localContainer = m.getEntity(localEntity);
// Field localField = new Field();
// if (localContainer.getField(e.getName()) == null)
// {
// localField.setName(e.getName());
// }
//
// localField.setType(Field.Type.XREF_MULTIPLE);
// localField.setXrefField(remoteEntity + "." + remoteEntityField);
// localField.setMrefName(e.getName());
// localField.setMrefLocalid(localIdField);
// localField.setMrefRemoteid(remoteIdField);
// localContainer.getFields().add(localField);
//
// // add mref to remote end
// Entity remoteContainer = m.getEntity(remoteEntity);
// Field remoteField = new Field();
// remoteField.setType(Field.Type.XREF_MULTIPLE);
// remoteField.setXrefField(localEntity + "." + localEntityField);
// remoteField.setMrefName(e.getName());
// // don't need to add local id as it is refering back
// remoteField.setMrefLocalid(remoteIdField);
// remoteField.setMrefRemoteid(localIdField);
//
// if (remoteContainer.getField(e.getName()) == null)
// {
// remoteField.setName(e.getName());
// }
// else
// {
// throw new RuntimeException("MREF creation failed: there is already a field "
// + remoteContainer.getName() + "." + e.getName());
// }
//
// remoteContainer.getFields().add(remoteField);
//
// // remove the link table as separate entity
// toBeRemoved.add(e);
// logger.debug("guessed mref " + e.getName());
// }
// }
// }
// m.getEntities().removeAll(toBeRemoved);
//
// // logger.info(MolgenisLanguage.summarize(m));
// logger.info(toString(m));
// return m;
logger.debug(entity);
}
} catch (Exception ex) {
logger.error(ex);
ex.printStackTrace();
return null;
} finally {
try {
conn.close();
} catch (Exception ex2) {
throw new RuntimeException(ex2);
}
}
return null;
}