<commit_before>/*******************************************************************************
 * Copyright © 2015 EMBL - European Bioinformatics Institute
 * <p/>
 * Licensed under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License. You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p/>
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific
 * language governing permissions and limitations under the
 * License.
 ******************************************************************************/

package org.mousephenotype.cda.loads.sanitycheck;

import joptsimple.OptionParser;
import joptsimple.OptionSet;
import org.mousephenotype.dcc.exportlibrary.datastructure.core.specimen.*;
import org.mousephenotype.dcc.exportlibrary.xmlserialization.exceptions.XMLloadingException;
import org.mousephenotype.dcc.utils.xml.XMLUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.config.AutowireCapableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.stereotype.Component;

import javax.validation.constraints.NotNull;
import javax.xml.bind.JAXBException;
import java.io.File;
import java.io.IOException;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by mrelac on 09/02/2016.
 * <p/>
 * This class encapsulates the code and data necessary to load the specified target database with the source dcc report
 * files currently found on ebi-00x at /nfs/komp2/web/phenotype_data/impc. This class is meant to be an executable jar
 * whose parameters describe the source location and the target database.
 */
@Component
public class SpecimenLoader {
    /**
     * Load specimen data that was encoded using the IMPC XML format
     */

    // Required by the Harwell DCC export utilities
    public static final String CONTEXT_PATH = "org.mousephenotype.dcc.exportlibrary.datastructure.core.common:org.mousephenotype.dcc.exportlibrary.datastructure.core.procedure:org.mousephenotype.dcc.exportlibrary.datastructure.core.specimen:org.mousephenotype.dcc.exportlibrary.datastructure.tracker.submission:org.mousephenotype.dcc.exportlibrary.datastructure.tracker.validation";
    private static final Logger logger = LoggerFactory.getLogger(SpecimenLoader.class);

    private String filename;
    private String username = "";
    private String password = "";
    private String dbName;

    private Connection connection;

    public static void main(String[] args) throws IOException, NoSuchAlgorithmException, XMLloadingException, KeyManagementException, SQLException, JAXBException, DccLoaderException {

        // Wire up spring support for this application
        SpecimenLoader main = new SpecimenLoader();
        main.initialize(args);
        main.run();

        logger.debug("Process finished.  Exiting.");
    }

    private void initialize(String[] args)
            throws IOException, SQLException, KeyManagementException, NoSuchAlgorithmException {

        OptionParser parser = new OptionParser();

        // parameter to indicate the application context xml file.
        parser.accepts("context").withRequiredArg().ofType(String.class);

        // parameter to indicate the database name
        parser.accepts("dbname").withRequiredArg().ofType(String.class);

        // parameter to indicate the name of the file to process
        parser.accepts("filename").withRequiredArg().ofType(String.class);

        // parameter to indicate the database username
        parser.accepts("username").withRequiredArg().ofType(String.class);

        // parameter to indicate the database password
        parser.accepts("password").withRequiredArg().ofType(String.class);

        OptionSet options = parser.parse(args);

        // Wire up spring support for this application
        ApplicationContext applicationContext;
        String context = (String) options.valuesOf("context").get(0);
        logger.debug("Using application context file {}", context);
        applicationContext = loadApplicationContext((String)options.valuesOf("context").get(0));
        applicationContext.getAutowireCapableBeanFactory().autowireBeanProperties(this, AutowireCapableBeanFactory.AUTOWIRE_BY_TYPE, true);

        dbName = (String) options.valuesOf("dbname").get(0);
        String dbUrl = "jdbc:mysql://mysql-mi-dev:4356/"
                + dbName
                + "?autoReconnect=true&amp;useUnicode=true&amp;connectionCollation=utf8_general_ci&amp;characterEncoding=utf8&amp;characterSetResults=utf8&amp;zeroDateTimeBehavior=convertToNull";

        username = (String) options.valuesOf("username").get(0);
        password = (String) options.valuesOf("password").get(0);
        connection = DriverManager.getConnection(dbUrl, username, password);

        filename = (String) options.valuesOf("filename").get(0);
        logger.info("Loading specimen file {}", filename);
    }

    private void run() throws JAXBException, XMLloadingException, IOException, SQLException, KeyManagementException, NoSuchAlgorithmException, DccLoaderException {
        List<CentreSpecimen> centerSpecimens = XMLUtils.unmarshal(SpecimenLoader.CONTEXT_PATH, CentreSpecimenSet.class, filename).getCentre();

        if (centerSpecimens.size() == 0) {
            logger.error("{} failed to unmarshall", filename);
            throw new XMLloadingException(filename + " failed to unserialize.");
        }

        logger.debug("There are {} center specimen sets in specimen file {}", centerSpecimens.size(), filename);

        PreparedStatement ps;
        String query;
        for (CentreSpecimen centerSpecimen : centerSpecimens) {
            logger.debug("Parsing center {}", centerSpecimen.getCentreID());

            Long centerPk = 0L;
            for (Specimen specimen : centerSpecimen.getMouseOrEmbryo()) {

                connection.setAutoCommit(false);    // BEGIN TRANSACTION

                Long statuscodePk, specimenPk;
                ResultSet rs;

                // center
                query = "";
                ps = connection.prepareStatement("SELECT * FROM center WHERE centerId = ? AND pipeline = ? AND project = ?;");
                ps.setString(1, centerSpecimen.getCentreID().value());
                ps.setString(2, specimen.getPipeline());
                ps.setString(3, specimen.getProject());
                rs = ps.executeQuery();
                if (rs.next()) {
                    centerPk = rs.getLong("pk");
                } else {
                    query = "INSERT INTO center (centerId, pipeline, project) VALUES (?, ?, ?);";
                    ps = connection.prepareStatement(query);
                    ps.setString(1, centerSpecimen.getCentreID().value());
                    ps.setString(2, specimen.getPipeline());
                    ps.setString(3, specimen.getProject());
                    ps.execute();
                    rs = ps.executeQuery("SELECT LAST_INSERT_ID();");
                    rs.next();
                    centerPk = rs.getLong(1);
                }

                // statuscode
                if (specimen.getStatusCode() != null) {
                    query = "INSERT INTO statuscode (dateOfStatuscode, value) VALUES ( ?, ?);";
                    ps = connection.prepareStatement(query);
                    ps.setDate(1, new java.sql.Date(specimen.getStatusCode().getDate().getTime().getTime()));
                    ps.setString(2, specimen.getStatusCode().getValue());
                    ps.execute();
                    rs = ps.executeQuery("SELECT LAST_INSERT_ID();");
                    rs.next();
                    statuscodePk = rs.getLong(1);
                } else {
                    statuscodePk = null;
                }

                // specimen
                query =
                          "SELECT *\n"
                        + "FROM dccimport_2015_12_16.specimen s\n"
                        + "JOIN center_specimen cs ON cs.specimen_fk =  s.pk\n"<commit_msg>Removed database name from query!<commit_after>/*******************************************************************************
 * Copyright © 2015 EMBL - European Bioinformatics Institute
 * <p/>
 * Licensed under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License. You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p/>
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific
 * language governing permissions and limitations under the
 * License.
 ******************************************************************************/

package org.mousephenotype.cda.loads.sanitycheck;

import joptsimple.OptionParser;
import joptsimple.OptionSet;
import org.mousephenotype.dcc.exportlibrary.datastructure.core.specimen.*;
import org.mousephenotype.dcc.exportlibrary.xmlserialization.exceptions.XMLloadingException;
import org.mousephenotype.dcc.utils.xml.XMLUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.config.AutowireCapableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.stereotype.Component;

import javax.validation.constraints.NotNull;
import javax.xml.bind.JAXBException;
import java.io.File;
import java.io.IOException;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by mrelac on 09/02/2016.
 * <p/>
 * This class encapsulates the code and data necessary to load the specified target database with the source dcc report
 * files currently found on ebi-00x at /nfs/komp2/web/phenotype_data/impc. This class is meant to be an executable jar
 * whose parameters describe the source location and the target database.
 */
@Component
public class SpecimenLoader {
    /**
     * Load specimen data that was encoded using the IMPC XML format
     */

    // Required by the Harwell DCC export utilities
    public static final String CONTEXT_PATH = "org.mousephenotype.dcc.exportlibrary.datastructure.core.common:org.mousephenotype.dcc.exportlibrary.datastructure.core.procedure:org.mousephenotype.dcc.exportlibrary.datastructure.core.specimen:org.mousephenotype.dcc.exportlibrary.datastructure.tracker.submission:org.mousephenotype.dcc.exportlibrary.datastructure.tracker.validation";
    private static final Logger logger = LoggerFactory.getLogger(SpecimenLoader.class);

    private String filename;
    private String username = "";
    private String password = "";
    private String dbName;

    private Connection connection;

    public static void main(String[] args) throws IOException, NoSuchAlgorithmException, XMLloadingException, KeyManagementException, SQLException, JAXBException, DccLoaderException {

        // Wire up spring support for this application
        SpecimenLoader main = new SpecimenLoader();
        main.initialize(args);
        main.run();

        logger.debug("Process finished.  Exiting.");
    }

    private void initialize(String[] args)
            throws IOException, SQLException, KeyManagementException, NoSuchAlgorithmException {

        OptionParser parser = new OptionParser();

        // parameter to indicate the application context xml file.
        parser.accepts("context").withRequiredArg().ofType(String.class);

        // parameter to indicate the database name
        parser.accepts("dbname").withRequiredArg().ofType(String.class);

        // parameter to indicate the name of the file to process
        parser.accepts("filename").withRequiredArg().ofType(String.class);

        // parameter to indicate the database username
        parser.accepts("username").withRequiredArg().ofType(String.class);

        // parameter to indicate the database password
        parser.accepts("password").withRequiredArg().ofType(String.class);

        OptionSet options = parser.parse(args);

        // Wire up spring support for this application
        ApplicationContext applicationContext;
        String context = (String) options.valuesOf("context").get(0);
        logger.debug("Using application context file {}", context);
        applicationContext = loadApplicationContext((String)options.valuesOf("context").get(0));
        applicationContext.getAutowireCapableBeanFactory().autowireBeanProperties(this, AutowireCapableBeanFactory.AUTOWIRE_BY_TYPE, true);

        dbName = (String) options.valuesOf("dbname").get(0);
        String dbUrl = "jdbc:mysql://mysql-mi-dev:4356/"
                + dbName
                + "?autoReconnect=true&amp;useUnicode=true&amp;connectionCollation=utf8_general_ci&amp;characterEncoding=utf8&amp;characterSetResults=utf8&amp;zeroDateTimeBehavior=convertToNull";

        username = (String) options.valuesOf("username").get(0);
        password = (String) options.valuesOf("password").get(0);
        connection = DriverManager.getConnection(dbUrl, username, password);

        filename = (String) options.valuesOf("filename").get(0);
        logger.info("Loading specimen file {}", filename);
    }

    private void run() throws JAXBException, XMLloadingException, IOException, SQLException, KeyManagementException, NoSuchAlgorithmException, DccLoaderException {
        List<CentreSpecimen> centerSpecimens = XMLUtils.unmarshal(SpecimenLoader.CONTEXT_PATH, CentreSpecimenSet.class, filename).getCentre();

        if (centerSpecimens.size() == 0) {
            logger.error("{} failed to unmarshall", filename);
            throw new XMLloadingException(filename + " failed to unserialize.");
        }

        logger.debug("There are {} center specimen sets in specimen file {}", centerSpecimens.size(), filename);

        PreparedStatement ps;
        String query;
        for (CentreSpecimen centerSpecimen : centerSpecimens) {
            logger.debug("Parsing center {}", centerSpecimen.getCentreID());

            Long centerPk = 0L;
            for (Specimen specimen : centerSpecimen.getMouseOrEmbryo()) {

                connection.setAutoCommit(false);    // BEGIN TRANSACTION

                Long statuscodePk, specimenPk;
                ResultSet rs;

                // center
                query = "";
                ps = connection.prepareStatement("SELECT * FROM center WHERE centerId = ? AND pipeline = ? AND project = ?;");
                ps.setString(1, centerSpecimen.getCentreID().value());
                ps.setString(2, specimen.getPipeline());
                ps.setString(3, specimen.getProject());
                rs = ps.executeQuery();
                if (rs.next()) {
                    centerPk = rs.getLong("pk");
                } else {
                    query = "INSERT INTO center (centerId, pipeline, project) VALUES (?, ?, ?);";
                    ps = connection.prepareStatement(query);
                    ps.setString(1, centerSpecimen.getCentreID().value());
                    ps.setString(2, specimen.getPipeline());
                    ps.setString(3, specimen.getProject());
                    ps.execute();
                    rs = ps.executeQuery("SELECT LAST_INSERT_ID();");
                    rs.next();
                    centerPk = rs.getLong(1);
                }

                // statuscode
                if (specimen.getStatusCode() != null) {
                    query = "INSERT INTO statuscode (dateOfStatuscode, value) VALUES ( ?, ?);";
                    ps = connection.prepareStatement(query);
                    ps.setDate(1, new java.sql.Date(specimen.getStatusCode().getDate().getTime().getTime()));
                    ps.setString(2, specimen.getStatusCode().getValue());
                    ps.execute();
                    rs = ps.executeQuery("SELECT LAST_INSERT_ID();");
                    rs.next();
                    statuscodePk = rs.getLong(1);
                } else {
                    statuscodePk = null;
                }

                // specimen
                query =
                          "SELECT *\n"
                        + "FROM specimen s\n"
                        + "JOIN center_specimen cs ON cs.specimen_fk =  s.pk\n"
{'fraction_non_alphanumeric': 0.11732841583587852, 'fraction_numerical': 0.005762692329856509, 'mean_word_length': 2.9458844929513415, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 8}, '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': '4267356', 'n_tokens_mistral': 4506, 'n_tokens_neox': 4265, 'n_words': 1053}