Newer
Older
/*
* _____________________________________________________________________________
*
* INAF - OATS National Institute for Astrophysics - Astronomical Observatory of
* Trieste INAF - IA2 Italian Center for Astronomical Archives
* _____________________________________________________________________________
*
* Copyright (C) 2017 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.datalayer;
import it.inaf.ia2.tsm.Column;
import it.inaf.ia2.tsm.ColumnHolder;
import it.inaf.ia2.tsm.ConsistencyChecks;
import it.inaf.ia2.tsm.Key;
import it.inaf.ia2.tsm.KeyColumn;
import it.inaf.ia2.tsm.Schema;
import it.inaf.ia2.tsm.Status;
import it.inaf.ia2.tsm.TSMUtil;
import it.inaf.ia2.tsm.Table;
import it.inaf.ia2.tsm.TapSchema;
import it.inaf.ia2.tsm.TapSchemaEntity;
import it.inaf.ia2.tsm.UpdateOperations;
import it.inaf.ia2.tsm.model.ColumnModel;
import it.inaf.ia2.tsm.model.TableModel;
import it.inaf.ia2.tsm.model.SchemaModel;
import it.inaf.ia2.tsm.model.SchemaModels;
import it.inaf.ia2.tsm.model.TypesMapping;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Template class containing all {@link DBBroker} features that are not vendor
* specific.
*
* @author Sonia Zorba {@literal <zorba at oats.inaf.it>}
*/
public abstract class DBBrokerTemplate implements DBBroker {
private static final Logger LOG = LoggerFactory.getLogger(DBBrokerTemplate.class);
protected final DataSource dataSource;
private final char escapeCharacter;
Sonia Zorba
committed
private final DataTypeMode dataTypeMode;
Sonia Zorba
committed
public DBBrokerTemplate(DataSource dataSource, char escapeCharacter, DataTypeMode dataTypeMode) {
this.dataSource = dataSource;
this.escapeCharacter = escapeCharacter;
Sonia Zorba
committed
this.dataTypeMode = dataTypeMode;
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
}
protected List<String> getAllItemsNames(String query) throws SQLException {
List<String> allSchemas = new ArrayList<>();
LOG.debug("Executing query: {}", query);
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query)) {
while (resultSet.next()) {
allSchemas.add(resultSet.getString(1));
}
}
Collections.sort(allSchemas, String.CASE_INSENSITIVE_ORDER);
return allSchemas;
}
protected String buildColumnsList(String[] columns) {
StringBuilder sb = new StringBuilder();
boolean first = true;
for (String keyColumn : columns) {
if (!first) {
sb.append(",");
}
first = false;
sb.append(escape(keyColumn));
}
return sb.toString();
}
protected String escape(String name) {
return String.format("%s%s%s", escapeCharacter, name, escapeCharacter);
}
protected void appendSize(StringBuilder sb, int size) {
sb.append(String.format("(%s)", size));
}
/**
* {@inheritDoc}
*/
@Override
public void createTable(String schemaName, TableModel tableModel) throws SQLException {
try (Connection conn = dataSource.getConnection()) {
createTable(schemaName, tableModel, conn);
}
}
protected abstract void createTable(String schemaName, TableModel tableModel, Connection conn) throws SQLException;
protected abstract String getAddPrimaryKeyQuery(String tapSchemaName, String tableName, String[] keyColumns);
protected abstract String getAddForeignKeyQuery(String tapSchemaName, String tableName, String[] fromKeyColumns, String targetTableName, String[] toKeyColumns);
private String getAddPrimaryKeyQuery(String tapSchemaName, String tableName, String keyColumn) {
return getAddPrimaryKeyQuery(tapSchemaName, tableName, new String[]{keyColumn});
private String getAddForeignKeyQuery(String tapSchemaName, String tableName, String fromKeyColumn, String targetTableName, String toKeyColumn) {
return getAddForeignKeyQuery(tapSchemaName, tableName, new String[]{fromKeyColumn}, targetTableName, new String[]{toKeyColumn});
protected abstract String getCreateDatabaseQuery(String databaseName);
private void execute(String query, Connection conn) throws SQLException {
try (Statement stat = conn.createStatement()) {
LOG.debug("Executing query: {}", query);
stat.execute(query);
}
}
/**
* {@inheritDoc}
*/
public void createTapSchemaStructure(String tapSchemaName, SchemaModel tapSchemaModel) throws SQLException {
try (Connection conn = dataSource.getConnection()) {
execute(getCreateDatabaseQuery(tapSchemaName), conn);
for (TableModel tableModel : tapSchemaModel.getTables()) {
createTable(tapSchemaName, tableModel, conn);
}
// schemas keys
execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.SCHEMAS_TABLE, Schema.SCHEMA_NAME_KEY), conn);
// tables keys
execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);
execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.TABLES_TABLE, Table.SCHEMA_NAME_KEY, TapSchema.SCHEMAS_TABLE, Schema.SCHEMA_NAME_KEY), conn);
// columns keys
execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.COLUMNS_TABLE, new String[]{Column.TABLE_NAME_KEY, Column.COLUMN_NAME_KEY}), conn);
execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.COLUMNS_TABLE, Column.TABLE_NAME_KEY, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);
// keys keys
execute(getAddPrimaryKeyQuery(tapSchemaName, TapSchema.KEYS_TABLE, Key.ID_KEY), conn);
execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.KEYS_TABLE, Key.FROM_TABLE_KEY, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);
execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.KEYS_TABLE, Key.TARGET_TABLE_KEY, TapSchema.TABLES_TABLE, Table.TABLE_NAME_KEY), conn);
// key columns key
//addPrimaryKey(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, new String[]{KeyColumn.KEY_ID_KEY, KeyColumn.FROM_COLUMN_KEY, KeyColumn.TARGET_COLUMN_KEY}, conn);
execute(getAddForeignKeyQuery(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, Key.ID_KEY, TapSchema.KEYS_TABLE, Key.ID_KEY), conn);
//addForeignKey(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, KeyColumn.FROM_COLUMN_KEY, TapSchema.COLUMNS_TABLE, Column.COLUMN_NAME_KEY, conn);
//addForeignKey(tapSchemaName, TapSchema.KEY_COLUMNS_TABLE, KeyColumn.TARGET_COLUMN_KEY, TapSchema.COLUMNS_TABLE, Column.COLUMN_NAME_KEY, conn);
}
}
/**
* {@inheritDoc}
*/
public void createIvoaSchemaStructure(SchemaModel ivoaSchemaModel, String realIvoaSchemaName) throws SQLException {
try (Connection conn = dataSource.getConnection()) {
execute(getCreateDatabaseQuery(realIvoaSchemaName), conn);
for (TableModel tableModel : ivoaSchemaModel.getTables()) {
createTable(realIvoaSchemaName, tableModel, conn);
Loading full blame...