package net.raymanoz.migrate;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import net.raymanoz.config.Configuration;
import net.raymanoz.config.DatabaseType;
import net.raymanoz.config.ScriptStatus;
import net.raymanoz.domain.SchemaVersion;
import net.raymanoz.domain.SchemaVersionImpl;
public class SchemaVersionRepositoryImpl implements SchemaVersionRepository {
private Configuration configuration;
private Connection cacheConnection;
private Connection connection(){
if (cacheConnection == null) cacheConnection = configuration.getConnection();
return cacheConnection;
}
private String versionTableName;
private String scriptHistoryTableName;
private DatabaseType dbType;
private Long getFirstAsLong(PreparedStatement statement) throws SQLException {
if (statement == null) return 0L;
Long result = null;
try {
ResultSet resultSet = statement.executeQuery();
try {
if (resultSet.next()) {
result = resultSet.getLong(1);
}
} finally {
resultSet.close();
}
} finally {
statement.close();
}
return result;
}
public boolean checkTableExists(String tableName){
return checkTableExists(tableName, null);
}
public boolean checkTableExists(String tableName, String owner){
if (dbType == null || !dbType.supportsSQLTableCheck()) return true;
boolean result = true;
try {
PreparedStatement statement = connection().prepareStatement(dbType.getMetaDataChkTableSQL());
statement.setString(1, tableName);
statement.setString(2, owner);
result = getFirstAsLong(statement) > 0L;
} catch (SQLException e) {
//throw new RuntimeException(e);
}
return result;
}
private void validateVersionTables(){
if (dbType == null || !dbType.supportsSQLTableCheck()){
return; // database doesn't support checking via SQL/or haven't coded it yet
}
if (!checkTableExists(scriptHistoryTableName)){
if (checkTableExists(versionTableName)){
final String errFmt = "Table %s doesn't exist in schema - Please run uMigrate Upgrade Script";
throw new RuntimeException(String.format(errFmt, scriptHistoryTableName));
}
else {
final String errFmt = "Table %s doesn't exist in schema - Please run uMigrate Setup Script";
throw new RuntimeException(String.format(errFmt, versionTableName));
}
}
}
public SchemaVersionRepositoryImpl(Configuration config) {
this.configuration = config;
this.versionTableName = config.getSchemaVersionTable();
this.scriptHistoryTableName = config.getScriptHistoryTable();
this.dbType = config.getDatabaseType();
}
private static long aslong(Long value) {
if (value == null) {
return 0L;
} else {
return value;
}
}
private static final String GET_VERSION_SQL = "SELECT max(DBVersion) FROM %s";
private static final String GET_PATCH_SQL = "SELECT max(patch) FROM %s where DBversion = ? and status in (?, ?)";
private PreparedStatement scriptHistoryTableStatement(String sqlfmt)throws SQLException{
String sql = String.format(sqlfmt, scriptHistoryTableName);
PreparedStatement result = connection().prepareStatement(sql);
return result;
}
public SchemaVersion getVersion() {
validateVersionTables();
try {
PreparedStatement getMaxVerStatement = scriptHistoryTableStatement(GET_VERSION_SQL);
Long ver = Math.max(1, aslong(getFirstAsLong(getMaxVerStatement)));
PreparedStatement getPatchStatement = scriptHistoryTableStatement(GET_PATCH_SQL);
getPatchStatement.setLong(1, ver);
getPatchStatement.setString(2, ScriptStatus.SKIPPED.getKey());
getPatchStatement.setString(3, ScriptStatus.COMPLETED.getKey());
Long patch = aslong(getFirstAsLong(getPatchStatement));
SchemaVersion version = new SchemaVersionImpl(ver, patch);
return version;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private String computerName = null;
private String getComputerName(){
if (computerName == null) {
try {
computerName = InetAddress.getLocalHost().getHostName();
} catch (UnknownHostException e) {
computerName = "Error Retrieving Name";
}
}
return computerName;
}
private static final String START_PATCH_HISTORY = "INSERT INTO %s (DBVERSION, PATCH, PATCH_SCRIPT, " +
"STATUS, USERNAME, Machine) values(?, ?, ?, ?, ?, ?)";
public void recordStartPatch(Script script) {
try {
PreparedStatement statement = scriptHistoryTableStatement(START_PATCH_HISTORY);
try {
statement.setLong(1, script.getDBVersion());
statement.setLong(2, script.getPatch());
statement.setString(3, script.getFileName());
statement.setString(4, ScriptStatus.STARTED.getKey());
statement.setString(5, System.getProperty("user.name"));
statement.setString(6, getComputerName());
statement.execute();
} finally {
statement.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static final String FINISH_PATCH_HISTORY = "UPDATE %s set STATUS = ?, COMPLETED = ?, CONDITION_STATUS = ? " +
"where DBVERSION = ? and PATCH = ? and STATUS = ?";
public void recordFinishPatch(Script script, ScriptStatus status) {
assert(status != null);
try {
PreparedStatement statement = scriptHistoryTableStatement(FINISH_PATCH_HISTORY);
try {
statement.setString(1, status.getKey());
java.util.Date today = new java.util.Date();
statement.setTimestamp(2, new java.sql.Timestamp(today.getTime()));
String condStatus = script.condtionStatus();
if (condStatus != null && condStatus.length() > 2000){
condStatus = condStatus.substring(0, 1999);
}
statement.setString(3, condStatus);
statement.setLong(4, script.getDBVersion());
statement.setLong(5, script.getPatch());
statement.setString(6, ScriptStatus.STARTED.getKey());
statement.execute();
} finally {
statement.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public boolean hasScriptBeenApplied(long dbVersion, long patchNo) {
return recordOfScript(dbVersion, patchNo, ScriptStatus.COMPLETED);
}
private static final String CHECK_PATCH_HISTORY = "SELECT COUNT(1) FROM %s " +
"where DBVERSION = ? and PATCH = ? and STATUS = ?";
public boolean recordOfScript(long dbVersion, long patchNo, ScriptStatus status) {
assert(status != null);
boolean result = false;
try {
PreparedStatement statement = scriptHistoryTableStatement(CHECK_PATCH_HISTORY);
try {
statement.setLong(1, dbVersion);
statement.setLong(2, patchNo);
statement.setString(3, status.getKey());
ResultSet resultSet = statement.executeQuery();
try {
if (resultSet.next()) {
result = resultSet.getLong(1) > 0;
}
} finally {
resultSet.close();
}
} finally {
statement.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return result;
}
private static final String GET_LATER_ACTIVITY_SQL = "SELECT " +
" DBVERSION, PATCH, PATCH_SCRIPT, USERNAME, STATUS, STARTED, COMPLETED " +
"FROM %s where status in (?, ?, ?) and ((DBversion > ?) or (DBversion = ? and PATCH >= ?)) " +
"order by DBversion, PATCH";
public void validateNoOtherLaterActivity(SchemaVersion currentVersion) {
validateNoOtherActivity(currentVersion.getDBVersion(), currentVersion.getPatchNo() + 1);
}
public void validateNoOtherActivity(long dbVersion, long patchNo){
String message = "";
Boolean laterFound = false;
try {
PreparedStatement statement = scriptHistoryTableStatement(GET_LATER_ACTIVITY_SQL);
try {
statement.setString(1, ScriptStatus.SKIPPED.getKey());
statement.setString(2, ScriptStatus.COMPLETED.getKey());
statement.setString(3, ScriptStatus.STARTED.getKey());
statement.setLong(4, dbVersion);
statement.setLong(5, dbVersion);
statement.setLong(6, patchNo);
ResultSet resultSet = statement.executeQuery();
try {
while (resultSet.next()) {
laterFound = true;
String newLine = String.format(
"\nDB Ver %d patch %d script '%s' user '%s' status '%s'",
resultSet.getLong(1),
resultSet.getLong(2),
resultSet.getString(3),
resultSet.getString(4),
resultSet.getString(5)
);
message += newLine;
}
} finally {
resultSet.close();
}
} finally {
statement.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
if (laterFound){
throw new RuntimeException(String.format("Activity for DB Ver %d Patch %d or later found on DB\n%s", dbVersion, patchNo, message));
}
}
}