DataProvider.java

package it.inaf.oats.ia2.tapschemamanager.datalayer;

import java.sql.Connection;
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 javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

/**
 *
 * @author Sonia Zorba <zorba at oats.inaf.it>
 */
public class DataProvider {

    public static List<String> getAllSchemasNames(Connection sourceConnection) throws SQLException {
        final List<String> allSchemas = new ArrayList<String>();
        (new ResultSetReader("SHOW DATABASES;") {

            @Override
            public void manipulateItem(ResultSet resultSet) throws SQLException {
                allSchemas.add(resultSet.getString(1));
            }

        }).read(sourceConnection);
        return allSchemas;
    }

    public static List<String> getAllTAPSchemasNames(Connection tapSchemaConnection, final List<String> allSchemas) throws SQLException {

        class TAPSchemaChecker {

            boolean schemas;
            boolean tables;
            boolean columns;
            boolean keys;
            boolean key_columns;

            // if database has schemas, tables, columns, keys and key_columns
            // then it is a tap_schema database
            boolean isTAPSchema() {
                return schemas && tables && columns && keys && key_columns;
            }
        }

        final List<String> allTAPSchemas = new ArrayList<String>();

        for (String schemaName : allSchemas) {

            final TAPSchemaChecker tapSchemaChecker = new TAPSchemaChecker();

            (new ResultSetReader("SHOW TABLES FROM `" + schemaName + "`;") {

                @Override
                public void manipulateItem(ResultSet resultSet) throws SQLException {
                    // searches if the current database has schemas, tables, columns,
                    // keys, key_columns, that is a tap_schema database

                    String shortTableName = resultSet.getString(1);

                    if (shortTableName.equals("schemas")) {
                        tapSchemaChecker.schemas = true;
                    } else if (shortTableName.equals("tables")) {
                        tapSchemaChecker.tables = true;
                    } else if (shortTableName.equals("columns")) {
                        tapSchemaChecker.columns = true;
                    } else if (shortTableName.equals("keys")) {
                        tapSchemaChecker.keys = true;
                    } else if (shortTableName.equals("key_columns")) {
                        tapSchemaChecker.key_columns = true;
                    }
                }

            }).read(tapSchemaConnection);

            if (tapSchemaChecker.isTAPSchema()) {
                allTAPSchemas.add(schemaName);
            }
        }

        return allTAPSchemas;
    }

    public static List<String> getExposedSchemas(Connection tapSchemaConnection, final String tapSchemaName) throws SQLException {
        final List<String> exposedSchemas = new ArrayList<String>();

        (new ResultSetReader("SELECT schema_name FROM `" + tapSchemaName + "`.`schemas`") {

            @Override
            public void manipulateItem(ResultSet resultSet) throws SQLException {
                exposedSchemas.add(resultSet.getString(1));
            }
        }).read(tapSchemaConnection);

        return exposedSchemas;
    }

    /**
     * @param tapSchemaCredentials
     * @param tapSchemaName
     * @return JPA entity manager for given TAP_SCHEMA name and credentials
     */
    protected static EntityManager getEntityManager(Credentials tapSchemaCredentials, String tapSchemaName) {
        Map<String, String> persistenceMap = new HashMap<String, String>();

        persistenceMap.put("javax.persistence.jdbc.url", "jdbc:mysql://" + tapSchemaCredentials.getHostname() + ":" + tapSchemaCredentials.getPort() + "/" + tapSchemaName);
        persistenceMap.put("javax.persistence.jdbc.user", tapSchemaCredentials.getUsername());
        persistenceMap.put("javax.persistence.jdbc.password", tapSchemaCredentials.getPassword());
        persistenceMap.put("javax.persistence.jdbc.driver", tapSchemaCredentials.getDatabaseDriverClass());

        EntityManagerFactory managerFactory = Persistence.createEntityManagerFactory("pu", persistenceMap);
        return managerFactory.createEntityManager();
    }

    protected static void createDatabase(Connection tapSchemaConnection, String tapSchemaName) throws SQLException {
        Statement statement = tapSchemaConnection.createStatement();
        statement.executeUpdate("CREATE DATABASE IF NOT EXISTS `" + tapSchemaName + "`");
        statement.close();
    }

    protected static ArrayList<String> getAllTablesNames(Connection sourceConnection, String schemaName) throws SQLException {
        final ArrayList<String> allTables = new ArrayList<String>();

        (new ResultSetReader("SHOW TABLES FROM `" + schemaName + "`;") {

            @Override
            public void manipulateItem(ResultSet resultSet) throws SQLException {
                allTables.add(resultSet.getString(1));
            }
        }).read(sourceConnection);

        return allTables;
    }

    protected static Map<String, String> getTablesTypes(Connection sourceConnection, String schemaName) throws SQLException {
        final Map<String, String> tablesTypes = new HashMap<String, String>();

        (new ResultSetReader("SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = '" + schemaName + "'") {

            @Override
            public void manipulateItem(ResultSet resultSet) throws SQLException {
                String tableName = resultSet.getString("table_name");
                String tableType = resultSet.getString("table_type").equalsIgnoreCase("VIEW") ? "view" : "table";
                tablesTypes.put(tableName, tableType);
            }
        }).read(sourceConnection);

        return tablesTypes;
    }

    /**
     * Generate list of KeyEntity for a given schema. This keys are without id!
     * It has to be set when a table is added to a schema.
     *
     * @param sourceConnection
     * @param schemaName
     * @return
     * @throws SQLException
     */
    protected static List<KeyEntity> getSchemaKeys(final Connection sourceConnection, final String schemaName) throws SQLException {

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

        (new ResultSetReader("SELECT k.TABLE_NAME, k.COLUMN_NAME, "
                + "k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME "
                + "FROM information_schema.TABLE_CONSTRAINTS i "
                + "LEFT JOIN information_schema.KEY_COLUMN_USAGE k "
                + "ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME "
                + "WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' "
                + "AND i.TABLE_SCHEMA = '" + schemaName + "' "
                + "AND k.TABLE_SCHEMA = '" + schemaName + "';") {

                    @Override
                    public void manipulateItem(ResultSet resultSet) throws SQLException {
                        String fromTable = schemaName + "." + resultSet.getString("k.TABLE_NAME");
                        String targetTable = schemaName + "." + resultSet.getString("k.REFERENCED_TABLE_NAME");

                        String mapKey = fromTable + "_" + targetTable;

                        KeyEntity keyEntity = schemaKeysMap.get(mapKey);
                        if (keyEntity == null) {
                            keyEntity = new KeyEntity();
                            keyEntity.setFromTableFullName(fromTable);
                            keyEntity.setTargetTableFullName(targetTable);
                            schemaKeysMap.put(mapKey, keyEntity);
                        }

                        KeyColumnEntity keyColumnEntity = new KeyColumnEntity();
                        keyColumnEntity.setFromColumn(resultSet.getString("k.COLUMN_NAME"));
                        keyColumnEntity.setTargetColumn(resultSet.getString("k.REFERENCED_COLUMN_NAME"));
                        keyEntity.getKeyColumns().add(keyColumnEntity);
                    }
                }).read(sourceConnection);

        return new ArrayList(schemaKeysMap.values());
    }

    private static boolean equalsOneOf(String string, String... values) {
        for (String value : values) {
            if (string.equals(value)) {
                return true;
            }
        }
        return false;
    }

    protected static List<ColumnInfo> getAllColumns(final Connection sourceConnection, final TableEntity table) throws SQLException {
        final List<ColumnInfo> allColumns = new ArrayList<ColumnInfo>();

        (new ResultSetReader("SHOW COLUMNS FROM `" + table.getSchemaName() + "`.`" + table.getShortTableName() + "`;") {

            @Override
            public void manipulateItem(ResultSet resultSet) throws SQLException {

                String columnName = resultSet.getString("Field");

                ColumnEntity columnEntity = DLUtil.getEntityByName(table.getColumns(), columnName);
                boolean alreadyLoaded = columnEntity != null;

                if (columnEntity == null) {
                    columnEntity = new ColumnEntity(columnName);
                }

                columnEntity.setFullTableName(table.getName());
                columnEntity.setTable(table);

                ColumnInfo columnInfo = new ColumnInfo(columnEntity);

                // Key
                String key = resultSet.getString("Key");
                columnInfo.setPrimaryKey(key.equals("PRI"));
                columnEntity.setIndexed(equalsOneOf(key, "PRI", "UNI", "MUL") ? 1 : 0);
                if (!alreadyLoaded) {
                    columnEntity.setPrincipal(key.equals("PRI") ? 1 : 0);
                }

                // Datatype and Size
                String type = resultSet.getString("Type").toLowerCase();

                int size = 0;
                String datatype;
                if (type.startsWith("int")) {
                    datatype = "adql:INTEGER";
                } else if (type.startsWith("smallint")) {
                    datatype = "adql:SMALLINT";
                } else if (type.startsWith("bigint")) {
                    datatype = "adql:BIGINT";
                } else if (type.startsWith("float")) {
                    datatype = "adql:REAL";
                } else if (type.startsWith("char")) {
                    int beginIndex = type.indexOf('(');
                    int endIndex = type.indexOf(')');
                    size = Integer.parseInt(type.substring(beginIndex + 1, endIndex));
                    datatype = "adql:CHAR";
                } else if (type.startsWith("varchar")) {
                    int beginIndex = type.indexOf('(');
                    int endIndex = type.indexOf(')');
                    size = Integer.parseInt(type.substring(beginIndex + 1, endIndex));
                    datatype = "adql:VARCHAR";
                } else if (type.contains("timestamp")) {
                    datatype = "adql:TIMESTAMP";
                } else {
                    datatype = "adql:" + type.toUpperCase();
                }
                columnEntity.setDatatype(datatype);
                columnEntity.setSize(size);

                if (!alreadyLoaded) {
                    columnEntity.setStd(0);
                }

                allColumns.add(columnInfo);
            }
        }).read(sourceConnection);

        return allColumns;
    }
}