Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

Test Driven Development – splitting logic from DB

by | Oct 30, 2024 | Blogging, MS Access, MS Access VBA Coding, TDD | 0 comments

In a message I sent a few days ago:

Test Driven Development Fakes Are Faking me out!

I was lamenting that some tests I was trying to write using a fake testing object, simply were returning the values that I needed to pass the test.

I’ll use a small bit of my objects and interfaces to show you what I mean:

'Part of Order_Status module which handles status conditions
'and looks up needed info in the database. BEFORE
Option Compare Database
Option Explicit

Public Const STATUS_OPEN As String = "Open"
Public Const STATUS_CLOSED As String = "Closed"
Public Const STATUS_VENDORPROBLEM As String = "Vendor Problem"
Public Const STATUS_LOCKED As String = "Locked"
Public Const STATUS_CANCELED As String = "Canceled"
Public Const STATUS_INTERNALHOLD As String = "Internal Hold"

Private Function StatWrap() As I_StatusWrap
    Set StatWrap = New ECI_StatusDbSourced
End Function

Public Function IsLineGroupReadOnly(order_id As Long, Concrete_Group As Long) As Boolean
    Dim retVal As Boolean: retVal = False
    If StatWrap.IsLineGroupReadOnly Then retVal = True
    IsLineGroupReadOnly = retVal
End Function
Visual Basic

Now what we are seeing above is the original Order_Status module. It used to have it’s own functionality attached to the IsLineGroupReadOnly public function that would query the database for the conditions necessary to determine whether the given line group for the order was read only.

I needed this to return true for a test so I created an interface which you see is returned by the StatWrap function and defaults to ECI_StatusDbSourced which implements the interface and now has the code that talks to the database.

I also created two test classes called:
T_StatusConcreteGroupLinesAllOpen
T_StatusConcreteGroupLinesReadOnly

And each of them simply implemented that IsLineGroupReadOnly to return false in the first function and true in the second function.

What happened here though, is that although that works, I ended up creating functions that aren’t really testing the business logic. Here is the code with the business logic and database call combined:

'ECI_StatusDbSourced before
Option Compare Database
Option Explicit

Implements I_StatusWrap

Private Function I_StatusWrap_IsLineGroupReadOnly(order_id As Long, Concrete_Group As Long) As Boolean
    Dim retVal As Boolean
    retVal = _
                NiceDLookup("Count(*)", "orders_line_items", _
                "Line_Valid=True And Line_Status Not In ('" & STATUS_OPEN & "','" & STATUS_VENDORPROBLEM & "','" & STATUS_CANCELED & "') " & _
                "And order_id=" & order_id & " And Concrete_Group=" & Concrete_Group) > 0
    I_StatusWrap_IsLineGroupReadOnly = retVal
End Function
Visual Basic

In this case if you blink you’ll miss the database and business logic intermingling, but notice that I’m checking the database call result to see if it’s greater than 0.

What I ended up doing was to move the comparison back into the OrderStatus object to keep the business logic there and changed the interface function to a new function that says it is counting the lines in the group for read only and valid lines:

'ECI_StatusDbSourced after
Option Compare Database
Option Explicit

Implements I_StatusWrap

Private Function I_StatusWrap_CountLinesInGroupReadOnlyAndValid(order_id As Long, Concrete_Group As Long) As Long
    Dim retVal As Long
    retVal = _
        CLng( _
            Nz( _
                NiceDLookup("Count(*)", "orders_line_items", _
                "Line_Valid=True And Line_Status Not In ('" & STATUS_OPEN & "','" & STATUS_VENDORPROBLEM & "','" & STATUS_CANCELED & "') " & _
                "And order_id=" & order_id & " And Concrete_Group=" & Concrete_Group) _
            , 0) _
        )
    I_StatusWrap_CountLinesInGroupReadOnlyAndValid = retVal
End Function
Visual Basic

And then the original Order_Status changed the public function IsLineGroupReadOnly to use the wrapper to get the count of the lines which is a pure database call.

'Part of Order_Status module which handles status conditions
'and looks up needed info in the database. AFTER
Option Compare Database
Option Explicit

Public Const STATUS_OPEN As String = "Open"
Public Const STATUS_CLOSED As String = "Closed"
Public Const STATUS_VENDORPROBLEM As String = "Vendor Problem"
Public Const STATUS_LOCKED As String = "Locked"
Public Const STATUS_CANCELED As String = "Canceled"
Public Const STATUS_INTERNALHOLD As String = "Internal Hold"

Private Function StatWrap() As I_StatusWrap
    Set StatWrap = New ECI_StatusDbSourced
End Function

Public Function IsLineGroupReadOnly(order_id As Long, Concrete_Group As Long) As Boolean
    Dim retVal As Boolean: retVal = False
    ' All valid lines must either be canceled or in a non-read-only status
    If StatWrap.CountLinesInGroupReadOnlyAndValid(order_id, Concrete_Group) > 0 Then retVal = True
    IsLineGroupReadOnly = retVal
End Function
Visual Basic

And what this allows me to do is to create fakes that can return the number of lines I want vs just overriding the function that contains the business logic to make it true or false.

I can now fake the database itself and feed the values into the same logic object and I will be testing the logic itself based on what’s in the database vs what I want the logic to tell me.

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.
    Built with Kit

    Submit a Comment