DaoTable.java

/* 
 * _____________________________________________________________________________
 * 
 * INAF - OATS National Institute for Astrophysics - Astronomical Observatory of
 * Trieste INAF - IA2 Italian Center for Astronomical Archives
 * _____________________________________________________________________________
 * 
 * Copyright (C) 2016 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.api;

import it.inaf.ia2.tsm.api.contract.DatabaseType;
import it.inaf.ia2.tsm.api.contract.Schema;
import it.inaf.ia2.tsm.api.contract.Status;
import it.inaf.ia2.tsm.api.contract.Table;
import it.inaf.ia2.tsm.api.contract.TapSchema;
import it.inaf.ia2.tsm.api.contract.TapSchemaEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Utility class that contains static methods for managing {@link Table}s into
 * the database.
 *
 * @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
 */
public class DaoTable {

    private static final Logger log = LoggerFactory.getLogger(DaoTable.class);

    /**
     * Retrieve the list of the names of all the tables contained in a schema,
     * given its name, also if the schema has never been added into the
     * TAP_SCHEMA.
     *
     * @return list of all tables names alphabetically and case insensitively
     * ordered.
     */
    public static List<String> getAllTablesNames(DBWrapper dbWrapper, TapSchema tapSchema, String schemaName) throws SQLException {

        DataSource dataSource = TSMUtil.getSchemaDataSource(dbWrapper, tapSchema, schemaName);
        DatabaseType dbType = TSMUtil.getSchemaDatabaseType(dbWrapper, tapSchema, schemaName);

        String query;
        if (dbType == DatabaseType.MYSQL) {
            query = "SHOW TABLES FROM `" + schemaName + "`";
        } else if (dbType == DatabaseType.POSTGRES) {
            query = "SELECT tablename FROM pg_catalog.pg_tables where schemaname = '" + schemaName + "'";
        } else {
            throw new UnsupportedOperationException("Database type " + dbType + " not supported");
        }

        log.debug("Executing query {}", query);

        List<String> allTables = new ArrayList<>();
        try (Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(query)) {
            while (resultSet.next()) {
                allTables.add(resultSet.getString(1));
            }
        }

        return TSMUtil.sortStringsList(allTables);
    }

    /**
     * Retrieve the association between the tables names and their types
     * (<code>table</code> or <code>view</code>), given a
     * <code>DataSource</code> and a schema name.
     *
     * @return a map which has the tables names as keys and the table types as
     * values.
     */
    protected static Map<String, String> getTablesTypes(DBWrapper dbWrapper, TapSchema tapSchema, String schemaName) throws SQLException {

        log.debug("getTablesTypes");

        final Map<String, String> tablesTypes = new HashMap<>();

        DataSource dataSource = TSMUtil.getSchemaDataSource(dbWrapper, tapSchema, schemaName);
        DatabaseType dbType = TSMUtil.getSchemaDatabaseType(dbWrapper, tapSchema, schemaName);

        String query;
        if (dbType == DatabaseType.MYSQL) {
            query = "SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = '" + schemaName + "'";
        } else if (dbType == DatabaseType.POSTGRES) {
            query = "SELECT tablename AS table_name, 'table' AS table_type\n"
                    + "FROM pg_catalog.pg_tables WHERE schemaname = '" + schemaName + "'\n"
                    + "UNION\n"
                    + "SELECT table_name AS table_name, 'view' AS table_type\n"
                    + "FROM INFORMATION_SCHEMA.views\n"
                    + "WHERE table_schema = '" + schemaName + "'";
        } else {
            throw new UnsupportedOperationException("Database type " + dbType + " not supported");
        }

        log.debug("Executing query {}", query);

        try (Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(query)) {
            while (resultSet.next()) {
                String tableName = resultSet.getString("table_name");
                String tableType = resultSet.getString("table_type").equalsIgnoreCase("VIEW") ? "view" : "table";
                tablesTypes.put(tableName, tableType);
            }
        }

        return tablesTypes;
    }

    /**
     * Retrieves saved {@code Table}s from the database and add them into the
     * specified {@code TapSchema}.
     */
    protected static void fillSavedTables(DBWrapper dbWrapper, final TapSchema tapSchema) throws SQLException {

        log.debug("fillSavedTables");

        SelectQueryBuilder selectQueryBuilder = new SelectQueryBuilder(dbWrapper.getTapSchemaDatabaseType(), tapSchema, TapSchema.TABLES_TABLE) {

            @Override
            protected TapSchemaEntity getEntity(ResultSet rs) throws SQLException {
                String schemaName = rs.getString("schema_name");
                String completeTableName = rs.getString("table_name");

                Schema schema = tapSchema.getChild(schemaName);

                Table table = schema.addChild(completeTableName.split(Pattern.quote("."))[1]);
                if (table == null) {
                    return null;
                }
                table.setStatus(Status.ADDED_PERSISTED);
                return table;
            }
        };

        selectQueryBuilder.executeQuery(dbWrapper.getTapSchemaConnection());
    }

    /**
     * Save a new {@code Table} into the TAP_SCHEMA schema.
     */
    protected static void insertNewTable(DatabaseType dbType, Connection connection, TapSchema tapSchema, Table table) throws SQLException {

        log.debug("insertNewTable");

        InsertQueryBuilder insertQueryBuilder = new InsertQueryBuilder(dbType, tapSchema, table, TapSchema.TABLES_TABLE);
        insertQueryBuilder.executeQuery(connection);
    }

    /**
     * Updates an existing {@code Table}.
     */
    protected static void updateTable(DatabaseType dbType, Connection connection, TapSchema tapSchema, Table table) throws SQLException {

        UpdateQueryBuilder updateQueryBuilder = new UpdateQueryBuilder(dbType, tapSchema, table, TapSchema.TABLES_TABLE, "table_name = ?");

        String query = updateQueryBuilder.getQuery();

        try (PreparedStatement statement = connection.prepareStatement(query)) {

            log.debug("Executing query {}", query);

            int i = updateQueryBuilder.addStatementValues(statement);
            statement.setString(i, table.getCompleteName());

            statement.executeUpdate();
        }
    }
}