Wiki

Clone wiki

MoleculeDatabaseFramework / Molecule Database Framework Tutorial

Prerequisites

PostgreSQL and Bingo Cartridge

Molecule Database Framework depends on Bingo PostgreSQL Cartridge for chemical structure search. Hence you need to install PostgreSQL and Bingo PostgreSQL Cartridge before you can use this framework. Mind the versions. Currently (July 2013) Bingo supports PostgreSQL up to 9.2. Both are free (in terms of no cost and libre) and Open-Source.

Add Dependencies

In case you wish to work with snapshot releases of Molecule Database Framework, you need to add the snapshot repository

<repository>
    <id>sonatype-nexus-snapshots</id>
    <url>https://oss.sonatype.org/content/repositories/snapshots</url>
</repository>

to your projects repositories. All other dependencies are available on the Maven Central Repository.

Maven Configuration

See Maven Configuration. This is required to generate QueryDSL Metamodel classes required for search methods.

Introduction

Entities

MDF has 3 Abstract entities that you can extend. The minimum requirement is to create 1 subclass of ChemicalCompound. An application can have multiple types of ChemicalCompounds. If security is enabled, users can be limited to only see certain types of ChemicalCompounds.

A ChemicalCompound can be associated with a Containable. Consider you operate a factory and produce compound A. Now each lot of compound A might have slightly different properties and you must be able to track it. What you sell to the customer is a container containing a specific lot of compound A. Hence a lot is a Containable.

A Containable can then be put into a ChemicalCompoundContainer. This entity represents a physically available sample of a ChemicalCompound.

The chemical structure and mixture data are stored in the fixed classes ChemicalStructure and ChemicalCompoundComposition.

Simple Class Diagram

1. Extend ChemicalCompound

import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Entity
@Table(name="registration_compound")
@Data
@EqualsAndHashCode(callSuper=false, of = {"registrationNumber"})
public class RegistrationCompound extends ChemicalCompound<Batch> {

    @Column(name = "reg_number", unique = true, nullable = false)
    private String registrationNumber;

    public RegistrationCompound() {
        super();
    }

    public RegistrationCompound(Long id, String regNumber, String compoundName,
            String cas, String description,
            List<ChemicalCompoundComposition> composition,
            Set<Batch> containables) {

        super(id, compoundName, cas, description, composition, containables);
        this.registrationNumber = registrationNumber;
    }

    public RegistrationCompound(String registrationNumber, String compoundName,
            String cas, String description,
            List<ChemicalCompoundComposition> composition,
            Set<Batch> containables) {

        this(null, registrationNumber, compoundName, cas, description, composition, containables);
    }

    public List<Batch> getBatches(){
        return new ArrayList<>(super.getContainables());
    }
}

2. Extend Containable

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
import lombok.Data;
import lombok.EqualsAndHashCode;


@Entity
@Table(name="batch", uniqueConstraints=
        @UniqueConstraint(columnNames = {"chemical_compound_id", "batch_Number"}))
@Data
@EqualsAndHashCode(callSuper=true, of = {"batchNumber"})
public class Batch extends Containable<RegistrationCompound>{

    @Column(name = "batch_number")
    private Integer batchNumber;

    public Batch() {
    }

    public Batch(Long id, RegistrationCompound chemicalCompound, Integer batchNumber) {
        super(id,chemicalCompound);
        this.batchNumber = batchNumber;
    }

    public Batch(RegistrationCompound chemicalCompound, Integer batchNumber) {
        super(chemicalCompound);
        this.batchNumber = batchNumber;
    }

    public RegistrationCompound getRegistrationCompound(){
        return super.getChemicalCompound();
    }
}

Note that ChemicalCompound and Containable form a "Pair", meaning ChemicalCompound can only be associated with one specific implementation of Containable and vice-versa.

3. Extend ChemicalCompoundContainer

If you need an entity class that holds 1 reference to a Containable and requires chemical structure search be performed on it, extend ChemicalCompoundContainer. This is optional. Note that for design reasons a ChemicalCompoundContainer holds exactly 1 Containable and that Containable references 1 ChemicalCompound.

import javax.persistence.DiscriminatorValue;
import javax.persistence.Entity;
import lombok.Data;
import lombok.EqualsAndHashCode;


@Entity
@DiscriminatorValue("TestContainer")
@Data
@EqualsAndHashCode(callSuper=true)
public class TestCompoundContainer<T extends Containable> extends ChemicalCompoundContainer<T> {

    public TestCompoundContainer() {
    }

    public TestCompoundContainer(Long id, T containable) {
        super(id, containable);
    }

    public TestCompoundContainer(T containable) {
        super(containable);
    }
}

You can create any custom class that extends Containable and put it into a ChemicalCompoundContainer. That container is then automatically searchable by chemical structure.

4. Create Repository

The framework provides repository classes implementing the chemical structure search. To use this functionality you need to extend the provided interface and class and you may add your custom search methods according to spring data conventions (see Spring Data JPA Reference for further information).

@Repository
public interface RegistrationCompoundRepository extends ChemicalCompoundRepository<RegistrationCompound> {    

    List<RegistrationCompound> findByRegistrationNumberStartingWith(String registrationNumber);

}

Spring Data will automatically create the implementations for your custom search methods.

To enable chemical structure search you need to extend the provided repository implementation. Mind the naming conventions, eg. it is required to name it same as the repository interface + Impl:

public class RegistrationCompoundRepositoryImpl extends ChemicalCompoundRepositoryImpl<RegistrationCompound>{

    public RegistrationCompoundRepositoryImpl() {
        super();
    }

    public RegistrationCompoundRepositoryImpl(EntityManager entityManager,
            CacheManager cacheManager) {
        super(entityManager, cacheManager);
    }
}

5. Create Service

The important part is to always create an interface and implementation and only use the interface in code. This is required due to how Spring works. First create the interface for your service:

public interface RegistrationCompoundService extends ChemicalCompoundService<RegistrationCompound> {

    // Spring security annotation
    @PreAuthorize("hasRole(#root.this.getReadRole())")
    List<RegistrationCompound> findByRegistrationNumberStartingWith(String registrationNumber);
}

Then you need to create an implementation of your service following the convention as shown below. You need to implement the checkUniqueness() and getExistingCompound() methods.

checkUniqueness() must return if a Compound is unique (does not violate any unique constraints) and if it is not unique it must return a Mapping of the violated constraint(all field names of constraint) and the offending value.

getExistingCompound() is called during import of SD-Files. The method must use the data from the SD-File (sdfRecord) to check if the current compound that is being imported already exists in the database. If it already exists it must return it, else it must return null.

For both method the implementation depends on the implementation of ChemicalCompound and especially the unique constraints used in it.

@Service
@Transactional
public class RegistrationCompoundServiceImpl extends ChemicalCompoundServiceImpl<RegistrationCompound>
        implements RegistrationCompoundService {

    @Autowired
    private RegistrationCompoundRepository registrationCompoundRepository;

    public RegistrationCompoundServiceImpl() {
        super(RegistrationCompound.class);
    }

    @Override
    protected ChemicalCompoundRepository<RegistrationCompound> getRepository(){
        return this.registrationCompoundRepository;
    }

    public List<RegistrationCompound> findByRegNumberStartingWith(String regNumber){
        return this.registrationCompoundRepository.findByRegNumberStartingWith(regNumber);
    }  

    @Override
    public UniquenesscheckResult checkUniqueness(RegistrationCompound compound) {
        QRegistrationCompound qCompound = QRegistrationCompound.registrationCompound;
        Predicate predicateReg = qCompound.regNumber.eq(compound.getRegNumber());
        RegistrationCompound existingCompound = getRepository().findOne(predicateReg);

        HashMap<String, Object> fields = new HashMap<>();
        boolean isUnique = true;

        if (existingCompound != null) {
            fields.put("regNumber", compound.getRegNumber());
            isUnique = false;
        }
        return new UniquenesscheckResult(isUnique, fields);
    }

    @Override
    public RegistrationCompound getExistingCompound(SdfRecord sdfRecord,
            Map<String, String> mappedCompoundProperties) {

        String regNumberField = "regNumber";

        if (mappedCompoundProperties == null
                || !mappedCompoundProperties.containsValue(regNumberField)) {
            return null;
        }

        String uniqueKeyRegNumber = null;
        for (String key : mappedCompoundProperties.keySet()) {
            if (mappedCompoundProperties.get(key).equals(regNumberField)) {
                uniqueKeyRegNumber = key;
                break;
            }
        }
        String regNumber = sdfRecord.getProperty(uniqueKeyRegNumber);
        Predicate predicate = QRegistrationCompound.registrationCompound.regNumber.eq(regNumber);
        return findOne(predicate);
    }
}

You now have a service layer for the RegistrationCompound that offers chemical structure search and simple to use, type-safe, transactional search methods!

6. Create Spring Context Configuration

Spring Configuration

7. Create Chemical Structure Index

After you have finished your domain model and generated the according database tables using Hibernate you need to create the Chemical Structure Index or else Chemical Structure Searches will perform poorly. See Bingo PostgreSQL Manual.

8. Create Repository and Service for Containable and ChemicalCompoundContainer

See Creating Repositories and Services for Containable and ChemicalCompoundContainer.

Updated