/**
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.apache.airavata.sharing.registry.db.utils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.StringTokenizer;
/**
* This class creates the database tables required for airavata with default configuration this
* class creates derby database in server mode. User can specify required database in appropriate
* properties files.
*/
public class DatabaseCreator {
private final static Logger logger = LoggerFactory.getLogger(DatabaseCreator.class);
public enum DatabaseType {
derby("(?i).*derby.*"), mysql("(?i).*mysql.*"), other("");
private String pattern;
private DatabaseType(String matchingPattern) {
this.pattern = matchingPattern;
}
public String getMatchingPattern() {
return this.pattern;
}
}
private static DatabaseType[] supportedDatabase = new DatabaseType[] { DatabaseType.derby, DatabaseType.mysql };
private static Logger log = LoggerFactory.getLogger(DatabaseCreator.class);
private static final String delimiter = ";";
/**
* Creates database
*
* @throws Exception
*/
public static void createRegistryDatabase(String prefix, Connection conn) throws Exception {
createDatabase(prefix, conn);
}
/**
* Checks whether database tables are created by using select * on given table name
*
* @param tableName
* Table which should be existed
* @return <code>true</core> if checkSQL is success, else <code>false</code> .
*/
public static boolean isDatabaseStructureCreated(String tableName, Connection conn) {
try {
log.debug("Running a query to test the database tables existence.");
// check whether the tables are already created with a query
Statement statement = null;
try {
statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from " + tableName);
if (rs != null) {
rs.close();
}
} finally {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
return false;
}
}
} catch (SQLException e) {
return false;
}
return true;
}
/**
* executes given sql
*
* @param sql
* @throws Exception
*/
private static void executeSQL(String sql, Connection conn) throws Exception {
// Check and ignore empty statements
if ("".equals(sql.trim())) {
return;
}
Statement statement = null;
try {
log.debug("SQL : " + sql);
boolean ret;
int updateCount = 0, updateCountTotal = 0;
statement = conn.createStatement();
ret = statement.execute(sql);
updateCount = statement.getUpdateCount();
do {
if (!ret) {
if (updateCount != -1) {
updateCountTotal += updateCount;
}
}
ret = statement.getMoreResults();
if (ret) {
updateCount = statement.getUpdateCount();
}
} while (ret);
log.debug(sql + " : " + updateCountTotal + " rows affected");
SQLWarning warning = conn.getWarnings();
while (warning != null) {
log.info(warning + " sql warning");
warning = warning.getNextWarning();
}
conn.clearWarnings();
} catch (SQLException e) {
if (e.getSQLState().equals("X0Y32")) {
// eliminating the table already exception for the derby
// database
log.info("Table Already Exists", e);
} else {
throw new Exception("Error occurred while executing : " + sql, e);
}
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
log.error("Error occurred while closing result set.", e);
}
}
}
}
/**
* computes relatational database type using database name
*
* @return DatabaseType
* @throws Exception
*
*/
public static DatabaseType getDatabaseType(Connection conn) throws Exception {
try {
if (conn != null && (!conn.isClosed())) {
DatabaseMetaData metaData = conn.getMetaData();
String databaseProductName = metaData.getDatabaseProductName();
return checkType(databaseProductName);
}
} catch (SQLException e) {
String msg = "Failed to create Airavata database." + e.getMessage();
log.error(msg, e);
throw new Exception(msg, e);
}
return DatabaseType.other;
}
/**
* Overloaded method with String input
*
* @return DatabaseType
* @throws Exception
*
*/
public static DatabaseType getDatabaseType(String dbUrl) throws Exception {
return checkType(dbUrl);
}
private static DatabaseType checkType(String text) throws Exception {
try {
if (text != null) {
for (DatabaseType type : supportedDatabase) {
if (text.matches(type.getMatchingPattern()))
return type;
}
}
String msg = "Unsupported database: " + text
+ ". Database will not be created automatically by the Airavata. "
+ "Please create the database using appropriate database scripts for " + "the database.";
throw new Exception(msg);
} catch (SQLException e) {
String msg = "Failed to create Airavatadatabase." + e.getMessage();
log.error(msg, e);
throw new Exception(msg, e);
}
}
/**
* Get scripts location which is prefix + "-" + databaseType + ".sql"
*
* @param prefix
* @param databaseType
* @return script location
*/
private static String getScriptLocation(String prefix, DatabaseType databaseType) {
String scriptName = prefix + "-" + databaseType + ".sql";
log.debug("Loading database script from :" + scriptName);
return scriptName;
}
private static void createDatabase(String prefix, Connection conn) throws Exception {
Statement statement = null;
try {
conn.setAutoCommit(false);
statement = conn.createStatement();
executeSQLScript(getScriptLocation(prefix, DatabaseCreator.getDatabaseType(conn)), conn);
conn.commit();
log.debug("Tables are created successfully.");
} catch (SQLException e) {
String msg = "Failed to create database tables for Airavata resource store. " + e.getMessage();
log.error(msg, e);
conn.rollback();
throw new Exception(msg, e);
} finally {
conn.setAutoCommit(true);
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
log.error("Failed to close statement.", e);
}
}
}
private static void executeSQLScript(String dbscriptName, Connection conn) throws Exception {
StringBuffer sql = new StringBuffer();
BufferedReader reader = null;
try {
InputStream is = DatabaseCreator.class.getClassLoader().getResourceAsStream(dbscriptName);
if(is == null) {
logger.info("Script file not found at " + dbscriptName + ". Uses default database script file");
DatabaseType databaseType = DatabaseCreator.getDatabaseType(conn);
if(databaseType.equals(DatabaseType.derby)){
is = DatabaseCreator.class.getClassLoader().getResourceAsStream("sharing-registry-derby.sql");
}else if(databaseType.equals(DatabaseType.mysql)){
is = DatabaseCreator.class.getClassLoader().getResourceAsStream("sharing-registry-mysql.sql");
}
}
reader = new BufferedReader(new InputStreamReader(is));
String line;
while ((line = reader.readLine()) != null) {
line = line.trim();
if (line.startsWith("//")) {
continue;
}
if (line.startsWith("--")) {
continue;
}
StringTokenizer st = new StringTokenizer(line);
if (st.hasMoreTokens()) {
String token = st.nextToken();
if ("REM".equalsIgnoreCase(token)) {
continue;
}
}
sql.append(" ").append(line);
// SQL defines "--" as a comment to EOL
// and in Oracle it may contain a hint
// so we cannot just remove it, instead we must end it
if (line.indexOf("--") >= 0) {
sql.append("\n");
}
if ((checkStringBufferEndsWith(sql, delimiter))) {
executeSQL(sql.substring(0, sql.length() - delimiter.length()), conn);
sql.replace(0, sql.length(), "");
}
}
// Catch any statements not followed by ;
if (sql.length() > 0) {
executeSQL(sql.toString(), conn);
}
} catch (IOException e) {
log.error("Error occurred while executing SQL script for creating Airavata database", e);
throw new Exception("Error occurred while executing SQL script for creating Airavata database", e);
} finally {
if (reader != null) {
reader.close();
}
}
}
/**
* Checks that a string buffer ends up with a given string. It may sound trivial with the existing JDK API but the
* various implementation among JDKs can make those methods extremely resource intensive and perform poorly due to
* massive memory allocation and copying. See
*
* @param buffer
* the buffer to perform the check on
* @param suffix
* the suffix
* @return <code>true</code> if the character sequence represented by the argument is a suffix of the character
* sequence represented by the StringBuffer object; <code>false</code> otherwise. Note that the result will
* be <code>true</code> if the argument is the empty string.
*/
public static boolean checkStringBufferEndsWith(StringBuffer buffer, String suffix) {
if (suffix.length() > buffer.length()) {
return false;
}
// this loop is done on purpose to avoid memory allocation performance
// problems on various JDKs
// StringBuffer.lastIndexOf() was introduced in jdk 1.4 and
// implementation is ok though does allocation/copying
// StringBuffer.toString().endsWith() does massive memory
// allocation/copying on JDK 1.5
// See http://issues.apache.org/bugzilla/show_bug.cgi?id=37169
int endIndex = suffix.length() - 1;
int bufferIndex = buffer.length() - 1;
while (endIndex >= 0) {
if (buffer.charAt(bufferIndex) != suffix.charAt(endIndex)) {
return false;
}
bufferIndex--;
endIndex--;
}
return true;
}
}