/*
 * Copyright (c) 2014, WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
 *
 * WSO2 Inc. licenses this file to you 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
 *
 * 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.wso2.carbon.ml.database.internal;

import org.apache.commons.lang.SerializationUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.wso2.carbon.ml.commons.constants.MLConstants;
import org.wso2.carbon.ml.commons.domain.*;
import org.wso2.carbon.ml.commons.domain.config.MLConfiguration;
import org.wso2.carbon.ml.database.DatabaseService;
import org.wso2.carbon.ml.database.exceptions.DatabaseHandlerException;
import org.wso2.carbon.ml.database.exceptions.MLConfigurationParserException;
import org.wso2.carbon.ml.database.internal.constants.MSSQLQueries;
import org.wso2.carbon.ml.database.internal.constants.SQLQueries;
import org.wso2.carbon.ml.database.internal.ds.LocalDatabaseCreator;
import org.wso2.carbon.ml.database.util.MLDBUtil;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.SortedMap;

public class MLDatabaseService implements DatabaseService {

    private static final Log logger = LogFactory.getLog(MLDatabaseService.class);
    private MLDataSource dbh;
    private MLConfiguration mlConfig;
    private static final String DB_CHECK_SQL = "SELECT * FROM ML_PROJECT";
    private SQLQueries sqlQueries;
    private String databaseName;

    public MLDatabaseService() {

        MLConfigurationParser mlConfigParser = new MLConfigurationParser();
        try {
            mlConfig = mlConfigParser.getMLConfiguration(MLConstants.MACHINE_LEARNER_XML);
        } catch (MLConfigurationParserException e) {
            String msg = "Failed to parse machine-learner.xml file.";
            logger.error(msg, e);
            throw new RuntimeException(msg, e);
        }

        try {
            dbh = new MLDataSource(mlConfig.getDatasourceName());
            databaseName = dbh.getDataSource().getConnection().getMetaData().getDatabaseProductName();
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                sqlQueries = new MSSQLQueries();
            } else {
                sqlQueries = new SQLQueries();
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e.getMessage(), e);
        }

        String value = System.getProperty("setup");
        if (value != null) {
            LocalDatabaseCreator databaseCreator = new LocalDatabaseCreator(dbh.getDataSource());
            try {
                if (!databaseCreator.isDatabaseStructureCreated(DB_CHECK_SQL)) {
                    databaseCreator.createRegistryDatabase();
                } else {
                    logger.info("Machine Learner database already exists. Not creating a new database.");
                }
            } catch (Exception e) {
                String msg = "Error in creating the Machine Learner database";
                throw new RuntimeException(msg, e);
            }
        }


    }

    public MLConfiguration getMlConfiguration() {
        return mlConfig != null ? mlConfig : new MLConfiguration();
    }

    @Override
    public void insertDatasetSchema(MLDataset dataset) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement insertStatement = null;
        try {
            // Insert the data-set details to the database.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            insertStatement = connection.prepareStatement(sqlQueries.INSERT_DATASET_SCHEMA, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            insertStatement.setString(1, dataset.getName());
            insertStatement.setInt(2, dataset.getTenantId());
            insertStatement.setString(3, dataset.getUserName());
            insertStatement.setString(4, dataset.getComments());
            insertStatement.setString(5, dataset.getDataSourceType());
            insertStatement.setString(6, dataset.getDataTargetType());
            insertStatement.setString(7, dataset.getDataType());
            insertStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the details of data set");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting details of dataset "
                    + " to the database: " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }

    @Override
    public void insertDatasetVersion(MLDatasetVersion datasetVersion) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement insertStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            insertStatement = connection.prepareStatement(sqlQueries.INSERT_DATASET_VERSION, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            insertStatement.setLong(1, datasetVersion.getDatasetId());
            insertStatement.setString(2, datasetVersion.getName());
            insertStatement.setString(3, datasetVersion.getVersion());
            insertStatement.setInt(4, datasetVersion.getTenantId());
            insertStatement.setString(5, datasetVersion.getUserName());
            insertStatement.setString(6, datasetVersion.getTargetPath());
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                byte[] byteArray = SerializationUtils.serialize(datasetVersion.getSamplePoints());
                int lengthOfBytes = byteArray.length;
                InputStream samplePointsInputStream = new ByteArrayInputStream(byteArray);
                insertStatement.setBinaryStream(7, samplePointsInputStream, lengthOfBytes);
            } else {
                insertStatement.setObject(7, datasetVersion.getSamplePoints());
            }
            insertStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the value set");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting value set " + " to the database: "
                    + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }

    }

    @Override
    public void insertProject(MLProject project) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement createProjectStatement = null;
        int tenantId = project.getTenantId();
        String userName = project.getUserName();
        String projectName = project.getName();

        if (getProject(tenantId, userName, projectName) != null) {
            throw new DatabaseHandlerException(String.format("Project [name] %s already exists.", projectName));
        }
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            createProjectStatement = connection.prepareStatement(sqlQueries.INSERT_PROJECT, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            createProjectStatement.setString(1, project.getName());
            createProjectStatement.setString(2, project.getDescription());
            createProjectStatement.setLong(3, project.getDatasetId());
            createProjectStatement.setInt(4, project.getTenantId());
            createProjectStatement.setString(5, project.getUserName());
            createProjectStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted details of project: " + project.getName());
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while inserting details of project: " + project.getName()
                    + " to the database: " + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, createProjectStatement);
        }
    }

    @Override
    public void insertAnalysis(MLAnalysis analysis) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement insertStatement = null;
        int tenantId = analysis.getTenantId();
        String userName = analysis.getUserName();
        long projectId = analysis.getProjectId();
        String analysisName = analysis.getName();

        if (getAnalysisOfProject(tenantId, userName, projectId, analysisName) != null) {
            throw new DatabaseHandlerException(String.format(
                    "Analysis [name] %s already exists in project [id] %s.",
                    analysisName, projectId));
        }
        try {
            // Insert the analysis to the database.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            insertStatement = connection.prepareStatement(sqlQueries.INSERT_ANALYSIS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            insertStatement.setLong(1, projectId);
            insertStatement.setString(2, analysisName);
            insertStatement.setInt(3, tenantId);
            insertStatement.setString(4, userName);
            insertStatement.setString(5, analysis.getComments());
            insertStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the analysis");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting analysis " + " to the database: "
                    + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }

    @Override
    public void insertModel(MLModelData model) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement insertStatement = null;
        try {
            // Insert the model to the database
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            insertStatement = connection.prepareStatement(sqlQueries.INSERT_MODEL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            insertStatement.setString(1, model.getName());
            insertStatement.setLong(2, model.getAnalysisId());
            insertStatement.setLong(3, model.getVersionSetId());
            insertStatement.setInt(4, model.getTenantId());
            insertStatement.setString(5, model.getUserName());
            insertStatement.setString(6, model.getStorageType());
            insertStatement.setString(7, model.getStorageDirectory());
            insertStatement.setString(8, model.getStatus());
            insertStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the model");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting model " + " to the database: "
                    + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }

    /**
     * Retrieves the path of the value-set having the given ID, from the database.
     *
     * @param datasetVersionId Unique Identifier of the value-set
     * @return Absolute path of a given value-set
     * @throws DatabaseHandlerException
     */
    public String getDatasetVersionUri(long datasetVersionId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement getStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getStatement = connection.prepareStatement(sqlQueries.GET_DATASET_VERSION_LOCATION, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            getStatement.setLong(1, datasetVersionId);
            result = getStatement.executeQuery();
            if (result.first()) {
                return result.getString(1);
            } else {
                logger.error("Invalid value set ID: " + datasetVersionId);
                throw new DatabaseHandlerException("Invalid value set ID: " + datasetVersionId);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while reading the Value set " + datasetVersionId
                    + " from the database: " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result);
        }
    }

    /**
     * Retrieves the path of the value-set having the given ID, from the database.
     *
     * @param datasetId Unique Identifier of the value-set
     * @return Absolute path of a given value-set
     * @throws DatabaseHandlerException
     */
    public String getDatasetUri(long datasetId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement getStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getStatement = connection.prepareStatement(sqlQueries.GET_DATASET_LOCATION, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            getStatement.setLong(1, datasetId);
            result = getStatement.executeQuery();
            if (result.first()) {
                return result.getString(1);
            } else {
                logger.error("Invalid value set ID: " + datasetId);
                throw new DatabaseHandlerException("Invalid value set ID: " + datasetId);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while reading the Value set " + datasetId
                    + " from the database: " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result);
        }
    }


    @Override
    public long getDatasetId(String datasetName, int tenantId, String userName) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            //statement = connection.prepareStatement(sqlQueries.GET_DATASET_ID);
            statement = connection.prepareStatement(sqlQueries.GET_DATASET_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setString(1, datasetName);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                return result.getLong(1);
            } else {
                return -1;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting dataset name: " + datasetName, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public MLDatasetVersion getVersionSetWithVersion(long datasetId, String version, int tenantId, String userName)
            throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_DATASETVERSION_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, datasetId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            statement.setString(4, version);
            result = statement.executeQuery();
            if (result.first()) {
                MLDatasetVersion versionset = new MLDatasetVersion();
                versionset.setId(result.getLong(1));
                versionset.setName(result.getString(2));
                versionset.setTargetPath(result.getString(3) == null ? null : result.getString(3));
                if (result.getBinaryStream(4) != null) {
                    versionset.setSamplePoints(MLDBUtil.getSamplePointsFromInputStream(result.getBinaryStream(4)));
                }
                versionset.setTenantId(tenantId);
                versionset.setUserName(userName);
                versionset.setVersion(version);
                return versionset;
            } else {
                return null;
            }
        } catch (Exception e) {
            throw new DatabaseHandlerException(
                    String.format(
                            " An error has occurred while extracting dataset version id of [dataset] %s [version] %s",
                            datasetId, version), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public long getVersionsetId(String datasetVersionName, int tenantId, String userName) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_VERSIONSET_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setString(1, datasetVersionName);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                return result.getLong(1);
            } else {
                throw new DatabaseHandlerException("No value-set id associated with dataset-version name: " + datasetVersionName);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting dataset-version name: "
                    + datasetVersionName, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public long getDatasetVersionIdOfModel(long modelId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.SELECT_DATASET_VERSION_ID_OF_MODEL,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, modelId);
            result = statement.executeQuery();
            if (result.first()) {
                return result.getLong(1);
            } else {
                throw new DatabaseHandlerException("No dataset-version id associated with the model id: " + modelId);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting dataset-version for model: "
                    + modelId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Retrieve all versionset of a dataset
     */
    @Override
    public List<MLDatasetVersion> getAllVersionsetsOfDataset(int tenantId, String userName, long datasetId)
            throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLDatasetVersion> versionsets = new ArrayList<MLDatasetVersion>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_VERSIONSETS_OF_DATASET,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, datasetId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLDatasetVersion versionset = new MLDatasetVersion();
                versionset.setId(result.getLong(1));
                versionset.setName(result.getString(2));
                versionset.setVersion(result.getString(3));
                versionset.setTargetPath(result.getString(4));
                if (result.getBinaryStream(5) != null) {
                    SamplePoints samplePoints = MLDBUtil.getSamplePointsFromInputStream(result.getBinaryStream(5));
                    if (samplePoints.isGenerated() == true) {
                        versionset.setSamplePoints(MLDBUtil.getSamplePointsFromInputStream(result.getBinaryStream(5)));
                        versionset.setStatus(MLConstants.DatasetVersionStatus.COMPLETE.getValue());
                    } else {
                        versionset.setStatus(MLConstants.DatasetVersionStatus.FAILED.getValue());
                    }
                } else {
                    versionset.setStatus(MLConstants.DatasetVersionStatus.IN_PROGRESS.getValue());
                }
                versionset.setTenantId(tenantId);
                versionset.setUserName(userName);
                versionsets.add(versionset);
            }
            return versionsets;
        } catch (Exception e) {
            throw new DatabaseHandlerException("An error has occurred while extracting version sets for dataset id: "
                    + datasetId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Retrieve a Versionset from its ID
     */
    @Override
    public MLDatasetVersion getVersionset(int tenantId, String userName, long datasetVersionId)
            throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_VERSIONSET_USING_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, datasetVersionId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.next()) {
                MLDatasetVersion versionset = new MLDatasetVersion();
                versionset.setId(result.getLong(1));
                versionset.setName(result.getString(2));
                versionset.setTargetPath(result.getString(3));
                if (result.getBinaryStream(4) != null) {
                    versionset.setSamplePoints(MLDBUtil.getSamplePointsFromInputStream(result.getBinaryStream(4)));
                }
                versionset.setTenantId(tenantId);
                versionset.setUserName(userName);
                return versionset;
            } else {
                return null;
            }
        } catch (Exception e) {
            throw new DatabaseHandlerException("An error has occurred while extracting dataset-version of id: "
                    + datasetVersionId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Retrieve all datasets
     */
    @Override
    public List<MLDataset> getAllDatasets(int tenantId, String userName) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLDataset> datasets = new ArrayList<MLDataset>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_DATASETS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLDataset dataset = new MLDataset();
                dataset.setId(result.getLong(1));
                dataset.setName(result.getString(2));
                dataset.setComments(MLDatabaseUtils.toString(result.getClob(3)));
                dataset.setDataSourceType(result.getString(4));
                dataset.setDataTargetType(result.getString(5));
                dataset.setDataType(result.getString(6));
                dataset.setTenantId(tenantId);
                dataset.setUserName(userName);
                if (dataset.getId() != 0) {
                    List<MLDatasetVersion> datasetVersions = getAllVersionsetsOfDataset(tenantId, userName, dataset.getId());
                    if (datasetVersions.size() > 0) {
                        String datasetStatus = MLDBUtil.getDatasetStatus(datasetVersions);
                        dataset.setStatus(datasetStatus);
                    }
                }
                datasets.add(dataset);
            }
            return datasets;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting datasets.", e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Retrieve a dataset from ID
     */
    @Override
    public MLDataset getDataset(int tenantId, String userName, long datasetId) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_DATASET_USING_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            statement.setLong(3, datasetId);
            result = statement.executeQuery();
            if (result.first()) {
                MLDataset dataset = new MLDataset();
                dataset.setId(result.getLong(1));
                dataset.setName(result.getString(2));
                dataset.setComments(MLDatabaseUtils.toString(result.getClob(3)));
                dataset.setDataSourceType(result.getString(4));
                dataset.setDataTargetType(result.getString(5));
                dataset.setDataType(result.getString(6));
                dataset.setTenantId(tenantId);
                dataset.setUserName(userName);
                if (dataset.getId() != 0) {
                    List<MLDatasetVersion> datasetVersions = getAllVersionsetsOfDataset(tenantId, userName, dataset.getId());
                    if (datasetVersions.size() > 0) {
                        String datasetStatus = MLDBUtil.getDatasetStatus(datasetVersions);
                        dataset.setStatus(datasetStatus);
                    }
                }
                return dataset;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error has occurred while extracting a dataset with [id] " + datasetId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Retrieve the datasetID of a given version set
     */
    @Override
    public long getDatasetId(long datasetVersionId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_DATASET_ID_FROM_DATASET_VERSION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, datasetVersionId);
            result = statement.executeQuery();
            if (result.first()) {
                return result.getLong(1);
            } else {
                throw new DatabaseHandlerException("No dataset id is associated with dataset version id: "
                        + datasetVersionId);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(
                    " An error has occurred while extracting dataset id for dataset version id: " + datasetVersionId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public String getDataTypeOfModel(long modelId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_DATA_TYPE_OF_MODEL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, modelId);
            result = statement.executeQuery();
            if (result.first()) {
                return result.getString(1);
            } else {
                throw new DatabaseHandlerException("No data type is associated with model id: " + modelId);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting data type for model id: "
                    + modelId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }


    /**
     * Update the model summary
     */
    @Override
    public void updateModelSummary(long modelId, ModelSummary modelSummary) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement updateStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            updateStatement = connection.prepareStatement(sqlQueries.UPDATE_MODEL_SUMMARY, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                byte[] byteArray = SerializationUtils.serialize(modelSummary);
                int lengthOfBytes = byteArray.length;
                InputStream samplePointsInputStream = new ByteArrayInputStream(byteArray);
                updateStatement.setBinaryStream(1, samplePointsInputStream, lengthOfBytes);
            } else {
                updateStatement.setObject(1, modelSummary);
            }
            updateStatement.setLong(2, modelId);
            updateStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully updated the model summary of model: " + modelId);
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while updating the model summary " + "of model "
                    + modelId + ": " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, updateStatement);
        }
    }

    /**
     * Retrieve the model summary
     */
    @Override
    public ModelSummary getModelSummary(long modelId) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement getStatement = null;
        ResultSet result = null;
        try {
            connection = dbh.getDataSource().getConnection();
            getStatement = connection.prepareStatement(sqlQueries.GET_MODEL_SUMMARY, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            getStatement.setLong(1, modelId);
            result = getStatement.executeQuery();
            if (result.first() && result.getBinaryStream(1) != null) {
                return MLDBUtil.getModelSummaryFromInputStream(result.getBinaryStream(1));
            } else {
                throw new DatabaseHandlerException("Summary not available for model: " + modelId);
            }
        } catch (Exception e) {
            throw new DatabaseHandlerException("An error occurred while retrieving the summary " + "of model " +
                    modelId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getStatement);
        }
    }

    /**
     * Update the model storage
     */
    @Override
    public void updateModelStorage(long modelId, String storageType, String location) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement updateStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            updateStatement = connection.prepareStatement(sqlQueries.UPDATE_MODEL_STORAGE, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            updateStatement.setString(1, storageType);
            updateStatement.setString(2, location);
            updateStatement.setString(3, MLConstants.MODEL_STATUS_COMPLETE);
            updateStatement.setLong(4, modelId);
            updateStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully updated the model storage of model: " + modelId);
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while updating the model storage " + "of model "
                    + modelId + ": " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, updateStatement);
        }
    }

    /**
     * Update the model status
     */
    @Override
    public void updateModelStatus(long modelId, String status) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement updateStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            updateStatement = connection.prepareStatement(sqlQueries.UPDATE_MODEL_STATUS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            updateStatement.setString(1, status);
            updateStatement.setLong(2, modelId);
            updateStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully updated the status of model: " + modelId);
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while updating the status" + "of model "
                    + modelId + ": " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, updateStatement);
        }
    }

    /**
     * Update the model storage
     */
    @Override
    public void updateModelError(long modelId, String error) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement updateStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            updateStatement = connection.prepareStatement(sqlQueries.UPDATE_MODEL_ERROR, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            updateStatement.setString(1, error);
            updateStatement.setLong(2, modelId);
            updateStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully updated the error of model: " + modelId);
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while updating the error " + "of model "
                    + modelId + ": " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, updateStatement);
        }
    }

    /**
     * Returns data points of the selected sample as coordinates of three features, needed for the scatter plot.
     *
     * @return A JSON array of data points
     * @throws DatabaseHandlerException
     */
    public List<Object> getScatterPlotPoints(ScatterPlotPoints scatterPlotPoints) throws DatabaseHandlerException {

        // Get the sample from the database.
        SamplePoints sample = getVersionsetSample(scatterPlotPoints.getTenantId(), scatterPlotPoints.getUser(),
                scatterPlotPoints.getVersionsetId());
        List<Object> points = new ArrayList<Object>();

        // Converts the sample to a JSON array.
        List<List<String>> columnData = sample.getSamplePoints();
        Map<String, Integer> dataHeaders = sample.getHeader();
        int firstFeatureColumn = dataHeaders.get(scatterPlotPoints.getxAxisFeature());
        int secondFeatureColumn = dataHeaders.get(scatterPlotPoints.getyAxisFeature());
        int thirdFeatureColumn = dataHeaders.get(scatterPlotPoints.getGroupByFeature());
        for (int row = 0; row < columnData.get(thirdFeatureColumn).size(); row++) {
            if (columnData.get(firstFeatureColumn).get(row) != null
                    && columnData.get(secondFeatureColumn).get(row) != null
                    && columnData.get(thirdFeatureColumn).get(row) != null
                    && !columnData.get(firstFeatureColumn).get(row).isEmpty()
                    && !columnData.get(secondFeatureColumn).get(row).isEmpty()
                    && !columnData.get(thirdFeatureColumn).get(row).isEmpty()) {
                Map<Double, Object> map1 = new HashMap<Double, Object>();
                Map<Double, Object> map2 = new HashMap<Double, Object>();
                String val1 = columnData.get(secondFeatureColumn).get(row);
                String val2 = columnData.get(firstFeatureColumn).get(row);
                if (NumberUtils.isNumber(val1) && NumberUtils.isNumber(val2)) {
                    map2.put(Double.parseDouble(val1), columnData.get(thirdFeatureColumn).get(row));
                    map1.put(Double.parseDouble(val2), map2);
                    points.add(map1);
                }
            }
        }

        return points;
    }

    /**
     * Returns sample data for selected features
     *
     * @param versionsetId      Unique Identifier of the value-set
     * @param featureListString String containing feature name list
     * @return A JSON array of data points
     * @throws DatabaseHandlerException
     */
    public List<Object> getChartSamplePoints(int tenantId, String user, long versionsetId, String featureListString)
            throws DatabaseHandlerException {

        List<Object> points = new ArrayList<Object>();

        // Get the sample from the database.
        SamplePoints sample = getVersionsetSample(tenantId, user, versionsetId);

        if (sample == null) {
            return points;
        }
        // Converts the sample to a JSON array.
        List<List<String>> columnData = sample.getSamplePoints();
        Map<String, Integer> dataHeaders = sample.getHeader();

        if (featureListString == null || featureListString.isEmpty()) {
            return points;
        }

        // split categoricalFeatureListString String into a String array
        String[] featureList = featureListString.split(",");

        // Check whether features exists
        for (String feature : featureList) {
            if (!dataHeaders.containsKey(feature)) {
                throw new DatabaseHandlerException(String.format("%s is not a feature of version set Id: %s",
                        feature, versionsetId));
            }
        }

        // for each row in a selected categorical feature, iterate through all features
        for (int row = 0; row < columnData.get(dataHeaders.get(featureList[0])).size(); row++) {

            Map<String, Object> data = new HashMap<String, Object>();

            // for each categorical feature in same row put value into a point(JSONObject)
            // {"Soil_Type1":"0","Soil_Type11":"0","Soil_Type10":"0","Cover_Type":"4"}
            for (int featureCount = 0; featureCount < featureList.length; featureCount++) {
                data.put(featureList[featureCount], columnData.get(dataHeaders.get(featureList[featureCount]))
                        .get(row));
            }

            points.add(data);
        }
        return points;
    }

    /**
     * Retrieve the SamplePoints object for a given value-set.
     *
     * @param tenantId     Tenant id
     * @param user         Tenant user name
     * @param versionsetId Unique Identifier of the dataset version
     * @return SamplePoints object of the value-set
     * @throws DatabaseHandlerException
     */
    public SamplePoints getVersionsetSample(int tenantId, String user, long versionsetId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement updateStatement = null;
        ResultSet result = null;
        SamplePoints samplePoints = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            updateStatement = connection.prepareStatement(sqlQueries.GET_SAMPLE_POINTS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            updateStatement.setLong(1, versionsetId);
            updateStatement.setInt(2, tenantId);
            updateStatement.setString(3, user);
            result = updateStatement.executeQuery();
            if (result.first() && result.getBinaryStream(1) != null) {
                samplePoints = MLDBUtil.getSamplePointsFromInputStream(result.getBinaryStream(1));
            }
            return samplePoints;
        } catch (Exception e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while retrieving the sample of " + " dataset version "
                    + versionsetId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, updateStatement, result);
        }
    }

    /**
     * Returns a set of features in a given range, from the alphabetically ordered set of features, of a data-set.
     *
     * @param tenantId         Tenant id
     * @param userName         Tenant user name
     * @param analysisId       Unique id of the analysis
     * @param startIndex       Starting index of the set of features needed
     * @param numberOfFeatures Number of features needed, from the starting index
     * @return A list of Feature objects
     * @throws DatabaseHandlerException
     */
    public List<FeatureSummary> getFeatures(int tenantId, String userName, long analysisId, int startIndex,
                                            int numberOfFeatures)
            throws DatabaseHandlerException {
        List<FeatureSummary> features = new ArrayList<FeatureSummary>();
        Connection connection = null;
        PreparedStatement getFeatues = null;
        ResultSet result = null;

        long datasetSchemaId = getDatasetSchemaIdFromAnalysisId(analysisId);

        try {
            // Create a prepared statement and retrieve data-set configurations.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getFeatues = connection.prepareStatement(sqlQueries.GET_FEATURES, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            getFeatues.setLong(1, analysisId);
            getFeatues.setInt(2, tenantId);
            getFeatues.setString(3, userName);
            getFeatues.setLong(4, datasetSchemaId);

            // startIndex and numberOfFeatures are not used. This change is required by kernel 4.4.9

            result = getFeatues.executeQuery();
            while (result.next()) {
                String featureType = FeatureType.NUMERICAL;
                if (FeatureType.CATEGORICAL.toString().equalsIgnoreCase(result.getString(4))) {
                    featureType = FeatureType.CATEGORICAL;
                }
                // Set the impute option
                String imputeOperation = ImputeOption.DISCARD;
                if (ImputeOption.REPLACE_WTH_MEAN.equalsIgnoreCase(result.getString(5))) {
                    imputeOperation = ImputeOption.REPLACE_WTH_MEAN;
                } else if (ImputeOption.REGRESSION_IMPUTATION.equalsIgnoreCase(result.getString(5))) {
                    imputeOperation = ImputeOption.REGRESSION_IMPUTATION;
                }
                String featureName = result.getString(2);
                boolean isImportantFeature = result.getBoolean(3);
                String summaryStat = result.getString(6);
                int index = result.getInt(1);

                features.add(new FeatureSummary(featureName, isImportantFeature, featureType, imputeOperation,
                        summaryStat, index));
            }
            return features;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving features of " + "the data set: "
                    + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatues, result);
        }
    }

    /**
     * Returns the customized set of features of an analysis in a given range, from the alphabetically ordered set
     * of features, of a dataset.
     *
     * @param tenantId         ID of the tenant
     * @param userName         Username of the tenant
     * @param analysisId       Unique ID of the analysis
     * @param startIndex       Starting index of the set of features needed
     * @param numberOfFeatures Number of features needed, from the starting index
     * @return A list of feature configuration objects
     * @throws DatabaseHandlerException
     */
    public List<MLCustomizedFeature> getCustomizedFeatures(int tenantId, String userName, long analysisId,
                                                           int startIndex, int numberOfFeatures)
            throws DatabaseHandlerException {
        List<MLCustomizedFeature> mlCustomizedFeatures = new ArrayList<MLCustomizedFeature>();
        Connection connection = null;
        PreparedStatement getCustomizedFeatures = null;
        ResultSet result = null;

        try {
            // Create a prepared statement and retrieve dataset configurations.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getCustomizedFeatures = connection.prepareStatement(sqlQueries.GET_CUSTOMIZED_FEATURES,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                getCustomizedFeatures.setInt(1, numberOfFeatures);
                getCustomizedFeatures.setLong(2, analysisId);
                getCustomizedFeatures.setInt(3, startIndex);
            } else {
                getCustomizedFeatures.setLong(1, analysisId);
                getCustomizedFeatures.setInt(2, numberOfFeatures);
                getCustomizedFeatures.setInt(3, startIndex);
            }

            result = getCustomizedFeatures.executeQuery();
            while (result.next()) {
                MLCustomizedFeature mlCustomizedFeature = new MLCustomizedFeature();
                mlCustomizedFeature.setName(result.getString(1));
                String featureType = FeatureType.NUMERICAL;
                if (FeatureType.CATEGORICAL.toString().equalsIgnoreCase(result.getString(3))) {
                    featureType = FeatureType.CATEGORICAL;
                }
                mlCustomizedFeature.setType(featureType);
                String imputeOption = ImputeOption.DISCARD;
                if (ImputeOption.REPLACE_WTH_MEAN.equalsIgnoreCase(result.getString(4))) {
                    imputeOption = ImputeOption.REPLACE_WTH_MEAN;
                } else if (ImputeOption.REGRESSION_IMPUTATION.equalsIgnoreCase(result.getString(4))) {
                    imputeOption = ImputeOption.REGRESSION_IMPUTATION;
                }
                mlCustomizedFeature.setImputeOption(imputeOption);
                mlCustomizedFeature.setInclude(result.getBoolean(5));
                mlCustomizedFeature.setLastModifiedUser(result.getString(6));
                mlCustomizedFeature.setTenantId(tenantId);
                mlCustomizedFeature.setUserName(userName);

                mlCustomizedFeatures.add(mlCustomizedFeature);
            }
            return mlCustomizedFeatures;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving customized features of the analysis: "
                    + analysisId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getCustomizedFeatures, result);
        }
    }

    /**
     * Get feature names in order and separated by the given column separator.
     */
    @Override
    public String getFeatureNamesInOrderUsingDatasetVersion(long datasetVersionId, String columnSeparator)
            throws DatabaseHandlerException {

        //Connection connection = null;
        //PreparedStatement getFeatureNamesStatement = null;
        //ResultSet result = null;

        long datasetId = getDatasetId(datasetVersionId);
        try {
            return getFeatureNamesInOrder(datasetId, columnSeparator);
        } catch (DatabaseHandlerException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving feature "
                    + "names of the dataset of a dataset version: " + datasetVersionId + ": " + e.getMessage(), e);
            //} finally {
            // Close the database resources.
            //MLDatabaseUtils.closeDatabaseResources(connection, getFeatureNamesStatement, result);
        }
    }

    /**
     * Get feature names in order and separated by the given column separator.
     */
    @Override
    public String getFeatureNamesInOrder(long datasetId, String columnSeparator) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement getFeatureNamesStatement = null;
        ResultSet result = null;
        StringBuilder headerRow = new StringBuilder();

        try {
            connection = dbh.getDataSource().getConnection();

            // Create a prepared statement and retrieve model configurations
            getFeatureNamesStatement = connection.prepareStatement(sqlQueries.GET_FEATURE_NAMES_IN_ORDER,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getFeatureNamesStatement.setLong(1, datasetId);

            result = getFeatureNamesStatement.executeQuery();
            // Convert the result in to a string array to e returned.
            while (result.next()) {
                headerRow.append(result.getString(1));
                if (!result.isLast()) {
                    headerRow.append(columnSeparator);
                }
            }
            return headerRow.toString();
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving feature "
                    + "names of the dataset : " + datasetId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatureNamesStatement, result);
        }
    }

    /**
     * Get feature names of a given dataset using the dataset ID
     */
    @Override
    public List<String> getFeatureNames(long datasetId) throws DatabaseHandlerException {

        List<String> featureNames = new ArrayList<String>();

        Connection connection = null;
        PreparedStatement getFeatureNamesStatement = null;
        ResultSet result = null;

        try {
            connection = dbh.getDataSource().getConnection();

            // Create a prepared statement and retrieve model configurations
            getFeatureNamesStatement = connection.prepareStatement(sqlQueries.GET_FEATURE_NAMES_IN_ORDER,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getFeatureNamesStatement.setLong(1, datasetId);

            result = getFeatureNamesStatement.executeQuery();
            // Convert the result in to a string array to e returned.
            while (result.next()) {
                featureNames.add(result.getString(1));
            }
            return featureNames;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving feature "
                    + "names of the dataset : " + datasetId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatureNamesStatement, result);
        }
    }


    /**
     * Returns the names of the features, belongs to a particular type
     * (Categorical/Numerical), of the analysis.
     *
     * @param analysisId  Unique identifier of the current analysis
     * @param featureType Type of the feature
     * @return A list of feature names
     * @throws DatabaseHandlerException
     */
    public List<String> getFeatureNames(String analysisId, String featureType)
            throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement getFeatureNamesStatement = null;
        ResultSet result = null;
        List<String> featureNames = new ArrayList<String>();
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            // Create a prepared statement and retrieve data-set configurations.
            getFeatureNamesStatement = connection.prepareStatement(sqlQueries.GET_FILTERED_FEATURE_NAMES,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getFeatureNamesStatement.setString(1, analysisId);
            getFeatureNamesStatement.setString(2, featureType);

            result = getFeatureNamesStatement.executeQuery();
            // Convert the result in to a string array to e returned.
            while (result.next()) {
                featureNames.add(result.getString(1));
            }
            return featureNames;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving feature " +
                    "names of the dataset for analysis: " + analysisId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatureNamesStatement, result);
        }
    }

    /**
     * Returns all the feature names of an analysis.
     *
     * @param analysisId Unique identifier of the current analysis
     * @return A list of feature names
     * @throws DatabaseHandlerException
     */
    public List<String> getFeatureNames(String analysisId)
            throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement getFeatureNamesStatement = null;
        ResultSet result = null;
        List<String> featureNames = new ArrayList<String>();
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            // Create a prepared statement and retrieve data-set configurations.
            getFeatureNamesStatement = connection.prepareStatement(sqlQueries.GET_ALL_FEATURE_NAMES,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getFeatureNamesStatement.setString(1, analysisId);

            result = getFeatureNamesStatement.executeQuery();
            // Convert the result in to a string array to e returned.
            while (result.next()) {
                featureNames.add(result.getString(1));
            }
            return featureNames;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving feature " +
                    "names of the dataset for analysis: " + analysisId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatureNamesStatement, result);
        }
    }

    /**
     * Returns the names of the features, belongs to a particular type
     * (Categorical/Numerical), of a dataset.
     *
     * @param datasetId   Unique identifier of a dataset
     * @param featureType Type of the feature
     * @return A list of feature names
     * @throws DatabaseHandlerException
     */
    public List<String> getFeatureNames(long datasetId, String featureType)
            throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement getFeatureNamesStatement = null;
        ResultSet result = null;
        List<String> featureNames = new ArrayList<String>();
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            // Create a prepared statement and retrieve dataset configurations.
            getFeatureNamesStatement = connection.prepareStatement(sqlQueries.GET_FILTERED_FEATURE_NAMES_OF_DATASET,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getFeatureNamesStatement.setLong(1, datasetId);
            getFeatureNamesStatement.setString(2, featureType);

            result = getFeatureNamesStatement.executeQuery();
            // Convert the result in to a string array to be returned.
            while (result.next()) {
                featureNames.add(result.getString(1));
            }
            return featureNames;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving feature " +
                    "feature names of the dataset: " + datasetId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatureNamesStatement, result);
        }
    }

    /**
     * Retrieve and returns the Summary statistics for a given feature of a given data-set version, from the database.
     *
     * @param tenantId    Tenant id
     * @param user        Tenant user name
     * @param analysisId  Unique identifier of the analysis
     * @param featureName Name of the feature of which summary statistics are needed
     * @return JSON string containing the summary statistics
     * @throws DatabaseHandlerException
     */
    @Override
    public String getSummaryStats(int tenantId, String user, long analysisId, String featureName) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement getSummaryStatement = null;
        ResultSet result = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getSummaryStatement = connection.prepareStatement(sqlQueries.GET_SUMMARY_STATS,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getSummaryStatement.setLong(1, analysisId);
            getSummaryStatement.setString(2, featureName);
            getSummaryStatement.setInt(3, tenantId);
            getSummaryStatement.setString(4, user);
            result = getSummaryStatement.executeQuery();
            if (result.first()) {
                return result.getString(1);
            } else {
                return "";
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving summary "
                    + "statistics for the feature \"" + featureName + "\" of the analysis " + analysisId
                    + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, getSummaryStatement, result);
        }
    }

    /**
     * Retrieve and returns the Summary statistics for a given feature of a given data-set version, from the database.
     *
     * @param datasetVersionId Unique identifier of the data-set version
     * @return Map; key - feature name : value - stats
     * @throws DatabaseHandlerException
     */
    @Override
    public Map<String, String> getSummaryStats(long datasetVersionId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement getSummaryStatement = null;
        ResultSet result = null;
        Map<String, String> summaryStatsOfFeatures = new HashMap<String, String>();
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getSummaryStatement = connection.prepareStatement(sqlQueries.GET_SUMMARY_STATS_OF_VERSION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getSummaryStatement.setLong(1, datasetVersionId);
            result = getSummaryStatement.executeQuery();
            while (result.next()) {
                summaryStatsOfFeatures.put(result.getString(1), result.getString(2));
            }

            return summaryStatsOfFeatures;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving summary "
                    + "statistics for the dataset version: " + datasetVersionId
                    + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, getSummaryStatement, result);
        }
    }

    /**
     * Retrieve and returns summary statistics for a given feature of a given dataset
     *
     * @param datasetId   Unique identifier of a dataset
     * @param featureName Name of the feature of which summary statistics are needed
     * @return JSON string containing summary statistics
     * @throws DatabaseHandlerException
     */
    @Override
    public String getSummaryStats(long datasetId, String featureName) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement getSummaryStatement = null;
        ResultSet result = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getSummaryStatement = connection.prepareStatement(sqlQueries.GET_SUMMARY_STATS_OF_DATASET,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getSummaryStatement.setLong(1, datasetId);
            getSummaryStatement.setString(2, featureName);
            result = getSummaryStatement.executeQuery();
            result.first();
            return result.getString(1);
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving summary "
                    + "statistics for the dataset: " + datasetId
                    + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, getSummaryStatement, result);
        }
    }

    /**
     * Returns the number of features of a given data-set version
     *
     * @param datasetSchemaId Unique identifier of the data-set version
     * @return Number of features in the data-set version
     * @throws DatabaseHandlerException
     */
    public int getFeatureCount(long datasetSchemaId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement getFeatues = null;
        ResultSet result = null;
        int featureCount = 0;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            // Create a prepared statement and extract data-set configurations.
            getFeatues = connection.prepareStatement(sqlQueries.GET_FEATURE_COUNT, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            getFeatues.setLong(1, datasetSchemaId);
            result = getFeatues.executeQuery();
            if (result.first()) {
                featureCount = result.getInt(1);
            }
            return featureCount;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(
                    "An error occurred while retrieving feature count of the dataset " + datasetSchemaId
                            + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatues, result);
        }
    }

    @Override
    public MLProject getProject(int tenantId, String userName, String projectName) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_PROJECT, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setString(1, projectName);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                MLProject project = new MLProject();
                project.setName(projectName);
                project.setId(result.getLong(1));
                project.setDescription(result.getString(2));
                project.setDatasetId(result.getLong(3));
                project.setTenantId(tenantId);
                project.setUserName(userName);
                project.setCreatedTime(result.getString(4));
                if (project.getDatasetId() != 0) {
                    MLDataset dataset = getDataset(tenantId, userName, project.getDatasetId());
                    project.setDatasetName(dataset.getName());
                }
                return project;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting project for project name:"
                    + projectName, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Returns project object for a given project ID from the database.
     *
     * @param tenantId  ID of the tenant
     * @param userName  Username of the tenant
     * @param projectId ID of the project
     * @return MLProject object
     * @throws DatabaseHandlerException
     */
    @Override
    public MLProject getProject(int tenantId, String userName, long projectId) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_PROJECT_BY_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, projectId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                MLProject project = new MLProject();
                project.setId(result.getLong(1));
                project.setName(result.getString(2));
                project.setDescription(result.getString(3));
                project.setDatasetId(result.getLong(4));
                project.setCreatedTime(result.getString(5));
                project.setTenantId(tenantId);
                project.setUserName(userName);
                if (project.getDatasetId() != 0) {
                    MLDataset dataset = getDataset(tenantId, userName, project.getDatasetId());
                    project.setDatasetName(dataset.getName());
                }
                return project;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error has occurred while extracting project for project ID:"
                    + projectId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public List<MLProject> getAllProjects(int tenantId, String userName) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLProject> projects = new ArrayList<MLProject>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_PROJECTS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLProject project = new MLProject();
                project.setName(result.getString(1));
                project.setId(result.getLong(2));
                project.setDescription(result.getString(3));
                project.setTenantId(tenantId);
                project.setUserName(userName);
                project.setDatasetId(result.getLong(4));
                project.setCreatedTime(result.getString(5));
                if (project.getDatasetId() != 0) {
                    MLDataset dataset = getDataset(tenantId, userName, project.getDatasetId());
                    project.setDatasetName(dataset.getName());
                    List<MLDatasetVersion> datasetVersions = getAllVersionsetsOfDataset(tenantId, userName, dataset.getId());
                    if (datasetVersions.size() > 0) {
                        String datasetStatus = MLDBUtil.getDatasetStatus(datasetVersions);
                        project.setDatasetStatus(datasetStatus);
                    }
                }
                projects.add(project);
            }
            return projects;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting all projects.", e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    /**
     * Get all models of a given project
     *
     * @param tenantId  tenant ID
     * @param userName  username
     * @param projectId Project ID
     * @return List of {@link org.wso2.carbon.ml.commons.domain.MLModelData} objects
     * @throws DatabaseHandlerException
     */
    @Override
    public List<MLModelData> getProjectModels(int tenantId, String userName, long projectId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLModelData> models = new ArrayList<MLModelData>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_PROJECT_MODELS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, projectId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLModelData model = new MLModelData();
                model.setId(result.getLong(1));
                model.setName(result.getString(2));
                model.setAnalysisId(result.getLong(3));
                model.setVersionSetId(result.getLong(4));
                model.setCreatedTime(result.getString(5));
                ModelSummary modelSummary = null;
                if (result.getBinaryStream(6) != null) {
                    modelSummary = MLDBUtil.getModelSummaryFromInputStream(result.getBinaryStream(6));
                }
                model.setModelSummary(modelSummary);
                model.setStorageType(result.getString(7));
                model.setStorageDirectory(result.getString(8));
                model.setTenantId(tenantId);
                model.setUserName(userName);
                model.setStatus(result.getString(9));
                model.setError(result.getString(10));
                models.add(model);
            }
            return models;
        } catch (Exception e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting all models of"
                    + "project ID:" + projectId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public void deleteProject(int tenantId, String userName, long projectId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_PROJECT, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, projectId);
            preparedStatement.setInt(2, tenantId);
            preparedStatement.setString(3, userName);
            preparedStatement.execute();
            connection.commit();
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                deleteProjectIdDependencies(projectId);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the project: " + projectId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the project: " + projectId + ": "
                    + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    @Override
    public void deleteModel(int tenantId, String userName, long modelId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_MODEL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, modelId);
            preparedStatement.setInt(2, tenantId);
            preparedStatement.setString(3, userName);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the model [id]: " + modelId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the model [id] : " + modelId + ": "
                    + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    @Override
    public MLAnalysis getAnalysis(int tenantId, String userName, long analysisId) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ANALYSIS_BY_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, analysisId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                MLAnalysis analysis = new MLAnalysis();
                analysis.setId(analysisId);
                analysis.setName(result.getString(1));
                analysis.setProjectId(result.getLong(2));
                analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
                analysis.setTenantId(tenantId);
                analysis.setUserName(userName);
                return analysis;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while retrieving analysis with Id: "
                    + analysisId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public List<MLAnalysis> getAllAnalyses(int tenantId, String userName) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLAnalysis> analyses = new ArrayList<MLAnalysis>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_ANALYSES, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLAnalysis analysis = new MLAnalysis();
                analysis.setId(result.getLong(1));
                analysis.setProjectId(result.getLong(2));
                analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
                analysis.setName(result.getString(4));
                analysis.setTenantId(tenantId);
                analysis.setUserName(userName);
                analyses.add(analysis);
            }
            return analyses;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting analyses.", e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public List<MLAnalysis> getAllAnalysesOfProject(int tenantId, String userName, long projectId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLAnalysis> analyses = new ArrayList<MLAnalysis>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_ANALYSES_OF_PROJECT,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            statement.setLong(3, projectId);
            result = statement.executeQuery();
            while (result.next()) {
                MLAnalysis analysis = new MLAnalysis();
                analysis.setId(result.getLong(1));
                analysis.setProjectId(result.getLong(2));
                analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
                analysis.setName(result.getString(4));
                analysis.setTenantId(tenantId);
                analysis.setUserName(userName);
                analyses.add(analysis);
            }
            return analyses;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting analyses for project id: " + projectId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public MLModelData getModel(int tenantId, String userName, String modelName) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ML_MODEL, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setString(1, modelName);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                MLModelData model = new MLModelData();
                model.setId(result.getLong(1));
                model.setAnalysisId(result.getLong(2));
                model.setVersionSetId(result.getLong(3));
                model.setCreatedTime(result.getString(4));
                model.setStorageType(result.getString(5));
                model.setStorageDirectory(result.getString(6));
                model.setName(modelName);
                model.setTenantId(tenantId);
                model.setUserName(userName);
                model.setStatus(result.getString(7));
                return model;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting the model with model name: "
                    + modelName, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public MLModelData getModel(int tenantId, String userName, long modelId) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ML_MODEL_FROM_ID, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, modelId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                MLModelData model = new MLModelData();
                model.setId(modelId);
                model.setName(result.getString(1));
                model.setAnalysisId(result.getLong(2));
                model.setVersionSetId(result.getLong(3));
                model.setCreatedTime(result.getString(4));
                model.setStorageType(result.getString(5));
                model.setStorageDirectory(result.getString(6));
                model.setTenantId(tenantId);
                model.setUserName(userName);
                model.setStatus(result.getString(7));
                return model;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting the model with model id: "
                    + modelId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public List<MLModelData> getAllModels(int tenantId, String userName, long analysisId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLModelData> models = new ArrayList<MLModelData>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_ML_MODELS_OF_ANALYSIS,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, analysisId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLModelData model = new MLModelData();
                model.setId(result.getLong(1));
                model.setAnalysisId(result.getLong(2));
                model.setVersionSetId(result.getLong(3));
                model.setCreatedTime(result.getString(4));
                model.setStorageType(result.getString(5));
                model.setStorageDirectory(result.getString(6));
                model.setName(result.getString(7));
                model.setTenantId(tenantId);
                model.setUserName(userName);
                model.setStatus(result.getString(8));
                model.setError(result.getString(9));
                ModelSummary modelSummary = null;
                if (result.getBinaryStream(10) != null) {
                    modelSummary = MLDBUtil.getModelSummaryFromInputStream(result.getBinaryStream(10));
                }
                model.setModelSummary(modelSummary);
                models.add(model);
            }
            return models;
        } catch (Exception e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting all the models of analysis " +
                    "id: " + analysisId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public List<MLModelData> getAllModels(int tenantId, String userName) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        List<MLModelData> models = new ArrayList<MLModelData>();
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ALL_ML_MODELS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            result = statement.executeQuery();
            while (result.next()) {
                MLModelData model = new MLModelData();
                model.setId(result.getLong(1));
                model.setAnalysisId(result.getLong(2));
                model.setVersionSetId(result.getLong(3));
                model.setCreatedTime(result.getString(4));
                model.setStorageType(result.getString(5));
                model.setStorageDirectory(result.getString(6));
                model.setName(result.getString(7));
                model.setTenantId(tenantId);
                model.setUserName(userName);
                model.setStatus(result.getString(8));
                models.add(model);
            }
            return models;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting all the models.", e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public MLStorage getModelStorage(long modelId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_MODEL_STORAGE, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, modelId);
            result = statement.executeQuery();
            if (result.first()) {
                MLStorage storage = new MLStorage();
                storage.setType(result.getString(1));
                storage.setLocation(result.getString(2));
                return storage;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting model storage for model id: "
                    + modelId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public boolean isValidModelId(int tenantId, String userName, long modelId) throws DatabaseHandlerException {

        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ML_MODEL_NAME, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, modelId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting model name for model id: "
                    + modelId, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public void deleteAnalysis(int tenantId, String userName, long analysisId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ANALYSIS_BY_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, analysisId);
            preparedStatement.setInt(2, tenantId);
            preparedStatement.setString(3, userName);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the analysis [id]: " + analysisId);
            }
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                deleteAnalysisIdDependencies(tenantId, userName, analysisId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the analysis [id]: " + analysisId + ": "
                    + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    @Override
    public void insertModelConfigurations(long analysisId, List<MLModelConfiguration> modelConfigs)
            throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement insertStatement = null;
        PreparedStatement searchStatement = null;
        ResultSet result;
        try {
            // Insert the model configuration to the database.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);

            for (MLModelConfiguration mlModelConfiguration : modelConfigs) {
                String key = mlModelConfiguration.getKey();
                String value = mlModelConfiguration.getValue();
                searchStatement = connection.prepareStatement(sqlQueries.GET_A_MODEL_CONFIGURATION,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                searchStatement.setLong(1, analysisId);
                searchStatement.setString(2, key);
                result = searchStatement.executeQuery();
                if (result.first()) {
                    insertStatement = connection.prepareStatement(sqlQueries.UPDATE_MODEL_CONFIGURATION,
                            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    insertStatement.setString(1, value);
                    insertStatement.setLong(2, analysisId);
                    insertStatement.setString(3, key);
                } else {
                    insertStatement = connection.prepareStatement(sqlQueries.INSERT_MODEL_CONFIGURATION,
                            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    insertStatement.setLong(1, analysisId);
                    insertStatement.setString(2, key);
                    insertStatement.setString(3, value);
                }
                insertStatement.execute();
            }
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the model configuration");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting model configuration "
                    + " to the database: " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, searchStatement);
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }

    @Override
    public void insertHyperParameters(long analysisId, List<MLHyperParameter> hyperParameters, String algorithmName)
            throws DatabaseHandlerException {
        String currentQuery = "";
        Connection connection = null;
        PreparedStatement insertStatement = null;
        PreparedStatement getStatement = null;
        PreparedStatement deleteStatement = null;
        ResultSet result = null;
        try {
            // Insert the hyper parameter to the database
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            currentQuery = sqlQueries.GET_EXISTING_ALGORITHM;
            getStatement = connection.prepareStatement(sqlQueries.GET_EXISTING_ALGORITHM,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getStatement.setLong(1, analysisId);
            result = getStatement.executeQuery();

            if (result.first() && algorithmName != null && !algorithmName.equals(result.getString(1))) {
                currentQuery = sqlQueries.DELETE_HYPER_PARAMETERS;
                deleteStatement = connection.prepareStatement(sqlQueries.DELETE_HYPER_PARAMETERS,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                deleteStatement.setLong(1, analysisId);
                deleteStatement.execute();
            }

            for (MLHyperParameter mlHyperParameter : hyperParameters) {
                String name = mlHyperParameter.getKey();
                String value = mlHyperParameter.getValue();
                currentQuery = sqlQueries.GET_EXISTING_HYPER_PARAMETER;
                getStatement = connection.prepareStatement(sqlQueries.GET_EXISTING_HYPER_PARAMETER,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                getStatement.setLong(1, analysisId);
                getStatement.setString(2, name);
                result = getStatement.executeQuery();
                if (result.first()) {
                    currentQuery = sqlQueries.UPDATE_HYPER_PARAMETER;
                    insertStatement = connection.prepareStatement(sqlQueries.UPDATE_HYPER_PARAMETER,
                            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    insertStatement.setString(1, algorithmName);
                    insertStatement.setString(2, value);
                    insertStatement.setLong(3, analysisId);
                    insertStatement.setString(4, name);
                } else {
                    currentQuery = sqlQueries.INSERT_HYPER_PARAMETER;
                    insertStatement = connection.prepareStatement(sqlQueries.INSERT_HYPER_PARAMETER,
                            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    insertStatement.setLong(1, analysisId);
                    insertStatement.setString(2, algorithmName);
                    insertStatement.setString(3, name);
                    insertStatement.setString(4, value);
                }
                insertStatement.execute();
            }

            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the hyper parameter");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException(currentQuery + " An error occurred while inserting hyper parameter "
                    + " to the database: " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(getStatement);
            MLDatabaseUtils.closeDatabaseResources(deleteStatement);
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }

    @Override
    public List<MLHyperParameter> getHyperParametersOfModel(long analysisId, String algorithmName)
            throws DatabaseHandlerException {
        List<MLHyperParameter> hyperParams = new ArrayList<MLHyperParameter>();
        Connection connection = null;
        PreparedStatement getFeatues = null;
        ResultSet result = null;
        try {
            // Create a prepared statement and retrieve data-set configurations.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            if (algorithmName == null) {
                getFeatues = connection.prepareStatement(sqlQueries.GET_HYPER_PARAMETERS_OF_ANALYSIS,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                getFeatues.setLong(1, analysisId);
            } else {
                getFeatues = connection.prepareStatement(sqlQueries.GET_HYPER_PARAMETERS_OF_ANALYSIS_WITH_ALGORITHM,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                getFeatues.setLong(1, analysisId);
                getFeatues.setString(2, algorithmName);
            }
            result = getFeatues.executeQuery();
            while (result.next()) {
                MLHyperParameter param = new MLHyperParameter();
                param.setKey(result.getString(1));
                param.setValue(result.getString(2));
                hyperParams.add(param);
            }
            return hyperParams;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving hyper parameters of "
                    + "the model: " + analysisId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatues, result);
        }
    }

    @Override
    public Map<String, String> getHyperParametersOfModelAsMap(long analysisId) throws DatabaseHandlerException {
        Map<String, String> hyperParams = new HashMap<String, String>();
        Connection connection = null;
        PreparedStatement getFeatues = null;
        ResultSet result = null;
        try {
            // Create a prepared statement and retrieve data-set configurations.
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(true);
            getFeatues = connection.prepareStatement(sqlQueries.GET_HYPER_PARAMETERS_OF_ANALYSIS,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getFeatues.setLong(1, analysisId);
            result = getFeatues.executeQuery();
            while (result.next()) {
                hyperParams.put(result.getString(1), result.getString(2));
            }
            return hyperParams;
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error occurred while retrieving hyper parameters of "
                    + "the analysis: " + analysisId + ": " + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatues, result);
        }
    }

    @Override
    public long getDatasetSchemaIdFromAnalysisId(long analysisId) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_DATASET_SCHEMA_ID_FROM_ANALYSIS,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, analysisId);
            result = statement.executeQuery();
            if (result.first()) {
                return result.getLong(1);
            } else {
                return -1;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(String.format(" An error has occurred while extracting dataset id of " +
                    "[analysis] %s ", analysisId), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public void insertDefaultsIntoFeatureCustomized(long analysisId, MLCustomizedFeature customizedValues)
            throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement insertStatement = null;

        long datasetSchemaId = getDatasetSchemaIdFromAnalysisId(analysisId);
        try {
            // Insert the feature-customized to the database
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);

            int tenantId = customizedValues.getTenantId();
            String imputeOption = customizedValues.getImputeOption();
            boolean inclusion = customizedValues.isInclude();
            String lastModifiedUser = customizedValues.getLastModifiedUser();
            String userName = customizedValues.getUserName();

            insertStatement = connection.prepareStatement(sqlQueries.INSERT_DEFAULTS_INTO_FEATURE_CUSTOMIZED,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            insertStatement.setLong(1, analysisId);
            insertStatement.setInt(2, tenantId);
            insertStatement.setString(3, imputeOption);
            insertStatement.setBoolean(4, inclusion);
            insertStatement.setString(5, lastModifiedUser);
            insertStatement.setString(6, userName);
            insertStatement.setLong(7, datasetSchemaId);

            insertStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the feature-customized");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting feature-customized "
                    + " to the database: " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }

    @Override
    public void insertFeatureCustomized(long analysisId, List<MLCustomizedFeature> customizedFeatures, int tenantId,
                                        String userName) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement insertStatement = null;
        try {
            // Insert the feature-customized to the database
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            for (MLCustomizedFeature mlCustomizedFeature : customizedFeatures) {
                String featureName = mlCustomizedFeature.getName();
                String type = mlCustomizedFeature.getType();
                String imputeOption = mlCustomizedFeature.getImputeOption();
                boolean inclusion = mlCustomizedFeature.isInclude();
                String lastModifiedUser = userName;

                insertStatement = connection.prepareStatement(sqlQueries.UPDATE_FEATURE_CUSTOMIZED,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                insertStatement.setString(1, type);
                insertStatement.setString(2, imputeOption);
                insertStatement.setBoolean(3, inclusion);
                insertStatement.setString(4, lastModifiedUser);
                insertStatement.setString(5, userName);
                insertStatement.setLong(6, analysisId);
                insertStatement.setString(7, featureName);
                insertStatement.execute();
            }
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully inserted the feature-customized");
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while inserting feature-customized "
                    + " to the database: " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertStatement);
        }
    }


    @Override
    public void updateSummaryStatistics(long datasetSchemaId, long datasetVersionId, SummaryStats summaryStats)
            throws DatabaseHandlerException {

        int count = getFeatureCount(datasetSchemaId);

        Connection connection = null;
        PreparedStatement insertFeatureDefaults = null, getFeatureIdStmt = null, insertFeatureSummary = null;
        ResultSet result;
        try {
            JSONArray summaryStatJson;
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            int columnIndex;
            for (Map.Entry<String, Integer> columnNameMapping : summaryStats.getHeaderMap().entrySet()) {
                columnIndex = columnNameMapping.getValue();
                // Get the JSON representation of the column summary.
                summaryStatJson = createJson(summaryStats.getType()[columnIndex], summaryStats.getGraphFrequencies()
                                .get(columnIndex), summaryStats.getMissing()[columnIndex],
                        summaryStats.getUnique()[columnIndex], summaryStats.getDescriptiveStats().get(columnIndex));

                if (count == 0) {
                    insertFeatureDefaults = connection.prepareStatement(sqlQueries.INSERT_FEATURE_DEFAULTS,
                            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    insertFeatureDefaults.setLong(1, datasetSchemaId);
                    insertFeatureDefaults.setString(2, columnNameMapping.getKey());
                    insertFeatureDefaults.setString(3, summaryStats.getType()[columnIndex]);
                    insertFeatureDefaults.setInt(4, columnIndex);
                    insertFeatureDefaults.execute();
                }

                // Get feature id
                getFeatureIdStmt = connection.prepareStatement(sqlQueries.GET_FEATURE_ID,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                getFeatureIdStmt.setLong(1, datasetSchemaId);
                getFeatureIdStmt.setString(2, columnNameMapping.getKey());
                result = getFeatureIdStmt.executeQuery();
                long featureId = -1;
                if (result.first()) {
                    featureId = result.getLong(1);
                }

                insertFeatureSummary = connection.prepareStatement(sqlQueries.INSERT_FEATURE_SUMMARY,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                insertFeatureSummary.setLong(1, featureId);
                insertFeatureSummary.setString(2, columnNameMapping.getKey());
                insertFeatureSummary.setLong(3, datasetVersionId);
                insertFeatureSummary.setString(4, summaryStatJson.toString());
                insertFeatureSummary.execute();
            }
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully updated the summary statistics for dataset version " + datasetVersionId);
            }
        } catch (Exception e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("An error occurred while updating the database "
                    + "with summary statistics of the dataset " + datasetVersionId + ": " + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, insertFeatureDefaults);
            MLDatabaseUtils.closeDatabaseResources(connection, getFeatureIdStmt);
            MLDatabaseUtils.closeDatabaseResources(connection, insertFeatureSummary);
        }
    }

    /**
     * Create the JSON string with summary statistics for a column.
     *
     * @param type             Data-type of the column
     * @param graphFrequencies Bin frequencies of the column
     * @param missing          Number of missing values in the column
     * @param unique           Number of unique values in the column
     * @param descriptiveStats DescriptiveStats object of the column
     * @return JSON representation of the summary statistics of the column
     */
    private JSONArray createJson(String type, SortedMap<?, Integer> graphFrequencies, int missing, int unique,
                                 DescriptiveStatistics descriptiveStats) throws JSONException {

        JSONObject json = new JSONObject();
        JSONArray freqs = new JSONArray();
        Object[] categoryNames = graphFrequencies.keySet().toArray();
        // Create an array with intervals/categories and their frequencies.
        for (int i = 0; i < graphFrequencies.size(); i++) {
            JSONArray temp = new JSONArray();
            temp.put(categoryNames[i].toString());
            temp.put(graphFrequencies.get(categoryNames[i]));
            freqs.put(temp);
        }
        // Put the statistics to a json object
        json.put("unique", unique);
        json.put("missing", missing);

        DecimalFormat decimalFormat = new DecimalFormat("#.###");
        if (descriptiveStats.getN() != 0) {
            json.put("mean", decimalFormat.format(descriptiveStats.getMean()));
            json.put("min", decimalFormat.format(descriptiveStats.getMin()));
            json.put("max", decimalFormat.format(descriptiveStats.getMax()));
            json.put("median", decimalFormat.format(descriptiveStats.getPercentile(50)));
            json.put("std", decimalFormat.format(descriptiveStats.getStandardDeviation()));
            if (type.equalsIgnoreCase(FeatureType.NUMERICAL)) {
                json.put("skewness", decimalFormat.format(descriptiveStats.getSkewness()));
            }
        }
        json.put("values", freqs);
        json.put("bar", true);
        json.put("key", "Frequency");
        JSONArray summaryStatArray = new JSONArray();
        summaryStatArray.put(json);
        return summaryStatArray;
    }

    @Override
    public Workflow getWorkflow(long analysisId) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement getStatement = null;
        
        try {
            Workflow mlWorkflow = new Workflow();
            mlWorkflow.setWorkflowID(analysisId);
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            List<Feature> mlFeatures = new ArrayList<Feature>();
            getStatement = connection.prepareStatement(sqlQueries.GET_ALL_CUSTOMIZED_FEATURES,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            getStatement.setLong(1, analysisId);
            result = getStatement.executeQuery();
            while (result.next()) {
                // check whether to include the feature or not
                if (result.getBoolean(5) == true) {
                    Feature mlFeature = new Feature();
                    mlFeature.setName(result.getString(1));
                    mlFeature.setIndex(result.getInt(2));
                    mlFeature.setType(result.getString(3));
                    mlFeature.setImputeOption(result.getString(4));
                    mlFeature.setInclude(result.getBoolean(5));
                    mlFeatures.add(mlFeature);
                }
            }
            mlWorkflow.setFeatures(mlFeatures);
            
            // set model configs
            mlWorkflow.setAlgorithmName(getAStringModelConfiguration(analysisId, MLConstants.ALGORITHM_NAME));
            mlWorkflow.setAlgorithmClass(getAStringModelConfiguration(analysisId, MLConstants.ALGORITHM_TYPE));
            mlWorkflow.setResponseVariable(getAStringModelConfiguration(analysisId, MLConstants.RESPONSE_VARIABLE));
            mlWorkflow.setUserVariable(getAStringModelConfiguration(analysisId, MLConstants.USER_VARIABLE));
            mlWorkflow.setProductVariable(getAStringModelConfiguration(analysisId, MLConstants.PRODUCT_VARIABLE));
            mlWorkflow.setRatingVariable(getAStringModelConfiguration(analysisId, MLConstants.RATING_VARIABLE));
            mlWorkflow.setObservations(getAStringModelConfiguration(analysisId, MLConstants.OBSERVATIONS));
            mlWorkflow.setTrainDataFraction(Double.valueOf(getAStringModelConfiguration(analysisId, MLConstants.TRAIN_DATA_FRACTION)));
            mlWorkflow.setNormalLabels(getAStringModelConfiguration(analysisId, MLConstants.NORMAL_LABELS));
            mlWorkflow.setNormalization(getABooleanModelConfiguration(analysisId, MLConstants.NORMALIZATION));
            mlWorkflow.setNewNormalLabel(getAStringModelConfiguration(analysisId, MLConstants.NEW_NORMAL_LABEL));
            mlWorkflow.setNewAnomalyLabel(getAStringModelConfiguration(analysisId, MLConstants.NEW_ANOMALY_LABEL));
	    mlWorkflow.setTimeSeriesDataset(getABooleanModelConfiguration(analysisId, MLConstants.TIME_SERIES_DATASET));

            // set hyper parameters
            mlWorkflow.setHyperParameters(getHyperParametersOfModelAsMap(analysisId));
            // result = getStatement.executeQuery();
            // if (result.first()) {
            // mlWorkflow.setAlgorithmClass(result.getString(1));
            // mlWorkflow.setAlgorithmName(result.getString(2));
            // mlWorkflow.setResponseVariable(result.getString(3));
            // mlWorkflow.setTrainDataFraction(result.getDouble(4));
            // List<HyperParameter> hyperParameters = (List<HyperParameter>) result.getObject(5);
            // mlWorkflow.setHyperParameters(MLDatabaseUtils.getHyperParamsAsAMap(hyperParameters));
            // }
            return mlWorkflow;
        } catch (SQLException e) {
            throw new DatabaseHandlerException(e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, getStatement, result);
        }
    }

    @Override
    public String getAStringModelConfiguration(long analysisId, String configKey) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement model = null;
        ResultSet result = null;
        try {
            connection = dbh.getDataSource().getConnection();
            model = connection.prepareStatement(sqlQueries.GET_A_MODEL_CONFIGURATION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            model.setLong(1, analysisId);
            model.setString(2, configKey);
            result = model.executeQuery();
            if (result.first()) {
                return result.getString(1);
            } else {
                return null;
            }
        } catch (SQLException e) {
            String msg = String.format(
                    "An error occurred white retrieving [model config] %s  associated with [model id] %s : %s",
                    configKey, analysisId, e.getMessage());
            throw new DatabaseHandlerException(msg, e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, model, result);
        }
    }

    @Override
    public double getADoubleModelConfiguration(long analysisId, String configKey) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement model = null;
        ResultSet result = null;
        try {
            connection = dbh.getDataSource().getConnection();
            model = connection.prepareStatement(sqlQueries.GET_A_MODEL_CONFIGURATION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            model.setLong(1, analysisId);
            model.setString(2, configKey);
            result = model.executeQuery();
            if (result.first()) {
                return result.getDouble(1);
            } else {
                return -1;
            }
        } catch (SQLException e) {
            String msg = String.format(
                    "An error occurred white retrieving [model config] %s  associated with [model id] %s : %s",
                    configKey, analysisId, e.getMessage());
            throw new DatabaseHandlerException(msg, e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, model, result);
        }
    }

    @Override
    public boolean getABooleanModelConfiguration(long analysisId, String configKey) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement model = null;
        ResultSet result = null;
        try {
            connection = dbh.getDataSource().getConnection();
            model = connection.prepareStatement(sqlQueries.GET_A_MODEL_CONFIGURATION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            model.setLong(1, analysisId);
            model.setString(2, configKey);
            result = model.executeQuery();
            if (result.first()) {
                return result.getBoolean(1);
            } else {
                return false;
            }
        } catch (SQLException e) {
            String msg = String.format(
                    "An error occurred white retrieving [model config] %s  associated with [model id] %s : %s",
                    configKey, analysisId, e.getMessage());
            throw new DatabaseHandlerException(msg, e);
        } finally {
            // Close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, model, result);
        }
    }

    @Override
    public void deleteDataset(long datasetId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_DATASET_SCHEMA,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetId);
            preparedStatement.execute();
            connection.commit();
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                deleteDatasetSchemaIdDependencies(datasetId);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the dataset schema : " + datasetId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting dataset schema: " + datasetId + ": "
                    + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    @Override
    public void deleteDatasetVersion(long datasetVersionId) throws DatabaseHandlerException {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_DATASET_VERSION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetVersionId);
            preparedStatement.execute();
            connection.commit();
            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                deleteDatasetVersionIdDependencies(datasetVersionId);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the dataset version : " + datasetVersionId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting dataset version: " + datasetVersionId + ": "
                    + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    @Override
    public MLAnalysis getAnalysisOfProject(int tenantId, String userName, long projectId, String analysisName)
            throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_ANALYSIS_OF_PROJECT,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, tenantId);
            statement.setString(2, userName);
            statement.setLong(3, projectId);
            statement.setString(4, analysisName);
            result = statement.executeQuery();
            if (result.first()) {
                MLAnalysis analysis = new MLAnalysis();
                analysis.setId(result.getLong(1));
                analysis.setProjectId(result.getLong(2));
                analysis.setComments(MLDatabaseUtils.toString(result.getClob(3)));
                analysis.setName(result.getString(4));
                analysis.setTenantId(tenantId);
                analysis.setUserName(userName);
                return analysis;
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(" An error has occurred while extracting analysis for project id: "
                    + projectId + " and analysis name: " + analysisName, e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
    }

    @Override
    public void updateSamplePoints(long datasetVersionId, SamplePoints samplePoints) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement updateStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            updateStatement = connection.prepareStatement(sqlQueries.UPDATE_SAMPLE_POINTS,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            if (MSSQLQueries.MSSQL_SERVER_NAME.equals(databaseName)) {
                byte[] byteArray = SerializationUtils.serialize(samplePoints);
                int lengthOfBytes = byteArray.length;
                InputStream samplePointsInputStream = new ByteArrayInputStream(byteArray);
                updateStatement.setBinaryStream(1, samplePointsInputStream, lengthOfBytes);
            } else {
                updateStatement.setObject(1, samplePoints);
            }
            updateStatement.setLong(2, datasetVersionId);
            updateStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully updated the sample points of dataset version: " + datasetVersionId);
            }
        } catch (SQLException e) {
            // Roll-back the changes.
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException(
                    "An error occurred while updating the sample points of dataset version: " + datasetVersionId + ": "
                            + e.getMessage(), e);
        } finally {
            // Enable auto commit.
            MLDatabaseUtils.enableAutoCommit(connection);
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, updateStatement);
        }
    }

    @Override
    public boolean isValidModelStatus(long modelId, int tenantId, String userName) throws DatabaseHandlerException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement(sqlQueries.GET_MODEL_STATUS, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setLong(1, modelId);
            statement.setInt(2, tenantId);
            statement.setString(3, userName);
            result = statement.executeQuery();
            if (result.first()) {
                if (MLConstants.MODEL_STATUS_COMPLETE.equalsIgnoreCase(result.getString(1))) {
                    return true;
                }
            } else {
                throw new DatabaseHandlerException("Failed to find the model for model id " + modelId);
            }
        } catch (SQLException e) {
            throw new DatabaseHandlerException(
                    "An error has occurred while fetching the status of the model for model id: " + modelId + ": "
                            + e.getMessage(), e);
        } finally {
            MLDatabaseUtils.closeDatabaseResources(connection, statement, result);
        }
        // Consider anything other than "Complete" status as an invalid model.
        return false;
    }

    public void shutdown() throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            statement = connection.prepareStatement("SHUTDOWN");
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseHandlerException("An error has occurred while shutting down the database: "
                    + e.getMessage(), e);
        } finally {
            // Close the database resources.
            MLDatabaseUtils.closeDatabaseResources(connection, statement);
        }
    }

    private void deleteAnalysisIdDependencies(int tenantId, String userName, long analysisId) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_MODEL_VIA_ANALYSIS_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, analysisId);
            preparedStatement.setInt(2, tenantId);
            preparedStatement.setString(3, userName);
            preparedStatement.execute();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_MODEL_CONFIGURATION_VIA_ANALYSIS_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, analysisId);
            preparedStatement.execute();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_FEATURE_CUSTOMIZED_VIA_ANALYSIS_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, analysisId);
            preparedStatement.execute();
            connection.commit();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_HYPER_PARAMETER_VIA_ANALYSIS_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, analysisId);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the model [id]s for analysis id : " + analysisId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the model [id]s for analysis id : "
                    + analysisId + ": " + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    private void deleteDatasetVersionIdDependencies(long datasetVersionId) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_FEATURE_SUMMARY_VIA_DATASET_VERSION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetVersionId);
            preparedStatement.execute();
            connection.commit();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_DATA_SOURCE_VIA_DATASET_VERSION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetVersionId);
            preparedStatement.execute();
            connection.commit();
            preparedStatement = connection.prepareStatement(sqlQueries.UPDATE_ML_MODEL_VIA_DATASET_VERSION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setNull(1, Types.BIGINT);
            preparedStatement.setLong(2, datasetVersionId);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the dependent [id]s for dataset version id :" + datasetVersionId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the dependent [id]s for dataset version id : "
                    + datasetVersionId + ": " + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    private void deleteDatasetSchemaIdDependencies(long datasetId) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sqlQueries.GET_FEATURE_DEFAULTS_VIA_SCHEMA,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetId);
            ResultSet result = preparedStatement.executeQuery();
            while (result.next()) {
                deleteFeatureIdDependencies(result.getLong(1));
            }
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_FEATURE_DEFAULTS_VIA_SCHEMA,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetId);
            preparedStatement.execute();
            connection.commit();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_DATASET_VERSION_VIA_SCHEMA,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, datasetId);
            preparedStatement.execute();
            connection.commit();
            preparedStatement = connection.prepareStatement(sqlQueries.UPDATE_PROJECT_VIA_SCHEMA_DELETION,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setNull(1, Types.BIGINT);
            preparedStatement.setLong(2, datasetId);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the dependent [id]s for dataset schema id :" + datasetId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the dependent [id]s for dataset schema id : "
                    + datasetId + ": " + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    private void deleteFeatureIdDependencies(long featureId) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_FEATURE_SUMMARY_VIA_FEATURE_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, featureId);
            preparedStatement.execute();
            connection.commit();
            preparedStatement = connection.prepareStatement(
                    sqlQueries.UPDATE_ML_FEATURE_CUSTOMIZED_TO_NULL_VIA_FEATURE_ID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setNull(1, Types.BIGINT);
            preparedStatement.setLong(2, featureId);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the dependent [id]s for feature id :" + featureId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the dependent [id]s for feature id : "
                    + featureId + ": " + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

    private void deleteProjectIdDependencies(long projectId) throws DatabaseHandlerException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dbh.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sqlQueries.DELETE_ML_ANALYSIS_VIA_PROJECT_ID, ResultSet
                    .TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setLong(1, projectId);
            preparedStatement.execute();
            connection.commit();
            if (logger.isDebugEnabled()) {
                logger.debug("Successfully deleted the dependent [id]s for project id :" + projectId);
            }
        } catch (SQLException e) {
            MLDatabaseUtils.rollBack(connection);
            throw new DatabaseHandlerException("Error occurred while deleting the dependent [id]s for project id : "
                    + projectId + ": " + e.getMessage(), e);
        } finally {
            // enable auto commit
            MLDatabaseUtils.enableAutoCommit(connection);
            // close the database resources
            MLDatabaseUtils.closeDatabaseResources(connection, preparedStatement);
        }
    }

}
