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;
}
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
public static List<String> getAllTAPSchemasNames(Connection connection, 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 tableName = resultSet.getString(1);
if (tableName.equals("schemas")) {
tapSchemaChecker.schemas = true;
} else if (tableName.equals("tables")) {
tapSchemaChecker.tables = true;
} else if (tableName.equals("columns")) {
tapSchemaChecker.columns = true;
} else if (tableName.equals("keys")) {
tapSchemaChecker.keys = true;
} else if (tableName.equals("key_columns")) {
tapSchemaChecker.key_columns = true;
}
}
}).read(connection);
if (tapSchemaChecker.isTAPSchema()) {
allTAPSchemas.add(schemaName);
}
}
return allTAPSchemas;
}
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);
return exposedSchemas;
}
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 {
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
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.setFromTableName(fromTable);
keyEntity.setTargetTableName(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 String schemaName, final String tableName, final Map<String, ColumnEntity> alreadyLoadedColumns) throws SQLException {
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
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
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
final List<ColumnInfo> allColumns = new ArrayList<ColumnInfo>();
(new ResultSetReader("SHOW COLUMNS FROM `" + schemaName + "`.`" + tableName + "`;") {
@Override
public void manipulateItem(ResultSet resultSet) throws SQLException {
String columnName = resultSet.getString("Field");
boolean alreadyLoaded = alreadyLoadedColumns != null && alreadyLoadedColumns.containsKey(columnName);
ColumnEntity columnEntity;
if (alreadyLoaded) {
columnEntity = alreadyLoadedColumns.get(columnName);
} else {
columnEntity = new ColumnEntity(columnName);
}
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);
}
// 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);
return allColumns;
}
}