Newer
Older
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 connection) 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(connection);
return allSchemas;
}
public static List<String> getAllTAPSchemasNames(Connection connection, final List<String> allSchemas) throws SQLException {
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>();
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")) {
} else if (shortTableName.equals("tables")) {
} else if (shortTableName.equals("columns")) {
} else if (shortTableName.equals("keys")) {
} else if (shortTableName.equals("key_columns")) {
tapSchemaChecker.key_columns = true;
}
}
if (tapSchemaChecker.isTAPSchema()) {
allTAPSchemas.add(schemaName);
}
}
public static List<String> getExposedSchemas(Connection connection, 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(connection);
protected static EntityManager getEntityManager(Credentials credentials, String tapSchemaName) {
Map<String, String> persistenceMap = new HashMap<String, String>();
persistenceMap.put("javax.persistence.jdbc.url", "jdbc:mysql://" + credentials.getHostname() + ":" + credentials.getPort() + "/" + tapSchemaName);
persistenceMap.put("javax.persistence.jdbc.user", credentials.getUsername());
persistenceMap.put("javax.persistence.jdbc.password", credentials.getPassword());
persistenceMap.put("javax.persistence.jdbc.driver", credentials.getDatabaseDriverClass());
EntityManagerFactory managerFactory = Persistence.createEntityManagerFactory("pu", persistenceMap);
return managerFactory.createEntityManager();
}
protected static void createDatabase(Connection connection, String tapSchemaName) throws SQLException {
Statement statement = connection.createStatement();
statement.executeUpdate("CREATE DATABASE IF NOT EXISTS " + tapSchemaName);
statement.close();
}
protected static ArrayList<String> getAllTablesNames(Connection connection, 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(connection);
return allTables;
}
/**
* 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 connection
* @param schemaName
* @return
* @throws SQLException
*/
protected static List<KeyEntity> getSchemaKeys(final Connection connection, 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(connection);
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 connection, 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();
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
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);
}
// STD TAP_SCHEMA
// boolean std = false;
// if ((schemaName.equals(tapSchemaName))
// && !equalsOneOf(columnName, "schemaID", "tableID", "columnID", "keyID", "key_columnID")) {
// std = true;
// }
// column.setStd(std ? 1 : 0);
allColumns.add(columnInfo);
}
}).read(connection);