Prepared for the Ed-Fi Alliance by: Jonathan Hickam, Learning Tapestry

Introduction

This document has been prepared as part of a larger initiative that is looking at scalable, economical, and reusable solutions for level 2 validations.  For further context, architecture, and vocabulary refer to the associated Ed-Fi Validation Architecture document.

A high-level assessment of a wide variety of open source solutions was undertaken that could potentially address all or some of the Ed-Fi Level 2 Data Validation System Requirements.  A high-level analysis of the tools that were considered can be found here.  However, the final approach taken was to take a deeper look at three different types of solutions from this list. The first one was using a database-centric testing framework.  The second was to look at an open-source business rules engine. The third was to look at what a pure-SQL implementation without external tool dependencies might look like.

Testing framework solution

Test driven and continuous integration development methodologies are widely considered best practices in contemporary software development shops, especially those embracing agile development.  The ubiquity of these approaches has led to a wide variety of automated testing frameworks, some of which are very database-centric. Our hypothesis is that such a tool could be useful for implementing a data validation testing infrastructure.

First try- DB Fit

Our research identified a couple of strong candidates for a database-centric testing infrastructure. The first one that was prototyped was DB Fit (http://dbfit.github.io/dbfit/). DB fit is a database-centric testing toolset that is modeled after the J-Unit or N-Unit testing infrastructures that are commonplace in Java and .Net respectively.  DB Fit was designed for projects that have significant database code and want to embrace test driven development without requiring a Java or .Net wrapper around all of the tests. DB Fit is open source and has an active user and developer community.  

DB Fit uses JDBC connectivity, so it is database agnostic.  This flexibility proved to be problematic with our time-boxed analysis however.  THe JDBC driver requires a JDK and some changes to the default SQL Server security module.  After spending an hour on this with little success we decided to look at something with less initial configuration.

Second try- tSQLt

The second attempt looked at a testing infrastructure that was written in T-SQL, and very tightly coupled with Microsoft SQL Server.  tSQLt (https://tsqlt.org/) is a unit testing infrastructure that also is open source with decent user and developer activity.  This tool installed with minimal configuration and allowed us to get some tests up quickly.

For our proof-of-concept, we implemented a test of the validation rule that students should have a physical or home address.  The tests are written in T-SQL.

Detailed steps to recreate the prototype

1. Download, install, and configure tSQLt to run on the Ed-Fi database as described in their getting started page. 

2. Write a test in T-SQL.

   EXEC tSQLt.NewTestClass 'testStudentAddress';
   GO
   
   CREATE PROCEDURE testStudentAddress.[test that every student has a home or physical address]
   AS
   BEGIN
       DECLARE @NoAddressCount INT;
       DECLARE @NoAddressThreshold INT; SET @NoAddressThreshold = 0;
   
       SELECT @NoAddressCount = count(distinct c.studentUSI)
   	from edfi.student c
   	JOIN edfi.StudentSchoolAssociation on c.studentUSI = StudentSchoolAssociation.StudentUSI
   	LEFT JOIN 
   		(SELECT studentUSI, b.CodeValue
   		FROM edfi.studentAddress a
   			JOIN edfi.AddressType b	on a.AddressTypeId = b.addressTypeId
   		and b.CodeValue in ( 'Physical', 'Home') 
   		) d on c.studentUSI = d.studentUSI;
   
   	EXEC tSQLt.AssertEquals @NoAddressCount, @NoAddressThreshold;
   END;
   GO

3. Run the test

   EXEC tSQLt.Run 'testStudentAddress';

4. Review the results 

   1 row affected)
   [testStudentAddress].[test that every student has a home or physical address] failed: (Failure) Expected: <21042> but was: <0>
    
   +----------------------+
   |Test Execution Summary|
   +----------------------+
    
   |No|Test Case Name                                                               |Dur(ms)|Result |
   +--+-----------------------------------------------------------------------------+-------+-------+
   |1 |[testStudentAddress].[test that every student has a home or physical address]|     36|Failure|
   -----------------------------------------------------------------------------
   Msg 50000, Level 16, State 10, Line 27
   Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
   -----------------------------------------------------------------------------