Newer
Older
package it.inaf.oats.ia2.tapschemamanager.api;
import it.inaf.oats.ia2.tapschemamanager.api.contract.DatabaseType;
import it.inaf.oats.ia2.tapschemamanager.api.contract.Schema;
import it.inaf.oats.ia2.tapschemamanager.api.contract.Status;
import it.inaf.oats.ia2.tapschemamanager.api.contract.TapSchema;
import it.inaf.oats.ia2.tapschemamanager.api.contract.TapSchemaEntity;
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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
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.List;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Utility class that contains static methods for managing {@link Schema}s into
* the database.
*
* @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
*/
public class DaoSchema {
private static final Logger log = LoggerFactory.getLogger(DaoSchema.class);
/**
* Retrieve the list of the names of the all the schemas contained into the
* database specified by the <code>DataSource</code> parameter.
*
* @return list of schemas names alphabetically and case insensitively
* ordered.
*/
public static List<String> getAllSchemasNames(DataSource dataSource, DatabaseType dbType) throws SQLException {
log.debug("getAllSchemasNames");
String query;
if (dbType == DatabaseType.MYSQL) {
query = "SHOW DATABASES";
} else if (dbType == DatabaseType.POSTGRES) {
query = "SELECT schema_name FROM information_schema.schemata";
} else {
throw new UnsupportedOperationException("Database type " + dbType + " not supported");
}
log.debug("Executing query {}", query);
List<String> allSchemas = new ArrayList<>();
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query)) {
while (resultSet.next()) {
allSchemas.add(resultSet.getString(1));
}
}
log.debug("{} schemas found", allSchemas.size());
return TSMUtil.sortStringsList(allSchemas);
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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
}
/**
* Retrieves saved {@code Schema}s from the database and add them into the
* specified {@code TapSchema}.
*/
protected static void fillSavedSchemas(DBWrapper dbWrapper, final TapSchema tapSchema) throws SQLException {
log.debug("fillSavedSchemas");
SelectQueryBuilder selectQueryBuilder = new SelectQueryBuilder(dbWrapper.getTapSchemaDatabaseType(), tapSchema, TapSchema.SCHEMAS_TABLE) {
@Override
protected TapSchemaEntity getEntity(ResultSet rs) throws SQLException {
String schemaName = rs.getString("schema_name");
Schema schema = tapSchema.addChild(schemaName);
schema.setStatus(Status.ADDED_PERSISTED);
return schema;
}
};
selectQueryBuilder.executeQuery(dbWrapper.getTapSchemaConnection());
}
/**
* Save a new {@code Schema} into the TAP_SCHEMA schema.
*/
protected static void insertNewSchema(DatabaseType dbType, Connection connection, TapSchema tapSchema, Schema schema) throws SQLException {
log.debug("insertNewSchema");
InsertQueryBuilder insertQueryBuilder = new InsertQueryBuilder(dbType, tapSchema, schema, TapSchema.SCHEMAS_TABLE);
insertQueryBuilder.executeQuery(connection);
}
/**
* Updates an existing {@code Schema}.
*/
protected static void updateSchema(DatabaseType dbType, Connection connection, TapSchema tapSchema, Schema schema) throws SQLException {
UpdateQueryBuilder updateQueryBuilder = new UpdateQueryBuilder(dbType, tapSchema, schema, TapSchema.SCHEMAS_TABLE, "schema_name = ?");
String query = updateQueryBuilder.getQuery();
try (PreparedStatement statement = connection.prepareStatement(query)) {
log.debug("Executing query {}", query);
int i = updateQueryBuilder.addStatementValues(statement);
statement.setString(i, schema.getName());
statement.executeUpdate();
}
}
}