<commit_before>package uk.ac.ebi.gxa.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import uk.ac.ebi.microarray.atlas.model.DesignElement;
import uk.ac.ebi.microarray.atlas.model.Gene;
import uk.ac.ebi.microarray.atlas.model.Property;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.google.common.base.Joiner.on;
import static com.google.common.collect.Iterables.partition;
import static java.util.Collections.nCopies;

public class OldGeneDAO implements BioEntityDAOInterface {

    public static final String GENES_SELECT =
            "SELECT DISTINCT g.geneid, g.identifier, g.name, s.name AS species " +
                    "FROM a2_gene g, a2_organism s " +
                    "WHERE g.organismid=s.organismid";


    public static final String GENES_BY_EXPERIMENT_ACCESSION =
            "SELECT DISTINCT g.geneid, g.identifier, g.name, s.name AS species " +
                    "FROM a2_gene g, a2_organism s, a2_designelement d, a2_assay a, " +
                    "a2_experiment e " +
                    "WHERE g.geneid=d.geneid " +
                    "AND g.organismid = s.organismid " +
                    "AND d.arraydesignid=a.arraydesignid " +
                    "AND a.experimentid=e.experimentid " +
                    "AND e.accession=?";

    public static final String DESIGN_ELEMENTS_BY_GENEID =
            "SELECT de.designelementid, de.arraydesignid, de.accession, de.name " +
                    "FROM a2_designelement de " +
                    "WHERE de.geneid=?";

    public static final String PROPERTIES_BY_RELATED_GENES =
            "SELECT ggpv.geneid, gp.name AS property, gpv.value AS propertyvalue FROM a2_genepropertyvalue gpv\n" +
                    " JOIN a2_geneproperty gp ON gpv.genepropertyid=gp.genepropertyid\n" +
                    " JOIN a2_genegpv ggpv ON ggpv.genepropertyvalueid = gpv.genepropertyvalueid\n" +
                    "WHERE ggpv.geneid in (:geneids)";

    public static final String GENES_COUNT =
            "SELECT COUNT(*) FROM a2_gene";

    private int maxQueryParams = 10;

    private Logger log = LoggerFactory.getLogger(getClass());
    protected JdbcTemplate template;

    public List<Gene> getAllGenesFast() {
        // do the query to fetch genes without design elements
        return template.query(GENES_SELECT, new GeneMapper());
    }


    public List<Gene> getGenesByExperimentAccession(String exptAccession) {
        // do the first query to fetch genes without design elements
        log.debug("Querying for genes by experiment " + exptAccession);
        List<Gene> results = template.query(GENES_BY_EXPERIMENT_ACCESSION,
                new Object[]{exptAccession},
                new GeneMapper());
        log.debug("Genes for " + exptAccession + " acquired");

        return results;
    }

    public void getPropertiesForGenes(List<Gene> genes) {
        // populate the other info for these genes
        if (genes.size() > 0) {
            fillOutGeneProperties(genes);
        }
    }

    public List<DesignElement> getDesignElementsByGeneID(long geneID) {
        return template.query(DESIGN_ELEMENTS_BY_GENEID,
                new Object[]{geneID},
                new RowMapper<DesignElement>() {
                    public DesignElement mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                        return new DesignElement(
                                rs.getString(3), rs.getString(4));
                    }
                });
    }

    public int getGeneCount() {
        return template.queryForInt(GENES_COUNT);
    }

    public List<String> getSpeciesForExperiment(long experimentId) {
        List<Long> designIds = template.query("select distinct a.arraydesignid from A2_ASSAY a\n" +
                " where a.EXPERIMENTID = ?\n",
                new Object[]{experimentId},
                new SingleColumnRowMapper<Long>());
        return template.query("select distinct o.name from A2_ORGANISM o\n" +
                " join a2_gene g on g.ORGANISMID = o.ORGANISMID\n" +
                " join a2_designelement de on de.geneid = g.geneid\n" +
                " where de.ARRAYDESIGNID in (" + on(",").join(nCopies(designIds.size(), "?")) + ")",
                designIds.toArray(),
                new SingleColumnRowMapper<String>());
    }


    private void fillOutGeneProperties(List<Gene> genes) {
        // map genes to gene id
        Map<Long, Gene> genesByID = new HashMap<Long, Gene>();
        for (Gene gene : genes) {
            // index this assay
            genesByID.put(gene.getGeneID(), gene);
        }

        // map of genes and their properties
        GenePropertyMapper genePropertyMapper = new GenePropertyMapper(genesByID);

        // query template for genes
        NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);

        // if we have more than 'MAX_QUERY_PARAMS' genes, split into smaller queries
        List<Long> geneIDs = new ArrayList<Long>(genesByID.keySet());
        for (List<Long> geneIDsChunk : partition(geneIDs, maxQueryParams)) {
            // now query for properties that map to one of these genes
            MapSqlParameterSource propertyParams = new MapSqlParameterSource();
            propertyParams.addValue("geneids", geneIDsChunk);
            namedTemplate.query(PROPERTIES_BY_RELATED_GENES, propertyParams, genePropertyMapper);
        }
    }

    public void setJdbcTemplate(JdbcTemplate template) {
        this.template = template;
    }

    private static class GeneMapper implements RowMapper<Gene> {
        public Gene mapRow(ResultSet resultSet, int i) throws SQLException {
            Gene gene = new Gene();

            gene.setGeneID(resultSet.getLong(1));
            gene.setIdentifier(resultSet.getString(2));
            gene.setName(resultSet.getString(3));
            gene.setSpecies(resultSet.getString(4));

            return gene;
        }
    }

    private static class GenePropertyMapper implements RowMapper<Property> {
        private Map<Long, Gene> genesByID;

        public GenePropertyMapper(Map<Long, Gene> genesByID) {
            this.genesByID = genesByID;
        }

        public Property mapRow(ResultSet resultSet, int i) throws SQLException {
            Property property = new Property();

            long geneID = resultSet.getLong(1);

            property.setName(resultSet.getString(2).toLowerCase());
            property.setValue(resultSet.getString(3));

            genesByID.get(geneID).addProperty(property);

            return property;
        }
    }

}
<commit_msg>show sample organisms rather than design elements' ones<commit_after>package uk.ac.ebi.gxa.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import uk.ac.ebi.microarray.atlas.model.DesignElement;
import uk.ac.ebi.microarray.atlas.model.Gene;
import uk.ac.ebi.microarray.atlas.model.Property;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.google.common.collect.Iterables.partition;

public class OldGeneDAO implements BioEntityDAOInterface {

    public static final String GENES_SELECT =
            "SELECT DISTINCT g.geneid, g.identifier, g.name, s.name AS species " +
                    "FROM a2_gene g, a2_organism s " +
                    "WHERE g.organismid=s.organismid";


    public static final String GENES_BY_EXPERIMENT_ACCESSION =
            "SELECT DISTINCT g.geneid, g.identifier, g.name, s.name AS species " +
                    "FROM a2_gene g, a2_organism s, a2_designelement d, a2_assay a, " +
                    "a2_experiment e " +
                    "WHERE g.geneid=d.geneid " +
                    "AND g.organismid = s.organismid " +
                    "AND d.arraydesignid=a.arraydesignid " +
                    "AND a.experimentid=e.experimentid " +
                    "AND e.accession=?";

    public static final String DESIGN_ELEMENTS_BY_GENEID =
            "SELECT de.designelementid, de.arraydesignid, de.accession, de.name " +
                    "FROM a2_designelement de " +
                    "WHERE de.geneid=?";

    public static final String PROPERTIES_BY_RELATED_GENES =
            "SELECT ggpv.geneid, gp.name AS property, gpv.value AS propertyvalue FROM a2_genepropertyvalue gpv\n" +
                    " JOIN a2_geneproperty gp ON gpv.genepropertyid=gp.genepropertyid\n" +
                    " JOIN a2_genegpv ggpv ON ggpv.genepropertyvalueid = gpv.genepropertyvalueid\n" +
                    "WHERE ggpv.geneid in (:geneids)";

    public static final String GENES_COUNT =
            "SELECT COUNT(*) FROM a2_gene";

    private Logger log = LoggerFactory.getLogger(getClass());
    protected JdbcTemplate template;

    public List<Gene> getAllGenesFast() {
        // do the query to fetch genes without design elements
        return template.query(GENES_SELECT, new GeneMapper());
    }


    public List<Gene> getGenesByExperimentAccession(String exptAccession) {
        // do the first query to fetch genes without design elements
        log.debug("Querying for genes by experiment " + exptAccession);
        List<Gene> results = template.query(GENES_BY_EXPERIMENT_ACCESSION,
                new Object[]{exptAccession},
                new GeneMapper());
        log.debug("Genes for " + exptAccession + " acquired");

        return results;
    }

    public void getPropertiesForGenes(List<Gene> genes) {
        // populate the other info for these genes
        if (genes.size() > 0) {
            fillOutGeneProperties(genes);
        }
    }

    public List<DesignElement> getDesignElementsByGeneID(long geneID) {
        return template.query(DESIGN_ELEMENTS_BY_GENEID,
                new Object[]{geneID},
                new RowMapper<DesignElement>() {
                    public DesignElement mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                        return new DesignElement(
                                rs.getString(3), rs.getString(4));
                    }
                });
    }

    public int getGeneCount() {
        return template.queryForInt(GENES_COUNT);
    }

    public List<String> getSpeciesForExperiment(long experimentId) {
        return template.query("select distinct o.name from a2_organism o\n" +
                "  join a2_sample s on s.ORGANISMID = o.ORGANISMID\n" +
                "  join A2_ASSAYSAMPLE ass on ass.SAMPLEID = s.SAMPLEID\n" +
                "  join A2_ASSAY a on a.ASSAYID = ass.ASSAYID\n" +
                "where a.EXPERIMENTID = ?",
                new Object[]{experimentId},
                new SingleColumnRowMapper<String>());
    }


    private void fillOutGeneProperties(List<Gene> genes) {
        // map genes to gene id
        Map<Long, Gene> genesByID = new HashMap<Long, Gene>();
        for (Gene gene : genes) {
            // index this assay
            genesByID.put(gene.getGeneID(), gene);
        }

        // map of genes and their properties
        GenePropertyMapper genePropertyMapper = new GenePropertyMapper(genesByID);

        // query template for genes
        NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);

        // if we have more than 'MAX_QUERY_PARAMS' genes, split into smaller queries
        List<Long> geneIDs = new ArrayList<Long>(genesByID.keySet());
        int maxQueryParams = 10;
        for (List<Long> geneIDsChunk : partition(geneIDs, maxQueryParams)) {
            // now query for properties that map to one of these genes
            MapSqlParameterSource propertyParams = new MapSqlParameterSource();
            propertyParams.addValue("geneids", geneIDsChunk);
            namedTemplate.query(PROPERTIES_BY_RELATED_GENES, propertyParams, genePropertyMapper);
        }
    }

    public void setJdbcTemplate(JdbcTemplate template) {
        this.template = template;
    }

    private static class GeneMapper implements RowMapper<Gene> {
        public Gene mapRow(ResultSet resultSet, int i) throws SQLException {
            Gene gene = new Gene();

            gene.setGeneID(resultSet.getLong(1));
            gene.setIdentifier(resultSet.getString(2));
            gene.setName(resultSet.getString(3));
            gene.setSpecies(resultSet.getString(4));

            return gene;
        }
    }

    private static class GenePropertyMapper implements RowMapper<Property> {
        private Map<Long, Gene> genesByID;

        public GenePropertyMapper(Map<Long, Gene> genesByID) {
            this.genesByID = genesByID;
        }

        public Property mapRow(ResultSet resultSet, int i) throws SQLException {
            Property property = new Property();

            long geneID = resultSet.getLong(1);

            property.setName(resultSet.getString(2).toLowerCase());
            property.setValue(resultSet.getString(3));

            genesByID.get(geneID).addProperty(property);

            return property;
        }
    }

}

{'fraction_non_alphanumeric': 0.08972691807542263, 'fraction_numerical': 0.004334633723450368, 'mean_word_length': 2.3705868030192354, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1459418', 'n_tokens_mistral': 4094, 'n_tokens_neox': 3836, 'n_words': 898}