/* * _____________________________________________________________________________ * * INAF - OATS National Institute for Astrophysics - Astronomical Observatory of * Trieste INAF - IA2 Italian Center for Astronomical Archives * _____________________________________________________________________________ * * Copyright (C) 2016 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.oats.ia2.tapschemamanager.api; import it.inaf.oats.ia2.tapschemamanager.api.contract.DatabaseType; import it.inaf.oats.ia2.tapschemamanager.api.contract.Key; import it.inaf.oats.ia2.tapschemamanager.api.contract.KeyColumn; import it.inaf.oats.ia2.tapschemamanager.api.contract.Schema; import it.inaf.oats.ia2.tapschemamanager.api.contract.Table; import it.inaf.oats.ia2.tapschemamanager.api.contract.TapSchema; import it.inaf.oats.ia2.tapschemamanager.api.contract.TapSchemaEntity; 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.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Pattern; import javax.sql.DataSource; import org.postgresql.ds.PGPoolingDataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Utility class that contains static methods for managing {@link Key}s and * {@link KeyColumn}s into the database. * * @author Sonia Zorba {@literal } */ public class DaoKey { private static final Logger log = LoggerFactory.getLogger(DaoKey.class); /** * Generate list of KeyEntity for a given schema, specifying its * DataSource and its name.
* IMPORTANT: this keys are without id. The id has to be * set when a table is added to a schema. */ protected static List getSchemaKeys(DBWrapper dbWrapper, TapSchema tapSchema, String schemaName) throws SQLException { log.debug("getSchemaKeys"); DataSource dataSource = TSMUtil.getSchemaDataSource(dbWrapper, tapSchema, schemaName); DatabaseType dbType = TSMUtil.getSchemaDatabaseType(dbWrapper, tapSchema, schemaName); if (dbType == DatabaseType.MYSQL) { Map schemaKeys = new HashMap<>(); String query = "SELECT\n" + "c.`CONSTRAINT_NAME` AS constraint_name,\n" + "k.`TABLE_SCHEMA` AS from_schema,\n" + "k.`TABLE_NAME` AS from_table,\n" + "k.`COLUMN_NAME` AS from_column,\n" + "k.`REFERENCED_TABLE_SCHEMA` AS target_schema,\n" + "k.`REFERENCED_TABLE_NAME` AS target_table,\n" + "k.`REFERENCED_COLUMN_NAME` AS target_column\n" + "FROM information_schema.TABLE_CONSTRAINTS c \n" + "LEFT JOIN information_schema.KEY_COLUMN_USAGE k \n" + "ON c.`CONSTRAINT_NAME` = k.`CONSTRAINT_NAME` AND c.`TABLE_SCHEMA` = k.`TABLE_SCHEMA`\n" + "WHERE c.`CONSTRAINT_TYPE` = 'FOREIGN KEY' \n" + "AND k.`TABLE_SCHEMA` = '" + schemaName + "' OR k.`REFERENCED_TABLE_SCHEMA` = '" + schemaName + "'"; try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query)) { while (resultSet.next()) { String constraintName = resultSet.getString("constraint_name"); Key key = schemaKeys.get(constraintName); if (key == null) { key = new KeyImpl( dbWrapper, tapSchema, resultSet.getString("from_schema"), resultSet.getString("from_table"), resultSet.getString("target_schema"), resultSet.getString("target_table") ); schemaKeys.put(constraintName, key); } ((KeyImpl) key).addKeyColumn(resultSet.getString("from_column"), resultSet.getString("target_column")); } } return new ArrayList<>(schemaKeys.values()); } else if (dbType == DatabaseType.POSTGRES) { String databaseName = ((PGPoolingDataSource) dataSource).getDatabaseName(); List schemaKeys = new ArrayList<>(); String queryKeys = "SELECT\n" + "conname AS constraint_name,\n" + "conrelid::regclass AS from_table, \n" + "confrelid::regclass AS target_table\n" + "FROM pg_catalog.pg_constraint\n" + "WHERE contype = 'f'\n" + "AND ((conrelid::regclass || '' LIKE '" + schemaName + ".%')\n" + "OR (confrelid::regclass || '' LIKE '" + schemaName + ".%'))"; try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(queryKeys)) { log.debug("Executing query {}", queryKeys); while (resultSet.next()) { String constraintName = resultSet.getString("constraint_name"); String[] fromTableFullNameSplit = resultSet.getString("from_table").split(Pattern.quote(".")); String fromSchema = fromTableFullNameSplit[0]; String fromTable = fromTableFullNameSplit[1]; String[] targetTableFullNameSplit = resultSet.getString("target_table").split(Pattern.quote(".")); String targetSchema = targetTableFullNameSplit[0]; String targetTable = targetTableFullNameSplit[1]; Key key = new KeyImpl(dbWrapper, tapSchema, fromSchema, fromTable, targetSchema, targetTable); schemaKeys.add(key); // conkey conrelid String queryFromKC = "SELECT\n" + "c.column_name AS key_column\n" + "FROM information_schema.columns c\n" + "JOIN pg_catalog.pg_constraint r ON c.ordinal_position = ANY(r.conkey)\n" + "AND (c.table_schema || '.' || c.table_name) = (r.conrelid::regclass || '')\n" + "WHERE r.conname = '" + constraintName + "' AND r.contype = 'f'\n" + "AND c.table_schema = '" + fromSchema + "'\n" + "AND table_catalog = '" + databaseName + "'"; // as above, but with confkey and confrelid and different c.table_schema where condition String queryTargetKC = "SELECT\n" + "c.column_name AS key_column\n" + "FROM information_schema.columns c\n" + "JOIN pg_catalog.pg_constraint r ON c.ordinal_position = ANY(r.confkey)\n" + "AND (c.table_schema || '.' || c.table_name) = (r.confrelid::regclass || '')\n" + "WHERE r.conname = '" + constraintName + "' AND r.contype = 'f'\n" + "AND c.table_schema = '" + targetSchema + "'\n" + "AND table_catalog = '" + databaseName + "'"; try (Statement statFromKC = connection.createStatement(); Statement statTargetKC = connection.createStatement()) { try (ResultSet rsFromKC = statFromKC.executeQuery(queryFromKC); ResultSet rsTargetKC = statTargetKC.executeQuery(queryTargetKC)) { log.debug("Executing query {}", queryFromKC); log.debug("Executing query {}", queryTargetKC); while (rsFromKC.next()) { if (rsTargetKC.next()) { ((KeyImpl) key).addKeyColumn( rsFromKC.getString("key_column"), rsTargetKC.getString("key_column") ); } } } } } } return schemaKeys; } else { throw new UnsupportedOperationException("Database type " + dbType + " not supported"); } } /** * Retrieves saved {@code Key}s from the database and add them into the * specified {@code TapSchema}. */ protected static void fillSavedKeys(DBWrapper dbWrapper, TapSchema tapSchema) throws SQLException { log.debug("fillSavedKeys"); // We can decide to work only on from tables or target tables, because // the same key is contained on both. // Schemas and tables have to be already added to the TAP_SCHEMA. List allVisibleKeys = new ArrayList<>(); // Reset to null all generated keyId. for (Key key : ((TapSchemaImpl) tapSchema).getAllKeys()) { key.initProperty(Key.ID_KEY, null); // Meanwhile we add all the visible keys to this list for // further checks if (key.isVisible()) { allVisibleKeys.add(key); } } SelectQueryBuilder keysSelect = new SelectQueryBuilder(dbWrapper.getTapSchemaDatabaseType(), tapSchema, TapSchema.KEYS_TABLE) { @Override protected TapSchemaEntity getEntity(ResultSet rs) throws SQLException { throw new UnsupportedOperationException(); } }; String queryKeys = keysSelect.getQuery(); SelectQueryBuilder keyColumnsSelect = new SelectQueryBuilder(dbWrapper.getTapSchemaDatabaseType(), tapSchema, TapSchema.KEY_COLUMNS_TABLE) { @Override protected TapSchemaEntity getEntity(ResultSet rs) throws SQLException { throw new UnsupportedOperationException(); } }; String queryKeyColumns = String.format("%s WHERE %s = ?", keyColumnsSelect.getQuery(), TSMUtil.escapeName(KeyColumn.KEY_ID_KEY, dbWrapper.getTapSchemaDatabaseType())); boolean supportKeyID = EntityPropertyInfo.getEntityPropertyInfo(TapSchema.KEYS_TABLE, Key.KEY_ID_KEY).acceptVersion(tapSchema.getVersion()); boolean supportKeyColumnID = EntityPropertyInfo.getEntityPropertyInfo(TapSchema.KEY_COLUMNS_TABLE, KeyColumn.KEY_COLUMN_ID_KEY).acceptVersion(tapSchema.getVersion()); try (Connection conn = dbWrapper.getTapSchemaConnection()) { log.debug("Executing query {}", queryKeys); // ResultSet type and concurrency are necessary for PostgreSQL try (Statement statementKeys = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rsKeys = statementKeys.executeQuery(queryKeys)) { while (rsKeys.next()) { // Searching the keys. String fromTableCompleteNameSplit[] = rsKeys.getString(Key.FROM_TABLE_KEY).split(Pattern.quote(".")); String fromSchemaName = fromTableCompleteNameSplit[0]; String fromTableName = fromTableCompleteNameSplit[1]; Schema fromSchema = tapSchema.getChild(fromSchemaName); if (fromSchema == null) { throw new InconsistentTapSchemaException("Saved TAP_SCHEMA contains a key that is referred to a schema that wasn't added to that TAP_SCHEMA."); } Table fromTable = fromSchema.getChild(fromTableName); if (fromTable == null) { throw new InconsistentTapSchemaException("Saved TAP_SCHEMA contains a key that is referred to a table that wasn't added to that TAP_SCHEMA."); } String keyId = rsKeys.getString(Key.ID_KEY); // ResultSet type and concurrency are necessary for PostgreSQL try (PreparedStatement statementKeyColumns = conn.prepareStatement(queryKeyColumns, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) { statementKeyColumns.setString(1, keyId); log.debug("Executing query {} [key_id={}]", queryKeyColumns, keyId); try (ResultSet rsKeyColumns = statementKeyColumns.executeQuery()) { for (Key fromKey : fromTable.getAllFromKeys()) { boolean columnsFound = false; for (KeyColumn keyColumn : fromKey.getKeyColumns()) { columnsFound = false; rsKeyColumns.beforeFirst(); while (rsKeyColumns.next()) { String fromColumn = rsKeyColumns.getString(KeyColumn.FROM_COLUMN_KEY); String targetColumn = rsKeyColumns.getString(KeyColumn.TARGET_COLUMN_KEY); if (keyColumn.getFromColumn().equals(fromColumn) && keyColumn.getTargetColumn().equals(targetColumn)) { columnsFound = true; break; } } if (!columnsFound) { break; } } if (columnsFound) { // all columns found --> key found! // Updating key String keyDescription = rsKeys.getString(Key.DESCRIPTION_KEY); String keyUtype = rsKeys.getString(Key.UTYPE_KEY); fromKey.initProperty(Key.ID_KEY, keyId); fromKey.initProperty(Key.DESCRIPTION_KEY, keyDescription); fromKey.initProperty(Key.UTYPE_KEY, keyUtype); if (supportKeyID) { fromKey.initProperty(Key.KEY_ID_KEY, TSMUtil.getObject(rsKeys, Key.KEY_ID_KEY, Long.class)); } ((KeyImpl) fromKey).setVisible(true); // Updating key columns for (KeyColumn keyColumn : fromKey.getKeyColumns()) { rsKeyColumns.beforeFirst(); while (rsKeyColumns.next()) { String fromColumn = rsKeyColumns.getString(KeyColumn.FROM_COLUMN_KEY); String targetColumn = rsKeyColumns.getString(KeyColumn.TARGET_COLUMN_KEY); if (keyColumn.getFromColumn().equals(fromColumn) && keyColumn.getTargetColumn().equals(targetColumn)) { keyColumn.initProperty(KeyColumn.KEY_ID_KEY, keyId); if (supportKeyColumnID) { keyColumn.initProperty(KeyColumn.KEY_COLUMN_ID_KEY, TSMUtil.getObject(rsKeyColumns, KeyColumn.KEY_COLUMN_ID_KEY, Long.class)); } break; } } } break; } } } } } // Check if the saved TAP_SCHEMA contains keys that aren't loaded (fictitious keys). List fictitiousKeys = new ArrayList<>(); rsKeys.beforeFirst(); while (rsKeys.next()) { String keyId = rsKeys.getString(Key.ID_KEY); boolean keyIdFound = false; for (Key key : allVisibleKeys) { if (keyId.equals(key.getId())) { keyIdFound = true; break; } } if (!keyIdFound) { String fromTableCompleteName = rsKeys.getString(Key.FROM_TABLE_KEY); String targetTableCompleteName = rsKeys.getString(Key.TARGET_TABLE_KEY); KeyImpl key = new KeyImpl(dbWrapper, tapSchema, fromTableCompleteName, targetTableCompleteName); key.initProperty(Key.ID_KEY, keyId); if (supportKeyID) { key.initProperty(Key.KEY_ID_KEY, TSMUtil.getObject(rsKeys, Key.KEY_ID_KEY, Long.class)); } key.setVisible(true); fictitiousKeys.add(key); tapSchema.getChild(key.getFromSchemaName()).getChild(key.getFromTableSimpleName()).addFromKey(key); tapSchema.getChild(key.getTargetSchemaName()).getChild(key.getTargetTableSimpleName()).addTargetKey(key); } } // filling fictitious keys columns for (Key key : fictitiousKeys) { try (PreparedStatement statementKeyColumns = conn.prepareStatement(queryKeyColumns)) { String keyId = key.getId(); statementKeyColumns.setString(1, keyId); log.debug("Executing query {} [key_id={}]", queryKeyColumns, keyId); try (ResultSet rsKeyColumns = statementKeyColumns.executeQuery()) { while (rsKeyColumns.next()) { String fromColumn = rsKeyColumns.getString(KeyColumn.FROM_COLUMN_KEY); String targetColumn = rsKeyColumns.getString(KeyColumn.TARGET_COLUMN_KEY); KeyColumn keyColumn = ((KeyImpl) key).addKeyColumn(fromColumn, targetColumn); if (supportKeyColumnID) { keyColumn.initProperty(KeyColumn.KEY_COLUMN_ID_KEY, TSMUtil.getObject(rsKeyColumns, KeyColumn.KEY_COLUMN_ID_KEY, Long.class)); } } } } // adding fictitious key to key set ((TapSchemaImpl) tapSchema).getAllKeys().add(key); } if (!fictitiousKeys.isEmpty()) { log.debug("{} fictitious keys found", fictitiousKeys.size()); for (Key key : fictitiousKeys) { log.debug(" {}", key); } } // Check if there are remaining keys with keyId = null (valid keys // that weren't saved into the TAP_SCHEMA). int keyId = ((TapSchemaImpl) tapSchema).getMaxKeyId() + 1; for (Key key : allVisibleKeys) { if (key.getId() == null) { key.setId(keyId + ""); keyId++; } } } } } /** * Save a new {@code Key} into the TAP_SCHEMA schema. */ protected static void insertNewKey(DatabaseType dbType, Connection connection, TapSchema tapSchema, Key key) throws SQLException { log.debug("insertNewKey"); InsertQueryBuilder insertQueryBuilder = new InsertQueryBuilder(dbType, tapSchema, key, TapSchema.KEYS_TABLE); insertQueryBuilder.executeQuery(connection); for (KeyColumn keyColumn : key.getKeyColumns()) { insertQueryBuilder = new InsertQueryBuilder(dbType, tapSchema, keyColumn, TapSchema.KEY_COLUMNS_TABLE); insertQueryBuilder.executeQuery(connection); } } /** * Updates an existing {@code Key}. */ protected static void updateKey(DatabaseType dbType, Connection connection, TapSchema tapSchema, Key key) throws SQLException { log.debug("updateKey"); if (key.getId() == null) { throw new IllegalStateException("Unable to update key: key_id is null"); } boolean keyIdChanged = key.isChanged(Key.ID_KEY); if (keyIdChanged) { // Deleting key columns to avoid problem with foreign key constraint failures String tapSchemaNameEscaped = TSMUtil.escapeName(tapSchema.getName(), dbType); String keyColumnsNameEscaped = TSMUtil.escapeName("key_columns", dbType); String query = String.format("DELETE FROM %s.%s WHERE key_id = ?", tapSchemaNameEscaped, keyColumnsNameEscaped); try (PreparedStatement statement = connection.prepareStatement(query)) { String originalKey = key.getOriginalValue(Key.ID_KEY, String.class); statement.setString(1, originalKey); log.debug("Executing query {} [key_id={}]", query, originalKey); statement.executeUpdate(); } } // Updating keys UpdateQueryBuilder updateQueryBuilder = new UpdateQueryBuilder(dbType, tapSchema, key, TapSchema.KEYS_TABLE, "key_id = ?"); String query = updateQueryBuilder.getQuery(); try (PreparedStatement statement = connection.prepareStatement(query)) { int i = updateQueryBuilder.addStatementValues(statement); String keyId = key.getId(); statement.setString(i, keyId); log.debug("Executing query {} [key_id={}]", query, keyId); statement.executeUpdate(); } if (keyIdChanged) { // Re-insert deleted key columns for (KeyColumn keyColumn : key.getKeyColumns()) { InsertQueryBuilder insertQueryBuilder = new InsertQueryBuilder(dbType, tapSchema, keyColumn, TapSchema.KEY_COLUMNS_TABLE); insertQueryBuilder.executeQuery(connection); } } else { // Update key columns for (KeyColumn keyColumn : key.getKeyColumns()) { if (keyColumn.isChanged()) { updateQueryBuilder = new UpdateQueryBuilder(dbType, tapSchema, keyColumn, TapSchema.KEY_COLUMNS_TABLE, "key_id = ?"); query = updateQueryBuilder.getQuery(); try (PreparedStatement statement = connection.prepareStatement(query)) { int i = updateQueryBuilder.addStatementValues(statement); String keyId = key.getId(); statement.setString(i, keyId); log.debug("Executing query {} [key_id={}]", query, keyId); statement.executeUpdate(); } } } } } }