package net.raymanoz.migrate;
import static org.junit.Assert.assertEquals;
import java.net.InetAddress;
import java.net.UnknownHostException;
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;
import org.junit.Test;
import static org.mockito.Mockito.inOrder;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.verify;
import static org.mockito.Mockito.when;
import static org.mockito.Mockito.never;
import static org.mockito.Mockito.any;
import static org.mockito.Mockito.eq;
import org.mockito.InOrder;
import org.mockito.invocation.InvocationOnMock;
import org.mockito.stubbing.Answer;
public class SchemaVersionRepositoryImplTest {
private static final String EXPECTED_TABLE_NAME = "test_schema_version";
private static final String EXPECTED_PATCH_HISTORY_TABLE = "test_patch_history";
private final java.sql.Connection connection = mock(java.sql.Connection.class);
private Configuration mockConfiguration(){
Configuration config = mock(Configuration.class);
when(config.getConnection()).thenReturn(connection);
when(config.getSchemaVersionTable()).thenReturn(EXPECTED_TABLE_NAME);
when(config.getScriptHistoryTable()).thenReturn(EXPECTED_PATCH_HISTORY_TABLE);
when(config.getDatabaseType()).thenReturn(DatabaseType.UNKNOWN);
return config;
}
private final Configuration config = mockConfiguration();
private SchemaVersionRepositoryImpl repos = new SchemaVersionRepositoryImpl(config);
private class ScriptStatusRecord {
final long dbVersion;
final long patch;
final String scriptName;
final String user;
final String Status;
ScriptStatusRecord(
final long dbVersion,
final long patch,
final String scriptName,
final String user,
final String Status
){
this.dbVersion = dbVersion;
this.patch = patch;
this.scriptName = scriptName;
this.user = user;
this.Status = Status;
}
}
private class ScriptStatusAnswer implements Answer<Boolean>{
final ResultSet rs;
final ScriptStatusRecord[] statusRecords;
int idx = 0;
ScriptStatusAnswer(ResultSet rs, ScriptStatusRecord[] statusRecords){
this.statusRecords = statusRecords;
this.rs = rs;
}
@Override
public Boolean answer(InvocationOnMock invocation) throws Throwable {
if (statusRecords == null || idx >= statusRecords.length){
return false;
}
ScriptStatusRecord record = statusRecords[idx];
when(rs.getLong(1)).thenReturn(record.dbVersion);
when(rs.getLong(2)).thenReturn(record.patch);
when(rs.getString(3)).thenReturn(record.scriptName);
when(rs.getString(4)).thenReturn(record.user);
when(rs.getString(5)).thenReturn(record.Status);
idx++;
return true;
}
}
private class MockQry{
final PreparedStatement qry = mock(PreparedStatement.class);
final private ResultSet rs = mock(ResultSet.class);
MockQry(String exectedSQL) throws SQLException{
when(connection.prepareStatement(exectedSQL)).thenReturn(qry);
}
void queryFirstLong(final Long returnvalue) throws SQLException{
final boolean rowfound = (returnvalue != null);
when(qry.executeQuery()).thenReturn(rs);
when(rs.next()).thenReturn(rowfound).thenReturn(false);
if (rowfound) {
when(rs.getLong(1)).thenReturn(returnvalue);
}
}
void queryScriptStatusRecord(ScriptStatusRecord ... statusRecords) throws SQLException{
when(qry.executeQuery()).thenReturn(rs);
when(rs.next()).thenAnswer(new ScriptStatusAnswer(rs, statusRecords));
}
void checkParams(final InOrder inOrder, final Object ... params ) throws SQLException{
int idx = 0;
for (Object param: params) {
idx++;
if (param instanceof String) {
inOrder.verify(qry).setString(idx, (String)param);
}
else if (param instanceof Long){
inOrder.verify(qry).setLong(idx, (Long)param);
}
else if (param instanceof Integer){
inOrder.verify(qry).setInt(idx, (Integer)param);
}
else if (param instanceof java.sql.Timestamp){
inOrder.verify(qry).setTimestamp(eq(idx), any(java.sql.Timestamp.class));
}
else {
throw new RuntimeException("Unhandled type for parameter idx: " + idx);
}
}
}
void checkQueryFirstLongCalls(final Long returnvalue, final Object ... params ) throws SQLException{
final boolean rowfound = (returnvalue != null);
InOrder inOrder = inOrder(qry, rs);
checkParams(inOrder, params);
inOrder.verify(qry).executeQuery();
inOrder.verify(rs).next();
if (rowfound) {
inOrder.verify(rs).getLong(1);
}
else {
verify(rs, never()).getLong(1);
}
inOrder.verify(rs).close();
inOrder.verify(qry).close();
}
void checkQueryExecute(final Object ... params ) throws SQLException{
InOrder inOrder = inOrder(qry, rs);
checkParams(inOrder, params);
inOrder.verify(qry).execute();
inOrder.verify(qry).close();
}
void checkScriptStatusRecordCalls(long dbVersion, long patchNo, int noRecords) throws SQLException{
InOrder inOrder = inOrder(qry, rs);
checkParams(
inOrder,
ScriptStatus.SKIPPED.getKey(),
ScriptStatus.COMPLETED.getKey(),
ScriptStatus.STARTED.getKey(),
dbVersion,
dbVersion,
patchNo
);
inOrder.verify(qry).executeQuery();
inOrder.verify(rs).next();
for (int idx = 0; idx < noRecords; idx++){
inOrder.verify(rs).getLong(1);
inOrder.verify(rs).getLong(2);
inOrder.verify(rs).getString(3);
inOrder.verify(rs).getString(4);
inOrder.verify(rs).getString(5);
inOrder.verify(rs).next();
}
inOrder.verify(rs).close();
inOrder.verify(qry).close();
}
}
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 void checkGetSchema(final Long returnDBVersion, final Long returnPatchNo, final long expectedDBVersion, final long expectedPatchNo) throws SQLException {
MockQry maxVerQry = new MockQry(String.format(GET_VERSION_SQL, EXPECTED_PATCH_HISTORY_TABLE));
maxVerQry.queryFirstLong(returnDBVersion);
MockQry maxPatch = new MockQry(String.format(GET_PATCH_SQL, EXPECTED_PATCH_HISTORY_TABLE));
maxPatch.queryFirstLong(returnPatchNo);
SchemaVersion ver = repos.getVersion();
assertEquals(ver.getDBVersion(), expectedDBVersion);
assertEquals(ver.getPatchNo(), expectedPatchNo);
maxVerQry.checkQueryFirstLongCalls(returnDBVersion);
maxPatch.checkQueryFirstLongCalls(returnPatchNo, (returnDBVersion==null||returnDBVersion<=0)?1l:returnDBVersion, ScriptStatus.SKIPPED.getKey(), ScriptStatus.COMPLETED.getKey());
}
@Test
public void checkGetVersion_all_set() throws SQLException{
checkGetSchema(3L, 10L, 3L, 10L);
}
@Test
public void checkGetVersion_no_patch() throws SQLException{
checkGetSchema(4L, null, 4L, 0L);
}
@Test
public void checkGetVersion_no_version() throws SQLException{
checkGetSchema(null, null, 1L, 0L);
}
@Test
public void checkGetVersion_neg_ver() throws SQLException{
checkGetSchema(-1L, null, 1L, 0L);
}
@Test(expected=IllegalArgumentException.class)
public void checkGetVersion_neg_patch() throws SQLException{
checkGetSchema(1L, -1L, 1L, 0L);
}
private String getComputerName(){
String computerName;
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(?, ?, ?, ?, ?, ?)";
private Script mockScript(long dbVersion, long patchNo, String patchName){
Script result = mock(Script.class);
when(result.getDBVersion()).thenReturn(dbVersion);
when(result.getPatch()).thenReturn(patchNo);
when(result.getFileName()).thenReturn(patchName);
when(result.condtionStatus()).thenReturn("Condion Status");
return result;
}
@Test
public void recordpatchStart() throws SQLException {
final long dbVer = 6L;
final long patchNo = 99L;
final String patchName = "009_Dummy_i1.sql";
final Script mockScript = mockScript(dbVer, patchNo, patchName);
MockQry mockQry = new MockQry(String.format(START_PATCH_HISTORY, EXPECTED_PATCH_HISTORY_TABLE));
repos.recordStartPatch(mockScript);
mockQry.checkQueryExecute(dbVer, patchNo, patchName, ScriptStatus.STARTED.getKey(), System.getProperty("user.name"), getComputerName());
}
private static final String FINISH_PATCH_HISTORY = "UPDATE %s set STATUS = ?, COMPLETED = ?, CONDITION_STATUS = ? " +
"where DBVERSION = ? and PATCH = ? and STATUS = ?";
private void recordPatchFinish(final ScriptStatus status) throws SQLException {
final long dbVer = 6L;
final long patchNo = 99L;
final Script mockScript = mockScript(dbVer, patchNo, "");
final MockQry qry = new MockQry(String.format(FINISH_PATCH_HISTORY, EXPECTED_PATCH_HISTORY_TABLE));
repos.recordFinishPatch(mockScript, status);
qry.checkQueryExecute(status.getKey(), new java.sql.Timestamp(0), "Condion Status", dbVer, patchNo, ScriptStatus.STARTED.getKey());
}
@Test
public void recordPatchFinish_complete() throws SQLException {
recordPatchFinish(ScriptStatus.COMPLETED);
}
public void recordPatchFinish_errored() throws SQLException {
recordPatchFinish(ScriptStatus.ERRORED);
}
private static final String CHECK_PATCH_HISTORY = "SELECT COUNT(1) FROM %s " +
"where DBVERSION = ? and PATCH = ? and STATUS = ?";
private void checkrecordOfScript(final Long dbVersion, final Long patchNo, final ScriptStatus status, final long returnVal) throws SQLException {
MockQry qry = new MockQry(String.format(CHECK_PATCH_HISTORY, EXPECTED_PATCH_HISTORY_TABLE));
qry.queryFirstLong(returnVal);
boolean expected = returnVal > 0;
assertEquals(String.format("checkrecordOfScript - db ver %d, patch %d, status %s", dbVersion, patchNo, status.getKey()),
expected, repos.recordOfScript(dbVersion, patchNo, status));
qry.checkQueryFirstLongCalls(returnVal, dbVersion, patchNo, status.getKey());
}
@Test
public void testRecordOfScripts() throws SQLException{
checkrecordOfScript(2L, 1L, ScriptStatus.ERRORED, 1L);
checkrecordOfScript(2L, 1L, ScriptStatus.COMPLETED, 0L);
checkrecordOfScript(3L, 99L, ScriptStatus.SKIPPED, 1L);
}
private void checkhasScriptBeenApplied(final Long dbVersion, final Long patchNo, final long returnVal) throws SQLException {
MockQry qry = new MockQry(String.format(CHECK_PATCH_HISTORY, EXPECTED_PATCH_HISTORY_TABLE));
qry.queryFirstLong(returnVal);
boolean expected = returnVal > 0;
assertEquals(String.format("checkScriptApplied - db ver %d, patch %d", dbVersion, patchNo),
expected, repos.hasScriptBeenApplied(dbVersion, patchNo));
qry.checkQueryFirstLongCalls(returnVal, dbVersion, patchNo, ScriptStatus.COMPLETED.getKey());
}
@Test
public void testhasScriptBeenApplied() throws SQLException{
checkhasScriptBeenApplied(2L, 1L, 1L);
checkhasScriptBeenApplied(99L, 2L, 0L);
checkhasScriptBeenApplied(3L, 99L, 1L);
}
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";
@Test
public void testNoActivityValidateNoOtherLaterActivity() throws SQLException{
MockQry qry = new MockQry(String.format(GET_LATER_ACTIVITY_SQL, EXPECTED_PATCH_HISTORY_TABLE));
qry.queryScriptStatusRecord();
repos.validateNoOtherLaterActivity(new SchemaVersionImpl(2L,4L));
qry.checkScriptStatusRecordCalls(2L, 5L, 0);
}
@Test
public void testNoActivity_ValidateNoOtherActivity() throws SQLException{
MockQry qry = new MockQry(String.format(GET_LATER_ACTIVITY_SQL, EXPECTED_PATCH_HISTORY_TABLE));
qry.queryScriptStatusRecord();
repos.validateNoOtherActivity(2L,4L);
qry.checkScriptStatusRecordCalls(2L, 4L, 0);
}
@Test
public void testSomeActivity_ValidateNoOtherLaterActivity() throws SQLException{
MockQry qry = new MockQry(String.format(GET_LATER_ACTIVITY_SQL, EXPECTED_PATCH_HISTORY_TABLE));
qry.queryScriptStatusRecord(
new ScriptStatusRecord(2L, 5L, "A Script", "A User", "A Status"),
new ScriptStatusRecord(2L, 6L, "Another Script", "Another User", "Another Status")
);
try{
repos.validateNoOtherLaterActivity(new SchemaVersionImpl(2L,4L));
assertEquals("Should throw Exception", true, false);
}
catch (RuntimeException e){
String expected =
"Activity for DB Ver 2 Patch 5 or later found on DB\n" +
"\nDB Ver 2 patch 5 script 'A Script' user 'A User' status 'A Status'" +
"\nDB Ver 2 patch 6 script 'Another Script' user 'Another User' status 'Another Status'";
assertEquals(expected, e.getMessage());
}
qry.checkScriptStatusRecordCalls(2L, 5L, 2);
}
@Test
public void testSomeActivity_ValidateNoOtherActivity() throws SQLException{
MockQry qry = new MockQry(String.format(GET_LATER_ACTIVITY_SQL, EXPECTED_PATCH_HISTORY_TABLE));
qry.queryScriptStatusRecord(
new ScriptStatusRecord(2L, 5L, "A Script", "A User", "A Status"),
new ScriptStatusRecord(2L, 6L, "Another Script", "Another User", "Another Status")
);
try{
repos.validateNoOtherActivity(2L,4L);
assertEquals("Should throw Exception", true, false);
}
catch (RuntimeException e){
String expected =
"Activity for DB Ver 2 Patch 4 or later found on DB\n" +
"\nDB Ver 2 patch 5 script 'A Script' user 'A User' status 'A Status'" +
"\nDB Ver 2 patch 6 script 'Another Script' user 'Another User' status 'Another Status'";
assertEquals(expected, e.getMessage());
}
qry.checkScriptStatusRecordCalls(2L, 4L, 2);
}
}