/* * _____________________________________________________________________________ * * 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.ia2.tsm; import it.inaf.ia2.tsm.datalayer.DatabaseType; import it.inaf.ia2.tsm.datalayer.DBWrapper; 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.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 Key( // dbWrapper, // tapSchema, // resultSet.getString("from_schema"), // resultSet.getString("from_table"), // resultSet.getString("target_schema"), // resultSet.getString("target_table") // ); // schemaKeys.put(constraintName, key); // } // // ((Key) key).addKeyColumn(resultSet.getString("from_column"), resultSet.getString("target_column")); // } // } // // return new ArrayList<>(schemaKeys.values()); // // } else if (dbType == DatabaseType.POSTGRES) { // // String databaseName // = schemaName.equals(tapSchema.getName()) // ? dbWrapper.getTapSchemaCredentials().getDatabase() // : dbWrapper.getSourceCredentials().getDatabase(); // // 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 Key(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()) { // ((Key) 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 : ((TapSchema) 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); // } // } // // // Building query for the keys table // 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(); // // // Building query for the key_columns table // 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 keyId = rsKeys.getString(Key.ID_KEY); // 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) { // tapSchema.getConsistencyChecks().addUnexistingKey(keyId); // } else { // Table fromTable = fromSchema.getChild(fromTableName); // if (fromTable == null) { // tapSchema.getConsistencyChecks().addUnexistingKey(keyId); // } else { // // 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)); // } // ((Key) 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 && !tapSchema.getConsistencyChecks().getUnexistingKeys().contains(keyId)) { // String fromTableCompleteName = rsKeys.getString(Key.FROM_TABLE_KEY); // String targetTableCompleteName = rsKeys.getString(Key.TARGET_TABLE_KEY); // Key key = new Key(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 = ((Key) 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 // ((TapSchema) 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 = ((TapSchema) 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(); // } // } // } // } // } }