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;
  }
  @Override
  @Transactional(rollbackFor = IOException.class)
  public EntityImportReport doImport(
      RepositoryCollection repositories, DatabaseAction databaseAction) throws IOException {
    // All new repository identifiers
    List<String> newRepoIdentifiers = new ArrayList<String>();

    // First import entities, the data sheets are ignored in the entitiesimporter
    EntityImportReport importReport = entitiesImporter.importEntities(repositories, databaseAction);

    // RULE: Feature can only belong to one Protocol in a DataSet. Check it (see issue #1136)
    checkFeatureCanOnlyBelongToOneProtocolForOneDataSet();

    // Import data sheets
    for (String name : repositories.getEntityNames()) {
      Repository repository = repositories.getRepositoryByEntityName(name);

      if (repository.getName().startsWith(DATASET_SHEET_PREFIX)) {
        // Import DataSet sheet, create new OmxRepository
        String identifier = repository.getName().substring(DATASET_SHEET_PREFIX.length());

        if (!dataService.hasRepository(identifier)) {

          dataService.addRepository(
              new AggregateableCrudRepositorySecurityDecorator(
                  new OmxRepository(dataService, searchService, identifier, entityValidator)));
          newRepoIdentifiers.add(identifier);

          DataSet dataSet =
              dataService.findOne(
                  DataSet.ENTITY_NAME,
                  new QueryImpl().eq(DataSet.IDENTIFIER, identifier),
                  DataSet.class);

          List<Protocol> protocols =
              ProtocolUtils.getProtocolDescendants(dataSet.getProtocolUsed());
          List<ObservableFeature> categoricalFeatures = new ArrayList<ObservableFeature>();
          for (Protocol protocol : protocols) {
            List<ObservableFeature> observableFeatures = protocol.getFeatures();
            if (observableFeatures != null) {
              for (ObservableFeature observableFeature : observableFeatures) {
                String dataType = observableFeature.getDataType();
                FieldType type = MolgenisFieldTypes.getType(dataType);
                if (type.getEnumType() == FieldTypeEnum.CATEGORICAL) {
                  categoricalFeatures.add(observableFeature);
                }
              }
            }
          }
          for (ObservableFeature categoricalFeature : categoricalFeatures) {
            if (!dataService.hasRepository(
                OmxLookupTableEntityMetaData.createOmxLookupTableEntityMetaDataName(
                    categoricalFeature.getIdentifier()))) {
              dataService.addRepository(
                  new OmxLookupTableRepository(
                      dataService, categoricalFeature.getIdentifier(), queryResolver));
              newRepoIdentifiers.add(
                  OmxLookupTableEntityMetaData.createOmxLookupTableEntityMetaDataName(
                      categoricalFeature.getIdentifier()));
            }
          }
        }

        // Check if all column names in the excel sheet exist as attributes of the entity
        Set<ConstraintViolation> violations = Sets.newLinkedHashSet();
        EntityMetaData meta = dataService.getEntityMetaData(identifier);
        for (AttributeMetaData attr : repository.getEntityMetaData().getAttributes()) {
          if (meta.getAttribute(attr.getName()) == null) {
            String message =
                String.format(
                    "Unknown attributename '%s' for entity '%s'. Sheet: '%s'",
                    attr.getName(), meta.getName(), repository.getName());
            violations.add(new ConstraintViolation(message, attr.getName(), null, null, meta, 0));
          }
        }

        if (!violations.isEmpty()) {
          throw new MolgenisValidationException(violations);
        }

        // Import data into new OmxRepository
        try {
          dataService.add(identifier, repository);
        } catch (MolgenisValidationException e) {
          // Add sheet info
          for (ConstraintViolation violation : e.getViolations()) {
            if (violation.getRownr() > 0) {

              // Rownr +1 for header
              violation.setImportInfo(
                  String.format(
                      "Sheet: '%s', row: %d", repository.getName(), violation.getRownr() + 1));
            } else {
              violation.setImportInfo(String.format("Sheet: '%s'", repository.getName()));
            }
          }

          for (String newRepoIdentifier : newRepoIdentifiers) {
            dataService.removeRepository(newRepoIdentifier);
          }

          throw e;
        }

        int count = (int) RepositoryUtils.count(repository);
        importReport.addEntityCount(identifier, count);
        importReport.addNrImported(count);
      }
    }

    return importReport;
  }
 public DefaultAttributeMetaData(String name, FieldTypeEnum fieldType) {
   if (name == null) throw new IllegalArgumentException("Name cannot be null");
   if (fieldType == null) throw new IllegalArgumentException("FieldType cannot be null");
   this.name = name;
   this.fieldType = MolgenisFieldTypes.getType(fieldType.toString().toLowerCase());
 }
  @Override
  public Map<String, DefaultEntityMetaData> getEntityMetaData(RepositoryCollection source) {
    // TODO: this task is actually a 'merge' instead of 'import'
    // so we need to consider both new metadata as existing ...

    Map<String, DefaultEntityMetaData> entities =
        new LinkedHashMap<String, DefaultEntityMetaData>();

    // load attributes first (because entities are optional).
    for (Entity a : source.getRepositoryByEntityName("attributes")) {
      int i = 1;
      String entityName = a.getString("entity");

      // required
      if (entityName == null) throw new IllegalArgumentException("attributes.entity is missing");
      if (a.get("name") == null) throw new IllegalArgumentException("attributes.name is missing");

      // create entity if not yet defined
      if (entities.get(entityName) == null)
        entities.put(entityName, new DefaultEntityMetaData(entityName));
      DefaultEntityMetaData md = entities.get(entityName);

      DefaultAttributeMetaData am = new DefaultAttributeMetaData(a.getString("name"));

      if (a.get("dataType") != null) {
        FieldType t = MolgenisFieldTypes.getType(a.getString("dataType"));
        if (t == null)
          throw new IllegalArgumentException(
              "attributes.type error on line " + i + ": " + a.getString("dataType") + " unknown");
        am.setDataType(t);
      }
      if (a.get("nillable") != null) am.setNillable(a.getBoolean("nillable"));
      if (a.get("auto") != null) am.setAuto(a.getBoolean("auto"));
      if (a.get("idAttribute") != null) am.setIdAttribute(a.getBoolean("idAttribute"));

      md.addAttributeMetaData(am);
    }

    // load all entities (optional)
    if (source.getRepositoryByEntityName("entities") != null) {
      int i = 1;
      for (Entity e : source.getRepositoryByEntityName("entities")) {
        i++;
        String entityName = e.getString("name");

        // required
        if (entityName == null)
          throw new IllegalArgumentException("entity.name is missing on line " + i);

        if (entities.get(entityName) == null)
          entities.put(entityName, new DefaultEntityMetaData(entityName));
        DefaultEntityMetaData md = entities.get(entityName);

        if (e.get("description") != null) md.setDescription(e.getString("description"));
      }
    }

    // re-iterate to map the mrefs/xref refEntity (or give error if not found)
    // TODO: consider also those in existing db
    int i = 1;
    for (Entity a : source.getRepositoryByEntityName("attributes")) {
      i++;
      if (a.get("refEntity") != null) {
        DefaultEntityMetaData em = entities.get(a.getString("entity"));
        DefaultAttributeMetaData am =
            (DefaultAttributeMetaData) em.getAttribute(a.getString("name"));

        if (entities.get(a.getString("refEntity")) == null) {
          throw new IllegalArgumentException(
              "attributes.refEntity error on line "
                  + i
                  + ": "
                  + a.getString("refEntity")
                  + " unknown");
        }

        am.setRefEntity(entities.get(a.getString("refEntity")));
      }
    }

    return entities;
  }