DBBrokerTemplate.java

/*
 * _____________________________________________________________________________
 * 
 * INAF - OATS National Institute for Astrophysics - Astronomical Observatory of
 * Trieste INAF - IA2 Italian Center for Astronomical Archives
 * _____________________________________________________________________________
 * 
 * Copyright (C) 2017 Istituto Nazionale di Astrofisica
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License Version 3 as published by the
 * Free Software Foundation.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc., 51
 * Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
 */
package it.inaf.ia2.tsm.datalayer;

import it.inaf.ia2.tsm.Column;
import it.inaf.ia2.tsm.ColumnHolder;
import it.inaf.ia2.tsm.ConsistencyChecks;
import it.inaf.ia2.tsm.Key;
import it.inaf.ia2.tsm.KeyColumn;
import it.inaf.ia2.tsm.Schema;
import it.inaf.ia2.tsm.Status;
import it.inaf.ia2.tsm.TSMUtil;
import it.inaf.ia2.tsm.Table;
import it.inaf.ia2.tsm.TapSchema;
import it.inaf.ia2.tsm.TapSchemaEntity;
import it.inaf.ia2.tsm.UpdateOperations;
import it.inaf.ia2.tsm.model.ColumnModel;
import it.inaf.ia2.tsm.model.TableModel;
import it.inaf.ia2.tsm.model.SchemaModel;
import it.inaf.ia2.tsm.model.SchemaModels;
import it.inaf.ia2.tsm.model.TypesMapping;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Template class containing all {@link DBBroker} features that are not vendor
 * specific.
 *
 * @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
 */
public abstract class DBBrokerTemplate implements DBBroker {

    private static final Logger LOG = LoggerFactory.getLogger(DBBrokerTemplate.class);

    protected final DataSource dataSource;
    private final char escapeCharacter;
    private final DataTypeMode dataTypeMode;

    public DBBrokerTemplate(DataSource dataSource, char escapeCharacter, DataTypeMode dataTypeMode) {
        this.dataSource = dataSource;
        this.escapeCharacter = escapeCharacter;
        this.dataTypeMode = dataTypeMode;
    }

    protected List<String> getAllItemsNames(String query) throws SQLException {

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

        LOG.debug("Executing query: {}", query);

        try (Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(query)) {
            while (resultSet.next()) {
                allSchemas.add(resultSet.getString(1));
            }
        }

        Collections.sort(allSchemas, String.CASE_INSENSITIVE_ORDER);
        return allSchemas;
    }

    protected String buildColumnsList(String[] columns) {

        StringBuilder sb = new StringBuilder();

        boolean first = true;
        for (String keyColumn : columns) {
            if (!first) {
                sb.append(",");
            }
            first = false;
            sb.append(escape(keyColumn));
        }

        return sb.toString();
    }

    protected String escape(String name) {
        return String.format("%s%s%s", escapeCharacter, name, escapeCharacter);
    }

    protected void appendSize(StringBuilder sb, int size) {
        sb.append(String.format("(%s)", size));
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void createTable(String schemaName, TableModel tableModel) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            createTable(schemaName, tableModel, conn);
        }
    }

    protected abstract void createTable(String schemaName, TableModel tableModel, Connection conn) throws SQLException;

    protected abstract String getAddPrimaryKeyQuery(String tapSchemaName, String tableName, String[] keyColumns);

    protected abstract String getAddForeignKeyQuery(String tapSchemaName, String tableName, String[] fromKeyColumns, String targetTableName, String[] toKeyColumns);

    private String getAddPrimaryKeyQuery(String tapSchemaName, String tableName, String keyColumn) {
        return getAddPrimaryKeyQuery(tapSchemaName, tableName, new String[]{keyColumn});
    }

    private String getAddForeignKeyQuery(String tapSchemaName, String tableName, String fromKeyColumn, String targetTableName, String toKeyColumn) {
        return getAddForeignKeyQuery(tapSchemaName, tableName, new String[]{fromKeyColumn}, targetTableName, new String[]{toKeyColumn});
    }

    protected abstract String getCreateDatabaseQuery(String databaseName);

    private void execute(String query, Connection conn) throws SQLException {
        try (Statement stat = conn.createStatement()) {
            LOG.debug("Executing query: {}", query);
            stat.execute(query);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void createTapSchemaStructure(String tapSchemaName, SchemaModel tapSchemaModel) throws SQLException {

        try (Connection conn = dataSource.getConnection()) {

            execute(getCreateDatabaseQuery(tapSchemaName), conn);

            for (TableModel tableModel : tapSchemaModel.getTables()) {
                createTable(tapSchemaName, tableModel, conn);
            }

            // schemas keys
            execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.SCHEMAS_TABLE, Schema.SCHEMA_NAME_KEY), conn);

            // tables keys
            execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);
            execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.TABLES_TABLE, Table.SCHEMA_NAME_KEY, TapSchema.SCHEMAS_TABLE, Schema.SCHEMA_NAME_KEY), conn);

            // columns keys
            execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.COLUMNS_TABLE, new String[]{Column.TABLE_NAME_KEY, Column.COLUMN_NAME_KEY}), conn);
            execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.COLUMNS_TABLE, Column.TABLE_NAME_KEY, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);

            // keys keys
            execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.KEYS_TABLE, Key.ID_KEY), conn);
            execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.KEYS_TABLE, Key.FROM_TABLE_KEY, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);
            execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.KEYS_TABLE, Key.TARGET_TABLE_KEY, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);

            // key columns key
            //addPrimaryKey(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, new String[]{KeyColumn.KEY_ID_KEY, KeyColumn.FROM_COLUMN_KEY, KeyColumn.TARGET_COLUMN_KEY}, conn);
            execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, Key.ID_KEY, TapSchema.KEYS_TABLE, Key.ID_KEY), conn);
            //addForeignKey(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, KeyColumn.FROM_COLUMN_KEY, TapSchema.COLUMNS_TABLE, Column.COLUMN_NAME_KEY, conn);
            //addForeignKey(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, KeyColumn.TARGET_COLUMN_KEY, TapSchema.COLUMNS_TABLE, Column.COLUMN_NAME_KEY, conn);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void createIvoaSchemaStructure(SchemaModel ivoaSchemaModel, String realIvoaSchemaName) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            execute(getCreateDatabaseQuery(realIvoaSchemaName), conn);
            for (TableModel tableModel : ivoaSchemaModel.getTables()) {
                createTable(realIvoaSchemaName, tableModel, conn);