An integration test may use the database, The point of this exercise, therefore, is to provide scripts that allow us to backup and restore the database, so that every time our tests run, we start with the database in the same state.
Converting from embedded database to SQL
In order to enable the SQL Driver, it is necessary to run DataFlex Studio as administrator.
From the Database menu, select “Configure Database Drivers”, select MSSQL, check “Load on startup”, and select “Load now”.
Then, we select Configure Database Connection from the Database menu in Dataflex Studio. We use “Create Database”, and finish the connection.
From the Database Builder, we select “Convert to MS SQL Server”, select all tables, and click through the wizard using all the defaults. Now we have an SQL database ready to use.
Creating a database restore script
We want to be able to run integration tests repeatedly, with the same data every time. To accomplish this, we first make a backup file. From SQL Server Management Studio, right click on the database, select Tasks and Back Up. We set the Destination to a file inside our Project folder. We might even check it into source control.
If you want to automate this task, you can select the option “Script Action to File” from the Script pulldown menu. This will create a .SQL script file that you can run later. You can run the file, and it will execute the script through SQL Server Management Studio, or you can create a custom batch file where you specify details about which SQL server instance to use:
sqlcmd -S myServer\instanceName -i “C:\Projects\Automated Testing\Order Entry\backup_database.sql”
Finally, we do the reverse, to create the script to restore the database. This is the step which enables us to run the integration tests with the same data every time.
In SQL Server Management Studio, right click on the database, select Tasks, and Restore Database. The Source is our backup file (Device). In Options, we select “Overwrite the existing database (WITH REPLACE)”, and “Close existing connections to existing database”. Finally, we select “File” from the Script pulldown menu, to create the restore script file. Then, at last, we create a batch file to run the restore script:
sqlcmd -S myServer\instanceName -i “C:\Projects\Automated Testing\Order Entry\restore_database.sql”
Commiting our changes to source control
We have converted our workspace to MS SQL, and added a backup file and some scripts. We select all those changes in TortoiseHg, and commit them.
And voila, we are now ready to write integration tests that start with a fresh database every time.