The Effectiveness of Test Coverage Criteria for Relational Database Schema Integrity Constraints

Abstract

Despite industry advice to the contrary, there has been little work that has sought to test that a relational database's schema has correctly specified integrity constraints. These critically important constraints ensure the coherence of data in a database, defending it from manipulations that could violate requirements such as “usernames must be unique” or “the host name cannot be missing or unknown.” This article is the first to propose coverage criteria, derived from logic coverage criteria, that establish different levels of testing for the formulation of integrity constraints in a database schema. These range from simple criteria that mandate the testing of successful and unsuccessful INSERT statements into tables to more advanced criteria that test the formulation of complex integrity constraints such as multi-column PRIMARY KEYs and arbitrary CHECK constraints. Due to different vendor interpretations of the structured query language (SQL) specification with regard to how integrity constraints should actually function in practice, our criteria crucially account for the underlying semantics of the database management system (DBMS). After formally defining these coverage criteria and relating them in a subsumption hierarchy, we present two approaches for automatically generating tests that satisfy the criteria. We then describe the results of an empirical study that uses mutation analysis to investigate the fault-finding capability of data generated when our coverage criteria are applied to a wide variety of relational schemas hosted by three well-known and representative DBMSs—HyperSQL, PostgreSQL, and SQLite. In addition to revealing the complementary fault-finding capabilities of the presented criteria, the results show that mutation scores range from as low as just 12% of mutants being killed with the simplest of criteria to 96% with the most advanced.

AVMf: An Open-Source Framework and Implementation of the Alternating Variable Method

“…In 2007, Harman and McMinn [7] reported its effectiveness and efficiency for a series of C programs, and combined it with a GA to provide a "best of" Memetic Algorithm approach [8]. It has since been implemented into tools to generate test data for C programs (e.g., IGUANA [17] and AUSTIN [14,15]); generate Java test suites with EvoSuite [3,4]; create relational database data with the SchemaAnalyst tool [9,18]; and combined with dynamic symbolic execution in Microsoft's Pex tool [16]. The AVM has also found application to additional problems, including decision ordering for software product lines [22], balancing workload in requirements assignment [21], solving reliability-redundancy-allocation problems [20], as well as test case selection [19] and test suite prioritization [2].…”

“…The AVM has also found application to additional problems, including decision ordering for software product lines [22], balancing workload in requirements assignment [21], solving reliability-redundancy-allocation problems [20], as well as test case selection [19] and test suite prioritization [2]. Since Korel's original work, the AVM has been extended and improved for problems in SBSE: now it can handle more variable types, including fixed-point numbers [7] and strings [9,18], and can leverage new strategies proven to speed up the search for certain common types of objective function landscape [10,11].…”

“…"Lattice Search" (LS) is a slightly faster alternative to GS where the unimodal assumption holds. LS converges on the optimum through moves that increase x i from the lower value of the bracket through the addition of Fibonacci numbers (lines [16][17][18][19][20][21][22].…”

“…Exploratory moves correspond to the smallest possible increments and decrements of the variable (i.e., ±10 −p ). Strings may also now be handled by the approach [9,18]. A string variable is essentially 1: while true do ⊲ {IPS}…”

mrstudyr: Retrospectively Studying the Effectiveness of Mutant Reduction Techniques

“…As studying the mutant data retrospectively removes the need to comprehend the complexities of a target environment, mutant reduction methods can be extended to new domains such as that of relational database schemas [19], [20], [21]. Ensuring that a database's schema has correctly specified integrity constraints is important because these entities ensure that only valid data enters the database.…”