Identify currently executing test in the Test Explorer

[BluejacketScott]

It would be great to be able to determine the order in which test are run. Better yet, specifying the order would allow for devs to push known failing tests (i.e. methods that haven't been implemented yet but their tests have) to the end of the run.

Additionally (and this might be another thread) it would be very useful to be able to stop the test run if something goes off the rails right away such as having an invalid initial state in the host. I am developing in Access and sometimes table links are broken for various reasons (for example.)

Thoughts?

[bclothier]

Hmm. One thing is that the unit tests are supposed to be isolated and self-contained. Therefore, it should not matter what order you run them in. It makes sense for a module to do a setup (that'd be where you'd initialize the mocks, etc.) so that all methods have a "context" to run in but after that, it should be basically self-contained and not matter what order.

Furthermore, the typical thing to do when the context is invalid due to external factor is to make the tests inconclusive. In other words, the test should have only one reason to fail; and it should not fail if the context isn't valid. That's some other test's responsibility. Otherwise, you'd get a mess of failed tests and no idea what went wrong.

[retailcoder]

This goes against one of the principles of unit testing, or encourages having tests that depend on each other - good tests are isolated and don't depend on the outcome of other tests, so the order in which tests are executed should be utterly irrelevant.

As for cancelling... that's not quite possible. See #274 - we need to fire up a command in the VBIDE UI, but then, the tests are running on that thread (the only thread they can ever run on), so I've no idea how to go about this.

[BluejacketScott]

@bclothier I totally agree that tests should be able to run isolated and that's the way I'm building them.
@retailcoder I'm not wanting interdependent unit tests, that would be integration testing. However, I do think there's value in at least knowing which test is running because several of my tests utilize the same custom progress bar and insight into what is happening would be useful. I really don't NEED to order the tests at all - just thought it would be nice to be able to make order changes to be sure I don't have any interdependence creeping in. Even random would be fine if I could trace them somehow.

Here's an argument for knowing which tests are running: With referential integrity enabled, the ACE engine can cascade updates and deletes to other tables with a simple query. However, if that query fails for any reason it will leave the database in an inconsistent state (orphaned records etc). In order to suppress Access' warnings about appending records etc causing message boxes to appear every time a query is run, we issue a DoCmd.SetWarnings False, run the queries, then DoCmd.SetWarnings True. In that scenario, a failed query doesn't give any indication that something went wrong but fails silently. I need to know if a query fails and without knowing which query is running, there's no way to trace why the database is inconsistent.

I haven't seen any way to mock a query run such that I can trap errors without also enabling all the warnings dialog boxes. If you can think of a way to do that, I'd be very interested to learn how. The only way I can think of to do it is to '@ignoretest all tests that run any query and enable them one at a time - rerunning all tests (which takes a while on my very complex database) and examine the state of the tables. That would be a very painful way to figure out which query is not behaving properly. That's why knowing which test is running or at least tracing the order in which they are run could provide insight into where the problem lies.

The only alternative I can think of is to run the SQL in VBA and test that way but that causes a HUGE performance penalty because ACE can compile a query and reuse it rather than compiling it every time the VBA SQL is run.

Feedback is very welcome. Oh, and by the way - you guys are really great at responding quickly - thanks for that!

[retailcoder]

Oh, knowing which test is running is an entirely different problem - and completely feasible! Great suggestion!

[bclothier]

@BitSprocket Addressing your other questions -- I appreciate the difficulty you might be having here because those are not easily mockable and in reality, we should be abstracting the result of queries so that's there's no database. This blog may be of help in illustrating.

That said, I see few options here that would get you set up fairly quickly:

1. In your setup, you should create a new Access file with the tables you need and test against that. Then trash that file. That way you have a disposable database file that you can restart as many time as you want and not worry about results affecting one other.

2. I do not buy the idea that VBA SQL is that slow. I explicitly avoid using DoCmd for most of anything because it's horrid and has too many side effect and and can only work on an active object. The cost of compilation is what? At most 500 milliseconds. If your query requires a compilation time that is much longer than that, then I think there's something very strange. Even so, you still can execute parameterized queries via VBA with no problem and not worry about recompiling.

3. For same reason you shouldn't use DoCmd, DoCmd.SetWarnings is hiding too much from you. You should be using VBA to .Execute the query and use its error handling to trap any errors as a result of executing the query.

4. If this is appropriate, consider using DBEngine.BeginTrans + DBEngine.Rollback -- you don't need to commit. That would always reset your database. Because tests (ATM, at least) can't run in parallels, there should be no blocking.

[BluejacketScott]

@bclothier Those are great suggestions.

1. Check - doing that already.

2. My clients have a very slow network because they have to enable encryption of data at rest and have active virus scanning running all the time on every host and server. That overhead means I need every ounce of performance I can get and thus, compiled queries are very important. (And no, no access to a SQL server...) I don't have metrics to reference but this article talks about query performance - specifically mentioning compiled queries. Also, I've never had a problem with DoCmd. Can you point me to a resource?

3. .Execute is a great thought. Thanks for that. Hopefully it will throw an error for me to catch!

4. I'll have to research BeginTrans to see if that would help. I don't currently use it but it could very much be worthwhile. Thanks for that.

[bclothier]

Regarding the request for source RE: performance of compiled query -- I do not have the whitepaper directly and Microsoft has made it hard to find old whitepapers but there is a quotation here that recommends executing dynamic SQL to optimize the query at runtime.

As for DoCmd, this is my personal advice. Just don't rely on it because it basically simulates users' interaction, with all the UI involved. That's just horrid way of programming things. We should be replicating the underlying code (e.g. the .Execute) not the UI behavior of DoCmd.OpenQuery.

[retailcoder]

@bclothier Caveat: if Access doesn't support nested transactions, BeginTrans could cause problems if you're testing a procedure that commits a transaction. Just sayin' 😉
Also tests will never be able to run in parallel, at least for as long as VBA is running on a single thread and the VBE isn't updated (!) to allow invoking a macro while a macro is already running.

[BluejacketScott]

@bclothier From the article you posted:

If you build a SQL string in code or at runtime, then often the results are better and faster!

Well, that changes things doesn't it? Now I have to rethink my query strategy...

[bclothier]

@retailcoder actually it depends on the source. For an Access backend source, nested transactions are possible. For an ODBC source, it's not supported. But for unit testing, we shouldn't be dealing with external data source; only a temporary local database. No need to involve network errors in those tests.

@BitSprocket FWIW - if you still want to parameterize, you might be able to get it with ADO instead of DAO. But TBH, I almost never use ADO w/ Access sources; only external sources generally and I don't know if ADO would give you any better performance with parameters than DAO. I doubt so, since you're still relying on the Access optimizer which isn't quite as sophisticated as SQL Server's is.

[Hosch250]

FWIW, we have an attribute to ignore tests.

[retailcoder]

@Hosch250 from this post:

The only way I can think of to do it is to '@ignoretest all tests that run any query and enable them one at a time

😉

I'm going to re-purpose this issue in a minute.

[retailcoder]

Let's make the test engine instruct the test explorer to toggle some "running" test status (perhaps display it with some arrow icon) just before invoking a test method. The challenge will likely be to get the UI to repaint correctly before the test is actually running.