/* * _____________________________________________________________________________ * * 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.Column; 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.Status; 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.TapSchemaVersion; 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 interacting with databases. * * @author Sonia Zorba {@literal } */ public class Dao { private static final Logger log = LoggerFactory.getLogger(Dao.class); /** * Creates the TAP_SCHEMA schema and its tables. */ private static void createTapSchemaStructure(DatabaseType dbType, Connection conn, TapSchema tapSchema) throws SQLException { String tapSchemaName = tapSchema.getName(); TapSchemaVersion version = tapSchema.getVersion(); if (dbType == DatabaseType.MYSQL) { try (Statement statement = conn.createStatement()) { ///////////////////////////////////// // CREATE DATABASE // ///////////////////////////////////// String queryString = String.format("CREATE DATABASE IF NOT EXISTS `%s`", tapSchemaName); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); StringBuilder querySb = new StringBuilder(); ///////////////////////////////////// // CREATE schemas TABLE // ///////////////////////////////////// querySb.append("CREATE TABLE IF NOT EXISTS `"); querySb.append(tapSchemaName); querySb.append("`.`schemas` (\n"); querySb.append("schema_name varchar(64),\n"); querySb.append("utype varchar(512) NULL,\n"); querySb.append("description varchar(512) NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("schemaID bigint,\n"); } querySb.append("PRIMARY KEY (schema_name))"); queryString = querySb.toString(); log.debug("Creating \"schemas\" table"); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); ///////////////////////////////////// // CREATE tables TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS `"); querySb.append(tapSchemaName); querySb.append("`.tables (\n"); querySb.append("schema_name varchar(64),\n"); querySb.append("table_name varchar(128),\n"); querySb.append("table_type varchar(8),\n"); querySb.append("utype varchar(512) NULL,\n"); querySb.append("description varchar(512) NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("tableID bigint,\n"); } querySb.append("PRIMARY KEY (table_name),\n"); querySb.append("FOREIGN KEY (schema_name) REFERENCES `"); querySb.append(tapSchemaName); querySb.append("`.`schemas` (schema_name))"); queryString = querySb.toString(); log.debug("Creating \"tables\" table"); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); ///////////////////////////////////// // CREATE columns TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS `"); querySb.append(tapSchemaName); querySb.append("`.columns (\n"); querySb.append("table_name varchar(128),\n"); querySb.append("column_name varchar(64),\n"); querySb.append("utype varchar(512) NULL,\n"); querySb.append("ucd varchar(64) NULL,\n"); querySb.append("unit varchar(64) NULL,\n"); querySb.append("description varchar(512) NULL,\n"); querySb.append("datatype varchar(64) NOT NULL,\n"); querySb.append("size integer NULL,\n"); querySb.append("principal integer NOT NULL,\n"); querySb.append("indexed integer NOT NULL,\n"); querySb.append("std integer NOT NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("columnID bigint,\n"); } querySb.append("PRIMARY KEY (table_name, column_name),\n"); querySb.append("FOREIGN KEY (table_name) REFERENCES `"); querySb.append(tapSchemaName); querySb.append("`.tables (table_name))"); queryString = querySb.toString(); log.debug("Creating \"columns\" table"); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); ///////////////////////////////////// // CREATE keys TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS `"); querySb.append(tapSchemaName); querySb.append("`.keys (\n"); querySb.append("key_id varchar(64),\n"); querySb.append("from_table varchar(128) NOT NULL,\n"); querySb.append("target_table varchar(128) NOT NULL,\n"); querySb.append("utype varchar(512) NULL,\n"); querySb.append("description varchar(512) NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("keyID bigint,\n"); } querySb.append("PRIMARY KEY (key_id),\n"); querySb.append("FOREIGN KEY (from_table) REFERENCES `"); querySb.append(tapSchemaName); querySb.append("`.tables (table_name),\n"); querySb.append("FOREIGN KEY (target_table) REFERENCES `"); querySb.append(tapSchemaName); querySb.append("`.tables (table_name))"); queryString = querySb.toString(); log.debug("Creating \"keys\" table"); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); ///////////////////////////////////// // CREATE key_columns TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS `"); querySb.append(tapSchemaName); querySb.append("`.key_columns (\n"); querySb.append("key_id varchar(64),\n"); querySb.append("from_column varchar(64) NOT NULL,\n"); querySb.append("target_column varchar(64) NOT NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("key_columnID bigint,\n"); } querySb.append("FOREIGN KEY (key_id) REFERENCES `"); querySb.append(tapSchemaName); querySb.append("`.keys (key_id))"); queryString = querySb.toString(); log.debug("Creating \"key_columns\" table"); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); } } else if (dbType == DatabaseType.POSTGRES) { try (Statement statement = conn.createStatement()) { String tapSchemaNameEscaped = TSMUtil.escapeName(tapSchemaName, dbType); statement.executeUpdate("CREATE SCHEMA IF NOT EXISTS " + tapSchemaNameEscaped); StringBuilder querySb = new StringBuilder(); ///////////////////////////////////// // CREATE schemas TABLE // ///////////////////////////////////// querySb.append("CREATE TABLE IF NOT EXISTS "); querySb.append(tapSchemaNameEscaped); querySb.append(".schemas (\n"); querySb.append("schema_name character varying(64) NOT NULL,\n"); querySb.append("description character varying(512),\n"); if (TSMUtil.isIA2(version)) { querySb.append("schemaid bigint,\n"); } querySb.append("utype character varying(512))"); String queryString = querySb.toString(); log.debug("Creating \"schemas\" table"); log.debug("Executing query {}", queryString); int updateResult = statement.executeUpdate(queryString); if (updateResult > 0) { queryString = "ALTER TABLE ONLY schemas ADD CONSTRAINT schemas_pkey PRIMARY KEY (schema_name)"; log.debug("Adding constraints to \"schemas\" table"); log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); } ///////////////////////////////////// // CREATE tables TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS "); querySb.append(tapSchemaNameEscaped); querySb.append(".tables (\n"); querySb.append("table_name character varying(128) NOT NULL,\n"); querySb.append("description character varying(512),\n"); querySb.append("schema_name character varying(64),\n"); if (TSMUtil.isIA2(version)) { querySb.append("tableid bigint,\n"); } querySb.append("table_type character varying(8),\n"); querySb.append("utype character varying(512))"); queryString = querySb.toString(); log.debug("Creating \"tables\" table"); log.debug("Executing query {}", queryString); updateResult = statement.executeUpdate(queryString); if (updateResult > 0) { log.debug("Adding constraints to \"tables\" table"); queryString = "ALTER TABLE ONLY tables ADD CONSTRAINT tables_pkey PRIMARY KEY (table_name)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); queryString = "ALTER TABLE ONLY tables ADD CONSTRAINT fk_tables_schema_name FOREIGN KEY (schema_name) REFERENCES schemas(schema_name)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); } ///////////////////////////////////// // CREATE columns TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS "); querySb.append(tapSchemaNameEscaped); querySb.append(".columns (\n"); querySb.append("table_name character varying(128) NOT NULL,\n"); querySb.append("column_name character varying(64) NOT NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("columnid bigint,\n"); } querySb.append("datatype character varying(64),\n"); querySb.append("description character varying(512),\n"); querySb.append("id integer,\n"); querySb.append("indexed boolean,\n"); querySb.append("principal boolean,\n"); querySb.append("size integer,\n"); querySb.append("std boolean,\n"); querySb.append("ucd character varying(64),\n"); querySb.append("unit character varying(64),\n"); querySb.append("utype character varying(512))"); queryString = querySb.toString(); log.debug("Creating \"columns\" table"); log.debug("Executing query {}", queryString); updateResult = statement.executeUpdate(queryString); if (updateResult > 0) { log.debug("Adding constraints to \"columns\" table"); queryString = "ALTER TABLE ONLY columns ADD CONSTRAINT columns_pkey PRIMARY KEY (table_name, column_name)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); queryString = "ALTER TABLE ONLY columns ADD CONSTRAINT fk_columns_table_name FOREIGN KEY (table_name) REFERENCES tables(table_name)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); } ///////////////////////////////////// // CREATE keys TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS "); querySb.append(tapSchemaNameEscaped); querySb.append(".keys (\n"); querySb.append("key_id character varying(64) NOT NULL,\n"); querySb.append("description character varying(512),\n"); querySb.append("from_table character varying(128),\n"); if (TSMUtil.isIA2(version)) { querySb.append("keyid bigint,\n"); } querySb.append("target_table character varying(128),\n"); querySb.append("utype character varying(512))"); queryString = querySb.toString(); log.debug("Creating \"keys\" table"); log.debug("Executing query {}", queryString); updateResult = statement.executeUpdate(queryString); if (updateResult > 0) { log.debug("Adding constraints to \"keys\" table"); queryString = "ALTER TABLE ONLY keys ADD CONSTRAINT keys_pkey PRIMARY KEY (key_id)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); queryString = "ALTER TABLE ONLY keys ADD CONSTRAINT \"FK_keys_from_table\" FOREIGN KEY (from_table) REFERENCES tables(table_name)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); queryString = "ALTER TABLE ONLY keys ADD CONSTRAINT \"FK_keys_target_table\" FOREIGN KEY (target_table) REFERENCES tables(table_name)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); } ///////////////////////////////////// // CREATE key_columns TABLE // ///////////////////////////////////// querySb = new StringBuilder(); querySb.append("CREATE TABLE IF NOT EXISTS "); querySb.append(tapSchemaNameEscaped); querySb.append(".key_columns (\n"); querySb.append("from_column character varying(64) NOT NULL,\n"); querySb.append("target_column character varying(64) NOT NULL,\n"); if (TSMUtil.isIA2(version)) { querySb.append("key_columnid bigint,\n"); } querySb.append("key_id character varying(64) NOT NULL)"); queryString = querySb.toString(); log.debug("Creating \"key_columns\" table"); log.debug("Executing query {}", queryString); updateResult = statement.executeUpdate(queryString); if (updateResult > 0) { log.debug("Adding constraints to \"key_columns\" table"); queryString = "ALTER TABLE ONLY key_columns ADD CONSTRAINT key_columns_pkey PRIMARY KEY (from_column, target_column, key_id)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); queryString = "ALTER TABLE ONLY key_columns ADD CONSTRAINT fk_key_columns_key_id FOREIGN KEY (key_id) REFERENCES keys(key_id)"; log.debug("Executing query {}", queryString); statement.executeUpdate(queryString); } } } else { throw new UnsupportedOperationException("Database type " + dbType + " not supported"); } } protected static void save(DBWrapper dbWrapper, TapSchema tapSchema) throws SQLException { log.debug("Saving TAP_SCHEMA"); DatabaseType dbType = dbWrapper.getTapSchemaDatabaseType(); DataSource dataSource = dbWrapper.getTapSchemaDataSource(); Connection connection = null; PreparedStatement statement = null; boolean transactionStarted = false; try { connection = dataSource.getConnection(); UpdateOperations operations = new UpdateOperations(tapSchema); if (!tapSchema.exists()) { createTapSchemaStructure(dbType, connection, tapSchema); } // Start update connection.setAutoCommit(false); // start transaction transactionStarted = true; String tapSchemaNameEscaped = TSMUtil.escapeName(tapSchema.getName(), dbType); // REMOVE ELEMENTS if (tapSchema.exists()) { for (Key key : operations.getKeysToRemove()) { String keyId = key.getId(); String query = String.format("DELETE FROM %s.%s WHERE key_id = ?", tapSchemaNameEscaped, TSMUtil.escapeName("key_columns", dbType)); statement = connection.prepareStatement(query); statement.setString(1, keyId); log.debug("Executing query {} [key_id={}]", query, keyId); statement.executeUpdate(); query = String.format("DELETE FROM %s.%s WHERE key_id = ?", tapSchemaNameEscaped, TSMUtil.escapeName("keys", dbType)); statement = connection.prepareStatement(query); statement.setString(1, keyId); log.debug("Executing query {} [key_id={}]", query, keyId); statement.executeUpdate(); } for (Column column : operations.getColumnsToRemove()) { String query = String.format("DELETE FROM %s.%s WHERE table_name = ? AND column_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("columns", dbType)); statement = connection.prepareStatement(query); String tableName = column.getTableCompleteName(); String columnName = column.getName(); statement.setString(1, tableName); statement.setString(2, columnName); log.debug("Executing query {} [table_name={}, column_name={}]", query, tableName, columnName); statement.executeUpdate(); } for (Table table : operations.getTablesToRemove()) { String query = String.format("DELETE FROM %s.%s WHERE table_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("tables", dbType)); statement = connection.prepareStatement(query); String tableCompleteName = table.getCompleteName(); statement.setString(1, tableCompleteName); log.debug("Executing query {} [table_name={}]", query, tableCompleteName); statement.executeUpdate(); } for (Schema schema : operations.getSchemasToRemove()) { String query = String.format("DELETE FROM %s.%s WHERE schema_name = ?", tapSchemaNameEscaped, TSMUtil.escapeName("schemas", dbType)); statement = connection.prepareStatement(query); String schemaName = schema.getName(); statement.setString(1, schemaName); log.debug("Executing query {} [schema_name={}]", query, schemaName); statement.executeUpdate(); } } // INSERT ELEMENTS if (!operations.getSchemasToAdd().isEmpty()) { log.debug("Inserting {} new schemas", operations.getSchemasToAdd().size()); } for (Schema schema : operations.getSchemasToAdd()) { DaoSchema.insertNewSchema(dbType, connection, tapSchema, schema); } if (!operations.getTablesToAdd().isEmpty()) { log.debug("Inserting {} new tables", operations.getTablesToAdd().size()); } for (Table table : operations.getTablesToAdd()) { DaoTable.insertNewTable(dbType, connection, tapSchema, table); } if (!operations.getColumnsToAdd().isEmpty()) { log.debug("Inserting {} new columns", operations.getColumnsToAdd().size()); } for (Column column : operations.getColumnsToAdd()) { DaoColumn.insertNewColumn(dbType, connection, tapSchema, column); } if (!operations.getKeysToAdd().isEmpty()) { log.debug("Inserting {} new keys", operations.getKeysToAdd().size()); } for (Key key : operations.getKeysToAdd()) { // insert new keys and their key columns DaoKey.insertNewKey(dbType, connection, tapSchema, key); } //UPDATE ELEMENTS if (tapSchema.exists()) { for (Key key : operations.getKeysToUpdate()) { // update keys and their key columns DaoKey.updateKey(dbType, connection, tapSchema, key); } for (Schema schema : operations.getSchemasToUpdate()) { DaoSchema.updateSchema(dbType, connection, tapSchema, schema); } for (Table table : operations.getTablesToUpdate()) { DaoTable.updateTable(dbType, connection, tapSchema, table); } for (Column column : operations.getColumnsToUpdate()) { DaoColumn.updateColumn(dbType, connection, tapSchema, column); } } connection.commit(); // Status cleanup after commit // added for (Key key : operations.getKeysToAdd()) { key.save(); } for (Schema schema : operations.getSchemasToAdd()) { schema.save(); } for (Table table : operations.getTablesToAdd()) { table.save(); } for (Column column : operations.getColumnsToAdd()) { column.save(); } // removed for (Key key : operations.getKeysToRemove()) { key.initProperty(Key.ID_KEY, null); for (KeyColumn keyColumn : key.getKeyColumns()) { keyColumn.initProperty(KeyColumn.KEY_ID_KEY, null); } } for (Column column : operations.getColumnsToRemove()) { column.setStatus(Status.LOADED); } for (Table table : operations.getTablesToRemove()) { Schema schema = tapSchema.getChild(table.getSchemaName()); if (schema != null) { ((SchemaImpl) schema).cleanTable(table.getName()); } } for (Schema schema : operations.getSchemasToRemove()) { ((TapSchemaImpl) tapSchema).cleanSchema(schema.getName()); } // updated for (Key key : operations.getKeysToUpdate()) { key.save(); } for (Schema schema : operations.getSchemasToUpdate()) { schema.save(); } for (Table table : operations.getTablesToUpdate()) { table.save(); } for (Column column : operations.getColumnsToUpdate()) { column.save(); } } catch (SQLException e) { log.error("Exception caught", e); try { if (connection != null && transactionStarted) { log.debug("Executing rollback"); connection.rollback(); } } catch (SQLException e2) { log.error("Exception caught", e2); } throw e; } finally { if (connection != null) { try { if (statement != null) { statement.close(); } connection.close(); } catch (SQLException e2) { log.error("Exception caught", e2); } } } } public static List getAllTAPSchemasNames(DBWrapper dbs) throws SQLException { List allSchemas = DaoSchema.getAllSchemasNames(dbs.getTapSchemaDataSource(), dbs.getTapSchemaDatabaseType()); return getAllTAPSchemasNames(dbs, allSchemas); } /** * Retrieve the list of all TAP_SCHEMA schemas names contained in the * TAP_SCHEMA DataSource.
* TAP_SCHEMA schemas are selected simply checking if they contains the * TAP_SCHEMA standard tables. Currently no check on columns is performed. * * @param allSchemas usually the TAP_SCHEMA schemas list is loaded together * the list of all schemas, so this list is passed by parameter to avoid * repeating the query twice. * * @return list of all TAP_SCHEMA schemas names alphabetically and case * insensitively ordered. */ public static List getAllTAPSchemasNames(DBWrapper dbs, List allSchemas) throws SQLException { List allTAPSchemas = new ArrayList<>(); for (String schemaName : allSchemas) { boolean schemas = false, tables = false, columns = false, keys = false, keyColumns = false; DatabaseType dbType = dbs.getTapSchemaDatabaseType(); String query; if (dbType == DatabaseType.MYSQL) { query = "SHOW TABLES FROM `" + schemaName + "`"; } else if (dbType == DatabaseType.POSTGRES) { query = "SELECT tablename FROM pg_catalog.pg_tables where schemaname = '" + schemaName + "'"; } else { throw new UnsupportedOperationException("Database type " + dbType + " not supported"); } log.debug("Executing query {}", query); try (Connection connection = dbs.getTapSchemaConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query)) { while (resultSet.next()) { String shortTableName = resultSet.getString(1); if (null != shortTableName) { switch (shortTableName) { case "schemas": schemas = true; break; case "tables": tables = true; break; case "columns": columns = true; break; case "keys": keys = true; break; case "key_columns": keyColumns = true; break; } } } } if (schemas && tables && columns && keys && keyColumns) { // the schema is a TAP_SCHEMA allTAPSchemas.add(schemaName); } } log.debug("{} TAP_SCHEMA schemas found", allTAPSchemas.size()); return TSMUtil.sortStringsList(allTAPSchemas); } /** * Retrieve the list of the name of the schemas exposed by the TAP_SCHEMA * specified by the tapSchemaName parameter. * * @return list of exposed schemas names alphabetically and case * insensitively ordered. */ public static List getExposedSchemas(DBWrapper dbs, String tapSchemaName) throws SQLException { final List exposedSchemas = new ArrayList<>(); DatabaseType dbType = dbs.getTapSchemaDatabaseType(); String query; if (dbType == DatabaseType.MYSQL) { query = "SELECT schema_name FROM `" + tapSchemaName + "`.`schemas`"; } else if (dbType == DatabaseType.POSTGRES) { query = "SELECT schema_name FROM \"" + tapSchemaName + "\".\"schemas\""; } else { throw new UnsupportedOperationException("Database type " + dbType + " not supported"); } log.debug("Executing query " + query); try (Connection connection = dbs.getTapSchemaConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query)) { while (resultSet.next()) { exposedSchemas.add(resultSet.getString(1)); } } return exposedSchemas; } }