@Test
public void testDropIndexOnTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.createIndexTable(NAME, Integer.class, String.class, connection);
DBQueries.createIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
DBQueries.dropIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", false, connectionManager);
verify(statement, times(3)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@Test
public void testClearIndexTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
createSchema(connectionManager);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.clearIndexTable(NAME, connection);
List<DBQueries.Row<Integer, String>> expectedRows = Collections.emptyList();
assertQueryResultSet("SELECT * FROM " + TABLE_NAME, expectedRows, connectionManager);
verify(statement, times(1)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
void initWithTestData(final ConnectionManager connectionManager) {
createSchema(connectionManager);
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'abs')"), 1);
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'gps')"), 1);
assertEquals(
statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (2, 'airbags')"), 1);
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (3, 'abs')"), 1);
} catch (Exception e) {
throw new IllegalStateException("Unable to initialize test database", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
public void assertQueryResultSet(
final String query,
final List<DBQueries.Row<Integer, String>> rows,
final ConnectionManager connectionManager)
throws SQLException {
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
assertResultSetOrderAgnostic(resultSet, rows);
} catch (Exception e) {
throw new IllegalStateException("Unable to verify resultSet", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
void createSchema(final ConnectionManager connectionManager) {
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
assertEquals(
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (objectKey INTEGER, value TEXT)"),
0);
assertEquals(
statement.executeUpdate(
"CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON " + TABLE_NAME + "(value)"),
0);
} catch (Exception e) {
throw new IllegalStateException("Unable to create test database schema", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}