Unit Testing Tips and Tricks: Database Interaction

Unit Testing Tips and Tricks: Database Interaction Louis Thomas

Overview Preaching About Testing What is a Unit Test Common Unit Testing Patterns Unit Testing Database Interactions Acceptance Tests With Databases

Are You Test Infected? • There are two kinds of people: • People who don’t like writing tests • Can’t be done attitude • People who do like writing tests • “I think I can” attitude • Expect a learning curve. Be creative! • Unit tests are good even if you don’t do Test Driven Development!

There Are Many Kinds Of Tests • Acceptance tests, user tests, integration tests, unit tests; black box, white box… • All tests have merit if they can detect bugs. • Tests only have value if they are run!

Unit Tests • From developer's point of view. • Tests the smallest amount of a system that is interesting. Often just one part of one class! • Highly automated

Unit Test Rule Of Thumb • If you are having trouble writing a unit test or (for those of you who aren't test infected)if it is "impossible" to write a test for your system, • You are trying to test to much. Test a smaller chunk.

But How? • Sometimes objects have complex behaviors, extensive state, and tight relationships. This makes tests difficult: set up is difficult and time consuming, and objects cannot be isolated. • (But wait, that’s not right! Right?)

Loosening The Coupling • Introduce interfaces between complex objects. • Create a mock object to stand in for the complex object. • Repeat as needed. (Be creative.)

Creating Interfaces • If it's our object, just create an interface! • if it's not our object, • create a mock that extends the object and overrides all its methods (works sometimes) • create an interface anyway and create an adapter for the foreign object

singleton instance for convenience implementation of interface Example: WallClock Interface publicinterface WallClock { long getTime(); } Wrapper for normal system service publicclass DefaultWallClock implements WallClock { publicstaticfinal WallClock INSTANCE = new DefaultWallClock(); publiclong getTime() { return System.currentTimeMillis(); } }

Mock Objects • Start out as simple as possible (throw exceptions on all methods). • Add recording of incoming method calls

usefull base class • mock implements one interesting interface • simple naming convention • implementation of interface: • record interesting input param • implementation of interface: • no return value • no side effects • - just record that call occurred • implementation of interface: • simple behavior, hard-coded • return value sufficient Example: MockClientSession publicclass MockClientSession extends ReportingMockObject implements ClientSession { publicvoid flushOutgoingBuffer() { recordActivity("fOB"); } publicvoid setInterval(int nUpdateIntervalMilliseconds) { recordActivity("sI("+nUpdateIntervalMilliseconds+")"); } publicvoid notifyNewOutgoingData() { recordActivity("nNOD"); } public String getClientName() { recordActivity("gCN"); return "mockClient"; } }

Example: ReportingMockObject publicclass ReportingMockObject { StringBuffer m_stringBuffer = new StringBuffer(); public String getActivityRecordAndReset() { String sActivityRecord = m_stringBuffer.toString(); m_stringBuffer = new StringBuffer(); return sActivityRecord; } publicvoid recordActivity(String sMessage) { m_stringBuffer.append(sMessage); } }

Mock Objects, cont’d • Add facility for sending back canned responses • (ex, setNextReply, setFailOnNextRequest)

Example: MockWallClock publicclass MockWallClock implements WallClock { private List m_nextTimes=new LinkedList(); publicvoid addNextTime(long nNextTime) { m_nextTimes.add(new Long(nNextTime); } publicvoid addNextTimes(long[] nextTimes) { Require.neqNull(nextTimes, "nextTimes"); for (int nIndex=0; nIndex<nextTimes.length; nIndex++) { addNextTime(nextTimes[nIndex]); } } publiclong getTime() { Assert.gtZero(m_nextTimes.size(), "m_nextTimes.size()"); return ((Long)m_nextTimes.remove(0)).longValue(); } }

Mock Objects, cont’d • Do whatever you need • Often one mock object will support all tests for a given object, but can create special ones for certain tests • Often, one mock object will support tests for many objects that interact with it

Example: MockMultiTableSessionListener publicclass MockMultiTableSessionListener extends ReportingMockObject implements MultiTableSession.Listener { publicinterface Thumbprinter { String getThumbprint(MultiTableSession.Update update); String getThumbprint(SessionState sessionState); } privatefinal Thumbprinter m_thumbprinter; privateboolean m_bLogSessionStateNotification = true; public MockMultiTableSessionListener(Thumbprinter thumbprinter) { m_thumbprinter = thumbprinter; } publicvoid setLogSessionStateNotification(boolean bLogSessionStateNotification) { m_bLogSessionStateNotification = bLogSessionStateNotification; } publicvoid sessionStateNotification(SessionState sessionState) { if (true==m_bLogSessionStateNotification) { recordActivity("sSN("+m_thumbprinter.getThumbprint(sessionState)+")"); } } }

Mock Object Frameworks • EasyMock (http://easymock.org) • jMock (http://www.jmock.org/) • YMMV!

Object Mother (?) • Sometimes you will need a complex data structure set up. Refactor mercilessly. • Especially if you need canned data that is ancillary to the test, it is often worth while to factor creation out into a static method in a util class so you can use it as necessary thereafter.

Testing Accessor • Problem: there are private methods you would like to test, or private members you would like to inspect for your test • You could make them public, but they really are private • Alternative: an inner class! TestingAccessor

Example: TestingAccessor //################################################################ // testing private WallClock m_wallClock = DefaultWallClock.INSTANCE; private IStepper m_getConStepper = DefaultStepper.INSTANCE; private IStepper m_maintStepper = DefaultStepper.INSTANCE; publicclass TestingAccessor { publicvoid setWallClock(WallClock wallClock) { m_wallClock = wallClock; } publicvoid setGetConStepper(IStepper stepper) { m_getConStepper = stepper; } publicvoid setMaintStepper(IStepper stepper) { m_maintStepper = stepper; } publicvoid setNextOverdueConnectionCheck(long tsNextOverdueConnectionCheck) { m_tsNextOverdueConnectionCheck = tsNextOverdueConnectionCheck; } publicint getAllConnectionsSize() { return m_allConnections.size(); } publicint getUnusedConnectionsSize() { return m_unusedConnections.size(); } publicint getTotalConnections() { return m_nTotalConnections; } publicvoid cacheMaintenaceThread() {DBConnectionPool.this.cacheMaintenaceThread(); } publicvoid doNotifyAll() { synchronized (m_oStateLock) { m_oStateLock.notifyAll(); } } } public TestingAccessor getTestingAccessor() { returnnew TestingAccessor(); }

Testing Database Interactions • All the database classes are interfaces already! How convenient! • Create mocks and away you go • Insert / update / delete – straight forward

Example: writeChangeStepsToDatabase publicstaticvoid writeChangeStepsToDatabase(Statement dbStatement, InstrumentListChangeRequest instrumentListChangeRequest, int nParentId) throws SQLException { InstrumentListChangeRequest.ChangeStep[] changeSteps = instrumentListChangeRequest.getChangeStepsSnapshot(); StringBuffer sb = new StringBuffer(); for (int nIndex = 0; nIndex<changeSteps.length; nIndex++) { InstrumentListChangeRequest.ChangeStep changeStep = changeSteps[nIndex]; sb.setLength(0); changeStep.persistTo(sb); String sSql = "INSERTINTO"+TABLE_NAME +"("+FIELD_PARENT_ID +","+FIELD_STEP_NUM +","+FIELD_STEP_DETAIL +")VALUES" +"("+FORMAT_PARENT_ID.format(nParentId) +","+FORMAT_STEP_NUM.format(nIndex) +","+FORMAT_STEP_DETAIL.format(sb.toString()) +")"; // send it to the database int nRows = dbStatement.executeUpdate(SqlFormatHelper.showSql(sSql)); if (0==nRows) { thrownew SQLException("Failedtowritetodatabase."); } } }

Example: testWriteChangeStepsToDatabase publicclass TestChangeStepSqlHelper extends BaseTradingSystemTest { publicvoid testWriteChangeStepsToDatabase() throws Exception { CurrencySnapshot instrument = new CurrencySnapshot(new InstrumentSnapshotId("0.FOO"), new InstrumentLineageId(1), null, ValidityPeriod.ALL_TIME, new CurrencyIsoSymbol("F"), "F", "Foo"); InstrumentListChangeRequest instrumentListChangeRequest1 = new InstrumentListChangeRequest(); instrumentListChangeRequest1.addNewInstrument(instrument); InstrumentListChangeRequest instrumentListChangeRequest2 = new InstrumentListChangeRequest(); instrumentListChangeRequest2.addNewInstrument(instrument); instrumentListChangeRequest2.addNewInstrument(instrument); MockStatement mockStatement = new MockStatement(); SimulatedDatabase.Table changeStepTable = new SimulatedDatabase.Table(ChangeStepSqlHelper.ALL_FIELDS); CaxManagementSimulatedDatabaseTables.setUpChangeStepTable(mockStatement, changeStepTable); ChangeStepSqlHelper.writeChangeStepsToDatabase(mockStatement, instrumentListChangeRequest1, 1); assertEquals(1, changeStepTable.getRowCount()); ChangeStepSqlHelper.writeChangeStepsToDatabase(mockStatement, instrumentListChangeRequest2, 2); assertEquals(3, changeStepTable.getRowCount()); } }

Example: MockStatement publicclass MockStatement implements Statement { publicinterface UpdateHandler { int handleUpdate(String sql) throws SQLException; } private Map m_stringMatcherToUpdateHandlerMap=new TreeMap(new SystemWebDirectory.StringMatchComparator()); publicvoid registerUpdateHandler(String sql, UpdateHandler updateHandler) { Object key; if (sql.endsWith("*")) { sql=sql.substring(0, sql.length()-1); key=new SystemWebDirectory.StringPrefixMatcher(sql); } else { key=new SystemWebDirectory.StringMatcher(sql); } Object prevValue=m_stringMatcherToUpdateHandlerMap.put(key, updateHandler); Require.eqNull(prevValue, "prevValue"); } publicint executeUpdate(String sql) throws SQLException { UpdateHandler updateHandler=(UpdateHandler)m_stringMatcherToUpdateHandlerMap.get(sql); if (null==updateHandler) { thrownew SQLException("Unexpected update\""+sql+"\"."); } else { return updateHandler.handleUpdate(sql); } }

Example: setUpChangeStepTable publicclass CaxManagementSimulatedDatabaseTables { publicstaticvoid setUpChangeStepTable(MockStatement mockStatement, SimulatedDatabase.Table changeStepTable) { //… mockStatement.registerUpdateHandler( "INSERT INTO "+ChangeStepSqlHelper.TABLE_NAME+" (" +ChangeStepSqlHelper.FIELD_PARENT_ID+", *", getInsertNewHandler(changeStepTable, new String[] {ChangeStepSqlHelper.FIELD_PARENT_ID, ChangeStepSqlHelper.FIELD_STEP_NUM, ChangeStepSqlHelper.FIELD_STEP_DETAIL})); } privatestatic MockStatement.UpdateHandler getInsertNewHandler(final SimulatedDatabase.Table table, final String[] columnNames) { returnnew MockStatement.UpdateHandler() { publicint handleUpdate(String sql) throws SQLException { SimulatedDatabase.Table.Row row = table.addRow(); SimpleSqlTokenizer simpleSqlTokenizer = new SimpleSqlTokenizer(sql); for (intnIndex = 0; nIndex<columnNames.length; nIndex++) { Object columnValue = simpleSqlTokenizer.getNextParameter(); String sColumnName = columnNames[nIndex]; row.set(sColumnName, columnValue); } return 1; } }; }

Testing Database Interactions, Cont’d • Read – trickier • Can use hard coded expectations • Mocks will act as factories: statements return record sets • load your mock statement with the mock record set to return. • load your mock connection with the mock statement to return. • Can start out with mocks with hard coded returns, but will probably refactor into more general objects.

Example: testReadChangeStepsFromDatabase public void testReadChangeStepsFromDatabase() throws Exception { MockStatement mockStatement=new MockStatement(); setUpChangeStepTable(mockStatement); InstrumentListChangeRequest instrumentListChangeRequest; InstrumentListChangeRequest.ChangeStep[] changeSteps; instrumentListChangeRequest=ChangeStepSqlHelper.readChangeStepsFromDatabase(mockStatement, 5, 1); changeSteps=instrumentListChangeRequest.getChangeStepsSnapshot(); assertEquals(1, changeSteps.length); assertNull(changeSteps[0].getOldInstrument()); assertNotNull(changeSteps[0].getNewInstrument()); assertEquals(new InstrumentSnapshotId("0.CTO"), changeSteps[0].getNewInstrument().getInstrumentSnapshotId()); instrumentListChangeRequest=ChangeStepSqlHelper.readChangeStepsFromDatabase(mockStatement, 10, 2); changeSteps=instrumentListChangeRequest.getChangeStepsSnapshot(); assertEquals(2, changeSteps.length); assertNull(changeSteps[0].getOldInstrument()); assertNotNull(changeSteps[0].getNewInstrument()); assertEquals(new InstrumentSnapshotId("0.B"), changeSteps[0].getNewInstrument().getInstrumentSnapshotId()); assertNotNull(changeSteps[1].getOldInstrument()); assertNotNull(changeSteps[1].getNewInstrument()); assertEquals(new InstrumentSnapshotId("0.A"), changeSteps[1].getNewInstrument().getInstrumentSnapshotId()); assertEquals("a-old", ((Equity)changeSteps[1].getOldInstrument()).getCompanyName());

Example: readChangeStepsFromDatabase public static InstrumentListChangeRequest readChangeStepsFromDatabase(Statement dbStatement, int nParentId, int nExpectedSteps) throws SQLException { InstrumentListChangeRequest.ChangeStep[] changeSteps=new InstrumentListChangeRequest.ChangeStep[nExpectedSteps]; int nFoundSteps=0; // process all the rows ResultSet resultSet=dbStatement.executeQuery(SqlFormatHelper.showSql("SELECT * FROM "+TABLE_NAME+" WHERE "+FIELD_PARENT_ID+" = "+FORMAT_PARENT_ID.format(nParentId))); try { while (resultSet.next()) { int nStepNum=resultSet.getInt(FIELD_STEP_NUM); checkForNull(resultSet, FIELD_STEP_NUM); String sPersistedChangeStep=resultSet.getString(FIELD_STEP_DETAIL); checkForNull(resultSet, FIELD_STEP_DETAIL); InstrumentListChangeRequest.ChangeStep changeStep=new InstrumentListChangeRequest.ChangeStep(new BloombergTokenizer(sPersistedChangeStep)); if (nStepNum<0 || nStepNum>=nExpectedSteps) { throw new SQLException("Found change step "+nStepNum+" but expected 0 < changeStep <= "+nExpectedSteps+"."); } else if (null!=changeSteps[nStepNum]) { throw new SQLException("Found second change step "+nStepNum+"."); } changeSteps[nStepNum]=changeStep; nFoundSteps++; } } finally { try { resultSet.close(); } catch (SQLException e) { Syslog.warning(ChangeStepSqlHelper.class, "Failed to close result set.", e); } } if (nFoundSteps!=nExpectedSteps) { throw new SQLException("Found only "+nFoundSteps+" change steps out of "+nExpectedSteps+" expected."); } InstrumentListChangeRequest instrumentListChangeRequest=new InstrumentListChangeRequest(); instrumentListChangeRequest.setChangeSteps(changeSteps); return instrumentListChangeRequest; }

Example: MockStatement publicclass MockStatement implements Statement { public interface QueryHandler { ResultSet handleQuery(String sql) throws SQLException; } private Map m_stringMatcherToQueryHandlerMap=new TreeMap(new SystemWebDirectory.StringMatchComparator()); public void registerQueryHandler(String sql, QueryHandler queryHandler) { Object key; if (sql.endsWith("*")) { sql=sql.substring(0, sql.length()-1); key=new SystemWebDirectory.StringPrefixMatcher(sql); } else { key=new SystemWebDirectory.StringMatcher(sql); } Object prevValue=m_stringMatcherToQueryHandlerMap.put(key, queryHandler); Require.eqNull(prevValue, "prevValue"); } public ResultSet executeQuery(String sql) throws SQLException { QueryHandler queryHandler=(QueryHandler)m_stringMatcherToQueryHandlerMap.get(sql); if (null==queryHandler) { throw new SQLException("Unexpected query \""+sql+"\"."); } else { return queryHandler.handleQuery(sql); } }

Example: setUpChangeStepTable publicclass CaxManagementSimulatedDatabaseTables { publicstaticvoid setUpChangeStepTable(MockStatement mockStatement, SimulatedDatabase.Table changeStepTable) { //… mockStatement.registerQueryHandler( "SELECT * FROM "+ChangeStepSqlHelper.TABLE_NAME+" WHERE " +ChangeStepSqlHelper.FIELD_PARENT_ID+" = *", getSelectByIdHandler(changeStepTable, new String[] {ChangeStepSqlHelper.FIELD_PARENT_ID})); } private static MockStatement.QueryHandler getSelectByIdHandler(final SimulatedDatabase.Table table, final String[] columnNames) { return new MockStatement.QueryHandler() { public ResultSet handleQuery(String sql) throws SQLException { // identify the ids that must match String[] ids=new String[columnNames.length]; SimpleSqlTokenizer simpleSqlTokenizer=new SimpleSqlTokenizer(sql); for (int nIdIndex=0; nIdIndex<ids.length; nIdIndex++) { ids[nIdIndex]=simpleSqlTokenizer.getNextParameter(); } // create a new table containing all the matching rows final SimulatedDatabase.Table resultTable=new SimulatedDatabase.Table(table.getColumnNames()); for (Iterator itr=table.getRowIterator(); itr.hasNext();) { SimulatedDatabase.Table.Row row=(SimulatedDatabase.Table.Row)itr.next(); boolean bMatched=true; for (int nIdIndex=0; nIdIndex<ids.length; nIdIndex++) { if (!idMatch(ids[nIdIndex], row.get(columnNames[nIdIndex]))) { bMatched=false; break; } } if (true==bMatched) { resultTable.addRow(row.getAll()); } } return new BaseMockResultSet() { protected int getTableSize() { return resultTable.getRowCount(); } public Object getObjectInternal(String columnName) throws SQLException { return resultTable.getRow(m_nIndex).get(columnName); } }; } }; }

Example: BaseMockResultSet public abstract class BaseMockResultSet implements ResultSet { protected int m_nIndex=-1; private boolean m_bWasNull=false; protected abstract int getTableSize(); protected abstract Object getObjectInternal(String columnName) throws SQLException; public boolean next() throws SQLException { m_nIndex++; return m_nIndex<getTableSize(); } public void close() throws SQLException { // do nothing } public void setWasNull(boolean bWasNull) { m_bWasNull=bWasNull; } public Object setWasNull(Object object) { m_bWasNull=null==object; return object; } public boolean wasNull() throws SQLException { return m_bWasNull; } public int getInt(String columnName) throws SQLException { Object columnValue=setWasNull(getObjectInternal(columnName)); if (null==columnValue) { return -1; } else { String sValue=columnValue.toString(); try { return Integer.parseInt(sValue); } catch (NumberFormatException e) { throw new SQLException("Value "+sValue+" of column "+columnName +" can't be converted to int. "+e); } } } public String getString(String columnName) throws SQLException { Object columnValue=setWasNull(getObjectInternal(columnName)); if (null==columnValue) { return null; } else { return columnValue.toString(); } }

Example: SimulatedDatabase.Table //---------------------------------------------------------------- public static class Table { private String[] m_columnNames; private int m_nColumns; private List m_rows=new ArrayList(); private Map m_columnNameToColumnIndexMap; //------------------------------------------------------------ public Table(String[] columnNames) { Require.neqNull(columnNames, "columnNames"); Require.gtZero(columnNames.length, "columnNames.length"); m_columnNames=columnNames; m_nColumns=m_columnNames.length; createColumnMap(); } //------------------------------------------------------------ public String[] getColumnNames() { return m_columnNames; } //------------------------------------------------------------ private void createColumnMap() { m_columnNameToColumnIndexMap=new HashMap(); for (int nIndex=0; nIndex<m_nColumns; nIndex++) { String sColumnName=m_columnNames[nIndex]; m_columnNameToColumnIndexMap.put(sColumnName, new Integer(nIndex)); } } //------------------------------------------------------------ private int getIndexForName(String sColumnName) throws SQLException { Integer columnIndex=(Integer)m_columnNameToColumnIndexMap.get(sColumnName); if (null==columnIndex) { throw new SQLException("Unknown column name \""+sColumnName+"\"."); } return columnIndex.intValue(); } //------------------------------------------------------------ public int getRowCount() { return m_rows.size(); } //------------------------------------------------------------ public Iterator getRowIterator() { return m_rows.iterator(); } //------------------------------------------------------------ public Row getRow(int nRowIndex) { Require.geqZero(nRowIndex, "nRowIndex"); Require.lt(nRowIndex, "nRowIndex", getRowCount(), "getRowCount()"); return (Row)m_rows.get(nRowIndex); } //---------------------------------------------------------------- public void addRow(Object[] objects) { Require.neqNull(objects, "objects"); Require.eq(objects.length, "objects.length", m_nColumns, "m_nColumns"); Row row=new Row(); m_rows.add(row); for (int nIndex=0; nIndex<objects.length; nIndex++) { Object object=objects[nIndex]; row.set(nIndex, object); } } //------------------------------------------------------------ public Row addRow() { Row row=new Row(); m_rows.add(row); return row; } //############################################################ //------------------------------------------------------------ public class Row { private Object[] m_objects; public Row() { m_objects=new Object[m_nColumns]; } public Object get(String sColumnName) throws SQLException { return m_objects[getIndexForName(sColumnName)]; } public void set(String sColumnName, Object object) throws SQLException { m_objects[getIndexForName(sColumnName)]=object; } public Object get(int nColumnIndex) { return m_objects[nColumnIndex]; } public void set(int nColumnIndex, Object object) { m_objects[nColumnIndex]=object; } public Object[] getAll() { return m_objects; } } }

Acceptance Tests With Databases • An acceptance test: Want to test the "whole" app. • Good for testing that the database really likes the SQL we hard coded in the unit tests, and really responds the way we expect.

Acceptance Tests With Databases • Will take longer than a unit test • May need special environment (acquire exclusive access to the testing database) • Should still use a small interesting subset of the the production dataset for speed. • Should still be as automated as possible. • Test code will still look similar to unit tests

Acceptance Tests With Databases, Cont’d • Big question is, how can we automate? I built up a toolkit as I went. • BulkLoadData: reads CSV files and loads data into DB (use Excel to edit) (214 lines) • ExecuteSqlScript: processes a text file of SQL commands. (222 lines) • Used to create tables, etc. • ExecuteDatabaseSetupScript: allows me to write little scripts (205 lines) • Knows about 4 commands, including BulkLoadData and ExecuteSqlScript

Example: BulkLoadData input file null_value,null table,inst_definitions begin_data_with_columns inst_snapshot_id,validity_begin,validity_end,inst_lineage_id,alleged_type,equity_ticker_detail,cusip,isin,sedol, country_id,currency_id,company_name,round_lot_size,registrar_venue_id,opra_symbol_root, opra_symbol_suffix,underlying_id,strike_price,expiration_timestamp,parity 1,null,null,1,:3:,A,a.cusip,null,null,1,1,null,100,5,null,null,null,null,null,null 2,null,null,2,:3:,B,null,null,null,1,1,null,100,4,null,null,null,null,null,null end_data reset_sequence,inst_snapshot_id_seq,3,1 reset_sequence,inst_lineage_id_seq,3,1 table,inst_definitions_audit begin_data_with_columns revision,rev_begin,rev_begin_user,rev_begin_comment,rev_end,rev_end_user,rev_end_comment, inst_snapshot_id,validity_begin,validity_end,inst_lineage_id,alleged_type,equity_ticker_detail,cusip, isin,sedol,country_id,currency_id,company_name,round_lot_size,registrar_venue_id, opra_symbol_root,opra_symbol_suffix,underlying_id,strike_price,expiration_timestamp,parity 0,"to_date('2005-01-01','YYYY-MM-DD')",lt,created A,null,null,null,1,null,null,1,:3:,A, a.cusip,null,null,1,1,null,100,5,null,null,null,null,null,null 0,"to_date('2005-01-01','YYYY-MM-DD')",lt,null,null,null,null,2,null,null,2,:3:,B, null,null,null,1,1,null,100,4,null,null,null,null,null,null end_data

Example: ExecuteSqlScript input file [def boolean] number(1) [main] @ignore_errors@ drop table table_metadata; create table table_metadata ( table_id number not null, table_name varchar2(50) not null, is_metatable &boolean; not null, is_audited &boolean; not null, is_editable &boolean; not null, is_deletable &boolean; not null, is_pend_changeable &boolean; not null, display_name varchar2(100), java_table_handler varchar2(500) ) tablespace instr_svc_data; create unique index table_metadata_pk on table_metadata (table_id) tablespace instr_svc_idx; alter table table_metadata add (constraint table_metadata_pk primary key(table_id)); @ignore_errors@ drop sequence table_id_seq; create sequence table_id_seq start with 1 increment by 1;

Example: ExecuteDatabaseSetupScript files setupScript-schemaAndAllData.txt select_db_config DeephavenInstrumentService execute_sql_script tearDownEntireSchema-1.0.txt execute_sql_script createEntireSchema-1.0.txt execute_setup_script setupScript-allDataOnly.txt setupScript-instDataOnly.txt select_db_config DeephavenInstrumentService delete_all_from_table inst_definitions delete_all_from_table inst_definitions_audit bulk_load_data data-inst_definitions-1.0.csv

Acceptance Tests With Databases, Cont’d • I built up a toolkit as I went, cont’d • TestingResource framework • I can define the testing resources that my test needs, setup/teardown methods, and dependencies. • Resources should be able to set themselves up from any initial state (ex, delete all rows in table and reload) • Now, the acceptance test can just declare all the resources it needs, and framework will set them up. Just needs to mark which resources it dirties, so they can be reset for subsequent tests.

TestingResource Framework STATE_TORN_DOWN: 0 STATE_SET_UP: 1 STATE_DIRTY: 2 <<Interface>> * TestingResource * * TestingResourceManager +getName() +markAsDirty() +tearDown() +setUp() +addDependant() +addDependancy() -nameToTestingResourceMap 1 +get() +put() +tearDownAll() +markResourceAsDirty() BaseTestingResource 1 -dependents -dependencies -name -isAlwaysSetUp -state 1 #doTearDown() #doSetUp() #assumeAlwaysSetUp() DatabaseTestingResource InstrumentServiceSessionTestingResource DeephavenInstrumentServiceTestingResource +getInstrumentSession3() +getDeephavenInstrumentService()

TestingResource Framework Depends on InstrumentServiceSessionTestingResource Active Session DeephavenInstrumentServiceTestingResource Service DatabaseTestingResource(s) Instrument Tables Fixed Tables Change Management Tables Schema public void setUp() { for (Iterator itr=m_dependencies.iterator(); itr.hasNext();) { TestingResource testingResource=(TestingResource)itr.next(); testingResource.setUp(); } if (STATE_SET_UP!=m_nState) { if (STATE_TORN_DOWN!=m_nState) { Syslog.info(this, "Noticed "+getName()+" is dirty!"); tearDown(); } Syslog.info(this, "Setting up "+getName()+"."); if (false==m_bIsAlwaysSetUp) { doSetUp(); } m_nState=STATE_SET_UP; } } public void tearDown() { for (Iterator itr=m_dependents.iterator(); itr.hasNext();) { TestingResource testingResource=(TestingResource)itr.next(); testingResource.tearDown(); } if (STATE_TORN_DOWN!=m_nState) { Syslog.info(this, "Tearing down "+getName()+"."); if (false==m_bIsAlwaysSetUp) { doTearDown(); } m_nState=STATE_TORN_DOWN; } }

Example: testReadTablesAndMetadata private void setUpInstrumentService() { m_testingResourceManager.get(TEST_RESOURCE_ACTIVE_SESSION).setUp(); m_deephavenInstrumentService=((DeephavenInstrumentServiceTestingResource)m_testingResourceManager.get(TEST_RESOURCE_SERVICE)).getDeephavenInstrumentService(); m_instrumentSession3=((InstrumentServiceSessionTestingResource)m_testingResourceManager.get(TEST_RESOURCE_ACTIVE_SESSION)).getInstrumentSession3(); } private void testReadTablesAndMetadata() throws Exception { Syslog.info(this, "===== testReadTablesAndMetadata ====="); setUpInstrumentService(); // request metadata for regions table ReplyCatcher replyCatcher=new ReplyCatcher(); m_instrumentSession3.requestLookUp(new MultiTableSession.Filter[] {new SingleTableFilterImpl(DisConstants.Tables.METADATA_OF+DisConstants.Tables.REGIONS, new FilterPredicates.Any())}, replyCatcher, null); replyCatcher.waitForReply(); MultiTableSession.RefreshUpdate[] refreshUpdates=checkAndGetUpdatesFromReply(replyCatcher); Assert.eq(refreshUpdates.length, "refreshUpdates.length", 3); Object[][] regionsMetadataTableData=new Object[][] { {DisConstants.ColumnMetadata.COLUMN_ID, DisConstants.ColumnMetadata.COLUMN_NAME, DisConstants.ColumnMetadata.TABLE_ID}, {new Integer(200), "region_id", new Integer(9)}, {new Integer(201), "region_name", new Integer(9)}, {new Integer(202), "agg_id", new Integer(9)}, }; checkUpdateHasRows(refreshUpdates, regionsMetadataTableData); // test request with output column list // test AttriubteMatch filter replyCatcher=new ReplyCatcher(); AttributeBundle.Key[] outputKeys=new AttributeBundle.Key[] { DisConstants.Countries.THREE_LETTER_CODE, }; FilterPredicates.AttributeMatch filterPredicate=new FilterPredicates.AttributeMatch(DisConstants.Countries.REGION_ID, new Integer(1)); m_instrumentSession3.requestLookUp(new MultiTableSession.Filter[] {new SingleTableFilterImpl(DisConstants.Tables.COUNTRIES, filterPredicate, outputKeys)}, replyCatcher, null); replyCatcher.waitForReply(); refreshUpdates=checkAndGetUpdatesFromReply(replyCatcher); Object[][] countriesTableData=new Object[][] { {DisConstants.Countries.THREE_LETTER_CODE, DisConstants.Countries.REGION_ID}, {"USA", null}, {"CAN", null}, }; checkUpdateHasRows(refreshUpdates, countriesTableData); }

But Wait, There’s More All from developer PoV so far – What about users? Yes! Help users create tests that make sense to them. Same rules still apply: automated, decoupled, simple. Testing Stored Procedures and Referential Integrity Yes! All rules and behaviors are worth testing. (I haven’t done this yet.) Same rules still apply: automated, decoupled, simple.

Summary Preaching about TDD What is a Unit Test Common Unit Testing Patterns Unit Testing Database Interactions Acceptance Tests With Databases Questions?

Unit Testing Tips and Tricks: Database Interaction