3

I understand there are many questions in this site revolving around the same concept, but I could not get a precise answer for my case.

Problem

I am handling an ERP System, with code base in both:

  • VB.NET/ASP Forms for the user interface portion of the system
  • TSQL Stored Procedures on SQL Server, handling business logic.

So both database tables and database stored procedures are tightly coupling data and business logic

We have been trying to figure out a way to automate unit tests to help us detect regression and other potential bugs. But the problem with our setup is the presence of database as a major component of our system's business logic; Databases retains states, i.e. we cannot run something like xUnit with independent classes. because each test will alter that state of our system, by changing one of the records of data tables, lookup tables or control tables.

Constraints

We are not expected to shift our logic to the VB portion of our code (For political reasons)

Note that mocking database will not solve our problem, because business logic runs on stored procedures, that is by design relying on state of data

Question

What are our options?

  • is there a way to implement automated unit/integration testing?
  • Are we stuck in manual testing?
  • Should we place our environment in a farm of virtual machines with the same baseline and run independent tests on different machines?

Unfortunately, I could not find any literature related to best practices for TDD and Stored Procedures based systems.

A.Rashad
  • 594
  • 3
  • 19
  • 1
    You can't use a test database? We solved a similar issue changing the connection string to point to a test database, running the procedures and then querying the database to see if they did what they are supposed to do. You can make your first test database using a backup from the production data if you don't need to anonymize anything. Also - keep in mind that TDD doesn't lend itself _that well_ towards managing legacy code. Unit Testing is good, but by itself it doesn't make TDD. – T. Sar Aug 22 '17 at 13:03
  • Possible duplicate of [What is the most effective way to add functionality to unfamiliar, structurally unsound code?](https://softwareengineering.stackexchange.com/questions/135311/what-is-the-most-effective-way-to-add-functionality-to-unfamiliar-structurally) – gnat Aug 22 '17 at 13:03
  • @T.Sar test database contains big portion of my code. And code relies on the state of data. so when I call a certain stored procedure, it will alter contents of a table or two. That means we need to rewind our database to a baseline after each test – A.Rashad Aug 22 '17 at 13:37
  • Can you give an example of what behavior you're trying to test that depends that much on the state of the data on the Database? – T. Sar Aug 22 '17 at 13:47
  • A file is placed in a predefined location, picked up by windows service, that will parse the file using control and lookup tables and insert its contents into a temporary table. then calls some stored procedures that will read those contents, validates its sanity (based on rules in a table) and perform some accounting calculations based on historical records and the records added in this file. this is one simple scenario. – A.Rashad Aug 22 '17 at 13:51
  • 1
    @A.Rashad - You can certainly mock and fake database tables to do this easily... Look at frameworks such as tSQLt: http://tsqlt.org/ – Milney Aug 22 '17 at 14:26
  • 1
    I would suggest that you abandon the concept of Unit Tests in the pure form and focus on testing the most important pieces of the system: the stored procedures. Focus on having a single script clear our the database and prepare it for a full test run of your app in a testing location, pointed to a test version of the database, with some known set of data that is the state of the table if the tests pass. – Graham Aug 25 '17 at 18:58
  • @Graham So instead of unit testing stored procedurs directly, I should have an alternate Web App like interface calling test cases in integration testing manner? – A.Rashad Aug 25 '17 at 19:01
  • @ARashad Yeah, and you can automate the web app testing with a tool like Selinium, which will load up whatever pages you like, set the fields, click buttons, verify page text, etc. Basically, you'll have the script that clears out the test SQL database, then you'll run your Selinium test suite (or just click through the web app manually), then you'll ideally have a SQL script that looks for expected values (totals, counts, etc) in the database. – Graham Aug 25 '17 at 19:05
  • 1
    It won't be as fast as code-only unit tests, but it means you'll be testing both the database stuff (sprocs, triggers, etc) AND the web app behavior (button clicks, links, etc). – Graham Aug 25 '17 at 19:05

4 Answers4

8

I've implemented automated testing including the database in a system with some of the same problems. The approach I took was roughly:

  • Before all unit tests, bring up a DB from scratch with schema and no data
  • Before each test case, truncate all the relevant db tables

This works, though it does produce very slow tests.

Once you have regression/characterization tests in place, you should begin decoupling the application logic from the database. Hide the business logic behind an interface and use inversion of control so that you can pass in the implementation. You'll start with the db-backed implementation, and in tests you can use an in-memory version that's lightweight and faster.

For example, if you have:

def frob_widgets(foo, bar):
    if db.some_query(params=blah):
        foo.flip()
    bar.calcinate(foo)
    return db.explode(bar, foo)

Change the signature to def frob_widgets(db, foo, bar)

You may find a tool like ephemeral_pg useful, which helps to run a postgres database as lean as possible by running in memory with fewer safety/reliability checks enabled

Daenyth
  • 8,077
  • 3
  • 31
  • 46
  • If you can get the DB to run in memory, or switch to one that does, it may help. – Frank Hileman Aug 22 '17 at 23:05
  • @FrankHileman:any idea how one easily switch to a MSSQL compatible, lightweigt in-memory database which supports stored procedures in TSQL? I have none. For me it looks you are suggesting the OP to throw his system away and start from scratch - not very constructive. – Doc Brown Aug 24 '17 at 05:45
  • @DocBrown Virtual machines is a start. I don't know of the latest techniques, but there may be a lighter way to do it. – Frank Hileman Aug 24 '17 at 19:52
  • @FrankHileman: VMs will help to install a test database quicker, especially if that has to be repeated. But they will not make the tests run "in memory" or faster, quite the opposite. – Doc Brown Aug 25 '17 at 08:25
  • You need to run the same database in test that you use in production for these tests to be properly valuable. Tune it to not use journaling, not sync to disk if possible, maybe make the DB volume mounted on a ramdisk. – Daenyth Aug 25 '17 at 10:50
  • @DocBrown I had a colleague with the same problem recently, so I am interested in a better solution. I was thinking a VM running in memory entirely, not super realistic. Running in a ram disk is the only thing I can think of to speed it up. The real problem is the installation, setup is so heavy and slow with SQL server. Seems like there must be a better solution. – Frank Hileman Aug 25 '17 at 18:03
  • In that case you could let the server be persistent and perhaps use multiples schemas, or just let the process live across test runs? – Daenyth Aug 27 '17 at 02:15
4

Assuming all logic is within your DB, in stored procedures, and you simply cannot change that in your project, I'd do the following:

  1. (optional) Refactor your stored procedures in order that each one has one clear responsibility, and that you can map that for an input A, there will be an output (or some specific data in a table) B;
  2. have a test database, where you will clear all data, and insert specific data on-the-fly during the unit tests execution;
  3. With that, create your unit tests as stored procedures, and also stored procedures to insert specific input data (input A from step 1), and stored procedures to clear all current data;
  4. Each test procedure (unit test) tests One of your logical stored procedures, and inserts data as input A and expects the procedure to generate output data as output B, if that is done then the test PASSES, otherwise if FAILS;
  5. (optional) the test procedures exist only in the test database; you could also have a separate UnitTests table, which contains the name of the stored procedure and its last status of execution; with this, you could create a UnitTestManager stored procedure, that will lookup in the UnitTests table, and dynamically calls the tests procedures, and updates the UnitTests data. Later, after execution, you can check for detailed information of failures and successes within you UnitTests table.
Emerson Cardoso
  • 2,050
  • 7
  • 14
1

You can try to build mocks, stubs and fakes around your units as replacement for the dependencies.

I haven't got much experience with VB.NET but I'm sure there are unit testing frameworks out there for you to find.

A colleague recently pointed me at an article from Martin Fowler. I hope that can help you find your way as well.

bemk
  • 39
  • 6
0

I just thought of a theoretical solution to this problem as follows:

  1. Create a transaction
  2. Do your testing

  3. Rollback the transaction 

    Begin Transaction test_case
   -- Test Preparation Steps
   ...
   -- Assertion Steps 
   ...
Rollback transaction test_case

This will guarantee that tests will not alter the state of the tested system, unless of course one of the tested steps involves DML.

There is a drawback in this approach, that we can't save test results in this database. instead, we can place results in some report or a spool file

A.Rashad
  • 594
  • 3
  • 19