Unit Testing Tips and Tricks: Database Interaction

1. Unit Testing Tips and Tricks: Database Interaction Louis Thomas

2. Overview Attitudes Toward Testing What is a Unit Test Common Unit Testing Patterns Unit Testing Database Interactions Acceptance Tests With Databases

3. Attitudes Toward Testing

4. 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!

5. What Is A Unit Test

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

7. 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. • Fast. • No configuration or external dependencies.

8. Common Unit Testing Patterns

9. 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.

10. 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?)

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

12. Example! 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.

13. 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(); } }

14. Example! Mock Objects • A mock object is a stand-in for a complex system object. • Start out as simple as possible (throw exceptions on all methods). • Add recording of incoming method calls.

15. useful 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"; } }

16. Get activity details and clear StringBuffer. Accumulate activity details into a StringBuffer. 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); } }

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

18. Accumulate canned responses into List. Return next response from List on each call. 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(); } }

19. Example! Mock Objects, cont’d • Add whatever functionality 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.

20. Thumbprinter allows customization of how activity is recorded, per unit test. Flag allows disabling recording of uninteresting activity, per unit test. 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)+")"); } } }

21. Mock Object Frameworks • If you don’t want to create mock objects by hand, consider a framework that does it by reflection: • EasyMock (http://easymock.org) • jMock (http://www.jmock.org/)

22. 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 utility class (object mother) so you can use it as necessary thereafter.

23. Example! 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

24. Default behavior can be modified for testing. View and modify important private variables. Invoke private methods or other custom behavior. Easy to set up. 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(); }

25. Unit Testing Database Interactions

26. Example! Testing Database Interactions • All the database classes are interfaces already! How convenient! • Create mocks and away you go. • Insert / update / delete • (relatively) straight forward

27. SQL string we want to test. Method call on Statement. Example: writeChangeStepsToDatabase publicstaticvoid writeChangeStepsToDatabase(Statement dbStatement, InstrumentListChangeRequest instrumentListChangeRequest, int nParentId) throws SQLException { InstrumentListChangeRequest.ChangeStep[] changeSteps = instrumentListChangeRequest.getChangeStepsSnapshot(); StringBuffer stringBuffer = new StringBuffer(); for (int nIndex = 0; nIndex<changeSteps.length; nIndex++) { InstrumentListChangeRequest.ChangeStep changeStep = changeSteps[nIndex]; stringBuffer.setLength(0); changeStep.persistTo(stringBuffer); 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(stringBuffer.toString()) +")"; // send it to the database int nRows = dbStatement.executeUpdate(SqlFormatHelper.showSql(sSql)); if (0==nRows) { thrownew SQLException("Failedtowritetodatabase."); } } }

28. Set up MockStatement. Invoke method to be tested, passing mock. Verify correct behavior seen. 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()); } }

29. Beforehand, Handlers registered with SQL string they support. SQL string compared against known updates to find handler. Handler invoked to process update. 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); } } //...

30. Code to configure generic MockStatement for this group of unit tests factored out into Object Mother. SQL string to be handled. Build custom handler. Parse SQL string and add new row to backing store. 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 (int nIndex = 0; nIndex<columnNames.length; nIndex++) { Object columnValue = simpleSqlTokenizer.getNextParameter(); String sColumnName = columnNames[nIndex]; row.set(sColumnName, columnValue); } return 1; } }; }

31. Example! Testing Database Interactions • Read – must return something – trickier. • 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 expectations and returns, but will probably refactor into more general objects.

32. SQL string we want to test. Method call on Statement. Method calls on ResultSet. privatestaticvoid checkForNull(ResultSet resultSet, String sColumnName) throws SQLException { if (resultSet.wasNull()) { thrownew SQLException("Unexpectednull for column"+sColumnName+"."); } } Example: readChangeStepsFromDatabase publicstatic InstrumentListChangeRequest readChangeStepsFromDatabase(Statement dbStatement, int nParentId, int nExpectedSteps) throws SQLException { InstrumentListChangeRequest.ChangeStep[] changeSteps = new InstrumentListChangeRequest.ChangeStep[nExpectedSteps]; int nFoundSteps = 0; // process all the rows String sSql = "SELECT * FROM"+TABLE_NAME+" WHERE " +FIELD_PARENT_ID+" = "+FORMAT_PARENT_ID.format(nParentId); ResultSet resultSet = dbStatement.executeQuery(SqlFormatHelper.showSql(sSql)); 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)); //…

33. Method call on ResultSet. Example: readChangeStepsFromDatabase //… if (nStepNum<0 || nStepNum>=nExpectedSteps) { thrownew SQLException("Found change step"+nStepNum +"but expected 0 < changeStep <="+nExpectedSteps+"."); } elseif (null!=changeSteps[nStepNum]) { thrownew 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) { thrownew SQLException("Found only"+nFoundSteps+"change steps out of" +nExpectedSteps+"expected."); } InstrumentListChangeRequest instrumentListChangeRequest = new InstrumentListChangeRequest(); instrumentListChangeRequest.setChangeSteps(changeSteps); return instrumentListChangeRequest; }

34. Set up MockStatement. Invoke method to be tested, passing mock. Verify correct behavior seen. Example: testReadChangeStepsFromDatabase publicvoid 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()); }

35. Beforehand, Handlers registered with SQL string they support. SQL string compared against known queries to find handler. Handler invoked to process query. Example: MockStatement publicclass MockStatement implements Statement { publicinterface QueryHandler { ResultSet handleQuery(String sql) throws SQLException; } private Map m_stringMatcherToQueryHandlerMap=new TreeMap(new SystemWebDirectory.StringMatchComparator()); publicvoid 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) { thrownew SQLException("Unexpected query\""+sql+"\"."); } else { return queryHandler.handleQuery(sql); } } //...

36. Code to configure generic MockStatement for this group of unit tests factored out into Object Mother. SQL string to be handled. Build custom handler. Parse SQL string “where” clause to find IDs. 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 })); } privatestatic MockStatement.QueryHandler getSelectByIdHandler(final SimulatedDatabase.Table table, final String[] columnNames) { returnnew 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(); } //...

37. Create temp table to hold results. Find all matching rows and copy to result table. Return mock ResultSet that acts as Iterator over result table. Example: setUpChangeStepTable // 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()); } } returnnew BaseMockResultSet() { protectedint getTableSize() { return resultTable.getRowCount(); } public Object getObjectInternal(String columnName) throws SQLException { return resultTable.getRow(m_nIndex).get(columnName); } }; } }; }

38. Simple interface for derived classes. Manage iterating through rows. Implement JDBC null handling. Example: BaseMockResultSet publicabstractclass BaseMockResultSet implements ResultSet { protectedint m_nIndex = -1; privateboolean m_bWasNull = false; protectedabstractint getTableSize(); protectedabstract Object getObjectInternal(String columnName) throws SQLException; publicboolean next() throws SQLException { m_nIndex++; return m_nIndex<getTableSize(); } publicvoid close() throws SQLException { // do nothing } publicvoid setWasNull(boolean bWasNull) { m_bWasNull = bWasNull; } public Object setWasNull(Object object) { m_bWasNull = null==object; return object; } publicboolean wasNull() throws SQLException { return m_bWasNull; } //...

39. Getting a string in the current row is easy. Getting integers is similar. Example: BaseMockResultSet //... public String getString(String columnName) throws SQLException { Object columnValue = setWasNull(getObjectInternal(columnName)); if (null==columnValue) { returnnull; } else { return columnValue.toString(); } } publicint 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) { thrownew SQLException("Value"+sValue+"of column"+columnName +" can't be converted to int."+e); } } } No other methods used, so no other methods implemented.

40. List of column names List of Rows Example: SimulatedDatabase.Table publicstaticclass Table { private String[] m_columnNames; privateint m_nColumns; private Map m_columnNameToColumnIndexMap; private List m_rows=new ArrayList(); 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; } publicint 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); }

41. Example: SimulatedDatabase.Table //... publicvoid 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; } //...

42. Example: SimulatedDatabase.Table //... privatevoid 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)); } } privateint getIndexForName(String sColumnName) throws SQLException { Integer columnIndex=(Integer)m_columnNameToColumnIndexMap.get(sColumnName); if (null==columnIndex) { thrownew SQLException("Unknown column name\""+sColumnName+"\"."); } return columnIndex.intValue(); } //...

43. An object for each column. Example: SimulatedDatabase.Table.Row publicclass 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)]; } publicvoid set(String sColumnName, Object object) throws SQLException { m_objects[getIndexForName(sColumnName)]=object; } public Object get(int nColumnIndex) { return m_objects[nColumnIndex]; } publicvoid set(int nColumnIndex, Object object) { m_objects[nColumnIndex]=object; } public Object[] getAll() { return m_objects; } } }

44. Acceptance Tests With Databases

45. 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.

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

47. Example! 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 database. (Use Excel to edit.) (Just 214 lines.) • ExecuteSqlScript: processes a text file of SQL commands. (Just 222 lines.) • Used to create tables, etc. • ExecuteDatabaseSetupScript: allows me to write little scripts (Just 205 lines.) • Knows about 5 commands, including BulkLoadData and ExecuteSqlScript

48. Name of next table. First row is column names. All the column values for a row. Sequences can be easily reset. 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

49. Macros can be defined. Keyword to ignore errors for one statement. All other errors are fatal. SQL statements delimited by semicolons. 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;

50. Identifies database config by name from system properties. Executes SQL script by file name. Executes child setup script by file name. Convenient truncate of table before bulk load without requiring separate SQL script file. Executes bulk load by file name. 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