Alternative naming convention for Test stored procedures

Jul 9, 2009 at 8:25 PM

Would it be possible to add a feature to identify Test stored procedures based on a (configurable) schema rather than a prefix?  Perhaps even the SQLTest_ prefix could be configurable.  For example:

SQLTest_City_Insert becomes SQLTest.City_Insert or test.City_Insert

Jul 10, 2009 at 9:51 PM

T.S.T. will allow you to place the test procedure in a schema already. Now, if you want all the sprocs in a certain schema to be picked up by T.S.T. as test procedures without you having to also prefix them that may not necessarily be a good idea. Here is why:

It is common for any non trivial system to have the following types of procedures involved in tests:

  • SETUP procedures (the ones that are prefixed with SQLTest_SETUP_).
  • Test procedures (the ones that are prefixed with SQLTest_).
  • Helper procedures that called by various setup and test procedures. These helpers are created specifically to support testing and have no role in the production system.

T.S.T. needs a way to distinguish between the types. If T.S.T. was to pick up all the procedures in a certain schema and treat those as test procedures then you won’t be able to place helpers in that schema. I think that would create more problem than it solves. At least you will have to create a separate schema for test helpers.

I can see how writing Test.SQLTest_CityInsert seems redundant (because you have both the “Test” schema and the SQLTest prefix) but sooner or later you will want to have helper procedures in the “Test” schema. At that point the prefix will make sense.

Also I like the fact that there is a level of standardization. Being able to configure the prefixes has (I think) marginal benefits. Keeping them not configurable can simplify readability and make things less complicated.

If I miss something here please let me know. I am open to add new features but in the same time I want to be very careful when doing so.

May 5, 2010 at 10:35 PM
Edited May 5, 2010 at 10:39 PM

No, there are a LOT of benefits to doing this.

1. Test suites

I create a bunch of unit tests to test various objects and features.  I then decide that I want to break all of those unit tests into test suites.  At that point, it means that I have to rename all of my objects.  I have to delete all of the existing unit tests from my source control system and re-add all of the tests with new names, losing all of the version history.  I have to do this, because the framework demands that your unit tests are in the dbo schema and test suites have a specific naming convention.  By allowing unit tests to be created in multiple schemas, you get rid of all of this string parsing of names, and you can configure test suites just by using schemas.

2. Central management

We have some instances with 60+ databases.  We obviously want to build unit tests for all of them.  But, this requires us to create 60+ additional databases.  Each database now has a companion unit test database, just so that we can isolate the unit tests for one database from the unit tests for another database.  It would be a LOT cleaner to just be able to separate the unit tests with schemas.

We do not put the unit tests in the actual database being tested, because that introduces all kinds of issues.  A. If you are refreshing the development databases using a production backup, you would wipe out all of the unit tests in the process and then have to reload everything.  B. If you want to refresh dev from prod without wiping out the unit tests, then that means you have to deploy your unit tests into your production database - not a good idea.  C. Having unit tests within your development database creates significant clutter since right now they are required to be created in the dbo schema, it can double (or more) the number of objects in the database, and make production deployments much more error prone and complicated.

3. Basing functionality on string parsing names is extremely brittle and error prone

While the claim is that there is a low learning curve, named schemas can turn this into virtually no learning curve.  There is a learning curve to this framework that goes against everything that people have been taught since schemas were introduced in SQL Server 2005.  They have to learn that they MUST put everything in the dbo schema and failure to do so breaks the system.  They MUST use a very specific naming convention and failure to do so breaks the entire system.  Basing functionality on string parsing object names is extremely brittle and is something that is strongly discouraged in every programming language and every programming environment.

4. Security

Schemas aren't a naming convention.  Schemas are a security structure.  Schemas are used to separate one group of objects from another.  Schemas are also used to control access to a group of objects.  As it currently stands, if I have access to create/alter/drop a single unit test, I can do that for every single unit test.  If these were put into schemas, then I could restrict access to the unit tests allowing one group of people to write unit tests for one set of functionality without interfering with another group writing unit tests for a different piece of functionality.

5. Complicated naming conventions

The less complicated argument also doesn't work.  Requiring a specific naming convention actually complicates things.  It becomes complicated, because I then need to create yet another naming convention on top of your naming convention just to make things work.  I can have the same "object name" in a database as long as it is in different schemas.  So I could have schema1.myproc, schema2.myproc, and schema3.myproc.  So I now have to add in another naming convention that says, all of your unit tests must start with dbo.SQLTest_ and then you add <schema name>_+<object name>.  If you take the fact that the test suite feature is based on additional pieces of a naming convention, which I would then need to add a schema name and an object name into it to even be able to create the unit test.  By forcing a naming convention and not allowing schemas, it actually makes things much more difficult.

6. Helper procedures

The argument about helper procedures also doesn't make any sense here.  As it currently stands, if I create helper procedures that do not start with dbo.SQLTest_, then they are ignored.  If I put my helper procedures in one or more schemas, they are ignored by the existing code and can just as easily be ignored by code that allows named schemas.

May 9, 2010 at 3:06 PM


T.S.T. will allow you to place the test procedure in a schema. You don't have to place them in the dbo schema.
However, once placed in the schema of your choice, TST will differentiate between the tests and the helper ones based on the prefix. Let me know if this does not solve the issues you raised.