Tests and DB. How to roll back the changes made by the test?

Task statement

If unit tests work with the base and change it - what to do to make the results of the run repeatable?

The answer is to clean the base before the dough. But I want to have some kind of data set in the database so that each time it is not recorded there from the tests (we will call such data the “base set”). Thus, we simplify the tests themselves and set them up many times.

Inspection solutions

How can this be implemented? Several options come to mind:

• Clear the database completely and insert into it the data of the basic set each time from a code or a script;
• cleanup script to delete new entities. But it is necessary to somehow separate the essence of the base set from the changing. There is also the danger of changing the base set of test entities;
• roll back the database to the backup before the test;
• the same, but snapshots instead of a backup.

(what other options will you offer?)

Decision making

Having worked for some time with a similar solution based on the “cleanup script”, it was decided to try something new. This is the new version with “backup”.

I note that I like snapshots more, but they are not in MS Sql Express, and I work with him.

The whole approach was developed for the .Net platform and MS Sql server.

Implementation

The first tests revealed the following problems:

• The problem of employment base. To execute RESTORE commands, exclusive access to the database is required. If there are other active connections with this database, then the execution fails.
  
  
  • It happens that tests do not close the connection; Surely, you need to close and monitor it. Or - to cover suspended sub-forces forcibly before a rollback.
  • In .Net there is a ConnectionPool. It keeps connections even after closing for increased performance. The solution is SqlConnection.ClearAllPools ();
  • Someone left can simply connect to the database. for example, through Managment Studio and spoil the build of tests. The solution is to discard these figures from the test base by closing their connections.
• The RESTORE command must accept a source for recovery. This is usually the file name on the MS Sql server. Of course, I would like to hide this detail in the DBMS itself. But it does not work. The solution could be SNAPSHOT (then only the name of the snapshot database would have to be loaded into the code / script, which is acceptable). But they are not in express.
• The speed of application backup. Since the rollback occurs ideally before each test, its speed is very critical. My small base of 11 MB was restored in 0.216 seconds, which is acceptable. The growth characteristics of this parameter, depending on the size of the base, have not been properly studied.
  
  
  • In my experience, the size of the basic set does not tend to grow as much as the project develops;
  • When compiling a basic set, you should consider minimizing its size, in megabytes.

')

So, it's time to implement.

It turns out, before the test, in SetUp, scripts will be executed to remove unnecessary connections to the database, and then restore from a backup.



User Disable Script:

DECLARE @twho TABLE( SPID int , ecid int , [STATUS] NVARCHAR(64) , [Loginame] NVARCHAR(64) , [HostName] NVARCHAR(64) , [Blk] int , [DBName] NVARCHAR(64) , cmd NVARCHAR(64), request_id INT) INSERT INTO @twho EXEC SP_WHO DECLARE spid_cursor CURSOR FOR SELECT SPID FROM @twho WHERE DBName = @dbname OPEN spid_cursor DECLARE @SpidToClose INT FETCH NEXT FROM spid_cursor INTO @SpidToClose WHILE @@FETCH_STATUS = 0 BEGIN IF @@SPID <> @SpidToClose BEGIN -- kill     . declare @str varchar(32) set @str='KILL ' + cast(@SpidToClose as varchar(16)) exec(@str) END FETCH NEXT FROM spid_cursor INTO @SpidToClose END CLOSE spid_cursor; DEALLOCATE spid_cursor; 

Script for rolling back the database:

 USE master RESTORE DATABASE [FSID_test] FROM DISK = N'c:\BackupPathHere\BackupNameHere.bak' WITH FILE = 2 

Code to call from SetUp test

 public static void RevertDb() { //      -     .     var sb = new SqlConnectionStringBuilder(Utilities.ConnectionDb) { ConnectTimeout = 2, ApplicationName = "FSID Tests, clearing" }; using (var con = new SqlConnection(sb.ToString())) { con.Open(); using (var cmd = con.CreateCommand()) { cmd.CommandText = Utilities.CommandKillAllConectionsToDb; cmd.Parameters.AddWithValue("@ dbname", sb.InitialCatalog); //       var result = cmd.ExecuteScalar(); } } //      -     , .     .  . SqlConnection.ClearAllPools(); using (var con = new SqlConnection(sb.ToString())) { con.Open(); using (var cmd = con.CreateCommand()) { cmd.CommandText = Utilities.CommandRevertTestDb; cmd.ExecuteScalar(); } } } 

Now everything looks quite simple, but in the process I had to solve several minor problems that I had not encountered before:

• ConnectionPool - amazing nearby. I searched for a long time, what infection keeps the connection.
• KILL param - it turns out that KILL cannot be called with a parameter. There is an EXEC bypass
• SELECT FROM SP () - did not know how to query the results of stored procedures. I had to read and I did not like the decision.

So, having tried all this in practice, we can safely say that the approach is viable and convenient. Features:

• Detection of loose connections in the code by tests. (if you do not clean them forcibly or add logic to the cleaning more difficult)
• The base set is explicit and not subject to change from tests. It's easy to change when you need it.
• Elimination of attempts to use db tests during a build on a continuous integration server.

UPD:

Two things are suggested in the comments:

• create a new database for each test run - avoiding the need to disable left users.
• use for rollback transactions, not backup

These approaches, like the prepositional proposal by me, are not without their shortcomings (the created database must be deleted, the transaction is not always rolled back faster and imposes restrictions on the stored procedures), but you should seriously consider them.